Як працювати з формулами в MS Excel?

Формула в клітинці таблиці  MS Excel обов’язково починається зі знака “=”. Якщо про цей знак забути, то введене буде сприйнято як звичайний текст. Зрозуміло, що така формула працювати не буде.

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

Основні правила для побудови формул такі:

q формула записується тільки в рядок;

q аргументи функцій обов’язково беруться в дужки;

q дужки у формулах можуть бути тільки круглими;

q аргументи функції відокремлюються один від другого комою (якщо було виконано відповідну настройку);

q знак множення у формулах пропускати не можна;

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

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

Приорітет	Знак операції	Найменування операції
1 (найвищий)	«-»	унарний мінус
	«^»	піднесення до степеня
	«*», «/»	множення і ділення
	«+», «-»	додавання і віднімання

Основні повідомлення про помилки у формулах такі:

q #ДЕЛ/0! – ділення на нуль. Таке повідомлення виникає в тих випадках, коли значення дільника операції ділення виявляється рівним нулю.

q «Обнаружены ошибки во введённой формуле». Таке повідомлення бачимо, наприклад, при введенні помилкової формули «=10+*2», в якій указано дві операції підряд. При цьому пропонується варіант виправлення формули. Якщо ви погоджуєтесь із запропонованим, то натискуєте кнопку Да. Якщо ви натискуєте кнопку Нет, то це означає, що виправлення ви збираєтесь внести самостійно.  

