Використання формул в Microsoft Office Excel

1. Формули в MS Excel

Всі розрахунки в Excel виконують формули. Excel вважає формулою все, що починається із знаку "=". Якщо в комірці написати просто 1+1, то Excel не буде обчислювати цей вираз. Для того, що б в комірці з'явиться результат обчислення виразу, потрібно після набору формули натиснути клавішу Enter. Після натискання клавіші Enter формула не пропадає, її можна побачити у рядку формул.

Формула – це рівняння, яке використовується для обчислення значення певного поняття або закону. Формула починається зі знака рівності (=). У формулі можна використовувати різні типи операторів, текст, посилання на комірку або діапазон комірок, круглі дужки, іменовані діапазони. У формулах дотримується пріоритет виконання операцій (множення виконується раніше додавання і т.п.). Для зміни порядку виконання операцій використовуються круглі дужки.

Формула також може містити такі елементи, як функція, посилання, оператор та константа.

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

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

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

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

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

Якщо у формулі використовується текст, то він обов'язково повинен бути включений у подвійні лапки. Якщо написати формулу «= мама», Excel видасть помилку, а якщо написати «=" мама "» – формула буде коректною. 

2. Використання констант

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

3. Оператори

Існує чотири різні типи операторів обчислення:

1. арифметичні;

2. порівняльні;

3. оператори об’єднання тексту;

4. оператори посилання. 

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

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

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

Нижче наведено оператори, за допомогою яких можна порівняти два значення. Результатом порівняння є логічне значення: ІСТИНА або ХИБНІСТЬ.

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

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

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

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

4. Пріоритет обчислення формули

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

Формули обчислюють значення в певному порядку. Формула в Excel завжди починається зі знака рівності (=). Знак рівності свідчить, що подальші символи утворюють формулу. Після знаку рівності йдуть елементи, які потрібно обчислити (операнди), розділені операторами обчислення. 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) 

5. Функції Excel

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

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

Щоб використати функцію, потрібно ввести її як частину формули в комірку робочого аркуша. Послідовність, у якій мають розміщуватися використовувані у формулі символи називають синтаксисом функції. Всі функції використовують однакові основні правила синтаксису. Якщо порушити правила синтаксису, то Excel видасть повідомлення про помилку у формулі. Для спрощення роботи з функціями більшість з них була названа від скорочення російськомовних значень цих функцій: Наприклад:

СУММ – функція, що здійснює додавання елементів.

СРЗНАЧ – функція, що визначає середнє значення.

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

Приклад функції ROUND, яка округляє число у клітинці A10, ілюструє синтаксис функції.

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

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

2. Ім’я функції. Список доступних функцій можна відкрити, клацнувши клітинку і натиснувши сполучення клавіш SHIFT+F3.

3. Аргументи. Аргументами можуть бути числа, текст, логічні значення, масив, значення помилок, такі як #N/A, або посилання на клітинку. Аргументом також може бути константа, формула або інша функція.

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

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

Для зручності роботи Excel функції розбиті за категоріями: 

• математичні функції;

• статистичні функції;

• логічні функції;

• фінансові функції;

• функції дати і часу;

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

• функції роботи з базами даних;

• текстові функції;

• функції посилання та масивів.

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

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

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

1) Вставка → Функції;

2) натискання кнопки Майстер функцій (fx), що розміщена на панелі інструментів Стандартна;

3) Shift+F3.

Вікно Майстра функцій складається з трьох частин.

У першому можна ввести опис дії, яку необхідно виконати і натиснути кнопку Найти. Цей метод використовується, якщо користувач не знає чи не пам’ятає, як називається потрібна йому функція. Нижче є поле для вибору категорії функцій. Для спрощення роботи з великим обсягом вбудованих функцій всі вони розділені на категорії залежно від призначення. Це значно спрощує пошук потрібної функції. Є окремо виділені категорії: 10 недавно використаних функцій та Повний алфавітний перелік для спрощення пошуку функцій. У третій частині є можливість вибору функцій відповідно до категорії. При виборі функцій в нижній частині вікна відображається коротка інформація про призначення обраної функції.

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

5.2 Математичні, логічні та статичні функції

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

• СУММ – додає аргументи.

• КОРЕНЬ – повертає додатне значення квадратного кореня.

• COS, SIN, TAN – тригонометричні функції cos, sin і tg.

• ACOS, ATAN – зворотні тригонометричні функції arсcos, arсtg.

• ГРАДУСЫ – перетворює радіани в градуси.

• LN – натуральний логарифм числа.

• ABS – модуль числа.

• ПИ – повертає число Пі (π=3.14).

• ЗНАК – повертає знак числа.

• ПРОИЗВЕД – повертає добуток аргументів.

• СТЕПЕНЬ – повертає результат піднесення до степеня.

• ОКРУГЛ – закруглює число до заданої кількості десяткових розрядів.

• ОСТАТ – повертає залишок від ділення.

• СЛЧИС – повертає випадкове число в інтервалі від 0 до 1.

• РИМСКОЕ – перетворює число в арабському записі до числа в римському як текст.

• СУММЕСЛИ – повертає суму вмісту комірок, яке задовольняє заданий критерій.

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

Окрему групу становлять функції, призначені для роботи з матрицями. При їх застосуванні є особливості: аргументами таких функцій є діапазон комірок. При введенні функцій, аргументами яких є масиви (матриці) і які повертають як результат матрицю, необхідно перед введенням функції виділяти не одну комірку, куди буде розміщений результат, а діапазон. Завершити введення аргументів матричних функцій слід обов’язково натисканням комбінації клавіш Ctrl+Shift+Enter, а не просто кнопки OK.

Статистичні функції призначені для проведення статистичного аналізу. Крім того, їх можна використовувати для факторного та регресійного аналізу. Спочатку розглянемо найуживаніші:

• СРЗНАЧ – визначає середнє значення.

• МИН, МАКС – визначає мінімальне та максимальне значення.

• СЧЕТ – визначає кількість числових аргументів.

Ці функції винесені на панель інструментів Стандартна.

Приклад: Необхідно знайти максимальне значення в діапазоні від А2 до А7.

Для розв’язання цієї задачі використовують функцію МАКС(аргументи), що повертає максимальне значення зі списку аргументів: =МАКС(А2:А7).

Розглянемо деякі інші функції:

• СРГЕОМ – визначає середнє геометричне.

• СРЗНАЧ – визначає середнє арифметичне аргументів, якими можуть бути як числа, так і текст, логічні значення.

• СЧЕТЕСЛИ – підраховує кількість значення у переліку аргументів, які задовольняють деяку умову.

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

1. Найбільш важливою є функція ЕСЛИ. Ця функція використовується для розв’язання задач, в яких необхідно перевірити деяку умову, і залежно від того виконується вона чи ні, повертає одне з двох значень.

Ця функція записується так:

ЕСЛИ (логічний вираз;значення, якщо істина;значення, якщо хибна).

Якщо умова виконується, то виконується вираз «значення, якщо істина». Якщо умова не виконується, то виконується вираз «значення, якщо хибна».

2. Функція И повертає значення істина, якщо всі аргументи мають значення істина, тобто коли виконуються всі умови.

Синтаксис:=И(логічне_значення_1;логічне_значення_2;...).

Цю функцію використовують для об’єднання двох і більше умов.

3. Функція ЛОЖЬ повертає логічне значення ЛОЖЬ. Синтаксис: =ЛОЖЬ().

4. Функція НЕ – змінює на протилежне логічне значення аргументу. Наприклад, якщо є значення «-1», при використанні даної функції значення зміниться на протилежне, тобто буде «1».

5. Функція ИЛИ – повертає логічне значення істина, якщо коли хоч один з аргументів має значення істина. 

5.3 Функції дати і часу

Для роботи зі значенням типу дата та час в Excel використовують функції категорії ДАТА і ВРЕМЯ. Розглянемо деякі із них.

• ДАТА повертає значення дати. Загальний вигляд функції ДАТА(рік;місяць;день). Функція ДАТА(2000;2;1) залежно від установленого формату дати повертає значення 01.02.00.

• ДЕНЬ повертає день дати в числовому форматі. Наприклад, у комірці F2 вміщена дата 28.10.2003, тоді значення функції ДЕНЬ(F2) дорівнює 28.

