Как преобразовать сводную таблицу в список в Excel?
В повседневной работе с Excel вы можете создать комплексную сводную таблицу для анализа, суммирования или визуализации больших объемов данных. Однако бывают ситуации, когда вам нужно «упростить» или преобразовать структурированную сводную таблицу обратно в обычный список или стандартный табличный формат — возможно, чтобы дополнительно обработать данные, поделиться ими с другими или импортировать их в другое приложение. В этом руководстве рассматриваются несколько практических методов преобразования сводной таблицы обратно в список в Excel, подходящих как для небольших, так и для крупных наборов данных.
Преобразование сводной таблицы в список
Код VBA: Автоматическое преобразование сводной таблицы в список
Преобразование сводной таблицы в список
Перед преобразованием вашей сводной таблицы в стандартный список убедитесь, что макет сводной таблицы окончательно настроен. Например, предположим, что вы создали следующую сводную таблицу, которую хотите вернуть к плоской структуре списка:
1. Выберите любую ячейку в сводной таблице. Перейдите на вкладку Конструктор в ленте, выберите Итоги, а затем выберите Не показывать промежуточные итоги. Этот шаг удаляет любые подгруппы или строки промежуточных итогов, которые могут помешать желаемому формату плоского списка.
2. Все еще находясь во вкладке Конструктор, щелкните Общие итоги и выберите Отключить для строк и столбцов. Отключение общих итогов гарантирует, что останутся только фактические данные, предотвращая появление строчек или столбцов с итогами в вашем выводе.
3. Во вкладке Конструктор щелкните Макет отчета и выберите Повторять все метки элементов. Этот шаг важен для упрощения многоуровневых меток строк, чтобы каждая строка в конечном списке показывала правильный контекст, независимо от её группы или категории.
4. Щелкните Макет отчета снова и выберите Показать в табличной форме. Табличная форма представляет все данные в истинной колоночной структуре, которая ближе к стандартному списку или таблице.
На этом этапе ваша сводная таблица будет напоминать традиционную таблицу, что значительно упростит экспорт её в виде списка:
5. Для дальнейшего улучшения вывода перейдите на вкладку Параметры (или Анализ, в зависимости от версии Excel). В группе Показать снимите флажки Кнопки и Заголовки полей. Удаление этих элементов помогает очистить ваши данные для преобразования в список и делает вывод более аккуратным.
Теперь ваша сводная таблица станет более понятной, оставляя только соответствующие поля данных:
6. Если есть пустые ячейки, которые вы предпочитаете отображать как нули, щелкните правой кнопкой мыши любую ячейку в сводной таблице и выберите Параметры сводной таблицы. В открывшемся диалоговом окне, на вкладке Макет и формат, снимите флажок Для пустых ячеек показывать. Затем нажмите OK. Этот шаг гарантирует, что в экспортированном списке не будут пустые места там, где вы ожидали значения, что может быть важно при дальнейшей обработке или импорте в другие системы.
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-дневная полнофункциональная пробная версия — без регистрации и кредитной карты
- Лучшее соотношение цены и качества — экономия по сравнению с покупкой отдельных надстроек