Excel top5 formules: #3 INDIRECT

Als ALS en VERT.ZOEKEN niet toereikend zijn

nerd-excelNu 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):

cumulatief mbv functie als

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:

horizontaal zoeken excel

=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:

indirect excel hulp

  • 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)):

indirect excel

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:

indirect excel andere maand

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!

11 gedachten over “Excel top5 formules: #3 INDIRECT

  1. Nico Bakker

    Ik vindt het fantastisch wat je gedaan hebt met de formule INDIRECT ! Het gebruik van de formule + heldere presentatie, niet alleen slim maar ook veel werk dunkt mij.
    Zelf ben ik bezig me te verdiepen in Scenarios met oplosser. Er zijn vele handboeken en hulpwebjes maar die lijken altijd een paar stappen over te slaan met hun uitleg.
    Dus het enige wat erop zit is zelf met ‘ vallen en opstaan’ mijn scenario bouwen.

    Reageren
  2. Berry

    Dag Annemarie,

    Ik vind dat je op een heel heldere manier uitleg geeft aan soms lastige formules, zoals de INDIRECT functie, echter kan het natuurlijk allemaal nog veel handiger. Ik geef een voorbeeld:

    In jouw eerste voorbeeld over de INDIRECT functie ga je in veld V7 de functie =SOM(J7:INDIRECT(X7)) plaatsen, waarbij je verwijst naar veld X7 waarin je de functie =$V$1&W7 hebt staan, waarin je weer verwijst naar veld W7 waarin je de regelnummer van die regel hebt staan. Je zou hiervoor alvast de functie =ROW() kunnen gebruiken die je direct het rijnummer teruggeeft. Verder zou je dan in de fucntie in X7 al gebruik kunnen maken van deze =ROW() functie waardoor je al een hulpkolom minder nodig hebt. Dit zou er dan zo uit kunnen zien: =$V$1&ROW(). Dit geeft ook L7 als resultaat.

    Als je dit dan weer in veld V7 toepast kan je daar X7 vervangen door $V$1&ROW() en dan heb je in veld V7 de volgende formule staan: =SUM(J7:INDIRECT($V$1&ROW())) en ben je gelijk 2 hulpkolommen kwijt. Dit ziet er misschien voor de beginnende excel gebruiker moeilijk uit, maar door dit stap voor stap uit te leggen (zoals je doet in dit blog) komt die boodschap ook wel aan.

    Ik hoop dat je dit gaat gebruiken, want ik hou wel van een beetje structuur….

    Groetjes,
    Berry

    Reageren
    1. Annemarie Bericht auteur

      Hoi Berry,

      Je hebt helemaal gelijk hoor! Maar om mijn verhaal ook zo overzichtelijk en duidelijk te houden voor iedereen heb ik gewerkt met hulpkolommen. Jouw omschreven manier pas ik voor mezelf al toe trouwens :-)

      Groetjes,
      Annemarie

      Reageren
  3. Ben

    Hallo Berry en Annemarie,

    een heerlijk en mooi geschreven tutorial waar ik al ruim een jaar naar opzoek ben.
    in mijn werk (leraar op een Mavo-school) heb ik te maken met cijferlijsten van leerlingen én de overgangsnormeringen. Als medeverantwoordelijke voor ons team (de nieuwe manier van werken 😉 ) heb ik afgelopen jaar (bijna 2 jaar) gewerkt aan het ‘vergemakkelijken’ van de overgangsvergaderingen…

    Vaak werden dat van die discussies terwijl het in mijn beleving makkelijker zou moeten kunnen (en zakelijker), vandaar dat ik in excel een aantal werkbladen heb gecreëerd waarbij op de leerlinglijst via verticaal zoeken de cijfers van het laatste rapport worden getoond met daarbij of de leerling wel of niet over is (en aan welke criteria de leerling (nog) niet voldoet. De mentoren vinden het ook een prachtig hulpmiddel tijdens gesprekken.

    Ik exporteer nu uit ons cijfersysteem de cijfers per leerling per rapport, en moet dan elke keer checken of de kolommen wel in de juiste volgorde staan, maar met jullie oplossing wordt het een stuk makkelijker….
    dus mijn dank is groot…. ik ga deze formules eens lekker uitproberen en er mee stoeien…

    ik heb alleen een vraag over de verschillende jaren onder elkaar én het oproepen van de verschillende jaren op het tabblad gegevensset 1, welke formule/instelling gebruiken jullie daarvoor?… dan wordt het terugkijken naar vorige rapporten/leerjaren NOG makkelijker….

    met vriendelijke groet,
    Ben

    Reageren
  4. Rizo

    Goeie uitleg van de functie indirect. Wat alleen als ik voor de data van een grafiek wil verwijzen naar een cel waarin de sheet-naam staat. Dit gaat niet met indirect, dat trekt de grafiek niet. Heb je daar een oplossing voor?

    Reageren
  5. nico bakker

    Met sommen.als kun je ook twee kolom-tags optellen.
    ik gebruik een blad als bron, een ander blad met zoek-functie, in dit andere
    blad laat ik de opgezochte categorien (b.v. kostuum, techn. mat. in de kolom ernaast verwijzen naar een andere categorie) kostuum valt dan in de sub. Activiteiten materieel b.v..
    In dit blad wil ik dan een scenario maken ( met de naam-functie o.a.)
    en kan ik op meer manieren aan de collega’s rapporteren.
    Concreet voorbeeld : een andere partij ( b.v. buitenlandse) wil een andere
    groepering kosten.
    Een geheel ander ding dan waar jij mee bezig bent

    Reageren
  6. nico bakker

    Op you tube legt dany rocks uit
    over groepering van afdelingen
    vanuit een slordig document naar een consistenter, logischer geheel.
    Hij gebruikt daarvoor ‘data-validation’ en de ‘indirect-functie’ om binnen een divisie een bepaalde afdeling te zoeken.

    titel filmpje : use the indirect ( ) function to create a 2nd pick from list
    in excel.
    Ik ben benieuwd of je het interessant vindt .

    Reageren
  7. nico bakker

    Excel Magic Trick 913: Select From Drop Down and Pull
    Data From Different Sheet
    ( maakt ook gebruik van de “indirect” functie om data uit verschillende
    sheets op te halen)
    op you tube

    Reageren
  8. Johan Schurink

    Heerlijk dit….

    Was al een tijd op zoek naar een goed werkende formule om mijn dagelijkse data, die in een weekrapport wordt weergegeven, ook de cumulatieve te verwerken. Zojuist even een proefsessie op mijn testblad losgelaten en het werkt fantastisch :)

    Helder en duidelijk uitgelegd en met het voorbeeld bestand was het omzetten naar mijn eigen situatie een fluitje van een cent.

    Heel veel dank hiervoor.

    Reageren

Geef een reactie

Het e-mailadres wordt niet gepubliceerd. Verplichte velden zijn gemarkeerd met *