Tagarchief: avz

Verder zoeken 2



Er zijn op deze site al heel wat artikelen verschenen over het zoeken in Excel. Een artikel in november 2017 heeft de naam Verder-zoeken en gaat over het zoeken in niet-unieke items.

Onlangs kreeg ik een vraag van Dean, die in het verlengde van dat artikel ligt: om de rapportvoorbereidingen op een school beter te kunnen doen, zou het handig zijn om per leerling niet alleen gemiddeldes over onderdelen uit te rekenen, maar ook de maximum behaalde score en te zien voor welk onderdeel dit maximum is behaald. Dit laatste leverde hoofdbrekens op: uiteraard kan dat maximum bij meer dan één onderdeel voorkomen.
Met behulp van enkele hulpkolommen kwamen we snel tot een werkbare oplossing.

Maar: het zou natuurlijk veel mooier zijn, als dit met behulp van een Excel-functie zou kunnen. Helaas deze bestaat (nog) niet, dus tijd voor het bouwen van een Eigen functie in Visual Basic.

NB in de nieuwste versies van Excel bestaat de functie X.ZOEKEN. Ook al is deze functie veel flexibeler dan het bestaande VERT.ZOEKEN, maar ook deze functie levert altijd slechts maar één resultaat op.

Probleem

Dean had een mooie werkmap in elkaar gezet, waarbij uiteindelijk op een tabblad een cijfer-overzicht werd gegenereerd.

In het overzicht op het tabblad Rapport van het Voorbeeldbestand heb ik wat Voorwaardelijke opmaak toegevoegd om snel inzicht te krijgen in de spreiding van de resultaten.
Het bepalen van het gemiddelde per leerling of onderdeel leverde geen probleem op; ook het bepalen van de hoogste score niet. Maar wel: bij welk onderdeel (of leerling) komt dit maximum voor?

Oplossing 1

Dean was er al snel achter gekomen dat hij met de standaardfuncties HOR.ZOEKEN en VERT.ZOEKEN niet veel verder kwam. Maar zoeken op www.ginfo.nl leverde het begin van een oplossing in de vorm van de combinatie Index-Vergelijken.

In cel J3 staat de formule:
=INDEX($C$2:$G$2;VERGELIJKEN(I3;C3:G3;0))
De functie Index levert die waarde uit het bereik C2:G2, waarvan het volgnummer gelijk is aan het resultaat van de functie Vergelijken.
Deze laatste functie ‘kijkt’ op welke plaats de waarde uit cel I3 in het bereik C3:G3 staat; de derde parameter (0, nul) geeft aan dat er een exacte vergelijking moet worden uitgevoerd.

NB1 op een vergelijkbare manier wordt in cel C15 bekeken welke leerling de hoogste score op het eerste onderdeel heeft gehaald.

NB2 moeite om te onthouden hoe de combinatie van deze functies moet worden ingevoerd? Gebruik de avz-truc uit het artikel Zoeken: Index en vergelijken.

Helaas bij leerling 9 gaat dat fout: de maximale score 7,7 komt 2 keer voor maar de formule laat alleen Item 2 zien.

Oplossing 2

Gelukkig: de functie ZoekWaarde levert wel het gewenste resultaat!
Maar … deze functie is niet terug te vinden in de gereedschapskist van Excel. Dit is een zogenaamde Eigen functie (in het Engels UDF, User Defined Function). Hoe je deze in je spreadsheets kunt implementeren, zal ik hierna uitleggen.

De functie ziet er als volgt uit:

Deze functie kent 3 parameters (Waarde, Waarzoeken en Resultaat); de functie kijkt waar de Waarde in het bereik Waarzoeken voorkomt en levert de overeenkomende waarde uit het bereik Resultaat. Komt de Waarde vaker dan één keer voor dan worden de resultaten gescheiden door een /.

De functie kan op dezelfde manier in een Excel-cel ingevoerd worden als iedere andere (standaard-)functie. Weet je niet welke parameters er in welke volgorde nodig zijn, ga dan als volgt te werk:

  1. plaats de cursor in de cel waar de formule moet komen
  2. tik in =zoekwaarde(
    Of tik het eerste gedeelte in en klik dubbel op de functie in het lijstje dat automatisch tevoorschijn komt:
  3. klik op denet voor de formulebalk
  4. vul in de pop-up de parameters (argumenten) in:

NB voor Eigen functies is geen Help beschikbaar; zorg dat de namen van de parameters aangeven wat de bedoeling is (dus niet Bereik1, Bereik2 of iets dergelijks)

In het tabblad Rapport van het Voorbeeldbestand is deze functie gebruikt in kolom K en rij 16 met het gewenste resultaat.

NB zoals uit de formules in kolom L en rij 17 blijkt, is het voor deze functie niet nodig dat het hele resultaat-bereik wordt opgegeven; de begincel is voldoende.

In het tabblad Rap2 van het Voorbeeldbestand is te zien, dat wanneer de rapportcijfers gehele getallen zijn, het probleem van meervoudige resultaten nog veel vaker zal voorkomen.

NB de functie kent geen enkele fout-detectie; in de praktijk hoeft dit bij het (met verstand) toepassen geen probleem te zijn. In regel 19 staan de resultaten van ‘vreemd’ gebruik van de functie.

Oplossing 3

De hiervoor geschetste oplossing lijkt veel op de Index-Vergelijken-oplossing met dat verschil, dat meervoudige resultaten mogelijk zijn.
Om ook het probleem van foutdetectie aan te pakken (en de functie wat flexibeler te maken) bevat het Voorbeeldbestand nog een andere Eigen functie; deze heeft 4 parameters waarvan de laatste optioneel is (hoeft dus niet ingevoerd te worden; in dat geval zal Excel bij deze functie “/” gebruiken): IndexVergelijken(Result_matrix, Zoekwaarde, Zoeken_matrix, Optional Scheiding As String = “/”)

Na de declaratie van de functie (Public is de standaardinstelling; maak je de functie Private dan zul je bij het invoeren niet meer uit de lijst kunnen kiezen en is het niet mogelijk om het Functieargumenten-scherm op te roepen) staan diverse commentaar-regels, die uitleg geven over de functie en de randvoorwaarden.
Daarna volgen 3 regels waarin variabelen, die binnen de functie gebruikt worden, worden gedeclareerd (Boolean variabelen kunnen alleen de waarden False en True bevatten; variabelen van het type Long kunnen grote gehele getallen weergeven).
In het volgende gedeelte worden diverse testen uitgevoerd op de ingevoerde parameters. Als een test de waarde Waar/True oplevert, wordt een foutmelding als resultaat van de functie doorgegeven en stopt de verdere verwerking (Exit Function).

In het 2e gedeelte vindt de daadwerkelijke verwerking van de parameters plaats.
Eerste krijgen de 2 booleans een waarde.
Dan lopen we met 2 geneste For-Nextloops door alle cellen van de rijen en kolommen van Zoeken_matrix (door bovenstaande controles kan het maar 1 kolom of 1 rij zijn).
Als de Zoekwaarde in een cel voorkomt wordt de overeenkomende waarde uit de Result-matrix aan het resultaat van de functie toegevoegd, inclusief de standaard- of de in de 4e parameter opgegeven scheidingstekst. Als het zoek- en resultaat-gebied gelijk zijn wordt de betreffende rij of kolom als resultaat doorgegeven.
Als na de For-Next-loops blijkt dat er minstens één resultaat is gevonden wordt de laatste scheidingstekst verwijderd (Len bepaalt de lengte van een tekst). Als er geen enkel resultaat is gevonden, wordt als resultaat van de eigen functie de foutcode xlErrNA teruggegeven; in de Nederlandstalige versie van Excel krijg je dan de standaard-foutmelding #N/B.

In het tabblad Rap3 van het Voorbeeldbestand staan diverse voorbeelden van het gebruik van deze eigen functie IndexVergelijken.

NB1 omdat deze eigen functie heel veel lijkt op het gebruik van de combinatie Index-Vergelijken heb ik ook de volgorde van de parameters aangehouden, zoals ze daarbij worden ingevoerd.
De werking van de functie verandert niet, als de eerste regel wordt gewijzigd in het meer logische
Public Function IndexVergelijken(Zoekwaarde, Zoeken_matrix, Result_matrix, Optional Scheiding As String = “/”)

NB2 optionele parameters moeten altijd achteraan komen.

Extra voorbeelden 1

Hiernaast staat een afdelingsoverzicht met bijbehorende persoon; aan iedere persoon is een waarde toegekend (zie de Excel-tabel tblAfdPers in het tabblad Ovz1 in het Voorbeeldbestand).

Wil je weten bij welke afdeling of persoon een bepaalde waarde hoort, dan kun je geen gebruik maken van VERT.ZOEKEN (deze functie kan niet ‘naar links kijken’).

De combinatie Index en Vergelijken ligt hier meer voor de hand. Deze formule voldoet als je zeker weet dat de waardes uniek zijn. In dit geval lijkt dat zo, maar …

Het is veiliger om de nieuwe functie IndexVergelijken() te gebruiken:

De waarde 21 komt bij 2 afdelingen voor, telkens bij de persoon P1.
In cel I10 is met deze functie een persoonsoverzicht gemaakt voor Afd3, terwijl in cel I13 te zien is in welke afdelingen P4 voor komt.

Extra voorbeelden 2

Op het tabblad Ovz2 van het Voorbeeldbestand staan voorbeelden van het zoeken in een draaitabel met de functie IndexVergelijken().

Cel D19 bevat het resultaat van de functie wanneer de zoek- en resultaat-matrix gelijk zijn.

Implementatie van een Eigen functie

Er zijn ruwweg 2 methodes: koppel de functie aan de werkmap waar je hem nodig hebt (net als in het Voorbeeldbestand) of zorg er voor, dat de functie in iedere werkmap beschikbaar is. De functie moet dan opgenomen worden in een zogenaamde persoonlijke macro-werkmap (PERSONAL.XLSB). Helaas kent deze methode nogal wat haken en ogen. We gaan daar hier niet verder op in. Misschien een idee voor een apart artikel?

Visual Basic Editor

In allebei de gevallen hebben we de Visual Basic Editor nodig. Er zijn verschillende mogelijkheden om die te openen:

  1. druk op de toetscombinatie Alt-F11
  2. klik rechts op één van de tabs van de werkbladen onderaan (in het Voorbeeldbestand bijvoorbeeld op de tab Rapport) en kies de optie Programmacode weergeven
  1. klik in de menutab Ontwikkelaars in het blok Programmacode op de optie Visual Basic
    NB staat de menutab Ontwikkelaars niet in het ‘lint’, klik dan rechts op één van de menutabs, kies de optie Het lint aanpassen en zorg dat in het rechterblok de optie Ontwikkelaars is aangevinkt.

In aparte werkmap opnemen
  1. maak of open het bestand waar de eigen functie gebruikt zal gaan worden (in dit voorbeeld Map1).
    Open ook het Voorbeeldbestand.
  2. open de Visual Basic Editor. Linksboven (tenminste in de standaardinstelling van VBA) staat de project-verkenner, waar alle geopende Excel-projecten/werkmappen weergegeven worden.
  3. wanneer het nieuwe project geen modules heeft (zoals hiernaast Map1), zorg dan dat dit project is geselecteerd en kies Invoegen/Module
  1. dubbel-klik op Module1 van het project Meervoudig zoeken. Selecteer de hele functie IndexVergelijken (vanaf Public tot en met End Function) en kopieer (bijvoorbeeld door Ctrl-C)
  2. dubbel-klik op Module1 van het project waar de functie gebruikt zal gaan worden (hier dus Map1) en plak de eigen functie (Ctrl-V)
  3. sluit VBA af via Alt-Q of Bestand/Sluiten en terugkeren
  4. de eigen functie is nu te gebruiken in de nieuwe werkmap.

LET OP wanneer het bestand bewaard wordt moet je er wel voor zorgen dat je deze opslaat als werkblad met macro’s (dus extensie xlsm) of als binair (met de extensie xlsb).


Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd:


Zoeken: Index en Vergelijken

vertzoekenOp ieder Excel-forum zie je regelmatig de opmerking, dat het gebruik van de standaard-functie VERT.ZOEKEN (verticaal zoeken) niet flexibel is en foutgevoelig (en dat geldt ook voor de horizontale variant). Meestal wordt er dan als alternatief verwezen naar het gebruik van INDEX, gecombineerd met VERGELIJKEN.
Ik heb daar al eens eerder een artikel aan gewijd (Alternatief voor verticaal zoeken).

In de praktijk zie ik, dat er toch vaak met VERT.ZOEKEN wordt gewerkt, zo nodig met behulp van allerlei hulpkolommen. Bijna altijd met het argument: “Ik weet niet meer hoe ik die 2 functies moet combineren.

Ik kwam pas een handige truc tegen, waardoor het invoeren van deze gecombineerde functies werkelijk een fluitje van een cent wordt.
Hierna zal ik deze tip toelichten, maar eerst laat ik (nogmaals) zien dat de combinatie van INDEX en VERGELIJKEN echt beter is dan VERT.ZOEKEN.

Voorbeeld van VERT.ZOEKEN

vertzoeken1Hiernaast zie je een simpel voorbeeld van een werknemersadministratie: naast een personeelsnummer staat de naam en de afdeling.

Wanneer we een personeelsnummer kennen zien we in één oogopslag welke persoon daar bij hoort. Zou het bestand veel groter zijn (en niet gesorteerd op nummer), dan kan het zoeken wat lastiger worden. Daar komen de Excel-zoekfuncties om de hoek kijken. In het Voorbeeldbestand is in het tabblad VZoeken het bestandje opgenomen.

vertzoeken2In cel F3 staat het personeelsnummer waarvan we de naam en afdeling willen weten. In G3 staat de formule
=VERT.ZOEKEN(F3;B3:D7;2;ONWAAR)
Ofwel: zoek de waarde van F3 op in de eerste kolom (VERTicaal) van het bereik B3:D7 en geef de corresponderende waarde uit de 2e kolom van dat bereik terug. De zoekwaarde moet exact overeenkomen vandaar dat bij de laatste parameter/argument van de functie (Benaderen) ONWAAR is ingevuld.

Wijzig de waarde in F3 en de corresponderende naam wordt opgehaald; net als de betreffende afdeling in cel H3, waar de gegevens uit de 3e kolom worden uitgelezen.

NB1 als het nummer niet in het bestand voorkomt zal de functie #N/B (niet beschikbaar) opleveren.

NB2 de Excel zoek-functie zoekt de waarde van het eerste argument altijd in de eerste kolom van het bereik, dat opgegeven is in het tweede argument. Vandaar dat je niet ‘naar links’ kunt zoeken. Dus als je een naam weet kun je op deze manier niet het nummer opzoeken.

NB3 voeg tussen de kolommen C en D een nieuwe kolom in en kijk wat er met het resultaat van het zoeken gebeurt!

NB4 voeg een nieuwe werknemer onderaan toe, plaats het personeelsnummer in cel F3. Wat gebeurt er?

Index en Vergelijken (deel 1)

indverg1In het tabblad IndVerg van het Voorbeeldbestand is gebruik gemaakt van INDEX en VERGELIJKEN.

Hoe werkt dit?

  1. allereerst moet de zoekwaarde (in dit geval cel F3) vergeleken worden met de kolom met personeelsnummers (B3:B7).  Het resultaat is de positie van de zoekwaarde in het bereik (in dit geval 1; zie cel J3).
    Met de derde parameter van de functie VERGELIJKEN kunnen we aangeven welk soort resultaat er moet worden opgeleverd; met 0 geven we aan, dat er een exacte match moet zijn.
  2. de zo gevonden positie wordt gebruikt in INDEX om een resultaat op te halen in de kolom met namen (C3:C7).

Op deze manier is het probleem van de NB3 van hierboven in ieder geval opgelost.

indverg2Ook de NB2 kunnen we hiermee omzeilen: in cel G7 zoeken we het nummer op in B3:B7, dat hoort bij de naam  in cel F7 door die waarde te vergelijken met de inhoud van C3:C7.

NB5 de functie VERGELIJKEN levert altijd maar 1 resultaat terug; de eerste die aan de voorwaarde voldoet. Een oplossing hiervoor wordt verderop uitgewerkt.

Index en Vergelijken (deel 2)

Ook de NB3 moeten we nog oplossen: als er werknemers bij komen moeten de formules blijven werken.

indverg3Het eerste wat we doen is van het personeelsbestand een tabel maken:

  1. klik op één van de cellen in het bestand
  2. kies in de menutab Invoegen de optie Tabel; zorg dat het vinkje aan staat bij De tabel bevat kopteksten.
  3. de tabel wordt automatisch van een ‘zebra’-opmaak voorzien. indverg4In het Voorbeeldbestand heeft de tabel op het tabblad IndVerg2 de naam tblWerknemers gekregen (klik op Ontwerpen in de nieuwe menutab Hulpmiddelen voor tabellen en wijzig de standaardnaam).

indverg5In cel G3 gaan we opnieuw de alternatieve opzoek-formule maken om de naam bij een nummer te vinden:

  1. tik in: =index(
  2. maar in plaats van het intikken van C3:C7 (het bereik met namen) wijzen we met de cursor de bovenrand van cel C2 aan; de cursor wordt dan een zwarte pijl naar beneden. Op dat moment 1 keer klikken en de formule wordt aangevuld met tblWerknemers[Naam] (ofwel de kolom Naam uit de tabel tblWerknemers).
  3. de rest mag dan duidelijk zijn: tik in ;vergelijken( en klik op cel F3
  4. tik in ; en klik op de bovenrand van cel B2; de formule wordt aangevuld met tblWerknemers[Nr]
  5. tik in ;0))
  6. druk op Enter

Voeg een nieuwe werknemer toe en kijk of de formules nog werken.

Index en Vergelijken (deel 3)

Het blijkt niet zo makkelijk om te onthouden hoe de combinatie van deze functies moet worden ingevoerd. Maar pas kwam ik een handige truc tegen (zie Wyn Hopkins op LinkedIn): gebruik de auto-correctie-mogelijkheden van Office! En dit kan natuurlijk met iedere complexe of anderszins moeilijk te onthouden functie(-combinatie).

  1. indverg6Kies in Excel de menutab Bestand
  2. klik op Opties en dan op Controle.
  3. klik op de button AutoCorrectie-opties
  4. in het nieuwe scherm vullen we onder Vervangen: een afkorting in die we hierna zullen gebruiken, bijvoorbeeld avz (Alternatief Voor Zoeken).
    NB werkt u bij of voor de firma Aluminium Verwerking Zuid dan zult u een andere, niet gebruikte afkorting moeten nemen.
  5. onder Door: komt de formule
    =INDEX(Stap3_Waar_is_het_resultaat?;VERGELIJKEN(Stap1_Wat_zoeken?;Stap2_Waar_zoeken?;0))
  6. Kies Toevoegen en OK

Nu gaan we deze afkorting gebruiken:

  1. open het tabblad IndVerg3 in het Voorbeeldbestand
  2. klik in cel G3; de formule die daar staat gaan we opnieuw opbouwen
  3. tik in avz (of een andere afkorting die bij Vervangen: is ingevuld) en voeg nog een spatie toe
  4. de auto-correctie doet zijn werk en in plaats van avz staat er nu onze index/vergelijken-formule!
  5. dubbelklik op Stap1 (door de underscores wordt alles tot aan de ; geselecteerd) en klik op cel F3
  6. dubbelklik op Stap2 en klik op de bovenrand van cel B2
  7. dubbelklik op Stap3 en klik op de bovenrand van cel C2

Makkelijk toch? Er is nu geen enkele reden meer om VERT.ZOEKEN (of HORIZ.ZOEKEN) te blijven gebruiken.

Index en Vergelijken (multi-criteria)

In ons voorbeeldbestand komen twee mensen voor met de naam Jansen. Excel levert ons bij het zoeken altijd de eerste (zie cel I3 in het tabblad IndVergMulti van het Voorbeeldbestand).
Willen we zeker weten dat we de juiste persoon ophalen dan zullen we nog meer eigenschappen er bij moeten betrekken om zo de zoekopdracht uniek te maken, bijvoorbeeld de geboortedatum.

NB voordat u in Excel een zoek-optie implementeert, moet u zeker weten op welke manier u iemand of iets uniek kunt identificeren; misschien is naam en geboortedatum niet genoeg.

Voordat we de multi-criteria-formule gaan implementeren moeten we eerst kijken naar een alternatieve implementatie van VERGELIJKEN.
In het tabblad IndVergMulti in cel L4 plaatsen we de volgende formule:

=VERGELIJKEN(WAAR;tblWerknemers3[Naam]=G4;0)

In plaats van het vergelijken van een zoekwaarde (G4) met een bepaald bereik (tblWerknemers3[Naam]) vergelijken we hier de zoekwaarde WAAR met een reeks uitkomsten, die ontstaat door te controleren of de cellen in de kolom Naam gelijk zijn aan G4.

NB wilt u weten hoe een formule intern de tussenstappen berekent, kies dan in de menutab Formules de optie Formule evalueren.

Excel moet wel weten dat G4 met ALLE cellen uit de kolom Naam moet worden vergeleken; daarom moet deze formule als een zogenaamde array-formule worden ingevoerd door niet gewoon op Enter te drukken maar op Ctrl-Shift-Enter (soms wordt dit dan ook de CSE-methode genoemd).
Excel plaatst automatisch accolades om de formule bij CSE; dit is niet hetzelfde als wanneer er handmatig accolades geplaatst worden!!

Maar nu multicriteria: de vergelijking van G4 met de cellen van de kolom Naam levert een reeks op met daarin WAAR of ONWAAR; intern Excel is dit een 1 , respectievelijk 0.
Door deze reeks WAAR/ONWAAR (ofwel 1 en 0) te vermenigvuldigen met een andere reeks, die ontstaat door H4 (een datum) te vergelijken met de kolom GebDtm, krijgen we een nieuwe reeks met een 1 als aan alle twee de voorwaarden is voldaan, anders een 0.

In cel M4 staat dan ook de formule
=VERGELIJKEN(1;(tblWerknemers3[Naam]=G4)*(tblWerknemers3[GebDtm]=H4);0)

LET OP ook deze formule moet met CSE worden ingevoerd.

LET OP plaats bij het vermenigvuldigen van dit soort reeksen er haakjes omheen.

NB aangezien de vermenigvuldiging altijd een 1 of 0 oplevert (en niet WAAR of ONWAAR) moet als zoekwaarde een 1 worden opgegeven (bekijk de tussenstappen met Formule evalueren).

Om het juiste resultaat op te halen op basis van de 2 criteria staat in I4 de formule
=INDEX(tblWerknemers3[Nr];VERGELIJKEN(1;(tblWerknemers3[Naam]=G4)*(tblWerknemers3[GebDtm]=H4);0))

LET OP ook deze formule moet met CSE worden ingevoerd!

Cadeau: in de cellen I7 en J7 staat een aangepaste formule, die er voor zorgt, dat bij een onbekende werknemer er geen #N/B als resultaat komt.


Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd: