Для роботи з великими об'ємами даних попередні методи, як то умовне форматування, закріплення діапазонів, масштабування екрану або навіть пошук (з використанням інструменту пошуку [Ctrl] + [F]) не підійдуть. Діло в тому, що якщо таблиця велика як вшир так і вглиб, нам не оминути перемотування її по екрану за допомогою важельків скролера. А особливо це стає актуальним, коли нам треба внести у деякі клітинки необхідну інформацію, а ці клітинки розташовані далеко одна від одної. Постійні пошуки необхідної клітинки та контроль, щоб вона була у правильному рядку та стовбчику, приводять до втрати продуктивності роботи. В такому разі нам на допомогу прийде операція впорядкування (сортування).

Перше занурення. Інструмент сортування.

EXCEL вміє сортувати рядочки або стовбчики виділеного діапазону, при цьому він буде орієнтуватися на обраний стовбчик або рядочок відповідно. Пояснимо на прикладі. Нехай нам треба певним чином відсортувати список переможців ІІ етапу Всеукраїнської олімпіади з Математики з 6-8 класів. Для цього, подібно до попереднього майстер-класу, скачати необхідний файл з таблицями результатів зі сторінки "Матеріали Всеукраїнських учнівських олімпіад", консолідувати таблиці протоколів необхідних класів в одну велику таблицю та знищити у ній не потрібні стовбці та рядочки.

Live hack! Під час знищення стовців з балами за окремі завдання, можливий такий випадок, що стовбець з сумою балів "зіпсується", тобто, з'являться якісь помилки. Причиною цього є те, що в цьому стовбці знаходиться формула, що підраховує суму балів. При знищені цих балів, формула не знає що їй додавати і сигналить нам помилккою.

Щоб в подальшому цього  уникнути,  варто виділити всю зону з балами та фомулами, скопіювати її в буфер пам'яті ( [Ctrl] + [C] ) і ще раз вставити туди ж  за допомогою правої кнопки мишки та обравши режим вставки "значення".  Тоді замість формул будуть вставлені обчислені ними значення. Тепер ніякі трансформації та перебудови таблиці вже не зможуть змінити ці значення. Див мал. внизу.

Тепер маємо ідеальну табличку для нашого прикладу. Познайомимось із інструментом сортування. Він знаходиться під кнопкою із зображеними на ній "лійкою" (загальноприйнятим символом фільтрування) та літерами А-Я (загальноприйнятим символом сортування). По натиску на цю кнопку відкриється випадне меню де ми побачимо пункти А-Я - сортування за алфавітом чи зростанням числового значення, Я-А - сортування проти алфавіту чи спаданням числового значення та кнопкою "Сортування з налаштуванням".

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

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

Але основні можливості цього інструмента вам відкриються при використанні третьої кнопки меню сортування - "Сортування з налаштуванням". Вам відкриється діалогове вікно в якому ви можете точно вказати згідно якого стовбчика ви хочете відсортувати виділений діапазон. 

Наприклад, ви можете відсортувати всю таблицю за стовбчиком D, тобто, за прізвищами учнів. 

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

Live hack! Будьте уважні із прапорцем "Мої дані містять заголовки". Якщо він виставлений, то система не буде брати до уваги перший рядочок виділеного вами діапазону, бо вважатиме, що там міститься заголовок таблиці, який, звісно, у процес сортування не вступає.

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

В нас спочатку відсортується таблиця по стовбцю G ("Клас"), тобто, від 6 по 8 клас. Далі в кожному класі буде застосовано сортування по стовбцю J ("Сума балів") по спадному порядку, тобто, від кращих результатів до гірших. Далі сортуємо всіх дітей,які отримали однакову к-сть балів за прізвищами по алфавіту (тобто, по стовбцю D). Правда, класно! ;)

Друге занурення. Інструмент фільтрації.

Ще потужнішим є інструмент фільтрації. В нашому спадному меню він позначається лійочками.

Live hack! Для правильного функціонування цього чудового інструменту обов'язковою умовою є наявність одного рядочка вверху над табличкою в якому бажано мати вписані назви стовбців. Якщо його немає, або він не один (наприклад є зліплені в висоту кілька клітинок) то варто просто створити його новенького. Можна і без назв, також буде все робити.

