I cookie ci aiutano i nostri servizi. Utilizzando i nostri servizi, l'utente accetta di utilizzare i cookie.
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

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 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.

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.

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!

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:


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.
    Laxmannarayanan · 5 years ago
    Wonderful!!! Thank you so much!!!
  • To post as a guest, your comment is unpublished.
    imamoglu · 5 years ago
    Very useful article, thanks a lot
  • To post as a guest, your comment is unpublished.
    Petr · 5 years ago
    Very good, thanks a lot !!!
  • To post as a guest, your comment is unpublished.
    Gowrisankar · 5 years ago
    Very useful tool , Thank you verymuch
    • To post as a guest, your comment is unpublished.
      HR · 4 years ago
      Very useful. Thank you
      • To post as a guest, your comment is unpublished.
        Aniko · 2 years ago
        I also tried the formula.
        Works in the first row and does not with second and third.

        ????
        • To post as a guest, your comment is unpublished.
          Mark · 2 years ago
          Try it like this:

          1. be certain you have the function saved as described

          2. permit me to use an example
          * I have hundreds of rows of data
          * I use columns A to AB with more data
          * whenever I have an issue with my data, I highlight it in yellow
          * I use this cool formula to 'count' the number of highlights on each row

          3. How to count the number of my highlights per row
          a. figure[u] the range of cells[/u]that could have highlights that you want to count (or sum)
          * for me, I want to COUNT the quantity of my flagged highlights on each row (my range)
          b. pick a cell where you will report the count (or sum)
          * for me, I placed it on the far right of my data...in column AE
          c. insert the following formula in the cell you chose in item b (above)
          =colorfunction(AE3,A3:AB3,FALSE)
          * for me, I placed this formula in cell AE3 (the end of my row) AND AND I highlighted the same formula cell Yellow
          d. I then copied this formula down for all my data rows

          4. I observed
          a. no counts were made. (bad)

          5 KEY ISSUE HERE:
          a. I pressed CTL+ ALT + F9
          . PRESTO !!! it works.!

          hope this banter is of some help.

          Cheers




          do this:

          A B C D
          1
          2
          3
          4
        • To post as a guest, your comment is unpublished.
          Mark · 2 years ago
          I agree with Jarod.

          The first two lines of my function says:

          Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
          Application.Volatile

          The problem is that only a Alt-Ctrl-F9 will re-calculate. We're looking for a more automated solution. Ideas?
    • To post as a guest, your comment is unpublished.
      Gowrisankar · 5 years ago
      After inserting the function , when we change the value the sum not automatically udpated , any suggestion
      • To post as a guest, your comment is unpublished.
        Jacqui · 2 years ago
        I double clicked the cell with the ColorFunction formula, and then pressed Enter. It updated.
      • To post as a guest, your comment is unpublished.
        12345678998765432154 · 3 years ago
        Add a second line directly below the first that says Application.volitile it makes it recalculate after something is updated
        • To post as a guest, your comment is unpublished.
          abrugg · 2 years ago
          [quote name="12345678998765432154"]Add a second line directly below the first that says Application.volitile it makes it recalculate after something is updated[/quote]

          Application.Volatile is the correct function not Application.volitile
          • To post as a guest, your comment is unpublished.
            Jo · 2 years ago
            I don't see any line that says application.volatile.
            Where do I find it?
        • To post as a guest, your comment is unpublished.
          Jarod · 3 years ago
          I tried to put [b]Application.Volatile[/b] unde the first line and nothing hapens :(
      • To post as a guest, your comment is unpublished.
        WestwoodMike · 3 years ago
        Alt-Ctrl-F9 will re-calculate
        • To post as a guest, your comment is unpublished.
          Mike Brannigan · 1 years ago
          You can add and update button if someone else is using the file and they don't know about the Alt-Ctrl-F9. just add this line of code to the macro "Application.CalculateFull". it's that same as Alt-Ctrl-F9 just programmed into a button.