Как преобразовать сводную таблицу в обычный список в Excel?
В повседневной работе с Excel вы можете создавать подробные сводные таблицы для анализа, обобщения или визуализации больших объемов данных. Однако иногда возникает необходимость «развернуть» структурированную сводную таблицу обратно в обычный список или стандартный табличный формат — например, чтобы дополнительно обработать данные, передать их коллегам или импортировать в другое приложение. В этом руководстве представлены несколько практических способов преобразования сводной таблицы в список в Excel, подходящих как для небольших, так и для крупных наборов данных.
Преобразовать Сводная таблица в список
Код VBA: автоматическое преобразование сводной таблицы в список
Преобразовать Сводная таблица в список
Перед преобразованием сводной таблицы в стандартный список убедитесь, что её макет полностью соответствует вашим требованиям. Например, допустим, вы создали следующую сводную таблицу и хотите вернуть её к плоской структуре списка:
1. Выберите любую ячейку внутри своей сводной таблицы. Перейдите на вкладку Конструктор на ленте, выберите Промежуточные итоги и укажите Не показывать промежуточные итоги. Этот шаг удалит все подгруппы и строки промежуточных итогов, которые могут помешать получению плоского списка.
2. Оставаясь на вкладке Конструктор, нажмите Итоги и выберите Отключить для строк и столбцов. Отключение итогов гарантирует, что в выходных данных останутся только ваши фактические данные — без сводных строк или столбцов.
3. На вкладке Конструктор нажмите Макет отчёта и выберите Повторять все подписи элементов. Этот шаг важен для «разворачивания» многоуровневых меток строк, чтобы каждая строка в итоговом списке отображала корректный контекст независимо от своей группы или категории.
4. Снова нажмите Макет отчёта и выберите Табличная форма. Табличная форма отображает все данные в строго столбцовом виде, что делает её похожей на стандартный список или таблицу.
На данном этапе ваша сводная таблица будет напоминать обычную таблицу, что значительно упрощает её экспорт в виде списка:
5. Чтобы ещё больше упростить результат, перейдите на вкладку Параметры(или)Анализ, в зависимости от версии Excel). В группе Показать снимите флажки с пунктов Кнопки и Заголовки полей. Удаление этих элементов поможет очистить данные для преобразования в список и сделает результат аккуратнее.
Теперь ваша сводная таблица будет выглядеть проще, содержа только поля Соответствующие данные:
6. Если вы хотите, чтобы пустые ячейки отображались как нули, щелкните правой кнопкой мыши по любой ячейке сводной таблицы и выберите Параметры сводной таблицы. В открывшемся диалоговом окне перейдите на вкладку Макет и формат и снимите флажок с пункта Для пустых ячеек показывать. Затем нажмите ОК. Этот шаг гарантирует, что в экспортированном списке не останется пустых ячеек там, где ожидаются значения — особенно важно при последующей обработке или импорте в другие системы.
7. Выделите весь диапазон сводной таблицы, который нужно преобразовать. Нажмите Ctrl + C, чтобы скопировать, затем переместите курсор в нужную ячейку на листе, куда вы хотите вставить данные. Щёлкните правой кнопкой мыши, выберите Вставить специально и укажите Значения (V). В результате будут вставлены только значения — без структуры и интерактивности сводной таблицы.
Примечание: в Excel 2007 или более ранних версиях используйте команду Главная > Вставить > Вставить значения, чтобы выполнить это действие.
После вставки ваши данные теперь отображаются в стандартном формате списка и готовы к дальнейшему анализу, передаче или экспорту:
Этот метод прост и отлично подходит для небольших и средних сводных таблиц, которые не нуждаются в частом обновлении. Однако если ваша сводная таблица динамична или часто меняется, мы рекомендуем автоматизировать процесс преобразования или воспользоваться встроенными инструментами преобразования данных — это повысит эффективность и точность работы. Обратите также внимание: сложные сводные таблицы с вычисляемыми полями или сгруппированными элементами могут потребовать дополнительной корректировки после преобразования.
Код VBA: автоматическое преобразование сводной таблицы в список
Если вам часто приходится преобразовывать сводные таблицы в обычные списки или если ваши сводные таблицы особенно объёмны, автоматизация этого процесса поможет значительно сэкономить время и усилия. С помощью VBA (Visual Basic for Applications) вы можете «развернуть» сводную таблицу в список, выполнив минимум ручных действий. Это решение идеально подходит продвинутым пользователям, для пакетной обработки или при работе с десятками и сотнями сводных таблиц.
Преимущества: ускоряет выполнение повторяющихся задач, эффективно обрабатывает большие объёмы данных и минимизирует риск ошибок при ручном вводе.
Недостатки: требует включения макросов и базового понимания VBA. Перед первым запуском кода настоятельно рекомендуется создать резервную копию.
1. Нажмите Разработчик > Visual Basic, чтобы открыть редактор VBA. В редакторе выберите Вставка > Модуль и вставьте следующий код в новое окно модуля:
Sub ConvertPivotTableToList()
Dim pt As PivotTable
Dim wsPivot As Worksheet
Dim rngTable As Range
Dim wsNew As Worksheet
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set wsPivot = Application.ActiveSheet
Set pt = wsPivot.PivotTables(1)
If pt Is Nothing Then
MsgBox "No PivotTable found on the current sheet.", vbExclamation, xTitleId
Exit Sub
End If
Set rngTable = pt.TableRange2
Set wsNew = Worksheets.Add
rngTable.Copy
wsNew.Range("A1").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
MsgBox "Converted PivotTable to static list on new sheet: " & wsNew.Name, vbInformation, xTitleId
End Sub2. Вернитесь на лист и выделите сводную таблицу, которую нужно преобразовать в список. Нажмите Alt + F8, выберите в списке макросов ConvertPivotTableToList и нажмите Выполнить. В результате будет создан новый лист со сводной таблицей, скопированной в виде статического списка.
Если появится сообщение об ошибке, убедитесь, что перед запуском макроса вы выбрали правильный лист со сводной таблицей. Также проверьте, включены ли макросы в 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-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек