Преобразуйте сводную кросс-таблицу в плоский список быстро и точно

Кросс-таблицы также называют сводными, двумерными (2D) или таблицами в «пользовательском» представлении. Они преподносят информацию в сжатой и наглядной матрице с заголовками столбцов и строк. Но такое представление данных не подходят для построения сводных таблиц PivotTable, графиков, фильтрации, экспорта данных в сторонние системы, т.д. Поэтому перед анализом данных так важно аккуратно преобразовать сводные таблицы в «плоский» список.

Инструмент «Редизайн таблицы» точно преобразует сводные таблицы в плоский список без написания макросов:

«Редизайн таблицы» – это один из 20+ инструментов в составе надстройки XLTools для Excel. Работает в Excel 2019, 2016, 2013, 2010, десктоп Office 365.

Скачать XLTools для Excel

1. Нажмите кнопку Редизайн таблицы на вкладке XLTools Откроется диалоговое окно.

2. Выделите сводную таблицу, включая заголовки.

   

   Совет: нажмите на любую ячейку таблицы, и вся таблица будет выделена автоматически.

3. Укажите размер заголовков:

   В простой таблице: Заглавных строк = 1, Заглавных столбцов = 1

4. Укажите, следует ли поместить результат на новый или на существующий лист.

   Чтобы вставить плоский список на существующий лист, укажите начальную ячейку (верхняя левая).

5. Нажмите ОК Готово. Надстройка автоматически подберёт ширину столбцов для плоского списка.

Некоторые сводные таблицы могут иметь сложную структуру и многоуровневые заголовки. Их также можно сделать плоскими с помощью XLTools:

1. Нажмите кнопку Редизайн таблицы на вкладке XLTools Откроется диалоговое окно.
2. Выделите сводную таблицу, включая заголовки.

   

   Совет: нажмите на любую ячейку таблицы, и вся таблица будет выделена автоматически.
3. Укажите размер заголовков:

   • Заглавных строк: число строк, которые составляют заголовок таблицы сверху.
   • Заглавных столбцов: число столбцов, которые составляют заголовок таблицы слева.
4. Укажите, следует ли поместить результат на новый или на существующий лист.
5. Нажмите ОК Готово. Надстройка автоматически подберёт ширину столбцов для плоского списка.

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

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

1. Нажмите кнопку Редизайн таблицы на вкладке XLTools.
2. Выделите сводную таблицу, включая заголовки.
3. Укажите размер заголовков.
4. Отметьте флажком Пропустить пустые значения.
5. Укажите, куда поместить результат.
6. Нажмите ОК Готово.

1. Нажмите кнопку Редизайн таблицы на вкладке XLTools.
2. Выделите сводную таблицу, включая заголовки.
3. Укажите размер заголовков.
4. Отметьте флажком Дублировать значение в объединённых ячейках:

   • Если объединённые ячейки находятся в заголовке: содержимое объединённых ячеек заголовка будет продублировано в каждой соответствующей строке плоского списка.
   • Если в объединённые ячейки находятся в теле таблицы: значения в объединённых ячейках будут продублированы в каждой соответсвующей ячейке плоского списка.
5. Укажите, куда поместить результат.
6. Нажмите ОК Готово.

1. Нажмите кнопку Редизайн таблицы на вкладке XLTools.
2. Выделите сводную таблицу, включая заголовки.
3. Укажите размер заголовков.
4. Отметьте флажком Сохранить заголовки:

   • Где это возможно, надстройка продублирует заголовки из сводной таблицы.
   • Категориям таблицы будет автоматически присвоен заголовок «Категория».
   • Переменным значениям таблицы будет автоматически присвоен заголовок «Значение».
5. Укажите, куда поместить результат.
6. Нажмите ОК Готово.

1. Нажмите кнопку Редизайн таблицы на вкладке XLTools.

2. Выделите сводную таблицу, включая заголовки.

3. Укажите размер заголовков.

4. Отметьте флажком Сохранить формат ячеек:

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

5. Укажите, куда поместить результат.

6. Нажмите ОК Готово.

Внимание: обработка больших таблиц с множеством форматов займёт больше времени.

Редизайн таблицы по сути означает, что данные исходной таблицы копируются и преобразуются так, чтобы сформировать плоский список. Ваши исходные таблицы не изменяются. Чтобы избежать искажения данных, вместо ссылок на ячейки, функций или формул в исходной таблице, в результирующий плоский список надстройка XLTools вставляет их значения.

Термином «Таблица» в Excel часто обозначают разные понятия:

• «Настоящая» таблица — именованный диапазон с применением стиля таблицы (операция «Форматировать как таблицу»). Может быть преобразована в простой диапазон.
• Диапазон — простой диапазон внешне похожий на таблицу, с применением (или без) форматирования цвета фона, границ, т.д. Может быть преобразован в «настоящую» таблицу.
• Сводная таблица (PivotTable) — динамическая таблица, сгенерированная с помощью операции Excel «Сводная таблица» (PivotTable). Ячейки не могут быть отредактированы.

Надстройка XLTools «Редизайн таблицы» позволяет преобразовать в плоский список «настоящие» таблицы и диапазоны. Чтобы произвести редизайн PivotTable, сначала скопируйте диапазон такой таблицы и вставьте значения — это создаст простой диапазон, который далее можно преобразовать.