✅ Kako prebrojati Obojene ćelije u Excelu (Vodič korak po korak + VIDEO)

Sadržaj

Kako brojati obojene ćelije u Excelu

Pogledajte video - Kako prebrojati obojene ćelije u Excelu

Ne bi li bilo sjajno da postoji funkcija koja može brojati obojene ćelije u Excelu?

Nažalost, ne postoji ugrađena funkcija za to.

ALI…

To se lako može učiniti.

Kako brojati obojene ćelije u Excelu

U ovom vodiču pokazat ću vam tri načina brojanja obojenih ćelija u Excelu (sa i bez VBA):

Korištenje funkcije Filter i SUBTOTAL
Korištenje funkcije GET.CELL
Korištenje prilagođene funkcije stvorene pomoću VBA

#1 Broji obojene ćelije pomoću filtra i SUBTOTALA

Za brojanje obojenih ćelija u Excelu morate koristiti sljedeća dva koraka:

Filtrirajte obojene ćelije
Upotrijebite funkciju SUBTOTAL za brojanje obojenih ćelija koje su vidljive (nakon filtriranja).

Pretpostavimo da imate skup podataka kao što je prikazano u nastavku:

U ovom skupu podataka koriste se dvije boje pozadine (zelena i narančasta).

Evo koraka za brojanje obojenih ćelija u Excelu:

U bilo kojoj ćeliji ispod skupa podataka upotrijebite sljedeću formulu: = SUBTOTAL (102, E1: E20)
Odaberite zaglavlja.
Idite na Podaci -> Poredaj i filtriraj -> Filtriraj. To će primijeniti filtar na sva zaglavlja.
Kliknite bilo koji od padajućih padajućih filtara.
Idite na "Filtriraj po boji" i odaberite boju. U gornjem skupu podataka, budući da se za isticanje ćelija koriste dvije boje, filter prikazuje dvije boje za filtriranje ovih ćelija.

Čim filtrirate ćelije, primijetit ćete da se vrijednost u funkciji SUBTOTAL mijenja i vraća samo broj ćelija koje su vidljive nakon filtriranja.

Kako ovo radi?

Funkcija SUBTOTAL koristi 102 kao prvi argument, koji se koristi za brojanje vidljivih ćelija (skriveni retci se ne broje) u navedenom rasponu.

Ako se podaci ne filtriraju, vraća se 19, ali ako se filtrira, tada se vraća samo broj vidljivih ćelija.

Pokušajte sami … Preuzmite datoteku primjera

#2 Broji obojene ćelije pomoću funkcije GET.CELL

GET.CELL je funkcija Macro4 koja je zadržana zbog razloga kompatibilnosti.

Ne radi ako se koristi kao redovne funkcije na radnom listu.

Međutim, radi u Excel imenovanim rasponima.

Vidi također: Saznajte više o funkciji GET.CELL.

Evo tri koraka za korištenje GET.CELL za brojanje obojenih ćelija u Excelu:

Izradite imenovani raspon pomoću funkcije GET.CELL
Upotrijebite Named Range da biste unijeli kôd boje u stupac
Korištenje broja boje za brojanje ćelija u boji (po boji)

Zaronimo duboko i vidimo što učiniti u svakom od tri spomenuta koraka.

Stvaranje imenovanog raspona

Idite na Formule -> Definiraj naziv.
U dijaloški okvir Novo ime unesite:
- Naziv: GetColor
- Opseg: Radna bilježnica
- Odnosi se na: = GET.CELL (38, List1! $ A2)
  U gornjoj formuli sam koristio List1! $ A2 kao drugi argument. Morate koristiti referencu stupca u kojem imate ćelije s bojom pozadine.

Dobivanje koda boje za svaku ćeliju

U ćeliji uz podatke upotrijebite formulu = GetColor

Ova bi formula vratila 0 ako nema boje pozadine u ćeliji i vratila bi određeni broj ako postoji boja pozadine.

Ovaj je broj specifičan za boju pa sve ćelije s istom bojom pozadine dobivaju isti broj.

Brojte obojene ćelije pomoću koda boje

Ako slijedite gornji postupak, imat ćete stupac s brojevima koji odgovaraju boji pozadine.

Da biste dobili broj određene boje:

Negdje ispod skupa podataka dajte istu boju pozadine ćeliji koju želite prebrojati. Provjerite radite li to u istom stupcu koji ste koristili pri stvaranju imenovanog raspona. Na primjer, koristio sam stupac A, pa ću koristiti ćelije samo u stupcu 'A'.
U susjednoj ćeliji upotrijebite sljedeću formulu:

= COUNTIF ($ F $ 2: $ F $ 20, GetColor)

Ova formula će vam dati broj svih ćelija s navedenom bojom pozadine.

Kako radi?

Funkcija COUNTIF koristi imenovani raspon (GetColor) kao kriterij. Imenovani raspon u formuli odnosi se na susjednu ćeliju s lijeve strane (u stupcu A) i vraća kôd boje za tu ćeliju. Stoga je ovaj kôd u boji kriterij.

Funkcija COUNTIF koristi raspon ($ F $ 2: $ F $ 18) koji sadrži brojeve kodova u boji svih ćelija i vraća broj na temelju broja kriterija.

Pokušajte sami … Preuzmite datoteku primjera

#3 Broji u boji pomoću VBA (stvaranjem prilagođene funkcije)

U gornje dvije metode naučili ste brojati obojene stanice bez upotrebe VBA.

No, ako se dobro snalazite s korištenjem VBA -e, ovo je najjednostavnija od tri metode.

Koristeći VBA, stvorili bismo prilagođenu funkciju koja bi radila kao funkcija COUNTIF i vraćala broj ćelija s određenom bojom pozadine.

Evo koda:

'Kôd koji je stvorio Sumit Bansal s https://trumpexcel.com Funkcija GetColorCount (CountRange kao raspon, CountColor kao raspon) Dim CountColorValue kao cijeli broj Dim TotalCount As Integer CountColorValue = CountColor.Interior.ColorIndex Postavite rCell = CountRange Za svaki rCell In Count rCell.Interior.ColorIndex = CountColorValue Zatim TotalCount = TotalCount + 1 kraj Ako je sljedeće rCell GetColorCount = TotalCount Krajnja funkcija

Da biste stvorili ovu prilagođenu funkciju:

Dok je radna knjiga aktivna, pritisnite Alt + F11 (ili desnom tipkom miša kliknite karticu radnog lista i odaberite Prikaz koda). Time bi se otvorio VB Editor.
U lijevom oknu, ispod radne knjige u kojoj radite, desnom tipkom miša kliknite bilo koji radni list i odaberite Umetni -> Modul. Time bi se umetnuo novi modul. Kopirajte i zalijepite kôd u prozor koda modula.
Dvaput kliknite na naziv modula (prema zadanim postavkama naziv modula u Modulu 1) i zalijepite kôd u prozor koda.
Zatvorite VB Editor.
To je to! Sada imate prilagođenu funkciju na radnom listu pod nazivom GetColorCount.

Da biste koristili ovu funkciju, jednostavno je upotrijebite kao bilo koju redovitu Excel funkciju.

Sintaksa: = GetColorCount (CountRange, CountColor)

CountRange: raspon u kojem želite brojati ćelije s navedenom bojom pozadine.
CountColor: boju za koju želite prebrojati ćelije.

Da biste koristili ovu formulu, upotrijebite istu boju pozadine (koju želite prebrojati) u ćeliji i upotrijebite formulu. Argument CountColor bio bi ista ćelija u koju unosite formulu (kao što je prikazano u nastavku):