Лабораторна робота №6, Операції редагування таблиць. Фільтрація даних в MS Excel

« Назад

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

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

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

Функція ПРОСМОТР:

Шаг 1. Застосувати Майстер функцій (кнопка f_x)

Шаг 2.

Шаг 3.

(Не забудьте натиснути F4, щоб надпис мав вигляд: 'Довідник посад'!B3:$F$3)

Шаг 4.

Після створення формули застосувати авто заповнення.

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

Напр., =(D5/'Довідник посад'!$B$8)*Січень!C5

5. Премія дорівнює 20% від нарахованих грошей. Напр., =20%*E5

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

7. Підрахувати Прибутковий податок, який розраховується з наступних умов:

Якщо Сума < 1500, то прибутковий податок дорівнює 0.

Якщо Сума >= 1500, то прибутковий податок дорівнює 20% від Суми.

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

9. Підрахувати Суму до видавання, яка розраховується по формулі:

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

10. Підрахувати загальну суму по полю Сума до видавання (рядок Всього).

11. Скласти аналогічну відомісті для лютого та березня:

- Скопіювати лист Січень.

- Перейменувати лист в Лютий (Березень).

- Внести зміни в Кількості відпрацьованих днів.

Лютий			Березень
Прізвища	Відпрац. дні		Прізвища	Відпрац. дні
Іващенко	17		Іващенко	20
Сидорук	16		Сидорук	16
Коваленко	17		Коваленко	21
Гаврилов	17		Гаврилов	17
Денисенко	16		Денисенко	19
Петренко	14		Петренко	18
Давидов	17		Давидов	17
Карпенко	10		Карпенко	21
Симоненко	12		Симоненко	20
Ваше прізвище	17		Ваше прізвище	19

- Внести зміни в формулу стовпчика «Нараховано» (Зміниться Кол-во роб. днів).

- Прослідкуйте за змінами, які відбуваються автоматично при переобчисленні формул.

Сортування та фільтрація

1. На Листі Січень виконати сортування по полю Посада (по спаданню), а потім по полю Сума до видання (за зростанням).

- Курсор в будь-якому місці заголовку таблиці (рядок 4)

- Застосувати меню Данные-Сортировка.

- Вибрати відповідні поля та види сортування (спадання/зростання).

2. На Листі Лютий виконати відбір записів, які відповідають наступним умовам:

Кількість відпрацьованих днів – 17 та Сума до видання > 2000 грн.

- Курсор в будь-якому місці заголовку таблиці (рядок 4)

- Застосувати меню Данные-Фильтр-Автофільтр.

Для знаходження більше або менше використовується Пользовательский автофильтр (у випадаючому списку біль назви стовпчика вибрати пункт Условие).

3. На Листі Березень виконати відбір записів, які відповідають наступним умовам:

- Прізвище має літеру «е» та не має літеру «л»

- Посада почитається на літеру «ю» або «і»

- Сума до видання >=1600 та <=1700.

Для всіх стовпчиків з умовою (Прізвище, Посада, Сума до видання) застосувати Пользовательский автофильтр.