ТЕРМІНОВО: Першо усиновлювачі повинні перевірити свої формули 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 як результат.

"Це справді підступна гра" побиття крота ", - сказав Білл Джелен, видавець of.com. Ви натискаєте клавішу F2, щоб переглянути формулу, і формула перестає працювати. Можливо, інші формули на аркуші продовжують працювати, але вони є тикаючою бомбою сповільнення, яка чекає помилки, коли спрацьовує повторне повторення "

Щоб побачити, як відбуваються зміни, дивіться з 0:35 до 0:55 секунди в цьому відео:

Переглянути відео

Коли ви підписуєтесь на програму Office Insiders, пункт 7c Загальних положень та умов говорить, що "Ми можемо випустити Служби або їх функції в попередньому перегляді або бета-версії, які можуть не працювати належним чином або таким же чином може працювати остаточна версія . "

Команда 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

Версія регулярного виразу

Наступний код використовує регулярний вираз для пошуку декількох функцій 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