Je kent het misschien wel, je ontvangt een Excelsheet en het eerste wat je denkt is “ojee…”. De look en feel belooft niet veel goeds voor de inhoud. Hoe krijg je dat vertrouwen terug en hoe spoor je eventuele fouten op die de verzender heeft gemaakt?
In het vorige artikel hebben we gekeken naar de mogelijkheden van PowerQuery en zoekfuncties binnen Excel. In dit artikel gaat Koen van Haeften in op kostbare Excel-fouten en komen mogelijkheden om die op te sporen aan bod.
Fouten opsporen en de controle van je sheets
Fouten maken is en blijft menselijk. Grote kans dat de mensen die voor jou aan een Excel zaten te hobbyen fouten gemaakt hebben en die fouten kunnen flink in de papieren lopen. Kleine foutjes kunnen flinke gevolgen hebben. Dat bleek wel toen een knip-en-plak fout een Canadees bedrijf $24.000.000 kostte omdat contracten veel te duur werden ingekocht. Maar ook bij Olympische Spelen kan het misgaan. Tijdens de Olympische Spelen in London werden 10.000 kaartjes verkocht voor niet-bestaande stoelen door een invoerfout. De organisatie had maar één keus en moest de gedupeerden compenseren met kaartjes voor de finalerondes. Geen prettige fout om op je geweten te hebben. In dit artikel lees je meer over 12 grote en kostbare fouten in de Excel-geschiedenis.
Weten hoe je fouten opspoort en je sheets kan controleren is dus zeker van belang. Hieronder een aantal tips:
- Zet de weergave van Excel op “formule weergave” (CTRL-T of CTRL-~ (Engels)). Je kan dan sneller afwijkingen zien. Daarbij krijg je duidelijk alle verwijzingen van de cel te zien die je selecteert.
- Gebruik: Opties -> Formules -> Verwijzingstypen R1-K1. Tezamen met “formule weergave”. Je kan dan goed zien en testen of de formules consistent zijn.
- Gebruik: Start -> Zoeken/Selecteren -> Selecteren Speciaal (F5 -> Speciaal) -> Constanten (bijvoorbeeld). Excel selecteert dan alle cellen die constant zijn. Hierdoor kan je controleren of er geen constante staat waar eigenlijk een formule hoort te zijn.
- Gebruik je draaitabellen? Controleer dan of de bron van de draaitabel verwijst naar het volledige bereik en er geen regels zijn toegevoegd die niet worden meegenomen.
- Heb je koppelingen die niet meer werken, maar kan je ze niet vinden in je werkblad? Vergeet dan niet je Bereiknamen en de regels in je Gegevensvalidatie te controleren. Installeer FINDLINK Manville als ook dit niet helpt.
Tabellen, tips en terugschakelen
Een lijst met alle Excel-tips en tricks zou geen einde kennen. Vandaar dat we er voor dit artikel een aantal hebben geselecteerd die we met jou willen delen. Bijvoorbeeld over het gebruiken van tabellen:
- Maak van alles wat lijkt op een tabel, ook daadwerkelijk een tabel in Excel! (Ctrl + L)
- Tabellen zijn dynamisch, verwijzingen (in formules en draaitabellen) naar de tabel gaan daardoor altijd goed (en beter dan een geselecteerd bereik).
- Geef je tabel altijd een naam. Hierdoor weet je snel en zeker bij formules en draaitabellen dat je het goede bereik hebt. Begin de naam met bijvoorbeeld “tbl” zodat je direct een lijstje met je tabellen krijgt wanneer je formules typt.
Of het zoeken naar tekst, schakelen tussen formules en het opsporen van koppelingen die niet meer werken:
- In ‘if functies’ kan je zoeken naar tekst die “niet gelijk is aan” door <> te gebruiken
- Als je wel eens [Evaluate Formula] gebruikt of een deel van de formule wilt controleren, selecteer dan in je formulebalk het deel wat je wilt controleren en druk op F9. Excel berekent dan dat gedeelte van je formule. Druk op Escape om de berekening weer terug te halen.
- Draaitabellen zijn 6000x sneller in berekeningen dan SUMproduct() of Sum.ifs() etc. op grote datasets. Gebruik dus bij redelijk formaat aan data altijd draaitabellen (die als dataset een tabel hebben).
- Wil je snel schakelen tussen je formule en het (eerste) bereik in je formule? Gebruik CTRL-[. Wil je terugschakelen? gebruik CTRL-G (of F5 -> Enter).
Excel. Als financial kun je er wonderen mee verrichten. Maar alleen bij goed gebruik natuurlijk. Daar gaat deze Whitepaper je bij helpen. We hebben alle praktische tips op een rij gezet om de kracht van Excel maximaal in te zetten. Deze Whitepaper is voor elke financial, van starter tot ervaren kracht. We zien namelijk altijd ruimte voor verbetering, dat zit in ons DNA.