Итог: Экономите тонны времени, автоматически форматируя числа в области ваших сводных таблиц с помощью макроса VBA. Форматирование чисел автоматически применяется при перетаскивании поля в область «Значения» за один шаг.
Уровень мастерства: Средний
Видео
Посмотрите видео ниже, чтобы увидеть автоматическое форматирование чисел в действии.
Скачать файл
Загрузите файл, содержащий макросы.
Pivot Table Automatic Number Formatting Macro.xlsm (28.4 KB)
Надстройка PivotPal (платная) также имеет эту функцию и может быть загружена и установлена.
Второе видео, объясняющее макрос, доступно ниже.
Проблема с форматами чисел сводных таблиц
Обычно, когда мы добавляем поле в область значений сводной таблицы, ячейки форматируются в формате общего числа. Это формат по умолчанию для всех ячеек на листе, и сводная таблица НЕ меняет его.
К сожалению, общий числовой формат делает номера уродливыми. Как вы можете видеть на скриншоте ниже, общий формат не добавляет запятые/точки для разделителей тысяч, количество десятичных знаков не фиксировано.
После добавления поля в область значений, мы должны предпринять дополнительные шаги для форматирования чисел. Это многошаговый процесс, требующий нескольких кликов. Существует несколько различных способов применения форматирования чисел к области значений, и это может занять много времени.
Итак, как мы можем изменить форматирование по умолчанию на что-то, кроме General?
Я создал макрос, который автоматически применяет форматирование чисел при добавлении поля в область значений. Я также добавил эту функцию в надстройку PivotPal. Если вы не хотите связываться с макросами, вы можете скачать и установить надстройку, чтобы получить эту функцию.
Макрос автоматического форматирования чисел
Макрос автоматически форматирует числа в области значений сводной таблицы, когда мы добавляем поле в область значений в списке полей сводной таблицы. Это одношаговый процесс. Вам не нужно предпринимать никаких дополнительных шагов для форматирования чисел.
Какой формат чисел он использует?
Макрос определяет числовой формат, используемый в диапазоне исходных данных поля. В приведенном ниже примере столбец «Доход» в диапазоне исходных данных отформатирован в формате «Финансовый». Таким образом, макрос обнаруживает это и автоматически применяет этот числовой формат к полю «Доход» в области значений.
Если столбец в диапазоне исходных данных не отформатирован, то применяется числовой формат по умолчанию. Числовой формат по умолчанию в настоящее время установлен в формате запятой [0] (#, 000), но вы можете изменить это в коде макроса или в надстройке PivotPal.
Числовой формат по умолчанию также используется, если диапазон исходных данных не существует в рабочей книге или находится в модели данных.
Этот макрос должен сэкономить вам массу времени благодаря форматированию чисел в ваших сводных таблицах. Вы можете применить форматирование к диапазону исходных данных один раз и автоматически применить его ко всем сводным таблицам, которые вы создадите / измените в будущем.
Макрос будет работать с любой существующей сводной таблицей или новой сводной таблицей.
Автоматическое форматирование чисел может быть включено / выключено нажатием кнопки в меню правой кнопки мыши на сводной таблице. Эта кнопка добавлена с макросом, который включен в загружаемый файл.
Как я могу использовать эту функцию?
Есть два способа реализовать эту функцию.
Вариант 1. Добавьте макросы в личную книгу макросов.
Вы можете скачать файл, содержащий макросы, и добавить модули кода в свою личную книгу макросов (PMW).
Pivot Table Automatic Number Formatting Macro.xlsm (28.4 KB)
Файл можно загрузить бесплатно, а также вы можете изменить код. В видео ниже я объясню, как установить макросы в PMW, а также расскажу, как все это работает.
Изучите мою статью и серию видео в Персональной рабочей книги для макросов, чтобы узнать все об этом инструменте для Excel.
Вариант 2. Получить надстройку PivotPal
Эта функция также была добавлена в надстройку PivotPal. Это платная надстройка.
Вы можете скачать и установить PivotPal, чтобы получить эту функцию. Вам не нужно знать VBA или связываться с любым кодом для этой опции. Просто скачайте и установите надстройку, и кнопка «Автоформатирование чисел» будет добавлена в меню, вызываемое правой кнопкой мыши.
Нажмите здесь, чтобы узнать больше о PivotPal
Как работает макрос?
Видео ниже объясняет в деталях, как работает макрос.
Это решение работает с использованием события PivotTableUpdate для автоматического запуска макроса при внесении каких-либо изменений в сводную таблицу.
Макрос использует цикл For Next Loop для циклического прохождения всех полей в области значений (поля данных) и проверяет свойство NumberFormat, чтобы увидеть, отформатированы ли они в общем формате. Если поле содержит общее форматирование, то макрос пытается получить форматирование для поля в диапазоне исходных данных. Если он не найден или является общим, то макрос применяет заданный вами формат чисел по умолчанию. Так или иначе, ячейки в области значений НИКОГДА не будут отформатированы с общим форматом.
Вы можете отключить автоматическое форматирование чисел в любое время с помощью кнопки в меню, вызываемом правой кнопкой мыши, если вы хотите сохранить числа, отформатированные как Общие.
Решение использует События приложения для мониторинга изменений в любой сводной таблице в любой открытой книге. Это означает, что вы можете добавить модули кода в свою личную книгу макросов или файл надстройки, код будет выполняться на любой открытой книге. Вам НЕ нужно добавлять макросы в каждую книгу, которая содержит сводные таблицы.
Вот две хорошие статьи от Chip Pearson и Microsoft.
Модули кода также содержат макросы для добавления кнопки автоматического форматирования чисел в контекстное меню (контекстное меню). Это работает как кнопка переключения, которая позволяет вам включать / выключать функцию.
* Важное примечание. Это решение является полным обходным решением очень распространенной и трудоемкой проблемы в Excel. В настоящее время событие PivotTableUpdate будет срабатывать, когда в сводную таблицу вносятся какие-либо изменения или когда обновляется сводная таблица.
Итак, если у вас есть существующая сводная таблица с общим форматированием и вы обновите сводную таблицу, макрос запустится, чтобы изменить форматирование на исходный / стандартный числовой формат. Если вы хотите сохранить формат чисел как Общий в сводной таблице, отключите автоматическое форматирование чисел перед обновлением / изменением сводной таблицы.
Если поле в области значений уже отформатировано с использованием чего-то отличного от General, то макрос НЕ изменит числовой формат при выполнении обновления / модификации. Макрос ТОЛЬКО изменяет форматирование чисел с общего на формат источника / по умолчанию.
Будущие улучшения
Я использую эту функцию в течение нескольких недель, и у меня уже есть идеи по ее улучшению.
# 1 — Запускать макрос можно только при добавлении нового поля в область значений.
Как я упоминал выше, макрос запускается каждый раз, когда сводная таблица изменяется или обновляется. Это не вызвало никаких проблем для меня, потому что я никогда не хочу оставлять область значений в формате General. Однако мы могли бы улучшить это, так что макрос запускается только тогда, когда новое поле добавляется в область значений. Это, вероятно, потребовало бы от нас хранения количества полей данных, которые в настоящий момент находятся в активной сводной таблице, с дополнительным мониторингом с помощью событий Worksheet_Change и Worksheet_Activate.
# 2 — Интеллектуальное форматирование чисел
Включение форматирования из диапазона исходных данных — это здорово, но в некоторых случаях агрегация вызывает необходимость в другом формате. Например, мы могли бы использовать стандартный формат номера бухгалтерского учета в столбце исходных данных для дохода. Когда мы добавляем это поле в область значений, оно может суммироваться с гораздо большими числами. Таким образом, мы могли бы вместо этого использовать числовой формат тысяч (500 000 долларов) или миллионов (5,5 миллионов долларов США).
Макрос также может обнаружить это и даже установить пороги или правила для того, какое форматирование чисел следует применять, основываясь на максимальных, минимальных или средних значениях в области значений.
Другой вариант — поместить любимые форматы чисел в контекстное меню для быстрого доступа. Есть пробный взгляд на будущую особенность …
Опять же, если вам не нужны макросы, функция автоматического форматирования чисел включена в мою надстройку PivotPal.
Какие предложения у вас есть для функций? Пожалуйста, оставьте комментарий ниже с предложениями или вопросами. Спасибо!