Совет. Другие языки - Google-Translated. Вы можете посетить English версия этой ссылки.
Войти
x
or
x
x
Регистрация
x

or

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

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

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

Вкладка «Office» Включите редактирование и просмотр с вкладками в Office и упростите свою работу ...
Kutools для Excel - лучший инструмент для работы в офисе, который решит большинство ваших проблем с Excel
  • Повторно использовать что-либо: Добавьте наиболее часто используемые или сложные формулы, диаграммы и все остальное в избранное и быстро используйте их в будущем.
  • Больше, чем текстовые функции 20: Извлечь номер из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова ...
  • Инструменты слияния: Несколько рабочих книг и листов в одном; Объединить несколько ячеек / строк / столбцов без потери данных; Объединить повторяющиеся строки и суммы ...
  • Сплит Инструменты: Разбить данные на несколько листов на основе значения; Одна рабочая книга для нескольких файлов Excel, PDF или CSV; От одного столбца до нескольких столбцов ...
  • Вставить Пропуск Скрытые / отфильтрованные строки; Подсчет и сумма по цвету фона; Создать список рассылки и Отправка писем по значению ячейки...
  • Суперфильтр: Создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировать по неделям, дням, частоте и более; Фильтр жирным шрифтом, формулами, комментариями ...
  • Более чем мощные функции 300; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простота развертывания в компании; Полнофункциональная 60-дневная бесплатная пробная версия.

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


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

док-скрыть используемый-элементы, выпадающий-список-1

1, Помимо вашего имени, введите эту формулу = ЕСЛИ (СЧЁТЕСЛИ ($ F $ 1: $ F $ 11, A1)> = 1, "", ROW ()) в ячейку B1, см. снимок экрана:

док-скрыть используемый-элементы, выпадающий-список-1

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

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

док-скрыть используемый-элементы, выпадающий-список-1

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, см. снимок экрана:

док-скрыть используемый-элементы, выпадающий-список-1

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

док-скрыть используемый-элементы, выпадающий-список-1

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

док-скрыть используемый-элементы, выпадающий-список-1

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

док-скрыть используемый-элементы, выпадающий-список-1

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

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

док-скрыть используемый-элементы, выпадающий-список-1

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

док-скрыть используемый-элементы, выпадающий-список-1

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

док-скрыть используемый-элементы, выпадающий-список-1

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


Статьи по теме:

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

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

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


Kutools для Excel - лучший инструмент для повышения производительности в офисе Повысьте производительность на 80%

  • Снова использовать: Быстро вставить сложные формулы, графики и все, что вы использовали раньше; Шифрование ячеек с паролем; Создать список рассылки и отправлять электронные письма ...
  • Супер Формула Бар (легко редактировать несколько строк текста и формул); Макет чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон...
  • Объединить ячейки / строки / столбцы без потери данных; Содержание сплит-клеток; Объединить дублирующиеся строки / столбцы... предотвратить повторяющиеся клетки; Сравнить диапазоны...
  • Выберите Дубликат или Уникальный Ряды; Выберите пустые строки (все ячейки пусты); Супер найти и нечеткая находка во многих рабочих тетрадях; Случайный выбор ...
  • Точная копия Несколько ячеек без изменения формулы ссылки; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое ...
  • Извлечение текстаДобавить текст, Удалить по позиции, Удалить пространство; Создание и печать промежуточных итогов подкачки; Преобразование содержимого ячеек и комментариев...
  • Суперфильтр (сохранить и применить схемы фильтров к другим листам); Расширенный поиск по месяцам / неделям / дням, частоте и более; Специальный фильтр жирным шрифтом, курсивом ...
  • Объединить рабочие тетради и рабочие листы; Объединение таблиц на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF...
  • Более чем мощные функции 300, Поддерживает Office / Excel 2007-2019 и 365. Поддерживает все языки. Простота развертывания на вашем предприятии или в организации. Полнофункциональная 60-дневная бесплатная пробная версия.
вкладка kte 201905

Вкладка «Office» предоставляет интерфейс с вкладками для Office и упрощает работу

  • Включить редактирование и чтение с вкладками в Word, Excel, PowerPoint, Издатель, Доступ, Visio и Проект.
  • Открывайте и создавайте несколько документов в новых вкладках одного и того же окна, а не в новых окнах.
  • Увеличивает вашу производительность на 50% и уменьшает сотни щелчков мышью для вас каждый день!
