Note: The other languages of the website are Google-translated. Back to English

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

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

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

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

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

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

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

1. Помимо списка имен, введите эту формулу = ЕСЛИ (СЧЁТЕСЛИ ($ F $ 1: $ F $ 11; A1)> = 1, "", СТРОКА ()) в ячейку 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, а лист 2 - это текущий лист, который вы используете.

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

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

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

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

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

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

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


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

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

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

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


Лучшие инструменты для работы в офисе

Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%

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

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

  • Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
  • Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
  • Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
офисный дно
Сортировать комментарии по
Комментарии (18)
Оценок пока нет. Оцените первым!
Этот комментарий был сведен к минимуму модератором на сайте
Привет, я не могу заставить его работать должным образом. При попытке выполнить шаг 8 я получаю сообщение о том, что «Источник возвращает ошибку при оценке». Вы хотите продолжить?' Я использую Excel 2010, есть идеи?
Этот комментарий был сведен к минимуму модератором на сайте
У меня тоже так было поначалу. Я не изменил часть «лист2» ​​на правильное имя листа для того, что я использовал.
Этот комментарий был сведен к минимуму модератором на сайте
Кто-нибудь знает, можно ли использовать это между листами? Например, если исходная информация (часть столбца A) находится на одном листе, а раскрывающийся список (часть столбца F) — на другом? Как это изменит формулу?
Этот комментарий был сведен к минимуму модератором на сайте
Спасибо за это руководство. Я просто спрашиваю, возможно ли, когда я обновляю значение в формуле, это значение будет автоматически обновляться в списке элементов? Пример: я выбираю значение «Джеймс» из списка в ячейке F. Теперь я хочу изменить значение с «Джеймс» на «Томас». Я переписываю значение «Джеймс» на «Томас» в ячейке A, формула автоматически меняет значение в ячейке C. Это нормально, но мне нужно, чтобы это измененное значение автоматически обновлялось и в ячейке F. Как я могу достичь этого? Любые идеи?
Этот комментарий был сведен к минимуму модератором на сайте
Привет всем, я заставил это работать, и это работает как шарм! @ Аманда, да. У меня есть данные на другом листе в виде выпадающего меню. См. приведенные ниже письменные формулы. @Filip, да, вы можете использовать формулу для автоматического выбора уникальных значений из списка. Я использовал это, чтобы иметь динамический список. Имейте в виду; Я использовал Google и множество других веб-сайтов, чтобы получить эту формулу, так что это не только моя собственная работа. Во-первых: получить список вещей для отображения: ЕСЛИ(ИНДЕКС(Лист1!$A$2:$A$100;MATCH(0;СЧЁТЕСЛИ($AA$14:AA14;Лист1!$A$2:$A$100);0) )=0;"";ИНДЕКС(Лист1!$A$2:$A$100;СООТВЕТСТВИЕ(0;СЧЁТЕСЛИ($AA$14:AA14;Лист1!$A$2:$A$100);0))) === В основном это одна и та же формула дважды. Что даст пустое ("") значение, если другие уникальные значения не будут найдены. В любом случае, формула возвращает уникальный список значений из моего «Листа1!». (скажем, для удобства у меня есть эта формула на листе 2, столбец A). Затем я просто начинаю использовать ту же формулу, что и выше (мой столбец листа 2 B): ЕСЛИ (СЧЁТЕСЛИ (Лист3! $ S $ 2: $ U $ 4; A1 )>=1;"";СТРОКА()) === Лист 3 - это место, где у меня есть раскрывающиеся списки. Вероятно, это то, что вы ищете для Аманды. Затем последний бит формулы: ЕСЛИ(СТРОКА(A1)-СТРОКА(A$1)+1>СЧЕТ(B$1:B$24);"";ИНДЕКС(A:A;МАЛЕНЬКИЙ(B$1:B$24;1) +СТРОКА(A1)-СТРОКА(A$1)))) === Эта формула находится в моей колонке C Листа 2. Это *должно* работать. Желаю вам всем удачи! И еще раз большое спасибо автору! С уважением, Найт
Этот комментарий был сведен к минимуму модератором на сайте
Мне нужно иметь возможность использовать этот список несколько раз в нескольких столбцах, но как только элемент исчезает, он исчезает навсегда. Кто-нибудь знает, как я могу создать это и использовать его для нескольких столбцов? Спасибо!
Этот комментарий был сведен к минимуму модератором на сайте
Мне нужно иметь возможность создать список с исчезающими элементами, но я хочу использовать его в нескольких столбцах на одном листе. Кто-нибудь знает как это сделать? - Спасибо!
Этот комментарий был сведен к минимуму модератором на сайте
Я изменил свой, чтобы приспособиться к своим потребностям: "=ЕСЛИ(СЧЁТЕСЛИ(Статусборд!$C:$C,A1)>=1,"",СТРОКА())" Убедитесь, что вы изменили $F$1:$F$11 с "= IF(COUNTIF($F$1:$F$11,A1)>=1,"",ROW())" туда, где находится ваш список, в моем случае это был Statusboard!$C:$C,A1.
Этот комментарий был сведен к минимуму модератором на сайте
Круто, работает на меня...
Этот комментарий был сведен к минимуму модератором на сайте
Как бы вы изменили эту формулу для использования списка проверки данных в нескольких строках вместо одного столбца. Это возможно? Спасибо
Этот комментарий был сведен к минимуму модератором на сайте
Однако прекрасно работает, если у вас есть два человека в списке с одинаковым именем, например, Джон Смит, он удаляет оба инцидента «Джон Смит» из списка, когда вы выбираете одного из них.


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


Благодарю.
Этот комментарий был сведен к минимуму модератором на сайте
Я ввел все формулы правильно, но появляется только первое имя в списке. Что я делаю не так??
Этот комментарий был сведен к минимуму модератором на сайте
Как мне заставить это действие работать, если я переношу из строки в столбец
Этот комментарий был сведен к минимуму модератором на сайте
Есть ли способ, чтобы при выборе удалялись только некоторые параметры, а другие оставались постоянными?
Этот комментарий был сведен к минимуму модератором на сайте
Шаг 6 не работает для меня. Я продолжаю получать сообщение об ошибке, в котором говорится, что синтаксис этого имени неверен... Кто-нибудь может помочь?
Этот комментарий был сведен к минимуму модератором на сайте
Браво! Фантастическое решение! Моя рабочая книга настроена таким образом, что элементы в моем списке находятся на отдельном рабочем листе, с которым я сопоставляю индекс через свои числовые и вспомогательные столбцы, оставляя только их два на моей странице расчета. Опять же, очень чистое решение, Программист!
Этот комментарий был сведен к минимуму модератором на сайте
Можно ли успешно использовать эту формулу в нескольких столбцах одной электронной таблицы? Я пытаюсь создать три столбца, в которых можно выбрать до трех элементов из раскрывающегося списка, т. е. элемент 1 в столбце 3, элемент 2 в столбце 4 и элемент 3 в столбце 5. Я могу заставить формулу работать в первое вхождение, однако во второй раз, когда я пытаюсь скопировать формулу, выбор (ы) не исчезает из раскрывающегося списка, как в столбце первого вхождения.
Этот комментарий был сведен к минимуму модератором на сайте
Это сработало как шарм ... ожидайте, что мне нужно будет «повторить» в трех местах в одной и той же электронной таблице. Я не могу заставить его работать должным образом, второй раз. Элементы из первого раскрывающегося списка удаляются, но при выборе записи во втором месте она не удаляется из списка. Любая идея о том, как это может работать правильно?
KL
Здесь еще нет комментариев
Оставляйте свои комментарии
Публикация как гость
×
Оценить этот пост:
0   Характеристики
Предлагаемые места

Подписывайтесь на Нас

Copyright © 2009 - www.extendoffice.ком. | Все права защищены. Питаться от ExtendOffice, | Карта сайта
Microsoft и логотип Office являются товарными знаками или зарегистрированными товарными знаками Microsoft Corporation в США и / или других странах.
Защищено Sectigo SSL