Використання формул і функцій

Формули

 

Формула в Excel — це вираз, що задає правила знаходження потрібного значення. Формули є основним засобом аналізу даних.

Формула розміщується в комірці і повертає значення в ту ж комірку.

Формула може містити такі компоненти:

§ символ „=”, з якого розпочинається запис формули;

§ знаки операцій (наприклад, +,?);

§ числа або текстові значення (наприклад, 0,12 або „Замовлення тістечок”);

§ функції з набору вбудованих функцій Excel (наприклад, СУММ або COS);

§ посилання на комірки і діапазони (наприклад, А2, С3:С15).

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

Слід підкреслити, що Excel вважає формулою будь-який запис у комірці, який починається із символу =. Однак запис формули можна почати і зі знаків + або -, у такому разі він також буде сприйнятий як формула.

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

=С1+В1
=СУМ(А3:А12)
=0,35*D7 + 1,4*F7

Введення формул

 

Формули вписуються в рядок формул або безпосередньо в комірку. Запис формули починається із символу =, який вводиться з клавіатури або викликається клацанням по кнопці = у рядку формул. Одразу після цього в рядку формул з'являються нові кнопки: і (мал. 6). Зміст їх простий: клацання по кнопці з червоним хрестиком скасовує внесені до формули зміни, а клацання по кнопці із зеленою «галочкою» дає команду на виконання формули, записаної у комірці.

Завершити введення формули можна натисканням на клавішу Enter, або переміщенням курсору до іншої комірки, або клацанням по кнопці із зеленою «галочкою» в рядку формул. Будь-яка з цих трьох дій рівносильна команді на введення запису в комірку.

Мал. 6. Рядок формул

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

Відредагувати введену формулу можна як звичайний текст у комірці.

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

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

Щоб побачити всі введені формули, потрібно задати режим відображення формул у комірках. Сервис → Параметры → закладка Вид → Формулы → ОК. Щоб знову побачити результати, потрібно зняти режим відображення формул (вимкнути перемикач).

Правила запису формул

· формула починається зі знаку „=”;

· у формулі не повинно бути пробілів, за винятком тих, що містяться в текстах;

· якщо у формулах використовуються тексти, то вони беруться в лапки;

· у формулу можуть входити наступні елементи: оператори, посилання

Арифметичні вирази

 

В Excel можуть застосовуватися арифметичні операції над числами (табл. 1). Для позначення кожної з операцій застосовується спеціальний символ.

Таблиця 1. Арифметичні операції.

Символ операції	Назва операції	Приклад формули	Результат
+	Додавання	=1,5+2,2	3,7
-	Віднімання	=6-5,5	0,5
-	Зміна знака на протилежний (заперечення)	= -33	-33
/	Ділення	=4/5	0,8
*	Множення	=5*6
^	Піднесення до степеня	=3^2
%	Обчислення відсотка	=25%	0,25

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

§ зміна знака на протилежний,

§ обчислення відсотка,

§ піднесення до степеня,

§ множення, ділення (у порядку прямування),

§ додавання, віднімання(у порядку прямування).

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

Наприклад:

Вираз	14/2+5	5+3^2*2	1+8/2*3*5%	9/3^2*(3+5)	(24-9)/3*5
Значення			1,6

 

Вбудовані функції

 

Формули, що вписуються до комірок ЕТ, можуть містити в собі вбудовані (або стандартні) функції.

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

§ математичні — sіn(х), соs(х), ехр(х), sqrt(x) тощо;

§ статистичні — СРЗНАЧ (), МИН (), МАКС (), СУММ () тощо;

§ логічні;

§ для опрацювання дат і текстів;

§ фінансові та ін.

Розглянемо, як здійснюється в Excel звернення до функції.

Запис функції починається з зазначення імені функції, за ним прямує список аргументів, взятий у дужки. Наприклад, функція знаходження максимального серед декількох заданих числових значень... має вигляд: МАКС(число1,число2,..)

Числа — число1, число2…, серед яких потрібно знайти найбільше, виступають аргументами функції.

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

Дужки, в які взято список аргументів, завжди мають бути парними; пробіли перед дужками або після них не допускаються. Окремі аргументи в списку розділяються комами. Деякі функції можуть мати до 30 аргументів.

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

Отже, функція СУММ(В2:В6; С10:Е10) повертає значення, що дорівнює сумі чисел, записаних у комірках стовпчика В від В2 до В6 і у комірках рядка 10 від С10 доЕ10. Функція СУММ (А1; В6:С8; 20) означає, що має бути обчислена сума чисел, розташованих у комірках А1, В6, В7, В8, С6, С7, С8, і числа 20.

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

Наприклад, у запису СУМ(А1:С5, МАКС(D1:Е3)) функція МАКС є вкладеною.

Робота з майстром функцій

 

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

Майстер функцій викликається через меню Вставка → Функция. Діалогове вікно Мастер функций (мал. 7) містить два списки: Категория і Функция. У першому списку потрібно вибрати категорію, в якій є функція, а в другому — саму функцію. Усього в наборі Excel десять категорій функцій, і цей список доповнюється ще одним рядком 10 недавно использовавшихся зі списком функцій, які найчастіше використовувалися останнім часом. Усього вбудованих функцій, що виконують різні типи обчислень, понад 200.

Мал. 7. Діалогове вікно Мастер функций

 

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

Обравши потрібну функцію, клацніть по кнопці ОК, після чого відкриється наступне діалогове вікно Мастера функций (мал. 8), назване палітрою формул. Аргументи можна вводити з клавіатури, але бажано під час введення посилань використовувати мишу, тобто клацнути мишею по відповідних комірках електронної таблиці (адреси вибраних комірок чи діапазонів автоматично запишуться в рядку аргументів).

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

Мал. 8. Діалогове вікно Палитра формул