Consigli Excel: Conta/somma celle per colore (sfondo, carattere, formattazione condizionale)
Nelle attività quotidiane, la marcatura a colori è un metodo popolare per distinguere e mettere in evidenza rapidamente i dati cruciali. Ma come possiamo contare o sommare i dati delle celle in base a un colore specifico (colore di riempimento, colore del carattere, formattazione condizionale)? Di default, Excel non offre una funzione diretta per contare o sommare per colore. Tuttavia, con alcuni trucchi e metodi indiretti, possiamo comunque raggiungere questo obiettivo. Questo articolo esplorerà come contare o sommare i dati per colore.
Conta e somma le celle in base al colore di sfondo
- Utilizzando una Funzione Definita dall'Utente
- Utilizzando una potente funzionalità – Kutools per Excel
- Utilizzando Filtro e SUBTOTALE
Conta e somma le celle in base al colore del carattere
- Utilizzando una Funzione Definita dall'Utente
- Utilizzando una funzionalità semplice – Kutools per Excel
Conta e somma le celle in base al colore della formattazione condizionale
Conta e somma le celle in base al colore di sfondo
Ad esempio, se hai un intervallo di dati in cui i valori sono riempiti con diversi colori di sfondo come mostrato nello screenshot sottostante. Per contare o sommare le celle in base a un colore specifico, Excel non offre una funzione diretta per contare o sommare le celle in base al loro colore di sfondo. Tuttavia, con un po' di ingegno e alcune tecniche utili, puoi portare a termine questo compito. Esploriamo alcuni metodi utili in questa sezione.
Conta e somma le celle per colore di sfondo con una Funzione Definita dall'Utente
Qui ti mostreremo come creare e utilizzare una tale Funzione Definita dall'Utente per risolvere questo compito in Excel. Procedi con i seguenti passaggi:
Passo 1: Apri l'editor del modulo VBA e copia il codice
- Premi Alt + F11 per aprire la finestra Microsoft Visual Basic for Applications.
- Nella finestra aperta, clicca su Inserisci > Modulo per creare un nuovo modulo vuoto.
- Quindi, copia e incolla il seguente codice nel modulo vuoto.
Codice VBA: Conta e somma le celle in base al colore di sfondoFunction ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean = False) As Variant 'Updateby Extendoffice Dim rCell As Range Dim lCol As Long Dim vResult As Double lCol = rColor.Interior.ColorIndex vResult = 0 If SUM Then For Each rCell In rRange If rCell.Interior.ColorIndex = lCol Then vResult = vResult + rCell.Value End If Next rCell Else For Each rCell In rRange If rCell.Interior.ColorIndex = lCol Then vResult = vResult + 1 End If Next rCell End If ColorFunction = vResult End Function
Passo 2: Crea formule per contare e sommare le celle per colore di sfondo
Dopo aver incollato il codice sopra, chiudi la finestra del modulo, quindi applica le seguenti formule:
- Conta le celle in base al colore di sfondo specifico:
Copia o digita la formula fornita sotto nella cella desiderata per il risultato. Quindi, trascina la maniglia di riempimento verso il basso per ottenere altri risultati. Vedi screenshot:
=colorfunction(G2,$B$2:$E$12,FALSE)
Nota: In questa formula, G2 è la cella di riferimento con il colore di sfondo specifico che vuoi abbinare; $B$2:$E$12 è l'intervallo in cui vuoi contare il numero di celle del colore di G2; FALSE viene usato per contare le celle con il colore corrispondente. - Somma le celle in base al colore di sfondo specifico:
Copia o digita la formula fornita sotto nella cella desiderata per il risultato. Quindi, trascina la maniglia di riempimento verso il basso per ottenere altri risultati. Vedi screenshot:
=colorfunction(G2,$B$2:$E$12,TRUE)
Nota: In questa formula, G2 è la cella di riferimento con il colore di sfondo specifico che vuoi abbinare; $B$2:$E$12 è l'intervallo in cui vuoi contare il numero di celle del colore di G2; TRUE viene usato per sommare le celle con il colore corrispondente.
Conta e somma le celle per colore di sfondo con una funzionalità potente
Per coloro che non hanno familiarità con la programmazione, il VBA può apparire piuttosto complesso. Qui introdurremo uno strumento potente - Kutools per Excel, la sua funzionalità Conta per Colore ti permette di calcolare facilmente (contare, sommare, fare la media, ecc.) in base al colore di sfondo con pochi clic. Impressionante, la funzionalità Conta per Colore va oltre i soli colori di sfondo – può anche differenziare e calcolare in base ai colori del carattere e alla formattazione condizionale.
Dopo aver scaricato e installato Kutools per Excel, prima seleziona l'intervallo di dati che vuoi contare o sommare in base a un colore di sfondo specifico. Successivamente, vai su Kutools Plus e scegli Conta per Colore.
Nella finestra di dialogo Conta per Colore, specifica le operazioni:
- Seleziona Formattazione standard dal menu a discesa Metodo colore;
- Specifica Sfondo dal menu a discesa Tipo conteggio, e puoi visualizzare in anteprima i risultati statistici per ogni colore di sfondo nella finestra di dialogo;
- Infine, clicca Genera report per esportare i risultati calcolati in una nuova cartella di lavoro.
Risultato:
Ora otterrai una nuova cartella di lavoro con le statistiche. Vedi screenshot:
- La funzionalità Conta per Colore supporta anche il conteggio e la somma delle celle in base al colore del carattere standard, al colore di sfondo o al colore del carattere dalla formattazione condizionale, e a una combinazione di entrambi i colori di riempimento e formattazione condizionale.
- Interessato a questa funzionalità? Clicca qui per scaricare e ottenere una prova gratuita di 30 giorni.
Conta e somma le celle per colore di sfondo con Filtro e funzione SUBTOTALE
Supponiamo di avere una tabella delle vendite di frutta come mostrato nello screenshot sottostante, e conteremo o sommeremo le celle colorate nella colonna Importo .
Passo 1: Applica la funzione SUBTOTALE
Seleziona celle vuote per inserire la funzione SUBTOTALE.
- Per contare tutte le celle con lo stesso colore di sfondo, inserisci la formula:
=SUBTOTAL(102, F2:F16)
- Per sommare tutte le celle con lo stesso colore di sfondo, inserisci la formula:
=SUBTOTAL(109, F2:F16)
- Nota: nelle formule sopra, 102 rappresenta il conteggio dei valori numerici in un elenco filtrato escludendo le celle nascoste; 109 rappresenta la somma dei valori in un elenco filtrato escludendo le celle nascoste; F2:F16 è l'intervallo su cui verrà calcolato il conteggio o la somma.
Passo 2: Filtra le celle in base a un colore specifico
- Seleziona l'intestazione della tabella, e clicca Dati > Filtro. Vedi screenshot:
- Clicca l'icona Filtro icona
nella cella dell'intestazione della Importo colonna, e clicca Filtra per Colore e il colore specifico che vuoi contare successivamente. Vedi screenshot:
Risultato:
Dopo aver filtrato, le formule SUBTOTALE contano e sommano automaticamente le celle colorate nella colonna Importo . Vedi screenshot:
Conta e somma le celle in base al colore del carattere
Vuoi contare o sommare le celle in base al loro colore del carattere in Excel? Supponiamo che tu abbia i dati, come nello screenshot dato, con celle contenenti testi nei colori rosso, blu, arancione e nero. Excel non rende facile questa operazione di default. Ma non preoccuparti! In questa sezione, ti mostreremo alcuni semplici trucchi per farlo.
Conta e somma le celle in base al colore del carattere con una Funzione Definita dall'Utente
Per contare e sommare le celle con colori del carattere specifici, la seguente Funzione Definita dall'Utente può aiutarti a risolvere questo compito. Procedi con i seguenti passaggi:
Passo 1: Apri l'editor del modulo VBA e copia il codice
- Premi Alt + F11 per aprire la finestra Microsoft Visual Basic for Applications.
- Nella finestra aperta, clicca su Inserisci > Modulo per creare un nuovo modulo vuoto.
- Quindi, copia e incolla il seguente codice nel modulo vuoto.
Codice VBA: Conta e somma le celle in base al colore del carattereFunction ProcessByFontColor(pRange1 As Range, pRange2 As Range, FunctionType As String) As Double 'Updateby Extendoffice Application.Volatile Dim rng As Range Dim xTotal As Double Dim xCount As Double xTotal = 0 xCount = 0 For Each rng In pRange1 If rng.Font.Color = pRange2.Font.Color Then If UCase(FunctionType) = "SUM" Then xTotal = xTotal + rng.Value ElseIf UCase(FunctionType) = "COUNT" Then xCount = xCount + 1 End If End If Next If UCase(FunctionType) = "SUM" Then ProcessByFontColor = xTotal ElseIf UCase(FunctionType) = "COUNT" Then ProcessByFontColor = xCount Else ProcessByFontColor = CVErr(xlErrValue) End If End Function
Passo 2: Crea formule per contare e sommare le celle per colore del carattere
Dopo aver incollato il codice sopra, chiudi la finestra del modulo, quindi applica le seguenti formule:
- Conta le celle in base al colore del carattere specifico:
Copia o digita la formula fornita sotto nella cella desiderata per il risultato. Quindi, trascina la maniglia di riempimento verso il basso per ottenere altri risultati. Vedi screenshot:
=ProcessByFontColor($B$2:$E$12,G2, "COUNT")
Nota: In questa formula, G2 è la cella di riferimento con il colore del carattere specifico che vuoi abbinare; $B$2:$E$12 è l'intervallo in cui vuoi contare il numero di celle del colore di G2. - Somma le celle in base al colore del carattere specifico:
Copia o digita la formula fornita sotto nella cella desiderata per il risultato. Quindi, trascina la maniglia di riempimento verso il basso per ottenere altri risultati. Vedi screenshot:
=ProcessByFontColor($B$2:$E$12,G2, "SUM")
Nota: In questa formula, G2 è la cella di riferimento con il colore del carattere specifico che vuoi abbinare; $B$2:$E$12 è l'intervallo in cui vuoi contare il numero di celle del colore di G2.
Conta e somma le celle in base al colore del carattere con una funzionalità semplice
Vuoi contare o sommare facilmente i valori delle celle in Excel in base al colore del carattere? Scopri la funzionalità Conta per Colore di Kutools per Excel! Con questo strumento intelligente, contare e sommare le celle per un colore del carattere specifico diventa un gioco da ragazzi. Scopri come Kutools può trasformare la tua esperienza con Excel.
Dopo aver scaricato e installato Kutools per Excel, prima seleziona l'intervallo di dati che vuoi contare o sommare in base a un colore del carattere specifico. Poi, clicca su Kutools Plus > Conta per Colore per aprire la finestra di dialogo Conta per Colore.
Nella finestra di dialogo Conta per Colore, specifica le operazioni:
- Seleziona Formattazione standard dal menu a discesa Metodo colore;
- Specifica Carattere dal menu a discesa Tipo conteggio, e puoi visualizzare in anteprima i risultati statistici per ogni colore del carattere nella finestra di dialogo;
- Infine, clicca Genera report per esportare i risultati calcolati in una nuova cartella di lavoro.
Risultato:
Ora, hai una nuova cartella di lavoro che mostra le statistiche dettagliate in base al colore del carattere. Vedi screenshot:
Conta e somma le celle in base al colore della formattazione condizionale
In Excel, potresti comunemente utilizzare la Formattazione Condizionale per applicare un colore specifico alle celle che soddisfano determinati criteri, rendendo la visualizzazione dei dati intuitiva. Ma cosa succede se hai bisogno di contare o sommare quelle celle formattate in modo speciale? Mentre Excel non offre un modo diretto per farlo, ecco alcuni modi per aggirare questa limitazione.
Conta e somma le celle formattate condizionalmente con il codice VBA
Contare e sommare le celle formattate condizionalmente in Excel non è semplice utilizzando le funzioni integrate. Tuttavia, puoi portare a termine questo compito utilizzando il codice VBA. Vediamo come puoi utilizzare il VBA per questo scopo:
Passo 1: Apri l'editor del modulo VBA e copia il codice
- Premi Alt + F11 per aprire la finestra Microsoft Visual Basic for Applications.
- Nella finestra aperta, clicca su Inserisci > Modulo per creare un nuovo modulo vuoto.
- Quindi, copia e incolla il seguente codice nel modulo vuoto.
Codice VBA: Conta e somma le celle in base al colore della formattazione condizionaleSub SumCountByConditionalFormat() 'Updateby Extendoffice Dim sampleColor As Range Dim selectedRange As Range Dim cell As Range Dim countByColor As Long Dim sumByColor As Double Dim refColor As Long Set selectedRange = Application.InputBox("Select a range to evaluate:", _ "Kutools for Excel", _ Type:=8) If selectedRange Is Nothing Then Exit Sub Set sampleColor = Application.InputBox("Select a conditional formatting color:", _ "Kutools for Excel", _ Type:=8) If Not sampleColor Is Nothing Then refColor = sampleColor.Cells(1, 1).DisplayFormat.Interior.color For Each cell In selectedRange If cell.DisplayFormat.Interior.color = refColor Then countByColor = countByColor + 1 sumByColor = sumByColor + cell.Value End If Next cell MsgBox "Count: " & countByColor & vbCrLf & _ "Sum: " & sumByColor, _ vbInformation, "Results based on Conditional Format Color" End If End Sub
Passo 2: Esegui questo codice VBA
- Dopo aver incollato il codice, premi F5 per eseguire questo codice, apparirà una finestra di prompt, seleziona l'intervallo di dati in cui vuoi contare e sommare le celle in base alla formattazione condizionale. Poi, clicca OK, Vedi screenshot:
- In un'altra finestra di prompt, seleziona un colore specifico della formattazione condizionale che vuoi contare e sommare, e clicca OK pulsante, vedi screenshot:
Risultato:
Ora, il risultato, che include sia il conteggio che la somma delle celle con il colore specifico della formattazione condizionale, verrà visualizzato nella finestra pop-up. Vedi screenshot:
Conta e somma le celle formattate condizionalmente con una funzionalità intelligente
Se stai cercando altri metodi veloci e facili per contare e sommare le celle formattate condizionalmente, Kutools per Excel è la tua soluzione ideale. La sua funzionalità Conta per Colore può risolvere questo compito in pochi clic. Scopri l'efficienza e la precisione che Kutools può portare al tuo flusso di lavoro.
Dopo aver scaricato e installato Kutools per Excel, prima seleziona l'intervallo di dati che vuoi contare o sommare in base a un colore specifico della formattazione condizionale. Poi, clicca su Kutools Plus > Conta per Colore per aprire la finestra di dialogo Conta per Colore.
Nella finestra di dialogo Conta per Colore, specifica le operazioni:
- Seleziona Formattazione condizionale dal menu a discesa Metodo colore;
- Specifica Sfondo dal menu a discesa Tipo conteggio, e puoi visualizzare in anteprima i risultati statistici per ogni colore della formattazione condizionale nella finestra di dialogo;
- Infine, clicca Genera report per esportare i risultati calcolati in una nuova cartella di lavoro.
Risultato:
Ora, hai una nuova cartella di lavoro che mostra le statistiche dettagliate in base al colore della formattazione condizionale. Vedi screenshot:
Articoli Correlati:
- Se il colore del carattere è rosso, restituisci un testo specifico
- Come potresti restituire un testo specifico se il colore del carattere è rosso in un'altra cella come mostrato nello screenshot sottostante? In questo articolo, introdurrò alcuni trucchi per eseguire alcune operazioni basate sul testo rosso in Excel.
- Filtra i dati per più colori
- Normalmente, in Excel, puoi filtrare rapidamente le righe con un solo colore, ma hai mai considerato di filtrare le righe con più colori contemporaneamente? In questo articolo, parlerò di un trucco rapido per affrontare questo problema.
- Aggiungi colore all'elenco a discesa
- In Excel, creare un elenco a discesa può esserti di grande aiuto, e a volte, potresti voler codificare a colori i valori dell'elenco a discesa a seconda della selezione corrispondente. Ad esempio, ho creato un elenco a discesa dei nomi di frutta, quando seleziono Apple, voglio che la cella sia colorata automaticamente di rosso, e quando scelgo Orange, la cella può essere colorata di arancione.
- Colora le righe alternate per le celle unite
- È molto utile formattare le righe alternate con un colore diverso in un grande set di dati per scansionarli facilmente, ma a volte potrebbero esserci alcune celle unite nei tuoi dati. Per evidenziare le righe alternativamente con un colore diverso per le celle unite come mostrato nello screenshot sottostante, come potresti risolvere questo problema in Excel?
I migliori strumenti per la produttività in Office
Potenzia le tue competenze in Excel con Kutools per Excel e sperimenta un'efficienza mai vista prima. Kutools per Excel offre oltre300 funzionalità avanzate per aumentare la produttività e risparmiare tempo. Clicca qui per ottenere la funzione di cui hai più bisogno...
Office Tab porta l'interfaccia a schede in Office e rende il tuo lavoro molto più semplice
- Abilita la modifica e la lettura a schede in Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Apri e crea più documenti in nuove schede della stessa finestra, invece che in nuove finestre.
- Aumenta la tua produttività del50% e riduce centinaia di clic del mouse ogni giorno!
Indice
- Video
- Conta e somma le celle in base al colore di sfondo
- Utilizzando una Funzione Definita dall'Utente
- Utilizzando una potente funzionalità – Kutools per Excel
- Utilizzando Filtro e SUBTOTALE
- Conta e somma le celle in base al colore del carattere
- Utilizzando una Funzione Definita dall'Utente
- Utilizzando una funzionalità semplice – Kutools per Excel
- Conta e somma le celle in base al colore della formattazione condizionale
- Utilizzando il codice VBA
- Utilizzando una funzionalità intelligente – Kutools per Excel
- Articoli Correlati
- I migliori strumenti di produttività per Office
- Commenti