Формули Excel: що таке формули Excel і як ними користуватися

Термін «формули Excel» може стосуватися будь-якої комбінації оператори ді Excel та/або Функції Excel.

Формула Excel вводиться в клітинку електронної таблиці шляхом введення знака =, після якого слідують необхідні оператори та/або функції. Це може бути просто доповнення (наприклад, «=A1+B1») або це може бути складна комбінація операторів Excel і кількох вкладених функцій Excel.

Оператори Excel

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

Це:

• Арифметичні оператори
• Текстові оператори
• Оператори порівняння
• Довідкові оператори

Давайте опишемо чотири типи операторів:

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

Арифметичні оператори Excel і порядок їх обчислення наведено в наведеній нижче таблиці.

Пріоритет арифметичних операторів

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

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

Приклади арифметичних операторів

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

Оператор конкатенації Excel (позначений символом &) об’єднує текстові рядки, щоб створити додатковий єдиний текстовий рядок.

Приклад оператора конкатенації

Наступна формула використовує оператор конкатенації для об’єднання текстових рядків “SMITH" " і "John"

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

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

Приклади операторів порівняння

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

Довідкові оператори

Довідкові оператори Excel використовуються під час посилань на діапазони в електронній таблиці. Довідкові оператори:

Приклади довідкових операторів

Приклад 1 – Оператор діапазону Excel

У клітинці C1 у наведеній нижче електронній таблиці показано оператор діапазону, який використовується для визначення діапазону A1-B3. Потім діапазон надається функції SUM Excel, який додає значення в клітинках A1-B3 і повертає значення 21.

Приклад 2 – Оператор об’єднання Excel

клітина C1 наступної електронної таблиці показано оператор об’єднання, який використовується для визначення діапазону, що складається з комірок у двох діапазонах A1-A3 e A1-B1. Отриманий діапазон потім надається функції SUM в Excel, який підсумовує значення в комбінованому діапазоні та повертає значення 12.

Зауважте, що оператор об’єднання Excel не повертає справжнє математичне об’єднання, наприклад комірку A1, який входить до обох діапазонів A1-A3 e A1-B1 враховується двічі при обчисленні суми).

Приклад 3 – Оператор перетину Excel

У клітинці C1 у наведеній нижче електронній таблиці показано оператор перетину, який використовується для визначення діапазону, створеного над клітинками на перетині діапазонів A1-A3 e A1-B2. Отриманий діапазон (діапазон A1-A2) потім передається до функції SUM Excel, який підсумовує значення в пересічному діапазоні та повертає значення 4.

Більше інформації про оператори Excel доступно на Веб-сайт Microsoft Office.

Функції Excel

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

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

Статистичні функції Excel:

Кількість і частота

• COUNT: повертає кількість числових значень у наданому наборі клітинок або значень;
• COUNTA: повертає кількість непробілів у наданому наборі клітинок або значень;
• COUNTBLANK: повертає кількість порожніх клітинок у наданому діапазоні;
• COUNTIF: повертає кількість клітинок (заданого діапазону), які задовольняють заданий критерій;
• COUNTIFS: повертає кількість комірок (наданого діапазону), які задовольняють заданому набору критеріїв (Нове в Excel 2007);
• FREQUENCY: повертає масив, що показує кількість значень із наданого масиву, які потрапляють у вказані діапазони;

Пошук максимуму і мінімуму

• MAX: повертає найбільше значення зі списку наданих чисел
• MAXA: повертає найбільше значення зі списку наданих значень, враховуючи текст і логічне значення FALSE як значення 0 і підрахунок логічного значення TRUE як значення 1
• MAXIFS: повертає найбільше значення з підмножини значень у вказаному списку на основі одного чи кількох критеріїв. (Нове в Excel 2019)
• MIN: повертає найменше значення зі списку наданих чисел
• MINA: Повертає найменше значення зі списку наданих значень, враховуючи текст і логічне значення FALSE як значення 0 і підраховуючи логічне значення TRUE як значення 1
• MINIFS: повертає найменше значення з підмножини значень у вказаному списку на основі одного чи кількох критеріїв. (Що нового в Excel 2019)
• LARGE: повертає K-те НАЙБІЛЬШЕ значення зі списку наданих чисел для заданого значення K
• SMALL: повертає K-те НАЙМЕНШЕ значення зі списку наданих чисел для заданого значення K

середній

