O HotXLS Delphi Component avalia =1<2<3 como FALSE, a mesma resposta que o Excel 16 dá, porque desde a v2.384.3 que o seu parser de fórmulas dobra operadores de comparação da esquerda para a direita: 1<2 torna-se TRUE, e TRUE<3 é FALSE porque um boolean fica acima de qualquer número. A mesma versão faz um operando vazio igualar tanto 0 como "", e deixa o SUMIF esticar um intervalo de soma de uma célula até à forma do seu intervalo de critérios. Cada uma destas parece trivialidade até um livro calculado no Delphi discordar do mesmo livro aberto no Excel
A discordância costuma começar numa fórmula que alguém escreveu por intuição. Alguém digita =0<B2<100 para verificar que uma quantidade está no intervalo, o Excel responde FALSE em silêncio a todas as linhas, e a folha sai com esse bug cozido. Um motor de cálculo não tem autoridade para corrigir a intenção do utilizador; o trabalho dele é produzir o valor que o Excel produziria, para que o resultado em cache que o HotXLS escreve no ficheiro bata com o que o Excel mostra depois de um recálculo. Antes da v2.384.3 o HotXLS respondia TRUE a essa verificação de intervalo em todas as linhas, errado na direção oposta, e um relatório gerado num servidor contradizia o mesmo relatório aberto num desktop
Porque é 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 interior perde depois o concurso de ordenação de tipos contra o número 3. O velho parser do HotXLS lia o mesmo texto como 1<(2<3): o TXLSSyntax.Parse_expr no lxFormula.pas analisava um operando, via um token de comparação, e recorria ao Parse_expr para o lado direito, o que torna o operador associativo à direita. Isso dá 1<TRUE, e um número está abaixo de um boolean, por isso 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 da correção. A regressão CalculateFormula_ComparisonChainsFoldLeftToRight fixa sete fórmulas assim contra os valores que o Excel 16 devolve, e corre cada uma pelas duas arquiteturas de motor, o clássico TXLSWorkbook e o TXLSXWorkbook nativo de XLSX, usando o método Calculate descrito na 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
// O TXLSXWorkbook.Calculate avalia contra a folha ativa e
// devolve Null quando o livro não tem folha nenhuma
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;
A correção transforma o Parse_expr num loop da mesma forma que o Parse_expr1 já usava para +, - e &. Analisa o primeiro operando com o Parse_expr1, e enquanto o próximo token for um de =, <>, <, >, <= ou >=, cria um nó de comparação, apega o resultado esquerdo acumulado como primeiro filho, analisa o próximo operando com o Parse_expr1 em vez do Parse_expr, e faz do novo nó o resultado esquerdo da ronda seguinte. 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 tem de ser entregue (lChild := Item; Item := nil) nessa ordem, e o caminho de erro tem de fazer Exit depois de libertar o nó meio construído em vez de cair fora do loop e devolver uma árvore pendurada
Como ordena o HotXLS números, texto e booleans numa comparação?
O HotXLS ordena tipos mistos como o Excel faz: qualquer número é menor do que qualquer valor de texto, e qualquer valor de texto é menor do que qualquer boolean. O TXLSCalculator.CompareVariants no lxCalc.pas classifica ambos os operandos com o GetRetValueType na enumeração TXLSRetValueType = (xlVariantValue, xlNumberValue, xlStringValue, xlBooleanValue), e quando as duas classes diferem compara simplesmente os seus ordinais, por isso 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 primeiro pelo lxUpperCase, por isso ="abc"="ABC" é TRUE. Esta ordenação é a razão pela qual o resultado da cadeia não se consegue 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-se como xlNumberValue), por isso uma data está sempre abaixo de qualquer texto, incluindo texto que por acaso pareça uma data
A que iguala uma célula vazia numa comparação?
Uma célula vazia usada como operando de comparação iguala 0 quando o outro lado é um número, iguala "" quando o outro lado é texto, e desde a v2.384.53 iguala FALSE quando o outro lado é um valor lógico, por isso com A1 vazio =A1=0, =A1="" e =A1=FALSE são todos TRUE. O TXLSCalculator.CompareVarValues, que serve os seis operadores de comparação, substitui o vazio antes de chamar o CompareVariants: se exatamente um operando é Null torna-se WideString('') quando o parceiro é uma string, False quando o parceiro é um boolean, e 0 nos restantes casos. Dois vazios continuam a comparar iguais entre si sem substituição. O caminho aritmético sempre tinha tornado um vazio em 0, razão pela qual =A1+1 dava 1, mas o CompareVariants mantinha Null como o seu próprio escalão mais baixo, abaixo de qualquer número, e os operadores de comparação usavam esse escalão diretamente
var
Wb: IXLSWorkbook;
Sh: TXLSWorksheet;
begin
Wb := TXLSWorkbook.Create;
Sh := Wb.Sheets.Add;
Sh.Range['B1', 'B1'].Value := 5; // A1 fica vazio 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 doe na prática. Sob o velho escalão, um vazio era menor do que qualquer número, negativos incluídos, por isso =IF(A1<0,"overdrawn","ok") rotulava cada célula de saldo vazio como descoberto, e =A1=0 era FALSE para uma célula que qualquer utilizador descreveria como zero. Uma fronteira ficou depois da v2.384.3: a substituição escolhia só entre 0 e a string vazia, por isso um vazio comparado com um boolean tornava-se 0, que fica abaixo tanto de TRUE como de FALSE, e =A1=FALSE num A1 vazio avaliava a FALSE. Desde o HotXLS 2.384.53 que um vazio comparado com um valor lógico é tratado como FALSE em ambos os motores XLS e XLSX, como o Excel faz: com A1 vazio, =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 ou no HotXLS; quando uma folha precisa dessa distinção, teste com ISBLANK ou =A1=""
Porque é que o SUMIF com um intervalo de soma de uma célula devolvia 0?
O SUMIF devolvia 0 porque o HotXLS prendia a iteração ao mais pequeno dos dois intervalos, enquanto o Excel mantém a forma do intervalo de critérios e só usa o intervalo de soma para a sua célula do canto superior esquerdo. =SUMIF(A1:A10,">5",B1) significa portanto B1:B10 no Excel, uma conveniência de que muitos modelos feitos à mão dependem. O worker partilhado TXLSCalculator.GetValueItemRange2 encolhia as suas contagens de linhas e colunas para as do intervalo de valores, o que reduzia o exemplo a um único teste de A1 contra B1. A v2.384.3 remove a prisão: o loop agora percorre o intervalo de critérios e lê cada valor no mesmo desvio a partir do canto superior esquerdo do intervalo de soma. Como o CalcSumIF e o CalcAverageIF ambos chamam esse worker, o AVERAGEIF recebe o mesmo redimensionamento, e um intervalo de soma maior do que o intervalo de critérios é aparado à forma dos critérios pela mesma razão. O argumento de critérios no meio é um argumento de classe valor e os dois exteriores são de classe referência, a distinção coberta no artigo sobre interseção implícita e classes de argumentos
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
// Tanto D1 como 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 discretas
O INDIRECT agora respeita o seu segundo argumento, e texto depois de uma referência válida é um erro em vez de ser ignorado. Com o a1 a FALSE o texto é analisado como R1C1 absoluto, por isso =INDIRECT("R2C3",FALSE) lê C2; o código velho ignorava a flag, lia "R2" como coluna R, linha 2, e devolvia silenciosamente a célula errada. A flag é despachada pelo tipo variante (boolean, número ou texto) porque converter uma string variante diretamente para Double dispara uma exceção. Texto R1C1 relativo como R[1]C[1] devolve #REF!, porque o INDIRECT não tem origem de célula de fórmula contra a qual o resolver, e texto A1 com caracteres a mais, "B2 junk", também devolve #REF!. O YEARFRAC com base 0 aplica agora as regras NASD do fim de fevereiro que o DAYS360 já implementava: quando ambas as datas são o último dia de fevereiro o dia final torna-se 30, depois um início no último dia de fevereiro torna-se 30. De 2024-02-29 a 2025-02-28 a contagem é agora de 360 dias, uma fração de exatamente 1, onde o anterior Days360US contava 359
O que garantem estas correções, e qual foi a lição?
O comportamento da cadeia de comparações é garantido por um teste que compara ambos os motores com valores medidos no Excel 16, e esse teste existe porque a primeira descrição da correção estava errada. A nota da versão v2.384.3 dizia originalmente que a dobra da esquerda para a direita tornava =1<2<3 TRUE, o que é precisamente o que o velho parser associativo à direita produzia e o oposto do que tanto o Excel como o código novo devolvem. Ninguém tinha avaliado o exemplo; foi escrito a partir da intuição de que "1 é menor do que 2 é menor do que 3". A nota foi corrigida e o teste de sete fórmulas acrescentado num commit de seguimento, e a regra que daí saiu vale para quem documentar semântica de folhas de cálculo: corra o exemplo no Excel antes de escrever o valor esperado. A substituição de operandos vazios e o redimensionamento do SUMIF seguem o mesmo comportamento do Excel, incluindo o caso vazio-versus-boolean desde a v2.384.53, e os agregados condicionais que também têm de saltar linhas filtradas ou ocultas seguem as regras separadas no artigo sobre SUBTOTAL e AGGREGATE com linhas ocultas
O HotXLS é um componente de folhas de cálculo 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 aqui descritas vivem no motor de cálculo partilhado por ambas as arquiteturas de livro. A lista completa de funções e as opções de licenciamento estão na página do produto do componente de folhas de cálculo HotXLS para Delphi