Palyginimo grandinės, tušti langeliai ir SUMIF HotXLS Delphi

HotXLS Delphi Component =1<2<3 vertina kaip FALSE — tą patį atsakymą duoda Excel 16, nes nuo v2.384.3 jo formulių parseris susuka palyginimo operatorius iš kairės į dešinę: 1<2 tampa TRUE, o TRUE<3 yra FALSE, nes boolean reitinge stovi aukščiau už bet kokį skaičių. Tas pats leidimas tuščią operandą padaro lygų ir 0, ir "", ir leidžia SUMIF ištempti vieno langelio sumų diapazoną iki kriterijų diapazono formos. Kiekviena iš šių smulkmenų atrodo kaip smulkmena, kol darbaknygė, apskaičiuota Delphi, nesutampa su ta pačia darbaknyge, atverta Excel

Nesutarimas paprastai prasideda nuo formulės, kurią žmogus parašė iš intuicijos. Kas nors suveda =0<B2<100, kad patikrintų, ar kiekis diapazone, Excel ramiai atsako FALSE kiekvienai eilutei, ir lapas išvyksta su tą bugą įkeptu vidun. Skaičiavimo varikliui negalima taisyti vartotojo ketinimo; jo darbas — duoti tą reikšmę, kurią duotų Excel, kad HotXLS į failą rašomas sukaupus rezultatas sutaptų su tuo, ką Excel parodo po perskaičiavimo. Iki v2.384.3 HotXLS tam diapazono tikrinimui kiekvienoje eilutėje atsakydavo TRUE — neteisingai priešinga kryptimi, ir serveryje sugeneruota ataskaita prieštarautų tai pačiai ataskaitai, atvertai staliniame kompiuteryje

Kodėl =1<2<3 Excel grąžina FALSE?

Excel grąžina FALSE, nes palyginimų grandinę skaito kaip (1<2)<3, ir vidinis TRUE tada pralaimi tipo reitingo kovą prieš skaičių 3. Senas HotXLS parseris tą patį tekstą skaitė kaip 1<(2<3): TXLSSyntax.Parse_expr iš lxFormula.pas išskaisdavo vieną operandą, pamatydavo palyginimo tokeną ir rekursingai vengdavosi į Parse_expr dėl dešinės pusės, kas daro operatorių dešinės asociacijos. Išeidavo 1<TRUE, o skaičius stovi žemiau boolean, tad rezultatas būdavo TRUE. Klaida simetriška: =3>2>1 Excel yra TRUE, HotXLS buvo FALSE, o =1=1=TRUE Excel yra TRUE ir iki pataisymo buvo FALSE. Regresija CalculateFormula_ComparisonChainsFoldLeftToRight prisega septynias tokias formules prieš Excel 16 grąžintas reikšmes ir kiekvieną praleidžia per abu variklių architektūras, klasikinę TXLSWorkbook ir XLSX natyvią TXLSXWorkbook, naudodama Calculate metodą, aprašytą HotXLS formulių variklio apžvalgoje

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');
  // Ką grąžina Excel 16:  FALSE, TRUE, FALSE, TRUE, TRUE, TRUE, TRUE
var
  Classic: IXLSWorkbook;
  Xlsx: TXLSXWorkbook;
  i: Integer;
begin
  Classic := TXLSWorkbook.Create;
  Xlsx := TXLSXWorkbook.Create;
  try
    // TXLSXWorkbook.Calculate vertina prieš aktyvųjį lapą ir
    // grąžina Null, kai darbaknygė visai neturi lapo
    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;

Pataisymas paverčia Parse_expr tos pačios formos ciklu, kokiu jau naudojasi Parse_expr1 dėl +, - ir &. Jis išskaido pirmąjį operandą su Parse_expr1, ir, kol kitas tokenas yra vienas iš =, <>, <, >, <= ar >=, sukuria palyginimo mazgą, pririša sukauptą kairįjį rezultatą kaip pirmąjį vaiką, išskaido kitą operandą su Parse_expr1, o ne su Parse_expr, ir padaro naują mazgą kairiuoju rezultatu kitam ratui. Du dalykai konvertuojant rekursiją į iteraciją buvo lengvai sugadinami, ir abu jie yra priežiūrėtojų pastabose: sukauptą mazgą reikia perduoti būtent tokia tvarka (lChild := Item; Item := nil), o klaidos kelias atlaisvinęs nepilnai sukurtą mazgą turi išeiti per Exit, vietoj to, kad iškristų iš ciklo ir grąžintų pakibusį medį

Kaip HotXLS reitinguoja skaičius, tekstą ir boolean palyginime?

HotXLS mišrius tipus reitinguoja taip, kaip Excel: kiekvienas skaičius mažesnis už kiekvieną tekstinę reikšmę, o kiekviena tekstinė reikšmė mažesnė už kiekvieną boolean. TXLSCalculator.CompareVariants iš lxCalc.pas abu operandus per GetRetValueType pasiskirsto į enumerated TXLSRetValueType = (xlVariantValue, xlNumberValue, xlStringValue, xlBooleanValue), ir, kai dvi klasės skiriasi, tiesiog lygina jų ordinals, tad to enum deklaracijos tvarka yra tarp tipų galiojanti taisyklė. Vienos klasės viduje palyginimas natūralus, su viena Excel būdinga gudrybe tekstui: abi eilutės pirmiausia praeina per lxUpperCase, tad ="abc"="ABC" yra TRUE. Be šio reitingo grandinės rezultato nepaaiškinsi. TRUE<3 nėra TRUE pavertimas 1 — tai boolean, lyginamas su skaičiumi, ir boolean laimi. Datos varikliui yra serijiniai skaičiai (varDate klasifikuojasi kaip xlNumberValue), tad data visada stovi žemiau bet kokio teksto, įskaitant tekstą, kuris atsitiktinai atrodo kaip data

Su kuo tuščias langelis lygus palyginime?

Tuščias langelis, panaudotas kaip palyginimo operandas, lygus 0, kai kitoje pusėje skaičius, lygus "", kai kitoje pusėje tekstas, ir nuo v2.384.53 lygus FALSE, kai kitoje pusėje loginė reikšmė, tad su tuščiu A1 =A1=0, =A1="" ir =A1=FALSE visi yra TRUE. TXLSCalculator.CompareVarValues, aptarnaujantis visus šešis palyginimo operatorius, tuščiąjį pakeičia prieš kviesdamas CompareVariants: jei tik vienas operandas yra Null, jis tampa WideString(''), kai porininkas string, False, kai porininkas boolean, ir 0 kitu atveju. Du tuščiai vis tiek lygūs vienas kitam be jokio pakeitimo. Aritmetikos kelias tuščiąjį visada pavardavo į 0, todėl =A1+1 davė 1, bet CompareVariants Null laikė savu žemiausiu reitingu, žemiau kiekvieno skaičiaus, ir palyginimo operatoriai tą reitingą naudojo tiesiogiai

var
  Wb: IXLSWorkbook;
  Sh: TXLSWorksheet;
begin
  Wb := TXLSWorkbook.Create;
  Sh := Wb.Sheets.Add;
  Sh.Range['B1', 'B1'].Value := 5;      // A1 paliktas tuščias tyčia

  Writeln(VarToStr(Wb.Calculate('=A1=0')));    // True
  Writeln(VarToStr(Wb.Calculate('=A1=""')));   // True
  Writeln(VarToStr(Wb.Calculate('=A1<B1')));   // True: tuščias lyginamas kaip 0
  Writeln(VarToStr(Wb.Calculate('=A1<0')));    // False; True iki v2.384.3
end;

Paskutinė eilutė yra ta, kuri praktikoje skaudėjo. Senajame reitinge tuščias buvo mažesnis už kiekvieną skaičių, įskaitant neigiamuosius, tad =IF(A1<0,"overdrawn","ok") kiekvieną tuščią likučio langelį pažymėdavo kaip įsiskolinusį, o =A1=0 buvo FALSE langeliui, kurį bet kuris vartotojas apibūdintų kaip nulį. Po v2.384.3 liko viena riba: pakeitimas rinkdavosi tik tarp 0 ir tuščios eilutės, tad tuščias, lyginamas su boolean, tapdavo 0, kuris reitinge stovi žemiau ir TRUE, ir FALSE, ir =A1=FALSE su tuščiu A1 vertindavosi į FALSE. Nuo HotXLS 2.384.53 tuščias, lyginamas su loginės reikšmės, XLS ir XLSX varikliuose elgiamas kaip FALSE, kaip ir Excel: su tuščiu A1 =A1=FALSE ir =A1<TRUE grąžina TRUE, o =A1=TRUE — FALSE. Tai taip pat reiškia, kad palyginimas negali atskirti tuščio nuo FALSE, nei Excel, nei HotXLS; kai lapui to skirtumo reikia, tikrinkite su ISBLANK ar =A1=""

Kodėl SUMIF su vieno langelio sumų diapazonu grąžindavo 0?

SUMIF grąžindavo 0, nes HotXLS iteraciją gniaužė iki mažesniojo iš dviejų diapazonų, o Excel laiko kriterijų diapazono formą ir sumų diapazoną naudoja tik dėl jo viršutinio kairiojo langelio. =SUMIF(A1:A10,">5",B1) Excel reiškia B1:B10 — patogumas, kuriuo remiasi daug rankom sukurtų šablonų. Bendras darbuotojas TXLSCalculator.GetValueItemRange2 anksčiau suspausdavo savo eilučių ir stulpelių skaičius iki reikšmių diapazono, kas pavyzdį suprastindavo iki vieno A1 prieš B1 tikrinimo. v2.384.3 gniaužimą pašalina: ciklas dabar pereina kriterijų diapazoną ir kiekvieną reikšmę skaito tuo pačiu poslinkiu nuo sumų diapazono viršutinio kairiojo kampo. Kadangi CalcSumIF ir CalcAverageIF abu kviečia tą darbuotoją, AVERAGEIF gauna tą patį praplėtimą, o sumų diapazonas, didesnis už kriterijų diapazoną, dėl tos pačios priežasties nukarpomas iki kriterijų formos. Kriterijų argumentas viduryje yra value klasės argumentas, o išoriniai du — reference klasės; skirtumas aprašytas straipsnyje apie implicit intersection ir argumentų klases

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;          // kriterijų stulpelis: 1..10
      Sheet.Cells[Row, 2].Value := Row * 100;    // sumos: 100..1000
    end;
    Sheet.Cells[1, 4].Formula := '=SUMIF(A1:A10,">5",B1)';     // vieno langelio sumų diapazonas
    Sheet.Cells[2, 4].Formula := '=SUMIF(A1:A10,">5",B1:B10)'; // aiškiai nurodytas sumų diapazonas
    if Book.Recalculate = lxOk then
      // D1 ir D2 abu yra 4000 (600+700+800+900+1000); D1 buvo 0 iki v2.384.3
      Writeln(VarToStr(Sheet.Cells[1, 4].Value), ' ', VarToStr(Sheet.Cells[2, 4].Value));
  finally
    Book.Free;
  end;
end;

INDIRECT ir YEARFRAC: dvi tylesnės pataisos

INDIRECT dabar gerbia antrąjį argumentą, o tekstas po teisėtos nuorodos yra klaida, o ne ignoruojamas. Su a1 FALSE tekstas išskaidomas kaip absoliutus R1C1, tad =INDIRECT("R2C3",FALSE) skaito C2; senas kodas ignoruodavo vėliavėlę, "R2" skaitė kaip stulpelis R, eilutė 2, ir tyliai grąžindavo neteisingą langelį. Vėliavėlė išskirstoma pagal jos varianto tipą (boolean, skaičius ar tekstas), nes string variantą tiesiai pavertus Double kyla išimtis. Reliatyvus R1C1 tekstas, toks kaip R[1]C[1], grąžina #REF!, nes INDIRECT neturi formulės langelio kilmės, prieš kurią jį išspręsti, o A1 tekstas su galiniais simboliais, "B2 junk", taip pat grąžina #REF!. YEARFRAC su pagrindu 0 dabar taiko NASD vasario paskutinės dienos taisykles, kurias DAYS360 jau buvo įgyvendinęs: kai abi datos yra paskutinė vasario diena, galutinė diena tampa 30, o tada pradžia paskutinę vasario dieną tampa 30. Nuo 2024-02-29 iki 2025-02-28 skaičius dabar yra 360 dienų, trupmena tiksliai 1, kur ankstesnis Days360US skaičiavo 359

Ką šios pataisos garantuoja ir kokia buvo pamoka?

Palyginimų grandinės elgseną garantuoja testas, lyginantis abu variklius su Excel 16 išmatuotomis reikšmėmis, ir tas testas egzistuoja todėl, kad pirmasis pataisymo aprašymas buvo neteisingas. v2.384.3 leidimo pastaba iš pradžių teigė, kad kairės į dešinę susukimas =1<2<3 padaro TRUE — būtent tai, ką duodavo senas dešinės asociacijos parseris ir kas yra priešinga tam, ką grąžina ir Excel, ir naujasis kodas. Niekas nebuvo pavertęs pavyzdžio; jis buvo parašytas iš intuicijos, jog „1 mažesnis už 2, o 2 mažesnis už 3“. Pastaba buvo pataisyta, o septynių formulių testas pridėtas vėlesniame commit, ir iš to išaugusi taisyklė tinka bet kam, dokumentuojančiam skaičiuoklės semantiką: paleiskite pavyzdį Excel dar prieš užsirašydami tikėtiną reikšmę. Tuščiojo operando pakeitimas ir SUMIF praplėtimas seka tą pačią Excel elgseną, įskaitant tuščias-prieš-boolean atvejį nuo v2.384.53, o sąlyginiai agregatai, kuriems dar reikia praleisti per filtrus ar paslėptas eilutes, seka atskiras taisykles SUBTOTAL ir AGGREGATE paslėptų eilučių straipsnyje

HotXLS yra natyvus Delphi ir C++Builder skaičiuoklės komponentas, skaitantis, perskaičiuojantis ir rašantis XLS, XLSX, ODS ir CSV be įdiegto Excel, ir čia aprašytos palyginimo, tuščiojo langelio bei SUMIF taisyklės gyvena skaičiavimo variklyje, kurį dalijasi abi darbaknygių architektūros. Pilnas funkcijų sąrašas ir licencijavimo parinktys yra HotXLS Delphi skaičiuoklės komponento produkto puslapyje