Directrices y ejemplos de 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. Una matriz es una fila de valores, una columna de valores o una combinación de filas y columnas de valores. Las fórmulas de matriz pueden devolver varios resultados o uno solo.

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 o hacia 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.

Puede usar fórmulas de matriz para realizar tareas complejas, como:

Crear rápidamente conjuntos de datos de muestra.
Contar el número de caracteres incluidos en un rango de celdas.
Sumar únicamente aquellos números que cumplan ciertas condiciones, como los valores más bajos de un rango o los números comprendidos entre un límite superior e inferior.
Sumar cada valor n de un rango de valores.

Los siguientes ejemplos muestran cómo crear fórmulas de matriz de una y varias celdas. Siempre que sea posible, se incluyen ejemplos con algunas de las funciones de matriz dinámica, así como fórmulas de matriz existentes ingresadas como matrices tanto dinámicas como heredadas.

Descargar nuestros ejemplos

Descargar un libro de ejemplo con todos los ejemplos de fórmula de matriz de este artículo.

Microsoft 365 Office 2013 - 2021

Este ejercicio muestra cómo usar fórmulas de matriz de una y varias celdas para calcular un conjunto de cifras de ventas. La primera serie de pasos usa una fórmula de varias celdas para calcular un conjunto de subtotales. La segunda usa una fórmula de una celda para calcular un total general.

Fórmula de matriz de varias celdas
Aquí se calculan las Ventas totales de vehículos tipo cupé o sedán de cada vendedor al introducir =F10:F19*G10:G19 en la celda H10.

Al presionar Entrar, verá los resultados del conjunto de células H10:H19. Observe que el rango de desbordamiento se resalta con un borde cuando selecciona cualquier celda dentro del mismo. También percibirá que las fórmulas en las celdas H10:H19 están atenuadas. Solo están ahí como referencia, por lo que si desea ajustar la fórmula, deberá seleccionar la celda H10, donde se encuentra la fórmula maestra.
Fórmula de matriz de una sola celda

En la celda H20 del libro de ejemplos, escriba o copie y pegue =SUMA(F10:F19*G10:G19) y, a continuación, presione Entrar.

En este caso, Excel multiplica los valores de la matriz (el rango de celdas de F10 a G19) y usa la función SUMA para sumar los totales. El resultado es un total general de 1.590.000 $ en ventas.

Este ejemplo demuestra lo eficaz que puede resultar este tipo de fórmula. Por ejemplo, imagine que 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 arrastrar a las 1.000 filas. Además, observe que la fórmula de una sola celda (en la celda H20) es totalmente independiente de la fórmula de varias celdas (la fórmula de las celdas H10 a H19). Esto pone de manifiesto otra ventaja de las fórmulas de matriz : la flexibilidad. Puede cambiar las otras fórmulas de la columna H sin afectar a la fórmula de la celda H20. También puede ser una buena práctica tener totales independientes como este, ya que ayudan a validar la precisión de los resultados.
Las fórmulas de matriz dinámica también ofrecen estas ventajas:
- Coherencia Si hace clic en cualquiera de las celdas de la columna H10 hacia abajo, verá la misma fórmula. Esa coherencia garantiza una mayor precisión.
- Seguridad No es posible sobrescribir un componente de una fórmula de matriz de varias celdas. Por ejemplo, haga clic en la celda H11 y presione Supr. Excel no cambiará la salida de la matriz. Para cambiarlo, debe seleccionar la celda superior izquierda de la matriz o la celda H10.
- Tamaños de archivo más pequeños A menudo puede usar una fórmula de matriz sencilla en lugar de varias fórmulas intermedias. Por ejemplo, el ejemplo de las ventas de coches emplea una fórmula de matriz para calcular los resultados de la columna E. Si hubiera usado fórmulas estándar (como =F10*G10, F11*G11, F12*G12…), habría necesitado 11 fórmulas distintas para calcular los mismos resultados. No es nada del otro mundo, pero ¿y si tuviera miles de filas en total? Ahí si notaría una gran diferencia.
- Eficacia Las funciones de matriz pueden ser una forma eficiente de compilar fórmulas complejas. La fórmula de matriz =SUM(F10:F19*G10:G19) es lo mismo que: =SUM(F10*G10,F11*G11,F12*G12,F13*G13,F14*G14,F15*G15,F16*G16,F17*G17,F18*G18,F19*G19).
- Desbordamiento Las fórmulas de matriz dinámica se desbordarán automáticamente en el rango de salida. Si los datos de soporte están en una tabla de Excel, las fórmulas de matriz dinámica cambiarán de tamaño automáticamente al agregar o eliminar datos.
- Error #¡DESBORDAMIENTO! Las matrices dinámicas incorporan el error #¡DESBORDAMIENTO!, que indica que el rango de desbordamiento previsto se bloquea por alguna razón. Al solucionar el bloqueo, la fórmula se desbordará automáticamente.

Las constantes de matriz son un componente de las fórmulas de matriz. Para crear constantes de matriz, puede especificar una lista de elementos y delimitarla manualmente entre llaves ({ }), como:

={1\2\3\4\5} o ={"enero"\"febrero"\"marzo"}

Si separa los elementos con comas, creará una matriz horizontal (una fila). Si usa caracteres de punto y coma, creará una matriz vertical (una columna). Para crear una matriz bidimensional, delimite los elementos de cada fila con comas y separe cada fila con punto y coma.

Con el siguiente procedimiento podrá adquirir cierta práctica en la creación de constantes horizontales, verticales y bidimensionales. Se muestran ejemplos usando la función SECUENCIA para generar automáticamente constantes de matriz, así como también constantes de matriz ingresadas manualmente.

Crear una constante horizontal

Use el libro con los ejemplos anteriores o si lo prefiere cree uno nuevo. Seleccione una celda vacía y escriba =SECUENCIA(1,5). La función SECUENCIA compila una matriz de 1 fila por 5 columnas de la misma manera que ={1\2\3\4\5}. Aparece el siguiente resultado:
Crear una constante vertical

Seleccione cualquier celda vacía que tenga espacio debajo y escriba =SECUENCIA(5), o ={1;2;3;4;5}. Aparece el siguiente resultado:
Crear una constante bidimensional

Seleccione cualquier celda vacía que tenga espacio a la derecha y debajo y escriba =SECUENCIA(3,4). Verá el resultado siguiente:

También puede escribir: o ={1\2\3\4;5\6\7\8;9\10\11\12}, pero querrá prestar atención a dónde pone punto y coma frente a comas.

Como puede ver, la opción SECUENCIA ofrece ventajas significativas sobre la introducción manual de los valores constantes de la matriz. Principalmente, le ahorra tiempo, pero también puede ayudar a reducir los errores de la entrada manual. También es más fácil de leer, especialmente porque los puntos y comas pueden ser difíciles de distinguir de los separadores de coma.

Aquí hay un ejemplo que usa constantes de matriz como parte de una fórmula más grande. En el libro de muestra, vaya a Constante en una hoja de fórmula o cree una nueva hoja de cálculo.

En la celda D9, se escribió =SECUENCIA (1,5,3,1), pero también puede ingresar 3, 4, 5, 6 y 7 en las celdas A9:H9. No hay nada especial en esa selección de números en particular, simplemente se eligió algo distinto del 1 al 5 para la diferenciación.

Escriba en la celda E11 =SUM(D9:H9*SECUENCIA(1,5)) o =SUM(D9:H9*{1\2\3\4\5}). Las fórmulas entregan 85.

Usar constantes de matriz en fórmulas. En este ejemplo, usamos =SUMA(D9:H(*SECUENCIA(1,5))

La función SECUENCIA crea el equivalente de la constante de matriz {1\2\3\4\5}. Dado que Excel realiza en primer lugar las operaciones de las expresiones incluidas entre paréntesis, los dos siguientes elementos que entran en funcionamiento son los valores de las celdas en D9:H9 y el operador de multiplicación (*). En este punto, la fórmula multiplica los valores de la matriz almacenada por los valores correspondientes de la constante. Es el equivalente de:

=SUM(D9*1,E9*2,F9*3,G9*4,H9*5) o =SUM(3*1,4*2,5*3,6*4,7*5)

Por último, la función SUMA suma los valores y devuelve 85.

Para evitar el uso de la matriz almacenada y conservar la operación en su totalidad en memoria, puede sustituirla por otra constante de matriz:

=SUM(SECUENCIA(1,5,3,1)*SECUENCIA(1,5)) o =SUM({3\4\5\6\7}*{1\2\3\4\5})

Elementos que puede usar en constantes de matriz

Las constantes de matriz 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. Si incluye texto, deberá especificarlo entre comillas ("texto”).
Las constantes de matriz no pueden contener matrices, fórmulas ni funciones adicionales. En otras palabras, 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 muestra un mensaje de advertencia. Además, los valores numéricos no pueden incluir signos de porcentaje, dólar, comas o paréntesis.

Posiblemente, el mejor modo de usar las constantes de matriz sea 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 ponerle nombre a una constante de matriz y usarla en una fórmula, haga lo siguiente:

Vaya a Fórmulas > Nombres definidos > Definir nombre. En el cuadro Nombre, escriba Trimestre1. En el cuadro Se refiere a, escriba la siguiente constante (recuerde escribir las llaves de forma manual):

={"Enero","Febrero","Marzo"}

El cuadro de diálogo debería ahora tener un aspecto similar al siguiente:

Agregar una constante de matriz con nombre desde Fórmulas > Nombres definidos > Administrador de nombres > Nuevo

Clique Aceptar, seleccione cualquier fila con tres celdas vacías y escriba =Trimestre1.

Aparece el siguiente resultado:

Use una constante de matriz con nombre en una fórmula, como =Trimestre1, donde Trimestre1 se ha definido como ={"enero","febrero","marzo"}

Si desea que los resultados se muestren de forma vertical y no horizontal, use =TRANSPONER(Trimestre1).

Si desea mostrar una lista de 12 meses, como podría usar al crear un estado financiero, puede basar uno en el año actual con la función SECUENCIA. Lo bueno de esta función es que, aunque solo se muestra el mes, hay una fecha válida detrás que puede usar en otros cálculos. Encontrará estos ejemplos en las hojas de trabajo Constante de matriz con nombre y Conjunto de datos de muestra rápida del libro de trabajo de ejemplo.

=TEXTO(FECHA(AÑO(HOY()),SECUENCIA(1,12),1),"mmm")

Use una combinación de las funciones TEXTO, FECHA, AÑO, HOY y SECUENCIA para crear una lista dinámica de 12 meses

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. ). Si desea mostrar el nombre del mes completo, como enero, use "mmmm".

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. Por último, tenga en cuenta que puede usar combinaciones de funciones, texto y números. Todo depende de lo creativo que quiera ser.

Los ejemplos siguientes muestran algunas formas de usar constantes de matriz en fórmulas de matriz. Algunos ejemplos usan la función TRANSPONER para convertir filas en columnas y viceversa.

Multiplicar cada elemento de una matriz

Escriba =SECUENCIA(1,12)*2 o ={1\2\3\4;5\6\7\8;9\10\11\12}*2

También puede dividir con (/), sumar con (+) y restar con (-).
Elevar al cuadrado los elementos de una matriz

Escriba =SECUENCIA(1,12)^2 o ={1\2\3\4;5\6\7\8;9\10\11\12}^2
Encuentre la raíz cuadrada de los elementos al cuadrado en una matriz

Introducir =SQRT(SECUENCIA(1,12)^2) o =SQRT({1\2\3\4;5\6\7\8;9\10\11\12}^2)
Transponer una fila unidimensional

Escribir =TRANSPONER(SECUENCIA(1,5)) o =TRANSPONER({1\2\3\4\5})

Aunque haya escrito una constante de matriz horizontal, la función TRANSPONER la convierte en una columna.
Transponer una columna unidimensional

Escribir =TRANSPONER(SECUENCIA(5,1)) o =TRANSPONER({1;2;3;4;5})

Aunque haya escrito una constante de matriz vertical, la función TRANSPONER la convierte en una fila.
Transponer una constante bidimensional

Escribir =TRANSPONER(SECUENCIA(3,4)) o =TRANSPONER({1\2\3\4;5\6\7\8;9\10\11\12})

La función TRANSPONER convierte cada fila en una serie de columnas.

Esta sección proporciona ejemplos de fórmulas de matriz básicas.

Crear una matriz a partir de valores existentes

El siguiente ejemplo explica cómo usar fórmulas de matriz para crear una nueva matriz a partir de una matriz existente.

Escriba =SECUENCIA(3,6,10,10) o ={10\20\30\40\50\60;70\80\90\100\110\120;130\140\150\160\170\180}

Asegúrese de escribir { (llave de apertura) antes de escribir 10 y } (llave de cierre) después de escribir 180, porque está creando una matriz de números.

A continuación, escriba =D9# o =D9:I11 en una celda vacía. Una matriz de celdas de 3 x 6 aparece con los mismos valores que ve en D9:D11. El signo # se llama operador de rango desplazado y es la forma en que Excel hace referencia a todo el rango de la matriz, en lugar de tener que escribirlo.
Crear una constante de matriz a partir de valores existentes

Puede tomar los resultados de una fórmula de matriz desplazada y convertirlos en sus componentes. Seleccione la celda D9 y, a continuación, presione F2 para cambiar al modo de edición. A continuación, presione F9 para convertir las referencias de celda en valores, que Excel luego convierte en una constante de matriz. Al presionar Entrar, la fórmula, =D9#, debería ser ahora ={10\20\30;40\50\60;70\80\90}.
Contar los caracteres de un rango de celdas

En el ejemplo siguiente se muestra cómo contar el número de caracteres de un rango de celdas. Esto incluye los espacios.

=SUMA(LARGO(C9:C13))

En este caso, 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 y muestra el resultado (66). Si desea obtener un número promedio de caracteres, puede usar:

=PROMEDIO(LARGO(C9:C13))
Contenido de la celda más larga en el rango C9:C13

=INDICE(C9:C13,COINCIDIR(MAX(LARGO(C9:C13)),LARGO(C9:C13),0),1)

Esta fórmula solo funciona cuando un rango de datos contiene una sola columna de celdas.

Examinemos la fórmula desde los elementos interiores hacia fuera. La función LARGO devuelve la longitud de cada uno de los elementos del rango de celdas D2:D6. La función MAX calcula el valor más grande entre esos elementos, que corresponde a la cadena de texto más larga, que se encuentra en la celda D3.

En este punto es donde este tema se complica un poco. La función COINCIDIR calcula la colocación en capas (la posición relativa) de la celda que contiene la cadena de texto más larga. Para ello, necesita tres argumentos: valor de búsqueda, una matriz de búsqueda y un tipo de coincidencia. La función COINCIDIR busca el valor de búsqueda especificado en la matriz de búsqueda. En este caso, se trata de la cadena de texto más larga:

MAX(LARGO(C9:C13)

y esa cadena se encuentra en esta matriz:

LARGO(C9:C13)

El argumento del tipo de coincidencia en este caso es 0. El tipo de coincidencia puede constar de un valor 1, 0 o -1.
- 1: devuelve el valor más largo que sea menor o igual que el valor de búsqueda.
- 0: devuelve el primer valor exactamente igual que el valor de búsqueda.
- -1: devuelve el valor más pequeño que sea mayor o igual que el valor de búsqueda especificado.
- Si omite un tipo de coincidencia, Excel asume 1.
Por último, la función INDICE usa estos argumentos: una matriz y un número de fila y columna dentro de esa matriz. El rango de celdas C9:C13 proporciona la matriz, la función COINCIDIR proporciona la dirección de las celdas y el argumento final (1) especifica que el valor proviene de la primera columna de la matriz.

Si quiere obtener el contenido de la cadena de texto más pequeña, reemplace MAX del ejemplo anterior por MIN.
Buscar los n valores más pequeños de un rango

Este ejemplo muestra cómo encontrar los tres valores más pequeños en un rango de celdas, donde se ha creado una matriz de datos de muestra en las celdas B9:B18 con: =INT(RANDARRAY(10,1)*100). Tenga en cuenta que RANDARRAY es una función volátil, por lo que obtendrá un nuevo conjunto de números aleatorios cada vez que Excel calcule.

Escriba =K.ESIMO.MENOR(B9#,SECUENCIA(D9), =K.ESIMO.MENOR(B9:B18,{1;2;3})

Esta fórmula utiliza una constante de matriz para evaluar la función K.ESIMO.MENOR tres veces y devolver los 3 miembros más pequeños de la matriz que está contenida en las celdas B9:B18, donde 3 es un valor variable de la celda D9. Para encontrar más valores, puede aumentar el valor en la función SECUENCIA o agregar más argumentos a la constante. También puede usar funciones adicionales con esta fórmula, por ejemplo SUMA o PROMEDIO. Por ejemplo:

=SUMA(K.ESIMO.MENOR(B9#,SECUENCIA(D9))

=PROMEDIO(K.ESIMO.MENOR(B9#,SECUENCIA(D9))
Buscar los n valores mayores de un rango

Para buscar los valores mayores de un rango, puede reemplazar la función K.ESIMO.MENOR por la función K.ESIMO.MAYOR. Además, el ejemplo siguiente usa las funciones FILA e INDIRECTO.

Escriba =K.ESIMO.MAYOR(B9#,FILA(INDIRECTO("1:3"))) o =K.ESIMO.MAYOR(B9:B18,FILA(INDIRECTO("1:3")))

Llegados a este punto, es posible que desee más información sobre las funciones FILA e INDIRECTO. Puede usar la función FILA para crear una matriz de enteros consecutivos. Por ejemplo, selecciona una vacía y escriba:

=FILA(1:10)

La fórmula crea una columna de 10 enteros consecutivos. Para ver un problema potencial, inserte una fila sobre el rango que contiene la fórmula de matriz (es decir, sobre la fila 1). Excel ajusta las referencias de fila y la fórmula genera ahora los enteros de 2 a 11. Para solucionar el problema, agregue la función INDIRECTO a la fórmula como sigue:

=FILA(INDIRECTO("1:10"))

La función INDIRECTO usa cadenas de texto como argumentos (y, por ello, el rango 1:10 está incluido entre comillas). Excel no ajusta los valores de texto cuando se insertan filas o se mueve la fórmula de matriz. El resultado es que la función FILA siempre generará la matriz de enteros que desee. Podría usar SECUENCIA con la misma facilidad:

=SECUENCIA(10)

Examinemos la fórmula que usó anteriormente: =K.ESIMO.MAYOR(B9#,FILA(INDIRECTO("1:3"))). Comenzando desde el paréntesis interno y trabajando hacia afuera, la función INDIRECTO devuelve un conjunto de valores de texto, siendo, en este caso, los valores del 1 al 3. La función FILA, a su vez, genera una matriz de columnas de tres celdas. La función K.ESIMO.MAYOR usa los valores en el rango de celdas B9:B18 y se evalúa tres veces, una por cada referencia devuelta por la función FILA. Si desea encontrar más valores, agregue un mayor rango de celdas a la función INDIRECTO. Por último, como en el ejemplo de K.ESIMO.MENOR, puede usar esta fórmula con otras funciones, como SUMA y PROMEDIO.

Sumar un rango que contiene valores de error

La función SUMA de Excel no funcionará si intenta sumar un rango que contenga un valor de error, como #¡VALOR! o #N/A. Este ejemplo muestra cómo sumar los valores de un rango con el nombre Datos que contiene errores:
=SUMA(SI(ESERROR(Datos),"",Datos))

La fórmula crea una nueva matriz que contiene los valores originales menos los valores de error. A partir de las funciones interiores y hacia fuera, la función ESERROR busca errores en el rango de celdas (Datos). La función SI devuelve un valor concreto si una condición especificada se evalúa como VERDADERO y otro valor si se evalúa como FALSO. En este caso, devuelve cadenas vacías ("") para todos los valores de error, evaluados como VERDADERO, y devuelve los valores restantes del rango (Datos) evaluados como FALSO, lo que significa que no contienen valores de error. Seguidamente, la función SUMA calcula el total de la matriz filtrada.
Contar el número de valores de error de un rango

Este ejemplo es similar a la fórmula anterior pero, en lugar de filtrarlos, devuelve el número de valores de error de un rango con el nombre Datos:

=SUMA(SI(ESERROR(Datos),1,0))

Esta fórmula crea una matriz que contiene el valor 1 para las celdas que contienen errores y el valor 0 para las que no los contienen errores. Puede simplificar la fórmula y conseguir el mismo resultado si quita el tercer argumento de la función SI, como sigue:

=SUMA(SI(ESERROR(Datos),1))

Si no especifica el argumento, la función SI devuelve FALSO cuando una celda no contiene ningún valor de error. Puede simplificarla aún más:

=SUMA(SI(ESERROR(Datos)*1))

Esta versión funciona porque VERDADERO*1=1 y FALSO*1=0.

Tal vez necesite sumar valores basados en condiciones.

Puede usar matrices para realizar cálculos en base a condiciones específicas. =SUMA(SI(Ventas>0,Ventas)) sumará todos los valores mayores a 0 en un rango llamado Ventas.

Por ejemplo, esta fórmula de matriz suma únicamente los enteros positivos de un rango con el nombre de Ventas, que representa a las celdas E9:E24 del ejemplo anterior:

=SUMA(SI(Ventas>0;Ventas))

La función SI crea una matriz de valores positivos y falsos. La función SUMA básicamente ignora los valores falsos, dado que 0+0=0. El rango de celdas que usa esta fórmula puede estar compuesto por cualquier número de filas y columnas.

También puede sumar valores que cumplan varias condiciones. Por ejemplo, esta fórmula de matriz calcula valores mayores que 0 Y menores que 2500:

=SUMA((Ventas>0)*(Ventas<2500)*(Ventas))

Tenga en cuenta que esta fórmula devuelve un error si el rango contiene una o varias celdas no numéricas.

También puede crear fórmulas de matriz que usan un tipo de condición O. Por ejemplo, puede sumar valores que sean mayores que 0 O menores que 2500:

=SUMA(SI((Ventas>0)+(Ventas<2500),Ventas))

No puede usar las funciones Y y O directamente en las fórmulas de matriz, ya que esas funciones devuelven un único valor, ya sea VERDADERO o FALSO, y las funciones de matriz necesitan matrices de resultados. Puede solucionar este problema si usa la lógica de la fórmula anterior. En otras palabras, puede realizar operaciones de coincidencia, como suma o multiplicación, en valores que cumplan la condición O o Y.

Este ejemplo muestra cómo quitar los ceros de un rango cuando necesite calcular la media de los valores que contiene. La fórmula usa un rango de datos con el nombre de Ventas:

=PROMEDIO(SI(Ventas<>0;Ventas))

La función SI crea una matriz de valores que no son iguales que 0 y los pasa a la función PROMEDIO.

Esta fórmula de matriz compara los valores de dos rangos de celdas denominados MisDatos y TusDatos y devuelve el número de diferencias entre ellos. Si el contenido de los dos rangos es idéntico, la fórmula devuelve 0. Para usar esta fórmula, los rangos de celdas deben ser del mismo tamaño y de la misma dimensión. Por ejemplo, si MisDatos es un rango de 3 filas por 5 columnas, TusDatos debe tener las mismas dimensiones:

=SUMA(SI(MisDatos=TusDatos,0,1))

La fórmula crea una nueva matriz del mismo tamaño que los rangos que está comparando. La función SI rellena la matriz con el valor 0 y el valor 1 (0 para no coincidencias y 1 para celdas idénticas). Entonces, la función SUMA devuelve la suma de los valores de la matriz.

Puede simplificar la fórmula como sigue:

=SUMA(1*(MisDatos<>TusDatos))

Al igual que la fórmula que cuenta los valores de error de un rango, esta fórmula funciona porque VERDADERO*1=1 y FALSO*1=0.

Esta fórmula de matriz devuelve el número de fila del valor máximo de un rango de una columna con el nombre de Datos:

=MIN(SI(Datos=MAX(Datos),FILA(Datos),""))

La función SI crea una nueva matriz que corresponde al rango denominado Datos. Si una celda correspondiente contiene el valor máximo del rango, la matriz contiene el número de fila. De lo contrario, contiene una cadena vacía (""). La función MIN usa la nueva matriz como segundo argumento y devuelve el valor más pequeño, que corresponde al número de fila del valor máximo de Datos. Si el rango con el nombre de Datos contiene valores máximos idénticos, la fórmula devuelve la fila del primer valor.

Para devolver la dirección de celda real de un valor máximo, use esta fórmula:

=DIRECCION(MIN(SI(Datos=MAX(Datos),FILA(Datos),"")),COLUMNA(Datos))

Encontrará ejemplos similares en el libro de muestra de la hoja de cálculoDiferencias entre conjuntos de datos.

Fórmula de matriz de varias celdas

Copie la tabla de abajo completa y péguela en la celda A1 de una hoja de cálculo vacía.

Empleado comercial	Tipo de vehículo	Número vendido	Precio unitario
Bernabé	Sedán	5	33000
	Cupé	4	37000
Inda	Sedán	6	24000
	Cupé	8	21000
Robledo	Sedán	3	29000
	Cupé	1	31000
Parra	Sedán	9	24000
	Cupé	5	37000
Sánchez	Sedán	6	33000
	Cupé	8	31000
Fórmula (Total general)			Total general
'=SUMA(C2:C11*D2:D11)			=SUMA(C2:C11*D2:D11)

Para ver las Ventas totales de vehículos de tipo cupé y sedán de cada vendedor, seleccione las celdas E2:E11, escriba la fórmula =C2:C11*D2:D11 y presione Ctrl+Mayús+Entrar.
Para ver el Total general de todas las ventas, seleccione la celda F11, escriba la fórmula =SUMA(C2:C11*D2:D11) y presione Ctrl+Mayús+Entrar.

Al presionar Ctrl+Mayús+Entrar, Excel rodea la fórmula con llaves ({ }) e inserta un ejemplo de la fórmula en cada celda del rango seleccionado. Esto pasa muy deprisa, por consiguiente, lo que ve en la columna E corresponde al importe total de ventas por tipo de vehículo y vendedor. Si selecciona primero E2, después E3, E4, etc., verá que aparece la misma fórmula: {=C2:C11*D2:D11}.

Una fórmula de matriz calcula los totales de la columna E

Crear una fórmula de matriz de una sola celda

En la celda D13 del libro, escriba la siguiente fórmula y presione Ctrl+Mayús+Entrar:

=SUMA(C2:C11*D2:D11)

En este caso, Excel multiplica los valores de la matriz (el rango de celdas de C2 a D11) y usa la función SUMA para sumar los totales. El resultado es un total general de 1.590.000 $ en ventas. Este ejemplo demuestra lo eficaz que puede resultar este tipo de fórmula. Por ejemplo, imagine que 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 arrastrar a las 1.000 filas.

Además, observe que la fórmula de una celda (en la celda D13) es totalmente independiente de la fórmula de varias celdas (la fórmula de las celdas de E2 a E11). Esto pone de manifiesto otra ventaja de las fórmulas de matriz: la flexibilidad. Puede cambiar las fórmulas de la columna E o eliminar toda la columna sin afectar a la fórmula de la celda D13.

Las fórmulas de matriz también ofrecen estas ventajas:

Coherencia Si hace clic en cualquiera de las celdas de la columna E2 hacia abajo, verá la misma fórmula. Esa coherencia garantiza una mayor precisión.
Seguridad No es posible sobrescribir un componente de una fórmula de matriz de varias celdas. Por ejemplo, haga clic en la celda E3 y presione Supr. Tendrá que seleccionar todo el rango de celdas (de E2 a E11) y modificar la fórmula de la matriz completa o dejar la matriz como está. Como medida de seguridad adicional, debe presionar Ctrl+Mayús+Entrar para confirmar cualquier cambio en la fórmula.
Tamaños de archivo más pequeños A menudo puede usar una fórmula de matriz sencilla en lugar de varias fórmulas intermedias. Por ejemplo, el libro que ha creado para este ejercicio emplea una fórmula de matriz para calcular los resultados de la columna E. Si hubiera usado fórmulas estándar (como =C2*D2, C3*D3, C4*D4…), habría necesitado 11 fórmulas distintas para calcular los mismos resultados.

En general, las fórmulas de matriz usan sintaxis de fórmula estándar. Todas comienzan con un signo igual (=) y en ellas se pueden usar todas las funciones integradas de Excel. La principal diferencia es que, al usar una fórmula de matriz, es necesario presionar Ctrl+Mayús+Entrar para especificarla. Al hacer esto, Excel delimita la fórmula de matriz con llaves ; si escribe las llaves manualmente, la fórmula se convertirá en una cadena de texto y no funcionará.

Las funciones de matriz pueden ser una forma eficiente de compilar fórmulas complejas. La fórmula de matriz =SUMA(C2:C11*D2:D11) es igual a =SUMA(C2*D2,C3*D3,C4*D4,C5*D5,C6*D6,C7*D7,C8*D8,C9*D9,C10*D10,C11*D11).

Importante: Presione Ctrl+Mayús+Entrar cuando quiera escribir una fórmula de matriz. Esto se aplica a las fórmulas de una y de varias celdas.

Siempre que trabaje con fórmulas de varias celdas, también deberá seguir estas reglas:

Seleccione el rango de celdas donde va a incluir los resultados antes de especificar la fórmula. Lo hizo anteriormente al seleccionar las celdas de E2 a E11 para crear la fórmula de matriz de varias celdas.
No puede modificar el contenido de una celda individual de una fórmula de matriz. Para intentarlo, seleccione la celda E3 del libro y presione Supr. Excel muestra un mensaje que indica que no puede cambiar parte de una matriz.
Puede mover o eliminar una fórmula de matriz completa, pero no solo parte. En otras palabras, para reducir una fórmula de matriz, primero debe eliminar la fórmula existente y comenzar de nuevo.
Para eliminar una fórmula de matriz, seleccione todo el rango de fórmulas (por ejemplo, E2:E11) y, luego, presione Eliminar.
No puede insertar celdas en blanco en una fórmula de matriz de varias celdas ni eliminar celdas de la misma.

A veces puede necesitar ampliar una fórmula de matriz. Seleccione la primera celda en el rango de matriz existente y continúe hasta que haya seleccionado todo el rango al que desea extender la fórmula. Presione F2 para editar la fórmula, luego presione CTRL+MAYÚS+ENTRAR para confirmar la fórmula una vez que haya ajustado su rango. La clave es seleccionar todo el rango, comenzando por la celda superior izquierda de la matriz. La celda superior izquierda es la que se edita.

Las fórmulas de matriz pueden parecer mágicas, pero también tienen algunas desventajas:

Ocasionalmente puede olvidarse de presionar Ctrl+Mayús+Entrar. Puede sucederle incluso a los usuarios de Excel más experimentados. Recuerde presionar esta combinación de teclas siempre que escriba o modifique una fórmula de matriz.
Es posible que otros usuarios de su libro no comprendan las fórmulas. En la práctica, las fórmulas de matriz generalmente no se explican en una hoja de cálculo. Por lo tanto, si es necesario que otras personas modifiquen los libros, debería evitar las fórmulas de matriz o asegurarse de que esas personas las conozcan y sepan cómo modificarlas si lo necesitan.
Según la velocidad de procesamiento y la memoria del equipo, las fórmulas de matriz de gran tamaño pueden ralentizar los cálculos.

Las constantes de matriz son un componente de las fórmulas de matriz. Para crear constantes de matriz, puede especificar una lista de elementos y delimitarla manualmente entre llaves ({ }), como:

={1\2\3\4\5}

Ahora ya sabe que tiene que presionar Ctrl+Mayús+Entrar al crear fórmulas de matriz. Dado que las constantes son uno de los componentes de estas fórmulas, enciérrelas entre llaves manualmente. Presione luego Ctrl+Mayús+Entrar para especificar la fórmula completa.

Este es un ejemplo de una matriz de una sola fila: {1\2\3\4}. Esta es una matriz de una sola columna: {1;2;3;4}. Y esta es una matriz de dos filas y cuatro columnas: {1\2\3\4;5\6\7\8}. En la matriz de dos filas, la primera incluye 1, 2, 3 y 4 y la segunda 5, 6, 7 y 8. Un punto y coma, entre 4 y 5, separa las dos filas.

Al igual que ocurre con las fórmulas de matriz, puede usar las constantes con todas las funciones incorporadas en Excel. Las siguientes secciones explican cómo crear cada tipo de constante y cómo usarlas con las funciones de Excel.

Con el siguiente procedimiento podrá adquirir cierta práctica en la creación de constantes horizontales, verticales y bidimensionales.

Crear una constante horizontal

En la hoja de cálculo, seleccione las celdas de la A1 a la E1.
Escriba la fórmula siguiente en la barra de fórmulas y presione Ctrl+Mayús+Entrar:

={1\2\3\4\5}

En este caso, debería escribir las llaves de apertura y cierre ({ }), y Excel agregará el segundo conjunto por usted.

Aparece el siguiente resultado.

Crear una constante vertical

En el libro, seleccione una columna de cinco celdas.
Escriba la fórmula siguiente en la barra de fórmulas y presione Ctrl+Mayús+Entrar:

={1;2;3;4;5}

Aparece el siguiente resultado.

Crear una constante bidimensional

En el libro, seleccione un bloque de celdas de cuatro columnas de ancho por tres filas de alto.
Escriba la fórmula siguiente en la barra de fórmulas y presione Ctrl+Mayús+Entrar:

={1\2\3\4;5\6\7\8;9\10\11\12}

Verá el resultado siguiente:

Usar constantes en fórmulas

Vea este sencillo ejemplo que pone en práctica todo lo explicado hasta ahora:

En el libro de muestra, cree otra hoja de cálculo.
Escriba 3 en la celda A1 y luego 4 en B1, 5 en C1, 6 en D1 y 7 en E1.
En la celda A3 escriba la siguiente fórmula y presione Ctrl+Mayús+Entrar:

=SUMA(A1:E1*{1\2\3\4\5})

Observe que Excel también delimita la constante entre llaves. Se debe a que la ha especificado como una fórmula de matriz.

En la celda A3 aparece el valor 85.

En la sección siguiente se explica cómo funciona la fórmula.

La fórmula que acaba de usar contiene varias partes.

Sintaxis de fórmula de matriz con constante de matriz

1. Función

2. Matriz almacenada

3. Operador

4. Constante de matriz

El último elemento incluido en los paréntesis es la constante de matriz: {1\2\3\4\5}. Recuerde que Excel no incluye las constantes de matriz entre llaves, es usted quien debe hacerlo. Además, recuerde que después de agregar una constante a una fórmula de matriz, debe presionar Ctrl+Mayús+Entrar para escribir la fórmula.

Dado que Excel realiza en primer lugar las operaciones de las expresiones incluidas entre paréntesis, los dos siguientes elementos que entran en funcionamiento son los valores almacenados en el libro (A1:E1) y el operador. En este punto, la fórmula multiplica los valores de la matriz almacenada por los valores correspondientes de la constante. Es el equivalente de:

=SUMA(A1*1,B1*2,C1*3,D1*4,E1*5)

Por último, la función SUMA agrega los valores y en la celda A3 aparece la suma 85:

Para evitar el uso de la matriz almacenada y simplemente conservar la operación en su totalidad en memoria, sustituya la matriz almacenada por otra constante de matriz:

=SUMA({3\4\5\6\7}*{1\2\3\4\5})

Para probarla, copie la función, seleccione una celda vacía del libro, pegue la fórmula en la barra de fórmulas y presione Ctrl+Mayús+Entrar. Verá el mismo resultado que en el ejercicio anterior, cuando empleó la fórmula de matriz:

=SUMA(A1:E1*{1\2\3\4\5})

Las constantes de matriz 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. Si incluye texto, debe especificarlo entre comillas (").

Las constantes de matriz no pueden contener matrices, fórmulas ni funciones adicionales. En otras palabras, 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 muestra un mensaje de advertencia. Además, los valores numéricos no pueden incluir signos de porcentaje, dólar, comas o paréntesis.

En la pestaña Fórmulas vaya al grupo Nombres definidos y haga clic en Definir nombre.
Aparecerá el cuadro de diálogo Definir nombre.
En el cuadro Nombre, escriba Trimestre1.
En el cuadro Se refiere a, escriba la siguiente constante (recuerde escribir las llaves de forma manual):

={"Enero"\"Febrero"\"Marzo"}

El contenido del cuadro de diálogo tiene ahora el siguiente aspecto:
Haga clic en Aceptar y seleccione una fila de tres celdas en blanco.
Escriba la fórmula siguiente y presione Ctrl+Mayús+Entrar.

=Trimestre1

Aparece el siguiente resultado.

Cuando emplee una constante con nombre como fórmula de matriz, recuerde escribir el signo igual. Si no lo hace, Excel interpretará la matriz como una cadena de texto. Por último, tenga en cuenta que puede usar combinaciones de texto y números.

Si las constantes de matriz no funcionan, puede que se deba a uno de los siguientes problemas:

Es posible que no haya separado algunos elementos con el carácter adecuado. Si omite una coma o un punto y coma, o coloca uno de estos caracteres en una ubicación incorrecta, probablemente no podrá crear la constante de matriz correctamente o aparecerá un mensaje de advertencia.
Es posible que haya seleccionado un rango de celdas que no coincida con el número de elementos de la constante. Por ejemplo, si selecciona una columna de seis celdas para usarla con una constante de cinco celdas, aparecerá el valor de error #N/A en la celda vacía. Por el contrario, si no selecciona las celdas suficientes, Excel omite los valores que no se corresponden con ninguna celda.

Los ejemplos siguientes muestran algunas formas de usar constantes de matriz en fórmulas de matriz. Algunos ejemplos usan la función TRANSPONER para convertir filas en columnas y viceversa.

Multiplicar cada elemento de una matriz

Cree otra hoja de cálculo y seleccione un bloque de celdas vacías de cuatro columnas de ancho por tres filas de alto.
Escriba la fórmula siguiente y presione Ctrl+Mayús+Entrar:

={1\2\3\4;5\6\7\8;9\10\11\12}*2

Elevar al cuadrado los elementos de una matriz

Seleccione un bloque de celdas vacías de cuatro columnas de ancho por tres filas de alto.
Escriba la fórmula de matriz siguiente y presione Ctrl+Mayús+Entrar:

={1\2\3\4;5\6\7\8;9\10\11\12}*{1\2\3\4;5\6\7\8;9\10\11\12}

También puede escribir esta fórmula de matriz, donde se usa el operador de intercalación (^):

={1\2\3\4;5\6\7\8;9\10\11\12}^2

Transponer una fila unidimensional

Seleccione una columna de cinco celdas en blanco.
Escriba la fórmula siguiente y presione Ctrl+Mayús+Entrar:

=TRANSPONER({1\2\3\4\5})

Aunque haya escrito una constante de matriz horizontal, la función TRANSPONER la convierte en una columna.

Transponer una columna unidimensional

Seleccione una fila de cinco celdas en blanco.
Escriba la fórmula siguiente y presione Ctrl+Mayús+Entrar:

=TRANSPONER({1;2;3;4;5})

Aunque haya escrito una constante de matriz vertical, la función TRANSPONER la convierte en una fila.

Transponer una constante bidimensional

Seleccione un bloque de celdas de tres columnas de ancho por cuatro filas de alto.
Escriba la constante siguiente y presione Ctrl+Mayús+Entrar:

=TRANSPONER({1\2\3\4;5\6\7\8;9\10\11\12})

La función TRANSPONER convierte cada fila en una serie de columnas.

Esta sección proporciona ejemplos de fórmulas de matriz básicas.

Crear matrices y constantes de matriz a partir de valores existentes

El siguiente ejemplo explica cómo usar fórmulas de matriz para crear vínculos entre rangos de celdas de distintas hojas de cálculo. También muestra cómo crear una constante de matriz a partir del mismo conjunto de valores.

Crear una matriz a partir de valores existentes

En una hoja de cálculo de Excel, seleccione las celdas C8:E10 y escriba esta fórmula:

={10\20\30;40\50\60;70\80\90}

Asegúrese de escribir { (llave de apertura) antes de escribir 10 y } (llave de cierre) después de escribir 90, porque está creando una matriz de números.

Presione Ctrl+Mayús+Entrar para escribir esta matriz de números en el rango de celdas C8:E10 con una fórmula de matriz. En la hoja de cálculo, el aspecto de C8 a E10 debería ser el siguiente:

10	20	30
40	50	60
70	80	90

Seleccione el rango de celdas de C1 a E3.
Escriba la fórmula siguiente en la barra de fórmulas y presione Ctrl+Mayús+Entrar:

=C8:E10

En las celdas C1 a E3 aparecerá una matriz de 3x3 celdas con los mismos valores que ve en las celdas C8 a E10.

Crear una constante de matriz a partir de valores existentes

Con las celdas C1:C3 seleccionadas, presione F2 para cambiar al modo Edición.
Presione F9 para convertir las referencias de celda en valores. Excel convierte los valores en una constante de matriz. La fórmula debería ser ahora ={10\20\30;40\50\60;70\80\90}.
Presione Ctrl+Mayús+Entrar para escribir la constante de matriz como una fórmula de matriz.

Contar los caracteres de un rango de celdas

En el ejemplo siguiente se muestra cómo contar el número de caracteres, incluidos los espacios, de un rango de celdas.

Copie esta tabla y péguela en una hoja de cálculo en la celda A1.

Datos
Este es un
conjunto de celdas que
están agrupadas
para formar una
sola oración.

Caracteres totales en A2:A6
=SUMA(LARGO(A2:A6))

Contenido de la celda más larga (A3)
=INDICE(A2:A6,COINCIDIR(MAX(LARGO(A2:A6)),LARGO(A2:A6),0),1)

Seleccione la celda A8 y presione Ctrl+Mayús+Entrar para ver el número total de caracteres en las celdas A2:A6 (66).
Seleccione la celda A10 y presione Ctrl+Mayús+Entrar para ver el contenido de la más larga de las celdas A2:A6 (celda A3).

La siguiente fórmula, que se usa en la celda A8, cuenta el número total de caracteres (66) en las celdas de A2 a A6.

=SUMA(LARGO(A2:A6))

En este caso, la función LARGO devuelve la longitud de todas las cadenas de texto contenidas en las celdas del rango. A continuación, la función SUMA suma esos valores y muestra el resultado (66).

Buscar los n valores más pequeños de un rango

Este ejemplo muestra cómo buscar los tres valores más pequeños de un rango de celdas.

Ingrese algunos números aleatorios en las celdas A1:A11.
Seleccione las celdas de la C1 a la C3. Este conjunto de celdas contendrá los resultados que devuelva la fórmula de matriz.
Escriba la fórmula siguiente y presione Ctrl+Mayús+Entrar:

=K.ESIMO.MENOR(A1:A11,{1;2;3})

Esta fórmula usa una constante de matriz para evaluar la función K.ESIMO.MENOR tres veces y devolver el integrante más pequeño (1), el segundo más pequeño (2) y el tercero más pequeño (3) de la matriz incluida en las celdas A1:A10. Para buscar más valores, agregue más argumentos a la constante. También puede usar funciones adicionales con esta fórmula, por ejemplo SUMA o PROMEDIO. Por ejemplo:

=SUMA(K.ESIMO.MENOR(A1:A10,{1\2\3})

=PROMEDIO(K.ESIMO.MENOR(A1:A10,{1\2\3})

Buscar los n valores mayores de un rango

Para buscar los valores mayores de un rango, puede reemplazar la función K.ESIMO.MENOR por la función K.ESIMO.MAYOR. Además, el ejemplo siguiente usa las funciones FILA e INDIRECTO.

Seleccione las celdas de la D1 a la D3.
Escriba la fórmula siguiente en la barra de fórmulas y presione Ctrl+Mayús+Entrar:

=K.ESIMO.MAYOR(A1:A10;FILA(INDIRECTO("1:3")))

Llegados a este punto es posible que desee más información sobre las funciones FILA e INDIRECTO. Puede usar la función FILA para crear una matriz de enteros consecutivos. Por ejemplo, seleccione una columna vacía de 10 celdas en el libro de práctica, ingrese esta fórmula de matriz y, luego, presione Ctrl+Mayús+Entrar:

=FILA(1:10)

La fórmula crea una columna de 10 enteros consecutivos. Para ver un problema potencial, inserte una fila sobre el rango que contiene la fórmula de matriz (es decir, sobre la fila 1). Excel ajusta las referencias de fila y la fórmula genera los enteros de 2 a 11. Para solucionar el problema, agregue la función INDIRECTO a la fórmula como sigue:

=FILA(INDIRECTO("1:10"))

La función INDIRECTO usa cadenas de texto como argumentos (y por ello el rango 1:10 está incluido entre comillas). Excel no ajusta los valores de texto cuando se insertan filas o se mueve la fórmula de matriz. El resultado es que la función FILA siempre generará la matriz de enteros que desee.

Veamos la fórmula que usó anteriormente: =K.ESIMO.MAYOR(A5:A14,FILA(INDIRECTO("1:3"))). Comenzando desde el paréntesis interno y trabajando hacia afuera, la función INDIRECTO devuelve un conjunto de valores de texto, siendo, en este caso, los valores del 1 al 3. La función FILA, a su vez, genera una matriz de columnas de tres celdas. La función K.ESIMO.MAYOR usa los valores en el rango de celdas A5:A14 y se evalúa tres veces, una por cada referencia devuelta por la función FILA. Los valores 3200, 2700 y 2000 se devuelven a la matriz de columnas de tres celdas. Si desea encontrar más valores, agregue un mayor rango de celdas a la función INDIRECTO.

Como en ejemplos anteriores, puede usar esta fórmula con otras funciones, como SUMA y PROMEDIO.

Buscar la cadena de texto más larga de un rango de celdas

Regrese al ejemplo de cadena de texto anterior, ingrese la siguiente fórmula en una celda vacía y presione Ctrl+Mayús+Entrar:

=INDICE(A2:A6,COINCIDIR(MAX(LARGO(A2:A6)),LARGO(A2:A6),0),1)

El texto "grupo de celdas que" aparece.

Examinemos la fórmula desde los elementos interiores hacia fuera. La función LARGO devuelve la longitud de cada uno de los elementos del rango de celdas A2:A6. La función MAX calcula el valor más grande de esos elementos, que corresponde a la cadena de texto más larga, que se encuentra en la celda A3.

En este punto es donde este tema se complica un poco. La función COINCIDIR calcula el desplazamiento (la posición relativa) de la celda que contiene la cadena de texto más larga. Para ello, necesita tres argumentos: valor de búsqueda, una matriz de búsqueda y un tipo de coincidencia. La función COINCIDIR busca el valor de búsqueda especificado en la matriz de búsqueda. En este caso, se trata de la cadena de texto más larga:

(MAX(LARGO(A2:A6))

y esa cadena se encuentra en esta matriz:

LARGO(A2:A6)

El argumento de tipo de coincidencia es 0. El tipo de coincidencia puede constar de un valor 1, 0 o -1. Si especifica 1, COINCIDIR devuelve el valor más largo que sea menor o igual que el valor de búsqueda. Si especifica 0, COINCIDIR devuelve el primer valor exactamente igual que el valor de búsqueda. Si especifica -1, COINCIDIR busca el valor más pequeño que sea mayor o igual que el valor de búsqueda especificado. Si omite un tipo de coincidencia, Excel asume 1.

Por último, la función INDICE usa estos argumentos: una matriz y un número de fila y columna dentro de esa matriz. El rango de celdas A2:A6 proporciona la matriz, la función COINCIDIR proporciona la dirección de las celdas y el argumento final (1) especifica que el valor proviene de la primera columna de la matriz.

Esta sección proporciona ejemplos de fórmulas de matriz avanzadas.

Sumar un rango que contiene valores de error

La función SUMA de Excel no funciona si intenta sumar un rango que contiene un valor de error, como #N/A. Este ejemplo muestra cómo sumar los valores de un rango con el nombre Datos que contiene errores.

=SUMA(SI(ESERROR(Datos),"",Datos))

La fórmula crea una nueva matriz que contiene los valores originales menos los valores de error. A partir de las funciones interiores y hacia fuera, la función ESERROR busca errores en el rango de celdas (Datos). La función SI devuelve un valor concreto si una condición especificada se evalúa como VERDADERO y otro valor si se evalúa como FALSO. En este caso, devuelve cadenas vacías ("") para todos los valores de error, evaluados como VERDADERO, y devuelve los valores restantes del rango (Datos) evaluados como FALSO, lo que significa que no contienen valores de error. Seguidamente, la función SUMA calcula el total de la matriz filtrada.

Contar el número de valores de error de un rango

Este ejemplo es similar a la fórmula anterior pero, en lugar de filtrarlos, devuelve el número de valores de error de un rango con el nombre Datos:

=SUMA(SI(ESERROR(Datos),1,0))

Esta fórmula crea una matriz que contiene el valor 1 para las celdas que contienen errores y el valor 0 para las que no los contienen errores. Puede simplificar la fórmula y conseguir el mismo resultado si quita el tercer argumento de la función SI, como sigue:

=SUMA(SI(ESERROR(Datos),1))

Si no especifica el argumento, la función SI devuelve FALSO cuando una celda no contiene ningún valor de error. Puede simplificarla aún más:

=SUMA(SI(ESERROR(Datos)*1))

Esta versión funciona porque VERDADERO*1=1 y FALSO*1=0.

Sumar valores basados en condiciones

Tal vez necesite sumar valores basados en condiciones. Por ejemplo, esta fórmula de matriz suma únicamente los enteros positivos de un rango con el nombre de Ventas:

=SUMA(SI(Ventas>0;Ventas))

La función SI crea una matriz de valores positivos y valores falsos. La función SUMA básicamente ignora los valores falsos, dado que 0+0=0. El rango de celdas que usa esta fórmula puede estar compuesto por cualquier número de filas y columnas.

También puede sumar valores que cumplan varias condiciones. Por ejemplo, esta fórmula de matriz calcula los valores mayores que 0 y menores o iguales que 5:

=SUMA((Ventas>0)*(Ventas<=5)*(Ventas))

Tenga en cuenta que esta fórmula devuelve un error si el rango contiene una o varias celdas no numéricas.

También puede crear fórmulas de matriz que usan un tipo de condición O. Por ejemplo, puede sumar valores que sean menores que 5 y mayores que 15:

=SUMA(SI((Ventas<5)+(Ventas>15);Ventas))

La función SI busca todos los valores menores que 5 y mayores que 15 y se los pasa a la función SUMA.

Calcular una media que excluya los ceros

Este ejemplo muestra cómo quitar los ceros de un rango cuando necesite calcular la media de los valores que contiene. La fórmula usa un rango de datos con el nombre de Ventas:

=PROMEDIO(SI(Ventas<>0;Ventas))

La función SI crea una matriz de valores que no son iguales que 0 y los pasa a la función PROMEDIO.

Contar el número de diferencias entre dos rangos de celdas

Esta fórmula de matriz compara los valores de dos rangos de celdas denominados MisDatos y TusDatos y devuelve el número de diferencias entre ellos. Si el contenido de los dos rangos es idéntico, la fórmula devuelve 0. Para usar esta fórmula, los rangos de celdas deben ser del mismo tamaño y de la misma dimensión (por ejemplo, si MisDatos es un rango de 3 filas por 5 columnas, TusDatos debe tener las mismas dimensiones):

=SUMA(SI(MisDatos=TusDatos,0,1))

Puede simplificar la fórmula como sigue:

=SUMA(1*(MisDatos<>TusDatos))

Al igual que la fórmula que cuenta los valores de error de un rango, esta fórmula funciona porque VERDADERO*1=1 y FALSO*1=0.

Buscar la ubicación del valor máximo de un rango

Esta fórmula de matriz devuelve el número de fila del valor máximo de un rango de una columna con el nombre de Datos:

=MIN(SI(Datos=MAX(Datos),FILA(Datos),""))

La función SI crea una nueva matriz que corresponde al rango denominado Datos. Si una celda correspondiente contiene el valor máximo del rango, la matriz contiene el número de fila. De lo contrario, contiene una cadena vacía (""). La función MIN usa la nueva matriz como segundo argumento y devuelve el valor más pequeño, que corresponde al número de fila del valor máximo de Datos. Si el rango con el nombre de Datos contiene valores máximos idénticos, la fórmula devuelve la fila del primer valor.

Para devolver la dirección de celda real de un valor máximo, use esta fórmula:

=DIRECCION(MIN(SI(Datos=MAX(Datos),FILA(Datos),"")),COLUMNA(Datos))

Reconocimientos

Partes del siguiente artículo se basan en una serie de columnas de usuario avanzado de Excel, escritas por Colin Wilcox y adaptadas de los capítulos 14 y 15 de Fórmulas de Excel 2002, un libro escrito por John Walkenbach, un MVP de Excel.