Vertailuketjut, tyhjät solut ja SUMIF HotXLS:ssä

HotXLS Delphi Component evaluoi lausekkeen =1<2<3 arvoon FALSE, sama vastaus jonka Excel 16 antaa, koska versiosta v2.384.3 alkaen sen kaavajäsentin taittaa vertailuoperaattorit vasemmalta oikealle: 1<2 muuttuu arvoksi TRUE, ja TRUE<3 on FALSE koska boolean arvostetaan jokaisen luvun yläpuolelle. Sama julkaisu tekee tyhjästä operandista yhtä suuren sekä 0:n että "":n kanssa ja antaa SUMIFin venyttää yksisoluista summa-aluetta kriteerialueen muotoon. Jokainen näistä näyttää triviaalilta kunnes Delphillä laskettu työkirja on eri mieltä kuin sama työkirja avattuna Excelissä

Erimielisyys alkaa yleensä kaavasta jonka joku kirjoitti intuitiolla. Joku näppäilee =0<B2<100 tarkistaakseen että määrä on alueella, Excel vastaa hiljaisena FALSE:n jokaiselle riville, ja taulukko lähtee matkaan kyseinen bugi sisälläan. Laskukoneen ei kuulu korjata käyttäjän tarkoitusta; sen tehtävä on tuottaa arvo jonka Excel tuottaisi, niin että HotXLS:n tiedostoon kirjoitettu välimuistitulos täsmää siihen mitä Excel näyttää uudelleenlaskennan jälkeen. Ennen v2.384.3:aa HotXLS vastasi TRUE:a kyseiseen aluetarkistukseen jokaisella rivillä, väärin päinvastaiseen suuntaan, ja palvelimella generoitu raportti olisi ollut eri mieltä kuin sama raportti avattuna työpöydällä

Miksi =1<2<3 palauttaa FALSE:n Excelissä?

Excel palauttaa FALSE:n koska se lukee vertailuketjun muodossa (1<2)<3, ja sisempi TRUE häviää sitten tyyppijärjestyskilpailun lukua 3 vastaan. Vanha HotXLS-jäsentin luki saman tekstin muodossa 1<(2<3): tiedoston lxFormula.pas TXLSSyntax.Parse_expr jäsensi yhden operandin, näki vertailutokenin ja kutsui itseään rekursiolla Parse_expria oikean puolen puolelle, mikä tekee operaattorista oike-assosiatiivisen. Siitä tulee 1<TRUE, ja luku on booleanin alapuolella, joten tulos oli TRUE. Virhe on symmetrinen: =3>2>1 on TRUE Excelissä ja oli FALSE HotXLS:ssä, ja =1=1=TRUE on TRUE Excelissä ja oli FALSE ennen korjausta. Regressiotesti CalculateFormula_ComparisonChainsFoldLeftToRight lukitsee seitsemän tällaista kaavaa Excel 16:n palauttimiin arvoihin ja ajaa jokaisen läpi molempien konearkkitehtuurien, klassisen TXLSWorkbookin ja XLSX-natiivin TXLSXWorkbookin, kautta käyttäen Calculate-metodia jota artikkeli HotXLS:n kaavakoneyleiskatsaus kuvaa

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');
  // Mitä Excel 16 palauttaa:  FALSE, TRUE, FALSE, TRUE, TRUE, TRUE, TRUE
var
  Classic: IXLSWorkbook;
  Xlsx: TXLSXWorkbook;
  i: Integer;
begin
  Classic := TXLSWorkbook.Create;
  Xlsx := TXLSXWorkbook.Create;
  try
    // TXLSXWorkbook.Calculate evaluoi aktiivista taulukkoa vasten ja
    // palauttaa Nullin kun työkirjalla ei ole yhtään taulukkoa
    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;

Korjaus muuttaa Parse_exprin samanmuotoiseksi silmukaksi jota Parse_expr1 käyttää jo operaattoreille +, - ja &. Se jäsentää ensimmäisen operandin Parse_expr1illa, ja niin kauan kuin seuraava tokeni on joko =, <>, <, >, <= tai >=, se luo vertailusolmun, kiinnittää kertyneen vasemman tuloksen ensimmäiseksi lapseksi, jäsentää seuraavan operandin Parse_expr1illa eikä Parse_exprilla, ja tekee uudesta solmusta vasemman tuloksen seuraavalle kierrokselle. Kaksi yksityiskohtaa oli helppo saada väärin kun rekursio muutettiin iteraatioksi, ja molemmat ovat ylläpitäjien muistiinpanoissa: kertynyt solmu on luovutettava järjestyksessä (lChild := Item; Item := nil), ja virhepolun on tehtävä Exit puoliarakennetun solmun vapauttamisen jälkeen sen sijaan että putoaisi silmukasta ulos ja palauttaisi roikkuvan puun

Miten HotXLS järjestää luvut, tekstit ja booleanit vertailussa?

HotXLS järjestää sekatyypit niin kuin Excel: jokainen luku on pienempi kuin jokainen tekstiarvo, ja jokainen tekstiarvo on pienempi kuin jokainen boolean. TXLSCalculator.CompareVariants tiedostossa lxCalc.pas luokittelee molemmat operandit GetRetValueTypeilla luetteloon TXLSRetValueType = (xlVariantValue, xlNumberValue, xlStringValue, xlBooleanValue), ja kun luokat eroavat, se vertaa pelkät ordinaalit, joten luokituksen esitysjärjestys on ristiintyyppinen sääntö. Yhden luokan sisällä vertailu on luonnollinen, yhdellä Excel-kohtaisella mutkalla tekstissä: molemmat merkkijonot kulkevat ensin lxUpperCasein läpi, joten ="abc"="ABC" on TRUE. Tämä järjestys on syy siihen ettei ketjun tulosta voi päätellä ilman sitä. TRUE<3 ei ole TRUE:n pakotus arvoon 1, se on boolean verrattuna lukuun, ja boolean voittaa. Päivämäärät ovat sarjanumeroita koneelle (varDate luokitellaan xlNumberValueksi), joten päivämäärä on aina minkä tahansa tekstin alapuolella, myös tekstin joka sattuu näyttämään päivämäärältä

Mihin tyhjä solu on yhtä suuri vertailussa?

Tyhjää solua vertailuoperandina käytettäessä on yhtä suuri kuin 0 kun toinen puoli on luku, yhtä suuri kuin "" kun toinen puoli on tekstiä, ja versiosta v2.384.53 alkaen yhtä suuri kuin FALSE kun toinen puoli on looginen arvo, joten A1:n ollessa tyhjä =A1=0, =A1="" ja =A1=FALSE ovat kaikki TRUE. TXLSCalculator.CompareVarValues, joka palvelee kaikkia kuutta vertailuoperaattoria, korvaa tyhjän ennen CompareVariantsin kutsumista: jos täsmälleen yksi operandi on Null, siitä tulee WideString('') kun kumppani on merkkijono, False kun kumppani on boolean, ja muuten 0. Kaksi tyhjää vertautuu yhä toisiinsa yhtä suurina ilman korvausta. Aritmetiikkapolku on aina kääntänyt tyhjän arvoksi 0, mistä syystä =A1+1 antoi 1:n, mutta CompareVariants pitää Nullin omana alimman tason rankina, jokaisen luvun alapuolella, ja vertailuoperaattorit käyttivät kyseistä rankia suoraan

var
  Wb: IXLSWorkbook;
  Sh: TXLSWorksheet;
begin
  Wb := TXLSWorkbook.Create;
  Sh := Wb.Sheets.Add;
  Sh.Range['B1', 'B1'].Value := 5;      // A1 jätetään tarkoituksella tyhjäksi

  Writeln(VarToStr(Wb.Calculate('=A1=0')));    // True
  Writeln(VarToStr(Wb.Calculate('=A1=""')));   // True
  Writeln(VarToStr(Wb.Calculate('=A1<B1')));   // True: tyhjä vertautuu arvona 0
  Writeln(VarToStr(Wb.Calculate('=A1<0')));    // False; True ennen v2.384.3:a
end;

Viimeinen rivi on se joka sattui käytännössä. Vanhalla rankilla tyhjä oli pienempi kuin jokainen luku, negatiiviset mukaan lukien, joten =IF(A1<0,"overdrawn","ok") leimasi jokaisen tyhjän saldosolun ylivirittyneeksi, ja =A1=0 oli FALSE solulle jonka kuka tahansa käyttäjä kuvailisi nollaksi. Yksi raja jäi v2.384.3:n jälkeen: korvaus valitsi vain arvon 0 ja tyhjän merkkijonon väliltä, joten tyhjä booleania vastaan vertautuna tuli arvoksi 0, joka rankkaa sekä TRUE:n että FALSE:n alapuolelle, ja =A1=FALSE tyhjällä A1:llä evaluoitui arvoon FALSE. HotXLS 2.384.53:sta alkaen tyhjä loogista arvoa vastaan vertautuna tulkitaan FALSE:ksi sekä XLS- että XLSX-koneessa, niin kuin Excel tekee: A1:n ollessa tyhjä =A1=FALSE ja =A1<TRUE palauttavat TRUE:n ja =A1=TRUE palauttaa FALSE:n. Se tarkoittaa myös ettei vertailu erota tyhjää FALSE:sta, ei Excelissä eikä HotXLS:ssä; kun taulukko tarvitsee tuon eron, testaa ISBLANKilla tai =A1=""lla

Miksi SUMIF yksisoluista summa-aluetta käyttäen palautti 0:n?

