Трюк №23. Как в Excel определить поддержку нескольких списков в поле со списком
При работе с несколькими списками можно переключаться между ними, используя комбинацию переключателей и поля со списком (ComboBox).
В Excel предусмотрено множество способов выбора элементов списка, являются ли они именами, продуктами, днями недели — любыми элементами, составляющими список. Но чтобы получить доступ к вариантам одновременно нескольких списков, обычно требуется несколько элементов управления, например, три поля со списком (ComboBox) с панели инструментов Формы (Forms).
Вместо этого поле со списком можно использовать в комбинации с переключателями (option button, radio button) — они также находятся на панели инструментов Формы (Forms), чтобы список изменялся автоматически в зависимости от того, какой переключатель выбран. Чтобы увидеть, как это работает, введите числа от 1 до 7 в диапазоне А1:А7 на новом рабочем листе. В диапазоне В1:В7 введите дни недели, начиная с понедельника (Monday) и заканчивая воскресеньем (Sunday). В диапазоне С1:С7 введите названия месяцев от января (January) до июля (July).
[stextbox id=»warning»]Возможности автоматического заполнения Excel могут упростить эту задачу. Введите 1 в ячейке А1, выделите ячейку А1, нажмите клавишу Ctrl и левой кнопкой мыши щелкните маркер заполнения. Удерживая левую кнопку мыши и клавишу Ctrl, перетащите указатель к строке 7. Excel заполнит столбец числами. Затем введите Monday в ячейке В1 и дважды щелкните маркер заполнения этой ячейки. Введите January в ячейке С1 и дважды щелкните маркер заполнения этой ячейки. Excel заполнит столбец днями недели и месяцами.[/stextbox]
Выберите команду Разработчик → Вставить → Формы (Developer → Insert → Forms) и дважды щелкните элемент управления «переключатель» на панели инструментов. Затем три раза щелкните таблицу в разных местах, чтобы разместить на ней три кнопки выбора.
Теперь щелкните элемент управления Поле со списком (ComboBox) и снова щелкните таблицу, чтобы поместить на нее это поле. Используя маркеры перетаскивания, придайте полю со списком нужный размер и разместите кнопки выбора прямо под полем со списком.
Правой кнопкой мыши щелкните первую кнопку выбора, в контекстном меню выберите команду Изменить текст (Edit Text), а затем замените слова Перекл. 1 (Option Button 1) словом Numbers. To же самое сделайте для Перекл. 2 (Option Button 2), заменив надпись словом Weekdays, и для Перекл. 3 (Option Button 3), заменив фразу словом Months (рис. 2.8).
Рис. 2.8. Поле с несколькими списками, управляемое переключателями[/stextbox]
Удерживая клавишу Ctrl, щелкните каждый переключатель, чтобы все три были выделены, а затем щелкните правой кнопкой мыши и в контекстном меню выберите команду Формат объекта → Элемент управления (Format Control → Control). Выберите связь с ячейкой $F$1 (удостоверьтесь, что связь абсолютная, используйте знак доллара). В ячейке Е6 введите следующую формулу: =ADDRESS(1;$F$1)&":"&ADDRESS(7;$F$1), в русской версии Excel =АДРЕС(1;$F$1)&":"&АДРЕС(7;$F$1). Выберите команду Формулы → Присвоить (Formulas → Define Name). В поле Имя (Names in workbook) введите MyRange, а в поле Формула (Refers to) введите следующее: =INDIRECT($E$6), в русской версии Excel =ДВССЫЛ($Е$6).
Щелкните кнопку Add (Добавить), затем кнопку ОК. Правой кнопкой мыши щелкните элемент управления «поле со списком» и выберите команду Формат объекта → Элемент управления (Format Control → Control). Для параметра Форматировать список по диапазону (Input range) введите MyRange и укажите связь с ячейкой $G$1, затем щелкните кнопку ОК. Теперь можно выбрать один из переключателей, и список в поле со списком автоматически изменится в соответствии с установленным переключателем.
При настройке такого списка в собственных таблицах рекомендуется для полей со списками и списков использовать ячейки, которые не видны на экране. Можно даже скрыть эти ячейки от пользователей, чтобы связи всегда оставались на своем месте. Кроме того, необходимо модифицировать две функции АДРЕС (ADDRESS), чтобы они отражали ваш диапазон ячеек. В функции АДРЕС (ADDRESS) для этого примера 1 отражает номер первой строки, используемой в списках, а 7 — номер последней строки.