Excel top5 formules: #1 Verticaal Zoeken

Geek alert!

geekGoogle Analytics kent een aantal standaardrapportages die al heel veel goede inzichten kunnen geven. En voor kleine adhoc vragen is dit ook genoeg. Echter, wil je echt kijken naar trends en ontwikkelingen over tijd en dat voor meerdere dimensies, dan zal je toch de data moeten exporteren. Excel is een programma waar we de exporteerde data goed mee kunnen analyseren. I know: it’s not everybody’s favourite, maar je kunt toch meer met Excel dan je zou denken. Hiervoor zijn een aantal formules een must-know. En #1 is Verticaal Zoeken! In deze post leg ik uit hoe deze formule werkt en hoe je deze toe kunt passen.

Wat doet Verticaal zoeken?

[wp_ad_camp_2]

Verticaal zoeken is volgens de Office help: “Verticaal zoeken zoekt naar een waarde in de eerste kolom van een tabelmatrix en geeft als resultaat een waarde uit dezelfde rij in een andere kolom in de tabelmatrix.”

In formuletaal ziet dat er zo uit:  =VERT.ZOEKEN(zoekwaarde;tabelmatrix;kolomindex_getal;benaderen)

Oftewel: abracadabra! Met een concreet voorbeeld zal ik proberen deze formule uit te leggen. Stel je hebt uit Google Analytics een export gedraaid met per maand, per betaald medium (bijv. AdWords, affiliatemarketing, displaymarketing en partnerships) het aantal bezoeken, het aantal boekingen en bijbehorende omzet. En dit met een datumperiode van 01-01-2013 t/m 31-3-2013.

De export zou er dan zo ongeveer uitzien:export

Omdat dit betaalde kanalen zijn, wil je de effectiviteit hiervan meten. Oftewel: hoeveel kosten staan er tegenover de omzet? De kosten van AdWords kunnen automatisch worden doorgespoeld naar Google Analytics, maar de kosten van de affiliates,displaycampagnes en dergelijke heb je niet paraat in Google Analytics. Je zult dus een koppeling moeten leggen met een apart kostenbestand.

Deze ziet er bijvoorbeeld zo uit:
kosten

We moeten er nu eerst voor zorgen dat de beide bestanden op elkaar lijken qua opbouw. Daarom maken we van de export een draaitabel met als kolommen de maand en als rijen de verschillende kanalen en waarden:

draaitabel

De namen van de verschillende media noemen we ook om, zodat deze precies hetzelfde zijn als in het kostenbestand. affiliatemarketing wordt Affiliates etcetera. Nu maken we eenzelfde tabel voor de kosten onder de Opbrengst:

verticaal zoeken

So let’s start the magic :-)

Nu zijn we klaar om onze formule te gebruiken. We typen eerst “=vert.zoeken” en een “(“. Nu klikken we de cel aan, waarnaar hij moet zoeken in het andere bestand. We willen dat Excel de kosten ophaalt van Affiliates in het kostenbestand, dus selecteren we deze.

Vervolgens geef je een puntkomma in en ga je naar het kostenbestand. Hier selecteer je het bereik waarin Excel moet gaan zoeken naar de kosten. De eerste kolom die je selecteert moet de waarde of label bevatten waarnaar je zoekt. In de eerste kolom staan de media dus dat is goed. De bijbehorende waarde moet Excel zoeken in het overig geselecteerde bereik:

verticaal zoeken

We typen wederom een puntkomma en geven nu in in de hoeveelste kolom de kosten staan. We zoeken naar de kosten van januari, dus dit is in de 2e kolom. We typen dus een 2. Hierna geven we nog een keer een puntkomma in en hierachter moeten we aangeven of Excel moet zoeken naar exact het woord Affiliates (dan type je ONWAAR) of dat het erop moet lijken (gaat vooral op bij getallen, dan type je WAAR). Wij geven dus ONWAAR in. Ten slotte sluiten we de formule met een “)”.

[wp_ad_camp_2]

vert.zoeken-3

Et voilá! De kosten zijn uit het geselecteerde bestand opgehaald: €6.009 euro in januari!

Make it flexible

We kunnen de formule niet zomaar doortrekken naar de andere cellen. De formule past zich namelijk ook aan. Door de formule naar rechts te verschuiven, kijkt hij niet meer naar I21, maar naar J21 (schuift ook 1 op naar rechts) en het bereik wordt dan B2:N2 in plaats van A2:M2. Ook blijft hij zoeken naar de kosten in de 2e kolom van het kostenbestand (de maand januari). Om dit op te lossen, moeten we de formule wat aanpassen.

We gaan cellen vastzetten en de kolom waarnaar hij moet zoeken flexibel maken:

Met $-tekens kun je binnen Excel cellen vastzetten. Dit doe je door in de formule te gaan staan met je cursor en F4 in te drukken. Bij 1x F4 zet Excel twee dollartekens – 1 voor en 1 na de kolomnaam. Dit betekent dat zowel de rij als de kolom wordt vastgezet. Klik je 2x F4 dan wordt er één dollarteken gezet – na de kolomnaam. Dit wil zeggen dat de rij wordt vastgezet. Klik je 3x dan verschijnt er één dollarteken vóór de kolomnaam. Nu staat de kolom vast.

  • =VERT.ZOEKEN(I21; veranderen we naar: =VERT.ZOEKEN($I21;. Hiermee zeggen we dat de kolom vastgezet moet worden. Als je de formule doortrekt, blijft de formule staan op de kolom I. Alleen het rijnummer past hij aan;
  • A2:M2; veranderen we naar $A$2:$M$2;. Met de dubbele dollartekens geven we aan dat zowel de kolom als de rij vastgezet moet worden. Het bereik waarbinnen de formule moet zoeken moet dus exact hetzelfde blijven;
  • Door boven de kolommen van de maanden de cijfers 0 tot en met 11 (0 = januari, 11 = december) toe te voegen, kunnen we dit gebruiken in de formule om deze flexibel te maken voor het hele jaar. 2; vervangen we door 2+K$2; (de rij zetten we vast). Nu zoekt de formule naar de 2e kolom (= januari) + 1 (= februari), dus de 3e kolom. Voor maart dus de 4e kolom etcetera.

flexibel

Nu kunnen we de formule naar beneden en naar rechts door kopiëren. En tadaaaa: alle kosten staan erin! Nu denk je vast nog wat een gedoe voor een paar cijfertjes – ik kan toch ook gewoon Ctrl-V, Ctrl-C gebruiken (kopiëren > plakken)? Ja dat kan, maar dit is een stuk foutgevoeliger en vaak wordt het kostenbestand (kosten zijn niet altijd definitief en kunnen nog veranderen) nog veranderd in de loop der tijd. Als er wijzigingen worden doorgevoerd in het kostenbestand wordt dit nu automatisch overgenomen in deze rapportage.

#2 van de Excel top5 formule is de ALS-functie. Met deze formule zullen we de gestarte rapportage verder uitbreiden. Dus houdt mijn blog in de gaten 😉

P.S. heb je vragen over Verticaal Zoeken in Excel of andere rapportage vragen, neem dan gerust contact met me op of reageer op deze post!

[wp_ad_camp_3]

Lees ook mijn andere blogposts

  • Excel top5 formules: #4 Tijdseenheden Time is tickin' In mijn functie als Webanalist maak ik (veel) Excel rapportages. Vanuit de business is vaak vraag naar zowel week- als maand- en soms zelfs kwartaalrapportages. Vaak zijn […]
  • Campaign tagging, eventtracking en virtual pageviews Google Analytics meet niet alles! Tenminste niet automatisch. Met de standaard trackingcode van Google Analytics worden alle unieke URL's gemeten. Daarnaast wordt een aantal media […]
  • Doelen instellen in Google Analytics Zonder doel geen optimalisatie! Google Analytics kent een aantal standaardrapporten. Dit zijn de meest belangrijke rapporten om naar te kijken om meer te weten te komen over jouw […]
  • 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 […]

Een gedachte over “Excel top5 formules: #1 Verticaal Zoeken

  1. Pingback: Excel top5 formules: #3 INDIRECT | Annelytics

Geef een reactie

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