Преобразование перекрестной (двумерной) таблицы в плоский список в Excel: полное руководство
При работе с данными в Excel часто встречаются перекрёстные таблицы (также известные как сводные или матричные таблицы), где информация представлена в двумерном формате. Однако многие задачи анализа и составления отчётов требуют нормализованного списка — то есть таблицы в «длинном формате». В этой статье описаны эффективные методы преобразования перекрёстной таблицы в такой список, что значительно упрощает анализ, фильтрацию и дальнейшее использование данных в базах.
Преобразование перекрестной таблицы (двумерной таблицы) в список в Excel
Преобразование перекрестной таблицы в список с помощью POWER QUERY
В Excel Power Query — это самый эффективный способ преобразования данных, особенно при работе с большими наборами.
- Выделите свои данные, перейдите на вкладку «Данные» и нажмите «Из таблицы/диапазона» (см. снимок экрана).
- В появившемся диалоговом окне «Создание таблицы» нажмите кнопку «ОК».
- Затем откроется окно «Редактор Power Query». В нём выделите все заголовки столбцов, кроме первого — «Категория».
- Затем выберите «Преобразование» > «Разворот столбцов». Power Query создаст два новых столбца: «Атрибут» (содержащий прежние заголовки столбцов) и «Значение» (содержащий значения ячеек).
- Переименуйте эти столбцы так, как вам нужно (например, «Месяц» и «Продажи»).
- Затем нажмите «Главная» > «Закрыть и загрузить», чтобы вернуть преобразованный список в Excel — плоский список появится на новом листе.
Преобразование перекрестной таблицы в список с помощью Kutools для Excel
Хотя Power Query позволяет справиться с этой задачей, Kutools для Excel предлагает самое быстрое и простое решение — всего в несколько кликов. С помощью функции «Преобразование размера таблицы» от Kutools для Excel вы легко превратите сложные перекрёстные таблицы в структурированные списки или выполните обратное преобразование — без формул и многочисленных шагов.
После установки Kutools для Excel выполните следующие действия:
- Выделите таблицу, которую нужно преобразовать в список, и нажмите «Kutools» > «Диапазон» > «Преобразование размера таблицы».
- В диалоговом окне «Преобразование размера таблицы» установите флажок «Преобразовать двумерную таблицу в одномерную», затем в разделе «Диапазон результатов» укажите ячейку, в которую следует поместить результат (см. снимок экрана).
- Наконец, нажмите кнопку «ОК» — и исходная таблица преобразуется в плоский список.
Преобразование перекрестной таблицы в список с помощью формул
Если вы пока не готовы использовать 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.
Связанные статьи:
Лучшие инструменты повышения продуктивности в Office
Раскройте весь потенциал Excel с помощью Kutools для Excel и ощутите эффективность как никогда раньше.Kutools для Excel предлагает более 300 расширенных функций для повышения продуктивности и Экономия времени.Нажмите здесь, чтобы получить нужную Вам функцию…
Office Tab добавляет в Office вкладки и значительно упрощает Вашу работу
- Включите редактирование и чтение во вкладках в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна — вместо того чтобы использовать отдельные окна.
- Повышает вашу продуктивность на 50 % и экономит сотни кликов мышью каждый день!
Все надстройки Kutools — один установщик
Kutools for Office — набор надстроек для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro, идеально подходящий командам, работающим с разными приложениями Office.
- Единый комплект— надстройки для Excel, Word, Outlook и PowerPoint + Office Tab Pro
- Один установщик, одна лицензия— настройка занимает считанные минуты (готово для MSI)
- Лучше работать вместе— оптимизированная продуктивность во всех приложениях Office
- 30-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек