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

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

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

  • Редизайн сводной таблицы в список в секунды
  • Преобразование сложных таблиц с многоуровневыми заголовками
  • Корректный редизайн таблиц с объединёнными или пустыми ячейками
  • Сохранение заголовков столбцов
  • Сохранение форматирования ячеек
(Внимание: видео может не отражать последние обновления. Используйте инструкцию ниже.)

Как преобразовать сводную таблицу 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. Нажмите ОК Готово.
Внимание:обработка больших таблиц с множеством форматов займёт больше времени.
Преобразование сводных таблиц Excel: сохранить формат ячеек в плоском списке

Какие таблицы обрабатывает надстройка «Редизайн таблицы»

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

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

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

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

Any questions or suggestions?
close window
How can we help?