Как создать выпадающий список в Microsoft Excel
Раскрывающиеся списки ограничивают количество записей в поле ввода. Они помогают предотвратить опечатки и опечатки. И Excel поддерживает удивительно универсальные функции выпадающего списка. Таким образом, если вы хотите использовать Excel для форм или сбора данных, упростите пользователям выбор элементов с раскрывающимися списками.
Используйте раскрывающийся список в ячейке, когда для этой ячейки доступны определенные параметры. Например, вы можете создать выпадающие списки, содержащие такие параметры, как мужчина и женский, да и нетили любой другой пользовательский список опций.
Мы покажем вам, как добавить раскрывающиеся списки в электронные таблицы Excel.
Как обойти ограничение Excel и сделать выпадающий список зависимым
- Создайте именованный диапазон: Этот шаг необходим для создания списка, который мы будем использовать в раскрывающемся списке.
- Добавить проверку данных: Это не столь очевидный шаг, когда вы заполняете ячейку для отображения выпадающего списка.
- Добавить именованный диапазон к проверке данных: Наконец, вы соберете все вместе и определите источник раскрывающегося списка (т. Е. Ранее названный вами диапазон).
- Установить входное сообщение для проверки данных: Этот шаг не является обязательным. Это позволяет вам добавить всплывающее сообщение для руководства ваших пользователей электронных таблиц.
Если вы не хотите сохранять какие-либо значения при удалении раскрывающегося списка, вы можете скопировать пустую ячейку и вставить ее в ячейку, содержащую раскрывающийся список. Раскрывающийся список удаляется, и ячейка становится пустой ячейкой.
Пошаговая инструкция
Предварительные замечания
В файле примера выпадающий список с поиском реализован сразу в двух вариантах: для обычного диапазона (лист Range ) и для умной таблицы (лист Table ). Мы будим эти варианты обсуждать одновременно, отмечая их различия.
Шаг 1. Готовим таблицу для списка
Подготовьте таблицу с четырьмя колонками: Город (или то, что вам нужно), Статус , Индекс , Фильтр . Заполните столбец Город значениями. В остальных трёх колонках будут формулы, которые мы обсудим ниже. Я всем рекомендую использовать умную таблицу, так как это значительно проще.
Шаг 2. Формулы для столбца Статус
На примере ячейки F2 рассмотрим формулу, аналогичную для всего столбца Статус (столбец F ). Из F2 формулу можно протягивать вниз до конца, а в случае умной таблицы Excel это сделает за вас. Это также относится ко всем формулам, которые мы будем обсуждать в этой статье.
$B$2 — ячейка, с которой будет связан выпадающий список (добавляется на шаге 6). Что значит связано? Всё, что вы введёте в выпадающий список, тут же отразится в ячейке B2 .
= НЕ( ЕОШИБКА ( ПОИСК ( $B$2 ; $E2 ) ) )
=NOT( ISERROR( SEARCH( $B$2; $E2) ) )
= НЕ ( ЕОШИБКА ( ПОИСК ( $B$2 ; [@ Город ]) ) )
=NOT( ISERROR( SEARCH( $B$2; [@Город]) ) )
[@ Город ] — на языке структурных формул умных таблиц это ссылка на ячейку столбца Город в той же строке, в которой находится сама формула. Поскольку ссылка идёт внутри таблицы, то имя самой таблицы в формуле можно не использовать. В остальном всё — тоже самое.Шаг 3. Формула для столбца Индекс
Функция ЕСЛИ отсекает все значения в F , которые не равны ИСТИНА. Функция СЧЁТЕСЛИ подсчитывает количество значений ИСТИНА в F .
= ЕСЛИ( $F2 ; СЧЁТЕСЛИ ( $F$2:$F2 ; ИСТИНА ); «»)
=IF( $F2; COUNTIF( $F$2:$F2; TRUE ); «»)
= ЕСЛИ ( [@ Статус ]; СЧЁТЕСЛИ ( $F$2 :[@ Статус ]; ИСТИНА ); «»)
=IF( [@Статус]; COUNTIF( $F$2:[@Статус]; TRUE ); «»)
Шаг 4. Формула для столбца Фильтр
Теперь наша задача, опираясь на столбец Индекс , сформировать в столбце H отфильтрованный список городов, который необходимо показывать в выпадающем списке.
= ЕСЛИОШИБКА( ИНДЕКС ( стлГород ; ПОИСКПОЗ ( ЧСТРОК ( $G$2:$G2 ); стлИндекс ; 0) ); «»)
=IFERROR( INDEX( стлГород; MATCH( ROWS($G$2:$G2); стлИндекс; 0) ); «» )
Обратите внимание на динамический именованный диапазон стлГород и стлИндекс, которые мы вынуждены создавать для случая диапазона, чтобы придать решению должный уровень универсальности. Техника, по которой созданы эти именованные диапазоны разобрана тут.
= ЕСЛИОШИБКА ( ИНДЕКС ( [ Город ]; ПОИСКПОЗ ( ЧСТРОК ( $G$2 :[@ Индекс ]); [ Индекс ]; 0) ); «»)
=IFERROR( INDEX( [Город]; MATCH( ROWS($G$2:[@Индекс]); [Индекс]; 0) ); «» )
Не путайте: [ Индекс ] — ссылка на весь столбец, а [@ Индекс ] — ссылка на ячейку из этого столбца в текущей строке. Никакие дополнительные именованные диапазоны нам создавать нет никакой необходимости, так как мы пользуемся встроенным в умные таблицы сервисом при ссылке на столбцы.
Формула ЧСТРОК ( $G$2:$G2 ) используется для генерации последовательных номеров от 1 (для второй строки) до N (в строке N+1), равному количеству найденных подстрок. Просто генерируется диапазон соответствующего размера, а формула ЧСТРОК возвращает его высоту в строках.
Формула ПОИСКПОЗ ищет номер реальной строки, содержащий соответствующий индекс. Например, в столбце Фильтр мы видим Магнитогорск на третьей позиции, но в реальности он взят из E45 , так как в G45 стоит цифра 3, которую мы и нашли через ПОИСКПОЗ . То есть ПОИСКПОЗ сказал нам, что Магнитогорск находится в 45-й строке, а извлекли мы его оттуда уже при помощи формулы ИНДЕКС .
Если же при извлечении возникает ошибка (текущая строка находится ниже строки N+1), то формула возвращает пустую строку. За это отвечает ЕСЛИОШИБКА .
Как создать выпадающий список в Microsoft Excel • Оки Доки
Если вы просто хотите скопировать выпадающий список с проверкой данных, но НЕ с форматированием, выберите ячейку и скопируйте ее, как правило, используя Ctrl + C, Затем перейдите к Дом вкладка и нажмите Вставить в буфер обмена раздел. Выбрать Специальная паста,
