Metodo 1. Formule
Iniziamo, per il riscaldamento, con l'opzione più semplice: le formule. Se abbiamo una piccola tabella ordinata per data come input, quindi per calcolare il totale parziale in una colonna separata, abbiamo bisogno di una formula elementare:
La caratteristica principale qui è la complicata correzione dell'intervallo all'interno della funzione SOMMA: il riferimento all'inizio dell'intervallo è reso assoluto (con il simbolo del dollaro) e alla fine - relativo (senza dollari). Di conseguenza, copiando la formula sull'intera colonna, otteniamo un intervallo in espansione, la cui somma calcoliamo.
Gli svantaggi di questo approccio sono evidenti:
- La tabella deve essere ordinata per data.
- Quando si aggiungono nuove righe con dati, la formula dovrà essere estesa manualmente.
Metodo 2. Tabella pivot
Questo metodo è un po' più complicato, ma molto più piacevole. E per esacerbare, consideriamo un problema più serio: una tabella di 2000 righe di dati, in cui non c'è l'ordinamento per colonna della data, ma ci sono ripetizioni (cioè possiamo vendere più volte nello stesso giorno):
Convertiamo la nostra tabella originale in una scorciatoia da tastiera "intelligente" (dinamica). Ctrl+T o squadra Home – Formatta come una tabella (Home — Formatta come tabella), quindi costruiamo una tabella pivot su di essa con il comando Inserisci – Tabella pivot (Inserisci — Tabella pivot). Mettiamo la data nell'area delle righe nel riepilogo e il numero di merci vendute nell'area dei valori:
Tieni presente che se disponi di una versione non molto vecchia di Excel, le date vengono raggruppate automaticamente per anni, trimestri e mesi. Se hai bisogno di un raggruppamento diverso (o non ne hai affatto bisogno), puoi risolverlo facendo clic con il pulsante destro del mouse su qualsiasi data e selezionando i comandi Raggruppa / Separa (Raggruppa/Separa).
Se vuoi vedere sia i totali risultanti per periodi che il totale parziale in una colonna separata, allora ha senso inserire il campo nell'area del valore Presto Disponibile di nuovo per ottenere un duplicato del campo: in esso si accende la visualizzazione dei totali parziali. Per fare ciò, fare clic con il tasto destro del mouse sul campo e selezionare il comando Calcoli aggiuntivi – Totale cumulativo (Mostra valori come — Totali parziali):
Lì puoi anche selezionare l'opzione per aumentare i totali in percentuale e nella finestra successiva devi selezionare il campo per il quale andrà l'accumulo - nel nostro caso, questo è il campo della data:
I vantaggi di questo approccio:
- Una grande quantità di dati viene letta rapidamente.
- Non è necessario inserire manualmente le formule.
- Quando si modificano i dati di origine è sufficiente aggiornare il riepilogo con il tasto destro del mouse o con il comando Dati – Aggiorna tutto.
Gli svantaggi derivano dal fatto che questo è un riassunto, il che significa che non puoi fare quello che vuoi in esso (inserire righe, scrivere formule, costruire diagrammi, ecc.) Non funzionerà più.
Metodo 3: Power Query
Carichiamo la nostra tabella "intelligente" con i dati di origine nell'editor di query di Power Query utilizzando il comando Dati: da tabella/intervallo (Dati — Da tabella/intervallo). Nelle ultime versioni di Excel, tra l'altro, è stato rinominato - ora si chiama Con foglie (Dal foglio):
Quindi eseguiremo i seguenti passaggi:
1. Ordina la tabella in ordine crescente in base alla colonna della data con il comando Ordine crescente nell'elenco a discesa del filtro nell'intestazione della tabella.
2. Poco dopo, per calcolare il totale parziale, abbiamo bisogno di una colonna ausiliaria con il numero di riga ordinale. Aggiungiamolo con il comando Aggiungi colonna – Colonna indice – Da 1 (Aggiungi colonna — Colonna indice — Da 1).
3. Inoltre, per calcolare il totale parziale, abbiamo bisogno di un riferimento alla colonna Presto Disponibile, dove si trovano i nostri dati riepilogati. In Power Query, le colonne sono anche chiamate liste (elenco) e per ottenere un collegamento ad essa, fare clic con il tasto destro del mouse sull'intestazione della colonna e selezionare il comando Detailing (Mostra i dettagli). L'espressione di cui abbiamo bisogno apparirà nella barra della formula, costituita dal nome del passaggio precedente #"Indice aggiunto", da cui prendiamo la tabella e il nome della colonna [I saldi] da questa tabella tra parentesi quadre:
Copia questa espressione negli appunti per un ulteriore utilizzo.
4. Elimina l'ultimo passaggio non necessario Presto Disponibile e aggiungere invece una colonna calcolata per il calcolo del totale parziale con il comando Aggiunta di una colonna – Colonna personalizzata (Aggiungi colonna — Colonna personalizzata). La formula di cui abbiamo bisogno sarà simile a questa:
Ecco la funzione Elenco.Intervallo prende la lista originale (colonna [I saldi]) e ne estrae gli elementi, a partire dal primo (nella formula, questo è 0, poiché la numerazione in Power Query parte da zero). Il numero di elementi da recuperare è il numero di riga che prendiamo dalla colonna [Indice]. Quindi questa funzione per la prima riga restituisce solo una prima cella della colonna Presto Disponibile. Per la seconda riga – già le prime due celle, per la terza – le prime tre, ecc.
Bene, allora la funzione Somma.Elenco somma i valori estratti e otteniamo in ogni riga la somma di tutti gli elementi precedenti, ovvero il totale cumulativo:
Resta da eliminare la colonna Indice che non ci serve più e caricare di nuovo i risultati su Excel con il comando Home – Chiudi e carica su.
Il problema è risolto.
Fast & Furious
In linea di principio, questo avrebbe potuto essere fermato, ma c'è una piccola mosca nell'unguento: la richiesta che abbiamo creato funziona alla velocità di una tartaruga. Ad esempio, sul mio PC non più debole, una tabella di sole 2000 righe viene elaborata in 17 secondi. E se ci sono più dati?
Per velocizzare, puoi usare il buffering usando la speciale funzione List.Buffer, che carica l'elenco (elenco) assegnatogli come argomento nella RAM, il che velocizza notevolmente l'accesso ad esso in futuro. Nel nostro caso, ha senso memorizzare nel buffer l'elenco #"Indice aggiunto"[venduto], a cui Power Query deve accedere durante il calcolo del totale parziale in ogni riga della nostra tabella di 2000 righe.
Per fare ciò, nell'editor di Power Query nella scheda Principale, fare clic sul pulsante Editor avanzato (Home – Editor avanzato) per aprire il codice sorgente della nostra query nel linguaggio M integrato in Power Query:
E poi aggiungi una riga con una variabile lì La mia lista, il cui valore viene restituito dalla funzione di buffering, e nel passaggio successivo sostituiamo la chiamata alla lista con questa variabile:
Dopo aver apportato queste modifiche, la nostra query diventerà notevolmente più veloce e gestirà una tabella di 2000 righe in soli 0.3 secondi!
Un'altra cosa, giusto? 🙂
- Grafico di Pareto (80/20) e come costruirlo in Excel
- Ricerca di parole chiave nel testo e buffer di query in Power Query