Быстрая настройка форматов данных в сводной таблице

При построении сводных таблиц все пользователи каждый раз сталкиваются с одной и той же проблемой. Форматы отображения данных в сводной нужно каждый раз настраивать - по умолчанию все поля отображаются в формате Общий (General), что приводит к появлению длинных "хвостов" после запятой разной длины, некорректному отображению денежных сумм и процентов:

pivot_formats_automate1.gif

 

Известный автор многочисленных книг по Excel и программированию на VBA Mike Alexander у себя в блоге предлагает простой и полезный макрос, который настраивает в сводной таблице числовые форматы полей, взяв их из исходной таблицы. Также этот макрос заменяет громоздкие стандартные названия полей типа "Сумма по полю Выручка", на простые и компактные:

pivot_formats_automate2.gif

 

Совершенно гениальная вещь. Не могу удержаться, чтобы не перепостить его макрос с минимальными исправлениями здесь для русскоязычной аудитории.

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).

Ссылки по теме



DROL72
22.11.2012 23:26:41
Хорошая штука, но есть одно "но" - не работает, если сводная таблица создана на основе "Таблицы" в версии Excel 2007, 2010. Эх, поправить бы..
19.11.2013 16:43:53
Если сначала сформировать сводную на основе диапазона данных, а потом диапазон преобразовать в "Таблицу", то работает! У меня 2010
20.09.2014 23:20:58
Я исправил исходную версию. Теперь она работает, как с диапазонами, так и с умными таблицами.
21.09.2014 09:45:52
Спасибо за доработку, Денис :)
30.06.2013 00:51:29
не работает! эксель 2007-2010(((( и номера строчек убрала(
01.07.2013 20:58:33
Все работает! Скачайте пример в заголовке статьи и посмотрите живой код. Если копируете код из текста статьи, то используйте значок "<>" в правом верхнем углу макроса, чтобы скопировать его без номеров строк.
02.07.2013 01:23:37
Спасибо, макрос появился, ошибок не выдает, только слова сумма по полю не убирает, помогите, не пойму в чем дело...таблица многокомпонентная, без объед ячеек, только есть строки не заполненные
27.08.2013 10:30:22
Супер!!!!
25.02.2014 18:19:59
Спасибо, Николай!

:cry: , что не работает когда в одной колонке разные форматы.  
26.02.2014 07:03:22
Виталий, а как у вас в исходных данных в одном столбце (поле) могут быть разные форматы?
Хорошая вещь, но доработка действительно не помешала бы! Например, если ячейка форматируется по разным видам валют, макрос это не учитывает :(
21.09.2014 09:47:09
ячейка форматируется по разным видам валют
Это как?:o
12.03.2018 20:50:03
Здравствуйте, Николай!
А можно кодом изменить формат поля в кэше сводной таблицы?
Наверх