Piškotki nam pomagajo dostaviti naših storitev. Z uporabo naših storitev se strinjate z našo uporabo piškotkov.
Namig: drugi jeziki so prevedeni v Google. Lahko obiščete English različico te povezave.
Vpiši se
x
or
x
x
Registracija
x

or

Kako izračunati in povzeti celice glede na barvo ozadja v programu Excel?

Recimo, da imate obseg celic z različnimi barvami ozadja, kot so rdeča, zelena, modra in tako naprej, zdaj pa morate prešteti, koliko celic v tem razponu ima določeno barvo ozadja in se zberejo barve celic z isto določeno barvo. . V Excelu ni neposredne formule za izračun zneska in števila barvnih celic, tukaj bom predstavil nekaj načinov za rešitev tega problema.

Count in Sum celice, ki temeljijo na določeni barvi polnila s filtrom in SUBTOTAL

Count in Sum celice, ki temeljijo na posebni barvi polnjenja s funkcijo GET.CELL

Številke in vsote celic, ki temeljijo na specifični barvi polnila z uporabniško določeno funkcijo

Count in Sum celice, ki temeljijo na določeni barvi polnjenja (ali barvi pogojnega oblikovanja) s Kutools za Excel

En klik za štetje, vsoto in povprečno obarvane celice v Excelu

Z odličnim Count by Color značilnost Kutools za Excel, lahko hitro preštejete, zbirate in povprečite celice z določeno barvo polnila ali barvo pisave z enim samim klikom v Excelu. Poleg tega bo ta funkcija ugotovila tudi največje in najmanjše vrednosti celic z barvo polnila ali barvo pisave. Popolna brezplačna preizkusna različica 60-dan!
Število oglasov po barvi 2


Count in Sum barve celic s filtrom in SUBTOTAL

Če predpostavimo, da imamo prikazano tabelo prodaje sadja, kot je prikazano spodaj, in bomo v stolpcu Amount upoštevali ali zbirali barvne celice. V tem primeru lahko stolpec Amount filtriramo po barvah in nato v Excelu izračunamo ali filtriramo obarvane celice s funkcijo SUBTOTAL.

1. Izberite prazne celice, da vstopite v funkcijo SUBTOTAL.

  1. Če želite prešteti vse celice z isto barvo ozadja, vnesite formulo = SUBTOTAL (102, E2: E20);
  2. Če želite zbrati vse celice z isto barvo ozadja, vnesite formulo = SUBTOTAL (109, E2: E20);


Opombe: V obeh formulah je E2: E20 stolpec Količina, ki vsebuje barvne celice, in jih lahko spremenite, kot želite.

2. Izberite glavo tabele in kliknite datum > filter. Prikaz slike:

3. Kliknite ikono filtra v celici glave stolpca Količina in kliknite Filter by Color in določeno barvo, ki jo boste upoštevali zaporedoma. Oglejte si posnetek zaslona:

Po filtriranju obe formuli SUBTOTAL samodejno preštejeta in seštejeta vse filtrirane barvne celice v stolpcu Amount. Oglejte si posnetek zaslona:

Opombe: Ta metoda zahteva, da so obarvane celice, ki jih boste šteli ali vsota, v istem stolpcu.


Count ali Sum barvne celice z GET.CELL funkcijo

V tej metodi bomo ustvarili imenovani obseg s funkcijo GET.CELL, dobili barvno kodo celic in nato v Excelu zbrali ali zbrali barvno kodo. Prosimo, storite naslednje:

1. Kliknite Formule > Določite ime. Prikaz slike:

2. V pogovornem oknu Novo ime naredite spodaj prikazano sliko:
(1) Vnesite ime v polje Ime;
(2) Vnesite formulo = GET.CELL (38, Sheet4! $ E2) v polju Nanaša se na (Upoštevajte: v formuli, 38 pomeni vrniti kodo celice in Sheet4! $ E2 je prva celica v stolpcu Znesek, razen glave stolpca, ki jo morate spremeniti glede na podatke tabele.)
(3) Kliknite na OK gumb.

3. Sedaj dodajte nov stolpec Barva v prvotno tabelo. Nato vnesite formulo = NumColor in povlecite ročico za samodejno izpolnjevanje, da uporabite formulo za druge celice v stolpcu Barva. Oglejte si posnetek zaslona:
Opombe: V formuli, NumColor je imenovani obseg, ki smo ga podali v prvih 2 korakih. Spremeniti ga morate na določeno ime, ki ste ga nastavili.

Zdaj se barvna koda vsake celice v stolpcu Količina prikaže v barvnem stolpcu. Oglejte si posnetek zaslona:

4. Kopirajte in navedite barvo polnila v praznem razponu v aktivnem delovnem listu in vnesite formule zraven njega, kot je prikazano spodaj:
A. Če želite prešteti celice po barvi, vnesite formulo = COUNTIF ($ F $ 2: $ F $ 20, NumColor);
B. Če želite zbrati celice po barvi, vnesite formulo = SUMIF ($ F $ 2: $ F $ 20, NumColor, $ E $ 2: $ E $ 20).

Opombe: V obeh formulah: $ F $ 2: $ F $ 20 je stolpec Barva, NumColor je podano imenovano območje, $ E $ 2: $ E $ 20 je stolpec Znesek in lahko jih spreminjate, kot želite.

Sedaj boste videli celice v stolpcu Količina se bodo šteli in sešteli po barvah polnjenja.


Številke in vsote celic, ki temeljijo na specifični barvi polnila z uporabniško določeno funkcijo


Če predpostavimo, da so barvne celice razpršene v obsegu, kot je prikazano na spodnji sliki, obe zgoraj navedeni metodi ne moreta šteti ali zbirati barvnih celic. Tukaj bo ta metoda uvedla VBA za rešitev problema.

1. Drži dol ALT + F11 tipke in odpre Microsoft Visual Basic za aplikacije okno.

2. Kliknite Vstavi > Moduliin prilepite naslednjo kodo v okno modula.

VBA: Število in seštevek celic na podlagi barve ozadja:

Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
Dim rCell As Range
Dim lCol As Long
Dim vResult
lCol = rColor.Interior.ColorIndex
If SUM = True Then
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = WorksheetFunction.SUM(rCell, vResult)
End If
Next rCell
Else
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = 1 + vResult
End If
Next rCell
End If
ColorFunction = vResult
End Function

3. Nato shranite kodo in uporabite naslednjo formulo:
A. Preštejte obarvane celice: = barvna funkcija (A, B: C, FALSE)
B. Sum barvnih celic: = barvna funkcija (A, B: C, TRUE)

Opomba: V zgornjih formulah, A je celica z določeno barvo ozadja, ki jo želite izračunati in štetje, in B: C je obseg celic, kjer želite izračunati število in vsoto.

4. Na primer, vnesite naslednjo sliko, vnesite formulo= barvna funkcija (A1, A1: D11, FALSE) za štetje rumenih celic. In uporabite formulo = barvna funkcija (A1, A1: D11, TRUE) za vsoto rumenih celic. Prikaz slike:

5. Če želite prešteti in povzeti druge barvne celice, ponovite korak 4. Nato boste dobili naslednje rezultate:


Count in Sum celice, ki temeljijo na specifični barvi polnila z Kutools za Excel

Z zgornjo uporabniško določeno funkcijo morate vnesti formulo eno za drugo, če je veliko različnih barv, ta metoda bo dolgočasna in dolgotrajna. Ampak, če imaš Kutools za ExcelJe Count by Color uporabnost, lahko hitro ustvarite poročilo o barvnih celicah. Ne samo, da lahko računate in seštejete barvne celice, ampak lahko tudi dobite povprečne, maksimalne in min vrednosti barvnega območja.

1. Izberite obseg, ki ga želite uporabiti, in kliknite Kutools Plus > Count by Color, si oglejte sliko zaslona:

2. In v Count by Color v pogovornem oknu, kot je prikazano spodaj:
(1) Izberite Standardno oblikovanje Iz Barvna metoda spustni seznam;
(2) Izberite Ozadje Iz Tip štetja spustni seznam.
(3) Kliknite gumb Generiraj poročilo.

Kutools za Excel - Vključuje več kot priročna orodja Excel za 300. Celotna brezplačna preizkusna različica 60-dan, brez kreditne kartice! Get it now!

Opombe: Za štetje in seštevanje barvnih celic z določeno barvo pogojnega oblikovanja izberite Pogojno oblikovanje Iz Barvna metoda ali pa izberite Standardno in pogojno oblikovanje s spustnega seznama preštejte vse celice, ki jih zapolni določena barva.

Zdaj boste dobili nov delovni zvezek s statistiko. Oglejte si posnetek zaslona:


Povezani članek:

Kako štetje / vsota celic glede na barve pisave v Excelu?


Demo: štetje in vsota celic na podlagi ozadja, pogojno oblikovanje barve:

V tem videoposnetku Kutools in zavihek Kutools Plus zavihek doda Kutools za Excel. Če je potrebno, kliknite tukaj da brezplačno brezplačno preizkusite 60-dan!



Priporočena orodja za produktivnost

Office Tab

zlata zvezda1 Pripeljite priročne zavihke v programsko opremo Excel in drugo programsko opremo, tako kot Chrome, Firefox in novi Internet Explorer.

Kutools za Excel

zlata zvezda1 Neverjetno! Povečajte produktivnost v minutah 5. Ne potrebujete posebnih veščin, prihranite dve uri vsak dan!

zlata zvezda1 300 Nove funkcije za Excel, naredite Excel precej enostavno in zmogljivo:

  • Spoji celice / vrstice / stolpci brez izgube podatkov.
  • Združite in konsolidirajte več listov in delovnih zvezkov.
  • Primerjajte obsege, kopiraj več razponov, pretvorite besedilo v datum, pretvorbo enote in valute.
  • Count by Colors, Paging Subtotals, Advanced Sort in Super Filter,
  • Več Izberite / Vstavi / Izbriši / Besedilo / Format / Povezava / Komentar / Delovne knjige / Delovni listi ...

Zaslonski posnetek Kutools za Excel

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Thirumalraj · 4 years ago
    Very Helpful...thanks a lott :)
  • To post as a guest, your comment is unpublished.
    Tiny · 4 years ago
    how do you calculate average using this?
  • To post as a guest, your comment is unpublished.
    Sree · 4 years ago
    Thanks a ton. This is awesome.
  • To post as a guest, your comment is unpublished.
    Srinu · 4 years ago
    Hi ,i have applied conditional formatting for color coding ,now i need to count & sum the colored cells ,plz help
  • To post as a guest, your comment is unpublished.
    Nik · 4 years ago
    Wonderful bit of code - thanks so much!