O HotXLS, o componente de folha de cálculo nativo para Delphi e C++Builder, avalia XLOOKUP e XMATCH através de um núcleo de pesquisa partilhado. Esse núcleo aceita quatro modos de correspondência (-1, 0, 1, 2) e quatro modos de pesquisa (-2, -1, 1, 2), executa uma descida binária logarítmica sempre que o modo de pesquisa absoluto é 2, e rejeita qualquer outra combinação com um erro de fórmula
O relatório de bug que o traz aqui nunca diz "modo de pesquisa". Diz que o livro de trabalho gerado pelo servidor mostra um número diferente do mesmo ficheiro aberto no Excel, em talvez quatro linhas entre nove mil. Essas quatro linhas têm sempre algo em comum: uma chave de pesquisa duplicada, ou uma correspondência aproximada que teve de escolher um vizinho, ou uma coluna de pesquisa que alguém ordenou por uma coluna diferente na semana passada. As funções de pesquisa são onde um motor de fórmulas deixa de ser aritmética e passa a ser um contrato, e o contrato tem cláusulas que a maioria de quem chama nunca lê
Que números de modo aceita realmente o XLOOKUP?
Exatamente quatro de cada, e mais nada. O HotXLS valida match_mode contra -1, 0, 1 e 2 e search_mode contra -2, -1, 1 e 2 antes de tocar numa única célula, e qualquer outro valor devolve #VALUE! em vez de ser limitado ao modo legal mais próximo. Os quatro modos de correspondência são 0 para exato, -1 para exato ou o mais pequeno seguinte, 1 para exato ou o maior seguinte, e 2 para wildcard; os quatro modos de pesquisa são 1 para uma varredura linear para a frente, -1 para uma varredura linear para trás, 2 para uma pesquisa binária sobre dados ascendentes, e -2 para uma pesquisa binária sobre dados descendentes. Omiti-los seleciona o modo de correspondência 0 e o modo de pesquisa 1, o emparelhamento que quase todas as fórmulas reais usam. As contagens de argumentos são fiscalizadas da mesma forma: XLOOKUP aceita de três a seis argumentos e XMATCH aceita de dois a quatro, e qualquer coisa fora desses intervalos é um #VALUE! antes de a avaliação começar
// Shared by XLOOKUP and XMATCH, before any cell is read
if ((RequestedMatchMode <> -1) and (RequestedMatchMode <> 0) and
(RequestedMatchMode <> 1) and (RequestedMatchMode <> 2)) or
((RequestedSearchMode <> -2) and (RequestedSearchMode <> -1) and
(RequestedSearchMode <> 1) and (RequestedSearchMode <> 2)) then
begin
Result := lxErrorValue; // #VALUE!
Exit;
end;
if Abs(RequestedSearchMode) = 2 then
begin
if RequestedMatchMode = 2 then // wildcards cannot ride a binary descent
begin
Result := lxErrorValue;
Exit;
end;
// ... O(log n) descent over the lookup vector
end;Um passo antes há uma verificação mais silenciosa que vale a pena conhecer. Os argumentos de modo chegam como expressões de folha de cálculo, pelo que o HotXLS coage-os a um número, recusa NaN e infinito, e depois exige que o número seja igual ao seu próprio valor arredondado. XLOOKUP(x, A:A, B:B, "none", 0, 1.5) é um #VALUE!, não um modo de pesquisa 2 disfarçado. Isso importa quando o modo vem de uma célula que um cálculo com muito arredondamento produziu, o que é mais comum em livros de trabalho gerados do que em livros escritos à mão
Por que dá search_mode 2 a resposta errada em dados não ordenados?
Porque está a fazer exatamente aquilo que lhe pediu. O modo de pesquisa 2 diz ao motor que o vetor de pesquisa já está em ordem ascendente, e uma pesquisa binária não consegue verificar essa afirmação sem uma passagem O(n) que destruiria a razão de a usar. O HotXLS confia por isso em quem chama, divide o intervalo ao meio, e devolve o que quer que a descida encontre. Em entrada não ordenada, a resposta não é um erro, é silenciosamente errada, e isto é uma violação de contrato em vez de um defeito no motor
A Microsoft documenta a mesma assimetria para XLOOKUP e XMATCH: os modos binários exigem dados ordenados e produzem resultados inválidos caso contrário. A ISO 29500-1 cláusula 18.17, que define a gramática de fórmulas SpreadsheetML, transporta as descrições mais antigas de LOOKUP e VLOOKUP com o seu próprio requisito de ordem ascendente, e XLOOKUP e XMATCH são posteriores a esse texto o suficiente para viajarem no ficheiro como _xlfn.XLOOKUP e _xlfn.XMATCH sob a convenção de função futura. Geração diferente, mesmo acordo: quem chama fornece o invariante de ordenação, o motor fornece o logaritmo
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
begin
Book := TXLSXWorkbook.Create;
try
Sheet := Book.Sheets.Add('Rates');
Sheet.Cells[1, 1].Value := 40; Sheet.Cells[1, 2].Value := 0.10;
Sheet.Cells[2, 1].Value := 10; Sheet.Cells[2, 2].Value := 0.25;
Sheet.Cells[3, 1].Value := 30; Sheet.Cells[3, 2].Value := 0.15;
// Forward linear scan: finds key 40 wherever it sits
Sheet.Cells[5, 1].Formula := 'XLOOKUP(40,A1:A3,B1:B3,"missing",0,1)';
// Binary ascending: the promise was broken, the key is never visited
Sheet.Cells[6, 1].Formula := 'XLOOKUP(40,A1:A3,B1:B3,"missing",0,2)';
Book.SaveAs('lookup-modes.xlsx');
finally
Book.Free;
end;
end;Trace a segunda fórmula e a falha é completamente mecânica. A descida sonda a célula do meio, lê 10, decide que 10 é menor do que 40, descarta a metade esquerda incluindo a linha que realmente continha 40, sonda 30, descarta de novo, e fica sem intervalo. O Excel comporta-se da mesma forma, que é o objetivo: reproduzir a resposta errada é um requisito de compatibilidade, não uma cortesia. A premissa de ordenação é também mais rígida do que "números ascendentes", porque o comparador classifica os valores por tipo primeiro, pela ordem números, depois texto, depois booleanos, depois valores de erro, depois vazios, e só compara dentro de um tipo depois disso. Uma coluna de códigos de peça numéricos que tem três células a armazenar texto em vez disso não está ascendente sob esse comparador, por muito bem ordenada que pareça no ecrã, e os modos binários vão lê-la mal com toda a naturalidade
Onde ficam as chaves duplicadas?
Numa extremidade determinística da sequência de duplicados, e qual extremidade depende do modo de pesquisa em vez da sorte. Quando a descida binária encontra uma chave igual sob o modo de pesquisa 2, regista a posição e depois continua a estreitar para a esquerda, pelo que o resultado é o índice mais baixo da sequência; sob o modo de pesquisa -2, sobre dados descendentes, regista a posição e estreita para a direita, pelo que o resultado é o índice mais alto. Os modos lineares são mais simples: o modo de pesquisa 1 devolve o primeiro acerto para a frente, o modo de pesquisa -1 o primeiro acerto para trás. Este é o detalhe que produz a discrepância de quatro linhas do parágrafo de abertura, porque um livro de trabalho cujas chaves são únicas dá respostas idênticas sob os quatro modos de pesquisa e esconde a diferença em todos os testes que escreveu a partir de um ficheiro de amostra limpo. Acrescente um código de cliente duplicado a dados de produção e os modos começam a discordar precisamente nas linhas que duplicaram: nada mudou no motor, a entrada apenas deixou de ser um conjunto e passou a ser um multiconjunto
// A1:A7 holds 1, 3, 5, 5, 5, 7, 9 - ascending, with a run of three
Sheet.Cells[1, 3].Formula := 'XMATCH(5,A1:A7,0,1)'; // 3, first forward hit
Sheet.Cells[2, 3].Formula := 'XMATCH(5,A1:A7,0,-1)'; // 5, first reverse hit
Sheet.Cells[3, 3].Formula := 'XMATCH(5,A1:A7,0,2)'; // 3, lowest index of the run
// B1:B7 holds 9, 7, 5, 5, 5, 3, 1 - descending
Sheet.Cells[4, 3].Formula := 'XMATCH(5,B1:B7,0,-2)'; // 5, highest index of the runComo escolhe a correspondência aproximada o segundo lugar?
Mantendo um candidato ótimo ao lado da pesquisa de correspondência exata, e devolvendo-o apenas se não aparecer nenhum acerto exato. O HotXLS trata match_mode -1 como "o maior valor que não é maior do que o alvo" e match_mode 1 como "o menor valor que não é menor", e ambos são resolvidos sobre toda a região percorrida em vez de parar no primeiro vizinho aceitável. No percurso binário, a mesma ideia surge de graça a partir da descida: cada passo que ultrapassa ou fica aquém atualiza o candidato, pelo que o candidato final é o elemento limite ao lado da posição onde a chave teria sido inserida
// Linear path: refine the candidate only on a strict improvement
if (RequestedMatchMode = -1) or (RequestedMatchMode = 1) then
begin
CompareResult := CompareDynamicValues(CurrentValue, RequestedValue);
if ((RequestedMatchMode = -1) and (CompareResult <= 0) and
((CandidateIndex < 0) or
(CompareDynamicValues(CurrentValue, CandidateValue) > 0))) or
((RequestedMatchMode = 1) and (CompareResult >= 0) and
((CandidateIndex < 0) or
(CompareDynamicValues(CurrentValue, CandidateValue) < 0))) then
begin
CandidateIndex := ScanIndex;
CandidateValue := CurrentValue;
end;
end;Leia a condição interna com atenção, porque é aí que vive o desempate. Uma nova célula substitui o candidato vigente apenas quando é estritamente melhor, nunca quando meramente iguala, pelo que entre várias células que contêm o mesmo valor de segundo lugar, a que fica é a primeira encontrada na ordem de varredura: a de índice mais baixo numa varredura para a frente, a mais alta numa varredura para trás. Se XLOOKUP e XMATCH não encontram nem um acerto exato nem um vizinho aceitável, XLOOKUP recorre ao seu argumento if_not_found quando um foi fornecido e a #N/A quando não foi, enquanto XMATCH devolve sempre #N/A
Por que não podem os wildcards e a pesquisa binária coexistir?
Porque um padrão wildcard não é uma posição numa ordem. O modo de correspondência 2 pergunta se uma célula corresponde a uma máscara, e a correspondência de máscara responde sim ou não; uma descida binária precisa de uma resposta de três vias que lhe diga que metade manter. Não há forma defensável de perguntar se ACME-* está à esquerda ou à direita de uma dada célula, pelo que o HotXLS rejeita match_mode 2 combinado com search_mode 2 ou -2 à partida com #VALUE! em vez de adivinhar uma ordenação e produzir um disparate plausível. Os dois percursos também comparam valores de forma diferente, o que reforça a divisão: a varredura linear decide igualdade com uma comparação de texto insensível a maiúsculas, ou com correspondência de máscara quando os wildcards estão ativos, enquanto a descida binária decide igualdade perguntando ao comparador de ordenação por um zero. Isso é deliberado em vez de um acidente de camadas, uma vez que o percurso binário só pode usar a relação que está de facto a navegar. Se precisar de wildcards, use o modo de pesquisa 1 ou -1 e aceite o custo linear, que é a mesma troca que o rastreamento de dependências por detrás de recálculo incremental foi desenhado para manter fora do seu caminho crítico
Erros de forma: intervalos bidimensionais e vetores de retorno incompatíveis
Ambas as funções exigem um intervalo de pesquisa genuinamente unidimensional. Se o intervalo fornecido se estender por mais do que uma linha e mais do que uma coluna ao mesmo tempo, o HotXLS devolve #VALUE! em vez de escolher um eixo em seu nome, e um intervalo de linha única ou coluna única é lido ao longo do seu eixo longo. XLOOKUP acrescenta uma segunda regra de forma: o intervalo de retorno tem de ter exatamente o mesmo comprimento que o intervalo de pesquisa ao longo do eixo de correspondência, pelo que uma pesquisa vertical sobre 500 linhas emparelhada com um intervalo de retorno de 499 linhas é um erro, não um desvio de um resolvido silenciosamente na última linha. Quando o intervalo de retorno é mais largo do que uma coluna para uma pesquisa vertical, ou mais alto do que uma linha para uma horizontal, XLOOKUP devolve toda a fatia correspondida como um array e ela derrama para as células vizinhas sob as mesmas regras que as outras funções de array dinâmico, descritas no artigo sobre intervalos de derrame e arrays dinâmicos. Isso é genuinamente útil para extrair um registo inteiro de uma tabela com uma fórmula, e é também a forma mais rápida de sobrescrever uma coluna que pretendia manter
Escolher um modo quando ninguém está a olhar para o ecrã
A geração do lado do servidor merece uma política mais rígida do que o uso interativo, porque não há um humano para reparar que um total parece errado. A predefinição defensável é o modo de pesquisa 1 com o modo de correspondência 0: linear, exato, independente de ordem, e impossível de invalidar reordenando uma folha. Recorra ao modo de pesquisa 2 apenas onde o mesmo caminho de código também produziu a ordenação, na mesma execução, sobre a mesma coluna, e escreva essa dependência ao lado da fórmula, porque uma pesquisa binária numa coluna ordenada por uma chave diferente é a forma mais barata possível de calcular um número errado com confiança. Quando a pesquisa é genuinamente intensiva e os dados genuinamente ordenados, o retorno é real: a descida lê da ordem de log n células em vez de n, e cada uma dessas leituras passa por uma resolução completa de célula do livro de trabalho, pelo que a poupança é maior do que a contagem de instruções sugere
Se a forma do problema estiver mais próxima de uma regra de domínio do que de uma pesquisa, um callback para o seu próprio código Pascal, como coberto no artigo sobre funções personalizadas de folha de cálculo, normalmente vai superar qualquer arranjo engenhoso das funções incorporadas. As implementações de XLOOKUP e XMATCH aqui discutidas são disponibilizadas com o componente de folha de cálculo Delphi HotXLS standard, cuja página de produto contém a referência completa de funções suportadas para Delphi e C++Builder