Автоматическое форматирование чисел по умолчанию в сводных таблицах Excel

Итог: Экономите тонны времени, автоматически форматируя числа в области ваших сводных таблиц с помощью макроса VBA. Форматирование чисел автоматически применяется при перетаскивании поля в область «Значения» за один шаг.

Уровень мастерства: Средний

Number Formatting Automatically Applied GIF2

Видео

Посмотрите видео ниже, чтобы увидеть автоматическое форматирование чисел в действии.

Скачать файл

Загрузите файл, содержащий макросы.

Pivot Table Automatic Number Formatting Macro.xlsm (28.4 KB)

Надстройка PivotPal (платная) также имеет эту функцию и может быть загружена и установлена.

Второе видео, объясняющее макрос, доступно ниже.

Проблема с форматами чисел сводных таблиц

Обычно, когда мы добавляем поле в область значений сводной таблицы, ячейки форматируются в формате общего числа. Это формат по умолчанию для всех ячеек на листе, и сводная таблица НЕ меняет его.

К сожалению, общий числовой формат делает номера уродливыми. Как вы можете видеть на скриншоте ниже, общий формат не добавляет запятые/точки для разделителей тысяч, количество десятичных знаков не фиксировано.

Pivot Table General Formatting Used by Default

После добавления поля в область значений, мы должны предпринять дополнительные шаги для форматирования чисел. Это многошаговый процесс, требующий нескольких кликов. Существует несколько различных способов применения форматирования чисел к области значений, и это может занять много времени.

Итак, как мы можем изменить форматирование по умолчанию на что-то, кроме General?

Я создал макрос, который автоматически применяет форматирование чисел при добавлении поля в область значений. Я также добавил эту функцию в надстройку PivotPal. Если вы не хотите связываться с макросами, вы можете скачать и установить надстройку, чтобы получить эту функцию.

Макрос автоматического форматирования чисел

Макрос автоматически форматирует числа в области значений сводной таблицы, когда мы добавляем поле в область значений в списке полей сводной таблицы. Это одношаговый процесс. Вам не нужно предпринимать никаких дополнительных шагов для форматирования чисел.

Number Formatting Automatically Applied Field Added to Values Area

Какой формат чисел он использует?

Макрос определяет числовой формат, используемый в диапазоне исходных данных поля. В приведенном ниже примере столбец «Доход» в диапазоне исходных данных отформатирован в формате «Финансовый». Таким образом, макрос обнаруживает это и автоматически применяет этот числовой формат к полю «Доход» в области значений.

Number Formatting from Source Data Range is Detected and Applied

