✅ Kako šteti BARVANE celice v Excelu (Vodnik po korakih + VIDEO)

Kazalo

Kako šteti barvne celice v Excelu

Oglejte si video - Kako šteti barvne celice v Excelu

Ali ne bi bilo super, če bi obstajala funkcija, ki bi lahko štela barvne celice v Excelu?

Žal za to ni vgrajene funkcije.

ALI…

To je enostavno narediti.

Kako šteti barvne celice v Excelu

V tej vadnici vam bom pokazal tri načine štetja barvnih celic v Excelu (z in brez VBA):

Uporaba funkcije Filter in SUBTOTAL
Uporaba funkcije GET.CELL
Uporaba funkcije po meri, ustvarjene z uporabo VBA

#1 Štejte barvne celice z uporabo filtra in SUBTOTAL

Za štetje barvnih celic v Excelu morate uporabiti naslednja dva koraka:

Filtrirajte obarvane celice
Uporabite funkcijo SUBTOTAL za štetje vidnih barvnih celic (po filtriranju).

Recimo, da imate nabor podatkov, kot je prikazano spodaj:

V tem naboru podatkov se uporabljata dve barvi ozadja (zelena in oranžna).

Tu so koraki za štetje barvnih celic v Excelu:

V kateri koli celici pod nizom podatkov uporabite naslednjo formulo: = SUBTOTAL (102, E1: E20)
Izberite glave.
Pojdite na Podatki -> Razvrsti in filtriraj -> Filter. S tem boste za vse glave uporabili filter.
Kliknite kateri koli spustni meni filtra.
Pojdite na "Filtriraj po barvi" in izberite barvo. Ker v zgornjem naboru podatkov za označevanje celic uporabljamo dve barvi, filter prikaže dve barvi za filtriranje teh celic.

Takoj, ko filtrirate celice, boste opazili, da se vrednost v funkciji SUBTOTAL spremeni in vrne samo število celic, ki so vidne po filtriranju.

Kako to deluje?

Funkcija SUBTOTAL uporablja 102 kot prvi argument, ki se uporablja za štetje vidnih celic (skrite vrstice se ne štejejo) v podanem obsegu.

Če podatki niso filtrirani, vrne 19, če pa je filtrirano, vrne le število vidnih celic.

Poskusite sami … Prenesite datoteko z vzorcem

#2 Štejte barvne celice s funkcijo GET.CELL

GET.CELL je funkcija Macro4, ki je bila ohranjena zaradi združljivosti.

Ne deluje, če se uporablja kot redne funkcije na delovnem listu.

Vendar pa deluje v imenovanih obsegih Excel.

Poglej tudi: Več o funkciji GET.CELL.

Tu so trije koraki za uporabo GET.CELL za štetje barvnih celic v Excelu:

Ustvarite imenovano območje s funkcijo GET.CELL
Uporabite imenovano območje, da dobite barvno kodo v stolpcu
Uporaba barvne številke za štetje števila barvnih celic (po barvi)

Potopimo se globoko in poglejmo, kaj storiti v vsakem od treh omenjenih korakov.

Ustvarjanje imenovanega obsega

Pojdite na Formule -> Določi ime.
V pogovornem oknu Novo ime vnesite:
- Ime: GetColor
- Področje uporabe: Delovni zvezek
- Nanaša se na: = GET.CELL (38, Sheet1! $ A2)
  V zgornji formuli sem uporabil List1! $ A2 kot drugi argument. Uporabiti morate sklic na stolpec, kjer imate celice z barvo ozadja.

Pridobivanje barvne kode za vsako celico

V celici ob podatkih uporabite formulo = GetColor

Ta formula bi vrnila 0, če v celici ni barve ozadja, in bi vrnila določeno število, če obstaja barva ozadja.

Ta številka je značilna za barvo, zato vse celice z isto barvo ozadja dobijo enako število.

Štejte barvne celice z barvno kodo

Če sledite zgornjemu postopku, bi imeli stolpec s številkami, ki ustrezajo barvi ozadja.

Če želite prešteti določeno barvo:

Nekje pod nizom podatkov dajte isti barvi ozadja celici, ki jo želite šteti. To naredite v istem stolpcu, ki ste ga uporabili pri ustvarjanju imenovanega obsega. Na primer, uporabil sem stolpec A, zato bom celice uporabil samo v stolpcu 'A'.
V sosednji celici uporabite naslednjo formulo:

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

Ta formula vam bo dala število vseh celic z določeno barvo ozadja.

Kako deluje?

Funkcija COUNTIF kot merilo uporablja imenovano območje (GetColor). Imenovani obseg v formuli se nanaša na sosednjo celico na levi strani (v stolpcu A) in vrne barvno kodo za to celico. Ta barvna oznaka je torej merilo.

Funkcija COUNTIF uporablja obseg ($ F $ 2: $ F $ 18), ki vsebuje številke barvnih kod vseh celic in vrne število na podlagi števila meril.

Poskusite sami … Prenesite datoteko z vzorcem

#3 Štej barvno z uporabo VBA (z ustvarjanjem funkcije po meri)

V zgornjih dveh metodah ste se naučili šteti obarvane celice brez uporabe VBA.

Če pa uporabljate VBA, je to najlažja od treh metod.

Z uporabo VBA bi ustvarili funkcijo po meri, ki bi delovala kot funkcija COUNTIF in vrnila število celic z določeno barvo ozadja.

Tukaj je koda:

'Koda, ki jo je ustvaril Sumit Bansal iz https://trumpexcel.com Funkcija GetColorCount (CountRange As Range, CountColor As Range) Dim CountColorValue As Integer Dim TotalCount As Integer CountColorValue = CountColor.Interior.ColorIndex Set rCell = CountRange Za vsako rCell rCell.Interior.ColorIndex = CountColorValue Potem TotalCount = TotalCount + 1 Konec Naslednja rCell GetColorCount = Končna funkcija TotalCount

Če želite ustvariti to funkcijo po meri:

Ko je vaš delovni zvezek aktiven, pritisnite Alt + F11 (ali z desno tipko miške kliknite zavihek delovnega lista in izberite Ogled kode). To bi odprlo urejevalnik VB.
V levem podoknu pod delovnim zvezkom, v katerem delate, z desno miškino tipko kliknite kateri koli delovni list in izberite Vstavi -> Modul. To bi vstavilo nov modul. Kopirajte in prilepite kodo v okno kode modula.
Dvokliknite ime modula (privzeto ime modula v modulu 1) in kodo prilepite v okno za kodo.
Zaprite urejevalnik VB.
To je to! Zdaj imate na delovnem listu funkcijo po meri, imenovano GetColorCount.

Če želite uporabiti to funkcijo, jo preprosto uporabite kot katero koli običajno funkcijo Excel.

Sintaksa: = GetColorCount (CountRange, CountColor)

CountRange: obseg, v katerem želite šteti celice z določeno barvo ozadja.
CountColor: barvo, za katero želite šteti celice.

Če želite uporabiti to formulo, uporabite isto barvo ozadja (ki jo želite šteti) v celici in uporabite formulo. Argument CountColor bi bil ista celica, v katero vnesete formulo (kot je prikazano spodaj):