✅ Ako počítať FAREBNÉ bunky v programe Excel (podrobný sprievodca + VIDEO)

Obsah

Ako počítať farebné bunky v programe Excel

Pozrite si video - Ako počítať farebné bunky v programe Excel

Nebolo by skvelé, keby existovala funkcia, ktorá dokáže počítať farebné bunky v programe Excel?

Smutné je, že na to neexistuje žiadna vstavaná funkcia.

ALE…

Dá sa to ľahko zvládnuť.

Ako počítať farebné bunky v programe Excel

V tomto návode vám ukážem tri spôsoby počítania farebných buniek v programe Excel (s VBA a bez neho):

Použitie filtra a funkcie SÚČET
Použitie funkcie GET.CELL
Použitie vlastnej funkcie vytvorenej pomocou VBA

#1 Spočítajte farebné bunky pomocou filtra a SUBTOTAL

Na počítanie farebných buniek v programe Excel musíte použiť nasledujúce dva kroky:

Filtrovať farebné bunky
Pomocou funkcie SUBTOTAL počítajte farebné bunky, ktoré sú viditeľné (po filtrovaní).

Predpokladajme, že máte množinu údajov, ako je uvedené nižšie:

V tejto množine údajov sú použité dve farby pozadia (zelená a oranžová).

Tu sú kroky na počítanie farebných buniek v programe Excel:

V ľubovoľnej bunke pod množinou údajov použite nasledujúci vzorec: = SUBTOTAL (102, E1: E20)
Vyberte hlavičky.
Prejdite na položku Údaje -> Zoradiť a filtrovať -> Filter. Tým sa použije filter na všetky hlavičky.
Kliknite na ľubovoľné rozbaľovacie zoznamy filtrov.
Prejdite na položku „Filtrovať podľa farby“ a vyberte farbu. Pretože vo vyššie uvedenom súbore údajov existujú dve farby použité na zvýraznenie buniek, filter zobrazuje dve farby na filtrovanie týchto buniek.

Hneď ako odfiltrujete bunky, všimnete si, že hodnota vo funkcii SUBTOTAL sa zmení a vráti iba počet buniek, ktoré sú viditeľné po filtrovaní.

Ako to funguje?

Funkcia SUBTOTAL používa ako prvý argument 102, ktorý sa používa na počítanie viditeľných buniek (skryté riadky sa nepočítajú) v uvedenom rozsahu.

Ak údaje nie sú filtrované, vrátia 19, ale ak sú filtrované, vrátia iba počet viditeľných buniek.

Skúste to sami … Stiahnite si ukážkový súbor

#2 Spočítajte farebné bunky pomocou funkcie GET.CELL

GET.CELL je funkcia Macro4, ktorá bola zachovaná z dôvodov kompatibility.

Nefunguje, ak sa používa ako bežné funkcie v pracovnom hárku.

Funguje to však v programe Excel s názvom rozsahy.

Pozri tiež: Získajte viac informácií o funkcii GET.CELL.

Tu sú tri kroky na použitie GET.CELL na počítanie farebných buniek v programe Excel:

Vytvorte pomenovaný rozsah pomocou funkcie GET.CELL
Na pomenovanie rozsahu použite kód farby v stĺpci
Použitie čísla farby na spočítanie počtu farebných buniek (podľa farby)

Poďme sa ponoriť do hĺbky a uvidíme, čo robiť v každom z troch uvedených krokov.

Vytvorenie pomenovaného rozsahu

Prejdite na Vzorce -> Definovať meno.
V dialógovom okne Nový názov zadajte:
- Názov: GetColor
- Rozsah: Pracovný zošit
- Vzťahuje sa na: = GET.CELL (38, list1! $ A2)
  Vo vyššie uvedenom vzorci som použil List 1! $ A2 ako druhý argument. Musíte použiť odkaz na stĺpec, kde máte bunky s farbou pozadia.

Získanie farebného kódu pre každú bunku

V bunke susediacej s údajmi použite vzorec = GetColor

Tento vzorec by vrátil 0, ak v bunke NIE JE žiadna farba pozadia, a vrátil by konkrétne číslo, ak existuje farba pozadia.

Toto číslo je špecifické pre farbu, takže všetky bunky s rovnakou farbou pozadia dostanú rovnaké číslo.

Spočítajte farebné bunky pomocou farebného kódu

Ak budete postupovať podľa vyššie uvedeného postupu, budete mať stĺpček s číslami zodpovedajúcimi farbe pozadia.

Ak chcete získať počet konkrétnych farieb:

Niekde pod množinou údajov dajte bunke rovnakú farbu pozadia, ktorú chcete počítať. Uistite sa, že to robíte v rovnakom stĺpci, ktorý ste použili pri vytváraní pomenovaného rozsahu. Použil som napríklad stĺpec A, a preto použijem bunky iba v stĺpci „A“.
V susednej bunke použite nasledujúci vzorec:

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

Tento vzorec vám poskytne počet všetkých buniek so zadanou farbou pozadia.

Ako to funguje?

Funkcia COUNTIF používa ako kritérium pomenovaný rozsah (GetColor). Pomenovaný rozsah vo vzorci odkazuje na susednú bunku vľavo (v stĺpci A) a vracia kód farby pre túto bunku. Preto je kritériom toto číslo kódu farby.

Funkcia COUNTIF používa rozsah ($ F $ 2: $ F $ 18), ktorý obsahuje čísla farebných kódov všetkých buniek a vracia počet na základe čísla kritéria.

Skúste to sami … Stiahnite si ukážkový súbor

Počet 3 sfarbených pomocou VBA (vytvorením vlastnej funkcie)

Vo vyššie uvedených dvoch metódach ste sa naučili počítať farebné bunky bez použitia VBA.

Ak vám však používanie VBA vyhovuje, je to najľahšia z týchto troch metód.

Pomocou VBA by sme vytvorili vlastnú funkciu, ktorá by fungovala ako funkcia COUNTIF a vrátili by počet buniek so špecifickou farbou pozadia.

Tu je kód:

'Kód vytvorený spoločnosťou Sumit Bansal z https://trumpexcel.com Funkcia GetColorCount (CountRange As Range, CountColor As Range) Dim CountColorValue As Integer Dim TotalCount As Integer CountColorValue = CountColor.Interior.ColorIndex Set rCell = CountRange For every rCell In CountRange If rCell.Interior.ColorIndex = CountColorValue Then TotalCount = TotalCount + 1 End If Next rCell GetColorCount = TotalCount End Function

Ak chcete vytvoriť túto vlastnú funkciu:

Keď je váš zošit aktívny, stlačte kombináciu klávesov Alt + F11 (alebo kliknite pravým tlačidlom myši na kartu pracovného hárka a vyberte položku Zobraziť kód). Tým sa otvorí editor VB.
Na ľavej table pod zošitom, v ktorom pracujete, kliknite pravým tlačidlom myši na ktorýkoľvek z pracovných hárkov a vyberte položku Vložiť -> Modul. Tým by sa vložil nový modul. Skopírujte a prilepte kód do okna kódu modulu.
Dvakrát kliknite na názov modulu (štandardne názov modulu v Module1) a vložte kód do okna kódu.
Zatvorte editor VB.
To je všetko! Teraz máte v pracovnom hárku vlastnú funkciu s názvom GetColorCount.

Ak chcete použiť túto funkciu, jednoducho ju používajte ako bežnú funkciu programu Excel.

Syntax: = GetColorCount (CountRange, CountColor)

CountRange: rozsah, v ktorom chcete počítať bunky so zadanou farbou pozadia.
CountColor: farba, pre ktorú chcete bunky počítať.

Ak chcete použiť tento vzorec, v bunke použite rovnakú farbu pozadia (akú chcete počítať) a použite vzorec. Argument CountColor by bol rovnakou bunkou, do ktorej zadávate vzorec (ako je uvedené nižšie):