Recimo, da imamo tabelo prodaje sadja, kot je prikazano na sliki spodaj, in bomo v stolpcu Količina šteli ali seštevali barvne celice. V tem primeru lahko stolpec Količina filtriramo po barvah in nato filtrirane barvne celice s funkcijo SUBTOTAL enostavno preštejemo ali seštejemo v Excelu.

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

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

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

2. Izberite glavo tabele in kliknite datum > filter. Oglejte si posnetek zaslona:

3. Kliknite ikono Filter  v celici z glavo stolpca Količina in kliknite Barva in določeno barvo, ki jo boste zaporedoma šteli. Oglejte si posnetek zaslona:

Po filtriranju obe formuli SUBTOTAL samodejno štejeta in seštevata vse filtrirane barvne celice v stolpcu Količina. Oglejte si posnetek zaslona:

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

En klik za štetje, vsoto in povprečno obarvanje celic v Excelu

Z odličnim Štej po barvah značilnost Kutools for Excel, celice lahko hitro preštejete, seštevate in povprečite po določeni barvi polnila ali barvi pisave z enim samim klikom v Excelu. Poleg tega bo ta funkcija ugotovila tudi največjo in najmanjšo vrednost celic glede na barvo polnila ali barvo pisave.

Pri tej metodi bomo s funkcijo GET.CELL ustvarili imenovani obseg, dobili barvno kodo celic in nato v Excelu enostavno prešteli ali sešteli po barvni kodi. Naredite naslednje:

1. Kliknite Formule > Določite ime. Oglejte si posnetek zaslona:

2. V pogovornem oknu Novo ime naredite, kot je prikazano spodaj:
(1) V polje Ime vnesite ime;
(2) Vnesite formulo = GET.CELL (38, list4! $ E2) v polje Se nanaša na (Upoštevajte: v formuli, 38 pomeni vrnitev kode celice in List4! $ E2 je prva celica v stolpcu Količina, razen glave stolpca, ki jo morate spremeniti glede na podatke v tabeli.)
(3) Kliknite OK gumb.

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

Zdaj se barvna koda vsake celice v stolpcu Količina vrne v barvni stolpec. Oglejte si posnetek zaslona:

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

Opombe: V obeh formulah 2 $ F $: 20 $ F $ je stolpec Barva, NumColor je navedeni imenovani obseg, $ 2 $: $ 20 $ je stolpec Količina in jih lahko spremenite po potrebi.

Zdaj boste videli, da se celice v stolpcu Količina štejejo in seštejejo po barvah polnila.

Če se barvne celice razpršijo v območju, kot je prikazano na spodnji sliki zaslona, ​​obe zgornji metodi ne moreta šteti ali seštevati barvnih celic. Tu bo ta metoda uvedla VBA za rešitev problema.

1. Držite tipko ALT + F11 in odpre tipko Microsoft Visual Basic za aplikacije okno.

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

VBA: Štetje in seštevanje celic glede na barvo 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 barvne celice: = barvna funkcija (A, B: C, FALSE)
B. Seštejte barvne celice: = barvna funkcija (A, B: C, TRUE)

Opomba: V zgornjih formulah A je celica z določeno barvo ozadja, za katero želite izračunati štetje in vsoto, in B: C je obseg celic, v katerem želite izračunati štetje in vsoto.

4. Na primer, naredite naslednji posnetek zaslona, ​​vnesite formulo= barvna funkcija (A1, A1: D11, FALSE) za štetje rumenih celic. In uporabite formulo = barvna funkcija (A1, A1: D11, TRUE) za seštevanje rumenih celic. Oglejte si posnetek zaslona:

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

Kutools for Excel podpira tudi nekatere uporabne funkcije, ki uporabnikom Excela pomagajo pri posebnih izračunih, na primer štetje po barvi ozadja celice, vsota po barvi pisave itd.

1. Izberite prazno celico, kamor postavite rezultate štetja, in kliknite Kutools > Kutools funkcije > Statistični in matematika > COUNTBYCELLCOLOR. Oglejte si posnetek zaslona:

2. V pogovornem oknu Funkcijski argumenti določite obseg, znotraj katerega boste šteli barvne celice v Reference izberite celico, ki je zapolnjena z določeno barvo ozadja v Barvni indeks št in kliknite OK . Oglejte si posnetek zaslona:

Opombe:
(1) Vnesete lahko tudi določeno funkcijo Kutools = COUNTBYCELLCOLOR ($ A $ 1: $ E $ 20, G2)  neposredno v prazno celico ali vrstico formule, da dobite rezultate štetja;
(2) Kliknite Kutools > Kutools funkcije > Statistični in matematika > SUMBYCELLCOLOR ali tip = SUMBYCELLCOLOR ($ A $ 1: $ E $ 20, G2) v prazno celico neposredno za seštevanje celic glede na določeno barvo ozadja.
uporablja COUNTBYCELLCOLOR in SUMBYCELLCOLOR funkcije za vsako barvo ozadja posebej in dobili boste rezultate, kot je prikazano spodaj:

Kutools funkcije vsebujejo številne vgrajene funkcije, ki uporabnikom Excela olajšajo izračun, vključno z Štetje / vsota / povprečje vidnih celic, Štetje / vsota po barvi celice, Štetje / vsota glede na barvo pisave, Štejte znake, Štejte s krepko pisavo, Itd Brezplačno preskusite!

Z zgornjo uporabniško določeno funkcijo morate formulo vnašati eno za drugo, če je veliko različnih barv, bo ta metoda dolgočasna in dolgotrajna. Če pa že imate Kutools for ExcelJe Štej po barvah Pripomoček lahko hitro ustvarite poročilo o barvnih celicah. Ne samo, da lahko preštejete in seštevate barvne celice, temveč lahko dobite tudi povprečne, največje in najmanjše vrednosti barvnega obsega.

1. Izberite obseg, ki ga želite uporabiti, in kliknite Kutools Plus > Štej po barvah, glej posnetek zaslona:

2. In v Štej po barvah pogovorno okno, naredite, kot je prikazano na spodnji sliki zaslona:
(1) Izberite Standardno oblikovanje Iz Barvna metoda spustni seznam;
(2) Izberite Ozadje Iz Vrsta štetja spustni seznam.
(3) Kliknite gumb Ustvari poročilo.

Opombe: Če želite šteti in seštevati barvne celice glede na določeno barvo pogojnega oblikovanja, izberite Pogojno oblikovanje Iz Barvna metoda spustni seznam v zgornjem pogovornem oknu ali izberite Standardno in pogojno oblikovanje s spustnega seznama za štetje vseh celic, zapolnjenih z določeno barvo.

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

O Štej po barvah funkcija izračuna celice (štetje, vsota, povprečje, največ itd.) glede na barvo ozadja ali barvo pisave. Brezplačno preskusite!

Kako šteti / seštevati celice na podlagi barv pisave v Excelu?