Формули робочих таблиць

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

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

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

оператори (наприклад, + " додавання" або * " множення");

адреса клітинки, включаючи поіменовані клітинки і діапазони;

числа;

функції робочих таблиць (наприклад, СУММ (SUM)).

Довжина формули не повинна перевищувати 1024 символи. Якщо ввести формулу в клітинку, то в ній буде відтворений результат розрахунку за цією формулою. Однак у пам'яті комп'ютера зберігається саме формула, а не результат розрахунків за нею.

Наведемо кілька прикладів формул:

2.4.2. Оператори, які використовуються у формулах

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

Користувач може застосовувати скільки завгодно операторів (формули можуть бути достатньо складними). На рис. 2.35 відтворена робоча таблиця з формулою у клітинці В6. Ця фомула має такий вигляд:

=(В2-В3)*В4

Рис. 2.35. Формула, у якій використовуються два оператори

У цьому випадку формула віднімає число, розміщене в клітинці В3, від числа, розміщеного в клітинці В4. Якби користувач присвоїв цим клітинкам імена, то формула була б більш наочною. Ось як трансформується ця формула після присвоєння імен:

=(Прибуток-Витрати)*СтавкаПодатку

Теперь зрозуміло, чому корисно застосовувати імена клітинок. Наведемо ще кілька прикладів, у яких використовуються різні оператори.

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

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

=Прибуток-Витрати*СтавкаПодатку

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

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

Таблиця 2.2

Пріоритет операторів у формулах Excel

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

=(Прибуток-Витрати)*СтавкаПодатку

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

=((В2*С2)+(В3*С3)+(В4*С4))*В6

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

2.4.4. Абсолютна, відносна та змішана адресація клітинок у формулах

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

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

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

=В2*С2

Якщо скопіювати цю формулу в дві клітинки, розміщені нижче від клітинки D2, то Excel розмістить у них не зовсім точні копії наведеної формули. Excel змінить їх так:

Клітинка D3 =В3*С3

Клітинка D4 =В4*С4

Рис. 2.36. Приклад робочої таблиці Excel перед копіюванням формули у графі D

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

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

2.4.4.2. Абсолютна адресація клітинок у формулах

У деяких випадках необхідно, щоб адреси клітинок копіювалися без змін. На рис. 2.37 наведено приклад, коли в клітинці В6 міститься значення ставки податку на продаж. Формула у клітинці Е2 має такий вигляд:

=(В2*С2)*$В$6 або = D2*$В$6

Зверніть увагу на те, як у цій формулі подано адресу клітинки В6 (попереду адрес графи В і рядка 6 стоїть знак $ (долар)). Це означає, що адреса цієї клітинки є абсолютною. При копіюванні цієї формули в дві клітинки, розміщені нижче від клітинки Е2, будуть відтворені такі формули:

Клітинка Е3 =(В3*С3)*$В$6 або = D3*$В$6

Клітинка Е4 =(В4*С4)*$В$6 або = D4*$В$6

Відносні адреси клітинок змінились, але адреса клітинки В6 залишилась без змін, оскільки у формулі вона була подана як абсолютна.

Рис. 2.37. Приклад робочої таблиці Excel перед копіюванням формули у графі Е

2.4.4.3. Змішана адресація клітинок у формулах

В абсолютній адресі клітинки використовуються два знаки $ (долар). Один - попереду імені графи, а інший - попереду номера рядка. Однак в Excel є ще і змішана адресація клітинок. В табл. 2.3 подані всі можливі типи адрес клітинок.

Таблиця 2.3

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

2.4.5. Вмонтовані функції Excel

Excel має велику кількість вмонтованих функцій робочих таблиць, які користувачі можуть застосовувати в своїх формулах. До них належать як досить поширені функції, такі як СУММ (SUM), СРЗНАЧ (AVERAGE) і КОРЕНЬ (SQRT), так і функції, призначені для виконання спеціальних обчислень, наприклад, статистичні функції або прикладні функції. Функції можуть спростити формули і зробити їх зрозумілішими, крім того, вони здебільшого дають змогу робити обчислення, які без них виконати дуже важко.

В Excel входить більше як 300 вмонтованих функцій.

2.4.5.1. Поняття про функції Excel

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

спрощувати формули;

виконувати за формулами такі обчислення, які без них зробити неможливо;

прискорювати виконання деяких завдань редагування.

Крім того, вони дозволяють " умовне" виконання формул, завдяки чому користувач може реалізовувати алгоритми прийняття рішень.

2.4.5.2. Приклади функцій

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

Для розрахунку середнього значення чисел, розміщених у десяти клітинках (А1: А10), використана така формула:

=(А1+А2+А3+А4+А5+А6+А7+А8+А9+А10)/10

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

=СРЗНАЧ(А1: А10) або = AVERAGE(А1: А10)

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

=МАКС(А1: D100) або = MAX(А1: D100)

