Как обойти ограничение Excel и сделать выпадающий список зависимым

Рубрика: 7. Полезняшки Excel

Недавно дочь обратилась с вопросом, нельзя ли в Excel выпадающий в ячейке список сделать контекстным, например, зависящим от содержания ячейки, находящейся слева от ячейки со списком (рис. 1)? Я довольно давно не использовал в работе выпадающие списки, поэтому для начала решил освежить свои знания по вопросу проверки данных в Excel.

Рис. 1. Состав выпадающего списка зависит от содержания соседней ячейки

Скачать заметку в формате Word или pdf, примеры в формате Excel2007

Команда Проверка данных находится на вкладке Данные, область Работа с данными.

Примечание. Иногда команда Проверка данных может быть недоступна:

  • Возможно, в настоящее время вводятся данные. Во время ввода данных в ячейку команда Проверка данных недоступна. Чтобы завершить ввод данных, нажмите клавишу ВВОД или ESC.
  • Возможно, лист защищен или является общим. Если лист защищен или является общим, изменить параметры проверки данных невозможно. Снимите защиту или отмените режим «общий».
  • Возможно, таблица Excel связана с узлом SharePoint. Невозможно добавить проверку данных в таблицу Excel, которая связана с узлом SharePoint. Чтобы добавить проверку данных, необходимо удалить связь таблицы Excel или преобразовать ее в диапазон.

К сожалению, Excel в своем стандарте позволяет делать списки только на основе:

  • имени массива
  • диапазона ячеек
  • прямого перечисления элементов списка (рис. 2).

Примечание. Элементы списка вводите через стандартный разделитель элементов списка Microsoft Windows (в русском Excel по умолчанию это точка с запятой).

Рис. 2. Возможные источники списка: вверху – имя массива; посередине – диапазон ячеек; внизу – элементы списка

Попытка ввести формулу в поле Источник диалогового окна Проверка вводимых значений заканчивается неудачей (рис. 3). Видно, что Excel не воспринял значение ячейки D2 ("цвет"), как имя массива, и просто включил это значение в качестве единственного элемента списка.

Рис. 3. Недопустимый источник списка – формула

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

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

Чтобы указать, как обрабатывать пустые (нулевые) значения, установите или снимите флажок Игнорировать пустые ячейки. При включенном флажке ячейку можно будет оставить пустой.

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

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

И всё же в Excel есть одна функция непрямого действия. На английском языке у нее говорящее название – INDIRECT. На русском – название функции ни о чем – ДВССЫЛ… В чем же заключается непрямое действие? В отличие от других функций Excel, ДВССЫЛ возвращает не значение, хранящееся в ячейке, а ссылку, хранящуюся в ячейке. Непонятно? Сам «продирался» через это с трудом 🙂 Попробую пояснить. Вот что написано в справке Excel: ДВССЫЛ – возвращает ссылку, заданную текстовой строкой. Ссылки немедленно вычисляются для вывода их содержимого (рис. 4).

Рис. 4. Как работает функция ДВССЫЛ: вверху – формулы, внизу – значения

Понимаю, что если вы впервые столкнулись с функцией ДВССЫЛ, то разобраться сложно. Пробуйте, экспериментируйте, и понимание со временем придет.

Итак, еще раз, ДВССЫЛ возвращает ссылку, а не значение, хранящееся в ячейке. Ссылка немедленно вычисляется, и выводится ее значение (или содержимое). Именно это свойство позволит нам ввести непрямую ссылку на соседнюю ячейку так, что вернется не значение, хранящееся в этой соседней ячейке (как на рис. 3), а ссылка, хранящаяся в ячейке, эта ссылка тут же вычисляется, и получается имя массива (рис. 5).

Рис. 5. Формирование списка, зависящего от значения в левой ячейке

Примечание. Ссылка в формуле =ДВССЫЛ(D2) должна быть относительной (D2), а не абсолютной ($D$2). Подробнее об этом см. раздел Тип ссылок на ячейки в формулах для проверки данных заметки Excel. Проверка данных.

Если настоящая заметка не подсказала вам путь к решению вашей проблемы, рекомендую почитать Связанные (зависимые) выпадающие списки (обратите также внимание на многочисленные комментарии к ней).

Комментарии: 10 комментариев

Огромное Вам спасибо! Вы мне очень помогли!

Большое человеческое спасибо

Спасибо за Ваше разъяснение этой удивительной функции !!!!!

Браво!

Сделал выпадающий список, всё работает. спасибо!
Формулу взял такую: =ДВССЫЛ("Регионы["&$T31&"]"). (Регионы — это название таблицы).
В каждой колонке разное количество наименований, и в выпадающем списке отображается "КУЧА" пустых строк.
Как сделать, чтобы отображались только заполненые?
Если можно, то просьба подкорректировать мою формулу.
У меня задача, чтобы 2 поля завили от 3его…
то есть, в 1ом выбрал страну, от этого должны показываться только регионы этой страны(2столбец) и в 3ем показываются магазины в этой стране.

Сергей, пришлите Excel-файл в личку. Если смогу, помогу.

Спасибо за ответ!

Сам смог разобраться, но через другую формулу.
=СМЕЩ($AS$1;ПОИСКПОЗ($K$13;$AS:$AS; 0)-1;1;СЧЁТЕСЛИ($AS:$AS;$K$13);1)

У меня вопрос, может сможете мне помочь.
Я сделал выплывающий список телефонов, в соседней ячейке имя человека.

Когда я сортирую имена людей по алфавиту, списки телефонов путаются,т.е. мне нужно сделать зависимую проверку данных от соседней ячейки.
как?

Большое спасибо за материал, разобрался, очень полезно!

Однако заодно встал вопрос — а можно ли Проверку данных использовать в контексте оператора ЕСЛИ? То есть ЕСЛИ(;;)? Или настолько сложные условия не реализуемые?
Вопрос встал с необходимостью при смене значения ячейки, к которой привязано условия ДВССЫЛ, присваивать ячейке, на которой задано выпадение списка ДВССЫЛ, значение по умолчанию (оно является константой для каждого из вариантов выбора)

Пробовал через оператор ЕСЛИ с типом данных — не вариант, т.к. получается, что подхватываются одновременно список по ДВССЫЛ и заданное значение через &, как следствие — ошибка типа данных.

Gimalon, не понял)) Пришлите файл на s_bag@mail.ru


Прокомментировать