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

Как администратор листа Excel, вы не хотите, чтобы кто-либо редактировал лист где угодно. Поэтому мы ограничиваем других пользователей, чтобы разрешить только из упомянутых значений в списке. Это инструмент проверки данных, который позволяет ограничивать пользователей. Множественный выпадающий список означает, что один список связан с другим списком. Например, если мы выберем неделю из первого списка, теперь другие варианты останутся такими: Понедельник, Вторник…, Воскресенье. Если мы выберем фрукты из первого списка, то в предыдущем списке будут показаны названия фруктов, а не названия дней недели.

Все это может сбивать с толку. Давайте разберемся, как пользоваться функцией, на примере. Здесь у нас есть несколько списков, как показано ниже.

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

Выберите «Формула»> «Диспетчер имен» в «Определенные имена» ИЛИ используйте сочетание клавиш Ctrl F3, чтобы открыть диспетчер имен, где мы будем хранить списки массива с их именами, чтобы мы могли вызывать их по имени, когда это необходимо.

Нажмите New, чтобы создать. Здесь Имя будет Месяц, а в поле Относится к опции введите список под Месяцем, как показано ниже.

То же самое мы сделаем для Week_Days, и он будет выглядеть как

Нажмите Close и теперь выберите ячейку, в которую нам нужно добавить раскрывающийся список.

Затем щелкните Проверка данных под панелью данных. Выберите вариант списка «Разрешить» и выберите ячейки для названий основных категорий, которые в данном случае находятся в ячейках B2 и C2 «Месяц» и «Неделя_Дни»

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

Теперь выберите ячейку в разделе Sub_Category и просто напишите формулу в поле «Проверка данных» и нажмите «ОК».

Результат отображается так

Если я не хочу «Месяц» и «Неделя_Дни». Вместо этого я хочу Fruits_Name и Veget_Name. Нам просто нужно отредактировать наш список диспетчера имен.

Нажмите Ctrl + F3, чтобы открыть Диспетчер имен, удалить уже вставленный список и добавить новые списки, то есть Fruits_Name и Vegetables_Name.

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

Вместо ячеек Month и Week_Days мы будем использовать Fruits_Name и Vegetables_Name в Data Validation и нажать OK

Как видите, сюда добавлен новый список.

Таким образом мы можем редактировать раскрывающийся список и изменять выбор списка.

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

В мастер-листе вы хотите поместить ВПР, чтобы проверить, присутствовал ли этот студент в этом месяце или отсутствовал. Было бы просто, если бы ваши данные были на одном листе, но это не так. Он находится на разных листах. Но это не значит, что мы не можем извлекать данные из другого листа в Excel. Мы можем и будем.

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

В этом примере моя посещаемость в таблицах «Янв», «Фев» и «Март» находится в том же диапазоне A2: C11.

Теперь я подготовил мастер-лист.

В ячейку C4 поместите эту формулу и перетащите ее вниз.

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

Мы использовали функцию Excel Indirect, чтобы получить значение с другого листа.

INDIRECT заменяет текст на ссылку. Мы использовали INDIRECT для ссылки на другие листы в Excel.

Например, если вы напишете КОСВЕННЫЙ («лист2: А2») в a1 на листе 1. Он будет извлекать значение из листа2! A2 в лист1: A1. Если вы напишете = VLOOKUP («abc», INDIRECT («sheet2! A2: B100»), 2,0) на любом листе, функция VLOOKUP будет искать «abc» в диапазоне A2: B100 на листе 2. КОСВЕННО («» & $ C $ 1 & «! B2: C11»): Здесь мы хотим, чтобы имя листа изменилось, поэтому мы написали это так. Если ячейка C1 содержит «Jan», она будет преобразована в INDIRECT («Jan! B2: C11»), которая затем будет преобразована в Jan! B2: C11 для массива таблицы VLOOKUP. Если C1 имеет фев, он будет преобразован в НЕПРЯМОЙ («Фев! B2: C11») * и так далее.

После этого VLOOKUP сделал свое дело.

VLOOKUP (B4, INDIRECT («» & $ C $ 1 & «! B2: C11»), 2,0): теперь, поскольку Indirect предоставил массив таблицы, VLOOKUP просто легко извлекает данные из этого диапазона.

Надеюсь, эта статья о том, как создать несколько раскрывающихся списков без повторения с использованием именованных диапазонов в Excel, является пояснительной. Дополнительные статьи о проверке значений и связанных формул Excel можно найти здесь. Если вам понравились наши блоги, поделитесь ими с друзьями на Facebook. А также вы можете подписаться на нас в Twitter и Facebook. Мы будем рады услышать от вас, дайте нам знать, как мы можем улучшить, дополнить или усовершенствовать нашу работу и сделать ее лучше для вас. Напишите нам на [email protected].

link: / excel-edit-validating-text-entries-in-microsoft-excel [Проверка данных в Excel]: Проверка данных — это инструмент, используемый для ограничения пользователей на ввод значений вручную в ячейку или лист в Excel. У него есть список вариантов на выбор.

link: / tips-vlookup-function-in-data-validation [Способ использования функции Vlookup при проверке данных]: Ограничьте пользователей, чтобы разрешить значения из таблицы поиска с помощью поля формулы проверки данных в Excel.

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

link: / tips-restrict-date-using-data-validation [Ограничить даты с помощью проверки данных]: Ограничить пользователя, чтобы разрешить даты из заданного диапазона в ячейке, которая находится в формате даты Excel в Excel.

link: / tips-how-to-give-the-error-messages-in-data-validation [Как выдавать сообщения об ошибках при проверке данных]: ограничить пользователей настраивать вводимую информацию на листе и направлять вводимую информацию через сообщения об ошибках при проверке данных в Excel.

link: / tips-how-to-create-drop-down-lists-in-excel-sheet [Создать раскрывающиеся списки в Excel с помощью проверки данных]: Ограничить пользователей, чтобы разрешить значения из раскрывающегося списка с помощью списка проверки данных вариант в Excel. Поле со списком в проверке данных позволяет вам выбрать тип необходимого ограничения.

link: / tips-if-condition-in-excel [Как использовать функцию IF в Excel]: оператор IF в Excel проверяет условие и возвращает конкретное значение, если условие истинно, или другое конкретное значение, если оно ложно .

link: / formulas-and-functions-Introduction-of-vlookup-function [Как использовать функцию ВПР в Excel]: Это одна из наиболее часто используемых и популярных функций Excel, которая используется для поиска значений из разных диапазонов и листы.

link: / excel-formula-and-function-excel-sumif-function [Как использовать функцию СУММЕСЛИ в Excel]: Это еще одна важная функция информационной панели. Это поможет вам суммировать значения для конкретных условий.

link: / tips-countif-in-microsoft-excel [Как использовать функцию СЧЁТЕСЛИ в Excel]: Подсчет значений с условиями с помощью этой удивительной функции. Вам не нужно фильтровать данные для подсчета определенных значений. Функция Countif важна для подготовки вашей приборной панели.