Лабораторна робота №5. Оформлення окремих видів документів в MS Excel. Оформлення бланків документів в Excel

Лабораторна робота №5

Тема: "Оформлення окремих видів документів в MS Excel. Оформлення бланків документів в Excel "

Завдання 1. На листі 1 оформити "Технологію розрахунків у таблицях"

11. Надати заголовок таблиці за допомогою WordArt.
12. Зробити фільтрацію даних за допомогою меню Дані- Фільтр.
13. Побудувати Діаграму за Назвою товару і Сумою по залишку (на цьому ж листі і назвати її «Діаграма».
14. Показати виконану роботу Викладачу. Приступити до виконання наступного завдання.

Завдання 2. На листі 2 цього ж документу оформити «Успішність студентів і заробітна плата викладачів ВВПК»

Хід роботи

1. Відкрити Новий лист (Лист2) програми MS Excel.
2. Занести дані на першому листі книги до таблиці за зразком (рис. 1) .
3. Ввести додатково ще прізвища 10 своїх одногрупників.

(рис. 1)

4. Перейменуйте Лист 2 книги на Практика.
5. Введіть в стовпці таблиці Бал за 1 семестр,  Бал за 2 семестр, бали (від 1 до 12).
6. Введіть в стовпці таблиці Пропущено за 1 семестр, Пропущено за 2 семестр, к-сть пропущених днів (від 0 до 20).
7. Використовуючи функції (Формули-Інші функції-Статичні-Срзнач) обчисліть середній бал за 2 семестри  та загальну к-сть пропусків за 2 семестри.
8. Використовуючи логічну функцію ЕСЛИ та И (Формули-Логічні), введіть у комірки стовпця Результат умову для порівняння середнього бала та к-сті пропусків кожного учня з прохідним балом та допустимою к-стю пропусків. Результатом порівняння буде текстове повідомлення (ЗАРАХОВАНО або НЕЗАРАХОВАНО). Прохідний бал – 7, а допустима к-сть пропусків – 15.
9. Побудувати діаграму успішності на цьому ж листі за пунктами Прізвище учня, Середній бал за навч. рік, Пропущено за навч. рік.
10. Зробити фільтрацію даних за допомогою меню Дані-Фільтр.
11. Надати заголовок таблиці за допомогою WordArt.
12. Показати виконану роботу Викладачу.
13. Зберегти документ у своїй папці на Сервері, надавши йому ім’я Лабораторна_Excel_5.
14. Закрити програму.
15. Виключити комп'ютер (Пуск – Вихід із системи – Вихід).

Самостійна робота №5 (Домашнє завдання)

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

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

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

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

Січень

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

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

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

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

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

6. Підрахувати Суму до видання, яка розраховується по формулі:
   «Сума до видання=Сума – Прибутковий податок – Пенсійний фонд»
7. Підрахувати Загальну суму (Всього) по полях Сума, Сума до видання і полю Нараховано.
8. Скласти аналогічну відомість для Лютого. Для цього на Лист2 скопіювати таблицю
   Довідник посад, змінити в ній дані по полю Кількість робочих днів: для лютого – 17, та скопіювати таблицю Відомість заробітної плати, де в полі Відпрацьовані дні
   внести зміни згідно Табеля (див. нижче).

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

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

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

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

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

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

Діапазон умов (зробити на листі окрему табличку) -

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

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