Ekscytująca zmiana nastąpiła w funkcji XLOOKUP w aktualizacji niejawnych testerów pakietu Office, która ukazała się 1 listopada 2019 r. Wielu niejawnych testerów otrzyma tę aktualizację po przybyciu do pracy w poniedziałek 4 listopada 2019 r.
Jeśli korzystałeś z nowej funkcji XLOOKUP i jeśli użyłeś argumentu Match_Mode do wyszukania wartości po prostu większej lub po prostu mniejszej, istniejące funkcje XLOOKUP ulegną awarii.
Nowa zmiana w XLOOKUP: argument If_Not_Found, który został pierwotnie dodany jako opcjonalny szósty argument, został przeniesiony na czwarty argument.
Rozważmy następującą formułę, która wcześniej pytała o następne większe dopasowanie:
=XLOOKUP(A2,H2:H99,J2:J99,1)
Po otwarciu skoroszytu z taką formułą formuła nie jest natychmiast przerywana. Inteligentne przeliczanie programu Excel nie przeliczy formuły, dopóki nie edytujesz formuły lub dopóki nie edytujesz jednej z liczb w H2: H99 lub J2: J99.
Jednak po edycji tabeli przeglądowej program Excel ponownie przelicza wszystkie funkcje XLOOKUP, które używały tej tabeli. Przed zmianą prosiłeś o przybliżone dopasowanie, które zwróciło następną większą wartość. Po zmianie pytasz o dokładne dopasowanie (ponieważ oryginalna formuła nie ma piątego argumentu), a także przypadkowo określasz, że jeśli dokładne dopasowanie nie zostanie znalezione, zamiast tego chcesz wstawić 1 jako wynik.
„To naprawdę podstępna gra w walenie w kreta” - powiedział Bill Jelen, wydawca witryny.com. Naciskasz F2, aby spojrzeć na formułę, a formuła przestaje działać. Inne formuły w arkuszu mogą wydawać się nadal działać, ale są to tykająca bomba zegarowa, która czeka, aż się zepsuje po wyzwoleniu przeliczenia ”.
Aby zobaczyć zachodzącą zmianę, obejrzyj od 0:35 do 0:55 sekundy w tym filmie:
Obejrzyj wideo
Kiedy rejestrujesz się w programie Office Insiders, paragraf 7c Warunków mówi, że „Możemy udostępniać Usługi lub ich funkcje w wersji zapoznawczej lub beta, które mogą nie działać poprawnie lub w ten sam sposób, w jaki może działać wersja ostateczna ”.
Zespół programu Excel radzi, aby dostosować wszelkie formuły XLOOKUP, które używały opcjonalnych argumentów. Jeśli często korzystasz z XLOOKUP, poniższy kod przeanalizuje skoroszyt i zidentyfikuje możliwe formuły problemu.
Wersja podstawowa
Poniższy kod wyszukuje komórki formuły zaczynające się od =XLOOKUPi zawierające więcej niż 2 przecinki.
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 SubWersja Regex
Poniższy kod używa Regex do znalezienia wielu funkcji XLOOKUP używanych w tej samej formule lub używanych z innymi funkcjami może zawierać dodatkowe przecinki.
* Aby korzystać z tego kodu, należy dodać odwołanie do wyrażeń regularnych Microsoft VBScript w języku Visual Basic (Narzędzia> Referencje w 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
