За допомогою формул і функцій у списках або бібліотеках можна обчислювати дані різними способами. Додавши обчислюваний стовпець до списку або бібліотеки, можна створити формулу, яка містить дані з інших стовпців, і виконувати функції для обчислення дат і часу, виконання математичних формул або роботи з текстом. Наприклад, у списку завдань за допомогою стовпця можна обчислити кількість днів, потрібних для виконання кожного завдання, залежно від стовпців Дата початку та Дата завершення.
Примітка.: У цій статті описано основні поняття, пов'язані з використанням формул і функцій. Докладні відомості про певну функцію див. в статті про цю функцію.
У цій статті
Огляд формул
Формули – це формули, які виконують обчислення значень у списку або бібліотеці. Формула починається знаком рівності (=). Наприклад, наведена нижче формула множить 2 на 3, а потім додає до результату 5.
=5+2*3
Формулу можна використовувати в обчислюваному стовпці та для обчислення значень за замовчуванням для стовпця. Формула може містити функції, посилання на стовпці, оператори та константи, як у наведеному нижче прикладі.
=PI()*[Result]^2
|
Елемент |
Опис |
|---|---|
|
Функція |
Функція PI() повертає значення числа пі: 3,141592654. |
|
Посилання (або ім'я стовпця) |
[Результат] представляє значення в стовпці Результат для поточного рядка. |
|
Константа |
Числа або текстові значення, введені безпосередньо у формулу, наприклад 2. |
|
Оператор |
Оператор * (зірочка) множиться, а оператор ^ (кришка) підводить число до степеня. |
У формулі може використовуватися один або кілька елементів із попередньої таблиці. Нижче наведено кілька прикладів формул (у порядку складності).
Прості формули (наприклад, =128+345)
Наведені нижче формули містять константи та оператори.
|
Приклад |
Опис |
|---|---|
|
=128+345 |
Додає 128 і 345 |
|
=5^2 |
Квадрати 5 |
Формули, які містять посилання на стовпці (наприклад, =[Дохід] >[Витрати])
Наведені нижче формули посилаються на інші стовпці в тому самому списку або бібліотеці.
|
Приклад |
Опис |
|---|---|
|
=[Дохід] |
Використовує значення в стовпці Дохід. |
|
=[Дохід]*10/100 |
10% від значення в стовпці Дохід. |
|
=[Дохід] > [Вартість] |
Повертає значення Так, якщо значення в стовпці Дохід перевищує значення в стовпці Витрати. |
Формули, які викликають функції (наприклад, =AVERAGE(1, 2, 3, 4, 5))
Наведені нижче формули викликають вбудовані функції.
|
Приклад |
Опис |
|---|---|
|
=AVERAGE(1, 2, 3, 4, 5) |
Повертає середнє значення набору значень. |
|
=MAX([Q1];[Q2];[Q3];[Q4]) |
Ця функція повертає найбільше значення з набору значень. |
|
=IF([Вартість]>[Дохід];"Не OK";"OK") |
Повертає значення Not OK, якщо вартість більша за прибуток. Інакше повертає ОК. |
|
=DAY("15-кві-2008") |
Повертає частину дня дати. Ця формула повертає число 15. |
Формули з вкладеними функціями (наприклад, =SUM(IF([A]>[B], [A]-[B], 10), [C]))
Наведені нижче формули визначають одну або кілька функцій як аргументи функції.
|
Приклад |
Опис |
|---|---|
|
=SUM(IF([A]>[B], [A]-[B], 10), [C]) |
Функція IF повертає різницю між значеннями у стовпцях A та B або 10. Функція SUM додає повернуте значення функції IF і значення у стовпці C. |
|
=DEGREES(PI()) |
Функція PI повертає число 3,141592654. Функція DEGREES перетворює значення, указане в радіанах, на градуси. Ця формула повертає значення 180. |
|
=ISNUMBER(FIND("КМ";[Стовпець1])) |
Функція FIND шукає рядок BD у Стовпці1 і повертає початкове положення рядка. Повертає значення помилки, якщо рядок не знайдено. Функція ISNUMBER повертає значення "Так", якщо функція FIND повертає числове значення. Інакше він повертає значення "Ні". |
Огляд функцій
Функції – це попередньо визначені формули, які виконують обчислення в особливому порядку за допомогою спеціальних значень, які називаються аргументами. Функції можуть використовуватися для виконання простих або складних обчислень. Наприклад, наведений нижче екземпляр функції ROUND округлює число у стовпці Витрати до двох десяткових розрядів.
=ROUND([Cost], 2)
Під час вивчення функцій і формул корисний такий словник:
Структура Структура функції починається зі знака рівності (=), імені функції, відкривної дужки, аргументів для функції, розділеної комами, і закривної дужки.
Назва функції Це ім'я функції, яка підтримується списками або бібліотеками. Кожна функція приймає певну кількість аргументів, обробляє їх і повертає значення.
Аргументи Аргументи можуть бути числами, текстом, логічними значеннями, такими як True, False або column references. Аргумент, який призначається, має створити припустиме значення для цього аргументу. Аргументи також можуть бути константами, формулами або іншими функціями.
У певних випадках можна використати функцію як один з аргументів іншої функції. Наприклад, наведена нижче формула використовує вкладену функцію AVERAGE і порівнює результат із сумою значень двох стовпців.
=AVERAGE([Cost1], SUM([Cost2]+[Discount]))
Припустимі повернення Коли функція використовується як аргумент, вона має повертати той самий тип значення, який використовує аргумент. Наприклад, якщо в аргументі використовується значення "Так" або "Ні", вкладена функція має повернути "Так" або "Ні". Якщо цього не сталося, у списку або бібліотеці відображається #VALUE! .
<c0>Обмеження вкладених рівнів</c0>. Формула може містити до восьми рівнів вкладених функцій. Коли функція B використовується як аргумент у функції A, функція B – це функція другого рівня. Наприклад, у наведеному вище прикладі функція SUM – це функція другого рівня, оскільки вона є аргументом функції AVERAGE. Функція, вкладена у функцію SUM, буде функцією третього рівня тощо.
Примітки.:
-
Списки та бібліотеки не підтримують функції RAND і NOW.
-
Функції TODAY і ME не підтримуються в обчислюваних стовпцях, але підтримуються в стандартному параметрі значення стовпця.
Використання посилань на стовпці у формулі
Посилання визначає клітинку в поточному рядку та вказує на список або бібліотеку, де шукати значення або дані, які потрібно використовувати у формулі. Наприклад, [Витрати] посилається на значення в стовпці Витрати в поточному рядку. Якщо стовпець Витрати має значення 100 для поточного рядка, =[Вартість]*3 повертає 300.
З посиланнями в одній або кількох формулах можна використовувати дані, які містяться в різних стовпцях списку або бібліотеки. У формулі можна посилатися на стовпці таких типів даних: один рядок тексту, число, грошова одиниця, дата й час, вибір, так/ні та обчислюване значення.
Коротке ім'я стовпця використовується для посилання на нього у формулі. Якщо ім'я містить пробіл або спеціальний символ, ім'я потрібно взяти у квадратні дужки ([ ]). Посилання не чутливі до регістра. Наприклад, ви можете посилатися на стовпець "Ціна за одиницю" у формулі як [Ціна за одиницю] або [ціна за одиницю].
Примітки.:
-
Не можна посилатися на значення в рядку, відмінному від поточного рядка.
-
Не можна посилатися на значення в іншому списку або бібліотеці.
-
Не можна посилатися на ідентифікатор рядка для щойно вставленого рядка. Ідентифікатор ще не існує, коли виконується обчислення.
-
Не можна посилатися на інший стовпець у формулі, яка створює стандартне значення для стовпця.
Використання констант у формулі
Константа – це значення, яке не обчислюється. Наприклад, дата 09.10.2008, число 210 або текст "Квартальний прибуток" – це константи. Константи можуть мати такі типи даних:
-
Рядок (приклад: =[Прізвище] = "Коваль")
Рядкові константи взято в лапки та можуть містити до 255 символів.
-
Число (приклад: =[Вартість] >= 29,99)
Числові константи можуть містити десяткові розряди та можуть бути додатні або від'ємні.
-
Дата (наприклад: =[Дата] > DATE(2007;7;1))
Константи дат вимагають використання функції DATE(рік;місяць;день).
-
Логічне значення (наприклад: =IF([Вартість]>[Дохід], "Збиток", "Без втрат")
Так і Ні – це логічні константи. Їх можна використовувати в умовних виразах. У наведеному вище прикладі, якщо аргумент «вартість» більший за «Прибуток», функція IF повертає значення «Так», а формула повертає рядок «Збиток». Якщо аргумент «вартість» дорівнює або менший за «прибуток», функція повертає значення «Ні», а формула повертає рядок «Без втрат».
Використання операторів обчислення у формулі
Оператори визначають тип обчислень, які потрібно виконати з елементами формули. Списки і бібліотеки підтримують три різні типи операторів обчислення: арифметичні, порівняльні та текстові.
Арифметичні оператори
Використовуйте такі арифметичні оператори для виконання основних математичних операцій, як-от додавання, віднімання або множення; для об'єднання чисел; або для отримання числових результатів.
|
Арифметичний оператор |
Значення (наприклад) |
|---|---|
|
+ (знак плюс) |
Додавання (3+3) |
|
– (знак мінус) |
Віднімання (3–1)Заперечення (–1) |
|
* (зірочка) |
Множення (3*3) |
|
/ (ліва скісна риска) |
Ділення (3/3) |
|
% (знак відсотка) |
Відсоток (20%) |
|
^ (''кришка'') |
Експоненція (3^2) |
Оператори порівняння
Нижче наведено оператори, за допомогою яких можна порівняти два значення. Якщо за допомогою цих операторів порівнюються два значення, результатом буде логічне значення "Так" або "Ні".
|
Оператор порівняння |
Значення (наприклад) |
|---|---|
|
= (знак рівності) |
Дорівнює (A=B) |
|
> (знак ''більше'') |
Більше (A>B) |
|
< (знак ''менше'') |
Менше (A<B) |
|
>= (знак ''більше або дорівнює'') |
Більше або дорівнює (A>=B) |
|
<= (знак ''менше або дорівнює'') |
Менше або дорівнює (A<=B) |
|
<> (знак ''не дорівнює'') |
Не дорівнює (A<>B) |
Текстовий оператор
Використовуйте амперсанд (&), щоб об'єднати один або кілька текстових рядків, щоб створити один фрагмент тексту.
|
Текстовий оператор |
Значення (наприклад) |
|---|---|
|
& (амперсанд) |
Об'єднує два значення для отримання одного безперервного текстового значення ("Північ"&"вітер") |
Порядок виконання операцій зі списком або бібліотекою у формулі
Формули обчислюють значення в певній послідовності. Формула може починатися зі знака рівності (=). Після знака рівності обчислюються елементи (операнди), розділені операторами обчислення. Списки та бібліотеки обчислюють формулу зліва направо відповідно до певного порядку для кожного оператора у формулі.
Пріоритет оператора
Якщо поєднати кілька операторів в одній формулі, операції в списках і бібліотеках виконуються в порядку, указаному в таблиці нижче. Якщо формула містить оператори з однаковим пріоритетом , наприклад, якщо формула містить оператор множення та оператор ділення, списки та бібліотеки обчислює оператори зліва направо.
|
Оператор |
Опис |
|---|---|
|
– |
Від’ємність (як у –1) |
|
% |
Відсоток |
|
^ |
Піднесення до степеня |
|
* та / |
Множення й ділення |
|
+ і – |
Додавання й віднімання |
|
& |
Об'єднання (з'єднує два рядки тексту) |
|
= < > <= >= <> |
Порівняння. |
Використання дужок
Щоб змінити порядок обчислення, візьміть у дужки частину формули, яку потрібно обчислити спочатку. Наприклад, наведена нижче формула повертає 11, оскільки список або бібліотека обчислює множення перед додаванням. Формула помножує 2 на 3, а потім додає 5 до результату.
=5+2*3
На відміну від цього, якщо для змінення синтаксису використовуються дужки, список або бібліотека додає 5 і 2 разом, а потім перемножує результат на 3, щоб отримати 21.
=(5+2)*3
У наведеному нижче прикладі дужки навколо першої частини формули змушують список або бібліотеку спочатку обчислювати [Витрати]+25, а потім ділити результат на суму значень у стовпцях EC1 і EC2.
=([Cost]+25)/SUM([EC1]+[EC2])
