Er is een opwindende verandering opgetreden in de functie XLOOKUP in de Office Insiders-update die op 1 november 2019 uitkwam. Veel Insiders zullen deze update ontvangen zodra ze op maandag 4 november 2019 aan het werk komen.
Als je de nieuwe XLOOKUP-functie hebt gebruikt en als je het Match_Mode-argument hebt gebruikt om te zoeken naar de waarde die alleen groter of juist kleiner is, gaan je bestaande XLOOKUP-functies kapot.
De nieuwe wijziging in XLOOKUP: het If_Not_Found-argument, dat oorspronkelijk was toegevoegd als een optioneel zesde argument, is verplaatst naar het vierde argument.
Beschouw de volgende formule, die eerder om de volgende grotere overeenkomst vroeg:
=XLOOKUP(A2,H2:H99,J2:J99,1)
Wanneer u een werkmap opent met een formule als deze, wordt de formule niet meteen afgebroken. Excel's intelligente herberekening zal de formule pas herberekenen als u de formule bewerkt, of totdat u een van de getallen in H2: H99 of J2: J99 bewerkt.
Als u de opzoektabel eenmaal hebt bewerkt, herberekent Excel alle XLOOKUP-functies die de tabel hebben gebruikt. Vóór de wijziging vroeg u om een geschatte overeenkomst die de volgende hogere waarde opleverde. Na de wijziging vraagt u om een exacte overeenkomst (omdat uw originele formule geen vijfde argument heeft) en geeft u ook per ongeluk aan dat als er geen exacte overeenkomst wordt gevonden, u in plaats daarvan een 1 als resultaat wilt invoegen.
"Het is echt een verraderlijk spelletje whack-a-mole", zegt Bill Jelen, uitgever van.com. U drukt op F2 om naar een formule te kijken en de formule werkt niet meer. Andere formules in het werkblad lijken te blijven werken, maar ze zijn een tikkende tijdbom die wacht om fout te worden wanneer een herberekening wordt geactiveerd. "
Om de verandering te zien, bekijk je van 0:35 tot 0:55 seconde in deze video:
Bekijk video
Wanneer u zich aanmeldt voor het Office Insiders-programma, zegt paragraaf 7c van de Algemene voorwaarden: "We kunnen de Services of hun functies vrijgeven in een preview- of bètaversie, die mogelijk niet correct werkt of op dezelfde manier als de definitieve versie. . "
Het Excel-team adviseert dat u XLOOKUP-formules moet aanpassen die de optionele argumenten gebruikten. Als u XLOOKUP vaak hebt gebruikt, onderzoekt de volgende code een werkmap en identificeert mogelijke probleemformules.
Basisversie
De volgende code zoekt naar de formulecellen die beginnen met =XLOOKUPen meer dan 2 komma's bevatten.
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-versie
De volgende code gebruikt Regex om meerdere XLOOKUP-functies te vinden die in dezelfde formule worden gebruikt, of die met andere functies worden gebruikt, kunnen extra komma's bevatten.
* U moet Microsoft VBScript Regular Expressions-referentie toevoegen in Visual Basic om deze code te gebruiken (Tools> References 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
