Un cambiamento entusiasmante è avvenuto alla funzione XLOOKUP nell'aggiornamento di Office Insiders uscito il 1 ° novembre 2019. Molti Insider riceveranno questo aggiornamento non appena arriveranno al lavoro lunedì 4 novembre 2019.
Se hai utilizzato la nuova funzione XLOOKUP e se hai utilizzato l'argomento Match_Mode per cercare il valore appena più grande o appena più piccolo, le tue funzioni XLOOKUP esistenti si interromperanno.
La nuova modifica a XLOOKUP: l'argomento If_Not_Found, che era stato originariamente aggiunto come sesto argomento opzionale, è stato spostato come quarto argomento.
Considera la seguente formula, che in precedenza richiedeva la corrispondenza successiva più grande:
=XLOOKUP(A2,H2:H99,J2:J99,1)
Quando apri una cartella di lavoro con una formula come questa, la formula non si interrompe immediatamente. Il ricalcolo intelligente di Excel non ricalcola la formula finché non modifichi la formula o finché non modifichi uno dei numeri in H2: H99 o J2: J99.
Tuttavia, una volta modificata la tabella di ricerca, Excel ricalcola tutte le funzioni XLOOKUP che hanno utilizzato la tabella. Prima della modifica, chiedevi una corrispondenza approssimativa che restituisse il valore successivo più grande. Dopo la modifica, stai chiedendo una corrispondenza esatta (perché la tua formula originale non ha un quinto argomento) e specificando anche accidentalmente che se non viene trovata una corrispondenza esatta, allora vuoi inserire un 1 come risultato.
"È davvero un gioco insidioso di whack-a-mole", ha detto Bill Jelen, editore di.com. Premi F2 per guardare una formula e la formula smette di funzionare. Altre formule nel foglio di lavoro potrebbero sembrare che continuino a funzionare, ma sono una bomba a orologeria in attesa di diventare sbagliata quando viene attivato un ricalcolo.
Per vedere il cambiamento in atto, guarda dal secondo 0:35 allo 0:55 in questo video:
Guarda un video
Quando ti iscrivi al programma Office Insiders, il paragrafo 7c dei Termini e condizioni dice che "Potremmo rilasciare i Servizi o le loro funzionalità in una versione di anteprima o beta, che potrebbe non funzionare correttamente o nello stesso modo in cui la versione finale potrebbe funzionare . "
Il team di Excel consiglia di modificare le formule XLOOKUP che utilizzavano gli argomenti facoltativi. Se hai utilizzato XLOOKUP spesso, il codice seguente esaminerà una cartella di lavoro e identificherà le possibili formule problematiche.
Versione base
Il codice seguente cerca le celle della formula che iniziano con =XLOOKUPe contengono più di 2 virgole.
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 SubVersione Regex
Il codice seguente utilizza Regex per trovare più funzioni XLOOKUP utilizzate nella stessa formula o utilizzate con altre funzioni possono contenere virgole aggiuntive.
* È necessario aggiungere il riferimento alle espressioni regolari Microsoft VBScript in Visual Basic per utilizzare questo codice (Strumenti> Riferimenti in 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
