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

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

Якщо ви лише почали працювати з Excel, то незабаром зрозумієте, що це не просто таблиця, у якій ви вводите числа в рядках або стовпцях. Звісно, в Excel ви можете додавати значення в стовпцях або рядках, але також можете розрахувати іпотечний платіж, розв’язати математичні або технічні задачі чи знайти найкращий сценарій на основі введених змінних даних.

Для цього програма Excel використовує формули в клітинках. Формула виконує обчислення або інші дії з даними на аркуші. Формула завжди починається зі знака рівності (=), після якого можуть іти числа, математичні оператори (наприклад, знак "+" чи "-" для додавання чи віднімання), а також вбудовані функції Excel, які можуть значно розширити можливості формули.

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

=2*3+5

Нижче наведено зразки типів формул, які можна вводити в аркуш.

  • =A1+A2+A3   . Додає значення в клітинках A1, A2 та A3.

  • =SUM(A1:A10)   . За допомогою функції SUM повертає суму значень у клітинках від A1 до A10.

  • =TODAY()   . Повертає поточну дату.

  • =UPPER("вітаю")   . Перетворює текст "вітаю" на "ВІТАЮ", використовуючи функцію UPPER.

  • =IF(A1>0)   . За допомогою функції IF перевіряє клітинку A1, щоб визначити, чи вона містить додатне значення.

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

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

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

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

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

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

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

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

Оператори визначають тип обчислень, які потрібно виконати з елементами формули. У програмі Excel діють загальноприйняті математичні правила для обчислень, тобто дужки, експоненти, множення й ділення, додавання й віднімання. Дужки дають змогу змінити порядок обчислення.

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

  • Арифметичні оператори

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

    Арифметичний оператор

    Значення

    Приклад

    + (знак плюс)

    Додавання

    =3+3

    – (знак мінус)

    Віднімання
    Від’ємність

    =3–3
    =-3

    * (зірочка)

    Множення

    =3*3

    / (ліва скісна риска)

    Ділення

    =3/3

    % (знак відсотка)

    Відсоток

    30%

    ^ (''кришка'')

    Піднесення до степеня

    =3^3

  • Оператори порівняння

    Нижче наведено оператори, за допомогою яких можна порівняти два значення. Результатом порівняння буде логічне значення TRUE (істина) або FALSE (хибність).

    Оператор порівняння

    Значення

    Приклад

    = (знак рівності)

    Дорівнює

    =A1=B1

    > (знак ''більше'')

    Більше

    =A1>B1

    < (знак ''менше'')

    Менше

    =A1<B1

    >= (знак ''більше або дорівнює'')

    Більше або дорівнює

    =A1>=B1

    <= (знак ''менше або дорівнює'')

    Менше або дорівнює

    =A1<=B1

    <> (знак ''не дорівнює'')

    Не дорівнює

    =A1<>B1

  • Оператор об’єднання тексту

    Амперсанд (&) використовується для об’єднання одного або кількох текстових рядків в один фрагмент тексту.

    Текстовий оператор

    Значення

    Приклад

    & (амперсанд)

    Об’єднує два значення та створює одне безперервне текстове значення

    ="North"&"wind" повертає "Northwind".
    Якщо клітинка A1 містить "Прізвище", а B1 – "Ім’я", то =A1&", "&B1 повертає "Прізвище, Ім’я".

  • Оператори посилань

    Об’єднують діапазони клітинок для обчислень із такими операторами.

    Оператор посилання

    Значення

    Приклад

    : (двокрапка)

    Оператор діапазону. Створює посилання на всі клітинки, які розташовано між двома посиланнями (включно з ними).

    B5:B15

    , (кома)

    Оператор об’єднання. Об’єднує кілька посилань в одне.

    =SUM(B5:B15;D5:D15)

    (пробіл)

    Оператор перетину. Створює одне посилання на клітинки, спільні у двох посиланнях.

    B7:D7 C6:C8

На початок сторінки

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

  • Порядок обчислення

    Формули обчислюють значення в певному порядку. Формула у програмі Excel завжди починається зі знака рівності (=). Символи після знака рівності інтерпретуються як формула. Після знака рівності розташовуються елементи, які потрібно обчислити (операнди), наприклад константи або посилання на клітинки. Їх розділяють оператори обчислення. Формули у програмі Excel обчислюються зліва направо, відповідно до порядку для кожного оператора формули.

  • Пріоритет операторів у формулах Excel

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

    Оператор

    Опис

    : (двокрапка)

    (один пробіл)

    ; (крапка з комою)

    Оператори посилань

    Від’ємність (як у –1)

    %

    Відсоток

    ^

    Піднесення до степеня

    * та /

    Множення й ділення

    + і –

    Додавання й віднімання

    &

    Поєднання двох рядків тексту (об’єднання)

    =
    < >
    <=
    >=
    <>

    Порівняння

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

    Щоб змінити порядок обчислення, візьміть у дужки частину формули, яку потрібно обчислити першою. Наприклад, наведена нижче формула дає результат 11, оскільки програма Excel виконує спочатку множення, а потім додавання. Формула помножує 2 на 3, а потім додає 5 до результату.

    =5+2*3

    На відміну від цього, якщо використати дужки для змінення синтаксису, програма Excel спочатку додасть 5 і 2, а потім помножить результат на 3. У результаті отримаємо 21.

    =(5+2)*3

    У наведеному нижче прикладі дужки, у які взято першу частину формули, визначають такий порядок обчислень: спочатку обчислюється значення B4+25, відтак отриманий результат ділиться на суму значень у клітинках D5, E5 і F5.

    =(B4+25)/SUM(D5:F5)

    На початок сторінки

Функції – це попередньо визначені формули, які виконують обчислення в особливому порядку чи за певною структурою, використовуючи спеціальні значення, що називаються аргументами. За допомогою функцій можна виконувати прості та складні обчислення. Усі функції в програмі Excel можна знайти на вкладці "Формули" стрічки.

