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 -
# 1 - Somma per colore usando la funzione subtotale
Per comprendere l'approccio per il calcolo della somma dei valori in base ai colori di sfondo, si consideri la tabella di dati sottostante, che fornisce i dettagli degli importi in dollari americani per regione e mese.
- Passaggio 1: supponiamo di voler evidenziare quelle celle che hanno valori negativi a scopo indicativo, questo 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 mostrato 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, che vengono filtrate come mostrato sotto.
Come mostrato nello screenshot 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 viene spiegato per arrivare alla somma delle celle di colore in Excel, come 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, che siamo disposti a ottenere alla 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 dimenticando .CELL in "Fare riferimento a" come mostrato nello screenshot qui sotto.
Come si vede nello screenshot sopra, il nome immesso 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, che è stata 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 in base ai 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 inseriti 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
- Mentre si utilizza l'approccio per formula SUBTOTALE, questa funzionalità consente agli utenti di filtrare solo un 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 riga nelle diverse colonne, 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.
