La compañía de Jeffrey tiene una serie de informes que utilizan una gran cantidad de fórmulas de matriz CSE (Ctrl + Shift + Enter). Cuando alguien olvida mantener presionadas las teclas Ctrl y Shift al presionar Enter, las fórmulas resultantes no son iguales a la respuesta correcta. Auditar cada celda, buscar los corchetes \ {} es tedioso y requiere mucho tiempo. Jeffrey se pregunta si hay una manera rápida de encontrar los «corchetes que faltan» o generar un indicador de error si no se presiona Ctrl + Shift + Enter cuando debería hacerlo.

No existe un método intrínseco o formulado para hacer esto en Excel. Esto significa que debe recurrir a una solución basada en una macro.

Afortunadamente, VBA ofrece varias formas diferentes de abordar este problema. Un enfoque es simplemente usar una fórmula para asegurarse de que cada fórmula dentro de una selección sea en realidad una fórmula de matriz.

Sub MakeCSE1()

Dim rCell As Range

For Each rCell In Selection         rCell.FormulaArray = rCell.Formula     Next rCell End Sub

Esta macro asume que seleccionará las celdas que se «convertirán» antes de ejecutar la macro. Si lo prefiere, puede definir un rango de celdas (darle un nombre al rango) y luego ejecutar una macro similar que siempre hace su trabajo en ese rango.

Sub MakeCSE2()

Dim rng As Range     Dim rCell As Range     Dim rArea As Range

Set rng = Range("CSERange")

For Each rArea In rng.Areas         For Each rCell In rArea.Cells             If rCell.HasArray = False Then                 rCell.FormulaArray = rCell.Formula             End If         Next rCell     Next rArea End Sub

Esta macro busca un rango llamado CSERange y luego verifica cada celda en el rango. Si no contiene una fórmula de matriz, la fórmula se convierte en una fórmula de matriz.

Tenga en cuenta el uso de la propiedad HasArray para verificar si una celda contiene una fórmula de matriz. En realidad, esta propiedad puede resultar útil de otras formas. Por ejemplo, podría crear una función simple definida por el usuario, como esta:

Function NoCellArray1(rng As Range) As Boolean     NoCellArray1 = Not rng.HasArray End Function

Esta función devuelve True si la celda a la que se apunta no contiene una fórmula de matriz. Si contiene uno, se devuelve False. A continuación, puede utilizar esta función como base para un formato condicional. Todo lo que necesitas hacer es crear un formato que lo use de esta manera:

=NoCellArray1(A5)

Dado que NoCellArray devuelve True si la celda no contiene una fórmula de matriz, su formato condicional podría establecer el color de la celda en rojo o establecer algún otro signo visible de que la celda no tiene la fórmula de matriz requerida. También puede utilizar la siguiente función para realizar la misma tarea:

Function NoCellArray2(rng As Range) As Boolean     NoCellArray2 = (Evaluate(rng.FormulaArray) <> rng.Value)

End Function

Un enfoque completamente diferente es agregar algo a sus fórmulas que les permita reconocerlas fácilmente como fórmulas de matriz. Por ejemplo, puede agregar lo siguiente al final de cualquiera de sus fórmulas de matriz:

+N("{")

Esto no afecta el cálculo de ninguna manera, pero se puede verificar fácilmente para ver si está allí. La verificación puede realizarla un controlador de eventos, como el siguiente:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Right(Selection.FormulaArray, 5) = "(""{"")" Then         ActiveCell.Select         Selection.FormulaArray = ActiveCell.Formula     End If End Sub

Tenga en cuenta que el controlador comprueba si la fórmula termina con («\ {«) y, si lo hace, obliga a que la fórmula se trate como una fórmula de matriz. Lo mejor de este enfoque es que nunca más tendrá que presionar Ctrl + Shift + Enter en la hoja de trabajo; el controlador de eventos se encarga de ello por usted. Si, en algún momento, desea volver a convertir la fórmula a una versión normal (sin matriz), simplemente modifique la fórmula para que no incluya + N («\ {«).

_Nota: _

Si desea saber cómo usar las macros descritas en esta página (o en cualquier otra página de los sitios ExcelTips), he preparado una página especial que incluye información útil.

link: / excelribbon-ExcelTipsMacros [Haga clic aquí para abrir esa página especial en una nueva pestaña del navegador].

ExcelTips es su fuente de formación rentable en Microsoft Excel.

Este consejo (478) se aplica a Microsoft Excel 2007, 2010, 2013, 2016, 2019 y Excel en Office 365. Puede encontrar una versión de este consejo para la interfaz de menú anterior de Excel aquí:

link: / excel-Checking_for_Proper_Entry_of_Array_Formulas [Comprobación de la entrada correcta de fórmulas de matriz].