• ДЕНЬНЕД визначає день тижня, на який припадає дата, визначена як аргумент. Синтаксис функції: ДЕНЬНЕД(дата;тип). При цьому аргумент тип визначає порядок розрахунку і може мати значення: 1 (за замовчуванням) – число від 1 (неділя) до 7; 2 – число від 1 (понеділок) до 7; 3 – число від 0 (неділя) до 6.

• СЕГОДНЯ має загальний вигляд СЕГОДНЯ() і повертає значення поточної дати.

• ЧАС повертає значення часу в налаштованому часовому форматі. Синтаксис запису: ЧАС(години;хвилини;секунди).

• ТДАТ повертає поточну дату та час. Синтаксис функції: ТДАТ().

• МЕСЯЦ використовується для визначення місяця. Синтаксис функції: МЕСЯЦ(дата в числовому форматі). Наприклад, МЕСЯЦ(10.01.2007) повертає значення 1.

• ДНЕЙ360 визначає кількість днів між двома датами, яку вона вираховує на основі 360-денного року. 

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

Вкладені функції – це функції, аргументами яких можуть бути інші функції.

Наприклад, необхідно визначити поточний рік. Функція ГОД() визначає значення року від певної дати, а функція СЕГОДНЯ() визначає поточну дату. Тому для визначення поточного року необхідно записати: =ГОД(СЕГОДНЯ()).

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

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

Коли вкладена функція використовується як аргумент, вона має повернути такий же тип значення, який використовує аргумент. Наприклад, якщо аргумент повертає значення ІСТИНА або ХИБНІСТЬ, то вкладена функція також має повернути значення ІСТИНА або ХИБНІСТЬ. У протилежному випадку Microsoft Excel відображає значення помилки #VALUE!.

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

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

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

За замовчуванням Excel використовує стиль посилань A1, який посилається на стовпці з буквами (від A до XFD, загалом 16 384 стовпців) і на рядки з номерами (від 1 до 1 048 576). Ці букви й номери називаються заголовками рядків і стовпців. Щоб послатися на клітинку, потрібно ввести букву стовпця, а після неї – номер рядка. Наприклад, B2 – посилання на клітинку на перетині стовпця B і рядка 2.

Створення посилання на інший аркуш. У наведеному нижче прикладі функція AVERAGE використовується для обчислення середнього значення в діапазоні B1:B10 на аркуші «Маркетинг» у тій самій книзі.

6.1 Різниця між абсолютними, відносними та змішаними посиланнями

Відносні посилання на клітинку у формулі, наприклад, A1, базуються на відносній позиції клітинки, що містить формулу, і клітинки, на яку вказує посилання. Якщо зміниться позиція клітинки, що містить формулу, змінюється й посилання. Якщо скопіювати або заповнити формулу у рядку або у стовпці, то посилання зміниться автоматично. За замовчуванням, нові формули використовують відносні посилання. Наприклад, якщо скопіювати або заповнити відносне посилання з клітинки B2 до клітинки B3, воно автоматично зміниться з =A1 на =A2.

Абсолютне посилання на клітинку у формулі, наприклад $A$1, завжди посилається на клітинку у заданому розташуванні. Якщо зміниться позиція клітинки, що містить формулу, абсолютне посилання залишається таким самим. Якщо скопіювати або заповнити формулу в рядку або стовпці, то абсолютне посилання не змінюється. За замовчуванням, нові формули використовують відносні посилання, і може виникнути потреба змінити їх на абсолютні. Наприклад, якщо скопіювати або заповнити абсолютне посилання з клітинки B2 до клітинки B3, воно в обох клітинках буде однакове: =$A$1.

Змішане посилання має або абсолютний стовпець і відносний рядок, або абсолютний рядок і відносний стовпець. Абсолютне посилання на стовпець приймає форму $A1, $B1, і т.д. Абсолютне посилання на рядок приймає форму A$1, B$1, і т.д. Якщо змінюється позиція клітинки, яка містить формулу, то відносне посилання змінюється, а абсолютне – ні. Якщо скопіювати або заповнити формулу в рядку або стовпці, то відносне посилання автоматично змінюється, а абсолютне посилання не змінюється. Наприклад, якщо скопіювати або заповнити змішане посилання з клітинки A2 до B3, воно змінюється з =A$1 до =B$1.

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

