Лекція 11. Аналіз даних в Microsoft Excel

Сортування даних

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

Рис. Приклад сортування даних за кількома рівнями

 Дані можна сортувати за текстом (від «А» до «Я» або від «Я» до «А»), числами (від найменшого до найбільшого та від найбільшого до найменшого) і датою та часом (від найстаршого до наймолодшого, від наймолодшого до найстаршого) в одному або кількох стовпцях. Їх можна також сортувати за самостійно створеним настроюваним списком (наприклад, «низький», «середній», «високий») або за форматом, зокрема за кольором клітинки, шрифту чи набором піктограм.

Автофільтр даних

Для того, щоб відфільтрувати дані за визначеним критерієм необхідно виділити дані, які потрібно відфільтрувати. На вкладці Дані обрати Фільтр. В заголовку стовпця натисніть на стрілку та вирішіть, чи потрібно вибрати певні значення або пошуку. За потреби також можна обрати Текстові фільтри  та обрати необхідний варіант для фільтрування. Далі потрібно натиснути кнопку ОК, щоб застосувати фільтр або ж Скасувати, якщо відмінити обраний варіант фільтру.

Рис. Приклад використання Автофільтру

Використання розширеного фільтру

Для того, щоб використати Розширений фільтр, перш за все, потрібно створити додаткову таблицю з критеріями відбору. Заголовки стовпців цієї таблиці мають бути ідентичними основній таблиці (вихідним даним). Тільки після того, як додаткова таблиця створена, можна переходити до запуску Розширеного фільтра. Для цього, переходимо у вкладку Дані, і на стрічці в блоці інструментів Сортування і фільтр, натискаємо на кнопку Додатково. Далі відкривається вікно розширеного фільтра. Як бачимо, існує два режими використання даного інструменту: Фільтрувати список на місці і Скопіювати результати в інше місце. У першому випадку, фільтрація буде проводитися в початковій таблиці, а в другому випадку ‒ окремо в діапазоні осередків, які Ви зазначите самі.

В полі Вихідний діапазон потрібно вказати діапазон комірок вихідної таблиці. Це можна зробити вручну, ввівши діапазон комірок з клавіатури, або виділивши потрібний діапазон за допомогою мишки. В поле Діапазон умов потрібно аналогічним чином вказати діапазон додаткової таблиці, яка містить умову для фільтрування. При цьому, потрібно звернути увагу, щоб в даному діапазоні не було порожніх рядків, інакше нічого не вийде. Після того, як всі налаштування виконані, необхідно натиснути кнопку OK. У вихідній таблиці залишаться лише ті дані, які мали бути відфільтровані відповідно до умови.

Використання зведеної таблиці

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

Для того, щоб побудувати зведену таблицю необхідно її виділити або певний діапазон. На вкладці Вставка потрібно обрати Зведена таблиця, далі з'явиться діалогове вікно Створення зведеної таблиці, в якому буде вказано діапазон або ім'я таблиці.

У розділі Виберіть розташування зведеної таблиці виберіть варіант Новий аркуш або Наявний аркуш. При виборі варіанту Наявний аркуш буде потрібно вказати розташування для вставки зведеної таблиці. Далі необхідно натиснути ОК і буде створено порожню зведену таблицю та виведено список Поля зведеної таблиці.

Робота зі списком полів зведеної таблиці

У верхній частині області поля зведеної таблиці необхідно встановити прапорець для поля, яке необхідно додати до зведеної таблиці.

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

Рис. Список полів зведеної таблиці

Робота з значеннями зведеної таблиці

За замовчуванням поля зведеної таблиці, що розміщуються в область Значення, будуть відображатися у вигляді Суми. Якщо додаток MS Excel інтерпретує дані як текст, то вони будуть відображатися як Кількість. Можна змінити обчислення за замовчуванням, натиснувши стрілку праворуч від імені поля, а потім вибравши параметр Параметри поля значення.

Потім змінити функцію в розділі Операція. Необхідно звернути увагу на те, що при зміні методу обчислення MS Excel автоматично додає його назву в розділ користувача ім'я (наприклад, Сума по полю ім’я_поля), але можна і змінити ім'я. Щоб змінити числовий формат для всього поля, необхідно натиснути Числовий формат.

Додаткові обчислення

Значення можна також виводити в відсотках від значення поля. У наведеному нижче прикладі змінено загальну вартість за товари на % від загальної суми.

Рис. Приклад зміни загальної вартості на % від загальної суми

Можна налаштувати такі параметри в діалоговому вікні Параметри значення поля на вкладці Відображати значення як.

Використання зрізів

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

Для того, щоб створити Зріз (Роздільник) необхідно виділити комірку в будь-якому місці зведеної таблиці. На вкладці Вставка натисніть Зріз. У діалоговому вікні Вставка зрізу встановіть прапорці для полів зведеної таблиці, для яких потрібно створити зріз і далі натисніть ОК. Для кожного обраного поля буде відображений зріз. При необхідності можна налаштувати параметри зрізу на вкладці Параметри → Параметри роздільника.

Щоб очистити фільтри зрізу, необхідно вибрати команду Очистити фільтри або натиснути комбінацію Alt + C.

Групування даних

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

Рис. Групування і скасування групування даних у зведеній таблиці

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

Групування виділених елементів

Утримуючи Ctrl, виділіть кілька значень та натисніть правою кнопкою миші і виберіть команду Групувати.

Присвоєння імені групі

Виберіть групу, далі на вкладці Аналізувати натисніть кнопку Параметри поля. Змініть значення в полі Користувацьке ім'я та натисніть кнопку ОК.

Розгруповування згрупованих даних

Клацніть правою кнопкою миші будь-який елемент в групі та далі виберіть команду Розгрупувати.

Створення часової шкали

Замість того щоб відображати дати за допомогою фільтрів (налаштувавши їх відповідно) є можливість використовувати часову шкалу зведеної таблиці. Це параметр динамічного фільтра, який дає змогу легко фільтрувати дані за датою або часом, а також переходити до потрібного періоду за допомогою повзунка. Щоб додати цю шкалу до аркушу, виберіть Вставлення → Часова шкала. У діалоговому вікні Вставлення часових шкал виберіть необхідні поля дати та натисніть кнопку OK.

Рис. Приклад створення часової шкали

Створення зведеної діаграми

Щоб створити зведену діаграму в MS Excel, спочатку необхідно створити зведену таблицю. Далі виділити комірку у зведеній таблиці та на вкладці Вставка в спадному меню Вставити діаграму обравши будь-який параметр її. Діаграма буде відображена на аркуші. Якщо виділити елемент діаграми в будь-якому місці, на стрічці з'явиться вкладка Конструктор → Додати елемент діаграми. Дана функція дасть можливість змінити або додати будь який елемент діаграми.

Рис. Приклад створення зведеної діаграми