Как создать раскрывающийся список в Excel (единственное руководство, которое вам нужно)

Выпадающий список - отличный способ дать пользователю возможность выбрать из заранее определенного списка.

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

Выпадающие списки довольно часто встречаются на веб-сайтах / в приложениях и очень интуитивно понятны для пользователя.

Посмотреть видео - Создание раскрывающегося списка в Excel

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

Как создать выпадающий список в Excel

В этом разделе вы узнаете, как создать раскрывающийся список Excel:

1. Использование данных из ячеек.
2. Ввод данных вручную.
3. Используя формулу СМЕЩЕНИЕ.

# 1 Использование данных из ячеек

Допустим, у вас есть список предметов, как показано ниже:

Вот шаги для создания раскрывающегося списка Excel:

1. Выберите ячейку, в которой вы хотите создать раскрывающийся список.
2. Перейдите в Data -> Data Tools -> Data Validation.
3. В диалоговом окне «Проверка данных» на вкладке «Параметры» выберите «Список» в качестве критерия проверки.
   • Как только вы выбираете Список, появляется исходное поле.
4. В поле «Источник» введите = $ A $ 2: $ A $ 6 или просто щелкните поле «Источник», выберите ячейки с помощью мыши и нажмите «ОК». Это вставит раскрывающийся список в ячейку C2.
   • Убедитесь, что в раскрывающемся списке установлен флажок (по умолчанию). Если этот параметр не установлен, в ячейке не отображается раскрывающийся список, однако вы можете вручную ввести значения в список.

Примечание: Если вы хотите создать раскрывающиеся списки в нескольких ячейках за один раз, выберите все ячейки, в которых вы хотите его создать, а затем выполните указанные выше действия. Убедитесь, что ссылки на ячейки являются абсолютными (например, $ A $ 2), а не относительными (например, A2, или A $ 2, или $ A2).

# 2 Ввод данных вручную

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

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

• Выберите ячейку, в которой вы хотите создать раскрывающийся список (в этом примере ячейка C2).
• Перейдите в Data -> Data Tools -> Data Validation.
• В диалоговом окне «Проверка данных» на вкладке «Параметры» выберите «Список» в качестве критерия проверки.
  • Как только вы выбираете Список, появляется исходное поле.
• В поле источника введите Да, Нет
  • Убедитесь, что в раскрывающемся списке установлен флажок.
• Щелкните ОК.

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

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

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

# 3 Использование формул Excel

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

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

Например, предположим, что у вас есть набор данных, как показано ниже:

Вот шаги для создания раскрывающегося списка Excel с помощью функции СМЕЩЕНИЕ:

• Выберите ячейку, в которой вы хотите создать раскрывающийся список (в этом примере ячейка C2).
• Перейдите в Data -> Data Tools -> Data Validation.
• В диалоговом окне «Проверка данных» на вкладке «Параметры» выберите «Список» в качестве критерия проверки.
  • Как только вы выбираете Список, появляется исходное поле.
• В поле Источник введите следующую формулу: = СМЕЩЕНИЕ ($ A $ 2,0,0,5)
  • Убедитесь, что в раскрывающемся списке установлен флажок.
• Щелкните ОК.

Это создаст раскрывающийся список, в котором перечислены все названия фруктов (как показано ниже).

Примечание: Если вы хотите создать раскрывающийся список из нескольких ячеек за один раз, выберите все ячейки, в которых вы хотите его создать, а затем выполните указанные выше действия. Убедитесь, что ссылки на ячейки являются абсолютными (например, $ A $ 2), а не относительными (например, A2, или A $ 2, или $ A2).

Как работает эта формула ??

В приведенном выше случае мы использовали функцию OFFSET для создания раскрывающегося списка. Он возвращает список элементов из ра

Он возвращает список элементов из диапазона A2: A6.

Вот синтаксис функции СМЕЩЕНИЕ: = СМЕЩЕНИЕ (ссылка, строки, столбцы, [высота], [ширина])

