V dnešním videu si ukážeme velmi užitečnou a jednoduchou techniku, a to je, jak sčítat nebo počítat buňky na základě jejich barvy. Neboli jak spočítat nebo počítat buňky na základě jejich barevné výplně. Ti, co pracují v Excelu pravidelně se s podobnou situací setkali nebo setkávají denně. Řekněme, že máme v Excelu tabulku, kde máme barevně označené buňky, a chceme spočítat jejich počet a hodnoty, které obsahují. Jak asi mnozí z nás zjistili, tak v Excelu není zrovna jednoduchý a přímočarý způsob, jak počítat nebo sčítat hodnoty na základě barvy buňky. Způsob, i když skrytý, ale existuje a překvapivě není nijak složitý. Jen je velmi, velmi skrytý.
Nezapomeňte se podívat na navazující bonusové video pro členy Akademie – Tajné funkce O.POLÍČKU a POLÍČKO
Excelový soubor ke stažení
Jak sečíst a spočítat barevné buňky v Excelu
V tabulce máme informace o klientech a vystavených fakturách. Při kontrole zaplacených plateb jsme si my nebo někdo jiný barevně rozlišili řádky. Každá barva něco znamená, červená barva je například pro faktury, které jsou po splatnosti a zelená barva je pro faktury, které jsou stále ve splatnosti. Naším úkolem je spočítat, kolik faktur a v jaké celkové částce je po splatnosti a kolik je stále ve splatnosti.
Součet a počet barevných buněk filtrováním a funkcí SUBTOTAL
Nejjednodušší způsob, jak podobnou situaci s obarvenými buňkami vyřešit, je pomocí filtru nad tabulkou a funkce SUBTOTAL. Tento způsob s filtrováním je sice jednoduchý, ale vyžaduje spoustu manuální práce a ještě u něho hrozí, že uděláte chybu. Nejprve bychom nad sloupec s hodnotami vložili funkci SUBTOTAL, kde bychom jako funkci použili sumu, tedy číslo devět a označili celý sloupec hodnot B5:B12. Následně do tabulky s fakturami vložíme jednoduchý filtr, a to pomocí klávesové kombinace CTRL+SHIFT a písmeno L. Následně klikneme do záhlaví ve sloupci hodnota a vybereme možnost Filtrovat podle barvy. Vybereme příslušnou barvu, potvrdíme a tabulka se vyfiltruje. Funkce SUBTOTAL nám nad tabulkou sečetla obarvené hodnoty a tuto hodnotu bychom si následně zapsali tam, kam potřebujeme.
Jak sečíst a spočítat barevné buňky s funkcí O.POLÍČKU
K řešení příkladu nám pomůže funkce, o které mnoho uživatelů Excelu neví, že existuje. Jedná se o funkci O.POLÍČKU, v anglické verzi Excelu je to funkce GET.CELL. Důvodem, proč o této funkci skoro nikdo neví je to, že se jedná o velmi starou makro funkci, která se neobjeví v nabídce funkcí. Když klikneme do jakékoliv buňky, napíšeme rovná se a budeme se snažit vložit funkci O.POLÍČKU, tak se žádná funkce neobjeví. Tím vzniká dojem, že tato funkce již dávno v Excelu nefunguje. Jedná se o podobně skrytou funkci jako je funkce DATEDIF, o které máme na webu Akademie Excelu video pro členy.
To ale není vše. I kdybyste o funkci O.POLÍČKU věděli, tak to nestačí. Nejen, že pro výpočet musíme použít funkci, o jejíž existenci skoro nikdo neví, ale ještě ji nemůžeme použít přímo v excelovém sešitu, ale musíme k tomu použít správce názvů. Zdá se vám to komplikované? Vůbec ne, celý výpočet bude hotový za pár sekund.
Pomocný sloupec v tabulce
Nejprve v tabulce, kde máme obarvené hodnoty vytvoříme nový pomocný sloupec, který nazveme třeba Barva. Klikneme do první buňky a na kartě Vzorce vybereme Správce názvů. Zde vybereme Nový název. Jako název napíšeme například BarvaPole. V poli Odkaz na použijeme funkci O.POLÍČKU. Otevřeme závorku a jako první parametr napíšeme číslo 38, jelikož to je parametr pro určení barvy v buňce.
Seznam funkcí ve funkci O.POLÍČKU najdete na konci tohoto článku.
Následuje středník a jako druhý parametr označíme první barevnou buňku. V základu se buňka označí plně zafixována, my ale vzorec budeme chtít použít na více řádků, takže buňku odfixujeme pomocí klávesy F4. Ukončíme závorku. To je celé. Teď to celé potvrdíme.
Použití funkce ze Správce názvů
Vrátíme se k první buňce, kterou jsme předtím označili (C5), napíšeme rovná se a název, který jsme dali funkci (BarvaPole). Funkci potvrdíme a funkce O.POLÍČKU vrátí kódy barev výplně. Tento červený odstín barevné výplně má kód 3 a zelený odstín má kód 10. Stáhneme funkci dolů pro ostatní řádky a u každé barvy se objevilo číslo, které reprezentuje kód dané barvy. Jednotlivé kódy barev nemusíte znát. Nám stačí, že stejné barvy mají stejný kód.
Chybová hláška #BLOKOVÁNO
Některým z vás se může stát, že se po potvrzení funkce objeví chybová hláška #BLOKOVÁNO. Jedná se jen o nesoulad v nastavení excelového sešitu. Tuto chybu odstraníme následujícím způsobem. Nejprve musíme excelový soubor uložit jako soubor, který podporuje makra. Klikneme na Soubor > Uložit jako a vybereme příponu souboru, který podporuje makra tedy xlsm.
Excel uložíme a následně klikneme znovu na Soubor a vybereme Možnosti. Zde vybereme Centrum Zabezpečení a Nastavení centra zabezpečení.
Zde vybereme Nastavení maker a zaškrtneme Povolit makra Excel 4.0. Zde nahoře vybereme Zakázat makra VBA s oznámením.
Následně se přepneme do Nastavení rozšířeného blokování souborů a ujistíme se, že máme zaškrtnutá Excel 4.0 a zde dole Otevírat vybrané typy souborů v chráněném zobrazení a povolit úpravy. Všechno potvrdíme. Uložíme Excel, zavřeme ho a znovu otevřeme. Vybereme povolit makra a teď by vám funkce měla již normálně fungovat.
Teď se můžeme vrátit k řešení příkladu.
Pro součet i počet můžete použít funkce COUNTIF nebo SUMIF. Můžete si daný kód barvy napsat například vedle tabulky. Pro spočítání počtu faktur můžeme použít funkci COUNTIF. Do buňky napíšeme funkci COUNTIF, kde jako oblast označíme pomocný sloupec s kódem barvy a jako co hledáme označíme kód barvy. Potvrdíme a vrátil se počet červených řádků v tabulce. To samé můžeme udělat pro druhou barvu, napíšeme funkci COUNTIF, označíme pomocný sloupec a následně označíme barevný kód, který hledáme.
Pro součet faktur požijeme funkci SUMIF. Napíšeme funkci SUMIF, kde nejprve označíme pomocný sloupec hodnot, následuje oblast kritéria a jako poslední sloupec hodnot, které chceme sečíst. Pokud buňky správně zafixujeme, budeme moci funkci zkopírovat dolů i pro zelenou barvu. Plně zafixovat musíme v tomto případě jak sloupec hodnot, tak pomocný sloupec. Funkci potvrdíme a zkopírujeme ji dolů. Teď máme spočítané i sečtené hodnoty v tabulce podle barevné výplně.
Na závěr je nutné zmínit dvě důležité věci, které si musíte pamatovat. První je, že aby vám funkce O.POLÍČKU fungovala, tak excelový soubor musíte uložit jako soubor podporující makra, a to i přesto, že jsme pro řešení žádné makro nepoužili. Takže když jsme s výpočtem hotoví, tak klikneme na Soubor, uložit jako a vybereme jako příponu souboru xlsm, což je excelový soubor podporující makra.
Druhá a daleko důležitější věc, kterou si musíte pamatovat je to, že funkce O.POLÍČKU se automaticky neaktualizuje. Pokud tedy teď změním barvu výplně jedné buňky, tak se funkce automaticky nepřepočítá. Řešení je ale snadné. Stačí se vrátit k první buňce a funkci poslat dolů znovu pro všechny řádky. Tím se funkce aktualizuje. Stačí si tedy pamatovat, že kdykoliv provedete nějaké barevné změny, že musíte funkci znovu pro všechny buňky nakopírovat.
Seznam funkcí ve funkci O.POLÍČKU
1 Absolutní odkaz buňky
2 Číslo řádku buňky
3 Číslo sloupce buňky
4 Stejné jako TYP (odkaz)
5 Obsah odkazu
6 Vzorec v odkazu jako text ve stylu A1 nebo R1C1 v závislosti na nastavení pracovního prostoru
7 Formát čísla buňky, jako text (například „m/d/rr“ nebo „General“)
8 Číslo označující vodorovné zarovnání buňky:
1 = Obecné
2 = vlevo
3 = střed
4 = správně
5 = výplň
6 = odůvodnit
7 = Střed napříč buňkami
9 Číslo označující styl levého ohraničení přiřazený buňce:
0 = Bez ohraničení
1 = tenká čára
2 = střední čára
3 = přerušovaná čára
4 = tečkovaná čára
5 = tlustá čára
6 = dvojitá čára
7 = vlasová linie
10 Číslo označující styl pravého ohraničení přiřazený buňce. Popis vrácených čísel stejné jako u funkce 9
11 Číslo označující styl horního ohraničení přiřazený buňce. Popis vrácených čísel stejné jako u funkce 9
12 Číslo označující styl spodního ohraničení přiřazený buňce. Popis vrácených čísel stejné jako u funkce 9
13 Číslo od 0 do 18 označující vzor vybrané buňky, jak je zobrazeno na kartě Vzorky v dialogovém okně Formát buněk, který se zobrazí, když v nabídce Formát vyberete příkaz Buňky. Pokud není vybrán žádný vzor, vrátí 0.
14 Pokud je buňka uzamčena, vrátí hodnotu TRUE; jinak vrátí FALSE.
15 Pokud je vzorec buňky skrytý, vrátí hodnotu TRUE; jinak vrátí FALSE.
16 Dvoupoložkové horizontální pole obsahující šířku aktivní buňky a logickou hodnotu udávající, zda je šířka buňky nastavena tak, aby se měnila se změnou standardní šířky (TRUE) nebo je vlastní šířka (FALSE).
17 Výška řádku buňky v bodech.
18 Název písma jako text.
19 Velikost písma v bodech.
20 Pokud jsou všechny znaky v buňce nebo pouze první znak tučně, vrátí hodnotu TRUE; jinak vrátí FALSE.
21 Pokud jsou všechny znaky v buňce nebo pouze první znak kurzívou, vrátí hodnotu TRUE; jinak vrátí FALSE.
22 Pokud jsou všechny znaky v buňce nebo pouze první znak podtrženy, vrátí hodnotu TRUE; jinak vrátí FALSE.
23 Pokud jsou přeškrtnuty všechny znaky v buňce nebo pouze první znak, vrátí hodnotu TRUE; jinak vrátí FALSE.
24 Barva písma prvního znaku v buňce jako číslo v rozsahu 1 až 56. Pokud je barva písma automatická, vrátí 0.
25 Pokud jsou ohraničeny všechny znaky v buňce nebo pouze první znak, vrátí hodnotu TRUE; jinak vrátí FALSE. Formát obrysového písma není podporován aplikací Microsoft Excel pro Windows.
26 Pokud jsou všechny znaky v buňce nebo pouze první znak stínované, vrátí hodnotu TRUE; jinak vrátí FALSE. Formát stínového písma není podporován aplikací Microsoft Excel pro Windows.
27 Číslo udávající, zda v buňce dojde k ručnímu zalomení stránky:
0 = Bez přerušení
1 = řada
2 = sloupec
3 = řádek i sloupec
28 Úroveň řádku (obrys).
29 Úroveň sloupce (obrys).
30 Pokud je řádek obsahující aktivní buňku souhrnným řádkem, vrátí hodnotu TRUE; jinak vrátí FALSE
31 Pokud je sloupec obsahující aktivní buňku souhrnným sloupcem, vrátí hodnotu TRUE; jinak vrátí FALSE.
32 Název sešitu a listu obsahujícího buňku Pokud okno obsahuje pouze jeden list, který má totéž název jako sešit bez jeho přípony, vrátí pouze název knihy ve tvaru BOOK1.XLS. V opačném případě vrátí název listu ve tvaru “[Sešit1]List1”.
33 Pokud je buňka formátována tak, aby zalomila, vrátí hodnotu TRUE; jinak vrátí FALSE.
34 Barva levého okraje jako číslo v rozsahu 1 až 56. Pokud je barva automatická, vrátí 0.
35 Barva pravého okraje jako číslo v rozsahu 1 až 56. Pokud je barva automatická, vrátí 0.
36 Barva horního okraje jako číslo v rozsahu 1 až 56. Pokud je barva automatická, vrátí 0.
37 Barva dolního okraje jako číslo v rozsahu 1 až 56. Pokud je barva automatická, vrátí 0.
38 Stínujte barvu popředí jako číslo v rozsahu 1 až 56. Pokud je barva automatická, vrátí 0.
39 Stínujte barvu pozadí jako číslo v rozsahu 1 až 56. Pokud je barva automatická, vrátí 0.
40 Styl buňky jako text.
41 Vrátí vzorec v aktivní buňce bez jeho překladu (užitečné pro mezinárodní listy maker).
42 Vodorovná vzdálenost, měřená v bodech, od levého okraje aktivního okna k levému okraji buňky. Může být záporné číslo, pokud je okno posunuto za buňku.
43 Vertikální vzdálenost, měřená v bodech, od horního okraje aktivního okna k hornímu okraji buňky. Může být záporné číslo, pokud je okno posunuto za buňku.
44 Vodorovná vzdálenost, měřená v bodech, od levého okraje aktivního okna k pravému okraji buňky. Může být záporné číslo, pokud je okno posunuto za buňku.
45 Vertikální vzdálenost, měřená v bodech, od horního okraje aktivního okna ke spodnímu okraji buňky. Může být záporné číslo, pokud je okno posunuto za buňku.
46 Pokud buňka obsahuje textovou poznámku, vrátí TRUE; jinak vrátí FALSE.
47 Pokud buňka obsahuje zvukovou poznámku, vrátí TRUE; jinak vrátí FALSE.
48 Pokud buňky obsahují vzorec, vrátí TRUE; pokud je konstanta, vrátí FALSE.
49 Pokud je buňka součástí pole, vrátí TRUE; jinak vrátí FALSE.
50 Číslo označující vertikální zarovnání buňky:
1 = nahoře
2 = střed
3 = dole
4 = oprávněné
51 Číslo označující vertikální orientaci buňky:
0 = horizontální
1 = vertikální
2 = nahoru
3 = dolů
52 Znak předpony buňky (nebo zarovnání textu) nebo prázdný text (“”), pokud jej buňka neobsahuje.
53 Obsah buňky tak, jak je aktuálně zobrazen, jako text, včetně jakýchkoli dalších čísel nebo symbolů vyplývajících z formátování buňky.
54 Vrátí název zobrazení kontingenční tabulky obsahující aktivní buňku.
55 Vrátí polohu buňky v zobrazení kontingenční tabulky.
56 Vrátí název pole obsahujícího odkaz na aktivní buňku, pokud je uvnitř zobrazení kontingenční tabulky.
57 Vrátí hodnotu TRUE, pokud jsou všechny znaky v buňce nebo pouze první znak formátovány horním indexem; jinak vrátí FALSE.
58 Vrátí styl písma jako text všech znaků v buňce nebo pouze první znak, jak je zobrazen na kartě Písmo v dialogovém okně Formát buněk: například “Tučná kurzíva”.
59 Vrátí číslo pro styl podtržení:
1 = žádný
2 = svobodný
3 = dvojnásobek
4 = jednoduché účetnictví
5 = podvojné účetnictví
60 Vrací hodnotu TRUE, pokud jsou všechny znaky v buňce nebo pouze první znak naformátovány dolním indexem; jinak vrátí FALSE.
61 Vrátí název položky kontingenční tabulky pro aktivní buňku jako text.
62 Vrátí název sešitu a aktuálního listu ve tvaru “[sešit1]list1”.
63 Vrátí barvu výplně (pozadí) buňky.
64 Vrátí barvu vzorku (popředí) buňky.
65 Vrátí hodnotu PRAVDA, pokud je zapnuta možnost Přidat odsazení (pouze verze aplikace Microsoft Excel pro Dálný východ); jinak vrátí FALSE.
66 Vrátí název knihy sešitu obsahujícího buňku ve tvaru BOOK1.XLS.
2 komentáře
😊
Dobrý den, toto je nejbližší návod, který jsem objevil. Já však potřebuji sečíst počet barevných ikon v tabulce. Dotaz jsem zadával AI a nepomohla. Dotaz: mám tabulku se sloupci C,D,E,F na řádku 5 až 35. V této tabulce jsou skryté čísla hodnot, které se odkazují na jinou tabulku v jiném listě (skryté jen proto, aby ikona kolečko bylo uprostřed buňky a aby čísla nerozptylovaly). Na základě těchto skrytých hodnot (podmíněné form.) se v buňkách zobrazuje pouze sada ikon, která má čtyři barvy, zelená, žlutá, červená a černá. Těchto ikon je tabulka plná. A já teď potřebuji ze všech těchto různě barevných ikon udělat jeden přehledný kruhový graf, který bude mít ty stejné 4 barvy a jeho velikosti výseče budou odpovídat počtu daných ikon. Lze takový graf vytvořit? Případně je možné spočítat kolik je zelených, žlutých, červených a černých ikon a udělat tabulku s počtem těchto ikon?
Děkuji a přeji hezký den ☺☺☺