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

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

Рис 11.15. Две сводные таблицы содержат поля страниц, которые выполняют фильтрацию данных по рынкам. Для анализа данных отдельного рынка нужно синхронизировать обе сводные таблицы

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

Для решения этой задачи нужно выполнить следующие действия.

1. Создайте новый макрос и присвойте ему имя SynchMarkets. Когда начнется запись, выберите в поле Рынок сбыта обеих сводных таблиц рынок сбыта Калифорния и остановите запись.
2. Отобразите на экране палитру элементов управления формы и добавьте на рабочий лист раскрывающееся меню.
3. Создайте жестко определенный список всех рынков сводной таблицы. Заметьте, что первым элементом списка указывается значение (Все). Вы должны включить этот элемент, если хотите иметь возможность выбирать в раскрывающемся меню все рынки. На этом этапе отчет сводной таблицы должен выглядеть так, как показано на рис. 11.16.


Рис 11.16. В вашем распоряжении есть все требуемые средства: макрос, изменяющий поле Рынок сбыта обеих сводных таблиц, раскрывающееся меню и список всех рынков сбыта, содержащихся в сводной таблице

4. Щелкните правой кнопкой мыши на раскрывающемся списке и в контекстном меню выберите команду Формат объекта (Format Control), чтобы выполнить исходную настройку элемента управления. Вначале задайте исходный диапазон значений списка, используемого для заполнения раскрывающегося меню, как показано на рис. 11.17. В данном случае речь идет о списке рынков, созданном вами в п.3. Затем укажите ячейку, отображающую порядковый номер выбранного элемента (в данном примере таковой является ячейка Н1). Щелкните на кнопке ОК.
   

   Рис. 11.17. Настройки раскрывающегося меню должны указывать на список рынков как на исходный диапазон значений, а в качестве точки связывания — определять ячейку Н1

   Теперь у вас появилась возможность выбирать в раскрывающемся меню рынок, а также определять связанный с ним порядковый номер в ячейке H1 (рис. 11.18). Возникает вопрос: зачем вместо реального имени рынка используется его индексное значение? Потому что раскрывающееся меню возвращает не имя, а номер. Например, при выборе в раскрывающемся меню имени Шарлотта оно возвращает в ячейку H1 значение 5. Это означает, что Шарлотта является пятым элементом списка.

   

   Рис. 11.18. Раскрывающееся меню теперь заполняется названиями рынков с выводом порядкового номера выбранного рынка в ячейке H1

   Чтобы использовать порядковый номер вместо имени рынка, вам следует передать его с помошью функции ИНДЕКС (INDEX). Функция индекс преобразует порядковый номер в распознаваемое значение. Введите функцию ИНДЕКС, которая преобразует порядковый номер из ячейки H1 в значение.

5. Функция ИНДЕКС требует для нормальной работы два аргумента. Первый аргумент представляет диапазон значений списка. В большинстве случаев вы будете использовать тот же диапазон, которым заполняется раскрывающееся меню. Второй аргумент — это порядковый номер. Если порядковый номер вводится в ячейке (например, в ячейке H1, как на рис. 11.19), то можете просто сослаться на эту ячейку.


Рис. 11.19. Функция индекс в ячейке 11 преобразует порядковый номер в ячейке H1 в значение. В конечном счете вы будете использовать значение в ячейке 11 для изменения макроса

6. Отредактируйте макрос SynchMarkets, используя значение в ячейке и вместо жестко заданного значения. Перейдите на вкладку Разработчик и щелкните на кнопке Макросы (Macros). На экране появится диалоговое окно, показанное на рис. 11.20. Выберите в нем макрос SynchMarkets и щелкните на кнопке Изменить (Edit).
   

   Рис. 11.20. Чтобы получить доступ к VBA-коду макроса, выберите макрос SynchMarkets и щелкните на кнопке Изменить

   При записи макроса вы выбрали в обеих сводных таблицах рынок сбыта Калифорния из поля Market (Рынок сбыта). Как видно из рис. 11.21, рынок Калифорния теперь жестко задан в VBA-коде макроса. Замените значение «Калифорния» выражением ActiveSheet.Range(«I1»).Value, которое ссылается на значение в ячейке I1.

   

   Рис. 11.21. Рынок сбыта Калифорния жестко задан в VBA-коде записанного макроса

   На этом этапе код макроса должен выглядеть так, как показано на рис. 11.22. После изменения макроса закройте редактор Visual Basic и вернитесь к электронной таблице.



Рис. 11.22. Замените значение «Калифорния» выражением Activesheet.Range(«I1»).Value и закройте редактор Visual Basic

7. Осталось только обеспечить выполнение макроса при выборе рынка сбыта в раскрывающемся меню. Щелкните правой кнопкой мыши на раскрывающемся меню и выберите параметр Назначить макрос. Выберите макрос SynchMarket и щелкните на кнопке ОК.
8. Скройте строки и столбцы с полями страниц в сводных таблицах, а также созданный вами список рынков и формулы индекса.

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

Рис. 11.23. Отчет сводной таблицы, готовый к использованию

При выборе в раскрывающемся списке нового элемента размеры столбцов автоматически изменяются, чтобы вместить все отображаемые в них данные. Подобное поведение программы порядком надоедает при форматировании шаблона рабочего листа. Можно предотвратить его, если щелкнуть на сводной таблице правой кнопкой мыши и выбрать команду Параметры сводной таблицы (PivotTable Options). На экране появится одноименное диалоговое окно, в котором необходимо сбросить флажок Автоматически изменять ширину столбцов при обновлении (Autofit Column Widths on Update).