Suggerimento: le altre lingue sono tradotte da Google. Puoi visitare il English versione di questo link.
Log In
x
or
x
x
Registrati
x

or

Come contare e sommare le celle in base al colore di sfondo in Excel?

Supponendo che tu abbia un intervallo di celle con colori di sfondo diversi, come rosso, verde, blu e così via, ma ora devi contare quante celle in quell'intervallo hanno un determinato colore di sfondo e sommare le celle colorate con lo stesso colore certo . In Excel, non esiste una formula diretta per calcolare la somma e il conteggio delle celle di colore, qui ti presenterò alcuni modi per risolvere questo problema.

Conta e somma le celle in base al colore di riempimento specifico per Filtro e SUBTOTALE

Contare e sommare le celle in base al colore di riempimento specifico mediante la funzione GET.CELL

Contare e sommare le celle in base al colore di riempimento specifico con Funzione definita dall'utente

Contare e sommare le celle in base al colore di riempimento specifico con le funzioni di Kutools

Conta e somma le celle in base al colore di riempimento specifico (o al colore di formattazione condizionale) con Kutools per Excel

Un clic per contare, sommare e medie celle colorate in Excel

Con l'eccellente Contare per colore caratteristica di Kutools for Excel, puoi contare rapidamente, sommare e calcolare le celle in base al colore di riempimento o al colore del carattere specificato con un solo clic in Excel. Inoltre, questa funzione individuerà anche i valori massimi e minimi delle celle in base al colore di riempimento o al colore del carattere. Prova gratuita di funzionalità complete 60-day!
numero di annunci per colore 2


Conta e Somma celle colorate per Filtro e SUBTOTALE

Supponiamo di avere una tabella delle vendite di frutta come mostrato nell'immagine sottostante, e conterremo o sommeremo le celle colorate nella colonna Quantità. In questa situazione, possiamo filtrare la colonna Quantità per colore, quindi contare o sommare celle colorate filtrate dalla funzione SUBTOTALE facilmente in Excel.

1. Selezionare le celle vuote per inserire la funzione SUBTOTALE.

  1. Per contare tutte le celle con lo stesso colore di sfondo, inserisci la formula = SUBTOTALE (102, E2: E20);
  2. Per sommare tutte le celle con lo stesso colore di sfondo, inserisci la formula = SUBTOTALE (109, E2: E20);


Note: In entrambe le formule, E2: E20 è la colonna Importo contenente le celle colorate e puoi modificarle in base alle tue esigenze.

2. Seleziona l'intestazione della tabella e fai clic dati > Filtro. Vedi screenshot:

3. Fai clic sull'icona Filtro nella cella di intestazione della colonna Quantità, quindi fare clic su Filtra per colore e il colore specificato che contate successivamente. Vedi screenshot:

Dopo il filtraggio, entrambe le formule SUBTOTALI contano e sommano automaticamente tutte le celle di colore filtrate nella colonna Quantità. Vedi screenshot:

Note: Questo metodo richiede che le celle colorate conteggiate o sommate si trovino nella stessa colonna.


Conta o Somma celle colorate mediante la funzione GET.CELL

In questo metodo, creeremo un intervallo denominato con la funzione GET.CELL, otteniamo il codice colore delle celle e quindi contiamo o sommiamo per codice colore facilmente in Excel. Si prega di fare come segue:

1. Clic Formule > Definisci nome. Vedi screenshot:

2. Nella finestra di dialogo Nuovo nome, si prega di fare come mostrato di seguito screenshot:
(1) Digitare un nome nella casella Nome;
(2) Immettere la formula = GET.CELL (38, Sheet4! $ E2) nella casella Fa riferimento a (Nota: nella formula, 38 significa restituire il codice della cella, e Sheet4! $ E2 è la prima cella della colonna Amount ad eccezione dell'intestazione di colonna che è necessario modificare in base ai dati della tabella.)
(3) Fare clic su OK pulsante.

3. Ora aggiungi una nuova colonna Colore direttamente alla tabella originale. Quindi digita la formula = NumColor e trascinare il riempimento automatico per applicare la formula ad altre celle nella colonna Colore. Vedi screenshot:
Note: Nella formula, NumColor è l'intervallo denominato specificato nei primi passaggi 2. Devi cambiarlo con il nome specificato che hai impostato.

Ora il codice colore di ogni cella nella colonna Amount ritorna nella Colonna Colore. Vedi screenshot:

4. Copia ed elenca il colore di riempimento in un intervallo vuoto nel foglio di lavoro attivo e digita le formule accanto ad esso come mostrato di seguito:
A. Per contare le celle per colore, inserisci la formula = COUNTIF ($ F $ 2: $ F $ 20, NumColor);
B. Per sommare le celle per colore, inserisci la formula = SUMIF ($ F $ 2: $ F $ 20, NumColor, $ E $ 2: $ E $ 20).

Note: In entrambe le formule, $ F $ 2: $ F $ 20 è la colonna Colour, NumColor è l'intervallo denominato specificato, $ E $ 2: $ E $ 20 è la colonna Importo e puoi cambiarli secondo le tue necessità.

Ora vedrai che le celle nella colonna Quantità vengono contate e sommate in base ai colori di riempimento.


Contare e sommare le celle in base al colore di riempimento specifico con Funzione definita dall'utente


Supponendo che le celle colorate si diffondano in un intervallo come mostrato nell'immagine sottostante, entrambi i metodi sopra riportati non possono contare o sommare le celle colorate. Qui, questo metodo introdurrà un VBA per risolvere il problema.

1. Tenere premuto il tasto ALT + F11 chiavi, e apre il Microsoft Visual Basic, Applications Edition finestra.

2. Clic inserire > Moduloe incollare il seguente codice nella finestra del modulo.

VBA: contare e sommare le celle in base al colore di sfondo:

Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
Dim rCell As Range
Dim lCol As Long
Dim vResult
lCol = rColor.Interior.ColorIndex
If SUM = True Then
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = WorksheetFunction.SUM(rCell, vResult)
End If
Next rCell
Else
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = 1 + vResult
End If
Next rCell
End If
ColorFunction = vResult
End Function

3. Quindi salva il codice e applica la seguente formula:
A. Contare le celle colorate: = Colorfunction (A, B: C, FALSE)
B. Sommare le celle colorate: = Colorfunction (A, B: C, VERO)

Nota: nelle formule sopra, A è la cella con il particolare colore di sfondo che vuoi calcolare il conteggio e la somma, e AVANTI CRISTO è l'intervallo di celle in cui si desidera calcolare il conteggio e la somma.

4. Prendi la seguente schermata per esempio, inserisci la formula= Colorfunction (A1, A1: D11, FALSE) contare le celle gialle. E usa la formula = Colorfunction (A1, A1: D11, VERO) per sommare le celle gialle. Vedi screenshot:

5. Se si desidera contare e sommare altre celle colorate, ripetere il passaggio 4. Quindi otterrai i seguenti risultati:


Contare e sommare le celle in base al colore di riempimento specifico con le funzioni di Kutools

Kutools per Excel supporta anche alcune funzioni utili per aiutare gli utenti di Excel a fare calcoli speciali, dice il conteggio per colore di sfondo della cella, somma per colore del carattere, ecc.

Kutools for Excel - Include più di 300 utili strumenti di Excel. 60-giorno di prova gratuita per tutte le funzionalità, nessuna carta di credito richiesta! Scarica la guida!

1. Selezionare la cella vuota in cui si posizionano i risultati del conteggio e fare clic Kutools > Funzioni di Kutools > Statistica e matematica > COUNTBYCELLCOLOR. Vedi screenshot:

2. Nella finestra di dialogo Argomenti della funzione, specificare l'intervallo in cui verranno contate le celle colorate all'interno di Riferimento casella, selezionare la cella che viene riempita dal colore di sfondo specificato nel Color_index_nr casella e fare clic su OK pulsante. Vedi screenshot:

Note:
(1) È inoltre possibile digitare la funzione Kutools specificata = COUNTBYCELLCOLOR ($ A $ 1: $ E $ 20, G2) nella cella vuota o nella barra della formula direttamente per ottenere i risultati del conteggio;
(2) Fare clic Kutools > Funzioni di Kutools > Statistica e matematica > SUMBYCELLCOLOR o digita = SUMBYCELLCOLOR ($ A $ 1: $ E $ 20, G2) nella cella vuota direttamente per sommare le celle in base al colore di sfondo specificato.
applicare il COUNTBYCELLCOLOR e SUMBYCELLCOLOR funzioni per ogni colore di sfondo separatamente, e otterrai i risultati come mostrato di seguito:

Funzioni di Kutools contiene una serie di funzioni integrate per aiutare gli utenti di Excel a calcolare facilmente, incluso Conteggio / somma / media delle celle visibili, Conta / Somma per colore cella, Contare / Somma per colore del carattere, Conta personaggi, Contare per carattere in grassetto, Etc. Fai una prova gratuita!


Contare e sommare le celle in base al colore di riempimento specifico con Kutools per Excel

Con la funzione definita dall'utente sopra, è necessario inserire la formula uno per uno, se ci sono molti colori diversi, questo metodo sarà noioso e dispendioso in termini di tempo. Ma se lo hai Kutools for Excel'S Contare per colore utilità, è possibile generare rapidamente un report delle celle colorate. Non solo puoi contare e sommare le celle colorate, ma puoi anche ottenere i valori medi, massimi e minimi dell'intervallo colorato.

Kutools for Excel - Include più di 300 utili strumenti di Excel. 60-giorno di prova gratuita per tutte le funzionalità, nessuna carta di credito richiesta! Scarica la guida!

1. Selezionare l'intervallo che si desidera utilizzare e fare clic Kutools Plus > Contare per colore, vedi screenshot:

2. E nella Contare per colore finestra di dialogo, si prega di fare come mostrato di seguito screenshot:
(1) Seleziona Formattazione standard dal Metodo di colore menu `A tendina;
(2) Seleziona sfondo dal Tipo di conteggio menu `A tendina.
(3) Fai clic sul pulsante Genera rapporto.

Note: Per contare e sommare le celle colorate secondo il colore di formattazione condizionale specifico, selezionare Formattazione condizionale dal Metodo di colore menu a discesa nella finestra di dialogo precedente o selezionare Formattazione standard e condizionale dall'elenco a discesa per contare tutte le celle riempite dal colore specificato.

Ora otterrai una nuova cartella di lavoro con le statistiche. Vedi screenshot:

L' Contare per colore la funzione calcola le celle (Count, Sum, Average, Max, ecc.) in base al colore di sfondo o al colore del carattere. Fai una prova gratuita!


Articolo correlato:

Come contare / sommare le celle in base ai colori dei caratteri in Excel?


Demo: contare e sommare le celle in base allo sfondo, al colore di formattazione condizionale:

In questo video, il Kutools scheda e il Kutools Plus scheda sono aggiunti da Kutools for Excel. Se necessario, fare clic qui avere una prova gratuita 60 di giorni senza limiti!



Strumenti di produttività consigliati

Office Tab

stella d'oro1 Porta pratiche schede ad Excel e ad altri software Office, proprio come Chrome, Firefox e il nuovo Internet Explorer.

Kutools for Excel

stella d'oro1 Stupefacente! Aumenta la tua produttività in 5 minuti. Non servono abilità speciali, risparmiate due ore al giorno!

stella d'oro1 300 Nuove funzionalità per Excel, rendono Excel molto semplice e potente:

  • Unisci cella / righe / colonne senza perdere dati.
  • Combina e consolida più fogli e cartelle di lavoro.
  • Confronta intervalli, copia intervalli multipli, conversione testo in data, unità e conversione valuta.
  • Contare in base a colori, totali parziali cercapersone, ordinamento avanzato e filtro super,
  • Altro Seleziona / Inserisci / Elimina / Testo / Formato / Link / Commento / Cartelle di lavoro / Fogli di lavoro Strumenti ...

Schermata di Kutools per Excel

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Mori · 1 months ago
    I copied and paste but calculation result is "0" why?? I am using Office 2016.
  • To post as a guest, your comment is unpublished.
    Troy Havard · 1 months ago
    I've copied and pasted as stated and have used this formula for over a year but recently saved the workbook to a new name and now the function doesn't work! I can't figure out what the deal is and I'm losing hair and sleep over it! Loading the original workbook, the formula works like a charm but going back to the new one, it doesn't! I've loaded VBA and tried re-creating the function but it doesn't work. Using Office 2019 - any help appreciated.
    • To post as a guest, your comment is unpublished.
      Jku · 1 months ago
      #NAME clearly indicates some keyword is not used in 2019, say, Interior.ColorIndex. Try to figure out changing the code from minimum lines to the full by adding one by one or search for the keywords in Excel/VBA in 2019
    • To post as a guest, your comment is unpublished.
      Troy Havard · 1 months ago
      I should probably have also noted that I get a #NAME? error in the cell in which I try to use the function.
  • To post as a guest, your comment is unpublished.
    Karolin · 3 months ago
    works fine, except it does not update the value in the totals when i change a cell to a diffrent colour. i have to click in the totals cell again to trigger a recount. Any Ideas how to fix this?
    • To post as a guest, your comment is unpublished.
      Arnier Hamz · 2 months ago
      Same here, i tried Ctrl+Alt+F9 or Ctrl+Shift+Alt+F9 to recalculate all formula and it works
  • To post as a guest, your comment is unpublished.
    Aaron · 4 months ago
    This doesn't work at all. It's total BS.
  • To post as a guest, your comment is unpublished.
    Schmingy · 6 months ago
    I was experiencing issues when attempting to use this function as-is to count/sum the lighter (pastel?) colors, at least in Excel 2016. Some debugging led me to realize that the range of "ColorIndex" values is quite limited, and the function above fails to distinguish between light yellow and light orange, reading both as ColorIndex = 19.

    Using the "Color" attribute, instead (as in "lCol = rColor.Interior.Color" and the two instances of "If rCell.Interior.Color = lCol Then", per the sample code above), resolved the issue.