Тепер виділяємо цей верхній рядочок та тицяємо у випадному меню кноку з лійочкою та написом "Фільтр". Бачимо що біля кожної клітинки виділеного рядочка з'вились кнопочки з трикутничками. Це і є кнопки меню налаштування фільтрації таблиці по даному стовбцю. Що ми можемо витворити за допомогою кнопок цього випадного меню.

По-перше ми можемо небагато легше сортувати всю таблицю (зауважте, без її попереднього виділення) використавши перші дві кнопки. А також тут з'вляється цікава можливісь сортувати вміст таблиці за кольором клітинок даного стовбця. Це буває в нагоді, коли нам треба підняти вверх виділені кольором  рядочки. (Наприклад, в олімпіаді є кілька  проблемних учасників, і треба швидко переглянути їх список. Тоді, по ходу роботи, "маркуємо" відповідні клітинки якимось кольором, наприклад, жовтим, та натиснувши на вищезгадану кнопку, виводимо всі жовті рядочки вверх)

По-друге ми можемо відфільтрувати всю таблицю (знову таки, без її попереднього виділення), тобто, залишити на екрані тільки необхідні нам рядочки всі інші сховати. Найпростіше це пояснити на попередньому, "кольоровому" випадку. Натиснувши на кнопку меню "Фільтувати по кольору", ви залишите на екрані тільки жовті рядочки, всі інші будуть сховані. (Вони не пропадуть. Просто "зхлопнуться" - висота їх стане дорівнювати 0). Зручно? Тепер на екрані невелика табличка де є те, що нам треба. Якщо ж нам треба повернути всі рядочки на місце, тиснемо в меню кнопку "Видалити фільтр зі стовбця ..."

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

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

Ще крутіша задача. Ми знаємо лиш по батькові учениці, наприклад "Миколаївна". Застосувавши інструмент "Текстові фільтри", ми зможемо "виловити" двох учениць із таким ініціалом. Чим вам не детектив, де супер-пупер комп'ютерний геній - агент ФБР, який з величезної купи даних "витягує" кілька варіантів для подальшого пошуку? ;)

Ще крутіша можливість, коли ми накладаємо послідовно кілька фільтрів на ту саму таблиці, поступово звужуючи коло пошуку. В нашому прикладі ми шукали за допомогою фільтру тільки переможців, а серед них іншим фільтром учнів ВТЛ, ВФМЛ №17 та ВЛ №35. Це так і називається, багаторівневі фільтри.

Третє занурення. Інструмент звідних таблиць.

Даний інструмент в EXCEL прийшов з теорії баз даних. Там він називається перехресні запити. Суть його доволі важко пояснити словами. Краще всього його опановувати на практиці. Але, якщо в двух словах, ми матимемо можливість "розложити" довільну таблицю по одному, двом, або навіть трьом параметрам (стовбцям) так, що всі дані будуть підраховані або просумовані по групам. Важко? А ви як думали?

Тепер розберемо приклад. Спочатку виділимо таблицю яку будемо аналізувати та натиснемо на кнопку "Звідні таблиці". Відкриється  допоміжне вікно "Створення звідної таблиці". У ньому потрібно вказати де буде створюватись звідна таблиця. Є два варіанти: перший - таблиця створюється на новому листку, другий - на тому ж листі що і наша таблиця тільки треба вказати місце поза нею. Що ми і зробимо.

