Якщо ви раніше не вебпрограма Excel, скоро ви побачите, що це більше, ніж просто сітка, у яку вводяться числа в стовпці або рядки. Так, за допомогою вебпрограма Excel можна знайти підсумки для стовпця або рядка чисел, але також можна обчислити іпотечний платіж, розв'язати математичні або технічні задачі або знайти найкращий сценарій на основі змінних чисел, які ви підключаєте.
вебпрограма Excel це робить за допомогою формул у клітинках. Формула виконує обчислення або інші дії з даними на аркуші. Формула завжди починається зі знака рівності (=), після якого може бути вказано числа, математичні оператори (такі як "плюс" або "мінус"), а також функції, які можуть значно розширити можливості формули.
Наприклад, у наступній формулі 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. Оператори: оператор ^ (кришка) піднімає число до степеня, а оператор * (зірочка) перемножує числа.
Використання констант у формулах
Константа – це значення, яке не обчислюється та завжди залишається однаковим. Наприклад, дати 09.10.2028, число 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 виконує операції у формулах
Іноді порядок виконання обчислень може вплинути на результат формули, тому важливо розуміти, як визначається порядок і як його можна змінити для отримання потрібних результатів.
Порядок обчислення
Формули обчислюють значення в певному порядку. Формула завжди починається знаком рівності (=). вебпрограма Excel інтерпретує символи після знака рівності як формулу. Після знака рівності розташовуються елементи, які потрібно обчислити (операнди), наприклад константи або посилання на клітинки. Їх розділяють оператори обчислення. вебпрограма Excel обчислює формулу зліва направо відповідно до певного порядку для кожного оператора у формулі.
Пріоритет оператора
Якщо поєднати кілька операторів в одній формулі, операції вебпрограма Excel виконуватимуться в порядку, указаному в таблиці нижче. Якщо формула містить оператори з однаковим пріоритетом, наприклад, якщо формула містить оператор множення та ділення, вебпрограма Excel обчислює оператори зліва направо.
| Оператор | Опис |
|---|---|
| : (двокрапка) (один пробіл) ; (крапка з комою) |
Оператори посилань |
| – | Від’ємність (як у –1) |
| % | частка |
| ^ | Піднесення до степеня |
| * та / | Множення й ділення |
| + і – | Додавання й віднімання |
| & | Поєднання двох рядків тексту (об’єднання) |
| = < > <= >= <> |
Порівняння. |
Використання дужок
Щоб змінити порядок обчислення, візьміть у дужки частину формули, яку потрібно обчислити першою. Наприклад, наведена нижче формула створює 11, оскільки вебпрограма Excel виконує множення перед додаванням. Формула помножує 2 на 3, а потім додає 5 до результату.
=5+2*3
На відміну від цього, якщо для змінення синтаксису використовуються дужки, вебпрограма Excel додає 5 і 2 разом, а потім перемножує результат на 3, щоб отримати 21.
=(5+2)*3
У наведеному нижче прикладі дужки, які беруть першу частину формули, змушують вебпрограма Excel спочатку обчислити B4+25, а потім розділити результат на суму значень у клітинках D5, E5 і F5.
=(B4+25)/SUM(D5:F5)
Використання функцій і вкладених функцій у формулах
Функції – це попередньо визначені формули, які виконують обчислення в особливому порядку за допомогою спеціальних значень, які називаються аргументами. Функції можуть використовуватися для виконання простих або складних обчислень.
Синтаксис функцій
Наведений нижче приклад функції ROUND, яка округляє число у клітинці A10, ілюструє синтаксис функції.
1. Структура. Структура функції починається зі знака рівності (=), імені функції, відкривної дужки, аргументів для функції, розділеної комами, і закривної дужки.
2. Ім'я функції. Щоб переглянути список доступних функцій, клацніть клітинку та натисніть клавіші Shift+F3.
3. Аргументи. Аргументами можуть бути числа, текст, логічні значення, наприклад TRUE (істина) або FALSE (хибність), масиви, значення помилок, такі як #N/A, або посилання на клітинки. Аргумент, який призначається, має створювати припустиме для цього аргументу значення. Аргументи також можуть бути константами, формулами або іншими функціями.
4. Підказка аргументу. Під час введення функції з’являється підказка із синтаксисом і аргументами. Наприклад, введіть =ROUND(, і з’явиться підказка. Підказки відображаються лише для вбудованих функцій.
Введення функцій
Під час створення формули, яка містить функцію, для введення функції до аркуша можна використовувати діалогове вікно Вставлення функції. Під час введення функції у формулу діалогове вікно Вставлення функції відображає ім’я функції, кожен із її аргументів, опис функції та кожного аргументу, поточний результат функції та поточний результат усієї формули.
Щоб спростити створення й редагування формул і зменшити кількість помилок вводу та синтаксичних помилок, використовуйте функцію автозаповнення формул. Коли ви вводите = (знак рівності) і перші букви або тригер відображення, вебпрограма Excel відображає під клітинкою динамічний розкривний список припустимих функцій, аргументів та імен, які відповідають буквам або тригеру. Потрібний елемент цього списку можна вставити у формулу.
Вкладення функцій
У певних випадках можна використати функцію як один з аргументів іншої функції. Наприклад, наведена формула використовує вкладену функцію AVERAGE і порівнює результат зі значенням 50.
1. Функції AVERAGE і SUM вкладено у функцію IF.
Припустимі повернення Якщо вкладена функція використовується як аргумент, вкладена функція має повертати той самий тип значення, який використовується в аргументі. Наприклад, якщо аргумент повертає значення TRUE або FALSE, вкладена функція має повернути значення TRUE або FALSE. Якщо ця функція не працює, вебпрограма Excel відображає #VALUE! .
Обмеження рівня вкладення Формула може містити до семи рівнів вкладених функцій. Якщо функція Б використовується як аргумент у функції A, то функція Б – це функція другого рівня. Наприклад, функції 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. Відокремлення посилання на аркуш від посилання на діапазон клітинок
Різниця між абсолютними, відносними та мішаними посиланнями
Відносні посилання Відносне посилання на клітинку у формулі, наприклад A1, базується на відносному положенні клітинки, яка містить формулу, і клітинки, на яку посилається посилання. Якщо змінюється положення клітинки, що містить формулу, змінюється й посилання. Якщо скопіювати або заповнити формулу в рядку або у стовпці, то посилання зміниться автоматично. За промовчанням нові формули використовують відносні посилання. Наприклад, якщо заповнити або скопіювати відносне посилання із клітинки B2 до клітинки B3, воно автоматично зміниться з =A1 на =A2.
Абсолютні посилання Абсолютне посилання на клітинку у формулі, наприклад $A$1, завжди посилається на клітинку в певному розташуванні. Якщо змінюється положення клітинки, яка містить формулу, абсолютне посилання залишається без змін. Якщо скопіювати або заповнити формулу в рядку або стовпці, то абсолютне посилання не змінюється. За промовчанням нові формули використовують відносні посилання, тому може бути потрібно змінити їх на абсолютні. Наприклад, якщо заповнити або скопіювати абсолютне посилання із клітинки B2 до клітинки B3, воно буде однаковим в обох клітинках: =$A$1.
Змішані посилання Змішане посилання має абсолютний стовпець і відносний рядок, або абсолютний рядок і відносний стовпець. Абсолютне посилання на стовпець має вигляд $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 (кінцеві точки в цьому прикладі), вебпрограма Excel включає всі значення в клітинках A2–A5 із доданих аркушів у обчисленнях.
- Видалити Якщо видалити аркуші між Аркушем2 та Аркушем6, вебпрограма Excel видалить їхні значення з обчислення.
- Переміщення Якщо перемістити аркуші з аркуша між Аркушем2 та Аркушем6 до розташування за межами діапазону аркушів, на який посилається посилання, вебпрограма Excel видалить їх значення з обчислення.
- Переміщення кінцевої точки Якщо перемістити Аркуш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, посилань.
Використання імен у формулах
Ви можете створити визначені імена для представлення клітинок, діапазонів клітинок, формул, констант або вебпрограма Excel таблиць. Ім’я – це змістовне умовне позначення, яке допомагає зрозуміти призначення посилання на клітинку, константу, формулу або таблицю. Нижче наведено типові приклади імен і того, як вони допоможуть краще розуміти формули.
| Приклад типу | Приклад із діапазоном замість імені | Приклад з іменем |
|---|---|---|
| Посилання | =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 іноді створює визначене ім'я, наприклад, коли ви встановлюєте область друку.
Ім'я таблиці Ім'я вебпрограма Excel таблиці– це набір даних про певний предмет, який зберігається в записах (рядках) і полях (стовпцях). вебпрограма Excel створюється стандартне ім'я вебпрограма Excel таблиці "Таблиця1", "Таблиця2" тощо під час кожного вставлення вебпрограма Excel таблиці, але ці імена можна змінити, щоб зробити їх зрозумілішими.
Створення та введення імен
Ім'я створюється за допомогою команди "Створити ім'я з виділення". Ви можете легко створити імена з наявних підписів рядків і стовпців за допомогою виділення клітинок аркуша.
Примітка.
За замовчуванням в іменах використовуються абсолютні посилання на клітинки.
Вводити імена можна такими способами:
- Набравши Введіть ім'я, наприклад, як аргумент у формулі.
- Використати автозаповнення формул. Скористатися розкривним списком автозаповнення формул, у якому автоматично наводяться припустимі імена.
Використання формул масивів і констант масивів
вебпрограма Excel не підтримує створення формул масивів. Можна переглядати результати формул масивів, створених у програмі Excel для настільних комп'ютерів, але не можна їх редагувати або переобчислювати. Якщо у вас є класична програма Excel, виберіть Редагувати>відкрити в класичній програмі, щоб працювати з масивами.
У наведеному нижче прикладі масиву обчислюється загальна вартість масиву біржових цін і акцій без використання рядка клітинок для обчислення та відображення окремих значень для кожної акції.
Якщо ввести формулу ={SUM(B2:D2*B3:D3)} як формулу масиву, вона кратна акціям і цінам для кожної акції, а потім підсумовує результати цих обчислень.
Обчислення кількох результатів Деякі функції аркуша повертають масиви значень або вимагають масив значень як аргумент. Для обчислення кількох результатів за допомогою формули масиву необхідно ввести масив у діапазон клітинок, кількість рядків і стовпців у якому відповідає кількості рядків і стовпців у масиві аргументів.
Наприклад, якщо встановити ряд із трьох показників збуту (у стовпці B) за три місяці (у стовпці A), функція TREND визначає прямі значення для показників збуту. Щоб відобразити всі результати формули, її буде введено в три клітинки в стовпці C (C1:C3).
Якщо ввести формулу =TREND(B1:B3;A1:A3) як формулу масиву, вона дає три окремі результати (22196, 17079 і 11962) на основі трьох показників збуту та трьох місяців.
Використання констант-масивів
У звичайній формулі можна ввести посилання на клітинку, яка містить значення, або саме значення, яке також називається константою. Так само у формулу масиву можна ввести посилання на масив або ввести масив значень, які містяться у клітинках і також називаються константою-масивом. Формули масиву приймають константи так само, як і звичайні формули, але константи-масиви потрібно вводити в певному форматі.
Константи-масиви можуть містити числа, текст, логічні значення, зокрема 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}.