Умовне форматування

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

Для встановлення умов форматування необхідно виділити область робочого листа, призначену для форматування, та виконати команду меню Формат/Условное форматирование (рис. 3).

Умов може бути до трьох. Вікно кожної наступної умови відкривається за допомогою кнопки А также. Умови можуть бути двох типів:

- значение – дозволяє задати значення, відносно якого перевіряють комірки, що форматуються;

- формула – дозволяє вказати логічну формулу будь-якої складності.

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

Завдання для лабораторної роботи

1. Запустити програму Microsoft Excel за допомогою ярлика на робочому столі , або ПУСК/Все программы/Microsoft Excel.

2. Зберегти Книгу 1 в особистому каталозі на диску D:. При збереженні дати назву книзі EXCEL_lab_6.xls

Файл/Сохранить/ обрати потрібну папку/ у полі Имя файла ввести EXCEL_lab_6 /Сохранить.

3. Задати першому робочому листу назву Зарплата.

Зробити активним Лист1/Формат/Лист/Переименовать/вводимо з клавіатури назву Зарплата/Enter.

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

Форматування виділених комірок: Формат/Ячейки.

5. Для зменшення помилок при введенні даних встановіть параметри перевірки даних: для діапазону комірок B7:B13 – тільки значення “ч” або “ж”; для діапазону С7:С13 – цілі значення від 0 до 5; для діапазону D7:D13 – числові значення не менше 350 грн.

Для формування списку: у комірку К1 внести значення “ч”, у комірку К2 – “ж”. Виділити діапазон В7:В13, обирати Данные/ Проверка/ вкладинка Параметры/ Тип данных вибирати Список/у полі Источник вибирати $K$1:$K$2. Перейти на вкладинку Сообщение об ошибке/у полі Вид обирати значення Останов/ у полі Заголовок ввести Увага!/ у полі Сообщение ввести Список значень обмежений. Допустимі значення “ч” та “ж”/ ОК.

	A		B	C	D	E	F	G
1	Місяць				3		Премії
2	Мінімальний неоподатковуваний мінімум				17 грн.		До 23 лютого	10%
3	Податок				13%		До 8 березня	500 грн.
4							До Дня митника	400 грн.
5	Розрахунок премії, податків та заробітної плати митників
6	Співробітник	Стать		Діти	Оклад	Премія	Сума податків	Сума на руки
7	Петров В.А.	ч		1	2000 грн.	грн.	грн.	грн.
8	Кислова К.Д.	ж		2	1500 грн.	грн.	грн.	грн.
9	Синиціна А.Д.	ж		0	1700 грн.	грн.	грн.	грн.
10	Марков П.Р.	ч		1	1750 грн.	грн.	грн.	грн.
11	Косов К.Л	ч		1	1650 грн.	грн.	грн.	грн.
12	Волкова Т.П.	ж		2	1700 грн.	грн.	грн.	грн.
13	Федотов К.Н.	ч		0	1450 грн.	грн.	грн.	грн.
14	Всього				грн.	грн.	грн.	грн.
15	Серед них жінок:					Серед всіх співробітників:
16	Загальна кількість					Середні заробітна плата		грн.
17	Кількість з дітьми					Отримують зарплату більше середньої		грн.
18	Загальна сума заробітку			грн.		Перша найбільша зарплата		грн.
19	Середній заробіток			грн.		Друга найбільша зарплата		грн.
20	Максимальна зарплата			грн.		Третя найбільша зарплата		грн.

Виділити діапазон С7:С13, обирати команду меню Данные/Проверка/ на вкладинці Параметры у полі Тип данных вибирати Целое число/ у полі Значение вибирати Между/ у полі Минимум вказати 0/ у полі Максимум вказати 5. Перейти на вкладинку Сообщение для ввода / у полі Заголовок ввести Попередження! у полі Сообщение ввести Припустимі цілі значення від 0 до 5! /на вкладинці Сообщение об ошибке / у полі Вид обрати значення Предупреждение/ у полі Заголовок ввести Увага!/ у полі Сообщение ввести Невірно введені дані!/ ОК.

Виділити діапазон D7:D13, обирати команду Данные/ Проверка / на вкладинці Параметры/ у полі Тип данных вибрати Действительное/ у полі Значение вибрати Больше либо равно/ у полі Минимум вазати 350/ на вкладниці Сообщение для ввода/ у полі Заголовок ввести Увага! / у полі Сообщение ввести Мінімальна заробітна плата становить 350 грн.! /на вкладинці Сообщение об ошибке / у полі Вид обрати значення Сообщение/ у полі Заголовок ввести Увага!/ у полі Сообщение ввести Мінімум –350 грн.!/ ОК.