• AVERAGE: повертає середнє зі списку наданих чисел
• AVERAGEA: повертає середнє зі списку наданих чисел, враховуючи текст і логічне значення FALSE як значення 0, а логічне значення TRUE вважаючи значенням 1
• AVERAGEIF: обчислює середнє значення клітинок у наданому діапазоні, які відповідають заданому критерію (Нове в Excel 2007)
• AVERAGEIFS: обчислює середнє значення клітинок у наданому діапазоні, які відповідають багатьом критеріям (Нове в Excel 2007)
• MEDIAN: повертає медіану (середнє значення) списку наданих чисел
• MODE: обчислює режим (найчастіше значення) заданого списку чисел (замінено функцією Mode.Sngl в Excel 2010)
• MODE.SNGL: обчислює режим (найчастіше значення) списку наданих чисел (Нове в Excel 2010: замінює функцію Mode)
• MODE.MULT: повертає вертикальний масив найбільш частих значень у масиві або діапазоні даних (Нове в Excel 2010)
• GEOMEAN: повертає середнє геометричне заданого набору чисел
• HARMEAN: повертає середнє гармонічне набору наданих чисел
• TRIMMEAN: повертає внутрішнє середнє заданого набору значень

Перестановки

• PERMUT: повертає кількість перестановок для заданої кількості об’єктів
• PERMUTATIONA: повертає кількість перестановок для заданої кількості об’єктів (із повторами), які можна вибрати із загальної кількості об’єктів (Нове в Excel 2013)

Довірчі інтервали

• CONFIDENCE: повертає довірчий інтервал для середнього сукупності за допомогою нормального розподілу (замінено функцією Confidence.Norm у Excel 2010)
• CONFIDENCE.NORM: повертає довірчий інтервал для середнього сукупності за допомогою нормального розподілу (Нове в Excel 2010: замінює функцію «Достовірність»)
• CONFIDENCE.T: повертає довірчий інтервал для середнього сукупності за допомогою t-розподілу Стьюдента (Нове в Excel 2010)

Процентили та квартилі

• PERCENTILE: повертає K-й процентиль значень у наданому діапазоні, де K знаходиться в діапазоні 0–1 (включно) (замінено функцією Percentile.Inc в Excel 2010)
• PERCENTILE.INC: повертає K-й процентиль значень у наданому діапазоні, де K знаходиться в діапазоні 0–1 (включно) (Нове в Excel 2010: замінює функцію Percentile)
• PERCENTILE.EXC: повертає K-й процентиль значень у наданому діапазоні, де K знаходиться в діапазоні 0–1 (виключно) (Нове в Excel 2010)
• QUARTILE: повертає вказаний квартиль заданого набору чисел на основі значення процентиля 0–1 (включно) (замінено функцією Quartile.Inc у Excel 2010)
• QUARTILE.INC: повертає вказаний квартиль заданого набору чисел на основі значення процентиля 0–1 (включно) (Нове в Excel 2010: замінює функцію Quartile)
• QUARTILE.EXC: повертає вказаний квартиль заданого набору чисел на основі значення процентиля 0–1 (виключне) (Нове в Excel 2010)
• RANK: повертає статистичний ранг заданого значення в межах наданого масиву значень (замінено функцією Rank.Eq в Excel 2010)
• RANK.EQ: повертає режим (найчастіше значення) списку наданих чисел (якщо кілька значень мають однаковий ранг, повертається найвищий ранг цього набору) (Нове в Excel 2010: замінює функцію Rank)
• RANK.AVG: повертає статистичний ранг заданого значення в межах наданого масиву значень (якщо кілька значень мають однаковий ранг, повертається середній ранг) (Нове в Excel 2010)
• PERCENTRANK: повертає ранг значення в наборі даних у відсотках (0–1 включно) (замінено функцією Percentrank.Inc у Excel 2010)
• PERCENTRANK.INC: повертає ранг значення в наборі даних у відсотках (0–1 включно) (Нове в Excel 2010: замінює функцію Percentrank)
• PERCENTRANK.EXC: повертає ранг значення в наборі даних у відсотках (за винятком 0–1) (Нове в Excel 2010)

Відхилення та дисперсія

