Формули і функції в Excel

 

1.5 Повідомлення про помилки

Якщо формула у вічку не може бути правильно обчислена, Microsoft Excel виводить у вічко повідомлення про помилку. Якщо формула містить посилання на вічко, що містить значення помилки, то замість цієї формули також буде виводитися повідомлення про помилку.

Значення помилок:

#### – ширина вічка не дозволяє відобразити число в заданому форматі;

#ИМЯ? – Microsoft Excel не зміг розпізнати ім'я, використане в формулі;

#ДЕЛ/0! – у формулі робиться спроба поділу на нуль;

#ЧИСЛО! – порушені правила задавання операторів, прийняті в математиці;

#Н/Д – таке повідомлення може з'явитися, якщо в якості аргументу задане посилання на порожнє вічко;

#ПУСТО! – невірно зазначене перетинання двох областей, що не мають загальних комірок;

#ССЫЛКА! – у формулі задане посилання на неіснуюче вічко;

#ЗНАЧ! – використаний неприпустимий тип аргументу

1.6 Створення формул

Створити просту формулу  можна за допомогою констант і операторів обчислення. Наприклад, формула =5+2*3 помножує два числа, а потім додає до результату ще одне число. У Microsoft Office Excel використовується стандартний порядок математичних операцій. У попередньому прикладі спочатку виконується множення (2*3), до результату якого додається 5.

Також для створення формули  можна використати функцію. Наприклад, у формулах =SUM(A1:A2) і SUM(A1;A2) функція SUM служить для додавання значень клітинок A1 і A2.

Залежно від типу створюваної формули  вона може містити деякі або всі  нижченаведені елементи. (Малюнок 1.1)

Малюнок 1.1

1. Функція, така як PI(), починається зі знака рівності (=) і може містити аргументи, забрані в дужки. Кожна функція має свій синтаксис аргументів.

2. Посилання на клітинки. Можна звертатися до даних у клітинках на аркуші, долучаючи до формули посилання на клітинки. Зокрема, посилання A2 повертає значення цієї клітинки або використовує це значення в обчисленні.

3. Константи. Числові (наприклад, 2) або текстові значення, які вводяться безпосередньо у формулу.

4. Оператори. Оператори — це символи, які визначають тип обчислення, яке має виконувати формула. Наприклад, оператор ^ (дашок) підносить число до степеня, а оператор * (зірочка) перемножує числа.

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

1.6.1 Створення простої формули з використанням констант і операторів обчислення

1. Клацніть клітинку, в яку потрібно ввести формулу.
2. Введіть = (знак рівності).
3. Щоб ввести формулу, виконайте одну з таких дій.
• Введіть константи й оператори, які має бути використано в обчисленні.

Приклад формули	Дія
=5+2	Додає 5 і 2
=5-2	Віднімає 5 від 2
=5/2	Ділить 5 на 2
=5*2	Помножує 5 на 2
=5^2	Підносить 5 до степеня 2

Таблиця 1.2 Приклад створення простої формули з

 використанням констант

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

 

Приклад формули	Дія
=A1+A2	Додає значення клітинок A1 і A2
=A1-A2	Віднімає значення клітинки A2 від значення A1
=A1/A2	Ділить значення клітинки A1 на значення клітинки A2
=A1*A2	Помножує значення клітинки A1 на значення клітинки A2
=A1^A2	Підносить значення клітинки A1 до степеня, записаного у клітинці A2

Таблиця 1.3 Приклад створення простої формули з використанням

операторів обчислення 

4. Порада. Кількість констант і операторів у формулі не обмежується.
5. Натисніть клавішу ENTER.

1.6.2 Створення формули з використанням посилань на

клітинки та імен

Формули у прикладах  у кінці цього розділу містять відносні посилання на інші клітинки та їх імена. Якщо значення клітинки, яка містить формулу, залежить від значень інших клітинок, така клітинка називається залежною. Наприклад, клітинка B2 є залежною, якщо вона містить формулу =C2.

1. Клацніть клітинку, в яку потрібно ввести формулу.
2. У рядку формул введіть = (знак рівності).
3. Виконайте одну з таких дій:
• Щоб створити посилання, виберіть клітинку, діапазон клітинок, розташування на іншому аркуші або в іншій книзі. Цей спосіб має назву «частковий вибір». Можна перетягнути межу виділення клітинок, щоб перемістити його, або потягнути кут виділення, щоб його збільшити.

1. Перше посилання — на клітинку B3 синього кольору, діапазон клітинок має синю межу з прямими кутами.

2.  Друге посилання — на клітинку C3 зеленого кольору, діапазон клітинок має зелену межу з прямими кутами.

Примітка. Якщо в жодному кутку кольорової межі немає прямокутної позначки, це посилання на іменований діапазон.

• Щоб ввести посилання на іменований діапазон, натисніть клавішу F3, виберіть ім’я в полі Ім’я та натисніть кнопку ОК.

Приклад формули	Дія
=C2	Використовує значення з клітинки C2
=Аркуш2!B2	Використовує значення з клітинки B2 на аркуші Аркуш2
=Актив-Пасив	Віднімає значення клітинки «Пасив» від значення клітини  «Актив»

 

Таблиця 1.4 Приклад створення формули з використанням

посилань на клітинки та імен

4. Натисніть клавішу ENTER.

1.6.3 Створення формули з використанням функції

1. Клацніть клітинку, в яку потрібно ввести формулу.
2. Щоб почати формулу з функції, натисніть кнопку Вставлення функції в рядку формул .
3. Виберіть потрібну функцію.

Можна ввести запит з  описом операції, яку потрібно виконати, у полі Пошук функції (наприклад, за словами «додавання чисел» буде знайдено функцію SUM), або вибрати категорію в полі Категорія.

4. Введіть аргументи.

Порада. Щоб ввести посилання на клітинки як аргумент, натисніть кнопку згортання вікна (діалогове вікно буде тимчасово приховано), виберіть клітинки на аркуші й натисніть кнопку розгортання вікна .

Приклад формули	Дія
=SUM(A:A)	Додає всі числа у  стовпці A
=AVERAGE(A1:B4)	Знаходить середнє для  всіх чисел у визначеному  діапазоні

Таблиця 1.5 Приклад створення формули з використанням функції

5. Завершивши введення формули, натисніть клавішу ENTER.

Порада. Щоб швидко підсумувати значення, також можна скористатися засобом Автосума. На вкладці Основне у групі Редагування натисніть кнопку Автосума та виберіть потрібну функцію.

1.6.4 Створення формули з використанням вкладених функцій

У вкладених функціях одні функції використовуються як аргументи  інших. Дозволяється глибина вкладення до 64 рівнів. Нижченаведена формула підсумовує набір чисел (G2:G5) лише в тому разі, якщо середнє значення іншого набору чисел (F2:F5) більше за 50. В іншому разі вона повертає значення 0.

Функції AVERAGE і SUM вкладено у функцію IF.

1. Клацніть клітинку, в якій потрібно ввести формулу.
2. Щоб почати формулу з функції, натисніть кнопку Вставлення функції в рядку формул .
3. Виберіть потрібну функцію.

Можна ввести запит з  описом операції, яку потрібно виконати, у полі Пошук функції (наприклад, за словами «додавання чисел» буде знайдено функцію SUM), або вибрати категорію в полі Категорія.

4. Щоб ввести аргументи функції, виконайте такі дії.
• Щоб ввести посилання на клітинку як аргумент, натисніть кнопку згортання вікна поруч із потрібним аргументом (діалогове вікно буде тимчасово приховано), виберіть клітинки на аркуші, відтак натисніть кнопку розгортання вікна .
• Щоб додати іншу функцію як аргумент, введіть її в потрібному полі аргументу. Наприклад, можна додати SUM(G2:G5) до поля вводу значення_якщо_істина функції IF.
• Елементи формули, які відображаються в діалоговому вікні Аргументи функції, відповідають функції, вибраній на попередньому кроці. Наприклад, якщо було вибрано функцію IF, у вікні Аргументи функції відображаються аргументи функції IF.

1.6.5 Створення формули масиву для обчислення єдиного результату

Можна скористатися формулою масиву, щоб виконати кілька обчислень для отримання єдиного результату. За допомогою цього типу формули масиву можна спростити модель аркуша, замінивши кілька різних формул на одну формулу масиву.

1. Клацніть клітинку, в яку потрібно ввести формулу масиву.
2. Введіть потрібну формулу.

Порада. У формулах масиву використовується стандартний синтаксис формули. Усі вони починаються зі знака рівності та підтримують використання будь-яких вбудованих функцій Excel.

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

Якщо ввести формулу {=SUM(B2:C2*B3:C3)} як формулу масиву, вона перемножує кількість і ціну для кожної акції (500*10 і 300*15), відтак підсумовує отримані результати, отримуючи загальний підсумок 9500.

3. Натисніть сполучення клавіш CTRL+SHIFT+ENTER.

Формулу буде автоматично  охоплено фігурними дужками { }. 

Примітка. Якщо ввести дужки навколо формули вручну, вона не стане формулою масиву — для створення формули масиву обов'язково потрібно натиснути клавіші CTRL+SHIFT+ENTER.

Увага! Після редагування формули масиву фігурні дужки { } зникають, і необхідно натиснути клавіші CTRL+SHIFT+ENTER знову, щоб застосувати зміни та додати дужки.

1.6.6 Створення формули масиву для обчислення кількох результатів

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

1. Виділіть діапазон клітинок, в який потрібно ввести формулу масиву.
2. Введіть потрібну формулу.

Порада. У формулах масиву використовується стандартний синтаксис формули. Усі вони починаються зі знака рівності та підтримують використання будь-яких вбудованих функцій Excel.

Наприклад, за вказаним рядом  із трьох значень продажу (стовпець В) і рядом із трьох місяців (стовпець А) функція TREND визначає продовження лінійного ряду обсягу продажу. Для відображення всіх обчислюваних значень формулу введено у три клітинки стовпця C (C1:C3).