✅ Подготовка исходных данных для сводной таблицы

Содержание

Каков хороший дизайн исходных данных для сводной таблицы?
Распространенные ошибки, которых следует избегать при подготовке исходных данных
Примеры плохого дизайна исходных данных
[ПРИМЕР ИЗУЧЕНИЯ] Преобразование плохо отформатированных данных в исходные данные, готовые к сводной таблице

Наличие данных в правильном формате - решающий шаг в создании надежной и безошибочной сводной таблицы. Если все сделать неправильно, у вас может возникнуть множество проблем со сводной таблицей.

Каков хороший дизайн исходных данных для сводной таблицы?

Давайте посмотрим на пример хороших исходных данных для сводной таблицы.

Вот что делает его хорошим дизайном исходных данных:

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

Распространенные ошибки, которых следует избегать при подготовке исходных данных

В исходных данных не должно быть пустых столбцов. Это легко заметить. Если у вас есть пустой столбец в исходных данных, вы не сможете создать сводную таблицу. Он покажет ошибку, как показано ниже.
В исходных данных не должно быть пустых ячеек / строк. Несмотря на то, что вы можете успешно создать сводную таблицу, несмотря на наличие пустых ячеек или строк, существует множество побочных эффектов, которые могут укусить вас позже в тот же день.
- Например, предположим, что у вас есть пустая ячейка в столбце продаж. Если вы создадите сводную таблицу, используя эти данные, и поместите поле продаж в область столбцов, она покажет вам COUNT, а не SUM. Это потому, что Excel интерпретирует весь столбец как содержащий текстовые данные (только из-за одной пустой ячейки).
Примените соответствующий формат к ячейкам в исходных данных. Например, если у вас есть даты (которые хранятся как серийные номера в бэкэнде в Excel), примените один из допустимых форматов даты. Это поможет вам создать сводную таблицу и использовать дату в качестве одного из критериев для суммирования, группировки и сортировки данных.
- Если у вас есть пара секунд, попробуйте это. Отформатируйте даты в сводной таблице как числа, а затем создайте сводную таблицу, используя эти данные. Теперь в сводной таблице выберите поле даты и посмотрите, что произойдет. Он автоматически поместит его в область значений. Это потому, что ваша сводная таблица не знает, что это даты. Он интерпретирует их как числа.
Не включайте итоги по столбцам, итоги по строкам, средние значения и т. Д. Как часть исходных данных. Если у вас есть сводная таблица, вы можете легко получить ее позже.
Всегда создавайте таблицу Excel, а затем используйте ее в качестве источника для сводной таблицы. Это скорее хорошая практика, а не ловушка. Ваша сводная таблица будет отлично работать с исходными данными, которые также не являются таблицей Excel. Преимущество таблицы Excel в том, что она может корректировать расширяющиеся данные. Если вы добавите больше строк в набор данных, вам не нужно будет снова и снова корректировать исходные данные. Вы можете просто обновить сводную таблицу, и она автоматически учтет новые строки, добавленные к исходным данным.

Примеры плохого дизайна исходных данных

Давайте посмотрим на несколько плохих примеров дизайна исходных данных.

Плохой дизайн исходных данных - пример 1

Это распространенный способ поддерживать данные, поскольку за ними легко следить и понимать. При таком расположении данных есть две проблемы:

Вы не получаете полной картины. Например, вы можете увидеть, что продажи для Среднего Запада в 1 квартале составляют 2924300. Но это одна продажа или несколько продаж. Если у вас есть каждая запись в отдельной строке, вы можете сделать анализ лучше.
Если вы продолжите и создадите сводную таблицу, используя это (что вы можете), вы получите разные поля для разных кварталов. Что-то вроде того, что показано ниже:

Плохой дизайн исходных данных - пример 2

Такое представление данных может быть хорошо воспринято руководством и аудиторией презентаций PowerPoint, но оно не подходит для создания сводной таблицы.

Опять же, это своего рода сводка, которую вы можете легко создать с помощью сводной таблицы. Поэтому, даже если вы в конечном итоге захотите такой вид ваших данных, сохраняйте исходные данные в формате Pivot Ready и создайте это представление с помощью сводной таблицы.

Плохой дизайн исходных данных - пример 3

Это снова результат, который можно легко получить с помощью сводной таблицы. Но его нельзя использовать для создания сводной таблицы.

В наборе данных есть пустые ячейки, а кварталы распределены как заголовки столбцов.

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

[ПРИМЕР ИЗУЧЕНИЯ] Преобразование плохо отформатированных данных в исходные данные, готовые к сводной таблице

Иногда вы можете получить набор данных, который не подходит для использования в качестве исходных данных для сводной таблицы. В таком случае у вас может не быть другого выбора, кроме как преобразовать данные в формат данных, удобный для Pivot.

Вот пример плохого дизайна данных:

Теперь вы можете использовать функции Excel или сводный запрос для преобразования этих данных в формат, который можно использовать в качестве исходных данных для сводной таблицы.

Давайте посмотрим, как работают оба этих метода.

Метод 1. Использование формул Excel

Давайте посмотрим, как использовать функции Excel для преобразования этих данных в готовый формат сводной таблицы.

Создайте уникальный заголовок столбца для всех категорий в исходном наборе данных. В этом примере это будут «Регион», «Квартал» и «Продажи».
В ячейке под заголовком региона используйте следующую формулу: = ИНДЕКС ($ A $ 2: $ A $ 5, ROUNDUP (ROWS ($ A $ 2: A2) / COUNTA ($ B $ 1: $ E $ 1), 0))
- Перетащите формулу вниз, и она повторит все регионы.
В ячейке под заголовком квартала используйте следующую формулу: = ИНДЕКС ($ B $ 1: $ E $ 1, ROUNDUP (MOD (ROWS ($ A $ 2: A2), COUNTA ($ B $ 1: $ E $ 1) +0,1)) , 0))
- Перетащите формулу вниз, и она повторится во всех четвертях.
В заголовке ниже Продажи используйте следующую формулу: = ИНДЕКС ($ B $ 2: $ E $ 5, ПОИСКПОЗ (G2, $ A $ 2: $ A $ 5,0), ПОИСКПОЗ (H2, $ B $ 1: $ E $ 1,0). ))
- Перетащите его вниз, чтобы получить все значения. Эта формула использует данные региона и квартала в качестве значений поиска и возвращает значение продаж из исходного набора данных.

Теперь вы можете использовать полученные данные в качестве исходных данных для сводной таблицы.

Щелкните здесь, чтобы загрузить файл примера.

Метод 2: использование Power Query

В Power Query есть функция, с помощью которой можно легко преобразовать такие данные в формат данных, готовый к Pivot.

Если вы используете Excel 2016, функции Power Query будут доступны на вкладке «Данные» в группе «Получить и преобразовать». Если вы используете Excel 2013 или более ранние версии, вы можете использовать его как надстройку.

Вот отличное руководство по установке Power Query от Джона из Excel Campus.

Опять же, учитывая, что вы отформатировали данные, как показано ниже:

Вот шаги, чтобы преобразовать исходные данные в готовый формат сводной таблицы:

Преобразуйте данные в таблицу Excel. Выберите набор данных и перейдите в Вставить -> Таблицы -> Таблица.
В диалоговом окне «Вставить таблицу» убедитесь, что выбран правильный диапазон, и нажмите «ОК». Это преобразует табличные данные в таблицу Excel.
В Excel 2016 перейдите в Данные -> Получить и преобразовать -> Из таблицы.
- Если вы используете надстройку Power Query в предыдущей версии, перейдите в Power Query -> Внешние данные -> Из таблицы.
В редакторе запросов выберите столбцы, которые нужно развернуть. В данном случае это те, которые рассчитаны на четыре четверти. Чтобы выбрать все столбцы, удерживайте клавишу Shift, а затем выберите первый столбец, а затем последний столбец.
В редакторе запросов перейдите в Transform -> Any Column -> Unpivot Columns. Это преобразует данные столбца в формат, удобный для сводной таблицы.
Power Query дает столбцам общие имена. Измените эти имена на те, которые вам нужны. В этом случае измените Атрибут на Квартал и Стоимость на Продажи.
В редакторе запросов перейдите в Файл -> Закрыть и загрузить. Это закроет диалоговое окно редактора Power Query и создаст отдельный рабочий лист, который будет содержать данные с несведенными столбцами.