Come sommare per colore in Excel? (2 metodi utili)
I primi 2 metodi per sommare per colori in Excel sono i seguenti:
- Utilizzo della formula SUBTOTALE in Excel e filtro per funzione colore.
- Applicazione della formula GET.CELL definendo il nome nella scheda della formula e applicando la formula SUMIF in Excel per riepilogare i valori in base ai codici colore.
Parliamo di ciascuno di essi in dettaglio -
Puoi scaricare questo modello Excel Somma per colore qui - Modello Excel Somma per colore# 1 - Somma per colore utilizzando la funzione subtotale
Per comprendere l'approccio per il calcolo della somma dei valori in base ai colori di sfondo, prendiamo in considerazione la tabella di dati sottostante che fornisce i dettagli degli importi in dollari USA per regione e mese.
- Passaggio 1: supponiamo di voler evidenziare quelle celle che sono negative nei valori a scopo indicativo, ciò può essere ottenuto applicando la formattazione condizionale o evidenziando le celle manualmente come mostrato di seguito.
- Passaggio 2: ora per ottenere la somma delle celle colorate in Excel, inserisci la formula per SUBTOTALE sotto la tabella dei dati. La sintassi per la formula SUBTOTALE è mostrata di seguito.
La formula inserita per calcolare la somma è
= SUBTOTALE (9, D2: D13)
Qui il numero "9" nell'argomento function_num si riferisce alla funzionalità di somma e l'argomento di riferimento viene fornito come intervallo di celle da calcolare. Di seguito è riportato lo screenshot per lo stesso.
- Passaggio 3: come si vede nello screenshot sopra, è stata calcolata una somma dell'importo in USD. Per calcolare gli importi evidenziati in un colore di sfondo rosso chiaro. Applicare il filtro alla tabella dati andando su Dati e quindi selezionando un filtro.
- Passaggio 4: quindi selezionare il filtro per colore e scegliere il colore della cella rosso chiaro in "Filtra per colore della cella". Di seguito lo screenshot per descrivere meglio il filtro.
- Passaggio 5: una volta applicato il filtro Excel, la tabella dei dati verrà filtrata solo per le celle di sfondo rosso chiaro e la formula del totale parziale applicata nella parte inferiore della tabella dei dati visualizzerà la somma delle celle colorate filtrate come mostrato di seguito.
Come mostrato nella schermata sopra, il calcolo della cella colorata si ottiene nella cella E17, formula del totale parziale.
# 2 - Somma per colore utilizzando la funzione Get.Cell
Il secondo approccio è spiegato per arrivare alla somma delle celle di colore in Excel è discusso nell'esempio seguente. Considera la tabella dei dati per comprendere meglio la metodologia.
- Passaggio 1: ora evidenziamo l'elenco di celle nella colonna Importo USD a cui siamo disposti a ottenere la somma desiderata di celle colorate come mostrato di seguito.
- Passaggio 2: come possiamo vedere nello screenshot qui sopra, a differenza del primo esempio qui abbiamo più colori. In tal modo utilizzeremo la formula = GET.CELL definendolo nella casella del nome e non utilizzandolo direttamente in Excel.
- Passaggio 3: ora una volta visualizzata la finestra di dialogo per "Definisci nome", immettere il nome e la formula per GET.CELL in "Fare riferimento a" come mostrato nell'immagine sottostante.
Come si vede nello screenshot qui sopra, il nome inserito per la funzione è "CellColor" e la formula = GET.CELL (38, "Esempio 2! $ D2) deve essere inserita in" Riferito a ". All'interno della formula, il numero 38 si riferisce alle informazioni sul codice della cella e il secondo argomento è il numero di cella D2 si riferisce alla cella di riferimento. Ora fai clic su OK.
- Passaggio 4: ora inserisci la funzione Nome 'CellColor' nella cella accanto alla cella con codice colore definita nella finestra di dialogo come spiegato nel passaggio 3.
Come si può vedere nello screenshot sopra, viene inserita la funzione 'CellColor' che restituisce il codice colore per il colore della cella di sfondo.
Allo stesso modo, la formula viene trascinata per l'intera colonna.
- Passaggio 5: ora per arrivare alla somma dei valori per colori in Excel, inseriremo la formula SUMIF. La sintassi per la formula SOMMA.SE è la seguente: -
Come si può vedere dalla schermata sopra, i seguenti argomenti vengono inseriti nella formula SUMIF: -
- L'argomento dell'intervallo viene immesso per l'intervallo di celle E2: E13
- I criteri vengono immessi come G2, i cui valori riepilogati devono essere recuperati.
- L'intervallo di celle viene immesso come D2: D13
La formula SOMMA.SE viene trascinata verso il basso per tutti i numeri di codice colore per i quali i valori devono essere sommati.
Cose da ricordare
- Durante l'utilizzo dell'approccio per formula SUBTOTALE, questa funzionalità consente agli utenti di filtrare un solo colore di filtro alla volta. Inoltre, questa funzionalità può essere utilizzata per aggiungere solo una colonna di valori in base ai colori del filtro. Se è presente più di una colonna con colori diversi per righe nella colonna diversa, il SUBTOTALE può mostrare solo il risultato corretto per un filtro per colore solo per una colonna specifica.
- La formula GET.CELL in combinazione con l'approccio SUMIF elimina questa limitazione, poiché questa funzionalità potrebbe essere utilizzata per riepilogare in base ai colori per più codici colore sullo sfondo della cella.
