Быстрая настройка форматов данных в сводной таблице
При построении сводных таблиц все пользователи каждый раз сталкиваются с одной и той же проблемой. Форматы отображения данных в сводной нужно каждый раз настраивать - по умолчанию все поля отображаются в формате Общий (General), что приводит к появлению длинных "хвостов" после запятой разной длины, некорректному отображению денежных сумм и процентов:
Известный автор многочисленных книг по Excel и программированию на VBA Mike Alexander у себя в блоге предлагает простой и полезный макрос, который настраивает в сводной таблице числовые форматы полей, взяв их из исходной таблицы. Также этот макрос заменяет громоздкие стандартные названия полей типа "Сумма по полю Выручка", на простые и компактные:
Совершенно гениальная вещь. Не могу удержаться, чтобы не перепостить его макрос с минимальными исправлениями здесь для русскоязычной аудитории.
Sub AdoptSourceFormatting() 'Mike Alexander 'www.datapigtechnologies' 'Перед запуском макроса удостоверьтесь, что активная ячейка внутри сводной. Dim oPivotTable As PivotTable Dim oPivotFields As PivotField Dim oSourceRange As Range Dim strLabel As String Dim strFormat As String Dim i as Integer On Error GoTo MyErr 'Определяем источник данных текущей сводной таблицы Set oPivotTable = ActiveSheet.PivotTables(ActiveCell.PivotTable.Name) Set oSourceRange = Range(Application.ConvertFormula(oPivotTable.SourceData, xlR1C1, xlA1)) 'Обновляем сводную таблицу oPivotTable.PivotCache.Refresh 'Начинаем цикл прохода по всем столбцам исходного диапазона For i = 1 To oSourceRange.Columns.Count 'Определяем заголовок столбца и числовой формат для первой ячейки в столбце strLabel = oSourceRange.Cells(1, i).Value strFormat = oSourceRange.Cells(2, i).NumberFormat 'Проходим в цикле по полям сводной таблицы For Each oPivotFields In oPivotTable.DataFields 'Применяем числовой формат исходных данных к полю в сводной таблице If oPivotFields.SourceName = strLabel Then oPivotFields.NumberFormat = strFormat 'Бонус: меняем имя поля на заголовок столбца исходных данных oPivotFields.Caption = strLabel & " " End If Next oPivotFields Next i Exit Sub 'Обработка ошибок MyErr: If Err.Number = 1004 Then MsgBox "Сначала установите активную ячейку в сводную таблицу." Else MsgBox Err.Number & vbCrLf & Err.Description End If End Sub
Чтобы использовать этот код, нажмите сочетание клавиш Alt + F11, чтобы войти в редактор Visual Basic. Затем вставьте новый модуль через меню Insert - Module и скопируйте туда код этого макроса. Осталось установить активную ячейку в любое место вашей сводной таблицы и запустить макрос через диалоговое окно Макросы (Alt + F8).
Ссылки по теме
А можно кодом изменить формат поля в кэше сводной таблицы?