Формули та функції

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

Огляд формул у програмі Excel

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

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

Створення завдання

Увага!: Результати обчислення формул і деякі функції на аркушах Excel можуть дещо відрізнятися на ПК під керуванням ОС Windows з архітектурою x86 або x86-64 та ПК під керуванням ОС Windows RT з архітектурою ARM. Дізнайтеся більше про такі відмінності.

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

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

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

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

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

    виберіть клітинку

  4. Введіть потрібний оператор, наприклад віднімання (–).

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

    наступна клітинка

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

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

  1. Введена в клітинку формула також відображається в рядку формул.

    Рядок формул
  2. Щоб переглянути формулу, виберіть потрібну клітинку. Формула з’явиться в рядку формул.

    Погляньте на рядок формул

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

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

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

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

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

    діапазон

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

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

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

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

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

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

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

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

1. Функції. Функція PI() повертає значення числа пі: 3,142…

2. Посилання. A2 повертає значення клітинки A2.

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

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

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

Посилання визначає клітинку або діапазон клітинок на аркуші та вказує програмі 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. Знак оклику (!) відокремлює посилання на аркуш від посилання на діапазон клітинок.

    Примітка.: Якщо ім’я аркуша містить пробіли або цифри, потрібно взяти його в одинарні лапки ('), наприклад ='123'!A1 або ='Січневий прибуток'!A1.

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

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

      Скопійована формула з відносним посиланням   

      Скопійована формула з відносним посиланням

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

      Скопійована формула з абсолютним посиланням   

      Скопійована формула з абсолютним посиланням
    3. Мішані посилання   . Мішане посилання має або абсолютний стовпець і відносний рядок, або абсолютний рядок і відносний стовпець. Абсолютне посилання на стовпець має вигляд $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 із доданих аркушів.

    • Видалення   . У разі видалення аркушів діапазону між Аркушем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 User Voice.

Див. також

Перехід між відносними, абсолютними та мішаними посиланнями для функцій

Використання операторів обчислення у формулах Excel

Порядок здійснення операцій у формулах Excel

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

Визначення й використання імен у формулах

Приклади формул масивів і рекомендації

Видалення або вилучення формули

Способи уникнення недійсних формул

Пошук і виправлення помилок у формулах

Сполучення клавіш у програмі Excel а також функціональні клавіші

Функції Excel (за категоріями)

Удосконалення навичок роботи з Office
Ознайомтеся з навчальними матеріалами
Отримуйте нові функції раніше за інших
Приєднайтеся до оцінювачів Office

Ця інформація корисна?

Дякуємо за ваш відгук!

Дякуємо за відгук! Схоже, вам може стати в нагоді допомога одного з наших спеціалістів служби підтримки Office, з яким ми вас можемо з’єднати.

×