Uma checagem de faixa escrita como =0<B2<100 devolve FALSE no Excel em toda linha, inclusive na linha em que B2 guarda 50. O Excel avalia 0<B2 primeiro, obtém TRUE, e então pergunta se TRUE é menor que 100; um valor lógico classifica acima de todo número, então a resposta é FALSE não importa o que B2 contenha. Células vazias e SUMIF têm surpresas do mesmo tipo: uma célula vazia iguala 0, "" e FALSE ao mesmo tempo, e =SUMIF(A1:A10,">5",B1) soma dez células da coluna B
Um motor de cálculo que recalcula um workbook desses fora do Excel tem que reproduzir essas respostas, mesmo quando obviamente não é o que o autor quis dizer. O resultado em cache gravado no arquivo é o que um visualizador, uma prévia de email ou um import a jusante vê antes de alguém apertar F9. Se o servidor grava TRUE para a checagem de faixa e o Excel depois mostra FALSE, o mesmo relatório contém dois números diferentes dependendo de onde foi aberto, e ninguém consegue dizer em qual confiar. Consertar a fórmula do autor é trabalho do autor
Por que =1<2<3 devolve FALSE no Excel?
O Excel devolve FALSE para =1<2<3 porque os seis operadores de comparação compartilham um nível de precedência e se associam da esquerda para a direita, então a fórmula significa (1<2)<3, e o TRUE interno então perde a disputa de classificação de tipos contra o número 3. O HotXLS Delphi Component faz o parse de cadeias de comparação do mesmo jeito nos dois engines de workbook dele, o TXLSWorkbook Classic e o TXLSXWorkbook nativo de XLSX. O parser lê um operando, e enquanto o próximo token é =, <>, <, >, <= ou >=, ele constrói um nó de comparação com tudo o que foi parseado até agora como filho esquerdo e o próximo operando como filho direito, e então leva esse nó para a próxima rodada. Essa é a mesma forma de loop que ele usa para +, - e &
A armadilha está na gramática de livro-texto. Escrita como expr = operand op expr, uma gramática de descida recursiva recursa nela mesma para o lado direito e faz silenciosamente o operador associativo à direita. Sob essa leitura, =1<2<3 vira 1<(2<3), que é 1<TRUE, que é TRUE. O erro corre nas duas direções: =3>2>1 é TRUE no Excel e FALSE sob associatividade à direita, e =1=1=TRUE vira do mesmo jeito. Formas com parênteses saem certas de qualquer jeito, então o engano escapa de qualquer teste que só confere (a<b)<c
O método Calculate avalia uma string de fórmula sem colocá-la em uma célula, o que o torna um jeito rápido de conferir uma lista inteira de cadeias contra os dois engines. O método é coberto com mais profundidade em a visão geral do motor de fórmulas do HotXLS
uses
SysUtils, Variants, lxCalc, lxHandle, lxHandleX; // lxOk é declarado em 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');
// O que o Excel responde: FALSE, TRUE, FALSE, TRUE, TRUE, TRUE, TRUE
var
Classic: IXLSWorkbook;
Xlsx: TXLSXWorkbook;
i: Integer;
begin
Classic := TXLSWorkbook.Create; // com contagem de referências pela interface
Xlsx := TXLSXWorkbook.Create;
try
// TXLSXWorkbook.Calculate avalia contra a sheet ativa e
// devolve Null quando o workbook ainda não tem sheet
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;
Números, texto e booleans: a classificação entre tipos
Quando os dois lados de uma comparação têm tipos diferentes, o Excel não converte um no outro. Ele classifica os tipos em vez disso: todo número é menor que todo valor de texto, e todo valor de texto é menor que todo boolean. O HotXLS classifica os dois operandos como número, texto ou boolean e, quando as classes diferem, compara só as classes. Então =TRUE>99999 é TRUE, ="1">2 é TRUE, e =FALSE>"zzz" também é TRUE. Essa é a peça que faz comparações encadeadas parecerem arbitrárias. TRUE<3 não é TRUE coercido para 1 e comparado com 3; é um boolean comparado com um número, e o boolean ganha
Dentro de uma classe a comparação é a natural, com uma torção para texto: a comparação de strings ignora caixa, então ="abc"="ABC" devolve TRUE no Excel e no HotXLS. Datas são números de série para o engine, o que significa que uma data sempre classifica abaixo de qualquer texto, inclusive uma célula de texto que apenas pareça uma data. Uma coluna de datas importadas em que metade das células chegou como strings vai ordenar e comparar em duas faixas separadas, e =A2>DATE(2026,1,1) devolve TRUE para cada uma das células de string
O que uma célula vazia iguala numa comparação?
Uma célula vazia usada como operando de comparação assume o tipo do outro lado: ela iguala 0 contra um número, "" contra texto e FALSE contra um valor lógico. Com A1 vazia, =A1=0, =A1="" e =A1=FALSE são todos TRUE no Excel. O HotXLS faz a mesma substituição antes de classificar os dois operandos, para os seis operadores de comparação e nos dois engines. Quando os dois operandos estão vazios, nenhuma substituição acontece, e eles comparam iguais
var
Wb: IXLSWorkbook;
Sh: TXLSWorksheet;
begin
Wb := TXLSWorkbook.Create;
Sh := Wb.Sheets.Add; // a primeira sheet vira a ativa
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=FALSE'))); // True
Writeln(VarToStr(Wb.Calculate('=A1<B1'))); // True: o vazio compara como 0
Writeln(VarToStr(Wb.Calculate('=A1<0'))); // False: 0 não está abaixo de 0
end;
A última linha é a que morde na prática. A aritmética já trata um vazio como 0, então =A1+1 dá 1 em qualquer engine. A comparação é onde um atalho dá errado. Um engine que mantém "vazio" como o próprio rank mais baixo dele, abaixo de todo número, responde TRUE para =A1<0, e então =IF(A1<0,"overdrawn","ok") rotula toda célula de saldo vazia como estourada. Substituir só por 0 ou pela string vazia também não basta: contra um boolean o vazio viraria 0, que classifica abaixo de TRUE e de FALSE, e =A1=FALSE sairia FALSE numa célula vazia
Há uma consequência que vale planejar. Como um vazio compara como FALSE contra um valor lógico, =A1=FALSE não distingue uma célula vazia de uma célula que guarda FALSE, no Excel ou no HotXLS, e =A1=0 não distingue vazio de zero. Templates que precisam saber se alguém de fato digitou um valor devem testar com ISBLANK(A1). A forma =A1="" é mais fraca, já que também é TRUE para uma fórmula que devolve uma string vazia
Por que o SUMIF com intervalo de soma de uma célula cobre a coluna inteira?
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 dele, então =SUMIF(A1:A10,">5",B1) significa B1:B10. Muitos templates feitos à mão dependem dessa abreviação sem que os autores saibam. A implementação óbvia, travar o percurso no menor dos dois intervalos, reduz o exemplo a um único teste de A1 contra B1 e devolve 0. O HotXLS percorre o intervalo de critérios e lê cada valor no mesmo offset de linha e coluna a partir do canto superior esquerdo do intervalo de soma. O AVERAGEIF passa pelo mesmo percurso e ganha o mesmo redimensionamento, e um intervalo de soma maior que o de critérios é aparado para a forma dos critérios
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
Sheet.Cells[3, 4].Formula := '=AVERAGEIF(A1:A10,">5",B1)'; // mesmo redimensionamento
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;
Para templates que você mesmo escreve, escreva o intervalo de soma por inteiro. O engine honra a abreviação porque arquivos existentes dependem dela, mas um B1 numa fórmula parece uma única célula para a próxima pessoa que mantiver a sheet, e essa pessoa vai razoavelmente assumir que só um valor está sendo somado. O critério no meio é avaliado como um único valor enquanto os dois argumentos externos são referências; essa divisão entre classes de argumento é explicada em o artigo sobre interseção implícita e classes de argumento. Totais condicionais que também precisam pular linhas filtradas ou ocultas seguem regras diferentes de novo, cobertos em o artigo de linhas ocultas do SUBTOTAL e AGGREGATE
Estilos de referência do INDIRECT e base 0 do YEARFRAC
Duas funções menores carregam o mesmo tipo de armadilha. O INDIRECT recebe um segundo argumento opcional, a1, e quando ele é FALSE o texto é uma referência R1C1: =INDIRECT("R2C3",FALSE) lê C2. Um parser que ignora a flag lê "R2" como coluna R, linha 2, que é uma célula real, então a fórmula devolve um valor plausível do lugar errado. O HotXLS lê a flag como boolean, número (zero significa FALSE) ou o texto "FALSE", e converte texto R1C1 antes de resolvê-lo, com offsets entre colchetes como R[1]C[1] medidos a partir da célula que guarda a fórmula. Texto com qualquer coisa sobrando depois de uma referência válida, como "B2 junk", devolve #REF! em vez de usar B2 em silêncio
O YEARFRAC com base 0 usa a contagem de dias 30/360 US (NASD), incluindo as regras de último de fevereiro que o DAYS360 aplica: quando ambas as datas são o último dia de fevereiro, o dia final vira 30, e então um início no último dia de fevereiro vira 30. De 2024-02-29 a 2025-02-28 isso dá 360 dias e uma fração de exatamente 1. Uma contagem 30/360 simples que pula essas regras dá 359 dias e 0,99722, e a diferença aparece em acumulações de juros que atravessam o fim de fevereiro
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)'))); // linha 2, coluna 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 você documenta ou testa comportamento assim, tire os valores esperados do próprio Excel e não do que a fórmula parece dizer. "1 é menor que 2 é menor que 3" sugere que =1<2<3 é TRUE, que é precisamente a resposta que um parser associativo à direita dá e o oposto do que o Excel devolve. Uma tabela curta de fórmulas com os valores que o Excel mostra ao lado pega os quatro casos deste artigo: cadeias, vazios, o redimensionamento do SUMIF e a contagem de dias de fevereiro
O HotXLS lê, recalcula e grava XLS, XLSX, ODS e CSV a partir de Delphi e C++Builder sem Excel na máquina, e as regras de comparação, de célula vazia e de agregação condicional acima pertencem ao motor de cálculo que as duas classes de workbook compartilham. A lista de funções suportadas, as edições e o licenciamento estão na página do produto componente de planilha Delphi HotXLS