In deze aflevering presenteer ik u weer een aantal interessante vragen die ik heb mogen ontvangen en de daarbij behorende antwoorden. Door deze onderwerpen te bestuderen kunt u uw kennis van Excel verhogen en deze toepassen in de praktijk.

Automatische lijst unieke items zonder macro

Vraag: Elke maand worden transactiegegevens uit het bronbestand naar Excel binnengehaald. Een van deze kolommen bevat de namen van klanten, die meerdere keren kunnen voorkomen. Ik wil automatisch een lijst aanmaken van alle uniek items zonder gebruik van een macro. Is dat mogelijk?

Antwoord: In Excel kunt u gebruikmaken van Power Query. Stel de transactietabel ziet er als volgt uit:

Plaats de cursor in de tabel. Vanuit het lint kiest u: Gegevens => Gegevens ophalen en transformeren => Van tabel/bereik.
Vervolgens ziet u de data in Power Query verschijnen.

U verwijdert de kolommen Datum en Omzet. Markeer met de Control-toets de kolommen Datum en Omzet en druk op de Delete-knop. Klik met de rechtermuisknop op de header Klant en vanuit het verkorte menu kiest u: Dubbele waarden verwijderen. U kunt de lijst met unieke waarden nu overbrengen naar Excel via: Startpagina => Sluiten => Sluiten en Laden => Sluiten en Laden. Het resultaat is een nieuwe tabel in Excel.

Zodra u nieuwe records toevoegt, kunt u de tabel direct vernieuwen door uit het Lint te kiezen: Gegevens => Query’s en Verbindingen => Alles vernieuwen => Alles vernieuwen.

Kruistabel naar langetabel voor draaitabel

Vraag: Ik wil een kruistabel omzetten naar een lange tabel zodat ik op basis daarvan een draaitabel kan maken.

De kopteksten met de maanden bevatten formules. Als ik Power Query ga toepassen voor de conversie worden de formules geconverteerd naar waarden. Is hiervoor een omweg te bedenken?

Antwoord: U kunt Power Query toepassen door een extra regel op te nemen. Kies uit het Lint Gegevens => Gegevens ophalen en transformeren => Van tabel/bereik.

In ons voorbeeld kiezen we een rij extra boven de oorspronkelijke tabel. In Power Query doorloopt u de volgende stappen:
• Kies uit Lint: Transformeren => Tabel => De eerste rij als veldnamen gebruiken.
• Markeer de eerste twee kolommen Rekening en Teken.
• Kies uit het Lint: Transformeren => Alle kolommen => Draaitabel opheffen voor kolommen => Draaitabel opheffen voor andere kolommen.

• Verander de header Kenmerk in Periode en verander het type in Datum/tijd.
• Breng de tabel over naar Excel via Startpagina => Sluiten => Sluiten en Laden => Sluiten en Laden.
De tabel ziet er dan als volgt uit:

Padnaam aansturen in Power Query

Vraag: Hoe kan ik in Power Query de padnaam van het op te halen bestand via een cel aansturen?

Antwoord: Maak een aparte cel waarin u de padnaam en bestandsnaam vermeld en geef deze cel een bereiknaam.

Plaats de cursor op de bewuste cel en kies uit het lint: Gegevens => Gegevens ophalen en transformeren => Van tabel/bereik. U belandt dan in Power Query. Verwijder de 2de en 3de stap in de Query Editor. Klik met de rechtermuisknop op de cel en uit het verkorte menu kiest u Inzoomen.

Daarna kiest u: Startpagina => Sluiten en Laden => Alleen verbinding. U heeft nu een parameter query gemaakt genaamd Filepath, die u in de hoofdquery kunt gebruiken. Daar kunt u via Startpagina => Geavanceerde Query de tekst aanpassen.

Auteur: Tony De Jonker

Dit artikel verscheen eerder in cm: 2019, afl. 2.

Training Interactieve financiële rapportage

Het programma van deze cursus door Tony de Jonker bestaat uit drie dagen, waarvan één dag online (webinar) en twee dagen fysiek op locatie. De eerste dag (2 juni) is een webinar waarbij het basisniveau van alle deelnemers gelijk wordt getrokken zodat u tijdens de fysieke bijeenkomst dezelfde groei kunt maken. Op 16 en 23 juni volgen fysieke trainingsdagen waarin u onder meer een mastersjabloon maakt om een prognosemodel te maken. Neem uw eigen laptop mee om op te werken. Meer informatie vindt u hier of schrijf u direct in!