Tip: Jiné jazyky jsou přeloženy společností Google. Můžete navštívit English verze tohoto odkazu.
Přihlásit se
x
or
x
x
Registrovat
x

or

Jak počítat a součet buněk na základě barvy pozadí v aplikaci Excel?

Předpokládáme, že máte řadu buněk s různými barvami pozadí, jako je červená, zelená, modrá a tak dále, ale nyní musíte počítat, kolik buněk v tomto rozsahu má určitou barvu pozadí a sumarizovat barevné buňky se stejnou jistou barvou . V aplikaci Excel neexistuje přímý vzorec pro výpočet sumy a počtu barevných buněk, zde vám představím některé způsoby řešení tohoto problému.

Počet a součet buněk založených na konkrétní barvě výplně podle filtrů a SUBTOTAL

Počet a součet buněk na základě konkrétní barvy výplně pomocí funkce GET.CELL

Počet a suma buněk na základě konkrétní barvy výplně s funkcí definovanou uživatelem

Počítat a sumovat buňky na základě konkrétní barvy výplně (nebo podmíněné formátování barvy) s Kutools pro Excel

Jedno kliknutí počítat, součet a průměrné barevné buňky v aplikaci Excel

S vynikajícími Počet podle barvy vlastnost Kutools pro Excel, můžete rychle počítat, součet a průměrné buňky podle zadané barvy výplně nebo barvy písma pouze jedním kliknutím v aplikaci Excel. Kromě toho tato funkce zjistí také maximální a minimální hodnoty buněk barvou výplně nebo barvou písma. Full Feature zdarma zkušební 60-den!
počet inzerátů podle barvy 2


Počet a součet barevných buněk pomocí filtru a SUBTOTAL

Předpokládáme, že máme tabulku prodeje ovoce, jak je ukázáno níže, a do kolonky Částka počítáme nebo sčítajeme barevné články. V této situaci můžeme filtrovat sloupec Množství podle barvy a poté v programu Excel snadno a rychle vyčíslit nebo souhrnně filtrované barevné buňky pomocí funkce SUBTOTAL.

1. Chcete-li vstoupit do funkce SUBTOTAL, vyberte prázdné buňky.

  1. Chcete-li počítat všechny buňky se stejnou barvou pozadí, zadejte prosím vzorec = SUBTOTAL (102, E2: E20);
  2. Chcete-li shrnout všechny buňky se stejnou barvou pozadí, zadejte prosím vzorec = SUBTOTAL (109, E2: E20);


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

2. Vyberte záhlaví tabulky a klepněte na tlačítko Data > Filtr. Zobrazit screenshot:

3. Klikněte na ikonu Filtr v buňce záhlaví sloupce Částka a klepněte na tlačítko Filtr podle barvy a zadanou barvu počítáte postupně. Zobrazit screenshot:

Po filtrování se budou ve sloupci Část automaticky zaúčtovat obě SUBTOTAL vzorce a počítat všechny filtrované barevné buňky. Zobrazit screenshot:

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


Počet nebo součet barevných buněk pomocí funkce GET.CELL

V této metodě vytvoříme pojmenovaný rozsah s funkcí GET.CELL, získáme barevný kód buněk a počítáme nebo sumujeme kódem barev snadno v aplikaci Excel. Postupujte následujícím způsobem:

1, klikněte Vzorce > Definujte jméno. Zobrazit screenshot:

2. V dialogu Nové jméno proveďte následující postup:
(1) Zadejte název do pole 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 Sheet4! $ E2 je první buňka ve sloupci Částka kromě záhlaví sloupce, kterou je třeba změnit na základě údajů tabulky.)
(3) Klepněte na tlačítko OK tlačítko.

3. Nyní přidejte nový sloupec Barva přímo k původní tabulce. Dále napište vzorec = NumColor , a přetáhněte popisovač automatického naplnění použít vzorec na jiné buňky ve sloupci Barva. Zobrazit screenshot:
Poznámka: Ve vzorci, NumColor je pojmenovaný rozsah, který jsme zadali v prvních krocích 2. Musíte jej změnit na zadané jméno, které jste nastavili.

Nyní se barevný kód každé buňky ve sloupci Částka vrátí do sloupce Barvy. Zobrazit screenshot:

4. Zkopírujte a zadejte barvu výplně do prázdného rozsahu v aktivním listu a zadejte následující vzorce jako níže uvedený snímek obrazovky:
A. Chcete-li počítat buňky podle barvy, zadejte vzorec = COUNTIF ($ F $ 2: $ F $ 20, NumColor);
B. Chcete-li součet buněk podle barvy, zadejte prosím 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 Částka a můžete je měnit podle potřeby.

Nyní uvidíte, že buňky ve sloupci Částka jsou počítány a součtem jejich barvy výplně.


Počet a suma buněk na základě konkrétní barvy výplně s funkcí definovanou uživatelem


Předpokládáme-li, že barevné buňky budou rozptylovat v rozmezí, jak je znázorněno na obrázku níže, oba výše uvedené metody nemohou počítat ani sumarizovat barevné buňky. Zde tato metoda zavede VBA k vyřešení problému.

1. Podržte stisknuté tlačítko ALT + F11 klávesy 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 Modul.

VBA: Počet a suma 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. Pak kód uložte a použijte následující vzorec:
A. Počítání barevných buněk: = barevné funkce (A, B: C, FALSE)
B. Souhrn barevných buněk: = barevné funkce (A, B: C, TRUE)

