In dit artikel leren we hoe u een dynamische vervolgkeuzelijst kunt maken in Microsoft Excel.
Zoals we weten, verbetert de functie Gegevensvalidatie de efficiëntie van gegevensinvoer in Excel en vermindert het fouten en typefouten. Het wordt gebruikt om de gebruiker te beperken voor het type gegevens dat in het bereik kan worden ingevoerd. In het geval van een ongeldige invoer, wordt een bericht weergegeven en kan de gebruiker de gegevens invoeren op basis van de opgegeven voorwaarde.
Maar een dynamische vervolgkeuzelijst in Excel is een handiger manier om gegevens te selecteren, zonder wijzigingen aan de bron aan te brengen. Met andere woorden, stel dat u de lijst regelmatig gaat bijwerken die u in de vervolgkeuzelijst hebt opgenomen. En u denkt dat als u wijzigingen aanbrengt in de lijst, u de gegevensvalidatie elke keer moet wijzigen om de bijgewerkte vervolgkeuzelijst te krijgen.
Maar dit is waar dynamische vervolgkeuzelijsten in beeld komen en het is de beste optie om gegevens te selecteren zonder wijzigingen aan te brengen in de gegevensvalidatie. Het lijkt erg op de normale gegevensvalidatie. Wanneer u de lijst echter bijwerkt, verandert de dynamische vervolgkeuzelijst om die actie mogelijk te maken, terwijl de normale vervolgkeuzelijst dat niet doet.
Laten we dus een voorbeeld nemen en begrijpen hoe we een dynamische vervolgkeuzelijst maken: -
We hebben een lijst met producten in kolom A en we krijgen de dynamische vervolgkeuzelijst met producten in cel D9.
Tabelnaam met indirecte functie
Eerst zullen we een tabel maken; volg de onderstaande stappen:-
- Selecteer het bereik A8:A16
- Ga naar Tabblad invoegen en klik vervolgens op Tabel
- Nadat u op de optie "Tabel" hebt geklikt, verschijnt er een tabelvenster
- Selecteer vervolgens het bereik waarvoor we tabel A8:A17 willen invoegen
- Klik op OK
- Nu klikken we op OK
- U kunt zien dat dit bereik is geconverteerd naar een tabel en de koptekst van deze tabel heeft ook een vervolgkeuzelijst voor filters
Opmerking: - Als we een product of item onderaan de lijst toevoegen, wordt de tabel automatisch uitgebreid om de nieuwe producten of items op te nemen.
Nu maken we de dynamische vervolgkeuzelijst in cel D9, volg de onderstaande stappen::-
- Selecteer de cel D9
- Open het dialoogvenster Gegevensvalidatie door op de toets ALT+D+L . te drukken
- Selecteer in de vervolgkeuzelijst Toestaan de optie Lijst
- En voer dan deze functie in =INDIRECT(“Tabel1”) in het brontabblad
- Klik op OK
Opmerking: - Als we op OK klikken, verschijnt er in Excel een venster met de melding dat er iets mis is met de invoer. Dat komt omdat Excel geen zelfuitbreidende tabel rechtstreeks in de gegevensvalidatie accepteert.
Voeg nu nieuwe producten toe in de productlijst.
We kunnen in de bovenstaande afbeelding zien dat er een nieuw toegevoegd product in de vervolgkeuzelijst verschijnt.
2nd Voorbeeld:-
In dit voorbeeld zullen we leren hoe we de tabelnaam als bereiknaam kunnen geven
We hebben de tabelnaam al, maar hier moeten we de naam van deze tabel definiëren om de dynamische vervolgkeuzelijst te krijgen; volg de onderstaande stappen: -
- Selecteer de cel D10
- Ga naar het tafelbereik en behalve de kop selecteren we het bereik van het eerste product tot het laatste product
- Ga naar het naamvak en typ de korte naam "tablerange", druk op Enter
- Nadat we op enter hebben gedrukt, zien we dat er niets is veranderd in het naamvak
- Klik op de vervolgkeuzelijstoptie om alle benoemde bereiken te zien die beschikbaar zijn
- In de vervolgkeuzelijst kunnen we zien dat de naam, die we zojuist voor deze tabel hebben gedefinieerd, ook verschijnt
- Nu gaan we naar Gegevensvalidatie en in "Bron" voeren we het "tabelbereik" in
Opmerking: - Als u niet meer weet welke naam u aan dat bereik hebt gegeven, kunt u op de F3-toets drukken en er verschijnt een venster met alle beschikbare benoemde bereiken.
- Ga nu naar het tabblad "Bericht invoeren" en in de titel typen we "Product selecteren" en vervolgens schrijven we in de berichttekst: "Selecteer uw product uit de lijst"
- Ga nu naar het tabblad "Foutwaarschuwing" en daar in de titel schrijven we "Ongeldig product" en in het foutbericht typen we "Je hebt een verkeerd product ingevoerd
- Klik op OK
- Cel D10 met invoerbericht samen met vervolgkeuzelijst
- Wanneer we nu een product aan de lijst toevoegen, verschijnt het automatisch in de vervolgkeuzelijst
Maar wat gebeurt er als we een cel na de laatste cel overslaan en vervolgens een nieuw product of item toevoegen? U kunt zien dat het tafelassortiment deze keer niet is uitgebreid, en in feite is het nieuw toegevoegde product in algemeen formaat. Dus, wordt het weergegeven in de vervolgkeuzelijst of niet? Om dat te controleren, kunnen we, wanneer we naar cel D10 gaan en de vervolgkeuzelijst controleren, dezelfde oude vervolgkeuzelijst zien zonder nieuw product. Het is omdat het tabelbereik niets heeft gevonden na de allerlaatste cel en daarom is het bereik niet uitgebreid.
3rd Voorbeeld:-
In de volgende twee methoden leren we hoe we onze vervolgkeuzelijst dynamischer kunnen maken door de functie OFFSET en COUNTA te gebruiken.
Volg de onderstaande stappen:-
- Selecteer cel D11 en druk op ALT + D + L
- Dialoogvenster Gegevensvalidatie wordt geopend
- Selecteer nu de lijst in de optie "Toestaan"
- Voer vervolgens in de optie Bron de onderstaande formule in: -
=OFFSET($A$9,0,0,COUNTA($A:$A),1)
Formule Uitleg:- We hebben A9 geselecteerd, het eerste product in het assortiment, en dan typen we 0 op de 2nd argument omdat we de rij niet vanaf het startpunt willen verplaatsen; dan weer 0 in de 3rd argument, want hier willen we geen wijzigingen in het aantal kolommen en ook niet vanaf het startpunt. En dan hebben we de COUNTA-functie ingevoerd en de hele kolom A geselecteerd. Dit argument controleert de hoogte in het aantal rijen om het niet-lege aantal te retourneren. Het zal het bereik uitbreiden wanneer er wijzigingen in het bereik worden aangebracht.
En het laatste argument "Breedte" is een optioneel argument. Het is de breedte in aantal kolommen. We kunnen het overslaan of voorlopig hier 1 typen. Als we overslaan, wordt standaard rekening gehouden met de breedte van het geretourneerde bereik dat we in het argument hebben opgegeven en vervolgens sluiten we de haakjes.
- Nadat we op OK hebben geklikt, zien we een vervolgkeuzelijst in cel D11
- Het toont de lijst inclusief blanco en vervolgens de producten die we hebben toegevoegd
4e Voorbeeld:-
In dit voorbeeld gebruiken we de functie om de naam te definiëren.
Volg de onderstaande stappen om de bereiknaam te definiëren: -
- Druk op CTRL + F3, het dialoogvenster Name Manager verschijnt
- Klik op Nieuw
- Definieer de bereiknaam "ProdName" en voer de onderstaande formule in: -
=OFFSET('Dynamische vervolgkeuzelijst met DV'!$A$9,0,0,COUNTA('Dynamische vervolgkeuzelijst met DV'!$A:$A))
- Klik op OK
- Open het dialoogvenster Gegevensvalidatie door op de toets Alt + D + L . te drukken
- Selecteer Lijst in de vervolgkeuzelijst Toestaan
- Voer =ProdName in op het tabblad Bron
- Klik op OK
- Als we nu iets in de lijst toevoegen, verschijnt hetzelfde in de lijst
Dit is dus hoe u de dynamische lijst voor elk product of item kunt krijgen met verschillende methoden met behulp van gegevensvalidatie. Dat is het voor nu. In de volgende video van deze serie zullen we uitleggen hoe u de afhankelijke vervolgkeuzelijst met verschillende methoden in Excel kunt maken.
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