Создание нескольких раскрывающихся списков в Excel без повторения

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

Выпадающие списки Excel интуитивно понятны в использовании и чрезвычайно полезны при создании информационной панели Excel или формы ввода данных.

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

Создание нескольких раскрывающихся списков в Excel без повторения

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

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

Вот как вы можете создать эти внутренние данные:

1. Столбец B (Список участников) содержит список всех участников (или элементов), которые вы хотите отобразить в раскрывающемся списке.
2. Столбец C (вспомогательный столбец 1) использует комбинацию функций ЕСЛИ и СЧЁТЕСЛИ. Это дает имя, если имя еще не использовалось, иначе это пустое место.

= ЕСЛИ (СЧЁТЕСЛИ ('Выпадающее без повторения'! $ C $ 3: $ C $ 7, B3)> 0, "", B3)

3. Столбец D (вспомогательный столбец 2) использует комбинацию функций IF и ROWS. Это дает серийный номер, если имя не было повторено, иначе он дает пробел.

= ЕСЛИ (C3 ""; ROWS ($ C $ 3: C3), "")

4. Столбец E (вспомогательный столбец 3) использует комбинацию IFERROR, SMALL и ROWS. Это складывает вместе все доступные серийные номера.

= ЕСЛИОШИБКА (МАЛЕНЬКИЙ ($ D $ 3: $ D $ 9, СТРОКИ ($ D $ 3: D3)), "")

5. Столбец F (вспомогательный столбец 4) использует комбинацию функций ЕСЛИОШИБКА и ИНДЕКС. Это дает имя, соответствующее серийному номеру.

= ЕСЛИОШИБКА (ИНДЕКС ($ B $ 3: $ B $ 9; E3); "")

6. Используйте следующие шаги, чтобы создать динамический именованный диапазон
   • Перейдите в Формула -> Диспетчер имен
   • В диалоговом окне "Диспетчер имен" выберите "Создать".
   • В диалоговом окне «Новое имя» используйте следующие сведения.
     • Имя: DropDownList
     • Относится к: = List! $ F $ 3: INDEX (List! $ F $ 3: $ F $ 9, COUNTIF (List! $ F $ 3: $ F $ 9, ”? *”))
       Эта формула дает диапазон, в котором все имена указаны в столбце F. Он является динамическим и обновляется по мере изменения имен в столбце F.
7. Перейдите в раскрывающийся список вкладок Без повторения и создайте раскрывающийся список проверки данных в диапазоне ячеек C2: C6. Вот как это сделать:
   • Перейдите в Data -> Data Tools -> Data Validation.
   • В диалоговом окне Проверка данных используйте следующее:
     • Критерии проверки: список
     • Источник: = DropDownList
   • Нажмите ОК.

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

Попробуйте сами… Скачать файл

Другие полезные статьи в раскрывающихся списках в Excel:

• Как создать зависимый раскрывающийся список в Excel.
• Извлечь данные из выпадающего списка в Excel.
• Замаскируйте числа как текст в раскрывающемся списке.
• Создайте раскрывающийся список с предложениями поиска.
• Множественный выбор из раскрывающегося списка в одной ячейке.