Если столбец в диапазоне исходных данных не отформатирован, то применяется числовой формат по умолчанию. Числовой формат по умолчанию в настоящее время установлен в формате запятой [0] (#, 000), но вы можете изменить это в коде макроса или в надстройке PivotPal.

Числовой формат по умолчанию также используется, если диапазон исходных данных не существует в рабочей книге или находится в модели данных.

Default Number Formatting Applied if Source Data Column is Not Formatted

Этот макрос должен сэкономить вам массу времени благодаря форматированию чисел в ваших сводных таблицах. Вы можете применить форматирование к диапазону исходных данных один раз и автоматически применить его ко всем сводным таблицам, которые вы создадите / измените в будущем.

Макрос будет работать с любой существующей сводной таблицей или новой сводной таблицей.

Автоматическое форматирование чисел может быть включено / выключено нажатием кнопки в меню правой кнопки мыши на сводной таблице. Эта кнопка добавлена с макросом, который включен в загружаемый файл.

Automatic Number Formatting Pivot Table Right Click Menu Button

Как я могу использовать эту функцию?

Есть два способа реализовать эту функцию.

Вариант 1. Добавьте макросы в личную книгу макросов.

Вы можете скачать файл, содержащий макросы, и добавить модули кода в свою личную книгу макросов (PMW).

Pivot Table Automatic Number Formatting Macro.xlsm (28.4 KB)

Файл можно загрузить бесплатно, а также вы можете изменить код. В видео ниже я объясню, как установить макросы в PMW, а также расскажу, как все это работает.

Изучите мою статью и серию видео в Персональной рабочей книги для макросов, чтобы узнать все об этом инструменте для Excel.

Вариант 2. Получить надстройку PivotPal

Эта функция также была добавлена в надстройку PivotPal. Это платная надстройка.

" width="640" height="360"

Вы можете скачать и установить PivotPal, чтобы получить эту функцию. Вам не нужно знать VBA или связываться с любым кодом для этой опции. Просто скачайте и установите надстройку, и кнопка «Автоформатирование чисел» будет добавлена в меню, вызываемое правой кнопкой мыши.

Нажмите здесь, чтобы узнать больше о PivotPal

Как работает макрос?

Видео ниже объясняет в деталях, как работает макрос.

Это решение работает с использованием события PivotTableUpdate для автоматического запуска макроса при внесении каких-либо изменений в сводную таблицу.

Class Module for PivotTableUpdate Auto Number Formatting App Event

Макрос использует цикл For Next Loop для циклического прохождения всех полей в области значений (поля данных) и проверяет свойство NumberFormat, чтобы увидеть, отформатированы ли они в общем формате. Если поле содержит общее форматирование, то макрос пытается получить форматирование для поля в диапазоне исходных данных. Если он не найден или является общим, то макрос применяет заданный вами формат чисел по умолчанию. Так или иначе, ячейки в области значений НИКОГДА не будут отформатированы с общим форматом.

Вы можете отключить автоматическое форматирование чисел в любое время с помощью кнопки в меню, вызываемом правой кнопкой мыши, если вы хотите сохранить числа, отформатированные как Общие.

Решение использует События приложения для мониторинга изменений в любой сводной таблице в любой открытой книге. Это означает, что вы можете добавить модули кода в свою личную книгу макросов или файл надстройки, код будет выполняться на любой открытой книге. Вам НЕ нужно добавлять макросы в каждую книгу, которая содержит сводные таблицы.

Вот две хорошие статьи от Chip Pearson и Microsoft.

Модули кода также содержат макросы для добавления кнопки автоматического форматирования чисел в контекстное меню (контекстное меню). Это работает как кнопка переключения, которая позволяет вам включать / выключать функцию.

* Важное примечание. Это решение является полным обходным решением очень распространенной и трудоемкой проблемы в Excel. В настоящее время событие PivotTableUpdate будет срабатывать, когда в сводную таблицу вносятся какие-либо изменения или когда обновляется сводная таблица.

Итак, если у вас есть существующая сводная таблица с общим форматированием и вы обновите сводную таблицу, макрос запустится, чтобы изменить форматирование на исходный / стандартный числовой формат. Если вы хотите сохранить формат чисел как Общий в сводной таблице, отключите автоматическое форматирование чисел перед обновлением / изменением сводной таблицы.

Если поле в области значений уже отформатировано с использованием чего-то отличного от General, то макрос НЕ изменит числовой формат при выполнении обновления / модификации. Макрос ТОЛЬКО изменяет форматирование чисел с общего на формат источника / по умолчанию.

Будущие улучшения

Я использую эту функцию в течение нескольких недель, и у меня уже есть идеи по ее улучшению.

# 1 — Запускать макрос можно только при добавлении нового поля в область значений.

Как я упоминал выше, макрос запускается каждый раз, когда сводная таблица изменяется или обновляется. Это не вызвало никаких проблем для меня, потому что я никогда не хочу оставлять область значений в формате General. Однако мы могли бы улучшить это, так что макрос запускается только тогда, когда новое поле добавляется в область значений. Это, вероятно, потребовало бы от нас хранения количества полей данных, которые в настоящий момент находятся в активной сводной таблице, с дополнительным мониторингом с помощью событий Worksheet_Change и Worksheet_Activate.

# 2 — Интеллектуальное форматирование чисел

Включение форматирования из диапазона исходных данных — это здорово, но в некоторых случаях агрегация вызывает необходимость в другом формате. Например, мы могли бы использовать стандартный формат номера бухгалтерского учета в столбце исходных данных для дохода. Когда мы добавляем это поле в область значений, оно может суммироваться с гораздо большими числами. Таким образом, мы могли бы вместо этого использовать числовой формат тысяч (500 000 долларов) или миллионов (5,5 миллионов долларов США).

Intelligent Automatic Number Formatting for Pivot Tables

Макрос также может обнаружить это и даже установить пороги или правила для того, какое форматирование чисел следует применять, основываясь на максимальных, минимальных или средних значениях в области значений.

Другой вариант — поместить любимые форматы чисел в контекстное меню для быстрого доступа. Есть пробный взгляд на будущую особенность …

My Number Formats Pivot Table Right Click Menu

Опять же, если вам не нужны макросы, функция автоматического форматирования чисел включена в мою надстройку PivotPal.

Какие предложения у вас есть для функций? Пожалуйста, оставьте комментарий ниже с предложениями или вопросами. Спасибо!

Оцените статью
Добавить комментарий