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
- Z uporabo uporabniško definirane funkcije
- Z uporabo zmogljive funkcije – Kutools za Excel
- Z uporabo filtra in SUBTOTAL
Štetje in seštevanje celic glede na barvo pisave
Štetje in seštevanje celic na podlagi barve pogojnega oblikovanja
Š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
- Pritisnite Alt + F11 tipke za odpiranje Microsoft Visual Basic za aplikacije okno.
- V odprtem oknu kliknite Vstavi > Moduli da ustvarite nov prazen modul.
- Nato kopirajte in prilepite spodnjo kodo v prazen modul.
Koda VBA: štetje in seštevanje celic glede na barvo ozadjaFunction 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:
- Izberite Standardno oblikovanje Iz Barvna metoda spustni seznam;
- Določite Ozadje Iz Vrsta štetja spustni seznam in v pogovornem oknu si lahko predogledate statistične rezultate za vsako barvo ozadja;
- 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:
- 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.
- 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
- Izberite glavo tabele in kliknite datum > filter. Oglejte si posnetek zaslona:
- 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:
Š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
- Pritisnite Alt + F11 tipke za odpiranje Microsoft Visual Basic za aplikacije okno.
- V odprtem oknu kliknite Vstavi > Moduli da ustvarite nov prazen modul.
- Nato kopirajte in prilepite spodnjo kodo v prazen modul.
Koda VBA: štetje in seštevanje celic glede na barvo pisaveFunction 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:
- Izberite Standardno oblikovanje Iz Barvna metoda spustni seznam;
- Določite Pisava Iz Vrsta štetja spustni seznam in v pogovornem oknu si lahko predogledate statistične rezultate za vsako barvo pisave;
- 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
- Pritisnite Alt + F11 tipke za odpiranje Microsoft Visual Basic za aplikacije okno.
- V odprtem oknu kliknite Vstavi > Moduli da ustvarite nov prazen modul.
- Nato kopirajte in prilepite spodnjo kodo v prazen modul.
Koda VBA: štetje in seštevanje celic na podlagi barve pogojnega oblikovanjaSub 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
- 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:
- 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:
- Izberite Pogojno oblikovanje Iz Barvna metoda spustni seznam;
- Določite Ozadje Iz Vrsta štetja spustni seznam in v pogovornem oknu si lahko predogledate statistične rezultate za vsako barvo oblikovanja pogojev;
- 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?
Najboljša pisarniška orodja za produktivnost
Napolnite svoje Excelove spretnosti s Kutools za Excel in izkusite učinkovitost kot še nikoli prej. Kutools za Excel ponuja več kot 300 naprednih funkcij za povečanje produktivnosti in prihranek časa. Kliknite tukaj, če želite pridobiti funkcijo, ki jo najbolj potrebujete...
Kartica Office prinaša vmesnik z zavihki v Office in poenostavi vaše delo
- Omogočite urejanje in branje z zavihki v Wordu, Excelu, PowerPointu, Publisher, Access, Visio in Project.
- Odprite in ustvarite več dokumentov v novih zavihkih istega okna in ne v novih oknih.
- Poveča vašo produktivnost za 50%in vsak dan zmanjša na stotine klikov miške za vas!
Kazalo
- Video
- Štetje in seštevanje celic glede na barvo ozadja
- Z uporabo uporabniško definirane funkcije
- Z uporabo zmogljive funkcije – Kutools za Excel
- Z uporabo filtra in SUBTOTAL
- Štetje in seštevanje celic glede na barvo pisave
- Z uporabo uporabniško definirane funkcije
- Z uporabo preproste funkcije – Kutools za Excel
- Štetje in seštevanje celic na podlagi barve pogojnega oblikovanja
- Z uporabo kode VBA
- Z uporabo pametne funkcije – Kutools for Excel
- Povezani članki
- Najboljša orodja za pisarniško produktivnost
- Komentarji