Eine aufregende Änderung hat die XLOOKUP-Funktion im Office Insider-Update vom 1. November 2019 erfahren. Viele Insider werden dieses Update erhalten, wenn sie am Montag, dem 4. November 2019, zur Arbeit kommen.
Wenn Sie die neue XLOOKUP-Funktion verwendet haben und das Match_Mode-Argument verwendet haben, um nach dem Wert zu suchen, der nur größer oder nur kleiner ist, werden Ihre vorhandenen XLOOKUP-Funktionen unterbrochen.
Die neue Änderung an XLOOKUP: Das Argument If_Not_Found, das ursprünglich als optionales sechstes Argument hinzugefügt wurde, wurde als viertes Argument verschoben.
Betrachten Sie die folgende Formel, die zuvor nach der nächstgrößeren Übereinstimmung gefragt hat:
=XLOOKUP(A2,H2:H99,J2:J99,1)
Wenn Sie eine Arbeitsmappe mit einer solchen Formel öffnen, wird die Formel nicht sofort unterbrochen. Die intelligente Neuberechnung von Excel berechnet die Formel erst neu, wenn Sie die Formel bearbeiten oder wenn Sie eine der Zahlen in H2: H99 oder J2: J99 bearbeiten.
Sobald Sie jedoch die Nachschlagetabelle bearbeitet haben, berechnet Excel alle XLOOKUP-Funktionen, die die Tabelle verwendet haben, neu. Vor der Änderung haben Sie nach einer ungefähren Übereinstimmung gefragt, die den nächstgrößeren Wert zurückgibt. Nach der Änderung fragen Sie nach einer genauen Übereinstimmung (da Ihre ursprüngliche Formel kein fünftes Argument enthält) und geben versehentlich an, dass Sie stattdessen eine 1 als Ergebnis einfügen möchten, wenn keine genaue Übereinstimmung gefunden wird.
"Es ist wirklich ein heimtückisches Schlag-auf-Schlag-Spiel", sagte Bill Jelen, Herausgeber von.com. Sie drücken F2, um eine Formel anzuzeigen, und die Formel funktioniert nicht mehr. Andere Formeln im Arbeitsblatt scheinen weiterhin zu funktionieren, aber sie sind eine tickende Zeitbombe, die darauf wartet, falsch zu werden, wenn eine Neuberechnung ausgelöst wird. "
Um zu sehen, wie sich die Änderung vollzieht, schauen Sie in diesem Video von 0:35 auf 0:55 Sekunden:
Schau Video
Wenn Sie sich für das Office Insiders-Programm anmelden, heißt es in Absatz 7c der Allgemeinen Geschäftsbedingungen: "Wir veröffentlichen die Dienste oder ihre Funktionen möglicherweise in einer Vorschau- oder Beta-Version, die möglicherweise nicht richtig oder genauso funktioniert wie die endgültige Version . "
Das Excel-Team empfiehlt, dass Sie alle XLOOKUP-Formeln anpassen müssen, die die optionalen Argumente verwendet haben. Wenn Sie XLOOKUP häufig verwendet haben, untersucht der folgende Code eine Arbeitsmappe und identifiziert mögliche Problemformeln.
Basisversion
Der folgende Code sucht nach den Formelzellen, die mit =XLOOKUPmehr als 2 Kommas beginnen und diese enthalten.
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 SubRegex-Version
Der folgende Code verwendet Regex, um mehrere XLOOKUP-Funktionen zu finden, die in derselben Formel verwendet werden oder mit anderen Funktionen verwendet werden. Diese können zusätzliche Kommas enthalten.
* Sie müssen in Visual Basic eine Microsoft VBScript-Referenz für reguläre Ausdrücke hinzufügen, um diesen Code verwenden zu können (Extras> Referenzen in 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
