Directrices y ejemplos de fórmulas de matriz

Directrices y ejemplos de fórmulas de matriz

Nota: Nos gustaría proporcionarle el contenido de ayuda actual lo más rápido posible en su idioma. Esta página se ha traducido mediante un sistema automático y es posible que contenga imprecisiones o errores gramaticales. Nuestro objetivo es que este contenido sea útil para usted. ¿Puede informarnos sobre si la información le ha sido útil al final de la página? Aquí tiene el artículo en inglés para que pueda consultarlo fácilmente.

Una fórmula de matriz es una fórmula que puede realizar varios cálculos en uno o más elementos de una matriz. Una matriz se puede considerar como una fila o columna de valores, o como una combinación de filas y columnas de valores. Las fórmulas de matriz pueden devolver varios resultados o bien un solo resultado.

A partir de la actualización 2018 de septiembre para Office 365, cualquier fórmula que pueda devolver varios resultados las pondrá automáticamente por debajo o en las celdas vecinas. Este cambio de comportamiento también viene acompañado de varias funciones de matriz dinámicasnuevas. Las fórmulas de matriz dinámica, si están usando funciones existentes o las funciones de matriz dinámica, solo deben introducirse en una sola celda y, a continuación, confirmarse al presionar la tecla entrar. Antes, las fórmulas de matriz heredadas requieren que se seleccione primero todo el rango de salida y, a continuación, se confirma la fórmula con Ctrl + Mayús + entrar. Suelen conocerse como fórmulas de CSE .

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

  • Crear rápidamente conjuntos de comandos de ejemplo.

  • Contar el número de caracteres incluidos en un rango de celdas.

  • Sumar solo los 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, hemos incluido ejemplos con algunas de las funciones de matriz dinámica, así como fórmulas de matriz existentes escritas como matrices dinámicas y heredadas.

