Articolo tecnico

Catene di confronto, celle vuote e SUMIF in HotXLS Delphi

Un controllo di intervallo scritto come =0<B2<100 restituisce FALSE in Excel su ogni riga, inclusa quella dove B2 contiene 50. Excel valuta prima 0<B2, ottiene TRUE, e poi chiede se TRUE sia minore di 100; un valore logico è superiore a ogni numero, quindi la risposta è FALSE qualunque cosa contenga B2. Celle vuote e SUMIF hanno sorprese dello stesso tipo: una cella vuota è uguale a 0, a "" e a FALSE nello stesso momento, e =SUMIF(A1:A10,">5",B1) somma dieci celle della colonna B

Un motore di calcolo che ricalcola una cartella di lavoro simile fuori da Excel deve riprodurre quelle risposte, anche quando evidentemente non sono ciò che l'autore intendeva. Il risultato in cache memorizzato nel file è ciò che vede un viewer, un'anteprima email o un import a valle prima che qualcuno prema F9. Se il server scrive TRUE per il controllo di intervallo e Excel mostra poi FALSE, lo stesso report contiene due numeri diversi a seconda di dove è stato aperto, e nessuno può dire a quale fidarsi. Sistemare la formula dell'autore è compito dell'autore

Perché =1<2<3 restituisce FALSE in Excel?

Excel restituisce FALSE per =1<2<3 perché tutti e sei gli operatori di confronto condividono uno stesso livello di precedenza e associano da sinistra a destra, quindi la formula significa (1<2)<3, e il TRUE interno perde poi una gara di ordinamento per tipo contro il numero 3. HotXLS Delphi Component analizza le catene di confronto allo stesso modo in entrambi i suoi motori di cartelle di lavoro, il classico TXLSWorkbook e il nativo XLSX TXLSXWorkbook. Il parser legge un operando, e mentre il prossimo token è =, <>, <, >, <= o >=, costruisce un nodo di confronto con tutto ciò che ha analizzato finora come figlio sinistro e il prossimo operando come figlio destro, poi porta quel nodo al round successivo. È la stessa forma di ciclo che usa per +, - e &

La trappola sta nella grammatica da manuale. Scritta come expr = operand op expr, un parser recursive-descent ricorsa in se stesso per il lato destro e rende silenziosamente l'operatore associativo a destra. Con quella lettura =1<2<3 diventa 1<(2<3), cioè 1<TRUE, cioè TRUE. L'errore corre in entrambe le direzioni: =3>2>1 è TRUE in Excel e FALSE sotto associatività a destra, e =1=1=TRUE si capovolge allo stesso modo. Le forme con parentesi escono giuste in entrambi i casi, quindi lo sbaglio scivola oltre qualunque test che controlli solo (a<b)<c

Il metodo Calculate valuta una stringa di formula senza metterla in una cella, il che lo rende un modo rapido per controllare un'intera lista di catene contro entrambi i motori. Il metodo è trattato più a fondo in la panoramica del motore di formule HotXLS

uses
  SysUtils, Variants, lxCalc, lxHandle, lxHandleX;   // lxOk è dichiarato 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');
  // Risposte di Excel:  FALSE, TRUE, FALSE, TRUE, TRUE, TRUE, TRUE
var
  Classic: IXLSWorkbook;
  Xlsx: TXLSXWorkbook;
  i: Integer;
begin
  Classic := TXLSWorkbook.Create;   // reference counted attraverso l'interfaccia
  Xlsx := TXLSXWorkbook.Create;
  try
    // TXLSXWorkbook.Calculate valuta sul foglio attivo e
    // restituisce Null quando la cartella di lavoro non ha ancora nessun foglio
    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;
Due alberi di parsing per =1<2<3: un parsing associativo a destra valuta 1<(2<3) come TRUE, mentre la riduzione da sinistra a destra valuta (1<2)<3 come FALSE, perché Excel ordina ogni numero sotto il testo e ogni valore testo sotto un boolean
Entrambi i motori HotXLS riducono le catene di confronto da sinistra a destra — un boolean prevale su ogni numero, così TRUE che perde contro 3 è esattamente ciò che rende FALSE il controllo di intervallo in catena

Numeri, testo e boolean: l'ordinamento tra tipi

Quando i due lati di un confronto hanno tipi diversi, Excel non converte l'uno nell'altro. Ordina invece i tipi: ogni numero è minore di ogni valore testo, e ogni valore testo è minore di ogni boolean. HotXLS classifica entrambi gli operandi come numero, testo o boolean e, quando le classi differiscono, confronta le sole classi. Così =TRUE>99999 è TRUE, ="1">2 è TRUE, e =FALSE>"zzz" è TRUE anche lei. Questo è il pezzo che fa sembrare arbitrarie le catene di confronto. TRUE<3 non è TRUE convertito a 1 e confrontato con 3; è un boolean confrontato con un numero, e il boolean vince

Dentro una stessa classe il confronto è quello naturale, con una variante per il testo: il confronto tra stringhe ignora le maiuscole, quindi ="abc"="ABC" restituisce TRUE in Excel e in HotXLS. Le date sono numeri seriali per il motore, il che significa che una data è sempre sotto qualunque testo, inclusa una cella di testo che sembra solo una data. Una colonna di date importate dove metà delle celle è arrivata come testo verrà ordinata e confrontata in due fasce separate, e =A2>DATE(2026,1,1) restituisce TRUE per ognuna delle celle di testo

A cosa è uguale una cella vuota in un confronto?

Una cella vuota usata come operando di confronto prende il tipo dell'altro lato: è uguale a 0 contro un numero, a "" contro testo e a FALSE contro un valore logico. Con A1 vuota, =A1=0, =A1="" e =A1=FALSE sono tutti TRUE in Excel. HotXLS fa la stessa sostituzione prima di ordinare i due operandi, per tutti e sei gli operatori di confronto e in entrambi i motori. Quando entrambi gli operandi sono vuoti non avviene nessuna sostituzione, e si confrontano come uguali

var
  Wb: IXLSWorkbook;
  Sh: TXLSWorksheet;
begin
  Wb := TXLSWorkbook.Create;
  Sh := Wb.Sheets.Add;                  // il primo foglio diventa quello attivo
  Sh.Range['B1', 'B1'].Value := 5;      // A1 resta vuota di proposito

  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: il vuoto confronta come 0
  Writeln(VarToStr(Wb.Calculate('=A1<0')));      // False: 0 non è sotto 0
end;

L'ultima riga è quella che morde nella pratica. L'aritmetica tratta già un vuoto come 0, quindi =A1+1 dà 1 in qualsiasi motore. Il confronto è dove la scorciatoia va male. Un motore che conserva "vuoto" come proprio rango più basso, sotto ogni numero, risponde TRUE per =A1<0, e allora =IF(A1<0,"overdrawn","ok") etichetta ogni cella di saldo vuota come scoperta. Sostituire solo 0 o la stringa vuota non basta nemmeno: contro un boolean il vuoto diventerebbe 0, che è sotto sia TRUE che FALSE, e =A1=FALSE uscirebbe FALSE su una cella vuota

Sostituzione dell'operando vuoto in HotXLS: una A1 vuota è uguale a 0 e al testo vuoto, un vuoto contro un boolean confronta come FALSE così =A1=FALSE è TRUE come in Excel, mentre un vuoto tenuto come rango più basso renderebbe =A1<0 TRUE per ogni saldo vuoto
La sostituzione replica il tipo dell'altro operando, 0, la stringa vuota o FALSE — un rango Null grezzo è ciò che etichetterebbe ogni saldo vuoto come scoperto

C'è una conseguenza da pianificare. Poiché un vuoto confronta come FALSE contro un valore logico, =A1=FALSE non può distinguere una cella vuota da una cella che contiene FALSE, in Excel come in HotXLS, e =A1=0 non può distinguere vuoto da zero. I template che devono sapere se qualcuno ha davvero inserito un valore dovrebbero testare con ISBLANK(A1). La forma =A1="" è più debole, visto che è TRUE anche per una formula che restituisce una stringa vuota

Perché SUMIF con intervallo somma a una cella copre l'intera colonna?

Excel conserva la forma dell'intervallo criteri e usa l'intervallo somma solo per la sua cella in alto a sinistra, quindi =SUMIF(A1:A10,">5",B1) significa B1:B10. Parecchi template costruiti a mano contano su quella scorciatoia senza che i loro autori lo sappiano. L'implementazione ovvia, limitare il giro al più piccolo dei due intervalli, riduce l'esempio a un solo test di A1 contro B1 e restituisce 0. HotXLS percorre l'intervallo criteri e legge ogni valore allo stesso scostamento di riga e colonna dall'angolo in alto a sinistra dell'intervallo somma. AVERAGEIF passa per lo stesso giro e ottiene lo stesso ridimensionamento, e un intervallo somma più grande dell'intervallo criteri viene ridotto alla forma dei criteri

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;          // colonna criteri: 1..10
      Sheet.Cells[Row, 2].Value := Row * 100;    // importi: 100..1000
    end;
    Sheet.Cells[1, 4].Formula := '=SUMIF(A1:A10,">5",B1)';      // intervallo somma di una cella
    Sheet.Cells[2, 4].Formula := '=SUMIF(A1:A10,">5",B1:B10)';  // intervallo somma esplicito
    Sheet.Cells[3, 4].Formula := '=AVERAGEIF(A1:A10,">5",B1)';  // stesso ridimensionamento
    if Book.Recalculate = lxOk then
      // D1 e 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;
Ridimensionamento di SUMIF e AVERAGEIF in HotXLS dove =SUMIF(A1:A10,">5",B1) percorre l'intervallo criteri di dieci righe leggendo da B1 a B10 agli offset corrispondenti per un risultato di 4000, dove il limitarsi all'intervallo somma di una cella restituirebbe 0
Excel prende in prestito solo l'angolo in alto a sinistra dell'intervallo somma e conserva la forma dei criteri, così un template che passa B1 intende B1:B10 — SUMIF e AVERAGEIF percorrono tutti e dieci gli offset e riducono un intervallo oversize allo stesso modo

Per i template che scrivi tu, scrivi per intero l'intervallo somma. Il motore onora la scorciatoia perché i file esistenti dipendono da lei, ma B1 in una formula si legge come una cella sola alla prossima persona che manterrà il foglio, e quella persona assumerà ragionevolmente che si stia sommando un solo importo. Il criterio in mezzo viene valutato come valore singolo mentre i due argomenti esterni sono riferimenti; quella divisione tra classi di argomenti è spiegata in l'articolo su intersezione implicita e classi di argomenti. I totali condizionali che devono anche saltare le righe filtrate o nascoste seguono regole diverse ancora, trattate in l'articolo su SUBTOTAL e AGGREGATE e le righe nascoste

Stili di riferimento di INDIRECT e basis 0 di YEARFRAC

Due funzioni minori portano lo stesso tipo di trappola. INDIRECT prende un secondo argomento opzionale, a1, e quando è FALSE il testo è un riferimento R1C1: =INDIRECT("R2C3",FALSE) legge C2. Un parser che ignora il flag legge "R2" come colonna R, riga 2, che è una cella reale, quindi la formula restituisce un valore plausibile dal posto sbagliato. HotXLS legge il flag come boolean, numero (zero significa FALSE) o testo "FALSE", e converte il testo R1C1 prima di risolverlo, con offset tra parentesi come R[1]C[1] misurati dalla cella che tiene la formula. Testo con qualcosa di avanzato dopo un riferimento valido, come "B2 junk", restituisce #REF! invece di usare B2 in silenzio

YEARFRAC con basis 0 usa il conteggio giorni 30/360 US (NASD), incluse le regole dell'ultimo di febbraio che DAYS360 applica: quando entrambe le date sono l'ultimo giorno di febbraio, il giorno finale diventa 30, e poi un inizio all'ultimo giorno di febbraio diventa 30. Dal 29-02-2024 al 28-02-2025 questo dà 360 giorni e una frazione di esattamente 1. Un conteggio 30/360 semplice che salta quelle regole dà 359 giorni e 0,99722, e la differenza affiora negli accumuli di interessi che attraversano la fine di febbraio

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)')));   // riga 2, colonna 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;

Quando documenti o testi un comportamento simile, prendi i valori attesi da Excel stesso e non da ciò che la formula sembra dire. "1 è minore di 2 è minore di 3" suggerisce che =1<2<3 sia TRUE, che è precisamente la risposta che dà un parser associativo a destra e l'opposto di ciò che Excel restituisce. Una breve tabella di formule con accanto i valori che Excel mostra becca tutti e quattro i casi di questo articolo: le catene, i vuoti, il ridimensionamento di SUMIF e il conteggio dei giorni di febbraio

HotXLS legge, ricalcola e scrive XLS, XLSX, ODS e CSV da Delphi e C++Builder senza Excel sulla macchina, e le regole di confronto, cella vuota e aggregati condizionali di sopra appartengono al motore di calcolo condiviso da entrambe le classi di cartelle di lavoro. L'elenco delle funzioni supportate, le edizioni e le licenze sono sulla pagina del componente fogli elettronici Delphi HotXLS