Вкладка "Формули" на стрічці Excel
  • Синтаксис функцій Excel

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

    Структура функції

    1. Структура. Структура функції починається зі знака рівності (=), за яким іде ім’я функції, ліва дужка, аргументи функції, розділені крапками з комою, і права дужка.

    2. Ім’я функції. Щоб переглянути список доступних функцій, клацніть клітинку та натисніть клавіші Shift+F3 (відкриється діалогове вікно Вставлення функції).

    Формули Excel – діалогове вікно "Вставлення функції"

    3. Аргументи. Аргументами можуть бути числа, текст, логічні значення, як-от TRUE (істина) або FALSE (хибність), масиви, значення помилок, як-от #N/A, або посилання на клітинки. Аргумент, який ви призначаєте, має створювати припустиме для цього аргументу значення. Аргументами також можуть бути константи, формули або інші функції.

    4. Підказка аргументів. Коли ви вводите функцію, з’являється підказка із синтаксисом і аргументами. Наприклад, введіть =ROUND( і побачите підказку. Підказки відображаються лише для вбудованих функцій.

    Примітка : Вам не обов’язково вводити функції великими буквами, як-от =ROUND, адже програма Excel автоматично замінить малі букви в імені функції на великі, коли ви натиснете клавішу Enter. Якщо ви зробите помилку в імені функції, наприклад введете =SUME(A1:A10) замість =SUM(A1:A10), програма Excel поверне помилку #NAME?.

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

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

    Майстер функцій Excel

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

    Автозаповнення формул в Excel

  • Вкладення функцій Excel

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

    вкладені функції

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

    Припустимі відповіді   . Коли вкладена функція використовується як аргумент, вона має повернути такий самий тип значення, який використовує цей аргумент. Наприклад, якщо аргумент повертає значення TRUE (істина) або FALSE (хибність), вкладена функція також має повернути значення TRUE (істина) або FALSE (хибність). У протилежному випадку програма Excel відображає значення помилки #VALUE!.

    Обмеження вкладених рівнів   . Формула може містити до семи рівнів вкладених функцій. Якщо функція Б використовується як аргумент у функції А, то функція Б – це функція другого рівня. Наприклад, функція AVERAGE і функція SUM – це функції другого рівня, якщо вони використовуються як аргументи функції IF. Функція, вкладена у вкладену функцію AVERAGE, буде функцією третього рівня тощо.

    На початок сторінки

Посилання визначає клітинку або діапазон клітинок на аркуші та вказує програмі 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. Щоб відкрити це діалогове вікно, перейдіть на вкладку Файл.

    На початок сторінки

Можна створити визначені імена, які позначатимуть клітинки, діапазони клітинок, формули, константи або таблиці Excel. Ім’я – це змістовне умовне позначення, яке допомагає зрозуміти призначення посилання на клітинку, константу, формулу або таблицю. Нижче наведено типові приклади імен і того, як вони допоможуть краще розуміти формули.

Приклад 1

Тип прикладу

Приклад із діапазоном замість імені

Приклад з іменем

Посилання

=SUM(A16:A20)

=SUM(Продажі)

Константа

=PRODUCT(A12;9,5%)

=PRODUCT(Ціна;Ставка_податку)

Формула

=TEXT(VLOOKUP(MAX(A16;A20),A16:B20,2,FALSE);"дд/мм/рррр")

=TEXT(VLOOKUP(MAX(Продажі);ДаніПродажів;2;FALSE);"дд/мм/рррр")

Таблиця

A22:B25

=PRODUCT(Ціна;Таблиця1[@Ставка_податку])

Приклад 2

Скопіюйте дані прикладу з наведеної нижче таблиці та вставте їх у клітинку A1 нового аркуша Excel. Щоб відобразити результат обчислення формул, виберіть їх, натисніть клавішу F2, а потім натисніть клавішу Enter. За потреби можна змінити ширину стовпців, щоб відобразити всі дані.

Примітка :  У формулах у стовпцях C та D визначене ім’я "Продажі" замінюється на посилання на діапазон A9:A13, а ім’я "ДаніПродажів" – на діапазон A9:B13. Якщо не створити ці імена в тестовому аркуші, формули в клітинках D2:D3 повернуть помилку #NAME?.

Тип прикладу

Приклад без імені

Приклад із іменем

Формула та результат із іменем

Посилання

'=SUM(A9:A13)

'=SUM(Продажі)

=SUM(Продажі)

Формула

'=TEXT(VLOOKUP(MAX(A9:13);A9:B13;2;FALSE);"m/dd/yyyy")

'=TEXT(VLOOKUP(MAX(Продажі);ДаніПродажів;2;FALSE);"m/dd/yyyy")

=TEXT(VLOOKUP(MAX(Продажі);ДаніПродажів;2;FALSE);"m/dd/yyyy")

Ціна

995₴

Продажі

Дата продажу

249₴

17.03.2011

399₴

02.04.2011

643₴

23.04.2011

275₴

30.04.2011

447₴

04.05.2011

  • Типи імен

    Існує кілька типів імен, які можна створити та використовувати.

    • Визначене ім’я   . Ім’я, яке представляє клітинку, діапазон клітинок, формулу або константу. Ви можете створити власне визначене ім’я. Також у програмі Excel визначені імена іноді створюються автоматично, наприклад під час установлення області друку.

    • Ім’я таблиці   . Ім’я для таблиці Excel, набору даних про певний предмет, збережених у записах (рядках) і полях (стовпцях). Під час кожного додавання таблиці Excel програма Excel створює стандартне ім’я таблиці (''Таблиця1'', ''Таблиця2'' тощо), проте його можна змінити, щоб зробити більш зрозумілим.

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

  • Створення та введення імен

    Створювати імена можна наведеними нижче способами.

    • Поле ''Ім’я'' в рядку формул   . Це найзручніший спосіб створити ім’я рівня робочої книги для виділеного діапазону.

    • Створення імені з виділення   . Ви можете легко створити імена з наявних підписів рядків і стовпців за допомогою виділення клітинок аркуша.

    • Діалогове вікно "Нове ім’я"   . Це діалогове вікно доцільно використовувати, коли потрібна більша гнучкість у створенні імен, наприклад можливість визначення області локального рівня аркуша або створення примітки до імені.

    Примітка :  За замовчуванням в іменах використовуються абсолютні посилання на клітинки.

    Вводити імена можна такими способами:

    • Вводити на клавіатурі.    Вводити ім’я на клавіатурі, наприклад як аргумент у формулі.

    • Використати автозаповнення формул.    Скористатися розкривним списком автозаповнення формул, у якому автоматично наводяться припустимі імена.

    • Вибір із команди ''Використовувати у формулі''   . Вибір визначеного імені зі списку, доступного з команди Використовувати у формулі у групі Визначені імена на вкладці Формули.

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

На початок сторінки

Формула масиву може виконувати кілька обчислень і повертати один або кілька результатів. Формула масиву використовує для обчислень два або більше наборів значень, які називаються масивом аргументів. Кожний масив аргументів має містити однакову кількість рядків і стовпців. Формули масивів створюються так само, як і решта формул, з тією відмінністю, що для ведення формули потрібно натискати сполучення клавіш Ctrl+Shift+Enter. Деякі вбудовані функції – це формули масивів, і їх потрібно вводити як масиви, щоб отримати правильний результат.

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

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

Примітка : Коли ви вводите формулу масиву в Excel, вона автоматично береться у фігурні дужки { }. Якщо спробувати ввести фігурні дужки самостійно, формула відобразиться як текст.

  • Формула масиву з одним результатом   . За допомогою формули масиву цього типу можна спростити модель аркуша, замінивши кілька різних формул на одну формулу масиву.

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

    формула масиву, яка повертає єдиний результат

    Якщо ввести формулу ={SUM(B2:D2*B3:D3)} як формулу масиву, вона перемножує кількість і ціну для кожної акції та додає результати цих обчислень.

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

    Наприклад, ми маємо послідовність із трьох показників збуту (стовпець B) для трьох місяців (стовпець A). Функція TREND визначає лінійні значення показників збуту. Нам потрібно відобразити всі результати обчислення формули, тому ми вводимо її в три клітинки стовпця C (C1:C3).

    Формула масиву, яка повертає кілька результатів

    Якщо ввести формулу =TREND(B1:B3;A1:A3) як формулу масиву, вона повертає три окремі результати (22 196, 17 079 і 11 962), обчислені на основі трьох значень показників продажу та трьох місяців.

Використання констант-масивів

У звичайній формулі можна ввести посилання на клітинку, яка містить значення, або саме значення, яке також називається константою. Так само у формулу масиву можна ввести посилання на масив або ввести масив значень, які містяться у клітинках і також називаються константою-масивом. Формули масиву приймають константи так само, як і звичайні формули, але константи-масиви потрібно вводити в певному форматі.

Константи-масиви можуть містити числа, текст, логічні значення, зокрема TRUE (істина) або FALSE (хибність), або значення помилок, такі як #N/A. Одна константа-масив може містити різні типи значень, наприклад {1;3;4:TRUE;FALSE;TRUE}. Числа в масиві констант можуть бути цілими числами, десятковими дробами або в експоненційному форматі. Текст потрібно брати в подвійні лапки, наприклад ''Вівторок''.

Константи-масиви не можуть містити посилання на клітинки, стовпці або рядки різної довжини, формули або спеціальні символи, як $ (знак долара), дужки або % (знак відсотка).

Під час форматування констант-масивів переконайтеся, що виконуються наведені нижче умови.

  • Масив узято у фігурні дужки ({ }).

  • Значення в різних стовпцях розділено крапкою з комою (;). Наприклад, щоб представити значення 10, 20, 30 і 40, введіть {10;20;30;40}. Ця константа-масив називається також матрицею розмірності 1 на 4 – еквівалент посилання на 1 рядок і 4 стовпця.

  • Значення в різних рядках розділено двокрапкою (:). Наприклад, щоб представити значення 10, 20, 30 і 40 в одному рядку, а 50, 60, 70 і 80 в іншому під ним, можна ввести масив констант 2 на 4: {10;20;30;40:50;60;70;80}.

На початок сторінки

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

  • Щоб видалити формули разом зі значеннями результату, виконайте наведені нижче дії.

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

    2. Натисніть клавішу Delete.

  • Щоб видалити формули, але не видаляти значення результату, виконайте наведені нижче дії.

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

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

      Виділення діапазону клітинок, що містять формулу масиву

      1. Клацніть клітинку у формулі масиву.

      2. На вкладці Основне у групі Редагування виберіть пункт Знайти й виділити, а потім виберіть пункт Перейти.

      3. Натисніть кнопку Особливі.

      4. Установіть перемикач поточний масив.

    2. На вкладці Основне в групі Буфер обміну натисніть кнопку Копіювати Зображення кнопки .
      Зображення стрічки Excel

      Сполучення клавіш    Також можна натиснути клавіші Ctrl+C.

    3. На вкладці Основне у групі Буфер обміну клацніть стрілку під кнопкою Вставити  Зображення кнопки та виберіть пункт Вставити значення.

На початок сторінки

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

Контрольний список

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

У формулі кожна ліва та права дужки мають пару   

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

Діапазони у формулі позначено двокрапкою   

Двокрапки (:) розділяють у посиланні першу та останню клітинку в діапазоні. Наприклад, A1:A5.

Введено всі потрібні аргументи    

Функції можуть мати обов’язкові та необов’язкові аргументи (у синтаксисі позначаються квадратними дужками). Без обов’язкових аргументів функція не обчислюватиметься. Крім того, переконайтеся, що не введено забагато аргументів.

У формулу не вкладено більше 64 функцій   

Вкладення функцій в одну формулу обмежено до 64 рівнів.

Імена книг або аркушів узято в одинарні лапки    

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

До зовнішніх книг указано шлях    

Зовнішні посилання мають містити ім’я книги та шлях до неї.

Числа введено без форматування    

У формулу слід вводити числа без знаків долара ($) і десяткових роздільників, оскільки знаки долара позначають абсолютні посилання, а пробіли порушують синтаксис формули. Наприклад, замість $1 000 введіть 1000 у формулі.

На початок сторінки

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

Відповіді на запитання щодо конкретних функцій

Опублікувати запитання на форумі спільноти Excel

Допомога в удосконаленні програми Excel

Маєте пропозиції щодо того, як покращити наступну версію програми Excel? Тоді перегляньте розділи на веб-сайті Excel User Voice

Див. також

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

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

Виявлення помилок у формулах

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

Функції Excel (за алфавітом)

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

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

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

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

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

×