Bekijk video - Excel geavanceerd filter
Excel Advanced Filter is een van de meest onderschatte en onderbenutte functies die ik ben tegengekomen.
Als je met Excel werkt, weet ik zeker dat je het reguliere Excel-filter hebt gebruikt (of in ieder geval hebt gehoord). Het filtert snel een dataset op basis van selectie, gespecificeerde tekst, nummer of andere dergelijke criteria.
In deze handleiding laat ik je een aantal leuke dingen zien die je kunt doen met het geavanceerde Excel-filter.
Maar eerst… Wat is Excel Advanced Filter?
Excel Advanced Filter is - zoals de naam al doet vermoeden - de geavanceerde versie van het reguliere filter. U kunt dit gebruiken wanneer u complexere criteria moet gebruiken om uw dataset te filteren.
Hier zijn enkele verschillen tussen het gewone filter en het geavanceerde filter:
- Hoewel het normale gegevensfilter de bestaande gegevensset filtert, kunt u het geavanceerde Excel-filter gebruiken om de gegevensset ook naar een andere locatie te extraheren.
- Met Excel Advanced Filter kunt u complexe criteria gebruiken. Als u bijvoorbeeld verkoopgegevens heeft, kunt u gegevens filteren op een criterium waarbij de verkoper Bob is en de regio Noord of Zuid is (we zullen in voorbeelden zien hoe u dit doet). Office-ondersteuning heeft hier een goede uitleg over.
- U kunt het Excel Advanced Filter gebruiken om unieke records uit uw gegevens te extraheren (daarover later meer).
EXCEL GEAVANCEERD FILTER (Voorbeelden)
Laten we nu eens kijken naar een voorbeeld over het gebruik van het geavanceerde filter in Excel.
Voorbeeld 1 - Een unieke lijst extraheren
U kunt Excel Advanced Filter gebruiken om snel unieke records uit een dataset te extraheren (of met andere woorden om duplicaten te verwijderen).
In Excel 2007 en latere versies is er een optie om duplicaten uit een dataset te verwijderen. Maar dat verandert uw bestaande dataset. Om de originele gegevens intact te houden, moet u een kopie van de gegevens maken en vervolgens de optie Duplicaten verwijderen gebruiken. Met Excel Advanced filter kunt u een locatie selecteren om een unieke lijst te krijgen.Laten we eens kijken hoe we geavanceerde filters kunnen gebruiken om een unieke lijst te krijgen.
Stel dat u een dataset heeft zoals hieronder weergegeven:
Zoals u kunt zien, zijn er dubbele records in deze dataset (gemarkeerd in oranje). Deze kunnen te wijten zijn aan een fout in de gegevensinvoer of het resultaat van de gegevensverzameling.
In zo'n geval kunt u Excel Advanced Filter-tool gebruiken om snel een lijst te krijgen van alle unieke records op een andere locatie (zodat uw oorspronkelijke gegevens intact blijven).
Dit zijn de stappen om alle unieke records te krijgen:
- Selecteer de volledige dataset (inclusief de headers).
- Ga naar het tabblad Gegevens -> Sorteren en filteren -> Geavanceerd. (U kunt ook de sneltoets gebruiken - Alt + A + Q). Hierdoor wordt het dialoogvenster Geavanceerd filter geopend.
- Gebruik in het dialoogvenster Geavanceerd filter de volgende details:
- Actie: Selecteer de optie 'Kopiëren naar een andere locatie'. Hiermee kunt u de locatie opgeven waar u de lijst met unieke records kunt krijgen.
- Lijstbereik: Zorg ervoor dat het verwijst naar de dataset waaruit u unieke records wilt zoeken. Zorg er ook voor dat headers in de dataset zijn opgenomen.
- Criteriabereik: Laat dit leeg.
- Kopiëren naar: Geef het celadres op waar u de lijst met unieke records wilt ophalen.
- Alleen unieke records kopiëren: Vink deze optie aan.
- Klik OK.
Dit geeft u direct een lijst met alle unieke records.
Voorzichtigheid: Wanneer u Geavanceerd filter gebruikt om de unieke lijst te krijgen, zorg er dan voor dat u ook de koptekst hebt geselecteerd. Als u dat niet doet, wordt de eerste cel als de koptekst beschouwd.
Voorbeeld 2 - Criteria gebruiken in Excel Advanced Filter
Het verkrijgen van unieke records is een van de vele dingen die u kunt doen met het geavanceerde filter van Excel.
Het belangrijkste nut ligt in de mogelijkheid om complexe criteria voor het filteren van gegevens toe te staan.
Dit is wat ik bedoel met complexe criteria. Stel dat u een dataset heeft zoals hieronder weergegeven en dat u snel alle records wilt krijgen waarvan de verkoop groter is dan 5000 en de regio de VS is.
Hier leest u hoe u Excel Advanced Filter kunt gebruiken om de records te filteren op basis van de opgegeven criteria:
- De eerste stap bij het gebruik van Excel Advanced Filter met complexe criteria is het specificeren van de criteria. Kopieer hiervoor de kopteksten en plak deze ergens in het werkblad.
- Geef de criteria op waarvoor u de gegevens wilt filteren. Aangezien we in dit voorbeeld alle records voor de VS willen hebben met verkopen van meer dan 5000, voert u 'VS' in de cel onder Regio in en >5000 in de cel onder Verkoop. Dit zou nu worden gebruikt als invoer in Geavanceerd filter om de gefilterde gegevens te krijgen (zoals weergegeven in de volgende stappen).
- Selecteer de volledige dataset (inclusief de headers).
- Ga naar het tabblad Gegevens -> Sorteren en filteren -> Geavanceerd. Hierdoor wordt het dialoogvenster Geavanceerd filter geopend.
- Gebruik in het dialoogvenster Geavanceerd filter de volgende details:
- Actie: Selecteer de optie 'Kopiëren naar een andere locatie'. Hiermee kunt u de locatie opgeven waar u de lijst met unieke records kunt krijgen.
- Lijstbereik: Zorg ervoor dat het verwijst naar de dataset waaruit u unieke records wilt zoeken. Zorg er ook voor dat headers in de dataset zijn opgenomen.
- Criteriabereik: Geef de criteria op die we in de bovenstaande stappen hebben opgesteld. In dit voorbeeld zou dit F1:I3 zijn.
- Kopiëren naar: Geef het celadres op waar u de lijst met unieke records wilt ophalen.
- Alleen unieke records kopiëren: Vink deze optie aan.
- Klik OK.
Dit zou u onmiddellijk alle records geven waar de regio de VS is en de verkoop meer dan 5000 is.
Het bovenstaande voorbeeld is een geval waarin de filtering wordt gedaan op basis van twee criteria (VS en verkopen groter dan 5000).
Excel Advanced-filter stelt u in staat om veel verschillende combinaties van criteria te maken.
Hier zijn enkele voorbeelden van hoe u deze filters kunt maken.
De EN-criteria gebruiken
Als u AND-criteria wilt gebruiken, moet u dit onder de kop opgeven.
Bijvoorbeeld:
- Om records te filteren wanneer de regio de VS is EN de verkoper Joe is.
- Om records te filteren wanneer de regio de VS is EN de verkoopwaarde groter is dan 5000.
- Wanneer de regio de VS is EN de verkopen worden geregistreerd na 31-03-2017.
De OK-criteria gebruiken
Als u OR-criteria wilt gebruiken, moet u de criteria in dezelfde kolom specificeren.
Bijvoorbeeld:
- Om records te filteren wanneer de regio de VS is OF de regio Azië is.
- Records filteren wanneer de verkoper Bob OF Martha is.
Voorbeeld 3 - WILDCARD-tekens gebruiken in geavanceerd filter in Excel
Excel Advanced Filter staat ook het gebruik van jokertekens toe tijdens het samenstellen van de criteria.
Er zijn drie jokertekens in Excel:
- * (sterretje) - Het vertegenwoordigt een willekeurig aantal tekens. Ex* kan bijvoorbeeld betekenen excel, excels, example, expert, etc.
- ? (vraagteken) - Het vertegenwoordigt één enkel teken. Tr?mp kan bijvoorbeeld Trump of Vagebond betekenen.
- ~ (tilde) - Het wordt gebruikt om een jokerteken (~, *, ?) in de tekst te identificeren.
Laten we nu eens kijken hoe we deze jokertekens kunnen gebruiken om geavanceerde filtering in Excel uit te voeren.
- Als u records wilt filteren waarvan de naam van de verkoper begint met J.
Merk op dat * een willekeurig aantal tekens vertegenwoordigt. Dus elke vertegenwoordiger met de naam die begint met J wordt gefilterd met deze criteria.
Op dezelfde manier kunt u ook de andere twee jokertekens gebruiken.
Opmerking: als u Office 365 gebruikt, moet u de FILTER-functie bekijken. Het kan veel dingen doen die een geavanceerd filter kan doen met een eenvoudige formule.
OPMERKING:
- Onthoud dat de koppen in de criteria exact hetzelfde moeten zijn als die in de dataset.
- Geavanceerde filtering kan niet ongedaan worden gemaakt bij het kopiëren naar andere locaties.
