Předpokládejme, že máme zobrazenou tabulku prodeje ovoce, jak je uvedeno níže, a budeme počítat nebo sčítat barevné buňky ve sloupci Množství. V této situaci můžeme filtrovat sloupec Množství podle barvy a potom snadno počítat nebo sčítat filtrované barevné buňky funkcí SUBTOTAL v aplikaci Excel.

1. Vyberte prázdné buňky a zadejte funkci SUBTOTAL.

1. Chcete-li spočítat všechny buňky se stejnou barvou pozadí, zadejte vzorec = MEZISOUČET (102, E2: E20);
2. Chcete-li sečíst všechny buňky se stejnou barvou pozadí, zadejte vzorec = MEZISOUČET (109, E2: E20);

Poznámka: V obou vzorcích je E2: E20 sloupec Množství obsahující barevné buňky a můžete je podle potřeby změnit.

2. Vyberte záhlaví tabulky a klikněte na Data > Filtr. Viz snímek obrazovky:

3. Klikněte na ikonu Filtr  v buňce záhlaví sloupce Částka a klikněte na Filtr podle barvy a zadanou barvu budete postupně počítat. Viz screenshot:

Po filtrování oba SUBTOTAL vzorce automaticky spočítají a sčítají všechny filtrované barevné buňky ve sloupci Množství. Viz snímek obrazovky:

Poznámka: Tato metoda vyžaduje barevné buňky, které budete počítat nebo sčítat, ve stejném sloupci.

Jedno kliknutí spočítá, sčítá a průměruje barevné buňky v aplikaci Excel

S vynikajícími Počítat podle barvy vlastnost Kutools pro Excel, můžete rychle počítat, sčítat a průměrovat buňky podle zadané barvy výplně nebo barvy písma jediným kliknutím v aplikaci Excel. Kromě toho tato funkce také zjistí maximální a minimální hodnoty buněk podle barvy výplně nebo barvy písma.

V této metodě vytvoříme pojmenovanou oblast pomocí funkce GET.CELL, získáme barevný kód buněk a poté snadno spočítáme nebo sčítáme barevným kódem v aplikaci Excel. Postupujte prosím následovně:

1, klikněte Vzorce > Definujte jméno. Viz snímek obrazovky:

2. V dialogovém okně Nový název proveďte níže uvedený snímek obrazovky:
(1) Do pole Název zadejte název;
(2) Zadejte vzorec = GET.CELL (38, Sheet4! $ E2) v poli Odkazuje na (na vědomí: ve vzorci, 38 znamená vrátit kód buňky a List4! $ E2 je první buňka ve sloupci Částka kromě záhlaví sloupce, které musíte změnit na základě údajů z vaší tabulky.)
(3) Klikněte na OK .

3. Nyní přidejte nový sloupec Barva přímo do původní tabulky. Dále zadejte vzorec = NumColor a přetažením úchytu automatického vyplňování použijete vzorec na další buňky ve sloupci Barva. Viz screenshot:
Poznámka: Ve vzorci NumColor je pojmenovaný rozsah, který jsme zadali v prvních 2 krocích. Musíte jej změnit na zadaný název, který jste nastavili.

Nyní se barevný kód každé buňky ve sloupci Množství vrátí ve sloupci Barva. Viz snímek obrazovky:

4. Zkopírujte a vypsejte barvu výplně v prázdném rozsahu v aktivním listu a zadejte vzorce vedle ní, jak je uvedeno níže:
A. Chcete-li počítat buňky podle barvy, zadejte vzorec = COUNTIF ($ F $ 2: $ F $ 20, NumColor);
B. Chcete-li buňky seřadit podle barvy, zadejte vzorec = SUMIF ($ F $ 2: $ F $ 20, NumColor, $ E $ 2: $ E $ 20).

Poznámka: V obou vzorcích $ F $ 2: $ F $ 20 je sloupec Barva, NumColor je zadaný pojmenovaný rozsah, $ E $ 2: $ E $ 20 je sloupec částky a můžete je podle potřeby měnit.

Nyní uvidíte, že buňky ve sloupci Množství jsou spočítány a součtem podle barev výplně.

Předpokládejme, že se barevné buňky rozptýlí v rozsahu, jak je uvedeno níže, obě výše uvedené metody nemohou spočítat nebo sčítat barevné buňky. Zde tato metoda zavede VBA k vyřešení problému.

1. Podržte ALT + F11 klíče a otevře se Microsoft Visual Basic pro aplikace okno.

2, klikněte Vložit > Modula vložte následující kód do okna modulu.

VBA: Počet a součet buněk na základě barvy pozadí:

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. Poté uložte kód a použijte následující vzorec:
A. Spočítat barevné buňky: = colorfunction (A, B: C, FALSE)
B. Součet barevných buněk: = colorfunction (A, B: C, TRUE)

Poznámka: Ve výše uvedených vzorcích A je buňka s konkrétní barvou pozadí, pro kterou chcete vypočítat počet a součet, a PŘED NAŠÍM LETOPOČTEM je rozsah buněk, kde chcete vypočítat počet a součet.

4. Vezměte si například následující snímek obrazovky, zadejte vzorec= colorfunction (A1, A1: D11, FALSE) spočítat žluté buňky. A použijte vzorec = colorfunction (A1, A1: D11, TRUE) sečíst žluté buňky. Viz screenshot:

5. Chcete-li spočítat a sečíst další barevné buňky, opakujte krok 4. Potom získáte následující výsledky:

Kutools pro Excel také podporuje některé užitečné funkce, které pomáhají uživatelům aplikace Excel provádět speciální výpočty, říká počet podle barvy pozadí buňky, součet podle barvy písma atd.

1. Vyberte prázdnou buňku, do které umístíte výsledky počítání, a klikněte Kutools > Funkce Kutools > Statistické a matematické > COUNTBYCELLCOLOR. Viz snímek obrazovky:

2. V dialogu Funkční argumenty zadejte rozsah, ve kterém budete počítat barevné buňky v Odkaz V poli vyberte buňku, která je vyplněna zadanou barvou pozadí v poli Color_index_nr a klepněte na tlačítko OK knoflík. Viz screenshot:

Poznámky:
(1) Můžete také zadat zadanou funkci Kutools = COUNTBYCELLCOLOR ($ A $ 1: $ E $ 20, G2)  přímo do prázdné buňky nebo řádku vzorců pro získání výsledků počítání;
(2) Klepněte na tlačítko Kutools > Funkce Kutools > Statistické a matematické > SUMBYCELLCOLOR nebo typ = SUMBYCELLCOLOR ($ A $ 1: $ E $ 20, G2) v prázdné buňce přímo k součtu buněk na základě zadané barvy pozadí.
Použít COUNTBYCELLCOLOR a SUMBYCELLCOLOR funkce pro každou barvu pozadí samostatně a výsledky získáte, jak je uvedeno níže:

Funkce Kutools obsahují řadu vestavěných funkcí, které pomáhají uživatelům aplikace Excel snadno vypočítat, včetně Počet / součet / průměr viditelných buněk, Počet / součet podle barvy buňky, Počet / součet podle barvy písma, Počet znaků, Počítat tučným písmem, Etc. Vyzkoušejte zdarma!

S výše uvedenou funkcí definovanou uživatelem musíte zadat vzorec jeden po druhém, pokud existuje spousta různých barev, bude tato metoda zdlouhavá a časově náročná. Ale pokud ano Kutools pro ExcelJe Počítat podle barvy nástroj, můžete rychle vygenerovat zprávu o barevných buňkách. Můžete nejen počítat a sečíst barevné buňky, ale také můžete získat průměrné, maximální a minimální hodnoty barevného rozsahu.

1. Vyberte rozsah, který chcete použít, a klikněte Kutools Plus > Počítat podle barvy, viz screenshot:

2. A v Počítat podle barvy v dialogovém okně proveďte níže uvedený snímek obrazovky:
(1) Vyberte položku Standardní formátování z Barevná metoda rozbalovací seznam;
(2) Vyberte položku Pozadí z Typ počítání rozbalovací seznam.
(3) Klikněte na tlačítko Vytvořit zprávu.

Poznámka: Chcete-li počítat a sečíst barevné buňky podle konkrétní barvy podmíněného formátování, vyberte prosím Podmíněné formátování z Barevná metoda rozevírací seznam ve výše uvedeném dialogovém okně nebo vyberte Standardní a podmíněné formátování z rozevíracího seznamu spočítat všechny buňky vyplněné zadanou barvou.

Nyní získáte nový sešit se statistikami. Viz screenshot:

Projekt Počítat podle barvy funkce počítá (počet, součet, průměr, maximum atd.) buněk podle barvy pozadí nebo barvy písma. Vyzkoušejte zdarma!

Jak počítat / sčítat buňky na základě barev písma v aplikaci Excel?