Tot nu toe hebben we in deze reeks gegevensvalidatie geleerd om een normale vervolgkeuzelijst en een dynamische vervolgkeuzelijst te maken met behulp van verschillende technieken met gegevensvalidatie in Excel.
En vandaag laten we u in dit hoofdstuk zien hoe u een afhankelijke vervolgkeuzelijst kunt maken in Microsoft Excel, met behulp van verschillende methoden.
Afhankelijke vervolgkeuzelijst staat ook bekend als trapsgewijze gegevensvalidatie en beperkt de keuzes in een vervolgkeuzelijst, afhankelijk van de waarde die is geselecteerd in de andere cel die gegevensvalidatie bevat. Met andere woorden, het hangt af van de waarde die is geselecteerd in de eerste vervolgkeuzelijst die de waarden bepaalt die moeten worden weergegeven in de tweede vervolgkeuzelijst.
Dit is een veel voorkomend scenario van werken met grote gegevens of sommige dynamische rapporten, waar u 2 . wilt hebbennd cel geeft een lijst weer die afhankelijk is van het lijstitem dat is gekozen in de eerste vervolgkeuzelijst.
Zoals we weten, zijn er in Excel tal van manieren om een bepaalde taak uit te voeren en op dezelfde manier zijn er veel manieren om afhankelijke gegevensvalidatie in Excel te maken. En vandaag zullen we 5 verschillende technieken demonstreren om een afhankelijke gegevensvalidatielijst te maken.
De onbewerkte gegevens kunnen in elke volgorde of indeling zijn en elke keer kunt u de gegevens of het formaat niet wijzigen om te krijgen wat u zoekt.
We hebben dus één dataset genomen, maar in 3 verschillende formaten om de afhankelijke vervolgkeuzelijst te krijgen. En zoals u kunt zien, bevinden onze gegevens zich aan de linkerkant, van kolom A tot kolom E, en we hebben onze verwachte uitvoer aan de rechterkant, dat is in kolom J & K. Kolom J krijgt de primaire validatie lijst, terwijl de kolom K afhankelijk zal zijn en de waarden zal weergeven afhankelijk van de waarde die is geselecteerd in kolom J.
1NS Voorbeeld:-
2nd Voorbeeld:-
3rd Voorbeeld:-
1NS Voorbeeld:-
We hebben een productlijst voor elke productcode van kolom A8 tot E13. En we willen de productcode in J10 selecteren en vervolgens, afhankelijk van de geselecteerde productcode, een productnaam in cel K10.
Eerste methode:-
De eerste methode is heel eenvoudig en kort en er zijn slechts 3 stappen nodig om de afhankelijke vervolgkeuzelijst te krijgen. Het werkt echter alleen succesvol totdat u geen wijzigingen aanbrengt in uw bereik. Zodra u uw gegevens wijzigt, moet u eerst het genoemde bereik wijzigen om de bijgewerkte trapsgewijze gegevensvalidatie te krijgen.
Volg de onderstaande stappen:-
- Selecteer de hele tabel van A8 tot E13
- Ga vervolgens naar het tabblad "Formules" en klik vervolgens onder de categorie "Gedefinieerde namen" op "Maken vanuit selectie"
- U kunt ook de sneltoets CTRL + SHIFT + F3 gebruiken
- Het dialoogvenster Namen maken uit selecties verschijnt
- Het vraagt om te bevestigen welke rijen en kolommen moeten worden gebruikt om de namen voor andere rijen en kolommen te maken. We bevestigen dat we de "bovenste rij" gebruiken om de namen te maken en de 2 . uitschakelennd optie en dan klikken we op OK
Opmerking: - Spaties en andere speciale tekens behalve underscore en punt zijn niet toegestaan als namen. Standaard wordt deze omgezet in onderstrepingsteken. Gebruik dus een onderstrepingsteken en een punt om woorden te scheiden. Ook kan de eerste letter geen cijfer zijn; het moet een letter, een onderstrepingsteken of een backslash zijn.
- Om te bevestigen dat elk bereik een naam heeft, gaan we naar de "Name Manager" (druk op CTRL + F3)
- Daar kunnen we alle 5 genoemde reeksen zien die beschikbaar zijn
- En we kunnen ook zien dat elke bereiknaam een onderstrepingsteken heeft in plaats van een spatie in het midden van de tekenreeks
Nu zullen we een vervolgkeuzelijst maken:-
- Selecteer cel J10 en druk op ALT++D+L om het dialoogvenster Gegevensvalidatie te openen
- Selecteer Lijst > voer vervolgens het bereik A8:E8 in op het tabblad Bron
- Klik op OK
- Nu gaan we een afhankelijke lijst maken in cel K10
- Open het dialoogvenster Gegevensvalidatie door op de toets ALT+D+L . te drukken
- Selecteer Lijst, voer in de bron deze functie in:- =INDIRECT(SUBSTITUTE($J$10," ","_"))
Bij gegevensvalidatie hebben we, om de afhankelijke lijst te maken, de functie INDIRECT gebruikt om de waarde te retourneren op basis van de primaire gegevensvalidatielijst. En om het onderstrepingsteken te vervangen door spatie, gebruiken we de SUBSTITUTE-functie in de INDIRECT-functie.
- Klik op OK
Wanneer we een productcode selecteren in cel J10, verschijnt de productlijst met de geselecteerde productcode in cel K10. Bijvoorbeeld: - We hebben ETV 501 geselecteerd, nu kunt u zien dat de afhankelijke productlijst verschijnt in cel K10
Opmerking: - Telkens wanneer u de productnaam en productcode toevoegt die niet in de lijst zullen verschijnen.
Bijvoorbeeld: - We hebben Product 26 toegevoegd onder de ETV 505-productcode, maar wanneer we ETV 505-product selecteren, verschijnt het toegevoegde product niet in de vervolgkeuzelijst.
Dit is dus hoe u een afhankelijke vervolgkeuzelijst kunt maken met behulp van een eenvoudige techniek in slechts 3 eenvoudige stappen.
2nd Voorbeeld:-
In dit voorbeeld zullen we zien hoe u een afhankelijke vervolgkeuzelijst kunt krijgen wanneer u uw gegevens hebt zoals weergegeven in deze verticale tabel.
We zullen twee verschillende methoden gebruiken om een afhankelijke vervolgkeuzelijst te maken. Beide zijn bijna vergelijkbare technieken. De ene is echter zonder het benoemde bereik en de andere heeft het benoemde bereik.
1NS Methode:-
Om hetzelfde te doen, zullen we de functies OFFSET, MATCH & COUNTIF samen gebruiken.
Omdat we weten dat de OFFSET-functie wordt gebruikt om het dynamische bereik te maken, gebruiken we de OFFSET-functie om het dynamische bereik te retourneren.
MATCH wordt gebruikt om de relatieve positie van een item in een lijst in Excel te retourneren. En hier zal het ons helpen om de categorie te matchen die is geselecteerd in de primaire vervolgkeuzelijst in ons bereik op het blad, en het zal een nummer retourneren.
En AANTAL.ALS wordt gebruikt om het aantal cellen te krijgen dat overeenkomt met de criteria. En hier zullen we dit gebruiken om het aantal rijen te tellen dat wordt weergegeven met behulp van de AANTAL.ALS-functie.
Volg de onderstaande stappen:-
- Selecteer de cel J21, waarin we onze primaire gegevensvalidatielijst zullen maken
- Druk op de toets ALT+D+L om het dialoogvenster Gegevensvalidatie te openen
- Selecteer lijst uit categorie toestaan
- Klik op het tabblad Bron en selecteer het bereik van B20:B24
- En klik op OK
- Ga naar cel K21 en open het dialoogvenster voor gegevensvalidatie nogmaals
- Vervolgens selecteren we Lijst en voeren in de bron onderstaande functie in:
- =OFFSET($E$19,MATCH($J$21,$D$20:$D$32,0),0,COUNTIF($D$20:$D$32,$J$21))
- Klik op OK
- In cel K21 kunnen we alle corresponderende waarden van de geselecteerde productcode zien: -
Dit is dus hoe u de afhankelijke lijst kunt krijgen door celverwijzingen in de functie te nemen.
2nd Methode:-
In de volgende methode zullen we het genoemde bereik in dezelfde functie gebruiken om de trapsgewijze gegevensvalidatie te krijgen. Eerst moeten we de dynamische lijst voor productcode maken. Als er een nieuw product aan de gegevens wordt toegevoegd, moet de vervolgkeuzelijst worden bijgewerkt om hetzelfde weer te geven.
Volg de onderstaande stappen om hetzelfde te doen::-
- Selecteer B19 en druk vervolgens op CTRL + F3 om het venster "Name Manager" te openen
- Nu klikken we op "Nieuw" en het dialoogvenster "Naam definiëren" verschijnt
- We kunnen zien dat de naam al in het naamvak verschijnt - dat komt omdat we B9 hebben geselecteerd voordat we het venster "Name Manager" openden. En aangezien B19 tekst bevat, kunnen we die, als we dat willen, veranderen in een andere naam.
- Voer onderstaande formule in: -
=OFFSET('DependentDropDownList'!$B$20,0,0,COUNTA('DependentDropDownList'!$B$20:$B$32))
- Klik op OK
Omdat we een dynamische lijst voor unieke producten hebben gemaakt, zullen we nu een dynamisch bereik maken voor het productcodebereik dat zich in kolom D bevindt.
Volg dezelfde stappen die we hebben gevolgd voor een uniek product: -
- Selecteer de cel D19, open het dialoogvenster Naam definiëren
- Je zult zien dat de naam er al is
- Voer bij verwijst de onderstaande formule in: -
=OFFSET('Afhankelijke vervolgkeuzelijst'!$D$20,0,0,COUNTA('Afhankelijke vervolgkeuzelijst'!$D$20:$D$35))
- Klik op OK
- Nu zijn beide dynamische bereiken klaar. Dus we gaan naar J22 en drukken op "ALT + D + L" en selecteren "Lijst"
- In de bron hebben we het benoemde bereik dat we hebben gedefinieerd voor "Unieke productcode", dus we drukken op F3 om alle beschikbare benoemde bereiken te zien
- We kunnen het genoemde bereik "Unieke productcode" zien, dus we klikken erop en klikken vervolgens op OK en we drukken op enter
- Op het moment dat we op enter drukken, krijgen we een vervolgkeuzepijl in cel J22, die de lijst met unieke productcodes bevat
- Selecteer cel K22 en open het dialoogvenster "Gegevensvalidatie"
- We zullen dezelfde functie gebruiken die we in de laatste methode hebben gebruikt, maar met genoemd bereik
- Selecteer lijst en voer vervolgens in de bron de onderstaande formule in: -
=OFFSET($E$19,MATCH($J$22,Product_Code,0),0,COUNTIF(Product_Code,J22))
- Klik op OK
- Nu hebben we de primaire vervolgkeuzelijst en de onderliggende lijst met producten
- Selecteer "ETV-101" product van J22, en in K22 kunnen we alleen de namen zien die onder dit "ETV-101" product vallen. En wanneer we een product (“ETV-103) in J22 wijzigen, geeft K22 de corresponderende waarden voor die code weer
Nu zullen we zien wat er gebeurt als we een nieuwe productcode aan de lijst toevoegen? Worden deze vervolgkeuzelijsten bijgewerkt?
Laten we een nieuw product aan de lijst toevoegen; Volg de onderstaande stappen: -
- Voeg productcode toe aan de lijst met Unique_Prod_Code
- Voeg ook de Product_Code en Product_Name toe aan de gegevens: -
- Controleer nu de vervolgkeuzelijst - productcode toegevoegd en naam verschijnt
3rd Voorbeeld:-
We hebben de dynamische rubrieken direct uit de tabel, en we zullen nieuwe producten aan het assortiment toevoegen. De tabel heeft hetzelfde formaat dat we gebruikten voor 1NS methode.
4e Methode:-
Volg de onderstaande stappen:-
- Selecteer de rubriek A40:E40
- Maak eerst het dynamische bereik voor koppen, open het dialoogvenster "Naam definiëren"
- Schrijf "Kop" in de plaats van de naam en voer vervolgens in "verwijst naar" de onderstaande formule in: -
- Voer de onderstaande functie in: -
- =OFFSET('Afhankelijke vervolgkeuzelijst'!$A$40,,,,COUNTA('Afhankelijke vervolgkeuzelijst'!$40:$40))
- Klik op OK
- Dynamisch "Heading" -bereik is nu klaar
En nu zullen we het genoemde bereik voor elke kop maken, volg de onderstaande stappen: -
- Selecteer de tafel van A40 tot E50
- CTRL + SHIFT + F3 sneltoets
- We deselecteren de 2nd optie
- En, voordat we op OK klikken, zorg ervoor dat de 1NS optie “Bovenste rij” is geselecteerd
- Nu zijn we klaar met beide reeksen
Nu zullen we de vervolgkeuzelijst voor ouders voorbereiden
- Selecteer de cel J42
- Open het dialoogvenster Gegevensvalidatie
- Nadat we "Lijst" hebben geselecteerd, drukken we op F3 in de bron om het genoemde bereik voor koppen te krijgen. We klikken op "Heading" en vervolgens op OK en druk op enter. We hebben nu de bovenliggende lijst in J42
- Om de lijst met itemdetails te maken, selecteert u de cel K42
- Open het dialoogvenster Gegevensvalidatie door op de toets ALT+D+L . te drukken
- Selecteer Lijst en voer vervolgens onderstaande functie in op het tabblad Bron: -
- =OFFSET(INDIRECT(VERVANG($J$42," ","_")),,,COUNTA(INDIRECT(VERVANGING($J$42," ","_")))))
- Klik op OK
Selecteer nu een item in de J42, stel dat we "Item 01" selecteren en kijk naar de vervolgkeuzelijst K42. En, net als de vorige 3 methoden, hebben we hier ook een afhankelijke lijst.
Dus, wat is er nieuw? In het eerste voorbeeld kon je geen enkel product aan de lijst toevoegen, maar hier kan je elk nieuw product toevoegen. Dus, stel dat we een nieuw product aan dit item toevoegen. We gaan naar A45, en we typen "ETV-501 Prod 05" en komen dan terug naar K42 en hier ga je. U ziet, het nieuwe product is toegevoegd.
- Voeg nu enkele producten toe onder het nieuwe item
Wanneer we "Item 06" selecteren, gaan we naar K42 en klikken op de vervolgkeuzelijst. Verrassend genoeg gebeurt er niets als we op de vervolgkeuzepijl klikken. Dat komt omdat we alles dynamisch hebben gemaakt en zijn vergeten om dynamisch bereik voor tafel te maken, daarom worden de producten niet weergegeven in de onderliggende lijst.
Om dat te doen, moeten we verschillende technieken gebruiken. Er zijn twee methoden om dat te doen. U kunt de tabel maken of alleen de OFFSET-functie gebruiken. En in de volgende methode zullen we de OFFSET-functie gebruiken, en we zullen de truc zien om ook het tafelbereik uit te breiden.
- Dus we gaan eerst naar J43 en drukken op "ALT + D + L"
- We selecteren "Lijst" en vervolgens in de bron drukken we op F3 en selecteren we "Kop" en klikken op OK en drukken vervolgens op enter
- Nu gaan we naar K43, en na het selecteren van "Lijst", gaan we naar "Bron" en voeren onderstaande functie in
=OFFSET($A$40,1,MATCH($J$43,$40:$40,0)-1,COUNTA(OFFSET($A$40,1,MATCH($J$43,$40:$40,0)-1,1000 ,1)))
- Klik op OK
Nu gaan we terug en selecteren "Item 06 in de J43-cel en keren terug naar K43 en klikken op de vervolgkeuzepijl. Maar deze tijdlijst toont producten die we hebben toegevoegd voor een nieuw item. En we selecteren het eerste product "ETV-506 Prod 01".
Dit is hoe u de afhankelijke vervolgkeuzelijst kunt maken met verschillende methoden op elk type gegevensformaat.
Video: Hoe maak je een afhankelijke (cascadering) vervolgkeuzelijst in Excel met behulp van 5 verschillende technieken in Microsoft Excel
Klik op de videolink voor een snelle verwijzing naar het gebruik ervan. Abonneer je op ons nieuwe kanaal en blijf met ons leren!
Als je onze blogs leuk vond, deel deze dan met je vrienden op Facebook. En je kunt ons ook volgen op Twitter en Facebook.
We horen graag van je, laat ons weten hoe we ons werk kunnen verbeteren, aanvullen of vernieuwen en het voor jou beter kunnen maken. Schrijf ons op de e-mailsite