Fórmulas y Funciones Matriciales en Excel.

Operaciones matriciales de Tipo 4.

 

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 4 (fórmulas matriciales de resumen que se ponen en una celda y se copian con el cuadro de relleno).

Las hemos definido antes como:

"TIPO 4: Fórmulas matriciales de Tipo 2 ó 3 que se crean en una celda y luego se copian en un rango de celdas con el cuadro de relleno. Es lo mismo que hacemos con las funciones normales. Lo importante es que no deben confundirse con fórmulas de Tipo 1. En estos casos hay que tener en cuenta cómo se ponen los $ en los rangos."

En este Minicurso hemos clasificado las fórmulas matriciales como de Tipo 1, Tipo 2, Tipo 3 y Tipo 4. Pero esta clasificación solamente queda dentro de este Minicurso. Microsoft no hace esta clasificación. Microsoft subdivide las fórmulas matriciales en multicelda y unicelda. Las multicelda equivalen a nuestra clasificación de Tipo 1, y las unicelda entrarían en las de Tipo 2 y 3 pero también en el Tipo 4 que vamos a ver ahora.

Siempre que hablamos de matrices unicelda, es lo mismo que hablar de una función de resumen (suma, promedio, conteo...) dentro de la cual ponemos una fórmula matricial multicelda o bien varios rangos y criterios.

En las fórmulas matriciales de Tipo 4, lo que vamos a hacer es teclear, en una sola celda, una función de resumen unicelda de Tipo 2 o de Tipo 3. Pero entonces, lo que haremos será copiar con el cuadro de relleno esa fórmula con la finalidad de repetir esa fórmula en varias celdas porque queremos tener varias fórmulas de resumen.

 

De acuerdo con nuestra clasificación en este Minicurso, las fórmulas matriciales de Tipo 4 están formadas por varias funciones de resumen matriciales pero que, con la finalidad de ahorrarnos el trabajo de teclearlas todas una por una, solamente teclearemos la primera de ellas y el resto las copiaremos con el cuadro de relleno. Para que esto funcione correctamente, habrá que poner referencias absolutas en los rangos.

 

Vamos a ver cómo se hacen.

 

Si ya has descargado los ejemplos, abre el archivo "MatricesTipo4.xls" en la hoja "Resúmenes", que tiene que tener este aspecto:

 

 

En el tema anterior hemos usado la función CONTAR.SI() como función de Tipo 3. Esto nos permitía contar los clientes. Por ejemplo, cuántos clientes eran el "C001". Ahora vamos a hacer lo mismo pero en muchas celdas a la vez y tecleando la fórmula solamente una vez. Sigue estos pasos:

• Haz un clic en K5
• Teclea la fórmula =CONTAR.SI(D2:D21;J5)
• Acaba con un ENTER normal.

Es de Tipo 3, por tanto, acaba con un ENTER normal. Al hacerlo tenemos el primer resumen que es el conteo de las facturas que hay del cliente "C001":

 

 

Lo que pasa es que con esta fórmula de resumen unicelda (de Tipo 3 en este caso) no me basta. Necesito más fórmulas de resumen en el resto de la columna hasta K10 para el resto de los clientes.

Ahora bien, como que estas fórmulas que necesito hasta K10 cumplen el mismo patrón que la primera fórmula en K5, no necesito teclearlas todas. Puedo hacer clic en K5 y copiar la fórmula hasta K10, así me ahorro de volver a teclear cada función de resumen. Es decir, lo que estoy haciendo es ahorrarme el trabajo de teclear, no tiene nada que ver con hacer una matriz desde K5 hasta K10.

Por consiguiente, hemos de respetar el uso de los $, ya que en este caso rellenaremos el rango hasta K10 con la misma fórmula y debemos cumplir las reglas de referencias absolutas y relativas. Es exactamente lo mismo que se hace con las fórmulas normales no matriciales, no hay más misterio. Para conseguirlo, haz los siguientes pasos:

• Haz clic en K5
• Coloca el puntero del mouse en la barra de fórmulas y selecciona la parte de la fórmula que aquí resaltamos en color:

=CONTAR.SI(D2:D21;J5)

• Pulsa la tecla F4, la fórmula cambia a =CONTAR.SI($D$2:$D$21;J5). Recuerda que la tecla F4 convierte los rangos relativos en rangos absolutos sin necesidad de que tengas que teclear los $ a mano.

Nota: esta fórmula la podías haber tecleado poniendo los dólares ($) directamente a mano tranquilamente.

• Y acaba con un ENTER normal

El resultado en K5 no cambia porque en realidad sigue siendo la misma fórmula de antes, pero ahora con los rangos como referencia absoluta. Ahora esta fórmula ya está lista para ser copiada hasta K10. Sigue estos pasos:

• Haz clic en K5
• Coloca el puntero del mouse en el cuadro de relleno del selector de celda (parte inferior derecha de K5). El puntero del mouse toma forma de una cruz pequeña.
• Arrastra con el mouse hasta K10 (o si quieres haz doble clic en el cuadro de relleno)

El resultado debería ser:

 

 

Lo que me da cuántas facturas tiene cada cliente, desde el cliente C001 hasta el cliente C006.

Y las fórmulas en cada celda deberían ser las siguientes (destacamos en color la parte que cambia en cada fórmula):

 

=CONTAR.SI($D$2:$D$21;J5)
=CONTAR.SI($D$2:$D$21;J6)
=CONTAR.SI($D$2:$D$21;J7)
=CONTAR.SI($D$2:$D$21;J8)
=CONTAR.SI($D$2:$D$21;J9)
=CONTAR.SI($D$2:$D$21;J10)

 

Lo que hemos hecho, es en realidad lo habitual para rellenar una columna con la misma fórmula. Hemos puesto en la celda K5 una sola función de resumen. Entonces, dado que necesitábamos la misma función de resumen para el resto de las columna, lo que hemos hecho ha sido copiarla. En realidad, cada una de las celdas desde K5 hasta K10 contiene una función de resumen independiente, lo que ocurre es que en vez de teclearlas todas una por una, solamente he tecleado la primera fórmula y he usado el cuadro de relleno para ahorrarme trabajo. Es igual que cuando queremos rellenar una columna con funciones no matriciales. Eso sí, es necesario poner bien los $ para que, al copiar la fórmula, no cambien los rangos.

 

Por tanto, una fórmula matricial de Tipo 4 no es más que hacer siempre este proceso para tener muchas fórmulas de resumen: teclear solamente la primera fórmula de resumen matricial con los $ en los rangos y copiar dicha fórmula en el resto de las celdas.

 

 

Mira cómo se hace:

 

Ahora hazlo tú:

Supón que te piden que, en las celdas desde L5 hasta L10, calcules la suma de las facturas no pagadas y vencidas para cada cliente. Los clientes están en las celdas desde J5 hasta J10. Cada suma de cada cliente ha de estar en la misma fila que su correspondiente cliente en la columna J. Teclea solamente en L5 la fórmula para el cliente C001 y las demás fórmulas para los demás clientes cópialas de esta primera fórmula.

Ten en cuenta lo siguiente: Has de usar una fórmula SI CONJUNTO. La suma se calcula en base a los valores de la columna E. El cliente es el que está en la columna D. "No pagadas" significa que haya un "NO" en la columna F. "Vencidas" significa que haya una "X" en la columna G. El resultado debería ser:

 

 

Te damos una pista (1):

Clic aquí para ver

 

Te damos una pista (2):

Clic aquí para ver

 

Te damos la solución:

Clic aquí para ver

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