q «Несоответствие скобок». Таке повідомлення виникає в тих випадках, коли у введеній формулі порушено баланс між відкриваючими та закриваючими дужками. Наприклад: «=(2+3*4».

q #ЗНАЧ! – помилка значення, що входить до складу формули. Виникає, наприклад, у випадку «=SIN("кут")», де для функції указано аргумент не числового, а текстового типу.

q #ИМЯ? – помилка в імені. Виникає, наприклад, при обчисленні виразу , якщо застосувати неправильне найменування функції «=КОРЕНЬ(tg(1)+1)». Правильне рішення: «=КОРЕНЬ(TAN(1)+1)».

q #ЧИСЛО! – помилка в аргументі функції. Наприклад, аргумент функції у формулі «=КОРЕНЬ(-5)» – від’ємний.

q #Н/Д – стандартне помилкове значення, яке означає НеДоступно. Правило тут таке: якщо хоча б один аргумент будь-якої функції дорівнює #Н/Д, то і значення цієї функції теж дорівнює #Н/Д. Наприклад, «=COS(#Н/Д)».

2. Використання функцій

До складу MS Excel входить надзвичайно велика кількість різноманітних стандартних функцій. Для ознайомлення з ними можна скористуватись спеціальним засобом, який називається Мастер функций. Основні стандартні елементарні математичні функції представлено в таблиці.

Математичний запис	Запис в Excel
Sin x	SIN(x)
Cos x	COS(x)
Tg x	TAN(x)
Arcsin x	ASIN(x)
Arccos x	ACOS(x)
Arctg x	ATAN(x)
Ln x	LN(x)
Lg x	LOG10(x)
Logax	LOG(x, a)
ex	EXP(x)
|x|	ABS(x)
	КОРЕНЬ(x)
	ПИ()
	x^3
	x^(1/3)
Заокруглення числа x до найближчого меншого цілого	ЦЕЛОЕ(x)
Обчислення остачі від ділення націло числа N на число D	ОСТАТ(N, D)
Заокруглення числа x до заданого числа розрядів k	ОКРУГЛ(x, k)

Зауваження 1. В наведених тригонометричних функціях використовується радіан як одиниця вимірювання величин кутів.

Зауваження 2. Функції ЦЕЛОЕ(x) і ОСТАТ(N,D) утворюють загальне правило обчислення частки і остачі від ділення націло числа N на число D: N=D*ЦЕЛОЕ(N/D)+ОСТАТ(N,D).

Зауваження 3. Функція ОКРУГЛ(x, k) виконує заокруглення за звичайним арифметичним правилом заокруглення (якщо цифра, яка відкидається, менша 5, то попередня цифра залишається без змін, у протилежному випадку попередня цифра збільшується на одиницю). Якщо k – додатне, то число x заокруглюється до указаної кількості десяткових розрядів справа від десяткової крапки. Якщо k=0, то число x заокруглюється до найближчого цілого. Якщо k – від’ємне, то число x заокруглюється до указаної кількості десяткових розрядів зліва від десяткової крапки.

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

Приклади використання деяких функцій:

q Формула «=ЦЕЛОЕ(5.7)» дає результат 5, а формула «=ЦЕЛОЕ(-5.7)» дає результат 6.

q Формула «=ЦЕЛОЕ(23/3)» дає частку від ділення націло числа 23 на число 3, тобто 7.

q Формула «=ОСТАТ(23,3)» дає результат 2.

q Якщо x=143.3184, то формула «=ОКРУГЛ(x,2)» дає результат 143.32.

q Якщо x=143.3184, то формула «=ОКРУГЛ(x,0)» дає результат 143.

q Якщо x=143.3184, то формула «=ОКРУГЛ(x,-1)» дає результат 140.

Тут розглянуто приклади використання лише деяких функцій. Описи решти функцій слід шукати в області задач Справка Excel, яка з’являється після натискування клавіши <F1>. В цій області задач треба натиснути на пункт Оглавление і після його появи знайти в ньому розділ Справка по функциям.

Крім арифметичних виразів, важливим компонентом формул є логічні вирази, зокрема, логічні функції. Логічний вираз – це є спільна назва для висловлювання та предиката. Висловлюванням називається твердження, відносно якого відразу можна зробити висновок, вірне воно чи ні. Наприклад, значенням висловлювання «7>5» буде ИСТИНА. Значенням висловлювання «3>5» буде ЛОЖЬ. Висловлювання, яке містить змінні величина, називається предикатом. В залежності від значень змінних предикат може набувати значення ИСТИНА або ЛОЖЬ. Наприклад, результатом порівняння «x>3» буде ЛОЖЬ при x=2 і ИСТИНА – при x=6. Предикат утворюється внаслідок порівняння двох арифметичних виразів, з яких хоча б один містить змінні.

У логічних виразах можуть використовуватись такі операції порівняння: «>» – більше, «>=» – більше або дорівнює, «<» – менше, «<=» – менше або дорівнює, «=» – дорівнює, «<>» – не дорівнює. Треба пам’ятати, що операції порівняння мають нижчий пріоритет, ніж арифметичні операції.

У логічних виразах можуть використовуватись також логічні операції, реалізовані у вигляді логічних функцій: НЕ(x) – заперечення, И(x,y) – логічне множення, ИЛИ(x,y) – логічне додавання.

В арифметичних виразах логічне значення ИСТИНА поводить себе як число 1, а ЛОЖЬ – як число 0. І навпаки – в логічних виразах число 1 поводить себе як ИСТИНА, а число 0 – як ЛОЖЬ. Більше того, замість ИСТИНА можна указувати будь-яке число, відмінне від нуля.

Логічні вирази найчастіше застосовуються як перший аргумент логічної функції ЕСЛИ:

ЕСЛИ(лог_выражение, значение_если_истина, значение_если_ложь).

Ця функція має три аргументи, зміст яких такий: якщо лог_выражение дорівнює ИСТИНА, то значення функції обчислюється як значення другого аргументузначение_если_истина, а якщо лог_выражение дорівнює ЛОЖЬ, то значення функції обчислюється як значення третього аргументу значение_если_ложь. Особливість функції ЕСЛИ полягає в тому, що її тип наперед не визначений і співпадає з типом або другого, або третього свого аргумента.

Приклад 1. Задано число z. Побудувати формулу, яка дає результат z+1, якщо z>1, і дає результат z-1 у протилежному випадку.

Розв’язок: «=ЕСЛИ(z>1,z+1,z-1)».

Приклад 2. Задано число z. Побудувати формулу, результатом якої є повідомлення «Перевищено порогове значення», якщо z>100, і яка дає результат z у протилежному випадку.

Розв’язок: «=ЕСЛИ(z>100, "Перевищено порогове значення",z)».

Приклад 3. Задано число z. Побудувати формулу, яка дає результат z/2, якщо , дає результат 10, якщо z<10, і дає результат 25, якщо z>25.

Розв’язок: «=ЕСЛИ(z<10,10,ЕСЛИ(z>25,25,z/2))».

3. Введення та копіювання формул

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

При використанні рядка формул відомі наступні способи введення формул:

q безпосередній,

q за схемою автоматичного отримання адрес,

q за допомогою майстра функцій.

В процесі введення формули є доступними всі клавіши редагування для виправлення допущених помилок. Якщо надруковане не влаштовує повністю, то можна натиснути кнопку Отмена (червоний хрестик у рядку формул) або клавішу <Esc>. Якщо процес друкування закінчено, то слід натиснути кнопку Ввод (зелена позначка у рядку формул) або клавішу <Enter>.

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

При введення формули за допомогою Мастера функций роботу починаємо з натискування кнопки Вставка функции рядка формул. Внаслідок цього отримуємо в рядку формул знак “=” і текстовий курсор, а також діалогове вікно Мастер функций – шаг 1 из 2. Указане вікно містить повний перелік функцій, із яких слід вибрати потрібну. Тепер виникає діалогове вікно Аргументы функции, в якому поле Число призначене для вказівки аргументів вибраної функції.

Відзначимо, що діалогове вікно Мастер функций – шаг 1 из 2 містить функції, перелік яких розбито на категорії. Перелік категорій міститься у підвікні Категория:. Перелік функцій, що входять до складу відміченої категорії, представлений у другому підвікні Выберите функцию:. Відмітивши потрібну функцію у цьому підвікні, нижче у вікні можна побачити стислий опис цієї функціїї. Для отримання детальної довідки (повний опис, математичне обгрунтування, приклад застосування) треба натиснути пункт Справка по этой функции в нижній частині вікна. Внаслідок цього буде отримано область задач Справка Excel з детальною інформацією.

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

Як і будь-які інші дані, формули в комірках ЕТ можна копіювати. При цьому вони автоматично настроюються на нове місце свого розташування. В багатьох випадках копіювання формули можна зробити шляхом автозаповнення. Для цього треба виділити комірку з формулою і сумістити вказівник мишки з правим нижнім кутком селектора. При цьому вказівник мишки має набути форми маленького чорного хрестика. Утримуючи ліву кнопку мишки у натиснутому стані, протягуємо вказівник мишки у потрібному напрямі на потрібну кількість комірок. Після цього кнопку мишки відпускаємо.