Практична робота № 6. Фільтрація даних і обчислення підсумкових характеристик

Виконав учень (учениця) _______________________________________(Прізвище, ім’я )

Робота збережена на комп’ютері № ____  на диску ____  у папці____________________

Практична робота № 6. Фільтрація даних і обчислення  підсумкових характеристик

Мета: навчитися фільтрувати дані і обчислювати підсумкові характеристики.

Теоретичні відомості

Ø    Проміжні підсумки — це узагальнюючі значення (суми, середнього, кількості тощо), які обчислюються для груп представлених у певній таблиці об'єктів, а також засіб для обчислення цих значень. Проміжні підсумки обчислюють лише для таблиць, впорядкованих за значеннями певного параметра.

Ø    Зведена таблиця — це засіб Місrоsоft Excel, який дає змогу обчислю­вати підсумкові характеристики та водночас фільтрувати дані, розмі­щені в певному діапазоні.

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

Ø    В область Сторінка перетягують назви тих стовпців, за якими відбу­ватиметься фільтрація.

Ø    В області Рядок та Стовпець перетягують назви тих стовпців, за зна­ченнями яких проводитиметься групування.

Ø    В область Дані перетягують назви тих стовпців, над даними яких здійснюватимуться обчислення.

Хід роботи

1. Відкрийте файл  Практична_6.xls  із папки Практичні_11 клас\ Практична_6

2. На аркуші Аркуш1 виконайте сортування даних за спаданням за значеннями в стовпці

Прізвище.

а)Виділіть усю таблицю вхідних даних.

б)    Виконайте команду Дані ► Сортування.

в)    У вікні Сортування діапазону зі списку Сортувати за виберіть параметр Прізвище та встановіть верхній перемикач у  положення за спаданням.

3. На аркуші Аркуш2 виберіть дані про учнів, зріст яких більше ніж 1м  80 см.

а)Виділіть усю таблицю вхідних даних.

б)    Виконайте команду Дані ► Фільтр ► Автофільтр.

в)    Клацніть кнопку в клітинці з назвою того стовпця, на значення якого накладатиметься умова. Зі списку, що відкриється, виберіть певну умову фільтрації.

4. На аркуші Аркуш3 виберіть дані про учнів, які народилися у 2000 році або в місті Києві.

а) У порожніх клітинках аркуша створіть критерій фільтрації.

б)    Виберіть команду Дані ► Фільтр ► Розширений фільтр. Відобразиться вікно діалогу.

в)    У полі Вихідний діапазон укажіть діапазон, де розміщується таблиця, а в полі Діапазон умов – діапазон критерію і клацніть кнопку ОК.

5. На аркуші Аркуш4 установіть умовне форматування, щоб клітинки,  в яких маса учнів більша ніж 80 кг, зафарбовувалися червоним кольором, а клітинки, в яких маса учнів менша ніж 75 кг, - синім.

а)Виділіть діапазон, що підлягає умовному форматуванню.

б)    Виконайте команду Формат ► Умовне форматування.  На екран буде виведено однойменне вікно з такими елементами керування, як розкривні списки, поля для введення адрес та кнопка Формат.

в) Задайте параметри форматування, вибравши один із двох елементів:

·         Значення – умова, від якої залежатиме формат, накладатиметься на значення саме тієї клітинки, яка форматується

·         Формула – умову буде задано як формулу, що набуває логічного значення

г) Клацніть кнопку Формат і у вікні Формат клітинок задайте формат шрифту, меж і кольору тла клітинок, який встановлюватиметься в разі виконання умови. Задайте обидві умови.  Клацніть кнопку ОК.

6. На аркуші Аркуш5 знайдіть проміжні підсумки за значеннями у стовпці Рік народження, які знаходять середній зріст і максимальну масу. (Спо­чатку встановіть режим знаходження середнього зросту, а потім - мак­симальної маси, знявши при цьому позначку прапорця Замінити поточні підсумки.)

7. На аркуші Аркуш6 створіть зведену таблицю, розташувавши в її рядках значення зі стовпця Місто народження, в її стовпцях - значення зі стовпця Рік народження, а в області Значення - середній зріст.

а) Виконайте команду Дані ► Зведена таблиця. Буде відкрито перше вікно майстра зведених таблиць. Нічого в ньому не змінюючи,
клацніть кнопку Далі.

б) У другому вікні майстра зведених таблиць виберіть діапазон вихідних даних (таблицю).

в)  Клацніть кнопку Макет і визначте структуру таблиці, а за допомогою кнопки Параметри  задайте її вигляд.

8. На аркуші Аркуш7 виконайте сортування таблиці за значеннями в кількох стовпцях в указаному порядку: Рік народження (за спаданням), Прізвище (за зростанням), Ім'я (за спаданням), Зріст (за зростанням).

9. На аркуші Аркуш8 відфільтруйте дані про учнів, які мають зріст від 1 м 75 см до 1 м 80 м або народилися у Вінниці.

10. На аркуші Аркуш9 установіть умовне форматування, щоб клітинки з містом народження Київ зафарбувалися блакитним кольором, з містом народження Суми - жовтим, а в клітинках зі зростом учнів меншим ніж 1 м 78 см - символи стали червоними, напівжирними, розміром 14 пт.

11*. На аркуші Аркуш10 створіть зведену таблицю, розташувавши в її рядках значення зі стовпців Рік народження і Зріст, у її стовпцях - значення з: стовпця Місто народження, а в області Значення - середню масу.

12. Збережіть внесені у файл зміни