§ 5. Використання функцій

В цьому параграфі ви вивчите:

• правила запису функцій у електронних таблицях;

• різні способи додавання комірок;

• використання палітри функцій;

• повідомлення про помилки у формулах.

Синтаксис функцій

Формули, що вписуються до комірок електронних таблиць, можуть містити в собі функції. Програма Excel пропонує широкий набір вбудованих функцій, що істотно полегшують процес обробки даних, звільняють від довгих записів формул і зменшують імовірність помилок.

Розглянемо звернення до функцій в Excel. Запис функції починається із вказання імені функції, за яким в дужках слідує список аргументів. Наприклад, функція знаходження максимального значення серед аргументів число1, число2, … має вигляд:

MAX (число1, число2,…)

Аргументи – це величини, що використовуються для обчислення значення функцій. Результат обчислення функції називається поверненим значенням.

Дужки, в які взято список аргументів, завжди мають бути парними, тобто кількість відкритих дужок повинна дорівнювати закритим. Пробіли перед дужками або після них не допускаються. Список аргументів може складатися з чисел, посилань, тексту, масивів, логічних величин (TRUE або FALSE), значень помилок (наприклад, #DIV/0). Окремі аргументи в списку слід розділяти комами. Деякі функції можуть мати до 30 аргументів.

Аргументи можуть належати до різних типів. Список аргументів, що складається з чисел і посилань, може бути таким:

SUM (1; 10; В2:В8)

Ця формула повертає значення, що дорівнює сумі чисел 1, 10 і чисел у комірках від В2 до В8.

В електронних таблицях Excel допускається вкладення функцій одна в одну, тобто використання значення, що повертається однією функцією, як аргумент для іншої функції. Функція, що є аргументом іншої функції, називається вкладеною. Наприклад, у запису

SUM (А1:С5, MAX(D1:E3))

функція MAX, що повертає максимальне значення в діапазоні D1:E3, є вкладеною.

Введення функцій

Функції вставляються у формули, а останні починаються зі знака =. Якщо формулу почати з назви функції, то знак рівності буде підставлений автоматично. Функція включається до формули записом «вручну» або за допомогою майстра функцій, що спрощує введення функції.

Викликається майстер через меню Вставка→Функція. Діалог Майстер функцій (мал. 5.1) містить два списки: Категорія і Функція. У першому списку потрібно вибрати категорію, у якій є функція, що вас цікавить, а в другому – саму функцію. Усього в наборі Excel 10 категорій функцій: фінансові, дата и время, математичні тощо. Крім того, список категорій доповнений рядком 10 недавно використовуваних зі списком функцій, які найчастіше використовувалися останнім часом. Усього вбудованих функцій, що виконують різні типи обчислень, понад 200.

Мал.. 5.1. Діалог Мастер Функций

У діалозі Майстер функцій під списками розташовано рядок опису синтаксису і подано коментар про призначення виділеної функції. У рядку синтаксису аргументи, виділені напівжирним шрифтом, є обов’язковими. Якщо обов’язкові для функції аргументи не будуть зазначені, то її неможливо буде ввести до комірки.

Обравши потрібну функцію, натисніть кнопку Ok, після чого відкриється наступне діалогове вікно майстра функцій (мал. 5.2), назване палітрою формул. Аргументи можна вводити з клавіатури, але бажано при введені посилань використовувати мишу, що прискорить процес введення і позбавить помилок.

Для введення однакових або схожих функцій до різних комірок користуйтеся можливостями копіювання в Excel.

Мал. 5.2. Палітра формул для функції СУММ

Прості приклади використання функцій

Спектр можливостей Excel надзвичайно великий, але, мабуть, кожен користувач у своїй практиці не обходиться без операції додавання. Для цього Excel має цілу низку зручних інструментів.

Автододавання

Припустимо, вам потрібно скласти числа, записані до комірок стовпця А (мал. 5.3,а). Опишемо два найпростіші способи додавання.

• Додавання виділених комірок. Виділіть комірки, зміст яких потрібно додати (мал. 5.3,б), а потім натисніть кнопку (Автосума), розташованій на панелі інструментів. Значення суми буде вміщено до комірки під виділеним стовпцем. Аналогічно можна додати виділені комірки у рядку – результат буде записаний до найближчої комірки праворуч від виділеного фрагмента рядка.

• Додавання з автоматичним виділенням доданків. Щоб додати комірки, вам не обов’язково їх виділяти – програма може це зробити за вас. Вам досить лише активізувати комірку, до якої ви хочете помістити суму і, натиснути кнопку Автосума. Програма занесе до поточної комірки формули (у даному випадку SUM(А1:А5) і виділить рухомим пунктиром ті комірки, що будуть підсумовані (мал. 5.3,в). Натисніть Enter, і ви отримаєте результат у поточній комірці.

а	Б	в
Мал. 5.3. Прийоми знаходження суми за допомогою кнопки Автосума: а – первинні комірки-доданки; б – додавання виділених комірок; в – додавання без виділення

Додавання за допомогою функції SUM

Виділіть комірку, до якої буде занесено суму комірок, розташованих вище або лівіше. Потім натисніть кнопку Вставка функції у рядку формул і в категорії Математичні оберіть функцію SUM. У діалозі Палітра формул (мал. 5.2.) буде зазначено діапазон комірок, що додаються, і виведена сума. Щоб помістити суму до цільової комірки натисніть Ok.

Ви можете ввести функцію з клавіатури. Але використання палітри формул прискорює саму операцію введення і зменшує ймовірність помилки.

Виведення результату обчислень у рядку стану

Опишемо найпростіший і найшвидший спосіб обчислень, при якому не потрібно вводити формули до комірок. Відповідно до цього способу результат обчислень виводиться у рядок стану без занесення його до будь-якої комірки (якщо рядок стану у вашому вікні електронних таблиць відсутній, виконайте команду меню Вигляд→рядок стану).

Мал. 5.4. Рядок стану засобу Автообчислення і контекстного меню

У рядку стану може відображатися сума виділених комірок, середнє арифметичне, максимальне або мінімальне з виділених значень тощо. Для виведення результату в рядку стану виділіть комірки, які потрібно обробити (протягнувши покажчик миші через ці комірки). Прочитайте результат додавання комірок у рядку стану, який починається як Сума=…(мал.5.4).

Якщо після виділення комірок натиснути праву кнопку миші на рядку стану, то з’явиться контекстне меню (мал. 5.4), з якого ви зможете вибрати будь-яку з доступних операцій (знаходження середнього, мінімального, макси-мального значень тощо). У рядку стану після цього з’явиться запис Мінімум=… або Максимум=… тощо.

Діагностування помилок у формулах

Реакцією на неправильне введення формул є повідомлення Excel про помилки, тобто значення в комірках, що починаються зі знака #. Наведемо список можливих значень помилок.

• ##### - найчастіше з’являється у користувачів-початківців і означає, що ширина комірки недостатня для розміщення у ній числа, дати або часу. Змініть ширину комірки або змініть формат числа.

• #NAME? – неможливість розпізнати ім’я, що використовується. Ця помилка виникає, коли неправильно зазначене ім’я об’єкта або є посилання на видалене ім’я, коли неправильно записана функція, коли при запису адрес замість латини використана кирилиця тощо.

• #VALUE! – спроба некоректного використання функції. Поширеною помилкою є невідповідність даних встановленому формату, наприклад, замість числа або дати в аргументі стоїть текст.

• #NUM! – з’являється при неправильному запису або використанні чисел. Можливо, у функції з числовим аргументом використовується аргумент нечислового формату або до комірки введена формула, що повертає занадто велике значення по модулю (понад 10³⁷).

• #REF! – означає неправильне вживання посилань, які є у формулі. Можливо, формула містить посилання на комірку, що уже видалена або посилання на комірку, до якої скопійовано вміст інших комірок.

• #DIV/0! – спроба ділення на нуль. Така ситуація найчастіше виникає при використанні як дільника посилання на порожню комірку або комірку, яка містить нульове значення.

• #NULL! – значення помилки, що з’являється при заданні в посиланні порожньої множини комірок. Можливо, що ви помилилися у визначенні перетину двох діапазонів. Наприклад, поставили у вираз типу (А3:А6 С4:F7) замість коми пробіл.

• #N/A – скорочення від терміна «невизначені дані». Це значення помилки звичайно спеціально вводять до комірок, щоб запобігти обчисленням у них (наприклад, при відсутності даних). Коли необхідні дані з’являються, їх просто вводять у формулу.

Контрольні запитання

1. Які правила запису функції в електронних таблицях Excel?

2. Що таке вкладена функція?

3. Як ввести функцію до формули за допомогою майстра функцій?

4. Чи можна записати функцію вручну?

5. Назвіть найпростіші способи обчислення суми комірок.

6. Значення яких функцій можна вивести в рядок стану?

7. На які категорії поділяються вбудовані функції Excel?

8. Що таке значення помилки? Які бувають значення помилок?

Практикум. Обчислення дати

Розглянемо приклад обчислення змісту комірки за допомогою функції, якою буде вбудована функція DATE (рік, місяць, день). Ця функція повертає дату в заданому форматі за значенням року, місяця і дня. Припустимо, що в таблиці, яку ви склали, дата обчислюється, виходячи із значень, записаних до певних комірок. Нехай комірки А1, В1, С1 мають числові значення 2003, 10, 30 відповідно, а дату потрібно внести до комірки А2 (мал. 5.5). Послідовність ваших дій може бути такою:

1. Запишіть до комірок А1, В1, С1 числа, що відповідають року, числу і місяцю.

2. Активізуйте комірку А2 і виконайте команду Вставка→Функція.

3. Мал. 5.5. Приклад обчислення дати за допомогою вбудованої функції ДАТА

   Оберіть у вікні Майстер функцій категорію Дата і час, функцію DATE і натисніть кнопку Ok.

4. У палітрі формул установіть курсор у поле Рік і клацніть мишею по кнопці А1.

5. Переведіть курсор у поле Місяць і клацніть по комірці В1. Аналогічно введіть День з комірки С1. На закінчення натисніть кнопку Ok, після чого в цільовій комірці з’явиться результат 30.10.04.

Формат дати можна змінити. Для цього клацніть по кнопці А2, оберіть команду Формат→Клітинки і перейдіть на вкладку Число. У полі Тип вам слід обрати певний формат дати (30 окт 04, Октябрь 2004 тощо).