Poznámka: Ve výše uvedených vzorcích, A je buňka s konkrétní barvou pozadí, 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 například následující snímek obrazovky a zadejte vzorec= barevné funkce (A1, A1: D11, FALSE) počítat žluté buňky. A použijte vzorec = barevné funkce (A1, A1: D11, TRUE) k součtu žlutých buněk. Zobrazit screenshot:

5. Pokud chcete počítat a sčítat další barevné buňky, zopakujte prosím krok 4. Pak získáte následující výsledky:


Počet a součet buněk založených na konkrétní barvě výplně pomocí programu Kutools for Excel

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

1. Vyberte rozsah, který chcete použít, a klepněte na tlačítko Kutools Plus > Počet podle barvy, viz screenshot:

2. A v Počet podle barvy dialogové okno, proveďte následující postup:
(1) Vyberte položku Standardní formátování z Barevná metoda rozbalovací seznam;
(2) Vyberte položku Pozadí z Typ počtu rozbalovací seznam.
(3) Klepněte na tlačítko Generovat sestavu.

Kutools pro Excel - Zahrnuje více nástrojů Excel Excelentní než 300. Kompletní bezplatná zkušební verze 60-den, bez kreditní karty! Získat nyní!

Poznámka: Chcete-li počítat a součet barevných buněk podle konkrétní podmíněné formátovací barvy, vyberte prosím Podmíněné formátování z Barevná metoda rozbalovacího seznamu v dialogovém okně výše nebo vyberte položku Standardní a podmíněné formátování ze seznamu rozbalte všechny buňky vyplněné zadanou barvou.

Nyní dostanete nový sešit se statistikami. Zobrazit screenshot:


Související článek:

Jak počítat / součet buněk na základě barev v aplikaci Excel?


Demo: Počet a součet buněk na pozadí, podmíněná formátovací barva:

V tomto videozáznamu Kutools a tabulka Kutools Plus jsou přidány Kutools pro Excel. V případě potřeby klikněte na zde mít bezplatnou zkušební verzi 60 bez omezení!



Doporučené nástroje pro produktivitu

Karta Office

zlatá hvězda1 Přiveďte praktické karty do aplikace Excel a jiného softwaru Office, stejně jako Chrome, Firefox a nový Internet Explorer.

Kutools pro Excel

zlatá hvězda1 Úžasný! Zvyšte svou produktivitu v minutách 5. Nepotřebujete žádné speciální dovednosti, kromě dvou hodin denně!

zlatá hvězda1 300 Nové funkce pro aplikaci Excel, Značka Excel je velmi snadná a výkonná:

  • Sloučit buňky / řádky / sloupce bez ztráty dat.
  • Kombinovat a konsolidovat více listů a sešitů.
  • Srovnání rozsahů, kopírování více rozsahů, převod textu na datum, konverzi jednotek a měn.
  • Počet podle barvy, dílčí součty stránkování, třídění podle rozšířeného třídění a super filtru,
  • Více Vyberte / Vložit / Odstranit / Text / Formát / Odkaz / Komentář / Workbooks / Worksheets Nástroje ...

Snímka obrazovky Kutools pro Excel

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Mori · 1 days ago
    I copied and paste but calculation result is "0" why?? I am using Office 2016.
  • To post as a guest, your comment is unpublished.
    Troy Havard · 29 days ago
    I've copied and pasted as stated and have used this formula for over a year but recently saved the workbook to a new name and now the function doesn't work! I can't figure out what the deal is and I'm losing hair and sleep over it! Loading the original workbook, the formula works like a charm but going back to the new one, it doesn't! I've loaded VBA and tried re-creating the function but it doesn't work. Using Office 2019 - any help appreciated.
    • To post as a guest, your comment is unpublished.
      Jku · 12 days ago
      #NAME clearly indicates some keyword is not used in 2019, say, Interior.ColorIndex. Try to figure out changing the code from minimum lines to the full by adding one by one or search for the keywords in Excel/VBA in 2019
    • To post as a guest, your comment is unpublished.
      Troy Havard · 28 days ago
      I should probably have also noted that I get a #NAME? error in the cell in which I try to use the function.
  • To post as a guest, your comment is unpublished.
    Karolin · 2 months ago
    works fine, except it does not update the value in the totals when i change a cell to a diffrent colour. i have to click in the totals cell again to trigger a recount. Any Ideas how to fix this?
    • To post as a guest, your comment is unpublished.
      Arnier Hamz · 1 months ago
      Same here, i tried Ctrl+Alt+F9 or Ctrl+Shift+Alt+F9 to recalculate all formula and it works
  • To post as a guest, your comment is unpublished.
    Aaron · 3 months ago
    This doesn't work at all. It's total BS.
  • To post as a guest, your comment is unpublished.
    Schmingy · 5 months ago
    I was experiencing issues when attempting to use this function as-is to count/sum the lighter (pastel?) colors, at least in Excel 2016. Some debugging led me to realize that the range of "ColorIndex" values is quite limited, and the function above fails to distinguish between light yellow and light orange, reading both as ColorIndex = 19.

    Using the "Color" attribute, instead (as in "lCol = rColor.Interior.Color" and the two instances of "If rCell.Interior.Color = lCol Then", per the sample code above), resolved the issue.