O HotXLS Delphi Component avalia =1<2<3 como FALSE, a mesma resposta que o Excel 16 dá, porque desde a v2.384.3 o parser de fórmulas dele dobra operadores de comparação da esquerda para a direita: 1<2 vira TRUE, e TRUE<3 é FALSE porque um boolean classifica acima de qualquer número. A mesma release faz um operando vazio igualar tanto 0 quanto "", e deixa o SUMIF esticar um intervalo de soma de uma célula para a forma do intervalo de critérios dele. Cada um desses parece detalhe de trivia até uma pasta de trabalho calculada em Delphi discordar da mesma pasta aberta no Excel
A discordância normalmente começa com uma fórmula que alguém escreveu por intuição. Alguém digita =0<B2<100 para conferir que uma quantidade está na faixa, o Excel responde FALSE para toda linha sem fazer barulho, e a planilha vai para produção com esse bug assado. Um motor de cálculo não tem o direito de corrigir a intenção do usuário; o trabalho dele é produzir o valor que o Excel produziria, para que o resultado em cache que o HotXLS grava no arquivo bata com o que o Excel mostra depois de um recálculo. Antes da v2.384.3 o HotXLS respondia TRUE para essa checagem de faixa em toda linha, errado na direção oposta, e um relatório gerado num servidor contradizia o mesmo relatório aberto num desktop
Por que =1<2<3 devolve FALSE no Excel?
O Excel devolve FALSE porque lê uma cadeia de comparações como (1<2)<3, e o TRUE interno então perde a disputa de classificação de tipos contra o número 3. O parser antigo do HotXLS lia o mesmo texto como 1<(2<3): o TXLSSyntax.Parse_expr no lxFormula.pas parseava um operando, via um token de comparação e recursava no Parse_expr para o lado direito, o que torna o operador associativo à direita. Isso dá 1<TRUE, e um número fica abaixo de um boolean, então o resultado era TRUE. O engano é simétrico: =3>2>1 é TRUE no Excel e era FALSE no HotXLS, e =1=1=TRUE é TRUE no Excel e era FALSE antes do fix. A regressão CalculateFormula_ComparisonChainsFoldLeftToRight fixa sete fórmulas desse tipo contra os valores que o Excel 16 devolve, e roda cada uma pelas duas arquiteturas de motor, o TXLSWorkbook Classic e o TXLSXWorkbook nativo de XLSX, usando o método Calculate descrito em a visão geral do motor de fórmulas do HotXLS
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');
// O que o Excel 16 devolve: FALSE, TRUE, FALSE, TRUE, TRUE, TRUE, TRUE
var
Classic: IXLSWorkbook;
Xlsx: TXLSXWorkbook;
i: Integer;
begin
Classic := TXLSWorkbook.Create;
Xlsx := TXLSXWorkbook.Create;
try
// TXLSXWorkbook.Calculate avalia contra a planilha ativa e
// devolve Null quando a pasta de trabalho não tem planilha alguma
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;
O fix transforma o Parse_expr num loop da mesma forma que o Parse_expr1 já usava para +, - e &. Ele parseia o primeiro operando com o Parse_expr1, e enquanto o próximo token é um de =, <>, <, >, <= ou >=, cria um nó de comparação, pendura o resultado esquerdo acumulado como primeiro filho, parseia o próximo operando com o Parse_expr1 em vez do Parse_expr, e torna o nó novo o resultado esquerdo da próxima rodada. Dois detalhes eram fáceis de errar ao converter recursão em iteração, e ambos estão nas notas dos mantenedores: o nó acumulado precisa ser entregue (lChild := Item; Item := nil) nessa ordem, e o caminho de erro precisa dar Exit depois de liberar o nó meio construído em vez de cair fora do loop e devolver uma árvore pendurada
Como o HotXLS classifica números, texto e booleans numa comparação?
O HotXLS classifica tipos mistos do jeito que o Excel faz: todo número é menor que todo valor de texto, e todo valor de texto é menor que todo boolean. O TXLSCalculator.CompareVariants no lxCalc.pas classifica os dois operandos com o GetRetValueType na enumeração TXLSRetValueType = (xlVariantValue, xlNumberValue, xlStringValue, xlBooleanValue), e quando as duas classes diferem ele simplesmente compara os ordinais, então a ordem de declaração desse enum é a regra entre tipos. Dentro de uma classe a comparação é a natural, com uma torção específica do Excel para texto: ambas as strings passam pelo lxUpperCase primeiro, então ="abc"="ABC" é TRUE. É essa classificação que torna o resultado da cadeia impossível de raciocinar sem ela. TRUE<3 não é uma coerção de TRUE para 1, é um boolean comparado com um número, e o boolean ganha. Datas são números de série para o motor (varDate classifica como xlNumberValue), então uma data fica sempre abaixo de qualquer texto, inclusive texto que por acaso pareça uma data
O que uma célula vazia iguala numa comparação?
Uma célula vazia usada como operando de comparação iguala 0 quando o outro lado é número, iguala "" quando o outro lado é texto, e desde a v2.384.53 iguala FALSE quando o outro lado é um valor lógico, então com A1 vazia =A1=0, =A1="" e =A1=FALSE são todos TRUE. O TXLSCalculator.CompareVarValues, que atende os seis operadores de comparação, substitui o vazio antes de chamar o CompareVariants: se exatamente um operando é Null ele vira WideString('') quando o parceiro é string, False quando o parceiro é boolean, e 0 caso contrário. Dois vazios ainda comparam iguais entre si sem substituição. O caminho aritmético sempre transformou vazio em 0, por isso =A1+1 dava 1, mas o CompareVariants mantinha Null como o próprio rank mais baixo, abaixo de todo número, e os operadores de comparação usavam esse rank direto
var
Wb: IXLSWorkbook;
Sh: TXLSWorksheet;
begin
Wb := TXLSWorkbook.Create;
Sh := Wb.Sheets.Add;
Sh.Range['B1', 'B1'].Value := 5; // A1 fica vazia de propósito
Writeln(VarToStr(Wb.Calculate('=A1=0'))); // True
Writeln(VarToStr(Wb.Calculate('=A1=""'))); // True
Writeln(VarToStr(Wb.Calculate('=A1<B1'))); // True: o vazio compara como 0
Writeln(VarToStr(Wb.Calculate('=A1<0'))); // False; True antes da v2.384.3
end;
A última linha é a que doeu na prática. Sob o rank antigo, um vazio era menor que todo número, negativos incluídos, então =IF(A1<0,"overdrawn","ok") rotulava toda célula de saldo vazia como estourada, e =A1=0 era FALSE para uma célula que qualquer usuário descreveria como zero. Uma fronteira ficou depois da v2.384.3: a substituição escolhia só entre 0 e a string vazia, então um vazio comparado com um boolean virava 0, que classifica abaixo de TRUE e FALSE, e =A1=FALSE numa A1 vazia avaliava para FALSE. Desde o HotXLS 2.384.53 um vazio comparado com um valor lógico é tratado como FALSE nos dois motores, XLS e XLSX, como o Excel faz: com A1 vazia, =A1=FALSE e =A1<TRUE devolvem TRUE e =A1=TRUE devolve FALSE. Isso também significa que a comparação não distingue vazio de FALSE, no Excel nem no HotXLS; quando a planilha precisa dessa distinção, teste com ISBLANK ou =A1=""
Por que o SUMIF com intervalo de soma de uma célula devolvia 0?
O SUMIF devolvia 0 porque o HotXLS travava a iteração no menor dos dois intervalos, enquanto o Excel mantém a forma do intervalo de critérios e usa o intervalo de soma só pela célula do canto superior esquerdo. =SUMIF(A1:A10,">5",B1) portanto significa B1:B10 no Excel, uma conveniência de que muitos templates feitos à mão dependem. O worker compartilhado TXLSCalculator.GetValueItemRange2 encolhia as contagens de linha e coluna para as do intervalo de valores, o que reduzia o exemplo a um único teste de A1 contra B1. A v2.384.3 remove o travamento: o loop agora percorre o intervalo de critérios e lê cada valor no mesmo offset a partir do canto superior esquerdo do intervalo de soma. Como CalcSumIF e CalcAverageIF chamam esse worker, o AVERAGEIF ganha o mesmo redimensionamento, e um intervalo de soma maior que o de critérios é aparado para a forma dos critérios pela mesma razão. O argumento de critérios no meio é um argumento de classe valor e os dois externos são de classe referência, a distinção coberta em o artigo sobre interseção implícita e classes de argumento
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; // coluna de critérios: 1..10
Sheet.Cells[Row, 2].Value := Row * 100; // valores: 100..1000
end;
Sheet.Cells[1, 4].Formula := '=SUMIF(A1:A10,">5",B1)'; // intervalo de soma de uma célula
Sheet.Cells[2, 4].Formula := '=SUMIF(A1:A10,">5",B1:B10)'; // intervalo de soma explícito
if Book.Recalculate = lxOk then
// D1 e D2 são 4000 (600+700+800+900+1000); D1 era 0 antes da v2.384.3
Writeln(VarToStr(Sheet.Cells[1, 4].Value), ' ', VarToStr(Sheet.Cells[2, 4].Value));
finally
Book.Free;
end;
end;
INDIRECT e YEARFRAC: duas correções mais silenciosas
O INDIRECT agora respeita seu segundo argumento, e texto depois de uma referência válida é erro em vez de ser ignorado. Com a1 FALSE o texto é parseado como R1C1 absoluto, então =INDIRECT("R2C3",FALSE) lê C2; o código antigo ignorava a flag, lia "R2" como coluna R, linha 2, e silenciosamente devolvia a célula errada. A flag é despachada pelo tipo do variant (boolean, número ou texto) porque converter um variant string direto para Double levanta exceção. Texto R1C1 relativo como R[1]C[1] devolve #REF!, já que o INDIRECT não tem origem de célula de fórmula contra a qual resolver, e texto A1 com caracteres no final, "B2 junk", também devolve #REF!. O YEARFRAC com base 0 agora aplica as regras NASD de fim de fevereiro que o DAYS360 já implementava: quando ambas as datas são o último dia de fevereiro o dia final vira 30, depois um início no último dia de fevereiro vira 30. De 2024-02-29 a 2025-02-28 a contagem agora é 360 dias, uma fração de exatamente 1, onde o antigo Days360US contava 359
O que esses fixes garantem, e qual foi a lição?
O comportamento de cadeias de comparação é garantido por um teste que compara os dois motores com valores medidos no Excel 16, e esse teste existe porque a primeira descrição do fix estava errada. A nota de release da v2.384.3 originalmente dizia que o dobramento da esquerda para a direita tornava =1<2<3 TRUE, o que é precisamente o que o antigo parser associativo à direita produzia e o oposto do que o Excel e o código novo devolvem. Ninguém tinha avaliado o exemplo; ele foi escrito a partir da intuição de que "1 é menor que 2 é menor que 3". A nota foi corrigida e o teste de sete fórmulas adicionado num commit de follow-up, e a regra que saiu disso vale para qualquer um documentando semântica de planilha: rode o exemplo no Excel antes de anotar o valor esperado. A substituição de operando vazio e o redimensionamento do SUMIF seguem o mesmo comportamento do Excel, incluindo o caso vazio versus boolean desde a v2.384.53, e agregações condicionais que também precisam pular linhas filtradas ou ocultas seguem as regras separadas em o artigo de linhas ocultas do SUBTOTAL e AGGREGATE
O HotXLS é um componente de planilha nativo para Delphi e C++Builder que lê, recalcula e escreve XLS, XLSX, ODS e CSV sem Excel instalado, e as regras de comparação, vazio e SUMIF descritas aqui vivem no motor de cálculo que as duas arquiteturas de pasta de trabalho compartilham. A lista completa de funções e as opções de licenciamento estão na página do produto componente de planilha HotXLS para Delphi