6. Ввести дані у діапазон комірок B7:D13. Проаналізувати дію засобів по перевірці правильності введення даних. Чим вони відрізняються.

7. Нарахувати святкові премії співробітникам митниці: поточний місяць березень і жінкам встановлюється премія у розмірі 500 грн.

Розрахунок премії: Премія = ЕСЛИ ( стать = жіноча, то 500 грн., інакше – 0 грн.).

У комірку Е7 занести формулу: = ЕСЛИ (В7=”ж”; $G$3; 0). Для цього поставити курсор на комірку Е7 та натиснути кнопку Вставка функции. У вікні майстра обрати функцію ЕСЛИ(). У вікні, що розкриється (рис. 1), у рядок Лог_выражение занести В7=”ж”, у рядок Значение_если_істина занести $G$3, у рядок Значение_если_лож занести 0/ ОК (або Enter).

За допомогою маркеру заповнення скопіювати формулу у діапазон комірок Е8:Е13.

8. Перевірити правильність нарахування премії для жінок у березні місяці. Зминити номер поточного місяця на інший ( у комірку D1 ввести інший номер місяця). Проаналізувати отриманий результат.

9. Змініть формулу так, щоб премія до 8 Березня жінкам нараховувалася тільки у березні місяці.

У формулі необхідно врахувати значення місяця:

Премія = ЕСЛИ ( стать = жіноча ТА місяць = березень, то 500 грн., інакше – 0 грн.).

У формулу у комірці Е7 внести виправлення: =ЕСЛИ( И(В7=”ж”;$D$1=3); $G$3; 0). Для цього поставити курсор на комірку Е7 і натиснути кнопку у рядку формул. У функції ЕСЛИ() для виділеного рядку Лог_выражение зі списку функцій у рядку формул вибирати функцію И(). У рядок Логическое_значение1 заносимо В7=”ж”, у рядок Логическое_значение2 занести $D$1=3 /Ok.

За допомогою маркеру заповнення скопіювати формулу у діапазон комірок Е8:Е13.

10. Перевірити правильність нарахування премії для жінок у березні місяці. Змінити номер поточного місяця на інший ( у комірку D1 ввести номер місяця 3; потім – 4). Проаналізувати отриманий результат.

11. Нарахувати святкові премії співробітникам митниці: у березні жінкам встановлюється премія у розмірі 500 грн., у лютому – чоловікам у розмірі 10% від зарплати.

Премія = ЕСЛИ ( стать = жіноча ТА місяць = березень, то 500 грн.

ЕСЛИ( стать = чоловіча ТА місяць = лютий,

то 10% від зарплати, інакше – 0 грн.)

Нарахування премії у вигляді формули:

E7=ЕСЛИ( И(В7=”ж”;$D$1=3); $G$3; ЕСЛИ( И(В7=”ч”;$D$1=2); $G$2*С7; 0))

Складну формулу можна спростити, поділивши на два додатки:

E7=ЕСЛИ( И(B7=”ж”;$D$1=3); $G$3;0) + ЕСЛИ( И(В7=”ч”;$D$1=2); $G$2*С7; 0))

За допомогою маркеру заповнення скопіювати формулу у діапазон комірок Е8:Е13.

12. Перевірити правильність нарахування премії. Змінити номер на інший у комірку D1 ввести номер місяця 2; потім – 3; потім – 4. Проаналізувати отриманий результат.

13. Нарахувати святкові премії співробітникам митниці: у березні жінкам встановлюється премія у розмірі 500 грн., у лютому – чоловікам у розмірі 10% від зарплати, у червні до Дня митника усім співробітникам нараховується премія у розмірі 400 грн.

Для спрощення формули представимо кожний вид премії окремим додатком:

Премія = ЕСЛИ ( стать = жіноча ТА місяць = березень, то 500 грн., інакше – 0) + ЕСЛИ( стать = чоловіча ТА місяць = лютий, то 10% від зарплати,

інакше – 0 грн.)+ ЕСЛИ(місяць = червень, то 400 грн., інакше – 0 грн.)

Тоді формула виглядає:

E7=ЕСЛИ( И(В7=“ж”;$D$1=3); $G$3;0) + ЕСЛИ( И(В7=“ч”;$D$1=2); $G$2*D7; 0) +

