MS Office tips

Dynamisch data ophalen uit een Excel draaitabel

Blog-Excel-.jpg

Hoe zorg je ervoor dat gegevens gevonden blijven worden als je later de opbouw van een draaitabel wijzigt?

Allereerst hebben we een databron nodig, oftewel een lijst met gegevens. Eerste tip: maak van de draaitabel bron eerst een Tabel, en gebruik dan de Tabel als bron voor de draaitabel. Waarom? Omdat een Tabel in Excel een dynamisch bereik is. Als er op een later moment rijen en/of kolommen aan de tabel worden toegevoegd, hoeven we de draaitabel alleen maar te verversen zonder te hoeven nadenken over het feit dat de bron sinds het maken van de draaitabel van formaat is veranderd.

Klik ergens in de lijst, ga naar Invoegen en klik op Tabel en OK.
De lijst wordt nu opgemaakt als ene tabel zoals hieronder weergegeven.

Excel dynamische gegevens draaitabel 1

Klik in de tabel en ga naar Invoegen – Draaitabel. Maak de draaitabel.

Excel dynamische gegevens draaitabel 2 Excel dynamische gegevens draaitabel 3

Dan gaan we een ophaaltabelletje maken:

Excel dynamische gegevens draaitabel 4

In de eerste lege cel gaan we nu de functie maken die uit de draaitabel de gegevens van Breda voor Excel 2003 basis ophaalt. Dit gaat heel eenvoudig door in de cel een “=” teken te typen en in de draaitabel de cel aan te klikken waar de betreffende waarde staat. Excel zal zelf de =DRAAITABEL.OPHALEN functie starten en invullen.

Excel dynamische gegevens draaitabel 5.jpg

Deze functie haalt de waarde uit de draaitabel op, ongeacht in welke cel de waarde terecht komt. Dus als we de opbouw van de draaitabel aanpassen, zal de waarde (zolang deze in de draaitabel voorkomt) gevonden worden.
Deze functie gaan we kopiëren naar de andere 8 cellen. Maar wat we ook willen is dat wanneer de plaatnamen en/of cursusnamen in de ophaal tabel andere waarden krijgen, de gegevens voor die waarden uit de draaitabel worden opgehaald.
We gaan daartoe twee namen aanmaken. Allereerst selecteren we de drie cellen met de cursusnamen, we klikken in het Naamvak en typen “cursus” gevolgd door een Enter. We doen hetzelfde voor de lokaties.

Excel dynamische gegevens draaitabel 6.jpg  Excel dynamische gegevens draaitabel 7

We gaan vervolgens beide namen in de DRAAITABEL.OPHALEN functie gebruiken. De oorspronkelijke functie ziet er zo uit:

Excel dynamische gegevens draaitabel 8

Waar nu “Excel 2003 basis” staat vervangen we deze tekst door de Naam “cursus” en waar nu “Breda” staat, vervangen we de tekst door de naam “Lokatie”. Met de toets F3 kun je het ‘Naam plakken’ dialoogscherm oproepen.

Excel dynamische gegevens draaitabel 9

De functie ziet er dan als volgt uit:

Excel dynamische gegevens draaitabel 10

Kopieer de functie met de vulgreep naar de andere cellen en klaar is Kees. Wanneer we nu in de ophaaltabel een andere cursus en/of locatie invullen, zullen de juiste gegevens automatisch uit de draaitabel worden opgehaald!

Excel dynamische gegevens draaitabel 11

Excel dynamische gegevens draaitabel 12

Bekijk ook onze Tips & Tricks trainingen voor MS Office: www.avk.nl/officetips 


 

Het ‘Venster controle’ is met name handig als de formule gebruik maakt van cellen van meerdere werkbladen. Nadat je van die cellen controle hebt toegevoegd, kun je de voortgang van al deze cellen blijven volgen in dit venster.