Geavanceerd filter in Excel

Wat is een geavanceerd filter in Excel?

Geavanceerd filter verschilt van het autofilter in Excel, deze functie is niet als een knop die kan worden gebruikt met een enkele muisklik, om een ​​geavanceerd filter te gebruiken, moeten we eerst een criterium voor het autofilter definiëren en vervolgens klikken op het tabblad Gegevens en vervolgens in het geavanceerde gedeelte voor het geavanceerde filter waar we onze criteria voor de gegevens zullen invullen.

Hoe geavanceerd filter in Excel te gebruiken? (Met voorbeelden)

Laten we het gebruik hiervan door enkele voorbeelden leren.

U kunt deze geavanceerde filter Excel-sjabloon hier downloaden - Geavanceerde filter Excel-sjabloon

Voorbeeld 1

Stel dat we de volgende gegevens hebben om te filteren op basis van verschillende criteria.

We moeten de verkooptransactie controleren die is gemaakt door 'Taran' en 'Suresh', dan kunnen we de OR-operator gebruiken die de records weergeeft die voldoen aan een van de voorwaarden. Om de resultaten te krijgen, kunnen we de stappen volgen om deze filters in Excel toe te passen.

  • Stap 1: Om een ​​geavanceerd filter toe te passen, moeten we eerst een van de cellen in het gegevensbereik selecteren

  • Stap 2: klik vervolgens op het tabblad Gegevens- > Sorteren en filteren groep -> Geavanceerd commando

  • Stap 3: Als we op 'Geavanceerd' klikken , wordt een dialoogvenster 'Geavanceerd filter' geopend waarin u List Range vraagt om te filteren, Criteriabereik om de criteria te definiëren en Bereik extraheren voor het kopiëren van de gefilterde gegevens (indien gewenst).

  • Stap 4: Voor criteria moeten we de kolomkoppen naar de bovenste rij kopiëren en de criteria onder de veldkop definiëren. Om de criteria te specificeren, kunnen we de vergelijkingsoperator gebruiken, die als volgt is:

  • Stap 5: Omdat we alle records met de naam 'Suresh' of 'Taran' willen hebben. Het criteriabereik zou er als volgt uitzien:

Voor 'OF'-voorwaarden waarbij we de records willen weergeven die aan een van de voorwaarden voldoen, moeten we de criteria in verschillende rijen specificeren.

Er zijn twee acties in een geavanceerd filter.

  • Filter de lijst op zijn plaats : deze optie filtert de lijst op de oorspronkelijke plaats, dwz op het lijstbereik zelf en na analyse kunnen we het filter verwijderen met de opdracht 'Wissen' in de groep 'Sorteren en filteren' onder 'Gegevens'

  • Kopiëren naar een andere locatie : met deze optie worden de gewenste gegevens volgens de criteria naar het opgegeven bereik gekopieerd.

We kunnen elk van de opties gebruiken op basis van onze behoefte, maar we zullen de tweede optie vaker gebruiken.

Nu moeten we het gewoon doen

  • Open het dialoogvenster 'Geavanceerd filter'

  • Specificeer het lijstbereik als $ A $ 5: $ D $ 26, criteriumbereik als $ A $ 1: $ D $ 3 en ' Kopiëren naar' bereik als $ F $ 5: $ I $ 26. Klik op 'OK' .

We kunnen zien dat alle records met de naam 'Suresh' of 'Taran' worden uitgefilterd en afzonderlijk worden weergegeven in een ander celbereik.

Voorbeeld # 2

Nu willen we alle verkooptransacties van Qtr 1 en Zuid-India krijgen. Het criteriabereik is als volgt:

Omdat we hier de 'EN'-voorwaarde hebben, dwz we willen de records weergeven waarin aan beide voorwaarden is voldaan, daarom hebben we de criteria onder beide kolomkoppen in dezelfde rij genoemd.

Nu klikken we op de opdracht 'Geavanceerd' in de groep 'Sorteren en filteren' onder het tabblad 'Gegevens' .

Vanuit de ' Geavanceerde Filter ' dialoogvenster, kiezen we voor 'Kopiëren naar een andere locatie' en dan zal het definiëren A5: D26 als Lijst Range , A1: D2 als Criteria Range en F5: I26 als ' Kopiëren naar' range.

Nu is het resultaat als volgt:

Voorbeeld # 3

Nu willen we verkopen vinden in Qtr 1 of gemaakt in Noord-India.

We moeten zowel de criteria in verschillende rijen als in verschillende kolommen specificeren, omdat we de gegevens moeten weergeven als aan een van de voorwaarden is voldaan en beide voorwaarden zijn gerelateerd aan verschillende kolommen.

Stappen:

  • U moet het dialoogvenster 'Geavanceerd filter' openen.

  • Specificeer Lijstbereik als $ A $ 5: $ D $ 26

  • Specificeer criteriabereik als $ A $ 1: $ D $ 3

  • Specificeer het bereik 'Kopiëren naar' als $ F $ 5: $ I $ 26

Het resultaat zou als volgt zijn:

Voorbeeld # 4

Nu willen we alle verkopen van Rs vinden. 2000-4000 en Rs. 10000-13000.

Omdat we vier voorwaarden hebben als (voorwaarde 1 EN voorwaarde 2) OF (voorwaarde 3 EN voorwaarde 4).

(> = 2000 EN = 10000 EN <= 13000)

Daarom hebben we de voorwaarden met “ AND” in dezelfde rij en Condities met “OR” in verschillende rijen genoemd.

Stappen:

  • Om het dialoogvenster 'Geavanceerd filter' te openen, klikken we op 'Geavanceerd' in de groep 'Sorteren en filteren' onder 'Gegevens'

  • In het dialoogvenster 'Geavanceerd filter' zullen we specificeren
  • Geef bereik op als $ A $ 5: $ D $ 26

  • Criteriabereik als $ A $ 1: $ D $ 3

  • Bereik 'Kopiëren naar' als $ F $ 5: $ I $ 26

  • Na klikken op 'OK'. Het resultaat is:

Voorbeeld # 5

Nu willen we de verkoop van Qtr 1 door Sunny of die van Qtr 3 door Mukesh vinden.

Omdat we AND en OR hebben , beide typen relaties in condities, zullen we daarom de condities specificeren in het criteriabereik in verschillende rijen (OR) en verschillende kolommen (AND).

Stappen:

  • Om het dialoogvenster 'Geavanceerd filter' te openen, klikken we op 'Geavanceerd' in de groep 'Sorteren en filteren' onder 'Gegevens'

  • In het dialoogvenster 'Geavanceerd filter' zullen we specificeren
  • Geef bereik op als $ A $ 5: $ D $ 26

  • Criteriabereik als $ A $ 1: $ D $ 3

  • Bereik 'Kopiëren naar' als $ F $ 5: $ I $ 26

  • Nadat u op OK hebt geklikt, zou het resultaat zijn

Voorbeeld # 6 - WILDCARD-tekens gebruiken

Nu willen we alle verkooptransacties vinden met een naam die eindigt op 'esh' of het eerste woord van de regio dat eindigt op 'st' en willen we alleen de naam, verkoop en regio ophalen.

Hier * geeft meer dan één letterteken aan en

'?' geeft slechts één teken aan.

Omdat we slechts enkele kolommen willen, niet alle, moeten we de kolomlabels specificeren op Kopiëren naar bereik voordat we het geavanceerde filter implementeren.

Nu zullen we het commando bellen.

Stappen:

  • Om het dialoogvenster 'Geavanceerd filter' te openen, klikken we op 'Geavanceerd' in de groep 'Sorteren en filteren' onder 'Gegevens'

  • In het dialoogvenster 'Geavanceerd filter' zullen we specificeren
  • Geef bereik op als $ A $ 5: $ D $ 26

  • Criteriabereik als $ A $ 1: $ D $ 3

  • 'Kopiëren naar' bereik als $ F $ 5: $ H $ 26

  • Na klikken op ' OK'. Het resultaat zou zijn:

Voorbeeld # 7

Nu willen we de top vijf verkopen filteren (van een groot aantal).

De formulecel moet resulteren in WAAR of ONWAAR . Omdat we de grootste 5 records willen krijgen, hebben we daarom LARGE Excel-functie gebruikt en de waarde vergeleken met het verkoopbedrag .

Zoals we kunnen zien, is de kolomkop voor de formulecel leeg. We kunnen het blanco laten of de naam, die niet overeenkomt, geven met een van de koppen van de kolom in het gegevensbereik.

Nu zullen we de bereiken specificeren in het dialoogvenster 'Geavanceerd filter' . Stappen zijn:

  • Om het dialoogvenster 'Geavanceerd filter' te openen, klikken we op 'Geavanceerd' in de groep 'Sorteren en filteren' onder 'Gegevens'

  • In het dialoogvenster 'Excel Advanced Filter' specificeren we
  • Geef bereik op als $ A $ 5: $ D $ 26

  • Criteriabereik als $ A $ 1: $ E $ 2

  • Bereik 'Kopiëren naar' als $ F $ 5: $ I $ 26

  • Nadat u op OK hebt geklikt . Het resultaat zou als volgt zijn:

Dingen om te onthouden

  • Het bereik waarop het moet worden toegepast, moet een unieke kop hebben, aangezien dubbele koppen problemen veroorzaken bij het uitvoeren van een geavanceerd filter.
  • Er moet ten minste één lege rij staan ​​tussen Lijstbereik en Criteriabereik.