Excel is zeer breed inzetbaar programma, maar met name de versies voor de 2007 versie hebben enkele eigenaardigheden. Alvorens serieus met Excel te werken is het goed om hiervan op de hoogte te zijn. Het niet kennen van deze valkuilen kan heel veel geld kosten. Zoals ik met de eerste valkuil hoop aan te tonen.
1] pas op met het filteren van rijen en vervolgens verwijderen van cellen
Slechts zelden geven bedrijven toe dat foute beslissingen genomen zijn doordat medewerkers niet over voldoende Excel kennis beschikken. Een uitzondering op deze regel betreft Barclays. Toen ze een bod deden op de assets van Lehman hebben ze de assets in Excel gezet. En de contracten die ze niet wensten over te nemen verborgen. Deze verborgen rijen werden weer zichtbaar toen het Excel bestand als PDF werd opgestuurd. Een assistent heeft daarvoor rijen gefilterd, en verwijderd. Dit ging niet goed en daardoor deed Barclays ook een bod op de niet gewenste contracten. excel error in de pers de motie en de uitspraak
Ik verwijder geen rijen als een autofilter is toegepast. En bij belangrijke bestanden dien je altijd het eindproduct te controleren. Doe je dat niet dan kan het veel geld kosten.
2] autofilter laat alleen de eerste 1000 unieke waardes zien (na 2007 alleen de eerste 10 000)
De autofilter functionaliteit (voor 2007: Data, filter, autofilter, 2007 en 2010 Gegevens, filter) is zeer handig. Middels het drop down menu kan je snel kijken of een waarde voorkomt. Echter let op, de getoonde drop down list van het autofilter bevat slechts de eerste 1000 voorkomende unieke waardes! Dus als u wil controleren of een waarde in een tabel voorkomt, en de lijst is langer dan 1000 unieke waardes, dan is deze functie niet betrouwbaar. Voor 2007 kreeg u ook geen foutmelding dat er meer gegevens waren dan getoond in de drop down lijst. Na 2007 is meer geheugenruimte gealloceerd voor deze functie, in plaats van dat het in een keer goed werd opgelost ziet u nu slechts de eerste tienduizend unieke waardes. Gelukkig is er nu wel de waarschuwingsboodschap "Niet alle items worden weergegeven". Hetgeen in ieder geval een verbetering is ten opzichte van geen waarschuwing en maar duizend items.
3] werkbladen kopieren voor 2007 neemt niet altijd alles mee
Indien meer dan 255 tekens in een cel staan dan werkt voor 2007 de functionaliteit van het kopieren van een werkblad niet, u krijgt gelukkig wel een waarschuwing. De workaround is een nieuw werkblad in te voeren, en dan middels bijvoorbeeld CTRL-A, CTRL-C alles van het te kopieren blad middels CTRL-V in het nieuwe werkblad kopieren.
4] let op het gebruik van de som functie (en de andere functies als aantal, gemiddelde, etcetera) icm autofilter
De som, aantal, gemiddelde en veel andere functies trekken zich niets van een toegepast autofilter aan. Bij een tabel waarop middels het autofilter een selectie is gemaakt, zullen deze functies nog steeds dezelfde uitkomsten laten zien. Terwijl natuurlijk de suggestie gewekt wordt dat het de som is van de zichtbare en getoonde rijen. Dat is dus niet zo. Om dit te voorkomen is er de subtotaal functie, die werkt wel zoals verwacht. De functie subtotaal neemt dus alleen de getoonde waardes mee in de functie. Om te voorkomen dat er een dozijn nieuwe functies aan Excel moesten worden toegevoegd (subtotaalsom, subtotaalaantal, subtotaalgemiddelde, etcetera) heeft men zich beperkt tot de functie subtotaal met een zogenaamd functie argument. Dit wil alleen maar zeggen dat je bij het invoeren van de functie een zogenaamd functie_getal moet ingeven. Dit geeft dan aan of je de somfunctie wil van de subtotaal functie (getal is hier 9), of dat je het aantal waardes wil zien (getal is dan 2) of het gemiddelde (functie getal is dan 1). Dit wijst zich allemaal vanzelf, de lijst met alle mogelijke functiegetallen kan tijdens het invoeren van de functie gemakkelijk worden opgeroepen.
5] opmaak versus gegevenstype
Middels celeigenschappen kan je de weergave van een cel veranderen. Dit verandert niet het gegevens type. Als middels een apostrophe (') is aangegeven dat iets door Excel beschouwd moet worden als tekst en niet als waarde dan wel formule kan je wel honderd keer naar celeigenschappen gaan en zeggen dat de opmaak getal moet zijn. Wat in die cel staat wordt dan nog steeds niet meegenomen als getal of waarde in de verschillende functies (ook niet de subtotaal functies). De manier om deze zaken toch als waarde mee te nemen is het gegevens type te wijzigen. Dit kan het gemakkelijkst door een data, tekst naar kolommen operatie. Hier kan tevens worden aangegeven of iets een amerikaanse getal notatie heeft of niet. Aangegeven kan worden of en wat de duizendtal seperators zijn en wat het decimaal scheidingsteken is. Hier kan ook een datum als datum worden weergegeven. (voor 2007, cel dan wel kolom selecteren (niet meer dan 1 kolom per keer), data, tekst naar kolommen, aste breedte (zorg dat er geen vertikale strepen zijn in dialoog scherm), en dan kan in een volgend dialoog scherm als gegevenstype standaard geselecteerd worden. Voor de datum velden, kies natuurlijk datum. Voor de amerikaanse getalnotaties, die kunnen onder geavanceerd gewijzigd worden, een beetje trial en error laat hier vanzelf zien hoe het werkt.
Risico is hier onder meer dat in een kolom zowel gegevenstype tekst als standaard (getal) voorkomt en dit niet tijdig wordt opgemerkt, waardoor bijvoorbeeld de totaal telling niet klopt. Want deze neemt de cellen met gegevenstype tekst niet mee. Zeker bij grote bestanden bijgewerkt door meerdere gebruikers dan wel 1 gebruiker die niet al te handig is met Excel kan dit voorkomen. Wees hierop bedacht.
6] een draaitabel ververst niet automatisch
Als u enige kennis heeft van draaitabellen dan weet u dat deze betrekking hebben op een onderliggende tabel (of eventueel meerdere onderliggende tabellen). Indien u nu gegevens toevoegt aan de onderliggende tabel dan rekent dat niet automatisch door in de draaitabel. Is waarschijnlijk een bewuste keuze van de ontwerpers omdat verversen van draaitabellen veel geheugen vergt. Dit verversen dient u handmatig te doen door ofwel in de versies voor 2007 op het rode uitroepteken te klikken in de werkbalk draaitabel, ofwel in de latere versies op de 2 groene halfronde pijltjes knop te klikken.
7] pas op met gegevensbereiken bij een draaitabel
Zoals wellicht bekend wordt bij een draaitabel aangegeven wat de onderliggende tabel is met gegevens. U heeft meerdere keuzes. Een van die keuzes is precies de tabel aan te geven, tot en met de laatste regel met gegevens van de betreffende tabel. De andere keuze is tot en met de laatste rij van het Excel blad te selecteren, dan wel indien de kolomkoppen in rij 1 staan de hele kolommen te selecteren. U merkt vanzelf het verschil, indien u onderaan de brontabel gegevens toevoegt is het mogelijk dat in de eerste optie deze niet meegenomen worden. U gaf immers aan dat de draaitabel gaat over de cellen A1 tot en met E100. kortom, de eerste honderd rijen. Voegt u iets toe op rij 101 en verder dan wordt dit niet meegenomen! Ook niet na het verversen van de draaitabel! Tot aan de onderste regel selecteren heeft vaak mijn voorkeur, maar dan kan er niet met totalen gewerkt worden op het bronblad, want die komen dan ook mee in de draaitabel. Wat u ook kiest, wees u bewust van de gevolgen van uw keuze.
8] vertikaal zoeken stopt bij de eerste "hit".
Vertikaal zoeken is geen magische somfunctie. Maar stop bij de eerste overeenkomst en retourneert de daarbij horende waarde. Stel, Robert heeft 3 contributie betalingen gedaan, met vertikaal zoeken vinden we slechts de eerste waarde. Een oplossing hier is te controleren of de sleutel die gebruikt wordt bij vertikaal zoeken uniek is (om het moeilijk te zeggen, of de zoekwaarde in de eerste kolom van de tabelmatrix uniek is, ik heb die benamingen niet verzonnen). Om het iets makkelijker te formuleren, kijk of de sleutelwaarde (bijvoorbeeld de naam Robert), uniek is in het bereik dat in het tweede argument van de vertikaal zoek functie wordt opgegeven. Is dit opzoek gedeelte (tabelmatrix) kolom A tm E dan dient de eerste kolom slechts te bestaan uit unieke waardes. En mag onze Robert daar maar 1 keer voorkomen.
Alternatieven zijn draaitabellen, het zelf maken van sleutels, en som.als functies. Draaitabel. Stel de lijst met contributie betalingen heeft 3 Roberts die allen dezelfde persoon zijn, sommeer dan eerst deze tabel in een draaitabel en maak dan gebruik van de draaitabel bij vertikaal zoeken. Zelf maken van sleutel, de Roberts zijn unieke personen, we hebben 3 Roberts die allen een aparte geboortedatum hebben die ook in de tabel staat. We voegen nu een kolom toe en maken daar middels de functie tekst.samenvoegen een nieuwe sleutel bestaande uit de voornaam en de geboorte datum. Dit doen we in beide tabellen en we kunnen nu vertikaal zoeken gebruiken.
9] vertikaal zoeken is gegevenstype afhankelijk
Een eerder vermeldde valkuil is het gegevenstype.dit speelt met name bij vertikaal zoeken en daarom wordt het apart vermeldt. Te vaak denkt men dat de vertikaal zoekfunctie fout is, maar betreft het het gegevenstype. Vertikaal zoeken wordt vaak gebruikt om 2 bestanden uit verschillende bronsystemen te vergelijken. Voorbeeld, sluiten de gegevens uit het grootboek aan met het datawarehouse. indien Excel de gegevens uit het datawarehouse als tekst interpreteert en de gegevens uit het grootboek als standaard, waarde, dan gaat het natuurlijk niet lukken deze gegevens middels vertikaal zoeken te combineren. Een oplossing is altijd even een data, tekst naar kolommen operatie uit te voeren, in beide bestanden.
10] het gevaar van het kleine zwarte plusje
Op een gegeven moment ontdek je de functionaliteit van het kleine zwarte plusteken. Na het invoegen van een nieuwe kolom wil je bijvoorbeeld een formule doortrekken tot de onderste cel van deze nieuwe kolom. Arbeidsintensief, totdat je ontdekt dat dubbelklikken de formule doortrekt van de cel waar je de rechteronderhoek hebt geselecteerd. Deze copieert dan automatisch de functie door tot naar je hoopt onderaan de tabel. Zodra er echter een blanco niet gevulde cel ter linkerzijde is, stopt het copieren van deze functie echter. Dus het verdient aanbeveling altijd even te controleren of de functie inderdaad tot onderaan is doorgetrokken (even shift, pijltje omlaag als routine inbouwen)
Er zijn nog meer valkuilen die ik hier niet allemaal behandel. En die ik ook niet allemaal ken. Een deel van deze valkuilen is het gevolg van ontwerp keuzes. De moraal van dit verhaal is altijd te zorgen dat je je gevens kan controleren. Kijk op het eind of wat je gedaan hebt nog klopt met waar je bent begonnen.
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.