V každodenních úkolech je barevné značení oblíbenou metodou pro rychlé rozlišení a zvýraznění důležitých dat. Jak ale spočítáme nebo sečteme data buněk na základě konkrétní barvy (barva výplně, barva písma, podmíněné formátování)? Ve výchozím nastavení Excel nenabízí přímou funkci počítání nebo sčítání podle barvy. Nicméně pomocí některých triků a nepřímých metod toho stále můžeme dosáhnout. Tento článek prozkoumá, jak počítat nebo sčítat data podle barvy.

Počítání a součet buněk na základě barvy pozadí

• Pomocí funkce definované uživatelem
• Pomocí výkonné funkce – Kutools pro Excel
• Pomocí filtru a SUBTOTAL

Počítat a sčítat buňky na základě barvy písma

• Pomocí funkce definované uživatelem
• Pomocí jednoduché funkce – Kutools pro Excel

Počítat a sčítat buňky na základě barvy podmíněného formátování

• Pomocí kódu VBA
• Pomocí chytré funkce – Kutools pro Excel

Video: Počítání a sčítání buněk na základě barvy

Počítání a součet buněk na základě barvy pozadí

Například, pokud máte rozsah dat, kde jsou hodnoty vyplněny různými barvami pozadí, jak je znázorněno na obrázku níže. Chcete-li počítat nebo sčítat buňky na základě konkrétní barvy, Excel nenabízí přímou funkci počítání nebo sečtení buněk na základě jejich barvy pozadí. S trochou vynalézavosti a některými šikovnými technikami však tento úkol zvládnete. Podívejme se v této části na některé užitečné metody.

Počítání a sčítání buněk podle barvy pozadí pomocí funkce definované uživatelem

Zde vám ukážeme, jak vytvořit a používat takovou User Defined Function k vyřešení tohoto úkolu v Excelu. Proveďte prosím následující kroky:

Krok 1: Otevřete editor modulu VBA a zkopírujte kód

1. Stisknout Alt + F11 klávesy pro otevření Microsoft Visual Basic pro aplikace okno.
2. V otevřeném okně klikněte na Vložit > Modul k vytvoření nového prázdného modulu.
3. Poté zkopírujte a vložte níže uvedený kód do prázdného modulu.
   Kód VBA: Počítání a sčítání buněk na základě barvy pozadí

   Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean = False) As Variant
   'Updateby Extendoffice
       Dim rCell As Range
       Dim lCol As Long
       Dim vResult As Double
       lCol = rColor.Interior.ColorIndex
       vResult = 0
       If SUM Then
           For Each rCell In rRange
               If rCell.Interior.ColorIndex = lCol Then
                   vResult = vResult + rCell.Value
               End If
           Next rCell
       Else
           For Each rCell In rRange
               If rCell.Interior.ColorIndex = lCol Then
                   vResult = vResult + 1
               End If
           Next rCell
       End If
       ColorFunction = vResult
   End Function
   

Krok 2: Vytvořte vzorce pro počítání a sčítání buněk podle barvy pozadí

Po vložení výše uvedeného kódu zavřete okno modulu a použijte následující vzorce:

• Počítejte buňky na základě konkrétní barvy pozadí:
  Pro výsledek zkopírujte nebo zadejte níže uvedený vzorec do požadované buňky. Poté přetáhněte rukojeť výplně dolů, abyste získali další výsledky. Viz snímek obrazovky:
  

  =colorfunction(G2,$B$2:$E$12,FALSE) 

  Pozor: V tomto vzorci G2 je referenční buňka se specifickou barvou pozadí, kterou chcete sladit; $ B $ 2: $ E $ 12 je rozsah, ve kterém chcete spočítat počet buněk barvy G2; NEPRAVDIVÉ se používá k počítání buněk s odpovídající barvou.
  
