Тема 15. Електронні таблиці MS Excel: Робота з формулами Ввід формул
Формула в MS Excel являє собою вираз, який містить дані або адреси комірок з даними. При відкритті робочого листа або при внесені нових даних MS Excel обчислює значення цих виразів. Результат обчислення за формулою вміщується в ту комірку, яка її містить.
Запис формули починається знаком =.
Приклади.
1) =2,3*4,5 – за цією формулою MS Excel помножить 2,3 на 4,5 і результат помістить в ту комірку, в якій записана ця формула.
2) =А1/А3 – за цією формулою MS Excel поділить вміст комірки А1 на вміст комірки А3. Результат від ділення буде вміщено в ту комірку, де буде записана ця формула.
3) =$A$1/$A$3 – за цією формулою буде обчислено те саме, що і в випадку 2). Знаки $, які стоять перед іменами стовпців і номерами рядків грають роль лише при копіюванні формул, а при обчисленнях просто опускаються.
Ввід формули можна здійснювати
безпосередньо, помістивши табличний курсор в потрібну комірку і набравши за
допомогою клавіатури знак дорівнює (=), а далі саму формулу. Можна також
скористатись кнопкою Изменить формулу 
, клацнувши на ній мишею. Ввід адрес комірок у формулу
можна здійснювати клацанням мишею на відповідних комірках. Наприклад, для вводу
формули =А1*В1 потрібно клацнути на кнопці Изменить формулу або набрати знак =,
після чого, клацнути на комірці А1, далі, набрати знак *, клацнути на комірці
В1, натиснути клавішу Enter.
дорівнює результату піднесення вмісту комірки А1 до степеня, що дорівнює вмісту комірки С5
MS Excel містить дуже велику кількість вбудованих функцій.[1] Для зручного їх вибору зі списку при вводі, вони поділені на категорії: математичні, фінансові, інженерні, текстові, інформаційні та інші.[2] Детальний опис усіх вбудованих (деколи ще кажуть – стандартних) функцій в рамках даного курсу неможливий по двох причинах: по-перше, функцій дуже багато, а по-друге, опис деяких категорій потребує спеціальних знань. Тому ми розглянемо лише найбільш важливі[3] з них. В той же час, розглянутих функцій цілком достатньо для виконання завдань контрольної роботи і розуміння матеріалу курсу.
В Excel немає вбудованої функції для
запису кореня n-го степеня 
. Для його обчислення потрібно скористатись функцією СТЕПЕНЬ(a;
b), виходячи з того, що .
Функції ctg x i arcctg x обчислюються за формулами:
,
. Приклад. Платіжна відомість
Сформуємо робочий лист для розрахунку заробітної плати фірми. АВС. Дані приведено в таблиці:
Податок, Аванс та На руки розраховуються за формулами: Податок = 0,06*Оклад, Аванс=0,44*Оклад, На руки = Оклад-Податок-Аванс. Таблицю доповнити рядком (Всього), який містить суми по відповідних стовпцях.
Заносимо дані в робочий лист:
Приведемо деякі зауваження щодо заповнення робочого листа.
В комірку В1 заносимо текст Платіжна
відомість. Щоби виділити заголовок напівжирним шрифтом, потрібно, після того,
як текст введено і натиснута клавіша Enter, встановити курсор на комірку В1 і
клацнути мишею на кнопці Полужирный 
.
Порядкові номери співробітників можна записати скориставшись автозаповненням. Для цього в комірці А4 записуємо 1, а в комірці А5 – 2. Після цього, виділивши ці дві комірки, перетягуємо маркер автозаповнення вниз, поки у віконці, що з’являється біля маркера, не буде записано 5.
Щоби в комірці В3 текст Прізвище та ініціали розмістився в два рядки, потрібно встановити курсор на цю комірку і вибрати з головного меню: ФорматÞЯчейки... . В вікні Формат ячеек, яке відкривається вибираємо вкладку Выравнивание:
В фреймі Отображение потрібно поставити позначку у віконці переносить по словам, як показано на малюнку вгорі.
Щоби в стовпці Оклад біля чисел з’явились найменування грошової одиниці, потрібно виділити комірки з числами і вибрати ФорматÞЯчейки... . В вікні Формат ячеек, яке відкривається вибираємо вкладку Число:
В списку Числовые форматы вибираємо Финансовый.
Після того, як дані занесені, заносимо розрахункові формули для першого співробітника: в комірку D4 заносимо формулу =0,06*C4, в комірку E4 – формулу =0,44*C4 і, нарешті, в комірку F4 – формулу =C4-D4-E4.
Виділивши комірки D4, E4, F4, використовуючи автозаповнення, заносимо формули в комірки D5:F8.
Для підрахунку суми окладів співробітників,
виділимо комірки С4:С9, після чого клацаємо мишею на кнопці Автосумма 
. Після цього, в комірці С9 з’явиться сума чисел, які
знаходяться в комірках С4:С8. Якщо замість суми в комірці буде записано 
, то це значить, що число, яке одержано в результаті обчислень,
має більшу кількість знаків ніж може поміститись в комірці. В цьому випадку
потрібно збільшити ширину комірки, перемістивши праву границю заголовка за
допомогою миші на достатню величину.
Виділяємо комірку С9 і за допомогою автозаповнення копіюємо формулу з неї в комірки D9:F9.
В результаті одержимо:
Логічні функції
При проведенні обчислень часто
виникає ситуація, коли наступне обчислення потрібно проводити за однією з двох
формул, в залежності від результатів попередніх обчислень.[4] Тобто, якщо
виконується деяка умова, то обчислюється значення одного виразу, а якщо не
виконується, то іншого. Прикладом такої задачі є обчислення значень функції,
яка задана різними аналітичними виразами (тобто різними формулами) на різних
інтервалах області визначення. Так, для обчислення значення функції 
, що задана формулою
потрібно обчислювати 
за формулою 
, якщо 
, або за формулою 
в противному випадку (тобто, якщо 
).
В MS Excel для такого роду обчислень використовують логічні вирази. Логічний вираз – це вираз, значенням якого є одне з двох логічних значень: ИСТИНА або ЛОЖЬ.[5] Простими прикладами логічних виразів є рівності та нерівності. Самі значення ИСТИНА і ЛОЖЬ також є логічними виразами.
Приклади логічних виразів.
1) 2=3 – логічний вираз, що має значення ЛОЖЬ;
2) 10<100 – логічний вираз, що має значення ИСТИНА;
3) х>y – логічний вираз, значення якого визначається конкретними значеннями x і y. Якщо значення х більше значення у, то значенням цього виразу буде ИСТИНА. В противному випадку значенням логічного виразу буде ЛОЖЬ.
Організація розгалужень здійснюється за допомогою логічної функції ЕСЛИ. Її синтаксис[6]:
ЕСЛИ(ЛВ; В1; В2),
де ЛВ – логічний вираз; В1, В2 – вирази, що обчислюються в двох різних гілках алгоритму.
Значення функції ЕСЛИ обчислюється так[7]:
· обчислюється значення логічного виразу ЛВ;
· якщо логічний вираз ЛВ має значення ИСТИНА, то обчислюється значення виразу В1. Обчислене значення і буде значенням функції ЕСЛИ.
· якщо логічний вираз ЛВ має значення ЛОЖЬ, то обчислюється значення виразу В2. Обчислене значення і буде, в цьому випадку, значенням функції ЕСЛИ.
Не обов’язково обидва вирази повинні бути одного типу. Вираз В1 може мати, наприклад, числовий тип, а вираз В2 –текстовий.
Приклади.
1) Припустимо, що в комірку А4 занесено формулу =ЕСЛИ(А1<3; A2+A3; A2*A3). Тоді, якщо в комірці А1 знаходиться число, яке менше 3, то обчислюється сума чисел, які знаходяться в комірках А2 і А3, і результат вміщується в комірку А4. Якщо ж в комірці А1 знаходиться число, яке більше за 3 або дорівнює 3, то обчислюється добуток чисел, які знаходяться в комірках А2 і А3, і результат вміщується в комірку А4.
Нехай в комірці А2 знаходиться число 2, а в комірці А3 знаходиться число 3. Якщо в комірці А1 знаходиться число 5, то в комірку А4 буде поміщено число 2×3=6. Якщо в комірці А1 знаходиться число 1, то в комірку А4 буде поміщено число 2+3=5.
2) Розглянемо знаходження коренів квадратного рівняння 2х2-7х-11=0. Сформуємо робочий лист, занісши в комірки дані, як зображено нижче:
Примітка. В комірку В1 занесено текст Знаходження коренів квадратного рівняння. На малюнку вгорі видно лише частину цього тексту.
Після занесення даних і формул в робочий лист одержимо результат:
Якщо в комірки В3:В5 занесемо нові значення, формули в робочому листі будуть автоматично перераховані. Так, занісши в комірки В3:В5 значення 1, 2, 6 відповідно, одержимо:
Організація розгалужень, що мають три гілки або більше, можна здійснити також за допомогою функції ЕСЛИ. Розглянемо це на прикладі обчислення значення функції
Нехай значення аргументу, для якого потрібно обчислити значення функції, знаходиться в комірці А1, а значення функції будемо обчислювати в комірці В1.
В комірку В1 заносимо формулу
=ЕСЛИ(А1<0; SIN(A1); ЕСЛИ(А1<=1;ATAN(A1);СТЕПЕНЬ(А1;1/3))
Обчислення за цією формулою буде здійснюватись так:
· Обчислюється значення логічного виразу А1<0.
· Якщо значенням цього виразу є ИСТИНА, то обчислюється значення виразу SIN(A1). Обчислене значення вміщується в комірку B1. На цьому процес обчислення завершується.
· Якщо значенням цього виразу є ЛОЖЬ, (а це значить, що А1³0), то обчислюється значення виразу ЕСЛИ(А1<=1;ATAN(A1);СТЕПЕНЬ(А1;1/3)). Його обчислення здійснюється так:
· Обчислюється значення виразу А1<=1, тобто, перевіряється виконання умови А1<=1.
· Якщо умова виконується[8], тобто значенням виразу є ИСТИНА, то обчислюється ATAN(A1) і знайдене значення вміщується в комірку В1.
· Якщо умова не виконується[9], тобто значенням виразу є ЛОЖЬ, то обчислюється A11/3 і знайдене значення вміщується в комірку В1.
Часто виникає необхідність запису складної умови, яка є істинною лише тоді, коли є істинними логічні вирази, які її складають. Це відповідає вживанню сполучника "і" в звичайній мові. В MS Excel для цієї мети використовують логічну функцію И.
Синтаксис: И(ЛВ1; ЛВ2; ...)
Тут ЛВ1, ЛВ2 і т. д. є логічними виразами. Кількість аргументів, що входять в функцію И, не повинна перевищувати 30.
Семантика: якщо значення всіх аргументів є ИСТИНА, то і функція має значення ИСТИНА. В противному випадку, значення функції є ЛОЖЬ.
Логічну функцію И, оскільки вона являє собою логічний вираз, можна використовувати як умову в функції ЕСЛИ.
Аналогічно, для побудови складної умови, яка є істинною, лише тоді, коли є істинним хоча б один з її аргументів, використовують логічну функцію ИЛИ. Це відповідає вживанню сполучника "або" в звичайній мові.
Синтаксис: ИЛИ(ЛВ1; ЛВ2; ...)
Тут ЛВ1, ЛВ2 і т. д. є логічними виразами. Кількість аргументів, що входять в функцію И, не повинна перевищувати 30.
Семантика: якщо значення хоча б одного з аргументів є ИСТИНА, то і функція має значення ИСТИНА. В противному випадку, значення функції є ЛОЖЬ.
Логічна функція НЕ змінює значення свого аргументу на протилежне.
Синтаксис: НЕ(ЛВ)
Тут ЛВ є логічним виразом.
Семантика: якщо значення логічного
виразу ЛВ є ИСТИНА, то значення функції НЕ є ЛОЖЬ. Якщо значення
логічного виразу ЛВ є ЛОЖЬ, то значення функції НЕ є ИСТИНА.
[1] Нагадаємо, що функція – це закон (правило), за яким одному числу ставиться у відповідність інше число. Функцію можна ототожнити з механізмом, що переробляє одне число в інше. В загальному випадку, функція – це закон, за яким декільком об’єктам ставиться у відповідність деякий об’єкт.
[2] На жаль, поділ функцій на категорії при використанні Майстра функцій і при використанні вбудованого в MS Excel довідника дещо відрізняється.
[3] Важливість – це, звичайно, відносне поняття.
[4] Таке місце в алгоритмі називається розгалуженням, а різні вирази – гілками. В даному випадку мова йде про розгалуження на дві гілки. В загальному випадку можна говорити про розгалуження на декілька гілок.
[5] ИСТИНА і ЛОЖЬ – це дані логічного типу – четвертий тип даних в Excel. Раніше ми говорили про три типи даних: текст, числа і формули.
[6] Синтаксис – це правила запису конструкції. Семантика – це смисл конструкції.
[7] тобто її семантика
[8] Це відповідає виконанню умови 0 £ А1 £1.
[9] Це відповідає умові А1>1.