Sur les clés 1, 3, 5, 5, 5, 7, 9 en A1:A7, la formule XMATCH(5, A1:A7, 0, 2) renvoie 3. Changez seulement le dernier argument en -1 et la même formule renvoie 5. Pointez la première version vers une colonne que quelqu'un a re-triée selon un autre champ, et un 5 parfaitement visible sur la feuille peut revenir en #N/A. Les trois réponses sont correctes. Le dernier argument de XMATCH, comme le sixième argument de XLOOKUP, est deux choses à la fois : une instruction sur quel doublon préférer, et une promesse sur comment les données sont ordonnées
Le rapport qui mène ici mentionne rarement les modes de recherche. Il dit qu'un classeur généré est en désaccord avec ce que quelqu'un attendait sur une poignée de lignes, et ces lignes partagent quelque chose : une clé répétée, une correspondance approximative qui a dû choisir un voisin, ou une colonne de recherche dont l'ordre a changé après l'écriture de la formule. Les sections ci-dessous passent les règles une à une, avec le résultat exact que chaque formule produit
Quelles valeurs de match_mode et search_mode XLOOKUP accepte-t-il ?
HotXLS, le composant tableur natif Delphi et C++Builder, évalue XLOOKUP et XMATCH à travers une routine de recherche partagée qui accepte exactement quatre modes de correspondance et quatre modes de recherche. match_mode peut être 0 (exact), -1 (exact ou suivant plus petit), 1 (exact ou suivant plus grand) ou 2 (joker). search_mode peut être 1 (balayage du premier au dernier), -1 (balayage du dernier au premier), 2 (recherche binaire sur données croissantes) ou -2 (recherche binaire sur données décroissantes). Tout autre nombre renvoie #VALUE! ; rien n'est serré vers le mode légal le plus proche. Laisser les arguments de côté sélectionne le mode de correspondance 0 et le mode de recherche 1, ce que la plupart des formules écrites à la main utilisent
Les comptes d'arguments sont vérifiés avant tout cela. XLOOKUP prend trois à six arguments et XMATCH deux à quatre, et un appel hors de ces plages est un #VALUE! avant que l'évaluation ne démarre. Les arguments de mode eux-mêmes sont des expressions de feuille ordinaires, si bien que HotXLS convertit chacun en nombre, renvoie #VALUE! pour NaN, l'infini ou une valeur hors de la plage d'entier 32 bits, puis tronque vers zéro. 1.5 devient le mode de recherche 1, -1.5 devient -1, et 2.9 devient 2. La troncature mord quand le mode vient d'une cellule calculée par arithmétique : une valeur qui s'affiche comme 2 mais est stockée comme 1.9999999 sélectionne un balayage linéaire. Écrivez le mode comme un littéral, ou enveloppez un mode calculé dans ROUND
Le programme suivant écrit une petite table de taux et cinq recherches qui exercent ces règles. Les commentaires donnent la valeur que chaque cellule de formule porte après Recalculate, dérivée des règles ci-dessus
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
begin
Book := TXLSXWorkbook.Create;
try
Sheet := Book.Sheets.Add('Rates'); // Sheets[1]
Sheet.Cells[1, 1].Value := 10; Sheet.Cells[1, 2].Value := 0.25;
Sheet.Cells[2, 1].Value := 20; Sheet.Cells[2, 2].Value := 0.20;
Sheet.Cells[3, 1].Value := 30; Sheet.Cells[3, 2].Value := 0.15;
Sheet.Cells[4, 1].Value := 40; Sheet.Cells[4, 2].Value := 0.10;
// Défauts : match_mode 0, search_mode 1
Sheet.Cells[1, 4].Formula := 'XLOOKUP(30,A1:A4,B1:B4)'; // 0.15
// Exact ou suivant plus petit, recherche binaire sur clés croissantes
Sheet.Cells[2, 4].Formula := 'XLOOKUP(35,A1:A4,B1:B4,"none",-1,2)'; // 0.15
// 1.5 se tronque en search_mode 1
Sheet.Cells[3, 4].Formula := 'XLOOKUP(40,A1:A4,B1:B4,"none",0,1.5)'; // 0.10
// 3 n'est pas un mode de recherche
Sheet.Cells[4, 4].Formula := 'XLOOKUP(40,A1:A4,B1:B4,"none",0,3)'; // #VALUE!
// La correspondance à jokers ne peut pas tourner sur une recherche binaire
Sheet.Cells[5, 4].Formula := 'XLOOKUP("4*",A1:A4,B1:B4,"none",2,2)'; // #VALUE!
if Book.Recalculate = lxOk then
Book.SaveAs('lookup-modes.xlsx');
finally
Book.Free;
end;
end;Pourquoi le search_mode 2 rate-t-il une clé qui est sur la feuille ?
Le mode de recherche 2 dit à HotXLS que le vecteur de recherche est déjà trié croissant, et le moteur le croit. Vérifier l'affirmation prendrait une passe complète sur la colonne, ce qui est exactement le coût qu'une recherche binaire existe pour éviter, si bien que la descente divise l'intervalle en deux à chaque sonde et revient où qu'elle finisse. Microsoft documente le même contrat pour XLOOKUP et XMATCH : les modes binaires exigent des données triées et donnent des résultats invalides sinon. L'ISO/IEC 29500-1, section 18.17, la clause des formules SpreadsheetML, décrit les anciens LOOKUP et VLOOKUP avec leur propre exigence d'ordre croissant ; XLOOKUP et XMATCH sont venus plus tard et sont stockés dans le fichier comme _xlfn.XLOOKUP et _xlfn.XMATCH sous le préfixe de fonctions futures
La figure trace XLOOKUP(40, A1:A3, B1:B3, "missing", 0, 2) sur les clés 40, 10, 30. La première sonde atterrit sur la ligne 2, lit 10, et comme 10 est sous 40 elle jette les lignes 1 et 2, y compris la ligne qui porte le 40. La ligne 3 lit 30, aussi sous 40, et l'intervalle est vide. La formule renvoie "missing". Avec le mode de recherche 1 le même appel trouve 40 en ligne 1
"Croissant" veut aussi dire quelque chose de plus spécifique qu'il en a l'air à l'écran. Le chemin binaire range les valeurs par sorte avant de les comparer : les nombres d'abord, puis le texte, puis les booléens, puis les valeurs d'erreur, puis les vides. Une colonne de codes de pièces numériques dans laquelle trois cellules portent les codes en texte n'est pas croissante sous cette règle, aussi soignée qu'elle paraisse. Le texte est comparé sans égard à la casse, et sous Windows l'ordre vient de la locale de l'utilisateur à travers CompareStringW, qui traite les tirets et les lettres accentuées différemment d'un tri par ordre d'octets. Une colonne de clés triée par une base de données avec un classement binaire peut donc manquer la promesse d'ordre exactement sur les codes qui portent de la ponctuation
Les vides rangés en dernier ont une conséquence déséquilibrée. Une colonne de clés croissante avec des lignes vides en dessous reste croissante, si bien que pointer le mode de recherche 2 vers une plage surdimensionnée comme A2:A5001 est inoffensif. Une colonne décroissante suivie de lignes vides n'est plus décroissante, et le mode de recherche -2 marche droit dans les vides
var
R: TXLSFormulaEvaluationResult;
begin
// B1:B3 porte 9, 7, 5 ; B4:B10 sont vides
R := Sheet.EvaluateFormulaAt(1, 4, 'XMATCH(5,B1:B10,0,-2)');
// R.Status = xlfeOk et VarIsError(R.Value) : le résultat est #N/A.
// La première sonde lit le B5 vide, les vides rangent au-dessus de tout nombre,
// si bien que la descente continue vers la droite et ne revient jamais
// Rognez la plage aux lignes remplies et le même appel marche
R := Sheet.EvaluateFormulaAt(1, 4, 'XMATCH(5,B1:B3,0,-2)'); // 3
// A linear scan skips the blanks and finds the key wherever it is
R := Sheet.EvaluateFormulaAt(1, 4, 'XMATCH(5,B1:B10,0,-1)'); // 3
end;Où atterrissent les clés dupliquées ?
XMATCH et XLOOKUP atterrissent sur une extrémité fixe d'une série de doublons, et le mode de recherche choisit l'extrémité. Sous le mode de recherche 2 la descente binaire note chaque clé égale qu'elle rencontre et continue de resserrer vers la gauche, si bien que le résultat est l'index le plus bas de la série. Sous le mode de recherche -2, sur données décroissantes, elle note la position et resserre vers la droite, donnant l'index le plus haut. Les modes linéaires renvoient le premier hit dans la direction de balayage : l'index le plus bas pour le mode de recherche 1, le plus haut pour le mode de recherche -1
Voilà pourquoi les rapports de divergence se regroupent sur des lignes particulières. Un fichier d'échantillon avec des clés uniques donne la même réponse sous les quatre modes de recherche, si bien que chaque test construit depuis lui passe. Une fois que les données de production répètent un code client, les modes se mettent en désaccord précisément sur les lignes répétées, et le code qui a écrit les formules n'a pas changé du tout
// A1:A7 porte 1, 3, 5, 5, 5, 7, 9 (croissant, une série de trois)
Sheet.Cells[1, 3].Formula := 'XMATCH(5,A1:A7,0,1)'; // 3, premier hit en avant
Sheet.Cells[2, 3].Formula := 'XMATCH(5,A1:A7,0,-1)'; // 5, premier hit en arrière
Sheet.Cells[3, 3].Formula := 'XMATCH(5,A1:A7,0,2)'; // 3, index le plus bas de la série
// B1:B7 porte 9, 7, 5, 5, 5, 3, 1 (décroissant)
Sheet.Cells[4, 3].Formula := 'XMATCH(5,B1:B7,0,-2)'; // 5, index le plus haut de la sérieComment la correspondance approximative choisit le second
Quand aucune cellule n'égale la valeur recherchée, le mode de correspondance -1 renvoie la plus grande valeur qui n'est pas supérieure à la cible et le mode de correspondance 1 renvoie la plus petite valeur qui n'est pas inférieure. Dans les modes linéaires HotXLS balaie le vecteur entier et garde un meilleur candidat, si bien que la correspondance approximative marche sur des données non triées. Une cellule remplace le candidat en place seulement quand elle est strictement plus proche, jamais quand elle fait simplement égalité. Parmi plusieurs cellules portant la même valeur de second, celle qui est gardée est la première rencontrée dans l'ordre de balayage
Prennez les clés 10, 30, 20, 30, 50, 40 en A1:A6. XMATCH(35, A1:A6, -1, 1) renvoie 2 : le premier 30 devient le candidat, et le second 30 ne fait qu'égalité. Retournez le balayage avec XMATCH(35, A1:A6, -1, -1) et le résultat est 4, parce que le balayage en arrière rencontre l'autre 30 en premier. XMATCH(35, A1:A6, 1, 1) renvoie 6, puisque 40 bat 50 comme plus petite valeur au-dessus de 35. Sur des données triées les modes binaires atteignent le même voisin en une poignée de sondes : sur 10, 20, 30, 40, 50, XMATCH(35, A1:A5, -1, 2) renvoie 3 et XMATCH(35, A1:A5, 1, 2) renvoie 4
Si ni un hit exact ni un voisin acceptable ne se présentent, XLOOKUP renvoie son argument if_not_found quand il a été fourni et #N/A quand il ne l'a pas été. XMATCH n'a pas d'argument de repli et renvoie toujours #N/A
Pourquoi les jokers et la recherche binaire ne peuvent pas coexister
Un motif à jokers n'a pas de position dans un ordre de tri. Le mode de correspondance 2 demande si une cellule colle à un masque, ce qui est une question par oui ou par non, pendant qu'une descente binaire a besoin d'une réponse à trois voies qui dit quelle moitié garder. Il n'y a pas de façon défendable de dire si ACME-* se trouve à gauche ou à droite d'une cellule donnée, si bien que HotXLS renvoie #VALUE! pour le mode de correspondance 2 combiné au mode de recherche 2 ou -2 et ne devine pas. Utilisez le mode de recherche 1 ou -1 avec les jokers et acceptez le coût linéaire
Le mode de correspondance 2 applique toujours les règles de jokers : * tient pour toute suite de caractères, ? pour un caractère, et ~ rend le caractère suivant littéral. XLOOKUP("PN~*", ...) trouve donc le texte PN* lui-même, ce qui compte quand des codes de produits portent vraiment des astérisques. En mode de correspondance 0 le balayage linéaire compare le texte sans égard à la casse, et le chemin binaire traite deux valeurs comme égales quand sa comparaison d'ordre rapporte une égalité. Cette comparaison est aussi insensible à la casse, mais c'est le même ordre conscient de la locale décrit ci-dessus, qui est la seule relation qu'une recherche binaire peut légitimement utiliser. Les feuilles riches en recherches façonnent aussi ce que le suivi de dépendances derrière le recalcul incrémental doit réévaluer après une modification
Erreurs de forme et tranches de retour versées
Les deux fonctions ont besoin d'une plage de recherche unidimensionnelle. Une plage qui s'étend sur plus d'une ligne et plus d'une colonne en même temps renvoie #VALUE! ; HotXLS ne choisit pas un axe à votre place. XLOOKUP ajoute une seconde règle : la plage de retour doit avoir la même longueur que la plage de recherche le long de l'axe d'appariement. Une recherche verticale sur 500 lignes appariée à une plage de retour de 499 lignes est une erreur, et la dernière ligne n'est jamais discrètement abandonnée. Quand la plage de recherche est une seule cellule, la forme de la plage de retour décide si la recherche compte comme verticale ou horizontale
Une plage de retour plus large qu'une colonne, pour une recherche verticale, fait remettre à XLOOKUP la ligne entière appariée comme un tableau. Elle se verse dans les cellules voisines sous les mêmes règles que les autres fonctions de tableaux dynamiques, couvertes dans l'article sur les plages de versement et les tableaux dynamiques. Les cellules vides à l'intérieur d'une tranche versée reviennent en 0. Sortir un enregistrement complet d'une table avec une formule est pratique, et c'est aussi la façon la plus rapide d'écraser une colonne qui se trouvait par hasard à droite de la formule
Choisir un mode pour des classeurs que personne ne surveille
La génération côté serveur mérite une politique plus stricte que l'usage interactif, parce que personne ne regarde le résultat avant son expédition. Laissez le mode de correspondance 0 avec le mode de recherche 1 en place là-bas : la correspondance est exacte, le balayage ignore l'ordre des lignes, et re-trier la feuille ne peut pas changer la réponse. Le mode de recherche 2 n'appartient qu'aux endroits où le même code a produit l'ordre, dans la même exécution, sur la même colonne. Une recherche binaire sur une colonne triée selon une autre clé est la façon la moins chère de calculer un nombre faux avec assurance
Quand vous basculez quand même une recherche vers le mode binaire, vérifiez l'ordre vous-même au point où vous émettez la formule. La fonction ci-dessous ne fait pas partie de HotXLS ; c'est une garde à ajouter à votre propre code. Pour beaucoup de recherches exactes contre une même table, la propriété de classeur RecalcLookupCache laisse Recalculate réutiliser le premier balayage d'une plage pour les recherches suivantes contre elle. Le cache se coupe lui-même quand la passe contient OFFSET, INDIRECT, des références circulaires ou des tables de données What-If, et les résultats sont les mêmes dans les deux cas
// Votre propre garde, pas une API HotXLS : confirmez qu'une colonne de clés numériques est
// croissante avant d'émettre une formule search_mode 2 qui s'y fie
function KeysAscending(Sheet: TXLSXWorksheet; Col, FirstRow,
LastRow: Integer): Boolean;
var
Row: Integer;
Prev, Cur: Variant;
begin
Result := False;
Prev := Sheet.Cells[FirstRow, Col].Value;
if not VarIsNumeric(Prev) then Exit;
for Row := FirstRow + 1 to LastRow do
begin
Cur := Sheet.Cells[Row, Col].Value;
if (not VarIsNumeric(Cur)) or (Cur < Prev) then Exit; // texte, vide ou désordonné
Prev := Cur;
end;
Result := True;
end;
// Feuille Rates : clés en A2:A5001, taux en B2:B5001
UseBinary := KeysAscending(Rates, 1, 2, 5001);
Book.RecalcLookupCache := True;
for Row := 2 to OrderCount + 1 do
if UseBinary then
Orders.Cells[Row, 5].Formula := Format(
'XLOOKUP(B%d,Rates!$A$2:$A$5001,Rates!$B$2:$B$5001,0,-1,2)', [Row])
else
Orders.Cells[Row, 5].Formula := Format(
'XLOOKUP(B%d,Rates!$A$2:$A$5001,Rates!$B$2:$B$5001,"missing")', [Row]);La garde est délibérément conservatrice : une seule cellule vide ou texte renvoie la colonne entière vers le chemin linéaire. Sur une colonne triée et sollicitée le gain du mode binaire est réel, environ log₂ n lectures par recherche au lieu de jusqu'à n, ce qui pour 5 000 clés veut dire 13 sondes au plus
Quand une recherche est en réalité une règle métier déguisée, comme un taux à paliers avec exceptions, un callback vers votre propre code Pascal est d'habitude plus clair que n'importe quel arrangement de fonctions intégrées ; l'article sur les fonctions de feuille personnalisées montre comment en enregistrer une. XLOOKUP, XMATCH et le reste du moteur de formules décrit ici font partie du composant tableur HotXLS pour Delphi, et sa page produit liste les fonctions de feuille prises en charge pour Delphi et C++Builder