Трюк №24. Как в Excel создать списки проверки, меняющихся на основе выбранного значения в другом списке

Требования к проверке могут изменяться в зависимости от контекста проверки. Однако вы можете создать таблицу, где один из списков проверки изменяется в зависимости от выбора в другом списке.

Чтобы заставить этот трюк работать, сначала нужно настроить рабочий лист, добавив какие-то данные. На чистом рабочем листе с именем Lists в ячейке А1 введите заголовок Objects. В ячейке В1 введите заголовок Corresponding List. В ячейках А2:А5 введите слово Сап. В ячейках Аб:А9 введите слово Sofa. В ячейках А10:А13 введите слово Shower. В ячейках АН:А17 введите слово Саг. Затем, начиная с ячейки В2 и заканчивая ячейкой В17, введите следующие слова (соответствующие списку Objects): Tin, Steel, Opener, Lid, Bed, Seat, Lounge, Cushion, Rain, Hot, Cold, Warm, Trip, Journey, Bonnet и Boot. В ячейке С1 введите заголовок Validation List. Затем, чтобы создать список уникальных элементов, введите слово Сап в ячейку С2, слово Sofa в ячейку СЗ, слово Shower в ячейку С4 и слово Саг в ячейку С5.
[stextbox id=»warning»]Список уникальных элементов можно также создать с помощью средства Расширенный фильтр (Advanced Filter). Выделите ячейки А1:А17, выберите команду Главная → Фильтр → Расширенный фильтр (Home → Filter → Advanced Filter) и установите флажки Только уникальные записи (Unique Records Only) и Фильтровать список на месте (Filter the List in Place). Щелкните кнопку OK, затем выделите ячейки А2:А14 (которые будут включать скрытые ячейки). Скопируйте их и вставьте в ячейку А18. Выберите команду Главная → Фильтр → Отобразить все (Home → Filter → Show All), выберите список уникальных объектов, вырежьте их и вставьте в ячейку С2. Теперь у вас есть список![/stextbox]
Выберите команду Формулы → Присвоить Имя (Insert → Define Name) и в поле Имя (Names in Workbook) введите слово Objects. В поле Формула (Refers to) введите следующую формулу и щелкните на кнопке Добавить (Add): =OFFSET($A$2;0;0,COUNTA($A$l:$A$20);1), в русской версии Excel =СМЕЩ($А$2;0;0;СЧЁТЗ($А$1:$А$20);1).

В поле Имя (Names in Workbook) введите слово ValList, а в поле Формула (Refers to) введите $С$2:$С$5. Щелкните на кнопке Добавить (Add). Теперь вставьте еще один рабочий лист, назовите его Sheet1 и приготовьтесь к тому, что вам придется заставить эти странные данные работать.

Сделайте лист Sheet1 активным и выберите пункт меню Вставка → Имя → Присвоить (Insert → Name → Define). В поле Имя (Names in Workbook) введите слово CorrespondingList, а в поле Формула (Refers to) введите следующую длинную формулу и щелкните на кнопке Добавить (Add): =OFFSET(INDIRECT(ADDRESS(MATCH(VallCell.Objects;0)+1;2..."Lists"));0;0;COUNTIF(Objects.VallCell);1), в русской версии Excel =СМЕЩ(ДВССЫЛ(АДРЕС(ПОИСКПОЗ(VallCell.Objects;0)+1;2..."Lists"));0;0;СЧЕТЕСЛИ(Objects.VallCell);1).

В поле Имя (Names in Workbook) введите слово VallCell, а в поле Формула (Refers to) введите $D$6 и щелкните на кнопке Добавить (Add). В поле Имя (Names in Workbook) введите слово Val2Cell, а в поле Формула (Refers to) введите $Е$6 и снова щелкните на кнопке Добавить (Add). Щелкните на кнопке ОК, чтобы вернуться на лист Sheet1 и затем выберите ячейку $D$6.

Это долгий процесс, но мы уже почти закончили.

Выберите команду Данные → Проверка → Параметры (Data → Validation → Settings). В поле Тип данных (Allow) выберите пункт Список (List), а в поле Источник (Source) введите =ValList. Удостоверьтесь, что установлен флажок Список допустимых значений (In-Cell), и щелкните на кнопке ОК. Выделите ячейку Е6 и снова выберите команду Данные → Проверка → Параметры (Data → Validation → Settings). В поле Тип данных (Allow) выберите пункт Список (List), а в поле Источник (Source) напечатайте =CorrespondingList. Затем удостоверьтесь, что установлен флажок Список допустимых значений (In-Cell), и щелкните на кнопке ОК. Выберите один из объектов в списке проверки в ячейке D6, и список проверки в ячейке Е6 автоматически изменится в соответствии с выбранным объектом.

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