Hnorizontaal e verticaal zoeken combineren.

In Cursus Excel 2007 Les 37 zien we de werking van de functies HORIZ.ZOEKEN en VERT.ZOEKEN.

We willen echter  horizontaal én verticaal zoeken.
Typ in cel C5 jan en druk Enter.
Selecteer terug cel C5 en sleep met de vulgreep naar rechts tot en met cel N5.
Excel vult nu automatisch de namen in van de maanden van het jaar.
Selecteer de cellen C5 tot en met N5 en geef dit bereik een naam bv. Maanden (Zie cursus Excel 2007 les 12: Het Bereik een naam geven).

Typ in de cellen B6 tot en met B12 enkele namen, bv. de namen van verkopers.
Geef dit bereik ook een naam, bv. Verkopers.
In de cellen C6 tot en met N12 vul je wat bedragen in en vervolgens geef je dit bereik ook een naam, bv. Bedragen.
We gaan nu twee “Drop-downlijsten” of “Keuzelijsten” maken.
Selecteer cel D2, kies het tabblad “Gegevens” in het lint, en klik de knop “Gegevensvalidatie”.
Op het tabblad “Instellingen”(1) kies je bij “Toestaan” voor “Lijst”(2) en bij “Bron”(3) typ je =Verkopers

Herhaal dit voor cel D3 en  geef als naam bij “Bron:” =Maanden.

We gaan de functies =INDEX en =VERGELIJKEN gebruiken om gegevens uit het bereik “Bedragen” op te vragen.
De functie = INDEX kent twee varianten nl. de verwijzingsvariant en de matrixvariant, we gaan in dit voorbeeld de verwijzingsvariant gebruiken.
=INDEX(verwijzing; rij_getal; [kolom_getal]; [bereik_getal]) geeft als resultaat de verwijzing naar de cel op het snijpunt van een bepaalde rij en kolom.
De eerste parameter “verwijzing”  ( is verplicht) verwijst  naar één of meer cellenbereiken (in ons voorbeeld is het celbereik “Bedragen”).
De tweede parameter “rij getal” (is verplicht)  geeft het nummer van de rij in de verwijzing  waaruit een waarde moet worden opgehaald.
De derde parameter “kolom getal” (is optioneel) geeft  het nummer van de kolom in de verwijzing waaruit een waarde moet worden opgehaald.
De vierde parameter “bereik getal” (is optioneel) selecteert een bereik in de verwijzing waaruit het snijpunt van rij_getal en kolom_getal moet worden opgehaald.
Willen we bijvoorbeeld weten hoeveel Peter (rij 6) verkocht heeft in maart (kolom 3) dan kunnen we  in de cel H2 de formule =INDEX(Bedragen;6;3) ingeven (we gebruiken de parameters 1,2 en 3 in dit voorbeeld) en Enter drukken en Excel zal ons dan als resultaat(1) de celinhoud geven van het snijpunt dat zich bevind op rij 6 (2) en kolom 3 (3) van het gegevensveld Bedragen.

We gaan echter om het snijpunt te bepalen de functie =VERGELIJKEN gebruiken als tweede parameter (rij) en derde parameter (kolom).
De functie =VERGELIJKEN(zoekwaarde; zoeken_matrix; [criteriumtype_getal]) heeft 3 parameters, nl zoekwaarde = wat zoeken we
zoeken matrix = waar zoeken we
criteriumtype_getal (optioneel) 1, 0 of -1
Wanneer we geen getal of 1 opgeven als criteria zoekt VERGELIJKEN naar de grootste waarde die kleiner is dan of gelijk is aan zoekwaarde. De waarden in het argument zoeken-matrix moeten in oplopende volgorde zijn gesorteerd, bijvoorbeeld …-2, -1, 0, 1, 2, …, A-Z; ONWAAR;
Wanneer we  een 0 ingeven zoekt VERGELIJKEN  naar de eerste waarde die exact gelijk is aan zoekwaarde. De waarden in het argument zoeken-matrix mogen in willekeurige volgorde zijn gesorteerd.
Wanneer we  -1 ingeven zoekt VERGELIJKEN  naar de kleinste waarde die groter is dan of gelijk is aan zoekwaarde. De waarden in het argument zoeken-matrix moeten in aflopende volgorde zijn gesorteerd, bijvoorbeeld WAAR, ONWAAR, Z-A, …2, 1, 0, -1, -2,

De formule om te bepalen waar de waarde van cel D2 in het gegevensveld “Verkopers” (rij) zich bevindt is=VERGELIJKEN(D2;Verkopers;0)  we geven als derde parameter een 0 want we willen immers exact zoeken en de argumenten (namen van verkopers) zijn in willekeurige volgorde  gesorteerd.
De formule om te bepalen waar de waarde van cel D3 in het gegevensveld “Maanden” (kolom) zich bevindt is =VERGELIJKEN(D3;Maanden;0)

De samengestelde formule is:=INDEX(Bedragen;VERGELIJKEN(D2;Verkopers;0);VERGELIJKEN(D3;Maanden;0))

We kunnen in de keuzemenu’s de verkoper en of de maand wijzigen en onze formule past het resultaat aan.

Ter informatie: Swotster.com denkt aan zijn cursisten, denk ook aan Swotster.com

Cursussen

• Downloading iPad Apps
• Lesson 1: Introduction to Publisher
• Lesson 65: File Formats