En spännande förändring hände med XLOOKUP-funktionen i Office Insiders-uppdateringen som kom ut den 1 november 2019. Många Insiders kommer att få den här uppdateringen när de kommer till jobbet måndagen 4 november 2019.
Om du har använt den nya XLOOKUP-funktionen och om du har använt Match_Mode-argumentet för att leta efter värdet som bara är större eller bara mindre, kommer dina befintliga XLOOKUP-funktioner att gå sönder.
Den nya ändringen till XLOOKUP: If_Not_Found-argumentet, som ursprungligen lades till som ett valfritt sjätte argument, har flyttats till det fjärde argumentet.
Tänk på följande formel, som tidigare bad om nästa större matchning:
=XLOOKUP(A2,H2:H99,J2:J99,1)
När du öppnar en arbetsbok med en sådan formel går formeln inte omedelbart. Excels intelligenta omräkning beräknar inte formeln förrän du redigerar formeln eller tills du redigerar ett av siffrorna i H2: H99 eller J2: J99.
Men när du har redigerat uppslagstabellen, räknar Excel om alla XLOOKUP-funktioner som använde tabellen. Innan ändringen bad du om en ungefärlig matchning som gav nästa större värde. Efter ändringen ber du om en exakt matchning (eftersom din ursprungliga formel inte har ett femte argument) och också av misstag anger att om en exakt matchning inte hittas, vill du istället infoga en 1 som resultatet.
"Det är verkligen ett lömskt spel av" whack-a-mole ", säger Bill Jelen, utgivare av.com. Du trycker på F2 för att titta på en formel och formeln slutar fungera. Andra formler i kalkylbladet kan tyckas fortsätta att fungera, men de är en tickande tidsbomb som väntar på att bli fel när en omräkning utlöses. "
För att se förändringen hända, titta från 0:35 till 0:55 andra märket i den här videon:
Kolla på video
När du registrerar dig för Office Insiders-programmet säger punkt 7c i användarvillkoren att "Vi kan släppa tjänsterna eller deras funktioner i en förhandsgranskning eller betaversion, som kanske inte fungerar korrekt eller på samma sätt som den slutliga versionen kan . "
Excel-teamet rekommenderar att du behöver justera alla XLOOKUP-formler som använde de valfria argumenten. Om du har använt XLOOKUP ofta kommer följande kod att undersöka en arbetsbok och identifiera möjliga problemformler.
Grundversion
Följande kod söker efter formelcellerna som börjar med =XLOOKUPoch innehåller mer än två komma.
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
Följande kod använder Regex för att hitta flera XLOOKUP-funktioner som används i samma formel, eller som används med andra funktioner kan innehålla ytterligare komma.
* Du måste lägga till Microsoft VBScript Regular Expressions-referens i Visual Basic för att använda den här koden (Verktyg> Referenser i 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
