Er valt veel meer over Excel te vertellen dan hier mogelijk is. Ik deel eerst een tweetal bestanden waarin een aantal nuttige trucjes en functies worden behandeld. Tevens vind u hier een bestand met tips om beter om te gaan met de vertikaal zoekfuntionaliteit. Ik leg deze hier niet uit, maar ga ervan uit dat u daar al kennis mee heeft gemaakt.

vertikaal zoeken

In het bestand laat ik zien hoe u die irritante foutmeldingen (#N/B) vervangt door nullen zodat u snel de resultaten kolom kunt optellen. Daarnaast laat ik zien waarom het vaak goed is een vertikaal zoekfunctie op een draaitabel los te laten, ga ik in op de gevaren van niet unieke zoekwaardes, laat zien hoe u deze kunt opsporen en hoe u eenvoudig toch nog unieke zoekwaardes (sleutels) kunt maken waardoor u toch de vertikaal zoekfunctie kunt gebruiken. Ook in gevallen waar u dat eerst wellicht onmogelijk achtte. Daarnaast laat ik zien hoe middels afronden u irritante verschillen niet als verschil meeneemt in uw vertikaal zoekroutine. Ik sluit af met een drietal valkuilen waar u voor dient te waken.

Excel Etiquette

Heel in het kort behandel ik hier de omgangsnormen betrekking hebbende op Excel. Stel, u zit wat lager in de organisatie hark en u wenst dat uw bestanden geaccepteerd worden door de accountant dan wel uw controller. Dan dienen uw bestanden netjes gestructureerd te zijn en ook reproduceerbaar. Ik behandel eerst bestanden die u voor uw externe dan wel interne accountant dient op te stellen om inzicht in bijvoorbeeld balansposten te verschaffen. Uw bronbestand dient altijd helder te zijn en bij voorkeur reproduceerbaar. Stel, het is een Oracle rapport. U maakt een screenshot van de submissie parameters met ook de naam van het rapport zichtbaar en slaat dit in Excel op onder het tabje "screenshot" of submissie parameters" oid. Daarna slaat u de uitkomst van het Oracle bestand op als tekst. Of als het een r12 rapport is dat u als Excel output kan krijgen, als Excel output. Eventueel indien mogelijk draait u het rapport ook nog even in pdf mode en slaat u dat ook op. Nu ligt uw vertrekpunt vast. En is dit voor de accountant inzichtelijk, controleerbaar en reproduceerbaar. Nu maakt u een tabje in het werkboek waarin u het onbewerkte rapport in Excel importeert. Zorg dat als het een txt output is dat is geimporteerd, dat er nog niets verdeeld is over kolommen. Dit tabblad noemt u "ruwe output", "origineel" of wat dan ook. Dit kopieert u naar een tabblad dat u "stap1" noemt. U maakt nu middels verschillende tabbladen (stap2, stap3 etc) de verschillende Excel stappen die u uitvoert inzichtelijk. Zodat de stappen stuk voor stuk te controleren zijn. Daarna komt u uiteindelijk tot uw resultaat blad. Dit kan een tabel zijn waarop u middels bijvoorbeeld draaitabellen het vereiste inzicht geeft. Noem dan deze tabel bijvoorbeeld "DATA" of "bron draaitabel" of iets dergelijks. De conclusie blijkt uit het blad "conclusie" of "analyse". U bent er nu nog niet, u maakt ook nog een blad "validatie" waarmee u uw bestandsvalidatie aantoont. Dus de totaaltelling van het ruwe onbewerkte rapport vergelijkt u met het uiteindelijke resultaat in het blad "conclusie". Om het helemaal af te maken zet u doel, werkwijze, wie het wanneer heeft opgesteld en de conclusie nog even kort samengevat op het blad "inleiding" of "bestandswijzer" oid. Daarnaast zorgt u dat de bladen die mogelijk geprint worden er netjes uitkomen en in de voetwijzer de bestandsnaam en bestandslocatie bevatten zodat de soft copy altijd traceerbaar is voor de persoon die de hardcopy in bezit heeft. Ook nummert u natuurlijk de pagina's. En zet u er eventueel ook op voor welk punt van de PBC lijst het is.

Als u bestanden voor uw controller opstelt dan dient een en ander natuurlijk ook inzichtelijk te zijn. Maar die is waarschijnlijk eerder geinteresseerd in het resultaat en eventuele verdere analyse mogelijkheden. Die wil meestal niet al dit tabjes. In beide gevallen geldt overigens dat u geen fouten maakt en zorgt dat u de valkuilen van Excel kent. Controleer altijd uw uitkomst met het bronbestand middels op zijn minst een controle op de totaal telling, maar beter nog een paar steekproeven om te kijken of alle velden goed zijn overgekomen.

Accountants, een woord van waarschuwing. Pas op voor slecht ingerichte datawarehouses of SQL queries! Ik ga ervan uit dat aan de voorafgaande stappen is voldaan (periode afsluiting uitgevoerd en aansluitingen zijn gemaakt). Ook dan is het mogelijk met een slecht ingericht datawarehouse de mist in te gaan. Idem dito een SQL query. Die laatste is immers maar zo goed als de bouwer daarvan. Als u accountant bent, pas hier dan mee op. Het meest betrouwbaar zijn over het algemeen de standaard Oracle rapporten. Ook al zijn deze niet in alle gevallen juist. Het is in ieder geval controleerbaar en naspeelbaar. Bij de opgeleverde SQL queries weet u meestal niet wie die gebouwd heeft en vaak zijn deze ook niet naspeelbaar omdat u de select statements niet meekrijgt, u krijgt na de informatie vraag alleen een tabel terug, de tussenliggende stappen zijn bij veel organisaties nogal vaag en niet gedocumenteerd. Maar opeens is deze tabel dan voor de accountant de waarheid! Een standaard EBS of SAP rapport dat middels controleerbare stappen is omgevormd is dan wellicht beter. Bij het datawarehouse weet u waarschijnlijk niet of het resultaat dat u ziet gebaseerd is op nieuw toegevoegde views of rapportages nadat uw edp auditor is langs geweest. 

ATTENIE:

EXCEL HEEFT EEN SUBOPTIMALE NAAM DOORDAT GEBRUIKERS VAAK NIET DE MOEITE NEMEN HUN FORMULES TE CONTROLEREN OF HUN UITKOMSTEN AAN TE SLUITEN MET HET BRONBESTAND. CONTROLEER ALTIJD UW UITKOMST.