✅ Jak počítat BAREVNÉ buňky v Excelu (Podrobný průvodce + VIDEO)

Obsah

Jak počítat barevné buňky v aplikaci Excel

Podívejte se na video - Jak počítat barevné buňky v Excelu

Nebylo by skvělé, kdyby existovala funkce, která dokáže počítat barevné buňky v Excelu?

Bohužel k tomu neexistuje žádná vestavěná funkce.

ALE…

To lze snadno provést.

Jak počítat barevné buňky v aplikaci Excel

V tomto tutoriálu vám ukážu tři způsoby, jak počítat barevné buňky v Excelu (s VBA i bez):

Pomocí funkce Filtr a SUBTOTAL
Pomocí funkce GET.CELL
Pomocí vlastní funkce vytvořené pomocí VBA

#1 Počítejte barevné buňky pomocí filtru a SUBTOTAL

Chcete -li počítat barevné buňky v aplikaci Excel, musíte použít následující dva kroky:

Filtrujte barevné buňky
Pomocí funkce SUBTOTAL spočítejte barevné buňky, které jsou viditelné (po filtrování).

Předpokládejme, že máte datovou sadu, jak je uvedeno níže:

V této sadě dat jsou použity dvě barvy pozadí (zelená a oranžová).

Zde jsou kroky počítání barevných buněk v aplikaci Excel:

V jakékoli buňce pod datovou sadou použijte následující vzorec: = SUBTOTAL (102, E1: E20)
Vyberte záhlaví.
Přejděte na Data -> Třídit a filtrovat -> Filtr. Tím se použije filtr na všechna záhlaví.
Klikněte na libovolné rozevírací seznam filtrů.
Přejděte na „Filtrovat podle barvy“ a vyberte barvu. Ve výše uvedené datové sadě, protože pro zvýraznění buněk jsou použity dvě barvy, filtr zobrazí dvě barvy k filtrování těchto buněk.

Jakmile filtrujete buňky, všimnete si, že se hodnota ve funkci SUBTOTAL změní a vrátí pouze počet buněk, které jsou viditelné po filtrování.

Jak to funguje?

Funkce SUBTOTAL používá jako první argument 102, který se používá k počítání viditelných buněk (skryté řádky se nepočítají) v zadaném rozsahu.

Pokud data, pokud nejsou filtrována, vrátí 19, ale pokud jsou filtrována, pak vrátí pouze počet viditelných buněk.

Vyzkoušejte sami … Stáhněte si ukázkový soubor

#2 Počítejte barevné buňky pomocí funkce GET.CELL

GET.CELL je funkce Macro4, která byla zachována z důvodu kompatibility.

Pokud je v listu používán jako běžné funkce, nefunguje.

Funguje však v Excelu pojmenované rozsahy.

Viz také: Zjistěte více o funkci GET.CELL.

Zde jsou tři kroky k použití GET.CELL k počítání barevných buněk v aplikaci Excel:

Vytvořte pojmenovaný rozsah pomocí funkce GET.CELL
Pomocí pojmenovaného rozsahu získáte barevný kód ve sloupci
Použití čísla barvy k spočítání počtu barevných buněk (podle barvy)

Pojďme se ponořit do hloubky a uvidíme, co dělat v každém ze tří uvedených kroků.

Vytvoření pojmenovaného rozsahu

Přejděte na Vzorce -> Definovat jméno.
V dialogovém okně Nový název zadejte:
- Název: GetColor
- Rozsah: Sešit
- Odkazuje na: = GET.CELL (38, Sheet1! $ A2)
  Ve výše uvedeném vzorci jsem použil List1! $ A2 jako druhý argument. Musíte použít odkaz na sloupec, kde máte buňky s barvou pozadí.

Získání barevného kódu pro každou buňku

V buňce sousedící s daty použijte vzorec = GetColor

Tento vzorec by vrátil 0, pokud v buňce není ŽÁDNÁ barva pozadí a vrátil by konkrétní číslo, pokud existuje barva pozadí.

Toto číslo je specifické pro barvu, takže všechny buňky se stejnou barvou pozadí získají stejné číslo.

Počítejte barevné buňky pomocí barevného kódu

Pokud budete postupovat podle výše uvedeného postupu, měli byste sloupec s čísly odpovídajícími barvě pozadí.

Chcete -li získat počet konkrétní barvy:

Někde pod datovou sadou zadejte buňce, kterou chcete počítat, stejnou barvu pozadí. Ujistěte se, že to děláte ve stejném sloupci, který jste použili při vytváření pojmenovaného rozsahu. Například jsem použil sloupec A, a proto použiji buňky pouze ve sloupci „A“.
V sousední buňce použijte následující vzorec:

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

Tento vzorec vám poskytne počet všech buněk se zadanou barvou pozadí.

Jak to funguje?

Funkce COUNTIF používá jako kritéria pojmenovaný rozsah (GetColor). Pojmenovaný rozsah ve vzorci odkazuje na sousední buňku vlevo (ve sloupci A) a vrací kód barvy pro tuto buňku. Proto je kritériem toto číslo barevného kódu.

Funkce COUNTIF používá rozsah ($ F $ 2: $ F $ 18), který obsahuje čísla barevných kódů všech buněk a vrací počet na základě čísla kritéria.

Vyzkoušejte sami … Stáhněte si ukázkový soubor

#3 Počet barevných pomocí VBA (vytvořením vlastní funkce)

Ve výše uvedených dvou metodách jste se naučili počítat barevné buňky bez použití VBA.

Ale pokud jste v pohodě s používáním VBA, je to nejjednodušší ze tří metod.

Pomocí VBA bychom vytvořili vlastní funkci, která bude fungovat jako funkce COUNTIF a vrátí počet buněk s konkrétní barvou pozadí.

Zde je kód:

'Kód vytvořený společností Sumit Bansal z https://trumpexcel.com Funkce GetColorCount (CountRange As Range, CountColor As Range) Dim CountColorValue As Integer Dim TotalCount As Integer CountColorValue = CountColor.Interior.ColorIndex Set rCell = CountRange For each rCell In CountRange If rCell.Interior.ColorIndex = CountColorValue Then TotalCount = TotalCount + 1 End If Next rCell GetColorCount = TotalCount End Function

Chcete -li vytvořit tuto vlastní funkci:

Když je váš sešit aktivní, stiskněte Alt + F11 (nebo klikněte pravým tlačítkem na kartu listu a vyberte Zobrazit kód). Tím by se otevřel editor VB.
V levém podokně pod sešitem, ve kterém pracujete, klikněte pravým tlačítkem na některý z listů a vyberte Vložit -> Modul. Tím by byl vložen nový modul. Zkopírujte a vložte kód do okna kódu modulu.
Poklepejte na název modulu (ve výchozím nastavení název modulu v Module1) a vložte kód do okna kódu.
Zavřete editor VB.
A je to! Nyní máte v listu vlastní funkci s názvem GetColorCount.

Chcete -li tuto funkci použít, jednoduše ji použijte jako jakoukoli běžnou funkci aplikace Excel.

Syntax: = GetColorCount (CountRange, CountColor)

CountRange: rozsah, ve kterém chcete počítat buňky se zadanou barvou pozadí.
CountColor: barvu, pro kterou chcete buňky spočítat.

Chcete -li použít tento vzorec, použijte v buňce stejnou barvu pozadí (kterou chcete počítat) a použijte vzorec. Argument CountColor by byl stejnou buňkou, kde zadáváte vzorec (jak je uvedeno níže):