In no time de juiste campagne-inzichten met Excel – data-analyse met zoekfuncties (deel 2)

Data samenvoegen uit verschillende programma’s kan een ramp zijn. Je wilt data combineren uit Google Analytics, Google Search Console, Google AdWords, Google MyBusiness en wellicht nog meer tools. Hoe krijg je dit op een overzichtelijke en begrijpelijke visueel gemaakt? Je kunt kiezen voor een dashboard, maar dat vergt kennis en middelen en is vaak niet de tijd waard bij eenmalige analyses. Een eenvoudigere oplossing is dan ook het gebruik van good old Excel. Want daarin kun je op verschillende manieren zoeken naar gegevens die jou verder helpen, zoals verticaal zoeken om makkelijk gegevens op te sporen in grote Excel-sheets. En er zijn alternatieven. Daarom twee tips hoe je inzichten verkrijgt met de zoekfuncties in Excel.

Tip 1: Verticaal zoeken

Een must-have bij data-exports

Als je echt actief aan de slag gaat met data-analyse, komen daar vaak verschillende tabellen of rapportages bij kijken. Dit is vergelijkbaar met de structuur van onder meer Google AdWords en Google Analytics, waarbij data in verschillende rapportages is terug te vinden. Hoe ga je hier verstandig mee om in je analyses?

Een van de geavanceerde mogelijkheden in Excel is de formule ‘verticaal zoeken’. Hiermee zoek je een waarde in de eerste kolom van een tabel en als resultaat krijg je de daarbij behorende waarde uit dezelfde rij. De formule ziet er zo uit:

VERT.ZOEKEN(zoekwaarde;tabelmatrix;kolomindex_getal;[benaderen])

Een beperking van de functie verticaal zoeken is de manier van zoeken. De functie zoekt namelijk alleen naar waarden die rechts van de oorspronkelijke waarde staan. Voorbeeld: je zoekt in een tabel bij “Online Marketing” een waarde. Je ziet dat in deze tabel “Online Marketing” in kolom D staat. Je kunt dan alleen een waarde zoeken vanaf kolom D en verder. Je kunt dan dus niet zoeken in de kolommen A, B en C.

In de praktijk

Als we verticaal zoeken in de praktijk brengen, ziet dat er als volgt uit. We beginnen met twee verschillende tabellen. Enerzijds heb je informatie over kanaal D, aan de andere kant een tabel met totale kosten over alle kanalen

De bedoeling is het bedrag van 800 euro in de lege cel van de linkertabel te plaatsen. Belangrijk is dat de namen van de kolommen overeenkomen, ‘Kosten Kanaal D’. De formule verticaal zoeken bevat diverse elementen.

• Zoekwaarde: de kolomnaam van de doelwaarde.
• Tabelmatrix: het bereik van de tabel waarin je de waarde zoekt.
• kolomindex_getal: het aantal kolommen dat Excel naar rechts moet opschuiven ten opzichte van de zoekwaarde in de brontabel.
• [benaderen]: Keuze tussen ONWAAR en WAAR: bij ONWAAR dient de zoekwaarde exact overeen te komen, bij WAAR gaat erom dat de zoekwaarden op elkaar moeten lijken. In vrijwel alle gevallen kies je de waarde ONWAAR.

In ons voorbeeld gaan we uit van de volgende waarden:

• Zoekwaarde: B5 (in linkertabel is dit ‘Kosten Kanaal D’)
• Tabelmatrix: [Map1]Blad1!$B$5:$E$11
• kolomindex_getal: 3
• [benaderen]: ONWAAR

In ons voorbeeld komt de formule er als volgt uit te zien:

=VERT.ZOEKEN(B5; [Map1]Blad1!$B$5:$E$11; 3; ONWAAR)

Uiteindelijk zal het resultaat als volgt zijn:

Als je de naam verandert in Kanaal E, zal ook de waarde zich automatisch aanpassen naar 1.000 euro.

Deze tips zullen je analyses een stuk makkelijker maken. Excel kent nog veel meer trucs, maar de bovenstaande tips zijn redelijk makkelijk onder de knie te krijgen. Op deze manier kun je zowel grotere datasets als meerdere datasets tegelijk efficiënt analyseren.

Wanneer te gebruiken?

Verticaal zoeken gebruik je als je uit een grote dataset specifieke waarden wilt halen. Je hoeft niet door de hele dataset op zoek naar een specifiek getal, want met behulp van verticaal zoeken laat je dit getal binnen de kortste keren naar voren komen.

Tip 2: Index en vergelijken

Verticaal zoeken 2.0

Een ander – en misschien zelfs betere – manier om gegevens bij elkaar te zoeken, zijn de INDEX- en VERGELIJKEN-functie.

Verticaal zoeken is gemakkelijker te gebruiken, maar heeft een paar beperkingen. Zo kun je met verticaal zoeken alleen réchts van de basiswaarde zoeken; alle data die links van deze waarde staan kun je met verticaal zoeken dus niet vinden.

Een oplossing hiervoor is om handmatig de kolommen om te draaien, maar zeker wanneer er regelmatig data worden toegevoegd aan de dataset, is dit geen handige oplossing.

Een betere oplossing voor dit probleem is het gebruik van de functies INDEX en VERGELIJKEN. Door deze twee functies te combineren, kun je zoeken naar een waarde, ongeacht of deze nu links of rechts in een andere kolom staat. Allereerst kijken we hiervoor naar de functie INDEX.

INDEX(verw;rij_getal;kolom_getal)

In deze functie geef je bij ‘verw’ het bereik van de tabel op. Vervolgens geef je aan in welk rijnummer Excel moet zoeken en als laatste ook het kolomnummer om aan te geven in welke kolom Excel moet zoeken. Let hierbij op dat de rijnummers en kolomnummers in cijfers worden aangegeven en niet met letters. Bijvoorbeeld wanneer we de functie “INDEX(A1;B10;4;2)” opgeeft, zal Excel de waarde uit cel B4 weergeven.

VERGELIJKEN(zoekwaarde;zoeken_matrix;[criteriumtype_getal])

Met VERGELIJKEN kun je de positie bepalen van een bepaalde waarde in een vooraf vastgesteld bereik. Bij zoekwaarde geef je logischerwijs de waarde op waar je naar op zoek bent. Let hierbij wel op dat als je naar tekst zoekt, je deze wel tussen dubbele aanhalingstekens plaatst.

Vervolgens geef je het bereik op waarin je wilt zoeken, dit kan een rij of een kolom zijn. Als laatste vul je bij criteriumtype getal de waarde 0 in. Wanneer de functie ingevuld is als “VERGELIJKEN(“conversies”;1:1;0)” en als conversies dan de titel is van kolom E, dan zal deze functie de waarde 5 geven.

Het combineren van deze functies geeft de mogelijkheid een gehele tabel te doorzoeken zonder dat de volgorde van de kolommen of rijen nog uitmaakt. Een voorbeeld van deze functie is als volgt:

=INDEX(A1:B13;VERGELIJKEN(“Campagne01”;A:A;0);VERGELIJKEN(“Conversies”;1:1;0))

De combinatie van deze twee functies geeft nu voor Campagne01 het aantal conversies weer, ongeacht of de gezochte waarde nu links of rechts in een kolom staat van de oorspronkelijke zoekwaarde.

Wanneer te gebruiken?

De combinatie van de functies INDEX en VERGELIJKEN gebruik je als je zoekt naar een waarde die niet per se rechts staat van de overeenkomende zoekwaarde. Met verticaal zoeken is het eenvoudig zoeken naar een waarde die hoort bij de zoekwaarde, maar naar alle informatie die links van deze zoekwaarde staat, kan niet gezocht worden. Een praktisch voorbeeld hiervan is een export van AdWords-data. Als je zoekt naar een campagnenaam en het bijbehorende aantal klikken, is dit met verticaal zoeken geen probleem.

Als je in bovenstaande tabel echter bij Campagne A de campagnestatus wilt zoeken, is dit met verticaal zoeken níet mogelijk. Door gebruik te maken van INDEX en VERGELIJKEN kun je wel links van de zoekwaarde zoeken en zien welke campagne is ingeschakeld of gepauzeerd.