СПЕШНО: Ранните осиновители трябва да проверят своите XLOOKUP формули - Новини

Вълнуваща промяна се случи с функцията XLOOKUP в актуализацията на Office Insiders, която излезе на 1 ноември 2019 г. Много инсайдери ще получат тази актуализация, когато пристигнат на работа в понеделник, 4 ноември 2019 г.

Ако сте използвали новата функция XLOOKUP и ако сте използвали аргумента Match_Mode, за да търсите само по-голяма или по-малка стойност, съществуващите ви функции XLOOKUP ще се счупят.

Новата промяна в XLOOKUP: аргументът If_Not_Found, който първоначално беше добавен като незадължителен шести аргумент, бе преместен за четвърти аргумент.

Обмислете следната формула, която преди това искаше следващото по-голямо съвпадение:

=XLOOKUP(A2,H2:H99,J2:J99,1)

Когато отворите работна книга с формула като тази, формулата не се разбива веднага. Интелигентното повторно извикване на Excel няма да припомни формулата, докато не редактирате формулата или докато не редактирате едно от числата в H2: H99 или J2: J99.

След като обаче редактирате таблицата за справка, Excel припомня всички функции на XLOOKUP, които са използвали таблицата. Преди промяната искахте приблизително съвпадение, което връща следващата по-голяма стойност. След промяната искате точно съвпадение (тъй като оригиналната формула няма пети аргумент) и също случайно посочвате, че ако не бъде намерено точно съвпадение, вместо това искате да вмъкнете 1 като резултат.

„Това наистина е коварна игра на ужас,“ каза Бил Джелен, издател на.com. Натискате F2, за да разгледате формула и формулата спира да работи. Може да изглежда, че други формули в работния лист продължават да работят, но те представляват тиктакаща бомба със закъснител, която чака да се обърка, когато се задейства повторно изчисляване. "

За да видите как се случва промяната, гледайте от 0:35 до 0:55 секунди в това видео:

Гледам видео

Когато се регистрирате за програмата Office Insiders, параграф 7в от Общите условия казва, че „Можем да пуснем Услугите или техните функции в предварителен или бета вариант, който може да не работи правилно или по същия начин окончателната версия . "

Екипът на Excel съветва, че трябва да коригирате всички формули XLOOKUP, които са използвали незадължителните аргументи. Ако често използвате XLOOKUP, следният код ще разгледа работна книга и ще идентифицира възможни формули за проблеми.

Основна версия

Следващият код търси клетките с формула, започващи с =XLOOKUPи съдържащи повече от 2 запетаи.

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 Sub

Regex версия

Следният код използва Regex за намиране на множество функции XLOOKUP, използвани в същата формула или използвани с други функции, може да съдържа допълнителни запетаи.

* Трябва да добавите справка за регулярни изрази на Microsoft VBScript в Visual Basic, за да използвате този код (Инструменти> Препратки в 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