4

Практична робота № 10

1. Тема: Створення формул (у Excel. Використання функцій для створення об’ємних формул)

2. Мета роботи : навчитись працювати з формулами й функціями

3. Обладнання: ПК, методичні вказівки до виконання роботи

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

	D	E	У зображеній тут формулі обчислюється розмір заробітної платні. Оплата за годину роботи (значення комірки E2) множиться на число відпрацьованих годин (значення комірки Е5)
1	Фотограф	Углова
2	Годинна ставка	4 ,50 грн.
3
4	Годинники	5
5	Усього	$E$2*5

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

Для введення формули (в активній комірці) у рядку формул потрібно спочатку ввести за допомогою клавіатури = (знак рівності). Можна натиснути кнопку Змінити формулу (=), що знаходиться лівіше поля введення в рядку формул (її немає в Excel 2002), або кнопку Вставити функцію – при цьому автоматично вставляється знак рівності. Потім потрібно ввести основну, функціональну частину формули, натиснути клавішу Enter (або кнопку із зеленим прапорцем). Зверніть увагу, що курсор у вигляді вертикальної лінії, що визначає крапку Введення, перебуває в рядку формул! Для введення формули безпосередньо в комірку потрібно двічі клацнути всередині комірки ( або клацнути один раз і натиснути F2). Зверніть увагу, що курсор у вигляді вертикальної лінії, що визначає Місце ВВЕДЕННЯ, перебуває безпосередньо в комірці! Існують способи прискорення процесу введення й зменшення ймовірності помилок:

Ту саму формулу можна ввести відразу в кілька комірок: для цього необхідно виділити комірку, ввести формулу, а потім натиснути клавіші Ctrl+Enter.

Створення посилань на комірки: Пряме введення адреси комірки із клавіатури

1. Введення посилань на комірки за допомогою миші (потрібно після введення знака «=» просто клацнути комірку, адресу якої потрібно ввести, наприклад А1. Ввести із клавіатури знак «+», потім клацнути комірку А2 і завершити введення формули, натиснувши Enter)

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

При цьому, якщо комірка перебуває на іншому аркуші робочої книги, то адреса буде містити також ім'я робочого аркуша, а при необхідності - також й ім'я робочої книги (іншої), де перебуває комірка. Наприклад, якщо в комірку В2 Книги_1 потрібно вставити формулу, у якій повинні складатися числа з комірок В1 й В2 робочого аркушу Аркуш3 робочоі книги Книга2, де введена формула буде мати вигляд: =[Книга 2]Аркуш3! $В$1+[Книга2]Аркуш3!$В$2.

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

Щоб почати набір формули з функції, натисніть кнопку Змінити формулу (=) у рядку формул¹. Зверніть увагу, що при переході в режим введення формули, у лівій частині рядка формул замість поля з адресою (посиланням) активної комірки з'являється список, що розкривається, з вбудованими функціями Excel (або можна скористатися кнопкою Вставка функції ( fx ) на панелі інструментів Стандартна. Після вставки функції в панелі формул відображається ім'я функції і її аргументи, опис функції й аргументів. Введіть аргументи². По завершенню набору формули натисніть клавішу Enter.

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

для встановлення режиму відображення формул на робочому аркуші: меню Сервіс – Параметри - вкладка Вид - встановіть прапорець Формули, а для відображення результатів обчислень - зніміть цей прапорець.

щоб перейти в режим редагування формули, треба виділити комірку, що містить формулу, і натиснути F2, або клацнути в рядку формул, або зробити подвійний щиглик в комірці. Для заміни формули її значенням виділіть діапазон, в комірках якого потрібно замінити формули їхніми значеннями – меню Виправлення – Копіювати – виділіть комірку або вихідний діапазон ще раз – меню Виправлення – Спеціальна вставка – встановіть перемикач Вставити – встановити прапорець Значення +ОК.

При переміщенні формули (вирізати - вставити) посилання, розташовані усередині формули, не змінюються. При копіюванні формули абсолютні посилання не змінюються, а відносні посилання змінюються.

