Трюк №47. Как автоматизировать процесс создания сводных таблиц в Excel 2010, используя простой макрос?

Шаги, которые нужно выполнить для создания сводной таблицы, требуют приложения некоторых усилий, и эти усилия зачастую являются излишними. Написав немного кода VBA, вы сможете автоматически создавать простые сводные таблицы.

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

Предположим, в столбце А есть длинный список имен, причем в ячейке А1 находится заголовок. Вы хотите узнать, сколько элементов содержится в списке, и создать список уникальных элементов. Выделите ячейку А1 (заголовок) и выберите команду Данные → Сводная таблица (Data → PivotTable and PivotChart Report) (или Data → PivotTable Report на Mac), чтобы запустить мастер сводных таблиц.

Удостоверьтесь, что установлен переключатель В списке или базе данных Microsoft Excel (Microsoft Excel List or Database) и что выделена одна ячейка данных. Это позволит Excel автоматически распознать данные, на основе которых будет далее строиться сводная таблица. Если вы работаете в windows microsoft, то в группе Вид создаваемого отчета (What kind of report do you want to create?) выберите Сводная таблица (PivotTable) (на Macintosh этот вопрос не выводится). Щелкните кнопку Далее (Next). Мастер сводных таблиц должен автоматически выбрать правильный диапазон данных в столбце А и выделить его на листе. Если это так, щелкните кнопку Далее (Next). Иначе выделите диапазон мышью. Щелкните кнопку Макет (Layout) и перетащите в область Данные (Data) то, что будет вашим единственным полем, — над этой областью вы должны увидеть заголовок из ячейки А1. Еще раз перетащите поле с заголовком, на этот раз в область Строка (Row). Щелкните на кнопке ОК.
[stextbox id=»info»]На данном этапе, если необходимо, можно дважды щелкнуть кнопку поля в области Данные (Data) и изменить значение параметра Операция (Summarize by). Можно выбрать Сумма (Sum), Среднее (Average) и т. д. По умолчанию Excel выбирает функцию СЧЁТ (COUNT), если работа идет с текстом, и функцию СУММ (SUM), если это числа.[/stextbox]
Теперь в качестве местоположения отчета сводной таблицы выберите Новый лист (New Worksheet) и щелкните кнопку Готово (Finish). На новом рабочем листе вы должны увидеть сводную таблицу, содержащую уникальные имена из исходного списка и указание, сколько раз каждый элемент (имя) содержится в списке.

А что если вы хотите, чтобы все эти шаги вместо вас выполнял макрос, создавая сводную таблицу на основе любого указанного столбца? Если вы просто запишете макрос, он будет работать, только если данные содержат тот же заголовок. Чтобы избежать этого, можно создать простой макрос, хранящийся в вашей рабочей книге или в личной книге макрокоманд, который будет создавать сводную таблицу по любому списку элементов. Для этого нужно написать некоторый общий код VBA и ввести его в стандартный модуль в вашей личной книге макрокоманд или любой другой книге макрокоманд.

Выберите команду Сервис → Макрос → Редактор Visual Basic (Tools → Macro → Visual Basic Editor) (сочетание клавиш Alt/Option+FH) и затем выберите команду Insert → Module. Введите код, приведенный в листинге 4.1.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
// Листинг 4.1
Sub GetCount()
Dim Pt As PivotTable
Dim strField As String
    strField = Selection.Cells(1, 1).Text
        Range(Selection, Selection.End(xlDown)).Name = "Items"
        ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
                SourceData:="=Items").CreatePivotTable TableDestination:="", _
                TableName:="ItemList"
    Set Pt = ActiveSheet.PivotTables("ItemList")
        ActiveSheet.PivotTableWizard TableDestination:=Cells(3, 1)
        Pt.AddFields RowFields:=strField
        Pt.PivotFields(strField).Orientation = xlDataField
End Sub

Чтобы вернуться в Excel, закройте окно Script или нажмите сочетание клавиш Alt/Apple+Q и сохраните рабочую книгу. Перед запуском этого кода выделите заголовок списка и удостоверьтесь, что список не содержит пустых ячеек.
[stextbox id=»warning»]Чтобы быстро удалить пустые ячейки, можно отсортировать список. (PivotTable Field) и Макет (PivotTable Layout)[/stextbox]
Код автоматически создаст именованный диапазон на основе вашего списка и присвоит ему имя Items. Затем он создаст на новом листе сводную таблицу, основываясь на данном именованном диапазоне. В следующий раз, когда у вас появится длинный список данных, вы сможете просто выделить его заголовок и запустить этот макрос. Вся работа по настройке сводной таблицы будет выполнена в мгновение ока.

Top