СРОЧНО: первые пользователи должны проверить свои формулы XLOOKUP - Новости

Захватывающее изменение произошло с функцией XLOOKUP в обновлении для участников программы предварительной оценки Office, которое вышло 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 в качестве результата.

«Это действительно коварная игра в крота», - сказал Билл Джелен, издатель of.com. Вы нажимаете F2, чтобы просмотреть формулу, и формула перестает работать. Может показаться, что другие формулы на листе продолжают работать, но они представляют собой бомбу замедленного действия, ожидающую, когда произойдет перерасчет ».

Чтобы увидеть, как происходит изменение, посмотрите на это видео с отметки 0:35 до 0:55 секунды:

Смотреть видео

Когда вы подписываетесь на программу предварительной оценки Office, в пункте 7c Условий говорится, что «Мы можем выпустить Услуги или их функции в предварительной или бета-версии, которая может работать некорректно или так же, как окончательная версия. . "

Команда Excel сообщает, что вам необходимо настроить любые формулы XLOOKUP, в которых использовались необязательные аргументы. Если вы часто использовали XLOOKUP, следующий код проверит книгу и определит возможные формулы проблемы.

Базовая версия

Следующий код ищет ячейки формулы, начинающиеся с =XLOOKUPдвух запятых и содержащие их.

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