Preskoči na glavno vsebino

Nasveti za Excel: štetje/seštevanje celic po barvi (ozadje, pisava, pogojno oblikovanje)

Pri vsakodnevnih opravilih je barvno označevanje priljubljena metoda za hitro razlikovanje in poudarjanje ključnih podatkov. Kako pa preštejemo ali seštejemo podatke celic na podlagi določene barve (barva polnila, barva pisave, pogojno oblikovanje)? Excel privzeto ne ponuja neposredne funkcije za štetje ali seštevanje po barvah. Kljub temu lahko z nekaterimi triki in posrednimi metodami to še vedno dosežemo. Ta članek bo raziskal, kako šteti ali sešteti podatke po barvah.

Štetje in seštevanje celic glede na barvo ozadja

Štetje in seštevanje celic glede na barvo pisave

Štetje in seštevanje celic na podlagi barve pogojnega oblikovanja


Video: štetje in seštevanje celic glede na barvo


Štetje in seštevanje celic glede na barvo ozadja

Na primer, če imate obseg podatkov, kjer so vrednosti zapolnjene z različnimi barvami ozadja, kot je prikazano na spodnjem posnetku zaslona. Za štetje ali seštevanje celic na podlagi določene barve Excel ne ponuja neposredne funkcije za štetje ali seštevanje celic na podlagi njihove barve ozadja. Z malo iznajdljivosti in nekaj priročnimi tehnikami pa lahko to nalogo opravite. Raziščimo nekaj uporabnih metod v tem razdelku.


Preštejte in seštejte celice glede na barvo ozadja z uporabniško določeno funkcijo

Tukaj vam bomo pokazali, kako ustvariti in uporabiti takšno uporabniško definirano funkcijo za rešitev te naloge v Excelu. Izvedite naslednje korake:

1. korak: Odprite urejevalnik modulov VBA in kopirajte kodo

  1. Pritisnite Alt + F11 tipke za odpiranje Microsoft Visual Basic za aplikacije okno.
  2. V odprtem oknu kliknite Vstavi > Moduli da ustvarite nov prazen modul.
  3. Nato kopirajte in prilepite spodnjo kodo v prazen modul.
    Koda VBA: štetje in seštevanje celic glede na barvo ozadja
    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
    

2. korak: Ustvarite formule za štetje in seštevanje celic glede na barvo ozadja

Ko prilepite zgornjo kodo, zaprite okno modula in nato uporabite naslednje formule:

  • Preštejte celice na podlagi določene barve ozadja:
    Kopirajte ali vnesite spodnjo formulo v želeno celico za rezultat. Nato povlecite ročico za polnjenje navzdol, da dobite druge rezultate. Oglejte si posnetek zaslona:
    =colorfunction(G2,$B$2:$E$12,FALSE) 
    Opombe: V tej formuli, G2 je referenčna celica z določeno barvo ozadja, ki jo želite ujemati; $B$2:$E$12 je obseg, kjer želite prešteti število celic barve G2; FALSE se uporablja za štetje celic z ujemajočo se barvo.
  • Seštejte celice glede na specifično barvo ozadja:
    Kopirajte ali vnesite spodnjo formulo v želeno celico za rezultat. Nato povlecite ročico za polnjenje navzdol, da dobite druge rezultate. Oglejte si posnetek zaslona:
    =colorfunction(G2,$B$2:$E$12,TRUE)  
    Opombe: V tej formuli, G2 je referenčna celica z določeno barvo ozadja, ki jo želite ujemati; $B$2:$E$12 je obseg, kjer želite prešteti število celic barve G2; TRUE se uporablja za seštevanje celic z ujemajočo se barvo.

Preštejte in seštejte celice glede na barvo ozadja z zmogljivo funkcijo

Za tiste, ki niso seznanjeni s programiranjem, se lahko VBA zdi precej zapleten. Tukaj bomo predstavili zmogljivo orodje – Kutool za Excel, njeno Štej po barvah funkcija vam omogoča enostavno izračunavanje (štetje, vsota, povprečje itd.) glede na barvo ozadja v le nekaj klikih. Impresivno, Štej po barvah funkcija presega le barve ozadja – lahko tudi razlikuje in izračuna na podlagi barv pisave in pogojnega oblikovanja.

po prenos in namestitev Kutools for Excel, najprej izberite obseg podatkov, ki jih želite prešteti ali sešteti celice na podlagi določene barve ozadja. Nato se pomaknite do Kutools Plus In izberite Štej po barvah.

v Štej po barvah pogovornem oknu določite operacije:

  1. Izberite Standardno oblikovanje Iz Barvna metoda spustni seznam;
  2. Določite Ozadje Iz Vrsta štetja spustni seznam in v pogovornem oknu si lahko predogledate statistične rezultate za vsako barvo ozadja;
  3. Končno kliknite Ustvari poročilo da izvozite izračunane rezultate v nov delovni zvezek.

Rezultat:

Zdaj boste dobili nov delovni zvezek s statističnimi podatki. Oglejte si posnetek zaslona:

nasveti:
  1. O Štej po barvah funkcija podpira tudi štetje in seštevanje celic na podlagi standardne barve pisave, barve ozadja ali pisave iz pogojnega oblikovanja ter kombinacije barv polnila in pogojnega oblikovanja.
  2. Vas zanima ta funkcija, prosim kliknite za prenos in dobite brezplačno preskusno različico za 30 dni.

Preštejte in seštejte celice glede na barvo ozadja s funkcijo Filter in SUBTOTAL

Recimo, da imamo tabelo s prodajo sadja, kot je prikazano na spodnjem posnetku zaslona, ​​in bomo prešteli ali sešteli obarvane celice v znesek stolpec.

1. korak: Uporabite funkcijo SUBTOTAL

Izberite prazne celice, da vnesete funkcijo SUBTOTAL.

  • Če želite prešteti vse celice z isto barvo ozadja, vnesite formulo:
    =SUBTOTAL(102, F2:F16)
  • Če želite sešteti vse celice z isto barvo ozadja, vnesite formulo;
    =SUBTOTAL(109, F2:F16)
  • Opombe: v zgornjih formulah, 102 predstavlja štetje številskih vrednosti na filtriranem seznamu, medtem ko izključuje skrite celice; 109 predstavlja seštevek vrednosti na filtriranem seznamu brez skritih celic; F2: F16 je obseg, v katerem bosta izračunana štetje ali vsota.

2. korak: Filtrirajte celice glede na določeno barvo

  1. Izberite glavo tabele in kliknite datum > filter. Oglejte si posnetek zaslona:
  2. Kliknite filter Ikona  v glavi celice znesek in kliknite Barva in določeno barvo, ki jo boste zaporedoma šteli. Oglejte si posnetek zaslona:

Rezultat:

Po filtriranju formule SUBTOTAL samodejno preštejejo in seštejejo obarvane celice v znesek stolpec. Oglejte si posnetek zaslona:

Opombe: Ta metoda zahteva, da so barvne celice, ki jih boste prešteli ali sešteli, v istem stolpcu.

Štetje in seštevanje celic glede na barvo pisave

Ali želite prešteti ali sešteti celice glede na njihovo barvo pisave v Excelu? Recimo, da imate podatke, kot na danem posnetku zaslona, ​​s celicami, ki vsebujejo besedila v rdeči, modri, oranžni in črni barvi. Excel tega privzeto ne olajša. Ampak ne skrbi! V tem razdelku vam bomo pokazali nekaj preprostih trikov za to.


Preštejte in seštejte celice glede na barvo pisave z uporabniško določeno funkcijo

Za štetje in seštevanje celic z določenimi barvami pisave vam lahko pri reševanju te naloge pomaga naslednja uporabniško definirana funkcija. Izvedite naslednje korake:

1. korak: Odprite urejevalnik modulov VBA in kopirajte kodo

  1. Pritisnite Alt + F11 tipke za odpiranje Microsoft Visual Basic za aplikacije okno.
  2. V odprtem oknu kliknite Vstavi > Moduli da ustvarite nov prazen modul.
  3. Nato kopirajte in prilepite spodnjo kodo v prazen modul.
    Koda VBA: štetje in seštevanje celic glede na barvo pisave
    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
    

2. korak: Ustvarite formule za štetje in seštevanje celic glede na barvo pisave

Ko prilepite zgornjo kodo, zaprite okno modula in nato uporabite naslednje formule:

  • Preštejte celice na podlagi določene barve pisave:
    Kopirajte ali vnesite spodnjo formulo v želeno celico za rezultat. Nato povlecite ročico za polnjenje navzdol, da dobite druge rezultate. Oglejte si posnetek zaslona:
    =ProcessByFontColor($B$2:$E$12,G2, "COUNT")
    Opombe: V tej formuli, G2 je referenčna celica z določeno barvo pisave, ki jo želite ujemati; $B$2:$E$12 je obseg, kjer želite prešteti število celic barve G2.
  • Seštejte celice glede na določeno barvo pisave:
    Kopirajte ali vnesite spodnjo formulo v želeno celico za rezultat. Nato povlecite ročico za polnjenje navzdol, da dobite druge rezultate. Oglejte si posnetek zaslona:
    =ProcessByFontColor($B$2:$E$12,G2, "SUM")  
    Opombe: V tej formuli, G2 je referenčna celica z določeno barvo pisave, ki jo želite ujemati; $B$2:$E$12 je obseg, kjer želite prešteti število celic barve G2.

Preštejte in seštejte celice glede na barvo pisave z enostavno funkcijo

Želite enostavno prešteti ali sešteti vrednosti celic v Excelu glede na barvo pisave? Potopite se v Kutools za Excel's Štej po barvah funkcija! S tem pametnim orodjem postane štetje in seštevanje celic po določeni barvi pisave preprosto. Odkrijte, kako Kutools lahko spremeni vašo izkušnjo Excela.

po prenos in namestitev Kutools for Excel, najprej izberite obseg podatkov, ki jih želite prešteti ali sešteti celice na podlagi določene barve pisave. Nato kliknite Kutools Plus > Štej po barvah da odprete Štej po barvah pogovorno okno.

v Štej po barvah pogovornem oknu določite operacije:

  1. Izberite Standardno oblikovanje Iz Barvna metoda spustni seznam;
  2. Določite Pisava Iz Vrsta štetja spustni seznam in v pogovornem oknu si lahko predogledate statistične rezultate za vsako barvo pisave;
  3. Končno kliknite Ustvari poročilo da izvozite izračunane rezultate v nov delovni zvezek.

Rezultat:

Zdaj imate nov delovni zvezek, ki prikazuje podrobno statistiko glede na barvo pisave. Oglejte si posnetek zaslona:


Štetje in seštevanje celic na podlagi barve pogojnega oblikovanja

V Excelu lahko običajno uporabite pogojno oblikovanje za uporabo določene barve v celicah, ki izpolnjujejo določena merila, zaradi česar je vizualizacija podatkov intuitivna. Kaj pa, če morate te posebej oblikovane celice prešteti ali sešteti? Medtem ko Excel ne ponuja neposrednega načina za to, so tukaj načini za manevriranje te omejitve.


Preštejte in seštejte pogojno oblikovane celice s kodo VBA

Štetje in seštevanje pogojno oblikovanih celic v Excelu ni preprosto z uporabo vgrajenih funkcij. Vendar pa lahko to nalogo opravite s kodo VBA. Oglejmo si, kako lahko za to uporabite VBA:

1. korak: Odprite urejevalnik modulov VBA in kopirajte kodo

  1. Pritisnite Alt + F11 tipke za odpiranje Microsoft Visual Basic za aplikacije okno.
  2. V odprtem oknu kliknite Vstavi > Moduli da ustvarite nov prazen modul.
  3. Nato kopirajte in prilepite spodnjo kodo v prazen modul.
    Koda VBA: štetje in seštevanje celic na podlagi barve pogojnega oblikovanja
    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
    

2. korak: Izvedite to kodo VBA

  1. Po lepljenju kode pritisnite F5 tipko za zagon te kode, se bo pojavilo okno s pozivom, izberite obseg podatkov, kjer želite prešteti in sešteti celice na podlagi pogojnega oblikovanja. Nato kliknite OK, Glej posnetek zaslona:
  2. V drugem pozivnem polju izberite določeno barvo pogojnega oblikovanja, ki jo želite prešteti in sešteti, ter kliknite OK gumb, glej posnetek zaslona:

Rezultat:

Zdaj bo rezultat, ki vključuje tako število kot vsoto celic z določeno barvo pogojnega oblikovanja, prikazan v pojavnem polju. Oglejte si posnetek zaslona:


Preštejte in seštejte pogojno oblikovane celice s pametno funkcijo

Če iščete druge hitre in enostavne metode za štetje in seštevanje pogojno oblikovanih celic, Kutools za Excel je vaša najboljša rešitev. Njegovo Štej po barvah funkcija lahko reši to nalogo v samo nekaj klikih. Poglobite se in odkrijte učinkovitost in natančnost, ki ju lahko Kutools prinese v vaš potek dela.

po prenos in namestitev Kutools for Excel, najprej izberite obseg podatkov, ki jih želite prešteti ali sešteti celice na podlagi določene barve pogojnega oblikovanja. Nato kliknite Kutools Plus > Štej po barvah da odprete Štej po barvah pogovorno okno.

v Štej po barvah pogovornem oknu določite operacije:

  1. Izberite Pogojno oblikovanje Iz Barvna metoda spustni seznam;
  2. Določite Ozadje Iz Vrsta štetja spustni seznam in v pogovornem oknu si lahko predogledate statistične rezultate za vsako barvo oblikovanja pogojev;
  3. Končno kliknite Ustvari poročilo da izvozite izračunane rezultate v nov delovni zvezek.

Rezultat:

Zdaj imate nov delovni zvezek, ki prikazuje podrobno statistiko na podlagi barve pogojnega oblikovanja. Oglejte si posnetek zaslona:


Sorodni članki:

  • Če je barva pisave rdeča, vrni določeno besedilo
  • Kako lahko vrnete določeno besedilo, če je barva pisave rdeča v drugi celici, kot je prikazano na spodnjem posnetku zaslona? V tem članku bom predstavil nekaj trikov za izvajanje nekaterih operacij na podlagi besedila rdeče pisave v Excelu.
  • Filtrirajte podatke po več barvah
  • Običajno lahko v Excelu vrstice hitro filtrirate samo z eno barvo, vendar, ali ste kdaj razmišljali o filtriranju vrstic z več barvami hkrati? V tem članku bom govoril o hitrem triku za reševanje te težave.
  • Na spustni seznam dodajte barvo
  • V Excelu vam ustvarjanje spustnega seznama lahko zelo pomaga, včasih pa morate vrednosti spustnega seznama barvno kodirati glede na ustrezno izbrano. Na primer, ustvaril sem spustni seznam imen sadja, ko izberem jabolko, potrebujem, da se celica samodejno obarva rdeče, in ko izberem oranžno, se celica lahko obarva oranžno.
  • Obarvajte nadomestne vrstice za združene celice
  • Zelo koristno je formatirati nadomestne vrstice z drugačno barvo v velikih podatkih, da skeniramo podatke, vendar je včasih v vaših podatkih morda nekaj združenih celic. Kako bi lahko rešili to težavo v Excelu, če želite izmenično označiti vrstice z drugo barvo za združene celice, kot je prikazano na spodnjem posnetku zaslona?
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