Fórmulas y Funciones Matriciales en Excel.

Operaciones matriciales de Tipo 3.

 

Fórmulas matriciales: Curso de Matrices en Excel para aprender a trabajar con rangos en vez de con celdas con la finalidad de trabajar con datos organizados en rangos más rápidamente.

Temario: trabajar con rangos matriciales: operaciones básicas y función SI(). Fórmulas de resumen de rangos matriciales: SUMA(), CONTAR(), PROMEDIO(). Uso de SUMA(SI()) para contar. CONTAR.SI() matricial. Funciones matriciales específicas: FRECUENCIA(), TENDENCIA() y SUMAPRODUCTO(). Funciones SI CONJUNTO. Lógica Matricial.

Descarga los ejemplos en: https://www.dropbox.com/s/s6o0zylb6thit0y/Excel-Matrices.zip?dl=1

 

Puedes ir al índice de contenidos para ver todos los Minicursos o a la primera entrega de este minicurso de Matrices en Excel.

 

Operaciones matriciales de Tipo 3: Funciones estándar que se introducen con ENTER pero que tienen un comportamiento matricial.

Las hemos definido antes como:

"TIPO 3: Funciones estándar con comportamiento matricial. Ocupan una sola celda y se introducen con ENTER, como cualquier fórmula estándar. Para crear este tipo de fórmulas matriciales debes hacer clic en una celda, teclear una fórmula y acabar con ENTER. La fórmula es una función estándar pero que se comporta de modo matricial de por sí. Básicamente es la función SUMAPRODUCTO()."

Por tanto, son funciones unicelda y de resumen, como las del Tipo 2, pero que se introducen con un ENTER normal, como cualquier función estándar.

Básicamente se trata de la función SUMAPRODUCTO(rango) que tiene la ventaja de ser usada como función de resumen acabada con un ENTER, es decir, no tenemos que introducirla con CONTROL+MAYUSCULAS+ENTER.

 

En el mundillo de las matrices de Excel, la función SUMAPRODUCTO(rango) se usa mucho por la comodidad que representa introducir la función con un ENTER y no tener que pulsar CONTROL+MAYUSCULAS+ENTER. De esta manera, SUMAPRODUCTO(rango) se usa mucho para sustituir a la función SUMA().

 

Aun así, SUMAPRODUCTO() tiene una limitación importante que explicaremos más adelante.

 

Otras funciones que se comportan matricialmente con sólo pulsar la tecla ENTER son: CONTAR.SI() y SUMAR.SI(). Si bien estas dos funciones no son consideradas matriciales en los manuales de Excel, nosotros las explicaremos aquí por su capacidad de trabajar ordenadamente con los elementos de los rangos.

A partir de la versión de Excel 2007 se añadieron las funciones PROMEDIO.SI(), CONTAR.SI.CONJUNTO(), SUMAR.SI.CONJUNTO(), PROMEDIO.SI.CONJUNTO(). Tampoco aparecen como matriciales en los manuales de Excel pero las explicaremos aquí por su capacidad de trabajar ordenadamente con los elementos de los rangos.

A partir de la versión de Excel 2016 se añadieron las funciones MAX.SI.CONJUNTO(), MIN.SI.CONJUNTO() y SI.CONJUNTO(). Tampoco aparecen como matriciales en los manuales de Excel pero las explicaremos aquí por su capacidad de trabajar ordenadamente con los elementos de los rangos, en especial la función SI.CONJUNTO() que nos evita tener que utilizar los SI() anidados, algo que interesa mucho en las fórmulas matriciales.

Las funciones xxx.SI.CONJUNTO, denominadas funciones SI CONJUNTO, admiten hasta 127 pruebas lógicas.

 

Estas funciones que aquí consideramos matriciales de Tipo 3, no son un tipo de funciones matriciales que hagan cosas únicas, se pueden sustituir perfectamente por funciones matriciales de Tipo 2, pero el caso es que usando las de Tipo 3 es más fácil y más cómodo hacer los cálculos y además se introducen con un ENTER normal, y esta es la "fama" que tienen dentro del mundillo de las matrices de Excel.

 