Он принимает пять аргументов, где мы указали ссылку как A2 (начальная точка списка). Строки / столбцы указаны как 0, поскольку мы не хотим смещать ссылочную ячейку. Высота указана как 5, так как в списке пять элементов.

Теперь, когда вы используете эту формулу, она возвращает массив со списком пяти фруктов в A2: A6. Обратите внимание: если вы введете формулу в ячейку, выделите ее и нажмете F9, вы увидите, что она возвращает массив названий фруктов.

Создание динамического раскрывающегося списка в Excel (с помощью OFFSET)

Вышеупомянутый метод использования формулы для создания раскрывающегося списка может быть расширен для создания динамического раскрывающегося списка. Если вы используете функцию СМЕЩЕНИЕ, как показано выше, даже если вы добавите больше элементов в список, раскрывающийся список не будет обновляться автоматически. Вам придется вручную обновлять его каждый раз, когда вы меняете список.

Вот способ сделать его динамичным (и это всего лишь небольшая поправка в формуле):

• Выберите ячейку, в которой вы хотите создать раскрывающийся список (в этом примере ячейка C2).
• Перейдите в Data -> Data Tools -> Data Validation.
• В диалоговом окне «Проверка данных» на вкладке «Параметры» выберите «Список» в качестве критерия проверки. Как только вы выбираете Список, появляется исходное поле.
• В поле источника введите следующую формулу: = СМЕЩЕНИЕ ($ A $ 2,0,0, СЧЁТЕСЛИ ($ A $ 2: $ A $ 100, ””))
• Убедитесь, что в раскрывающемся списке установлен флажок.
• Щелкните ОК.

В этой формуле я заменил аргумент 5 на СЧЁТЕСЛИ ($ A $ 2: $ A $ 100, ””).

Функция СЧЁТЕСЛИ подсчитывает непустые ячейки в диапазоне A2: A100. Следовательно, функция СМЕЩЕНИЕ настраивается для включения всех непустых ячеек.

Примечание:

• Для этого НЕ ДОЛЖНО быть пустых ячеек между заполненными ячейками.
• Если вы хотите создать раскрывающийся список из нескольких ячеек за один раз, выберите все ячейки, в которых вы хотите его создать, а затем выполните указанные выше действия. Убедитесь, что ссылки на ячейки являются абсолютными (например, $ A $ 2), а не относительными (например, A2, или A $ 2, или $ A2).

Копировать вставку раскрывающихся списков в Excel

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

Например, если у вас есть раскрывающийся список в ячейке C2, и вы хотите применить его также к C3: C6, просто скопируйте ячейку C2 и вставьте ее в C3: C6. Это скопирует раскрывающийся список и сделает его доступным в C3: C6 (вместе с раскрывающимся списком он также скопирует форматирование).

Если вы хотите скопировать только раскрывающийся список, а не форматирование, выполните следующие действия:

• Скопируйте ячейку с раскрывающимся списком.
• Выберите ячейки, в которые вы хотите скопировать раскрывающийся список.
• Перейдите на главную -> Вставить -> Специальная вставка.
• В диалоговом окне «Специальная вставка» выберите «Проверка в параметрах вставки».
• Щелкните ОК.

Это скопирует только раскрывающийся список, но не форматирование скопированной ячейки.

Осторожно при работе с раскрывающимся списком Excel

Будьте осторожны при работе с раскрывающимися списками в Excel.

Когда вы копируете ячейку (не содержащую раскрывающегося списка) поверх ячейки, содержащей раскрывающийся список, раскрывающийся список теряется.

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

Как выбрать все ячейки, в которых есть раскрывающийся список

Иногда бывает трудно понять, в каких ячейках находится раскрывающийся список.

Следовательно, имеет смысл пометить эти ячейки, придав им четкую границу или цвет фона.

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

• Перейдите на главную -> Найти и выбрать -> Перейти к специальному.
• В диалоговом окне «Перейти к специальному» выберите «Проверка данных».
  • У проверки данных есть два варианта: Все и Одинаково. All выберет все ячейки, к которым применено правило проверки данных. То же самое будет выбирать только те ячейки, которые имеют то же правило проверки данных, что и для активной ячейки.
