Tento tutoriál vám ukáže, ako vytvárať grafy programu Excel s podmieneným formátovaním vo všetkých verziách programu Excel: 2007, 2010, 2013, 2016 a 2022.
Podmienené formátovanie je postup priraďovania vlastného formátovania k bunkám programu Excel-farbe, písmu atď. Na základe zadaných kritérií (podmienok). Táto funkcia pomáha pri analýze údajov, hľadaní štatisticky významných hodnôt a identifikácii vzorov v rámci daného súboru údajov.
Ale bohužiaľ, Excel neponúka žiadne vstavané nástroje na aplikáciu podmieneného formátovania na grafy programu Excel.
S kvapkou tabuľkového mágie však nie je žiadny problém neprekonateľný. V tomto tutoriáli sa naučíte, ako vytvoriť dynamický graf s podmieneným formátovaním, v ktorom sú vykreslené dátové body automaticky formátované na základe ich príslušných skutočných hodnôt.
Pozrite sa napríklad na tento stĺpcový graf, ktorý zobrazuje výkonnosť manažéra predaja za obdobie ôsmich mesiacov. Všimnite si, ako sa farba stĺpca automaticky upravuje v závislosti od jeho základnej hodnoty:
Začíname
Zoberme si tento ukážkový súbor údajov obsahujúci údaje o výkonnosti predaja:
Než začneme, presuňte svoju množinu údajov tak, aby ste nad tabuľkou so svojimi nespracovanými údajmi mali tri prázdne riadky. Riadky sa použijú na nastavenie pravidiel podmieneného formátovania.
Krok č. 1: Údaje prípravného grafu.
Aby veci fungovali, musíte do rovnice pridať niekoľko ďalších údajov z grafu. Pre pohodlie sa na konci prípravnej fázy pozrite, ako by mali údaje v grafe vyzerať:
Naším prvým krokom je definovanie hraničných hodnôt (podmienok), ktoré by mali rozdeliť skutočné hodnoty do viacerých kategórií, a umožniť ich mapovanie ako samostatných radov údajov.
Na obrázku vyššie napríklad vidíte, že čísla tržieb za daný mesiac sa musia pohybovať v rozmedzí od 90 000 do 150 000 dolárov, aby sa považovali za uspokojivé.
Na vytvorenie pravidiel použite tri prázdne riadky nad množinou údajov:
- Riadok 1 (štítok): Tieto bunky použite na označenie pravidiel formátovania.
- Riadok 2 (minimálna hodnota): Hodnoty v tomto riadku definujú dolnú hranicu pre každú podmienku.
- Riadok 3 (max. Hodnota): Rovnako tieto hodnoty stanovujú horné hranice.
Pomocou tejto metódy môžete vytvoriť ľubovoľný počet pravidiel podmieneného formátovania-uistite sa, že sa rozsahy hodnôt neprekrývajú.
Pri vytváraní grafu sa posúvajte vpred a navrhujte položky legendy vlastného grafu, ktoré grafu poskytnú kontext. Zadajte tento vzorec do C4 a skopírujte ho do D4 potiahnutím rukoväte výplne:
| 1 | = C1 & ": od" & TEXT (C2, "$#, ##") & "do" & TEXT (C3, "$#, ##") |
Vzorec dáva dohromady úhľadne vyzerajúci dynamický štítok na základe predtým stanovených pravidiel podmieneného formátovania. Funkcia TEXT formátuje hodnoty ako mena. Ak sa však váš typ údajov líši, použite namiesto toho tento vzorec:
| 1 | = C1 & ": od" & TEXT (C2, "#, ##") & "do" & TEXT (C3, "#, ##") |
Alebo tento, keď pracujete s percentami:
| 1 | = C1 & ": od" & TEXT (C2, "#%") & "do" & TEXT (C3, "#%") |
Nakoniec nájdite hodnoty v grafe. Zadajte nasledujúci vzorec do C5, skopírujte ho do priečinka C12 a naprieč k zodpovedajúcim bunkám v stĺpec D:
| 1 | = IF (AND (C $ 2 <$ B5, $ B5 <= C $ 3), $ B5, NA ()) |
Vzorec porovnáva danú skutočnú hodnotu v stĺpec B proti uvedeným hraničným hodnotám pomocou funkcií IF a AND. Ak hodnota spadá do rozsahu, skopíruje sa do tohto stĺpca. V opačnom prípade vzorec vráti chybu #N/A, aby sa zabránilo opakovanému vykresleniu tej istej hodnoty.
Krok č. 2: Nastavte stĺpcový graf.
Po zhromaždení všetkých údajov grafu vytvorte alternatívny jednoduchý stĺpcový graf alebo stĺpcový graf:
- Zvýraznite všetky údaje v grafe okrem stĺpcov obsahujúcich skutočné hodnoty a pravidlá podržaním klávesu Ctrl kľúč (A4: A12 a C4: D12).
- Choďte do Vložiť tab.
- Vyberte „Vložiť stĺpcový alebo stĺpcový graf.”
- Vyberte „Klastrovaný stĺp/Klastrovaný pruh.”
V dôsledku toho skončíte s grafom s dátovými bodmi vykreslenými na základe pravidiel podmieneného formátovania-presne ako bolo sľúbené:
Krok č. 3: Upravte hodnoty prekrývania a šírky medzery.
Pred zatvorením tohto tutoriálu opravte problém s nesprávne umiestnenými stĺpcami spôsobenými neviditeľnými prázdnymi hodnotami (chyby #N/A) vykreslenými v grafe.
Kliknite pravým tlačidlom myši na ktorýkoľvek zo stĺpcov a zvoľte „Formátovať rad údajov”Z kontextového menu, ktoré sa objaví.
Na paneli úloh zmeňte polohu a šírku stĺpcov:
- Prepnúť na Možnosti série tab.
- Zmeniť „Prekrývanie série“Až„100%.”
- Nastaviť Šírka medzery na “60%.”
Krok č. 4: Upravte farebnú schému.
Nakoniec pridajte posledné úpravy. Bez zatvorenia Formátovať rad údajov na paneli úloh upravte farebnú schému grafu:
- Choďte do Fill & Line tab.
- V časti „Vyplniť"Vybrať"Pevná výplň.”
- Kliknite na „Farba výplne”A vyberte zelená z palety farieb.
Akonáhle ste tam, prefarbite ostatné rady údajov, zmeňte názov grafu a váš plne dynamický graf s podmieneným formátovaním je pripravený!
