Как добавить несколько полей в сводную таблицу?
При создании сводной таблицы мы обычно перетаскиваем поля в области «Метки строк» или «Значения» по одному. Если список полей длинный, легко добавить несколько из них как метки строк, но добавление остальных в область «Значения» может занять много времени. Существует ли способ одним щелчком добавить все оставшиеся поля в область «Значения»?
Добавление нескольких полей в область значений Сводная таблица с помощью кода VBA
Добавление нескольких полей в область значений Сводная таблица с помощью кода VBA
В Excel на данный момент отсутствует встроенная функция — например, флажок «Выбрать все» на панели списка полей, — позволяющая одним щелчком добавить все оставшиеся поля сразу в область «Значения». Однако добиться этого легко и эффективно можно с помощью простого макроса VBA. Такой подход особенно удобен, если вам часто приходится выполнять эту операцию или вы работаете с большим количеством полей данных. Ниже приведены подробные шаги и важные предостережения, которые помогут вам успешно применить этот метод:
1.После создания сводной таблицы добавьте нужные поля в качестве меток строк обычным способом — перетащив их мышью. Остальные поля, которые вы хотите использовать для суммирования значений, оставьте без отметки в разделе Выбор полей для добавления в отчет. Для справки см. следующий снимок экрана:
3. В окне редактора VBA перейдите в меню Вставка > Модуль, чтобы создать новый модуль. Затем скопируйте и вставьте приведённый ниже код VBA в окно модуля. Чтобы открыть редактор VBA, выберите на ленте Excel: Разработчик > Visual Basic. Убедитесь, что вы работаете в нужной книге.
2. Нажмите сочетание клавиш ALT + F11, чтобы открыть окно редактора Microsoft Visual Basic для приложений. Кроме того, вы можете открыть его через ленту Excel, выбрав команду
Код VBA: Добавление нескольких полей в область значений Сводная таблица на активном листе
Sub AddAllFieldsValues()
'Update 20141112
Dim pt As PivotTable
Dim I As Long
For Each pt In ActiveSheet.PivotTables
For I = 1 To pt.PivotFields.Count
With pt.PivotFields(I)
If .Orientation = 0 Then .Orientation = xlDataField
End With
Next
Next
End Sub
4. Далее нажмите клавишу F5, чтобы запустить макрос, или щёлкните кнопку «Выполнить» на панели инструментов редактора VBA. Макрос обработает все сводные таблицы на текущем листе: для каждой из них он добавит в область «Значения» все поля, которые вы ещё не использовали в качестве меток строк или столбцов. В результате каждое доступное поле данных появится в вашей сводной таблице в виде суммированного значения — это значительно ускорит комплексный анализ. Для справки см. следующий снимок экрана:
Примечание: Этот макрос применяет изменения ко всем сводным таблицам на активном листе во время его запуска. Если у вас несколько сводных таблиц и вы хотите обновить только одну из них, рекомендуется временно переместить эту сводную таблицу на отдельный лист. Перед запуском макроса VBA всегда сохраняйте свою работу — макросы могут вносить необратимые изменения. Кроме того, макрос попытается добавить все оставшиеся поля, включая те, которые не подходят для числового суммирования, что может привести к ошибкам в сводке или бессмысленным результатам для полей с нечисловыми данными.
См. также:
Как получить список всех сводных таблиц из книги?
Как проверить, есть ли в книге сводная таблица?
Лучшие инструменты повышения продуктивности в 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-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек