Vai al contenuto principale

Suggerimenti per Excel: contare/sommare le celle per colore (sfondo, carattere, formattazione condizionale)

Nelle attività quotidiane, la marcatura a colori è un metodo popolare per distinguere ed evidenziare rapidamente i dati cruciali. Ma come contiamo o sommiamo i dati delle celle in base a un colore specifico (colore di riempimento, colore del carattere, formattazione condizionale)? Per impostazione predefinita, Excel non offre una funzionalità diretta per contare o sommare in base al colore. Tuttavia, con alcuni trucchi e metodi indiretti, possiamo ancora raggiungere questo obiettivo. Questo articolo esplorerà come contare o sommare i dati per colore.

Contare e sommare le celle in base al colore di sfondo

Contare e sommare le celle in base al colore del carattere

Contare e sommare le celle in base al colore della formattazione condizionale


Video: contare e sommare le celle in base al colore


Contare e sommare le celle in base al colore di sfondo

Ad esempio, se disponi di un intervallo di dati in cui i valori sono riempiti con colori di sfondo diversi, come mostrato nello screenshot seguente. Per contare o sommare le celle in base a un colore specifico, Excel non offre una funzionalità diretta per contare o sommare le celle in base al colore di sfondo. Tuttavia, con un po' di ingegno e alcune tecniche utili, puoi portare a termine questo compito. Esploriamo alcuni metodi utili in questa sezione.


Conta e somma le celle in base al colore di sfondo con la funzione definita dall'utente

Qui ti mostreremo come creare e utilizzare una funzione definita dall'utente per risolvere questa attività in Excel. Si prega di eseguire i seguenti passaggi:

Passaggio 1: apri l'editor del modulo VBA e copia il codice

  1. Stampa Alt + F11 chiavi per aprire il Microsoft Visual Basic, Applications Edition finestra.
  2. Nella finestra aperta, fare clic inserire > Moduli per creare un nuovo modulo vuoto.
  3. Quindi, copia e incolla il codice seguente nel modulo vuoto.
    Codice VBA: conta e somma le celle in base al colore di sfondo
    Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean = False) As Variant
    'Updateby Extendoffice
        Dim rCell As Range
        Dim lCol As Long
        Dim vResult As Double
        lCol = rColor.Interior.ColorIndex
        vResult = 0
        If SUM Then
            For Each rCell In rRange
                If rCell.Interior.ColorIndex = lCol Then
                    vResult = vResult + rCell.Value
                End If
            Next rCell
        Else
            For Each rCell In rRange
                If rCell.Interior.ColorIndex = lCol Then
                    vResult = vResult + 1
                End If
            Next rCell
        End If
        ColorFunction = vResult
    End Function
    

Passaggio 2: crea formule per contare e sommare le celle in base al colore di sfondo

Dopo aver incollato il codice sopra, chiudi la finestra del modulo, quindi applica le seguenti formule:

  • Contare le celle in base al colore di sfondo specifico:
    Copia o digita la formula fornita di seguito nella cella desiderata per il risultato. Quindi, trascina la maniglia di riempimento verso il basso per ottenere altri risultati. Vedi schermata:
    =colorfunction(G2,$B$2:$E$12,FALSE) 
    Note:: In questa formula, G2 è la cella di riferimento con il colore di sfondo specifico che desideri abbinare; $B$2:$E$12 è l'intervallo in cui vuoi contare il numero di celle del colore G2; FALSO viene utilizzato per contare le celle con colore corrispondente.
  • Somma le celle in base al colore di sfondo specifico:
    Copia o digita la formula fornita di seguito nella cella desiderata per il risultato. Quindi, trascina la maniglia di riempimento verso il basso per ottenere altri risultati. Vedi schermata:
    =colorfunction(G2,$B$2:$E$12,TRUE)  
    Note:: In questa formula, G2 è la cella di riferimento con il colore di sfondo specifico che desideri abbinare; $B$2:$E$12 è l'intervallo in cui vuoi contare il numero di celle del colore G2; TRUE viene utilizzato per sommare le celle con colore corrispondente.

Conta e somma le celle in base al colore di sfondo con una potente funzionalità

Per chi non ha familiarità con la programmazione, VBA può sembrare piuttosto complesso. Qui introdurremo uno strumento potente: Kutool per Excel, suo Conta per colore la funzione ti consente di calcolare facilmente (conteggio, somma, media, ecc.) in base al colore di sfondo in pochi clic. Impressionante, Conta per colore la funzionalità va oltre i semplici colori di sfondo: può anche differenziare e calcolare in base ai colori dei caratteri e alla formattazione condizionale.

Dopo shavasana, sedersi in silenzio; saluti; download e installazione di Kutools per Excel, seleziona innanzitutto l'intervallo di dati che desideri contare o sommare le celle in base a un colore di sfondo specifico. Quindi, vai a Kutools Plus e scegli Conta per colore.

Nel Conta per colore finestra di dialogo, specificare le operazioni:

  1. Seleziona Formattazione standard dal Metodo del colore menu `A tendina;
  2. Specificare sfondo dal Tipo di conteggio elenco a discesa ed è possibile visualizzare in anteprima i risultati statistici per ciascun colore di sfondo nella finestra di dialogo;
  3. Finalmente, clicca Genera rapporto per esportare i risultati calcolati in una nuova cartella di lavoro.

Risultato:

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

Suggerimenti::
  1. Le Conta per colore la funzionalità supporta anche il conteggio e la somma delle celle in base al colore del carattere standard, allo sfondo o al colore del carattere dalla formattazione condizionale e a una combinazione di colori di riempimento e di formattazione condizionale.
  2. Interessato a questa funzione, per favore fare clic per scaricare per ottenere una prova gratuita per 30 giorni.

Conta e somma le celle in base al colore di sfondo con la funzione Filtro e SUBTOTALE

Supponiamo di avere una tabella di vendita della frutta come mostrata nell'immagine sottostante e conteremo o sommeremo le celle colorate in Quantità colonna.

Passaggio 1: applicare la funzione SUBTOTALE

Seleziona le celle vuote per accedere alla funzione SUBTOTALE.

  • Per contare tutte le celle con lo stesso colore di sfondo, inserisci la formula:
    =SUBTOTAL(102, F2:F16)
  • Per sommare tutte le celle con lo stesso colore di sfondo, inserisci la formula;
    =SUBTOTAL(109, F2:F16)
  • Note:: nelle formule sopra, 102 rappresenta il conteggio dei valori numerici in un elenco filtrato escludendo le celle nascoste; 109 rappresenta per riassumere i valori in un elenco filtrato escludendo le celle nascoste; F2: F16 è l'intervallo su cui verrà calcolato il conteggio o la somma.

Passaggio 2: filtra le celle in base al colore specifico

  1. Selezionare l'intestazione della tabella e fare clic Dati > Filtro. Vedi screenshot:
  2. Clicca su Filtro icona  nella cella di intestazione del Quantità colonna e fare clic su filtra per colore e il colore specificato verrà conteggiato successivamente. Vedi screenshot:

Risultato:

Dopo il filtraggio, le formule SUBTOTALE contano e sommano automaticamente le celle colorate nel Quantità colonna. Vedi screenshot:

Note:: Questo metodo richiede che le celle colorate che conterai o che sommerai siano nella stessa colonna.

Contare e sommare le celle in base al colore del carattere

Vuoi contare o sommare le celle in base al colore del carattere in Excel? Supponiamo che tu abbia i dati, come nello screenshot fornito, con celle contenenti testi in colore rosso, blu, arancione e nero. Excel non lo semplifica per impostazione predefinita. Ma non preoccuparti! In questa sezione ti mostreremo alcuni semplici trucchi per farlo.


Contare e sommare le celle in base al colore del carattere con la funzione definita dall'utente

Per contare e sommare le celle con colori di carattere specifici, la seguente funzione definita dall'utente può aiutarti a risolvere questo compito. Si prega di eseguire i seguenti passaggi:

Passaggio 1: apri l'editor del modulo VBA e copia il codice

  1. Stampa Alt + F11 chiavi per aprire il Microsoft Visual Basic, Applications Edition finestra.
  2. Nella finestra aperta, fare clic inserire > Moduli per creare un nuovo modulo vuoto.
  3. Quindi, copia e incolla il codice seguente nel modulo vuoto.
    Codice VBA: conta e somma le celle in base al colore del carattere
    Function ProcessByFontColor(pRange1 As Range, pRange2 As Range, FunctionType As String) As Double
    'Updateby Extendoffice
        Application.Volatile
        Dim rng As Range
        Dim xTotal As Double
        Dim xCount As Double
        xTotal = 0
        xCount = 0
        For Each rng In pRange1
            If rng.Font.Color = pRange2.Font.Color Then
                If UCase(FunctionType) = "SUM" Then
                    xTotal = xTotal + rng.Value
                ElseIf UCase(FunctionType) = "COUNT" Then
                    xCount = xCount + 1
                End If
            End If
        Next
        If UCase(FunctionType) = "SUM" Then
            ProcessByFontColor = xTotal
        ElseIf UCase(FunctionType) = "COUNT" Then
            ProcessByFontColor = xCount
        Else
            ProcessByFontColor = CVErr(xlErrValue)
        End If
    End Function
    

Passaggio 2: crea formule per contare e sommare le celle in base al colore del carattere

Dopo aver incollato il codice sopra, chiudi la finestra del modulo, quindi applica le seguenti formule:

  • Contare le celle in base al colore del carattere specifico:
    Copia o digita la formula fornita di seguito nella cella desiderata per il risultato. Quindi, trascina la maniglia di riempimento verso il basso per ottenere altri risultati. Vedi schermata:
    =ProcessByFontColor($B$2:$E$12,G2, "COUNT")
    Note:: In questa formula, G2 è la cella di riferimento con il colore del carattere specifico che desideri abbinare; $B$2:$E$12 è l'intervallo in cui vuoi contare il numero di celle del colore G2.
  • Somma le celle in base al colore del carattere specifico:
    Copia o digita la formula fornita di seguito nella cella desiderata per il risultato. Quindi, trascina la maniglia di riempimento verso il basso per ottenere altri risultati. Vedi schermata:
    =ProcessByFontColor($B$2:$E$12,G2, "SUM")  
    Note:: In questa formula, G2 è la cella di riferimento con il colore del carattere specifico che desideri abbinare; $B$2:$E$12 è l'intervallo in cui vuoi contare il numero di celle del colore G2.

Conta e somma le celle in base al colore del carattere con una funzionalità semplice

Stai cercando di contare o sommare facilmente i valori delle celle in Excel in base al colore del carattere? Immergiti Kutools for Excel's Conta per colore caratteristica! Con questo strumento intelligente, contare e sommare le celle in base al colore del carattere specifico diventa un gioco da ragazzi. Scopri come Kutools può trasformare la tua esperienza con Excel.

Dopo shavasana, sedersi in silenzio; saluti; download e installazione di Kutools per Excel, seleziona innanzitutto l'intervallo di dati che desideri contare o sommare le celle in base a un colore di carattere specifico. Quindi, fare clic Kutools Plus > Conta per colore per aprire il Conta per colore la finestra di dialogo.

Nel Conta per colore finestra di dialogo, specificare le operazioni:

  1. Seleziona Formattazione standard dal Metodo del colore menu `A tendina;
  2. Specificare fonte dal Tipo di conteggio elenco a discesa e puoi visualizzare in anteprima i risultati statistici per ciascun colore del carattere nella finestra di dialogo;
  3. Finalmente, clicca Genera rapporto per esportare i risultati calcolati in una nuova cartella di lavoro.

Risultato:

Ora hai una nuova cartella di lavoro che mostra le statistiche dettagliate in base al colore del carattere. Vedi schermata:

Suggerimenti:: Interessato a questa funzione, per favore fare clic per scaricare per ottenere una prova gratuita per 30 giorni.

Contare e sommare le celle in base al colore della formattazione condizionale

In Excel, è possibile utilizzare comunemente la formattazione condizionale per applicare un colore specifico alle celle che soddisfano determinati criteri, rendendo intuitiva la visualizzazione dei dati. Ma cosa succede se devi contare o sommare quelle celle appositamente formattate? Sebbene Excel non offra un modo diretto per farlo, ecco alcuni modi per aggirare questa limitazione.


Contare e sommare celle formattate in modo condizionale con codice VBA

Contare e sommare le celle formattate in modo condizionale in Excel non è semplice utilizzando le funzioni integrate. Tuttavia, puoi eseguire questa attività utilizzando il codice VBA. Esaminiamo come utilizzare VBA per questo:

Passaggio 1: apri l'editor del modulo VBA e copia il codice

  1. Stampa Alt + F11 chiavi per aprire il Microsoft Visual Basic, Applications Edition finestra.
  2. Nella finestra aperta, fare clic inserire > Moduli per creare un nuovo modulo vuoto.
  3. Quindi, copia e incolla il codice seguente nel modulo vuoto.
    Codice VBA: conta e somma le celle in base al colore della formattazione condizionale
    Sub SumCountByConditionalFormat()
    'Updateby Extendoffice
        Dim sampleColor As Range
        Dim selectedRange As Range
        Dim cell As Range
        Dim countByColor As Long
        Dim sumByColor As Double
        Dim refColor As Long
        Set selectedRange = Application.InputBox("Select a range to evaluate:", _
                                                 "Kutools for Excel", _
                                                 Type:=8)
        If selectedRange Is Nothing Then Exit Sub
        Set sampleColor = Application.InputBox("Select a conditional formatting color:", _
                                               "Kutools for Excel", _
                                               Type:=8)
        If Not sampleColor Is Nothing Then
            refColor = sampleColor.Cells(1, 1).DisplayFormat.Interior.color
            For Each cell In selectedRange
                If cell.DisplayFormat.Interior.color = refColor Then
                    countByColor = countByColor + 1
                    sumByColor = sumByColor + cell.Value
                End If
            Next cell
            MsgBox "Count: " & countByColor & vbCrLf & _
                   "Sum: " & sumByColor, _
                   vbInformation, "Results based on Conditional Format Color"
        End If
    End Sub
    

Passaggio 2: esegui questo codice VBA

  1. Dopo aver incollato il codice, premere F5 chiave per eseguire questo codice, verrà visualizzata una finestra di messaggio, seleziona l'intervallo di dati in cui desideri contare e sommare le celle in base alla formattazione condizionale. Quindi, fare clic OK, Vedi screenshot:
  2. In un'altra finestra di messaggio, seleziona un colore di formattazione condizionale specifico che desideri contare e sommare, quindi fai clic OK pulsante, vedi screenshot:

Risultato:

Ora, il risultato, che include sia il conteggio che la somma delle celle con il colore di formattazione condizionale specificato, verrà visualizzato nella casella spuntata. Vedi schermata:


Conta e somma le celle formattate in modo condizionale con una funzionalità intelligente

Se stai cercando altri metodi semplici e veloci per contare e sommare le celle formattate in modo condizionale, Kutools for Excel è la soluzione giusta. Suo Conta per colore funzione può risolvere questo compito in pochi clic. Immergiti per scoprire l'efficienza e la precisione che Kutools può apportare al tuo flusso di lavoro.

Dopo shavasana, sedersi in silenzio; saluti; download e installazione di Kutools per Excel, seleziona innanzitutto l'intervallo di dati che desideri contare o sommare le celle in base a uno specifico colore di formattazione condizionale. Quindi, fare clic Kutools Plus > Conta per colore per aprire il Conta per colore la finestra di dialogo.

Nel Conta per colore finestra di dialogo, specificare le operazioni:

  1. Seleziona Formattazione condizionale dal Metodo del colore menu `A tendina;
  2. Specificare sfondo dal Tipo di conteggio elenco a discesa ed è possibile visualizzare in anteprima i risultati statistici per ogni colore di formattazione condizione nella finestra di dialogo;
  3. Finalmente, clicca Genera rapporto per esportare i risultati calcolati in una nuova cartella di lavoro.

Risultato:

Ora hai una nuova cartella di lavoro che mostra le statistiche dettagliate in base al colore della formattazione condizionale. Vedi schermata:

Suggerimenti:: Interessato a questa funzione, per favore fare clic per scaricare per ottenere una prova gratuita per 30 giorni.

Articoli Correlati:

  • Filtra i dati per più colori
  • Normalmente, in Excel, puoi filtrare rapidamente righe con un solo colore, ma hai mai considerato di filtrare righe con più colori contemporaneamente? In questo articolo, parlerò di un trucco rapido per affrontare questo problema.
  • Aggiungi colore all'elenco a discesa
  • In Excel, creare un elenco a discesa può aiutarti molto e, a volte, è necessario codificare a colori i valori dell'elenco a discesa in base al corrispondente selezionato. Ad esempio, ho creato un elenco a discesa dei nomi dei frutti, quando seleziono Apple, ho bisogno che la cella venga colorata automaticamente di rosso e quando scelgo Arancione, la cella può essere colorata di arancione.
  • Colora le righe alternate per le celle unite
  • È molto utile formattare righe alternative con un colore diverso in un dato di grandi dimensioni per consentirci di eseguire la scansione dei dati, ma, a volte, potrebbero esserci alcune celle unite nei dati. Per evidenziare le righe alternativamente con un colore diverso per le celle unite come mostrato nell'immagine sottostante, come potresti risolvere questo problema in Excel?