Огляд формул в Excel

Створення формули, яка посилається на значення в інших клітинках

1. Виберіть клітинку.

2. Введіть знак рівності (=).

   Примітка.

   Формули в Excel завжди починаються зі знака рівності.

3. Виберіть клітинку або введіть її адресу у вибрану клітинку.

   

4. Введіть потрібний оператор, Наприклад, використовується - для віднімання.

5. Виберіть наступну клітинку або введіть її адресу у вибрану клітинку.

   

6. Натисніть клавішу Enter. Результат обчислення з’явиться в клітинці з формулою.

Перегляд формули

Якщо ввести формулу в клітинку, вона також з'явиться в рядку формул.

• Щоб переглянути формулу в рядку формул, виділіть клітинку.

  

Введення формули з вбудованою функцією

1. Виберіть пусту клітинку.

2. Введіть знак = рівності, а потім – функцію. Наприклад, введіть =SUM , щоб отримати загальний обсяг продажів.

3. Введіть відкривну дужку (.

4. Виділіть діапазон клітинок, а потім введіть праву дужку ).

   

5. Натисніть клавішу Enter, щоб отримати результат.

Завантаження посібника з формул

Завантажте книгу "Початок роботи з формулами". Ця демонстрація найпоширеніших формул Excel допоможе як новачкам, так і користувачам, які вже мають деякий досвід роботи в цій програмі. Завдяки реалістичним прикладам і корисним візуалізаціям ви навчитеся професійно підсумовувати, підраховувати, обчислювати середні значення та використовувати функцію VLOOKUP.

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

Докладні відомості про конкретні елементи формул див. в окремих розділах наведеного нижче списку.

Елементи формули Excel

Формула також може містити всі або деякі з таких елементів: функції, посилання, оператори та константи.

1. Функції: Функція PI() повертає значення числа пі: 3,142...
2. Посилання: A2 повертає значення клітинки A2.
3. Константи – числа або текстові значення, введені безпосередньо у формулу, наприклад 2.
4. Оператори: оператор ^ (символ кришки) підносить число до степеня, а оператор * (зірочка) перемножує числа.

Використання констант у формулах Excel

Константа – це значення, яке не обчислюється та завжди залишається однаковим. Наприклад, дата 09.10.2008, число 210 або текст "Квартальний прибуток" – це константи. Вираз або значення, отримані в результаті обчислення, не є константою. Якщо у формулі замість посилань на клітинки використовуються константи (наприклад, =30+70+110), то результат зміниться, лише якщо змінити саму формулу. Зазвичай краще зберігати константи в окремих клітинках, де їх можна легко змінити за потреби, а потім посилатися на ці клітинки у формулах.

Використання посилань у формулах Excel

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

Стиль посилань A1

За замовчуванням у програмі Excel використовується стиль посилань A1, який позначає стовпці буквами (від A до XFD, загалом 16 384 стовпці), а рядки – числами (від 1 до 1 048 576). Ці букви й числа називаються заголовками рядків і стовпців. Щоб створити посилання на клітинку, введіть букву стовпця, а після неї – номер рядка. Наприклад, "B2" посилається на клітинку на перетині стовпця B та рядка 2.

Об’єкт посилання	Логічне значення
Клітинка у стовпці A й рядку 10	A10
Діапазон клітинок у стовпці A й рядках від 10 до 20	A10:A20
Діапазон клітинок у рядку 15 і стовпцях від B до E	B15:E15
Усі клітинки в рядку 5	5:5
Усі клітинки в рядках від 5 до 10	5:10
Усі клітинки у стовпці H	H:H
Усі клітинки у стовпцях від H до J	H:J
Діапазон клітинок у стовпцях від A до E й рядках від 10 до 20	A10:E20

Створення посилання на клітинку чи діапазон клітинок з іншого аркуша тієї самої книги

У наведеному нижче прикладі функція AVERAGE обчислює середнє значення в діапазоні B1:B10 на аркуші "Маркетинг" у тій самій книзі.

1. Посилається на аркуш ''Маркетинг''.
2. Посилається на діапазон клітинок від B1 до B10.
3. Знак оклику (!) відокремлює посилання на аркуш від посилання на діапазон клітинок

Різниця між абсолютними, відносними та мішаними посиланнями

Відносні посилання.

Відносні посилання на клітинки у формулі, наприклад A1, базуються на відносній позиції клітинки, що містить формулу, і клітинки, на яку вказує посилання. Якщо змінюється положення клітинки, що містить формулу, змінюється й посилання. Якщо скопіювати або заповнити формулу в рядку або у стовпці, то посилання зміниться автоматично. За промовчанням нові формули використовують відносні посилання. Наприклад, якщо заповнити або скопіювати відносне посилання із клітинки B2 до клітинки B3, воно автоматично зміниться з =A1 на =A2.

Абсолютні посилання.

Абсолютне посилання на клітинку у формулі, наприклад $A$1, завжди посилається на клітинку в певному розташуванні. Якщо змінюється положення клітинки, яка містить формулу, абсолютне посилання залишається без змін. Якщо скопіювати або заповнити формулу в рядку або стовпці, то абсолютне посилання не змінюється. За промовчанням нові формули використовують відносні посилання, тому може бути потрібно змінити їх на абсолютні. Наприклад, якщо заповнити або скопіювати абсолютне посилання із клітинки B2 до клітинки B3, воно буде однаковим в обох клітинках: =$A$1.

Мішані посилання.

Мішане посилання має або абсолютний стовпець і відносний рядок, або абсолютний рядок і відносний стовпець. Абсолютне посилання на стовпець має вигляд $A1, $B1 тощо. Абсолютне посилання на рядок має вигляд A$1, B$1 тощо. Якщо змінюється положення клітинки, яка містить формулу, то відносне посилання змінюється, а абсолютне – ні. Якщо скопіювати або заповнити формулу в рядку або стовпці, то відносне посилання автоматично змінюється, а абсолютне посилання не змінюється. Наприклад, якщо заповнити або скопіювати мішане посилання із клітинки A2 до B3, воно змінюється з =A$1 на =B$1.

Стиль тривимірних посилань

Зручне посилання на кілька аркушів.

Якщо потрібно проаналізувати дані в тій самій клітинці або тому самому діапазоні клітинок у книзі, використовуйте тривимірне посилання. Тривимірне посилання містить посилання на клітинку або діапазон, перед яким указано діапазон імен аркушів. Програма Excel використовує всі збережені аркуші між першим і останнім іменем у посиланні. Наприклад, =SUM(Аркуш2:Аркуш13!B5) додає всі значення, які містяться у клітинці B5 на всіх аркушах між аркушами 2 та 13 включно.

• Тривимірні посилання можна використовувати для посилання на клітинки на інших аркушах, визначення імен і створення формул за допомогою таких функцій: SUM, AVERAGE, AVERAGEA, COUNT, COUNTA, MAX, MAXA, MIN, MINA, PRODUCT, STDEV.P, STDEV.S, STDEVA, STDEVPA, VAR.P, VAR.S, VARA і VARPA.
• У формулах масиву не можна використовувати тривимірні посилання.
• Тривимірні посилання не можна використовувати з оператором перетину (одним пробілом) або у формулах, які використовують неявний перетин.

Що відбувається під час переміщення, копіювання, вставлення або видалення аркушів.

Наведені нижче приклади пояснюють, що відбувається під час переміщення, копіювання, вставлення або видалення аркушів, які входять до тривимірного посилання. У прикладах використовується формула =SUM(Аркуш2:Аркуш6!A2:A5) для додавання клітинок від A2 до A5 на аркушах із 2 по 6.

• Вставлення або копіювання. У разі вставлення або копіювання аркушів діапазону між Аркушем2 й Аркушем6 (у цьому прикладі це кінцеві точки), до обчислення долучаються всі значення у клітинках від A2 до A5 із доданих аркушів.
• Delete У разі видалення аркушів діапазону між Аркушем2 й Аркушем6 значення видалених аркушів буде видалено з обчислення.
• Переміщення. У разі переміщення аркушів із діапазону між Аркушем2 й Аркушем6 до розташування поза діапазоном аркушів, на який посилається формула, значення переміщених аркушів буде видалено з обчислення.
• Переміщення кінцевої точки. У разі переміщення Аркуша2 або Аркуша6 до іншого розташування в тій самій книзі програма Excel змінить обчислення відповідно до нового діапазону аркушів між ними.
• Видалення кінцевої точки. У разі видалення Аркуша2 або Аркуша6 програма Excel змінить обчислення відповідно до діапазону аркушів між ними.

Стиль посилань R1C1

Також можна використовувати стиль посилань, у якому на аркуші пронумеровано як рядки, так і стовпці. Посилання в стилі R1C1 корисні для обчислення позицій рядків і стовпців у макросах. Якщо використовується стиль R1C1, програма Excel позначає розташування клітинки буквою "R", після якої йде номер рядка, і буквою "C", після якої йде номер стовпця.

Посилання	Значення
R[-2]C	Відносне посилання на клітинку на два рядки вище в тому самому стовпці
R[2]C[2]	Відносне посилання на клітинку на два рядки нижче й два стовпці праворуч
R2C2	Абсолютне посилання на клітинку в другому рядку та в другому стовпці
R[-1]	Відносне посилання на весь рядок над активною клітинкою
R	Абсолютне посилання на поточний рядок

Під час записування макросу програма Excel записує деякі команди за допомогою стилю посилань R1C1. Наприклад, під час записування команди натискання кнопки "Автосума " для вставлення формули, що додає діапазон клітинок, програма Excel записує формулу за допомогою стилю посилань R1C1, а не стилю A1.
Увімкнути або вимкнути стиль посилань R1C1 можна за допомогою встановлення або зняття прапорця стилю посилань R1C1 у розділі Робота з формулами в категорії Формули діалогового вікна Параметри . Щоб відкрити це діалогове вікно, перейдіть на вкладку "Файл ".

Потрібна додаткова довідка?

Ви завжди можете поставити запитання експерту в спільноті Tech у Excel або отримати підтримку в спільнотах.

Додаткові відомості

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