Se produjo un cambio emocionante en la función XLOOKUP en la actualización de Office Insiders que salió el 1 de noviembre de 2019. Muchos Insiders recibirán esta actualización a medida que lleguen a trabajar el lunes 4 de noviembre de 2019.
Si ha estado usando la nueva función XLOOKUP y si ha usado el argumento Match_Mode para buscar el valor más grande o más pequeño, sus funciones XLOOKUP existentes se romperán.
El nuevo cambio a XLOOKUP: el argumento If_Not_Found, que se agregó originalmente como un sexto argumento opcional, se ha movido para ser el cuarto argumento.
Considere la siguiente fórmula, que anteriormente pedía la siguiente coincidencia más grande:
=XLOOKUP(A2,H2:H99,J2:J99,1)
Cuando abre un libro con una fórmula como esta, la fórmula no se rompe inmediatamente. El recalc inteligente de Excel no recalcará la fórmula hasta que edite la fórmula, o hasta que edite uno de los números en H2: H99 o J2: J99.
Sin embargo, una vez que edita la tabla de búsqueda, Excel recalca todas las funciones XLOOKUP que usaron la tabla. Antes del cambio, estaba solicitando una Coincidencia aproximada que devolviera el siguiente valor mayor. Después del cambio, solicita una coincidencia exacta (porque su fórmula original no tiene un quinto argumento) y también especifica accidentalmente que si no se encuentra una coincidencia exacta, entonces desea insertar un 1 como resultado.
"Es realmente un juego insidioso de golpear un topo", dijo Bill Jelen, editor de.com. Presiona F2 para ver una fórmula y la fórmula deja de funcionar. Puede parecer que otras fórmulas de la hoja de trabajo siguen funcionando, pero son una bomba de tiempo esperando a equivocarse cuando se activa un recalc ".
Para ver cómo ocurre el cambio, mire desde la marca de 0:35 a 0:55 segundos en este video:
Ver video
Cuando se registra en el programa Office Insiders, el párrafo 7c de los Términos y condiciones dice que "Podemos lanzar los Servicios o sus funciones en una versión preliminar o beta, que puede no funcionar correctamente o de la misma manera que la versión final . "
El equipo de Excel advierte que debe ajustar las fórmulas de XLOOKUP que usaban argumentos opcionales. Si ha estado utilizando XLOOKUP con frecuencia, el siguiente código examinará un libro e identificará posibles fórmulas de problemas.
Versión básica
El siguiente código busca las celdas de fórmula que comienzan con =XLOOKUPy contienen más de 2 comas.
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 SubVersión Regex
El siguiente código usa Regex para encontrar múltiples funciones XLOOKUP usadas en la misma fórmula, o usadas con otras funciones pueden contener comas adicionales.
* Debe agregar la referencia de expresiones regulares de Microsoft VBScript en Visual Basic para usar este código (Herramientas> Referencias en 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
