In un post precedente avevo spiegato come contare celle colorate; immaginiamo ora di dover contare celle colorate in base al loro contenuto, ad esempio tutte le celle di colore rosso che contengono valori maggiori a 50. Vediamo come procedere:
Dovendo lavorare in ambiente VBA 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
In Excel 2010
Scheda File – Opzioni – Personalizzazone barra multifunzione – Selezionare Sviluppo nella sezione Schede principali
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) quindi copiare il codice riportato qui sotto:
Function ContacoloreIF(CellColor As Range, Content As Double, CountRange As Range)
Application.Volatile
Dim ICol As Integer
Dim TCell As Range
ICol = CellColor.Interior.ColorIndex
For Each TCell In CountRange
If ICol = TCell.Interior.ColorIndex And TCell.Value > Content Then
ContacoloreIF = ContacoloreIF + 1
End If
Next TCell
End Function
Chiudere la finestra e ritornare in Excel. La funzione che deve essere inserita per contare le celle di un determinato colore in base ad una condizione è la seguente:
=ContacoloreIF(CellColor;Content;CountRange)
Immaginando di voler contare quante celle rosse in un determinato intervallo contengono un valore maggiore a 50, la formula dovrà essere così composta:
CellColor: è la cella che contiene il colore che si vuole usare come filtro (nell’esempio il rosso)
Content: il valore di riferimento (nell’esempio 50)
CountRange: l’intervallo che contiene le celle da analizzare
Nel caso in cui invece di prendere in considerazione tutte le celle che contengono un valore maggiore di un numero, volessimo prendere come riferimento tutte quelle che contengono un valore minore o uguale ad un numero dovremo cambiare il criterio di confronto nel codice nella relativa riga come evidenziato di seguito:
If ICol = TCell.Interior.ColorIndex And TCell.Value > Content Then
Sostituendo al > il criterio di confronto desiderato, ad esempio < o = ecc.
Come ho scritto anche nei commenti la funzione conta i numeri; per far si che conti ad esempio le celle che presentano un determinato testo si deve cambiare il codice, ovvero nella prima riga:
Function ContacoloreIF(CellColor As Range, Content As Double, CountRange As Range)
si deve cambiare la definizione di Content cioè si deve sostituire Double con String, ovvero:
Function ContacoloreIF(CellColor As Range, Content As String, CountRange As Range)
Si deve anche cambiare il codice della riga che contiene il criterio di confronto:
If ICol = TCell.Interior.ColorIndex And TCell.Value > Content Then
mettendo = al posto di > ovvero:
If ICol = TCell.Interior.ColorIndex And TCell.Value = Content Then
Infine quando si inserisce nella cella di Excel la funzione, per l’argomento Content si deve scrivere il testo che interessa venga contato tra virgolette.
Informatics, ski racer, loves travels, Finland, Norway, mountains, squash, photography, Drones and RC Helicopter
RSS - Articoli
se invece di un valore dovessi contare quante volte è inserito un determinato testo con un determinato colore di sfondo, la formula è la stessa e sono io che sbaglio ad impostarla oppure bisogna fare delle modifiche al modulo VBA
@Sandro: La funzione conta i numeri; per far si che conti i testi si deve cambiare il codice, ovvero nella prima riga:
Function ContacoloreIF(CellColor As Range, Content As Double, CountRange As Range)
si deve cambiare la definizione di Content cioè si deve sostituire Double con String
Devi anche cambiare il codice di questa riga:
If ICol = TCell.Interior.ColorIndex And TCell.Value > Content Then
mettendo = al posto di >
Infine quando inserisci nella cella di Excel la funzione, per l’argomento Content devi scrivere il testo che ti interessa cercare tra virgolette.
grazie mille, sei stato gentilissimo.
Grazie mille.. molto utile e preciso… ma come è possibile aggiornare in automatico le celle senza dover chiudere e riaprire il file? magari con un tasto o in automatico
@Davide: per aggiornare ti basta schiacciare il tasto F9; se invece lo vuoi fare in automatico puoi aggiungere questo codice: https://www.andreaperotti.ch/2013/03/ricalcolo-automatico-celle-excel/
Salve,
Ho appena letto sul suo sito questo argomento, pero’ non riesco dopo aver copiato in un modulo quel pezzo di codice a richiamarlo in una determinata cella inserisco la funzione successiva ma non succede nulla come mai?
@Marco: spiegato così mi pare impossibile dirti dove sbagli 😉
E’ da tempo che stavo cercando questa formula
grazie mille
Ho provato la formula ed e’ validissima, pero’ ho un piccolo problema.
Nel mio foglio ho delle celle vuote gia’ colorate, se uso questa formula:
=ContacoloreIF(a1;0;b1:b100)
conta come zero anche le vuote.
Come posso risolvere?
Grazie davvero, sono riuscito a contare le celle gialle e vuote di un foglio excel immenso.
Andrea, tutto chiarissimo e utile su excel.
Io dovrei fare la stessa cosa su un foglio Google condiviso su Drive.
Riesco?
@elisa: penso di no.
Se anziché contare le celle gialle che contengono soltanto il testo “A” volessi contare quelle che contengono “A”, “B”, e “C”, come devo modificare la formula?