+ЕСЛИ($D$1=6; $G$4; 0)

За допомогою маркеру заповнення скопіювати формулу у діапазон комірок Е8:Е13.

14. Перевірити правильність нарахування премії. Змінити номер на інший (у комірку D1 ввести номер місяця 2; потім – 3; потім – 4. потім – 6. Проаналізувати отриманий результат.

15. Нарахувати суму податків. Сума податків складає 13% від суми окладу та премії за винятком мінімального неоподатковуваного мінімуму (МНМ) на кожну дитину для жінок.

Сума податку = (Оклад + Премія – ЕСЛИ( стать = жіноча, то кількість дітей * МНМ, інакше – 0))*Податок

У комірку F7 занести формулу: = (D7 + E7 – ЕСЛИ( В7 = “ж”; С7*$D$2; 0) )* $D$3. За допомогою маркеру заповнення скопіювати формулу у діапазон комірок F8:F13.

16. Нарахувати суму заробітної плати.

Сума на руки = Оклад + Премія – Сума податку

У комірку G7 занести формулу: = D7 + E7 – F7.

За допомогою маркеру заповнення скопіювати формулу у діапазон комірок G8:13.

17. Підрахувати кількість жінок серед співробітників.

У комірку С16 занести формулу =СЧЁТЕСЛИ (В7:В13; “ж”)

18. Підрахувати кількість жінок із дітьми:

У комірку С17 занести формулу {=СУММ (ЕСЛИ(С7:С13>0; ЕСЛИ (В7:В13=“ж”; 1)))}

Введення формули завершуємо натисканням CTRL+SHIFT+ENTER, при цьому фігурні дужки додаються автоматично.

Розрахунок можна виконати також за формулою: {=СЧЁТ(ЕСЛИ(С7:С13>0; ЕСЛИ (В7:В13=“ж”; 1)))}. Тут всі комірки, що містять у діапазоні В7:В13 букву “ж” та у діапазоні С7:С13 значення більше 0, будуть приймати участь у підрахунках та сумуванні як одиниці.

19. Підрахувати суму заробітної плати у жінок.

У комірку С18 занести формулу =СУММЕСЛИ (В7:В13 =“ж”; G7:G13)

20. Підрахувати середній заробіток серед жінок

У комірку С19 занести формулу ={СРЗНАЧ( ЕСЛИ (В7:В13 =“ж”; G7:G13;0))}

21. Підрахувати найбільший заробіток серед жінок

У комірку С20 занести формулу ={МАКС( ЕСЛИ (В7:В13 =“ж”; G7:G13;0))}

22. Підрахувати середню заробітну плату серед всіх співробітників.

У комірку G16 занести формулу =СРЗНАЧ( G7:G13)

23. Підрахувати кількість співробітників, що отримують заробітну плату вище середньої.

У комірку G17 занести формулу {=СУММ( ЕСЛИ(G7:G13 >= G16; 1;0))}

24. Отримати перші три найбільші суми заробітної плати серед всіх співробітників

У комірку G18 занести формулу =НАИБОЛЬШИЙ( G7:G13; 1)

У комірку G19 занести формулу =НАИБОЛЬШИЙ( G7:G13; 2)

У комірку G20 занести формулу =НАИБОЛЬШИЙ( G7:G13; 3)

25. За допомогою умовного форматування виділити комірки із значенням стать =“ж”.

Виділити комірки В7:В1, обрати команду Формат/Условное форматирование. У полі Условие1 обрати Значение равно “ж”, натиснути кнопку Формат/ вкладинка Шрифт обрати начертание – полужирный, цвет – красный/Ок/Ок.

26. За допомогою умовного форматування виділити прізвища співробітників, що отримують заробітну плату вище середнього.

Виділити комірки А7:А13, обрати команду Формат/Условное форматирование. У полі Условие1 обрати Формула і ввести =G7>=$G$16. Натиснути кнопку Формат, у вкладинці Вид обрати цвет – розовый /Ок/Ок.

27. Зберігти зроблені розрахунки за допомогою команди Файл/ Сохранить.

Завдання для самостійної роботи

На окремому листі Авто_дизель обчислити суму платежів, що сплачується при митному оформленні транспортних засобів, що були придбані за межами України. Сума платежів складається з:

• митного збору за видачу посвідчення на право реєстрації транспортного засобу (15$);

• мита;

• акцизного збору;

• податку на додану вартість (ПДВ).

Основою для нарахування мита та акцизного збору для легкових автомобілів є робочий об’єм двигуна (см³). Ставки мита та акцизного збору для легкових автомобілів із дизельним двигуном надані у таблиці.

Об’єм двигуна, см3	До 1500	З 1501 по 2500	Більше 2500
Акцизний збір, євро	0,2	0,3	0,8
Мито, євро	0,1	0,15	0,8

Розрахунки платежів виконуються за формулами:

Мито = Об’єм двигуна * Ставка мита * Курс євро

Акцизний збір = Об’єм двигуна * Ставка акцизного збору * Курс євро

ПДВ становить 20% від суми митного збору, мита та акцизного збору.

У таблиці 3 наведена структура даних, де комірки, значення яких слід розрахувати за допомогою формул, містять знак „?”.

Для перевірки правильності введення даних для стовпця Дата оформлення задайте умову на можливість введення тільки дат поточного року; для стовпця Об’єм двигуна дозволити введення цілих значень не менше 1000 та не більше 8000; марки автомобілів обирати з раніше сформованого списку (значення у списку задати самостійно).

За допомогою умовного форматування виділити кольором марки автомобілів, об’єм двигуна яких перевищує 2500 см³.

За допомогою умовного форматування виділити дати, які приходились на вихідні (субота, неділя). Примітка: функція ДЕНЬНЕД(дата; 2) повертає номер дня тижня.

Таблиця 3

	A	B	C	D	E	F	G
1	Курс євро		6,27 грн.		Об’єм двигуна, см3		До 1500	З 1501 по 2500	Більше 2500
2	ПДВ		20%		Акцизний збір, євро		0,2	0,3	0,8
3	Митний збір		15$		Мито, євро		0,1	0,15	0,8
4
5	Дата митного оформлення	Марка авто	Об’єм двигуна, см3	Мито	Акцизний збір	ПДВ	Сума платежів
6	1/10/10	Фольксваген	1400	? грн.	? грн.	? грн.	? грн.
7	23/03/10	Фіат	1814	? грн.	? грн.	? грн.	? грн.
8	20/04/10	Тайота	2200	? грн.	? грн.	? грн.	? грн.
9	30/04/10	Мерседес	6800	? грн.	? грн.	? грн.	? грн.
10	10/05/10	Ягуар	3400	? грн.	? грн.	? грн.	? грн.
11	7/06/10	Тайота	1600	? грн.	? грн.	? грн.	? грн.
12	Всього			? грн.	? грн.	? грн.	? грн.
13	В середньому		? грн.	? грн.	? грн.	? грн.	? грн.
14	Кількість авто з об’ємом двигуна:
15		До 1500		?
16		З 1500 по 2500		?
17		Більше 2500		?

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

1. Які типи даних може містити клітинка7

2. Що таке діапазон клітинок? Якими способами можна його виділити?

3. Які операції можна використовувати у формулах?

4. Порядок та пріоритет виконання операцій у формулах.

5. Що таке абсолютна, відносна та змішана адресація (посилання)?

6. Які існують операції відношення?

7. Які існують логічні функції? Їх призначення та формат.

8. Якими арифметичними операціями можна замінити функції И() та ИЛИ() у формулах?

9. Що таке розгалужений алгоритм? За допомогою якої функції реалізується розгалужений алгоритм?

10. Загальний вигляд функції ЕСЛИ(). Порядок виконання функції ЕСЛИ().

11. Що таке вкладені функції? Скільки вкладень може бути у функції ЕСЛИ()?

12. Як викликати будь-яку функцію? Які категорії функцій існують?

13. Чим відрізняється функція МИН() від функції НАЙМЕНШИЙ()?

14. Чим відрізняється функція МАКС() від функції НАЙБОЛЬШИЙ()?

15. Призначена та порядок виконання функції СЧЕТЕСЛИ()?

16. Яка функція може замінити функцію СЧЕТЕСЛИ()?

17. Призначена та порядок виконання функції СЧЕТ()?

18. Що таке масив?

19. Які функції призначені для оброблення масивів?

20. Особливості використання функцій для оброблення масивів?

21. Призначення та порядок виконання автоматичної перевірки правильності введення даних.

22. які можуть бути повідомлення про помилку при введенні невірних даних?

23. Призначення та виконання умовного форматування?

24. Які типи умов можна використовувати при умовному форматуванні?

25. Для чого використовується функція ДЕНЬНЕД()? Особливості її використання?

11