Obsah
- Vytváření pojmenovaných rozsahů v aplikaci Excel 2007 a 2010
- Vytvoření dynamického grafu v aplikaci Excel 2007 a 2010
- Závěr
Robbie většinou píše o Skyrimu, ale občas také osvětlí zvláštnosti aplikací společnosti Microsoft, jako jsou Excel a Outlook.
Ahoj a vítejte u mého článku o používání OFFSET funkce. V dnešním článku se chystám prozkoumat, jak pomocí aplikace vytvořit dynamické grafy v aplikaci Excel OFFSET jakož i Definovaná jména k vytvoření pojmenovaných rozsahů.
To vám umožní vytvořit grafy z vašich dat, které se automaticky aktualizují, když přidáte nebo odeberete data. Se statickým grafem, když přidáváte data, musíte graf ručně aktualizovat, aby zobrazoval nová data. Dynamický graf to dělá za vás automaticky.
V mém předchozí článek na OFFSET Funkce jsem zkoumal, jak ji používat s COUNTA funkce pro vytvoření dynamických datových rozsahů, takže jej můžete například použít s SOUČET přidat faktury, údaje o prodeji nebo provozu na webu. Pokud odstraníte nebo přidáte data do dynamického rozsahu, váš vzorec se automaticky přepočítá tak, aby odrážel nová data.
Vytváření pojmenovaných rozsahů v aplikaci Excel 2007 a 2010
Prvním krokem je definování pojmenovaných rozsahů z našich dat.
Pojmenované rozsahy se pak použijí k definování dat, která použijeme k vytvoření dynamických grafů.
Musíme vytvořit dva pojmenované rozsahy, jeden pro samotná data a druhý pro popisky grafů. Nejprve vytvoříme rozsah pro data, která se objeví v našem grafu.
- Vybrat Definujte jméno tlačítko na Vzorce záložka v Definovaná jména skupina.
- Definovat a název (bez mezer) můžete vidět. Použil jsem DailyVisits.
- V Odkazuje na do pole zadejte následující vzorec offsetu:
= OFFSET (DynamicCharts! $ B $ 2,1,0, COUNTA (DynamicCharts! $ B: $ B), 1)
Výše uvedený vzorec vytváří dynamický rozsah počínaje buňkou B2. Odpočítává stejný počet řádků jako počet řádků, u kterých bylo zjištěno, že obsahují data COUNTA.
Takže když COUNTA najde 23 buněk obsahujících data, rozsah začíná na B2 a končí na B25.
COUNTA je dynamický. Pokud jsou data přidána nebo odebrána, automaticky se aktualizují, což změní rozsah. The OFFSET funkce je vysvětlena mnohem podrobněji v mém prvním hubu na OFFSET (odkaz naleznete v úvodu).
Poznámka:Při provádění změn v Odkazuje na pole, nepoužívejte klávesy se šipkami pro pohyb po tomto dialogovém okně, jinak do vzorce přidáte odkazy na buňky. Místo toho se pohybujte kliknutím myši.
Poznámka:pro úpravu pojmenovaných rozsahů, jakmile jsou vytvořeny, použijte Správce jmen tlačítko nalevo od tlačítka Definovat jméno, které jste použili k jeho vytvoření.
- Dále musíme výše uvedený proces opakovat, tentokrát s použitím Label pro název a vzorec níže v Odkazuje na pole pro vytvoření našeho dynamického rozsahu pro popisky grafů.
= OFFSET (DynamicCharts! $ A $ 2,1,0, COUNTA (DynamicCharts! A $ 3: $ A $ 33), 1
Vytvoření dynamického grafu v aplikaci Excel 2007 a 2010
Nyní máme pojmenované rozsahy, musíme vytvořit graf:
- Vybrat Sloupec tlačítko v Grafy skupina na Vložit záložka
Tím se vytvoří zcela prázdný graf.
- S vybraným grafem klikněte na Vyberte Data tlačítko v Data skupina na Design záložka pod Nástroje grafu.
- Změňte rozsah dat grafu tak, aby se za název karty zadal výše uvedený název pojmenovaného rozsahu (použil jsem DailyVisits), jak je uvedeno níže.
Takže v mém příkladu jsem změnil:
= DynamicCharts! $ F $ 7
na
= DynamicCharts! DailyVisits
Tabulka bude nyní vypadat takto.
Přidání našich dynamických štítků:
- Znovu vyberte graf.
- Klikněte na Vyberte Data tlačítko v Data skupina na Design záložka pod Nástroje grafu.
- Pod Štítky horizontální (kategorie) osy, klikněte Upravit.
- V Štítek osy rozsah, zadejte název svého pojmenovaného rozsahu pro štítky (vybral jsem štítky pro můj).
- Klepněte na OK dvakrát a Excel nyní přidal vaše štítky do grafu.
Nakonec musíme zajistit, aby graf používal pojmenované rozsahy. Udělat toto:
- Ujistěte se, že je graf znovu vybrán.
- Klikněte na datovou řadu.
- Na řádku vzorců uvidíte vzorec začínající = SERIES.
Vzorec v mém grafu je:
= SERIES (, 'Analytics Overview.xlsm'! Labels, DynamicCharts! $ B $ 3: $ B $ 39,1)
Než tento vzorec upravíme, podívejme se na jeho syntaxi.
Vzorec se skládá ze čtyř částí.
- Název grafu (je volitelný)
- Umístění štítků pro vodorovnou osu
- Umístění dat pro sérii
- Pořadí, ve kterém se série objeví v grafu (1 pro první, 2 pro druhé atd.)
Existují tři části vzorce, které byste měli upravit.
- Prvním je přidání názvu grafu, pokud si přejete, z datové hlavičky (pokud kliknete nalevo od první čárky ve vzorci a poté vyberete buňku, která obsahuje datovou hlavičku (což je v mém příkladu B2) Excel bude přidejte jej automaticky), takže první část vzorce bude:
= SÉRIE (DynamicCharts! $ B $ 2,
- Druhá část je část, která obsahuje dynamické štítky. Pokud se štítky v tabulce změní, budou automaticky aktualizovány.
'Analytics Overview.xlsm'! Štítky,
- Třetí částí je zajistit, aby se graf aktualizoval při dynamickém přidávání nebo odebírání dat. Udělat toto:
Změňte poslední část vzorce $ B $ 3: $ B $ 39 na pojmenovaný rozsah dat pro data (moje je DailyVisits).
DynamicCharts! $ B $ 3: $ B $ 39,
Vzorec se stává:
= SERIES (DynamicCharts! $ B $ 2, 'Analytics Overview.xlsm'! Labels, DynamicCharts! DailyVisits, 1)
Nyní máme graf hotový, ještě jednu poslední věc, kterou si musíme všimnout při zadávání údajů. Pokud zadáte data bez odpovídajících štítků v sousedním řádku, Excel je do grafu nepřidá. Nová data se zobrazí až po přidání štítků. Pokud přidáte několik štítků bez dat, Excel zobrazí první štítek bez dat, ale do grafu nepřidá žádné další štítky bez dat.
Závěr
Jak se naše životy stávají rušnějšími a zvyšuje se množství informací, se kterými pracujeme, je vždy velmi užitečné z hlediska úspory času i redukce chyb automatizovat, jak je to jen možné, a OFFSET funkce je opravdu velmi užitečná, protože nám umožňuje automatizovat vzorce i grafy v aplikaci Excel. Zejména u tabulek, které se často aktualizují, je automatizace aktualizace vašich grafů opravdu velmi užitečná.
The OFFSET Funkce v aplikaci Excel umožňuje vytvářet dynamické rozsahy a dynamické grafy, které se automaticky aktualizují, když jsou v rozsahu přidána nebo odstraněna data. V tomto článku jsme vytvořili pojmenované rozsahy a použili jsme je s OFFSET funkce pro vytváření dynamických grafů.
Děkuji za přečtení a doufám, že si nyní užíváte své dynamicky se aktualizující grafy a trávíte svůj nový volný čas něčím zábavnějším. Níže prosím zanechejte jakékoli připomínky.
Tento článek je přesný a pravdivý podle nejlepších znalostí autora. Obsah slouží pouze k informačním nebo zábavním účelům a nenahrazuje osobní rady ani odborné rady v obchodních, finančních, právních nebo technických záležitostech.
