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 SI CONJUNTO.

Pasamos a la segunda entrega de las funciones SI CONJUNTO. Veremos en este tema las funciones SI CONJUNTO que se añadieron a partir de Office 365 versión Enero 2016 (Versión 16.0.7070.2036), con lo que necesitaréis la versión Office 365 o posterior para poder verlas.

Nota: En el momento de crear este Minicurso aún no existía una versión de Office de escritorio independiente de Office 365 que implementase estas funciones.

 

Como explicamos en la entrega anterior, si no tienes esta versión, puedes ver el uso de estas fórmulas en OneDrive de manera gratuita, pero las fórmulas estarán en inglés. Volvemos a poner aquí la manera de obtener una cuenta de OneDrive gratuita:

 

Mira cómo se hace:

 

Funciones MAX.SI.CONJUNTO() y MIN.SI.CONJUNTO() para versiones Excel 2016 y posteriores

Sus sintaxis son totalmente igual que PROMEDIO.SI.CONJUNTO(), la diferencia es que MAX.SI.CONJUNTO() retorna un sólo valor que es el valor mayor de las celdas del primer rango y MIN.SI.CONJUNTO() retorna un solo valor que es el valor menor de las celdas del primer rango:

=MAX.SI.CONJUNTO(rango de datos en el que se busca el valor mayor;rango de datos;"criterio a comprobar en ese rango";otro rango de datos;"otro criterio para ese rango";otro rango de datos;"otro criterio para ese rango";...)

=MIN.SI.CONJUNTO(rango de datos en el que se busca el valor menor;rango de datos;"criterio a comprobar en ese rango";otro rango de datos;"otro criterio para ese rango";otro rango de datos;"otro criterio para ese rango";...)

 

Nota: En inglés es:

=MAXIFS(rango,rango,"criterio",rango,"criterio",...) y =MINIFS(rango,rango,"criterio",rango,"criterio",...)

 

MAX.SI.CONJUNTO() busca el valor más grande que hay en el primer rango de datos, es decir, en el primer parámetro de la función. El resto de los criterios indican qué filas de dicho rango usará Excel para buscar el valor más grande. La función  MIN.SI.CONJUNTO() hace lo mismo pero busca el valor más pequeño. Observa que a la derecha de cada rango está el criterio a comprobar en ese rango. Se puede poner hasta 127 rangos con sus correspondientes 127 criterios. Entre estos 127 rangos se pueden repetir rangos pero poniendo criterios distintos.

 

Nota: en una búsqueda de un valor máximo o un valor mínimo dentro de un rango, las celdas de dicho rango con valores cero influyen en el resultado final. Por tanto, es conveniente que te acostumbres a usar las funciones MAX y MIN de manera diferente a la función suma, según desees que los ceros influyan o no en el resultado. Las celdas vacías no influyen en el cálculo del máximo o del mínimo.

 

Los criterios siguen la misma sintaxis que hemos visto hasta ahora: los criterios (pruebas lógicas) van entre comillas porque las pruebas lógicas se han 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 y además puede ponerse el valor de comparación en una celda externa.

Si ya has descargado los ejemplos, abre el archivo "FuncionesSiConjunto-2.xlsx" en la hoja "MAX y MIN", que tiene que tener este aspecto:

 

 

Observa que hay unos números positivos, cero y negativos dentro del rango desde A2 hasta E11. Supón que te piden calcular el número más grande y el número más pequeño de dichos números, pero con la condición de incluir en los cálculos solamente los valores que están entre -1000 y 1000 ambos inclusive, dicho de otra manera, entre lo que hay en H4 y H5. Los valores cero han de ser incluidos en los cálculos. Sigue estos pasos:

• Haz clic en la celda H6
• Teclea la fórmula:

=MIN.SI.CONJUNTO(A2:E11;A2:E11;">="&H4;A2:E11;"<="&H5)

• Y acaba con un ENTER normal
• Haz clic en la celda H7
• Teclea la fórmula:

=MAX.SI.CONJUNTO(A2:E11;A2:E11;">="&H4;A2:E11;"<="&H5)

• Y acaba con un ENTER normal.

Nota: observa que la segunda fórmula es exactamente igual que la primera salvo que cambia la palabra MIN por MAX. Puedes aprovechar esto para crear la segunda fórmula a partir de la primera.

El resultado debería ser:

 

 

Mira cómo se hace:

 

Es decir, Excel ha incluido en los cálculos solamente los números entre -1000 y 1000 de los valores que hay en el rango A2:E11, el número más pequeño que ha hallado Excel es -950 y el más grande es 850. Las celdas que Excel ha incluido en los cálculo te las ponemos en color amarillo y el máximo y el mínimo en color verde:

 

 

Puedes ir cambiando los valores que hay en H4 y H5 y los valores mínimo y máximo irán cambiando:

 

 

Observa que los ceros están incluidos dentro de los valores que Excel incluye en los cálculos.

Nota: Tal como hemos dicho, para conseguir el mismo resultado en Excel 2003, 2007, 2010, 2013 y 2016 antes de que existiesen estas funciones, habría que haber usado las siguientes fórmulas de lógica matricial si se quiere incluir los ceros en los cálculos:

 

=MIN((A2:E11>=H4)*(A2:E11<=H5)*(A2:E11)) y CONTROL+MAYUSCULAS+ENTER

y

=MAX((A2:E11>=H4)*(A2:E11<=H5)*(A2:E11)) y CONTROL+MAYUSCULAS+ENTER

... pero el tema de la lógica matricial lo veremos más adelante.

Ahora hazlo tú:

Se trata de que cambies las dos fórmulas para que los ceros no influyan en el cálculo del máximo y del mínimo. Por ejemplo, poniendo en H4 y H5 los valores -10000 y 0 el resultado debería ser:

 

 

Te damos una pista:

Clic aquí para ver

 

Te damos la solución:

Clic aquí para ver

 

 

Nota: no existen en Excel funciones para obtener el mínimo y el máximo de un rango con un solo criterio. Es decir, tanto si queremos usar un solo criterio como si queremos usar varios criterios, habrá que usar siempre MAX.SI.CONJUNTO() y MIN.SI.CONJUNTO().

 

 

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