prucommercialre.com


Comprobación de entrada apropiado de fórmulas de matriz

La compañía de Jeffrey tiene una serie de informes que utilizan un gran número de CSE (Ctrl + Shift + Enter) fórmulas de matriz. Cuando alguien se olvida de mantener Ctrl y Shift al pulsar Enter, las fórmulas resultantes no son iguales a la respuesta correcta. Auditoría de cada célula, en busca de las llaves {} es tanto tedioso y lento. Jeffrey se pregunta si hay una manera rápida de encontrar los "soportes faltantes" o levantar una bandera de error si Ctrl + Shift + Enter no se presiona cuando debería ser?

No hay ningún método intrínseco o fórmulas de hacer esto en Excel. Esto significa que usted necesita dar vuelta a una solución que se basa en una macro. Afortunadamente, VBA ofrece varias maneras diferentes que usted puede acercarse a este problema. Un enfoque es simplemente utilizar una fórmula para asegurarse de que cada fórmula dentro de una selección es en realidad una fórmula de matriz.

Sub MakeCSE1 ()
Dim rCell como gama

Para cada selección rCell En
rCell.FormulaArray = rCell.Formula
Siguiente rCell
End Sub

Esta macro se supone que usted va a seleccionar las células para ser "convertidos" antes de realmente ejecutar la macro. Si lo prefiere, puede definir un rango de celdas (dar el rango de un nombre) y luego ejecutar una macro similar que siempre hace su trabajo en ese rango.

Sub MakeCSE2 ()
Rng Dim como gama
Dim rCell como gama
Dim rArea como gama

Rng Set = Range ("CSERange")
Para Cada rArea En rng.Areas
Para Cada rCell En rArea.Cells
Si rCell.HasArray = False Then
rCell.FormulaArray = rCell.Formula
End If
Siguiente rCell
Siguiente rArea
End Sub

Esta macro busca un rango con nombre CSERange y luego comprueba cada celda del rango. Si no contiene una fórmula de matriz, entonces la fórmula se convierte en una fórmula de matriz.

Observe el uso de la propiedad HasArray para comprobar si una celda contiene una fórmula de matriz. Esta propiedad puede ser realmente útil en otras formas. Por ejemplo, puede crear una función definida por el usuario simple, como este:

Función NoCellArray1 (RNG como gama) As Boolean
NoCellArray1 = No rng.HasArray
End Function

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

= NoCellArray1 (A5)

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

Función NoCellArray2 (RNG como gama) As Boolean
NoCellArray2 = (Evaluar (rng.FormulaArray) <> rng.Value)
End Function

Un enfoque completamente diferente es añadir algo a sus fórmulas que les permite ser fácilmente reconocidos como fórmulas de matriz. Por ejemplo, se podría añadir lo siguiente al final de cualquiera de sus fórmulas de matriz:

+ N ("{")

Esto no afecta el cálculo de ninguna manera, pero puede ser fácilmente verificado para ver si está allí. La comprobación puede ser realizada por un controlador de eventos, tales como los siguientes:

Private Sub Worksheet_SelectionChange (ByVal Target como gama)
Si Derecha (Selection.FormulaArray, 5) = "(" "{" ")" Entonces
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 la fórmula debe ser tratada como una fórmula de matriz. La gran cosa acerca de este enfoque es que usted nunca tendrá que pulsar Ctrl + Shift + Enter en la hoja de trabajo de nuevo, el controlador de eventos se encarga de ello por usted. Si, en algún momento, que quiere convertir la fórmula de nuevo a un (no-array) versión normal, simplemente modifique la fórmula para que no se incluyen + N ("{").

ExcelTips es su fuente de capacitación rentable Microsoft Excel. Esta punta (473) se aplica a Microsoft Excel 97, 2000, 2002, y 2003. Usted puede encontrar una versión de este consejo para la interfaz de cinta de Excel (Excel 2007 y versiones posteriores) aquí: Comprobación de entrada apropiado de fórmulas de matriz.