SUMIF palautti 0:n koska HotXLS lukitsi iteraation kahdesta alueesta pienempään, kun taas Excel pitää kriteerialueen muodon ja käyttää summa-aluetta vain sen vasemmasta yläkulmasta. =SUMIF(A1:A10,">5",B1) tarkoittaa siis Excelissä aluetta B1:B10, mukavuus johon moni käsin rakennettu malli nojaa. Jaettu apurutiini TXLSCalculator.GetValueItemRange2 kutisti rivi- ja sarakejoukkonsa summa-alueen mittoihin, mikä reduktoi esimerkin yhdeksi testiksi A1 vastaan B1. v2.384.3 poistaa lukituksen: silmukka kävelee nyt kriteerialueen ja lukee jokaisen arvon samalta siirtymältä summa-alueen vasemmasta yläkulmasta. Koska CalcSumIF ja CalcAverageIF kutsuvat molemmat kyseistä apuria, AVERAGEIF saa saman koonmuutoksen, ja summa-alue joka on suurempi kuin kriteerialue typistetään kriteerin muotoon samasta syystä. Keskimmäinen kriteeriargumentti on arvoluokan argumentti ja ääreiset kaksi ovat viittausluokkaa, ero jota käsittelee artikkeli implisiittisestä leikkauksesta ja argumenttiluokista

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;          // kriteerisarake: 1..10
      Sheet.Cells[Row, 2].Value := Row * 100;    // summat: 100..1000
    end;
    Sheet.Cells[1, 4].Formula := '=SUMIF(A1:A10,">5",B1)';     // yksisoluinen summa-alue
    Sheet.Cells[2, 4].Formula := '=SUMIF(A1:A10,">5",B1:B10)'; // eksplisiittinen summa-alue
    if Book.Recalculate = lxOk then
      // Sekä D1 että D2 ovat 4000 (600+700+800+900+1000); D1 oli 0 ennen v2.384.3:a
      Writeln(VarToStr(Sheet.Cells[1, 4].Value), ' ', VarToStr(Sheet.Cells[2, 4].Value));
  finally
    Book.Free;
  end;
end;

INDIRECT ja YEARFRAC: kaksi hiljaisempaa korjausta

INDIRECT kunnioittaa nyt toista argumenttiaan, ja teksti kelvollisen viittauksen jälkeen on virhe sen sijaan että ohitettaisiin. Kun a1 on FALSE, teksti jäsennetään absoluuttisena R1C1:nä, joten =INDIRECT("R2C3",FALSE) lukee C2:n; vanha koodi ohitti lipun, luki "R2":n sarakkeeksi R, riviksi 2, ja palautti hiljaisena väärän solun. Lippu dispatchataan sen varianttityypin mukaan (boolean, luku tai teksti), koska merkkijonovariantin suora konversio Doubleksi nostaa poikkeuksen. Suhteellinen R1C1-teksti kuten R[1]C[1] palauttaa #REF!in, koska INDIRECTilla ei ole kaavasolun alkuperää jota vasten ratkaista, ja A1-teksti perämerkeillä, "B2 junk", palauttaa niin ikään #REF!in. YEARFRAC perusteen 0 kanssa soveltaa nyt NASD:n helmikuun-viimeinen-sääntöjä joita DAYS360 toteutti jo ennestään: kun molemmat päivämäärät ovat helmikuun viimeinen päivä, loppupäivästä tulee 30, ja sitten helmikuun viimeisenä päivänä alkavasta alusta tulee 30. Väliltä 2024-02-29 – 2025-02-28 lukumäärä on nyt 360 päivää, murto-osa täsmälleen 1, kun taas aiempi Days360US laski 359:ksi

Mitä nämä korjaukset takaavat, ja mikä oli opetus?

Vertailuketjukäyttäytymisen takaa testi joka vertaa molempia koneita Excel 16:ssa mitattuihin arvoihin, ja kyseinen testi on olemassa koska korjauksen ensimmäinen kuvaus oli väärä. v2.384.3:n julkaisumuistio sanoi alun perin että vasemmalta oikealle taittaminen teki =1<2<3:sta TRUE:n, mikä on täsmälleen mitä vanha oike-assosiatiivinen jäsentin tuotti ja päinvastoin kuin sekä Excel että uusi koodi palauttavat. Kukaan ei ollut evaluoinut esimerkkiä; se oli kirjoitettu intuitiosta että "1 on pienempi kuin 2 on pienempi kuin 3". Muistio korjattiin ja seitsemän kaavan testi lisättiin jatkokommitissa, ja siitä syntynyt sääntö pätee jokaiseen joka dokumentoi taulukoiden semantiikkaa: aja esimerkki Excelissä ennen kuin kirjoitat odotusarvon ylös. Tyhjän operandin korvaus ja SUMIFin koonmuutos noudattavat samaa Excelin käyttäytymistä, myös tyhjä vastaan boolean -tapaus versiosta v2.384.53 alkaen, ja ehdolliset aggregaatit joiden on lisäksi ohitettava suodatetut tai piilotetut rivit seuraavat erillisiä sääntöjä artikkelissa piilotetuista ja suodatetuista riveistä SUBTOTALilla ja AGGREGATElla

HotXLS on natiivi Delphi- ja C++Builder-taulukkolaskentakomponentti joka lukee, laskee uudelleen ja kirjoittaa XLS-, XLSX-, ODS- ja CSV-muodot ilman asennettua Exceliä, ja tässä kuvatut vertailu-, tyhjä- ja SUMIF-säännöt asuvat laskukoneessa jonka molemmat työkirja-arkkitehtuurit jakavat. Koko funktiolista ja lisenssivaihtoehdot löytyvät HotXLS Delphi Excel -komponentin tuotesivulta