Mètode 1. Fórmules
Comencem, per escalfar, amb l'opció més senzilla: les fórmules. Si tenim una petita taula ordenada per data com a entrada, per calcular el total acumulat en una columna separada, necessitem una fórmula elemental:
La característica principal aquí és la difícil fixació del rang dins de la funció SUMA: la referència al començament del rang es fa absoluta (amb signes de dòlar) i fins al final, relativa (sense dòlars). En conseqüència, en copiar la fórmula a tota la columna, obtenim un rang expansiu, la suma del qual calculem.
Els desavantatges d'aquest enfocament són evidents:
- La taula s'ha d'ordenar per data.
- En afegir noves files amb dades, la fórmula s'haurà d'ampliar manualment.
Mètode 2. Taula dinàmica
Aquest mètode és una mica més complicat, però molt més agradable. I per agreujar, considerem un problema més greu: una taula de 2000 files de dades, on no hi ha ordenació per la columna de data, però hi ha repeticions (és a dir, podem vendre diverses vegades el mateix dia):
Convertim la nostra taula original en una drecera de teclat "intel·ligent" (dinàmica). Ctrl+T o equip Inici – Format com a taula (Inici — Format com a taula), i després construïm una taula dinàmica amb l'ordre Insereix - Taula dinàmica (Insereix — Taula dinàmica). Posem la data a l'àrea de files del resum i el nombre de mercaderies venudes a l'àrea de valors:
Tingueu en compte que si teniu una versió d'Excel no gaire antiga, les dates s'agrupen automàticament per anys, trimestres i mesos. Si necessiteu una agrupació diferent (o no la necessiteu gens), podeu solucionar-la fent clic amb el botó dret a qualsevol data i seleccionant les ordres. Agrupar / Desagrupar (Agrupar/Desagrupar).
Si voleu veure tant els totals resultants per períodes com el total acumulat en una columna separada, té sentit llançar el camp a l'àrea de valors. Venut de nou per obtenir un duplicat del camp: en ell activarem la visualització dels totals acumulats. Per fer-ho, feu clic amb el botó dret al camp i seleccioneu l'ordre Càlculs addicionals – Total acumulat (Mostra els valors com a — Totals acumulats):
Allà també podeu seleccionar l'opció de créixer els totals com a percentatge, i a la finestra següent heu de seleccionar el camp al qual anirà l'acumulació; en el nostre cas, aquest és el camp de la data:
Els avantatges d'aquest enfocament:
- Es llegeix ràpidament una gran quantitat de dades.
- No cal introduir fórmules manualment.
- Quan es canvien les dades d'origen, n'hi ha prou amb actualitzar el resum amb el botó dret del ratolí o amb l'ordre Dades – Actualitza tot.
Els inconvenients es deriven del fet que es tracta d'un resum, el que significa que no hi podeu fer el que vulgueu (inserir línies, escriure fórmules, construir diagrames, etc.) ja no funcionarà.
Mètode 3: Power Query
Carreguem la nostra taula "intel·ligent" amb dades font a l'editor de consultes de Power Query mitjançant l'ordre Dades: de la taula/interval (Dades: de la taula/interval). A les últimes versions d'Excel, per cert, es va canviar el nom, ara s'anomena Amb fulles (Del full):
Després realitzarem els següents passos:
1. Ordena la taula en ordre ascendent per la columna de data amb l'ordre Ordena ascendent a la llista desplegable de filtres a la capçalera de la taula.
2. Una mica més tard, per calcular el total acumulat, necessitem una columna auxiliar amb el número de fila ordinal. Afegim-ho amb l'ordre Afegeix una columna - Columna d'índex - Des de 1 (Afegeix columna — Columna índex — Des de 1).
3. A més, per calcular el total acumulat, necessitem una referència a la columna Venut, on es troben les nostres dades resumides. A Power Query, les columnes també s'anomenen llistes (llista) i per obtenir-hi un enllaç, feu clic amb el botó dret a la capçalera de la columna i seleccioneu l'ordre Detall (Mostra el detall). A la barra de fórmules apareixerà l'expressió que necessitem, formada pel nom del pas anterior #"Índex afegit", d'on agafem la taula i el nom de la columna [Vendes] d'aquesta taula entre claudàtors:
Copieu aquesta expressió al porta-retalls per a un ús posterior.
4. Suprimeix l'últim pas més innecessari Venut i afegiu-hi una columna calculada per calcular el total acumulat amb l'ordre Afegir una columna: columna personalitzada (Afegeix columna: columna personalitzada). La fórmula que necessitem serà així:
Aquí la funció Llista.Rang agafa la llista original (columna [Vendes]) i n'extreu elements, començant pel primer (a la fórmula, aquest és 0, ja que la numeració a Power Query comença des de zero). El nombre d'elements a recuperar és el número de fila que prenem de la columna [Índex]. Per tant, aquesta funció per a la primera fila només retorna una primera cel·la de la columna Venut. Per a la segona línia, ja les dues primeres cel·les, per a la tercera, les tres primeres, etc.
Bé, doncs la funció Llista.Suma suma els valors extrets i obtenim a cada fila la suma de tots els elements anteriors, és a dir, total acumulat:
Queda per eliminar la columna Índex que ja no necessitem i tornar a carregar els resultats a Excel amb l'ordre Inici - Tanca i carrega a.
El problema està resolt.
Ràpid i furiós
En principi, això es podria haver aturat, però hi ha una petita mosca a la pomada: la petició que vam crear funciona a la velocitat d'una tortuga. Per exemple, al meu ordinador no és el més feble, es processa una taula de només 2000 files en 17 segons. Què passa si hi ha més dades?
Per accelerar, podeu utilitzar la memòria intermèdia mitjançant la funció especial List.Buffer, que carrega la llista (llista) que se li dóna com a argument a la RAM, la qual cosa accelera molt l'accés a ella en el futur. En el nostre cas, té sentit guardar la llista #"Índex afegit"[Vendut], a la qual ha d'accedir Power Query quan calcula el total acumulat a cada fila de la nostra taula de 2000 files.
Per fer-ho, a l'editor de Power Query a la pestanya Principal, feu clic al botó Editor avançat (Inici – Editor avançat) per obrir el codi font de la nostra consulta en l'idioma M integrat a Power Query:
I després afegiu-hi una línia amb una variable La meva llista, el valor del qual és retornat per la funció de memòria intermèdia, i al pas següent substituïm la crida a la llista per aquesta variable:
Després de fer aquests canvis, la nostra consulta serà molt més ràpida i s'afrontarà amb una taula de 2000 files en només 0.3 segons!
Una altra cosa, oi? 🙂
- Gràfic de Pareto (80/20) i com construir-lo a Excel
- Cerca de paraules clau al text i memòria intermèdia de consultes a Power Query