Descargar nuestros ejemplos

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

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

    Función de matriz de varias celdas en la celda H10 = F10: F19 * G10: G19 para calcular el número de coches vendidos por precio unitario

  • En este artículo, se calculan las ventas totales de las recuperaciones y sedán para cada comercial entrando = F19: F19 * G10: G19 en la celda H10.

    Cuando presione entrar, verá que los resultados se sobrepasan a las celdas H10: H19. Observe que el rango de derrame está resaltado con un borde al seleccionar cualquier celda del rango de derrame. También puede observar que las fórmulas de las celdas H10: H19 están atenuadas. Solo están allí para referencia, por lo que si desea ajustar la fórmula, tendrá que seleccionar la celda H10, donde vive la fórmula maestra.

  • Fórmula de matriz de una sola celda

    Fórmula de matriz de una sola celda para calcular un total general con = suma (F10: F19 * G10: G19)

    En la celda H2O del libro de ejemplo, escriba o copie y pegue = SUM (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, a continuación, usa la función suma para sumar los totales. El resultado es un total general de $1.590.000 en sales.

    Este ejemplo muestra lo eficaz que puede ser este tipo de fórmula. Por ejemplo, supongamos que tiene 1.000 filas de datos. Puede sumar parte de los datos o todos ellos creando una fórmula de matriz en una sola celda en lugar de arrastrar la fórmula hacia abajo en las filas de 1.000. Tenga en cuenta también que la fórmula de una sola celda en la celda H2O es totalmente independiente de la fórmula de varias celdas (la fórmula de las celdas H10 a H19). Esta es otra ventaja de usar fórmulas de matriz: flexibilidad. Puede cambiar las demás fórmulas de la columna H sin que ello afecte a la fórmula de H2O. También puede ser recomendable tener totales independientes como este, ya que ayuda 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 H10 hacia abajo, verá la misma fórmula. Esa coherencia puede ayudar a garantizar una mayor precisión.

    • Seguridad    No se puede sobrescribir un componente de una fórmula de matriz de varias celdas. Por ejemplo, haga clic en la celda H11 y presione suprimir. Excel no cambiará el resultado de la matriz. Para cambiarlo, tiene que 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 sola fórmula de matriz en lugar de varias fórmulas intermedias. Por ejemplo, el ejemplo de ventas de automóvil usa una fórmula de matriz para calcular los resultados de la columna E. Si ha usado fórmulas estándar como = F10 * G10, F11 * G11, F12 * G12, etc., habría usado 11 fórmulas diferentes para calcular los mismos resultados. Esto no es importante, pero ¿qué sucede si tuviera miles de filas para sumar? Entonces, puede hacer una gran diferencia.

    • Eficacia    Las funciones de matriz pueden ser una manera eficaz de crear fórmulas complejas. La fórmula de matriz = suma (F10: F19 * G10: G19) es la misma que esta: = SUM (F10 * G10, F11 * G11, F12 * G12, F13 * G13, F14 * G14, G15 * F16, G16 * F17, G17 * F18, G18 * F19, G19 *).

    • Derrame    Las fórmulas de matriz dinámica se reproducirán automáticamente en el rango de salida. Si los datos de origen están en una tabla de Excel, las fórmulas de matriz dinámica cambiarán automáticamente el tamaño al agregar o quitar datos.

    • #SPILL! error    Las matrices dinámicas introdujeron el error #SPILL!, que indica que el intervalo de derrame previsto está bloqueado por algún motivo. Al resolver el bloqueo, la fórmula se derramará 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 mediante comas, cree una matriz horizontal (una fila). Si separa los elementos con signos de punto y coma, crea una matriz vertical (una columna). Para crear una matriz bidimensional, puede delimitar los elementos de cada fila con comas y delimitar cada fila con punto y coma.

Los procedimientos siguientes le proporcionarán algunas prácticas para crear constantes horizontales, verticales y bidimensionales. Se mostrarán ejemplos mediante la función Sequence para generar automáticamente constantes de matriz, así como constantes de matriz especificadas manualmente.

  • Crear una constante horizontal

    Use el libro de los ejemplos anteriores o cree un libro nuevo. Seleccione una celda vacía y escriba = secuencia (1, 5). La función Sequence genera una fila de 1 fila por 5 columnas que es igual a = {1, 2, 3, 4, 5}. Se mostrará el siguiente resultado:

    Crea una constante de matriz horizontal con = SEQUENCE (1, 5) o = {1, 2, 3, 4, 5}

  • Crear una constante vertical

    Seleccione cualquier celda en blanco que tenga un salón por debajo de ella y escriba = secuencia (5)o = {1; 2; 3; 4; 5}. Se mostrará el siguiente resultado:

    Crea una constante de matriz vertical con = SEQUENCE (5) o = {1; 2; 3; 4; 5}

  • Crear una constante bidimensional

    Seleccione cualquier celda en blanco con espacio a la derecha y por debajo de ella, y escriba = secuencia (3, 4). Verá el siguiente resultado:

    Crea una constante de matriz de 3 filas por 4 columnas con = SEQUENCE (3, 4)

    También puede escribir: o = {1, 2, 3, 4; 5, 6, 7, 8; 9, 10, 12, 12}, pero deseará prestar atención al lugar en el que Inserte puntos y comas.

    Como puede ver, la opción SEQUENCE ofrece ventajas significativas con respecto a introducir manualmente los valores constantes de matriz. Principalmente, le ahorra tiempo, pero también puede ayudar a reducir los errores de las entradas manuales. También es más fácil de leer, sobre todo si los puntos y comas son difíciles de distinguir de los separadores de coma.

Este es un ejemplo que usa constantes de matriz como parte de una fórmula mayor. En el libro de ejemplo, vaya a la constante de una hoja de cálculo de fórmulas o cree una nueva hoja de cálculo.

En la celda D9, escribimos = Sequence (1, 5, 3, 1), pero también podría escribir 3, 4, 5, 6 y 7 en las celdas A9: H9. No hay nada especial sobre esa selección de números en particular, acabamos de elegir un valor distinto de 1-5 para diferenciarlo.

En la celda E11, escriba = suma (D9: secuencia H9 * Sequence (1, 5))o = SUM (D9: H9 * {1, 2, 3, 4, 5}). Las fórmulas devuelven 85.

Usar constantes matriciales en fórmulas. En este ejemplo, hemos usado = suma (D9: H (* SEQUENCE (1, 5))

La función SEQUENCE genera el equivalente de la constante de matriz {1, 2, 3, 4, 5}. Dado que Excel realiza operaciones en expresiones encerradas primero entre paréntesis, los dos siguientes elementos que entran en juego son los valores de celda de 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 equivalente a:

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

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

Para evitar usar la matriz almacenada y mantener la operación por completo en la memoria, puede reemplazarla por otra constante de matriz:

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

Elementos que se pueden usar en constantes de matriz

  • Las constantes matriciales pueden contener números, texto, valores lógicos (como verdadero y falso) y valores de error como #N/A. Puede usar números en los formatos entero, decimal y científico. Si incluye texto, tiene que encerrarlo 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.

Una de las mejores formas de usar constantes de matriz es denominarlas. Las constantes con nombre pueden ser mucho más fáciles de usar y pueden ocultar parte de la complejidad de las fórmulas de matriz de otras personas. Para asignar un nombre a una constante de matriz y usarla en una fórmula, haga lo siguiente:

Vaya a fórmulas > de nombres definidos > definir nombre. En el cuadro nombre, escriba Trimestre1. En el cuadro se refiere a, escriba la siguiente constante (Recuerde escribir las llaves manualmente):

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

El cuadro de diálogo debería tener ahora el siguiente aspecto:

Agregar una constante de matriz con nombre de fórmulas > nombres definidos > administrador de nombres > nueva

Haga clic en Aceptar y, a continuación, seleccione cualquier fila con tres celdas en blanco y escriba = Trimestre1.

Se mostrará 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 derramen verticalmente en lugar de horizontalmente, puede usar =transponer(Trimestre1).

Si desea mostrar una lista de 12 meses, como puede usar al crear un informe financiero, puede basar uno en el año actual con la función de secuencia. Lo bueno de esta función es que aunque solo se muestra el mes, hay una fecha válida detrás de ella que puede usar en otros cálculos. Encontrará estos ejemplos en la constante de matriz con nombre y en las hojas de cálculo de ejemplo rápidas en el libro de ejemplo.

= TEXTO (fecha (año (hoy ()), secuencia (1, 12), 1), "MMM")

Usar una combinación de las funciones texto, fecha, año, hoy y secuencia para crear una lista dinámica de 12 meses

Usa la función fecha para crear una fecha basada en el año actual, Sequence crea una constante de matriz del 1 al 12 para enero a diciembre y, a continuación, la función texto convierte el formato de visualización en "MMM" (Ene, Feb, mar, etc.). Si desea mostrar el nombre completo del mes, como enero, use "mmmm".

Cuando use una constante con nombre como una fórmula de matriz, Recuerde escribir el signo igual, como en = Trimestre1, no solo en Trimestre1. Si no lo hace, Excel interpreta la matriz como una cadena de texto y la fórmula no funcionará según lo esperado. Por último, tenga en cuenta que puede usar combinaciones de funciones, texto y números. Todo depende de la creatividad que desees obtener.

En los ejemplos siguientes se muestran algunas de las formas en las que puede poner constantes de matriz para usarlas en fórmulas de matriz. Algunos de los ejemplos usan la función transponer para convertir filas en columnas y viceversa.

  • Varios elementos de una matriz

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

    También puedes dividir con (/), agregar con (+) y restar con (-).

  • Elevar al cuadrado los elementos de una matriz

    Entrar = secuencia (1, 12) ^ 2, o = {1, 2, 3, 4, 5, 6, 7, 8; 9, 10, 11, 12} ^ 2

  • Buscar la raíz cuadrada de los elementos cuadrados en una matriz

    Entrar =sqrt(secuencia (1, 12) ^ 2), o = sqrt ({1, 2, 3, 4, 5, 6, 7, 8; 9, 10, 11, 12} ^ 2)

  • Transponer una fila unidimensional

    Entrar = 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

    Entrar = 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

    Entrar = 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

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

    Entrar = secuencia (3, 6, 10, 10), o = {10, 20, 30, 40, 50, 60; 70, 80, 90100110120; 130140150160170180}

    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 en blanco. Aparece una matriz de celdas de 3 x 6 con los mismos valores que aparecen en D9: D11. El signo # se denomina operador de rango derramadoy es Excel's forma de hacer referencia a todo el rango de matriz en lugar de tener que escribirlo.

    Usar el operador de rango derramado (#) para hacer referencia a una matriz existente

  • Crear una constante de matriz a partir de valores existentes

    Puede tomar los resultados de una fórmula de matriz derramada y convertirla en sus partes 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 a valores, que Excel convierte en una constante de matriz. Al presionar entrar, la fórmula, = D9 #, ahora debería ser = {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 espacios.

    Contar el número total de caracteres de un rango y otras matrices para trabajar con cadenas de texto

    = SUMA (LEN (C9: C13))

    En este caso, la función Len devuelve la longitud de cada cadena de texto de cada una de las celdas del rango. A continuación, la función suma suma los valores y muestra el resultado (66). Si deseara obtener el promedio de caracteres, podrías usar:

    = PROMEDIO (LEN (C9: C13))

  • Contenido de la celda más larga del intervalo de C9: C13

    = Índice (C9: C13, COINCIDIr (longitud máx. (de C9: C13)), LEN (C9: C13), 0), 1)

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

    Echemos un vistazo más detenidamente a la fórmula, empezando desde los elementos internos y trabajando hacia fuera. La función Len devuelve la longitud de cada uno de los elementos del rango de celdas D2: D6. La función Max calcula el valor mayor entre los elementos, que corresponde a la cadena de texto más larga, que está en la celda D3.

    Aquí es donde 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: un 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 el conjunto de búsqueda. En este caso, el valor de búsqueda es la cadena de texto más larga:

    MAX (LEN (C9: C13)

    Y esa cadena se encuentra en esta matriz:

    LEN (C9: C13)

    El argumento tipo de coincidencia en este caso es 0. El tipo de coincidencia puede ser un valor 1, 0 o-1.

    • 1-devuelve el valor mayor que es menor o igual que el Val de búsqueda.

    • 0-devuelve el primer valor exactamente igual al valor de búsqueda.

    • -1: devuelve el valor menor que es mayor o igual que el valor de búsqueda especificado.

    • Si omite un tipo de coincidencia, Excel supone 1.

    Por último, la función INDICE usa los siguientes 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 la celda y el último argumento (1) especifica que el valor proviene de la primera columna de la matriz.

    Si desea obtener el contenido de la cadena de texto más pequeña, reemplace Max en el ejemplo anterior por min.

  • 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, donde una matriz de datos de ejemplo de las celdas B9: B18has se ha creado 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 se calcule Excel.

    Fórmula de matriz de Excel para encontrar el valor n. minúscula: = pequeño (B9 #, secuencia (D9))

    Enter = Small (B9 #, secuencia (D9), = pequeño (B9: B18, {1; 2; 3})

    Esta fórmula usa una constante de matriz para evaluar la pequeña función tres veces y devolver los 3 miembros más pequeños de la matriz contenidos en las celdas B9: B18, donde 3 es un valor de variable en la celda D9. Para buscar más valores, puede aumentar el valor de la función SEQUENCE o agregar más argumentos a la constante. También puede usar funciones adicionales con esta fórmula, como suma o promedio. Por ejemplo:

    = SUMA (pequeño (B9 #, secuencia (D9))

    = PROMEDIO (pequeño (B9 #, secuencia (D9))

  • Buscar los n valores mayores de un rango

    Para buscar los valores más grandes de un rango, puede reemplazar la función pequeña por la función grande. Además, el siguiente ejemplo usa las funciones Row e indirecto .

    Entrar = grande (B9 #, fila (indirecto ("1:3"))), o = grande (B9: B18, fila (indirecto ("1:3")))

    Llegados a este punto, puede ser útil saber un poco acerca de las funciones de fila e inDIRECTA. Puede usar la función fila para crear una matriz de enteros consecutivos. Por ejemplo, seleccione vacío y entrar:

    = FILA (1:10)

    La fórmula crea una columna de 10 enteros consecutivos. Para ver un posible problema, inserte una fila encima del rango que contiene la fórmula de matriz (es decir, encima de la fila 1). Excel ajusta las referencias de fila y la fórmula genera ahora enteros de 2 a 11. Para solucionar este problema, agregue la función inDIRECTO a la fórmula:

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

    La función inDIRECTO usa cadenas de texto como argumentos (que es la razón por la que el rango 1:10 está rodeado por Comillas). Excel no ajusta los valores de texto al insertar filas o al mover la fórmula de matriz. Como resultado, la función ROW siempre genera la matriz de enteros que desee. Puede usar la secuencia de la misma manera que fácilmente:

    = SECUENCIA (10)

    Examinemos la fórmula que usó antes, = grande (B9 #, fila (inDIRECTO ("1:3"))), empezando desde los paréntesis interiores y trabajando hacia fuera: la función inDIRECTO devuelve un conjunto de valores de texto, en este caso, los valores 1 a 3. La función ROW, a su vez, genera una matriz de columnas de tres celdas. La función grande usa los valores del rango de celdas B9: B18 y se evalúa tres veces, una vez para cada referencia devuelta por la función fila. Si desea buscar más valores, agregue un rango de celdas mayor a la función inDIRECTO. Por último, al igual que con los pequeños ejemplos, puede usar esta fórmula con otras funciones, como suma y promedio.

  • Sumar un rango que contiene valores de error

    La función suma en Excel no funciona cuando intenta sumar un rango que contiene un valor de error, como #VALUE! o #N/A. Este ejemplo muestra cómo sumar los valores de un rango denominado Data que contiene errores:

    Usar matrices para tratar los errores. Por ejemplo, = suma (si (esERROR (datos), "", datos) suma el rango denominado datos aunque incluya errores, como #VALUE! o #NA!.

  • =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 devuelve el número de valores de error de un rango denominado datos en lugar de filtrarlos:

    =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.

Es posible que necesite sumar valores basados en condiciones.

Puede usar matrices para calcular según ciertas condiciones. = SUMA (si (Sales>0, ventas)) sumará todos los valores mayores que 0 en un intervalo llamado ventas.

Por ejemplo, esta fórmula de matriz suma solo los enteros positivos de un rango denominado ventas, que representa las celdas E9: E24 en el ejemplo anterior:

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

La función si crea una matriz de valores positivos y falsos. Esencialmente, la función suma omite los valores falsos porque 0 + 0 = 0. El rango de celdas que use en esta fórmula puede contener cualquier número de filas y columnas.

También puede sumar valores que cumplan más de una condición. Por ejemplo, esta fórmula de matriz calcula valores mayores que 0 y menores que 2500:

= SUMA ((Sales>0) * (Sales<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 usen un tipo de condición OR. Por ejemplo, puede sumar valores superiores a 0 o inferiores a 2500:

= SUMA (si ((Sales>0) + (Sales<2500), ventas))

No puede usar las funciones y y o en fórmulas de matriz directamente porque estas funciones devuelven un único resultado, ya sea verdadero o falso, y las funciones de matriz requieren matrices de resultados. Puede solucionar el problema usando la lógica que se muestra en la fórmula anterior. En otras palabras, se realizan operaciones matemáticas, como suma o multiplicación de valores que cumplen la condición OR o AND.

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 mis datos y Tusdatos y devuelve el número de diferencias entre las dos. 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 tener el mismo tamaño y de la misma dimensión. Por ejemplo, si mis datos son un rango de 3 filas por 5 columnas, Tusdatos también debe ser 3 filas por 5 columnas:

=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 en la hoja de cálculo diferencias entre conjuntos de valores .

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

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

Ventas Persona

Coche Escriba

Número Vendido

Unidad Precio

Total Ventas

Bernabé

Sedán

3,5

33000

Cupé

cuatro

37000

Inda

Sedán

6

24000

Cupé

9

21000

Robledo

Sedán

,3

29000

Cupé

1D

31000

Parra

Sedán

9

24000

Cupé

3,5

37000

Sánchez

Sedán

6

33000

Cupé

9

31000

Fórmula (Total general)

Total general

'=SUMA(C2:C11*D2:D11)

=SUMA(C2:C11*D2:D11)

  1. Para ver las ventas totales de las recuperaciones y sedán para cada vendedor, seleccione las celdas E2: E11, escriba la fórmula = C2: C11 * D2: D11y, a continuación, presione Ctrl + Mayús + entrar.

  2. Para ver el total general de todas las ventas, seleccione la celda F11, escriba la fórmula = suma (C2: C11 * D2: D11)y, a continuación, presione Ctrl + Mayús + entrar.

Al presionar Ctrl + Mayús + entrar, Excel coloca la fórmula entre llaves ({}) e inserta una instancia de la fórmula en cada celda del rango seleccionado. Esto sucede muy rápidamente, así que lo que ve en la columna E es el importe total de ventas de cada tipo de automóvil para cada vendedor. Si selecciona E2, después selecciona E3, E4, etc., verá que se muestra 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 fórmula siguiente 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, a continuación, usa la función sumapara sumar los totales. El resultado es un total general de $1.590.000 en sales. Este ejemplo muestra lo eficaz que puede ser este tipo de fórmula. Por ejemplo, supongamos que tiene 1.000 filas de datos. Puede sumar parte de los datos o todos ellos creando una fórmula de matriz en una sola celda en lugar de arrastrar la fórmula hacia abajo en las filas de 1.000.

Tenga en cuenta también que la fórmula de una sola celda en la celda D13 es totalmente independiente de la fórmula de varias celdas (la fórmula de las celdas E2 a E11). Esta es otra ventaja de usar fórmulas de matriz: flexibilidad. Puede cambiar las fórmulas de la columna E o eliminar todas las columnas, sin que ello afecte a la fórmula de 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 se puede sobrescribir un componente de una fórmula de matriz de varias celdas. Por ejemplo, haga clic en la celda E3 y presione suprimir. Debe seleccionar todo el rango de celdas (E2 a E11) y cambiar la fórmula para toda la matriz, o bien dejar la matriz como está. Como medida de seguridad adicional, tiene que 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 la sintaxis de fórmula estándar. Todos comienzan con el signo igual (=) y puede usar la mayoría de las funciones integradas de Excel en las fórmulas de matriz. La principal diferencia es que cuando se usa una fórmula de matriz, se presiona Ctrl + Mayús + entrar para introducir la fórmula. Al hacerlo, Excel rodea 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 manera eficaz de crear fórmulas complejas. La fórmula de matriz = suma (C2: C11 * D2: D11) es la misma que esta: = suma (C2 * D2, C3 * D3, C4 * D4, C5 * D5, C6 * D6, C7 * D7, C8 * D8, de C9 * D9, C10 * D10, C11 * D11).

Importante: Presione Ctrl + Mayús + entrar siempre que necesite introducir una fórmula de matriz. Esto se aplica tanto a las fórmulas de una sola celda como a las 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, a continuación, presione suprimir.

  • No se pueden insertar celdas en blanco en o eliminar celdas de una fórmula de matriz de varias celdas.

En ocasiones, es posible que tenga que expandir una fórmula de matriz. Seleccione la primera celda del rango de matriz existente y continúe hasta que haya seleccionado todo el rango al que desea extender la fórmula. Presione F2 para modificar la fórmula y, a continuación, presione Ctrl + Mayús + entrar para confirmar la fórmula una vez que haya ajustado el rango de fórmulas. La clave es seleccionar todo el rango, empezando por la celda superior izquierda de la matriz. La celda superior izquierda es aquella que se modifica.

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

  • En ocasiones, puede que se olvide de presionar Ctrl + Mayús + entrar. Puede suceder incluso con los usuarios más experimentados de Excel. Recuerde presionar esta combinación de teclas siempre que introduzca o edite una fórmula de matriz.

  • Puede que otros usuarios del 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 otros usuarios necesitan modificar los libros, debe evitar fórmulas de matriz o asegurarse de que esas personas conocen las fórmulas de matriz y comprender cómo cambiarlas, si es necesario.

  • 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}

Por ahora, sabe que tiene que presionar Ctrl + Mayús + entrar al crear fórmulas de matriz. Dado que las constantes de matriz son un componente de las fórmulas de matriz, las constantes se rodean entre llaves escribiéndola manualmente. A continuación, use Ctrl + Mayús + entrar para escribir toda la fórmula.

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.

Esta es una matriz en una sola fila: {1, 2, 3, 4}. Esta es una matriz en 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 fila es 1, 2, 3 y 4, y la segunda fila es 5, 6, 7 y 8. UN solo punto y coma separa las dos filas, entre 4 y 5.

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

  1. En una hoja de cálculo en blanco, seleccione las celdas de la A1 a la E1.

  2. En la barra de fórmulas, escriba la fórmula siguiente y presione Ctrl + Mayús + entrar:

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

    En este caso, debes escribir las llaves de apertura y cierre ({}), y Excel agregará el segundo conjunto automáticamente.

    Aparece el siguiente resultado.

    Constante de matriz horizontal en una fórmula

Crear una constante vertical

  1. En el libro, seleccione una columna de cinco celdas.

  2. En la barra de fórmulas, escriba la fórmula siguiente y presione Ctrl + Mayús + entrar:

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

    Aparece el siguiente resultado.

    Constante de matriz vertical en una fórmula de matriz

Crear una constante bidimensional

  1. En el libro, seleccione un bloque de celdas de cuatro columnas de ancho por tres filas de alto.

  2. En la barra de fórmulas, escriba la fórmula siguiente y presione Ctrl + Mayús + entrar:

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

    Verá el resultado siguiente:

    Constante de matriz bidimensional en una fórmula de matriz

Usar constantes en fórmulas

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

  1. En el libro de muestra, cree otra hoja de cálculo.

  2. Escriba 3 en la celda A1 y luego 4 en B1, 5 en C1, 6 en D1 y 7 en E1.

  3. En la celda a3, escriba la fórmula siguiente 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.

    Fórmula de matriz con constante 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 dentro de los paréntesis es la constante de matriz: {1, 2, 3, 4, 5}. Recuerde que Excel no rodea las constantes de matriz con llaves; los escribe realmente. Recuerde también que después de agregar una constante a una fórmula de matriz, presione 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 probar esto, copie la función, seleccione una celda en blanco del libro, pegue la fórmula en la barra de fórmulas y, a continuación, presione Ctrl + Mayús + entrar. Verá el mismo resultado que en el ejercicio anterior que usó 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.

Una de las mejores formas de usar constantes de matriz es denominarlas. Las constantes con nombre pueden ser mucho más fáciles de usar y pueden ocultar parte de la complejidad de las fórmulas de matriz de otras personas. Para asignar un nombre a una constante de matriz y usarla en una fórmula, haga lo siguiente:

  1. En la pestaña fórmulas, en el grupo nombres definidos, haga clic en definir nombre.
    Aparece el cuadro de diálogo definir nombre.

  2. En el cuadro Nombre, escriba Trimestre1.

  3. 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:

    Cuadro de diálogo Editar nombre con fórmula

  4. Haga clic en Aceptar y seleccione una fila de tres celdas en blanco.

  5. Escriba la fórmula siguiente y presione Ctrl + Mayús + entrar.

    =Trimestre1

    Aparece el siguiente resultado.

    Matriz con nombre especificada como una fórmula

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 algunos elementos no estén separados por el carácter correcto. Si omite una coma o un punto y coma, o si coloca una en el lugar equivocado, es posible que la constante de matriz no se haya creado correctamente o que vea 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.

En los ejemplos siguientes se muestran algunas de las formas en las que puede poner constantes de matriz para usarlas en fórmulas de matriz. Algunos de los ejemplos usan la función transponer para convertir filas en columnas y viceversa.

Multiplicar cada elemento de una matriz

  1. Cree otra hoja de cálculo y seleccione un bloque de celdas vacías de cuatro columnas de ancho por tres filas de alto.

  2. Escriba la fórmula siguiente y presione Ctrl + Mayús + entrar:

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

Elevar al cuadrado los elementos de una matriz

  1. Seleccione un bloque de celdas vacías de cuatro columnas de ancho por tres filas de alto.

  2. Escriba la siguiente fórmula de matriz y, a continuación, 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, en la que se utiliza el operador de intercalación (^):

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

Transponer una fila unidimensional

  1. Seleccione una columna de cinco celdas en blanco.

  2. 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

  1. Seleccione una fila de cinco celdas en blanco.

  2. 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

  1. Seleccione un bloque de celdas de tres columnas de ancho por cuatro filas de alto.

  2. Escriba la siguiente constante y, a continuación, 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

  1. 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.

  2. Presione Ctrl + Mayús + entrar, que especifica esta matriz de números en el rango de celdas C8: E10 mediante una fórmula de matriz. En la hoja de cálculo, C8 a E10 debería tener este aspecto:

    7

    508

    semestre

    40

    50

    60

    70

    80

    90

  3. Seleccione el rango de celdas de C1 a E3.

  4. Escriba la fórmula siguiente en la barra de fórmulas y, a continuación, presione Ctrl + Mayús + entrar:

    =C8:E10

    En las celdas C1 a E3 aparece 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

  1. Con las celdas C1: C3 seleccionadas, presione F2 para cambiar al modo de edición.

  2. Presione F9 para convertir las referencias de celda a valores. Excel convierte los valores en una constante de matriz. La fórmula debe ser ahora = {10, 20, 30; 40; 50; 60; 70; 80; 90}.

  3. Presione Ctrl + Mayús + entrar para especificar 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.

  1. 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)

  2. Seleccione la celda A8 y, a continuación, presione Ctrl + Mayús + entrar para ver el número total de caracteres de las celdas a2: A6 (66).

  3. Seleccione la celda A10 y, a continuación, 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 se usa en la celda A8 cuenta el número total de caracteres (66) en las celdas A2 a A6.

=SUMA(LARGO(A2:A6))

En este caso, la función Len devuelve la longitud de cada cadena de texto de cada una de las celdas del rango. A continuación, la función suma suma los 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.

  1. Escriba algunos números aleatorios en las celdas A1: A11.

  2. Seleccione las celdas C1 a C3. Este conjunto de celdas contendrá los resultados devueltos por la fórmula de matriz.

  3. Escriba la fórmula siguiente y presione Ctrl + Mayús + entrar:

    = PEQUEÑO (A1: A11, {1; 2; 3})

Esta fórmula usa una constante de matriz para evaluar la pequeña función tres veces y devolver el menor (1), el segundo menor (2) y el tercer miembro más pequeño (3) de la matriz contenida en las celdas a1: A10 para buscar más valores, agregue más argumentos a la permanente. También puede usar funciones adicionales con esta fórmula, como suma o promedio. Por ejemplo:

= SUMA (pequeña (A1: A10, {1, 2, 3})

= PROMEDIO (pequeño (A1: A10, {1, 2, 3})

Buscar los n valores más grandes 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.

  1. Seleccione las celdas de la D1 a D3.

  2. En la barra de fórmulas, escriba esta fórmula y, a continuación, presione Ctrl + Mayús + entrar:

    = LARGE (A1: A10, fila inDIRECTA ("1:3")))

Llegados a este punto, puede ser útil saber un poco acerca de las funciones de fila e indirecta. 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, introduzca esta fórmula de matriz y, a continuación, 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.

Echemos un vistazo a la fórmula que usó antes: = grande (A5: A14, fila (indirecto ("1:3"))) , empezando desde los paréntesis interiores y trabajando hacia fuera: la función indirecto devuelve un conjunto de valores de texto, en este caso, los valores 1 a 3. A su vez, la función fila genera una matriz de columnas de tres celdas. La función Large usa los valores del rango de celda A5: A14 y se evalúa tres veces, una vez para cada referencia devuelta por la función fila . Los valores 3200, 2700 y 2000 se devuelven a la matriz de tres celdas en columnas. Si desea buscar más valores, agregue un rango de celdas mayor a la función indirecto .

Como en los 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

Vuelva al ejemplo de cadena de texto anterior, escriba la fórmula siguiente 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.

Echemos un vistazo más detenidamente a la fórmula, empezando desde los elementos internos y trabajando hacia fuera. La función Len devuelve la longitud de cada uno de los elementos del rango de celdas a2: A6. La función Max calcula el valor mayor entre los elementos, que corresponde a la cadena de texto más larga, que está 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:

(Longitud máxima (A2: A6))

Y esa cadena se encuentra en esta matriz:

LEN (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 los siguientes 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 la celda y el último argumento (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.

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.

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))

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.

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))

Reconocer

Algunas partes de este artículo se basaban en una serie de columnas de usuario avanzado de Excel escritas por Colin Wilcox y se adaptaron de los capítulos 14 y 15 de fórmulas de Excel 2002, un libro escrito por John Walkenbach, un antiguo MVP de Excel.

¿Necesita más ayuda?

Siempre puede preguntar a un experto en Excel Tech Community, obtener soporte técnico en la Comunidad de respuestas o sugerir una característica nueva o mejora en el UserVoice de Excel.

Vea también

Matrices dinámicas y comportamiento de matriz desbordada

Fórmulas de matriz dinámica frente a fórmulas de matriz de CSE heredadas

Función FILTER

Función RANDARRAY

Función SEQUENCE

Función SINGLE

Función SORT

Función SORTBY

Función UNIQUE

Errores #SPILL! en Excel

Información general sobre fórmulas

Ampliar sus conocimientos de Office
Explorar los cursos
Obtener nuevas características primero
Únase a los participantes de Office Insider

¿Le ha sido útil esta información?

¡Gracias por sus comentarios!

Gracias por sus comentarios. Quizá le interese ponerse en contacto con uno de nuestros agentes de soporte de Office.

×