Ako vytvoriť rozsah dynamických grafov v programe Excel

Tento tutoriál vám ukáže, ako vytvoriť rozsah dynamických grafov vo všetkých verziách programu Excel: 2007, 2010, 2013, 2016 a 2022.

V predvolenom nastavení pri rozbalení alebo stiahnutí množiny údajov použitej na vykreslenie grafu v programe Excel je potrebné manuálne upraviť aj podkladové zdrojové údaje.

Vytvorením dynamických rozsahov grafov sa však môžete týmto problémom vyhnúť.

Dynamické rozsahy grafov vám umožňujú automaticky aktualizovať zdrojové údaje pri každom pridávaní alebo odstraňovaní hodnôt z rozsahu údajov, čo šetrí veľa času a úsilia.

V tomto návode sa naučíte všetko, čo potrebujete vedieť, aby ste uvoľnili silu Rozsahy dynamických grafov.

Rozsahy dynamických grafov - úvod

Uvažujte o nasledujúcom vzorovom súbore údajov, ktorý analyzuje kolísanie ziskovej marže:

V zásade existujú dva spôsoby nastavenia rozsahu dynamického grafu:

1. Konvertovanie rozsahu údajov na tabuľku
2. Použitie dynamických pomenovaných rozsahov ako zdrojových údajov grafu.

Obe metódy majú svoje klady a zápory, preto si o každej z nich povieme podrobnejšie, aby ste vám pomohli určiť, ktorá bude pre vás najvhodnejšia.

Bez ďalších okolkov začnime.

Tabuľková metóda

Na úvod vám ukážem najrýchlejší a najľahší spôsob, ako splniť danú úlohu. Takže, tu je nácvik: Premeňte rozsah údajov na tabuľku a budete oveľa jednoduchší ako lúskanie hrachu.

Takto bude všetko, čo zadáte do buniek na konci tabuľky, automaticky zahrnuté do zdrojových údajov grafu.

Tu je návod, ako to dosiahnuť v dvoch jednoduchých krokoch.

Krok č. 1: Premeňte rozsah údajov na tabuľku.

Priamo z brány transformujte rozsah buniek obsahujúci údaje vášho grafu na tabuľku.

1. Zvýraznite celý rozsah údajov (A1: B6).
2. Kliknite na Vložiť tab.
3. Kliknite na „Tabuľka”Tlačidlo.

V Vytvoriť tabuľku V dialógovom okne postupujte takto:

1. Znovu skontrolujte, či sa zvýraznený rozsah buniek zhoduje s celou tabuľkou údajov.
2. Ak tabuľka neobsahuje žiadny riadok hlavičky, zrušte začiarknutie políčka „Môj stôl má hlavičky”Box.
3. Kliknite na „OK.“

V dôsledku toho by ste mali skončiť s touto tabuľkou:

Krok č. 2: Vytvorte graf podľa tabuľky.

Základ bol položený, čo znamená, že teraz môžete pomocou tabuľky zostaviť graf.

1. Zvýraznite celú tabuľku (A1: B6).
2. Prejdite na Vložiť tab.
3. Vytvorte ľubovoľný 2-D graf. Na ilustračné účely si vytvoríme jednoduchý stĺpcový graf (Vložiť stĺpcový alebo stĺpcový graf> Klastrovaný stĺpec).

To je všetko! Ak chcete otestovať techniku, skúste pridať nové dátové body v spodnej časti tabuľky aby ste ich videli automaticky mapované na grafe. O koľko jednoduchšie to môže byť?

POZNÁMKA: Pri tomto prístupe by mal súbor údajov nikdy obsahovať prázdne bunky-to zničí graf.

Metóda dynamického pomenovaného rozsahu

Aj keď sa ľahko aplikuje, predtým preukázané, Tabuľková metóda má vážne nevýhody. Graf sa napríklad pokazí, kedykoľvek je súbor čerstvých údajov menší ako pôvodná tabuľka údajov plus, niekedy jednoducho nechcete, aby sa rozsah údajov konvertoval na tabuľku.

Voľba pomenovaných rozsahov môže trvať trochu viac času a úsilia z vašej strany, ale táto technika popiera nevýhody stolovej metódy a navyše robí dynamický rozsah oveľa pohodlnejším na prácu na diaľku.

Krok č. 1: Vytvorte dynamické pomenované rozsahy.

Na začiatok nastavte pomenované rozsahy, ktoré sa nakoniec použijú ako zdrojové údaje pre váš budúci graf.

1. Choďte do Vzorce tab.
2. Kliknite na „Správca mien.”
3. V Správca mien V zobrazenom dialógovom okne vyberte „Nový.”

V Nové meno v dialógovom okne vytvorte úplne nový pomenovaný rozsah:

1. Zadajte „Štvrťrok" vedľa "názov" lúka. Pre pohodlie uveďte názov dynamického rozsahu do zodpovedajúcej bunky riadka hlavičky stĺpec A (A1).
2. V “Rozsah”Vyberte aktuálny pracovný hárok. V našom prípade je to tak List 1.
3. Do poľa „Zadajte nasledujúci vzorec“Odkazuje na" lúka: = OFFSET (Sheet1! $ A $ 2,0,0, COUNTA (Sheet1! $ A: $ A) -1,1)

V jednoduchej angličtine, pri každej zmene ľubovoľnej bunky v hárku, funkcia OFFSET vráti iba skutočné hodnoty v stĺpec A, vynechávajúc bunku riadka hlavičky (A1), zatiaľ čo funkcia COUNTA prepočíta počet hodnôt v stĺpci pri každej aktualizácii pracovného hárka-efektívne vykoná všetku špinavú prácu za vás.

Rozoberme vzorec podrobnejšie, aby sme vám pomohli pochopiť, ako funguje:

POZNÁMKA: Názov pomenovaného rozsahu musí začínať písmenom alebo podčiarkovníkom a nesmie obsahovať medzery.

Rovnakým spôsobom nastavte ďalší pomenovaný rozsah na základe stĺpec Marža zisku (stĺpec B) pomocou tohto vzorca a označte ho „Zisk_Margin”:

1	= OFFSET (Sheet1! $ B $ 2,0,0, COUNTA (Sheet1! $ B: $ B) -1,1)

Ak vaša tabuľka údajov obsahuje viac stĺpcov so skutočnými hodnotami, opakujte rovnaký postup. V našom prípade by ste v dôsledku toho mali mať pripravené dva pomenované rozsahy:

Krok č. 2: Vytvorte prázdny graf.

Prešli sme najťažšou časťou. Teraz je načase vytvoriť prázdny graf, aby ste doň mohli manuálne vložiť dynamické pomenované rozsahy.

1. Vyberte ľubovoľnú prázdnu bunku v aktuálnom pracovnom hárku (List 1).
2. Vráťte sa späť do Vložiť tab.
3. Nastavte ľubovoľný 2-D graf. V našom prípade vytvoríme stĺpcový graf (Vložiť stĺpcový alebo stĺpcový graf> Klastrovaný stĺpec).

Krok č. 3: Pridajte pomenovaný rozsah/rozsahy obsahujúce skutočné hodnoty.

Najprv vložte pomenovaný rozsah (Zisk_Margin) prepojené so skutočnými hodnotami (stĺpec B) do grafu.

Pravým tlačidlom myši kliknite na prázdny graf a zvoľte „Vyberte položku Údaje”Z kontextového menu.

V Vyberte položku Zdroj údajov v dialógovom okne kliknite na „Pridať.”

V Upraviť sériu box, vytvorte nový rad údajov:

1. V časti „Názov série, ”Zvýraznite zodpovedajúcu bunku riadka hlavičky (B1).
2. V časti „Sériové hodnoty, “Zadajte pomenovaný rozsah, ktorý sa má vykresliť do grafu, zadaním nasledujúceho príkazu:„= List1! Zisk_Margin.„Referencia sa skladá z dvoch častí: názvov aktuálneho pracovného hárka (= List1) a príslušný dynamický pomenovaný rozsah (Profit_Margin). Výkričník sa používa na spojenie týchto dvoch premenných.
3. Vyberte „OK.”

Keď už ste tam, Excel automaticky zmapuje hodnoty:

Krok č. 4: Vložte pomenovaný rozsah so štítkami osí.

Nakoniec nahraďte predvolené menovky osí kategórie pomenovaným rozsahom, z ktorého sa skladajú stĺpec A (Štvrťrok).

V Vyberte položku Zdroj údajov dialógové okno v časti „Menovky horizontálnej (kategórie) osi,“Vyberte„Upraviť”Tlačidlo.

Potom vložte pomenovaný rozsah do grafu zadaním nasledujúceho odkazu do časti „Rozsah štítkov osi:”

1	= List1! Štvrťrok

Nakoniec je pripravený stĺpcový graf na základe rozsahu dynamického grafu:

Pozrite sa na to: Graf sa aktualizuje automaticky vždy, keď pridáte alebo odstránite údaje v dynamickom rozsahu.