Excel-formules werken niet: mogelijke redenen en hoe u het kunt oplossen!

Als je met formules in Excel werkt, krijg je vroeg of laat het probleem dat Excel-formules helemaal niet werken (of het verkeerde resultaat geven).

Hoewel het geweldig zou zijn geweest als er maar een paar mogelijke redenen waren geweest voor het slecht functioneren van formules. Helaas zijn er teveel dingen die fout kunnen gaan (en vaak ook).

Maar aangezien we in een wereld leven die het Pareto-principe volgt, zal het waarschijnlijk 80% (of misschien zelfs 90% of 95% van de problemen waarbij formules niet werken in Excel) oplossen als u enkele veelvoorkomende problemen controleert.

En in dit artikel zal ik de veelvoorkomende problemen benadrukken die waarschijnlijk de oorzaak zijn van uw Excel-formules werken niet.

Dus laten we beginnen!

Onjuiste syntaxis van de functie

Laat ik beginnen met te wijzen op het voor de hand liggende.

Elke functie in Excel heeft een specifieke syntaxis, zoals het aantal argumenten dat het kan hebben of het type argumenten dat het kan accepteren.

En in veel gevallen kan de reden dat uw Excel-formules niet werken of het verkeerde resultaat opleveren, een onjuist argument zijn (of ontbrekende argumenten).

De functie VERT.ZOEKEN heeft bijvoorbeeld drie verplichte argumenten en één optioneel argument.

Als u een verkeerd argument opgeeft of het optionele argument niet specificeert (waar het nodig is om de formule te laten werken), krijgt u een verkeerd resultaat.

Stel dat u een dataset heeft zoals hieronder weergegeven, waarbij u de score van Mark in Exam 2 (in cel F2) moet weten.

Als ik de onderstaande formule gebruik, krijg ik het verkeerde resultaat, omdat ik de verkeerde waarde gebruik in het derde argument (een dat om kolomindexnummer vraagt).

=VERT.ZOEKEN(A2,A2:C6,2,FALSE)

In dit geval berekent de formule (omdat deze een waarde retourneert), maar het resultaat is onjuist (in plaats van de score in examen 2, geeft het de score in examen 1).

Een ander voorbeeld waarbij u voorzichtig moet zijn met de argumenten, is wanneer u VERT.ZOEKEN gebruikt met de geschatte overeenkomst.

Aangezien u een optioneel argument moet gebruiken om aan te geven waar u wilt dat VERT.ZOEKEN een exacte overeenkomst of een geschatte overeenkomst moet doen, kan het niet specificeren hiervan (of het gebruiken van het verkeerde argument) problemen veroorzaken.

Hieronder ziet u een voorbeeld waarbij ik de cijfergegevens voor sommige studenten heb en ik de cijfers in Examen 1 wil extraheren voor de studenten in de tabel aan de rechterkant.

Wanneer ik de onderstaande VERT.ZOEKEN-formule gebruik, krijg ik een foutmelding voor sommige namen.

=VERT.ZOEKEN(E2,$A$2:$C$6,2)

Dit gebeurt omdat ik het laatste argument niet heb gespecificeerd (dat wordt gebruikt om te bepalen of een exacte match of geschatte match moet worden gedaan). Als u het laatste argument niet opgeeft, wordt standaard automatisch een benaderende overeenkomst uitgevoerd.

Omdat we in dit geval een exacte overeenkomst moesten doen, retourneert de formule een fout voor sommige namen.

Hoewel ik het voorbeeld van de functie VERT.ZOEKEN heb genomen, is dit in dit geval iets dat van toepassing kan zijn op veel Excel-formules die ook optionele argumenten hebben.

Lees ook: Fouten identificeren met behulp van foutopsporing in Excel-formules

Extra spaties die onverwachte resultaten veroorzaken

Voorloop- en volgspaties zijn moeilijk te achterhalen en kunnen problemen veroorzaken wanneer u een cel gebruikt die deze in formules heeft.

Als ik in het onderstaande voorbeeld bijvoorbeeld VERT.ZOEKEN probeer te gebruiken om de score voor Mark op te halen, krijg ik de fout #N/A (de niet-beschikbare fout).

Hoewel ik kan zien dat de formule correct is en dat de naam 'Mark' duidelijk de lijst is, kan ik niet zien dat er een volgspatie in de cel staat met de naam (in cel D2).

Excel beschouwt de inhoud van deze twee cellen niet als hetzelfde en beschouwt het daarom als een mismatch bij het ophalen van de waarde met VERT.ZOEKEN (of het kan een andere opzoekformule zijn).

Om dit probleem op te lossen, moet u deze extra spatietekens verwijderen.

U kunt dit doen door een van de volgende methoden te gebruiken:

1. Reinig de cel en verwijder alle voorloop-/naloopspaties voordat u deze in formules gebruikt
2. Gebruik de TRIM-functie in de formule om ervoor te zorgen dat voorloop-/achteraf-/dubbele spaties worden genegeerd.

In het bovenstaande voorbeeld kunt u in plaats daarvan de onderstaande formule gebruiken om te controleren of deze werkt.

=VERT.ZOEKEN(TRIM(D2),$A$2:$B$6,2,0)

Hoewel ik het voorbeeld VERT.ZOEKEN heb genomen, is dit ook een veelvoorkomend probleem bij het werken met TEKST-functies.

Als ik bijvoorbeeld de LEN-functie gebruik om het totale aantal tekens in een cel te tellen, als er voorloop- of volgspaties zijn, worden deze ook geteld en geven ze het verkeerde resultaat.

Meenemen / Hoe te repareren: Maak indien mogelijk uw gegevens schoon door voorloop-/achterloop- of dubbele spaties te verwijderen voordat u deze in formules gebruikt. Als u de oorspronkelijke gegevens niet kunt wijzigen, gebruikt u de TRIM-functie in formules om dit te regelen.

Handmatige berekening gebruiken in plaats van automatisch

Deze ene instelling kan je gek maken (als je niet weet dat dit alle problemen veroorzaakt).

Excel heeft twee berekeningsmodi - automatisch en Handleiding.

Standaard is de automatische modus ingeschakeld, wat betekent dat als ik een formule gebruik of wijzigingen aanbreng in de bestaande formules, deze automatisch (en onmiddellijk) de berekening maakt en mij het resultaat geeft.

Dit is de setting die we allemaal gewend zijn.

In de automatische instelling, telkens wanneer u een wijziging aanbrengt in het werkblad (zoals het invoeren van een nieuwe formule tot zelfs wat tekst in een cel), berekent Excel automatisch alles opnieuw (ja, alles).

Maar in sommige gevallen schakelen mensen de handmatige berekeningsinstelling in.

Dit wordt meestal gedaan als je een zwaar Excel-bestand hebt met veel gegevens en formules. In dergelijke gevallen wilt u misschien niet dat Excel alles opnieuw berekent wanneer u kleine wijzigingen aanbrengt (aangezien het enkele seconden of zelfs minuten kan duren) voordat deze herberekening is voltooid.

Als u handmatige berekening inschakelt, zal Excel niet berekenen, tenzij u dit forceert.

En dit kan u doen denken dat uw formule niet berekent.

Het enige dat u in dit geval hoeft te doen, is de berekening terugzetten op automatisch of een herberekening forceren door op de F9-toets te drukken.

Hieronder vindt u de stappen om de berekening van handmatig naar automatisch te wijzigen:

1. Klik op het tabblad Formule
2. Klik op Berekeningsopties
3. Selecteer Automatisch

Belangrijk: als u de berekening wijzigt van handmatig naar automatisch, is het een goed idee om een ​​back-up van uw werkmap te maken (voor het geval uw werkmap hierdoor vastloopt of Excel crasht)

