✅ Підготовка вихідних даних для зведеної таблиці

Зміст

Який хороший дизайн вихідних даних для зведеної таблиці?
Поширені підводні камені, яких слід уникати під час підготовки вихідних даних
Приклади поганого дизайну вихідних даних
[ДОСЛІДЖЕННЯ СЛУЖБИ] Перетворення погано відформатованих даних у вихідні дані, готові до зведеної таблиці

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

Який хороший дизайн вихідних даних для зведеної таблиці?

Давайте розглянемо приклад хороших вихідних даних для зведеної таблиці.

Ось що робить його хорошим дизайном вихідних даних:

Перший рядок містить заголовки, які описують дані у стовпцях.
Кожен стовпець представляє унікальну категорію даних. Наприклад, у стовпці С містяться лише дані про продукт, а у стовпці D і лише дані за місяць.
Кожен рядок є записом, який представляв би один екземпляр угоди або продажу.
Заголовки даних унікальні і не повторюються ніде в наборі даних. Наприклад, якщо у вас є номери продажів протягом чотирьох кварталів на рік, ви НЕ повинні називати всі ці пункти продажами. Замість цього дайте цим заголовкам стовпців унікальні назви, такі як Sales Q1, Sales Q2 тощо.
- Якщо у вас немає унікальних заголовків, ви можете продовжити створення зведеної таблиці, і Excel автоматично зробить їх унікальними, додавши суфікс (наприклад, Sales, Sales2, Sales3). Однак це був би жахливий спосіб підготувати та використовувати зведену таблицю.

Поширені підводні камені, яких слід уникати під час підготовки вихідних даних

У вихідних даних не повинно бути порожніх стовпців. Цей легко помітити. Якщо у вихідних даних є порожній стовпець, ви не зможете створити зведену таблицю. Він покаже помилку, як показано нижче.
У вихідних даних не повинно бути порожніх клітинок/рядків. Хоча ви можете успішно створити зведену таблицю, незважаючи на наявність порожніх клітинок або рядків, є багато побічних ефектів, які можуть з’явитися у вас пізніше протягом дня.
- Наприклад, припустимо, у вас є порожня клітинка у стовпці продажів. Якщо ви створите зведену таблицю з використанням цих даних і помістите поле продажів у область стовпців, вона покаже вам COUNT, а не SUM. Це тому, що Excel інтерпретує весь стовпець як текстові дані (лише через одну порожню клітинку).
Застосуйте відповідний формат до клітинок у вихідних даних. Наприклад, якщо у вас є дати (які зберігаються як серійні номери в бекенді в Excel), застосуйте один із прийнятних форматів дати. Це допоможе вам створити зведену таблицю та використовувати Дата як один із критеріїв для узагальнення, групування та сортування даних.
- Якщо у вас є пару секунд, спробуйте це. Відформатуйте дати у зведеній таблиці як числа, а потім створіть зведену таблицю, використовуючи ці дані. Тепер у зведеній таблиці виберіть поле дати та подивіться, що станеться. Він автоматично помістить його в область значень. Це тому, що ваша зведена таблиця не знає, що це дати. Він інтерпретує це як числа.
Не включайте будь -які підсумки стовпців, підсумки рядків, середні показники тощо як частину вихідних даних. Після того як у вас є зведена таблиця, ви можете легко отримати їх пізніше.
Завжди створюйте таблицю Excel, а потім використовуйте її як джерело зведеної таблиці. Це скоріше хороша практика, а не підводний камінь. Ваша зведена таблиця буде чудово працювати з вихідними даними, які також не є таблицею Excel. Перевага Excel Table полягає в тому, що вона може регулювати дані, що розширюються. Якщо ви додаєте більше рядків до набору даних, вам не потрібно налаштовувати вихідні дані знову і знову. Ви можете просто оновити зведену таблицю, і вона автоматично враховуватиме нові рядки, додані до вихідних даних.

Приклади поганого дизайну вихідних даних

Давайте розглянемо деякі погані приклади дизайну вихідних даних.

Неправильний дизайн вихідних даних - Приклад 1

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

Ви не можете отримати повну картину. Наприклад, ви можете побачити, що продажі на Середньому Заході у першому кварталі складають 2924300. Але це одиничний продаж чи ряд продажів. Якщо у вас кожен запис доступний в окремому рядку, ви можете зробити кращий аналіз.
Якщо ви продовжите та створите зведену таблицю, використовуючи це (що ви можете), ви отримаєте різні поля для різних кварталів. Щось, як показано нижче:

Неправильний дизайн вихідних даних - Приклад 2

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

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

Неправильний дизайн вихідних даних - Приклад 3

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

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

Крім того, регіон вказується вгорі, при цьому він повинен бути частиною кожного запису.

[ДОСЛІДЖЕННЯ СЛУЖБИ] Перетворення погано відформатованих даних у вихідні дані, готові до зведеної таблиці

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

Ось приклад поганого дизайну даних:

Тепер ви можете використовувати функції Excel або зведений запит, щоб перетворити ці дані у формат, який можна використовувати як вихідні дані для зведеної таблиці.

Подивимось, як працюють обидва ці способи.

Спосіб 1: Використання формул Excel

Давайте подивимося, як за допомогою функцій Excel конвертувати ці дані у формат, готовий до зведеної таблиці.

Створіть унікальний заголовок стовпця для всіх категорій вихідного набору даних. У цьому прикладі це буде Регіон, Квартал та Продажі.
У клітинці під заголовком регіону скористайтеся такою формулою: = INDEX ($ A $ 2: $ A $ 5, ROUNDUP (ROWS ($ A $ 2: A2)/COUNTA ($ B $ 1: $ E $ 1), 0))
- Перетягніть формулу вниз, і вона повторить усі регіони.
У клітинці під заголовком чверті використовуйте таку формулу: = INDEX ($ B $ 1: $ E $ 1, ROUNDUP (MOD (ROWS ($ A $ 2: A2), COUNTA ($ B $ 1: $ E $ 1) +0,1)) , 0))
- Перетягніть формулу вниз, і вона повторить усі чверті.
У заголовку нижче Продажі використовуйте таку формулу: = INDEX ($ B $ 2: $ E $ 5, MATCH (G2, $ A $ 2: $ A $ 5,0), MATCH (H2, $ B $ 1: $ E $ 1,0 ))
- Перетягніть його вниз, щоб отримати всі значення. Ця формула використовує дані регіону та кварталу як значення пошуку та повертає вартість продажів із вихідного набору даних.

Тепер ви можете використовувати ці отримані дані як вихідні дані для зведеної таблиці.

Натисніть тут, щоб завантажити файл прикладу.

Спосіб 2: Використання Power Query

Power Query має функцію, яка легко перетворює дані такого типу у формат даних, готовий для роботи з Pivot.

Якщо ви використовуєте Excel 2016, функції Power Query будуть доступні на вкладці Дані в групі Отримати та перетворити. Якщо ви використовуєте Excel 2013 або попередні версії, ви можете використовувати його як надбудову.

Ось чудовий посібник із встановлення Power Query Джона з Excel Campus.

Знову ж таки, враховуючи, що у вас дані відформатовані, як показано нижче:

Ось кроки для перетворення вихідних даних у формат, готовий до зведеної таблиці:

Перетворіть дані в таблицю Excel. Виберіть набір даних і перейдіть до пункту Вставка -> Таблиці -> Таблиця.
У діалоговому вікні Вставити таблицю переконайтеся, що вибрано правильний діапазон, і натисніть OK. Це перетворить табличні дані в таблицю Excel.
У Excel 2016 перейдіть до Дані -> Отримати та перетворити -> З таблиці.
- Якщо ви використовуєте надбудову Power Query у попередній версії, перейдіть до Power Query -> Зовнішні дані -> З таблиці.
У редакторі запитів виберіть стовпці, які потрібно розгорнути. У цьому випадку це чотири квартали. Щоб вибрати всі стовпці, утримуйте клавішу Shift, а потім виберіть перший стовпець, а потім останній.
У Редакторі запитів перейдіть до Трансформувати -> Будь -який стовпець -> Розгортати стовпці. Це перетворить дані стовпця у формат, зручний для зведеної таблиці.
Power Query дає загальні назви стовпцям. Змініть ці назви на потрібні. У цьому випадку змініть Атрибут на Квартал і Значення на Продажі.
У редакторі запитів перейдіть до Файл -> Закрити та завантажити. Це закриє діалогове вікно Power Query Editor і створить окремий робочий аркуш, у якому будуть дані з неперевершеними стовпцями.