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 2 giorni” le celle sono di colore grigio, “Consegnato” sono gialle e consegne “Scaduto” 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, grigie 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)”.
- 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(B2:B6;A10) dove B2:B6 è l’intervallo contenente le celle colorate che desiderate contare e A10 è la cella con un determinato colore di sfondo, uno giallo nel nostro caso.In modo simile, inserite la formula per gli altri colori che desiderate contare, ed ossia scaduto e consegna fra 2 giorni come nell’esempio di sotto.
Se avete dati numerici nelle celle colorate (ad esempio, una colonna “Quantità” invece di “Consegna”), è possibile sommarne i valori sulla base di un certo colore, utilizzando la funzione analoga SommaCellePerColore:
SommaCellePerColore(intervallo; codice colore)
In modo simile potete contare o sommare celle in base al colore del carattere utilizzando rispettivamente le funzioni ContaCellePerColoreCarattere e SommaCellePerColoreCarattere.