HotXLS, el componente de hoja de cálculo nativo para Delphi y C++Builder, evalúa XLOOKUP y XMATCH mediante un único 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 en valor absoluto sea 2, y rechaza cualquier otra combinación con un error de fórmula
El informe de fallo que te trae aquí nunca dice "modo de búsqueda". Dice que el libro generado por el servidor muestra un número distinto al del mismo archivo abierto en Excel, quizá en 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 otra columna 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 los llamadores 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 lugar de ajustarse al modo legal más cercano. Los cuatro modos de coincidencia son 0 para exacta, -1 para exacta o el valor menor siguiente, 1 para exacta o el valor mayor siguiente, y 2 para comodín; los cuatro modos de búsqueda son 1 para una exploración lineal hacia delante, -1 para una exploración 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, la combinación que usa casi toda fórmula real. El número de argumentos se controla de la misma manera: XLOOKUP acepta de tres a seis argumentos y XMATCH de dos a cuatro, y cualquier cosa fuera de esos rangos es un #VALUE! antes de que empiece 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 comprobación más discreta que merece 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 proviene de una celda que produjo un cálculo con mucho redondeo, lo que es más habitual en libros generados que en libros escritos a mano
¿Por qué search_mode 2 da la respuesta equivocada sobre datos sin ordenar?
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 una pasada O(n) que destruiría el motivo de usarla. HotXLS por tanto confía en el llamador, divide el intervalo por la mitad, y devuelve lo que sea que produzca el descenso. Sobre una entrada sin ordenar la respuesta no es un error, es silenciosamente errónea, y esto es una violación del contrato y no un defecto del motor
Microsoft documenta la misma asimetría para XLOOKUP y XMATCH: los modos binarios requieren datos ordenados y producen resultados no válidos en caso contrario. La cláusula 18.17 de la ISO 29500-1, que define la gramática de fórmulas de SpreadsheetML, lleva las descripciones más antiguas de LOOKUP y VLOOKUP con su propio requisito de orden ascendente, y XLOOKUP y XMATCH son lo bastante posteriores a ese texto como para viajar en el archivo como _xlfn.XLOOKUP y _xlfn.XMATCH bajo la convención de funciones futuras. Generación distinta, mismo trato: el llamador aporta el invariante de ordenación, el motor aporta 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 la traza 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 en realidad contenía 40, sondea 30, descarta de nuevo, y se queda sin intervalo. Excel se comporta de la misma manera, que es la cuestión: reproducir la respuesta equivocada es un requisito de compatibilidad, no una cortesía. La premisa de ordenación también es más estricta que "números ascendentes", porque el comparador clasifica los valores primero por tipo, en el orden números, luego texto, luego booleanos, luego valores de error, luego vacíos, y solo compara dentro de un tipo después de eso. Una columna de códigos de pieza numéricos que tiene tres celdas almacenando texto en su lugar no es ascendente bajo ese comparador por muy bien que 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 tirada de duplicados, y qué extremo depende del modo de búsqueda y no de la suerte. Cuando el descenso binario alcanza 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 tirada; 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 el primer acierto avanzando, el modo de búsqueda -1 el primer acierto retrocediendo. 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 oculta la diferencia en cada prueba que escribiste a partir de un archivo de muestra limpio. Añade 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 sustituto?
Manteniendo un mejor candidato junto a la búsqueda de coincidencia exacta y devolviéndolo solo si no aparece ningún acierto exacto. HotXLS trata match_mode -1 como "el mayor valor que no es superior al objetivo" y match_mode 1 como "el menor valor que no es inferior", y ambos se resuelven sobre toda la región explorada en lugar de detenerse en el primer vecino aceptable. En la ruta binaria la misma idea sale del descenso de forma gratuita: 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 con atención 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 con el mismo valor de sustituto, la que se conserva es la primera encontrada en el orden de exploración: la de índice más bajo en una exploración hacia delante, la de índice más alto en una exploración hacia atrás. Si XLOOKUP y XMATCH no encuentran ni un acierto exacto 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-* queda a la izquierda o a la derecha de una celda dada, así que HotXLS rechaza de entrada el match_mode 2 combinado con search_mode 2 o -2 con un #VALUE! en lugar de adivinar una ordenación y producir un disparate plausible. Las dos rutas también comparan valores de forma distinta, lo que refuerza la separación: la exploración lineal decide la igualdad con una comparación de texto insensible a mayúsculas y minúsculas, o con coincidencia de máscara cuando los comodines están activados, mientras que el descenso binario decide la igualdad preguntándole al comparador de ordenación si da cero. Eso es deliberado y no 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 coste lineal, que es el mismo trato que el seguimiento de dependencias detrás de el recálculo incremental está diseñado para mantener fuera de tu ruta crítica
Errores de forma: rangos bidimensionales y vectores de retorno desajustados
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 lugar 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 añade 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 array y se derrama hacia las celdas vecinas bajo las mismas reglas que las demás funciones de array dinámico, descritas en el artículo sobre rangos de derrame y arrays dinámicos. Eso es genuinamente útil para extraer un registro completo de una tabla con una sola 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 en el lado del servidor merece una política más estricta que el uso interactivo, porque no hay ningún 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 la ordenación, en la misma ejecución, sobre la misma columna, y anota esa dependencia junto a la fórmula, porque una búsqueda binaria sobre una columna ordenada por una clave distinta es la forma más barata posible de calcular un número erróneo con total confianza. Cuando la búsqueda es genuinamente intensa y los datos genuinamente ordenados, la recompensa es real: el descenso lee del orden de log n celdas en lugar de n, y cada una de esas lecturas pasa por una resolución completa de celda del libro, así que el ahorro es mayor de lo que sugiere el recuento de instrucciones
Si la forma del problema se parece más a una regla de dominio que a una búsqueda, una retrollamada a tu propio código Pascal, como se cubre en el artículo sobre funciones de hoja de cálculo personalizadas, normalmente superará a cualquier disposición ingeniosa de las funciones integradas. Las implementaciones de XLOOKUP y XMATCH tratadas aquí se incluyen con el estándar componente de hoja de cálculo para Delphi HotXLS, cuya página de producto lleva la referencia completa de funciones admitidas para Delphi y C++Builder