Artykuł techniczny

Łańcuchy porównań, puste komórki i SUMIF w HotXLS Delphi

Kontrola zakresu zapisana jako =0<B2<100 zwraca FALSE w Excelu w każdym wierszu, łącznie z wierszem, w którym B2 trzyma 50. Excel wylicza najpierw 0<B2, dostaje TRUE, a potem pyta, czy TRUE jest mniejsze od 100; wartość logiczna plasuje się nad każdą liczbą, więc odpowiedź brzmi FALSE cokolwiek zawiera B2. Puste komórki i SUMIF mają niespodzianki tego samego gatunku: pusta komórka równa się jednocześnie 0, "" i FALSE, a =SUMIF(A1:A10,">5",B1) sumuje dziesięć komórek kolumny B

Silnik obliczeń przeliczający taki skoroszyt poza Excelem musi odtworzyć te odpowiedzi, nawet gdy ewidentnie nie są tym, co autor miał na myśli. Wartość z pamięci podręcznej zapisana w pliku to to, co widzi przeglądarka, podgląd w poczcie albo import dalej w łańcuchu, zanim ktokolwiek wciśnie F9. Jeśli serwer zapisze TRUE dla kontroli zakresu, a Excel pokaże później FALSE, ten sam raport zawiera dwie różne liczby, zależnie od tego, gdzie został otwarty, i nikt nie potrafi powiedzieć, której ufać. Poprawianie formuły autora to praca autora

Dlaczego =1<2<3 zwraca FALSE w Excelu?

Excel zwraca FALSE dla =1<2<3, bo wszystkie sześć operatorów porównań dzieli jeden poziom pierwszeństwa i łączy od lewej do prawej, więc formuła znaczy (1<2)<3, a wewnętrzne TRUE przegrywa potem pojedynek rankingowy typów z liczbą 3. HotXLS Delphi Component parsuje łańcuchy porównań tak samo w obu swoich silnikach skoroszytów, klasycznym TXLSWorkbook i natywnym dla XLSX TXLSXWorkbook. Parser czyta jeden operand i, dopóki następny token to =, <>, <, >, <= albo >=, buduje węzeł porównania ze wszystkim dotąd sparsowanym jako lewe dziecko i następnym operandem jako prawym, po czym przenosi ten węzeł do następnej rundy. To ten sam kształt pętli, jakiego używa dla +, - i &

Pułapka siedzi w podręcznikowej gramatyce. Zapisany jako expr = operand op expr, parser zejścia rekurencyjnego wchodzi w samą siebie po prawą stronę i po cichu czyni operator prawostronnie łącznym. Przy tym czytaniu =1<2<3 staje się 1<(2<3), czyli 1<TRUE, czyli TRUE. Błąd biegnie w obie strony: =3>2>1 to TRUE w Excelu i FALSE przy prawostronnej łączności, a =1=1=TRUE odwraca się tak samo. Formy nawiasowane wychodzą dobrze tak czy inaczej, więc pomyłka prześlizguje się obok każdego testu, który sprawdza tylko (a<b)<c

Metoda Calculate wylicza łańcuch formuły bez wpisywania go w komórkę, co czyni ją szybkim sposobem sprawdzenia całej listy łańcuchów na obu silnikach. Metodę opisano głębiej w przeglądzie silnika formuł HotXLS

uses
  SysUtils, Variants, lxCalc, lxHandle, lxHandleX;   // lxOk jest zadeklarowany w 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');
  // Odpowiedzi Excela:  FALSE, TRUE, FALSE, TRUE, TRUE, TRUE, TRUE
var
  Classic: IXLSWorkbook;
  Xlsx: TXLSXWorkbook;
  i: Integer;
begin
  Classic := TXLSWorkbook.Create;   // liczone referencjami przez interfejs
  Xlsx := TXLSXWorkbook.Create;
  try
    // TXLSXWorkbook.Calculate wylicza względem aktywnego arkusza i
    // zwraca Null, gdy skoroszyt nie ma jeszcze arkusza
    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;
Dwa drzewa parsowania dla =1<2<3: parsowanie prawostronnie łączne wylicza 1<(2<3) jako TRUE, a zwijanie od lewej do prawej wylicza (1<2)<3 jako FALSE, bo Excel plasuje każdą liczbę pod tekstem, a każdą wartość tekstową pod booleanem
Oba silniki HotXLS zwijają łańcuchy porównań od lewej do prawej — boolean bije każdą liczbę, więc to, że TRUE przegrywa z 3, to dokładnie to, co czyni łańcuchową kontrolę zakresu FALSE

Liczby, tekst i booleany: ranking międzytypowy

Gdy obie strony porównania mają różne typy, Excel nie konwertuje jednej w drugą. Zamiast tego porządkuje typy: każda liczba jest mniejsza od każdej wartości tekstowej, a każda wartość tekstowa od każdego booleana. HotXLS klasyfikuje oba operandy jako liczbę, tekst albo boolean i, gdy klasy się różnią, porównuje same klasy. Więc =TRUE>99999 to TRUE, ="1">2 to TRUE, i =FALSE>"zzz" również TRUE. To jest ten kawałek, który sprawia, że łańcuchowe porównania czują się arbitralne. TRUE<3 nie jest TRUE skoercjonowanym do 1 i porównanym z 3; to boolean porównany z liczbą i boolean wygrywa

W obrębie jednej klasy porównanie jest naturalne, z akcentem dla tekstu: porównanie łańcuchów ignoruje wielkość liter, więc ="abc"="ABC" zwraca TRUE w Excelu i w HotXLS. Daty są dla silnika liczbami seryjnymi, co znaczy, że data zawsze plasuje się pod jakimkolwiek tekstem, łącznie z tekstową komórką, która tylko wygląda jak data. Kolumna zaimportowanych dat, w której połowa komórek przychodzi jako łańcuchy, będzie się sortować i porównywać w dwóch osobnych pasmach, a =A2>DATE(2026,1,1) zwraca TRUE dla każdej z łańcuchowych komórek

Czemu równa się pusta komórka w porównaniu?

Pusta komórka użyta jako operand porównania przybiera typ drugiej strony: równa się 0 wobec liczby, "" wobec tekstu i FALSE wobec wartości logicznej. Przy pustym A1 =A1=0, =A1="" i =A1=FALSE są wszystkie TRUE w Excelu. HotXLS robi to samo podstawienie przed uszeregowaniem dwóch operandów, dla wszystkich sześciu operatorów porównania i w obu silnikach. Gdy oba operandy są puste, podstawienie nie zachodzi i porównują się jako równe

var
  Wb: IXLSWorkbook;
  Sh: TXLSWorksheet;
begin
  Wb := TXLSWorkbook.Create;
  Sh := Wb.Sheets.Add;                  // pierwszy arkusz staje się aktywnym
  Sh.Range['B1', 'B1'].Value := 5;      // A1 zostaje puste celowo

  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: pustka porównuje się jak 0
  Writeln(VarToStr(Wb.Calculate('=A1<0')));      // False: 0 nie jest poniżej 0
end;

Ostatnia linia to ta, która gryzie w praktyce. Arytmetyka od zawsze traktuje pustkę jako 0, więc =A1+1 daje 1 w każdym silniku. Porównanie to miejsce, gdzie skrót idzie na bagno. Silnik trzymający „pustkę” jako własny najniższy szczebel, pod każdą liczbą, odpowiada TRUE dla =A1<0, a potem =IF(A1<0,"overdrawn","ok") oznacza każdą pustą komórkę salda jako przekroczone. Samo podstawianie 0 albo pustego łańcucha też nie wystarczy: wobec booleana pustka stałaby się 0, co plasuje się pod TRUE i pod FALSE, i =A1=FALSE wychodziłoby FALSE na pustej komórce

Podstawianie pustego operandu w HotXLS: puste A1 porównuje się jako równe 0 i pustemu tekstowi, pustka wobec booleana porównuje się jako FALSE, więc =A1=FALSE jest TRUE jak w Excelu, a pustka trzymana jako najniższy szczebel czyniłaby =A1<0 TRUE dla każdego pustego salda
Podstawienie dopasowuje się do typu drugiego operandu, 0, pustego łańcucha albo FALSE — surowy ranking Nulla to właśnie to, co oznaczałoby każde puste saldo jako przekroczone

Jest konsekwencja warta przemyślenia. Ponieważ pustka porównuje się jako FALSE wobec wartości logicznej, =A1=FALSE nie odróżni pustej komórki od komórki trzymającej FALSE, w Excelu ani w HotXLS, a =A1=0 nie odróżni pustej od zera. Szablony, które muszą wiedzieć, czy ktoś faktycznie wpisał wartość, powinny testować przez ISBLANK(A1). Forma =A1="" jest słabsza, bo jest też TRUE dla formuły zwracającej pusty łańcuch

Dlaczego SUMIF z jednokomórkowym zakresem sumy obejmuje całą kolumnę?

Excel zachowuje kształt zakresu kryteriów i używa zakresu sumy tylko dla jego lewej górnej komórki, więc =SUMIF(A1:A10,">5",B1) znaczy B1:B10. Sporo ręcznie budowanych szablonów polega na tej skróconej pisowni, nie wiedząc o tym. Oczywista implementacja, docinająca przejście do mniejszego z dwóch zakresów, redukuje przykład do pojedynczego testu A1 wobec B1 i zwraca 0. HotXLS chodzi po zakresie kryteriów i czyta każdą wartość w tym samym przesunięciu wiersza i kolumny od lewego górnego rogu zakresu sumy. AVERAGEIF przechodzi to samo przejście i dostaje to samo rozciąganie, a zakres sumy większy niż zakres kryteriów jest przycinany do kształtu kryteriów

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;          // kolumna kryteriów: 1..10
      Sheet.Cells[Row, 2].Value := Row * 100;    // kwoty: 100..1000
    end;
    Sheet.Cells[1, 4].Formula := '=SUMIF(A1:A10,">5",B1)';      // jednokomórkowy zakres sumy
    Sheet.Cells[2, 4].Formula := '=SUMIF(A1:A10,">5",B1:B10)';  // jawny zakres sumy
    Sheet.Cells[3, 4].Formula := '=AVERAGEIF(A1:A10,">5",B1)';  // to samo rozciąganie
    if Book.Recalculate = lxOk then
      // D1 i 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;
Rozciąganie SUMIF i AVERAGEIF w HotXLS, gdzie =SUMIF(A1:A10,">5",B1) obchodzi dziesięciowierszowy zakres kryteriów, czytając B1 do B10 w pasujących przesunięciach, z wynikiem 4000, gdzie docięcie do jednokomórkowego zakresu sumy zwróciłoby 0
Excel pożycza tylko lewy górny róg zakresu sumy i trzyma kształt kryteriów, więc szablon podający B1 znaczy B1:B10 — SUMIF i AVERAGEIF chodzą po wszystkich dziesięciu przesunięciach i nadmiarowy zakres przycinają tak samo

W szablonach pisanych samemu rozpisz pełny zakres sumy. Silnik honoruje skróconą pisownię, bo istniejące pliki od niej zależą, ale B1 w formule czyta się jak pojedynczą komórkę dla następnej osoby utrzymującej arkusz, a ta osoba rozsądnie założy, że dodawana jest tylko jedna kwota. Kryteria w środku jest wyliczane jako pojedyncza wartość, podczas gdy dwa zewnętrzne argumenty to referencje; ten podział klas argumentów wyjaśnia artykuł o implicit intersection i klasach argumentów. Sumy warunkowe, które muszą też pomijać wiersze przefiltrowane albo ukryte, idą za innymi regułami, omawia je artykuł o ukrytych wierszach SUBTOTAL i AGGREGATE

Style referencji INDIRECT i podstawa 0 YEARFRAC

Dwie mniejsze funkcje niosą pułapkę tego samego gatunku. INDIRECT przyjmuje opcjonalny drugi argument, a1, i gdy jest FALSE, tekst jest referencją R1C1: =INDIRECT("R2C3",FALSE) czyta C2. Parser ignorujący flagę czyta "R2" jako kolumnę R, wiersz 2, co jest prawdziwą komórką, więc formuła zwraca wiarygodną wartość z niewłaściwego miejsca. HotXLS czyta flagę jako boolean, liczbę (zero znaczy FALSE) albo tekst "FALSE" i konwertuje tekst R1C1 przed rozwiązaniem, z przesunięciami w nawiasach kwadratowych, jak R[1]C[1], mierzonymi od komórki trzymającej formułę. Tekst z czymkolwiek zostającym po poprawnej referencji, jak "B2 junk", zwraca #REF! zamiast po cichu użyć B2

YEARFRAC z podstawą 0 używa amerykańskiego (NASD) liczenia dni 30/360, łącznie z regułami ostatniego dnia lutego, które stosuje DAYS360: gdy obie daty to ostatni dzień lutego, dzień końca staje się 30, a potem początek w ostatnim dniu lutego staje się 30. Od 2024-02-29 do 2025-02-28 daje to 360 dni i ułamek dokładnie 1. Zwykłe liczenie 30/360 pomijające te reguły daje 359 dni i 0.99722, a różnica wychodzi w naliczaniach odsetek obejmujących koniec lutego

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)')));   // wiersz 2, kolumna 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;

Gdy dokumentujesz albo testujesz zachowanie tego gatunku, bierz oczekiwane wartości z samego Excela, a nie z tego, co formuła zdaje się mówić. „1 jest mniejsze od 2, a 2 od 3” sugeruje, że =1<2<3 to TRUE, co jest dokładnie odpowiedzią parsera prawostronnie łącznego i przeciwieństwem tego, co zwraca Excel. Krótka tabela formuł z wartościami, które pokazuje Excel, obok nich łapie wszystkie cztery przypadki z tego artykułu: łańcuchy, pustki, rozciąganie SUMIF i lutowe liczenie dni

HotXLS czyta, przelicza i zapisuje XLS, XLSX, ODS oraz CSV z Delphi i C++Buildera bez Excela na maszynie, a reguły porównań, pustych komórek i agregatów warunkowych powyżej należą do silnika obliczeń wspólnego dla obu klas skoroszytów. Lista obsługiwanych funkcji, edycje i licencjonowanie są na stronie komponentu arkuszy HotXLS dla Delphi