Ako zhrnúť podľa farby v programe Excel? (2 užitočné metódy)
Najlepšie 2 spôsoby súčtu podľa farieb v programe Excel sú nasledujúce -
- Použitie vzorca SUBTOTAL v programe Excel a filtrovanie podľa farebnej funkcie.
- Aplikácia vzorca GET.CELL definovaním názvu na karte vzorcov a aplikáciou vzorca SUMIF v programe Excel na zosumarizovanie hodnôt podľa farebných kódov.
Poďme si každú z nich podrobne rozobrať -
# 1 - Súčet podľa farieb pomocou funkcie medzisúčtu
Aby sme pochopili prístup k výpočtu súčtu hodnôt podľa farieb pozadia, zvážme nasledujúcu tabuľku s údajmi, ktorá poskytuje podrobnosti o sumách v USD podľa regiónu a mesiaca.
- Krok 1: Predpokladajme, že by sme chceli zvýrazniť tie bunky, ktoré majú na účely indikácie záporné hodnoty, čo je možné dosiahnuť buď použitím podmieneného formátovania alebo manuálnym zvýraznením buniek, ako je uvedené nižšie.
- Krok 2: Teraz, aby ste dosiahli súčet buniek zafarbených v programe Excel, zadajte pod tabuľkou s údajmi vzorec pre SUBTOTAL. Syntax vzorca SUBTOTAL je uvedená nižšie.
Vzorec, ktorý sa zadá na výpočet súčtu, je
= Medzisúčet (9, D2: D13)
Tu číslo „9“ v argumente function_num odkazuje na súčet funkcií a referenčný argument je uvedený ako rozsah buniek, ktoré sa majú vypočítať. Nižšie je uvedený screenshot.
- Krok 3: Ako je vidieť na snímke vyššie, bola vypočítaná súčet sumy v USD, aby sa mohli vypočítať sumy zvýraznené svetločervenou farbou pozadia. Použite filter na údajovú tabuľku tak, že prejdete na položku Údaje a vyberiete filter.
- Krok 4: Potom vyberte filter podľa farby a v časti „Filtrovať podľa farby bunky“ vyberte farbu svetločervenej bunky. Nižšie je uvedený obrázok obrazovky, ktorý lepšie popisuje filter.
- Krok 5: Po použití filtra programu Excel sa údajová tabuľka vyfiltruje iba pre svetločervené bunky pozadia a medzisúčetový vzorec použitý v dolnej časti tabuľky s údajmi zobrazí súčet farebných buniek, ktoré sa filtrujú, ako je to znázornené. nižšie.
Ako je uvedené na snímke vyššie, výpočet farebnej bunky sa dosiahne v bunke E17, medzisúčet.
# 2 - Súčet podľa farieb pomocou funkcie Get.Cell
Vysvetľuje sa druhý prístup, ktorý vedie k súčtu farebných buniek v programe Excel, ako je popísané v nasledujúcom príklade. Zvážte tabuľku údajov, aby ste lepšie pochopili metodiku.
- Krok 1: Teraz poďme zvýrazniť zoznam buniek v stĺpci USD Amount, kde sme ochotní prísť k požadovanému súčtu farebných buniek, ako je uvedené nižšie.
- Krok 2: Ako vidíme na snímke vyššie, na rozdiel od prvého príkladu tu máme viac farieb. Preto budeme používať vzorec = GET.CELL tak, že ho definujeme v poli pre meno a nepoužívame ho priamo v programe Excel.
- Krok 3: Teraz, keď sa objaví dialógové okno pre „Definovať meno“, zadajte názov a vzorec na zabudnutie. CELL do „Odkazovať“, ako je znázornené na nasledujúcom obrázku.
Ako je vidieť na snímke vyššie, pre funkciu je zadaný názov „CellColor“ a do poľa „sa odkazuje“ treba zadať vzorec = GET.CELL (38, „Príklad 2! $ D2). Vo vzorci číselná značka 38 odkazuje na informácie o kóde bunky a druhým argumentom je číslo bunky D2 na referenčnú bunku. Teraz kliknite, OK.
- Krok 4: Teraz zadajte funkciu Názov „CellColor“ do bunky vedľa farebne odlíšenej bunky, ktorá bola definovaná v dialógovom okne, ako je vysvetlené v kroku 3.
Ako je vidieť na snímke vyššie, zadáva sa funkcia „CellColor“ , ktorá vracia farebný kód pre farbu bunky pozadia.
Podobne je vzorec presunutý pre celý stĺpec.
- Krok 5: Teraz, aby sme sa dostali k súčtu hodnôt podľa farieb v programe Excel, zadáme vzorec SUMIF. Syntax vzorca SUMIF je nasledovná: -
Ako je zrejmé z vyššie uvedeného screenshotu, do vzorca SUMIF sú zadané nasledujúce argumenty: -
- Argument rozsah je zadaný pre rozsah buniek E2: E13.
- Kritériá sa zadávajú ako G2, ktorých súhrnné hodnoty je potrebné načítať.
- Rozsah buniek je zadaný ako D2: D13.
Vzorec SUMIF sa potiahne nadol pre všetky čísla farebných kódov, pre ktoré sa majú hodnoty sčítať.
Na čo treba pamätať
- Pri použití prístupu podľa SUBTOTAL vzorca táto funkcia umožňuje používateľom filtrovať naraz iba jednu farbu filtra. Túto funkciu je možné navyše použiť na pridanie iba jedného stĺpca hodnôt podľa farieb filtra. Ak je v rôznych stĺpcoch viac ako jeden stĺpec s rôznymi farbami podľa riadkov, SUBTOTAL môže zobraziť správny výsledok pre jeden filter podľa farieb iba pre konkrétny stĺpec.
- Vzorec GET.CELL v kombinácii s prístupom SUMIF eliminuje toto obmedzenie, pretože túto funkciu je možné použiť na sumarizáciu farieb pre viac farebných kódov na pozadí bunky.
