Как преобразовать матричную таблицу в три столбца в Excel?
При работе с данными в Excel вы часто сталкиваетесь с матричными таблицами, где информация организована в виде сетки, а строки и столбцы одновременно выполняют роль заголовков. Хотя такой формат удобен для определённых видов анализа, нередко возникает необходимость преобразовать матрицу в «список» — то есть в таблицу из трёх столбцов. Это требуется, например, для импорта данных в базу, их нормализации, построения диаграмм или проведения углублённого анализа. Преобразование матрицы в трёхстолбцовую структуру (иногда называемое «разворачиванием» данных) значительно упрощает фильтрацию, агрегирование и интеграцию с другими аналитическими инструментами. Ниже приведён пример такого преобразования:
➤ Преобразование таблицы в матричном формате в список с помощью сводной таблицы
➤ Преобразование таблицы в матричном формате в список с помощью кода VBA
➤ Преобразование таблицы в матричном формате в список с помощью Kutools для Excel
➤ Преобразование таблицы в матричном формате в список с помощью формул Excel
Преобразование таблицы в виде матрицы в список с помощью сводной таблицы
В Excel нет встроенной команды для прямого преобразования сводной (матричной) таблицы в формат «три столбца». Однако с помощью Мастера сводных таблиц можно эффективно преобразовать перекрёстную таблицу в плоские табличные данные, пригодные для дальнейшего анализа. Этот метод отлично подходит для небольших и средних наборов данных и особенно полезен при упрощении сложной структуры отчёта. В то же время он менее удобен для работы с большими объёмами данных или для пользователей, не имеющих опыта использования сводных таблиц.
1. Откройте лист с вашей матрицей. Нажмите Alt + D, затем P, чтобы открыть Мастер сводных таблиц и сводных диаграмм. В мастере:
- В разделе Где находятся данные, которые вы хотите проанализировать выберите Несколько диапазонов консолидации.
- В разделе Какой отчёт вы хотите создать выберите Сводная таблица.
2. Нажмите Далее. В диалоговом окне Шаг 2a из 3 выберите Я сам(а) создам поля страниц:
3. Нажмите Далее. В окне Шаг 2b из 3 нажмите кнопку 
и выберите полный диапазон данных матрицы, включая заголовки строк и столбцов. Нажмите Добавить, чтобы вставить диапазон в список Все диапазоны. Убедитесь, что выбранный диапазон охватывает всю матрицу.
4. Нажмите Далее. В окне Шаг 3 из 3 выберите, где разместить сводную таблицу — на новом листе или в определённой ячейке:
5. Нажмите Готово. Excel создаст сводную таблицу, обобщающую матрицу. По умолчанию она отображает итоговые значения на пересечениях. Для этой задачи настраивать структуру сводной таблицы не требуется — просто перейдите к следующему шагу:
6. Дважды щёлкните ячейку, где пересекаются строка и столбец Общий итог (например, ячейку F22). После этого Excel создаст новый лист с тремя столбцами, где каждая строка будет содержать уникальную комбинацию метки строки, метки столбца и соответствующего значения.
7. Чтобы завершить процесс, выделите новую таблицу, щёлкните правой кнопкой мыши и выберите Таблица > Преобразовать в диапазон. Это удалит форматирование таблицы, оставив обычный редактируемый список:
Совет: Если ваша матрица часто изменяется, вам придётся повторять эту процедуру для обновления трёхСтолбцы. Данный метод лучше всего подходит для статичных данных. Кроме того, если матрица содержит пустые ячейки или объединённые ячейки, перед использованием этого метода может потребоваться предварительная очистка данных.
Преобразование таблицы в виде матрицы в список с помощью кода VBA
Если вы предпочитаете автоматизацию или планируете многократно применять это преобразование, макрос VBA мгновенно превратит любую таблицу в виде матрицы в структурированную трёхстолбцовую. Этот метод особенно эффективен для крупных наборов данных и разнообразных макетов — он полностью избавляет от необходимости ручного форматирования. Идеальное решение для пользователей, уже знакомых с запуском скриптов VBA.
1. Нажмите Alt + F11, чтобы открыть редактор Microsoft Visual Basic for Applications.
2. В редакторе нажмите Вставка > Модуль, чтобы создать новый модуль. Затем вставьте следующий код в окно модуля:
📜 Код VBA: Преобразование матрицы в список
Sub ConvertTable()
' Updated by Extendoffice
Dim Rng As Range
Dim cRng As Range
Dim rRng As Range
Dim xOutRng As Range
xTitleId = "KutoolsforExcel"
Set cRng = Application.InputBox("Select your Column labels", xTitleId, Type:=8)
Set rRng = Application.InputBox("Select Your Row Labels", xTitleId, Type:=8)
Set Rng = Application.InputBox("Select your data", xTitleId, Type:=8)
Set outRng = Application.InputBox("Out put to (single cell):", xTitleId, Type:=8)
Set xWs = Rng.Worksheet
k = 1
xColumns = rRng.Column
xRow = cRng.Row
For i = Rng.Rows(1).Row To Rng.Rows(1).Row + Rng.Rows.Count - 1
For j = Rng.Columns(1).Column To Rng.Columns(1).Column + Rng.Columns.Count - 1
outRng.Cells(k, 1) = xWs.Cells(i, xColumns)
outRng.Cells(k, 2) = xWs.Cells(xRow, j)
outRng.Cells(k, 3) = xWs.Cells(i, j)
k = k + 1
Next j
Next i
End Sub3. Нажмите F5 или выберите Выполнить, чтобы запустить макрос. Серия запросов поможет вам выполнить необходимые действия:
Шаг 1:Выберите метки столбцов(обычно это Верхняя строка вашей матрицы):
Шаг 2:Выберите метки строк(обычно это первый столбец вашей матрицы):
Шаг 3:Выберите фактический диапазон матрицы Диапазон данных(исключая Заголовки строк и столбцов):
Шаг 4: Выберите ячейку вывода, с которой должен начинаться преобразованный трёхстолбцовый диапазон. Рекомендуется использовать пустую ячейку или новый лист:
Шаг 5: Нажмите ОК. Ваша матрица будет преобразована в плоскую таблицу из трёх столбцов.
⚠️ Примечания и советы:
• Убедитесь, что в диапазон матрицы «Диапазон данных» не включены заголовки столбцов или строк.
• Если в вашей матрице есть объединённые ячейки, разъедините их перед запуском макроса, чтобы избежать ошибок.
• В случае возникновения ошибок дважды проверьте выбранные диапазоны и убедитесь, что они правильно выровнены.
Преобразуйте таблицу в матричном формате в список с помощью Kutools для Excel
Хотя описанные выше методы эффективны, они могут показаться утомительными или сложными для менее опытных пользователей. Если вы ищете быстрое и удобное решение, Kutools для Excel предлагает специализированный инструмент под названием Преобразование размера таблицы, разработанный специально для этой задачи.
Этот инструмент идеально подходит для пользователей, часто преобразующих таблицы в матричный формат или выполняющих пакетную обработку. При необходимости он сохраняет исходное форматирование — например, шрифт, цвет заливки и формулы. Единственный недостаток в том, что Kutools — это сторонняя надстройка, требующая установки, однако она представляет собой мощное решение для всех, кто регулярно работает с преобразованием данных в Excel.
Шаги:
1. После установки Kutools перейдите на вкладку Kutools, нажмите Диапазон и выберите Преобразование размера таблицы:
2. В диалоговом окне Преобразование размера таблицы:
- (1) В разделе Тип преобразования выберите Преобразовать двумерную таблицу в одномерную таблицу.
- (2)Нажмите кнопку
рядом с полем Исходный диапазон, чтобы выбрать матричную таблицу. - (3) Нажмите кнопку рядом с полем Диапазон результатов, чтобы указать место размещения выходных данных.
Убедитесь, что выделили всю матрицу целиком — включая заголовки и данные, — чтобы избежать частичного преобразования или ошибочных результатов.
3. Нажмите ОК. Матрица мгновенно преобразуется в три столбца, а исходное форматирование ячеек будет сохранено по возможности:
Совет: Эта функция также поддерживает обратную операцию — преобразование плоского списка в двумерную матрицу. Это особенно полезно при восстановлении отчётов или подготовке данных для кросс-табличного анализа. Подробнее: как преобразовать список в двумерную Двумерная таблица..
➤ Подробнее об инструменте Преобразование размера таблицы
⏬ Скачайте и бесплатно протестируйте Kutools для Excel прямо сейчас!
Преобразование таблицы в матричном формате в список с помощью формул Excel
Если вы предпочитаете формульный подход — особенно полезный, когда ваша трёхстолбцовая таблица должна автоматически обновляться при изменении исходной матрицы, — можно использовать комбинацию функций ИНДЕКС, СТРОКА, СТОЛБЕЦ и СЧЁТЗ, чтобы вручную развернуть данные. Это решение не требует VBA или надстроек и идеально подходит тем, кто хочет избежать макросов и внешних инструментов. Однако оно требует особого внимания к ссылкам в формулах: их обычно нужно вводить как массив или последовательно протягивать вниз и вправо. Такой метод наиболее эффективен для матриц умеренного размера и ситуаций, когда итоговый список должен оставаться динамическим и мгновенно реагировать на изменения в исходных данных.
Предположим, что Ваша Диапазон данных выглядит следующим образом:
- Метки строк находятся в ячейках A2:A10.
- Метки столбцов находятся в ячейках B1:J1.
- Значения матрицы находятся в ячейках B2:J10.
1. Создайте новый лист или начните работу в пустой области существующего листа. В ячейке L2 введите следующую формулу для извлечения метки строки:
=INDEX($A$2:$A$10,INT((ROW(A1)-1)/COUNTA($B$1:$J$1))+1)2. В ячейке M2 введите эту формулу, чтобы извлечь соответствующую метку столбца:
=INDEX($B$1:$J$1,MOD(ROW(A1)-1,COUNTA($B$1:$J$1))+1)3. В ячейке N2 извлеките значение из матрицы с помощью:
=INDEX($B$2:$J$10,INT((ROW(A1)-1)/COUNTA($B$1:$J$1))+1,MOD(ROW(A1)-1,COUNTA($B$1:$J$1))+1)4. Выделите ячейки L2:N2 и протяните маркер заполнения вниз до строки с номером количество строк × количество столбцов (в данном примере — 9 строк × 9 столбцов = всего 81 строка).
✅ Советы:
- Настройте все диапазоны в соответствии с реальной структурой ваших данных.
- Используйте
ЕСЛИиПУСТО, чтобы при необходимости отфильтровать пустые строки. - Чтобы автоматически расширять диапазон, используйте функцию
СМЕЩили динамические именованные диапазоны. - Этот метод наиболее практичен при фиксированном размере матрицы и умерённом объёме данных.
ℹ️ Дополнительные примечания:
- Преимущества: Формулы остаются активными и автоматически отражают изменения в матрице. Не требуются VBA или надстройки.
- Недостатки: Может замедлять работу книги при использовании с большими матрицами. Требует тщательной настройки.
Демонстрация: Преобразование таблицы в матричном формате в список с помощью Kutools для 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-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек