10 veel voorkomende fouten in Excel – Deel 2

21 december 2010

Iedereen maakt op zijn tijd fouten. Onvermijdelijk overkomt het u als Excel gebruiker ook nog wel eens dat u met de handen in het haar zit. In dit tweede onder gelijke titel verschijnende artikel gaan we in een op een nieuwe reeks van 10 veel voorkomende fouten. Bij de bespreking per fout is steeds de opzet dat voorkomen beter dan genezen is, maar als de fout eenmaal is gemaakt, het ook handig is om te weten hoe een en ander te herstellen.

Lees hier: 10 veel voorkomende fouten in Excel – Deel 1

De volgende veel voorkomende fouten worden in deel 2 behandeld:
1. Een niet sluitende vierkantstelling snel corrigeren
2. Deling door nulwaarde: #DEEL/0 onderdrukken
3. Voorkom met een kleur dat formulecellen per abuis overschreven worden
4. Een uiterste actie om een verminkt bestand te reconstrueren
5. Draaitabelberekeningen: zoveel als mogelijk in de bron opnemen
6. Voorkom ongewenst overschrijven met matrixformules
7. Getransponeerde tabellen dynamisch maken en beveiligen
8. Voorkom verwijsfouten door gebruik van snijpuntoperatoren
9. Geïmporteerde tekstbestanden leiden tot de foutmelding #N/B
10. Het corrigeren van kringverwijzingen

1. Een niet sluitende vierkantstelling snel corrigeren
We hebben in het onderstaande overzicht de maandomzetten getotaliseerd voor vijf provincies. Daarbij hebben we zowel de provincie- als de maandtotalen bepaald door de autosomknop te gebruiken en deze functie eerst van links naar rechts en vervolgens van boven naar onder te kopiëren in respectievelijk B14:F14 en de G2:G14.

Dit leidt in rij 14 tot foutieve totalen, omdat de autosom functie primair van gevulde cellen uitgaat. In kolom B worden via de autosom functie alleen de cellen B5:B13 en in kolom C alleen de cellen C6:C13 opgeteld.

In kolom G is bij de totaaltelling en bij het kopiëren vanaf cel G2 wel alles goed gegaan, hetgeen leidt tot een juiste totaaltelling in cel G14.

Hoe herstellen we deze fout of beter nog hoe voorkomen we dit soort fouten bij vierkanttellingen ?

Welnu: dit is op eenvoudige wijze te realiseren door eerst de totaalkolom G en de totaalrij 14 leeg te maken (of beter nog: vanaf het begin leeg te laten !)

Hierna selecteren we het bereik B2:G14 en drukken op de autosom knop. Vervolgens verschijnt in een keer de correcte vierkantstelling.

__________________________________________________________________________________
Ontdek meer verbluffende en eenvoudige mogelijkheden van Excel:
Cursus Excel 2010
Cursus Effectieve liquiditeitsprognoses met Excel
Cursus VBA Excel
Cursus Interactieve Dashboards met Excel
__________________________________________________________________________________

2. Deling door nulwaarde: #DEEL/0 onderdrukken
Delen door nul is niet toegestaan. Als u dat toch probeert, plaatst Excel onvermijdelijk de #Deel/0” foutwaarde in de cel. Omdat Excel een lege cel eveneens als nulwaarde opvat, krijgt u deze fout ook als u een getal of formule deelt door een ontbrekende celwaarde. Dit is niet alleen storend, maar u kunt bovendien de reeks waarin deze foutwaarde voorkomt, niet optellen.

In het onderstaande voorbeeld hebben we de formule = B8/C8 in cel D8 gekopieerd naar D9:D13

U kunt voorkomen dat deze fout ontstaat, door als volgt met de “ALS” functie te controleren of er een lege waarde voorkomt:

D9 = ALS(C9=0;””;B9/C9).

De functie in D9 is gekopieerd naar het bereik D10:D13

NB:

U kunt ook met de meer algemene “ALS(ISFOUT(…)” constructie werken.
In dit geval nemen we in cel D9 de volgende vergelijking op:

D9 = ALS(ISFOUT(B9/C9);””;B9/C9).

3. Voorkom met een kleur dat formulecellen per abuis overschreven worden
Hoe vaak komt het niet voor dat u onbedoeld een of meerdere cellen overschrijft, inclusief de onderliggende formules? Dit kunt u natuurlijk voorkomen door de betreffende cellen “hard” te beveiligen. Een andere mogelijkheid is om deze cellen duidelijk zichtbaar te maken door ze vooraf te voorzien van een kleur of patroon.

In het bijgaande voorbeeld willen we voorkomen dat de rood gekleurde cellen in kolom D en rij 14 overschreven worden.

Om de cellen (met formules) in het voorbeeld rood gekleurd te krijgen werken we met “Voorwaardelijke opmaak”. Daartoe doorlopen we eerste de volgende voorbereidende stappen:

We kiezen voor Invoegen -> Naam -> Definiëren en voeren in het tekstvak de naam “Formuleincel|” in, met de volgende verwijzing:

Hierna klikken we op “Toevoegen” en “OK”.

Vervolgens selecteren we het cellenbereik dat we willen voorzien van de voorwaardelijke opmaak en kiezen voor Opmaak -> Voorwaardelijke opmaak. We vullen het dialoogscherm met de naam “Formuleincel” als volgt in, inclusief de gewenste (rode) opmaak:

Als we op OK klikken worden de formulecellen voorzien van de gewenste rode kleur.

Toelichting
We maken binnen de formule “Formuleincel” gebruik van de functie CEL.LEZEN, welke onderhuids deel uitmaakt van de in Excel nog steeds aanwezige XLM macro taal (te beschouwen als voorloper van de VBA macrotaal).
In combinatie met de parameterwaarde 48 voor CEL.LEZEN wordt celinformatie terugontvangen (met “WAAR” als de cel een formule bevat en “ONWAAR” indien de cel geen formule bevat). Met de functie INDIRECT() wordt een verwijzing met de cellen in het geselecteerde bereik gemaakt.

4. Een uiterste actie om een verminkt bestand te reconstrueren
Het komt gelukkig niet al te vaak voor, maar desondanks bestaat de mogelijkheid dat een Excel map niet meer op normale wijze is te openen. Om te redden wat er nog te redden valt, kunt u proberen om een via een “koppelingsformule” toegang te krijgen tot het verminkte bestand.

Stel dat we dit als voorbeeld trachten te doen bij het bestand “Verminktbestand.xls”, dan leggen we in een nieuw geopend bestand de volgende link met werkblad 1 (cel A1) van het verminkte bestand:

= [Verminktbestand.xls]Blad1!A1

De inhoud van het werkblad kunnen we reconstrueren door deze formule naar rechts en naar beneden te kopiëren.

5. Draaitabelberekeningen: zoveel als mogelijk in de bron opnemen
Als u met draaitabellen werkt bent u er ongetwijfeld wel eens mee geconfronteerd:
omdat het in de draaitabel zelf niet mogelijk is om een berekening uit te voeren, probeert u dit snel even in een van de naastgelegen kolommen.

In de onderstaande draaitabel hebben we dit gedaan in kolom D, waarbij we de omzet in kolom C hebben vermenigvuldigd met 1,19.

Als we nu bijvoorbeeld alleen de omzetgegevens van filiaal noord en oost willen zien, blijkt er weinig meer van kolom D over te blijven. De gegevens buiten de draaitabel blijken statisch en veranderen niet mee met de gewijzigde draaitabel.

Het is daarom aan te bevelen om bij dit soort gevallen een extra (reken)veld op te nemen in de bron van de draaitabel zelf.

Dat ziet er dan in een uitgebreide draaitabel, als volgt uit:

Een andere mogelijkheid is om te werken met een “berekend veld”. Een “berekend veld” kan binnen een draaitabel worden opgenomen via de keuze Draaitabel > Formules > berekend veld.

6. Voorkom ongewenst overschrijven met matrixformules

De onderstaande figuur toont een eenvoudig voorbeeld voor het bijhouden van de omzet voor een viertal filialen. Ter berekening van de bedragen in kolom D, zou u normaliter als volgt de deelberekeningen uitvoeren:

D2= B2*C2
D3=B3*C3
D4=B4*C4
D5=B5*C5

Ter voorkoming van mogelijke (over)schrijffouten kunt u de deelberekeningen in kolom D vervangen door een matrixformule.

Om zo’n matrixformule te maken, volgt u de volgende stappen:

•   Selecteer het bereik voor de resultaatcellen: in ons voorbeeld is dit D2:D5;
•   Voer in de formulebalk de volgende resultaatformule in: =B2:B5*C2:C5;
•   Omdat het een matrixformule is drukt u tot slot op Ctrl.+Shift+Enter

De formule is nu in één keer ingevoerd in de vier resultaatcellen. Als u nu op een van deze cellen gat staan ziet u tussen accolades de volgende matrixformule verschijnen:
={B2:B5*C2:C5}

Binnen het bereik van deze meercellige matrixformule is het niet mogelijk om een cel te muteren of te wijzigen.

NB:

Om een matrixformule te bewerken, selecteert u alle cellen in het matrixbereik en activeert u de formulebalk. Terwijl u de formule bewerkt verdwijnen de accolades. Als de formule aangepast is drukt u tot slot weer op Ctrl.+Shift+Enter.

7. Getransponeerde tabellen dynamisch maken en beveiligen
U kent naar alle waarschijnlijkheid de mogelijkheid om tabellen te transponeren, of eenvoudig gezegd om van rijen kolommen of vice versa van rijen kolommen te maken. Dat gaat via het Selecteren > Kopiëren > Plakken speciaal > Transponeren. Hiermee heb je een statisch getransponeerde tabel die niet meer gekoppeld is met de oorspronkelijke tabel. En dat is in veel gevallen nu juist wel de bedoeling. Bovendien is het vaak wenselijk dat het getransponeerde deel beveiligd is én blijft tegen overschrijven.

Om dit te bereiken kun je werken met de matrix variant van de functie “transponeren”. Dit hebben we ondertstaand gedaan door eerst het bereik A6:D11 te selecteren, vervolgens de functie =TRANSPONEREN(A1:F4) in te vullen en af te sluiten met de toetscombinate Ctrl + Shift + Enter. Met deze laatste actie maak je er een gekoppelde én tevens tegen overschrijven beveiligde matrix van. Dit blijkt als je op een van de cellen in het gebied gaat staan: de matrixuitdrukking ={TRANSPONEREN(A1:F4)}verschijnt.

8. Voorkom verwijsfouten door gebruik van snijpuntoperatoren
Als voorbeeld nemen we de onderstaande tabel met de behaalde maandomzetten binnen de drie continenten Europa, Azië en Amerika. Als u wilt verwijzen naar een van de maandomzetten kunt u natuurlijk een harde koppeling maken. De koppeling naar de in maart behaalde omzet binnen Azië wordt bijvoorbeeld direct gelegd met behulp van de formule =C4.

Deze simpele manier van verwijzen voldoet prima zolang het om overzichtelijke tabellen van geringe omvang gaat. Werkt u echter met omvangrijke tabellen en op meerdere werkbladen dan is, ter voorkoming van foute verwijzingen, het gebruik van intuïtief werkende snijpuntformules aan te raden.

Om met snijpuntformules te kunnen werken voorzien we de omzetcellen B2:D13 eerst van namen.
Dat doen we door het bereik A1:D13 te selecteren en vervolgens te kiezen voor Invoegen > Naam > Maken.

In het dialoogvenster dat verschijnt kiezen we vervolgens voor “Bovenste rij” en “Linkerkolom”. Hiermee hebben we onderstaande bereiken in één keer van een naam voorzien.

Met behulp van deze bereiknamen kunnen we eenduidig en foutloos naar deelomzetten verwijzen.
Als we nu bijvoorbeeld =Maart Azie (i.p.v. = C4) invullen verschijnt de bijbehorende maart omzet van Azië in de cel. Een bijkomend voordeel van snijpuntformules is bovendien dat deze ook werken op andere werkbladen. U hoeft dus niet “terug te bladeren” naar het blad waar de tabel zich bevindt, maar kunt de snijpuntformule rechtstreeks inbrengen.

9. Geïmporteerde tekstbestanden leiden tot de foutmelding #N/B
In Excel wordt vaak gewerkt met geïmporteerde gegevensbestanden. Daarbij moet u er alert op zijn dat deze gegevens vaak niet automatisch de juiste getalsopmaak bezitten.

Onderstaand een eenvoudig voorbeeld, waarbij we in de cellen C8 tot en met C11 hebben geprobeerd om met de functie VERT.ZOEKEN() de plaatsnaam op te halen uit het bereik A2:B8. In de cellen C8 tot en met C11 hebben we in dit verband de volgende formules opgenomen:

C8    = VERT.ZOEKEN(B8;$A$2:$B$5;2);
C9    = VERT.ZOEKEN(B9;$A$2:$B$5;2);
C10   = VERT.ZOEKEN(B10;$A$2:$B$5;2);
C11   = VERT.ZOEKEN(B11;$A$2:$B$5;2)

De oorzaak van deze foutmeldingen zit in de cellen A2 tot en met A5, die bij nadere analyse geen getallen maar tekst blijken te bevatten. Als we op een van de driehoekjes klikken dan verschijnt dan ook een volgende foutmelding:

Om de tekstwaarden om te zetten in getalwaarden plaatst u in een willekeurige cel de waarde “1”. Deze waarde kopieert en vermenigvuldigt u met de waarden in het bereik A2:A5.
Dat doet u door te kiezen voor Bewerken > Plakken Speciaal > Vermenigvuldigen.
Als het goed is verschijnt nu wel het juiste resultaat in de cellen C8:C11, en zijn bovendien de groene driehoekjes verdwenen.

10. Het corrigeren van kringverwijzingen
In onderstaande tabel was het de bedoeling om in rij 8 de waarden uit de bovengelegen cellen te sommeren en om deze vervolgens te vermenigvuldigen met de opslagfactor in rij 9. In rij 10 treffen we echter niet het verwachte eindresultaat, maar slechts nulwaarden aan.

In dit soort gevallen kunt u er bijna standaard van uitgaan dat er een of meerdere kringverwijzingen in het spel zijn. Om hier zeker van te zijn kijkt u of er een foutmelding op de statusbalk staat.

In bijgaand voorbeeld is dit de foutmelding:

Deze kringverwijzingen kunt u verwijderen door de onderliggende fout(en) te herstellen.
In het voorbeeld blijkt sprake van de volgende twee kringverwijzingen:

B8 = SOM(B2:B8)
C8 =SOM(C2:C8)

Na herstel zien de twee formules er als volgt uit:

B8 = SOM(B2:B7)
C8 = SOM(C2:C7)

Nu verschijnt wel het gewenste resultaat: