Un changement passionnant est arrivé à la fonction XLOOKUP dans la mise à jour d'Office Insiders publiée le 1er novembre 2019. De nombreux initiés recevront cette mise à jour dès leur arrivée au travail le lundi 4 novembre 2019.
Si vous avez utilisé la nouvelle fonction XLOOKUP et si vous avez utilisé l'argument Match_Mode pour rechercher la valeur juste plus grande ou juste plus petite, vos fonctions XLOOKUP existantes vont être interrompues.
La nouvelle modification apportée à XLOOKUP: l'argument If_Not_Found, qui a été initialement ajouté en tant que sixième argument facultatif, a été déplacé pour être le quatrième argument.
Considérez la formule suivante, qui demandait auparavant la prochaine correspondance plus grande:
=XLOOKUP(A2,H2:H99,J2:J99,1)
Lorsque vous ouvrez un classeur avec une formule comme celle-ci, la formule ne se rompt pas immédiatement. Le recalcul intelligent d'Excel ne recalcule pas la formule tant que vous ne la modifiez pas ou que vous ne modifiez pas l'un des nombres de H2: H99 ou J2: J99.
Cependant, une fois que vous modifiez la table de recherche, Excel recalcule toutes les fonctions XLOOKUP qui ont utilisé la table. Avant le changement, vous demandiez une correspondance approximative qui renvoyait la valeur immédiatement supérieure. Après la modification, vous demandez une correspondance exacte (car votre formule d'origine n'a pas de cinquième argument) et spécifiez également accidentellement que si une correspondance exacte n'est pas trouvée, vous souhaitez insérer un 1 comme résultat à la place.
"C'est vraiment un jeu insidieux de taupe", a déclaré Bill Jelen, éditeur de.com. Vous appuyez sur F2 pour regarder une formule et la formule cesse de fonctionner. D'autres formules de la feuille de calcul peuvent sembler continuer à fonctionner, mais il s'agit d'une bombe à retardement qui attend de se tromper lorsqu'un recalc est déclenché. "
Pour voir le changement se produire, regardez de 0:35 à 0:55 seconde dans cette vidéo:
Regarder la vidéo
Lorsque vous vous inscrivez au programme Office Insiders, le paragraphe 7c des Conditions générales indique que "Nous pouvons publier les Services ou leurs fonctionnalités dans une version préliminaire ou bêta, ce qui peut ne pas fonctionner correctement ou de la même manière que la version finale peut fonctionner. . "
L'équipe Excel vous informe que vous devez ajuster toutes les formules XLOOKUP qui utilisaient les arguments facultatifs. Si vous avez fréquemment utilisé XLOOKUP, le code suivant examinera un classeur et identifiera les formules de problème possibles.
Version de base
Le code suivant recherche les cellules de formule commençant par =XLOOKUPet contenant plus de 2 virgules.
Sub findXLOOKUPs() Dim sht As Worksheet Dim cll As Range Dim foundCells As String Set sht = ActiveSheet For Each cll In sht.UsedRange If cll.HasFormula Then If InStr(cll.Formula, "=XLOOKUP") = 1 Then If UBound(Split(cll.Formula, ","))> 2 Then foundCells = foundCells & vbCrLf & cll.Address End If End If End If Next cll If foundCells = "" Then MsgBox sht.Name & " is not affected with XLOOKUP function structure update.", vbOKOnly + vbInformation, "No errors" Else MsgBox sht.Name & " is likely affected with XLOOKUP function structure update. Please check formulas in the following cells:" & foundCells, vbOKOnly + vbExclamation, "Error(s) found" End If End SubVersion regex
Le code suivant utilise Regex pour rechercher plusieurs fonctions XLOOKUP utilisées dans la même formule, ou utilisées avec d'autres fonctions peuvent contenir des virgules supplémentaires.
* Vous devez ajouter une référence d'expressions régulières Microsoft VBScript dans Visual Basic pour utiliser ce code (Outils> Références dans VBA).
Sub advancedFindXLOOKUPs() Dim sht As Worksheet Dim cll As Range Dim rgx As RegExp Dim rMatches As Object Dim rMatch As Object Dim foundCells As String Set sht = ActiveSheet Set rgx = New RegExp With rgx .Pattern = "XLOOKUP(((^,))*,)(3,)(^,)*)" .MultiLine = False .IgnoreCase = True .Global = True End With For Each cll In sht.UsedRange If cll.HasFormula Then Set rMatches = rgx.Execute(cll.Formula) If rMatches.Count Then For Each rMatch In rMatches 'Debug.Print rMatch foundCells = foundCells & vbCrLf & cll.Address Next rMatch End If End If Next cll If foundCells = "" Then MsgBox sht.Name & " is not affected with XLOOKUP function structure update.", vbOKOnly + vbInformation, "No errors" Else MsgBox sht.Name & " is likely affected with XLOOKUP function structure update. Please check formulas in the following cells:" & foundCells, vbOKOnly + vbExclamation, "Error(s) found" End If End Sub
