Een bereikcontrole geschreven als =0<B2<100 geeft in Excel op elke rij FALSE terug, ook op de rij waar B2 50 bevat. Excel evalueert eerst 0<B2, krijgt TRUE, en stelt dan de vraag of TRUE minder is dan 100; een logische waarde rangschikt boven elk getal, dus het antwoord is FALSE wat B2 ook bevat. Lege cellen en SUMIF hebben verrassingen van dezelfde soort: een lege cel is tegelijk gelijk aan 0, aan "" en aan FALSE, en =SUMIF(A1:A10,">5",B1) telt tien cellen van kolom B bij elkaar op
Een rekenengine die zo'n werkboek buiten Excel om herberekent moet die antwoorden reproduceren, ook wanneer ze duidelijk niet zijn wat de auteur bedoelde. Het gecachte resultaat dat in het bestand staat is wat een viewer, een mailvoorbeeld of een stroomafwaartse import ziet voordat iemand op F9 drukt. Schrijft de server TRUE weg voor de bereikcontrole en toont Excel later FALSE, dan bevat hetzelfde rapport twee verschillende getallen, afhankelijk van waar het werd geopend, en niemand kan zeggen welk van de twee te vertrouwen. De formule van de auteur repareren is de taak van de auteur
Waarom geeft =1<2<3 FALSE terug in Excel?
Excel geeft FALSE terug voor =1<2<3 omdat alle zes vergelijkingsoperatoren één prioriteitsniveau delen en van links naar rechts associëren, dus de formule betekent (1<2)<3, en de binnenste TRUE verliest dan een rangschikkingswedstrijd tegen het getal 3. HotXLS Delphi Component parseert vergelijkingsketens op dezelfde manier in beide werkboekengines, de klassieke TXLSWorkbook en de XLSX-native TXLSXWorkbook. De parser leest één operand, en zolang het volgende token =, <>, <, >, <= of >= is, bouwt hij een vergelijkingsnode met alles wat tot dan toe is geparseerd als linkerkind en de volgende operand als rechterkind, en draagt die node mee naar de volgende ronde. Dat is dezelfde lusvorm die hij gebruikt voor +, - en &
De valkuil zit in de grammatica uit het leerboek. Geschreven als expr = operand op expr, recurseert een recursive-descent-parser in zichzelf voor de rechterkant en maakt de operator stilletjes rechtsassociatief. Onder die lezing wordt =1<2<3 tot 1<(2<3), wat 1<TRUE is, wat TRUE is. De fout loopt in beide richtingen: =3>2>1 is TRUE in Excel en FALSE onder rechtsassociativiteit, en =1=1=TRUE klapt op dezelfde manier om. Tussen haakjes gezette vormen komen hoe dan ook goed uit de bus, dus de fout glipt langs elke test die alleen (a<b)<c controleert
De methode Calculate evalueert een formulestring zonder hem in een cel te zetten, wat hem een snelle manier maakt om een hele lijst ketens tegen beide engines te toetsen. De methode wordt uitvoeriger behandeld in het overzicht van de HotXLS-formule-engine
uses
SysUtils, Variants, lxCalc, lxHandle, lxHandleX; // lxOk wordt gedeclareerd in lxCalc
const
Formulas: array [0..6] of string = ('=1<2<3', '=3>2>1', '=(1<2)<3',
'=1<(2<3)', '=1=1=TRUE', '=3>2>1=TRUE', '=1<2<3=FALSE');
// Antwoorden van Excel: FALSE, TRUE, FALSE, TRUE, TRUE, TRUE, TRUE
var
Classic: IXLSWorkbook;
Xlsx: TXLSXWorkbook;
i: Integer;
begin
Classic := TXLSWorkbook.Create; // op referentietelling via de interface
Xlsx := TXLSXWorkbook.Create;
try
// TXLSXWorkbook.Calculate evalueert tegen het actieve werkblad en
// geeft Null terug wanneer het werkboek nog geen werkblad heeft
Xlsx.Sheets.Add('Data');
for i := 0 to High(Formulas) do
Writeln(Formulas[i], ' classic=', VarToStr(Classic.Calculate(Formulas[i])),
' xlsx=', VarToStr(Xlsx.Calculate(Formulas[i])));
finally
Xlsx.Free;
end;
end;
Getallen, tekst en booleans: de rangschikking over typen heen
Als de twee kanten van een vergelijking verschillende typen hebben, converteert Excel de ene niet in de andere. Hij rangschikt de typen in plaats daarvan: elk getal is minder dan elke tekstwaarde, en elke tekstwaarde is minder dan elke boolean. HotXLS classificeert beide operands als getal, tekst of boolean en vergelijkt, wanneer de klassen verschillen, alleen de klassen. Dus =TRUE>99999 is TRUE, ="1">2 is TRUE, en =FALSE>"zzz" is ook TRUE. Dit is het stuk dat geketende vergelijkingen willekeurig laat aanvoelen. TRUE<3 is geen TRUE die naar 1 wordt gedwongen en met 3 vergeleken; het is een boolean die met een getal wordt vergeleken, en de boolean wint
Binnen één klasse is de vergelijking de natuurlijke, met een addertje voor tekst: tekenreeksvergelijking negeert hoofdletters, dus ="abc"="ABC" geeft TRUE terug in Excel en in HotXLS. Datums zijn serienummers voor de engine, wat betekent dat een datum altijd onder elke tekst rangschikt, ook onder een tekstcel die slechts op een datum lijkt. Een kolom geïmporteerde datums waarin de helft van de cellen als strings aankwam zal sorteren en vergelijken in twee aparte banden, en =A2>DATE(2026,1,1) geeft voor elke stringcel TRUE terug
Waaraan is een lege cel in een vergelijking gelijk?
Een lege cel die als vergelijkingsoperand dient neemt het type van de andere kant aan: hij is gelijk aan 0 tegen een getal, aan "" tegen tekst en aan FALSE tegen een logische waarde. Met A1 leeg zijn =A1=0, =A1="" en =A1=FALSE alle drie TRUE in Excel. HotXLS maakt dezelfde substitutie voordat hij de twee operands rangschikt, bij alle zes vergelijkingsoperatoren en in beide engines. Als beide operands leeg zijn vindt geen substitutie plaats en vergelijken ze gelijk
var
Wb: IXLSWorkbook;
Sh: TXLSWorksheet;
begin
Wb := TXLSWorkbook.Create;
Sh := Wb.Sheets.Add; // het eerste werkblad wordt het actieve
Sh.Range['B1', 'B1'].Value := 5; // A1 wordt met opzet leeg gelaten
Writeln(VarToStr(Wb.Calculate('=A1=0'))); // True
Writeln(VarToStr(Wb.Calculate('=A1=""'))); // True
Writeln(VarToStr(Wb.Calculate('=A1=FALSE'))); // True
Writeln(VarToStr(Wb.Calculate('=A1<B1'))); // True: de lege cel vergelijkt als 0
Writeln(VarToStr(Wb.Calculate('=A1<0'))); // False: 0 ligt niet onder 0
end;
De laatste regel is degene die in de praktijk bijt. Rekenkunde behandelt een lege cel al als 0, dus geeft =A1+1 in elke engine 1. Bij vergelijken gaat de afkorting mis. Een engine die "leeg" als zijn eigen laagste rang houdt, onder elk getal, antwoordt TRUE op =A1<0, en dan bestempelt =IF(A1<0,"overdrawn","ok") elke lege saldocel als overdrawn. Alleen 0 of de lege string substitueren is ook niet genoeg: tegen een boolean zou de lege cel 0 worden, die onder zowel TRUE als FALSE rangschikt, en =A1=FALSE zou op een lege cel FALSE opleveren
Er is een gevolg om rekening mee te houden. Omdat een lege cel tegen een logische waarde als FALSE vergelijkt, kan =A1=FALSE een lege cel niet onderscheiden van een cel die FALSE bevat, in Excel of in HotXLS, en kan =A1=0 leeg niet van nul onderscheiden. Sjablonen die moeten weten of iemand werkelijk een waarde heeft ingevoerd testen beter met ISBLANK(A1). De vorm =A1="" is zwakker, want hij is ook TRUE voor een formule die een lege string teruggeeft
Waarom dekt SUMIF met een sombereik van één cel de hele kolom?
Excel houdt de vorm van het criteriabereik aan en gebruikt het sombereik alleen voor zijn cel linksboven, dus betekent =SUMIF(A1:A10,">5",B1) B1:B10. Veel handgemaakte sjablonen leunen op die stenografie zonder dat hun auteurs het weten. De voor de hand liggende implementatie, die de wandeling afklemt op het kleinste van de twee bereiken, reduceert het voorbeeld tot één enkele test van A1 tegen B1 en geeft 0 terug. HotXLS wandelt het criteriabereik af en leest elke waarde op dezelfde rij- en kolomoffset vanuit de hoek linksboven van het sombereik. AVERAGEIF doorloopt dezelfde wandeling en krijgt dezelfde uitzetting, en een sombereik groter dan het criteriabereik wordt bijgesneden tot de criteriavorm
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
Row: Integer;
begin
Book := TXLSXWorkbook.Create;
try
Sheet := Book.Sheets.Add('Sales');
for Row := 1 to 10 do
begin
Sheet.Cells[Row, 1].Value := Row; // criteriakolom: 1..10
Sheet.Cells[Row, 2].Value := Row * 100; // bedragen: 100..1000
end;
Sheet.Cells[1, 4].Formula := '=SUMIF(A1:A10,">5",B1)'; // sombereik van één cel
Sheet.Cells[2, 4].Formula := '=SUMIF(A1:A10,">5",B1:B10)'; // expliciet sombereik
Sheet.Cells[3, 4].Formula := '=AVERAGEIF(A1:A10,">5",B1)'; // zelfde uitzetting
if Book.Recalculate = lxOk then
// D1 en D2: 4000 (600+700+800+900+1000); D3: 800
Writeln(VarToStr(Sheet.Cells[1, 4].Value), ' ',
VarToStr(Sheet.Cells[2, 4].Value), ' ', VarToStr(Sheet.Cells[3, 4].Value));
finally
Book.Free;
end;
end;
Voor sjablonen die u zelf schrijft: schrijf het volledige sombereik uit. De engine eert de stenografie omdat bestaande bestanden ervan afhangen, maar B1 in een formule leest als één enkele cel voor de volgende persoon die het werkblad onderhoudt, en die persoon zal redelijkerwijs aannemen dat er maar één bedrag wordt opgeteld. Het criterium in het midden wordt als enkele waarde geëvalueerd terwijl de twee buitenste argumenten verwijzingen zijn; die splitsing tussen argumentklassen wordt uitgelegd in het artikel over impliciete intersectie en argumentklassen. Voorwaardelijke totalen die ook gefilterde of verborgen rijen moeten overslaan volgen opnieuw andere regels, behandeld in het artikel over SUBTOTAL en AGGREGATE met verborgen rijen
INDIRECT-verwijzingstijlen en YEARFRAC-basis 0
Twee kleinere functies dragen hetzelfde soort valkuil met zich mee. INDIRECT neemt een optioneel tweede argument, a1, en wanneer dat FALSE is is de tekst een R1C1-verwijzing: =INDIRECT("R2C3",FALSE) leest C2. Een parser die de vlag negeert leest "R2" als kolom R, rij 2, wat een echte cel is, dus de formule geeft een geloofwaardige waarde terug van de verkeerde plek. HotXLS leest de vlag als boolean, als getal (nul betekent FALSE) of als de tekst "FALSE", en zet R1C1-tekst om voordat hij hem oplost, met offsets tussen haken zoals R[1]C[1] gemeten vanaf de cel die de formule bevat. Tekst waar na een geldige verwijzing nog iets overblijft, zoals "B2 junk", geeft #REF! terug in plaats van stilletjes B2 te gebruiken
YEARFRAC met basis 0 gebruikt de US (NASD) 30/360-dagtelling, inclusief de laatste-van-februari-regels die DAYS360 toepast: als beide datums de laatste dag van februari zijn wordt de einddag 30, en daarna wordt een begin op de laatste dag van februari 30. Van 29-2-2024 tot 28-2-2025 levert dat 360 dagen op en een fractie van precies 1. Een kale 30/360-telling die die regels overslaat geeft 359 dagen en 0,99722, en het verschil duikt op in renteopbouw die over het einde van februari loopt
var
Wb: IXLSWorkbook;
Sh: TXLSWorksheet;
begin
Wb := TXLSWorkbook.Create;
Sh := Wb.Sheets.Add;
Sh.Range['C2', 'C2'].Value := 'row 2, column 3';
Writeln(VarToStr(Wb.Calculate('=INDIRECT("R2C3",FALSE)'))); // rij 2, kolom 3
Writeln(VarToStr(Wb.Calculate('=ISERROR(INDIRECT("B2 junk"))'))); // True: #REF!
Writeln(VarToStr(Wb.Calculate(
'=YEARFRAC(DATE(2024,2,29),DATE(2025,2,28),0)'))); // 1
end;
Wanneer u dit soort gedrag documenteert of test, haal de verwachte waarden dan uit Excel zelf en niet uit wat de formule schijnbaar zegt. "1 is minder dan 2 is minder dan 3" suggereert dat =1<2<3 TRUE is, wat precies het antwoord is dat een rechtsassociatieve parser geeft en het omgekeerde van wat Excel teruggeeft. Een korte tabel met formules en de waarden die Excel ernaast toont vangt alle vier de gevallen in dit artikel: ketens, lege cellen, de SUMIF-uitzetting en de februaridagtelling
HotXLS leest, herrekent en schrijft XLS, XLSX, ODS en CSV vanuit Delphi en C++Builder zonder Excel op de machine, en de vergelijkings-, lege-cel- en voorwaardelijke-aggregatieregels hierboven horen bij de rekenengine die beide werkboekklassen delen. De lijst met ondersteunde functies, edities en licentiëring staan op de pagina van de HotXLS Delphi spreadsheet component