fbpx

#Excel – 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.

La tabella originale con le celle colorate in base al loro valore

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:

  1. Aprite la vostra cartella di lavoro di Excel e premere Alt + F11 per aprire il Visual Basic Editor (VBE)
  2. Fate clic destro sul nome della vostra cartella sotto “VBAProject” nella parte destra dello schermo, quindi scegliete Inserisci > Modulo dal menu di scelta rapida
    Cliccate Inserisci - Modulo per aggiungere una User Defined Function al foglio di lavoro
  3. Aggiungete il seguente codice al foglio di lavoro:
    [codesyntax lang=”vb”]
    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
    
    [/codesyntax]
  4. 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
  5. 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.

La formula per contare le celle per colore di sfondo

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)

La formula per sommare celle per colore di sfondo

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.

Le formule per contare e sommare celle per colore carattere

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:

[codesyntax lang=”vb”]

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
[/codesyntax]

 

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.

La formula per ottenere il colore del carattere

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.

[codesyntax lang=”vb”]

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

[/codesyntax]

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

  1. Aggiungete il codice qui sopra per il foglio di lavoro, come spiegato nel primo esempio
  2. 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
  3. Premete e tenete premuto Ctrl, selezionate una cella con il colore che volete contare o sommare, e poi rilasciate il tasto Ctrl
  4. Premete Alt + F8 per aprire l’elenco delle macro presenti nella cartella di lavoro
  5. Selezionate la macro SommaContaPerFormattazioneCondizionale e fate clic su Esegui

Eseguire una macro per contare o sommare celle colorate con formattazione condizionale

Di conseguenza, verrà visualizzato il seguente messaggio:

Il conteggio, la somma e il codice colore delle celle colorate con formattazione condizionale

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.

Aspetta! Potrebbe interessarti anche: