Как добавить необходимые функции в макрос сводной таблицы Excel 2010
В результате записи макроса программа Excel создает модуль, который хранит выполненные вами действия. Все записанные действия представляются строками VBA-кода, из которых состоит макрос. Можно добавлять в отчеты сводной таблицы различные функциональные возможности, специально настроив VBA-код для получения требуемых результатов.
Чтобы вам было легче понять, как все это работает, создадим новый макрос, выводящий пять первых записей о клиентах. Перейдите на вкладку Разработчик и щелкните на кнопке Запись макроса. Откроется диалоговое окно, показанное на рис. 11.7. Назовите создаваемый макрос TopNthCusts и укажите сохранять его в книге Эта книга. Щелкните на кнопке ОК, чтобы начать запись.
Рис. 11.7. Присвойте новому макросу имя и определите место его хранения
После того как начнете запись, щелкните правой кнопкой мыши в поле Customer (Заказчик), выполните команду Фильтр (Filter) и выберите параметр Первые 10 (Тор 10). В появившемся диалоговом окне установите параметры так, как показано на рис. 11.8. Эти настройки указывают вывести данные пяти клиентов, лучших по объемам продаж. Щелкните на кнопке ОК.
Рис. 11.8. Настройте указанные параметры, чтобы отобразить пятерку лучших клиентов по продажам
После успешной записи всех действий, требуемых для извлечения пятерки лучших клиентов по продажам, перейдите на вкладку Разработчик и щелкните на кнопке Остановить запись (Stop Recording). Теперь у вас имеется макрос, который будет фильтровать сводную таблицу для извлечения пятерки лучших клиентов по продажам. Необходимо сделать так, чтобы макрос реагировал на состояние полосы прокрутки, т.е. с помощью полосы прокрутки вы должны иметь возможность указывать макросу количество клиентов, данные которых будут отображаться в отчете сводной таблицы. Таким образом, с помощью полосы прокрутки пользователь сможет извлекать пятерку лучших, восьмерку лучших или 32 лучших клиента по своему усмотрению.
Чтобы добавить в электронную таблицу полосу прокругки, перейдите на вкладку Разработчик, щелкните на кнопке Вставить, выберите на палитре элемент управления Полоса прокрутки (Scrollbar) и расположите его на рабочем листе. Щелкните правой кнопкой мыши на элементе управления Полоса прокругки и в контекстном меню выберите команду Формат объекта (Format Control). Откроется диалоговое окно Формат элемента управления, показанное на рис. 11.9. В нем внесите следующие изменения в настройки: параметру Минимальное значение (Minimum Level) присвойте значение 1, параметру Максимальное значение (Maximum Level) — значение 200, а в поле Связь с ячейкой (Cell Link) введите значение $M$2, чтобы в ячейке М2 отображалось значение полосы прокрутки. Щелкните на кнопке ОК, чтобы применить указанные ранее настройки.
Рис. 11.9. После помещения полосы прокрутки в сводную таблицу настройте ее параметры
Теперь нужно сопоставить недавно записанный макрос TopNthCusts элементу управления Полоса прокрутки, находящемуся на рабочем листе (рис. 11.10). Щелкните правой кнопкой мыши на элементе управления Полоса прокрутки и в контекстном меню выберите команду Назначить макрос (Assign Macro), чтобы открыть диалоговое окно назначения макроса. Назначьте полосе прокрутки записанный макрос TopNthCusts. Макрос будет выполняться каждый раз при щелчке на полосе прокрутки.
Рис. 11.10. Выберите макрос TopNthCusts в списке и щелкните на кнопке ОК
Протестируйте созданную полосу прокрутки. Как только щелкнете на полосе, запустится макрос TopNthCusts и изменится число в ячейке М2 для отображения состояния полосы прокрутки. Чисто в ячейке М2 играет важную роль, поскольку оно используется для привязки макроса к полосе прокрутки.
Единственное, что осталось сделать, — это заставить макрос обрабатывать число в ячейке 14, связывая ее с полосой прокрутки. Для этого нужно перейти к VBA-коду макроса. Для выполнения этой задачи применяется несколько команд, но в нашем примере проще всего перейти на вкладку Разработчик и щелкнуть на кнопке Макросы (Macros). Откроется диалоговое окно Макрос, показанное на рис. 11.11. В нем можно запускать, удалять и редактировать выбранный макрос. Чтобы отобразить VBA-код макроса на экране, выберите макрос и щелкните на кнопке Изменить (Edit).
Рис. 11.11. Чтобы получить доступ к VBA-коду макроса TopNthCusts, выберите макрос и щелкните на кнопке Изменить
Как показано на рис. 11.12, на экране появится окно редактора Visual Basic с VBA-кодом макроса. Ваша цель заключается в том, чтобы заменить жестко заданное в коде число 5, устанавливаемое во время записи макроса, значением в ячейке М2, которое привязано к полосе прокрутки.
Рис. 11.12. А теперь нужно заменить число 5, которое жестко задается в коде макроса, значением в ячейке М2
Изначально был записан макрос, предназначенный для отображения первых пяти заказчиков, имеющих наибольший доход. Удалите в коде число 5 и введите вместо него следующее выражение: ActiveSheet.Range("М2").Value. Теперь код макроса должен выглядеть так, как показано на рис. 11.13.
Рис. 11.13. Удалите жестко заданное число 5 и введите вместо него ссылку на ячейку М2
Закройте редактор Visual Basic и вернитесь к отчету сводной таблицы. Протестируйте полосу прокрутки, перетащив ползунок до значения 11. Ваш макрос должен запуститься и отфильтровать 11 записей о лучших клиентах по продажам, как показано на рис. 11.14.
Рис. 11.14. После дополнительного изменения вы получите простой способ вывода данных о лучших клиентах