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;
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
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;
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