Hablemos de los totales acumulados. Veo por el mundo de internet muchas preguntas relacionadas con los totales acumulados y por eso he decidido realizar esta entrada y mostrarles unos trucos muy válidos para su creación.
Pues allá vamos!
Imaginemos que tenemos unos datos como éstos:
Estos datos están convertidos en tablas, para su mejor uso. Si necesitas una guía para entender las tablas, pincha en este enlace. (Guía sobre las Tablas en Excel)
Ahora hagámosnos 3 preguntas sobre totales acumulados:
- Cómo obtendría un total acumulado de la cantidad? Incluso ordenando por cualquier columna, el total acumulado no debería romperse
- El total acumulado ordenado por cliente debe restablecerse cuando se cambie el nombre del cliente.
- Mantener el total de la pregunta 2,(solo para filas visibles) cuando se filtra la tabla
Pues veamos las soluciones!
Solución 1: Total acumulado
La primera fórmula a usar es la siguiente.
=SUMA(INDICE([Cantidad],1):[@Cantidad])
Con esta fórmula los totales acumulados funcionarían bien incluso si se ordena por cualquiera de las otras columnas.
Si te estás preguntando si podría haber escrito una fórmula (mucho más ...) más simple en comparación con esto. ¡Sí! tienes muchas razón, pero veremos la esencia de esta fórmula cuando pasemos a los 2 siguientes problemas. Pero me gustaría explicar la fórmula aquí
- La función INDICE (para entender esta función mejor te recomiendo este enlace Función INDICE en Excel ) siempre devuelve el primer valor en la columna de cantidad
- El [@Cantidad] se refiere a la fila actual
- Los 2 puntos en el medio crea un rango desde el primer valor de la cantidad hasta la fila actual
- Y la función SUMA toma la suma de ese rango. Puro y simple (puede que no sea simple, pero sí seguro:D)!
Solución 2: Total acumulado por cliente
Se pone un poco complicado aquí. Ahora tenemos que obtener el total acumulado y restablecer el total cuando cambia el nombre del cliente. Se recomienda que la tabla esté ordenada por cliente.
La fórmula para esta ocasión sería:
=SUMA(INDICE([Cantidad],COINCIDIR([@Cliente],[Cliente],)):[@Cantidad])
Déjame explicar esto:
- La función INDICE funciona un poco diferente de lo que era antes. Si el nombre del cliente cambia (digamos Data Tronix), el primer valor devuelto por INDICE también corresponderá a ese cliente (Data Tronix), esto es lo que hace la función COINCIDIR. Para entender mejor cómo la función INDICE se puede asociar con COINCIDIR, aqui os un enlace muy válido (INDICE + COINCIDIR)
- [@Cantidad] y [@Cliente] se refieren a la fila actual
- La función SUMA envuelve esta fórmula para dar la suma.
Solución 3: Total acumulado por cliente y por filtro
Ahora, la ejecución no solo debe restablecerse en el cliente, sino que también debe mostrarse sólo para las filas visibles, cuando se aplica el filtro.
La fórmula para esta ocasión sería:
=AGREGAR(9,5,INDICE([Cantidad],COINCIDIR([@Cliente],[Cliente],)):[@Cantidad])
La fórmula puede parecer compleja, pero hay una pequeña diferencia
- Todo sigue igual, pero
- En lugar de utilizar la función SUMA, utilizo la función AGREGAR, que es capaz de manejar filas filtradas 😎
Voy a explicar un momento la primera parte de la función AGREGAR. El porqué de ese 9 y 5.
La primera parte de esta función indica que tipo de operación queremos que realice.
Como se ve el 9 corresponde a la SUMA, pero si se quisiese el PROMEDIO pues sería 1.
La segunda parte de la función es para restringir el rango a usar.
Como se puede ver, para omitir las filas ocultas se selecciona el 5, pero si también nos gustaría omitir los valores de error, pues seleccionaríamos el 7.
Nota al margen: es posible que se prefiera reemplazar la función SUMA por la función AGREGAR en todas las partes, ya que es más robusta y sólo considerará las filas visibles
Te ha gustado el artículo?
No te olvides de mostrar tu gratitud hacia el autor (yo) mediante una de las 3 siguientes acciones:
1. Puedes dejar una donación pinchando en la siguiente imagen.
2. Puedes hacer clic en los anuncios mostrados en la página
3. Puedes recomendar el artículo en Google haciendo clic en g+1 (justo después del artículo), también puedes compartirlo en Facebook, y/o dejar comentarios.
Quieres aprender más en Excel o tienes algún problema que necesita solución?
Puedo ofrecer servicios de consultoría así como cursos personalizados online. Mírate el siguiente enlace si estás interesado/a.
El Sabio de Excel busca colaborades del blog
Si te interesa compartir tu conocimiento sobre Excel de tal manera que puedas ayudar a otras personas, por favor mándame un email a elsabiodeexcel@gmail.com
Subscríbete y recibe todos las entradas por email
Para ello regístrate suministrando tu email en el sitio "Recibe los trucos en tu email" del menú de la derecha.
Para ello regístrate suministrando tu email en el sitio "Recibe los trucos en tu email" del menú de la derecha.
No hay comentarios:
Publicar un comentario