Funkce XLOOKUP se v aktualizaci Office Insider, která vyšla 1. listopadu 2019, stala vzrušující změnou. Mnoho Insiderů obdrží tuto aktualizaci, jakmile dorazí do práce v pondělí 4. listopadu 2019.
Pokud používáte novou funkci XLOOKUP a pokud jste použili argument Match_Mode k vyhledání hodnoty větší nebo menší, vaše stávající funkce XLOOKUP se rozbijí.
Nová změna XLOOKUP: argument If_Not_Found, který byl původně přidán jako volitelný šestý argument, byl přesunut jako čtvrtý argument.
Zvažte následující vzorec, který dříve požadoval další větší shodu:
=XLOOKUP(A2,H2:H99,J2:J99,1)
Když otevřete sešit s tímto vzorcem, vzorec se okamžitě nerozbije. Inteligentní přepočet aplikace Excel nebude přepočítávat vzorec, dokud neupravíte vzorec nebo dokud neupravíte jedno z čísel v H2: H99 nebo J2: J99.
Jakmile však upravíte vyhledávací tabulku, Excel přepočítá všechny funkce XLOOKUP, které tabulku používaly. Před změnou jste požadovali přibližnou shodu, která vrátila další větší hodnotu. Po změně požadujete přesnou shodu (protože váš původní vzorec nemá pátý argument) a také náhodně určíte, že pokud není nalezena přesná shoda, chcete místo toho vložit 1 jako výsledek.
„Je to opravdu zákeřná hra na ránu,“ řekl Bill Jelen, vydavatel of.com. Stisknutím klávesy F2 zobrazíte vzorec a vzorec přestane fungovat. Jiné vzorce v listu se mohou zdát, že fungují, ale jsou to tikající časovaná bomba, která čeká, až se pokazí, když se spustí přepočítání. “
Chcete-li vidět, jak se změna odehrává, sledujte v tomto videu značku od 0:35 do 0:55 sekund:
Sledovat video
Když se zaregistrujete do programu Office Insiders, odstavec 7c Podmínek říká, že „Služby nebo jejich funkce můžeme vydat v náhledové nebo beta verzi, která nemusí fungovat správně nebo stejně může fungovat i finální verze . “
Tým aplikace Excel doporučuje, že musíte upravit všechny vzorce XLOOKUP, které používaly volitelné argumenty. Pokud jste XLOOKUP používali často, následující kód prozkoumá sešit a identifikuje možné vzorce problémů.
Základní verze
Následující kód hledá buňky vzorce začínající =XLOOKUPa obsahující více než 2 čárky.
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 SubVerze Regex
Následující kód používá Regex k vyhledání více funkcí XLOOKUP použitých ve stejném vzorci nebo použitých s jinými funkcemi může obsahovat další čárky.
* Abyste mohli tento kód použít, musíte přidat odkaz na regulární výrazy Microsoft VBScript v jazyce Visual Basic (Nástroje> Odkazy ve 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
