Método 1. Fórmulas
Comecemos, para quentar, coa opción máis sinxela: as fórmulas. Se temos unha pequena táboa ordenada por data como entrada, entón para calcular o total acumulado nunha columna separada, necesitamos unha fórmula elemental:
A principal característica aquí é a complicada fixación do intervalo dentro da función SUMA: a referencia ao principio do intervalo faise absoluta (con signos de dólar) e ata o final relativa (sen dólares). En consecuencia, ao copiar a fórmula a toda a columna, obtemos un intervalo de expansión, cuxa suma calculamos.
As desvantaxes deste enfoque son obvias:
- A táboa debe estar ordenada por data.
- Ao engadir novas filas con datos, a fórmula terá que ampliarse manualmente.
Método 2. Táboa dinámica
Este método é un pouco máis complicado, pero moito máis agradable. E para agravar, consideremos un problema máis grave: unha táboa de 2000 filas de datos, onde non hai unha clasificación pola columna da data, pero hai repeticións (é dicir, podemos vender varias veces no mesmo día):
Convertemos a nosa táboa orixinal nun atallo de teclado "intelixente" (dinámico). Ctrl+T ou equipo Inicio - Formato como unha táboa (Inicio — Formato como táboa), e despois construímos unha táboa dinámica sobre ela co comando Inserir - Táboa dinámica (Inserir — Táboa dinámica). Poñemos a data na área de filas do resumo e o número de bens vendidos na área de valores:
Ten en conta que se tes unha versión non moi antiga de Excel, as datas agrúpanse automaticamente por anos, trimestres e meses. Se necesitas unha agrupación diferente (ou non o necesitas en absoluto), podes solucionalo facendo clic co botón dereito en calquera data e seleccionando os comandos Agrupar / Desagrupar (Agrupar/Desagrupar).
Se queres ver os totais resultantes por períodos e o total acumulado nunha columna separada, ten sentido colocar o campo na área de valores vendido de novo para obter un duplicado do campo: nel activaremos a visualización dos totais acumulados. Para iso, fai clic co botón dereito no campo e selecciona o comando Cálculos adicionais - Total acumulado (Mostrar valores como — Totais correntes):
Alí tamén podes seleccionar a opción de crecer os totais en porcentaxe, e na seguinte xanela tes que seleccionar o campo para o que se destinará a acumulación; no noso caso, este é o campo de data:
As vantaxes deste enfoque:
- Léase rapidamente unha gran cantidade de datos.
- Non hai que introducir fórmulas manualmente.
- Ao cambiar os datos de orixe, abonda con actualizar o resumo co botón dereito do rato ou co comando Datos - Actualizar todo.
As desvantaxes derivan do feito de que este é un resumo, o que significa que non podes facer o que queiras nel (inserir liñas, escribir fórmulas, construír diagramas, etc.) xa non funcionará.
Método 3: Power Query
Carguemos a nosa táboa "intelixente" con datos de orixe no editor de consultas de Power Query mediante o comando Datos: da táboa/rango (Datos: da táboa/rango). Nas últimas versións de Excel, por certo, cambiou o nome, agora chámase Con follas (Da folla):
Despois realizaremos os seguintes pasos:
1. Ordena a táboa en orde ascendente pola columna da data co comando Ordenar ascendente na lista despregable de filtros na cabeceira da táboa.
2. Un pouco máis tarde, para calcular o total acumulado, necesitamos unha columna auxiliar co número de fila ordinal. Engadímolo co comando Engadir columna - Columna de índice - Desde 1 (Engadir columna — Columna de índice — De 1).
3. Ademais, para calcular o total acumulado, necesitamos unha referencia á columna vendido, onde se atopan os nosos datos resumidos. En Power Query, as columnas tamén se denominan listas (lista) e para obter unha ligazón a ela, fai clic co botón dereito do rato na cabeceira da columna e selecciona o comando Detalle (Mostrar detalle). Na barra de fórmulas aparecerá a expresión que necesitamos, composta polo nome do paso anterior #"Índice engadido", de onde tomamos a táboa e o nome da columna [Vendas] desta táboa entre corchetes:
Copia esta expresión no portapapeis para usala posteriormente.
4. Elimina o último paso máis innecesario vendido e engade no seu lugar unha columna calculada para calcular o total acumulado co comando Engadir unha columna - Columna personalizada (Engadir columna — Columna personalizada). A fórmula que necesitamos será así:
Aquí a función Lista.Intervalo toma a lista orixinal (columna [Vendas]) e extrae elementos del, comezando polo primeiro (na fórmula, este é 0, xa que a numeración en Power Query parte de cero). O número de elementos a recuperar é o número de fila que tomamos da columna [Índice]. Polo tanto, esta función para a primeira fila só devolve unha primeira cela da columna vendido. Para a segunda liña, xa as dúas primeiras celas, para a terceira, as tres primeiras, etc.
Ben, entón a función Lista.Suma suma os valores extraídos e obtemos en cada fila a suma de todos os elementos anteriores, é dicir, o total acumulado:
Queda por eliminar a columna Índice que xa non necesitamos e cargar os resultados de novo a Excel co comando Inicio - Pechar e cargar.
O problema está resolto.
Rápido e furioso
En principio, isto podería ser detido, pero hai unha pequena mosca na pomada: a solicitude que creamos funciona á velocidade dunha tartaruga. Por exemplo, no meu PC non é o máis débil, unha táboa de só 2000 filas é procesada en 17 segundos. E se hai máis datos?
Para acelerar, podes usar o almacenamento en búfer usando a función especial List.Buffer, que carga a lista (lista) que se lle dá como argumento na RAM, o que acelera moito o acceso a ela no futuro. No noso caso, ten sentido almacenar a lista #"Índice engadido"[Vendido], á que Power Query ten que acceder ao calcular o total acumulado en cada fila da nosa táboa de 2000 filas.
Para iso, no editor de Power Query da pestana Principal, faga clic no botón Editor avanzado (Inicio – Editor avanzado) para abrir o código fonte da nosa consulta na linguaxe M integrada en Power Query:
E despois engade alí unha liña cunha variable A miña lista, cuxo valor é devolto pola función de almacenamento no búfer, e no seguinte paso substituímos a chamada á lista por esta variable:
Despois de facer estes cambios, a nosa consulta será significativamente máis rápida e atenderá a unha táboa de 2000 filas en só 0.3 segundos.
Outra cousa, non? 🙂
- Gráfico de Pareto (80/20) e como construílo en Excel
- Busca de palabras clave en texto e búfer de consultas en Power Query