K vzrušujúcej zmene došlo vo funkcii XLOOKUP v aktualizácii Office Insider, ktorá vyšla 1. novembra 2019. Mnoho Insiderov dostane túto aktualizáciu, keď prídu do práce v pondelok 4. novembra 2019.
Ak ste používali novú funkciu XLOOKUP a ak ste pomocou argumentu Match_Mode hľadali hodnotu, ktorá je väčšia alebo menšia, vaše existujúce funkcie XLOOKUP sa zlomia.
Nová zmena v XLOOKUPE: argument If_Not_Found, ktorý bol pôvodne pridaný ako voliteľný šiesty argument, bol presunutý ako štvrtý argument.
Zvážte nasledujúci vzorec, ktorý predtým požadoval nasledujúcu väčšiu zhodu:
=XLOOKUP(A2,H2:H99,J2:J99,1)
Keď otvoríte zošit s takýmto vzorcom, vzorec sa okamžite nerozbije. Inteligentný prepočet programu Excel neprepočíta vzorec, kým neupravíte vzorec alebo kým neupravíte jedno z čísel v H2: H99 alebo J2: J99.
Po úprave vyhľadávacej tabuľky však program Excel prepočíta všetky funkcie XLOOKUP, ktoré tabuľku používali. Pred zmenou ste požadovali približnú zhodu, ktorá vrátila nasledujúcu väčšiu hodnotu. Po zmene požadujete presnú zhodu (pretože váš pôvodný vzorec nemá piaty argument) a tiež náhodne zadáte, že ak sa nenájde presná zhoda, namiesto toho chcete do výsledku vložiť 1.
„Je to skutočne zákerná hra na facku,“ uviedol Bill Jelen, vydavateľ stránky.com. Stlačením klávesu F2 sa pozriete na vzorec a vzorec prestane fungovať. Môže sa zdať, že iné vzorce v pracovnom hárku fungujú ďalej, ale sú to tikajúca časovaná bomba, ktorá čaká na chybu, keď sa spustí prepočet. “
Ak chcete vidieť, ako sa zmena deje, sledujte od 0:35 do 0:55 sekundy v tomto videu:
Pozeraj video
Keď sa zaregistrujete do programu Office Insiders, v odseku 7c Zmluvných podmienok sa uvádza, že „Služby alebo ich funkcie môžeme vydať v ukážkovej alebo beta verzii, ktorá nemusí fungovať správne alebo rovnako môže fungovať aj finálna verzia. . “
Tím programu Excel radí, že je potrebné upraviť všetky vzorce XLOOKUPU, ktoré používali voliteľné argumenty. Ak ste XLOOKUP používali často, nasledujúci kód preskúma zošit a identifikuje možné problémové vzorce.
Základná verzia
Nasledujúci kód hľadá bunky vzorca začínajúce sa =XLOOKUPa obsahujúce viac ako 2 čiarky.
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 SubVerzia regulárneho výrazu
Nasledujúci kód používa program Regex na vyhľadanie viacerých funkcií XLOOKUPu použitých v rovnakom vzorci alebo použitých s inými funkciami, ktoré môžu obsahovať ďalšie čiarky.
* Ak chcete použiť tento kód, musíte do jazyka Visual Basic pridať odkaz na regulárne výrazy Microsoft VBScript (Nástroje> Odkazy vo 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
