Трюк №53. Взаимодействие с диаграммами при помощи пользовательских элементов управления
Чтобы сделать диаграммы действительно интерактивными, можно использовать один или несколько динамических диапазонов, а также подключить полосу прокрутки или раскрывающийся список панели инструментов Формы (Forms), чтобы отображать те значения, которые ваши читатели хотели бы изучить более внимательно.
Как вы увидели в предыдущем трюке, чтобы добавить диаграммам гибкости, можно воспользоваться динамическими именованными диапазонами. Но также при помощи динамических именованных диапазонов можно создавать интерфейсы, управляющие данными, которые выводятся на диаграмму. Связав динамические именованные диапазоны с пользовательскими элементами управления, вы позволите пользователям изменять данные диаграммы при помощи элементов управления, которые одновременно будут обновлять данные на рабочем листе или наоборот.
Использование динамического именованного диапазона, связанного с полосой прокрутки
В этом примере при помощи полосы прокрутки вы будете отображать значения за определенный месяц периода, включающего 12 месяцев. Полоса прокрутки используется для изменения количества месяцев, для которых строится диаграмма. Также значение полосы прокрутки применяется в динамическом диапазоне, который, в свою очередь, используется как источник данных для диаграммы.
Для начала введите некоторые данные (рис. 5.9). Выбрав команду Вставка → Имя → Присвоить (Insert → Name → Define), создайте динамический именованный диапазон и назовите его SALES_PERIOD. В поле Формула (Refers to) введите следующую формулу: =OFFSET($B$5;0;0;$C$5;1), в русской версии Excel =СМЕЩ($В$5;0;0;$С$5;1).
Благодаря функции СМЕЩ (OFFSET) ячейка $С$5 заставляет диапазон, названный SALES_PERIOD, расширяться вверх и вниз, если число в ячейке $С$5 изменяется. Другими словами, если в ячейку $С$5 ввести число 5, диапазон будет включать ячейки В5:В10.
Рис. 5.9. Данные на рабочем листе для динамической диаграммы, связанной с полосой прокрутки
[stextbox id=»info»]Если вы не хотите, чтобы пользователи видели ячейку С5, можно скрыть ее содержимое, щелкнув ячейку правой кнопкой мыши и в контекстном меню выбрав команду Формат ячеек → Все форматы (Format Cells → Custom). Введите формат ;;; и щелкните на кнопке ОК. На рис. 5.9 содержимое ячейки С5 скрыто.[/stextbox]
Создайте диаграмму (лучше всего подходит гистограмма или график). На шаге 2 мастера диаграмм перейдите на вкладку Ряд (Series) и в поле Значения (Values) введите =
После того как диаграмма создана, нужно добавить полосу прокрутки с панели инструментов Формы (Forms). Для этого проще всего щелкнуть правой кнопкой мыши серую область вверху экрана (которая называется областью панелей инструментов) и выбрать команду Формы (Forms). На экране появится панель инструментов Формы (Forms) (рис. 5.10).
Рис. 5.10. Динамическая диаграмма, связанная с полосой прокрутки
Щелкните значок полосы прокрутки. Вставив полосу прокрутки на экран, выделите ее и переместите на диаграмму. Теперь правой кнопкой мыши щелкните полосу прокрутки и в контекстном меню выберите команду Формат объекта (Format Control). Укажите минимальное значение 1, максимальное значение 12 и связь с ячейкой $С$5. Получившаяся диаграмма будет выглядеть, как на рис. 5.10.
Динамический именованный диапазон, связанный с раскрывающимся списком
Динамическую диаграмму можно связать и с раскрывающимся списком. Начав с данных (см. рис. 5.9), добавьте динамический диапазон, который будет использоваться как источник данных для диаграммы. Динамический диапазон будет связан с раскрывающимся списком. Вы сможете просмотреть результаты теста любого студента из группы студентов. В раскрывающемся списке нужно выбрать имя студента, результаты которого вы хотите просмотреть.
В ячейку В12 вставьте формулу =AVERAGE(B6:B11) и скопируйте ее в этой же строке до ячейки F12 (рис. 5.11).
Рис. 5.11. Динамическая диаграмма, связанная с раскрывающимся списком
Выберите команду Вставка → Имя I Присвоить (Insert → Name → Define) и создайте динамический диапазон с именем STUDENTS. В поле Формула (Refers to) введите следующую формулу: =OFFSET($A$5;$G$6;1;1;5), в русской версии Excel =СМЕЩ($A$5;$G$6;1;1;5). Создайте еще один динамический диапазон, на этот раз с именем STUDENT_NAME, и в поле Формула (Refers to) введите формулу =OFFSET($A$5;$G$6;0;1;1), в русской версии Excel =СМЕЩ($A$5;$G$6;0;1;1). Благодаря ссылке на ячейку $G$6 в формуле СМЕЩ (OFFSET) вы заставляете диапазоны STUDENTS и STUDENT_NAME расширяться вверх и вниз, когда число в ячейке $G$6 изменяется.
Теперь создайте диаграмму в виде обычной гистограммы, указав для нее диапазон A11:F12. На втором шаге мастера диаграмм перейдите на вкладку Ряд (Series) и в поле Значения (Values) для первого ряда (Frank) введите =
Теперь на лист нужно поместить поле со списком с панели инструментов Формы (Forms). Сделав это, выберите поле со списком (ComboBox), щелкните его правой кнопкой мыши, в качестве входного диапазона введите $А$6:$А$:11 и укажите связь с ячейкой $G$6. Наконец, в пустую ячейку, например, В4 вставьте функцию CONCATENATE (СЦЕПИТЬ), например, так: =CONCATENATE(«Test Result for «;INDEX(A6:A11;G6))), в русской версии Excel:=СЦЕПИТЬ(«Test Result for «;ИНДЕКС(A6:A11;G6))). Щелкните стрелку поля со списком (рис. 5.12), выберите имя студента и вы увидите его результаты теста.
Рис. 5.12. Полная динамическая диаграмма, связанная с раскрывающимся списком