Meenemen / Hoe te repareren: Als u merkt dat uw formules niet het verwachte resultaat geven, probeer dan iets eenvoudigs in een cel (zoals het toevoegen van 1 aan een bestaande formule. Zodra u het probleem identificeert als het probleem waarbij de berekeningsmodus moet worden gewijzigd, voert u een krachtberekening uit door F9 te gebruiken.

Rijen/kolommen/cellen verwijderen die leiden tot #REF! Fout

Een van de dingen die een verwoestend effect kunnen hebben op uw bestaande formules in Excel, is wanneer u een rij/kolom verwijdert die in berekeningen is gebruikt.

Wanneer dit gebeurt, past Excel soms de verwijzing zelf aan en zorgt ervoor dat de formules goed werken.

En soms… het kan niet.

Gelukkig is een duidelijke indicatie die je krijgt wanneer formules breken bij het verwijderen van cellen/rijen/kolommen de #REF! fout in de cellen. Dit is een verwijzingsfout die aangeeft dat er een probleem is met de verwijzingen in de formule.

Laat me je laten zien wat ik bedoel met een voorbeeld.

Hieronder heb ik de SOM-formule gebruikt om de cellen A2:A6 toe te voegen.

Als ik nu een van deze cellen/rijen verwijder, retourneert de SUM-formule een #REF! fout. Dit gebeurt omdat toen ik de rij verwijderde, de formule niet weet waar hij nu naar moet verwijzen.

Je kunt zien dat het derde argument in de formule #REF is geworden! (die eerder verwees naar de cel die we hebben verwijderd).

Meenemen / Hoe te repareren: Voordat u gegevens verwijdert die in formules worden gebruikt, moet u ervoor zorgen dat er geen fouten zijn na het verwijderen. Het is ook aan te raden om regelmatig een back-up van je werk te maken, zodat je altijd iets hebt om op terug te vallen.

Onjuiste plaatsing van haakjes (BODMAS)

Naarmate uw formules groter en complexer worden, is het een goed idee om haakjes te gebruiken om absoluut duidelijk te zijn welk deel bij elkaar hoort.

In sommige gevallen is het mogelijk dat de haakjes op de verkeerde plaats staan, waardoor u een verkeerd resultaat of een fout kunt krijgen.

En in sommige gevallen wordt aanbevolen om haakjes te gebruiken om ervoor te zorgen dat de formule begrijpt wat er eerst moet worden gegroepeerd en berekend.

Stel dat u bijvoorbeeld de volgende formule heeft:

=5+10*50

In de bovenstaande formule is het resultaat 505, aangezien Excel eerst de vermenigvuldiging doet en vervolgens de optelling (omdat er een rangorde is als het gaat om operators).

Als u wilt dat het eerst de optelling en vervolgens de vermenigvuldiging doet, moet u de onderstaande formule gebruiken:

=(5+10)*50

In sommige gevallen kan de volgorde van prioriteit voor u werken, maar het wordt toch aanbevolen om de haakjes te gebruiken om verwarring te voorkomen.

Als u geïnteresseerd bent, vindt u hieronder ook de volgorde van prioriteit voor verschillende operators die vaak in formules worden gebruikt:

Operator	Beschrijving	Volgorde van voorrang
: (dubbele punt)	Bereik	1
(enkele ruimte)	Kruispunt	2
, (komma)	unie	3
-	Negatie (zoals in -1)	4
%	Percentage	5
^	Machtsverheffing	6
* en /	Vermenigvuldigen en delen	7
+ en -	Optellen & aftrekken	8
&	aaneenschakeling	9
= =	Vergelijking	10

Meenemen / Hoe te repareren: Gebruik altijd haakjes om verwarring te voorkomen, zelfs als u de volgorde van prioriteit kent en deze correct gebruikt. Het hebben van haakjes maakt het gemakkelijker om formules te controleren.

Onjuist gebruik van absolute/relatieve celverwijzingen

Wanneer u formules kopieert en plakt in Excel, worden de verwijzingen automatisch aangepast. Soms is dit precies wat u wilt (meestal wanneer u formules in de kolom kopieert en plakt), en soms wilt u niet dat dit gebeurt.

Een absolute verwijzing is wanneer u een celverwijzing (of bereikverwijzing) corrigeert zodat deze niet verandert wanneer u formules kopieert en plakt, en een relatieve verwijzing is er een die verandert.

U kunt hier meer lezen over absolute, relatieve en gemengde verwijzingen.

U kunt een onjuist resultaat krijgen als u vergeet de verwijzing naar een absoluut te wijzigen (of omgekeerd). Dit is iets dat me vaak overkomt als ik opzoekformules gebruik.

Laat me je een voorbeeld laten zien.

Hieronder heb ik een dataset waar ik de score in examen 1 wil ophalen voor de namen in kolom E (een eenvoudige use-case voor VERT.ZOEKEN)

Hieronder staat de formule die ik gebruik in cel F2 en vervolgens kopieer naar alle cellen eronder:

=VERT.ZOEKEN(E2,A2:B6,2,0)

Zoals u kunt zien, geeft deze formule in sommige gevallen een fout.

Dit gebeurt omdat ik het array-argument van de tabel niet heb vergrendeld - het is A2:B6 in cel F2, terwijl het had moeten zijn $A$2:$B$6

Door deze dollartekens vóór het rijnummer en het kolomalfabet in een celverwijzing te plaatsen, dwing ik Excel om deze celverwijzingen vast te houden. Dus zelfs als ik deze formule kopieer, blijft de tabelarray verwijzen naar A2:B6

Pro-tip: om een ​​relatieve verwijzing naar een absolute te converteren, selecteert u die celverwijzing in de cel en drukt u op de F4-toets. Je zou merken dat het verandert door de dollartekens toe te voegen. U kunt op F4 blijven drukken totdat u de gewenste referentie krijgt.

Onjuiste verwijzing naar blad-/werkmapnamen

Wanneer u in een formule naar andere werkbladen of werkmappen verwijst, moet u een specifiek formaat volgen. En in het geval dat het formaat onjuist is, krijgt u een foutmelding.

Als ik bijvoorbeeld naar cel A1 in Blad2 wil verwijzen, zou de verwijzing zijn: =Blad2!A1 (waar een uitroepteken achter de bladnaam staat)

En als er meerdere woorden in de bladnaam staan ​​(laten we zeggen dat het voorbeeldgegevens zijn), zou de verwijzing zijn: ='Voorbeeldgegevens'!A1 (waarbij de naam tussen enkele aanhalingstekens staat, gevolgd door een uitroepteken).

Als u verwijst naar een externe werkmap (laten we zeggen dat u verwijst naar cel A1 in 'Voorbeeldblad' in de werkmap met de naam 'Voorbeeldwerkmap'), is de verwijzing zoals hieronder weergegeven:

='[Voorbeeldwerkmap.xlsx]Voorbeeldblad'!$A$1

En in het geval dat u de werkmap sluit, zou de verwijzing veranderen om het volledige pad van de werkmap op te nemen (zoals hieronder weergegeven):

='C:\Users\sumit\Desktop\[Voorbeeldwerkmap.xlsx]Voorbeeldblad'!$A$1

Als u uiteindelijk de naam van de werkmap of het werkblad waarnaar de formule verwijst, wijzigt, krijgt u een #REF! fout.

Lees ook: Externe links en verwijzingen vinden in Excel

Circulaire referenties

Een kringverwijzing is wanneer u (direct of indirect) verwijst naar dezelfde cel waar de formule wordt berekend.

Hieronder staat een eenvoudig voorbeeld, waarbij ik de SOM-formule in cel A4 gebruik terwijl ik deze in de berekening zelf gebruik.

=SOM(A1:A4)

Hoewel Excel u een prompt toont om u op de hoogte te stellen van de kringverwijzing, zal dit niet voor elke instantie worden gedaan. En dit kan u het verkeerde resultaat geven (zonder enige waarschuwing).

Als u vermoedt dat er een kringverwijzing in het spel is, kunt u controleren in welke cellen deze staat.

Klik hiervoor op het tabblad Formule en klik in de groep 'Formulecontrole' op het vervolgkeuzepictogram Foutcontrole (de kleine naar beneden wijzende pijl).

Beweeg de cursor over de optie Cirkelverwijzing en u ziet de cel met het probleem met de cirkelverwijzing.

Cellen opgemaakt als tekst

Als u zich in een situatie bevindt waarin zodra u de formule typt als hit enter, u de formule ziet in plaats van de waarde, het is duidelijk dat de cel is opgemaakt als tekst.

Wanneer een cel is opgemaakt als tekst, beschouwt deze de formule als een tekenreeks en toont deze zoals deze is.

Het dwingt het niet om te berekenen en het resultaat te geven.

En het heeft een gemakkelijke oplossing.

1. Wijzig het formaat in 'Algemeen' van 'Tekst' (het staat op het tabblad Start in de groep Nummers)
2. Ga naar de cel met de formule, ga naar de bewerkingsmodus (gebruik F2 of dubbelklik op de cel) en druk op Enter

Als de bovenstaande stappen het probleem niet oplossen, is een ander ding om te controleren of de cel aan het begin een apostrof heeft. Veel mensen voegen een apostrof toe om formules en getallen om te zetten in tekst.

Als er een apostrof is, kunt u deze eenvoudig verwijderen.

Tekst wordt automatisch omgezet in datums

Excel heeft de slechte gewoonte om een ​​datum die eruitziet als een datum om te zetten in een werkelijke datum. Als u bijvoorbeeld 1/1 invoert, converteert Excel dit naar 01-jan van het huidige jaar.

In sommige gevallen kan dit precies zijn wat u wilt, en in sommige gevallen kan dit tegen u werken.

En aangezien Excel datum- en tijdwaarden als getallen opslaat, wordt het, zodra u 1/1 invoert, omgezet in een getal dat 1 januari van het lopende jaar vertegenwoordigt. In mijn geval, als ik dit doe, wordt het omgezet in het nummer 43831 (voor 01-01-2020).

Dit kan uw formules in de war brengen als u deze cellen als argument in een formule gebruikt.

Hoe dit op te lossen?

Nogmaals, we willen niet dat Excel automatisch het formaat voor ons kiest, dus we moeten het formaat zelf duidelijk specificeren.

Hieronder vindt u de stappen om de opmaak in tekst te wijzigen, zodat tekst niet automatisch naar datums wordt geconverteerd:

1. Selecteer de cellen/het bereik waar u de opmaak wilt wijzigen
2. Klik op het tabblad Start
3. Klik in de groep Nummer op de vervolgkeuzelijst Opmaak
4. Klik op Tekst

Wanneer u nu iets in de geselecteerde cellen invoert, wordt het als tekst beschouwd en niet automatisch gewijzigd.

Opmerking: de bovenstaande stappen werken alleen voor gegevens die zijn ingevoerd nadat de opmaak is gewijzigd. Het zal geen tekst wijzigen die tot op heden is geconverteerd voordat u deze opmaakwijziging aanbracht.

Een ander voorbeeld waarbij dit erg frustrerend kan zijn, is wanneer u een tekst/nummer invoert met voorloopnullen. Excel verwijdert deze voorloopnullen automatisch omdat deze als nutteloos worden beschouwd. Als u bijvoorbeeld 0001 in een cel invoert, verandert Excel dit in 1. Als u deze voorloopnullen wilt behouden, gebruikt u de bovenstaande stappen.

Verborgen rijen/kolommen kunnen onverwachte resultaten opleveren

Dit is geen geval van een formule die u het verkeerde resultaat geeft, maar van het gebruik van de verkeerde formule.

Stel dat u bijvoorbeeld een gegevensset hebt zoals hieronder weergegeven en dat ik de som wil krijgen van alle zichtbare cellen in kolom C.

In cel C12 heb ik de SOM-functie gebruikt om de totale verkoopwaarde voor al deze gegeven records te krijgen.

Tot zover goed!

Nu pas ik een filter toe op de itemkolom om alleen de records voor printerverkopen weer te geven.

En hier is het probleem - de formule in cel C12 geeft nog steeds hetzelfde resultaat - d.w.z. de som van alle records.

Zoals ik al zei, geeft de formule niet het verkeerde resultaat. In feite werkt de SOM-functie prima.

Het probleem is dat we hier de verkeerde formule hebben gebruikt.

De SOM-functie kan geen rekening houden met de gefilterde gegevens en geeft u het resultaat voor alle cellen (verborgen of zichtbaar). Als u alleen de som/telling/gemiddelde van zichtbare cellen wilt krijgen, gebruikt u de functies SUBTOTAAL of AGGREGATE.

Belangrijkste afhaalmaaltijden - Begrijp het juiste gebruik en de beperkingen van een functie in Excel.

Dit zijn enkele van de veelvoorkomende oorzaken die ik heb gezien waarbij uw Excel-formules werken mogelijk niet of geven onverwachte of verkeerde resultaten. Ik weet zeker dat er nog veel meer redenen zijn waarom een ​​Excel-formule niet werkt of bijwerkt.

Als je een andere reden kent (misschien iets dat je vaak irriteert), laat het me dan weten in de comments.

Ik hoop dat je deze tutorial nuttig vond!