• Sečíst buňky podle konkrétní barvy pozadí:
  Pro výsledek zkopírujte nebo zadejte níže uvedený vzorec do požadované buňky. Poté přetáhněte rukojeť výplně dolů, abyste získali další výsledky. Viz snímek obrazovky:
  

  =colorfunction(G2,$B$2:$E$12,TRUE)  

  Pozor: V tomto vzorci G2 je referenční buňka se specifickou barvou pozadí, kterou chcete sladit; $ B $ 2: $ E $ 12 je rozsah, ve kterém chcete spočítat počet buněk barvy G2; TRUE se používá k sečtení buněk s odpovídající barvou.
  

Počítání a sčítání buněk podle barvy pozadí pomocí výkonné funkce

Pro ty, kteří nejsou obeznámeni s programováním, se VBA může jevit jako poměrně složitý. Zde představíme mocný nástroj - Kutool pro Excel, to je Počítat podle barvy Funkce vám umožňuje snadno vypočítat (počet, součet, průměr atd.) na základě barvy pozadí pomocí několika kliknutí. Působivě, Počítat podle barvy Tato funkce přesahuje pouhé barvy pozadí – umí také rozlišovat a počítat na základě barev písem a podmíněného formátování.

Po stažení a instalace Kutools pro Excel, nejprve vyberte rozsah dat, který chcete spočítat, nebo sečíst buňky na základě konkrétní barvy pozadí. Dále přejděte na Kutools Plus A zvolte Počítat podle barvy.

v Počítat podle barvy dialogovém okně zadejte operace:

1. vybrat Standardní formátování z Barevná metoda rozbalovací seznam;
2. Specifikovat Pozadí z Typ počítání rozevíracího seznamu a v dialogovém okně můžete zobrazit náhled statistických výsledků pro každou barvu pozadí;
3. Konečně klikněte na tlačítko Generovat zprávu exportovat vypočítané výsledky do nového sešitu.

Výsledek:

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

Počítání a sčítání buněk podle barvy pozadí pomocí funkce Filtr a SUBTOTAL

Předpokládejme, že máme tabulku prodeje ovoce, jak je zobrazena níže, a spočítáme nebo sečteme barevné buňky v Množství sloupec.

Krok 1: Použijte funkci SUBTOTAL

Vyberte prázdné buňky pro vstup do funkce SUBTOTAL.

• Chcete-li spočítat všechny buňky se stejnou barvou pozadí, zadejte vzorec:

  =SUBTOTAL(102, F2:F16)

• Chcete-li sečíst všechny buňky se stejnou barvou pozadí, zadejte vzorec;

  =SUBTOTAL(109, F2:F16)

• Pozor: ve výše uvedených vzorcích, 102 představuje pro počítání číselných hodnot ve filtrovaném seznamu při vyloučení skrytých buněk; 109 představuje součet hodnot ve filtrovaném seznamu bez skrytých buněk; F2: F16 je rozsah, ve kterém se bude počítat počet nebo součet.
• 

Krok 2: Filtrujte buňky na základě konkrétní barvy

1. Vyberte záhlaví tabulky a klikněte Data > Filtr. Viz snímek obrazovky:
   
2. Klepněte na tlačítko Filtr ikona  v buňce záhlaví Množství sloupec a klikněte na Filtr podle barvy a zadanou barvu budete postupně počítat. Viz screenshot:
   

Výsledek:

Po filtrování vzorce SUBTOTAL automaticky spočítají a sečtou barevné buňky v Množství sloupec. Viz snímek obrazovky:

Počítat a sčítat buňky na základě barvy písma

Chcete počítat nebo sčítat buňky na základě jejich barvy písma v Excelu? Řekněme, že máte data, jako na daném snímku obrazovky, s buňkami obsahujícími texty v červené, modré, oranžové a černé barvě. Excel to ve výchozím nastavení neusnadňuje. Ale nebojte se! V této části vám ukážeme několik jednoduchých triků, jak toho dosáhnout.

Počítání a sčítání buněk na základě barvy písma pomocí funkce definované uživatelem

Chcete-li spočítat a sečíst buňky se specifickými barvami písma, může vám při řešení tohoto úkolu pomoci následující uživatelsky definovaná funkce. Proveďte prosím následující kroky:

Krok 1: Otevřete editor modulu VBA a zkopírujte kód

1. Stisknout Alt + F11 klávesy pro otevření Microsoft Visual Basic pro aplikace okno.
2. V otevřeném okně klikněte na Vložit > Modul k vytvoření nového prázdného modulu.
3. Poté zkopírujte a vložte níže uvedený kód do prázdného modulu.
   Kód VBA: Počítání a sčítání buněk na základě barvy písma

   Function ProcessByFontColor(pRange1 As Range, pRange2 As Range, FunctionType As String) As Double
   'Updateby Extendoffice
       Application.Volatile
       Dim rng As Range
       Dim xTotal As Double
       Dim xCount As Double
       xTotal = 0
       xCount = 0
       For Each rng In pRange1
           If rng.Font.Color = pRange2.Font.Color Then
               If UCase(FunctionType) = "SUM" Then
                   xTotal = xTotal + rng.Value
               ElseIf UCase(FunctionType) = "COUNT" Then
                   xCount = xCount + 1
               End If
           End If
       Next
       If UCase(FunctionType) = "SUM" Then
           ProcessByFontColor = xTotal
       ElseIf UCase(FunctionType) = "COUNT" Then
           ProcessByFontColor = xCount
       Else
           ProcessByFontColor = CVErr(xlErrValue)
       End If
   End Function
   

Krok 2: Vytvořte vzorce pro počítání a sčítání buněk podle barvy písma

Po vložení výše uvedeného kódu zavřete okno modulu a použijte následující vzorce:

• Počítání buněk na základě konkrétní barvy písma:
  Pro výsledek zkopírujte nebo zadejte níže uvedený vzorec do požadované buňky. Poté přetáhněte rukojeť výplně dolů, abyste získali další výsledky. Viz snímek obrazovky:
  

  =ProcessByFontColor($B$2:$E$12,G2, "COUNT")

  Pozor: V tomto vzorci G2 je referenční buňka s konkrétní barvou písma, kterou chcete sladit; $ B $ 2: $ E $ 12 je rozsah, ve kterém chcete spočítat počet buněk barvy G2.
  
• Sečíst buňky podle konkrétní barvy písma:
  Pro výsledek zkopírujte nebo zadejte níže uvedený vzorec do požadované buňky. Poté přetáhněte rukojeť výplně dolů, abyste získali další výsledky. Viz snímek obrazovky:
  

  =ProcessByFontColor($B$2:$E$12,G2, "SUM")  

  Pozor: V tomto vzorci G2 je referenční buňka s konkrétní barvou písma, kterou chcete sladit; $ B $ 2: $ E $ 12 je rozsah, ve kterém chcete spočítat počet buněk barvy G2.
  

Počítat a sčítat buňky na základě barvy písma se snadnou funkcí

Chcete snadno spočítat nebo sečíst hodnoty buněk v Excelu na základě barvy písma? Potopit se do Kutools pro Excel's Počítat podle barvy Vlastnosti! S tímto chytrým nástrojem se počítání a sčítání buněk podle konkrétní barvy písma stává hračkou. Zjistěte jak Kutools může změnit vaše zkušenosti s Excelem.

Po stažení a instalace Kutools pro Excel, nejprve vyberte rozsah dat, který chcete spočítat, nebo sečíst buňky na základě konkrétní barvy písma. Poté klikněte Kutools Plus > Počítat podle barvy k otevření Počítat podle barvy dialogové okno.

v Počítat podle barvy dialogovém okně zadejte operace:

1. vybrat Standardní formátování z Barevná metoda rozbalovací seznam;
2. Specifikovat Písmo z Typ počítání rozevírací seznam a v dialogovém okně můžete zobrazit náhled statistických výsledků pro každou barvu písma;
3. Konečně klikněte na tlačítko Generovat zprávu exportovat vypočítané výsledky do nového sešitu.

Výsledek:

Nyní máte nový sešit zobrazující podrobné statistiky založené na barvě písma. Viz snímek obrazovky:

Počítat a sčítat buňky na základě barvy podmíněného formátování

V Excelu můžete běžně používat podmíněné formátování k použití konkrétní barvy na buňky, které splňují určitá kritéria, takže vizualizace dat je intuitivní. Ale co když potřebujete spočítat nebo sečíst tyto speciálně formátované buňky? Přestože Excel nenabízí přímý způsob, jak to provést, zde jsou způsoby, jak toto omezení obejít.

Počítat a sčítat podmíněně formátované buňky pomocí kódu VBA

Počítání a sčítání podmíněně formátovaných buněk v Excelu není přímočaré pomocí vestavěných funkcí. Tento úkol však můžete provést pomocí kódu VBA. Pojďme si projít, jak k tomu můžete použít VBA:

Krok 1: Otevřete editor modulu VBA a zkopírujte kód

1. Stisknout Alt + F11 klávesy pro otevření Microsoft Visual Basic pro aplikace okno.
2. V otevřeném okně klikněte na Vložit > Modul k vytvoření nového prázdného modulu.
3. Poté zkopírujte a vložte níže uvedený kód do prázdného modulu.
   Kód VBA: Počítání a sčítání buněk na základě barvy podmíněného formátování

   Sub SumCountByConditionalFormat()
   'Updateby Extendoffice
       Dim sampleColor As Range
       Dim selectedRange As Range
       Dim cell As Range
       Dim countByColor As Long
       Dim sumByColor As Double
       Dim refColor As Long
       Set selectedRange = Application.InputBox("Select a range to evaluate:", _
                                                "Kutools for Excel", _
                                                Type:=8)
       If selectedRange Is Nothing Then Exit Sub
       Set sampleColor = Application.InputBox("Select a conditional formatting color:", _
                                              "Kutools for Excel", _
                                              Type:=8)
       If Not sampleColor Is Nothing Then
           refColor = sampleColor.Cells(1, 1).DisplayFormat.Interior.color
           For Each cell In selectedRange
               If cell.DisplayFormat.Interior.color = refColor Then
                   countByColor = countByColor + 1
                   sumByColor = sumByColor + cell.Value
               End If
           Next cell
           MsgBox "Count: " & countByColor & vbCrLf & _
                  "Sum: " & sumByColor, _
                  vbInformation, "Results based on Conditional Format Color"
       End If
   End Sub
   

Krok 2: Spusťte tento kód VBA

1. Po vložení kódu stiskněte F5 klíč ke spuštění tohoto kódu, objeví se okno s výzvou, vyberte prosím rozsah dat, kde chcete počítat a sčítat buňky na základě podmíněného formátování. Poté klikněte OK, Viz screenshot:
   
2. V dalším okně výzvy vyberte konkrétní barvu podmíněného formátování, kterou chcete spočítat a sečíst, a klepněte OK tlačítko, viz screenshot:
   

Výsledek:

Nyní se ve vyskakovacím poli zobrazí výsledek, který zahrnuje počet i součet buněk se zadanou barvou podmíněného formátování. Viz snímek obrazovky:

Počítání a sčítání podmíněně formátovaných buněk pomocí chytré funkce

Pokud hledáte další rychlé a snadné metody pro počítání a sčítání podmíněně formátovaných buněk, Kutools pro Excel je vaším oblíbeným řešením. Své Počítat podle barvy Tato funkce může vyřešit tento úkol pouze několika kliknutími. Ponořte se a objevte efektivitu a přesnost, kterou může Kutools přinést do vašeho pracovního postupu.

Po stažení a instalace Kutools pro Excel, nejprve vyberte rozsah dat, který chcete spočítat, nebo sečíst buňky na základě konkrétní barvy podmíněného formátování. Poté klikněte Kutools Plus > Počítat podle barvy k otevření Počítat podle barvy dialogové okno.

v Počítat podle barvy dialogovém okně zadejte operace:

1. vybrat Podmíněné formátování z Barevná metoda rozbalovací seznam;
2. Specifikovat Pozadí z Typ počítání rozevíracího seznamu a v dialogovém okně můžete zobrazit náhled statistických výsledků pro každou barvu formátování podmíněné;
3. Konečně klikněte na tlačítko Generovat zprávu exportovat vypočítané výsledky do nového sešitu.

Výsledek:

Nyní máte nový sešit zobrazující podrobné statistiky založené na barvě podmíněného formátování. Viz snímek obrazovky:

Související články: