Excel top5 formules: #3 INDIRECT

Als ALS en VERT.ZOEKEN niet toereikend zijn

Nu hoor ik je denken: “is this even possible?” Ja, it is. Met Verticaal zoeken en de ALS functies kom je een eind in Excel, maar je kunt het jezelf nog een stapje makkelijker maken door gebruik te maken van de functie INDIRECT in je excelrapportages. Met deze functie kun je namelijk binnen een formule een verwijzing naar een cel wijzigen, zonder de formule zelf te wijzigen. Dit klinkt heel ingewikkeld en wellicht onnodig, maar in deze post leg ik uit wanneer de functie INDIRECT uitkomst biedt. 

In de vorige post over de ALS-functie ben ik geëindigd met een formule om dynamisch – op basis van een zelfgekozen maand – de belangrijkste statistieken te cumuleren. Zodat je zelf de controle en flexibiliteit houdt over je rapportage. Hiervoor heb ik een geneste ALS-functie gebruikt (je gebruikt dan meerdere ALS functies in één formule):

Hiermee kom je echter in de knoei als je meer dan 64 keer ALS aanroept in één formule. Dit is namelijk het maximale aantal keer dat je ALS kunt gebruiken in een geneste formule. Nu zal dit niet zo snel zo’n vaart lopen, maar met een weekrapportage zul je toch al 52 ALS-functies moeten bouwen. I can tell you – dat is heleboel intypen en een heleboel haakjes ;-).

Dat moet makkelijker kunnen

De functie INDIRECT biedt hierop uitkomst. Deze formule wordt door Office help omschreven als: “INDIRECT geeft als resultaat de verwijzing die door een tekenreeks wordt aangegeven. Verwijzingen worden onmiddellijk geëvalueerd, zodat de inhoud kan worden weergegeven. Met deze functie kun je zo binnen een formule een verwijzing naar een cel wijzigen, zonder de formule zelf te wijzigen”. 

Dit is de formule, zoals je hem invoert in Excel: INDIRECT(verw_tekst)

• de verw_tekst kan een verwijzing zijn naar een cel, maar ook naar een naam van een tabblad of van een bestand. Als verw_tekst geen geldige celverwijzing is, geeft INDIRECT de foutwaarde #VERW! als resultaat.
• Als verw_tekst naar een andere werkmap (een externe verwijzing) verwijst, moet deze werkmap wel open staan. Als de bronwerkmap niet geopend is, geeft INDIRECT de foutwaarde #VERW! als resultaat.

Verwijzing naar een cel

De rapportage uit de vorige post zal ik nu aan gaan passen met behulp van de functie INDIRECT. De formule die nu in de laatste kolom staat (met de geneste ALS functie) ga ik vervangen.

Horizontaal zoeken

Allereerst ga ik een hulprij toevoegen en daarna horizontaal zoeken. Horizontaal wat? Ja, horizontaal zoeken! In principe is dit niets anders dan verticaal zoeken, maar dan zoek je niet naar een bepaalde cel in de zoveelste kolom, maar naar een cel in de zoveelste rij. In rij 37 type ik in elke cel de corresponderende kolomnaam. In kolom J komt dus een J te staan, in kolom K een K etcetera.

In de cel V1 stel ik de formule in met horizontaal zoeken:

=HORIZ.ZOEKEN(V2;$J$5:$U$37;33;ONWAAR). Ik zoek dus naar de waarde uit cel V2 (hier mrt) in het bereik J5 tot en met U37. Als de waarde gevonden wordt, wordt de waarde uit de 33e rij teruggegeven. Het resultaat van deze horizontaal zoeken actie is dus de letter L.

Vervolgens ga ik een aantal hulpkolommen toevoegen:

• In kolom W type ik het rijnummer van die rij;
• In kolom X type ik de formule =$V$1&W6. De waarde uit de cel V1 wordt dus overgenomen (hier de letter L). De waarde uit de cel V1 is het resultaat van het horizontaal zoeken naar – in dit geval – maart (mrt). Daarachter wordt de waarde geplakt uit cel W6 (hier dus 6).

De INDIRECT formule

De uitkomst van de formule in kolom X kan als input gebruikt worden voor de INDIRECT formule. De formule in V7 gaan we nu vervangen door: =SOM(J7:INDIRECT(X7)):

Deze formule zegt nu: geef het resultaat terug van de som J7:L7 (L7 is namelijk het resultaat van INDIRECT(X7)). Dus de gecumuleerde waarde van januari tot en met maart. Dit is dus een veel kortere formule – en daardoor ook minder foutgevoelig – dan de geneste ALS functie! Als ik nu ‘mrt’ vervang door ‘feb’, wordt door horizontaal zoeken automatisch de goede kolomnaam gezocht en cumuleert de INDIRECT formule enkel nog J7:K7:

Ik heb het excelbestand bijgevoegd, zodat je de formules rustig kunt bekijken: download voorbeeld excel rapportage.

Verwijzing naar een naam van een tabblad of een bestand

Een andere toepassing van de INDIRECT formule is bij het verwijzen naar een ander bestand of zelfs naar een tabblad in een bestand. Dit kan bijvoorbeeld handig zijn als je moet verwijzen naar een extern bestand, waarbij elke maand gerapporteerd staat in een apart tabblad. Dan wil je niet elke maand je verwijzingen aan hoeven te passen in je rapportage.

Stel je hebt een excelbestand ‘Maandelijkse kosten 2012-2013.xls’. In dit bestand staan 12 tabbladen (jan t/m dec) met daarin de kosten per medium. De kosten staan altijd in cel B2 tot en met B15. Als je ervoor zorgt dat de tabbladen hetzelfde genoemd zijn als de maanden in jouw rapportage, dan kun je de volgende formule gebruiken:

=SOM(INDIRECT(“‘[Maandelijkse kosten 2012-2013.xls]”L$5&”‘!$B$2:$B$15”))

Deze formule gaat dus B2:B15 sommeren uit het bestand ‘Maandelijkse kosten 2012-2013.xls’ van het tabblad dat overeenkomt met L5 uit je eigen rapportage. L5 is ‘mrt’. De kosten van maart worden dus opgeteld en weergegeven. Als je de formule doortrekt naar rechts, wordt L$5 vanzelf M$5 en N$6 en wordt dus ook de formule automatisch zo aangepast dat de kosten uit het externe bestand worden opgehaald van respectievelijk ‘apr’ en ‘mei’. En dit zonder de verwijzingen aan te hoeven passen!

De formule INDIRECT kan dus een hoop werk besparen. Heb je vragen over de INDIRECT-functie in Excel of andere rapportage vragen, neem dan gerust contact met me op of reageer op deze post!