Перегляд відео - найшвидший спосіб розгортання даних у Excel
Зведені таблиці чудово підходять, коли ви хочете проаналізувати величезну кількість даних за лічені секунди. Він також дозволяє швидко створювати різні види даних, просто перетягуючи їх.
А щоб створити зведену таблицю, потрібно мати дані у певному форматі, готовому до зведеної таблиці.
У багатьох випадках ви, ймовірно, отримаєте дані у форматах, які не підтримують зведену таблицю.
Часто це трапляється, коли хтось збирає дані вручну та створює формат, який буде більш читабельним для людей (а не зведених таблиць).
Щось, як показано нижче:
Наведений вище формат даних - це те, що ви очікуєте отримати як результат аналізу зведеної таблиці.
Що робити, якщо ви хочете проаналізувати ці самі дані та подивитися, які загальні продажі по кожному регіону чи по місяцях.
Хоча це можна легко зробити за допомогою зведених таблиць, на жаль, ви не можете передати вищезгадані дані у зведену таблицю.
Тому вам потрібно розгорнути дані та зробити їх зручними для зведеної таблиці.
Хоча є деякі способи зробити це за допомогою формули Excel або VBA, Power Query (Отримати та перетворити в Excel 2016) є найкращим інструментом для розгортання даних.
Розгортання даних за допомогою Power Query
Ось кроки для розгортання даних за допомогою Power Query:
(Якщо ваші дані вже є в таблиці Excel, почніть з кроку 6 і далі)
- Виберіть будь -яку клітинку в наборі даних.
- Перейдіть на вкладку Вставка.
- Натисніть на значок таблиці.
- У діалоговому вікні "Створити таблицю" переконайтеся, що діапазон правильний. При необхідності можна змінити діапазон.
- Натисніть OK. Це перетворить ваші табличні дані в таблицю Excel.
- Якщо будь -яка клітинка вибрана в таблиці Excel, натисніть вкладку Дані.
- У групі даних «Отримати та перетворити» натисніть піктограму «З таблиці/діапазону».
- У діалоговому вікні «Створити таблицю», що відкриється (якщо воно відкриється), натисніть «OK». Відкриється редактор запитів за допомогою даних таблиці Excel.
- У редакторі запитів клацніть правою кнопкою миші стовпець Регіон.
- Натисніть на опцію «Розгорнути інші стовпці». Це миттєво розкриє ваші дані.
- Змініть назву стовпця "Атрибут" на більш значущу назву, наприклад "Місяці".
- Коли у вас є дані Unpivoted, рекомендується переконатися, що всі типи даних правильні. У цьому прикладі клацніть по одній клітинці для кожного стовпця і перегляньте тип даних на вкладці «Перетворення». При необхідності можна також змінити тип даних.
- (Необов’язково) Змініть назву вашого запиту на „Продажі”.
- Перейдіть на вкладку Домашня сторінка (у редакторі запитів).
- Натисніть кнопку Закрити та завантажити.
Вищеописані кроки розкривають ваш набір даних за допомогою Power Query і повертають його в Excel як таблицю на новому аркуші.
Тепер ви можете використовувати ці дані для створення різних представлень за допомогою зведеної таблиці. Наприклад, ви можете перевірити загальну вартість продажу за місяцями або регіонами.
Оновлення запиту при додаванні нових даних
Все це працює нормально.
Але що відбувається, коли до нашого вихідного набору даних додаються нові дані.
Скажімо, ви отримаєте дані за липень у тому ж форматі, що і з того, з якого ми почали.
Чи потрібно мені повторювати всі кроки ще раз, щоб включити ці дані до мого набору даних без змін?
Відповідь - НІ.
Ось що є таким чудовим у Power Query. Ви можете продовжувати додавати нові дані (або змінювати наявні), і Power Query буде оновлювати їх миттєво, як тільки ви оновите їх.
Дозвольте мені показати вам, як.
Припустимо, нижче мій новий набір даних (який містить додаткові дані за липень):
Ось кроки, щоб оновити вже створений запит та розкрутити ці дані:
- Додайте ці нові дані до вихідних даних, які ви використовували для створення запиту.
- Оскільки ви додаєте дані до сусіднього стовпця таблиці Excel, таблиця Excel розширюватиметься, щоб включити ці дані до неї. Якщо це не випадково, зробіть це вручну, перетягнувши маленьку перевернуту піктограму «L» у нижньому правому куті таблиці Excel.
- Перейдіть на вкладку "Дані" та натисніть "Запити та з'єднання". Це покаже панель із усіма існуючими запитами.
- Клацніть правою кнопкою миші на запиті "Продажі" на панелі "Запити".
- Натисніть Оновити.
Це воно! Ваші нові дані миттєво розкриваються і додаються до наявних.
Ви помітили б, що кількість рядків, показаних у оновленні запиту, показує вам нові числа. У цьому прикладі до оновлення було 24, а після оновлення - 28.
Це також означає, що якщо ви створили зведені таблиці з використанням даних, отриманих із Power Query, ці зведені таблиці також оновляться, щоб показати вам оновлені результати.
