#Excel Contare e sommare celle in base al colore
User Defined Functions (UDF) e Visual Basic for Applications (VBA)
Autore: Andrea Pacchiarotti
Ultimo aggiornamento: 01 Gennaio 2023
Categoria: Microsoft Office Excel Funzioni UDF
Excel può contare e sommare le celle in base al colore (a prescindere se siano colorate a mano o tramite formattazione condizionale). Purtroppo Microsoft Excel non possiede funzioni CONTA.COLORE o SOMMA.COLORE e, per soddisfare la richiesta, è necessario ricorrere alle User Defined Functions (UDF) e al Visual Basic for Applications (VBA), che in questo articolo sono già belle e pronte (puoi scaricare l'esempio a fine articolo). Non aspettiamo altro tempo e scopriamo come possa Excel Contare e sommare celle in base al colore.
Sommario #Excel: contare e sommare celle in base al colore
- Contare o sommare per colore in un foglio Excel
- Contare o sommare le celle colorate tramite formattazione condizionale
- Conclusione
Contare o sommare per colore in un foglio Excel
Supponiamo di avere una tabella che, nella colonna Pagamento, mostri tre risposte associate a tre colori (Effettuato è verde, Scaduto da 1 mese è giallo e Scaduto da oltre 1 mese è arancio). Con questo tutorial imparerai a creare, tramite linguaggio VBA, le funzioni che vedi a partire dalla riga 10.
Per contare le celle in base al colore:
- Apri Excel e premi Alt + F11 per visualizzare il Visual Basic Editor (VBE) oppure, dalla scheda Sviluppo, clicca l’icona Visual Basic (se la scheda Sviluppo non è presente, clicca la scheda File, poi Opzioni, Personalizzazione barra multifunzione e sulla destra spunta la voce Sviluppo)
- In alto a sinistra clicca con il tasto destro del mouse sul nome della tua cartella sotto la voce VBAProject e scegli Inserisci > Modulo
- Aggiungi il seguente codice al Modulo:
Function ColoreCella(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
ColoreCella = arRisultati
Else
ColoreCella = xlIntervallo.Interior.Color
End If
End Function
Function ColoreCarattere(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
ColoreCarattere = arRisultati
Else
ColoreCarattere = xlIntervallo.Font.Color
End If
End Function
Function ContaPerColoreCella(rData As Range, cellRefColor As Range) As Long
Dim indRefColor As Long
Dim cellaCorrente As Range
Dim conta As Long
Application.Volatile
conta = 0
indRefColor = cellRefColor.Cells(1, 1).Interior.Color
For Each cellaCorrente In rData
If indRefColor = cellaCorrente.Interior.Color Then
conta = conta + 1
End If
Next cellaCorrente
ContaPerColoreCella = conta
End Function
Function SommaPerColoreCella(rData As Range, cellRefColor As Range)
Dim indRefColor As Long
Dim cellaCorrente As Range
Dim somma
Application.Volatile
somma = 0
indRefColor = cellRefColor.Cells(1, 1).Interior.Color
For Each cellaCorrente In rData
If indRefColor = cellaCorrente.Interior.Color Then
somma = WorksheetFunction.Sum(cellaCorrente, somma)
End If
Next cellaCorrente
SommaPerColoreCella = somma
End Function
Function ContaPerColoreCarattere(rData As Range, cellRefColor As Range) As Long
Dim indRefColor As Long
Dim cellaCorrente As Range
Dim conta As Long
Application.Volatile
conta = 0
indRefColor = cellRefColor.Cells(1, 1).Font.Color
For Each cellaCorrente In rData
If indRefColor = cellaCorrente.Font.Color Then
conta = conta + 1
End If
Next cellaCorrente
ContaPerColoreCarattere = conta
End Function
Function SommaPerColoreCarattere(rData As Range, cellRefColor As Range)
Dim indRefColor As Long
Dim cellaCorrente As Range
Dim somma
Application.Volatile
somma = 0
indRefColor = cellRefColor.Cells(1, 1).Font.Color
For Each cellaCorrente In rData
If indRefColor = cellaCorrente.Font.Color Then
somma = WorksheetFunction.Sum(cellaCorrente, somma)
End If
Next cellaCorrente
SommaPerColoreCarattere = somma
End Function
- Salva il file come Cartella di lavoro con attivazione macro di Excel (.xlsm)
- Hai creato quattro nuove funzioni di Excel ovvero le seguenti UDF: ContaPerColoreCella
- Seleziona le celle dove vuoi contare o sommare il contenuto delle celle e immetti le seguenti funzioni: =ContaPerColoreCella(D3:D8; B10)
SommaPerColoreCella
ContaPerColoreCarattere
SommaPerColoreCarattere
=ContaPerColoreCella(D4:D9; B11)
=ContaPerColoreCella(D5:D10; B12)
=SommaPerColoreCella(E3:E8; B14)
=SommaPerColoreCella(E3:E8; B15)
=SommaPerColoreCella(E3:E8; B16)
Ovviamente per contare e sommare per colore carattere, anziché colore cella, basta sostituire nelle formule il termine Cella con Carattere.
Se successivamente si avesse la necessità di colorare manualmente altre celle dell’intervallo, le funzioni non verrebbero ricalcolate in maniera automatica; è infatti questo il comportamento delle macro di Excel, del VBA e delle User Defined Functions.
Ma non c’è problema perché, per aggiornare somma e conteggio, è sufficiente posizionare il cursore in una cella e premere F2 seguito dal tasto Invio.
Contare o sommare le celle colorate tramite formattazione condizionale
Purtroppo le UDF non funzioneranno correttamente se, per colorare le celle in base ai loro valori, è stata utilizzata la formattazione condizionale di Excel 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
È però possibile superare questo limite con il VBA, vediamo come:
Sub SommaContaFormCond()
Dim indRefColor As Long
Dim cellaCorrente As Range
Dim conta As Long
Dim somma
Dim contaCelle As Long
Dim indCellaCorrente As Long
conta = 0
somma = 0
contaCelle = Selection.CountLarge
indRefColor = ActiveCell.DisplayFormat.Interior.Color
For indCellaCorrente = 1 To (contaCelle - 1)
If indRefColor = Selection(indCellaCorrente) .DisplayFormat.Interior.Color Then
conta = conta + 1
somma = WorksheetFunction .Sum(Selection(indCellaCorrente), somma)
End If
Next
Range("G2").Value = conta
Range("G3").Value = somma
'oppure al posto delle due righe precedenti
MsgBox "Conteggio=" & conta & vbCrLf & "Somma= " & somma, , "Conteggio e Somma"
End Sub
Ecco come utilizzarlo:
- Aggiungi il codice come fatto prima o accodalo a quello precedente
- Seleziona uno o più intervalli da contare o sommare
- Tenendo premuto Ctrl, seleziona la cella con il colore da contare o sommare
- Rilascia Ctrl
- Premi Alt + F8 per aprire l’elenco delle macro
- Seleziona la macro SommaContaFormCond
- Clicca Esegui
Se opti per le due righe
Range("G2").Value = conta
Range("G3").Value = somma
otterrai il conteggio e la somma rispettivamente nelle celle G2 e G3 (che ovviamente puoi cambiare a tuo piacimento); se invece scegli la riga
MsgBox "Conteggio=" & conta & vbCrLf & "Somma= " & somma, , "Conteggio e Somma"
apparirà una finestra con i risultati del conteggio e della somma.
Scopri come contare con le funzioni statistiche di Microsoft Excel e come sommare con le funzioni matematiche.
Conclusione
Abbiamo visto insieme un'ottima soluzione per ovviare a una mancanza di Microsoft Excel, ovvero contare le celle in base al colore. Attraverso il VBA e le UDF siamo stati in grado di inventare delle funzioni e un metodo che ci ha consentito di migliorare l'applicativo risolvendo un'esigenza lavorativa molto comune.
Per saperne di più su Microsoft Excel potrebbero interessarti i video corsi Excel o questi libri: