Een tijdje geleden zijn we begonnen met het verkennen van de mogelijkheden vanExcel-gegevensvalidatieen heb geleerd hoe het moetmaak een eenvoudige vervolgkeuzelijst in Excelop basis van een door komma's gescheiden lijst, een celbereik of een benoemd bereik.
Vandaag gaan we deze functie grondig onderzoeken en leren hoe we trapsgewijze vervolgkeuzelijsten kunnen maken die keuzes weergeven afhankelijk van de waarde die is geselecteerd in de eerste vervolgkeuzelijst. Anders gezegd, we zullen een Excel-gegevensvalidatielijst maken op basis van de waarde van een andere lijst.
Hoe maak je meerdere afhankelijke vervolgkeuzelijsten in Excel
Het maken van vervolgkeuzelijsten met meerdere niveaus in Excel is eenvoudig. Het enige dat u nodig hebt, zijn een paar benoemde bereiken en de INDIRECT-formule. Deze methode werkt met alle versies van Excel 365 - 2010 en eerder.
1. Typ de gegevens voor de vervolgkeuzelijsten
Typ eerst de vermeldingen die u in de vervolgkeuzelijsten wilt weergeven, elke lijst in een afzonderlijke kolom. Ik maak bijvoorbeeld een trapsgewijze vervolgkeuzelijst met fruitexporteurs en kolom A van mijn bronblad (Fruit) bevat de items van de eerste vervolgkeuzelijst en 3 andere kolommen vermelden de items voor de afhankelijke vervolgkeuzelijsten.
2. Maak benoemde bereiken
Nu moet u namen maken voor uw hoofdlijst en voor elk van de afhankelijke lijsten. U kunt dit doen door een nieuwe naam toe te voegen in hetNaam Beheerderraam (Formulestabblad >Naam Manager > Nieuw)of typ de naam rechtstreeks in hetNaam vak.
Opmerking.Houd er rekening mee dat als uw eerste rij een soort kolomkop is, zoals u ziet in de bovenstaande schermafbeelding, u deze niet in het benoemde bereik opneemt.
Zie voor gedetailleerde stapsgewijze instructiesHoe een naam in Excel te definiëren.
Dingen om te onthouden:
- De items die in de eerste vervolgkeuzelijst moeten verschijnen, moeten uit één woord bestaan, b.v.Abrikoos,Mango,Sinaasappelen. Als u items heeft die uit twee, drie of meer woorden bestaan, zie danEen trapsgewijze vervolgkeuzelijst maken met vermeldingen van meerdere woorden.
- De namen van de afhankelijke lijsten moeten precies hetzelfde zijn als het overeenkomende item in de hoofdlijst. De afhankelijke lijst die moet worden weergegeven wanneer "Mango" is geselecteerd uit de eerste vervolgkeuzelijst moet een naam krijgenMango.
Als u klaar bent, wilt u misschien op drukkenCtrl+F3om de te openenNaam Beheerdervenster en controleer of alle lijsten de juiste namen en referenties hebben.
3. Maak de eerste (hoofd) vervolgkeuzelijst
- Selecteer in dezelfde of een andere spreadsheet een cel of meerdere cellen waarin u uw primaire vervolgkeuzelijst wilt weergeven.
- Ga naar deGegevenstabblad, klikGegevensvalidatieen stel op de gebruikelijke manier een vervolgkeuzelijst in op basis van een benoemd bereik door te selecterenLijstonderToestaanen het invoeren van de bereiknaam in hetBrondoos.
Zie voor de gedetailleerde stappenEen vervolgkeuzelijst maken op basis van een benoemd bereik.
Het resultaat is dat u een vervolgkeuzemenu in uw werkblad hebt dat er ongeveer zo uitziet:
4. Maak de afhankelijke vervolgkeuzelijst
Selecteer een cel(len) voor uw afhankelijke vervolgkeuzelijst en pas Excel-gegevensvalidatie opnieuw toe zoals beschreven in de vorige stap. Maar deze keer voert u in plaats van de naam van het bereik de volgende formule in deBronveld:
=INDIRECT(A2)
Waarbij A2 de cel is met uw eerste (primaire) vervolgkeuzelijst.
Als cel A2 momenteel leeg is, krijgt u de foutmelding "De bron evalueert momenteel naar een fout. Wil je doorgaan?"
Veilig klikkenJa, en zodra u een item uit het eerste vervolgkeuzemenu selecteert, ziet u de vermeldingen die ermee overeenkomen in de tweede, afhankelijke vervolgkeuzelijst.
5. Voeg een derde afhankelijke vervolgkeuzelijst toe (optioneel)
Indien nodig kunt u een 3 toevoegene.dtrapsgewijze vervolgkeuzelijst die afhankelijk is van de selectie in de 2zdvervolgkeuzemenu of op de selecties in de eerste twee vervolgkeuzelijsten.
Stel een 3e vervolgkeuzelijst in die afhankelijk is van de 2e lijst
U kunt de vervolgkeuzelijst van dit type op dezelfde manier maken als we zojuist een tweede afhankelijke vervolgkeuzelijst hebben gemaakt. Onthoud gewoon de 2 belangrijke dingen die hierboven zijn besproken, die essentieel zijn voor het juiste werk van uw trapsgewijze vervolgkeuzelijsten.
Als u bijvoorbeeld een lijst met regio's in kolom C wilt weergeven, afhankelijk van welk land is geselecteerd in kolom B, maakt u een lijst met regio's voor elk land en noemt u deze naar de naam van het land, precies zoals het land wordt weergegeven in de tweede vervolgkeuzelijsten. Een lijst met Indiase regio's moet bijvoorbeeld "India" heten, een lijst met Chinese regio's - "China", enzovoort.
Daarna selecteert u een cel voor de 3e.dvervolgkeuzelijst (C2 in ons geval) en pas Excel-gegevensvalidatie toe met de volgende formule (B2 is de cel met het tweede vervolgkeuzemenu dat een lijst met landen bevat):
=INDIRECT(B2)
Nu, elke keer dat u selecteertIndiëonder de lijst met landen in kolom B heeft u de volgende keuzes in de derde vervolgkeuzelijst:
Opmerking.De weergegeven lijst met regio's is uniek voor elk land, maar is niet afhankelijk van de selectie in de eerste vervolgkeuzelijst.
Maak een derde vervolgkeuzelijst afhankelijk van de eerste twee lijsten
Als u een trapsgewijze vervolgkeuzelijst moet maken die afhangt van de selecties in zowel de eerste als de tweede vervolgkeuzelijst, gaat u als volgt te werk:
- Maak aanvullende sets van benoemde bereiken en geef ze een naam voor de woordcombinaties in uw eerste twee vervolgkeuzemenu's. Je hebt bijvoorbeeldMango, Sinaasappelen, enz.bij de 1stlijst enIndia, Brazilië, enz.bij de 2zd. Vervolgens maakt u benoemde bereikenMangoIndia,MangoBrazilië,SinaasappelsIndia,SinaasappelsBrazilië, enz. Deze namen mogen geen onderstrepingstekens of andere aanvullende tekens bevatten.
- Pas Excel-gegevensvalidatie toe met de formule INDIRECTE VERVANGING die de namen van de vermeldingen in de eerste twee kolommen samenvoegt en de spaties uit de namen verwijdert. In cel C2 zou de formule voor gegevensvalidatie bijvoorbeeld zijn:
=INDIRECT(VERVANGEN(A2&B2," ",""))Waar A2 en B2 respectievelijk de eerste en tweede vervolgkeuzelijsten bevatten.
Als resultaat, uw 3e.dvervolgkeuzelijst toont de regio's die overeenkomen met deFruitEnLandgeselecteerd in de eerste 2 vervolgkeuzelijsten.
Dit is de gemakkelijkste manier om trapsgewijze vervolgkeuzelijsten in Excel te maken. Deze methode heeft echter een aantal beperkingen.
Beperkingen van deze aanpak:
- De items in uw primaire vervolgkeuzelijst moeten uit één woord bestaan. Bekijk hoe u kunt makentrapsgewijze vervolgkeuzelijsten met vermeldingen van meerdere woorden.
- Deze methode werkt niet als de vermeldingen in uw vervolgkeuzelijst tekens bevatten die niet zijn toegestaan in bereiknamen, zoals het koppelteken (-), ampersand (&), enz. De oplossing is om eendynamische trapsgewijze vervolgkeuzelijstdie deze beperking niet heeft.
- Vervolgkeuzemenu's die op deze manier zijn gemaakt, worden niet automatisch bijgewerkt, d.w.z. u zult de referenties van de benoemde bereiken moeten wijzigen elke keer dat u items toevoegt aan of verwijdert uit de bronlijsten. Om deze beperking te omzeilen, kunt u proberen eendynamische trapsgewijze vervolgkeuzelijst.
Maak trapsgewijze vervolgkeuzelijsten met vermeldingen van meerdere woorden
De INDIRECTE formules die we in het bovenstaande voorbeeld hebben gebruikt, kunnen alleen items van één woord verwerken. De formule =INDIRECT(A2) verwijst bijvoorbeeld indirect naar cel A2 en geeft het benoemde bereik exact weer met dezelfde naam als in de cel waarnaar wordt verwezen. Spaties zijn echter niet toegestaan in Excel-namen, daarom werkt deze formule niet met namen die uit meerdere woorden bestaan.
De oplossing is om deINDIRECTE functiein combinatie metVERVANGINGzoals we deden bij het maken van een3e vervolgkeuzelijst.
Stel dat je hebtWatermeloentussen de producten. In dit geval noem je een lijst met watermeloenexporteurs met één woord zonder spaties -Watermeloen.
Pas vervolgens voor de tweede vervolgkeuzelijst Excel-gegevensvalidatie toe met de volgende formule die de spaties uit de naam in cel A2 verwijdert:
=INDIRECT(VERVANGEN(A2," ",""))
Wijzigingen in de primaire vervolgkeuzelijst voorkomen
Stel je het volgende scenario voor. Uw gebruiker heeft de selecties gemaakt in alle vervolgkeuzelijsten, is vervolgens van gedachten veranderd, is teruggegaan naar de eerste lijst en heeft een ander item gekozen. Met als resultaat de 1sten 2zdselecties komen niet overeen. Om dit te voorkomen, kunt u eventuele wijzigingen in de eerste vervolgkeuzelijst blokkeren zodra er een selectie is gemaakt in de tweede lijst.
Om dit te doen, gebruikt u bij het maken van de eerste vervolgkeuzelijst een speciale formule die controleert of er een item is geselecteerd in het tweede vervolgkeuzemenu:
=IF(B2="", Fruit, INDIRECT("FakeList"))
Waar B2 de tweede vervolgkeuzelijst bevat, "Fruit" is de naam van de lijst die verschijnt in het eerste vervolgkeuzemenu, en "NepLijst" is een valse naam die niet bestaat.
Als er nu een item is geselecteerd in de 2zdvervolgkeuzelijst, zijn er geen keuzes beschikbaar wanneer de gebruiker op de pijl naast de eerste lijst klikt.
Dynamische vervolgkeuzelijsten maken in Excel
Het belangrijkste voordeel van een dynamische Excel-afhankelijke vervolgkeuzelijst is dat u vrij bent om de bronlijsten te bewerken en dat uw vervolgkeuzelijsten direct worden bijgewerkt. Het maken van dynamische vervolgkeuzemenu's vereist natuurlijk wat meer tijd en complexere formules, maar ik denk dat dit een waardevolle investering is, want eenmaal ingesteld, zijn dergelijke vervolgkeuzemenu's echt een plezier om mee te werken.
Zoals met bijna alles in Excel, kunt u op verschillende manieren hetzelfde resultaat bereiken. U kunt met name een dynamische vervolgkeuzelijst maken met een combinatie van OFFSET-, INDIRECT- en COUNTA-functies of een veerkrachtigerINDEX MATCH formule. Dit laatste heeft mijn voorkeur omdat het tal van voordelen biedt, waarvan de meest essentiële zijn:
- U hoeft slechts 3 benoemde reeksen te maken, ongeacht het aantal items in de hoofd- en afhankelijke lijsten.
- Uw lijsten kunnen items met meerdere woorden en speciale tekens bevatten.
- Het aantal vermeldingen kan in elke kolom variëren.
- De sorteervolgorde van de items doet er niet toe.
- Ten slotte is het heel eenvoudig om de bronlijsten bij te houden en aan te passen.
Oké, genoeg theorie, laten we naar de praktijk gaan.
1. Organiseer uw brongegevens in een tabel
Zoals gewoonlijk is het eerste dat u moet doen, alle keuzes voor uw vervolgkeuzelijsten in een werkblad opschrijven. Deze keer moet u de brongegevens opslaan in eenExcel-tabel. Hiervoor selecteert u, nadat u de gegevens hebt ingevoerd, alle vermeldingen en drukt u opCtrl+Tof klikInvoegentabblad >Tafel. Typ vervolgens een naam van uw tabel in hetTafel naamdoos.
De handigste en meest visuele benadering is om de items voor de eerste vervolgkeuzelijst op te slaan als tabelkoppen en de items voor de afhankelijke vervolgkeuzelijst als tabelgegevens. De onderstaande schermafbeelding illustreert de structuur van mijn tabel, genaamdexporteurs_tbl- de fruitnamen zijn tabelkoppen en een lijst met exporterende landen is toegevoegd onder de corresponderende fruitnaam.
2. Maak Excel-namen
Nu uw brongegevens gereed zijn, is het tijd om benoemde verwijzingen in te stellen die dynamisch de juiste lijst uit uw tabel ophalen.
2.1. Een naam toevoegen voor de koprij van de tabel (hoofdvervolgkeuzelijst)
Als u een nieuwe naam wilt maken die verwijst naar de tabelkop, selecteert u deze en klikt u vervolgens opFormules>Naam Beheerder>Nieuwof druk opCTRL + F3.
Microsoft Excel gebruikt het ingebouwde tabelreferentiesysteem om de naam van hettabelnaam[#Headers]patroon.
Geef het een zinvolle en gemakkelijk te onthouden naam, b.v.fruit_lijsten klikOK.
2.2. Maak een naam voor de cel met de eerste vervolgkeuzelijst
Ik weet dat je nog geen vervolgkeuzelijst hebt :) Maar je moet de cel kiezen om je eerste vervolgkeuzelijst te hosten en nu een naam voor die cel maken, want je moet deze naam opnemen in de referentie van de derde naam.
Mijn eerste vervolgkeuzelijst bevindt zich bijvoorbeeld in cel B1 op blad 2, dus ik maak er een naam voor, iets eenvoudigs en vanzelfsprekends zoalsfruit:
Tip.Gebruik gepastcelverwijzingennaarvervolgkeuzelijsten kopiërenover het werkblad.
Lees de volgende paragrafen aandachtig door, want dit is een zeer nuttige tip die u niet wilt missen. Heel erg bedanktKarenvoor het plaatsen!
Als u van plan bent uw vervolgkeuzelijsten naar andere cellen te kopiëren, gebruik dan gemengde celverwijzingen bij het maken van de naam voor de cel(len) met uw eerste vervolgkeuzelijst.
Om ervoor te zorgen dat de vervolgkeuzelijsten correct worden gekopieerd naar anderekolommen(d.w.z. aan de rechterkant), gebruik relatieve kolom (zonder het $-teken) en absolute rij (met $) referenties zoals = Blad2!B$1.
Als resultaat verschijnt de afhankelijke vervolgkeuzelijst van B1 in cel B2; De afhankelijke vervolgkeuzelijst van C1 wordt weergegeven in C2, enzovoort.
En als u van plan bent de vervolgkeuzelijsten te kopiëren naar otherrijen(d.w.z. naar beneden in de kolom), gebruik dan absolute kolom (met $) en relatieve rij (zonder $) coördinaten zoals = Blad2!$B1.
Om een vervolgkeuzelijst in een willekeurige richting te kopiëren, gebruikt u een relatieve verwijzing (zonder het $-teken) zoals = Blad2!B1.
2.3. Maak een naam om de items van het afhankelijke menu op te halen
In plaats van unieke namen in te stellen voor elk van de afhankelijke lijsten zoals we deden in devorig voorbeeld, we gaan er een makenbenoemde formuleDatis niet toegewezen aan een bepaalde cel of een celbereik. Hetzal de juiste lijst met vermeldingen voor de tweede vervolgkeuzelijst ophalen, afhankelijk van welke selectie is gemaakt in de eerste vervolgkeuzelijst. Het belangrijkste voordeel van het gebruik van deze formule is dat u geen nieuwe namen hoeft te maken wanneer u nieuwe items toevoegt aan de eerste vervolgkeuzelijst - één benoemde formule dekt ze allemaal.
U maakt een nieuwe Excel-naam aan met deze formule:
=INDEX(exporteurs_tbl,,MATCH(fruit,fruit_lijst,0))
Waar:
exporteurs_tbl- de naam van de tabel (aangemaakt in stap 1);fruit- de naam van de cel met de eerste vervolgkeuzelijst (aangemaakt in stap 2.2);fruit_lijst- de naam die verwijst naar de koprij van de tabel (gemaakt in stap 2.1).
Ik heb het een naam gegevenexporteurs_lijst, zoals je ziet in de onderstaande schermafbeelding.
Nou, je hebt het grootste deel van het werk al gedaan! Voordat u naar de laatste stap gaat, is het misschien een goed idee om de Name Manager te openen (CTRL + F3) en verifieer de namen en referenties:
3. Excel-gegevensvalidatie instellen
Dit is eigenlijk het makkelijkste gedeelte. Met de twee benoemde formules op hun plaats, stelt u gegevensvalidatie in op de gebruikelijke manier (Gegevenstabblad >Gegevensvalidatie).
- Voer voor de eerste vervolgkeuzelijst in het vak Bron in=fruit_lijst(de naam gemaakt in stap 2.1).
- Voer in voor de afhankelijke vervolgkeuzelijst=exporteurslijst(de naam gemaakt in stap 2.3).
Klaar! Uw dynamische trapsgewijze vervolgkeuzelijst is voltooid en wordt automatisch bijgewerkt met de wijzigingen die u in de brontabel hebt aangebracht.
Deze dynamische vervolgkeuzelijst voor Excel, perfect in alle andere opzichten, heeft één tekortkoming: als de kolommen van uw brontabel een ander aantal items bevatten, verschijnen de lege rijen als volgt in uw menu:
Sluit lege rijen uit van de dynamische trapsgewijze vervolgkeuzelijst
Als u lege regels in uw vervolgkeuzelijsten wilt opschonen, moet u een stap verder gaan en de INDEX / MATCH-formule verbeteren die wordt gebruikt om de afhankelijke dynamische vervolgkeuzelijst te maken.
Het idee is om 2 INDEX-functies te gebruiken, waarbij de eerste de cel linksboven krijgt en de tweede de cel rechtsonder van het bereik, of deVERSCHILLENfunctie met genestINHOUDSOPGAVEEnAANTAL. De gedetailleerde stappen volgen hieronder:
1. Maak twee extra namen aan
Om de formule niet te omvangrijk te maken, maakt u eerst een paar hulpnamen met de volgende eenvoudige formules:
- Een naam genoemdcol_numom naar het geselecteerde kolomnummer te verwijzen:
=VERGELIJKEN(fruit,fruit_lijst,0) - Een naam genoemdvolledige_colom naar de geselecteerde kolom te verwijzen (niet het nummer van de kolom, maar de hele kolom):
=INDEX(exporteurs_tbl,,kolom_getal)
In de bovenstaande formules,exporteurs_tblis de naam van je brontabel,fruitis de naam van de cel die de eerste vervolgkeuzelijst bevat, enfruit_lijstis de naam die verwijst naar de koprij van de tabel.
2. Maak de benoemde verwijzing voor de afhankelijke vervolgkeuzelijst
Gebruik vervolgens een van de onderstaande formules om een nieuwe naam te maken (laten we hetexporteurs_lijst2) voor gebruik met de afhankelijke vervolgkeuzelijst:
=INDEX(exporteurs_tbl,1,col_num) : INDEX(exporteurs_tbl, COUNTA(hele_col), col_num)
=OFFSET(INDEX(exporteurs_tbl,1,kolom_getal),0,0,COUNTA(gehele_kolom))
3. Pas gegevensvalidatie toe
Selecteer ten slotte de cel met de afhankelijke vervolgkeuzelijst en pas gegevensvalidatie toe door = in te voerenexporteurs_lijst2(de naam die in de vorige stap is gemaakt) in deBrondoos.
De onderstaande schermafbeelding toont het resulterende dynamische vervolgkeuzemenu in Excel waar alle lege regels zijn verdwenen!
Opmerking.Bij het werken met dynamische trapsgewijze vervolgkeuzelijsten die zijn gemaakt met de bovenstaande formules, belet niets de gebruiker om de waarde in de eerste vervolgkeuzelijst te wijzigen nadat hij de selectie in het tweede menu heeft gemaakt, met als gevolg dat de keuzes in de primaire en secundaire vervolgkeuzelijsten mogelijk niet overeenkomen. U kunt wijzigingen in het eerste vak blokkeren nadat een selectie is gemaakt in het tweede vak door gebruik te maken van VBA of complexe formules die worden voorgesteld indeze zelfstudie.
Zo maakt u een Excel-gegevensvalidatielijst op basis van de waarden van een andere lijst. Aarzel niet om onze voorbeeldwerkmappen te downloaden om de trapsgewijze vervolgkeuzelijsten in actie te zien. Bedankt voor het lezen!
Oefenboek om te downloaden
Trapsgewijze vervolgkeuzelijst 1- gemakkelijke versie
Trapsgewijze vervolgkeuzelijst 2- geavanceerde versie zonder spaties
Mogelijk bent u ook geïnteresseerd in
- Aangepaste gegevensvalidatie in Excel
- Gemakkelijke manier om een dynamische afhankelijke vervolgkeuzelijst toe te voegen in Excel 365
- Maak een afhankelijke vervolgkeuzelijst voor meerdere rijen
- Hoe maak je een vervolgkeuzelijst met afbeeldingen in Excel
- Hoe een gegevensinvoerformulier in Excel te maken en te gebruiken
