Jak vytvářet grafy aplikace Excel (sloupec nebo pruh) s podmíněným formátováním

Tento tutoriál předvede, jak vytvářet grafy aplikace Excel s podmíněným formátováním ve všech verzích aplikace Excel: 2007, 2010, 2013, 2016 a 2022.

Podmíněné formátování je postup přiřazování vlastního formátování buňkám aplikace Excel-barva, písmo atd. Na základě zadaných kritérií (podmínek). Tato funkce pomáhá při analýze dat, hledání statisticky významných hodnot a identifikaci vzorců v rámci dané datové sady.

Ale bohužel, Excel nenabízí žádné vestavěné nástroje pro aplikaci podmíněného formátování na grafy Excelu.

S kapkou tabulkového magie však není žádný problém nepřekonatelný. V tomto kurzu se naučíte, jak vytvořit dynamický graf s podmíněným formátováním, kde jsou vykreslené datové body automaticky formátovány na základě jejich příslušných skutečných hodnot.

Podívejte se například na tento sloupcový graf rozdělující výkon manažera prodeje za období osmi měsíců. Všimněte si, jak se barva sloupce automaticky upravuje v závislosti na jeho podkladové hodnotě:

Začínáme

Zvažte tuto ukázkovou datovou sadu obsahující údaje o výkonu prodeje:

Než začneme, přesuňte svou datovou sadu, abyste měli nad tabulkou tři prázdné řádky s nezpracovanými daty. Řádky budou použity pro nastavení pravidel podmíněného formátování.

Krok č. 1: Data přípravného grafu.

Aby vše fungovalo, musíte do rovnice přidat další data grafu. Pro vaše pohodlí se na konci přípravné fáze podívejte, jak by měla data vašeho grafu vypadat:

Naším prvním krokem je definování hraničních hodnot (podmínek), které by měly rozdělit skutečné hodnoty do více kategorií, a umožnit tak jejich mapování jako samostatných datových řad.

Na výše uvedeném snímku obrazovky například vidíte, že údaje o tržbách za daný měsíc musí být v rozmezí 90 000 až 150 000 USD, aby byly považovány za uspokojivé.

Chcete -li vytvořit pravidla, použijte tři prázdné řádky nad datovou sadou:

• Řádek 1 (štítek): Tyto buňky použijte k označení pravidel formátování.
• Řádek 2 (minimální hodnota): Hodnoty v tomto řádku definují dolní hranici pro každou podmínku.
• Řádek 3 (max. Hodnota): Podobně tyto hodnoty nastavují horní hranice.

Pomocí této metody můžete vytvořit libovolný počet pravidel podmíněného formátování-jen se ujistěte, že se rozsahy hodnot nepřekrývají.

Jak budete pokračovat v nastavování grafu, navrhujte položky legendy vlastního grafu, které budou poskytovat kontext grafu. Zadejte tento vzorec do C4 a zkopírujte jej do D4 přetažením úchytu výplně:

1	= C1 & ": od" & TEXT (C2, "$#, ##") & "do" & TEXT (C3, "$#, ##")

Vzorec dává dohromady úhledně vypadající dynamický štítek na základě dříve nastavených pravidel podmíněného formátování. Funkce TEXT formátuje hodnoty jako měna. Pokud se však váš datový typ liší, použijte místo toho tento vzorec:

1	= C1 & ": od" & TEXT (C2, "#, ##") & "do" & TEXT (C3, "#, ##")

Nebo tento, když pracujete s procenty:

1	= C1 & ": od" & TEXT (C2, "#%") & "do" & TEXT (C3, "#%")

Nakonec najděte hodnoty grafu. Zadejte následující vzorec do C5, zkopírujte jej do C12 a napříč k odpovídajícím buňkám v sloupec D:

1	= IF (AND (C $ 2 <$ B5, $ B5 <= C $ 3), $ B5, NA ())

Vzorec porovnává danou skutečnou hodnotu v sloupec B proti zadaným hraničním hodnotám pomocí funkcí IF a AND. Pokud hodnota spadá do rozsahu, zkopíruje se do tohoto sloupce. V opačném případě vzorec vrátí chybu #N/A, aby se zabránilo opakovanému vykreslení stejné hodnoty.

Krok č. 2: Nastavte sloupcový graf.

Po shromáždění všech dat grafu vytvořte alternativní jednoduchý sloupcový graf nebo sloupcový graf:

1. Zvýrazněte všechna data grafu kromě sloupců obsahujících skutečné hodnoty a pravidla podržením Ctrl klíč (A4: A12 a C4: D12).
2. Jděte na Vložit tab.
3. Vyberte „Vložte sloupcový nebo sloupcový graf.”
4. Vybrat "Clustered Column/Clustered Bar.”

V důsledku toho skončíte s grafem s datovými body vykreslenými na základě pravidel podmíněného formátování-přesně tak, jak jsme slíbili:

Krok č. 3: Upravte hodnoty překrývání a šířky mezery.

Před zavřením tohoto kurzu opravte problém chybně umístěných sloupců způsobených neviditelnými prázdnými hodnotami (chyby #N/A) vykreslenými v grafu.

Klikněte pravým tlačítkem na některý ze sloupců a vyberte „Formát datové řady”Z kontextové nabídky, která se objeví.

V podokně úloh změňte polohu a šířku sloupců:

1. Přepnout na Možnosti řady tab.
2. Změna "Překrývání řady“Až„100%.”
3. Nastav Šířka mezery na “60%.”

Krok č. 4: Upravte barevné schéma.

Nakonec přidejte poslední úpravy. Bez zavření Formát datové řady podokno úloh, upravte barevné schéma grafu:

1. Jděte na Fill & Line tab.
2. Pod „Vyplnit," Vybrat "Pevná výplň.”
3. Klikněte na „Vyplň barvu”A vyberte zelená z palety barev.

Jakmile jste tam, přebarvěte další datové řady, změňte název grafu a váš plně dynamický graf s podmíněným formátováním je připraven!