Поняття формули. Введення формул в комірку

 

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

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

Після знака = ідуть:

· операнд (посилання на комірку);

· функція;

· арифметичний оператор (+, –, /, *);

Оператори порівняння (<, >, <=, >=, =).

Можна обчислювати значення, вводячи в формулу числа. Наприклад: = 12 – 45*(50 – 32)/2.

Але перевага Excel полягає в тому, що в формулу можна вводити адреси комірок, а не їх значення, що забезпечує автоматичний перерахунок за формулою при змінюванні даних. Наприклад, формула = D2 + D4 + D12 знаходить суму значень, розташованих в комірках D2, D4 та D12. Якщо дані в цих комірках зміняться, то Excel перерахує суму із новими значеннями.

Після введення формули натисніть клавішу Enter або кнопку в рядку формул. В активній комірці з‘явиться результат обчислення. Саму формулу буде відображено в рядку формул.

 

2. Введенняфункцій

 

Функція – це стандартна формула для виконування певних задач.

Фактично функції заміняють собою декілька формул.

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

Це зручно, тому що:

· в Еxcel є дуже багато функцій і їх зручно вибирати із загального сховища;

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

 

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

І засіб Через рядок формул

ІІ засіб Через майстер функцій

Майстер функцій можна викликати або кнопкою на панелі інструментів, або через меню Вставка - Функция

 

В полі категорія вибрати тип функції (математична, статистична, тощо, або вивести весь список функцій і знайти потрібну там).

 

 

II. Практична частина.

 

 

Завдання 1. Таблиця «Крамниця «Сільпо».Продаж по кварталах» містить інформацію про кількість проданих мячів в асортименті по кварталах. Розрахувати суммарну кількість проданих мячів по кварталах.

 

Порядок роботи

 

1. Створити таблицю «Крамниця «Сільпо».Продаж по кварталах» за заразком. Робочий аркуш назвіть «Продаж м’ячів».
Параметри форматування повинні відповідати зовнішньому вигляду таблиці і з’ясовуються самостійно
Опишіть в зошиті установлені Вами параметри форматування.

2. Перейдіть до комірки B11.

3. Введіть в неї таку формулу = В5 + В6 + В7 + В8 + В9 + В10.

4. Потім, знаходячись на комірці В11, Автозаповнювачем скопіюйте формулу на діапазон С11: Е11.

5. Проаналізуйте результат і зробіть висновок: як копіюються формули за допомогою Автозаповнювача і завдяки чому отримуємо правильне значення сум по кожному кварталу?

6. Очистить зміст діапазону В11: Е11.

7. Перейдіть до комірки B11.

8. Натисніть кнопку Автосумма на панелі інструментів Стандартная.

9. Проаналізуйте адресу діапазону, який Excel пропонує для сумування.

10. Натисніть Enter.

11. Повторіть процес копіювання формули на діапазон С11:Е11.

12. Поясніть різницю між застосуванням формули із п.3 та функції для знаходження сум.

 

 

A B C D E

Крамниця "Сільпо" Продаж по кварталах
Квартал М'ячі	1 квартал	2 квартал	3 квартал	4 квартал
Футбольні
Баскетбольні
Волейбольні
Теннісні
Гандбольні
Інші
ВСЬОГО

 

Завдання 2. На базі попередноьої таблиці створіть таблицю, яка відібражає кількість продаж по кожному типу м’ячів за рік. Нову таблицю розташуйте на тому ж робочому аркуші нижче за попередню.

Структуру таблиці зробити самостійно.

 

Завдання 3. Створити таблицю табулювання функції z = (x – y)/y, якщо значення х змінюються від 0,1 до 0,5 з кроком 0,1, а у = 2.

Таблицю оформити згідно зразка.

 

Порядок роботи

 

1. Заповніть заголовок таблиці, починаючи з комірки А1.

2. В діапазон А2: А6 ввести відповідний ряд чисе, а в В2 – значення у..

3. В комірку С2 ввести формулу = (А2 – В2)/В2. Ви отримаєте число – 0,95.

4. Скопіюйте формулу в діапазон В3:В6 за допомогою автозаповнювача.
Ви повинні отримати повідомлення #ДЕЛ/0! (ділення на нуль).

5. Передивіться формули в комірках В3:В6 і зробіть висновок – чому.

6. Внесіть зміни в формулу в комірці С2: = (А2 – $В$2)/$В$2, тобто перетворіть посилання В2 з відносної на абсолютну.

7. Повторіть процес копіювання формули.

8. Перевірте правильність обчислень. Значення z повинні бути такими:
-0,95 -0,9 -0,85 -0,8 -0,75

 

 

x	y	Z
0,1
0,2
0,3
0,4
0,5

 

Завдання 4. На другому робочому аркуші створити таблицю Піфагора.

Робочий аркуш назвати «Таблиця Піфагора».

 

Таблиця Піфагора

Завдання розраховано на опанування застосуванням відносних і абсолютних посилань при розрахунках в електронних таблицях Ясно, що в комірку C9 треба ввести формулу = C8*B9 Але при застосуванні Автозаповнювача отримаємо неправильні результати, тому що в кожному стовпці комірки від C8 до K8 по своєму стовпцю мають бути незмінними, тобто абсолютними, а в формулі ми їх ввели як відносні. Тому доцільно дотримуватись такого сценарію при створенні таблиці Піфагора:

1. Заповнити комірки С8: К8 та В9: В17 цифами від 1 до 9. 2. Ввести в комірку С9 таку формулу = $С$8*В9. 3. Виконати операцію копіювання для комірки С9. 4. Виділити діапазн D9: K9.

5. В контекстному меню діапазону виконати Специальная вставка - Формулы - Ok.

6.Замінити в кожній формулі для кожної комірки діапазону літеру C на імя відповідного стовпця. 7. Виділити діапазон С9: К9.

8. В контекстному меню діапазону виконати команду Копировать. 9. Виділити діапазон C10: К17.

10. В контекстному меню діапазону виконати команду Специальная вставка - Формулы - Ok.

 

Контрольні запитання

1. Що таке формула?

2. Що є ознакою того, що в комірку введена формула?

3. Які елементи формули ідуть після знаку =?

4. Чи правильно записана формула (В7 – 7) / 2*В7 +? – 3*С7*С7, якщо значення х та у записані відповідно в комірках В7 і С7, а обчисленню підлягає дріб

 

 

5.. Що таке функція?

6. Засоби введення функцій

7. Опишіть порядок дій для обчислення значення у і розташування його в комірці А5, якщо

 

8. В чому полягає значення відносних посилань в формулах?

9. Як зкопіювати формулу в іншу комірку? В діапазон комірок?

10. Яка роль абсолютних посилань в формулах?

11. Як обчислити суму значень декількох комірок?

 

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

 

Тема: Зв’язування даних. Розрахунки за формулами. Застосування функцій.

 

Мета Показати можливості стоврення таблиць на базі ісходних шляхом зв’язування комірок.

Закріпити знання з використання відносних та абсолютних посилань.

Навчитись використовувати статистичні функції.

 

Завдання. Задана певна кількість робочих днів в місяці, а також свідоцтва про ставки робітників деякого підприємства. Створити табель виходів на роботу працівників та таблицю «Розрахунок зарплатні».

Для обчисления підсумку застосовується формула: " СТ/ДМ*ВД ", де СТ -ставка, ДМ - робочих днів, ВД - відпрацьовані дні.

 

Порядок роботи

 

1. Комірки К2: N2 з’єднуємо. В К2 вводимо днів в місяці, в О2 – кількість робочих днів в місяці.

 

днів в місяці

 

 

1. Дані в цій таблиці вводяться з клавіатури і є базовими для подальшого звязування комірок і розрахунків.

 

 

Ставки
№	ПІБ	Ставка
	Іваненко	5000,00грн.
	Петренко	2500,00грн.
	Сідоренко	3600,00грн.

 

Введення заголовку.

Об’єднати комірки B2..D2.

Шапку таблиці ввести в діапазон B3..D3

Номери за порядком ввести в В4: В6, прізвища - в С4: С6, ставки – в D4: D6,

1. Заголовок Табель ввести в комірку С9, з’єднавши комірки C9: N9.

Шапку таблиці ввести в діапазон В10: О10.
Номери за порядком ввести в В11: В13.
Для введення прізвищ в комірки С11: С13 установимо зв'язок з комірками С4; С6.

Поставити курсор на комірку С11. Ввести формулу = С4. Автозаповнювачем заповнити комірки С12 і С13.

1. Зміст таблиці заповнити згідно зразка. «в» - відпустка, «п» - прогул.
2. В комірку О11 ввести формулу = СЧИТАТЬПУСТОТЫ(D11:N11),
   Функція СЧИТАТЬПУСТОТЫ відноситься до статистичних і підраховує кількість порожніх комірок, не враховуючи нулі. В таблиці вона розраховує кількість відпрацьованого часу. Зкопіювати формулу в комірки О12 та О13.

 

 

ТАБЕЛЬ

№

ПІБ

ВСЬОГО

Іваненко

о

П

Петренко

о

о

О

Сідоренко

о

о

о

О

о

 

1. Заголовок Розрахунок зарплатні вводимо в комірку В17, об’єднавши комірки В17: Е17.
2. Шапку таблиці розташуємо в комірках В19: М19.
3. З’єднуємо комірки D19: F19 і вводимо Ставка; G19: I19 і вводимо пропущено; J19: L19 і вводимо відпрацьовано, M19: O19 і вводимо До сплати.
4. ПІБ працівників отримаємо шляхом зв’язування комірок першої і третьої таблиць: Введемо в комірку С20 формулу =С4 і скопіюємо її в комірки С21 і С22..
   Ставки отримаємо аналогічно, зв’язавши комірки D20: D22 з комірками D4: D6 відповідно.
   Кількість пропущеного часу виражена як різниця робочих днів місяця мінус кількість відпрацьованого часу формулою = $O$2 – J20 для комірки G20.
   Кількість відпрацьованого часу отримаємо шляхом зв’язування з комірками О11: О13 таблиці Табель.
   До сплати розраховуємо згідно формули
   = ставка/ днів в місяці *відпрацьовано днів..
   Для комірки М20 формула має такий вигляд: = D20/$О$2*J20.
   Скопіювати формулу на комрки М21 і М22.
   
   

 

розрахунок зарплатні

№

ПІБ

ставка

Пропущено

відпрацьовано

До сплати

Іваненко

5000,00грн.

4 090,91грн.

Петренко

2500,00грн.

1 818,18грн.

Сідоренко

3600,00грн.

1 963,64грн.

 

 

Контрольні запитання

 

1. Яку роль має комірка О2?
2. Чому в формулах посилання на неї є абсолютним?
3. Яка мета зв’язування комірок в ЕТ?
4. Яким чином відбувається зв’язування комірок?
   Практична робота № 7.

 

Тема: Логічні функції.

Мета Ввести поняття логічної функції.

Навчитись застосовувати логічну функцію ЕСЛИ для обчислень в таблиці і аналізу даних.

Ввести поняття вкладеної функції ЕСЛИ та показати її застосування при розв!язанні задач.

Навчитись застосовувати умовне форматування.

 

Теоретичні відомості

 

Логічна функція Если:

- загальна схема:

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

- призначення:

Повертає одне значення, якщо задана умова при обчисленні дає значення ИСТИНА, й інше, якщо ЛОЖЬ. Тобто дозволяє здійснювати складну перевірку умови.

Логічна функція И:

- загальна схема:

И(логическое_значение1; логическое_значение2;...)

- призначення:

Повертає значення ИСТИНА, якщо всі аргументи мають значення ИСТИНА; повертає значення ЛОЖЬ, якщо хоча б один аргумент має значення ЛОЖЬ. Таким чином дозволяє здійснювати перевірку правильності даних одразу багатьох комірок (об'єднує декілька умов).

 

Логічна функція ИЛИ:

-

- загальна схема:

ИЛИ(логическое_значение1;логическое_значение2;...)

- призначення:

Повертає ИСТИНА, якщо хоча б один з елементів має значення ИСТИНА; повертає ЛОЖЬ, якщо всі аргументи мають значення ЛОЖЬ.

 

Логічна функція Истина:

- загальна схема:

ИСТИНА()

- призначення:

Повертає логічне значення Истина. Функція ИСТИНА призначена для сумісності з іншими системами електронних таблиць.

 

Логічна функція Ложь:

- загальна схема:

ЛОЖЬ()

- призначення:

Повертає логічне значення Ложь. Функція Ложь призначена для сумісності з іншими системами електронних таблиць.

 

Логічна функція НЕ:

- загальна схема

НЕ(логическое_значение)

- призначення:

Логическое_значение - це значення або вираз, які при обчисленні дають ИСТИНА або ЛОЖЬ. Якщо “логическое_значение” має значення ИСТИНА, то функція НЕ повертає значення ЛОЖЬ.

Задача 1.

Скласти таблицю, що видає рекомендаціїї «Схуднути» або «Погладшати» в залежності від відповідності реальної ваги людини її ідеальній вазі. Ідеальна вага для дорослої людини з номальним зростом за однією із методик розраховується як Зріст - 110.

 

Порядок роботи

 

1. Створюємо таблицю згідно зразка в діапазоні А1: Е15.
   
   

№ з/п	Прізвище	Зріст (см)	Вага (кг)	Рекомендації
	Потапов
	Ревякіна
	Сімонова
	Колмогоров
	Кримов
	Сібірцева
	Москвін
	Волгіна
	Українцев
	Татарська
	Голденблюм
	Романов
	Бєлова
	Рижов

 

1. В комірку Е2 вводимо формулу згідно алгоритму:

1) викликаємо майстер функцій і в полі Категорія вибираємо Логічні, а потім ЕСЛИ;

2) в текстове поле Логическое выражение ввести D2<C2-110 (адреси комірок D2 і С2 з метою зменьшення помилок краще ввести за допомогою миші, клацнув на цих комірках).;

3) в текстове поле Значение_если_истина ввести «Погладшати»;

4) в текстове поле Значение_если_ложь ввести «Схуднути».

5) Ok.

1. Зкопіюйте формулу в діапазон Е3: Е15.
2. Проаналізуйте результат. Чи все правильно?
3. Для того, щоб вивести рекомендацію Норма у випадку, коли зріст і ідеальна вага співпадають, зкористуємось так званими вкладеними ЕСЛИ. З цією метою очистіть зміст діапазону Е2: Е15, а потім виконайте алгоритм:
   

1) установіть курсор на комірку Е2;

2) виконайте пункти 1), 2), 3) із попереднього завдання;

3) Перейти до текстового поля Значение_если_ложь (вертикальний курсор повинен бути в цьому полі).

4) Знов викликати через майстер функцій функцію ЕСЛИ (подивиться, як формується формула в рядку формул).

5) В текстове поле Логическое выражение ввести D2>C2-110;

6) В текстове поле Значение_если_истина ввести «Схуднути»;

7) В поле Значение_если_ложь ввести «Норма».

8) OK.

1. Установіть Умовне форматування для стовпця Рекомендації.

Для цього виконайте такі дії:

1) виділіть діапазон Е2: Е15;

2) меню Формат - Условное форматирование;

3) в полі Условие 1 установити Значение;

4) далі в полях відповідно Равно, «Схуднути»;

5) кнопка Формат, колір червоний, шрифт – напівжирний, Ok:

6) кнопка А также >>

7) повторити пункти 3, 4, 5 з новим значенням рекомендацій «Погладшати» та кольором – зелений;

8) кнопка А также >>

9) повторити пункти 3, 4, 5 із значенням рекомендаций «Норма» та кольором – синій.

 

 

Задача 2.

Микола та Сашко мають прізвища Шилов та Цвях. Яке прізвище має кожен з них, якщо Сашко із Шиловим живуть в різних будинках?

 

Аналізуючи умову задачі, можна зробити висновок, що прізвище Сашка- Цвях, а Миколи – Шилов.

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

А В

	Ім’я	Прізвище
	Микола	Шилов
	Сашко	Цвях

 

Для цього в комірки В2 і В3 треба ввести відповідні формули.

Спочатку складемо алгоритм, який реалізує поставлену задачу – введення в комірку В2 потрібного прівища.

 

ЕСЛИ А2 = "Микола"
ТО В2 = "Шилов"
ИНАЧЕ ЕСЛИ А2 = "Сашко"
ТО В2 = " Цвях"
ИНАЧЕ В2 = "Невірне ім'я"
ВСЕ
ВСЕ

 

Формула, що вводиться в комірку В2 має вигляд:

 

= ЕСЛИ(А2="Микола";"Шилов";ЕСЛИ(А2="Сашко";"Цвях";"Невірне імя"))