#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:
    1. Function TrovaColoreCella(xlIntervallo As Range)
    2. Dim indRiga, indColonna As Long
    3. Dim arRisultati()
    4.  
    5. Application.Volatile
    6.  
    7. If xlIntervallo Is Nothing Then
    8. Set xlIntervallo = Application.ThisCell
    9. End If
    10.  
    11. If xlIntervallo.Count > 1 Then
    12. ReDim arRisultati(1 To xlIntervallo.Rows.Count, 1 To xlIntervallo.Columns.Count)
    13. For indRiga = 1 To xlIntervallo.Rows.Count
    14. For indColonna = 1 To xlIntervallo.Columns.Count
    15. arRisultati(indRiga, indColonna) = xlIntervallo(indRiga, indColonna).Interior.Color
    16. Next
    17. Next
    18. TrovaColoreCella = arRisultati
    19. Else
    20. TrovaColoreCella = xlIntervallo.Interior.Color
    21. End If
    22. End Function
    23.  
    24. Function TrovaColoreCarattere(xlIntervallo As Range)
    25. Dim indRiga, indColonna As Long
    26. Dim arRisultati()
    27.  
    28. Application.Volatile
    29.  
    30. If xlIntervallo Is Nothing Then
    31. Set xlIntervallo = Application.ThisCell
    32. End If
    33.  
    34. If xlIntervallo.Count > 1 Then
    35. ReDim arRisultati(1 To xlIntervallo.Rows.Count, 1 To xlIntervallo.Columns.Count)
    36. For indRiga = 1 To xlIntervallo.Rows.Count
    37. For indColonna = 1 To xlIntervallo.Columns.Count
    38. arRisultati(indRiga, indColonna) = xlIntervallo(indRiga, indColonna).Font.Color
    39. Next
    40. Next
    41. TrovaColoreCarattere = arRisultati
    42. Else
    43. TrovaColoreCarattere = xlIntervallo.Font.Color
    44. End If
    45.  
    46. End Function
    47.  
    48. Function ContaCellePerColore(rData As Range, cellRefColor As Range) As Long
    49. Dim indRefColor As Long
    50. Dim cellaCorrente As Range
    51. Dim cntRes As Long
    52.  
    53. Application.Volatile
    54. cntRes = 0
    55. indRefColor = cellRefColor.Cells(1, 1).Interior.Color
    56. For Each cellaCorrente In rData
    57. If indRefColor = cellaCorrente.Interior.Color Then
    58. cntRes = cntRes + 1
    59. End If
    60. Next cellaCorrente
    61.  
    62. ContaCellePerColore = cntRes
    63. End Function
    64.  
    65. Function SommaCellePerColore(rData As Range, cellRefColor As Range)
    66. Dim indRefColor As Long
    67. Dim cellaCorrente As Range
    68. Dim sumRes
    69.  
    70. Application.Volatile
    71. sumRes = 0
    72. indRefColor = cellRefColor.Cells(1, 1).Interior.Color
    73. For Each cellaCorrente In rData
    74. If indRefColor = cellaCorrente.Interior.Color Then
    75. sumRes = WorksheetFunction.Sum(cellaCorrente, sumRes)
    76. End If
    77. Next cellaCorrente
    78.  
    79. SommaCellePerColore = sumRes
    80. End Function
    81.  
    82. Function ContaCellePerColoreCarattere(rData As Range, cellRefColor As Range) As Long
    83. Dim indRefColor As Long
    84. Dim cellaCorrente As Range
    85. Dim cntRes As Long
    86.  
    87. Application.Volatile
    88. cntRes = 0
    89. indRefColor = cellRefColor.Cells(1, 1).Font.Color
    90. For Each cellaCorrente In rData
    91. If indRefColor = cellaCorrente.Font.Color Then
    92. cntRes = cntRes + 1
    93. End If
    94. Next cellaCorrente
    95.  
    96. ContaCellePerColoreCarattere = cntRes
    97. End Function
    98.  
    99. Function SommaCellePerColoreCarattere(rData As Range, cellRefColor As Range)
    100. Dim indRefColor As Long
    101. Dim cellaCorrente As Range
    102. Dim sumRes
    103.  
    104. Application.Volatile
    105. sumRes = 0
    106. indRefColor = cellRefColor.Cells(1, 1).Font.Color
    107. For Each cellaCorrente In rData
    108. If indRefColor = cellaCorrente.Font.Color Then
    109. sumRes = WorksheetFunction.Sum(cellaCorrente, sumRes)
    110. End If
    111. Next cellaCorrente
    112.  
    113. SommaCellePerColoreCarattere = sumRes
    114. End Function
  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.

