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

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

2. Зберегти Книгу 1 в папці Модуль2 особистого каталогу на диску S:. При збереженні дати назву книзі lab5_прізвище.xls.

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

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

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

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

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

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

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

Виділити діапазон С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. Зберігти зроблені розрахунки за допомогою команди Файл/ Сохранить.

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

1. Завантажити Microsoft Excel 2003.

2. Створити нову книгу та зберегти у особистій папці Модуль2 на диску S: (задача7-№варіанта.xls).

3. Розв’язати індивідуальне завдання згідно варіанту (див. табл. 1). Увага! Оформлення таблиць виконати українською мовою!!!