HotXLS, el componente de hoja de cálculo nativo de Delphi y C++Builder, evalúa XLOOKUP y XMATCH a través de un núcleo de búsqueda compartido. Ese núcleo acepta cuatro modos de coincidencia (-1, 0, 1, 2) y cuatro modos de búsqueda (-2, -1, 1, 2), ejecuta un descenso binario logarítmico siempre que el modo de búsqueda absoluto es 2, y rechaza cualquier otra combinación con un error de fórmula
El reporte de error que te trae aquí nunca dice "modo de búsqueda". Dice que el libro generado por el servidor muestra un número diferente al mismo archivo abierto en Excel, en quizás cuatro filas de nueve mil. Esas cuatro filas siempre tienen algo en común: una clave de búsqueda duplicada, o una coincidencia aproximada que tuvo que elegir un vecino, o una columna de búsqueda que alguien ordenó por una columna diferente la semana pasada. Las funciones de búsqueda son donde un motor de fórmulas deja de ser aritmética y empieza a ser un contrato, y el contrato tiene cláusulas que la mayoría de quienes lo invocan nunca leen
¿Qué números de modo acepta realmente XLOOKUP?
Exactamente cuatro de cada uno, y nada más. HotXLS valida match_mode contra -1, 0, 1 y 2 y search_mode contra -2, -1, 1 y 2 antes de tocar una sola celda, y cualquier otro valor devuelve #VALUE! en vez de ajustarse al modo legal más cercano. Los cuatro modos de coincidencia son 0 para exacto, -1 para exacto o el siguiente menor, 1 para exacto o el siguiente mayor, y 2 para comodín; los cuatro modos de búsqueda son 1 para un escaneo lineal hacia adelante, -1 para un escaneo lineal hacia atrás, 2 para una búsqueda binaria sobre datos ascendentes, y -2 para una búsqueda binaria sobre datos descendentes. Omitirlos selecciona el modo de coincidencia 0 y el modo de búsqueda 1, el emparejamiento que usa casi toda fórmula real. Los conteos de argumentos se vigilan de la misma manera: XLOOKUP toma de tres a seis argumentos y XMATCH toma de dos a cuatro, y cualquier cosa fuera de esos rangos es un #VALUE! antes de que comience la evaluación
// 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;Un paso antes hay una verificación más silenciosa que vale la pena conocer. Los argumentos de modo llegan como expresiones de hoja de cálculo, así que HotXLS los convierte a un número, rechaza NaN e infinito, y luego exige que el número sea igual a su propio valor redondeado. XLOOKUP(x, A:A, B:B, "none", 0, 1.5) es un #VALUE!, no un modo de búsqueda 2 disfrazado. Eso importa cuando el modo viene de una celda que produjo un cálculo con mucho redondeo, lo cual es más común en libros generados que en libros escritos a mano
¿Por qué search_mode 2 da la respuesta equivocada en datos no ordenados?
Porque está haciendo exactamente lo que pediste. El modo de búsqueda 2 le dice al motor que el vector de búsqueda ya está en orden ascendente, y una búsqueda binaria no puede verificar esa afirmación sin un pase O(n) que destruiría la razón de usarla. HotXLS por lo tanto confía en quien llama, divide el intervalo por la mitad, y devuelve lo que sea que el descenso encuentre. En entrada no ordenada la respuesta no es un error, es silenciosamente incorrecta, y esto es una violación de contrato en vez de un defecto en el motor
Microsoft documenta la misma asimetría para XLOOKUP y XMATCH: los modos binarios requieren datos ordenados y producen resultados inválidos de lo contrario. ISO 29500-1 cláusula 18.17, que define la gramática de fórmula de SpreadsheetML, lleva las descripciones más antiguas de LOOKUP y VLOOKUP con su propio requisito de orden ascendente, y XLOOKUP y XMATCH son suficientemente posteriores a ese texto que viajan en el archivo como _xlfn.XLOOKUP y _xlfn.XMATCH bajo la convención de función futura. Generación diferente, mismo trato: quien llama suministra el invariante de orden, el motor suministra el 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;Sigue el rastro de la segunda fórmula y el fallo es completamente mecánico. El descenso sondea la celda del medio, lee 10, decide que 10 es menor que 40, descarta la mitad izquierda incluyendo la fila que realmente tenía 40, sondea 30, descarta de nuevo, y se queda sin intervalo. Excel se comporta de la misma manera, que es el punto: reproducir la respuesta equivocada es un requisito de compatibilidad, no una cortesía. La premisa de orden también es más estricta que "números ascendentes", porque el comparador clasifica los valores por tipo primero, en el orden números, luego texto, luego booleanos, luego valores de error, luego blancos, y solo compara dentro de un tipo después de eso. Una columna de códigos de parte numéricos que tiene tres celdas almacenando texto en su lugar no está ascendente bajo ese comparador sin importar cómo se vea en pantalla, y los modos binarios la malinterpretarán con gusto
¿Dónde aterrizan las claves duplicadas?
En un extremo determinista de la serie duplicada, y qué extremo depende del modo de búsqueda en vez de la suerte. Cuando el descenso binario encuentra una clave igual bajo el modo de búsqueda 2, registra la posición y luego sigue estrechando hacia la izquierda, así que el resultado es el índice más bajo de la serie; bajo el modo de búsqueda -2, sobre datos descendentes, registra la posición y estrecha hacia la derecha, así que el resultado es el índice más alto. Los modos lineales son más simples: el modo de búsqueda 1 devuelve la primera coincidencia yendo hacia adelante, el modo de búsqueda -1 la primera coincidencia yendo hacia atrás. Este es el detalle que produce la discrepancia de cuatro filas del párrafo inicial, porque un libro cuyas claves son únicas da respuestas idénticas bajo los cuatro modos de búsqueda y esconde la diferencia a través de cada prueba que escribiste desde un archivo de muestra limpio. Agrega un código de cliente duplicado a los datos de producción y los modos empiezan a discrepar precisamente en las filas que se duplicaron: nada cambió en el motor, la entrada simplemente dejó de ser un conjunto y se convirtió en un 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 run¿Cómo elige la coincidencia aproximada al finalista?
Manteniendo un mejor candidato junto a la búsqueda de coincidencia exacta y devolviéndolo solo si no aparece ninguna coincidencia exacta. HotXLS trata el match_mode -1 como "el valor más grande que no es mayor que el objetivo" y el match_mode 1 como "el valor más pequeño que no es menor", y ambos se resuelven sobre toda la región escaneada en vez de detenerse en el primer vecino aceptable. En la ruta binaria la misma idea sale gratis del descenso: cada paso que se pasa o se queda corto actualiza el candidato, así que el candidato final es el elemento límite junto a la posición donde se habría insertado la clave
// 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;Lee de cerca la condición interna, porque ahí vive el desempate. Una celda nueva reemplaza al candidato vigente solo cuando es estrictamente mejor, nunca cuando meramente lo iguala, así que entre varias celdas que tienen el mismo valor finalista, la que se conserva es la primera encontrada en orden de escaneo: el índice más bajo bajo un escaneo hacia adelante, el más alto bajo un escaneo hacia atrás. Si XLOOKUP y XMATCH no encuentran ni una coincidencia exacta ni un vecino aceptable, XLOOKUP recurre a su argumento if_not_found cuando se suministró uno y a #N/A cuando no, mientras que XMATCH siempre produce #N/A
Por qué los comodines y la búsqueda binaria no pueden coexistir
Porque un patrón comodín no es una posición en un orden. El modo de coincidencia 2 pregunta si una celda coincide con una máscara, y la coincidencia de máscara responde sí o no; un descenso binario necesita una respuesta de tres vías que le diga qué mitad conservar. No hay forma defendible de preguntar si ACME-* está a la izquierda o a la derecha de una celda dada, así que HotXLS rechaza match_mode 2 combinado con search_mode 2 o -2 de inmediato con #VALUE! en vez de adivinar un orden y producir un sinsentido plausible. Las dos rutas también comparan valores de forma diferente, lo que refuerza la división: el escaneo lineal decide igualdad con una comparación de texto insensible a mayúsculas, o con coincidencia de máscara cuando los comodines están activos, mientras que el descenso binario decide igualdad preguntándole al comparador de orden por un cero. Eso es deliberado en vez de un accidente de capas, ya que la ruta binaria solo puede usar la relación por la que realmente está navegando. Si necesitas comodines, usa el modo de búsqueda 1 o -1 y acepta el costo lineal, que es el mismo intercambio que el seguimiento de dependencias detrás del recálculo incremental está diseñado para mantener fuera de tu ruta crítica
Errores de forma: rangos bidimensionales y vectores de retorno no coincidentes
Ambas funciones requieren un rango de búsqueda genuinamente unidimensional. Si el rango suministrado abarca más de una fila y más de una columna al mismo tiempo, HotXLS devuelve #VALUE! en vez de elegir un eje en tu nombre, y un rango de una sola fila o columna se lee a lo largo de su eje largo. XLOOKUP agrega una segunda regla de forma: el rango de retorno debe tener exactamente la misma longitud que el rango de búsqueda a lo largo del eje coincidente, así que una búsqueda vertical sobre 500 filas emparejada con un rango de retorno de 499 filas es un error, no un desajuste de uno resuelto silenciosamente en la última fila. Cuando el rango de retorno es más ancho que una columna para una búsqueda vertical, o más alto que una fila para una horizontal, XLOOKUP devuelve toda la porción coincidente como un arreglo y se derrama en las celdas vecinas bajo las mismas reglas que las otras funciones de arreglo dinámico, descritas en el artículo sobre rangos de derrame y arreglos dinámicos. Eso es genuinamente útil para extraer un registro entero de una tabla con una fórmula, y también es la forma más rápida de sobrescribir una columna que pretendías conservar
Elegir un modo cuando nadie está mirando la pantalla
La generación del lado del servidor merece una política más estricta que el uso interactivo, porque no hay un humano que note que un total se ve mal. El valor por defecto defendible es el modo de búsqueda 1 con el modo de coincidencia 0: lineal, exacto, independiente del orden, e imposible de invalidar reordenando una hoja. Recurre al modo de búsqueda 2 solo donde la misma ruta de código también produjo el orden, en la misma ejecución, sobre la misma columna, y anota esa dependencia junto a la fórmula, porque una búsqueda binaria en una columna ordenada por una clave diferente es la forma más barata posible de calcular un número incorrecto con confianza. Cuando la búsqueda es genuinamente frecuente y los datos genuinamente ordenados, la ganancia es real: el descenso lee del orden de log n celdas en vez de n, y cada una de esas lecturas pasa por una resolución completa de celda de libro, así que el ahorro es mayor de lo que sugiere el conteo de instrucciones
Si la forma del problema se acerca más a una regla de dominio que a una búsqueda, una devolución de llamada a tu propio código Pascal, como se cubre en el artículo sobre funciones de hoja de cálculo personalizadas, generalmente superará a cualquier arreglo ingenioso de las incorporadas. Las implementaciones de XLOOKUP y XMATCH discutidas aquí vienen con el componente de hoja de cálculo HotXLS para Delphi estándar, cuya página de producto lleva la referencia completa de funciones soportadas para Delphi y C++Builder