Как скрыть ранее использованные элементы в выпадающем списке?

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

Скрытие ранее использованных элементов в выпадающем списке с помощью вспомогательных столбцов

Скрытие ранее использованных элементов в выпадающем списке с помощью вспомогательных столбцов

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

1. Рядом с вашим списком имен, пожалуйста, введите эту формулу =IF(COUNTIF($F$1:$F$11,A1)>=1,"",ROW()) в ячейку B1, см. снимок экрана:

Примечание: В приведенной выше формуле F1:F11 — диапазон ячеек, куда вы хотите поместить выпадающий список, а A1 — ячейка имени.

2. Затем перетащите маркер заполнения по диапазону, содержащему эту формулу, и вы получите следующий результат:

3. Продолжайте применять формулу в столбце C, введите эту формулу: =IF(ROW(A1)-ROW(A$1)+1>COUNT(B$1:B$11),"",INDEX(A:A,SMALL(B$1:B$11,1+ROW(A1)-ROW(A$1)))) в ячейку C1, см. снимок экрана:

4. Затем заполните эту формулу вниз по нужному диапазону, см. снимок экрана:

5. Теперь вам нужно определить имя диапазона для этих имен в столбце C, выберите C1:C11 (диапазон, к которому вы применили формулу на шаге 4), а затем нажмите Формулы > Определить имя, см. снимок экрана:

6. В диалоговом окне Новое имя введите имя в текстовое поле Имя, а затем введите эту формулу =OFFSET(Sheet2!$C$1,0,0,COUNTA(Sheet2!$C$1:$C$11)-COUNTBLANK(Sheet2!$C$1:$C$11),1) в поле Ссылается на, см. снимок экрана:

Примечание: В приведенной выше формуле C1:C11 — это диапазон вспомогательного столбца, который вы создали на шаге 3, а Лист2 — текущий лист, которым вы пользуетесь.

7. После завершения настроек вы можете создать выпадающий список, выберите ячейки F1:F11, куда вы хотите поместить выпадающий список, затем нажмите Данные > Проверка данных > Проверка данных, см. снимок экрана:

8. В диалоговом окне Проверка данных нажмите вкладку Параметры, затем выберите Список из раскрывающегося списка Разрешить, а затем в разделе Источник введите эту формулу: =namecheck, (namecheck — это имя диапазона, которое вы создали на шаге 6), см. снимок экрана:

9. Затем нажмите кнопку OK для закрытия этого диалога; теперь выпадающий список создан в выбранном диапазоне, и после выбора одного имени из выпадающего списка это использованное имя будет удалено из списка, и он покажет только те имена, которые еще не использовались, см. снимок экрана:

Совет: Вы не можете удалить вспомогательные столбцы, которые вы создали на предыдущих шагах, если вы их удалите, выпадающий список станет недействительным.

Связанные статьи:

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

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