§ 20. Формули в електронних таблицях
Для опрацювання даних за допомогою формул в Excel потрібно зазначити, у яких саме клітинках таблиці містяться ці дані.
20.1. Адресація
Як ви вже знаєте, кожна клітинка (діапазон клітинок) ЕТ має свою унікальну адресу.
Адреса клітинки складається із заголовка стовпця (A, B, C…) і номера рядка (1, 2, 3…), на перетині яких вона розташована. Адреса активної клітинки відображається в полі Ім’я рядка формул.
Адреса прямокутного діапазону складається з адрес двох клітинок: верхньої зліва та нижньої справа, розділених двокрапкою.
Приклад 1
Розгляньмо рисунок. Адреса клітинки на перетині стовпця А і рядка 2 — А2 (випадок а); адреса прямокутного діапазону з чотирьох клітинок — В2:С3 (випадок б); адреса діапазону, який об’єднує клітинки з двох перших прикладів, — А2;В2:С3 (випадок в); адреса третього стовпця — С (замість діапазону С1:С1048576).
У таблиці можна використовувати формули та функції.
Завдання 1
20.2. Використання формул
Формула в електронній таблиці — це послідовність символів, що починається зі знака рівності.
Ознайомімося з правилами записування формул. Запис формули починається знаком «=», далі йде сама формула. Для внесення у формулу адреси певної клітинки достатньо клацнути відповідну клітинку.
Приклад 2
Для розрахунку підсумкової оцінки (середнього бала) учениці, наприклад Сумлінної Ліни, знайдімо суму оцінок і поділімо на їх кількість.
Після введення формули та натискання клавіші Enter у клітинці з формулою отримаймо результат обчислень. Щоб приховати дробову частину числа (округлення), можна зменшити ширину стовпця.
Завдання 2
20.3. Використання функцій
Використана в прикладі 2 формула має суттєвий недолік: якщо учениця отримає нову оцінку, доведеться переробляти формулу. Зробити формулу однаковою для будь-якої кількості оцінок можна завдяки застосуванню функцій.
Функція в електронній таблиці — це іменоване позначення певних дій над даними таблиці.
Дані, які опрацьовує функція, указують у дужках після назви функції і називають аргументами.
У формулах для опрацювання даних є багато вбудованих функцій. Ознайомімося із застосуванням функції AVERAGE, яка повертає середнє арифметичне числових даних.
Приклад 3
Для розрахунку підсумкової оцінки (середнього бала) учениці, наприклад Сумлінної Ліни, застосуймо функцію AVERAGE. Як аргумент задаймо діапазон B2:F2. Порожні клітинки й клітинки з даними нечислового типу фунцією нехтуються.
Завдання 3
20.4. Копіювання формул
Копіювання формул здійснюється за допомогою команд контекстного меню клітинки або сполученнями клавіш Ctrl + С, Ctrl + V, або перетягуванням маркера автозаповнення.
Посилання — це імена та адреси клітинок і діапазонів клітинок, використані у формулах.
Розрізняють відносні, абсолютні та мішані посилання.
20.4.1. Відносні посилання
Посилання, які змінюються під час переміщення та/або копіювання формул, називають відносними.
Під час копіювання чи переміщення формул відбувається модифікація відносних посилань.
Для копіювання формули до суміжних клітинок рядка (стовпця) можна виділити клітинку з формулою й перетягнути маркер автозаповнення. У формулах відносні посилання будуть модифіковані.
Редагування формул відбувається аналогічно редагуванню даних іншого типу: у рядку формул або клітинці з формулою після її подвійного клацання.
Приклад 4
Щоб розрахувати підсумкову оцінку кільком особам, створену формулу можна скопіювати маркером автозаповнення.
20.4.2. Абсолютні та мішані посилання
Формули можуть містити посилання на значення, які після переміщення чи копіювання формул не повинні змінюватися.
Посилання, які не змінюються під час копіювання формул, називають абсолютними.
Посилання, у яких під час копіювання формул не змінюється одна з величин: номер рядка або заголовок стовпця, називають мішаними.
Зазначені посилання зліва від заголовка стовпця (рядка) містять символ $: абсолютне $A$1; мішане $A1, A$1.
Щоб тип посилань змінювався автоматично, необхідно встановити курсор на посилання у формулі та натиснути клавішу F4. Під час копіювання формул із мішаними посиланнями модифікуються лише відносні номери рядків та/або заголовки стовпців.
Приклад 5
Переведімо підсумкові оцінки із 12-бальної системи в N-бальну (N = 100) на основі прямої пропорційності, тобто 12 — це N балів, 6 — N/2 балів і т. д.
Завдання 4
Питання для самоперевірки
1. Запустіть Excel. Створіть Нову книгу. Заповніть таблицю за зразком.
2. У діапазоні Е2:F7 установіть формат Грошовий (у гривні) з трьома знаками після коми.
3. У клітинці D2 створіть формулу розрахунку кількості спожитої електроенергії за показами лічильників. Скопіюйте формулу для комунальних послуг (окрім водовідведення).
У клітинці D7 створіть формулу розрахунку кількості відведеної води, що дорівнює обсягу спожитої холодної і гарячої води.
4. У клітинці F2 створіть формулу підрахунку вартості спожитої електроенергії за тарифом (Е2) і обсягом спожитої енергії (D2).
Скопіюйте формулу для решти комунальних послуг.
5. У клітинці F8 створіть формулу підрахунку загальної вартості всіх комунальних послуг.
При цьому замість знака додавання «+» використайте відповідну функцію знаходження суми. Порівняйте результати обчислень зі зразком.
6. Знайдіть в інтернеті тарифи за відповідні комунальні послуги у вашому регіоні на поточний місяць.
Змініть табличні дані в діапазоні Е2:Е7 згідно з тарифами з точністю до тисячних.
Збережіть документ із назвою Вправа 20. Завершіть роботу.