Come contare le celle COLORATE in Excel (Guida passo passo + VIDEO)

Guarda il video - Come contare le celle colorate in Excel

Non sarebbe fantastico se ci fosse una funzione in grado di contare le celle colorate in Excel?

Purtroppo, non esiste alcuna funzione integrata per farlo.

MA…

Si può fare facilmente.

Come contare le celle colorate in Excel

In questo tutorial, ti mostrerò tre modi per contare le celle colorate in Excel (con e senza VBA):

1. Utilizzo della funzione Filtro e SUBTOTALE
2. Utilizzo della funzione GET.CELL
3. Utilizzo di una funzione personalizzata creata utilizzando VBA

#1 Conta le celle colorate usando il filtro e SUBTOTALE

Per contare le celle colorate in Excel, è necessario utilizzare i due passaggi seguenti:

• Filtra le celle colorate
• Utilizzare la funzione SUBTOTALE per contare le celle colorate visibili (dopo il filtraggio).

Supponiamo di avere un set di dati come mostrato di seguito:

Ci sono due colori di sfondo usati in questo set di dati (verde e arancione).

Ecco i passaggi per contare le celle colorate in Excel:

1. In qualsiasi cella al di sotto del set di dati, utilizzare la seguente formula: =SUBTOTALE(102,E1:E20)
   
2. Seleziona le intestazioni.
3. Vai su Dati -> Ordina e filtra -> Filtra. Questo applicherà un filtro a tutte le intestazioni.
4. Fare clic su uno dei menu a discesa dei filtri.
5. Vai su "Filtra per colore" e seleziona il colore. Nel set di dati sopra, poiché ci sono due colori usati per evidenziare le celle, il filtro mostra due colori per filtrare queste celle.

Non appena filtri le celle, noterai che il valore nella funzione SUBTOTALE cambia e restituisce solo il numero di celle che sono visibili dopo il filtraggio.

Come funziona?

La funzione SUBTOTALE utilizza 102 come primo argomento, che viene utilizzato per contare le celle visibili (le righe nascoste non vengono conteggiate) nell'intervallo specificato.

Se i dati non vengono filtrati restituisce 19, ma se viene filtrata restituisce solo il conteggio delle celle visibili.

Provalo tu stesso… Scarica il file di esempio

#2 Conta le celle colorate usando la funzione GET.CELL

GET.CELL è una funzione Macro4 che è stata mantenuta per motivi di compatibilità.

Non funziona se utilizzato come funzioni regolari nel foglio di lavoro.

Tuttavia, funziona negli intervalli denominati di Excel.

Guarda anche: Scopri di più sulla funzione GET.CELL.

Ecco i tre passaggi per utilizzare GET.CELL per contare le celle colorate in Excel:

• Crea un intervallo denominato utilizzando la funzione GET.CELL
• Usa l'intervallo con nome per ottenere il codice colore in una colonna
• Utilizzo del numero del colore per contare il numero di celle colorate (per colore)

Approfondiamo e vediamo cosa fare in ciascuno dei tre passaggi menzionati.

Creazione di un intervallo denominato

• Vai su Formule -> Definisci nome.
• Nella finestra di dialogo Nuovo nome, immettere:
  • Nome: GetColor
  • Ambito: cartella di lavoro
  • Si riferisce a: =GET.CELL(38,Foglio1!$A2)
    Nella formula sopra, ho usato Foglio1!$A2 come secondo argomento. Devi usare il riferimento della colonna in cui hai le celle con il colore di sfondo.

Ottenere il codice colore per ogni cella

Nella cella adiacente ai dati, usa la formula =GetColor

Questa formula restituirebbe 0 se NON c'è un colore di sfondo in una cella e restituirebbe un numero specifico se c'è un colore di sfondo.

Questo numero è specifico di un colore, quindi tutte le celle con lo stesso colore di sfondo ottengono lo stesso numero.

Conta le celle colorate usando il codice colore

Se segui il processo sopra, avresti una colonna con i numeri corrispondenti al colore di sfondo in essa.

Per ottenere il conteggio di un colore specifico:

• Da qualche parte sotto il set di dati, dai lo stesso colore di sfondo a una cella che vuoi contare. Assicurati di farlo nella stessa colonna che hai usato per creare l'intervallo denominato. Ad esempio, ho usato la colonna A e quindi utilizzerò solo le celle nella colonna "A".
• Nella cella adiacente, usa la seguente formula:

=CONTA.SE($F$2:$F$20, Ottieni Colore)

Questa formula ti darà il conteggio di tutte le celle con il colore di sfondo specificato.

Come funziona?

La funzione CONTA.SE utilizza l'intervallo denominato (GetColor) come criterio. L'intervallo denominato nella formula si riferisce alla cella adiacente a sinistra (nella colonna A) e restituisce il codice colore per quella cella. Quindi, questo numero di codice colore è il criterio.

La funzione CONTA.SE utilizza l'intervallo ($F$2:$F$18) che contiene i numeri del codice colore di tutte le celle e restituisce il conteggio in base al numero del criterio.

Provalo tu stesso… Scarica il file di esempio

#3 Conteggio colorato usando VBA (creando una funzione personalizzata)

Nei due metodi precedenti, hai imparato a contare le celle colorate senza usare VBA.

Ma, se stai bene con l'utilizzo di VBA, questo è il più semplice dei tre metodi.

Usando VBA, creeremmo una funzione personalizzata, che funzionerebbe come una funzione CONTA.SE e restituirebbe il conteggio delle celle con il colore di sfondo specifico.

Ecco il codice:

'Codice creato da Sumit Bansal da https://trumpexcel.com Funzione GetColorCount(CountRange As Range, CountColor As Range) Dim CountColorValue As Integer Dim TotalCount As Integer CountColorValue = CountColor.Interior.ColorIndex Set rCell = CountRange For Each rCell In CountRange If rCell.Interior.ColorIndex = CountColorValue Then TotalCount = TotalCount + 1 End If Next rCell GetColorCount = TotalCount End Function

Per creare questa funzione personalizzata:

• Con la cartella di lavoro attiva, premi Alt + F11 (o fai clic con il pulsante destro del mouse sulla scheda del foglio di lavoro e seleziona Visualizza codice). Questo aprirebbe l'editor VB.
• Nel riquadro di sinistra, sotto la cartella di lavoro in cui stai lavorando, fai clic con il pulsante destro del mouse su uno dei fogli di lavoro e seleziona Inserisci -> Modulo. Questo inserirebbe un nuovo modulo. Copia e incolla il codice nella finestra del codice del modulo.
• Fare doppio clic sul nome del modulo (per impostazione predefinita il nome del modulo in Module1) e incollare il codice nella finestra del codice.
• Chiudi l'editor VB.
• Questo è tutto! Ora hai una funzione personalizzata nel foglio di lavoro chiamata GetColorCount.

Per utilizzare questa funzione, usala semplicemente come qualsiasi normale funzione excel.

Sintassi: =GetColorCount(CountRange, CountColor)

• Intervallo di conteggio: l'intervallo in cui si desidera contare le celle con il colore di sfondo specificato.
• ConteColore: il colore per il quale si vogliono contare le celle.

Per usare questa formula, usa lo stesso colore di sfondo (che vuoi contare) in una cella e usa la formula. L'argomento CountColor sarebbe la stessa cella in cui stai inserendo la formula (come mostrato di seguito):

Nota: Poiché nella cartella di lavoro è presente un codice, salvalo con un'estensione .xls o .xlsm.

Provalo tu stesso… Scarica il file di esempio

Conosci un altro modo per contare le celle colorate in Excel?

Se sì, condividilo con me lasciando un commento.