Corsu tutale in Excel

Metudu 1. Formule

Cuminciamu, per riscaldamentu, cù l'opzione più simplice - formule. Se avemu una piccula tavola ordinata per data cum'è input, allora per calculà u totale in una colonna separata, avemu bisognu di una formula elementaria:

Corsu tutale in Excel

A funzione principale quì hè a fissazione complicata di a gamma in l'internu di a funzione SUM - a riferenza à u principiu di a gamma hè fatta assuluta (cù segni di dollari), è à a fine - relative (senza dollari). In cunsiquenza, quandu copià a formula in tutta a colonna, avemu un intervallu in espansione, a somma di quale calculemu.

I svantaghji di stu approcciu sò evidenti:

  • A tavula deve esse ordinata per data.
  • Quandu aghjunghjenu novi fila cù dati, a formula duverà esse allargata manualmente.

Metudu 2. Pivot table

Stu metudu hè un pocu più cumplicatu, ma assai più piacevule. È per aggravà, cunsideremu un prublema più seriu - una tavula di 2000 fila di dati, induve ùn ci hè micca una classificazione per a colonna di data, ma ci sò ripetizioni (vale à dì pudemu vende parechje volte in u stessu ghjornu):

Corsu tutale in Excel

Cunvertemu a nostra tavola originale in una scurciatoia di tastiera "intelligente" (dinamica). Ctrl+T o squadra Home - Formate cum'è una tavola (Casa - Formate cum'è Table), è poi custruemu una tabella pivot nantu à questu cù u cumandimu Inserisce - PivotTable (Inserisci - Pivot Table). Pudemu a data in l'area di fila in u riassuntu, è u numeru di merchenzie venduti in l'area di i valori:

Corsu tutale in Excel

Per piacè nutate chì sè vo avete una versione micca vechja di Excel, allora e date sò automaticamente raggruppati per anni, trimestri è mesi. Sè avete bisognu di un raggruppamentu diversu (o ùn avete micca bisognu à tuttu), allura pudete riparà clicchendu right-click nant'à ogni data è selezziunate cumandamenti Group / Ungroup (Gruppu / Ungruppu).

Se vulete vede sia i totali risultanti per periodi è u tutale cumunale in una colonna separata, allora hè sensu di scaccià u campu in l'area di valore. Venutu di novu per ottene un duplicatu di u campu - in questu avemu da attivà a visualizazione di i totali correnti. Per fà questu, fate un clic right in u campu è selezziunate u cumandamentu Calculi supplementari - Cumulative Total (Mostra i valori cum'è - Totali cumunali):

Corsu tutale in Excel

Quì pudete puru selezziunà l'opzione di crescita totali cum'è un percentinu, è in a finestra dopu avete bisognu di selezziunà u campu per quale l'accumulazione anderà - in u nostru casu, questu hè u campu di data:

Corsu tutale in Excel

I vantaghji di stu approcciu:

  • Una grande quantità di dati hè lettu rapidamente.
  • Nisuna formule deve esse inserita manualmente.
  • Quandu cambia in i dati fonte, hè abbastanza per aghjurnà u riassuntu cù u buttone drittu di u mouse o cù u cumandamentu Dati - Refresh All.

I disadvantages seguitanu da u fattu chì questu hè un riassuntu, chì significa chì ùn pudete micca fà ciò chì vulete in ellu (inserite linee, scrive formule, custruisce qualsiasi diagrammi, etc.) ùn funziona più.

Metudu 3: Power Query

Caricà a nostra tavula "intelligente" cù i dati fonte in l'editore di query Power Query usendu u cumandimu Dati - Da Table / Range (Dati - Da Table/Range). In l'ultime versioni di Excel, per via, hè statu rinominatu - avà hè chjamatu Cù foglie (Da u fogliu):

Corsu tutale in Excel

Allora avemu da fà i seguenti passi:

1. Sort the table in ordine crescente da a colonna di data cù u cumandamentu Sorte ascendente in a lista di u filtru in l'intestazione di a tavola.

2. Un pocu più tardi, per calculà u tutale tutale, avemu bisognu di una colonna ausiliaria cù u numeru di fila ordinale. Aghjunghjemu cù u cumandimu Add Column - Index Column - Da 1 (Aggiungi a colonna - Colonna Index - Da 1).

3. Inoltre, per calculà u tutale, avemu bisognu di una riferenza à a colonna Venutu, induve si trovanu i nostri dati riassunti. In Power Query, i culonni sò ancu chjamati listi (lista) è per uttene un ligame, fate un clic right in l'intestazione di a colonna è selezziunate u cumandimu. Dettagliu (Mostra i dettagli). L'espressione chì avemu bisognu apparirà in a barra di formula, custituita da u nome di u passu precedente #"Indice aghjuntu", da induve piglià a tavula è u nome di a colonna [Vendite] da sta tavula in parentesi quadrate:

Corsu tutale in Excel

Copia sta spressione à u clipboard per più usu.

4. Sguassà inutili più ultimu passu Venutu è aghjunghje invece una colonna calculata per u calculu di u tutale currettu cù u cumandimu Aghjunghjite una Colonna - Colonna Personalizzata (Aggiungi colonna - Colonna persunalizata). A formula chì avemu bisognu serà cusì:

Corsu tutale in Excel

Eccu a funzione Lista.Range piglia a lista originale (colonna [Vendite]) è estrae elementi da ellu, partendu da u primu (in a formula, questu hè 0, postu chì a numerazione in Power Query parte da zero). U numeru di elementi per ricuperà hè u numeru di fila chì pigliamu da a colonna [Indice]. Allora sta funzione per a prima fila torna solu una prima cellula di a colonna Venutu. Per a seconda linea - digià i primi dui celluli, per u terzu - i primi trè, etc.

Ebbè, allora a funzione Lista.Sum somma i valori estratti e otteniamo in ogni fila a somma di tutti gli elementi precedenti, vale a dire totale cumulativo:

Corsu tutale in Excel

Resta per sguassà a colonna Index chì ùn avemu più bisognu è caricate i risultati torna à Excel cù a Home - Close & Load to command.

U prublema hè risolta.

Fast and Furious

In principiu, questu puderia esse firmatu, ma ci hè una piccula mosca in l'unguentu - a dumanda chì avemu creatu travaglia à a velocità di una tartaruga. Per esempiu, in u mo micca u PC più debule, una tavola di solu fila 2000 hè processata in 17 seconde. E se ci hè più dati?

Per accelerà, pudete aduprà u buffering cù a funzione speciale List.Buffer, chì carica a lista (lista) data à ellu cum'è un argumentu in RAM, chì accelera assai l'accessu à questu in u futuru. In u nostru casu, hè sensu di buffer l'#"Indici aghjuntu" [Vendu] lista, chì Power Query hà da accede à u calculu di u tutale in ogni fila di a nostra tabella di 2000 fila.

Per fà questu, in l'editore Power Query in a tabulazione Principale, cliccate nantu à u buttone Editore Avanzatu (Home - Editor Avanzatu) per apre u codice fonte di a nostra dumanda in a lingua M integrata in Power Query:

Corsu tutale in Excel

E poi aghjunghje una linea cù una variabile quì MyList, u valore di quale hè tornatu da a funzione di buffering, è à u prossimu passu rimpiazzà a chjama à a lista cù sta variàbile:

Corsu tutale in Excel

Dopu avè fattu questi cambiamenti, a nostra dumanda diventerà significativamente più veloce è affruntà una tavola di 2000 fila in solu 0.3 seconde!

Un'altra cosa, nò? 🙂

  • Graficu Pareto (80/20) è cumu custruisce in Excel
  • Ricerca di keyword in testu è buffering di query in Power Query

Lascia un Audiolibro