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

При работе с данными в Excel часто встречаются таблицы в виде кросс-таблиц (также известные как сводные или матричные таблицы), где данные организованы в двухмерном формате. Однако многие задачи анализа данных и отчетности требуют, чтобы данные были представлены в нормализованном формате списка (таблица в «длинном формате»). В этой статье объясняются различные эффективные методы преобразования кросс-таблицы в список, что упрощает анализ, фильтрацию и использование данных в базах данных.

Преобразование кросс-таблицы (двухмерной таблицы) в список в Excel

• Используя Power Query
• Используя Kutools для Excel
• Используя формулы

Преобразование кросс-таблицы в список с помощью POWER QUERY

В Excel Power Query является наиболее эффективным способом трансформации данных, особенно при работе с большими наборами данных.

1. Выберите свои данные и перейдите на вкладку «Данные», затем нажмите «Из таблицы/диапазона», см. скриншот:
   
2. В появившемся диалоговом окне «Создать таблицу» нажмите кнопку ОК.
   
3. Затем откроется окно «Редактор Power Query». В этом окне выберите все заголовки столбцов, кроме первого (Категория).
   
4. После этого нажмите «Трансформировать» > «Развернуть столбцы». Power Query создаст два новых столбца: «Атрибут» (ваши предыдущие заголовки столбцов) и «Значение» (значения ячеек).
   
5. Переименуйте эти столбцы соответствующим образом (например, «Месяц» и «Продажи») согласно вашим потребностям.
6. Затем нажмите «Главная» > «Закрыть и загрузить», чтобы вернуть преобразованный список в Excel. Плоский список появится на новом листе.
   

Преобразование кросс-таблицы в список с помощью Kutools для Excel

Хотя Power Query может справиться с этой задачей, Kutools для Excel предоставляет самое быстрое и простое решение всего за несколько кликов. С помощью функции «Преобразовать размер таблицы» в Kutools для Excel можно быстро преобразовать сложные кросс-таблицы в структурированные списки или наоборот, без написания формул или выполнения множества шагов преобразования.

После установки Kutools для Excel выполните следующие шаги:

1. Выберите таблицу, которую нужно преобразовать в список. Затем нажмите «Kutools» > «Диапазон» > «Преобразовать размер таблицы».
   
2. В диалоговом окне «Преобразовать размер таблицы» отметьте опцию «Преобразовать двумерную таблицу в одномерную таблицу», в разделе «Диапазон результатов» выберите ячейку для размещения результата, см. скриншот:
   
3. Наконец, нажмите кнопку ОК, и вы увидите, что исходная таблица преобразована в плоский список.
   

Преобразование кросс-таблицы в список с помощью формул

Если вы еще не уверенно используете Power Query или вам нужен вариант, который работает в старых версиях Excel, эти мощные формулы эффективно преобразуют ваши данные из кросс-таблицы в структурированный формат списка.

Для создания столбца категорий примените следующую формулу:

=INDEX($A$2:$A$4, INT((ROW(A1)-1)/COLUMNS($B$1:$D$1))+1)

Протяните формулу вниз, чтобы получить все элементы категории до тех пор, пока не начнут отображаться ошибочные значения. См. скриншот:

Для создания столбца месяцев примените следующую формулу:

=INDEX($B$1:$D$1, MOD(ROW(A1)-1, COLUMNS($B$1:$D$1))+1)

Автоматически заполните формулу вниз, чтобы заполнить все необходимые строки, убедившись, что диапазон соответствует количеству строк в исходном столбце категории.

Для перечисления всех остальных данных примените следующую формулу:

=INDEX($B$2:$D$4, INT((ROW(A1)-1)/COLUMNS($B$1:$D$1))+1, MOD(ROW(A1)-1, COLUMNS($B$1:$D$1))+1)

Протяните формулу вниз, пока не будут извлечены все данные. См. скриншот:

Заключение

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

Выберите метод, который лучше всего соответствует вашему уровню технических навыков, версии Excel и частоте, с которой вам нужно выполнять это преобразование. Для регулярных отчетов или дашбордов возможность обновления Power Query делает его явным победителем в большинстве сценариев. Если вас интересуют дополнительные советы и хитрости Excel, наш сайт предлагает тысячи учебных материалов, которые помогут вам овладеть Excel.

Связанные статьи:

• Преобразование плоского списка в двумерную кросс-таблицу