нижняя часть офиса
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    John · 10 months ago
    Is there a way to have only some of the options get removed when selected and others be permanent?
  • To post as a guest, your comment is unpublished.
    Keith Race · 11 months ago
    How do I get this activity to work if I transpose from Row to Column
  • To post as a guest, your comment is unpublished.
    Sam · 1 years ago
    I’ve entered all the formulas correctly, but the only name showing up is the first one on the list. What am I doing wrong??
  • To post as a guest, your comment is unpublished.
    Stefan · 2 years ago
    Works great, however, if you have two people on the list with the same name e.g. John Smith it removes both incidents of 'John Smith' from the list when you select one of them.


    Is there a way to amend this so that you have have multiple versions of the one name without them all being removed?


    Thanks.
  • To post as a guest, your comment is unpublished.
    Derric · 3 years ago
    How would you change this formula for use of data validation list across multiple rows instead of a single column. Is that possible?

    Thanks
  • To post as a guest, your comment is unpublished.
    Mohanraj · 3 years ago
    Awesome, Working for me...
  • To post as a guest, your comment is unpublished.
    tim · 3 years ago
    I changed mine to accommodate my needs "=IF(COUNTIF(Statusboard!$C:$C,A1)>=1,"",ROW())" Make sure you change the $F$1:$F$11 from "=IF(COUNTIF($F$1:$F$11,A1)>=1,"",ROW())" to wherever your list is in my case it was Statusboard!$C:$C,A1.
  • To post as a guest, your comment is unpublished.
    Yolanda · 4 years ago
    I need to be able to create a list that has items that disappear but I want to be able to use it in multiple columns on the same sheet. Does anyone know how to do that? - Thanks!
  • To post as a guest, your comment is unpublished.
    Yolanda · 4 years ago
    I need to be able to use this list multiple times in multiple columns but as soon as an item disappears it's gone for good. Does anyone know how I can create this and be able to use it for multiple columns? Thanks!
  • To post as a guest, your comment is unpublished.
    NighT · 4 years ago
    Hey all,
    I got this to work, and it works like a charm!
    @Amanda, yes. I have my data on a different sheet as the dropdown menu. See the below written formulas.

    @Filip,
    Yes, you can use a formula to automatically select the unique values from a list. I used this to have a dynamic list.

    Mind you; I used google and a lot of different website to get to this formula, so it's not all my own work.
    First: to get the list of things to display:
    IF(INDEX(Sheet1!$A$2:$A$100;MATCH(0;COUNTIF($AA$14:AA14;Sheet1!$A$2:$A$100);0))=0;"";INDEX(Sheet1!$A$2:$A$100;MATCH(0;COUNTIF($AA$14:AA14;Sheet1!$A$2:$A$100);0)))

    === Basically this is the same formula twice. Which will give an empty ("") value if no further unique values are found. Anyway, the formula returns an unique list of values from my 'Sheet1!'. (lets say for easy reference I have this formula on Sheet 2, column A)

    Then I just start using the same formula as above (my sheet 2 column B):
    IF(COUNTIF(Sheet3!$S$2:$U$4;A1)>=1;"";ROW())

    === Sheet 3 is where I have my dropdowns. This is probably what you're looking for Amanda.

    Then the last bit of the formula:
    IF(ROW(A1)-ROW(A$1)+1>COUNT(B$1:B$24);"";INDEX(A:A;SMALL(B$1:B$24;1+ROW(A1)-ROW(A$1))))

    === This formula is in my Sheet 2 column C.
    This *should* work.

    Good luck to you all! And again, a great thank you to the author!
    Regards,

    NighT
  • To post as a guest, your comment is unpublished.
    Filip · 4 years ago
    Thanks for this guide. I'm just asking if it's possible when I update a value in formula, this value will be updated automatically in list of items?

    Example:
    I select value "James" from list in cell F
    Now, I want to change value from "James" to "Thomas". I rewrite value "James" to "Thomas" in cell A, formula automatically change value in cell C. It's OK, but I need this changed value is automatically updated in cell F as well.

    How can I reach that? Any ideas?
  • To post as a guest, your comment is unpublished.
    Amanda · 4 years ago
    Does anyone know if it is possible to use this between sheets? For instance if the original info (the column A portion) is on one sheet, but the dropdown (the column F portion) is on another? How would that change the formula?
  • To post as a guest, your comment is unpublished.
    Kent · 4 years ago
    Hi, I can't get it to work properly.
    When trying to complete step 8 I get a message about 'The source returns an error at evaluation. Do you wish to continue?'

    I am using Excel 2010, any idea?
    • To post as a guest, your comment is unpublished.
      Amanda · 4 years ago
      I had that happen at first, too. I had not changed the "sheet2" portion to the proper sheet name for what I was using.