Практична робота MS Excel "Технологія застосування функцій у таблицях. Операції редагування таблиць. Фільтрація даних."

Комплексна практична робота

Тема: Технологія застосування функцій у таблицях. Операції редагування таблиць. Фільтрація даних.

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

Хід роботи

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

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

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

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

Січень

3. Зробити нарахування заробітної плати за відпрацьовану кількість днів по формулі:

«Нараховано = (Оклад/Загальна кіл-ть днів)*Відпрац. дні»

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

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

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

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

6. Підрахувати До видачі, по формулі:

«до видачі=Сума - Прибутковий податок - Пенсійний фонд»

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

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

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

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

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

11. Підрахувати    Загальну суму    коштів,    отриманих    кожним    робітником    за    квартал.     Сума     дорівнює   сумі   коштів,   отриманих   за   кожний   місяць    (    у    формулі    для    обчислення    суми    повинні   бути   посилання   на   комірки   Листа1,    Листа2,    Листа3,    де    знаходяться    суми,    які    були отримані за кожен місяць).
12. Зробити   Автофильтр   (автоматичний   фільтр)   в   таблиці   Відомість    заробітної    плати    за березень. Показати всіх робітників, які отримали суму коштів > 15000 (пункт меню Данние/Фильтр/Автофильтр).
13. Зняти автофільтр.
14. Побудувати    Розширений    фільтр    (пункт    меню    Данние/Фильтр/Розширенний     фильтр).  Основною    базою    є    таблиця    Відомість    заробітної    плати     за     березень.     Спочатку     результат   фільтрації   слід   розмістити   на   місці   базової   таблиці   (у   діалоговому   вікні    Розширеного   фільтру   включити   перемикач   Фильтровать   список    на    месте).    У    вікні    Диапазон условий вказати діапазон комірок, де знаходиться наступна таблиця:

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

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

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

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