• AVEDEV: повертає середнє значення абсолютних відхилень точок даних від їх середнього
• DEVSQ: повертає суму квадратів відхилень набору точок даних від середнього вибіркового значення
• STDEV: повертає стандартне відхилення наданого набору значень (що представляє вибірку генеральної сукупності) (замінено функцією St.Dev в Excel 2010)
• STDEV.S: Повертає стандартне відхилення заданого набору значень (що представляє вибірку сукупності) (Нове в Excel 2010: замінює функцію STDEV)
• STDEVA: Повертає стандартне відхилення заданого набору значень (що представляє вибірку генеральної сукупності), враховуючи текст і логічне значення FALSE як значення 0 і підраховуючи логічне значення TRUE як значення 1
• STDEVP: повертає стандартне відхилення заданого набору значень (що представляє всю генеральну сукупність) (замінено функцією StdPDev в Excel 2010)
• STDEV.P: Повертає стандартне відхилення заданого набору значень (що представляє всю генеральну сукупність) (Нове в Excel 2010: замінює функцію STDEV)
• STDEVPA: Повертає стандартне відхилення заданого набору значень (що представляє всю генеральну сукупність), враховуючи текст і логічне значення FALSE як значення 0 і підраховуючи логічне значення TRUE як значення 1
• VAR: повертає дисперсію заданого набору значень (що представляє вибірку генеральної сукупності) (замінено функцією SVar в Excel 2010)
• VAR.S: повертає дисперсію заданого набору значень (що представляє вибірку генеральної сукупності) (Нове в Excel 2010 – замінює функцію Var)
• VARA: Повертає дисперсію заданого набору значень (що представляє вибірку генеральної сукупності), підраховуючи текст і логічне значення FALSE як значення 0 і підраховуючи логічне значення TRUE як значення 1
• VARP: повертає дисперсію заданого набору значень (що представляє всю генеральну сукупність) (замінено функцією Var.P в Excel 2010)
• VAR.P: повертає дисперсію заданого набору значень (що представляє всю генеральну сукупність) (Нове в Excel 2010 – замінює функцію Varp)
• VARPA: Повертає дисперсію заданого набору значень (що представляє всю генеральну сукупність), підраховуючи текст і логічне значення FALSE як значення 0, і підраховуючи логічне значення TRUE як значення 1
• COVAR: повертає коваріацію генеральної сукупності (тобто середнє значення добутків відхилень для кожної пари в межах двох заданих наборів даних) (замінено функцією Covariance.P в Excel 2010)
• COVARIANZA.P: повертає коваріацію сукупності (тобто середнє значення добутків відхилень для кожної пари в межах двох заданих наборів даних) (Нове в Excel 2010: замінює функцію Covar)
• COVARIANZA.S: повертає вибіркову коваріацію (тобто середнє значення добутків відхилень для кожної пари в межах двох заданих наборів даних) (Нове в Excel 2010)

Прогностичні функції

• FORECAST: Прогнозує майбутню точку на лінійній лінії тренду, яка відповідає заданому набору значень x і y ​​(замінено функцією FORECAST.LINEAR в Excel 2016)
• FORECAST.ETS: Utilizza un алгоритм di livellamento esponenziale per prevedere un valore futuro su una sequenza temporale, in base a una serie di valori esistenti (Nuovo in Excel 2016 – non disponibile in Excel 2016 per Mac)
• FORECAST.ETS.CONFINT: повертає довірчий інтервал для прогнозованого значення на вказану цільову дату (Нове в Excel 2016 – недоступне в Excel 2016 для Mac)
• FORECAST.ETS.SEASONALITY: повертає довжину шаблону, що повторюється, виявленого Excel для певного часового ряду (Нове в Excel 2016 – недоступне в Excel 2016 для Mac)
• FORECAST.ETS.STAT: повертає статистичне значення про прогноз часових рядів (Нове в Excel 2016 – недоступне в Excel 2016 для Mac)
• FORECAST.LINEAR: прогнозує майбутню точку на лінійній лінії тренду відповідно до заданого набору значень x і y ​​(Нове в Excel 2016 (не Excel 2016 для Mac) – замінює функцію прогнозу)
• INTERCEPT: обчислює найбільш прийнятну лінію регресії через ряд значень x і y, повертає значення, за якого ця лінія перетинає вісь y
• LINEST: повертає статистичну інформацію, яка описує тенденцію лінії найкращого підходу через ряд значень x і y
• SLOPE: повертає нахил лінії лінійної регресії через заданий набір значень x і y
• TREND: обчислює лінію тренду через заданий набір значень y і повертає додаткові значення y для заданого набору нових значень x
• GROWTH: повертає числа в експоненційному тренді зростання на основі набору наданих значень x і y
• LOGEST: повертає параметри експоненціального тренду для заданого набору значень x і y
• STEYX: повертає стандартну помилку прогнозованого значення y для кожного x у лінії регресії для заданого набору значень x і y

Ercole Palmeri