Після цього буде задіяний майстер створення звідної таблиці. Там де буде майбутня звідна таблиця зарезервовано місце і в ньому стоїть спеціальний маркер, Праворуч можна побачити список параметрів  (ними є  назви  всіх стовбців батьківської таблиці, а внизу чотири таємничих поля "Фільтри", "Стовбці", "Рядки", "Значення". 

Таємниці зразу розвіюються, якщо виділити параметр "Заклад освіти" та перетягнути його в поле "Рядки". Ми на зарезервованому місці побачимо список всіх навчальних закладів, що трапляються в одноїменному стовбці батьківської таблиці (доречі, відсортований). Далі зробимо це саме з першим параметром "№ з/п", та перетягнемо  його в поле "Значення". Як результат, звідна таблиця доповниться якимись числами навпроти  кожного  навчального закладу.

Live hack! Важливо, щоб перший стовбець "№ з/п" був заповнений унікальними ідентифікаторами. В нашому випадку, коли ми перенумеровували консолідовану таблицю, ми цю операцію вже здійснили.  Тепер важливо обрати тип операції, що буде проводитись із цими ідентифікаторами. Оскільки, нам потрібно визначити їх к-сть для кожного з навчальних закладів (тобто, визначити к-сть учасників у олімпіаді з кожного навчального закладу),  натискаємо правою кнопкою мишки на параметрі та обираємо цю операцію у випадному списку. Див. мал. нижче.

Тепер ми маємо список закладів освіти і навпроти кожного к-сть учасників з кожного закладу. 

Якщо добавити в поле "Стовбці" параметр "Сума балів", то отримаємо чотири стовбці в яких буде вказано кількість І, ІІ, ІІІ місць та к-сть учнів які не отримували місць. До того ж, в п'ятому стовбці ми знову таки побачимо к-сть учасників з кожного закладу (тобто, суму попередніх чотирьох). А під таблицею з'явиться рядок, що підсумує загальну к-сть І, ІІ, ІІІ місць та к-сть учнів які не отримували місць по всьому списку. 

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

Далі було пояснено як виконувати домашнє завдання. Для нього необхідно озброїтись справді великою таблицею. Ми для демонстрації можливостей EXCEL обрали таблиці результатів участі випускників України в ЗНО минулих років. Ці таблиці відкриті для скачування на сайті  Українського центру оцінювання якості освіти.  

Що ми робити:

1. Відкрили таблицю  OpenDataZNO2018.7z (22.4 Mb). Звернули увагу на те, що вона  збережена  у форматі CSV ( "comma-separated values" - в перекладі   "значення, розділені комою" (або іншим  символом)). Це популярний формат перенесення табличної інформації між базами даних та табличними процесорами. Важливо знати, що в ньому зберігається лише інформація  про дані в таблиці без форматування.
2. Далі оцінили величину таблиці. Оскільки в ній третина мільона (більше 300 000 рядочків) та кілька десятків стовбців! Незабуваймо, що оскільки дані таблиці повинні бути поміщені в опративну пам'ять комп'ютера, то цієї оперативної пам'яті в слабеньких машинах може і нехватити. Та навіть на потужних машинах всі маніпуляції з такими BigDatа- монстрами проходять зі значними затримками. Тому і стає актуальною задача зменшити к-сть інформації до необхідного мінімуму.
3. Розібравшись зі структурою таблиці (тобто, що знаходиться в кожному стовбчику) ми змогли визначити необхідні нам стовбці. Інші ми просто знищили. Таким чином. значно зменшили навантаження на комп'ютер.
4. Впевнились, що  перший стовбець містить унікальний ідентифікатор. Це необхідно для роботи звідних таблиць. (Див. Life Hack вище).
5. Застосовуючи інструмент фільтрування виберемо зі всього списка лише рядки, що належать роботам учасників з м. Вінниці, з них лише записи, що належать цьогорічним випускникам,  з них  лише учасників, що навчаються в "ВЛ №15". (Використовуємо багаторівневу фільтрацію)
6. На цьому етапі в нас залишилось лише 39 рядочків, що відповідає реальній к-сть учасників ЗНО 2018 року з цього закладу освіти. Перенесемо, що отримали (простим копіюванням) на новий листок. Тепер працювати стане набагато простіше, бо не треба буде маніпулювати третиною мільйона рядочків. Робота піде швидше.
7. Знайшли стовбці, що відповідають результатам ЗНО з української мови, історії, математики. Всі інші знищили. 
8. Використовуючи інструмент звідних таблиць зробили  кількісний зріз успішності учнів в ЗНО з математики.

_________________________________________________________________

Домашнє завдання

Користуючись  вище згаданим алгоритмом,  створити  кількісний зріз успішності учнів "ВФМЛ №17" в ЗНО 2019 року з математики.

Таблицю результатів ви зможете скачати з офіційної сторінки Українського центру оцінювання освіти

Домашнє завдання здавати у вигляді Excel файлу на пошту metiska64@gmaul.com

Бажаємо успіхів!