Berekeningsfout bij het wijzigen van de VERT.ZOEKEN-tabel - Excel-tips

Inhoudsopgave

Er is een vreemde fout die rekenfouten kan veroorzaken in Excel wanneer u wijzigingen aanbrengt in de opzoektabel. Gezien het motto van het Excel-team "Recalc or Die" is, weet ik niet zeker waarom ze deze bug niet zullen patchen.

De onderstaande afbeelding toont een VERT.ZOEKEN-formule in kolom C. Het zoekt het item op in B en retourneert de 4e kolom uit de oranje opzoektabel. Alles is op dit punt in orde.

Een typische VERT.ZOEKEN-functie. Excel is snel dankzij een intelligent herberekeningsalgoritme. In dit geval kiest het algoritme ervoor om de cellen die moeten worden berekend, niet opnieuw te berekenen.

Als iemand per ongeluk een kolom verwijdert of een kolom in de opzoektabel invoegt, gebeurt er iets vreemds.

Voeg kolom H in en het werkblad wordt slechts gedeeltelijk herberekend.

Wat is hier aan de hand? Het lijkt op:

De formule in C2 is afhankelijk van de kolommen F: K, dus wordt deze opnieuw berekend. We hebben het verpest omdat VERT.ZOEKEN nog steeds de 4e kolom van de tabel retourneert. Dit geeft ons kleur in plaats van prijs en zorgt ervoor dat de totaalformule in D2 mislukt.
Als ik nu de Excel Recalc Engine was en als ik bewust was en als ik een persoonlijkheid had, zou ik tegen mezelf kunnen zeggen: "Hmmm. De waarde in C2 is veranderd. Misschien zou ik een andere identieke formule in deze kolom moeten herberekenen." Die gedachte zou me ertoe brengen om C3, C4 en C5 opnieuw te berekenen. Maar Excel herberekent die cellen niet. Het heeft niets te maken met de fout in D2. Zelfs zonder de formule in D2 worden de formules in C3, C4 en C5 op dit moment niet berekend.
Cellen C3, C4 en C5 blijven verkeerd totdat u op Ctrl + alt = "" + Shift + F9 drukt voor een volledige herberekening.

Begrijp me niet verkeerd. Ik ben dol op VERT.ZOEKEN. Maar de mensen die klagen over VERT.ZOEKEN, stellen voor om een VERGELIJKEN als derde argument in VERT.ZOEKEN te gebruiken om deze situatie aan te pakken.

Voeg een overeenkomstformule toe als het derde VERT.ZOEKEN-argument.

Als u de bovenstaande formule gebruikt, zal het herberekeningsprobleem niet verschijnen.

Ik heb het Excel-team op de hoogte gesteld van deze bug, maar vreemd genoeg hebben ze geen prioriteit bij het oplossen van het probleem. Het bestaat al sinds Excel 2010.

Elke vrijdag onderzoek ik een bug of ander visachtig gedrag in Excel.

Excel-gedachte van de dag

Ik heb mijn Excel Master-vrienden om advies over Excel gevraagd. De gedachte van vandaag om na te denken: