Salt la conținutul principal

Sfaturi Excel: numărați/sumați celulele după culoare (fond, font, formatare condiționată)

În sarcinile zilnice, marcarea cu culori este o metodă populară pentru a distinge și a evidenția rapid datele esențiale. Dar, cum numărăm sau însumăm datele celulei pe baza unei anumite culori (culoarea de umplere, culoarea fontului, formatarea condiționată)? În mod implicit, Excel nu oferă o funcție directă de numărare sau însumare după culoare. Cu toate acestea, cu unele trucuri și metode indirecte, încă putem realiza acest lucru. Acest articol va explora cum să numărați sau să însumați datele după culoare.

Numărați și sumați celulele pe baza culorii de fundal

Numărați și însumați celulele în funcție de culoarea fontului

Numărați și însumați celulele pe baza culorii de formatare condiționată


Video: numărați și însumați celulele în funcție de culoare


Numărați și sumați celulele pe baza culorii de fundal

De exemplu, dacă aveți o gamă de date în care valorile sunt umplute cu culori de fundal diferite, așa cum se arată în captura de ecran de mai jos. Pentru a număra sau a suma celulele pe baza unei anumite culori, Excel nu oferă o caracteristică directă pentru a număra sau a suma celulele pe baza culorii lor de fundal. Cu toate acestea, cu puțină ingeniozitate și câteva tehnici la îndemână, puteți îndeplini această sarcină. Să explorăm câteva metode utile în această secțiune.


Numărați și însumați celulele după culoarea de fundal cu funcția definită de utilizator

Aici, vă vom arăta cum să creați și să utilizați o astfel de funcție definită de utilizator pentru a rezolva această sarcină în Excel. Vă rugăm să urmați următorii pași:

Pasul 1: Deschideți editorul de module VBA și copiați codul

  1. Anunturi Alt + F11 tastele pentru a deschide Microsoft Visual Basic pentru aplicații fereastră.
  2. În fereastra deschisă, faceți clic Insera > Module pentru a crea un nou modul gol.
  3. Apoi, copiați și inserați codul de mai jos în modulul gol.
    Cod VBA: numărați și însumați celulele pe baza culorii de fundal
    Function 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
    

Pasul 2: Creați formule pentru a număra și a însuma celulele după culoarea de fundal

După lipirea codului de mai sus, închideți fereastra modulului, apoi aplicați următoarele formule:

  • Numărați celulele pe baza culorii specifice de fundal:
    Copiați sau tastați formula furnizată mai jos în celula dorită pentru rezultat. Apoi, trageți mânerul de umplere în jos pentru a obține alte rezultate. Vedeți captura de ecran:
    =colorfunction(G2,$B$2:$E$12,FALSE) 
    notițe: În această formulă, G2 este celula de referință cu culoarea specifică de fundal pe care doriți să o potriviți; $B$2:$E$12 este intervalul în care doriți să numărați numărul de celule de culoare G2; FALS este folosit pentru a număra celulele cu culoarea potrivită.
  • Sumă celulele pe baza culorii specifice de fundal:
    Copiați sau tastați formula furnizată mai jos în celula dorită pentru rezultat. Apoi, trageți mânerul de umplere în jos pentru a obține alte rezultate. Vedeți captura de ecran:
    =colorfunction(G2,$B$2:$E$12,TRUE)  
    notițe: În această formulă, G2 este celula de referință cu culoarea specifică de fundal pe care doriți să o potriviți; $B$2:$E$12 este intervalul în care doriți să numărați numărul de celule de culoare G2; TRUE este folosit pentru a suma celulele cu culoarea potrivită.

Numărați și însumați celulele după culoarea de fundal cu o funcție puternică

Pentru cei care nu sunt familiarizați cu programarea, VBA poate părea destul de complex. Aici, vom introduce un instrument puternic - Kutool pentru Excel, ict Numără după culoare caracteristica vă permite să calculați cu ușurință (număr, sumă, medie etc.) pe baza culorii de fundal în doar câteva clicuri. Impresionant, Numără după culoare caracteristica depășește doar culorile de fundal – poate, de asemenea, să diferențieze și să calculeze pe baza culorilor fontului și a formatării condiționate.

După descărcarea și instalarea Kutools pentru Excel, mai întâi, selectați intervalul de date pe care doriți să îl numărați sau să însumați celulele pe baza unei anumite culori de fundal. Apoi, navigați la Kutools Plus Și alegeți Numără după culoare.

În Numără după culoare caseta de dialog, vă rugăm să specificați operațiunile:

  1. Selectați Formatare standard de la Metoda culorii lista verticală;
  2. Specifica Context de la Tipul de numărare listă derulantă și puteți previzualiza rezultatele statistice pentru fiecare culoare de fundal în caseta de dialog;
  3. În cele din urmă, faceți clic pe Generează raport pentru a exporta rezultatele calculate într-un nou registru de lucru.

Rezultat:

Acum veți obține un nou registru de lucru cu statistici. Vedeți captura de ecran:

sfaturi:
  1. Numără după culoare caracteristica acceptă, de asemenea, numărarea și însumarea celulelor pe baza culorii fontului standard, a fundalului sau a culorii fontului din formatarea condiționată și o combinație de culori de umplere și de formatare condiționată.
  2. Interesat de această funcție, vă rog faceți clic pentru a descărca pentru a obține o încercare gratuită timp de 30 de zile.

Numărați și însumați celulele după culoarea de fundal cu funcția Filtru și SUBTOTAL

Presupunând că avem un tabel de vânzări de fructe așa cum se arată mai jos, și vom număra sau însumăm celulele colorate din Sumă coloana.

Pasul 1: Aplicați funcția SUBTOTAL

Selectați celule goale pentru a intra în funcția SUBTOTAL.

  • Pentru a număra toate celulele cu aceeași culoare de fundal, introduceți formula:
    =SUBTOTAL(102, F2:F16)
  • Pentru a suma toate celulele cu aceeași culoare de fundal, introduceți formula;
    =SUBTOTAL(109, F2:F16)
  • notițe: în formulele de mai sus, 102 reprezintă numărarea valorilor numerice într-o listă filtrată, excluzând celulele ascunse; 109 reprezintă însumarea valorilor într-o listă filtrată, excluzând celulele ascunse; F2: F16 este intervalul în care se va calcula fie numărul, fie suma.

Pasul 2: Filtrați celulele pe baza unei anumite culori

  1. Selectați antetul tabelului și faceți clic Date > Filtru. Vedeți captura de ecran:
  2. Apasă pe Filtru icoană  în celula antet a Sumă și faceți clic pe Filtrul de culoare și culoarea specificată pe care o veți număra succesiv. Vedeți captura de ecran:

Rezultat:

După filtrare, formulele SUBTOTAL numără și însumează automat celulele colorate din Sumă coloană. Vedeți captura de ecran:

notițe: Această metodă necesită celulele colorate pe care le veți număra sau sumă sunt în aceeași coloană.

Numărați și însumați celulele în funcție de culoarea fontului

Doriți să numărați sau să însumați celule în funcție de culoarea fontului în Excel? Să presupunem că aveți datele, ca în captura de ecran dată, cu celule care conțin texte în roșu, albastru, portocaliu și negru. Excel nu face acest lucru ușor în mod implicit. Dar nu-ți face griji! În această secțiune, vă vom arăta câteva trucuri simple pentru a face exact asta.


Numărați și însumați celulele pe baza culorii fontului cu funcția definită de utilizator

Pentru a număra și a suma celulele cu anumite culori de font, următoarea funcție definită de utilizator vă poate ajuta să rezolvați această sarcină. Vă rugăm să urmați următorii pași:

Pasul 1: Deschideți editorul de module VBA și copiați codul

  1. Anunturi Alt + F11 tastele pentru a deschide Microsoft Visual Basic pentru aplicații fereastră.
  2. În fereastra deschisă, faceți clic Insera > Module pentru a crea un nou modul gol.
  3. Apoi, copiați și inserați codul de mai jos în modulul gol.
    Cod VBA: numărați și însumați celulele în funcție de culoarea fontului
    Function 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
    

Pasul 2: Creați formule pentru a număra și a însuma celulele după culoarea fontului

După lipirea codului de mai sus, închideți fereastra modulului, apoi aplicați următoarele formule:

  • Numărați celulele în funcție de culoarea specifică a fontului:
    Copiați sau tastați formula furnizată mai jos în celula dorită pentru rezultat. Apoi, trageți mânerul de umplere în jos pentru a obține alte rezultate. Vedeți captura de ecran:
    =ProcessByFontColor($B$2:$E$12,G2, "COUNT")
    notițe: În această formulă, G2 este celula de referință cu culoarea specifică a fontului pe care doriți să o potriviți; $B$2:$E$12 este intervalul în care doriți să numărați numărul de celule de culoare G2.
  • Sumă celulele în funcție de culoarea specifică a fontului:
    Copiați sau tastați formula furnizată mai jos în celula dorită pentru rezultat. Apoi, trageți mânerul de umplere în jos pentru a obține alte rezultate. Vedeți captura de ecran:
    =ProcessByFontColor($B$2:$E$12,G2, "SUM")  
    notițe: În această formulă, G2 este celula de referință cu culoarea specifică a fontului pe care doriți să o potriviți; $B$2:$E$12 este intervalul în care doriți să numărați numărul de celule de culoare G2.

Numărați și însumați celulele pe baza culorii fontului, cu o funcție simplă

Doriți să numărați sau să însumați fără efort valorile celulelor în Excel pe baza culorii fontului? Scufundă-te în Kutools pentru Excel's Numără după culoare caracteristică! Cu acest instrument inteligent, numărarea și însumarea celulelor după culoarea specifică a fontului devine ușor. Descoperă cum Kutools vă poate transforma experiența Excel.

După descărcarea și instalarea Kutools pentru Excel, mai întâi, selectați intervalul de date pe care doriți să îl numărați sau să însumați celulele pe baza unei anumite culori de font. Apoi apasa Kutools Plus > Numără după culoare pentru a deschide Numără după culoare căsuță de dialog.

În Numără după culoare caseta de dialog, vă rugăm să specificați operațiunile:

  1. Selectați Formatare standard de la Metoda culorii lista verticală;
  2. Specifica Font de la Tipul de numărare listă derulantă și puteți previzualiza rezultatele statistice pentru fiecare culoare de font în caseta de dialog;
  3. În cele din urmă, faceți clic pe Generează raport pentru a exporta rezultatele calculate într-un nou registru de lucru.

Rezultat:

Acum, aveți un nou registru de lucru care afișează statisticile detaliate bazate pe culoarea fontului. Vedeți captura de ecran:


Numărați și însumați celulele pe baza culorii de formatare condiționată

În Excel, puteți utiliza în mod obișnuit Formatarea condiționată pentru a aplica o anumită culoare celulelor care îndeplinesc anumite criterii, făcând vizualizarea datelor intuitivă. Dar ce se întâmplă dacă trebuie să numărați sau să însumați acele celule formatate special? Deși Excel nu oferă o modalitate directă pentru aceasta, iată modalități de a manevra această limitare.


Numărați și însumați celulele formatate condiționat cu cod VBA

Numărarea și însumarea celulelor formatate condiționat în Excel nu este simplă folosind funcțiile încorporate. Cu toate acestea, puteți îndeplini această sarcină folosind codul VBA. Să vedem cum puteți folosi VBA pentru asta:

Pasul 1: Deschideți editorul de module VBA și copiați codul

  1. Anunturi Alt + F11 tastele pentru a deschide Microsoft Visual Basic pentru aplicații fereastră.
  2. În fereastra deschisă, faceți clic Insera > Module pentru a crea un nou modul gol.
  3. Apoi, copiați și inserați codul de mai jos în modulul gol.
    Cod VBA: numărați și însumați celulele pe baza culorii de formatare condiționată
    Sub 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
    

Pasul 2: Executați acest cod VBA

  1. După lipirea codului, apăsați F5 pentru a rula acest cod, va apărea o casetă de prompt, vă rugăm să selectați intervalul de date în care doriți să numărați și să însumați celulele pe baza formatării condiționate. Apoi apasa OK, A se vedea captura de ecran:
  2. Într-o altă casetă de solicitare, selectați o anumită culoare de formatare condiționată pe care doriți să o numărați și să o însumați și faceți clic OK buton, vezi captura de ecran:

Rezultat:

Acum, rezultatul, care include atât numărul, cât și suma celulelor cu culoarea de formatare condiționată specificată, va fi afișat în caseta pop-out. Vedeți captura de ecran:


Numărați și însumați celulele formatate condiționat cu o funcție inteligentă

Dacă sunteți în căutarea altor metode rapide și ușoare de numărare și însumare a celulelor formatate condiționat, Kutools pentru Excel este soluția ta de bază. Este Numără după culoare funcția poate rezolva această sarcină în doar câteva clicuri. Pătrundeți-vă pentru a descoperi eficiența și precizia pe care Kutools le poate aduce fluxului dvs. de lucru.

După descărcarea și instalarea Kutools pentru Excel, mai întâi, selectați intervalul de date pe care doriți să îl numărați sau să însumați celulele pe baza unei anumite culori de formatare condiționată. Apoi apasa Kutools Plus > Numără după culoare pentru a deschide Numără după culoare căsuță de dialog.

În Numără după culoare caseta de dialog, vă rugăm să specificați operațiunile:

  1. Selectați Formatarea condițională de la Metoda culorii lista verticală;
  2. Specifica Context de la Tipul de numărare lista derulantă și puteți previzualiza rezultatele statistice pentru fiecare culoare de formatare a condiției în caseta de dialog;
  3. În cele din urmă, faceți clic pe Generează raport pentru a exporta rezultatele calculate într-un nou registru de lucru.

Rezultat:

Acum, aveți un nou registru de lucru care afișează statisticile detaliate bazate pe culoarea de formatare condiționată. Vedeți captura de ecran:


Articole pe aceeaşi temă:

  • Dacă culoarea fontului este roșie, returnați un anumit text
  • Cum ați putea returna un anumit text dacă culoarea fontului este roșie într-o altă celulă, așa cum se arată mai jos? În acest articol, voi introduce câteva trucuri pentru efectuarea unor operațiuni bazate pe textul cu font roșu din Excel.
  • Filtrați datele după mai multe culori
  • În mod normal, în Excel, puteți filtra rapid rândurile cu o singură culoare, dar v-ați gândit vreodată să filtrați rândurile cu mai multe culori în același timp? În acest articol, voi vorbi despre truc rapid pentru a vă rezolva această problemă.
  • Adăugați culoare la lista verticală
  • În Excel, crearea unei liste derulante vă poate ajuta foarte mult și, uneori, trebuie să codificați cu culori valorile listei derulante, în funcție de selecția corespunzătoare. De exemplu, am creat o listă derulantă cu numele fructelor, când selectez Apple, am nevoie ca celula să fie colorată automat cu roșu, iar când aleg Portocaliu, celula poate fi colorată cu portocaliu.
  • Colorează rândurile alternative pentru celulele îmbinate
  • Este foarte util să formatăm rânduri alternative cu o culoare diferită într-o date mari pentru ca noi să scanăm datele, dar, uneori, pot exista unele celule îmbinate în datele dvs. Pentru a evidenția rândurile alternativ cu o culoare diferită pentru celulele îmbinate, așa cum se arată mai jos, cum ați putea rezolva această problemă în Excel?
Comments (237)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
嗨~版主好,我用了VBA 模塊,但是完全沒有動靜,沒有出現顏色儲存格的統計數量,Count欄一片空白~~請問是為什麼呢?跟office版本有關嗎?謝謝
This comment was minimized by the moderator on the site
嗨,MINA,
文章中的VBA代碼,微軟office版本基本上都可以適用,我這代碼可以正常使用。 如果你那邊還用不了,可以上傳你的文件,我們可以幫忙看看哪裡的問題,謝謝!
This comment was minimized by the moderator on the site
I am using =IF(D272>F272,D272-F272,if(F272>D272,F272-D272,"")) formula for subtraction, and I want it will coloured also??
This comment was minimized by the moderator on the site
Hi namrata,
Do you want to fill color for the result of your formula?
So, if the result is D272-F272, you want it, say, red; If the result is F272-D272, you want it, say, green; If blank, blank?
Amanda
This comment was minimized by the moderator on the site
I ran into problems when trying to run the function. Macro errors telling me: No RETURN() or HALT() function found on macro sheet. perhaps somebody could assist here. ThanksPaul
This comment was minimized by the moderator on the site
Hi, How to make a diagram based on the colors in the table? For example, I want to count all the red, green and yellow colors in the cells in a table and make a diagram. How to do this? Please
This comment was minimized by the moderator on the site
Is there a way to count different color backgrounds from conditional formatting? The current code as of 7/14/2020 counts them all as default yellow or not at all.
This comment was minimized by the moderator on the site
Hi Dusty,
You can try the Count by Color feature of Kutools for Excel. This feature will help you quickly calculate (count, sum, average, etc.) cells by cell background color or font color, no matter they are formatted by conditional formatting or solidly format.
This comment was minimized by the moderator on the site
Did anyone find a solution to auto-refresh? I have to manually refresh for it to update. Otherwise, it works great!
This comment was minimized by the moderator on the site
Hi Dennis,
By default, formulas are calculated in Excel until you are turning off the Automatic Formula Calculation. You can enable it by clicking Formulas > Calculation Options > Automatic.
This comment was minimized by the moderator on the site
Anyone have tips on a max by color VBA?
This comment was minimized by the moderator on the site
Hi Natasha,VBA is good but hard to apply. But below methods may solve your work easily too.
Method 1: Use Find & Replace feature to select and statistic the color cells(1) Press Ctrl + H keys to open the Find and Replace dialog, and then enable the Find tab.
(2) In the dialog, click Options to show advanced find options.
(3) Then click Format > Choose Format From Cell, and select one of the specified color cells.
(4) Click Find All. Now all cells with the same fill color are found out and listed at the bottom of the dialog.
(5) Select one of found cells, and press Ctrl + A to select all found cells, so that these cells are selected in the worksheet.
(6) Now you can get the count, average, sum, min, max, etc. of these cells in the task bar.
Note: If a certain statistic result cannot be found one the taskbar, you can right click the task bar, and then tick the specified item to show it.

Method 2: Kutools for Excel
Kutools for Excel supports 30-day free trial. Therefore, you can download it and try its Count by color feature to solve your problem with several clicks only.
This comment was minimized by the moderator on the site
awesome fix! count by color over an entire sheet was just what i was looking for and your VBA code was tighter than others that i have looked at. Works like a charm. Thank you, and again, well done.
This comment was minimized by the moderator on the site
I copied and paste but calculation result is "0" why?? I am using Office 2016.
This comment was minimized by the moderator on the site
I try the same you this command =COUNTBYCELLCOLOR is counting only fill color but is not count by condition formating. Please help to improve code thx.
This comment was minimized by the moderator on the site
I've copied and pasted as stated and have used this formula for over a year but recently saved the workbook to a new name and now the function doesn't work! I can't figure out what the deal is and I'm losing hair and sleep over it! Loading the original workbook, the formula works like a charm but going back to the new one, it doesn't! I've loaded VBA and tried re-creating the function but it doesn't work. Using Office 2019 - any help appreciated.
This comment was minimized by the moderator on the site
#NAME clearly indicates some keyword is not used in 2019, say, Interior.ColorIndex. Try to figure out changing the code from minimum lines to the full by adding one by one or search for the keywords in Excel/VBA in 2019
This comment was minimized by the moderator on the site
I should probably have also noted that I get a #NAME? error in the cell in which I try to use the function.
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations