Come calcolare la somma di una colonna in excel. Come calcolare la somma di una colonna in excel Come inserire subtotali in excel

Funzione "Subtotale()". utilizzato per calcolare un totale parziale (somma, media, numero di valori, ecc.) in un intervallo con date nascoste.

La particolarità della funzione è che è destinata ad essere utilizzata insieme ad altri strumenti di Excel (ad esempio, filtri automatici).

Per dimostrare l'utilità della funzione subtotale() di Excel, considerare un'esportazione troncata dal database degli eventi.

Compito: in base ai parametri selezionati, valutare gli indicatori chiave rispetto alla situazione in tutte le aree.

Gli eventi possono essere filtrati in base a diversi parametri, ma per confronto, vorremmo comprendere gli indicatori per i parametri filtrati e confrontarli con la situazione nel suo complesso in tutte le aree.

Per fare ciò, puoi utilizzare la funzione di Excel = Totali parziali (). La funzione =Subtotal() esegue un calcolo basato sui valori filtrati.

Nella formula, puoi impostare il calcolo:

Medio - numero 1 o 101 - per il campionamento
Conteggio valore - Numero 3 o 103
Importo - 9 o 109
Eccetera.

Se il numero della funzione è di tre cifre, ad esempio 109, la funzione funziona come subtotali, ad es. calcola i valori in base ai parametri filtrati.
Se il numero è a una o due cifre 2 o 11, la formula subtotale() funziona come una formula standard per la funzione selezionata.

Andiamo alle nostre attività:

Abbiamo uno scarico dal database per le seguenti colonne:

Situazione
Azioni
Numero di clienti
Volume_Vendite_Prima
Volume_Piano_vendite
Volume_Vendite_Fatto
Costi_Piano
Costo_Fatto

Per valutare la situazione nel suo complesso, introduciamo la formula di sommatoria standard:

Per calcolare i subtotali per i parametri selezionati, immettere =SUBTOTALS(109;RC:RC), dove

109 - funzione di somma per il calcolo dei subtotali;
RC:RC - riferimento all'intervallo di sommatoria.

Ora, filtrando la colonna "Situazione" - "Situazione 4" e "Azione" - Azione 2 e 3, otterremo un ricalcolo dei subtotali per ciascuno degli indicatori e potremo confrontarli con gli indicatori "Totale":

Di conseguenza: si può vedere che la percentuale di crescita delle vendite per gli eventi filtrati è più alta che per tutti, e avevamo programmato di ottenere risultati inferiori rispetto al totale, ma siamo andati più in alto. Quelli. ha funzionato meglio del previsto e meglio che in tutti gli eventi.

La formula di Excel = subtotale () è un ottimo strumento per calcolare i punteggi in base a parametri filtrati.

Se hai domande, contattaci!

Unisciti a noi!

Scarica le app gratuite di previsione e business intelligence:

Novo Previsione Lite- automatico calcolo delle previsioni v eccellere.
4analisi- Analisi ABC-XYZ e analisi delle emissioni in Eccellere.
Qlik Senso Scrivania e QlikViewPersonal Edition - Sistemi di BI per l'analisi e la visualizzazione dei dati.

Excel ha un comando "Dati" - "Totali" che può visualizzare sia i subtotali che i totali complessivi in un elenco. Quando viene eseguito questo comando, l'elenco in Excel viene visualizzato sotto forma di un contorno, che consente di comprimere ed espandere rapidamente le voci nei subtotali utilizzando il mouse.

Ad esempio, riassumiamo i totali intermedi e generali della colonna "Numero magazzino" nell'elenco "Elenco contabilità merci in giacenza".

Riso. 1.

Per visualizzare i totali parziali, procedere come segue:

Prima di tutto, ordina l'elenco in base alla colonna che desideri riassumere, in questo caso ordina "Magazzino n." in ordine crescente
Quindi seleziona una cella qualsiasi nell'elenco e seleziona il comando "Totali" dal menu "Dati".
Nella finestra di dialogo Totali parziali visualizzata, selezionare l'intestazione di colonna "Column #" dall'elenco "Ogni volta che viene modificato in:"
Nell'elenco "Operazione:", selezionare la funzione desiderata che può essere utilizzata per calcolare i totali parziali, ad esempio la funzione SOMMA()
Successivamente, nell'elenco "Aggiungi totali per", seleziona la casella davanti all'intestazione della colonna su cui desideri calcolare il totale parziale (ad esempio, il costo)
Fare clic su OK per eseguire il comando subtotale

Riso. 2.

Come risultato dell'esecuzione del comando "Dati" - "Totali", l'elenco viene visualizzato sullo schermo sotto forma di una struttura. La figura mostra un elenco con voci compresse nei gruppi 2, 3 e 4.

Riso. 3.

Sul lato sinistro dell'elenco sono presenti i pulsanti con le icone "-" e "+", che indicano che sullo schermo vengono visualizzate tutte le voci del primo e del quinto gruppo dell'elenco e nei gruppi 2, 3, 4 - voci sono crollati. Se fai clic sul pulsante "-", le voci nel gruppo si comprimeranno e se fai clic sul pulsante "+", le voci nel gruppo si espanderanno.

Per terminare il comando Totali, fare clic sul pulsante Cancella tutto nella finestra Totali parziali.

Questo è un capitolo del libro di Bill Jelen.

Attività: la funzione SOMMA somma tutte le celle in un intervallo, indipendentemente dal fatto che siano nascoste o meno. Vuoi sommare solo le righe visibili.

Soluzione: è possibile utilizzare la funzione SUBTOTALE anziché SOMMA. La formula sarà leggermente diversa a seconda di come nascondi le righe. Se hai selezionato righe, fatto clic con il pulsante destro del mouse e selezionato dal menu contestuale nascondere, puoi utilizzare: =SUBTOTALE(109; allineare) (Fig. 1). È abbastanza insolito utilizzare SUB-TOTALI per questi scopi. In genere, questa funzione è necessaria per fare in modo che Excel ignori altri subtotali all'interno di un intervallo.

Scarica nota in formato o, esempi in formato

I SUB-TOTALI possono eseguire 11 operazioni. Il primo argomento della funzione indica di eseguire le seguenti operazioni: (1) AVERAGE, (2) COUNT, (3) COUNT, (4) MAX, (5) MIN, (6) PRODUCT, (7) STDEV, ( 8) DEV.ST, (9) SOMMA, (10) DISP, (11) DISP. Quando si aggiungono cento, vengono eseguite le stesse operazioni, ma solo sulle celle visibili. Ad esempio, 104 troverà il massimo tra le celle visibili. Sotto visibile Voglio dire, non visibile sullo schermo (ad esempio, 120 linee non si adattano allo schermo), e non nascoste, dal comando Nascondere.

La cella E566 (vedi Figura 1) usa la formula =SUBTOTALE(109;E2:E564). Excel restituisce la somma delle sole celle visibili (non nascoste) nell'intervallo, vale a dire - E2; E30; E72; E78; E564.

La funzione SUBTOTALE si applica ai set di dati verticali. Non è destinato a set di dati orizzontali. Quindi, quando si definiscono i subtotali di un set di dati orizzontale utilizzando il valore costante numero_funzione da 101 in poi (ad esempio, SUBTOTALI (109; С2: F2) Fig. 2), nascondere la colonna non influirà sul risultato.

Ulteriori informazioni: c'è un'eccezione insolita nel comportamento della funzione SUBTOTALE. Quando le righe sono state nascoste da uno dei comandi di filtro (Filtro avanzato, Filtro automatico o Filtro), Excel somma solo le righe visibili, anche in SUBTOTALE(9; allineare). Non è necessario utilizzare la versione 109 (Figura 3). Qui il filtro viene utilizzato per trovare i record Chevron.

Perché parlo di questa stranezza? Perché esiste una scorciatoia da tastiera poco conosciuta per sommare le righe visibili risultanti dal filtraggio. Prova questi passaggi:

Seleziona qualsiasi cella nel tuo set di dati.
Passa attraverso il menu DATI –> Filtro(oppure premi Alt + S, quindi senza rilasciare Alt, premi F; oppure premi Ctrl + Maiusc + L). Excel aggiunge un filtro (menu a discesa) per tutte le intestazioni di colonna.
Apri uno dei menu a discesa, ad esempio, cliente. Deseleziona Seleziona tutto, quindi seleziona un cliente. Nel nostro esempio - Chevron.
Seleziona le celle direttamente sotto i dati filtrati. Nel nostro esempio, celle E565:H565.
Premere Alt+= o fare clic sull'icona Somma automatica(menù CASA). Invece di utilizzare SOMMA, Excel utilizzerà SUBTOTALE(9; allineare), che sommerà solo le righe selezionate dal filtro (vedere la figura 3).

In Excel 2010 è apparsa un'altra funzione simile: AGGREGA (per maggiori dettagli, vedere ; sezione Funzione AGGREGA). Ha più funzioni nel suo "repertorio" e più opzioni per quali linee escludere e quali elaborare. Il suo vantaggio principale è la gestione di valori errati (ad esempio #DIV/0!). Sfortunatamente, anche questa funzione non si applica alla somma delle colonne visibili.

Riepilogo: puoi utilizzare la funzione SUBTOTALE per ignorare le righe nascoste.

Calcoliamo i subtotali nella tabella MS EXCEL. Ad esempio, in una tabella contenente informazioni sulle vendite per diverse categorie di merci, calcoliamo il costo di ciascuna categoria.

Abbiamo una tabella delle vendite di beni (i prodotti si ripetono). Vedi file di esempio.

Calcola il costo di ogni prodotto utilizzando MS EXCEL Subtotali (Dati/Struttura/Totali parziali).

Per questo hai bisogno di:

assicurati che i nomi delle colonne abbiano intestazioni;
dati di colonna Merce, per esempio con ;
selezionando qualsiasi cella nella tabella, chiama Subtotali(sul menu Struttura dati);

in campo" Ad ogni cambio di:" scegliere Prodotto;
in campo" Operazione" scegliere Somma;
in campo" Aggiungi i totali per» selezionare la casella accanto al valore Prezzo;
Premere OK.

Come puoi vedere dall'immagine sopra, dopo aver applicato lo strumento subtotali, MS EXCEL ha creato tre livelli di organizzazione dei dati: i controlli della struttura sono apparsi a sinistra della tabella. Livello 1: Totale complessivo (costo di tutti gli elementi nella tabella); Livello 2: Costo delle merci in ciascuna categoria; Livello 3: tutte le righe della tabella. Premendo gli appositi pulsanti è possibile visualizzare la tabella con il livello di dettaglio desiderato. Le figure seguenti mostrano i livelli 1 e 2.

Copia solo le righe con subtotali

Copiare solo le righe con i subtotali in un altro range non è così semplice: anche se la tabella è raggruppata al 2° livello (vedi figura sopra), poi selezionando le celle con i totali (infatti il range A4:D92) e copiandolo in un altro intervallo, otteniamo l'intera tabella. Per copiare solo i totali usiamo (useremo il fatto che MS EXCEL durante la creazione della struttura Subtotali inserisce le righe totali con la parola aggiunta Risultato o nella versione inglese - Totale).

creare nell'intervallo RE5: RE6 targa criteri: in D5 inserire un'intestazione di colonna che contenga le parole Totale, ad es. parola Prodotto; v D6 inserisci *Totale (verranno selezionate tutte le righe che hanno valori nella colonna Prodotto che terminano con la parola Risultato) Un asterisco significa *;

seleziona qualsiasi cella nella tabella;
chiamata ( Dati/ Ordina e filtra/ Avanzate);
nel campo Intervallo condizione, immettere RE5: RE6 ;
impostare l'opzione Copia il risultato in un'altra posizione;
in campo Inserisci il risultato nell'intervallo specificare una cella vuota, ad esempio A102 ;

Funzione SUBTOTALI() , la versione inglese di SUBTOTAL(), utilizzato per calcolare un totale parziale (somma, media, numero di valori, ecc.) in un intervallo con righe nascoste.

La particolarità della funzione è che è destinata ad essere utilizzata insieme ad altri strumenti EXCEL: e . Vedi file di esempio.

Funzione Sintassi

SUBTOTALI(numero_funzione;collegamento1;link2;...))

Numero_funzioneè un numero compreso tra 1 e 11 che specifica quale funzione utilizzare quando si calcolano i totali all'interno dell'elenco.

Numero_funzione (compresi i valori nascosti)	Numero_funzione (esclusi i valori nascosti)	Funzione
1	101	MEDIA
2	102	CONTROLLO
3	103	CONTARE
4	104	MASSIMO
5	105	MIN
6	106	PRODOTTI
7	107	DEV.ST
8	108	DEV.ST
9	109	SOMMA
10	110	DISP
11	111	DISP

Ad esempio, la funzione SUM() ha il codice 9. La funzione SUM() ha anche il codice 109, cioè puoi scrivere la formula = SUBTOTALI(9;A2:A10) o = TOTALI INTERMEDI(109;A2:A10). Qual è la differenza - leggi sotto. Di solito si usano i codici funzione da 1 a 11.

Se sono già presenti formule di somma all'interno degli argomenti link1;link2;... (totali nidificati), questi subtotali vengono ignorati per evitare la doppia somma.

Importante: Funzione SUBTOTALI() progettato per colonne di dati o set di dati verticali. Non è destinato a righe di dati o set di dati orizzontali (il suo utilizzo in questo caso può portare a risultati imprevedibili).

Funzione SUBTOTALI() e filtro automatico

Lascia che ci sia una tabella iniziale.

Funzione SUBTOTALI() e linee nascoste

Lascia che ci sia la stessa tabella di origine. Nascondi linee di prodotti Prodotto2 tramite il menu o tramite il menu contestuale.

In questo caso, c'è una differenza tra l'utilizzo del codice funzione SUM(): 9 e 109. La funzione con codice 109 "sente" se la riga è nascosta o meno. In altre parole, per una serie di codici numero_funzione Funzioni da 101 a 111 SUBTOTALI() esclude i valori delle righe nascoste con il comando Home/ Celle/ Formato/ Nascondi o Mostra . Questi codici vengono utilizzati per ottenere i subtotali solo per i numeri non nascosti dell'elenco.

Funzione SUBTOTALI() e lo strumento EXCEL Totali parziali

Sia anche una tabella iniziale. Creiamo una struttura usando il built-in EXCEL - .

Nascondi le linee con Prodotto2 facendo clic sul pulsante "meno" corrispondente nella struttura.

Funzione SUBTOTALI() esclude dalla struttura tutte le righe non visualizzate indipendentemente dal valore del codice utilizzato numero_funzione e, nel nostro caso, calcola la somma dei soli beni Prodotto1. Questo risultato è simile alla situazione con il filtro automatico.

Altre caratteristiche

Funzione SUBTOTALI() può calcolare la somma, il numero e la media dei valori selezionati e include anche altre 8 funzioni (vedi sintassi). Di norma, queste funzioni sono abbastanza, ma a volte è necessario estendere le capacità della funzione. SUBTOTALI(). Consideriamo un esempio di calcolo della media geometrica per i valori selezionati dal filtro automatico. La funzione SRGEOM() non è nella lista delle funzioni disponibili tramite i relativi codici, ma c'è una via d'uscita.