Vamos a ver cada una de ellas.

 

Abre el archivo "MatricesTipo3.xls". Veréis que tiene la hoja "Cálculos" que hemos usado en los temas anteriores, ya que como hemos dicho, las funciones matriciales de Tipo 3 sirven para sustituir a las de Tipo 2, pero veremos que son mucho más cómodas. Se trata, pues, de repetir ejemplos que hemos usado en temas anteriores, pero con las nuevas fórmulas de Tipo 3.

 

SUMAPRODUCTO() para versiones Excel 2003 y posteriores

Se trata de una función realmente matricial. Se puede saber porque, en la ayuda contextual que aparece cuando se teclea una fórmula en Excel, aparece la palabra "matriz":

 

 

Nota: si estás usando una versión en inglés aparece la palabra "array".

 

Para ver su uso, vamos a recordar que, en el tema anterior, en el archivo "MatricesTipo2.xls", nos poníamos en la hoja "Cálculos" y calculábamos la suma de las facturas en una sola celda con la fórmula matricial =SUMA(cantidad*precio unitario) acabada con CONTROL+MAYUSCULAS+ENTER. Ahora, usando =SUMAPRODUCTO(cantidad*precio unitario), podremos acabar con un ENTER normal para hacer los cálculos.

Lo vamos a comprobar rápidamente. En el archivo "MatricesTipo3.xls" en la hoja "Cálculos":

 

 

Hemos puesto las pestañas de las hojas de color amarillo para evitar confusiones con los otros archivos. Teclea las siguientes fórmulas en esta hoja "Cálculos":

• Haz clic en F27
• Teclea la fórmula =SUMAPRODUCTO(B3:B24*C3:C24)
• Y acaba con un ENTER normal.
• Haz clic en H27
• Teclea la fórmula  =SUMAPRODUCTO(B3:B24*C3:C24*(1-D3:D24)*(1+E3:E24))
• Y acaba con un ENTER normal.

Los resultado ha de ser el mismo que el que obtuvimos en "MatricesTipo2.xls":

 

 

¡Y todo sin pulsar CONTROL+MAYUSCULAS+ENTER!

 

Mira cómo se hace:

 

La función SUMAPRODUCTO() es en realidad una función que usa varias matrices:

 

=SUMAPRODUCTO(rango1;rango2;rango3;....)

 

Lo que hace es multiplicar los elementos que están en la misma posición en todas las matrices, y luego suma los elementos de la matriz resultante. Sin embargo, si se usa con un solo rango:

 

=SUMAPRODUCTO(rango)

 

entonces lo que hace la función es limitarse a sumar los elementos de esa matriz. Con este truco, podemos usar SUMAPRODUCTO(rango) y ENTER, como sustitución de SUMA(rango) y CONTROL+MAYUSCULAS+ENTER, lo cual es más cómodo de usar en el mundillo de las matrices en Excel.

Además, las dos fórmulas siguientes son totalmente equivalentes:

 

• =SUMAPRODUCTO(rango1;rango2;rango3;...;...;...) y ENTER
• =SUMAPRODUCTO(rango1*rango2*rango3*...*...*...) y ENTER

 

Esto es debido a que en la primera de ellas es la propia función SUMAPRODUCTO() la que multiplica las matrices y luego suma los elementos de la matriz resultante. En la segunda, somos nosotros quienes formulamos la multiplicación y SUMAPRODUCTO() se limita a sumar el resultado.

Al igual que en el tema anterior, en este caso concreto tenemos que hubiera sido lo mismo:

• poner en un rango cualquiera rangoX la fórmula matricial =B3:B24*C3:C24, y sumar los elementos de este rangoX en F27 con =SUMAPRODUCTO(rangoX) acabado con ENTER, hubiera sido lo mismo que...
• lo que hemos hecho, es decir, usar directamente en una sola celda, en este caso F27, la función =SUMAPRODUCTO(B3:B24*C3:C24) y acabarla con ENTER.

Y también:

• en el primer caso se hubiera visto la matriz con los cálculos intermedios en rangoX y se hubieran visto los elementos de esa matriz que luego sumaríamos.
• en el segundo caso no se ve la matriz, la pone Excel en la memoria de la computadora y hace la suma con esta matriz que no se ve.

Por tanto, no hay diferencia en realidad entre las operaciones matriciales de Tipo 2 y las de Tipo 3, lo que pasa es que son más cómodas, no necesitan CONTROL+MAYUSCULAS+ENTER y son más asequibles para todo el mundo, ya que el tema de las matrices siempre es difícil.

 

Ahora hazlo tú:

Teniendo en cuenta que SUMAPRODUCTO(rango1;rango2;rango3;rango4) multiplica ordenadamente los elementos de los rangos (los primeros con los primeros, los segundos con los segundos, los terceros con los terceros...) y luego suma estas multiplicaciones, te proponemos que compruebes por ti mismo que las siguientes dos fórmulas matriciales son equivalentes:

• =SUMAPRODUCTO(B3:B24*C3:C24*(1-D3:D24)*(1+E3:E24)) y ENTER
• =SUMAPRODUCTO(B3:B24;C3:C24;(1-D3:D24);(1+E3:E24)) y ENTER

Teclea ambas fórmulas en dos celdas cualesquiera, por ejemplo, la primera ya la tienes en H27, teclea la segunda en H28. Comprueba que dan el mismo valor y comprueba que cambiando los datos de las columnas B, C, D y E ambas fórmulas dan los mismos resultados.

Te pedimos que deduzcas por ti mismo cuál es la razón por la que ambas fórmulas son equivalentes y que encuentres, por el mismo método, la fórmula equivalente a =SUMAPRODUCTO(B3:B24*C3:C24) y la pongas en F28.

 

Te damos la solución:

Clic aquí para ver

 

Ahora hazlo tú:

Seguimos en el archivo "MatricesTipo3.xls" con las pestañas de color amarillo. Ve a la hoja "Pagar" donde verás una lista de personas cada una de las cuales ha trabajado una ciertas horas a un cierto precio/hora:

 

 

Te pedimos que, en la celda B21, pongas una fórmula matricial que calcule el total a pagar (para todas las personas) sin usar celdas con cálculos intermedios y sin usar CONTROL+MAYUSCULAS+ENTER. El resultado debería ser:

21.925,00 €

 

Te damos una pista:

Clic aquí para ver

 

Te damos la solución:

Clic aquí para ver

 

Función CONTAR.SI() para versiones Excel 2003 y posteriores

Es una función de resumen de rangos que mezcla el conteo con una sola prueba lógica. Esta función pueden sustituir a =CONTAR(SI()) que hemos visto en el apartado anterior para contar algunos datos, pero CONTAR.SI() es menos potente, si bien es más simple de usar. Veremos su sintaxis y luego unos ejemplos, ya que esta función se usa mucho:

 

=CONTAR.SI(rango a contar;"prueba lógica para el rango")

 

Observa que la prueba lógica va entre comillas porque la prueba lógica se ha de poner en forma de texto, pero por lo demás se usan los operadores lógicos normales = , > , >= , <  , <= , <>. En el caso del operador igual (=) puede sustituirse por no poner ningún operador.

 

Como podemos ver sólo hay 1 criterio o prueba lógica, por lo que tampoco se pueden hacer cosas sofisticadas. La prueba lógica se aplica a los mismos elementos del rango a contar.

 

No es una función matricial estrictamente hablando, porque en la ayuda contextual de Excel al teclear la fórmula no aparece la palabra "matriz":

 

 

Pero a pesar de ello, en este Minicurso la consideraremos como matricial por su capacidad de trabajar ordenadamente con rangos.

