URGENT: Adoptătorii timpurii ar trebui să își verifice formulele XLOOKUP - Știri

O schimbare interesantă s-a întâmplat cu funcția XLOOKUP din actualizarea Office Insiders care a apărut la 1 noiembrie 2019. Mulți insideri vor primi această actualizare pe măsură ce vor sosi la muncă luni 4 noiembrie 2019

Dacă ați folosit noua funcție XLOOKUP și dacă ați folosit argumentul Match_Mode pentru a căuta valoarea doar mai mare sau doar mai mică, funcțiile dvs. XLOOKUP existente se vor întrerupe.

Noua modificare la XLOOKUP: argumentul If_Not_Found, care a fost inițial adăugat ca un al șaselea argument opțional, a fost mutat pentru a fi al patrulea argument.

Luați în considerare următoarea formulă, care anterior solicita următoarea potrivire mai mare:

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

Când deschideți un registru de lucru cu o formulă de genul acesta, formula nu se rupe imediat. Recalcularea inteligentă a Excelului nu va recalcula formula până când nu editați formula sau până când editați unul dintre numerele din H2: H99 sau J2: J99.

Cu toate acestea, odată ce editați tabelul de căutare, atunci Excel recalculează toate funcțiile XLOOKUP care au folosit tabelul. Înainte de modificare, solicitați o potrivire aproximativă care să redea următoarea valoare mai mare. După modificare, solicitați o potrivire exactă (deoarece formula dvs. originală nu are un al cincilea argument) și, de asemenea, specificați accidental că, dacă nu se găsește o potrivire exactă, atunci doriți să inserați 1 ca rezultat.

„Este într-adevăr un joc insidios de lovitură-aluniță”, a spus Bill Jelen, editor of.com. Apăsați F2 pentru a privi o formulă, iar formula nu mai funcționează. Alte formule din foaia de lucru ar putea părea să funcționeze în continuare, dar sunt o bombă cu ceas care aștepta să devină greșită atunci când este declanșat un recalc. "

Pentru a vedea cum se întâmplă schimbarea, urmăriți de la 0:35 la 0:55 secunda în acest videoclip:

Urmăriți videoclipul

Când vă înscrieți la programul Office Insiders, paragraful 7c din Termeni și condiții spune că „Putem lansa Serviciile sau caracteristicile acestora într-o versiune de previzualizare sau beta, care poate să nu funcționeze corect sau în același mod în care poate funcționa versiunea finală . "

Echipa Excel vă recomandă să ajustați orice formulă XLOOKUP care folosea argumentele opționale. Dacă ați folosit XLOOKUP frecvent, următorul cod va examina un registru de lucru și va identifica posibilele formule de problemă.

Versiune de bază

Codul următor caută celulele formulei care încep cu =XLOOKUPși conțin mai mult de 2 virgule.

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

Versiunea Regex

Codul următor folosește Regex pentru a găsi mai multe funcții XLOOKUP utilizate în aceeași formulă sau utilizate cu alte funcții pot conține virgule suplimentare.

* Trebuie să adăugați referințe Microsoft VBScript Regular Expressions în Visual Basic pentru a utiliza acest cod (Instrumente> Referințe în 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