Как преобразовать таблицу в стиле матрицы в три столбца в Excel?
При работе с данными в Excel вы можете часто сталкиваться с таблицами в стиле матрицы, которые представляют информацию в виде сетки, где строки и столбцы служат заголовками. Хотя этот формат визуально полезен для некоторых видов анализа, вам может потребоваться преобразовать эту матрицу в «список» или таблицу из трех столбцов для задач, таких как импорт базы данных, нормализация данных, построение диаграмм или углубленный анализ. Преобразование матрицы в список из трех столбцов (иногда называемый «расплющенные данные») позволяет легче фильтровать, агрегировать и интегрировать с другими инструментами работы с данными. См. пример ниже, демонстрирующий это преобразование:
➤ Преобразование таблицы в стиле матрицы в список с помощью сводной таблицы
➤ Преобразование таблицы в стиле матрицы в список с помощью кода VBA
➤ Преобразование таблицы в стиле матрицы в список с помощью Kutools для Excel
➤ Преобразование таблицы в стиле матрицы в список с помощью формулы Excel
Преобразование таблицы в стиле матрицы в список с помощью сводной таблицы
В Excel нет прямой встроенной команды для преобразования таблицы в стиле матрицы в список из трех столбцов. Однако, используя Мастер сводных таблиц, вы можете эффективно преобразовать перекрестную матрицу в плоские табличные данные, подходящие для дальнейшего анализа. Этот подход идеален для небольших и средних наборов данных и особенно полезен, когда нужно упростить сложную структуру отчета. Он менее подходит для больших наборов данных или пользователей, не знакомых с операциями со сводными таблицами.
1. Откройте лист, содержащий вашу матрицу. Нажмите Alt + D, затем P, чтобы открыть мастер сводных таблиц и диаграмм. В мастере:
- В разделе Где находятся данные, которые вы хотите проанализировать, выберите Объединение нескольких диапазонов.
- В разделе Какой тип отчета вы хотите создать, выберите Сводная таблица.
2. Нажмите Далее. В диалоговом окне Шаг 2а из 3 выберите Я создам поля страниц:
3. Нажмите Далее. В Шаге 2b из 3, нажмите кнопку 
и выберите полный диапазон данных матрицы, включая заголовки строк и столбцов. Нажмите Добавить для вставки диапазона в все диапазоны список. Убедитесь, что выбранный диапазон охватывает всю матрицу.
4. Нажмите Далее. В Шаге 3 из 3 выберите, разместить ли сводную таблицу в новом листе или в определенной ячейке:
5. Нажмите Готово. Excel создаст сводную таблицу, суммирующую матрицу. По умолчанию она отображает агрегированные итоги на пересечениях. Вам не нужно изменять структуру сводной таблицы для этой задачи — просто переходите к следующему шагу:
6. Дважды щелкните ячейку, где пересекаются общие итоги строк и столбцов (например, ячейка F22). Excel создаст новый лист, содержащий список из трех столбцов, где каждая строка показывает уникальное сочетание заголовков строк и столбцов с соответствующим значением.
7. Для завершения выберите новую таблицу, щелкните правой кнопкой мыши, затем выберите Таблица > Преобразовать в диапазон. Это удаляет форматирование таблицы, оставляя простой редактируемый список:
Совет: Если ваша матрица часто меняется, вам придется повторять этот процесс для обновления списка из трех столбцов. Этот метод лучше всего подходит для статических данных. Кроме того, если ваша матрица содержит пустые или объединенные ячейки, перед использованием этого метода может потребоваться некоторая очистка.
Преобразование таблицы в стиле матрицы в список с помощью кода VBA
Если вы предпочитаете автоматизацию или хотите применить это преобразование многократно, использование макроса VBA может быстро конвертировать любую таблицу в стиле матрицы в структурированный список из трех столбцов. Этот метод особенно эффективен для больших наборов данных или различных макетов и исключает необходимость ручного форматирования. Он идеален для пользователей, знакомых с запуском скриптов VBA.
1. Нажмите Alt + F11, чтобы открыть редактор Microsoft Visual Basic для приложений.
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-дневная полнофункциональная пробная версия — без регистрации и кредитной карты
- Лучшее соотношение цены и качества — экономия по сравнению с покупкой отдельных надстроек