HotXLS, le composant tableur Delphi et C++Builder natif, évalue XLOOKUP et XMATCH à travers un noyau de recherche partagé. Ce noyau accepte quatre modes de correspondance (-1, 0, 1, 2) et quatre modes de recherche (-2, -1, 1, 2), exécute une descente binaire logarithmique chaque fois que le mode de recherche absolu vaut 2, et rejette toute autre combinaison avec une erreur de formule
Le rapport de bogue qui vous envoie ici ne dit jamais « mode de recherche ». Il dit que le classeur généré côté serveur affiche un nombre différent de celui du même fichier ouvert dans Excel, sur peut-être quatre lignes sur neuf mille. Ces quatre lignes ont toujours quelque chose en commun : une clé de recherche dupliquée, une correspondance approximative qui a dû choisir un voisin, ou une colonne de recherche que quelqu un a triée selon une autre colonne la semaine dernière. Les fonctions de recherche sont l endroit où un moteur de formules cesse d être de l arithmétique et commence à être un contrat, et le contrat a des clauses que la plupart des appelants ne lisent jamais
Quels numéros de mode XLOOKUP accepte-t-il réellement ?
Exactement quatre pour chacun, et rien d autre. HotXLS valide match_mode contre -1, 0, 1 et 2 et search_mode contre -2, -1, 1 et 2 avant de toucher une seule cellule, et toute autre valeur renvoie #VALUE! plutôt que d être plafonnée vers le mode légal le plus proche. Les quatre modes de correspondance sont 0 pour exact, -1 pour exact ou le plus petit suivant, 1 pour exact ou le plus grand suivant, et 2 pour joker ; les quatre modes de recherche sont 1 pour un balayage linéaire vers l avant, -1 pour un balayage linéaire vers l arrière, 2 pour une recherche binaire sur des données ascendantes, et -2 pour une recherche binaire sur des données descendantes. Les omettre sélectionne le mode de correspondance 0 et le mode de recherche 1, l appariement que presque toutes les formules réelles utilisent. Le nombre d arguments est policé de la même façon : XLOOKUP prend de trois à six arguments et XMATCH de deux à quatre, et tout ce qui sort de ces plages est un #VALUE! avant même que l évaluation ne commence
// 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;Une étape plus tôt se trouve une vérification plus discrète qui vaut la peine d être connue. Les arguments de mode arrivent comme des expressions de feuille de calcul, donc HotXLS les convertit en nombre, refuse NaN et l infini, puis exige que le nombre soit égal à sa propre valeur arrondie. XLOOKUP(x, A:A, B:B, "none", 0, 1.5) est un #VALUE!, pas un mode de recherche 2 déguisé. Cela compte quand le mode provient d une cellule qu un calcul chargé en arrondis a produite, ce qui est plus courant dans les classeurs générés que dans ceux écrits à la main
Pourquoi search_mode 2 donne-t-il la mauvaise réponse sur des données non triées ?
Parce qu il fait exactement ce que vous avez demandé. Le mode de recherche 2 dit au moteur que le vecteur de recherche est déjà en ordre ascendant, et une recherche binaire ne peut pas vérifier cette affirmation sans un passage O(n) qui détruirait la raison de l utiliser. HotXLS fait donc confiance à l appelant, coupe l intervalle en deux, et renvoie ce sur quoi la descente atterrit. Sur une entrée non triée, la réponse n est pas une erreur, elle est silencieusement fausse, et c est une violation de contrat plutôt qu un défaut du moteur
Microsoft documente la même asymétrie pour XLOOKUP et XMATCH : les modes binaires exigent des données triées et produisent des résultats invalides sinon. La norme ISO 29500-1 clause 18.17, qui définit la grammaire de formule SpreadsheetML, porte les anciennes descriptions LOOKUP et VLOOKUP avec leur propre exigence d ordre ascendant, et XLOOKUP et XMATCH sont suffisamment postérieurs à ce texte pour voyager dans le fichier sous _xlfn.XLOOKUP et _xlfn.XMATCH selon la convention des fonctions futures. Génération différente, même marché : l appelant fournit l invariant d ordre, le moteur fournit le logarithme
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;Tracez la seconde formule et l échec est entièrement mécanique. La descente sonde la cellule du milieu, lit 10, décide que 10 est plus petit que 40, écarte la moitié gauche y compris la ligne qui contenait réellement 40, sonde 30, écarte à nouveau, et épuise l intervalle. Excel se comporte de la même façon, ce qui est tout l intérêt : reproduire la mauvaise réponse est une exigence de compatibilité, pas une courtoisie. La prémisse d ordonnancement est aussi plus stricte que « nombres ascendants », car le comparateur classe les valeurs par type d abord, dans l ordre nombres, puis texte, puis booléens, puis valeurs d erreur, puis cellules vides, et ne compare au sein d un type qu ensuite. Une colonne de codes de pièces numériques qui a trois cellules stockant du texte au lieu de nombres n est pas ascendante sous ce comparateur peu importe son apparence à l écran, et les modes binaires la mal liront allègrement
Où atterrissent les clés dupliquées ?
Sur une extrémité déterministe de la séquence de doublons, et laquelle dépend du mode de recherche plutôt que de la chance. Quand la descente binaire touche une clé égale sous le mode de recherche 2, elle enregistre la position puis continue de resserrer vers la gauche, donc le résultat est l index le plus bas de la séquence ; sous le mode de recherche -2, sur des données descendantes, elle enregistre la position et resserre vers la droite, donc le résultat est l index le plus haut. Les modes linéaires sont plus simples : le mode de recherche 1 renvoie le premier hit en avançant, le mode de recherche -1 le premier hit en reculant. C est le détail qui produit la divergence sur quatre lignes du paragraphe d ouverture, car un classeur dont les clés sont uniques donne des réponses identiques sous les quatre modes de recherche et masque la différence à travers chaque test que vous avez écrit à partir d un fichier d échantillon propre. Ajoutez un code client dupliqué aux données de production et les modes commencent à diverger précisément sur les lignes qui ont été dupliquées : rien n a changé dans le moteur, l entrée a simplement cessé d être un ensemble pour devenir un multi-ensemble
// 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 runComment la correspondance approximative choisit-elle le dauphin ?
En conservant un meilleur candidat aux côtés de la recherche de correspondance exacte et en ne le renvoyant que si aucune correspondance exacte n apparaît. HotXLS traite le match_mode -1 comme « la plus grande valeur qui n est pas supérieure à la cible » et le match_mode 1 comme « la plus petite valeur qui n est pas inférieure », et les deux sont résolus sur toute la région balayée plutôt qu en s arrêtant au premier voisin acceptable. Dans le chemin binaire, la même idée découle gratuitement de la descente : chaque étape qui dépasse ou n atteint pas met à jour le candidat, donc le candidat final est l élément limite adjacent à la position où la clé aurait été insérée
// 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;Lisez attentivement la condition intérieure, car le bris d égalité s y trouve. Une nouvelle cellule ne remplace le candidat en place que quand elle est strictement meilleure, jamais quand elle lui est simplement égale, donc parmi plusieurs cellules portant la même valeur de dauphin, celle conservée est la première rencontrée dans l ordre de balayage : l index le plus bas sous un balayage vers l avant, le plus haut sous un balayage vers l arrière. Si XLOOKUP et XMATCH ne trouvent ni un hit exact ni un voisin acceptable, XLOOKUP se replie sur son argument if_not_found quand il a été fourni et sur #N/A sinon, tandis que XMATCH renvoie toujours #N/A
Pourquoi les jokers et la recherche binaire ne peuvent-ils pas coexister
Parce qu un motif joker n est pas une position dans un ordre. Le mode de correspondance 2 demande si une cellule correspond à un masque, et la correspondance de masque répond oui ou non ; une descente binaire a besoin d une réponse à trois voies qui lui dit quelle moitié conserver. Il n y a aucune façon défendable de demander si ACME-* se trouve à gauche ou à droite d une cellule donnée, donc HotXLS rejette match_mode 2 combiné avec search_mode 2 ou -2 d emblée avec #VALUE! au lieu de deviner un ordonnancement et de produire un non-sens plausible. Les deux chemins comparent aussi les valeurs différemment, ce qui renforce la séparation : le balayage linéaire décide de l égalité avec une comparaison de texte insensible à la casse, ou avec une correspondance de masque quand les jokers sont activés, tandis que la descente binaire décide de l égalité en demandant un zéro au comparateur d ordre. C est délibéré plutôt qu un accident de superposition, puisque le chemin binaire ne peut utiliser que la relation selon laquelle il navigue réellement. Si vous avez besoin de jokers, utilisez le mode de recherche 1 ou -1 et acceptez le coût linéaire, ce qui est le même compromis que le suivi de dépendances derrière le recalcul incrémental est conçu pour garder hors de votre chemin critique
Erreurs de forme : plages bidimensionnelles et vecteurs de retour non appariés
Les deux fonctions exigent une plage de recherche véritablement unidimensionnelle. Si la plage fournie s étend sur plus d une ligne et plus d une colonne en même temps, HotXLS renvoie #VALUE! plutôt que de choisir un axe à votre place, et une plage à une seule ligne ou une seule colonne est lue le long de son axe long. XLOOKUP ajoute une seconde règle de forme : la plage de retour doit être exactement aussi longue que la plage de recherche le long de l axe correspondant, donc une recherche verticale sur 500 lignes appariée avec une plage de retour de 499 lignes est une erreur, pas un décalage d un discrètement résolu à la dernière ligne. Quand la plage de retour est plus large qu une colonne pour une recherche verticale, ou plus haute qu une ligne pour une horizontale, XLOOKUP renvoie toute la tranche appariée comme un tableau et elle déborde dans les cellules voisines selon les mêmes règles que les autres fonctions de tableau dynamique, décrites dans l article sur les plages de débordement et les tableaux dynamiques. C est véritablement utile pour extraire un enregistrement entier d une table avec une seule formule, et c est aussi la façon la plus rapide d écraser une colonne que vous vouliez conserver
Choisir un mode quand personne ne regarde l écran
La génération côté serveur mérite une politique plus stricte que l usage interactif, car il n y a aucun humain pour remarquer qu un total paraît faux. Le défaut défendable est le mode de recherche 1 avec le mode de correspondance 0 : linéaire, exact, indépendant de l ordre, et impossible à invalider en retriant une feuille. Ne tendez la main vers le mode de recherche 2 que là où le même chemin de code a aussi produit l ordonnancement, dans la même exécution, sur la même colonne, et notez cette dépendance à côté de la formule, car une recherche binaire sur une colonne triée selon une clé différente est la façon la moins chère possible de calculer un mauvais nombre avec assurance. Quand la recherche est véritablement chaude et les données véritablement triées, le gain est réel : la descente lit de l ordre de log n cellules au lieu de n, et chacune de ces lectures passe par une résolution complète de cellule de classeur, donc l économie est plus grande que ce que le nombre d instructions suggère
Si la forme du problème se rapproche davantage d une règle métier que d une recherche, un rappel vers votre propre code Pascal, comme couvert dans l article sur les fonctions de feuille de calcul personnalisées, battra généralement tout arrangement astucieux des fonctions intégrées. Les implémentations XLOOKUP et XMATCH discutées ici sont livrées avec le composant tableur Delphi HotXLS standard, dont la page produit porte la référence complète des fonctions prises en charge pour Delphi et C++Builder