Інколи функції дозволяють уникнути ручного редагування. Припустимо, що в робочій таблиці є графа (наприклад, А), у якій наведений список працівників вашої (установи близько 1000) (рис. 2.38, а). Всі прізвища введені великими буквами: " ІВАНОВ П. А". Необхідно відтворити цей список так, щоб прізвища мали такий вигляд: " Іванов П. А.". На ручне переоформлення списку потрібно буде витратити кілька годин. Але є можливість значно скоротити час завдяки вмонтованій функції

ПРОПНАЧ (PROPER).

Для виконання цієї операції необхідно:

У робочу таблицю вставити допоміжну графу (між графами А і В). Після цього робоча таблиця набере такого вигляду, як подано на рис. 2.38, б.

Встановити табличний курсор у клітинку В2.

Ввести в цю клітинку формулу:

=ПРОПНАЧ(А2) або =PROPER(А2)

Скопіювати цю формулу в решту 999 клітинок (рис. 2.38, в).

Виділити діапазон клітинок (В2: В1000).

Виконати команду Правка та її опцію Специальная вставка (Edit? Paste Special), позначивши опцію Значення (Values).

Видалити графу А і робоча таблиця матиме такий вигляд, як подано на рис. 2.38, г.

А

Б

В

Г

Рис. 2.38. Приклад використання вмонтованої функції ПРОПНАЧ:

а - робоча таблиця Excel до застосування функції ПРОПНАЧ;

б - між графами А і В вставлена допоміжна графа;

в - результат копіювання формули =ПРОПНАЧ(А2) в решту клітинок;

г - робоча таблиця Excel після застосування функції ПРОПНАЧ

Припустимо, що користувачеві необхідно підрахувати в робочій таблиці комісійні за результатами продажу якого-небудь товару. Якщо продавець продав товару на суму, що перевищує 100 тис. грн., то його ставка комісійних становить 7, 5%, якщо на меншу суму, то 5%. Без використання функції користувачеві необхідно створити дві різні формули і правильно їх використати для кожної позиції списку. В цьому випадку необхідно скористатися функцією ЕСЛИ (IF) для розрахунку комісійних незалежно від суми продажів.

=ЕСЛИ(А1< 100000; A1*0, 05; A1*0, 075)

або

=IF(А1< 100000; A1*0, 05; A1*0, 075)

2.4.5.3. Aргументи функцій

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

без аргументів;

з одним аргументом;

з невизначеною кількістю аргументів;

з необов'язковим аргументом.

Прикладом функції, що не використовує аргумент, є функція СЛЧИС (RAND), яка генерує випадкове число в діапазоні від 0 до 1. Синтаксис цієї функції такий (хоч і немає аргументу, дужки обов'язкові):

=CЛЧИС() або =RAND()

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

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

Наприклад:

=SIN(РАДИАНЫ(В9)) або =SIN(RADIANS(В9))

Функція РАДИАНЫ (RADIANS) перетворює значення аргумента, яке задане в градусах, на радіани, оскільки в усіх тригонометричних функціях Excel аргументи задаються в радіанах. Тому, якщо в клітинці В9 міститься значення кута в градусах, то функція РАДИАНЫ (RADIANS) перетворює його на радіани, а функція SIN обчислює синус кута.

Вкладеність функцій обмежується тільки загальною довжиною рядка формули - 1024 символи.

Примітка. В багатьох функціях як аргумент використовується посилання на діапазон клітинок. Наприклад, в такій функції використовується діапазон клітинок А10: А20:

=СУММ(А10: А20) або =SUM(A10: A20)

Якщо користувач додає новий рядок між рядками 10 і 20, то Excel автоматично розширює діапазон у формулі. Наприклад, якщо користувач додав рядок між рядками 12 і 13, то формула матиме такий вигляд:

=СУММ(А10: А21) або =SUM(A10: A21)

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

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

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

=СУММ(А: А) або =SUM(A: A)

Зарезервувати в діапазоні клітинок кілька додаткових рядків. Наприклад, якщо дані введені в діапазон клітинок А1: А10, причому відомо, що у майбутньому цей діапазон буде розширюватися (до нього буде додано новий рядок), потрібно змінити діапазон у формулі на А1: А11 (клітинку А11 залиште вільною). Тоді буде можливість вставити новий рядок (11-й) у робочу таблицю, посилання на який вже є у формулі.

2.4.5.4. Способи введення функцій

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

2.4.5.4.1. Ручне введення функцій

Перший метод введення функцій у клітинки робочої таблиці полягає в тому, що необхідно просто ввести з клавіатури ім'я функції і список її аргументів. Дуже часто цей спосіб є найефективнішим.

Примітка.

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

Якщо користувач забув закрити дужки, то Excel зробить це автоматично. Наприклад, якщо була набрана формула =СУММ(А1: С12) і натиснута клавіша < Enter>, то Excel виправить формулу, додавши праву дужку.

2.4.5.4.2. Використання Мастера функций