Savjet: drugi jezici su prevedeni na Google. Možete posjetiti English verzija ove veze.
Prijava
x
or
x
x
Registracija
x

or

Kako računati i zbrojiti ćelije na temelju pozadinske boje u programu Excel?

Pretpostavimo da imate niz ćelija s različitim bojama pozadine, kao što su crvena, zelena, plava i sl., Ali sada morate prebrojati koliko stanica u tom rasponu ima određenu boju pozadine i sumiraju obojene stanice s istom bojom. , U Excelu ne postoji izravna formula za izračunavanje suma i broja ćelija u boji, ovdje ću vam predstaviti neke načine rješavanja ovog problema.

Brojne i zbrojene ćelije temeljene na određenoj boji ispune pomoću filtra i SUBTOTAL

Brojite i zbrajite ćelije na temelju određene boje ispune pomoću funkcije GET.CELL

Broje i zbroje ćelije na temelju specifične boje punjenja s funkcijom definiranom korisnikom

Brojne i zbrojene ćelije temeljene na specifičnoj boji ispune s Kutools funkcijama

Brojite i zbrajite ćelije na temelju određene boje ispune (ili boje uvjetnog oblikovanja) s Kutools za Excel

Jedan klik za brojanje, zbrajanje i prosječne obojene ćelije u Excelu

S izvrsnim Ocijenite bojom značajka Kutools za Excel, možete brzo brojati, zbrajati i prosječne stanice prema zadanoj boji ispisa ili boji fonta samo jednim klikom u Excelu. Osim toga, ova značajka će također otkriti maksimalne i min vrijednosti stanica prema boji ispune ili boji fonta. Cijela značajka besplatne probne verzije 60-dana!
Broj oglasa po boji 2


Odbrojavanje i zbrajanje obojenih stanica pomoću filtra i SUBTOTALA

Pretpostavimo da imamo tablicu prodaje plodova kao što je prikazano na slici ispod, a mi ćemo brojati ili zbrajati obojene ćelije u stupcu Iznos. U toj situaciji možemo filtrirati stupac Iznos po boji, a zatim brojati ili zbrajati obojene ćelije pomoću funkcije SUBTOTAL u Excelu.

1, Odaberite prazne ćelije za ulazak u funkciju SUBTOTAL.

  1. Da biste izračunali sve ćelije s istom bojom pozadine, unesite formulu = SUBTOTAL (102, E2: E20);
  2. Za sumiranje svih ćelija s istom bojom pozadine, unesite formulu = SUBTOTAL (109, E2: E20);


bilješke: U obje formule, E2: E20 je stupac Iznos koji sadrži obojene ćelije i možete ih mijenjati kako vam je potrebno.

2, Odaberite zaglavlje tablice i kliknite datum > Filter, Pogledajte snimak zaslona:

3, Kliknite ikonu Filtar u stupcu zaglavlja stupca Iznos i kliknite Filtriraj po boji i određenu boju koju ćete brojati sukcesivno. Pogledajte snimku zaslona:

Nakon filtriranja, obje formule SUBTOTAL broje i zbrajaju sve filtrirane ćelije boje u stupcu Iznos automatski. Pogledajte snimku zaslona:

bilješke: Ova metoda zahtijeva obojene ćelije koje ćete brojati ili zbrajati u istom stupcu.


Brojati ili sumirati obojene stanice pomoću funkcije GET.CELL

U ovoj metodi stvorit ćemo imenovani raspon pomoću funkcije GET.CELL, dobiti kod boja ćelija, a zatim brojati ili zbrajati kodom boje u Excelu. Molimo učinite sljedeće:

1, Klik Formule > Definirajte ime, Pogledajte snimak zaslona:

2, U dijaloškom okviru Novo ime učinite sljedeće:
(1) Upišite ime u okvir Ime;
(2) Unesite formulu = GET.CELL (38, Sheet4! $ E2) u okviru Odnosi se na okvir (bilješke: u formuli, 38 znači vraćanje koda ćelije, i Sheet4! $ E2 prva je ćelija u stupcu Iznos, osim zaglavlja stupca koje trebate promijeniti na temelju podataka tablice.)
(3) Kliknite gumb OK tipku.

3, Sada dodajte novi stupac Boja desno u izvornu tablicu. Zatim upišite formulu = NumColor i povucite ručicu AutoFill da biste primijenili formulu na druge ćelije u stupcu Boja. Pogledajte snimku zaslona:
bilješke: U formuli, NumColor je imenovani raspon koji smo naveli u prvim 2 koracima. Morate ga promijeniti na određeno ime koje ste postavili.

Sada se boja stupca svake ćelije u stupcu Iznos vraća u stupcu Boja. Pogledajte snimku zaslona:

4, Kopirajte i navedite boju ispune u praznom rasponu u aktivnom radnom listu i upišite formule pored nje kao što je prikazano ispod slike zaslona:
A. Za brojanje stanica po boji, unesite formulu = COUNTIF ($ F $ 2: $ F $ 20, NumColor);
B. Da biste zbrojili ćelije po boji, unesite formulu = SUMIF ($ F $ 2: $ F $ 20, NumColor, $ E $ 2: $ E $ 20).

bilješke: U obje formule, $ F $ 2: $ F $ 20 je stupac Boja, NumColor je navedeni naziv raspona, $ E $ 2: $ E $ 20 je stupac Iznos, a možete ih mijenjati prema potrebi.

Sada ćete vidjeti ćelije u stupcu Iznos se broje i zbrajaju po bojama popunjavanja.


Broje i zbroje ćelije na temelju specifične boje punjenja s funkcijom definiranom korisnikom


Pretpostavimo da se obojene stanice raspršuju u rasponu kao ispod prikazane slike, obje gore navedene metode ne mogu računati ili zbrajati obojene stanice. Ovdje će ova metoda uvesti VBA za rješavanje problema.

1, Držite pritisnuto ALT + F11 tipke i otvara Microsoft Visual Basic for Applications prozor.

2, Klik umetak > Moduli, i zalijepite sljedeći kod u prozor modula.

VBA: Brojanje i zbrajanje ćelija na temelju boje pozadine:

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, Zatim spremite kôd i primijenite sljedeću formulu:
A. Izbrojite obojene stanice: = Colorfunction (A, B: C, FALSE)
B. Zbroj obojenih stanica: = Colorfunction (A, B: C, TRUE)

Napomena: u gornjim formulama, A je ćelija s određenom bojom pozadine koju želite izračunati i zbroj, i PRIJE KRISTA je područje ćelija gdje želite izračunati zbroj i zbroj.

4, Na primjer, unesite sljedeću snimku zaslona, ​​unesite formulu= Colorfunction (A1, A1: D11, FALSE) da broje žute stanice. I upotrijebite formulu = Colorfunction (A1, A1: D11, TRUE) za zbroj žutih stanica. Pogledajte snimak zaslona:

5, Ako želite brojati i skupiti druge bijeljene ćelije, ponovite korak 4. Tada ćete dobiti sljedeće rezultate:


Brojne i zbrojene ćelije temeljene na specifičnoj boji ispune s Kutools funkcijama

Kutools for Excel također podržava neke korisne funkcije koje pomažu korisnicima programa Excel da izrađuju posebne izračune, kaže brojanje boje pozadine u ćeliji, suma prema boji fonta itd.

Kutools za Excel - Uključuje više od 300 praktičnih Excel alata. Cijeli besplatni probni 60-dan bez kreditne kartice! Nabavite ga sada!

1, Odaberite praznu ćeliju koju stavite rezultate brojanja i kliknite Kutools > Kutoolske funkcije > Statistički & matematika > COUNTBYCELLCOLOR, Pogledajte snimak zaslona:

2, U dijaloškom okviru Argumenti funkcija navedite raspon u kojem ćete brojati obojene ćelije unutar Upućivanje okvir, odaberite ćeliju koja je ispunjena navedenom bojom pozadine u Color_index_nr i kliknite OK dugme. Pogledajte snimak zaslona:

Napomene:
(1) Također možete upisati specificiranu Kutools funkciju = COUNTBYCELLCOLOR ($ A $ 1: $ E $ 20, G2) u praznu ćeliju ili formulu izravno za dobivanje rezultata brojanja;
(2) Kliknite Kutools > Kutoolske funkcije > Statistički & matematika > SUMBYCELLCOLOR ili tipa = SUMBYCELLCOLOR ($ A $ 1: $ E $ 20, G2) u praznu ćeliju izravno za zbrajanje ćelija na temelju određene boje pozadine.
Nanesite COUNTBYCELLCOLOR i SUMBYCELLCOLOR Funkcije za svaku boju pozadine zasebno, a rezultate ćete dobiti kao na slici ispod:

Kutoolske funkcije sadrže brojne ugrađene funkcije koje korisnicima programa Excel pomažu lako izračunati, uključujući Count / Sum / Average Vidljive stanice, Broj / suma po boji ćelija, Count / Sum po boji fonta, Broji znakove, Brojite po fontu podebljano, Itd Imajte besplatnu probnu verziju!


Broje i zbrojne ćelije na temelju specifične boje punjenja s Kutools za Excel

