Come contare e sommare le celle in base al loro colore

In questo articolo potrete imparare a contare le celle in base al loro colore in Excel e ottenere la somma delle celle colorate. Le soluzioni che vi presenteremo funzionano sia per le celle colorate “manualmente”, sia per quelle colorate con la formattazione condizionale.

Se siete soliti utilizzare un diverso colore di riempimento, oppure un diverso colore nei caratteri utilizzati nei vostri fogli di lavoro di Excel, per distinguere tra i vari tipi di celle o di valori, potreste voler sapere quante celle sono evidenziate in un certo modo. Se i valori delle vostre celle sono numeri, potreste anche voler calcolare automaticamente la somma delle celle con un dato colore di riempimento per avere, ad esempio, la somma di tutti le celle rosse.

Come tutti noi sappiamo, Microsoft Excel dispone di una varietà di formule per scopi diversi, e sarebbe logico supporre che alcune di esse possano aiutarci a contare le celle in base al loro colore. Ma purtroppo, non esiste una formula con queste caratteristiche.

A parte l’utilizzo di componenti aggiuntivi di terze parti, abbiamo una sola soluzione: utilizzare le User Defined Functions. Se conoscete poco di questa tecnologia oppure non l’avete mai sentita nominare, non abbiate paura, non sarà necessario scrivere del codice manualmente. Troverete qui il codice già pronto e tutto quello che dovrete fare è copiarlo/incollarlo nella cartella di lavoro.

Come contare o sommare per colore in un foglio di lavoro di Excel

Supponiamo di avere una tabella che elenca gli ordini della vostra azienda in cui le celle della colonna “Consegna” sono colorate in base ai loro valori: “Entro X giorni” le celle sono di colore giallo, “Consegnato” sono verdi e consegne “Scadute” sono di colore rosso.

Quello che vogliamo è effettuare automaticamente il conteggio delle celle in base al colore, ad esempio contare il numero di celle rosse, verdi e gialle nel foglio di lavoro. Come ho spiegato in precedenza, non esiste una soluzione semplice per questo compito. Ma per fortuna abbiamo la possibilità di usare il codice VBA (valido per Excel 2010 e 2013). Ecco la procedura illustrata passo passo:

Aprite la vostra cartella di lavoro di Excel e premere Alt + F11 per aprire il Visual Basic Editor (VBE)
Fate clic destro sul nome della vostra cartella sotto “VBAProject” nella parte destra dello schermo, quindi scegliete Inserisci > Modulo dal menu di scelta rapida

Aggiungete il seguente codice al foglio di lavoro:


Function TrovaColoreCella(xlIntervallo As Range)
    Dim indRiga, indColonna As Long
    Dim arRisultati()
 
    Application.Volatile
 
    If xlIntervallo Is Nothing Then
        Set xlIntervallo = Application.ThisCell
    End If
 
    If xlIntervallo.Count > 1 Then
      ReDim arRisultati(1 To xlIntervallo.Rows.Count, 1 To xlIntervallo.Columns.Count)
       For indRiga = 1 To xlIntervallo.Rows.Count
         For indColonna = 1 To xlIntervallo.Columns.Count
           arRisultati(indRiga, indColonna) = xlIntervallo(indRiga, indColonna).Interior.Color
         Next
       Next
     TrovaColoreCella = arRisultati
    Else
     TrovaColoreCella = xlIntervallo.Interior.Color
    End If
End Function
 
Function TrovaColoreCarattere(xlIntervallo As Range)
    Dim indRiga, indColonna As Long
    Dim arRisultati()
 
    Application.Volatile
 
    If xlIntervallo Is Nothing Then
        Set xlIntervallo = Application.ThisCell
    End If
 
    If xlIntervallo.Count > 1 Then
      ReDim arRisultati(1 To xlIntervallo.Rows.Count, 1 To xlIntervallo.Columns.Count)
       For indRiga = 1 To xlIntervallo.Rows.Count
         For indColonna = 1 To xlIntervallo.Columns.Count
           arRisultati(indRiga, indColonna) = xlIntervallo(indRiga, indColonna).Font.Color
         Next
       Next
     TrovaColoreCarattere = arRisultati
    Else
     TrovaColoreCarattere = xlIntervallo.Font.Color
    End If
 
