Тема: упорядковування даних у таблицях, автоматичні та розширені фільтри, проміжні підсумки

Мета: cформувати поняття та ознайомити з технологіями:

• упорядковування;
• фільтрування даних;
• підведення проміжних підсумків;

Після вичення матеріалу учень повинен

пояснювати:

• поняття впорядковування та фільтрування даних;
• мету впорядковування та фільтрування даних;
• різницю між упорядкуванням як усталено (алфавітним), логічним та визначеним користувачем порядком;
• різницю між Автоматичним, Стандартним, й Розширеним фільтрами;
• мету і можливості використання проміжних підсумків.

• упорядковування даних за різними критеріями;
• фільтрування даних за допомогою Автофільтра, Стандартного й Розширеного фільтрів;
• підбиття проміжних підсумків.

Обладнання: ПК зі встановленими ОС та LibrOffice, (дана) інструкція.

1. Організаційний момент.
2. Актуалізація опорних знань.
3. Пояснення нового матеріалу.
4. Закріплення вивченого матеріалу.
5. Інструктаж з охорони праці.
6. Закріплення нового матеріалу виконанням практичного завдання.
7. Підбиття підсумків уроку.
8. Домашнє завдання.

Хід уроку

1. Організаційний момент
Вітання з класом. Перевірка присутності і готовності учнів до уроку. Перевірка виконання домашнього завдання.

2. Актуалізація опорних знань

1. Для чого призначено редактор електронних таблиць?
2. Що являє собою файл електронної таблиці?
3. Які типи даних може містити комірка електронної таблиці?

3. Вивчення нового матеріалу.

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

Сортування — це процес перегрупування однотипних даних в деякому певному порядку за заданою ознакою.

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

Найпростіший спосіб упорядковувати дані — виділити діапазон та натиснути відповідну кнопку на панелі інструментів. Таким способом можна виконати упорядкування за зростанням або за спаданням, причому, верхній рядок вважають заголовком і не враховують у впорядкуванні.

Якщо потрібно провести сортування даних з налаштуванням параметрів, то потрібно використати вказівку меню Дані / Сортування,

щоб відкрити вікно діалогу Сортування з вкладками Умови сортування та Параметри. На вкладці Умови сортування ми можемо налаштувати порядок сортування та визначити кілька умов.

Для упорядкування в іншому порядку, наприклад, щоб розташувати місяці і дні тижня відповідно до їх логічного, а не алфавітного порядку, потрібно перейти на вкладку Параметри. На цій вкладці можна налаштувати параметри сортування, наприклад, застосувати порядок, визначений користувачем або враховувати (чи ні) заголовки стовпчиків в області.

Фільтрування даних — це знаходження підмножини даних, що відповідають певним критеріям.

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

Фільтрування даних в електронних таблицях можна здійснити трьома способами, а саме за допомогою Автофільтра, Стандартного фільтра або Розширеного фільтра.

Фільтрування даних з використанням Автофільтра:

1. Встановити курсор в будь-яку комірку з діапазону даних.

2. Натиснути кнопку Автофільтр на панелі інструментів Засоби (для підключення панелі у разі її відсутності можна використати вказівку меню Перегляд / Панелі інструментів)

   

   або використати вказівку меню Дані / Фільтр / Автофільтр.

   

3. Клацанням миші по кнопці зі стрілкою розкрити список стовпчика, за даними якого буде здійснено вибірку.

   

4. Вказати необхідні значення (виставити мітки) і натиснути кнопку Гаразд.

Для відновлення відображення всіх рядків таблиці потрібно клацнути мишею по кнопці зі стрілкою і в списку, що розкрився, вибрати Усі.

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

Умови для відбору записів (за даними певного стовпчика) можуть складатися з двох і більше частин, сполучених логічною зв'язкою І / АБО. Кожна частина умови може включати значення, яке можна вибрати зі списку, або містити шаблонні символи підстановки. За символ підстановки слугують:

• зірочка * — для заміни довільної кількості символів;
• знак питання ? — для заміни одного символу;
• оператор відношення (І чи АБО).

Оператори порівняння при заданні критеріїв відбору

Для відновлення відображення всіх рядків таблиці потрібно використати вказівку меню Дані / Фільтр / Видалити фільтр.

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

До виконання вказівки фільтрування необхідно сформувати спеціальну область для задання умов фільтрування даних — діапазон умов.

Вимоги до діапазону умов:

• між діапазоном умов і таблицею даних має бути щонайменше один порожній рядок, якщо вони розташовані на одному аркуші;

• діапазон умов має містити рядок із заголовками стовпчиків і кілька рядків для завдання умов відбору;

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

• якщо умови фільтрування подано одним рядком для різних стовпчиків, то їх вважають пов'язаними сполучником «І». Інакше, тобто при записі умов фільтрування у різних рядках для різних стовпчиків, ці умови вважають пов'язаними сполучником «АБО».

Наприклад, діапазон умов можна сформувати таким чином.

Після формування діапазону умов з критеріями вибірки записів потрібно вибрати комірку усередині діапазопу і використати вказівку меню Дані / Фільтр / Розширений фільтр,

щоб викликати вікно діалогу Розширений фільтр. У цьому вікні діалогу потрібно вказати діапазон комірок таблиці і адресу або назву сформованого заздалегідь діапазону умов і натиснути кнопку Гаразд.