S gornjom definiranom funkcijom korisnika morate unijeti jednu formulu jednu po jednu, ako postoji mnogo različitih boja, ta će metoda biti dosadna i dugotrajna. Ali ako jeste Kutools za Excel'S Ocijenite bojom korisnost, možete brzo generirati izvješće o boji stanica. Ne samo da možete brojati i zbrojiti obojene ćelije, već možete dobiti i prosječne, maksimumne i min vrijednosti obojenog raspona.

Kutools za Excel - Uključuje više od 300 praktičnih Excel alata. Cijeli besplatni probni 60-dan bez kreditne kartice! Nabavite ga sada!

1, Odaberite raspon koji želite koristiti i kliknite Kutools Više > Ocijenite bojom, pogledajte snimak zaslona:

2, I u Ocijenite bojom dijaloški okvir, učinite kao na slici ispod:
(1) Odaberite Standardno oblikovanje od Način boja padajući popis;
(2) Odaberite pozadina od Broj tipa padajući popis.
(3) Kliknite gumb Generiraj izvješće.

bilješke: Za brojanje i zbrajanje obojenih stanica prema određenoj boji uvjetnog oblikovanja, odaberite Uvjetno oblikovanje od Način boja padajućem popisu u gornjem dijaloškom okviru ili odaberite Standardno i uvjetno oblikovanje s padajućeg popisa broji sve ćelije ispunjene navedenom bojom.

Sada ćete dobiti novu radnu knjigu sa statistikom. Pogledajte snimku zaslona:

Odlomak Ocijenite bojom značajka izračunava (Count, Sum, Average, Max, itd.) stanice prema boji pozadine ili boji fonta. Imajte besplatnu probnu verziju!


Povezani članak:

Kako računati / zbrojiti ćelije na temelju boja fonta u programu Excel?


Demo: Brojanje i zbirne ćelije na temelju boje pozadinske, uvjetne oblikovanja:

U ovom videozapisu Kutools i karticu Kutools Više kartica dodana je za Kutools za Excel, Ako je potrebno, kliknite ovdje bez ograničenja besplatnog probnog razdoblja 60!



Preporučeni alati za produktivnost

Kartica sustava Office

zlato star1 Donesite praktične kartice u program Excel i drugi Office, kao što su Chrome, Firefox i novi Internet Explorer.

Kutools za Excel

zlato star1 Nevjerojatno! Povećajte produktivnost u 5 minuta. Ne trebaju nikakve posebne vještine, osim dva sata svaki dan!

zlato star1 300 nove značajke za Excel, izraditi Excel mnogo jednostavan i snažan:

  • Spoji ćeliju / retke / stupce bez gubitka podataka.
  • Kombinirajte i konsolidirajte više tablica i radnih knjiga.
  • Usporedba raspona, kopiranje više raspona, pretvaranje teksta na datum, jedinica i pretvorba valuta.
  • Count po bojama, Paging Subtotal, Advanced Sort i Super Filter,
  • Više Odabir / umetanje / brisanje / tekst / oblikovanje / veza / komentar / radne knjige / alati za radne listove ...

Screen shot tvrtke 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.
    Mori · 1 months ago
    I copied and paste but calculation result is "0" why?? I am using Office 2016.
  • To post as a guest, your comment is unpublished.
    Troy Havard · 2 months ago
    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.
    • To post as a guest, your comment is unpublished.
      Jku · 1 months ago
      #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
    • To post as a guest, your comment is unpublished.
      Troy Havard · 2 months ago
      I should probably have also noted that I get a #NAME? error in the cell in which I try to use the function.
  • To post as a guest, your comment is unpublished.
    Karolin · 3 months ago
    works fine, except it does not update the value in the totals when i change a cell to a diffrent colour. i have to click in the totals cell again to trigger a recount. Any Ideas how to fix this?
    • To post as a guest, your comment is unpublished.
      Arnier Hamz · 2 months ago
      Same here, i tried Ctrl+Alt+F9 or Ctrl+Shift+Alt+F9 to recalculate all formula and it works
  • To post as a guest, your comment is unpublished.
    Aaron · 4 months ago
    This doesn't work at all. It's total BS.
  • To post as a guest, your comment is unpublished.
    Schmingy · 6 months ago
    I was experiencing issues when attempting to use this function as-is to count/sum the lighter (pastel?) colors, at least in Excel 2016. Some debugging led me to realize that the range of "ColorIndex" values is quite limited, and the function above fails to distinguish between light yellow and light orange, reading both as ColorIndex = 19.

    Using the "Color" attribute, instead (as in "lCol = rColor.Interior.Color" and the two instances of "If rCell.Interior.Color = lCol Then", per the sample code above), resolved the issue.