Тема: сортування та фільтрування даних у електронних таблицях.
Мета:
ознайомити учнів з поняттям сортуванням і фільтрування, видами та методами сортування і фільтрування;
навчити використовувати відповідні інструменти в електронних таблицях;
показати приклади сортуванням таблиць з даними різних форматів, приклади фільтрування даних за певними умовами;
По завершенню вивчення учень
має уявлення про cортування і фільтрування даних в електронних таблицях;
знає алгоритми сортування і фільтрування таблиць з даними різних форматів;
розрізняє авто- й розширені фільтри;
Хід уроку 2. Актуалізація опорних знань
1. Організаційний момент
Вітання з класом. Перевірка присутності і готовності учнів до уроку. Перевірка виконання домашнього завдання.
3. Вивчення нового матеріалу
Cортування — це розміщення у визначеному порядку елементів даних.
Якщо дані відсортовано, то швидше можна знайти потрібні значення, ефективніше здійснити аналіз, усвідомити закономірності тощо. Сортувати, тобто змінювати порядок розташування в рядках або у стовпчиках, можна:
Примітка.
Символи упорядковують у відповідно до величини їхніх кодів у таблиці кодування. Самі таблиці кодування можуть бути різними.
Тексти упорядковують таким чином: спочатку їх упорядковують за першими символами, потім ті тексти, у яких перші символи збіглися, упорядковують за їх другими символами і т. д.
Порожні клітинки завжди розміщують останніми.
Розглянемо приклад сортування даних різних форматів (тут і надалі для ілюстрацій обрано LibreOffice Calc у стильовому оформленні «Танго», як типову вільнопоширювану програму створення й редагування електронних таблиць. Результати сортування за можливими напрямками зростання або спадання, розмістимо на цьому ж аркуші в сусідних стовчиках. Виділимо діапазон комірок (B3:B14 для поданих ілюстрацій), у яких бажаємо виконати сортування та виконаємо вказівку меню Дані / Сортування.
(Можливо) буде відкрито вікно попередження про наявність на аркуші непорожніх комірок (C3 i D3), розташованих у зв'язній з деякими обраними комірками області, з пропозицією підтвердити або змінити вибір діапазону комірок, в яких потрібно сортувати дані (на рисунку нижче цю область виділено синім прямокутником). Для підтвердження вибору потрібно натиснути кнопку Поточне виділення.
У вікні діалогу Сортування потрібно обрати умови та параметри сортування.
Умова сортування 2 (про неї і можливі наступні умови буде далі) у поданому прикладі залишено не визначенною, бо сортують дані лише одного стовпчика. Треба також звернути увагу на прапорець Заголовки стовпчиків у області. Якщо прапорець встановлено, першій рядок виділеного діапазона розглядають як рядок заголовків стовпчиків і не враховують у сортуванні, інакше його буде переміщено у відповідний умовам сортування рядок.
Виконавши аналогічні процедури для сортування за спаданням (із внесенням необхідних змін у вікні Сортування), отримаємо таблицю, що містить заданий стовпчик даних та відсортовані стовпчики.
Зауважимо: обрати зв'язний діапазон сортування можна, виділивши лише одну з його комірок (зробивши її поточною). Вказати напрям сортування за даними у стовпчику, що містить поточну комірку, можна, використавши кнопки 
Сортування за зростанням і 
Сортування за спаданням на панелі інструментів Форматування.
Розглянемо впорядкування числових даних прямокутних таблиць розміру 5 × 3, де:
верхню таблицю ліворуч відсортовано при виділенні зв'язного діпазону за зростанням даних її першого стовпчика;
нижню таблицю ліворуч відсортовано за поточною коміркою, обраною у другому стовпчику.
Відсортовані дані задля наочності винесено до інших діапазонів комірок.
При опрацюванні даних достатньо складної структури, сортування за даними лише одного стовпчика або рядка може бути недостатньо. У довільному виділеному діапазоні можна впорядкувати дані, відсортувавши їх спочатку за однією умовою, а за кожною наступною умовою сортування здійснювати при збігу значень щодо всіх попередніх умов.
Стовпчики, за даними яких проводять сортування, обирає користувач у тому самому вікні, що й інші умови та параметри сортування. У різних системах електронних таблиць по-різному називають обрані для сортування умови:
Розглянемо приклад такого багаторівневого сортування у середовищі LibreOffice. На наступному малюнку подано таблиця уcпішності учнів, упорядковану в алфавітному порядку прізвищ.
Нехай цю таблицю потрібно впорядкувати за спаданням Рейтингу, обчисленого як сума балів з профільних предметів. Якщо Рейтинги декількох учнів збігаються, дані потрібно впорядковати за оцінками з Інформатики, у разі збігу таких оцінок впорядкування провести за оцінками з Економіки. Якщо ж і ці дані збігаються, впорядкування здійснити за Прізвищем згідно з українською абеткою. Таким чином, впорядкування потрібно провести за чотирма ознаками, причому, не (обов'язково) у порядку слідування стовпчиків відповідних даних у таблиці. Порядкові номери рядків записів про кожного з учнів (стовпчик А) мають відповідати розташуванню рейтингів.
Виділимо діпазон даних для сортування — комірки, що містять прізвища і оцінки учнів та заголовки стовпчиків з даними. Комірки стовпчика А з порядковими номерами не входять до діапазону сортування. Вони у підсумковій таблиці будуть відповідати порядку рейтинга.
Застосуємо вказівку меню Дані / Сортування до виділеного діапазону і вкажемо умови для багаторівневого сортування у вікні діалогу Сортування з урахуванням приорітетів умов сортування та відповідний напрямок сортування.
Натиснувши кнопку Гаразд, отримаємо впорядковану за описаними вище умовами таблицю.
З невеликими відмінностями щодо інтерфейсу, багаторівневе і звичайне сортування здійснюють і в інших системах опрацювання електронних таблиць. Завершуючи опис впорядкування даних, зауважимо, що сортувати можна не лише рядки за розташуванням даних у стовчиках, а й стовпчики за розташуванням даних у рядках, обравши у вікні діалогу Сортування середовища LibreOffice Calc (або у аналогічних вікнах інших середовищ) відповідний перемикач: 
.
Фільтрування даних в електронній таблиці
Електронні таблиці можуть містити тисячі рядків і сотні стовчиків (або навпаки залежно від обраного користувачем способу подання даних). У таблиці, яку неможливо відобразити повністю на екрані монітора, знаходити потрібну інформацію, навіть за умови її попереднього сортування може виявитися нелегкою справою. У переважній більшості випадків користувача цікавлять дані, що відповідають певному набору умов. Електронні таблиці надають користувачу можливість приховати ті області таблиці, дані в яких цим умовам не відповідають. Такий споcіб відображення даних в електронній таблиці називають фільтруванням.
Фільтрування — це вибір даних у електронній таблиці, що відповідають певним умовам.
В електронних таблицях LibreOffice Calc можна обрати один з запропонованих способів фільтрування, наприклад, через вказівку меню Дані / Фільтр / … .
Застосування кожного з них має свої особливості.
Автофільтр — найшвидший за доступом і найбідніший за можливостями.
Розглянемо, наприклад, таке завдання: залишити у відфільтрованому списку призвіща лише тих учнів, що мають рейтинг не нижче 50 та оцінки з інформатики не нижче, ніж 10.
Для застосування Автофільтру потрібно віділити діапазон. Для зв'язного діапазону достатньо вибрати всередині цього діапазона будь-яку комірку. При застосування Автофільтру важливо, щоб стовпчики, дані яких фільтрують, мали заголовки. Після застосування вказівки меню Дані / Фільтр / Автофільтр (див. малюнок вище) cаме в комірках, що містять такі заголовки, з'являться списки умов фільтрування, що розкриваються. Як усталено, їх позначено кнопками 
.
Для поданого прикладу фільтрування (зв'язний діапазон задано поточною коміркою, див. попередній малюнок) спочатку побачимо вікно-попередження про наявність у зв'язному з обраною коміркою (С5) діапазоні (A3:H11), стовпчика, що не має заголовку (стовпчик А).
Натиснемо кнопку Так, інакше у фільтруванні буде враховано вміст комірок-заголовків, що не відповідає поставленому завданню. В отриманій внаслідок такого застосування таблиці і побачимо кнопки , згадані вище.
Якщо діпазон фільтрування виділити явно,
одразу отримаємо таблицю-результат. Кожна кнопка по черзі, в будь-якому порядку, відкриває список значень даних відповідного стовпчика (однакові не повторюються). Оберемо спочатку стовпчик Рейтинг та задамо в ньому умови фільтрування, залишивши прапорці для тих значень, що належать до обраного для показу діапазону (51 і 52).
Рядки таблиці, дані в яких не відповідають умовам фільтрування, буде приховано, але не видалено. Відміна фільтрування поновлює їх відображення у таблиці. У меню, що відкрилося, бачимо також додаткові можливості для фільтрування:
;
.У поданому прикладі немає потреби такого застосування, тому натиснемо кнопку Гаразд і отримаємо таке подання даних.
Сформуємо аналогічно умови фільтрування щодо стовпчика Інформатика і отримаємо таблицю, що відповідає всім сформульованим умовам фільтрування.
Кнопки у деяких стовпчиках змінили свій вигляд на 
, вказуючи на те, щодо чого задано умови фільтрування. На відсутність певних рядків початкової таблиці у відфільтрованій вказують також змінені межі нумерації рядків. Для поданого прикладу не выдображено рядки між 4-им і 6-им, між 7-им і 12-им.
Стандартний фільтр дає можливість скопіювати відфільтровані дані в інший діапазон на цьому самому аркуші або на інший аркуш, який має бути створений до фільтрування.
Застосування вказівки меню Дані / Фільтр / Стандартний фільтр при виділеному діапазоні призведить до відкриття вікна діалогу Стандартний фільтр.
У цьому вікні формують умови або критерії фільтрації. Щоб отримати повну уяву про можливі параметри фільтрування, потрібно застосувати одразу кнопку Більше і отримати повний перелік доступних умов. Для нашого прикладу важливим є параметр Діапазон містить позначки стовпчиків та Копіювати результати до…. Потрібно виставити відповідні прапорці та внести у відповідний рядок координати діпазону, в який потрібно скопіювати відфільтровані дані. Для цього достатньо вказати аркуш та ліву верхню комірку такого діапазону або з клавіатури, або (для наочності) мишею.
У списку Назва поля, що розкривається (випадає), оберемо перший за значимістю критерій Рейтинг і задамо у відповідному полі умову =, обравши її з випадного списку,
та значення для умови, яке можна так само обрати зі списку або ввести безпосередньо в рядок. Іншу умову фільтрування у поданому прикладі застосовують до відфільтрованих за першою умовою даних, шляхом приєднання її до вже заданих умов логічним оператором I зі списку Оператор. Інший доступний у цьому списку оператор Або.
Натиснувши кнопку Гаразд, отримаємо відфільтровані дані на вказаних аркуші та діапазоні.
На іншому буде розташовано початкову таблицю без змін, спричинених фільтруванням.
Примітка. Результати застосування Автофільтру та Стандартного фільтру відрізняються наявністю кнопок списків значень. При повторному застосуванні до (відфільтрованої) таблиці Стандартного фільтру, у вікні діалогу Стандартний фільтр область Критерій фільтру міститиме обрані раніше умови. Вони також зберігаються при збереженні відфільтрованої таблиці. Інакше кажучи, збережена відфільтрована таблиця містить відомості про проведене форматування її вмісту.
Розширений фільтр надає можливість створити власні крітерії фільтрування стовпчиків даних.
Цей спосіб доцільно використовувати, коли умови користувача неможливо відтворити раніше роглянутими способами. Для використання Розширеного фільтру необхідно попередньо створити запис критерію. Це зручно робити на тому самому аркуші, що й дані. Для запису умов фільтрування потрібно зробити таке:
Скопіювати заголовки рядків, за даними яких проводять фільтрування, у комірки в рядках, що розташованих вище або нижче рядків даних. Для запису умов потрібно три рядки та щонайменше один порожній між умовами і таблицею.
Записати умови фільтрування в комірках під заголовками.
Якщо кілька умови застосовують для одного стовпчика, його заголовок копіюють потрібну кількість разів в одному рядку.
Якщо умови для кількох стовпчиків застосовують одночасно (логічне «i»), їх розташовують у першому під заголовками, рядку.
Якщо з умов фільтрування має справджуватися хоча б одна (логічне «або»), їх розташовують у другому рядку під заголовками.
Можлива будь-яка комбінація таких умов.
Вказати діапазон даних для фільтрування, тобто зробити поточною будь-яку комірку з діапазону, шо фільтрують, або виділити його в цілому.
Застосувати вказівку меню Дані / Фільтр / Розширений фільтр.
У вікні діалогу Розширений фільтр вказати діапазон умов (крітеріїв) та за потреби обрати інший діапазон для відображення відфільтрованої таблиці, застосувавши кнопку Додатковоi (після застосування змінює назву на Менше). Діапазон умов найпростіше обрати, виділивши його повністю (у поданому прикладі це $Аркуш1$C$14:$D$15), діапазон відображення - виділивши його лівий верхій кут на потрібному аркуші (у поданому прикладі $Аркуш2.$B$5).
Натиснути кнопку Гаразд і отримати відфільтровану таблицю.
Відфільтровану таблицю можна фільтрувати далі за новими крітеріями на тому аркуші, де її зберегли, або скопіювати у інший діапазон і потім фільтрувати вже там. Наприклад, таким чином,
і отримати таке.
Проміжні підсумки
Таблиці, що відображають, наприклад, наявність товарів на складі, запасних частин для ремонту тощо, містять зазвичай багато рядків, що повторюються. У деяких випадках цікаво визначити загальної кількості певного різновиду, середня вартість одиниці товарної категорії, відповідність певним датам, тощо. Наступним рисунком подано два фрагмента (перші й останні рядки) порівняно великої (за кількістю рядків) таблиці, в який відображено відомості про відеомонітори популярних торгівельних марок (брендів), до якої вже застосували багаторівневе впорядкування (визначте, яке саме, самостійно).
Спробуємо зробити таблицю зручнішою для аналізу, пошуку та перегляду. Наприклад, перетворити таблицю таким чином, щоб мати швидкий доступ до даних про монітори одного бренда, щоб з цих даних було видно:
Для виконання таких завдань у середовищі LibreOffice Calc існує спеціальний інструмент Проміжні підсумки, який викликають вказівкою меню Дані / Проміжні підсумки… . Для застосування цього інструменту необхідно подбати про зв'язність відповідного діапазону і наявність назв кожного стовпчика даних. Необхідно також виділити таблицю, або зробити поточною її довільну комірку.
У вікні діалогу Проміжні підсумки потрібно встановити параметри групування даних у відповідності з умовами, як на поданих нижче малюнках.
У 1-шій групі — відомості щодо кількості товарів певного бренду, найбільшого значення діагоналі й найбільшої вартості. У 2-гій групі такі самі відомості про найменші значення. Для цих умови таблиця з підведеними проміжними підсумками матиме такий вигляд (показано початкові й останні рядки таблиці).
З певних міркувань можна стверджувати, що інформативність останньої таблиці вища, ніж початкової. В ній відомості не лише впорядковано за брендами, їх впорядковано для кожного бренду за довжиною діагоналі у порядку спадання вартості. Для кожного бренду окремо подано найвищу і найнижчу ціни. Згруппована таблиця має кілька рівнів, в залежності від обраних параметрів групування. Швидкий доступ до відповідного рівня надають кнопки 
. Кожна з них розкриває рівень при натисканні: першій рівень відображає відомості мінімально, тільки загальним підсумком.
До наступного рівня можна перейти, натиснувши кнопку розгортання 
. Він містить кількісні підсумки по кожному бренду, але без подробиць.
Отримати докладні відомості по кожному бренду окремо можна, натискаючи відповідні кнопки навпроти відповідного рядка підсумків. При цьому їхній вигляд змінюється на 
.
На останньому рівні всі групи повністю розкрито, всі кнопки згортання-розгортання мають вигляд . При потребі таблицю можна мінімізувати за виглядом, застосуваши «швидку» кнопку доступу до рівня 
. Збережена після такого опрацювання таблиця зберігає можливість подальших перетворень.
Повернення до початкового вигляду (в разі необхідності) можна зробити таким чином:
Використати вказівку меню Дані / Проміжні підсумки… .
Cкасувати группування даних зняттям усіх встановлених прапорців у вікні діалогу Проміжні підсумки.
Натиснути кнопку Гаразд.
Зведена таблиця — інструмент аналізу даних електронної таблиці, який групує і опрацьовує дані за достатньо великим набором параметрів.
Алгоритм побудови зведеної таблиці
Виділити діапазон даних, наприклад, зробивши поточною його довільну комірку.
Використати вказівку меню Дані / Зведена таблиця / Створити.
У вікні діалогу Вибрати джерело обрати поточне виділення, поставивши відповідну мітку, і натиснути кнопку Гаразд, і дочекатися відкриття вікна діалогу Зведена таблиця.
У вікні діалогу Зведена таблиця створити структуру, обравши відповідні поля для групування категорій (заголовків), для яких будуть розраховані обрані у вікні Поле даних числові характеристики. Заголовок стовпчика числових даних розташовують у полі даних. Для поданого прикладі потрібно згрупувати дані Бренд у рядках, Діагональ — у стовпчиках, у поля даних внесемо значення з Вартості. Це можна здійснити, перетягнувши мишею відповідні кнопки при натиснутій лівій клавіші миші. Також, натиснувши кнопку Додатково, вказати діапазон, у якому буде збережено зведену таблицю. Як усталено, на новому аркуші. Вікно діалогу містить запит, чи включати до таблиці підсумок по рядках і стовпчиках, чи вмикати можливість фільтрування даних у зведеній таблиці (вмикається Стандартний фільтр), чи враховувати порожні рядки. Режим Дозволити перехід до подробиць дає можливість отримати відомості щодо елемента рядка чи стовпчика після подвійного клацання на вмісті відповідної комірки. Режим Розпізнати категорії автоматично додає рядки, що не мають категорій (заголовків), до рядків, що стоять вище (корисно при наявності порожніх рядків у таблиці).
Викликати вікно діалогу Поле даних, натиснувши перетягнуту кнопку й кнопку Параметри.
У вікні діалогу Поле даних вибрати потрібну функцію (як усталено Сума) і натиснути кнопку Гаразд у вікні Поле даних.
Останні два кроки повторити для всіх перетягнутих кнопок.
У вікні діалогу Зведена таблиця натиснути кнопку Гаразд і отримати зведену таблицю.
Дані отриманої таблиці можна фільтрувати за даними рядків або стовпчиків, що буде відображено зміною вигляду кнопки фільтрування.
Двічі клацнувши по комірці з поля рядків, отримаємо спрощений варінт зведеної таблиці, в якій подробиці відображено лише для обраної комірки (бренд Acer у поданому прикладі). Нагадаємо, що такий режим відображення потрібно дозволити встановленням відповідного прапорця (або заборонити його зняттям).
4. Інструктаж з ТБ
5. Закріплення вивченного матеріалу
Завдання. Завантажити файл work.ods і виконати на наявних аркушах таблиці такі завдання (номер аркуша збігається з номером пункту), розміщуючи результати на тих самих аркушах.
Впорядкувати дані за зростанням по даним Прізвище.
Вибрати дані про учнів, більших за зрістом, ніж 1 м 80 см.
Вибрати дані про учнів, що народились у 1997 році або в Києві.
Знайти проміжні підсумки за даними Рік народження, які знаходять середній зріст та найбільшу вагу.
Створити зведену таблицю, розташувавши в рядках дані Місце народження, у стовпчиках — Рік народження, у полі даних — середній зріст. Запишіть на аркуші свої висновки з аналізу зведеної таблиці.
Виконати багаторівневе сортування рядків таблиці за даними стовпчиків Рік народження за спаданням, Прізвище (за зростанням), Ім'я (за спаданням), Зріст (за зростанням).
Створити зведену таблицю, в рядках якої розмістити Рік народження і Зріст, у стовпчиках — Місце народження, у полі значень — середне значення за стовпчиком Вага. Записати висновки на аркуші.
Результат записати у файл з назвою Ваше прізвище.ods у теку, вказану вчителем.
6. Підведення підсумків уроку
Виставлення оцінок.
7. Домашнє завдання
Завантажити файл example.ods.
Виконайте завдання, розглянуті на уроці як приклади.
Створити власноруч або знайти у відкритих джерелах файл з даними у формі таблиці. Запропонувати способи аналізу його вмісту сортуванням і фільтруванням даних, створенням проміжних підсумків та зведених таблиць.
Текст упорядкував Духович Олександр Андрійович, вчитель СЗШ № 262 Подільского району міста Києва, під час виконання випускної роботи на курсах підвищення кваліфікації з 28.10.2013 по 01.11.2013.