Home Blog

Tabel filteren tijdens typen (VBA)

0

Je hebt een tabel met veel informatie en je wilt snel kunnen filteren zonder te veel te moeten klikken. Dan is het mooi als je direct een invoerveld hebt om je zoekopdracht te starten.

Tabel filteren

Als je in een Excel tabel wilt filteren op een gedeeltelijke waarde dan moet je het filter open klikken en kun je typen in het invoerveld. Je klikt op OK en je hebt gefilterd. In onderstaand voorbeeld filteren we in een kolom met opslagcapaciteit van telefoons op de waarde “32”. We willen alle telefoons filteren die een opslagcapaciteit hebben van o.a. 32 GB.

Tabel filteren op tekst in Excel
Tabel filteren op gedeeltelijke waarde in Excel

Bij het filteren op 1 waarde is dat nog wel te doen. Maar als je het filter wilt toepassen op meerdere kolommen dan moet je veel klikken. Daarom is het handig om deze invoervelden direct in je werkblad te plaatsen zodat je snel op meerdere waarden filteren tijdens het typen.

Om dit voorbeeld te maken, maken we gebruik van het tabblad “Ontwikkelaars” in het lint. Als deze niet beschikbaar is, dan rechts-klik je op het lint en selecteer je “Het lint aanpassen…“. Zorg dat het hoofdtabblad “Ontwikkelaars” is aangevinkt en klik op OK.

Stap voor stap: Tabel filteren instellen tijdens typen

Benodigde tijd: 8 minuten

  1. Filteren op merk

    Schrijf in cel B2 “Merk:
    Ga in het lint naar tabblad “Ontwikkelaars“. Klik op “Invoegen” bij “Besturingselementen” en selecteer “Tekstvak“. Er verschijnt een plusje waarmee een tekstvak kunt tekenen. Na het tekenen kun je het tekstvak selecteren en verslepen. Versleep het tekstvak ongeveer over cel C2.

    Tekstvak maken en op juiste plek zetten.
    Let op: Je kunt een besturingselement alleen bewerken als “Ontwerpmodus” is ingeschakeld in het lint “Ontwikkelaarsmodus“.

  2. Eigenschappen besturingselement

    Rechtsklik op het tekstvak en selecteer “Eigenschappen” in het dialoogvenster.
    Zoek in de lijst met eigenschappen naar “LinkedCell” en voer hier C2 in.
    Eigenschappen besturingselement invoeren.

  3. VBA toevoegen voor tekstvak

    Nu willen we dat het filter aanpast als we iets typen in het tekstvak. Dat doen door middel van een VBA code in te voeren. Rechtsklik op het tekstvak en klik op “Programmacode weergeven“. De VBA editor opent en er wordt automatisch een change-event aangemaakt voor dit tekstvak.

    Private Sub TextBox1_Change()

    End Sub


    Wij voegen hier de volgende code tussen:

    ActiveSheet.ListObjects(“telefoons”).Range.AutoFilter Field:=1, _
    Criteria1:=”” & [C2] & ““, Operator:=xlFilterValues

    De volledige code ziet er dus als volgt uit:

    VBA code voor tekstvak
    telefoons” staat hier voor de tabelnaam.
    “C2” staat gelijk aan de “LinkedCell” van stap 2.
    Field:=1” refereert naar filteren op kolom 1 van de tabel.

    Schakel de ontwerpmodus uit in het lint “Ontwikkelaars” en test door te typen in het tekstvak. Je ziet nu dat de lijst direct filtert op wat je typt.

  4. Reset knop toevoegen

    Om je filter te wissen kun je een resetknop toevoegen. We gaan eerst de knop maken. Ga in het lint naar tabblad “Ontwikkelaars” en klik op “Invoegen” en selecteer de opdrachtknop. Er verschijnt een plusje op je scherm waarmee je de knop kunt tekenen.

    Knop toevoegen voor tabel filter.

  5. Reset knop aanpassen

    Rechtsklik op de knop en selecteer “Opdrachtknop-object -> Bewerken“.

    Verander de tekst van de knop naar RESET.Knop bewerken.

  6. Macro toevoegen aan reset knop

    We gaan nu een opdracht aan de knop toevoegen. Rechtsklik op de knop en klik op “Programmacode weergeven”. De VBA editor opent met de volgende code:

    Private Sub CommandButton1_Click()

    End Sub

    We voegen de volgende code toe:
    [C2] = “”
    ActiveSheet.ShowAllData
    TextBox1.Activate


    De totale code ziet er dan als volgt uit:

    [C2] = “” zorgt ervoor dat de cel C2 leeg wordt gemaakt. (linked cell uist stap 2)
    ActiveSheet.ShowAllData wist alle filters op de tabel.
    TextBox1.Activate zorgt dat het tekstvak weer is geselecteerd voor een volgende zoekopdracht.
    VBA code filter wissen.

  7. Tweede filter toepassen op de tabel

    We gaan nu een extra filter toevoegen voor de kolom Model.

    Ga naar cel B4 en schrijf hier “Model:“. Ga in het lint naar tabblad “Ontwikkelaars“. Klik op “Invoegen” bij “Besturingselementen” en selecteer “Tekstvak“. teken een tweede tekstvak in cel C4.

    Tweede tekstvak toevoegen voor tabel filteren.

  8. Eigenschappen tweede tekstvak

    Rechtsklik op het tweede tekstvak en selecteer “Eigenschappen” in het dialoogvenster.
    Zoek in de lijst met eigenschappen naar “LinkedCell” en voer hier C4 in.

    LinkedCell voor tweede tekstvak

  9. VBA toevoegen voor tweede tekstvak

    Dubbel klik op het tekstvak of rechtsklik op het tekstvak en klik op “Programmacode weergeven“. De VBA editor opent en je ziet de volgende code staan:

    Private Sub TextBox2_Change()

    End Sub


    Voeg hier de volgende code aan toe:

    ActiveSheet.ListObjects(“telefoons”).Range.AutoFilter Field:=2, _
    Criteria1:=”” & [C
    4] & “”, Operator:=xlFilterValues

    VBA voor tweede tekstvak.telefoons” staat hier voor de tabelnaam.
    C4” staat gelijk aan de “LinkedCell” van stap 8.
    Field:=2″ refereert naar filteren op kolom 2 (Model) van de tabel.

  10. Reset knop aanpassen

    Met het toevoegen van het tweede tekstveld moet ook de code voor de reset knop worden aangepast. Dubbelklik op de reset knop of rechtsklik en selecteer “Programmacode weergeven” om de VBA editor te openen.
    We moeten nu ook cel C4 wissen tijden het resetten. De volledige code ziet er als volgt uit:

    Resetcode aanpassen in de VBA.

Eindresultaat tabel filteren

Je kunt zoveel tekst vakken en filters toevoegen als gewenst. In dit voorbeeld heb ik nog een derde tekst vak toegevoegd en gekoppeld aan kolom 8, Opslagcapaciteit.

Het eindresultaat ziet er als volgt uit:

Eindresultaat tabel filteren tijdens typen

Download bestand

Klik op de download link om het kant en klare bestand te downloaden.
tabel_filteren_tijdens_typen_met_VBA_final.xlsm

Willekeurig sorteren

0

Vorige: Data sorteren op kleur

In dit voorbeeld gaan we een dataset willekeurig sorteren. Dat doen we door middel van een hulpkolom met een formule.

Benodigde tijd: 1 minuut

  1. Formule ASELECT()

    Zet in cel B2 de formule ASELECT().Deze functie zorgt voor een willekeurig getal tussen 0 en 1 in de cel B2.

    Willekeurig sorteren met ASELECT

  2. Sleep de formule door

    Klik rechts beneden op het groene vierkantje en sleep de formule door naar het einde van je lijst in cel B13.

    Willekeurig sorteren met ASELECT

  3. Sorteren van Z naar A

    Selecteer een willekeurige cel met getallen in kolom B. Klik in het lint op het tabblad “Gegevens” en klik “Z-A” in het menu “Sorteren en Filteren“.

    Willekeurig sorteren van Z naar A

  4. Resultaat willekeurige volgorde

    Het resultaat is de lijst in kolom A in willekeurige volgorde gesorteerd.

    Willekeurig sorteren van Z naar A het resultaat
    De functie ASELECT() veranderd de getallen na elke handeling op het werkblad. Wil je de getallen vastzetten, dan kun je kolom B kopiëren en plakken als waarde.

  5. ASELECT.MATRIX voor willekeurige getallen

    Als je gebruik maakt van Office365 of Excel 2021 kun je ook de formule ASELECT.MATRIX gebruiken om een lijst van unieke nummers te maken.
    De 12 staat voor het aantal rijen, de 1 staat voor het aantal kolommen.
    Willekeurig sorteren met ASELECT.MATRIX

  6. SORTEREN.OP()

    De SORTEREN.OP() functie kun je een dataset sorteren op basis van de waarden in de naastgelegen cel. Standaard sorteert deze functie op oplopende volgorde.
    Met deze functie sorteer je de waarden in kolom A op basis van de oplopende waarden in kolom B.

    Willekeurig sorteren met SORTEREN.OP
    Kolom A is gesorteerd van Z naar A, kolom C is gesorteerd van A naar Z.

Data sorteren op kleur in Excel

0

Vorige: Aangepaste sorteervolgorde

In dit voorbeeld gaan we de dataset sorteren op kleur. We hebben een voorbeeld met groene, gele en rode rijen. In dit voorbeeld gaan we sorteren op volgorde van groen, geel naar rood.

Excel data set sorteren kleur

Benodigde tijd: 1 minuut

  1. Selecteer een cel

    Klik op een willekeurige cel in je tabel tabel of bereik.Excel data set sorteren kleur

  2. Gegevens -> Sorteren en filteren

    Ga in het lint naar het tabblad “Gegevens” en klik op “Sorteren”.Knop sorteren in Excel

  3. Popup sorteren

    Er verschijnt een popup “Sorteren”.
    Sorteer op voornaam en klik bij “Sorteren op” de optie “Celkleur“.Sorteren op kleur - Selecteer een kleur

  4. Selecteer de kleur

    Er verschijnt een extra sorteer optie waar je kunt kiezen op welke kleur je wilt sorteren. Selecteer de groene kleur en klik op OK.Sorteren op kleur, selecteer de kleur.

  5. Selecteer tweede kleur

    Klik linksboven op “Niveau toevoegen” waarbij een nieuwe sorteerregel verschijnt.
    Selecteer “Voornaam“, sorteren op “celkleur” en bij volgorde selecteren we nu geel.Sorteren op kleur, geel toevoegen.

  6. Selecteer derde kleur

    We herhalen stap 5 nogmaals en voegen nu de kleur rood toe. Je ziet nu 3 rijen zoals onderstaand voorbeeld. Klik nu op OK.Sorteren op kleur, rood toevoegen.

Zodra je op OK hebt geklikt ziet het resultaat er als volgt uit:

Sorteren op, het eindresultaat.

We hebben de kolom “Voornaam” geselecteerd waardoor de namen per kleur op voornaam van A naar Z zijn gesorteerd.

Als je bedenkt dat je toch een andere volgorde wilt gebruiken om te sorteren selecteer dan een willekeurige cel in je dataset en klik weer op sorteren. De popup “Sorteren” verschijnt.

Sorteren op kleur, volgorde aanpassen.

Selecteer de rij met de kleur die je wilt aanpassen en klik vervolgens op pijltje omhoog of omlaag in de werkbalk. je zult zien dat je de rij kunt verplaatsen. Als je een nieuwe volgorde hebt bepaald klik je weer op OK.

Sorteren op kleur, volgorde aangepast.

Volgende: Willekeurig sorteren in Excel

Aangepaste sorteervolgorde

0

Vorige: Sorteren meerdere kolommen

In Excel kun je data in een tabel of bereik in een aangepaste volgorde sorteren. In onderstand voorbeeld gaan we de data sorteren op cijfer (hoog, normaal en laag).

Benodigde tijd: 1 minuut

  1. Aangepaste sorteervolgorde in Excel

    Klik op een willekeurige cel in je tabel tabel of bereik.
    Aangepaste sorteervolgorde in Excel

  2. Gegevens -> Sorteren en filteren

    Ga in het lint naar het tabblad “Gegevens” en klik op “Sorteren”.
    Knop sorteren in Excel

  3. Popup sorteren

    Er verschijnt een popup “Sorteren”.
    Selecteer “Cijfer” in de keuzelijst onder kolom.Sorteren op cijfer

  4. Aangepaste lijst selecteren

    Selecteer “Aangepaste lijst” onder “Volgorde”.Selecteer aangepaste lijst bij volgorde

  5. Aangepaste lijst maken in Excel

    Voer in de “Gegevens in lijst” de lijst in op volgorde van hoog naar laag.
    Klik daarna op OK.Sorteren aangepaste lijst Excel

  6. Sorteer volgorde

    De sorteervolgorde staat nu op hoog naar laag.
    Klik op OK.Sorteren in Excel klik op OK

Het resultaat is dat de lijst nu aangepast is gesorteerd op basis van het cijfer van Hoog naar Laag.

Lijst gesorteerd op aangepaste sorteervolgorde.

Volgende: Data sorteren op kleur

Sorteren meerdere kolommen

0

Vorige: Sorteren in Excel

In het vorige artikel hebben hebben we een set met data aangepast gesorteerd op Cijfer. Het eindresultaat is een sortering op van “Hoog” naar “Laag“.

Lijst gesorteerd op aangepaste sorteervolgorde.

Het is mogelijk om een nog een niveau toe te voegen om te sorteren. In kolom C staan de scores namelijk niet op volgorde.

Benodigde tijd: 1 minuut

Sorteren op extra niveau

  1. Selecteer een cel

    Klik op een willekeurige cel in je tabel tabel of bereik.Klik op een willekeurige cel in je tabel tabel of bereik.

  2. Gegevens -> Sorteren en filteren

    Ga in het lint naar het tabblad “Gegevens” en klik op “Sorteren”.Knop sorteren in Excel

  3. Popup sorteren

    Er verschijnt een popup “Sorteren”.
    Klik op “Niveau toevoegen
    Sorteren- Niveau toevoegen

  4. Vervolgens sorteren op

    Er verschijnt een extra regel “Vervolgens op”.
    Selecteer hier “Score” (1) en zet de volgorde op “van groot naar klein” (2) en klik op OK.Excel vervolgens sorteren op

Nu is de dataset eerst gesorteerd op Cijfer op de waarden Hoog, Normaal, Laag en vervolgens op de Score van groot naar klein:

Data gesorteerd op meerdere niveau's in Excel.
Data gesorteerd op meerdere niveaus in Excel.

Volgende: Aangepaste sorteervolgorde in Excel

Sorteren in Excel

0

Je kunt je Excel data sorteren op één of meerder kolommen. Daarnaast kun je kiezen om van A naar Z, Z naar A, van klein naar groot, van groot naar klein of op basis van een aangepaste lijst te sorteren.

We gaan stap voor enkele voorbeelden uitleggen:

  1. Sorteren in Excel
  2. Sorteren meerdere kolommen
  3. Aangepaste sorteervolgorde
  4. Data sorteren op kleur
  5. Willekeurig sorteren

Benodigde tijd: 1 minuut

Sorteren op 1 kolom

  1. Selecteer data

    Klik op een cel in de kolom waarop je wilt sorteren.
    In dit voorbeeld gaan we sorteren op “Voornaam”.

    Sorteren op voornaam

  2. Sorteren van A naar Z

    Klik in het lint op het tabblad “Gegevens” en klik “A-Z” in het menu “Sorteren en Filteren“.

    Sorteren van A naar Z

Het resultaat is je dataset gesorteerd op voornaam van A naar Z.

Resultaat sorteren van A naar Z

Wil je van “Z naar A” sorteren ga je in het lint naar het tabblad “Gegevens” en klik je op “Z-A” in het menu “Sorteren en Filteren“.

Volgende: Sorteren meerdere kolommen

X.ZOEKEN overeenkomst bij benadering

X.ZOEKEN overeenkomst bij benadering
X.ZOEKEN overeenkomst bij benadering

Om X.ZOEKEN te gebruiken om een resultaat bij benadering te vinden geef je expliciet een overeenkomstmodus op in het vierde argument van de formule. In het getoonde voorbeeld is de formule in F5 gekopieerd naar beneden in de tabel. De formule in F5 is:

=X.ZOEKEN(E5;B5:B14;C5:C14;;-1)

Basis formule

=X.ZOEKEN(zoekwaarde;zoeken-matrix; matrix_retourneren; ; -1;

Uitleg X.ZOEKEN functie

In het voorbeeld bevat de tabel in B4:C14 de basis kortingen gebaseerd op afname. Hoe meer afname hoe meer korting er wordt gegeven. De tabel in E4:F11 toont de korting die wordt gegeven bij afname van het aantal in kolom E. Met de X.ZOEKEN functie zoeken we in de staffel in tabel B4:C14 naar de juiste korting. De formule in cel F5 die naar beneden is gekopieerd is:

=X.ZOEKEN(E5;B5:B14;C5:C14;;-1)
  • De Zoekwaarde komt uit de cel E5.
  • De Zoeken-matrix is het bereik B5:B14, hierin staat het aantal.
  • De matrix_retourneren is het bereik C5:C14, dit bereik bevat de korting.
  • Het argument indien_niet_gevonden is niet opgegeven.
  • De overeenkomstmodus is ingesteld op -1 (Exacte overeenkomst of volgende kleiner).
  • De zoekmodus is niet ingevuld en is standaard ingesteld op 1 (eerste tot laatste).

Bij elke rij zoekt X.ZOEKEN het aantal op in kolom E in het bereik B5:B14. Wanneer er een exacte overeenkomst is gevonden, wordt de bijbehorende korting uit kolom C geretourneerd. Als er geen exacte overeenkomst wordt gevonden, wordt de korting die is gekoppeld aan de volgende kleinste hoeveelheid geretourneerd.

X.ZOEKEN versus VERT.ZOEKEN

Je kunt hetzelfde resultaat verkrijgen met de VERT.ZOEKEN functie.

=VERT.ZOEKEN(E5;B5:C14;2)    # Standaard bij benadering
=VERT.ZOEKEN(E5;B5:C14;2;1)  # Expliciet bij benadering

Er zijn een paar opvallende verschillen die X.ZOEKEN flexibeler en voorspelbaarder maken:

  • VERT.ZOEKEN vereist de volledige tabelmatrix als tweede argument. X.ZOEKEN vereist alleen het bereik met opzoekwaarden.
  • VERT.ZOEKEN vereist een kolomindex_getal om een ​​resultaatkolom op te geven. X.ZOEKEN vereist een bereik.
  • VERT.ZOEKEN voert standaard een benaderende overeenkomst uit. Dit gedrag kan ernstige problemen veroorzaken. X.ZOEKEN voert standaard een exacte match uit.

Meer over de basis functionaliteiten vind je in X.ZOEKEN functie in Excel en nog meer voorbeelden in 5 voorbeelden X.ZOEKEN functie in Excel.

3 manieren om willekeurige getallen te genereren in excel

0

In excel is het mogelijk om willekeurige getallen lijstjes te maken. Hiervoor volgen 3 functies uit de categorie Wiskundige en trigonometrische met alle drie hun eigen eigenschappen.

  • ASELECT()
  • ASELECTTUSSEN()
  • ASELECT.MATRIX()

Soms heb je een setje willekeurige getallen nodig. Bijvoorbeeld voor een kansberekening, een test of zomaar wat data. Je kunt ze natuurlijk zelf bedenken of door Excel laten genereren.

Willekeurige getallen met ASELECT

De ASELECT functie geeft als resultaat een gelijkmatig verdeeld willekeurig reëel getal, dat groter is dan of gelijk is aan 0 en kleiner dan 1.

De functie heeft geen argumenten en kan daarom met met haakjes worden geschreven zonder argumenten:

=ASELECT()

In onderstand voorbeeld zie je in cel C5 de formule =ASELECT(). Het resultaat is 0,638359353. je kunt de cel eigenschap aanpassen naar getal tot wel 15 decimalen om een de exacte waarde te zien.

Willekeurige getallen met ASELECT() functie in Excel
Voorbeeld ASELECT() functie in Excel

Je kunt me door middel van kleine aanpassingen nog wat andere waarden tevoorschijn toveren. Door heel simpel de formule te vermenigvuldigen met 100 krijg je een getal tussen 0 en 100. de formule in C6 is:

=ASELECT()*100

Het resultaat in dit voorbeeld is 35,13575271.

Wil je een integer getal als resultaat dan gebruik je de INTEGER functie. Je krijgt dan altijd een geheel getal als resultaat. de formule in cel C7 is:

=INTEGER(ASELECT()*100)

Echter, om een geheel getal als waarde te krijgen kun je misschien beter de volgende functie gebruiken.

Willekeurige getallen met ASELECTTUSSEN

De ASELECTTUSSEN functie in Excel geeft als resultaat een willekeurig integer getal tussen het laagste en hoogste opgegeven getal in de argumenten. De syntaxis voor de functie is:

=ASELECTTUSSEN(Laagst; Hoogst)

In het voorbeeld hieronder staat in de cel C5 de formule:

=ASELECTTUSSEN(0;20)

Met deze formule vragen we een integer getal tussen de 0 en de 20. Het resultaat is in het voorbeeld 4.

Willekeurige getallen met ASELECTTUSSEN() functie in Excel
Voorbeeld ASELECTTUSSEN() functie in Excel

De functie ASELECTTUSSEN kan ook omgaan met negatieve getallen. In cel C7 staat de formule:

=ASELECTTUSSEN(-20;1)

Het resultaat is een negatief getal, namelijk -7.

Willekeurige getallen ASELECT.MATRIX

De ASELECT.MATRIX formule retourneert een matrix van willekeurige getallen. Je kunt zelf opgeven hoeveel rijen en kolommen er moeten worden gevuld, wat de minimale en maximale waarden zijn en of je hele getallen of decimalen geretourneerd wilt hebben.

De basis syntaxis is als volgt:

=ASELECT.MATRIX([rijen];[kolommen];[min];[max];[gehele_getallen])

Alle argumenten staan tussen blokhaken wat betekent dat ze allemaal optioneel zijn.

Rijen – [optioneel] Het aantal rijen om te retourneren
Kolommen – [optioneel] Het aantal kolommen om te retourneren
Min – [optioneel] De minimale waarde die moet worden geretourneerd
Max – [optioneel] De maximale waarde die moet worden geretourneerd
Geheel – [optioneel] Gehele of decimale getallen retourneren. Geef WAAR (of 1) op voor gehele getalen. Geef ONWAAR (of 0) op voor decimale getallen.

In het eerste voorbeeld hebben we een matrix gemaakt die 4 rijen hoog is en 3 kolommen breed. de formule retourneert een verzameling getallen tussen 0,01 en 100 met GEHEEL als ONWAAR wat betekent dat het resultaat met decimalen is.

Willekeurige getallen met ASELECT.MATRIX() functie in Excel
Voorbeeld ASELECT.MATRIX() functie in Excel

In het tweede voorbeeld hebben we een matrix gemaakt die 4 rijen hoog is en 3 kolommen breed. de formule retourneert een verzameling getallen tussen 1 en 100 met GEHEEL als WAAR wat betekent dat het resultaat met integere getallen is.

Let op! Wanneer een werkblad opnieuw wordt berekend door een formule, gegevens in een andere cel worden ingevoegd of door handmatig te herberekenen (F9),wordt een nieuw willekeurig getal gegenereerd voor de formule.
Wil je geen nieuwe getallen dan kun je herberekening uitzetten in het lint bij “Formule -> Berekeningsopties -> handmatig” te selecteren.

Opmerkingen ASELECT.MATRIX functie

  • Als je geen rij- of kolomargument invoert, zal ASELECT.MATRIX een enkele waarde tussen 0 en 1 retourneren.
  • Als je geen minimum- of maximumwaarde als argument opgeeft, gebruikt de functie ASELECT.MATRIX respectievelijk de waarden 0 en 1.
  • De minimumwaarde moet lager zijn dan de maximumwaarde, anders retourneert de functie ASELECT.MATRIX de #WAARDE! -fout.
  • Als je het argument geheel niet opgeeft, is de standaardwaarde ONWAAR. De functie retourneert dan decimale getallen.
  • De functie ASELECT.MATRIX geeft een matrix als resultaat, en deze zal aangrenzende cellen beïnvloeden als het een uiteindelijk resultaat van een formule is. Dit betekent dat Excel dynamisch de juiste grootte van het bereik van de matrix creëert als je drukt op ENTER. Wanneer je gestructureerde verwijzingen gebruikt en als jouw ondersteunende gegevens zich in een Excel-tabel bevinden, wordt de grootte van de matrix automatisch aangepast als je gegevens toevoegt of uit het matrixbereik verwijdert.
  • ASELECT.MATRIX verschilt van de functie ASELECT, omdat ASELECT geen matrix retourneert. Daarom moet ASELECT naar het volledige bereik worden gekopieerd.
  • Een matrix kan worden gezien als een rij met waarden, een kolom met waarden, of een combinatie van rijen en kolommen met waarden. In het bovenstaande voorbeeld is de matrix voor onze formule ASELECT.MATRIX het bereik C13:E16, oftewel 4 rijen hoog en 3 kolommen breed.

X.ZOEKEN eenvoudige exacte overeenkomst

X.ZOEKEN eenvoudige exacte overeenkomst
X.ZOEKEN eenvoudige exacte overeenkomst

Als je met X.ZOEKEN een exacte overeenkomst wilt vinden, moet je een Zoekwaarde, een Zoeken-matrix en een Matrix_retourneren opgeven. In het voorbeeld is de formule in cel H5:

=X.ZOEKEN(H4;C5:C20;E5:E20)

De uitkomst van de formule is 23.600.00, de populatie voor Mumbai uit kolom E.

Basis formule

=X.ZOEKEN(zoekwaarde; zoeken-matrix; matrix_retourneren)

Uitleg X.ZOEKEN

In het voorbeeld bevat cel H5 de zoekwaarde “Mumbai”. X.ZOEKEN is geconfigureerd om deze waarde in de tabel te vinden en de populatie te retourneren. De formule in H5 is:

=X.ZOEKEN(H4;C5:C20;E5:E20) // Haal de popuplatie op
  • De zoekwaarde komt uit cel H4 (Mumbai)
  • De zoeken-matrix is het bereik C5:C20, hierin staan de plaatsnamen.
  • De matrix_retourneren is E5:E20, dit bereik bevat de populatie
  • De overeenkomstmodus is niet ingevuld en is standaard dus 0 (exacte overeenkomst)
  • De zoekmodus is niet ingevuld en is standaard ingesteld op 1 (eerste tot laatste)

Om de stad re retourneren in plaats van de populatie veranderen we alleen de matrix_retourneren van E5:E20 naar D5:D20. De formule in H5 is:

=X.ZOEKEN(H4;C5:C20;D5:D20)

X.ZOEKEN versus VERT.ZOEKEN

Je kunt hetzelfde resultaat verkrijgen met de VERT.ZOEKEN functie.

=VERT.ZOEKEN(H4;C5:E20;3)

Er zijn een paar opvallende verschillen die X.ZOEKEN flexibeler en voorspelbaarder maken:

  • VERT.ZOEKEN vereist de volledige tabelmatrix als tweede argument. X.ZOEKEN vereist alleen het bereik met opzoekwaarden.
  • VERT.ZOEKEN vereist een kolomindex_getal om een ​​resultaatkolom op te geven. X.ZOEKEN vereist een bereik.
  • VERT.ZOEKEN voert standaard een benaderende overeenkomst uit. Dit gedrag kan ernstige problemen veroorzaken. X.ZOEKEN voert standaard een exacte match uit.

Meer over de basis functionaliteiten vind je in X.ZOEKEN functie in Excel en nog meer voorbeelden in 5 voorbeelden X.ZOEKEN functie in Excel.

5 voorbeelden X.ZOEKEN functie in Excel

0
Excel X.ZOEKEN functie
Excel X.ZOEKEN functie

De Excel X.ZOEKEN functie is een moderne en flexibele vervanging voor oudere functies zoals VERT.ZOEKEN, HORIZ.ZOEKEN en ZOEKEN. X.ZOEKEN ondersteunt benaderende en exacte overeenkomsten en jokertekens (* ?) voor gedeeltelijke overeenkomsten en zoekopdrachten in verticale of horizontale reeksen.

Hieronder gaan we met een aantal voorbeelden de functie uitleggen. De basis uitleg van de functie vindt je op de pagina X.ZOEKEN functie in Excel.

Voorbeeld #1 – eenvoudige exacte overeenkomst

X.ZOEKEN zoekt altijd een naar een exacte overeenkomst tenzij anders is aangegeven. In het onderstaande voorbeeld wordt X.ZOEKEN gebruikt om de Prijs op te halen op basis van een exacte overeenkomst op Fruit. De formule in G5 is:

=X.ZOEKEN(F5;B5:B12;D5:D12)
Excel X.ZOEKEN voorbeeld #1
Excel X.ZOEKEN voorbeeld #1 – eenvoudige exacte overeenkomst

Voor meer uitleg lees: X.ZOEKEN eenvoudige exacte overeenkomst

Voorbeeld #2 – overeenkomst bij benadering

Om een ​​overeenkomst bij benadering mogelijk te maken, geef je een waarde op voor het argument overeenkomstmodus. In het onderstaande voorbeeld wordt X.ZOEKEN gebruikt om een ​​korting te berekenen op basis van de hoeveelheid, waarvoor een benaderende overeenkomst vereist is. In de formule in F5 geven we -1 voor overeenkomstmodus om een benaderende overeenkomst mogelijk te maken met een “exacte overeenkomst of op één na kleinste” gedrag. De formule in F5 is:

=X.ZOEKEN(E5;B5:B10;C5:C10;;-1)
Excel X.ZOEKEN voorbeeld #2
Excel X.ZOEKEN voorbeeld #2 – overeenkomst bij benadering

Voor meer uitleg lees: X.ZOEKEN overeenkomst bij benadering.

Voorbeeld #3 – meerdere waarden

X.ZOEKEN kan meer dan één waarde tegelijk retourneren voor dezelfde overeenkomst. Het onderstaande voorbeeld laat zien hoe X.ZOEKEN kan worden geconfigureerd om drie overeenkomende waarden te retourneren met een enkele formule. De formule in H5 is:

=X.ZOEKEN(G5;B5:B12;C5:E12)
Excel X.ZOEKEN voorbeeld #3
Excel X.ZOEKEN voorbeeld #3 – meerdere waarden

In dit voorbeeld bevat de zoeken-matrix drie kolommen (B12;C5): Aantal, Herkomst en Prijs. Alle 3 de waarden lopen over in de kolommen H5:J5.

Voorbeeld #4 – zoeken in twee richtingen

De X.ZOEKEN functie kun je ook gebruiken om op basis van twee waarden te zoeken in een bereik of tabel. De functie kan genest worden, wat wil zeggen, je kunt de functie in 1 formule twee keer gebruiken. In het onderstaande voorbeeld haalt de “binnenste” X.ZOEKEN een hele rij op (alle waarden voor Citroen), die wordt doorgegeven aan de “buitenste” X.ZOEKEN als de matrix_retourneren. De buitenste X.ZOEKEN vindt de juiste groep (B) en retourneert de overeenkomstige waarde (11,74) als het uiteindelijke resultaat.

=X.ZOEKEN(I5;C4:F4;X.ZOEKEN(I4;B5:B12;C5:F12))
Excel X.ZOEKEN voorbeeld #4
Excel X.ZOEKEN voorbeeld #4 – zoeken in twee richtingen

Voorbeeld #5 – indien_niet_gevonden bericht

Standaard geeft de X.ZOEKEN een waarde #N/A als er geen waarde is gevonden. Om een vervangende tekst te retourneren geef je een waarde op voor het optionele argument indien_niet_gevonden. Plaats de vervangende tekst tussen dubbele aanhalingstekens (“”). Als je bijvoorbeeld “Niet gevonden” wilt weergeven als het opgegeven fruit niet is gevonden gebruik je de volgende formule:

=X.ZOEKEN(F5;B5:B12;D5:D12;"Niet gevonden")
Excel X.ZOEKEN voorbeeld #5
Excel X.ZOEKEN voorbeeld #5 – indien_niet_gevonden bericht

De tekst “Niet gevonden” kan geheel naar eigen wens worden aangepast naar bijvoorbeeld “Geen overeenkomst”, “Fruit niet gevonden” etc.

Je kunt er ook voor kiezen om alleen dubbele aanhalingstekens te gebruiken zonder een vervangende tekst (“”). Dit geeft als resultaat een lege cel in plaats van de foutcode #N/A. het gevaar van deze weergave is wel dat de cel leegt is, ook als er een foutwaarde #N/A is.

Voordelen X.ZOEKEN functie

X.ZOEKEN biedt verschillende belangrijke voordelen, vooral in vergelijking met VERT.ZOEKEN:

  • X.ZOEKEN kan gegevens rechts of links van opzoekwaarden opzoeken.
  • X.ZOEKEN kan meerdere resultaten opleveren.
  • X.ZOEKEN is standaard ingesteld op een exacte overeenkomst (VERT.ZOEKEN is standaard ingesteld op benaderend).
  • X.ZOEKEN kan werken met verticale en horizontale gegevens.
  • X.ZOEKEN kan omgekeerd zoeken (laatste naar eerste)
  • X.ZOEKEN kan volledige rijen of kolommen retourneren, niet slechts één waarde.
  • X.ZOEKEN kan standaard met arrays werken  om complexe criteria toe te passen.

Opmerkingen X.ZOEKEN functie

  • X.ZOEKEN kan werken met zowel verticale als horizontale arrays.
  • X.ZOEKEN retourneert #N/A als de opzoekwaarde niet wordt gevonden.
  • De zoeken-matrix moet een dimensie hebben die compatibel is met het argument matrix_retourneren, anders retourneert X.ZOEKEN #WAARDE!
  • Als X.ZOEKEN wordt gebruikt tussen werkmappen, moeten beide werkmappen geopend zijn, anders retourneert X.ZOEKEN #REF!.
  • Net als de INDEX() functie retourneert X.ZOEKEN een verwijzing als resultaat.

De Excel X.ZOEKEN functie kun je op vele manieren toepassen. Voor de basis uitleg van de functie kijk je op de pagina X.ZOEKEN functie in Excel.