Як працюють формули Excel

Excel робить багато чудових речей. Щоб побачити їх у всій красі, потрібно розуміти, як працювати з формулами Excel. Формула, ви можете згадати з сонних годин ви провели в математичному класі, це спосіб чисел CALCulate. Наприклад, 2+3=5 є формулою. Коли ви вводите формулу в клітинку, Excel обчислює формулу та відображає її результати в клітинці. Клацніть у клітинці A3 та введіть =2+3 , наприклад, і Excel відобразить число 5 у клітинці A3.

Посилання на клітинки у формулах

Крім чисел, формули Excel можуть посилатися на вміст різних клітинок. Коли формула посилається на клітинку, число в комірці використовується для обчислення формули. На зображенні нижче, наприклад, клітинка A1 містить число 2; клітинка А2 містить число 3; а клітинка A3 містить формулу =A1+A2. Як показано в клітинці A3, результатом формули є 5. Якщо змінити число в клітинці A1 з 2 на 3, результат формули в клітинці A3 (=A1+A2) стане 6, а не 5. Коли формула посилається на клітинку, число в комірці змінюється, змінюється і результат формули.

Проста формула Excel.

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

• У колонці C, Фактичний дохід, перераховані доходи з різних джерел.
• Стовпець D, Прогнозований дохід, показує, яким буде прибуток з цих джерел, на думку членів PTA.
• Стовпець E, Перевищений/Недостатній бюджет, показує, як фактичний дохід порівнюється з прогнозованим доходом з різних джерел.

Використання формул Excel на аркуші.

Оскільки цифри в стовпці Фактичний дохід (стовпець C) оновлюються, цифри в стовпці «Бюджет «Перевищений/недостатній» (стовпець E) та рядку Загальний дохід (рядок 8) змінюються миттєво. Ці цифри змінюються миттєво, оскільки формули посилаються на числа в клітинках, а не на незмінні числа (відомі як константи ).

На зображенні нижче показано формули, які використовуються для обчислення даних на аркуші вище. У стовпці E формули віднімають числа в стовпці D від чисел у стовпці C, щоб показати, де PTA було перевищено або занижено в бюджеті для різних джерел доходу. У рядку 8 ви можете побачити, як функція SUM використовується для підсумовування клітинок у рядках з 3 по 7

Формули, які використовуються для створення чисел на аркуші Excel вище.

Excel надзвичайно добре оновлює посилання на клітинки у формулах під час переміщення клітинок. Щоб побачити, наскільки добре працює Excel, подумайте, що відбувається з адресами комірок у формулах, коли ви видаляєте рядок на аркуші. Якщо формула посилається на клітинку C1, але ви видаляєте рядок B, рядок C стає рядком B, а значення в клітинці C1 змінює адреси з C1 на B1. Ви могли б подумати, що посилання у формулах на клітинку C1 будуть застарілими, але ви помиляєтеся. Excel автоматично коригує всі формули, які посилаються на клітинку C1. Тепер ці формули посилаються на клітинку B1.

Якщо вам цікаво, ви можете відобразити формули в клітинках аркуша замість результатів формул, натиснувши Ctrl+' (апостроф) або натиснувши кнопку Показати формули на вкладці Формули. (Можливо, вам доведеться спочатку натиснути кнопку Аудит формул, залежно від розміру вашого екрана.) Натисніть кнопку Показати формули вдруге, щоб знову побачити результати формули.

Посилання на результати формул у формулах Excel 2019

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

• У стовпці «Загальні підсумки» (стовпець E) показано загальну кількість очок, яку набрав кожен гравець у трьох іграх.
• У стовпці «Середнє значення» (стовпець F), використовуючи результати формули в стовпці «Загальні підсумки», визначають, скільки в середньому набрав кожен гравець. У стовпці «Середнє» це відбувається шляхом ділення результатів у стовпці E на 3, кількість зіграних ігор.

Використання результатів формули Excel як інших формул.

У цьому випадку Excel використовує результати формул загальних розрахунків у стовпці E для обчислення середніх балів за гру в стовпці F.

Оператори в формулах Excel 2019

Додавання, віднімання та ділення — не єдині оператори, які можна використовувати у формулах. Таблиця нижче пояснює арифметичні оператори, які ви можете використовувати, і клавішу, яку ви натискаєте для введення кожного оператора. У таблиці оператори перераховані в порядку пріоритету.

Арифметичні оператори для використання у формулах

Іншим способом обчислення формули є використання функції. Функція — це вбудована формула, яка постачається з Excel. SUM, наприклад, додає числа в клітинках. AVG знаходить середнє для різних чисел.

Порядок пріоритету для формул Excel

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

=2+3*4

Чи отримує ця формула 14 (2+[3*4]) чи 20 ([2+3]*4)? Відповідь 14, оскільки Excel 2019 виконує множення перед додаванням у формулах. Іншими словами, множення має перевагу над додаванням.

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

Відсоток (%)

Підведення в ступінь (^)

Множення (*) і ділення (/); спочатку обчислюються крайні ліві операції

Додавання (+) і віднімання (-); спочатку обчислюються крайні ліві операції

Конкатенація (&)

Порівняння (<,> <=,>,>= і <>)

Щоб обійти проблему порядку пріоритету, вкладіть частини формул у дужки. Операції в дужках обчислюються перед усіма іншими частинами формули. Наприклад, формула =2+3*4 дорівнює 20, якщо вона записана таким чином: =(2+3)*4.