+31 624 239 426
annelytics@outlook.com
Annelytics
  • Home
  • Blog
  • Contact
  • Home
  • Blog
  • Contact
    Home > Blog > Excel > Excel top5 formules: #3 INDIRECT

Excel

16 jun

Excel top5 formules: #3 INDIRECT

  • By Annemarie
  • In Excel
  • 12 comments

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!

Lees ook mijn andere blogposts

  • Excel top5 formules: #1 Verticaal Zoeken
  • Excel top5 formules: #2 ALS functie
  • Campaign tagging, eventtracking en virtual pageviews
  • Doelen instellen in Google Analytics
  • Share:
Annemarie

    Comments

  1. Nico Bakker
    17 september 2013

    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.

    Beantwoorden
  2. Berry
    19 september 2013

    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

    Beantwoorden
    • Annemarie
      20 september 2013

      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

      Beantwoorden
  3. Ben
    24 september 2013

    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

    Beantwoorden
  4. Rizo
    1 februari 2014

    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?

    Beantwoorden
  5. nico bakker
    7 september 2014

    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

    Beantwoorden
  6. nico bakker
    7 september 2014

    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 .

    Beantwoorden
    • Annemarie
      7 september 2014

      Hi Nico,

      Ook een duidelijk en interessant filmpje! Ik heb hier eerder ook al gebruik van gemaakt en it works like a charm :-)!

      Beantwoorden
  7. nico bakker
    30 september 2014

    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

    Beantwoorden
  8. Leon
    19 maart 2015

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

    Beantwoorden
  9. Johan Schurink
    8 januari 2017

    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.

    Beantwoorden
  10. Koos Wubs
    15 oktober 2018

    Je voorbeeld heeft me geholpen om de juiste ‘instellingen’ over te kunnen nemen. Maar ik merk dat ik een verwijsfout krijg wanneer het bronbestand niet geopend is. Ik maak gebruik van meerdere bronbestanden (per maand).
    Blijkbaar moet het gehele pad in de indirect formule worden opgenomen wil de koppeling ook ‘werken’ wanneer de bronbestanden niet zijn geopend.
    Of weet je een oplossing voor dit ‘probleem’?

    Beantwoorden

Leave A Reply Reactie annuleren

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

Categorieën

  • A/B testen
  • Blog
  • Conversie-optimalisatie
  • Excel
  • Geen categorie
  • Google Analytics

Recent Posts

Waarom een Bayesiaanse A/B-test evaluatie meer oplevert
11nov,2018
How to visualize A/B-test results
11jun,2018
Pas op voor de Simpson’s Paradox
09jan,2017

Laatste twitterberichten

@MartijnSch Thx Martijn! :)

Ongeveer 4 maanden geleden op Annemarie Klaassen's Twitter via Twitter for Android

Are you re-energized after lunch? My talk - how to find more wins and be more confident in your winners - starts in 20 minutes. Will I see you there? digital-elite-day-1.heysummit… #digitalelite20

Ongeveer 2 jaar geleden op Annemarie Klaassen's Twitter via Twitter Web App

@e_maile @digitaleliteday I will be sharing case studies as well tomorrow :). Will you be there? digital-elite-day-1.heysummit…

Ongeveer 2 jaar geleden op Annemarie Klaassen's Twitter via Twitter for Android

@digitaleliteday @annatlewis @SEOMalc @craigcampbell03 @tonw @MrLukeCarthy @RicRodriguez_UK @rogerdooley @SimoAhava @jimsterne @DuaneForrester @georgizgeorgiev @chippy @jonoalderson @nilskattau @bart_goralewicz @Suzzicks @lilyraynyc Sneak peak of my talk. 10 tips how to find more wins and be more confident in your winners. #ded2020 #digitaleliteday pic.twitter.com/NByO6f5rEL

Ongeveer 2 jaar geleden op Annemarie Klaassen's Twitter via Twitter for Android

@hellemans I'd love to :)

Ongeveer 2 jaar geleden op Annemarie Klaassen's Twitter via Twitter for Android

Get in touch

+31 624 239 426

annelytics@outlook.com

Magnoliaerf, 5038 KP Tilburg

Social Links

  • Twitter
  • Linkedin

Wordpress Theme by ThimPress. Powered by WordPress.

  • Home
  • Contact