Синтаксис формули: формули в Excel підкоряються певному синтаксису, у який входить знак рівності (=), елементи що обчислюються (операнды) і оператори. Операндами можуть бути: константи, посилання або діапазони посилань, заголовки, імена або функції. Синтаксисом формул називається порядок, у якому обчислюються її значення. В Microsoft Excel включено чотири види операторів: арифметичні, текстові, а також оператори порівняння й адресні оператори.

Арифметичні оператори	Значення	Приклад
+ (знак плюс)	Додавання	3+3
– (знак мінус)	Віднімання	3–1
	Унарный мінус	–1
* (зірочка)	Множення	3*3
/ (коса риса)	Ділення	3/3
% (знак відсотка)	Процент	20%
^ (кришка)	Зведення в ступінь	3^2 (аналогічно 3*3)

Оператори порівняння використовуються для позначення операцій порівняння двох чисел. Результатом виконання операції порівняння є логічне значення ІСТИНА або ЛОЖЬ:

Оператори порівняння		Значення	Приклад
= (знак рівності)		Дорівнює	A1=B1
> (знак більше)		Більше	A1>B1
< (знак менше)		Менше	A1<B1
>= (знак більше й знак рівності)		Більше або дорівнює	A1>=B1
<= (знак менше й знак рівності)		Менше або дорівнює	A1<=B1
<> (знак більше й знак менше)		Не дорівнює	A1<>B1
Текстовий оператор & (амперсант)	Конкаденція. Для позначення операції об'єднання послідовностей символів у єдину послідовність.		Результатом виконання вираження "Північний" & " вітер" буде: "Північний вітер"

Адресні оператори поєднують діапазони осередків для здійснення обчислень:

Адресний оператор	Значення	Приклад
: (двокрапка)	Використовується як роздільник при завданні прямокутного діапазону	B5:B15
, (кома в англ. версії) ;(крапка з комою в російській версії)	Оператор, що використовується як роздільник при завданні діапазонів комірок (аргументів функції).	СУММ(B5:B15;D5:D15)
(пробіл)	Оператор перетинання діапазонів, що посилається на загальні комірки діапазонів.	СУМ(B1:B4 A2:В4) – в осередку В5 обчислює суму чисел, що знаходяться в комірках діапазону В2:В4 (перетинання діапазонів B1:B4 й A2:В4).

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

Оператори (розташовані по пріоритету операцій Excel)			Опис
: (двокрапка) (пробіл) ; (крапка з комою)		Оператори посилань
–		Унарный мінус (наприклад, –1)
%		Відсоток
^		Зведення в ступінь
* й /		Множення й ділення
+ й –		Додавання й віднімання
&	Об'єднання послідовностей символів в одну послідовність (див. ЗЧЕПИТИ)
= , < , > , <= , >=, <>		Оператори порівняння

Перетворення операндов і аргументів формул: Операнды кожного оператора у формулі, повинні бути певного типу. Якщо операнд не відповідає операторові, виконується, якщо можливо, перетворення типу операнда. Таким чином, вдається уникнути появи значення помилки.

Формула		Результат			Пояснення
="1"+"2"		3	При використання знака плюс (+), передбачається, що операнды є числами. Навіть якщо взяти числа в лапки (віражения "1" й "2" стануть текстовими значеннями), Microsoft Excel автоматично перетворить їх у числа
=1+"$4.00"		5			Коли операнды повинні бути числами, текстові значення перетворяться, якщо це можливо, у числа
="06.01.92"-"05.01.92"		31			Microsoft Excel інтерпретує текст як дати, записані у форматі «dd.mm.yy», а потім перетворить їх у числа для обчислення кількості днів між цими двома датами
=КОРІНЬ ("8+1")	#ЗНАЧ!			Текстове значення "8+1" не перетвориться в число. Для правильного виконання формули необхідно вказати наступні варіанти: "9"або "8"+"1";тоді формула виконає перетворення тексту в число й поверне як результат 9.
="A"& ИСТИНА	ІСТИНА			Коли операнды повинні бути текстовими значеннями, виконується перетворення чисел і логічних значень (ІСТИНА й НЕПРАВДА) у текст.