Scarica la cartella di lavoro di esempio
Questo tutorial mostra come usare il Funzione SUBTOTALE di Excel in Excel per calcolare le statistiche di riepilogo.
SUBTOTALE Panoramica delle funzioni
La funzione SUBTOTALE Calcola una statistica riassuntiva per una serie di dati. Le statistiche disponibili includono, ma non sono limitate a media, deviazione standard, conteggio, minimo e massimo. Vedere l'elenco completo di seguito nella sezione degli ingressi delle funzioni:
Per utilizzare la funzione del foglio di lavoro Excel SUBTOTALE, selezionare una cella e digitare:
(Notare come appaiono gli input della formula)
Sintassi e input della funzione SUBTOTALE:
| 1 | =SOTTOTOTALE(num_funzione,RIF1) |
numero_funzione - Un numero che rappresenta quale operazione eseguire.
RIF1 - Intervalli o riferimenti contenenti dati da calcolare.
Che cos'è la funzione SUBTOTALE?
Il SUBTOTALE è una delle funzioni uniche all'interno dei fogli di calcolo perché può distinguere tra celle nascoste e celle non nascoste. Questo può rivelarsi molto utile quando si tratta di intervalli filtrati o quando è necessario impostare calcoli basati su selezioni utente diverse. Poiché sa anche ignorare altre funzioni SUBTOTALI dai suoi calcoli, possiamo anche usarlo all'interno di grandi dati riepilogati senza timore di doppi conteggi.
Riepilogo di base con SUBTOTALE
Supponiamo che tu abbia una tabella di vendite di prodotti ordinate e desideri creare i totali per ciascun prodotto, oltre a creare un totale complessivo. Potresti usare una tabella pivot, oppure puoi inserire alcune formule. Considera questo layout:
Ho inserito alcune funzioni SUBTOTALE nelle celle B5 e B8 che sembrano
| 1 | =SOTTOTALE(9, B2:B4) |
Dalla sintassi, puoi usare una varietà di numeri per il primo argomento. Nel nostro caso specifico, stiamo usando 9 per indicare che vogliamo fare una somma.
Concentriamoci sulla cella B9. Ha questa formula, che include l'intero intervallo di dati della colonna B, ma non include gli altri subtotali.
| 1 | =SOTTOTALE(9, B2:B8) |
NOTA: se non si desidera scrivere personalmente tutte le formule di riepilogo, è possibile accedere alla barra multifunzione Dati e utilizzare la procedura guidata Struttura - Totale parziale. Inserirà automaticamente le righe e posizionerà le formule per te.
Differenza nei primi argomenti
Nel primo esempio, abbiamo usato un 9 per indicare che volevamo fare una somma. La differenza tra l'utilizzo di 9 e 109 sarebbe come vogliamo che la funzione gestisca le righe nascoste. Se utilizzi le designazioni 1XX, la funzione non includerà le righe che sono state nascoste o filtrate manualmente.
Ecco il nostro tavolo di prima. Abbiamo spostato le funzioni in modo da poter vedere la differenza tra gli argomenti 9 e 109. Con tutto visibile, i risultati sono gli stessi.
Se applichiamo un filtro per escludere il valore di 6 in col B, le due funzioni rimangono le stesse.
Se nascondiamo manualmente le righe, vediamo la differenza. La funzione 109 è stata in grado di ignorare la riga nascosta mentre la funzione 9 no.
Modifica operazione matematica con SUBTOTALE
Potrebbe piacerti a volte essere in grado di dare al tuo utente la possibilità di cambiare il tipo di calcoli che viene eseguito. Ad esempio, vogliono ottenere la somma o la media. Poiché SUBTOTALE controlla l'operazione matematica tramite un numero di argomento, puoi scriverlo in un'unica formula. Ecco la nostra configurazione:
Abbiamo creato un menu a discesa in D2 in cui l'utente può selezionare "Somma" o "Media". La formula in E2 è:
| 1 | =SOTTOTOTALE(SE(D2="Media",1,SE(D2="Somma",9)),B2:B4) |
Qui, la funzione SE determinerà quale argomento numerico dare al SUBTOTALE. Se A5 è "Media", verrà emesso un 1 e SUBTOTALE darà la media di B2: B4. Oppure, se A5 è uguale a "Sum", allora IF emette un 9 e otteniamo un risultato diverso.
È possibile espandere questa funzionalità utilizzando una tabella di ricerca per elencare ancora più tipi di operazioni che si desidera eseguire. La tua tabella di ricerca potrebbe essere simile a questa
Quindi, potresti cambiare la formula in E2 per essere
| 1 | =SOTTOTALE(CERCA.VERT(A5, Tabella di ricerca, 2, 0), B2:B4) |
Formule condizionali con SUBTOTALE
Sebbene SUBTOTALE abbia molte operazioni che può fare, non può controllare i criteri da solo. Tuttavia, possiamo usarlo in una colonna di supporto per eseguire questa operazione. Quando hai una colonna di dati che sai che sarà sempre contiene una parte di dati, è possibile utilizzare la capacità dei SUBTOTALI per rilevare le righe nascoste.
Ecco la tabella con cui lavoreremo in questo esempio. Alla fine, vorremmo essere in grado di sommare i valori per "Apple", ma anche consentire all'utente di filtrare la colonna Qtà.
Innanzitutto, crea una colonna di supporto che ospiterà la funzione SUBTOTALE. In C2, la formula è:
| 1 | =SOTTOTALE(103, A2) |
Ricorda che 103 significa che vogliamo fare un COUNTA. Raccomando di usare COUNTA perché puoi quindi riempire la tua cella di riferimento di A2 o numeri o testo. Ora avrai una tabella simile a questa:
Questo non sembra utile all'inizio perché tutti i valori sono solo 1. Tuttavia, se nascondiamo la riga 3, quel "1" in C3 cambierà in uno 0 perché punta a una riga nascosta. Sebbene sia impossibile avere un'immagine che mostri il valore della cella nascosta specifica, puoi controllarla nascondendo la riga e quindi scrivendo una formula di base come questa da verificare.
| 1 | =C3 |
Ora che abbiamo una colonna che cambierà di valore a seconda che sia nascosta o meno, siamo pronti per scrivere l'equazione finale. I nostri SUMIF saranno così
In questa formula, sommeremo solo i valori della colonna B quando la colonna A è uguale a "Mela", e il valore nella colonna C è 1 (ovvero, la riga non è nascosta). Diciamo che il nostro utente vuole filtrare il 600, perché sembra anormalmente alto. Possiamo vedere che la nostra formula dà il risultato corretto.
Con questa capacità, puoi applicare un controllo a COUNTIFS, SUMIFS o anche a SUMPRODUCT. Aggiungi la possibilità di consentire ai tuoi utenti di controllare alcune affettatrici della tabella e sei pronto per creare una dashboard fantastica.
SUBTOTALE in Fogli Google
La funzione SUBTOTALE funziona esattamente allo stesso modo in Fogli Google come in Excel:
SUBTOTALE Esempi in VBA
Puoi anche usare la funzione SUBTOTALE in VBA. Tipo:application.worksheetfunction.subtotal(function_num,reh1)
Esecuzione delle seguenti istruzioni VBA
| 1234567891011121314151617 | Range("C7") = Application.WorksheetFunction.Subtotal(1, Range("C2:C5"))Range("C8") = Application.WorksheetFunction.Subtotal(2, Range("C2:C5"))Range("C9") = Application.WorksheetFunction.Subtotal(4, Range("C2:C5"))Range("C10") = Application.WorksheetFunction.Subtotal(5, Range("C2:C5"))Range("C11") = Application.WorksheetFunction.Subtotal(9, Range("C2:CE5"))Range("D7") = Application.WorksheetFunction.Subtotal(1, Range("D2:D5"))Range("D8") = Application.WorksheetFunction.Subtotal(2, Range("D2:D5"))Range("D9") = Application.WorksheetFunction.Subtotal(4, Range("D2:D5"))Range("D10") = Application.WorksheetFunction.Subtotal(5, Range("D2:D5"))Range("D11") = Application.WorksheetFunction.Subtotal(9, Range("D2:D5"))Range("E7") = Application.WorksheetFunction.Subtotal(1, Range("E2:E5"))Range("E8") = Application.WorksheetFunction.Subtotal(2, Range("E2:E5"))Range("E9") = Application.WorksheetFunction.Subtotal(4, Range("E2:E5"))Range("E10") = Application.WorksheetFunction.Subtotal(5, Range("E2:E5"))Range("E11") = Application.WorksheetFunction.Subtotal(9, Range("E2:E5")) |
produrrà i seguenti risultati
Per gli argomenti della funzione (numero_funzione, ecc.), puoi inserirli direttamente nella funzione o definire le variabili da utilizzare al loro posto.
Torna all'elenco di tutte le funzioni in Excel
