Bekijk video - Extraheer gegevens met behulp van een vervolgkeuzelijst in Excel
In deze zelfstudie laat ik u zien hoe u een vervolgkeuzefilter in Excel maakt, zodat u gegevens kunt extraheren op basis van de selectie in de vervolgkeuzelijst.
Zoals te zien is in de onderstaande foto, heb ik een vervolgkeuzelijst gemaakt met landnamen. Zodra ik een land selecteer in de vervolgkeuzelijst, worden de gegevens voor dat land aan de rechterkant geëxtraheerd.
Merk op dat zodra ik India selecteer in het vervolgkeuzefilter, alle records voor India worden geëxtraheerd.
Gegevens extraheren uit vervolgkeuzelijstselectie in Excel
Dit zijn de stappen om een vervolgkeuzefilter te maken waarmee gegevens voor het geselecteerde item worden geëxtraheerd:
- Maak een unieke lijst met items.
- Voeg een vervolgkeuzefilter toe om deze unieke items weer te geven.
- Gebruik hulpkolommen om de records voor het geselecteerde item te extraheren.
Laten we eens diep duiken en kijken wat er in elk van deze stappen moet gebeuren.
Maak een unieke lijst met items
Hoewel er herhalingen kunnen zijn van een item in uw dataset, hebben we unieke itemnamen nodig zodat we er een vervolgkeuzefilter mee kunnen maken.
In het bovenstaande voorbeeld is de eerste stap het verkrijgen van de unieke lijst van alle landen.
Dit zijn de stappen om een unieke lijst te krijgen:
- Selecteer alle landen en plak deze op een ander deel van het werkblad.
- Ga naar Gegevens -> Duplicaten verwijderen.
- Selecteer in het dialoogvenster Duplicaten verwijderen de kolom met de lijst met landen. Dit geeft u een unieke lijst zoals hieronder weergegeven.
Nu zullen we deze unieke lijst gebruiken om de vervolgkeuzelijst te maken.
Zie ook: De ultieme gids voor het zoeken en verwijderen van duplicaten in Excel.
Het vervolgkeuzefilter maken
Dit zijn de stappen om een vervolgkeuzelijst in een cel te maken:
- Ga naar Gegevens -> Gegevensvalidatie.
- Selecteer in het dialoogvenster Gegevensvalidatie het tabblad Instellingen.
- Selecteer op het tabblad Instellingen "Lijst" in de vervolgkeuzelijst en selecteer in het veld 'Bron' de unieke lijst met landen die we hebben gegenereerd.
- Klik OK.
Het doel is nu om een willekeurig land te selecteren in de vervolgkeuzelijst, en dat zou ons de lijst met records voor het land moeten geven.
Om dit te doen, zouden we hulpkolommen en formules moeten gebruiken.
Helperkolommen maken om de records voor het geselecteerde item te extraheren
Zodra u de selectie uit de dropdown maakt, heeft u Excel nodig om automatisch de records te identificeren die bij dat geselecteerde item horen.
Dit kan met behulp van drie hulpkolommen.
Dit zijn de stappen om hulpkolommen te maken:
- Helper Kolom #1 - Voer het serienummer in voor alle records (20 in dit geval kunt u de functie ROWS() gebruiken om dit te doen).
- Helperkolom #2 - Gebruik deze eenvoudige IF-functiefunctie: =IF(D4=$H$2,E4,””)
- Deze formule controleert of het land in de eerste rij overeenkomt met het land in het dropdownmenu. Dus als ik India selecteer, wordt gecontroleerd of de eerste rij India als land heeft of niet. Als het waar is, retourneert het dat rijnummer, anders retourneert het blanco (""). Als we nu een land selecteren, worden alleen die rijnummers weergegeven (in de tweede hulpkolom) waarin het geselecteerde land staat. (Als India bijvoorbeeld is geselecteerd, ziet het er uit als de onderstaande afbeelding).
Nu hoeven we alleen de gegevens voor deze rijen te extraheren, die het nummer weergeven (omdat het de rij is die dat land bevat). We willen echter die records een voor een zonder de lege plekken. Dit kan worden gedaan met behulp van een derde helperkolom
- Derde Helper-kolom - Gebruik de volgende combinatie van IFERROR- en SMALL-functies:
=IFERROR(KLEIN($F$4:$F$23,E4),””)
Dit zou ons iets geven zoals hieronder op de foto wordt getoond:
Als we nu het nummer bij elkaar hebben, hoeven we alleen de gegevens in dat nummer te extraheren. Dit kan eenvoudig worden gedaan met behulp van de INDEX-functie (gebruik deze formule in de cellen waar u het resultaat wilt ophalen):
=IFERROR(INDEX($B$4:$D$23,$G4,KOLOMMEN($J$3:J3)),””)
Deze formule bestaat uit 2 delen:
INHOUDSOPGAVE - Dit extraheert de gegevens op basis van het rijnummer
IFERROR - Deze functie retourneert blanco als er geen gegevens zijn
Hier is een momentopname van wat je uiteindelijk krijgt:
U kunt nu desgewenst de originele gegevens verbergen. U kunt ook de originele gegevens en de geëxtraheerde gegevens in twee verschillende werkbladen hebben.
Ga je gang. gebruik deze techniek en maak indruk op je baas en collega's (een beetje opschepperij is nooit verkeerd).
Download het voorbeeldbestand
Vond je de tutorial leuk? Laat me je mening weten in de comments.
Mogelijk vindt u de volgende zelfstudies ook nuttig:
- Dynamisch Excel-filter - Extraheer gegevens terwijl u typt.
- Dynamisch zoeken in Excel met voorwaardelijke opmaak.
- Creëer een dynamische vervolgkeuzelijst met zoeksuggesties.
- Hoe een subtekenreeks in Excel te extraheren met formules
- Cellen filteren met vetgedrukte lettertype-opmaak in Excel