• Щелкните ОК.

Это мгновенно выберет все ячейки, к которым применено правило проверки данных (включая раскрывающиеся списки).

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

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

Создание зависимого / условного раскрывающегося списка Excel

Вот видео о том, как создать зависимый раскрывающийся список в Excel.

Если вы предпочитаете читать, а не смотреть видео, продолжайте читать.

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

Они называются зависимыми или условными раскрывающимися списками.

Ниже приведен пример условного / зависимого раскрывающегося списка:

В приведенном выше примере, когда элементы, перечисленные в «Выпадающем списке 2», зависят от выбора, сделанного в «Выпадающем списке 1».

Теперь давайте посмотрим, как это создать.

Вот шаги для создания зависимого / условного раскрывающегося списка в Excel:

• Выберите ячейку, в которой вы хотите отобразить первый (основной) раскрывающийся список.
• Перейдите в Data -> Data Validation. Откроется диалоговое окно проверки данных.
• В диалоговом окне проверки данных на вкладке настроек выберите Список.
• В поле «Источник» укажите диапазон, содержащий элементы, которые должны отображаться в первом раскрывающемся списке.
• Щелкните ОК. Это создаст выпадающий список 1.
• Выберите весь набор данных (A1: B6 в этом примере).
• Перейдите в Формулы -> Определенные имена -> Создать из выделенного (или вы можете использовать сочетание клавиш Control + Shift + F3).
• В диалоговом окне «Создать имя из выделенного» установите флажок «Верхняя строка» и снимите все остальные флажки. В результате будут созданы 2 диапазона имен («Фрукты» и «Овощи»). Именованный диапазон фруктов относится ко всем фруктам в списке, а именованный диапазон овощей относится ко всем овощам в списке.
• Щелкните ОК.
• Выберите ячейку, в которой вы хотите создать раскрывающийся список «Зависимые / условные» (в данном примере - E3).
• Перейдите в Data -> Data Validation.
• Убедитесь, что в диалоговом окне «Проверка данных» на вкладке настроек установлен флажок «Список».
• В поле Источник введите формулу = КОСВЕННО (D3). Здесь D3 - это ячейка, содержащая главное раскрывающееся меню.
• Щелкните ОК.

Теперь, когда вы делаете выбор в раскрывающемся списке 1, параметры, перечисленные в раскрывающемся списке 2, будут автоматически обновляться.

Загрузите файл примера

Как это работает? - Условный раскрывающийся список (в ячейке E3) относится к = КОСВЕННО (D3). Это означает, что когда вы выбираете «Фрукты» в ячейке D3, раскрывающийся список в E3 ссылается на именованный диапазон «Фрукты» (с помощью функции КОСВЕННО) и, следовательно, перечисляет все элементы в этой категории.

Важное примечание при работе с условными раскрывающимися списками в Excel:

• Когда вы сделали выбор, а затем изменили родительский раскрывающийся список, зависимый раскрывающийся список не изменится и, следовательно, будет неправильной записью. Например, если вы выберете США в качестве страны, а затем выберите Флориду в качестве штата, а затем вернетесь и измените страну на Индию, штат останется как Флорида. Вот отличный учебник от Дебры по очистке зависимых (условных) раскрывающихся списков в Excel при изменении выбора.
• Если основная категория состоит из более чем одного слова (например, «Сезонные фрукты» вместо «Фрукты»), тогда вам необходимо использовать формулу = КОСВЕННО (ПОДСТАВИТЬ (D3, ”“, ”_”)) вместо простая функция КОСВЕННО, показанная выше. Причина этого в том, что Excel не допускает пробелов в именованных диапазонах. Поэтому, когда вы создаете именованный диапазон, используя более одного слова, Excel автоматически вставляет подчеркивание между словами. Таким образом, диапазон с названием «Сезонные фрукты» будет называться «Сезонные_фрукты». Использование функции ПОДСТАВИТЬ в функции ДВССЫЛ позволяет убедиться, что пробелы являются преобразованы в символы подчеркивания.