Задача "Відомість заробітної плати"

Мета: Познайомитися з загальними правилами редагування таблиць. Отримати навички роботи з логічною функцією ЕСЛИ та з функцією ПРОСМОТР за допомогою майстра функцій. Використання складних функцій. Навчитися використовувати автофільтр та розширений фільтр.

Хід роботи

1.  Оформити Довідник посад, що містить оклади. Загальна кількість робітничих днів є константою для кожного місяця.

Довідник посад.

Довідник посад	Бухгалтер	Інженер	Начальник	Програміст
Оклад	180	140	240	220

К-сть роб. днів

19

2. На тому ж самому листі оформити Відомість заробітної плати по відділу за січень.

Відомість заробітної плати

Січень

Прізвища	Посада	Відпрац. дні	Начислено	Премія	Сума	Прибутковий податок	Пенсійний фонд	Сума до видання
Іващенко	Начальник	19
Сидорук	Бухгалтер	18
Коваленко	Програміст	17
Гаврилов	Програміст	19
Денисенко	Інженер	16
Петренко	Інженер	10
Давидов	Інженер	19
Карпенко	Інженер	10
Симоненко	Інженер	18
Всього:

3.Підрахувати суму налічених грошей за відпрацьовану кількість днів по формулі:
«Налічене = (Оклад/Загальна кіл-ть днів)*Відпрац. дні»

•         Оклад слідує одержувати з таблиці Довідник посад, застосувавши функцію
ПРОСМОТР, де розшукуване значення - це посада кожного з робітників, а масив – це діапазон чарунок, в яких розміщена таблиця Довідник посад (слідкуйте за однаковістю в назві текстових полів).

•         Премія дорівнює 20% від налічених грошей.

•         Загальна сума грошей обчислюється по формулі: «Сума=Налічене+Премія»

4.Підрахувати Прибутковий податок, який розраховується з наступних умов:
Якщо Сума < 100, то прибутковий податок дорівнює 0.

Якщо Сума < 200, то прибутковий податок дорівнює 10% від Суми. Якщо Сума >=200, то прибутковий податок дорівнює 20% від Суми. (Використати вкладені функції ЕСЛИ).

5.Підрахувати Пенсійний фонд, який дорівнює 2% від Суми.

6.     Підрахувати Суму до видавання, яка розраховується по формулі:
«Сума до видавання=Сума - Прибутковий податок - Пенсійний фонд»

7.     Підрахувати загальну суму по полю Сума до видавання.

8.     Скласти аналогічну відомість для лютого. Для цього на Лист2 скопіювати таблицю
Довідник посад, змінити в ній дані по полю Кількість відпрацьованих днів: для лютого
- 17, та скопіювати таблицю Відомість заробітної плати, де в полі Відпрацьовані дні
внести зміни згідно табеля. Простежити за змінами, які відбуваються автоматично при
переобчисленні формул.

Табель Відпрацьованих днів за місяць лютий:

Прізвища	Відпрац. дні	Прізвища	Відпрац. дні
Іващенко	15	Петренко	14
Сидорук	16	Давидов	17
Коваленко	17	Карпенко	10
Гаврилов	17	Симоненко	12
Денисенко	16

9.  Скласти на ЛистіЗ аналогічну відомість для березня. Кількість відпрацьованих днів: для березня — 21. Табель Відпрацьованих днів за місяць березень:

Прізвища	Відпрац. дні	Прізвища	Відпрац. дні
Іващенко	20	Петренко	18
Сидорук	16	Давидов	17
Коваленко	21	Карпенко	21
Гаврилов	17	Симоненко	20
Денисенко	19

10. Оформити на Листі4 загальну відомість за квартал. Загальна відомість за квартал

Прізвища	Посада	Сума	Прізвища	Посада	Сума
Іващенко	Начальник	Петренко	Інженер
Сидорук	Бухгалтер	Давидов	Інженер
Коваленко	Програміст	Карпенко	Інженер
Гаврилов	Програміст	Симоненко	Інженер
Денисенко	Інженер

11.Підрахувати суму грошей, отриманих кожним робітником за квартал. Сума дорівнює
сумі грошей, отриманих за кожний місяць ( у формулі для обчислення суми повинні
бути посилання на чарунки Листа1, Листа2, Листа3, де знаходяться суми, які були
отримані за кожен місяць).

12.Зробити Автофильтр (автоматичний фільтр) в таблиці Відомість заробітної плати за
березень. Показати всіх робітників, які отримали суму грошей > 150 (пункт меню
Данние/Фильтр/Автофильтр).

13.Зняти автофільтр.

14.Побудувати Розширений фільтр (пункт меню Данние/Фильтр/Розширенний фильтр).
Основною базою є таблиця Відомість заробітної плати за березень. Спочатку
результат фільтрації слід розмістити на місці базової таблиці (у діалоговому вікні
Розширеного фільтру включити перемикач Фильтровать список на месте). У вікні
Диапазон условий вказати діапазон чарунок, де знаходиться наступна таблиця:

Діапазон умов -

Посада	Відпрац. дні
Інженер	>=18

15. Побудувати аналогічний Розширений фільтр по тій же самій умові фільтрації, тільки результат фільтрації слід розмістити на вільному місці, тобто на чарунках, де знаходиться цільова область. Для цього у діалоговому вікні Розширеного фільтру треба включити перемикач Скопировать результат в другое место. У вікні Поместить результат в диапазон вказати діапазон чарунок, де знаходиться наступна таблиця:

Цільова область -

Прізвища

Начислено

Сума до видання

16. Зберегти документ у папці «Група <номер групи>», надавши йому ім’я Компл_Практична_Excel_10.

17. Показати виконану роботу Викладачу.

18. Закрити програму. Виключити комп'ютер.

( ВВПК ім А.Ю.Кримського             викладач: Ващук Б.В.)