Sommare o contare celle colorate in Excel
mercoledì, 23 maggio 2007
Prendiamo un intervallo di celle in Excel e immaginiamo di voler contare o ottenere la somma solo di quelle celle che presentano o nello sfondo o nel carattere, un particolare colore.
Con la procedura seguente vediamo come sia possibile.
Prima di tutto si deve lavorare tramite codice VBA quindi apriamo la relativa finestra.
In Excel XP o 2003:
Menù Strumenti - Macro - Visual Basic Editor
In Excel 2007:
Bottone di Office - Pulsante Opzioni di Excel - Impostazioni generali - Opzione "Mostra scheda Sviluppo sulla barra multifunzione"
A questo punto ritornando nella finestra di Excel verrà visualizzata anche la Scheda Sviluppo nella quale troviamo il pulsante Visual Basic
Nella finestra Visual Basic inserire un nuovo modulo (menù Inserisci - Modulo)
Copiare il codice riportato sotto relativo alla funzione desiderata:
Per contare le celle di un intervallo in base al colore dello sfondo o del carattere
Function CountByColor(InRange As Range, _
WhatColorIndex As Integer, _
Optional OfText As Boolean = False) As Long
'
' This function return the number of cells in InRange with
' a background color, or if OfText is True a font color,
' equal to WhatColorIndex.
'
Dim Rng As Range
Application.Volatile True
For Each Rng In InRange.Cells
If OfText = True Then
CountByColor = CountByColor - _
(Rng.Font.ColorIndex = WhatColorIndex)
Else
CountByColor = CountByColor - _
(Rng.Interior.ColorIndex = WhatColorIndex)
End If
Next Rng
End Function
Chiudere la finestra e ritornare in Excel. La funzione che deve essere inserita per contare le celle secondo un colore è la seguente:
=COUNTBYCOLOR(INTERVALLO,COLORE,TIPO COLORE)
In cui:
INTERVALLO: L'intervallo delle celle che verranno analizzate
COLORE: Il numero del colore relativo alle effettive celle da contare
TIPO COLORE: VERO per indicare che il colore è riferito al testo - FALSO per indicare che il colore è riferito allo sfondo
Esempio: =COUNTBYCOLOR(A10:B30,3,FALSO)
Conta, nell'intervallo A10:B30, le celle che hanno lo sfondo rosso
Il numero da inserire nella formula, relativo al colore da analizzare, lo si può dedurre dalla tabella in fondo (numeri da 1 a 56)
Per sommare le celle di un intervallo in base al colore dello sfondo o del carattere
Function SumByColor(InRange As Range, WhatColorIndex As Integer, _
Optional OfText As Boolean = False) As Double
'
' This function return the SUM of the values of cells in
' InRange with a background color, or if OfText is True a
' font color, equal to WhatColorIndex.
'
Dim Rng As Range
Dim OK As Boolean
Application.Volatile True
For Each Rng In InRange.Cells
If OfText = True Then
OK = (Rng.Font.ColorIndex = WhatColorIndex)
Else
OK = (Rng.Interior.ColorIndex = WhatColorIndex)
End If
If OK And IsNumeric(Rng.Value) Then
SumByColor = SumByColor + Rng.Value
End If
Next Rng
End Function
Chiudere la finestra e ritornare in Excel. La funzione che deve essere inserita per sommare le celle secondo un colore è la seguente:
=SUMBYCOLOR(INTERVALLO,COLORE,TIPO COLORE)
In cui:
INTERVALLO: L'intervallo delle celle che verranno analizzate
COLORE: Il numero del colore relativo alle effettive celle da sommare
TIPO COLORE: VERO per indicare che il colore è riferito al testo - FALSO per indicare che il colore è riferito allo sfondo
Esempio: =SUMBYCOLOR(A10:B30,3,FALSO)
Somma, nell'intervallo A10:B30, le celle che hanno lo sfondo rosso
Il numero da inserire nella formula, relativo al colore da analizzare, lo si può dedurre dalla tabella in fondo (numeri da 1 a 56)
Da tener presente, per entrambi i casi, che se il colore nelle celle viene cambiato il calcolo non si aggiorna automaticamente ma sarà necessario aggiornarlo manualmente ad esempio tramite il tasto F9.
P.S.
Nelle funzioni, per delimitare gli argomenti, andrà utilizzato il separatore appropriato a seconda della lingua di Excel, ossia la virgola nel caso che Excel sia in Inglese o il punto e virgola nel caso in cui Excel sia in Italiano.
Tabella Colori
| Black | [Color 1] | #000000 | #000000 | 0 | 0 | 0 | [Black] |
| White | [Color 2] | #FFFFFF | #FFFFFF | 255 | 255 | 255 | [White] |
| Red | [Color 3] | #FF0000 | #FF0000 | 255 | 0 | 0 | [Red] |
| Green | [Color 4] | #00FF00 | #00FF00 | 0 | 255 | 0 | [Green] |
| Blue | [Color 5] | #0000FF | #0000FF | 0 | 0 | 255 | [Blue] |
| Yellow | [Color 6] | #FFFF00 | #FFFF00 | 255 | 255 | 0 | [Yellow] |
| Magenta | [Color 7] | #FF00FF | #FF00FF | 255 | 0 | 255 | [Magenta] |
| Cyan | [Color 8] | #00FFFF | #00FFFF | 0 | 255 | 255 | [Cyan] |
| [Color 9] | [Color 9] | #800000 | #800000 | 128 | 0 | 0 | [Color 9] |
| [Color 10] | [Color 10] | #008000 | #008000 | 0 | 128 | 0 | [Color 10] |
| [Color 11] | [Color 11] | #000080 | #000080 | 0 | 0 | 128 | [Color 11] |
| [Color 12] | [Color 12] | #808000 | #808000 | 128 | 128 | 0 | [Color 12] |
| [Color 13] | [Color 13] | #800080 | #800080 | 128 | 0 | 128 | [Color 13] |
| [Color 14] | [Color 14] | #008080 | #008080 | 0 | 128 | 128 | [Color 14] |
| [Color 15] | [Color 15] | #C0C0C0 | #C0C0C0 | 192 | 192 | 192 | [Color 15] |
| [Color 16] | [Color 16] | #808080 | #808080 | 128 | 128 | 128 | [Color 16] |
| [Color 17] | [Color 17] | #9999FF | #9999FF | 153 | 153 | 255 | [Color 17] |
| [Color 18] | [Color 18] | #993366 | #993366 | 153 | 51 | 102 | [Color 18] |
| [Color 19] | [Color 19] | #FFFFCC | #FFFFCC | 255 | 255 | 204 | [Color 19] |
| [Color 20] | [Color 20] | #CCFFFF | #CCFFFF | 204 | 255 | 255 | [Color 20] |
| [Color 21] | [Color 21] | #660066 | #660066 | 102 | 0 | 102 | [Color 21] |
| [Color 22] | [Color 22] | #FF8080 | #FF8080 | 255 | 128 | 128 | [Color 22] |
| [Color 23] | [Color 23] | #0066CC | #0066CC | 0 | 102 | 204 | [Color 23] |
| [Color 24] | [Color 24] | #CCCCFF | #CCCCFF | 204 | 204 | 255 | [Color 24] |
| [Color 25] | [Color 25] | #000080 | #000080 | 0 | 0 | 128 | [Color 25] |
| [Color 26] | [Color 26] | #FF00FF | #FF00FF | 255 | 0 | 255 | [Color 26] |
| [Color 27] | [Color 27] | #FFFF00 | #FFFF00 | 255 | 255 | 0 | [Color 27] |
| [Color 28] | [Color 28] | #00FFFF | #00FFFF | 0 | 255 | 255 | [Color 28] |
| [Color 29] | [Color 29] | #800080 | #800080 | 128 | 0 | 128 | [Color 29] |
| [Color 30] | [Color 30] | #800000 | #800000 | 128 | 0 | 0 | [Color 30] |
| [Color 31] | [Color 31] | #008080 | #008080 | 0 | 128 | 128 | [Color 31] |
| [Color 32] | [Color 32] | #0000FF | #0000FF | 0 | 0 | 255 | [Color 32] |
| [Color 33] | [Color 33] | #00CCFF | #00CCFF | 0 | 204 | 255 | [Color 33] |
| [Color 34] | [Color 34] | #CCFFFF | #CCFFFF | 204 | 255 | 255 | [Color 34] |
| [Color 35] | [Color 35] | #CCFFCC | #CCFFCC | 204 | 255 | 204 | [Color 35] |
| [Color 36] | [Color 36] | #FFFF99 | #FFFF99 | 255 | 255 | 153 | [Color 36] |
| [Color 37] | [Color 37] | #99CCFF | #99CCFF | 153 | 204 | 255 | [Color 37] |
| [Color 38] | [Color 38] | #FF99CC | #FF99CC | 255 | 153 | 204 | [Color 38] |
| [Color 39] | [Color 39] | #CC99FF | #CC99FF | 204 | 153 | 255 | [Color 39] |
| [Color 40] | [Color 40] | #FFCC99 | #FFCC99 | 255 | 204 | 153 | [Color 40] |
| [Color 41] | [Color 41] | #3366FF | #3366FF | 51 | 102 | 255 | [Color 41] |
| [Color 42] | [Color 42] | #33CCCC | #33CCCC | 51 | 204 | 204 | [Color 42] |
| [Color 43] | [Color 43] | #99CC00 | #99CC00 | 153 | 204 | 0 | [Color 43] |
| [Color 44] | [Color 44] | #FFCC00 | #FFCC00 | 255 | 204 | 0 | [Color 44] |
| [Color 45] | [Color 45] | #FF9900 | #FF9900 | 255 | 153 | 0 | [Color 45] |
| [Color 46] | [Color 46] | #FF6600 | #FF6600 | 255 | 102 | 0 | [Color 46] |
| [Color 47] | [Color 47] | #666699 | #666699 | 102 | 102 | 153 | [Color 47] |
| [Color 48] | [Color 48] | #969696 | #969696 | 150 | 150 | 150 | [Color 48] |
| [Color 49] | [Color 49] | #003366 | #003366 | 0 | 51 | 102 | [Color 49] |
| [Color 50] | [Color 50] | #339966 | #339966 | 51 | 153 | 102 | [Color 50] |
| [Color 51] | [Color 51] | #003300 | #003300 | 0 | 51 | 0 | [Color 51] |
| [Color 52] | [Color 52] | #333300 | #333300 | 51 | 51 | 0 | [Color 52] |
| [Color 53] | [Color 53] | #993300 | #993300 | 153 | 51 | 0 | [Color 53] |
| [Color 54] | [Color 54] | #993366 | #993366 | 153 | 51 | 102 | [Color 54] |
| [Color 55] | [Color 55] | #333399 | #333399 | 51 | 51 | 153 | [Color 55] |
| [Color 56] | [Color 56] | #333333 | #333333 | 51 | 51 | 51 | [Color 56] |
posted by Andrea Perotti @ 19.26 Permalink ,
46 Comments:
-
At 29 settembre 2007 12.05,
said...
-
ho provato ad usare la formula ma mi dà un errore: cosa posso guardare?
-
At 29 settembre 2007 16.53,
Andrea Perotti said...
-
Segui alla lettera le istruzioni e vedrai che funziona.
-
At 2 novembre 2007 10.00,
said...
-
Per far funzionare la formula all'interno della parentesi rotonda ci vanno i PUNTI E VIRGOLA al posto della virgola per delimitare i parametri...
-
At 2 novembre 2007 10.23,
Andrea Perotti said...
-
@anonimo: dipende dalla versione di Excel che usi. In lingua italiana ci vuole il punto e virgola, in inglese la virgola.
-
At 2 gennaio 2008 15.12,
said...
-
non funziona per le celle colorate tramite formattazione condizionale!!!
-
At 2 gennaio 2008 16.16,
Andrea Perotti said...
-
@giodifi: queste formule funzionano solo utilizzando i colori impostati manualmente, non quelli che attribuisce la formattazione condizionale.
-
At 10 gennaio 2008 09.19,
said...
-
entrambe le formule sono ottime e funzionanti, e meritano un ringraziamento. Solo due considerazioni:
1. vengono contate / sommate tutte le celle formattate per un determinato colore, anche se vuote. E' possibile "dettagliare" la conta / somma solo per le celle dell'intervallo che contengono dei valori?
2. se cambio i colori dentro l'intervallo oggetto di conta / somma - se cambio il numero di colore nella barra della formula, questa non si ricalcola in automatico, ma deve essere reimpostata. Any suggest in proposito?
Ringrazio da subito per l'eventuale attenzione a questo post.
Domenico
dmorello@libero.it -
At 10 gennaio 2008 17.48,
Andrea Perotti said...
-
@Domenico: la formula così com'è non fa l'aggiornamento automatico e conta tutte le celle. Vedrò in futuro di fare delle modifiche per aggiornarla in tal senso.
-
At 18 gennaio 2008 17.44,
said...
-
Non mi funziona, e non capisco dove sia il problema. Ho bisogno di effettuare un calcolo di tutte le celle che hanno il colore giallo. Ho inserito il codice vba, ma non mi riconosce la funzione countbycolor. Utilizzo Office 2007. Mi riporta "Rilevato nome non univoco. CountByColor"
-
At 18 gennaio 2008 17.52,
said...
-
Scusate, non so come, ma ho risolto e funziona perfettamente.
-
At 25 gennaio 2008 11.33,
said...
-
Semplicemente FANTASTICO! Grazie sei stato chiaro e preciso!
-
At 15 luglio 2008 11.21,
said...
-
Ho bisogno di effettuare un calcolo di tutte le celle che hanno il colore rosso. Ho inserito il codice vba, ma non mi riconosce la funzione countbycolor.
Mi riporta "Rilevato nome non univoco. CountByColor" -
At 15 luglio 2008 11.40,
Andrea Perotti said...
-
@Anonimo: segui passo passo le istruzioni e vedrai che funziona. ;)
-
At 7 agosto 2008 18.41,
said...
-
Mi chiamo Maurizio, lavoro in una Banca, e voglio ringraziarTi perchè le indicazioni fornite sono precise ed assai utili, avendomi consentito fra l'altro di risolvere un problema posto da una collega.
Grazie ancora e buone ferie.
Maurizio -
At 26 agosto 2008 11.39,
marcaudo said...
-
Come da Tue indicazioni, ho copiato il codice (copia e incolla) su un modulo excel 2003 e riportato la formula (sempre col copia e incolla) sulla barra delle formule, ma mi dà sempre risultato = 0. Cosa ho omesso?
Grazie per l'aiuto -
At 26 agosto 2008 11.51,
marcaudo said...
-
Risolto il problema. Scusate.
Marcello -
At 7 ottobre 2008 14.26,
said...
-
Prima di tutto complimenti per il blog, grazie a voi ho risolto un bel problema con excel..
Solo un quesito: se volessi individuare individuare le celle che hanno come font il "colore automatico", come dovrei fare? Esiste un numero anche per esso?
Grazie mille
:) -
At 8 ottobre 2008 15.49,
Andrea Perotti said...
-
@LiL: la formula si basa sulla paletta dei colori e non vi è un numero per quello che viene definito "colore automatico".
-
At 5 novembre 2008 11.15,
zenzero said...
-
mi restituisce "nome?", dove ho sbagliato?
-
At 5 novembre 2008 12.34,
Andrea Perotti said...
-
@zenzero: probabilmente sbagli nel digitare il nome della formula o un argomento ;)
-
At 6 novembre 2008 10.40,
zenzero said...
-
il codice l'ho copiato e incollato e anche la formula, modificandola poi.
non credo che riconosca il COUNTBYCOLOR
ti copio e incollo qui la formula inserita:
=COUNTBYCOLOR(B6:L6;3;FALSO)
(doveva riconoscere il colore Rosso del carattere)windows xp - excel 2003
grazie. antonella -
At 6 novembre 2008 11.04,
zenzero said...
-
grazie ho risolto
antonella -
At 12 marzo 2009 19.54,
said...
-
La formula è eccezionale.
C'è solo il problema del carattere automatico (che non è RGB 000 ma 49 49 49)che non viene sommato e pertanto se un utente inesperto clicca sul colore automatico anzicchè sul n.1 crede che la formula non funzioni. Non si potrebbe fare in modo di mettere nell'argomento COLORE i 3 valori RGB? Ciao e grazie. -
At 7 aprile 2009 15.13,
said...
-
Formule preziosissime, grazie molte.
Aspetto impaziente il ricalcolo automatico delle celle, sempre sia possibile. Saluti! -
At 9 aprile 2009 16.25,
said...
-
Grande, cercavo una cosa così da una vita!!!
Funziona perfettamente, grazie!! -
At 22 luglio 2009 10.32,
said...
-
grazie per l'aiuto!
aspetto riconoscente gli aggiornamenti
saluti dalla Polonia -
At 2 agosto 2009 22.21,
federicoacdc said...
-
Mi chiamo Federico, da Bologna. GRAZIE. funziona a meraviglia. queste prime volte sembreranno sempre una specie di magia ...
grazie ancora -
At 12 ottobre 2009 10.45,
said...
-
SEMPLICEMENTE.. PERFETTO!
GRAZIE MILLE -
At 26 ottobre 2009 14.15,
zanzarame said...
-
Ciao.
Ho seguito passo passo le istruzioni (copia e incolla, in buona sostanza) però ottengo sempre l'errore "Rilevato nome non univoco. SumByColor".
Ho visto che si è presentato anche ad altri e che hanno risolto: potete dirmi come?
Anche un aiuto dall'autore sarebbe graditissimo :-)
Grazie 1000. -
At 17 novembre 2009 03.18,
said...
-
Grazie mille per questo modulo, molto utile, solo che è utile per i numeri (e colori) 1,2,3 oltre al 3 il risultato è SEMPRE 0...
ho seguito tutto alla lettera (e nn sn alle prime armi con un computer)
Stefano. -
At 17 novembre 2009 08.01,
Andrea Perotti said...
-
Stefano, probabilmente stai sbagliando qualcosa perchè funziona con tutti i numeri; se vuoi mandami il file per mail che provo a dare un'occhiata.
-
At 20 novembre 2009 11.57,
strigia said...
-
Prima di tutto grazie per il codice, l'ho provato funziona ma per farlo funzionare, una volta cambiato il colore, non mi basta fare f9 devo rientrare nella formula e fare invio altrimenti non aggiorna. La funzione appare tra le definite dall'utente ma ha questo problema. Dipende da qualche mia impostazione?
-
At 4 gennaio 2010 16.29,
said...
-
...grande..la formula funziona alla grande. Mi riconosce solo il colore di sfondo della cella e non il colore del font. Come devo modificare la formula per farla funzionare con il colore font?
Grazie 1000 x l'aiuto :-) -
At 4 gennaio 2010 22.59,
Andrea Perotti said...
-
@Stex81: per sommare le celle in base al colore del testo devi mettere VERO come ultimo argomento della formula. Se hai problemi mandami il file che provo a controllarlo.
-
At 20 gennaio 2010 14.14,
said...
-
Grazie mille, ho seguito alla lettera passo dopo passo, funziona benissimo, perfetto! Grazie. Giovanni
-
At 18 febbraio 2010 18.25,
said...
-
Help me !
mi sento stupido ma copiando tutto il codice (parte rossa e parte blu) nella finestra e poi uscendo con "esci e torna ad Excel", quando copio la formula nella cella che mi interessa, il programma mi restituisce "errore di compilazione: previsto : fine istruzione" e un quadratino azzurro mi si posiziona sul "le" del titolo. Forse dovevo compilare l'istruzione prima di chiudere ? Uso il 2003 su XP.
Grazie 1000 -
At 18 febbraio 2010 18.37,
Andrea Perotti said...
-
@Gabriele: il codice è quello blu, le righe in rosso sono il titolo per capire cosa fa una e cosa fa l'altra, non le devi copiare! Devi prendere solo la parte blu ;)
-
At 19 febbraio 2010 17.55,
said...
-
Ok, sono un pollo !
Ho cancellato il modulo già registrato, ho copiato solo il blu e poi ho copiato l'istruzione nella cella del foglio Excel COUNTBYCOLOR(F4:F40;3;false) .
Il risultato è che Excel non mi da più errore ma nella cella dove ho inserito la formula compare uno scoraggiante #VALORE!
Questa volta dove ho sbagliato ? Mannaggia a me !
Comunque grazie per il prezioso aiuto. Ciao
Gabriele -
At 20 febbraio 2010 09.21,
said...
-
Ragazzi ho provato ad inserire la lsita in Execl 2007 ma il risultato è sempre zero....
-
At 20 febbraio 2010 09.22,
said...
-
Anche a me risulta sempre zero vorrei contare nella colonna A di excel tutte le celle colorate in viola (13) ma il risultato è zero anche se uso altri colori.
GRAZIE -
At 21 febbraio 2010 16.19,
Andrea Perotti said...
-
@Gabriele: ammesso che tu abbia Excel in Italiano allora hai messo FALSE al posto di FALSO come ultimo argomento della formula.
-
At 21 febbraio 2010 16.24,
Andrea Perotti said...
-
@AXE e Anonimo: state sbagliando qualcosa o nella creazione del modulo o nella formula; se non riuscite a risolvere mandatemi il file che ve lo sistemo.
-
At 22 febbraio 2010 09.34,
said...
-
Sei un grande!
ho risolto in 5 minuti un problema che da solo non avrei mai risolto: grazie mille
Luca -
At 22 febbraio 2010 14.31,
said...
-
Ok, ho la conferma che io sono un pollo !
Per fortuna che ci sei tu che sei un GRANDE !
Non so come ringraziarti.
ciao
Gabriele -
At 22 febbraio 2010 19.46,
dott.ssa Lucia Giammarinaro said...
-
Grazie mille...
se mi assumono è merito tuo! -
At 22 febbraio 2010 21.25,
Andrea Perotti said...
-
@Gabriele: di nulla!
@Lucia: Allora nel caso devi pagare da bere ;)