Veamos unos ejemplos. Seguimos en el archivo "MatricesTipo3.xls" y vamos a la hoja "Resúmenes". Vamos a contar las facturas que tengan un plazo de 30 días, para hacerlo bastaría con contar cuántos 30 hay en la columna C. Vamos a poner la fórmula provisionalmente en L6 que sería:

• Haz clic en L6
• Teclea la fórmula =CONTAR.SI(C2:C21;"=30")
• Y acaba con un ENTER normal.

Esto da como resultado:

13

... que es cuántos 30's hay en la columna C. Observa que la prueba lógica se aplica al mismo rango que se cuenta. No hay posibilidad de usar varios rangos.

 

Mira cómo se hace:

 

Si te piden cuántas facturas hay a 60 días "y" a 90 días, hay que tener en cuenta que muchas veces en el lenguaje hablado, cuando se dice la palabra "y", en realidad se está diciendo "más" o "sumadas". Por tanto,  en este caso concreto, te estarían pidiendo "cuántas facturas hay a 60 días más cuántas a 90 días". Para conseguirlo, provisionalmente, teclea cualquiera de estas fórmula en L6:

• Haz clic en L6
• Teclea cualquiera de estas fórmulas:

=CONTAR.SI(C2:C21;"=60")+CONTAR.SI(C2:C21;"=90")

Como el signo igual (=) se puede obviar, también puedes poner:

=CONTAR.SI(C2:C21;"60")+CONTAR.SI(C2:C21;"90")

Y, solamente en caso de querer contar valores iguales a un número, se pueden quitar las comillas:

=CONTAR.SI(C2:C21;60)+CONTAR.SI(C2:C21;90)

• Y acaba con un ENTER normal.

En todos casos el resultado es:

7

Ahora hazlo tú:

Intenta teclear la última de las fórmulas en L6.

Por tanto, la ventaja de CONTAR.SI() es que puede contar tanto números y textos. Lo que diferencia contar números o textos cómo pongas la prueba lógica.

Por ejemplo, vamos a hacer un conteo de las facturas del cliente C001, para hacerlo bastaría con contar cuántos textos "C001" hay en la columna D. Vamos a poner la fórmula en L6 que sería:

• Haz clic en L6
• Pulsa la tecla SUPR para borrar su contenido
• Teclea la fórmula =CONTAR.SI(D2:D21;"=C001")
• Y acaba con un ENTER normal.

El resultado debería ser:

8

Pero como hemos dicho que podemos obviar el operador igual (=), la siguiente función es equivalente:

 

=CONTAR.SI(D2:D21;"C001") y ENTER

 

En cambio, no podemos quitar las comillas porque es un texto.

Pero sí que podemos poner el "C001" en una celda externa, por tanto la siguiente función es equivalente:

 

=CONTAR.SI(D2:D21;J6) y ENTER

 

En los tres casos ha de dar como resultado:

8

Ahora hazlo tú:

Intenta teclear esta última fórmula en L6. Una vez tecleada no la borres, ya que la usaremos en el siguiente apartado.

 

Compruébalo mirando cuántos C001 aparecen en la columna D. Quizás de las 3 funciones equivalentes la más útil sea la tercera ya que, cambiando el contenido de la celda J6 por C001, C002, C003, etc., obtendremos el conteo de las facturas del cliente que haya en la celda J6.

Ahora hazlo tú:

En la hoja "Resúmenes" cuenta en la celda L10 las facturas pagadas que son las que tienen un texto "SI" (un sí afirmativo pero sin tilde) en la columna F. El resultado debería ser:

5

 

Te damos una pista:

Clic aquí para ver

 

Te damos la solución:

Clic aquí para ver

 

Ahora hazlo tú:

Seguimos en el archivo "MatricesTipo3.xls" y vamos a la hoja "Pagar". Necesitamos contar cuántas personas han trabajado 40 ó más horas. Pon el conteo en la celda F3. El resultado debería ser:

6

 

Te damos una pista:

Clic aquí para ver

 

Te damos la solución:

Clic aquí para ver

 

Función SUMAR.SI() para versiones Excel 2003 y posteriores

Es una función de resumen de rangos que mezcla la suma con una sola prueba lógica. Esta función pueden sustituir a =SUMA(SI()) que hemos visto en el apartado anterior para sumar algunos datos pero es menos potente, si bien más simple de usar. Veremos su sintaxis y luego unos ejemplos, ya que esta función se usa mucho:

 

=SUMAR.SI(rango a comparar;"prueba lógica para el rango";rango a sumar)

 

SUMAR.SI() usa dos rangos: el rango que está a la izquierda de la prueba lógica que es el rango sobre el que aplicamos dicha prueba lógica, y el rango que está a la derecha de la prueba lógica que es el rango cuyas celdas sumamos.

Ha de quedar claro que SUMAR.SI() no sumará todas las celdas del rango de la derecha: solamente sumará las celdas que estén en la misma posición que las celdas del rango de la izquierda que cumplan la prueba lógica.

En el caso de que el rango a comparar y el rango a sumar sean el mismo rango, no hace falta poner el rango a sumar.

Observar que la prueba lógica va entre comillas porque la prueba lógica se ha de poner en forma de texto, pero por lo demás se usan los operadores lógicos normales = , > , >= , <  , <= , <>. En el caso del operador igual (=) puede sustituirse por no poner ningún operador.

 

Como podemos ver sólo hay 1 criterio o prueba lógica, por lo que tampoco se pueden hacer cosas sofisticadas. La función SUMAR.SI() no puede usarse para contar, como hacíamos con SUMA(SI(...;1;0)), es decir, no podemos usar ningún "truco" para hacer conteos usando la función SUMAR.SI().

 

SUMAR.SI() tampoco es una función estrictamente matricial:

 

 

Pero a pesar de ello, en este Minicurso la consideraremos como matricial por su capacidad de trabajar ordenadamente con rangos.

Vamos a aclararlo con un ejemplo. Seguimos en el archivo "MatricesTipo3.xls" en la hoja "Resúmenes" y vamos a sumar la cantidad (columna E) de las facturas de 30 días, o sea, las facturas que en la columna C tengan un 30. Poner provisionalmente en L6 la siguiente fórmula:

• Haz clic en L6
• Teclea la fórmula =SUMAR.SI(C2:C21;"=30";E2:E21)
• Y acaba con un ENTER normal.

El resultado debería ser:

9474

Para ver más claramente qué celdas del rango E2:E21 ha sumado la función SUMAR.SI(), te las mostramos coloreadas en verde:

 

 

Como hemos dicho, la fórmula =SUMAR.SI(C2:C21;"=30";E2:E21) suma las celdas de la columna E que están en la misma posición que los 30's de la columna C.

 

Mira cómo se hace:

 

Recuerda que cuando el operador lógico es un igual (=) no hace falta poner el signo igual (=) si no quieres y, si además de ser un igual (=), la prueba lógica es para comparar números, podemos obviar las comillas. Por tanto, la siguiente fórmula es equivalente a la que acabáis de teclear en L6:

 

=SUMAR.SI(C2:C21;30;E2:E21)

 

Ahora, vamos a sumar la cantidad (columna E) de las facturas del cliente C001, o sea, las facturas que en la columna D tengan el texto C001. Pon en L6 la siguiente fórmula:

• Haz clic en L6
• Pulsa la tecla SUPR para borrar su contenido
• Teclea la fórmula =SUMAR.SI(D2:D21;"C001";E2:E21)
• Y acaba con un ENTER normal.

Y el resultado debería ser:

6.222

Lo que ha hecho la función SUMAR.SI() es sumar las cantidades de la columna E de las facturas que en la columna D tengan C001. Para que funcione bien, los dos rangos han de tener el mismo tamaño, ya que Excel va comparando por orden: mira si la primera celda del rango D2:D21 es C001 y si es C001 suma la primera celda del rango E2:E21, si la 2ª celda del rango D2:D21 es C001 suma la 2ª celda del rango E2:E21, si la 3ª celda del rango D2:D21 es C001 suma la 3ª celda del rango E2:E21…. y así sucesivamente. Aquí te mostramos en color verde las celdas de la columna E sumadas:

 

Al igual que con CONTAR.SI() podemos usar el criterio en una celda externa, por ejemplo, podemos poner C001 en J6 y poner esta fórmula en M6:

• Haz clic en M6
• Teclea la fórmula =SUMAR.SI(D2:D21;J6;E2:E21)
• Y acaba con un ENTER normal.

En principio el resultado es el mismo, 6.222, pero si ahora vais cambiando J6 por C002, C003, C004…. Irá dando la suma de las cantidades de las facturas de esos clientes, que serían respectivamente: 2.460, 1.237, 920 ….

 

También, al igual que con CONTAR.SI(), si te piden que sumes las facturas de los clientes C001, C002 "y" C003, recuerda que muchas veces en el lenguaje hablado, cuando se dice la palabra "y", en realidad se está diciendo "más" o "sumadas". Por tanto,  en este caso concreto, te estarían pidiendo que sumes las facturas de los clientes C001, más las facturas de C002 más las facturas de C003. En tal caso deberías seguir estos pasos:

• Haz clic en una celda sin usar, por ejemplo en J15
• Teclea la fórmula:

=SUMAR.SI(D2:D21;"C001";E2:E21)+SUMAR.SI(D2:D21;"C002";E2:E21)+SUMAR.SI(D2:D21;"C003";E2:E21)

• Y acaba con un ENTER normal.

El resultado debería ser:

9.919,00

Que corresponde a la suma de estas cantidades:

 

 

Por último, acabaremos calculando el promedio de las cantidades de las facturas, en la celda N6 tendremos que poner:

=M6/L6

Recuerda que en L6 tienes el conteo de facturas que hicimos en el apartado anterior. Si no lo tienes teclea en L6 la fórmula =CONTAR.SI(D2:D21;J6) acabada con ENTER. Y en M6 tienes la suma =SUMAR.SI(D2:D21;J6;E2:E21) acabada en ENTER. El resultado en N6 debería ser: 778 para C001, 615 para C002, 1237 para C003... En el siguiente apartado veremos una función que simplifica este cálculo.

Ahora hazlo tú:

Seguimos en el archivo "MatricesTipo3.xls" en la hoja "Pagar". Nos piden en la celda F3 la suma de las horas que han trabajado las personas que cobran 20 ó menos euros de Precio hora. El resultado debería ser:

315

 

Te damos una pista:

Clic aquí para ver

 

Te damos la solución:

Clic aquí para ver

 

Función PROMEDIO.SI() para versiones Excel 2007 y posteriores (tema opcional)

Siguiendo con el ejemplo anterior, en el archivo "MatricesTipo3.xls" en la hoja "Resúmenes", si estamos usando Excel 2003 y queremos calcular en la celda N6 el promedio de las facturas del cliente C001, hemos tenido que poner en la celda N6 la fórmula =M6/L6 y, para poder crear esta fórmula, hemos tenido que poner previamente en L6 la fórmula =CONTAR.SI(D2:D21;J6) y en M6 la fórmula =SUMAR.SI(D2:D21;J6;E2:E21).

Pero a partir de la versión Excel 2007, ya podemos usar directamente la función PROMEDIO.SI() y ya no hace falta usar SUMAR.SI() y CONTAR.SI() y luego hacer la división. La sintaxis de PROMEDIO.SI() es idéntica a la de SUMAR.SI(), la única diferencia es que calcula el promedio en vez de la suma. Este tema es opcional porque Excel 2007 también tiene la versión PROMEDIO.SI.CONJUNTO() que puede sustituir a la función PROMEDIO.SI().

 

Este es un tema opcional. Haz clic aquí para mostrar/ocultar el tema.

 

_{Continua en la siguiente entrega. Puedes también ir al Indice de los Minicursos o Volver arriba.}