Записи можна фільтрувати на первинних данних або одночасно з фільтруванням копіювати в указану область на поточному аркуші. Щоб скопіювати відфільтровані рядки в іншу область аркуша, потрібно натиснути кнопку Додатково вікна діалогу Розширений фільтр (див. попередній малюнок), встановити мітку (перемикач) Результат фільтру до і вказати верхню ліву клітинку області вставки відібраних даних ($Допомога.$A$21 для поданого прикладу).

Робота з електронною таблицею як з базою даних
Електронну таблицю, всі рядки якої містять однорідну інформацію, розглядають як список або база даних. Терміни «база даних» і «список» в описі табличного редактора використовують як синоніми:

• рядок вважають записом бази даних;
• стовпчик вважають полем бази даних;
• заголовки стовпчиків вважаються назвами полів бази даних.

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

• Всі рядки таблиці мають містять однорідну інформацію — у всіх рядках одного стовпчика мають бути однотипні дані.

• Заголовки стовпчиків — у першому рядку списку.

• Назви рядків — у крайньому лівому стовпчику списку.

• У таблиці не має бути порожніх рядків і стовпчиків, у тому числі між заголовками і першим рядком даних.

• Перший порожній рядок (після рядку заголовків) вважають ознакою кінця списку.

• Функції списку не можна використовувати одночасно для несуміжних областей.

До даних, організованих у вигляді списку (бази даних), можна застосовувати спеціальні функції категорії База даних і виконувати з ними такі операції:

• додавати, змінювати і видаляти записи;
• знаходити записи;
• сортувати записи;
• фільтрувати за допомогою Автофільтра і Розширеного фільтра;
• підводити загальні та проміжні підсумки.

Автоматичне обчислення загальних і проміжних підсумків — це зручний спосіб швидкого узагальнення та аналізу даних в електронній таблиці.

Перед обчисленням проміжних підсумків потрібно виконати впорядкування за тими стовпчиками, за якими буде підбити підсумки — для того, щоб усі записи з однаковими полями цих стовпчиків потрапили в одну групу. Якщо дані таблиці організовано неправильно, тобто не у вигляді списку, то табличний редактор може не зрозуміти структуру таблиці і не створити проміжних підсумків.

Властивості підведення підсумків

1. Табличний редактор автоматично створює формулу, додає рядок або рядки для запису проміжних підсумків і підставляє адреси комірок даних.
2. Для однієї і тієї cсамої групи даних можна одночасно обчислювати проміжні підсумки за допомогою кількох функцій, а також обчислювати «вкладені» або багаторівневі підсумки.
3. Значення загальних і проміжних підсумків буде перераховано автоматично при кожній зміні первинних даних.

Алгоритм підбиття підсумків

1. Впорядкувати список за стовпчиком, для якого необхідно обчислити проміжні підсумки.

2. Виділити довільну клітину діапазону або весь діапазон.

3. Використати вказівку меню Дані / Проміжні підсумки….
   

4. У вікні діалогу Проміжні підсумки зробити таке:
   • у списку Групувати за вказати стовпчик, що містить групи, за якими необхідно підвести підсумки (це має бути той стовпчик, за яким проводили сортування списку);
     

   • у полі Обчислити проміжні підсумки вказати категорії, за якими потрібно обчислити підсумки;

   • у полі Використовувати функцію, вибрати потрібну функцію для підбиття підсумків (для поданого прикладу це Сума;

   • натиснути кнопку Гаразд.

Підсумки буде отримано на тому самому робочому аркуші у структурованій таблиці.

4. Закріплення нового матеріалу.

1. Що таке сортування даних?
2. Як впорядкувати дані таблиці?
3. Що таке фільтрування даних?
4. Яка мета впорядкування та фільтрування даних?
5. Назвіть і опишіть застосування 3 способів фільтрування.
6. Перелічіть вимоги до діапазону умов Розширеного фільтра.
7. Як копіювати результати фільтрування (Розширений фільтр)?
8. Як тлумачать рядок, стовпчик і залоговок стовпчика у термінах баз даних?
9. Перелічіть вимоги до списку електронної таблиці.
10. Для чого використовують проміжні підсумки?
11. Як автоматично підбити підсумки?

1. На окремому аркуші створити список місяців року та днів тижня. Переглянути всі можливості різних варіантів впорядковування таких списків.
   

2. Створити 4 копії аркуша, що містить табличку з результатами збору іграшок для дитячих будинків.

   

3. На окремих аркушах з результатами збору іграшок для дитячих будинків виконати фільтрування даних:

   • за допомогою Автофільтра вивести дані лише щодо класів 8 і 10;

   • за допомогою Стандартного фільтра вивести дані щодо тих класів, які зібрали більше 6 м'яких іграшок або менше 7 конструкторів;

   • за допомогою Розширеного фільтра — для такого діапазону умов.

   

4. На окремому аркуші з результатами збору іграшок для дитячих будинків підбити проміжні підсумки для стовпчиків М'які іграшки, Ляльки, Машинки, Конструктори, Всього з групуванням за паралеллю (як у розглянутому вище прикладі).

7. Підбиття підсумків уроку
Виставлення оцінок.

8. Домашнє завдання
Опрацювати матеріал уроку. З'ясувати дію (призначення) кнопок , , при підбитті проміжних підсумків. Підготуватись до виконання практичної роботи.

Текст упорядкувала Тетяна Леонтіївна Шпак, вчитель Природничо-наукового ліцею № 145 Печерського району міста Києва, під час виконання випускної роботи на курсах підвищення кваліфікації з 18.11.2013 по 06.12.2013.