End Function
 
Function ContaCellePerColore(rData As Range, cellRefColor As Range) As Long
    Dim indRefColor As Long
    Dim cellaCorrente As Range
    Dim cntRes As Long
 
    Application.Volatile
    cntRes = 0
    indRefColor = cellRefColor.Cells(1, 1).Interior.Color
    For Each cellaCorrente In rData
        If indRefColor = cellaCorrente.Interior.Color Then
            cntRes = cntRes + 1
        End If
    Next cellaCorrente
 
    ContaCellePerColore = cntRes
End Function
 
Function SommaCellePerColore(rData As Range, cellRefColor As Range)
    Dim indRefColor As Long
    Dim cellaCorrente As Range
    Dim sumRes
 
    Application.Volatile
    sumRes = 0
    indRefColor = cellRefColor.Cells(1, 1).Interior.Color
    For Each cellaCorrente In rData
        If indRefColor = cellaCorrente.Interior.Color Then
            sumRes = WorksheetFunction.Sum(cellaCorrente, sumRes)
        End If
    Next cellaCorrente
 
    SommaCellePerColore = sumRes
End Function
 
Function ContaCellePerColoreCarattere(rData As Range, cellRefColor As Range) As Long
    Dim indRefColor As Long
    Dim cellaCorrente As Range
    Dim cntRes As Long
 
    Application.Volatile
    cntRes = 0
    indRefColor = cellRefColor.Cells(1, 1).Font.Color
    For Each cellaCorrente In rData
        If indRefColor = cellaCorrente.Font.Color Then
            cntRes = cntRes + 1
        End If
    Next cellaCorrente
 
    ContaCellePerColoreCarattere = cntRes
End Function
 
Function SommaCellePerColoreCarattere(rData As Range, cellRefColor As Range)
    Dim indRefColor As Long
    Dim cellaCorrente As Range
    Dim sumRes
 
    Application.Volatile
    sumRes = 0
    indRefColor = cellRefColor.Cells(1, 1).Font.Color
    For Each cellaCorrente In rData
        If indRefColor = cellaCorrente.Font.Color Then
            sumRes = WorksheetFunction.Sum(cellaCorrente, sumRes)
        End If
    Next cellaCorrente
 
    SommaCellePerColoreCarattere = sumRes
End Function

Salvate la cartella di lavoro come “Cartella di lavoro con attivazione macro di Excel (.xlsm)”. Se non siete a vostro agio con VBA, potete trovare le istruzioni dettagliate passo-passo e alcuni consigli utili in questo tutorial: Come inserire ed eseguire codice VBA in Excel
Ora che tutti i lavori “dietro le quinte” vengono effettuati per voi dalla User Defined Function appena aggiunta, selezionate la cella in cui desiderate l’output dei risultati e immettete la funzione ContaCellePerColore:

ContaCellePerColore(intervallo; codice colore)

In questo esempio, abbiamo usato la formula =ContaCellePerColore(D2:D19;A22) dove D2:D19 è l’intervallo contenente le celle colorate che desiderate contare e A22 è la cella con un determinato colore di sfondo, uno rosso nel nostro caso.

In modo simile, inserite la formula per gli altri colori che desiderate contare: giallo e verde nella nostra tabella.

Se avete dati numerici nelle celle colorate (ad esempio, la colonna “Quantità” nella nostra tabella), è possibile sommarne i valori sulla base di un certo colore, utilizzando la funzione analoga SommaCellePerColore:

SommaCellePerColore(intervallo; codice colore)

Come mostrato nello screenshot qui sopra, abbiamo usato la formula =SommaCellePerColore(C2:C19;A22) dove C2:C19 è l’intervallo e A22 è la cella con un determinato colore.

In modo simile potete contare o sommare celle in base al colore del carattere utilizzando rispettivamente le funzioni ContaCellePerColoreCarattere e SommaCellePerColoreCarattere.

ATTENZIONE

Se dopo l’applicazione del codice VBA di cui sopra aveste bisogno di colorare manualmente altre celle dell’intervallo, la somma e il conteggio delle celle colorate non verrebbe automaticamente ricalcolato. Non arrabbiatevi con noi, non si tratta di un bug del codice:) In realtà, è il comportamento normale di tutte le macro di Excel, script VBA e User Defined Functions. Il punto è che tutte queste funzioni vengono richiamate solo con una variazione e Excel non percepisce il cambiamento del colore del carattere o dello sfondo come una modifica dei dati del foglio di lavoro. Così, dopo aver colorato manualmente le celle, è sufficiente posizionare il cursore in una cella e premere F2 seguito dal tasto Invio: la somma e il conteggio verranno aggiornati. Lo stesso vale per le altre macro che troverete a seguire in questo articolo.

Sommare e contare per colore su tutta la cartella di lavoro

Se aveste la necessità di contare o sommare le celle con un determinato colore presenti in tutta una cartella di lavoro, occorre inserire alcune righe di codice aggiuntive. Potete copiarle direttamente dal box qui sotto:



Function CartellaContaCellePerColore(cellRefColor As Range)
    Dim vWbkRes
    Dim foglioCorrente As Worksheet
 
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
 
    vWbkRes = 0
    For Each foglioCorrente In Worksheets
       foglioCorrente.Activate
       vWbkRes = vWbkRes + ContaCellePerColore(foglioCorrente.UsedRange, cellRefColor)
    Next
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
 
    CartellaContaCellePerColore = vWbkRes
End Function
 
Function CartellaSommaCellePerColore(cellRefColor As Range)
    Dim vWbkRes
    Dim foglioCorrente As Worksheet
 
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
 
    vWbkRes = 0
    For Each foglioCorrente In Worksheets
       foglioCorrente.Activate
       vWbkRes = vWbkRes + SommaCellePerColore(foglioCorrente.UsedRange, cellRefColor)
    Next
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
 
    CartellaSommaCellePerColore = vWbkRes
End Function

Questa macro si utilizza nello stesso modo del codice precedente e restituisce il conteggio e la somma delle celle colorate usando rispettivamente le seguenti formule, =CartellaContaCellePerColore() e =CartellaSommaCellePerColore(). Basta inserire la formula in una cella vuota su qualsiasi foglio senza definire un intervallo, specificare tra parentesi l’indirizzo di una cella con il colore che volete contare o sommare, ad esempio =CartellaSommaCellePerColore(A1), e la formula visualizzerà la somma di tutte le celle colorate con lo stesso colore della cella indicata.

Funzioni personalizzate per ottenere il colore di sfondo, il colore del carattere e il codice del colore di una cella

Qui troverete un riepilogo di tutte le funzioni che abbiamo usato in questo esempio, così come un paio di funzioni nuove che recuperano i codici colore.

ATTENZIONE

Ricordiamo che tutte queste formule funzionano solo se avete aggiunto la User Defined Function come mostrato in precedenza in questo articolo

Funzioni per contare per colore:

ContaCellePerColore(intervallo; codice colore) – conta le celle con il colore di sfondo specificato. Nel precedente esempio, abbiamo utilizzato la seguente formula per contare le celle per colore =ContaCellePerColore(D2:D19;A22) dove D2:D19 è l’intervallo e A22 è la cella con il colore di sfondo che vogliamo contare. È possibile utilizzare tutte le altre formule elencate qui di seguito in modo simile.
ContaCellePerColoreCarattere(intervallo; codice colore) – conta le celle con il colore del carattere specificato.

Funzioni per sommare per colore:

SommaCellePerColore(intervallo; codice colore) – calcola la somma delle celle con un determinato colore di sfondo.
SommaCellePerColoreCarattere(intervallo; codice colore) – calcola la somma delle celle con un certo colore del carattere.

Funzioni per ottenere il codice del colore:

TrovaColoreCella(cella) – restituisce il codice colore del colore di sfondo di una cella specificata.
TrovaColoreCarattere(cella) – restituisce il codice colore del colore del carattere di una cella specificata.

Beh, ottenere il conteggio o la somma delle celle in base al colore è stato abbastanza facile, non è vero? Naturalmente questo accade se avete quel piccolo gioiello VBA che fa la magia 🙂 Ma cosa succede se non si colorano celle a mano e piuttosto si utilizza la formattazione condizionale come dettagliato in questi articoli?

Come contare o sommare le celle che sono state colorate utilizzando la formattazione condizionale

Se avete applicato la formattazione condizionale per colorare le celle in base ai loro valori e ora volete contare o sommare tali celle in base al loro colore, ho una brutta notizia – non vi è alcuna User Defined Function universale che somma o conteggia in base al colore e fornisce l’output dei numeri risultanti direttamente nelle celle specificate. Almeno, io non sono a conoscenza di tale funzione, ahimè 🙁

Naturalmente, si possono trovare tonnellate di codice VBA su Internet che cercano di ottenere questo risultato, ma tutti i codici (almeno gli esempi che ho incontrato), non riescono ad elaborare le formattazioni condizionali del tipo “Formatta tutte le celle in base ai loro valori”, “Formatta solo i primi e gli ultimi valori”, “Formatta solo i valori che sono al di sopra o al di sotto della media”, “Formatta solo i valori univoci o duplicati”. In aggiunta, quasi tutti i codici VBA hanno una serie di specificità e dei limiti per i quali spesso potrebbero non funzionare correttamente con determinati cartelle di lavoro o tipi di dati.

Il codice VBA qui sotto supera i limiti di cui sopra e funziona nei fogli di calcolo di Microsoft Excel 2010 ed Excel 2013 con tutti i tipi di formattazione condizionale.

Come risultato, viene visualizzato il numero di celle colorate e la somma dei valori di tali celle, indipendentemente dal tipo di formattazione condizionali che è utilizzata nel foglio.


Sub SommaContaPerFormattazioneCondizionale()
    Dim indRefColor As Long
    Dim cellaCorrente As Range
    Dim cntRes As Long
    Dim sumRes
    Dim contaCelle As Long
    Dim indCellaCorrente As Long
 
    cntRes = 0
    sumRes = 0
 
    contaCelle = Selection.CountLarge
    indRefColor = ActiveCell.DisplayFormat.Interior.Color
 
    For indCellaCorrente = 1 To (contaCelle - 1)
        If indRefColor = Selection(indCellaCorrente).DisplayFormat.Interior.Color Then
            cntRes = cntRes + 1
            sumRes = WorksheetFunction.Sum(Selection(indCellaCorrente), sumRes)
        End If
    Next
   MsgBox "Conteggio=" & cntRes & vbCrLf & "Somma= " & sumRes & vbCrLf & vbCrLf & _
        "Colore=" & Left("000000", 6 - Len(Hex(indRefColor))) & _
        Hex(indRefColor) & vbCrLf, , "Conteggio e Somma per colore di Formattazione Condizionale"
End Sub

Come utilizzare il codice per contare le celle colorate e sommare i loro valori

Aggiungete il codice qui sopra per il foglio di lavoro, come spiegato nel primo esempio
Selezionate un intervallo o gli intervalli per i quali si desidera contare le celle colorate o/e sommare in base al colore, se contengono dati numerici
Premete e tenete premuto Ctrl, selezionate una cella con il colore che volete contare o sommare, e poi rilasciate il tasto Ctrl
Premete Alt + F8 per aprire l’elenco delle macro presenti nella cartella di lavoro
Selezionate la macro SommaContaPerFormattazioneCondizionale e fate clic su Esegui

Di conseguenza, verrà visualizzato il seguente messaggio:

Per questo esempio, abbiamo selezionato la colonna Quantità e abbiamo ottenuto i seguenti numeri:

Conteggio è il numero delle celle con il colore selezionato
Somma è la somma dei valori di tutte le celle di quel colore presenti nella colonna Quantità
Colore è il codice esadecimale del colore della cella selezionata, C2 nel nostro caso

Cartella di lavoro di esempio per il download

Se riscontrate delle difficoltà con l’aggiunta dello script alle vostre cartelle di lavoro di Excel, come ad esempio errori di compilazione, formule che non funzionano, e così via, scaricate pure la cartella di lavoro di esempio con tutte le funzioni e macro spiegate nell’articolo pronte per l’uso.

#Excel – Come contare e sommare le celle in base al loro colore