Редизайн таблицы: быстрое преобразование сводных таблиц в плоский список в Excel

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

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

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

Язык видео: английский. Субтитры: русский, английский. (Внимание: видео может не отражать последние обновления. Используйте инструкцию ниже.)

 

Добавить «Редизайн таблицы» в Excel 2016, 2013, 2010, 2007

Подходит для: Microsoft Excel 2016 – 2007, desktop Office 365 (32-бит и 64-бит).

Скачать надстройку XLTools

Кнопка Редизайн Excel таблиц

 

Как работать с надстройкой:

 

Как преобразовать сводную таблицу 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, сначала скопируйте диапазон такой таблицы и вставьте значения — это создаст простой диапазон, который далее можно преобразовать.

 

 

Появились вопросы или предложения? Оставьте комментарий ниже.

 

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *