Variabele functies

Aan het einde van deze les is bekend: Op welke manieren een argument van een functie variabel is te maken; Hoe argumenten eenvoudig -deels- vast en variabel kunnen worden gemaakt; Hoe de functies RIJ() en KOLOM() werken als argument; Hoe de functie INDIRECT() als argument werkt; Hoe de functie AANTALARG() als argument werkt;
Vertikaal zoeken in het kort De functie VERT.ZOEKEN bestaat uit vier argumenten en heeft de volgende syntax: =VERT.ZOEKEN(zoekwaarde;tabelmatrix;kolomindex_getal;benaderen) In dit voorbeeld is de functie: =VERT.ZOEKEN(D4;A1:C25;2;0) De zoekwaarde staat in cel D4 en is daarmee op twee manieren variabel gemaakt. Als de inhoud van cel D4 wijzigt, verandert de uitkomst in cel E4 mee. Daarnaast wijzigt de celverwijzing als de functie gekopieerd wordt of wordt doorgetrokken naar beneden of naar rechts. De tabelmatrix, ofwel de kolom(men) waarin de zoekwaarde gezocht wordt, beslaat de cellen A1 tot en met C25.Dit bereik is eveneens variabel, in die zin dat als je de functie kopieert of doortrekt naar beneden of naar opzij dat het bereik ook verandert. Als je de functie één cel naar rechts kopieert, verandert het bereik naar B1:D25. Kopieer je de formule twee cellen naar beneden, dan verandert het bereik naar A3:C27. Het kolom_indexgetal is vast, namelijk twee. Waar de functie ook naar wordt gekopieerd, dit getal blijft twee. Tenslotte is het enige optionele argument 'benaderen' op 0 gezet wat staat voor ONWAAR. Ook dit argument verandert niet bij het verplaatsen of kopiëren van de functie.
Argumenten vastzetten De zoekwaarde staat in cel D4 en is variabel. Het kan vast worden gezet door de zoekwaarde, bijv. periode "2007-04", in de plaats te zetten van D4: =VERT.ZOEKEN("2007-04";A1:C25;2;0). Het adres van de zoekwaarde is kolom D, rij 4. Zowel de rij als de kolom is vast te zetten via het $-teken. $D4 zorgt ervoor dat de zoekwaarde alijd in kolom D staat. Het rijnummer is afhankelijk van het rijnummer waarnaar de formule gekopieerd wordt. Daarentegen zorgt D$4 ervoor dat de zoekwaarde altijd op rij 4 staat, en dat de kolom afhankelijk is van de kolom waarin de functie komt te staan. Tenslotte zet $D$4 de zoekwaarde vast in cel D4 ongeacht waar de functie naartoe gekopieerd wordt. Op dezelfde manier kan de tabelmatrix vastgezet worden: $A$1:$C$25 zet het hele bereik vast; $A1:$C25 zet alleen de kolommen vast; A$1:C$25 zet de rijnummers vast; $A$1:C25 zet de begincel linksboven vast, maar laat de eindcel variabel; A1:$C$25 zet de eindcel vast, en laat de begincel variabel. N.B. Met de laatste optie is het oppassen, want als je de functie doortrekt tot meer dan 25 cellen naar beneden, dan zet Excel zelf de instellingen van A1:$C$25 naar A$25:$C26!
Het kolomindex_getal staat nu vast op 2 zodat als de zoekwaarde in kolom A gevonden is, dezelfde waarde uit de tweede kolom, dus B, weergegeven wordt. Maar als we nu niet de kosten maar de opbrengsten willen weten, dan zouden we de 2 in een 3 moeten veranderen. Ook dit argument is variabel te maken door bijv. te verwijzen naar cel D5:=VERT.ZOEKEN(D4;A1:C25;D5;0). Als in cel D5 een 2 staat worden de kosten weergegeven uit de gezochte periode. Staat er een 3 dan worden de opbrengsten van de gezochte maand weergegeven.
Een andere manier om het argument kolomindexgetal variabel te maken is met behulp van de functie RIJ() of KOLOM(). De functie =RIJ(D5) geeft als waarde een 5 omdat dit het rijnummer van het opgegeven adres is. De functie =KOLOM(D5) geeft een 4 omdat kolom D de vierde kolom is. De verwijzing in de functie kan ook weggelaten worden. De functie =RIJ() geeft het rijnummer van de cel waarin de functie staat. Van de uitkomst van deze functies is weer een getal af te trekken of bij op te tellen: =RIJ()+1 geeft het rijnummer van de cel waarin de functie staat plus een.
Stel nu dat we twee functies naast elkaar willen die bijna identiek zijn. De eerste moet de kosten van de gezochte periode weergeven, en de tweede de opbrengsten. Dit kan door het kolomindex_getal afhankelijk te stellen van de kolomnummer waarin de functie staat. De functie voor de kosten staat in kolom E, de vijfde kolom, maar moet de waarde uit de tweede kolom van de zoekmatrix weergeven. De functie voor de opbrengsten komt in kolom F, de zesde kolom, en moet de waarde uit de derde kolom weergeven: In cel E5 komt dan te staan:=VERT.ZOEKEN($D$4;$A$1:$C$25;kolom()-3;0). Let op dat in dit voorbeeld alle andere bereiken vastgezet zijn. Als we dan deze functie kopiëren naar cel F5 komt er eveneens te staan: =VERT.ZOEKEN($D$4;$A$1:$C$25;kolom()-3;0). Het argument kolom()-3 zorgt er in cel E5 voor dat de kosten getoond worden, terwijl in cel F5 de opbrengsten getoond worden. Tenslotte kan op deze manier ook het argument 'benaderen' variabel worden gemaakt door te verwijzen naar een andere cel waarin een nul of een een, of WAAR en ONWAAR gekozen kunnen worden.
Variabel over een werkblad heen. De zoekwaarde en de tabelmatrix staan nu op hetzelfde werkblad, genaamd auto. Maar de functie kan ook in een ander werkblad zoeken. Als de functie op een ander werkblad, zeg 'rapportage' komt te staan, dan kan de functie er zo uit zien: =VERT.ZOEKEN(A1;auto!A1:C25;2;0) De zoekwaarde uit cel A1 op het tabblad 'rapportage' wordt gezocht in de tabelmatrix op het werkblad 'auto'. Het argument tabelmatrix is nu: auto!A1:C25. De naam van het werkblad wordt gevolgd door een !. Stel dat het rapportagebestand uit drie tabladen bestaat: auto, fiets en rapportage. Op het werkblad rapportage wil je een rapportage bouwen waarbij eenvoudig instelbaar is dat er afwisselend gekeken wordt naar de gegevens over auto's of over fietsen. Hiervoor is de functie INDIRECT te gebruiken.
INDIRECT De functie INDIRECT maakt argumenten variabel door tekstdelen of delen van een functie aan elkaar vast te plakken. In ons geval gaan we het rode argument in: =VERT.ZOEKEN(A1;auto!A1:C25;2;0) variabel maken met INDIRECT. Om delen aan elkaar vast te plakken wordt het &-teken (ampersand) gebruikt. De tekst moet net als bij andere argumenten tussen dubbele quotjes gezet worden. In cel A2 kan gekozen worden uit 'auto' en 'fiets'. Het bereik blijft A1:C25. De uiteindelijke functie ziet er zo uit: =VERT.ZOEKEN(A1;INDIRECT(A2&"!A1:C25");2;0).
Werkbladnaam met spaties of cijfers Wanneer de naam van het werkblad uit cijfers bestaat of een spatie bevat, wordt de syntax van de functie INDIRECT iets ingewikkelder. Stel dat het bestand niet uit de drie tabbladen 'auto', 'fiets' en 'rapportage' bestaat, maar uit '2007', '2008' en 'rapportage. En stel dat de functie INDIRECT gebruikt moet worden om een rapportage variabel te maken over jaargangen heen. De oorspronkelijke functie zal er dan zo uit zien: =VERT.ZOEKEN(A1;'2008'!A1:C25;2;0). Het jaartal staat tussen enkele quotjes! Ook een naam met een spatie erin zoals 'Regio Zuid' krijgt in de functie enkele quotjes er omheen. De functie INDIRECT dient die enkele quotjes ook te bevatten. De uiteindelijke functie ziet er dan zo uit: =VERT.ZOEKEN(A1;INDIRECT("'"&A2&"'"&"!A1:C25");2;0). Voor en na de A2 staan tussen dubbele quotjes een enkele quote die met z'n drieën aan elkaar geplakt worden, tezamen met het bereik "!A1:C25". Ditzelfde kan iets korter opgeschreven worden door het tweede enkele quotje mee te nemen met het deel '!A1:C25'.: =VERT.ZOEKEN(A1;INDIRECT("'"&A2&"'!A1:C25");2;0) Als nu het bereik op werkblad 2007 hetzelfde is als 2008, dan is met deze functie de rapportage variabel gemaakt.
Nadelen INDIRECT De functie INDIRECT voegt vele mogelijkheden toe in combinatie met andere functies. Maar het heeft wel nadelen. Als de functie VERT.ZOEKEN verwijst naar A1:C25, en in dat bereik wordt een rij ingevoegd, dan verandert het bereik in de functie naar A1:C26. Bij de functie INDIRECT blijft het bereik onveranderd en dient dus handmatig aangepast te worden! Of via een andere functie zoals AANTALARG. Ook is het mogelijk om via INDIRECT te verwijzen naar een ander bestand. Echter, zodra het bronbestand waarnaar verwezen wordt gesloten wordt, raakt Excel de verbinding kwijt, en alle functies tonen de foutmelding #VERW!.
Variabele argumenten in combinatie met AANTALARG Via de functie =AANTALARG('2008'!C:C) op het werkblad 'rapportage' is te bepalen hoeveel rijen in kolom C op het tabblad 2008 gevuld zijn. In dit geval 25 omdat de rapportage uit 24 maanden bestaat en kolomtitels heeft. Met deze functie kunnen we het eerstgenoemde nadeel van de functie INDIRECT opheffen. De functie zoals deze nu is: =VERT.ZOEKEN(A1;INDIRECT("'"&A2&"'!A1:C25");2;0). Het tekstgedeelte "!A1:C25" wordt "!A1:C" met daaraan vastgeplakt het aantal gevulde cellen in kolom C: =VERT.ZOEKEN(A1;INDIRECT("'"&A2&"'!A1:C"&AANTALARG('2008'!C:C));2;0) Maar als nu de rapportage over het jaar 2007 gemaakt moet worden, en in cel A2 het jaartal 2007 komt te staan, dan kan de uitkomst van de functie fout zijn, omdat er nog wordt gekeken naar het aantal gevulde cellen in kolom C van tabblad 2008! Ook dat is weer variabel te maken door het rode gedeelte te vervangen: =VERT.ZOEKEN(A1;INDIRECT("'"&A2&"'!A1:C"&AANTALARG('2008'!C:C));2;0): =VERT.ZOEKEN(A1;INDIRECT("'"&A2&"'!A1:C"&AANTALARG(INDIRECT("'"&A2&"'"&"!C:C")));2;0) En ook dit kan weer iets korter geschreven worden: =VERT.ZOEKEN(A1;INDIRECT("'"&A2&"'!A1:C"&AANTALARG(INDIRECT("'"&A2&"'!C:C")));2;0) Wanneer je op deze manier functie-argumenten variabel aan het maken bent, dan is het verstandig om stapsgewijs te werk te gaan. Maak bijv. eerst INDIRECT("'"&A2&"'!A1:C25) om dit deel te plakken in de functie AANTALARG('2008'!A1:C25) en plak het over het rode gedeelte heen.
(Voorlopig) EINDE LES Variabele functies.