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. Puede considerar una matriz como una fila o columna de valores, o una combinación de filas y columnas de valores. Las fórmulas de matriz pueden devolver varios resultados o un solo resultado.

A partir de la actualización de septiembre de 2018 para Office 365, cualquier fórmula que pueda devolver varios resultados los hará de forma automática o bien 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, ya utilicen funciones existentes o las funciones de matriz dinámica, solo deben introducirse en una sola celda y, a continuación, confirmarse pulsando 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 denominarse fórmulas de CSE .

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

  • Cree rápidamente conjuntos de comandos de ejemplo.

  • Contar el número de caracteres que contiene 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 n valor de un rango de valores.

En los siguientes ejemplos se muestra cómo crear fórmulas de matriz de varias celdas y de una sola celda. Siempre que sea posible, incluimos ejemplos con algunas de las funciones de matriz dinámica, así como las 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 en 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

  • Aquí se calculan las ventas totales de recuperaciones y sedán para cada comercial, escribiendo = F10: F19 * G10: G19 en el H10 de celda.

    Cuando presione entrar, verá que los resultados se sobrepasarán a las celdas H10: H19. Observe que el rango de derrame está resaltado con un borde cuando selecciona 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í por referencia, por lo que si desea ajustar la fórmula, tendrá que seleccionar la celda H10, en la que 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 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 H2O es completamente 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 demás fórmulas de la columna H sin que ello afecte a la fórmula de H2O. También puede ser una buena práctica 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 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 H11 y presione suprimir. Excel no cambiará la salida de la matriz. Para cambiarlo, tiene que seleccionar la celda superior izquierda de la matriz o el H10 de celda.

    • 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 ventas de coches 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. Eso 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 forma eficaz de crear fórmulas complejas. La fórmula matricial = suma (F10: F19 * G10: G19) es la misma que esta: = suma (F10 * G10, F11 * G11, F12 * G12, F13 * G13, F14 * G14, F15 * G15, F16 * G16, F17 * G17, F18 * G18, F19 * G19).

    • Sobrepasen    Las fórmulas de matriz dinámica se recargará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 de tamaño a medida que agregue o quite los datos.

    • #SPILL. :    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 reproducirá 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, debe delimitar los elementos de cada fila con comas y delimitar 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. Mostraremos ejemplos usando la función Sequence para generar automáticamente constantes de matriz, así como constantes de matriz introducidas manualmente.

  • Crear una constante horizontal

    Use el libro con los ejemplos anteriores o si lo prefiere cree uno nuevo. Seleccione cualquier celda vacía y escriba = secuencia (1, 5). La función SEQUENCE genera una fila de 1 fila por 5 columnas de la misma forma que = {1, 2, 3, 4, 5}. Aparece el siguiente resultado:

    Crear 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}. Aparece el siguiente resultado:

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

  • Crear una constante bidimensional

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

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

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

    Como puede ver, la opción SEQUENCE ofrece ventajas significativas para introducir manualmente 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 el punto y coma puede ser difícil de distinguir de los separadores de coma.

Este es un ejemplo en el que se usan constantes de matriz como parte de una fórmula más amplia. En el libro de ejemplo, vaya a la constante de una hoja de cálculo de fórmulas o cree una hoja de cálculo nueva.

En la celda D9 escribimos = Sequence (1, 5, 3, 1), pero también puede escribir 3, 4, 5, 6 y 7 en las celdas A9: H9. No hay nada especial sobre esa selección de número en particular, simplemente elegimos un valor distinto de 1-5 para diferenciar.

En la E11 de la celda, escriba = suma (D9: 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, usamos = suma (D9: H (* SEQUENCE (1, 5))

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

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

Por último, la función suma 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:

= Suma (secuencia (1, 5, 3, 1) * secuencia (1, 5))o = suma ({3, 4, 5, 6, 7} * {1, 2, 3, 4, 5})

Elementos que se pueden 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 números en formato entero, decimal y científico. Si incluye texto, debe escribirlo 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 maneras de usar constantes de matriz es asignarles un 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 _GT_ 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 debe tener ahora un aspecto similar a este:

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

Haga clic en Aceptar, seleccione una fila con tres celdas en blanco 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 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 secuencia. Lo bueno de esta función es que aunque solo se muestra el mes, hay una fecha válida en la que se 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 de conjuntos de cálculo del libro 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

Usa la función fecha para crear una fecha basada en el año actual, secuencia crea una constante matricial de 1 a 12 para enero a diciembre, pero la función texto convierte el formato de presentació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 interpretará la matriz como una cadena de texto. Por último, tenga en cuenta que puede usar combinaciones de funciones, texto y números. Todo depende de la creatividad que quieras obtener.

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

  • Varios elementos en una matriz

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

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

  • Elevar al cuadrado los elementos de una matriz

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

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

    Entrar =raiz(secuencia (1, 12) ^ 2), o = raiz ({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 de D9: D11. El signo # se denomina operador de rango derramadoy es Excel's forma de hacer referencia a todo el rango de la 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 después en una constante de matriz. Al presionar entrar, la fórmula, = D9 #, debe 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 espacios.

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

    = SUMA (LARGO (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. La función suma agrega esos valores juntos y muestra el resultado (66). Si desea obtener el promedio de caracteres, puede usar:

    = PROMEDIO (LONGITUD (C9: C13))

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

    = ÍNDICE (C9: C13, COINCIDIR (LONGITUD MÁXIMA (DE 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 mayor entre los elementos, que corresponde a la cadena de texto más larga, que está en la celda D3.

    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 (LONGITUD (C9: C13)

    Y esa cadena se encuentra en esta matriz:

    LARGO (C9: C13)

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

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

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

    • -1: devuelve el valor más bajo 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 tiene 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 viene de la primera columna de la matriz.

    Si desea obtener el contenido de la cadena de texto más pequeña, debería reemplazar MAX en el ejemplo anterior con 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 creó 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 buscar el número n. º menor: = pequeño (B9 #, secuencia (D9))

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

    Esta fórmula usa una constante de matriz para evaluar la función pequeña 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, por ejemplo 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 mayores de un rango, puede reemplazar la función pequeña por la función grande. Además, el ejemplo siguiente usa las funciones FILA e INDIRECTO.

    Escriba = Large (B9 #, fila (indirecto ("1:3")))o = Large (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, seleccione un valor vacío y escriba lo siguiente:

    =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 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 (razón por la cual el rango 1:10 está rodeado por 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. Puede usar la secuencia de forma tan sencilla:

    = SECUENCIA (10)

    Vamos a examinar la fórmula que usó anteriormente (= 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 fila, 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 al intentar 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 datos que contienen errores:

    Use matrices para resolver errores. Por ejemplo, = suma (si (EsError (datos), "", datos) sumarán 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.

Tal vez 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 rango denominado ventas.

Por ejemplo, esta fórmula de matriz suma solo los enteros positivos en 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. 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 ((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 menores que 2500:

= SUMA (si ((Sales>0) + (Sales<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, se realizan operaciones matemáticas, como suma o multiplicación en 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 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 tener el mismo tamaño y de la misma dimensión. Por ejemplo, si los datos son un rango de 3 filas por 5 columnas, Tusdatos debe ser también 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 * (MyData<>YourData))

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 ejemplo 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 Vendidos

Unidad Precio

Total Ventas

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)

  1. Para ver las ventas totales de las recuperaciones y sedán para cada comercial, 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 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 D13 de celda 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 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 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 D13 es completamente independiente de la fórmula de varias celdas (la fórmula de las celdas 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 la columna por completo, 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 es posible sobrescribir un componente de una fórmula de matriz de varias celdas. Por ejemplo, haga clic en la celda E3 y presione suprimir. 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, 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 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 cuando use una fórmula de matriz, presione Ctrl + Mayús + entrar para introducir la fórmula. 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 eficaz de crear 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 siempre que necesite introducir 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 el rango de fórmulas completo (por ejemplo, E2: E11) y, a continuación, presione la tecla 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 tecla es seleccionar todo el rango, empezando por la celda superior izquierda de la matriz. La celda superior izquierda es la que se modifica.

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

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

  • Es posible 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 sus libros, debe evitar las 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}

En este momento, sabrá que debe 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. A continuación, puede usar 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 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 fila es 1, 2, 3 y 4, y la segunda fila es 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

  1. En una hoja de cálculo en blanco, seleccione las celdas de a1 a 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, debe escribir las llaves de apertura y cierre ({}) y Excel agregará el segundo conjunto para usted.

    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 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. Recuerde también que después de agregar una constante a una fórmula de matriz, presione Ctrl + Mayús + entrar para introducir 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, 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.

Una de las mejores formas de usar constantes de matriz es asignarles un 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:

  1. En la pestaña Fórmulas vaya al grupo Nombres definidos y 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 no haya separado algunos elementos con el carácter adecuado. Si omite una coma o un punto y coma, o si coloca uno 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.

Los ejemplos siguientes muestran algunas formas de usar constantes de matriz 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,11,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 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

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

  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, el aspecto de C8 a E10 debería ser el siguiente:

    10

    20

    0,30

    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

    Una matriz de 3x3 de celdas aparece en las celdas C1 a E3 con los mismos valores que se ven en 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 en 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 LARGO devuelve la longitud de todas las cadenas de texto contenidas en las celdas del rango. La función suma agrega esos valores juntos 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 de C1 a C3. Este conjunto de celdas contendrá los resultados que devuelva 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 función pequeña tres veces y devolver el menor (1), el segundo menor (2) y el tercer miembro menor (3) de la matriz contenida en las celdas a1: A10 para buscar más valores, agregue más argumentos al constante. También puede usar funciones adicionales con esta fórmula, por ejemplo 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 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.

  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 (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, 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 de 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 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 columnas de tres celdas. Si desea buscar más valores, agregue un rango de celdas mayor a la función indirecto .

Al igual que con 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.

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

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 la celda y el último argumento (1) especifica que el valor viene 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 usen un tipo de condición OR. Por ejemplo, puede sumar valores inferiores a 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 * (MyData<>YourData))

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

Acknowledgement

Partes de este 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 las 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 FILTRAR

Función MATRIZALEAT

Función SECUENCIA

Función SIMPLE

Función ORDENAR

Función ORDENARPOR

Función UNICOS

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.

×