Technisch artikel

Vergelijkingsketens, lege cellen en SUMIF in HotXLS Delphi

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;
Twee parsebomen voor =1<2<3: een rechtsassociatieve parse evalueert 1<(2<3) als TRUE, terwijl het van links naar rechts samenvouwen (1<2)<3 als FALSE evalueert, want Excel rangschikt elk getal onder tekst en elke tekstwaarde onder een boolean
Beide HotXLS-engines vouwen vergelijkingsketens van links naar rechts — een boolean gaat boven elk getal, dus TRUE die van 3 verliest is precies wat de geketende bereikcontrole FALSE maakt

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

Substitutie van lege operands in HotXLS: een lege A1 vergelijkt gelijk aan 0 en aan lege tekst, een lege cel tegen een boolean vergelijkt als FALSE zodat =A1=FALSE TRUE is zoals in Excel, terwijl een lege cel die als laagste rang wordt gehouden =A1<0 TRUE zou maken voor elke lege saldo
De substitutie matcht het type van de andere operand, 0, de lege string of FALSE — een kale Null-rangschikking is wat elke lege saldo als overdrawn zou bestempelen

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;
HotXLS SUMIF en AVERAGEIF zetten uit waar =SUMIF(A1:A10,">5",B1) het criteriabereik van tien rijen doorloopt en B1 tot B10 op passende offsets leest voor een resultaat van 4000, terwijl afklemmen op het sombereik van één cel 0 zou teruggeven
Excel leent alleen de hoek linksboven van het sombereik en houdt de criteriavorm aan, dus een sjabloon dat B1 doorgeeft bedoelt B1:B10 — SUMIF en AVERAGEIF doorlopen alle tien offsets en snijden een te groot bereik op dezelfde manier bij

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