Ante una nueva consulta efectuada por un lector habitual de mi blog, estimo necesario levantar esta entrada, la que vendría a ser un "mix" de distintos conceptos ya tratados, con el agregado de nuevas y frescas ideas.
Aquí aparecerán otra vez las poco conocidas "funciones matriciales", aquellas que se ingresan en la celda con la combinación de las teclas "ctrol + alt + enter".
No es un tema menor: la suma en Excel es un procedimiento por demás de sencillo, como así también determinar los máximos y mínimos de un rango de datos. Hasta nos brinda la muy útil =SUMAR.SI(rango, criterio, [rango de suma]), la cual solo sumará si se cumple una condición determinada:
Aquí aparecerán otra vez las poco conocidas "funciones matriciales", aquellas que se ingresan en la celda con la combinación de las teclas "ctrol + alt + enter".
No es un tema menor: la suma en Excel es un procedimiento por demás de sencillo, como así también determinar los máximos y mínimos de un rango de datos. Hasta nos brinda la muy útil =SUMAR.SI(rango, criterio, [rango de suma]), la cual solo sumará si se cumple una condición determinada:
primer argumento: que rango deseo evaluar / segundo argumento: el dato que tiene que coincidir / tercer argumento: que rango sumo. calculen "a mano" los puntos totales de cada jugador y verán que la función (valga la redundancia) funciona correctamente.
[+/-] Ver el resto / OcultarEstimo que la función arriba descripta es conocida por todos, si alguno desea ampliar al respecto espero su mensaje.
Ahora las cosas se complican si, para cualquier tipo de función, necesitamos evaluar varias condiciones. En el ejemplo de arriba si algún valor de la columna B es igual al segundo argumento, entonces suma el valor que dicho registro posea en la columna C. O sea, una sola condición.
¿Y si hay mas "cosas" para evaluar? Y aquí comienzan los problemas, a los cuales les iremos brindando sus respectivas soluciones.
Imaginemos que necesitamos obtener el puntaje mínimo de un jugador determinado, por ejemplo el mío. ¿Como se lo hago entender a Excel? Para sorpresa de todos, se le pregunta de la misma forma que a una persona cualquiera: anotame cual es menor valor de la tabla, correspondiente a "damian".
¿Que haría una persona para resolver nuestro pedido? Iría recorriendo uno a uno los registros (filas) de esa tabla, si en la columna B aparece "damian" anota el valor de la columna C. Sigue y si vuelve a ver a "damian", mira que valor hay en C: si es menor al anterior, lo guarda y sigue. Y así hasta terminar con todos los registros. Al final, nos anotará el menor valor detectado.
Y eso es lo que hace una función matricial: recorre un rango, evalúa las condiciones que le dimos, va guardando los resultados parciales y, finalmente, nos devuelve el resultado.
Yendo a la sintaxis de la función, sería:
=MIN(SI((B2:B10="damian");C2:C10))
noten que no es muy complicado: si alguna celda del rango B2:B10 es igual a "damian", andá guardando el menor valor que encuentres en C2:C10. O sea que MIN() solo se aplicará a todas aquellas celdas cuyo contenido sean igual a "damian", omitiendo cualquier otro.
En la siguiente imagen se aprecia el resultado:
exacto, el menor valor correspondiente a "damian" es 2.
Vamos con otro jugador:
sip: el puntaje mas bajo de "abril" es 10. Noten como el menor valor de toda la tabla es 2, pero la función lo omitió, ya que solo le estoy preguntado por "abril"
Entonces: anidé dos funciones. Dentro de MIN() coloqué una función SI(), que es la que lleva a cabo la evaluación. Si la condición se cumple (B2:B10="abril"), entonces calcula sobre el rango especificado, en este caso, C2:C10.
Mi lector tiene otro problema muy común: quiere omitir los ceros. Y es todo un problema, por que si hay algún puntaje (columna C) igual a cero, siempre lo devolverá como menor valor (salvo que estemos jugando al "chinchon" y acepte números negativos). En mas de una ocasión necesitamos conocer cual es el valor mínimo de una serie de datos, omitiendo los ceros. Así que las cosas se complican un poco: le tengo que decir a mi ayudante que me anote el menor puntaje obtenido por "barbara", si es mayor a cero.
Con lo aprendido hasta el momento el resultado sería:
el menor puntaje de "barbara" es cero, pero yo quiero que me de el menor, siempre y cuando sea mayor a cero. (en este ejemplo, 5)
Solo debemos agregar otro SI() a la función que ya vimos:
=MIN(SI((B2:B10)="barbara";SI((C2:C10>0);C2:C10)))
Igual igual a la anterior, solo que agregamos una nueva condición: al margen de evaluar que en B2:B10 esté "barbara", le pedimos que analice si en C2:C10 el mínimo valor correspondiente a esa jugadora sea mayor a cero.
Vamos a la imagen:
en rojo les resalté los ceros y hasta un negativo. en todos los casos nuestra función los omite, tal cual se lo indicamos.
Veamos un ejemplo mas complejo, así lo analizan y luego pueden implementar en sus proyectos, de acuerdo a las distintas necesidades que se presenten. Creamos una nueva tabla, en la cual se exponen los vendedores de nuestra empresa y sus respectivas ventas, discriminadas por zona. Esta novedad (la zona de venta) es otra variable a considerar. Calcularemos el total, maximos, minimos y promedio de cada vendedor, de acuerdo a su zona. También contaremos la cantidad de ventas efectuadas. Como se trata de un trabajo que entregaremos a otros usuarios, crearemos una interfaz para que estos no se equivoquen y no tengan que modificar las funciones manualmente. Utilizaremos validación de datos para los vendedores y zonas.
Observemos el resultado final: en F2 y F3, respectivamente, nuestro usuario ingresa el vendedor y la zona de venta. A partir de F7 le brindamos los resultados: el total, maximo, minimo, promedio y cantidad de ventas. Hacemos uso de =SUMAR.SI() para calcular el total de ventas, independientemente de la zona seleccionada:
un informe muy completo sobre la actuación del vendedor.
Si cambiamos los valores de F2 y F3, todo se actualiza automaticamente:
Las funciones utilizadas (en orden, a partir de F7) son:
=SUMA(SI((A2:A15=F2);SI((B2:B15=F3);C2:C15))) 'total x zona
=MAX(SI((A2:A15=F2);SI((B2:B15=F3);C2:C15))) 'maximo
=MIN(SI((A2:A15=F2);SI((B2:B15=F3);C2:C15))) 'minimo
=PROMEDIO(SI((A2:A15=F2);SI((B2:B15=F3);C2:C15))) 'promedio
=CONTAR(SI((A2:A15=F2);SI((B2:B15=F3);C2:C15))) 'cant.ventas
=SUMAR.SI(A2:A15;F2;C2:C15) 'suma de todas las ventas, sin importar la zona
Nada nuevo a lo anteriormente expuesto: dos condiciones, solo que agregamos CONTAR() y PROMEDIO(). Pero no me iría a dormir tranquilo si no "la complico" un poco mas, así observamos como podemos sumar condiciones a estas matriciales. Ahora nos piden los mismos cálculos, solo que si los importes vendidos superan cierto valor, el cual deberá ser ingresado en F4. Miremos un poco:
"barbara" no tiene ninguna venta superior a $2000 en la zona norte, de allí que nuestras funciones matriciales devuelvan 0.
Vean que la funcion PROMEDIO() tira un error, lo cual generé a propósito, para mostrar como lo solucionamos con el formato condicional. Seleccionamos la celda en cuestión y le decimos al formato que aplique un color de fuente blanco si se presenta un error:
Visto ya como solucionamos, de la forma mas sencilla, este problema, sigamos y apliquemos un ejemplo que no arroje errores:
"victoria" posee tres ventas en la zona oeste: dos de ellas son superiores a $1300 (F4).
Las funciones utilizadas son:
=SUMA(SI((A2:A15=F2);SI((B2:B15=F3);SI((C2:C15>F4);C2:C15))))
=MAX(SI((A2:A15=F2);SI((B2:B15=F3);SI((C2:C15>F4);C2:C15))))
=MIN(SI((A2:A15=F2);SI((B2:B15=F3);SI((C2:C15>F4);C2:C15))))
=PROMEDIO(SI((A2:A15=F2);SI((B2:B15=F3);SI((C2:C15>F4);C2:C15))))
=CONTAR(SI((A2:A15=F2);SI((B2:B15=F3);SI((C2:C15>F4);C2:C15))))
Idem a los visto hasta el momento, con el agregado de una nueva condición.
¿Se puede complicar mas? Obviamente, hasta el infinito... y mas allá. Pero lo dejaremos para futuras entradas o consultas de mis estimados lectores.
Les dejo mis saludos y el link al archivo. Suerte.
- Obtener enlace
- Correo electrónico
- Otras aplicaciones
Etiquetas
Funciones
Etiquetas:
Funciones
- Obtener enlace
- Correo electrónico
- Otras aplicaciones
Comentarios
Publicar un comentario