Directrices y ejemplos de fórmulas de matriz

---

• Obtener información sobre las fórmulas de matriz
• Obtener información acerca de las constantes de matriz
• Crear constantes unidimensionales y bidimensionales
• Trabajar con fórmulas de matriz básicas
• Trabajar con fórmulas de matriz avanzadas

 

Obtener información sobre las fórmulas de matriz

Generalmente, se conoce a las fórmulas de matriz como fórmulas CME (Ctrl+Mayús+Entrar) porque, en lugar de presionar solo la tecla Entrar, para completar la fórmula debe presionar Ctrl+Mayús+Entrar.

¿Por qué usar fórmulas de matriz?

Si tiene experiencia en el uso de fórmulas de Excel, sabrá que es posible realizar algunas operaciones bastante complejas. Por ejemplo, es posible calcular el costo total de un préstamo a lo largo de un número concreto de años. Sin embargo, si realmente desea dominar las fórmulas de Excel, tiene que aprender a usar fórmulas de matriz. Puede emplearlas para realizar tareas complejas como:

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

Introducción rápida a las matrices y fórmulas de matriz

Una fórmula de matriz es una fórmula que puede realizar varios cálculos en uno o varios elementos de una matriz. Las fórmulas de matriz pueden devolver varios resultados o uno solo. Por ejemplo, puede colocar una fórmula de matriz en un rango de celdas y usarla para calcular una columna o fila de subtotales. También puede especificar una fórmula de matriz para una sola celda y calcular una cantidad única. Una fórmula de matriz que se aplica a varias celdas se denomina fórmula de varias celdas, mientras que si se aplica a una sola, se denomina fórmula de una celda.

Los ejemplos de la siguiente sección muestran cómo crear fórmulas de matriz de una y varias celdas.

¿Se atreve?

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.

Crear una fórmula de matriz de varias celdas

Copie la tabla de abajo y péguela en la celda A1 de una hoja de cálculo en blanco de Excel. Después, siga las instrucciones de debajo de la tabla.

Para ver las Ventas totales de cupés y de sedán de cada comercial, seleccione E2:E11, escriba la fórmula =C2:C11*D2:D11 y presione Ctrl+Mayús+Entrar.

Para el Total general de todas las ventas, seleccione la celda F11, escriba la fórmula =SUMA(C2:C11*D2:D11) y presione Ctrl+Mayús+Entrar.

Si ya está trabajando en Excel, asegúrese de que Hoja1 está activa y seleccione las celdas E2:E11. Presione F2 y escriba la fórmula =C2:C11*D2:D11 en la celda actual, E2. Si presiona Entrar, verá que solo escribe la fórmula en la celda E2 y muestra 165000. En lugar de hacerlo así y presionar solo Entrar, escriba la fórmula y presione Ctrl+Mayús+Entrar. Ahora verá los resultados en las celdas E2:E11. Y observe que, en la barra de fórmulas, aparece la fórmula {=C2:C11*D2:D11}. Esto le indica que se trata de una fórmula de matriz, como se explica a continuación.

Al presionar Ctrl+Mayús+Entrar, Excel encierra la fórmula entre llaves ({ }) e inserta una instancia de la fórmula en cada celda del rango seleccionado. Esto pasa muy deprisa y en la columna E puede ver el importe total de ventas para cada tipo de coche y para cada vendedor. Si selecciona primero E2, después E3 y E4, y así sucesivamente, verá que aparece la misma fórmula: {=C2:C11*D2:D11}.

Crear una fórmula de matriz de una sola celda

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

=SUMA(C2:C11*D2:D11)

En este caso, Excel multiplica los valores de la matriz (el rango de celdas de C2 a D11) y usa la función SUMA para agregar los totales. El resultado es un total general de 1.590.000 $ en ventas. Este ejemplo demuestra lo eficaz que puede resultar este tipo de fórmula. Por ejemplo, imagine que tiene 1.000 filas de datos. Puede sumar parte de los datos o todos ellos si crea una fórmula de matriz de una sola celda en lugar de arrastrar a las 1.000 filas.

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

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

• Coherencia    Si hace clic en cualquiera de las celdas de la columna E2 hacia abajo, verá la misma fórmula. Esa coherencia garantiza una mayor precisión.
• Seguridad    No es posible sobrescribir un componente de una fórmula de matriz de varias celdas. Por ejemplo, haga clic en la celda E3 y presione Supr. Tendrá que seleccionar todo el rango de celdas (de E2 a E11) y modificar la fórmula de la matriz completa o dejar la matriz como está. Como medida de seguridad adicional, tiene que presionar Ctrl+Mayús+Entrar para confirmar la modificación de 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.

Un vistazo a la sintaxis de las fórmulas de matriz

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

Las funciones de matriz son un instrumento muy eficaz para crear una fórmula compleja. 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).

Reglas para especificar y modificar fórmulas de matriz

Merece la pena repetir la regla principal a la hora de crear una fórmula de matriz: presione Ctrl+Mayús+Entrar siempre que sea necesario para especificar o modificar una fórmula de matriz. Esa regla 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 la fórmula completa (por ejemplo, =C2:C11*D2:D11), presione Supr y después Ctrl+Mayús+Entrar.
• No puede insertar celdas en blanco en una fórmula de matriz de varias celdas ni eliminar celdas de la misma.

Ampliar una fórmula de matriz

A veces puede necesitar ampliar una fórmula de matriz. (Recuerde que no es posible reducirla). El proceso no es complicado, aunque debe recordar las reglas de la sección anterior.

En el libro se han agregado algunas filas más de ventas, de la 12 a la 17. Ahora queremos actualizar las fórmulas de matriz para que incluyan las nuevas filas.

Copie esta tabla en la celda A1 de una hoja de cálculo de Excel y siga el procedimiento de cuatro pasos que sigue a la tabla, a continuación.

Seleccione la celda E18, escriba la fórmula de Total general = SUMA(C2:C17*D2:D17) en la celda A20 y presione Ctrl+Mayús+Entrar. La respuesta debería ser 2.131.000.

1. Seleccione el rango de celdas que contiene la fórmula de matriz actual (E2:E11) más las celdas vacías (E12:E17) situadas junto a los nuevos datos. En otras palabras, seleccione las celdas E2:E17.
2. Presione F2 para ir al modo de edición.
3. En la barra de fórmulas, cambie C11 por C17 y D11 por D17. Presione Ctrl+Mayús+Entrar y verá cómo Excel actualiza la fórmula de las celdas de E2 a E11 y coloca una instancia de la misma en las nuevas celdas, de E12 a E17.
4. Escriba la fórmula de matriz = SUMA(C2:C17*D2*D17) en la celda F17 para aplicarla a las celdas comprendidas entre las filas 2 y 17, y presione Ctrl+Mayús+Entrar para insertar la fórmula. El nuevo total general debe ser 2.131.000.

Desventajas de usar fórmulas de matriz

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

• A veces, incluso los usuarios más expertos pueden olvidarse de presionar Ctrl+Mayús+Entrar. Recuerde presionar esta combinación de teclas siempre que especifique o modifique una fórmula de matriz.
• Es posible que otros usuarios no entiendan las fórmulas que usted escriba. En la práctica no suelen explicarse las fórmulas de matriz en las hojas de cálculo, así que si otros usuarios necesitan modificar los libros, debería evitarlas o asegurarse de que esos usuarios entiendan cómo modificarlas.
• 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.

 

Obtener información acerca de las constantes de matriz

Esta sección ofrece información general acerca de las constantes de matriz y explica cómo escribirlas, modificarlas y solucionar los problemas que puedan plantear.

Breve introducción a las constantes de matriz

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}

Las secciones anteriores de este artículo enfatizaban la necesidad de presionar Ctrl+Mayús+Entrar al crear fórmulas de matriz. Dado que las constantes son uno de sus componentes, para delimitarlas entre llaves de forma manual puede escribir dichas llaves. Seguidamente, presione Ctrl+Mayús+Entrar para especificar la fórmula completa.

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 caracteres de punto y coma.

Este es un ejemplo de una matriz de una sola fila: {1,2,3,4}. Esta es una matriz de una sola columna: {1;2;3;4}. Y esta es una matriz de dos filas y cuatro columnas: {1,2,3,4;5,6,7,8}. En la matriz de dos filas, la primera incluye a 1, 2, 3 y 4 y la segunda a 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.

 

Crear constantes unidimensionales y bidimensionales

Con este procedimiento podrá practicar la creación de constantes horizontales, verticales y bidimensionales.

Crear una constante horizontal

1. Use el libro con los ejemplos anteriores o si lo prefiere cree uno nuevo.
2. Seleccione las celdas de A1 a E1.
3. 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 ({ }).

Aparece el siguiente resultado.

---

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.

---

Crear una constante bidimensional

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

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

Verá el resultado siguiente:

---

 

Usar constantes en fórmulas

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

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

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

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

En la celda A3 aparece el valor 85. La sección siguiente explica cómo funciona la fórmula.

Un vistazo a la sintaxis de las constantes de matriz

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

---

---

El último elemento incluido en los paréntesis es la constante de matriz: {1,2,3,4,5}. Recuerde que Excel no incluye las constantes de matriz entre llaves, es usted quien debe hacerlo. Además, después de agregar una constante a una fórmula de matriz, debe presionar Ctrl+Mayús+Entrar para para especificar 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 intentarlo, copie la función, seleccione una celda en blanco del libro, pegue la fórmula en la barra de fórmulas y presione Ctrl+Mayús+Entrar. Verá el mismo resultado que en el ejercicio anterior, cuando empleó la fórmula de matriz: =SUMA(A1:E1*{1,2,3,4,5}).

Elementos que puede usar en las constantes

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.

Poner nombre a las constantes de matriz

Posiblemente el mejor modo de usar las constantes de matriz sea ponerles nombre. Las constantes con nombre pueden resultar mucho más sencillas de usar y tal vez ocultar parte de la complejidad de las fórmulas de matriz de 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.
   Aparecerá 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 debería tener este aspecto:

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.

---

---

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.

Solucionar problemas de las constantes de matriz

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

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

Constantes de matriz en acción

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

Multiplicar cada elemento de una matriz

1. Cree una nueva 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

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

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

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

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

Transponer una fila unidimensional

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.

 

Trabajar con fórmulas de matriz básicas

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. Después, presione Ctrl+Mayús+Entrar para escribir esta matriz de números en el rango de celdas C8:E10 usando una fórmula de matriz. En la hoja de cálculo, el aspecto de C8 a E10 debería ser el siguiente:

2. Ahora, seleccione el rango de celdas de C1 a E3.
3. Escriba la fórmula siguiente y 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 Edición. La fórmula de matriz debería seguir siendo = C8:E10.
2. Presione F9 para convertir las referencias de celda en valores. Excel convierte los valores en una constante de matriz. Ahora, la fórmula debería ser ={10,20,30;40,50,60;70,80,90}, como C8:E10
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.

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

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

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

=SUMA(LARGO(A2:A6))

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

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. Seleccione las celdas de A16 a A18. Este conjunto de celdas contendrá los resultados que devuelva la fórmula de matriz.
2. Escriba la fórmula siguiente en la barra de fórmulas y presione Ctrl+Mayús+Entrar:

=K.ESIMO.MENOR(A5:A14,{1;2;3})

En las celdas de A16 a A18, aparecerán los valores 400, 475 y 500 respectivamente.

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

=SUMA(K.ESIMO.MENOR(A5:A14,{1;2;3}))

=PROMEDIO(K.ESIMO.MENOR(A5:A14,{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 A1 a A3.
2. Escriba la fórmula siguiente en la barra de fórmulas y presione Ctrl+Mayús+Entrar:

=K.ESIMO.MAYOR(A5:A14,FILA(INDIRECTO("1:3")))

Los valores 3200, 2700 y 2000 aparecen en las celdas de A12 a A14, respectivamente.

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ácticas, escriba esta fórmula de matriz en las celdas A5:A14 y 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.

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

Por último, 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

Con este ejemplo se busca la cadena de texto más larga de un rango de celdas. Esta fórmula solo funciona cuando un rango de datos contiene una única columna de celdas. En la Hoja3, escriba la siguiente fórmula en la celda A16 y presione Ctrl+Mayús+Entrar.

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

El texto "grupo de celdas que" aparece en la celda A16.

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 A6:A9. La función MAX calcula el valor más largo de entre esos elementos, que corresponde a la cadena de texto más larga, que se encuentra en la celda A7.

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

(MAX(LARGO(A6:A9))

Y esa cadena se encuentra en esta matriz:

LARGO(A6:A9)

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 A6:A9 proporciona la matriz, la función COINCIDIR proporciona la dirección de las celdas y el argumento final (1) especifica que el valor proviene de la primera columna de la matriz.

Trabajar con fórmulas de matriz avanzadas

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