El término matriz se refiere a una colección de datos que se encuentran en una columna o fila de Excel o una combinación de éstas. En Excel, esos elementos pueden residir en una única fila (lo que se denomina una matriz horizontal unidimensional), una columna (una matriz vertical unidimensional) o varias filas y columnas (una matriz bidimensional).
¿Qué son las Fórmulas de Matriz?
Una fórmula de matriz es una fórmula que puede realizar varios cálculos en uno o varios elementos de una matriz. Las fórmulas de matriz pueden devolver varios resultados o un único resultado. Por ejemplo, se puede colocar una fórmula de matriz en un rango de celdas y utilizarla para calcular una columna o fila de subtotales. También se puede colocar en una sola celda y calcular una cantidad única.
Matrices Dinámicas vs. Fórmulas de Matriz Heredadas (CSE)
A partir de la actualización de septiembre de 2018 para Microsoft 365, cualquier fórmula que pueda devolver varios resultados los desbordará automáticamente hacia abajo o en las celdas vecinas. Este cambio de comportamiento también se acompaña de varias funciones de matriz dinámica nuevas. Las fórmulas de matriz dinámica, ya sea que estén usando funciones existentes o las funciones de matriz dinámica, solo deben ingresarse en una sola celda y luego confirmarse presionando Entrar.
Anteriormente, las fórmulas de matriz heredadas requerían primero seleccionar todo el rango de salida y luego confirmar la fórmula con Ctrl + Shift + Enter. Se suele hacer referencia a ellas como fórmulas CSE. Excel incluye la fórmula entre llaves ({ }), eso indica que estamos haciendo uso de una fórmula de matriz en Excel, y coloca una instancia de la misma en cada celda del rango seleccionado.
Las fórmulas de matriz dinámica se desbordarán automáticamente en el rango de salida. Si el rango de desbordamiento previsto está bloqueado por algún motivo, las matrices dinámicas introdujeron el error #DESBORDAMIENTO! (#SPILL!).
Lea también: IVA 21% Excel
Ventajas de las Fórmulas de Matriz
El uso de fórmulas de matriz ofrece varias ventajas significativas:
- Eficiencia: Las funciones de matriz pueden ser una forma eficaz de crear fórmulas complejas. Por ejemplo, si tiene 1.000 filas de datos, puede sumar parte de los datos o todos ellos si crea una fórmula de matriz de una sola celda en lugar de arrastrarla a las 1.000 filas.
- Tamaños de archivo más pequeños: A menudo puede usar una fórmula de matriz única en lugar de varias fórmulas intermedias. Esto puede reducir significativamente el tamaño del archivo, especialmente con miles de filas.
- Consistencia: Si hace clic en cualquiera de las celdas de un rango de salida de una fórmula de matriz, verá la misma fórmula.
- Seguridad: No se puede sobrescribir un componente de una fórmula de matriz de varias celdas. Si intenta eliminar una parte de la matriz, Excel no cambiará la salida de la matriz.
- Flexibilidad: Una fórmula de una sola celda puede ser totalmente independiente de una fórmula de varias celdas, lo que le permite cambiar otras fórmulas sin afectar el resultado de la matriz.
Declaración de Matrices Fijas en VBA para Excel
Las matrices fijas tienen un tamaño predefinido que no se puede modificar durante la ejecución del programa. También se conocen como matrices estáticas. En VBA, las matrices se declaran del mismo modo que otras variables, con las instrucciones Dim, Static, Private o Public. La diferencia fundamental con las variables escalares es que se debe especificar el tamaño de la matriz.
Sintaxis Básica
Una matriz se declara incluyendo paréntesis después del nombre o identificador de la matriz. Un número entero se coloca dentro de los paréntesis, definiendo el número de elementos de la matriz.
Es una buena práctica declarar siempre las matrices con un tipo de dato explícito. Al igual que con el resto de declaración de variables, a menos que especifique un tipo de datos para la matriz, el tipo de datos de los elementos de la matriz declarada será el tipo Variant.
- Dim miArray(3) As Integer: Esta línea crea una matriz de tipo Integer con 4 elementos (índices de 0 a 3, por defecto).
- Dim miArrayVariant(3) As Variant: Esta línea crea una matriz de tipo Variant con 4 elementos.
Valores Predeterminados
Después de declarar una matriz, contendrá valores predeterminados según su tipo de dato:
Lea también: Guía IVA reducido
- Las matrices numéricas contendrán el número 0.
- Las matrices de cadenas contendrán la cadena vacía ("").
- Las matrices Variant contendrán la palabra clave Empty.
- Las matrices de objetos contendrán la palabra clave Nothing.
Límite Inferior Explícito
Es posible declarar explícitamente el límite inferior de su matriz fija. Esto puede hacerse con la instrucción Option Base (al inicio de un módulo para afectar a todas las matrices no declaradas explícitamente) o usando la palabra clave To en la declaración.
Si una matriz se indexa desde 0 o 1 depende del valor de la instrucción Option Base. Por defecto, todas las matrices VBA comienzan en el índice cero.
- Dim miArray(0 To 100) As Integer: Esta matriz contiene 101 elementos, con índices del 0 al 100.
- Dim otroArray(1 To 100) As Integer: Esta matriz contiene 100 elementos, con índices del 1 al 100.
Uso de Memoria
El uso de la memoria para los elementos de una matriz fija en VBA varía según el tipo de datos:
| Tipo de Dato | Bytes por Elemento |
|---|---|
| Integer | 2 |
| Double | 8 | Long | 4 | Single | 4 |
| Variant (Numérico) | 16 |
| Variant (Cadena) | 22 |
El tamaño máximo de una matriz varía en función del sistema operativo y de la memoria disponible.
Matrices Dinámicas (Breve Contraste)
Aunque el foco es la declaración de matrices fijas, es importante mencionar las matrices dinámicas. Al declarar una matriz dinámica, puede establecer su tamaño mientras el código se está ejecutando. Puede usar la instrucción ReDim para declarar una matriz implícitamente en un procedimiento o para cambiar el número de dimensiones, definir el número de elementos y establecer los límites de cada dimensión de una matriz ya declarada como dinámica. Es importante recordar que cada vez que se usa ReDim (sin la palabra clave Preserve), se perderán los valores existentes de la matriz.
Lea también: ¿Cómo localizar tus XML del SAT?
Constantes de Matriz en Fórmulas de Hoja de Cálculo
Las constantes de matriz son un componente de las fórmulas de matriz y le permiten incrustar un conjunto fijo de valores directamente en su fórmula. Pueden contener números, texto, valores lógicos (como VERDADERO y FALSO) y valores de error como #N/A. Puede usar los números en formato entero, decimal y científico.
Sin embargo, las constantes de matriz no pueden contener matrices, fórmulas ni funciones adicionales. Solo pueden incluir texto o números separados por comas o puntos y coma. Si especifica una fórmula como {1\2\A1:D4} o {1\2\SUMA(Q2:Z8)}, Excel mostrará un mensaje de advertencia.
Creación de Constantes de Matriz
- Si separa los elementos con comas, creará una matriz horizontal (una fila), por ejemplo, ={1,2,3,4,5}.
- Si usa caracteres de punto y coma, creará una matriz vertical (una columna), por ejemplo, ={1;2;3;4;5}.
Uso de la Función SECUENCIA para Constantes
La función SECUENCIA ofrece ventajas significativas sobre la introducción manual de los valores constantes de la matriz, ya que ahorra tiempo y ayuda a reducir errores. Por ejemplo:
- =SECUENCIA(1,5) crea una matriz de 1 fila por 5 columnas igual que ={1,2,3,4,5}.
- =SECUENCIA(5) crea una matriz vertical de 5 elementos igual que ={1;2;3;4;5}.
- =SECUENCIA(3,4) crea una matriz bidimensional de 3 filas por 4 columnas.
Las constantes de matriz pueden ser parte de fórmulas más grandes, como =SUMA(D9:H9*SECUENCIA(1,5)) o =SUMA(D9:H9*{1,2,3,4,5}). La función SECUENCIA crea el equivalente de la constante matricial {1,2,3,4,5}. La fórmula multiplica los valores de la matriz almacenada por los valores correspondientes de la constante.
Asignación de Nombres a Constantes de Matriz
El mejor modo de usar las constantes de matriz es ponerles nombre. Las constantes con nombre pueden resultar mucho más sencillas de usar y pueden ocultar parte de la complejidad de sus fórmulas de matriz a otros usuarios. Para hacerlo, vaya a Nombres definidos por fórmulas > Definir nombre y asigne un nombre a su constante.
Cuando emplee una constante con nombre como fórmula de matriz, recuerde escribir el signo igual, como en =Trimestre1, y no solo Trimestre1. Si no lo hace, Excel interpretará la matriz como una cadena de texto y la fórmula no funcionará de la manera esperada.
Un ejemplo práctico es mostrar una lista de 12 meses usando la función SECUENCIA con nombre. Esto usa la función FECHA para crear una fecha basada en el año actual, la función SECUENCIA crea una constante de matriz del 1 al 12, de enero a diciembre y, después, la función TEXTO convierte el formato de visualización a "mmm" (ene, feb, mar, etc.).
Ejemplos Avanzados de Fórmulas de Matriz
Las fórmulas de matriz permiten realizar operaciones complejas en rangos de datos. A continuación, se presentan algunos ejemplos de su aplicación:
Contar Caracteres en un Rango de Celdas
Para contar el número total de caracteres en un rango de celdas, puede combinar LARGO y SUMA. La función LARGO devuelve la longitud de cada cadena de texto contenida en cada celda del rango. A continuación, la función SUMA agrega esos valores.
Encontrar los Valores Más Pequeños o Más Grandes
Para buscar los N valores más pequeños de un rango de celdas, se puede utilizar la función K.ESIMO.MENOR en combinación con una constante de matriz generada por SECUENCIA o una constante de matriz directa. Para encontrar los valores mayores de un rango, se puede reemplazar la función K.ESIMO.MENOR por la función K.ESIMO.MAYOR.
Para generar una matriz de enteros consecutivos que no se vea afectada por la inserción de filas, se pueden combinar las funciones FILA e INDIRECTO. La función INDIRECTO usa cadenas de texto como argumentos, lo que evita que Excel ajuste las referencias cuando se insertan filas o se mueve la fórmula de matriz.
Manejo de Errores en Rangos
La función SUMA de Excel no funcionará si intenta sumar un rango que contenga un valor de error, como #¡VALOR! o #N/A. Una fórmula de matriz puede crear una nueva matriz que contenga los valores originales excluyendo los errores. Esto se logra combinando ESERROR y SI. La función ESERROR busca errores en el rango y SI devuelve cadenas vacías ("") para los valores de error, manteniendo los valores válidos.
Suma y Comparación con Múltiples Condiciones
También se pueden sumar valores que cumplan varias condiciones o crear fórmulas de matriz que usen un tipo de condición O. Sin embargo, no se pueden usar las funciones Y y O directamente en las fórmulas de matriz, ya que devuelven un único valor lógico y las funciones de matriz necesitan matrices de resultados. Se puede solucionar este problema usando lógica condicional que opere a nivel de elementos de matriz.
Además, las fórmulas de matriz pueden comparar los valores de dos rangos de celdas y devolver el número de diferencias entre ellos. Para ello, los rangos de celdas deben ser del mismo tamaño y de la misma dimensión. La función SI puede rellenar una nueva matriz con 0 para no coincidencias y 1 para celdas idénticas.
Otro uso es encontrar el número de fila del valor máximo en un rango. Una fórmula de matriz puede crear una nueva matriz donde cada elemento es el número de fila si la celda correspondiente contiene el valor máximo, o una cadena vacía en caso contrario. La función MIN se puede usar luego para encontrar el número de fila más pequeño (el primero) que contiene el valor máximo.