Note: The other languages of the website are Google-translated. Back to English
Увійти  \/ 
x
or
x
Реєстрація  \/ 
x

or

Як приховати раніше використані елементи у випадаючому списку?

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

Сховати раніше використані елементи у випадаючому списку за допомогою допоміжних стовпців

Вкладка Office Увімкніть редагування та перегляд вкладок в Office та значно спростіть свою роботу ...
Kutools для Excel вирішує більшість ваших проблем і збільшує продуктивність на 80%
  • Повторне використання будь-чого: Додайте найбільш уживані або складні формули, діаграми та будь-що інше до вибраного та швидко використовуйте їх у майбутньому.
  • Більше 20 функцій тексту: Витяг номера з текстового рядка; Витяг або видалення частини текстів; Перетворення чисел і валют в англійські слова.
  • Злиття інструментів: Кілька робочих книжок та аркушів в одне ціле; Об’єднати кілька клітинок / рядків / стовпців без втрати даних; Об’єднати повторювані рядки та суму.
  • Розділити інструменти: Розділити дані на кілька аркушів на основі значення; Одна робоча книга до декількох файлів Excel, PDF або CSV; Один стовпець на кілька стовпців.
  • Пропустити вставку Приховані / відфільтровані рядки; Граф і сума за кольором тла; Надіслати персоналізовані електронні листи кільком одержувачам масово.
  • Суперфільтр: Створюйте розширені схеми фільтрів і застосовуйте їх до будь-яких аркушів; сортувати за тижнями, днями, періодичністю тощо; фільтр жирним шрифтом, формули, коментар ...
  • Понад 300 потужних функцій; Працює з Office 2007-2019 та 365; Підтримує всі мови; Простота розгортання на вашому підприємстві чи в організації.

стрілка синя права міхур Сховати раніше використані елементи у випадаючому списку за допомогою допоміжних стовпців


Припустимо, у вас є список імен у стовпці A, як показано на наведеному нижче знімку екрана, а потім виконайте наведені нижче кроки по одному, щоб закінчити це завдання.

doc-hide-used-items-dropdown-list-1

1. Окрім списку імен, введіть цю формулу = IF (COUNTIF ($ F $ 1: $ F $ 11, A1)> = 1, "", ROW ()) у клітинку B1, див. знімок екрана:

doc-hide-used-items-dropdown-list-1

примітки: У наведеній вище формулі, F1: F11- діапазон комірок, до якого потрібно додати випадаючий список, і A1 - це ваша клітина з іменем.

2. Потім перетягніть маркер заповнення до діапазону, що містить цю формулу, і ви отримаєте такий результат:

doc-hide-used-items-dropdown-list-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, див. знімок екрана:

doc-hide-used-items-dropdown-list-1

4. Потім заповніть цю формулу до потрібного діапазону, див. Знімок екрана:

doc-hide-used-items-dropdown-list-1

5. Тепер вам потрібно визначити ім'я діапазону для цих імен у стовпці C, вибрати C1: C11 (діапазон, який ви застосовуєте формула на кроці 4), а потім натисніть Формули > Визначте ім’я, див. скріншот:

doc-hide-used-items-dropdown-list-1

6, в Нове ім'я у діалоговому вікні введіть ім’я у текстовому полі Ім'я та введіть цю формулу =OFFSET(Sheet2!$C$1,0,0,COUNTA(Sheet2!$C$1:$C$11)-COUNTBLANK(Sheet2!$C$1:$C$11),1) в Відноситься до поле, див. скріншот:

doc-hide-used-items-dropdown-list-1

примітки: У наведеній вище формулі C1: C11 - це допоміжний діапазон стовпців, який ви створили на кроці 3, а Аркуш 2 - це поточний аркуш, який ви використовуєте.

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

doc-hide-used-items-dropdown-list-1

8, в Перевірка достовірності даних діалогове вікно натисніть кнопку настройки вкладку, а потім виберіть список від дозволяти випадаючий список, а потім під джерело розділу, введіть цю формулу: = перевірка імен, (перевірка імен це назва діапазону, яку ви створили на кроці 6), див. знімок екрана:

doc-hide-used-items-dropdown-list-1

9. А потім клацніть OK , щоб закрити це діалогове вікно, тепер у вибраному діапазоні створено випадаючий список, і після вибору одного імені зі спадного списку це використане ім'я буде видалено зі списку, і в ньому відображатимуться лише ті імена, які не використовувались. , див. скріншот:

doc-hide-used-items-dropdown-list-1

Чайові: Ви не можете видалити допоміжні стовпці, які ви робите в кроках вище, якщо їх видалити, випадаючий список буде недійсним.


Статті по темі:

Як вставити випадаючий список в Excel?

Як швидко створити динамічний випадаючий список в Excel?

Як створити випадаючий список із зображеннями в Excel?


Найкращі інструменти для підвищення продуктивності офісу

Kutools для Excel вирішує більшість ваших проблем і збільшує продуктивність на 80%

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

Вкладка Office забезпечує інтерфейс з вкладками для Office і значно спрощує вашу роботу

  • Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
  • Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
  • Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!
дно офісної таблиці
Say something here...
symbols left.
You are guest
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.
    KL · 1 years ago
    This worked like a charm ... expect I need to have this "repeat" in three places on the same spreadsheet. I can't make it function properly, the second time. The items from the first drop down list are eliminated but when an entry is selected in the second location, it isn't removed from the list. Any idea on how to may this function properly?

    KL

  • To post as a guest, your comment is unpublished.
    Kelly · 1 years ago
    Can this formula be used successfully across multiple columns on one spreadsheet? I'm trying to have three columns where someone can select up to three items from a drop down list, i.e. item 1 in column 3, items2 in column 4, and item 3 in column 5. I can get the formula to work at the first occurrence, however, the second time I try to copy the formula, the selection(s) don't disappear from the drop down list, as they do in the column of the first occurrence.
  • To post as a guest, your comment is unpublished.
    Pearson · 1 years ago
    Bravo! Fantastic solution! I have my workbook set up such that the items in my list are in a separate worksheet that I'm index-matching to through my number and helper columns leaving only them two on my calculation page. Again, very clean solution, Programmer!
  • To post as a guest, your comment is unpublished.
    Stacey · 1 years ago
    Step 6 isn't working for me. I keep getting an error message saying the syntax of this name isn't correct... Can anyone help?
  • To post as a guest, your comment is unpublished.
    John · 3 years 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 · 3 years 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.
    SBell · 3 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 · 4 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 · 5 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 · 5 years ago
    Awesome, Working for me...
  • To post as a guest, your comment is unpublished.
    tim · 5 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 · 6 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 · 6 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 · 6 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 · 6 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 · 6 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 · 6 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 · 6 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.