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

Один из параметров, доступных при проверке данных, это параметр Список (List), то есть удобный раскрывающийся список, из которого пользователи могут выбрать определенные элементы. Но есть один недостаток — если вы попытаетесь сослаться на список, находящийся на другом рабочем листе, то получите сообщение, что это невозможно. К счастью, при помощи очередного трюка невозможное можно сделать возможным.

Заставить Excel при проверке данных ссылаться на список на другом рабочем листе можно двумя способами — при помощи именованных диапазонов и функции ДВССЫЛ (INDIRECT).

Способ 1. Именованные диапазоны

Вероятно, самый простой и быстрый способ выполнить эту задачу — присвоить имя диапазону, где размещается список. Для этого упражнения мы предполагаем, что диапазону присвоено имя MyRange. Выделите ячейку, в которой должен будет появиться этот раскрывающийся список, и выберите команду Данные → Проверка (Data → Validation). В поле Тип данных (Allow) выберите Список (List), а в поле Источник (Source) введите =MyRange. Щелкните на кнопке ОК. Теперь список (который находится на другом рабочем листе) можно использовать как список проверки.

Способ 2. Функция ДВССЫЛ (INDIRECT)

Функция ДВССЫЛ (INDIRECT) позволяет ссылаться на ячейку, содержащую текст, который представляет собой адрес ячейки. Ячейку, содержащую функцию ДВССЫЛ (INDIRECT), можно использовать как ссылочную ячейку и применять эту возможность для связи с рабочим листом, где находится нужный список.

Предположим, список находится на листе Sheet1 в диапазоне $А$1:$А$10. Щелкните любую ячейку на другом рабочем листе, где должен будет появиться этот список проверки. Затем выберите команду Данные → Проверка (Data → Validation) и в поле Тип данных (Allow) выберите пункт Список (List). В поле Источник (Source) введите следующую функцию: =INDIRECT("Sheet1!$А$1:$А$10"), в русской версии Excel: =ДВССЫЛ("Sheet1!$А$1:$А$10"). Убедитесь, что флажок Список допустимых значений (In-Cell) установлен, и щелкните на кнопке ОК. Список на листе Sheetl окажется в вашем раскрывающемся списке проверки.

Если имя рабочего листа, на котором расположен список, содержит пробелы, функцию ДВССЫЛ (INDIRECT) нужно записать так: =INDIRECT("'Sheet 1'!$А$1:$А$10"), в русской версии Excel: =ДВССЫЛ("Sheet 1!$А$1:$А$10"). Различие заключается в том, что здесь после первой кавычки стоит один апостроф, а второй апостроф находится перед восклицательным знаком. Апострофы ограничивают название листа.
[stextbox id=»warning»]Полезно использовать одиночные апострофы всегда, независимо от того, содержит имя, пробелы или нет. С апострофами вы все так же сможете ссылаться на листы с именами без пробелов, но это и упростит внесение изменений позже.[/stextbox]

Преимущества и недостатки обоих методов

У именованных диапазонов и функции ДВССЫЛ (INDIRECT) при использовании их для связи со списком на другом рабочем листе есть преимущества и недостатки.

Преимущество использования именованного диапазона в данном сценарии заключается в том, что изменение названия листа не повлияет на список проверки. nЭто подчеркивает недостаток функции ДВССЫЛ (INDIRECT) — а именно, любое изменение названия листа не будет автоматически отражаться в функции ДВССЫЛ (INDIRECT), поэтому придется вручную изменить функцию, указав новое название листа.

Преимущество функции ДВССЫЛ (INDIRECT): если из именованного диапазона будет удалена первая ячейка или строка либо последняя ячейка или строка, то именованный диапазон вернет ошибку #REF!. В этом недостаток именованных диапазонов — если удалить ячейки или строки из именованного диапазона, эти изменения не повлияют на список проверки.