Огляд формул

Увага! : Цю статтю перекладено за допомогою служби машинного перекладу; див. застереження. Версію цієї статті англійською мовою див. тут для отримання довідки.

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

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

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

=2*3+5

У цій формулі функція PMT використовується для обчислення кредитного платежу (1 073,64 грн), розмір якого отриманий виходячи з 5 відсотків річної ставки (5% поділити на 12 місяців означає місячну відсоткову ставку) за 30-річний період (360 місяців) для кредиту на суму 200 000 грн:

=PMT(0,05/12;360;200000)

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

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

  • =SQRT(A1)   . Використовує функцію SQRT для повернення квадратного кореня значення в клітинці A1.

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

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

  • =IF(A1>0)   . Перевіряє клітинку A1, щоб визначити, чи вона містить значення, більше 0.

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

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

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

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

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

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

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

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

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

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

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

Типи операторів

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

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

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

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

Значення

Приклад

+ (знак плюс)

Додавання

3+3

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

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

3–1
–1

* (зірочка)

Множення

3*3

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

Ділення

3/3

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

Частка

20%

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

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

3^2

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

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

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

Значення

Приклад

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

Дорівнює

A1=B1

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

Більше

A1>B1

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

Менше

A1<B1

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

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

A1>=B1

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

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

A1<=B1

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

Не дорівнює

A1<>B1

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

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

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

Значення

Приклад

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

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

''Що''&''найдовше'' дає результат ''Щонайдовше''

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

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

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

Значення

Приклад

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

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

B5:B15

, (кома)

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

SUM(B5:B15;D5:D15)

(пробіл)

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

B7:D7 C6:C8

Порядок здійснення операцій у формулах веб-програмою Excel Online

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

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

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

Пріоритет операторів

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

Оператор

Опис

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

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

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

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

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

%

Відсоток

^

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

* та /

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

+ і –

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

&

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

=
< >
<=
>=
<>

Порівняння

Використання дужок

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

=5+2*3

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

=(5+2)*3

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

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

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

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

Синтаксис функцій

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Стиль посилань за промовчанням    За замовчуванням у веб-програмі Excel Online використовується стиль посилань 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 Online використовує всі аркуші між першим і останнім іменем у посиланні. Наприклад, формула =SUM(Аркуш2:Аркуш13!B5) підсумовує всі значення у клітинці B5 на всіх аркушах між другим та тринадцятим, включно з ними.

  • Тривимірні посилання можна використовувати для посилання на клітинки на інших аркушах, визначення імен і створення формул за допомогою таких функцій: 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.

  • Вставлення або копіювання    Якщо вставити або скопіювати аркуші між другим і шостим (у цьому прикладі це кінцеві точки), до обчислення у веб-програмі Excel Online долучаються всі значення у клітинках від A2 до A5 із доданих аркушів.

  • Видалення.     Якщо видалити аркуші між другим і шостим, значення з видалених аркушів не враховуватимуться під час обчислення у веб-програмі Excel Online.

  • Переміщення.    Якщо перемістити аркуші з діапазону між другим і шостим до розташування поза діапазоном аркушів, на який посилається формула, значення з переміщених аркушів не враховуватимуться під час обчислення у веб-програмі Excel Online.

  • Переміщення кінцевої точки    Якщо перемістити аркуш "Аркуш2" або "Аркуш6" до іншого розташування в тій самій книзі, веб-програма Excel Online змінить обчислення відповідно до нового діапазону аркушів між ними.

  • Видалення кінцевої точки    Якщо видалити аркуш "Аркуш2" або "Аркуш6", веб-програма Excel Online змінить обчислення відповідно до діапазону аркушів між ними.

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

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

Посилання

Значення

R[-2]C

відносне посилання на клітинку на два рядки вище в тому самому стовпці

R[2]C[2]

Відносне посилання на клітинку на два рядки нижче й два стовпці праворуч

R2C2

Абсолютне посилання на клітинку в другому рядку та в другому стовпці

R[-1]

Відносне посилання на весь рядок над активною клітинкою

R

Абсолютне посилання на поточний рядок

Коли записується макрос, веб-програма Excel Online записує деякі команди, використовуючи стиль посилань R1C1. Наприклад, якщо записати команду на кшталт натискання кнопки Автосума, яка вставляє формулу, що підсумовує діапазон клітинок, веб-програма Excel Online записує формулу, використовуючи для посилань стиль R1C1, а не A1.

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

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

Тип прикладу

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

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

Посилання

=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[@Ставка_податку])

Типи імен

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

Визначене ім’я.    Ім’я, яке позначає клітинку, діапазон клітинок, формулу або константу. Можна самостійно створити визначене ім’я. Крім того, веб-програма Excel Online іноді створює визначені імена сама (наприклад, коли задається область друку).

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

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

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

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

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

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

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

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

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

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

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

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

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

Наприклад, задано послідовність із трьох показників продажу (у стовпці B) для трьох місяців (у стовпці A). Функція TREND визначає лінійні значення показників продажу. Для відображення всіх результатів формули вона вводиться у три клітинки стовпця С (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}.

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

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

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

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

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

×