Deel 1.3: SERCOM – Enrichment (brondata met veel variatie) Copy
In deze les wordt besproken hoe de gegevens van de verrijkingstabellen automatisch kunnen worden toegevoegd aan de brondata met behulp van formules. Het doel is om de brondata op een dusdanige manier te verrijken zodat de rapportage automatisch kan worden opgesteld middels formules, zonder handmatige tussenkomst. De variabelen die in de rapportage gevraagd worden (bijvoorbeeld “Omzet auto” en “Omzet motor”), dienen dan ook terug te komen in de verrijkte brondata.
Het advies is om de vertaaltabellen op een apart tabblad te zetten, gescheiden van de brondata.
Het is mogelijk om meerdere vertaaltabellen op één tabblad te zetten. Gebruik dan verschillende kleuren zodat in één oogopslag duidelijk is dat het om verschillende vertaaltabellen gaat.
Hieronder een voorbeeld van drie vertaaltabellen op één tabblad:
Stel dat de originele data er als volgt uitziet:
De volgende wensen zijn bekend:
1 Vanuit de controle afdeling is het verzoek gekomen om de brondata zoveel mogelijk te verrijken zodat de data meer inzage geeft en de analyses eenvoudiger worden.
2 De directie wil graag een aparte kolom voor negatieve omzetten van nieuwe producten omdat het kan duiden op fouten in de administratie.
De meest gebruikte formules om brondata met veel variatie te verrijken zijn:
- VERT.ZOEKEN
- VERT.ZOEKEN in combinatie met ALS.FOUT / SPATIES.WISSEN / TEKST
- LINKS/RECHTS of DEEL in combinatie met VERT.ZOEKEN
De volledig verrijkte dataset ziet er dan als volgt uit:
Door de verrijking is de originele dataset uitgebreid van 3 naar 7 kolommen. Grootboekrekeningen en productcodes zijn vertaald in leesbare informatie waarmee de analyse een stuk eenvoudiger wordt. De volgende formules, die reeds zijn behandeld in de gevorderde cursus, zijn hiervoor gebruikt in regel 2:
Kolom D (Productomschrijving): =ALS.FOUT(VERT.ZOEKEN(SPATIES.WISSEN(B2);verrijkingstabel!A:B;2;0);”nieuw product”)
Middels VERT.ZOEKEN wordt de productomschrijving in de verrijkingstabel gezocht die past bij het product. Het is echter mogelijk dat er spaties in het productveld staan. Bovendien dienen producten die ontbreken in de verrijkingstabel gekenmerkt te worden als “nieuw product” hetgeen mogelijk is door middel van de ALS.FOUT formule.
Kolom E (Rapportagecode): = ALS(D2=”Nieuw product”;D2;VERT.ZOEKEN(DEEL(B2;6;1);verrijkingstabel!D:E;2;0))
Wanneer het gaat om een nieuw product wordt het als zodanig bestempeld. Middels VERT.ZOEKEN wordt de rapportagecode in de verrijkingstabel gezocht die past bij de (oude) producten. Op de zesde positie staat een letter die hiervoor bepalend is (de A of de M) waarvoor de DEEL formule kan worden gebruikt.
Kolom F (Grootboekomschrijving): =VERT.ZOEKEN(TEKST(A2;”#”);verrijkingstabel!G:H;2;0)
Middels VERT.ZOEKEN wordt de omschrijving in de verrijkingstabel gezocht die past bij de grootboekrekening. Omdat de verrijkingstabel celeigenschappen “tekst” heeft, dient ervoor gezorgd te worden dat kolom A ook dezelfde celeigenschappen krijgt.
Kolom G (Negatief nieuw): =ALS(EN(D2=”nieuw product”;C2<0);C2;0)
Omdat de directie een aparte kolom wil voor negatieve omzetten van nieuwe producten dient een combinatie van de ALS en EN formule te worden gebruikt.
Nu de brondata verrijkt is middels verrijkingstabellen, kent het Excel bestand 2 tabbladen: Brondata en Verrijkingstabel, zie hieronder. Het is effectief om de verrijkingstabel te isoleren op een apart tabblad. Hierdoor is duidelijk te zien hoe de diverse brondata oprolt naar bepaalde categorieën. Ook is dit eenvoudiger te beveiligen, zie één van de volgende lessen.
Er volgt een opdracht (en uitwerking), zie de groene balken hieronder. De opdracht is genaamd “Brondate met veel variatie”
Klik hier om het opdrachtbestand (a) te downloaden. Sla het bestand op op uw computer en volg de instructies.
Opdracht gereed? Klik hier om uitwerkingsbestand (b) te downloaden en controleer uw gemaakte opdracht.