6.2 Об’ємні посилання

Якщо потрібно проаналізувати дані в тій самій клітинці або тому самому діапазоні клітинок у книзі, використовується об’ємне посилання. Об’ємне посилання включає посилання на клітинку або діапазон, перед якими стоїть діапазон імен аркушів. Excel використовує будь-які аркуші між початковими і кінцевими іменами у посиланні. Наприклад, =SUM(Аркуш2:Аркуш13!B5) додає усі значення, які містяться у клітинці B5 на усіх аркушах між аркушами 2 та 13, включно з ними.

Використовувати цей стиль можна для посилання на клітинки на інших аркушах, для визначення імен і для створення формул за допомогою таких функцій: SUM, AVERAGE, AVERAGEA, COUNT, COUNTA, MAX, MAXA, MIN, MINA, PRODUCT, STDEV, STDEVA, STDEVP, STDEVPA, VAR, VARA, VARP і VARPA.

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

6.3 Посилання у стилі R1C1

Можна використовувати стиль посилань, в якому на аркуші пронумеровані і рядки, і стовпці. Посилання в стилі R1C1 корисні для обчислення позицій рядка і стовпця у макросі. У стилі R1C1 Excel позначає розташування клітинки буквою "R", за якою йде номер рядка, і буквою "C", за якою йде номер стовпця.

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

Увімкнути або вимкнути посилання у стилі R1C1 можна, установивши або знявши прапорець Стиль посилань R1C1 у розділі Робота з формулами категорії Формули в діалоговому вікні Параметри Excel, яке відкривається в меню кнопки Microsoft Office 

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

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

Є кілька типів імен, які можна створювати й використовувати:

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

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

Створювати імена можна, використовуючи:

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

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

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

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

Для введення імені можна використовувати:

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

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

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

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

Формула масиву виконує кілька обчислень і потім повертає єдиний результат або кілька результатів. Формула масиву виконує обчислення над двома або більше наборами значень, які називаються масивом аргументів. Кожний аргумент масиву повинен мати ту саму кількість рядків і стовпців. Формули масивів створюються так само, як і інші формули, з тією відмінністю, що потрібно натискати для введення формули сполучення клавіш CTRL+SHIFT+ENTER.

Під час введення формула масиву Microsoft Excel автоматично бере формулу у фігурні дужки ({ }).

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

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

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

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

1. Як змінити ширину стовпця в електронній таблиці?

2. Як змінити висоту рядка?

3. Від чого залежить відображення чисел в комірці?

4. Яке призначення формул в електронній таблиці?

5. Що зображено в клітинці після введення в неї формули? Як побачити формулу?

6. Для чого використовують текстові дані в електронній таблиці?

7. Як відобразити числа з символом грошової одиниці?

8. Як побачити формулу у одній клітинці, формули у всій електронній таблиці?

9. Як виконати копіювання формули?

10. Як задати чи змінити деякий формат відображення чисел?

11. Як вимкнути режим відображення сітки?

12. Які ви знаєте формати числових даних?

13. Як захистити клітинки від змін у них?

14. У чому суть автоматичного переобчислення в електронній таблиці?

15. Як заповнити стовпець зростаючою послідовністю чисел?

16. Що відбувається під час копіювання формули в електронній таблиці?

17. Які основні прийоми автоматизації обчислень в електронній таблиці?

18. Охарактеризуйте абсолютне посилання в електронній таблиці.

19. Чим відрізняється абсолютне посилання від відносного? У яких випадках використовують абсолютні посилання?

20. Що таке об’ємне посилання?

21. Яким чином можна іменувати дані в Excel?

1. Нужний Є.М. Інструментальні засоби електронного офісу [навіч. посіб.] / Є. М. Нужний, І.В. Клименко, І.В. Клименко, О.О. Акімов. – К.: «Центр учбової літератури», 2016. – 296 с.

2. Співаковський О.В. Інформаційні технології в юридичній діяльності: базовий курс: [навчальний посібник]. / О.В.Співаковський, М.І. Шерман, В.М. Стратонов, В.В. Лапінський – Херсон: ХДУ, 2012. – 220 с.

3. Microsoft Office 2010. Офіційний довідник користувача. (електронний варіант, формат pdf)