O HotXLS, o componente de planilha nativo para Delphi e C++Builder, avalia XLOOKUP e XMATCH através de um núcleo de busca compartilhado. Esse núcleo aceita quatro modos de correspondência (-1, 0, 1, 2) e quatro modos de busca (-2, -1, 1, 2), executa uma descida binária logarítmica sempre que o modo de busca absoluto é 2, e rejeita qualquer outra combinação com um erro de fórmula
O relatório de bug que traz você até aqui nunca diz "modo de busca". Ele diz que a pasta de trabalho gerada pelo servidor mostra um número diferente do mesmo arquivo aberto no Excel, em talvez quatro linhas entre nove mil. Essas quatro linhas sempre têm algo em comum: uma chave de busca duplicada, ou uma correspondência aproximada que teve que escolher um vizinho, ou uma coluna de busca que alguém ordenou por uma coluna diferente semana passada. Funções de busca são onde um motor de fórmulas para de ser aritmética e começa a ser um contrato, e o contrato tem cláusulas que a maioria dos chamadores nunca lê
Quais números de modo o XLOOKUP realmente aceita?
Exatamente quatro de cada, e nada mais. O HotXLS valida match_mode contra -1, 0, 1 e 2 e search_mode contra -2, -1, 1 e 2 antes de tocar em uma única célula, e qualquer outro valor retorna #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 menor imediatamente maior, 1 para exato ou o maior imediatamente maior, e 2 para curinga; os quatro modos de busca são 1 para varredura linear para frente, -1 para varredura linear para trás, 2 para busca binária sobre dados ascendentes, e -2 para busca binária sobre dados descendentes. Omiti-los seleciona modo de correspondência 0 e modo de busca 1, o pareamento que quase toda fórmula real usa. As contagens de argumento são fiscalizadas da mesma forma: XLOOKUP recebe de três a seis argumentos e XMATCH recebe 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 planilha, então o HotXLS os converte para 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 busca 2 disfarçado. Isso importa quando o modo vem de uma célula que um cálculo pesado em arredondamento produziu, o que é mais comum em pastas de trabalho geradas do que em manuscritas
Por que search_mode 2 dá a resposta errada em dados não ordenados?
Porque ele está fazendo exatamente o que você pediu. O modo de busca 2 diz ao motor que o vetor de busca já está em ordem ascendente, e uma busca binária não pode verificar essa afirmação sem uma passagem O(n) que destruiria o motivo de usá-la. O HotXLS portanto confia no chamador, divide o intervalo ao meio, e retorna o que quer que a descida encontre. Em entrada não ordenada a resposta não é um erro, é silenciosamente errada, e isso é 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órmula SpreadsheetML, carrega as descrições mais antigas de LOOKUP e VLOOKUP com sua própria exigência de ordem ascendente, e XLOOKUP e XMATCH são posteriores o suficiente a esse texto que viajam no arquivo como _xlfn.XLOOKUP e _xlfn.XMATCH sob a convenção de função futura. Geração diferente, mesmo acordo: o chamador 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;Rastreie a segunda fórmula e a falha é completamente mecânica. A descida sonda a célula do meio, lê 10, decide que 10 é menor que 40, descarta a metade esquerda incluindo a linha que de fato continha 40, sonda 30, descarta novamente, e esgota o intervalo. O Excel se comporta da mesma forma, que é o ponto: reproduzir a resposta errada é um requisito de compatibilidade, não uma cortesia. A premissa de ordenação também é mais rígida que "números ascendentes", porque o comparador classifica valores por tipo primeiro, na 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 armazenando texto em vez disso não está ascendente sob esse comparador não importa como pareça na tela, e os modos binários vão felizmente interpretá-la mal
Onde chaves duplicadas caem?
Em uma extremidade determinística da sequência de duplicatas, e qual extremidade depende do modo de busca em vez da sorte. Quando a descida binária encontra uma chave igual sob modo de busca 2 ela registra a posição e depois continua estreitando à esquerda, então o resultado é o menor índice da sequência; sob modo de busca -2, sobre dados descendentes, ela registra a posição e estreita à direita, então o resultado é o maior índice. Os modos lineares são mais simples: modo de busca 1 retorna o primeiro acerto indo para frente, modo de busca -1 o primeiro acerto indo para trás. Este é o detalhe que produz a discrepância de quatro linhas do parágrafo inicial, porque uma pasta de trabalho cujas chaves são únicas dá respostas idênticas sob os quatro modos de busca e esconde a diferença em todo teste que você escreveu a partir de um arquivo de amostra limpo. Adicione um código de cliente duplicado aos dados de produção e os modos começam a discordar precisamente nas linhas que duplicaram: nada mudou no motor, a entrada apenas parou de ser um conjunto e se tornou 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 a correspondência aproximada escolhe o vice-campeão?
Mantendo um melhor candidato ao lado da busca de correspondência exata e retornando-o apenas se nenhum acerto exato aparecer. O HotXLS trata match_mode -1 como "o maior valor que não é maior 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 varrida em vez de parar no primeiro vizinho aceitável. No caminho binário a mesma ideia surge da descida de graça: cada passo que ultrapassa ou fica aquém atualiza o candidato, então o candidato final é o elemento de fronteira 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 o desempate mora ali. Uma nova célula substitui o candidato atual apenas quando é estritamente melhor, nunca quando meramente o iguala, então entre várias células que contêm o mesmo valor vice-campeão a que é mantida é a primeira encontrada na ordem de varredura: o índice mais baixo sob uma varredura para frente, o mais alto sob uma 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 sempre produz #N/A
Por que curingas e busca binária não podem coexistir
Porque um padrão curinga não é uma posição em uma ordem. O modo de correspondência 2 pergunta se uma célula corresponde a uma máscara, e correspondência de máscara responde sim ou não; uma descida binária precisa de uma resposta de três vias que diga qual metade manter. Não há forma defensável de perguntar se ACME-* fica à esquerda ou à direita de uma célula dada, então o HotXLS rejeita match_mode 2 combinado com search_mode 2 ou -2 de imediato com #VALUE! em vez de adivinhar uma ordenação e produzir um absurdo plausível. Os dois caminhos 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 sem distinção entre maiúsculas e minúsculas, ou com correspondência de máscara quando curingas 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, já que o caminho binário só pode usar a relação pela qual está realmente navegando. Se você precisa de curingas, use modo de busca 1 ou -1 e aceite o custo linear, que é a mesma troca que o rastreamento de dependência por trás da recalculação incremental foi projetado 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 busca genuinamente unidimensional. Se o intervalo fornecido abrange mais de uma linha e mais de uma coluna ao mesmo tempo, o HotXLS retorna #VALUE! em vez de escolher um eixo em seu nome, e um intervalo de linha única ou coluna única é lido ao longo de seu eixo longo. XLOOKUP adiciona uma segunda regra de forma: o intervalo de retorno deve ter exatamente o mesmo comprimento do intervalo de busca ao longo do eixo correspondente, então uma busca vertical sobre 500 linhas pareada 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 que uma coluna para uma busca vertical, ou mais alto que uma linha para uma horizontal, XLOOKUP devolve a fatia correspondida inteira como um array e ela derrama nas células vizinhas sob as mesmas regras que as outras funções de array dinâmico, descritas no artigo sobre intervalos de derramamento e arrays dinâmicos. Isso é genuinamente útil para extrair um registro inteiro de uma tabela com uma fórmula, e também é a forma mais rápida de sobrescrever uma coluna que você pretendia manter
Escolhendo um modo quando ninguém está olhando a tela
A geração no lado do servidor merece uma política mais rígida que o uso interativo, porque não há um humano para notar que um total parece errado. O padrão defensável é modo de busca 1 com modo de correspondência 0: linear, exato, independente de ordem, e impossível de invalidar reordenando uma planilha. Recorra ao modo de busca 2 apenas onde o mesmo caminho de código também produziu a ordenação, na mesma execução, sobre a mesma coluna, e anote essa dependência ao lado da fórmula, porque uma busca binária em uma coluna ordenada por uma chave diferente é a forma mais barata possível de calcular um número errado com confiança. Quando a busca é genuinamente intensa e os dados genuinamente ordenados o ganho é 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 da pasta de trabalho, então a economia é maior do que a contagem de instruções sugere
Se a forma do problema está mais próxima de uma regra de domínio do que de uma busca, um callback para seu próprio código Pascal, como coberto no artigo sobre funções de planilha personalizadas, geralmente vence qualquer arranjo inteligente das embutidas. As implementações de XLOOKUP e XMATCH discutidas aqui são fornecidas com o componente de planilha HotXLS para Delphi padrão, cuja página de produto traz a referência completa de funções suportadas para Delphi e C++Builder