Método 1. Fórmulas
Comencemos, para calentar, con la opción más simple: fórmulas. Si tenemos una pequeña tabla ordenada por fecha como entrada, entonces para calcular el total acumulado en una columna separada, necesitamos una fórmula elemental:
La característica principal aquí es la complicada fijación del rango dentro de la función SUM: la referencia al comienzo del rango se hace absoluta (con signos de dólar) y al final, relativa (sin dólares). En consecuencia, al copiar la fórmula a toda la columna, obtenemos un rango de expansión, cuya suma calculamos.
Las desventajas de este enfoque son obvias:
- La tabla debe estar ordenada por fecha.
- Al agregar nuevas filas con datos, la fórmula deberá extenderse manualmente.
Método 2. Tabla dinámica
Este método es un poco más complicado, pero mucho más agradable. Y para empeorar, consideremos un problema más serio: una tabla de 2000 filas de datos, donde no hay clasificación por columna de fecha, pero hay repeticiones (es decir, podemos vender varias veces el mismo día):
Convertimos nuestra tabla original en un atajo de teclado "inteligente" (dinámico) Ctrl+T o equipo Inicio – Formato como tabla (Inicio — Formatear como tabla), y luego construimos una tabla dinámica con el comando Insertar – Tabla dinámica (Insertar: tabla dinámica). Ponemos la fecha en el área de filas en el resumen y la cantidad de bienes vendidos en el área de valores:
Tenga en cuenta que si tiene una versión no muy antigua de Excel, las fechas se agrupan automáticamente por años, trimestres y meses. Si necesita una agrupación diferente (o no la necesita en absoluto), puede arreglarla haciendo clic derecho en cualquier fecha y seleccionando comandos Agrupar / Desagrupar (Agrupar / Desagrupar).
Si desea ver los totales resultantes por períodos y el total acumulado en una columna separada, entonces tiene sentido colocar el campo en el área de valor Vendido nuevamente para obtener un duplicado del campo; en él, activaremos la visualización de los totales acumulados. Para hacer esto, haga clic derecho en el campo y seleccione el comando Cálculos Adicionales – Total Acumulativo (Mostrar valores como: totales acumulados):
Allí también puede seleccionar la opción de aumentar los totales como un porcentaje, y en la siguiente ventana debe seleccionar el campo al que irá la acumulación; en nuestro caso, este es el campo de fecha:
Las ventajas de este enfoque:
- Una gran cantidad de datos se lee rápidamente.
- No es necesario introducir fórmulas manualmente.
- Al cambiar los datos de origen, basta con actualizar el resumen con el botón derecho del mouse o con el comando Datos - Actualizar todo.
Las desventajas se derivan del hecho de que se trata de un resumen, lo que significa que no puede hacer lo que quiera en él (insertar líneas, escribir fórmulas, crear diagramas, etc.) ya no funcionará.
Método 3: consulta de poder
Carguemos nuestra tabla "inteligente" con datos de origen en el editor de consultas de Power Query usando el comando Datos: de la tabla/rango (Datos: de la tabla/rango). En las últimas versiones de Excel, por cierto, se le cambió el nombre, ahora se llama con hojas (De la hoja):
Luego realizaremos los siguientes pasos:
1. Ordene la tabla en orden ascendente por la columna de fecha con el comando Orden ascendente en la lista desplegable de filtros en el encabezado de la tabla.
2. Un poco más tarde, para calcular el total acumulado, necesitamos una columna auxiliar con el número de fila ordinal. Vamos a agregarlo con el comando Agregar columna – Columna de índice – Desde 1 (Agregar columna — Columna de índice — Desde 1).
3. Además, para calcular el total acumulado, necesitamos una referencia a la columna Vendido, donde se encuentran nuestros datos resumidos. En Power Query, las columnas también se denominan listas (lista) y para obtener un enlace, haga clic con el botón derecho en el encabezado de la columna y seleccione el comando detallando (Mostrar detalle). En la barra de fórmulas aparecerá la expresión que necesitamos, consistente en el nombre del paso anterior #”Índice agregado”, de donde tomamos la tabla y el nombre de la columna [Ventas] de esta tabla entre corchetes:
Copie esta expresión en el portapapeles para su uso posterior.
4. Eliminar innecesario más último paso Vendido y agregue en su lugar una columna calculada para calcular el total acumulado con el comando Agregar una columna: columna personalizada (Añadir columna — Columna personalizada). La fórmula que necesitamos se verá así:
Aquí la función Lista.Rango toma la lista original (columna [Ventas]) y extrae elementos de él, comenzando desde el primero (en la fórmula, este es 0, ya que la numeración en Power Query comienza desde cero). El número de elementos a recuperar es el número de fila que tomamos de la columna [Índice]. Entonces, esta función para la primera fila solo devuelve una primera celda de la columna Vendido. Para la segunda línea, ya las dos primeras celdas, para la tercera, las tres primeras, etc.
Bueno, entonces la función Lista.Suma suma los valores extraídos y obtenemos en cada fila la suma de todos los elementos anteriores, es decir, el total acumulado:
Queda por eliminar la columna Índice que ya no necesitamos y volver a subir los resultados a Excel con el comando Inicio – Cerrar y Cargar.
El problema esta resuelto.
Rápido y furioso
En principio, esto podría haberse detenido, pero hay una pequeña mosca en el ungüento: la solicitud que creamos funciona a la velocidad de una tortuga. Por ejemplo, en mi PC, que no es la más débil, una tabla de solo 2000 filas se procesa en 17 segundos. ¿Qué pasa si hay más datos?
Para acelerar, puede usar el almacenamiento en búfer usando la función especial List.Buffer, que carga la lista (lista) que se le da como argumento en la RAM, lo que acelera enormemente el acceso a ella en el futuro. En nuestro caso, tiene sentido almacenar en búfer la lista #”Índice agregado”[Vendido], a la que Power Query tiene que acceder al calcular el total acumulado en cada fila de nuestra tabla de 2000 filas.
Para ello, en el editor de Power Query en la pestaña Principal, haga clic en el botón Editor avanzado (Inicio – Editor avanzado) para abrir el código fuente de nuestra consulta en el lenguaje M integrado en Power Query:
Y luego agregue una línea con una variable allí MyList, cuyo valor es devuelto por la función de almacenamiento en búfer, y en el siguiente paso reemplazamos la llamada a la lista con esta variable:
Después de realizar estos cambios, nuestra consulta será significativamente más rápida y se ocupará de una tabla de 2000 filas en solo 0.3 segundos.
Otra cosa, ¿no? 🙂
- Gráfico de Pareto (80/20) y cómo construirlo en Excel
- Búsqueda de palabras clave en texto y almacenamiento en búfer de consultas en Power Query