Cookies nám pomáhajú poskytovať naše služby. Využitím našich služieb, súhlasíte s našimi používanie cookies.
Tip: Iné jazyky sú Google preložené. Môžete navštíviť English verziu tohto odkazu.
Prihlásiť sa
x
or
x
x
Registrovať
x

or

Ako počítať a sumovať bunky na základe farby pozadia v programe Excel?

Predpokladajme, že máte celú škálu buniek s rôznymi farbami pozadia, ako je červená, zelená, modrá a tak ďalej, ale teraz je potrebné počítať, koľko buniek v tomto rozmedzí má určitú farbu pozadia a súčet farebných buniek s rovnakou istou farbou , V programe Excel nie je žiadny priamy vzorec na výpočet sumy a počtu farebných buniek, tu vám predstavím niekoľko spôsobov, ako vyriešiť tento problém.

Počet a sumy buniek na základe špecifickej farby výplne podľa filtra a SUBTOTAL

Počet a sumy buniek na základe špecifickej farby výplne pomocou funkcie GET.CELL

Počet a sumy buniek na základe špecifickej farby výplne s funkciou definovanou používateľom

Počet a sumy buniek na základe špecifickej farby výplne (alebo podmienenej farby formátovania) pomocou programu Kutools for Excel

Jedným kliknutím môžete počítať, súčet a priemerné farebné bunky v programe Excel

S výborným Počet podľa farieb funkcia Kutools pre Excel, môžete rýchlo počítať, súčet a priemerné bunky podľa zadanej farby výplne alebo farby písma s jediným kliknutím v programe Excel. Okrem toho táto funkcia tiež zistí hodnoty max a min buniek podľa farby výplne alebo farby písma. Kompletná skúšobná verzia zdarma 60-deň!
počet reklám podľa farby 2


Počet a sumu farebných buniek podľa filtra a SUBTOTAL

Predpokladáme, že máme tabuľku predaja ovocia, ako je to znázornené nižšie, a do kolónky Suma budeme počítať alebo sumarizovať farebné bunky. V tejto situácii môžeme filtrovať stĺpec Množstvo podľa farby a následne počítat 'alebo sumarizovať farebné bunky pomocou funkcie SUBTOTAL v aplikácii Excel.

1, Ak chcete zadať funkciu SUBTOTAL, vyberte prázdne bunky.

  1. Ak chcete počítať všetky bunky s rovnakou farbou pozadia, zadajte vzor = SUBTOTAL (102, E2: E20);
  2. Ak chcete zhrnúť všetky bunky s rovnakou farbou pozadia, zadajte vzor = SUBTOTAL (109, E2: E20);


Poznámky: V oboch vzorcoch je E2: E20 stĺpec Množstvo obsahujúci farebné bunky a môžete ich zmeniť podľa potreby.

2, Vyberte záhlavie tabuľky a kliknite na tlačidlo dátum > filter, Pozri snímku obrazovky:

3, Kliknite na ikonu Filter v hlavičke bunky stĺpca Suma a kliknite na tlačidlo Filter podľa farby a zadanú farbu, ktorú budete postupne počítať. Pozrite si snímku obrazovky:

Po filtrovaní sa v stĺpci Suma automaticky započítavajú oba vzorce SUBTOTAL a súčet všetkých filtrovaných farebných buniek. Pozrite si snímku obrazovky:

Poznámky: Táto metóda vyžaduje, aby farebné bunky, ktoré sa budú počítať alebo súčet, nachádzajú v rovnakom stĺpci.


Počet alebo celkom farebné bunky pomocou funkcie GET.CELL

V tejto metóde vytvoríme pomenovaný rozsah s funkciou GET.CELL, dostaneme farebný kód buniek a počítame alebo sumujeme podľa kódu farby v programe Excel. Postupujte nasledovne:

1, kliknite vzorca > Definujte meno, Pozri snímku obrazovky:

2, V dialógovom okne Nový názov postupujte takto:
(1) Zadajte názov do poľa Názov;
(2) Zadajte vzorec = GET.CELL (38, Sheet4! $ E2) v poli Odkazy na (na vedomie: vo vzorci, 38 znamená vrátiť kód bunky a Sheet4! $ E2 je prvou bunkou v stĺpci Výška okrem hlavičky stĺpca, ktorú je potrebné zmeniť na základe údajov tabuľky.)
(3) Kliknite na OK tlačidlo.

3, Teraz pridajte nový stĺpec Farba priamo do pôvodnej tabuľky. Ďalej zadajte vzorec = NumColor a presunutím popisu automatického dopĺňania použijete vzorec na iné bunky v stĺpci Farba. Pozrite si snímku obrazovky:
Poznámky: Vo vzorci, NumColor je pomenovaný rozsah, ktorý sme zadali v prvých krokoch 2. Musíte ju zmeniť na zadané meno, ktoré ste nastavili.

Teraz sa farebný kód každej bunky v stĺpci Suma vráti do stĺpca farieb. Pozrite si snímku obrazovky:

4, Skopírujte a uveďte farbu výplne do prázdneho rozsahu v aktívnom pracovnom hárku a zadajte vzorce vedľa nej tak, ako je uvedené nižšie:
A. Ak chcete počítať bunky podľa farby, zadajte vzorec = COUNTIF ($ F $ 2: $ F $ 20, NumColor);
B. Ak chcete sumy buniek podľa farby, zadajte vzorec = SUMIF ($ F $ 2: $ F $ 20, NumColor, $ E $ 2: $ E $ 20).

Poznámky: V oboch vzorcoch, $ F $ 2: $ F $ 20 je stĺpec Farba, NumColor je uvedený pomenovaný rozsah, $ E $ 2: $ E $ 20 je stĺpec Suma a môžete ich zmeniť podľa potreby.

Teraz uvidíte, že bunky v stĺpci Množstvo sa započítavajú a sumujú sa podľa ich plniacich farieb.


Počet a sumy buniek na základe špecifickej farby výplne s funkciou definovanou používateľom


Ak predpokladáme, že farebné bunky sa rozptyľujú v rozmedzí, ako je uvedené nižšie, obidve vyššie uvedené metódy nemôžu počítať alebo sumarizovať farebné bunky. Tu táto metóda zavedie riešenie problémov VBA.

1, Podržte stlačené tlačidlo ALT + F11 a otvorí Microsoft Visual Basic pre aplikácie okno.

2, kliknite Vložiť > Modulya vložte nasledujúci kód do okna Modul.

VBA: Počet a suma buniek na základe farby pozadia:

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, Potom uložte kód a použite nasledujúci vzorec:
A. Počet farebných buniek: = Colorfunction (A, B: C, FALSE)
B. Zhrňte farebné bunky: = Colorfunction (A, B: C, TRUE)

Poznámka: Vo vyššie uvedených vzorcoch, A je bunka s konkrétnou farbou pozadia, ktorú chcete vypočítať počet a súčet a B: C je rozsah buniek, kde chcete vypočítať počet a súčet.

4, Nasledujte napríklad obrázok obrazovky a zadajte vzorec= Colorfunction (A1, A1: D11, FALSE) na počítanie žltých buniek. A použite vzorec = Colorfunction (A1, A1: D11, TRUE) na súčet žltých buniek. Pozri snímku obrazovky:

5, Ak chcete počítať a sumovať ďalšie farebné bunky, zopakujte krok 4. Potom získate nasledujúce výsledky:


Počet a sumy buniek na základe špecifickej farby výplne s programom Kutools for Excel

S vyššie uvedenou funkciou definovanou používateľom musíte zadať vzorec jeden po druhom, ak je veľa rôznych farieb, táto metóda bude zdĺhavá a časovo náročná. Ale ak máte Kutools pre Excel'S Počet podľa farieb môžete rýchlo vygenerovať správu o farebných bunkách. Môžete nielen počítať a sumarizovať farebné bunky, ale tiež môžete získať priemerné, max a min hodnoty farebného rozsahu.

1, Vyberte rozsah, ktorý chcete použiť, a kliknite na tlačidlo Kutools Plus > Počet podľa farieb, pozri snímku obrazovky:

2, A v Počet podľa farieb dialógové okno, postupujte takto:
(1) Vyberte položku Štandardné formátovanie z Farebná metóda rozbaľovací zoznam;
(2) Vyberte položku pozadia z Typ počtu rozbaľovací zoznam.
(3) Kliknite na tlačidlo Vytvoriť prehľad.

Kutools pre Excel - Zahŕňa viac ako 300 užitočné nástroje Excel. Kompletná bezplatná skúšobná verzia 60-deň, bez kreditnej karty! Získať teraz!

Poznámky: Ak chcete počítať a sumovať farebné bunky podľa konkrétnej podmienenej farby formátovania, vyberte, prosím Podmienené formátovanie z Farebná metóda v rozbaľovacom zozname alebo vyberte položku Štandardné a podmienené formátovanie z rozbaľovacieho zoznamu započítajte všetky bunky vyplnené zadanou farbou.

Teraz dostanete nový zošit so štatistikami. Pozrite si snímku obrazovky:


Súvisiaci článok:

Ako počítať / sumovať bunky na základe farieb písma v programe Excel?


Demo: Počet a súčet buniek na pozadí, podmienené formátovanie farieb:

V tomto videu, Kutools karta a Kutools Plus sú pridané Kutools pre Excel, V prípade potreby kliknite na tu mať bezplatnú skúšobnú verziu 60-dní bez obmedzenia!



Odporúčané nástroje produktivity

Karta Office

zlatá hviezdička1 Prinášajte užitočné karty do programu Excel a iného softvéru balíka Office, rovnako ako Chrome, Firefox a nový Internet Explorer.

Kutools pre Excel

zlatá hviezdička1 Úžasný! Zvýšte svoju produktivitu za minúty 5. Nepotrebujú žiadne špeciálne schopnosti, okrem dvoch hodín denne!

zlatá hviezdička1 300 Nové funkcie pre program Excel, robiť Excel veľmi ľahké a výkonné:

  • Zlúčiť bunky / riadky / stĺpce bez straty dát.
  • Kombinácia a konsolidácia viacerých hárkov a pracovných kníh.
  • Porovnanie rozsahov, kopírovanie viacerých rozsahov, prevod textu na dátum, konverzia jednotiek a mien.
  • Počet podľa farieb, Medzisúčty stránkovania, Rozšírené triedenie a Super filter,
  • Viac Vyberte / Vložiť / Odstrániť / Text / Formát / Odkaz / Komentár / Pracovné zošity / pracovné listy Nástroje ...

Obraz obrazovky Kutools pre 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 · 3 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 · 1 months 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 · 14 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 · 1 months 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.