LEGGI:  #Excel - Come salvare rapidamente in PDF il foglio attivo [VBA]

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:

LEGGI:  #Excel - Come alternare automaticamente il colore di righe o colonne

  1. Function CartellaContaCellePerColore(cellRefColor As Range)
  2. Dim vWbkRes
  3. Dim foglioCorrente As Worksheet
  4.  
  5. Application.ScreenUpdating = False
  6. Application.Calculation = xlCalculationManual
  7.  
  8. vWbkRes = 0
  9. For Each foglioCorrente In Worksheets
  10. foglioCorrente.Activate
  11. vWbkRes = vWbkRes + ContaCellePerColore(foglioCorrente.UsedRange, cellRefColor)
  12. Next
  13. Application.ScreenUpdating = True
  14. Application.Calculation = xlCalculationAutomatic
  15.  
  16. CartellaContaCellePerColore = vWbkRes
  17. End Function
  18.  
  19. Function CartellaSommaCellePerColore(cellRefColor As Range)
  20. Dim vWbkRes
  21. Dim foglioCorrente As Worksheet
  22.  
  23. Application.ScreenUpdating = False
  24. Application.Calculation = xlCalculationManual
  25.  
  26. vWbkRes = 0
  27. For Each foglioCorrente In Worksheets
  28. foglioCorrente.Activate
  29. vWbkRes = vWbkRes + SommaCellePerColore(foglioCorrente.UsedRange, cellRefColor)
  30. Next
  31. Application.ScreenUpdating = True
  32. Application.Calculation = xlCalculationAutomatic
  33.  
  34. CartellaSommaCellePerColore = vWbkRes
  35. 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.

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è 🙁

LEGGI:  Riferimenti di cella relativi e assoluti nella formattazione condizionale di #Excel

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.

  1. Sub SommaContaPerFormattazioneCondizionale()
  2. Dim indRefColor As Long
  3. Dim cellaCorrente As Range
  4. Dim cntRes As Long
  5. Dim sumRes
  6. Dim contaCelle As Long
  7. Dim indCellaCorrente As Long
  8.  
  9. cntRes = 0
  10. sumRes = 0
  11.  
  12. contaCelle = Selection.CountLarge
  13. indRefColor = ActiveCell.DisplayFormat.Interior.Color
  14.  
  15. For indCellaCorrente = 1 To (contaCelle - 1)
  16. If indRefColor = Selection(indCellaCorrente).DisplayFormat.Interior.Color Then
  17. cntRes = cntRes + 1
  18. sumRes = WorksheetFunction.Sum(Selection(indCellaCorrente), sumRes)
  19. End If
  20. Next
  21. MsgBox "Conteggio=" & cntRes & vbCrLf & "Somma= " & sumRes & vbCrLf & vbCrLf & _
  22. "Colore=" & Left("000000", 6 - Len(Hex(indRefColor))) & _
  23. Hex(indRefColor) & vbCrLf, , "Conteggio e Somma per colore di Formattazione Condizionale"
  24. End Sub

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.

Copyright © 2014-2017  Office Academy. Tutti i diritti riservati.
Vai alla barra degli strumenti