Obtener max y min según texto

. 06.14.17 #Fórmulas matriciales

Buenas noches,

Lo que solicitas puede ser resuelto con una herramienta muy poderosa que posee MS Excel: las Fórmulas Matriciales.

Es algo así como las fórmulas SUMAR. SI() o CONTAR. SI(), que suman o cuentan sobre la base de una condición, pero estas permiten agregar más condiciones y eventualmente modificar datos antes de usarlos para calcular "en el aire".

Estas fórmulas también son conocidas como CSE formulas, por Control+Shift+Enter que son las teclas que hay que presionar simultáneamente después de tipear la fórmula (no basta con Enter). Un par de llaves {} se agregarán automáticamente, encerrando la fórmula. Esto es FUNDAMENTAL para que funcione.

En tu caso tendremos que usar una batería de ellas para todo lo que solicitaste.

Sobre la base de tu planilla, que reproduzco aquí abajo:

Notarás que, a diferencia de lo que muestra tu planilla, habilité dos columnas más: Una para lo que empieza con PROD. Y otra para DIC-VENT. Y luego la TOTAL que solicitabas.

Esto te permitirá cambiar lo que quieras computar en las celdas que te marqué en verde y las fórmulas operarán sobre ese criterio. Adicionalmente si quisieras agregar otro criterio puedes insertar una columna entre ambas, copiar las fórmulas de cualquiera de ellas y calcularán similarmente sobre lo que le indiques en la celda de la fila 12.

Ahora te transcribo aquí las fórmulas de la columna F para que no tengas que tipearlas:

- Para ENCONTRADAS, la fórmula en F13 es:

=SUMA(SI(F$12=IZQUIERDA($A$3:$A$1000;LARGO(F$12));1;0))

Como te dije anteriormente, esta función se ingresa en la celda presionando Ctrl+Shift+Enter

Luego copia esta celda y pégala en la celda a la derecha (G13) y en la TOTAL (H13) coloca la suma de ambas celdas. Como muestro en la celdas a la derecha

- Para el valor MÁXIMO, la fórmula en F15, será:

=MAX(SI(F$12=IZQUIERDA($A$3:$A$1000;LARGO(F$12));$B$3:$B$1000))

Luego pegala al lado y obtendrás el máximo para el otro criterio, y a la derecha una función de máximo entre estas dos te dará el máximo en ambas.

- El Mínimo -distinto de cero- en F16 resulta de:

=MIN(SI(F$12=IZQUIERDA($A$3:$A$1000;LARGO(F$12));SI($B$3:$B$1000<>0;$B$3:$B$1000)))

Pegada al lado esta fórmula dará el mínimo de el otro criterio.

A su derecha -en la celda naranja- otra matricial te devolverá el mínimo de ambos, si no es cero:

=MIN(SI(F16:G16<>0;F16:G16))

- Para el valor medio, si bien no lo solicitaste, conviene tener la sumatoria de cada concepto (te lo marqué en amarillo).

Tal celda -F19- alojará esta función:

=SUMA(SI(F$12=IZQUIERDA($A$3:$A$1000;LARGO(F$12));$B$3:$B$1000))

Al lado, lo mismo, y una totalizadora de ambas celdas a su derecha.

Luego el VALOR MEDIO (en F18) puedes obtenerlo dividiendo este valor por el conteo que tienes en la celda F13

O usar esta otra matricial:

=SI(F13;SUMA(SI(F$12=IZQUIERDA($A$3:$A$1000;LARGO(F$12));$B$3:$B$1000))/F13;0)

En esa celda obtienes el promedio de el primer criterio y a su derecha el del otro.

PERO, para obtener el promedio de ambos criterios necesitarás indefectiblemente la sumatoria que calculaste en la fila 19, pues no es lo mismo el promedio de la suma que la suma de los promedios.

En otras palabras, la fórmula a usar para el VALOR MEDIO de ambos criterios, colocada en la celda H18 es:

=SI(H13<>0;H19/H13;0)

Hasta aquí, lo que pueden resolver las fórmulas matriciales.

Para el conteo del valor que más se repite y cuántas veces ocurre, echaremos mano a una columna auxiliar - columna en celeste - que cuenta cuántas veces está cada número de la lista con una simple función:

=CONTAR.SI($B$3:$B$1000;B3)

La celda H21 traerá el mayor número de veces que se repite un valor con esta fórmula en ella:

=MAX($C$3:$C$1000)

y, finalmente, la celda F21 tendrá una fórmula que identificará cual es el valor que se repitió más veces:

=INDICE($B$3:$B$1000;COINCIDIR(H21;$C$3:$C$1000))

Bien, respuesta larga, pero lo que planteaste es complejo. Aún así creo haber cubierto todos los puntos.

.