Přejít k hlavnímu obsahu

Jak skrýt dříve použité položky v rozevíracím seznamu?

V aplikaci Excel můžete rychle vytvořit normální rozevírací seznam, ale zkusili jste někdy vytvořit rozevírací seznam, když vyberete jednu položku, ta dříve použitá bude ze seznamu odstraněna? Například pokud mám rozevírací seznam se 100 jmény, když vyberu jméno, chci odstranit toto jméno z rozevíracího seznamu a nyní rozevírací seznam obsahuje 99 jmen atd., Dokud není rozevírací seznam prázdný. Možná je to pro většinu z nás obtížné a tady můžu mluvit o tom, jak vytvořit takový rozevírací seznam v aplikaci Excel.

Skrýt dříve použité položky v rozevíracím seznamu pomocí pomocných sloupců


šipka modrá pravá bublina Skrýt dříve použité položky v rozevíracím seznamu pomocí pomocných sloupců

Předpokládejme, že máte ve Sloupci A seznam jmen, jak je zobrazeno na následujícím snímku obrazovky, a poté dokončete tento úkol podle následujících kroků.

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

1. Kromě seznamu jmen zadejte tento vzorec = KDYŽ (COUNTIF ($ F $ 1: $ F $ 11, A1)> = 1, "", ROW ()) do buňky B1, viz screenshot:

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

Poznámka: Ve výše uvedeném vzorci, F1: F11je rozsah buněk, do kterého chcete umístit rozevírací seznam, a A1 je vaše jmenná buňka.

2. Poté přetáhněte úchyt výplně do rozsahu, který obsahuje tento vzorec, a získáte následující výsledek:

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

3. A pokračujte v aplikaci vzorce ve sloupci C, zadejte tento vzorec: =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)))) do buňky C1, viz screenshot:

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

4. Poté vyplňte tento vzorec do požadovaného rozsahu, viz screenshot:

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

5. Nyní musíte pro tyto názvy definovat název rozsahu ve sloupci C, vybrat C1: C11 (rozsah, ve kterém použijete vzorec v kroku 4) a poté kliknout Vzorce > Definujte jméno, viz screenshot:

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

6. V Nové jméno V dialogovém okně zadejte název do textového pole Název a zadejte tento vzorec =OFFSET(Sheet2!$C$1,0,0,COUNTA(Sheet2!$C$1:$C$11)-COUNTBLANK(Sheet2!$C$1:$C$11),1) do Odkazuje na pole, viz screenshot:

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

Poznámka: Ve výše uvedeném vzorci je C1: C11 rozsah pomocných sloupců, který jste vytvořili v kroku 3, a list 2 je aktuální list, který používáte.

7. Po dokončení nastavení můžete vytvořit rozevírací seznam, vybrat buňku F1: F11, kam chcete rozevírací seznam umístit, a poté kliknout Data > Ověření dat > Ověření dat, viz screenshot:

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

8. V Ověření dat , klepněte na tlačítko OK Nastavení kartu a poté vyberte Seznam z povolit rozevírací seznam a poté pod Zdroj sekci, zadejte tento vzorec: = namecheck, (jmenná kontrola je název rozsahu, který jste vytvořili v kroku 6), viz screenshot:

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

9. A pak klikněte na tlačítko OK tlačítko pro zavření tohoto dialogu, nyní byl ve vybraném rozsahu vytvořen rozevírací seznam a po výběru jednoho jména z rozevíracího seznamu bude tento použitý název odstraněn ze seznamu a zobrazí pouze názvy, které nebyly použity , viz screenshot:

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

Tip: Nemůžete odstranit pomocné sloupce, které vytvoříte ve výše uvedených krocích, pokud je odeberete, rozevírací seznam bude neplatný.


Související články:

Jak vložit rozevírací seznam v aplikaci Excel?

Jak rychle vytvořit dynamický rozevírací seznam v aplikaci Excel?

Jak vytvořit rozevírací seznam s obrázky v aplikaci Excel?

Nejlepší nástroje pro produktivitu v kanceláři

Populární funkce: Najít, zvýraznit nebo identifikovat duplikáty   |  Odstranit prázdné řádky   |  Kombinujte sloupce nebo buňky bez ztráty dat   |   Kolo bez vzorce ...
Super vyhledávání: Více kritérií VLookup    VLookup s více hodnotami  |   VLookup na více listech   |   Fuzzy vyhledávání ....
Pokročilý rozevírací seznam: Rychle vytvořte rozevírací seznam   |  Závislý rozbalovací seznam   |  Vícenásobný výběr rozevíracího seznamu ....
Správce sloupců: Přidejte konkrétní počet sloupců  |  Přesunout sloupce  |  Přepnout stav viditelnosti skrytých sloupců  |  Porovnejte rozsahy a sloupce ...
Doporučené funkce: Zaměření mřížky   |  Návrhové zobrazení   |   Velký Formula Bar    Správce sešitů a listů   |  Knihovna zdrojů (Automatický text)   |  Výběr data   |  Zkombinujte pracovní listy   |  Šifrovat/dešifrovat buňky    Odesílat e-maily podle seznamu   |  Super filtr   |   Speciální filtr (filtr tučné/kurzíva/přeškrtnuté...) ...
Top 15 sad nástrojů12 Text Tools (doplnit text, Odebrat znaky, ...)   |   50+ Graf Typ nemovitosti (Ganttův diagram, ...)   |   40+ Praktické Vzorce (Vypočítejte věk na základě narozenin, ...)   |   19 Vložení Tools (Vložte QR kód, Vložit obrázek z cesty, ...)   |   12 Konverze Tools (Čísla na slova, Přepočet měny, ...)   |   7 Sloučit a rozdělit Tools (Pokročilé kombinování řádků, Rozdělit buňky, ...)   |   ... a více

Rozšiřte své dovednosti Excel pomocí Kutools pro Excel a zažijte efektivitu jako nikdy předtím. Kutools for Excel nabízí více než 300 pokročilých funkcí pro zvýšení produktivity a úsporu času.  Kliknutím sem získáte funkci, kterou nejvíce potřebujete...

karta kte 201905


Office Tab přináší do Office rozhraní s kartami a usnadňuje vám práci

  • Povolte úpravy a čtení na kartách ve Wordu, Excelu, PowerPointu, Publisher, Access, Visio a Project.
  • Otevřete a vytvořte více dokumentů na nových kartách ve stejném okně, nikoli v nových oknech.
  • Zvyšuje vaši produktivitu o 50%a snižuje stovky kliknutí myší každý den!
Comments (19)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Does any one know how to get the formula to reset itself once all the items on the list are selected?
e.g.
List 1, 2, 3, 4 - Then after selecting 1, List 2, 3, 4 - Then after Selecting 2, List 3, 4 - Then after selecting 3, List 4 and last, after selecting 4, list empty. How can this be reset so that after you select 4, all the items in the list reset to original list? So, after selecting 4, instead of List being empty, for list to go back to List 1, 2, 3, 4.

Thank you
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
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.
This comment was minimized by the moderator on the site
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!
This comment was minimized by the moderator on the site
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?
This comment was minimized by the moderator on the site
Is there a way to have only some of the options get removed when selected and others be permanent?
This comment was minimized by the moderator on the site
How do I get this activity to work if I transpose from Row to Column
This comment was minimized by the moderator on the site
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??
This comment was minimized by the moderator on the site
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.
This comment was minimized by the moderator on the site
How would you change this formula for use of data validation list across multiple rows instead of a single column. Is that possible? Thanks
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations