Dynamické grafy využívající funkci OFFSET a pojmenované rozsahy aplikace Excel mění velikost při přidání nebo odstranění dat. Na rozdíl od použití funkce Tabulky pro dynamické grafy je tato metoda zpětně kompatibilní s dřívějšími verzemi aplikace Excel.
Pomocí funkcí OFFSET a COUNTA aplikace Excel vytvoříte dynamické názvy rozsahů, které se automaticky přizpůsobují při přidávání nebo odstraňování dat.
Klikněte pro stažení souboru s výukovým příkladem dynamické změny velikosti grafů pomocí funkce OFFSET a názvů rozsahů. Příklad obsahuje horizontální i vertikální data.
Metoda OFFSET a názvu rozsahu vyžaduje více práce než dvě kliknutí používaná pro tabulku, ale dává vám kontrolu nad všemi daty grafu, jak ukazuje moje kniha „Balanced Scorecards and Operational Dashboards with Microsoft Excel“. Na této stránce CriticalToSuccess.com najdete příklady dalších způsobů použití OFFSET, například vytvoření rolovacího grafu, ovládání grafů pomocí rozbalovacích nabídek a další. Pro zobrazení příkladů vyhledejte na stránkách CriticalToSuccess.com výraz „OFFSET“ nebo „dynamický rozsah“.
Vytvoření statického grafu
Dynamický graf začněte nejprve vytvořením statického grafu následujícím způsobem,
1. Klikněte dovnitř rozsahu B7:D12, aby Excel věděl, kde se data grafu nacházejí.
2. Na kartě Vložení ve skupině Grafy vyberte jednoduchý graf, například sloupcový, sloupcový nebo čárový.
Přesuňte tento graf tak, abyste měli prostor pro přidání dat pod rozsah dat.
Vytvoření dynamických názvů rozsahů pomocí OFFSET
Různé verze aplikace Excel mají různé metody vytváření a úprav vzorců pro názvy rozsahů. Následující pokyny jsou určeny pro aplikace Excel 2007, 2010 a 2013.
1. Zvolte si vzorce, které chcete použít. Na kartě Vzorce klepněte ve skupině Definovaný název na tlačítko Definovat název. Zobrazí se okno Definovat název.
V poli Definovat název vytvořte názvy rozsahů.
2. Do textového pole Název zadejte rngVDate. Konvence pojmenování je,
rng Název rozsahu
V Vertikální orientace dat v B7:B16
Date Typ dat v rozsahu
3. Do textového pole pro komentář napište komentář, který vám nebo ostatním pomůže zjistit, k čemu tento název rozsahu slouží.
4. Do pole Vztahuje se k napište vzorec OFFSET, který vypočítá rozsah dat Date.
=OFFSET(wrksht!$B$7,0,0,COUNTA(wrksht!$B$7:$B$16),1)
5. Do pole Vztahuje se k napište vzorec OFFSET, který vypočítá rozsah dat Date. Klikněte na tlačítko Ok.
Pomocí tohoto vzorce OFFSET můžete zadat další data kdekoli v rozsahu B7:B16, pokud jsou nová data na konci starého seznamu dat bez prázdných buněk mezi daty.
Nyní zopakujte stejný postup pro vytvoření názvů rozsahů pro data Prodej a Jednotky.
Názvy a vzorce, které doplníte, jsou následující,
Název rozsahu dat se vztahuje k
Prodej rngVSales =OFFSET(wrksht!$C$7,0,0,COUNTA(wrksht!$C$7:$C$16),1)
Jednotky rngVUnits =OFFSET(wrksht!$D$7,0,0,COUNTA(wrksht!$D$7:$D$16),1)
Klikněte sem a dozvíte se, jak OFFSET počítá velikost názvu dynamického rozsahu. V tomto článku najdete také tipy pro kontrolu názvů dynamických rozsahů.
Klikněte sem a dozvíte se, jakou chybu byste s OFFSETem nikdy nechtěli udělat. Děláte tuto chybu?“
Klikněte pro stažení souboru s výukovým příkladem dynamické změny velikosti grafů pomocí OFFSET a názvů rozsahů. Příklad obsahuje jak horizontální, tak vertikální data.
Vložení názvu rozsahu do vzorce data grafu
Nyní můžete dynamicky měnit grafy vložením vytvořených názvů rozsahů do vzorců, které grafy používají k odkazování na data. Nemějte obavy. Je to velmi jednoduché. Ve skutečnosti vám naučení tohoto postupu dává mystické schopnosti a moc ohýbat jakýkoli graf podle své vůle. (Umožní vám to také opravit grafy, které se pokazí.)
Výše jste vytvořili tři dynamické názvy rozsahů, které odkazují na data používaná grafem,
rngVDate
rngVSales
rngVUnits
Následující nahrazují odkazy na buňky používané grafem dynamickými názvy rozsahů, které jste vytvořili,
1. Nahrazují odkazy na buňky používané grafem. Označte graf kliknutím na jeho okraj.
2. Na kartě Nástroje návrhu grafu klikněte ve skupině Data na nástroj Vybrat data, čímž zobrazíte dialogové okno Vybrat zdroj dat.
Dialogové okno Vybrat zdroj dat vám poskytuje kouzelnou kontrolu nad daty, která graf používá.
3. V části Popisky vodorovných os (kategorie) klikněte na tlačítko Upravit, čímž zobrazíte okno Popisky os. V tomto dialogovém okně je těžké vidět vzorec, proto klikněte na ikonu selektoru vpravo od odkazu na buňku. Tím se zobrazí širší okno Popisky os, ve kterém můžete uvést odkaz na vzorec. Před úpravami si dávejte POZOR!!!
V okně Štítky osy se zobrazí odkaz na vodorovnou osu.
4. DÁVEJTE POZOR! Než se pokusíte vzorec upravit, klikněte uvnitř vzorce a stiskněte klávesu Upravit, F2, abyste mohli vzorec upravit. Stisknutím klávesy End přejdete na konec vzorce. Nahraďte odkazy na buňky za znakem ! názvem rozsahu, jak je znázorněno na tomto obrázku:
Váš vzorec by nyní měl vypadat takto,
=’Dynamicky změnit velikost grafů na data.xlsx‘!rngVDate
Po stisknutí klávesy F2 a úpravě vzorce tak, aby obsahoval název rozsahu Datum, vypadá pole Popisky os takto.
TIP: Pokud při úpravě vzorce uděláte chybu, stiskněte klávesovou zkratku Ctrl+Z (Undo), abyste se vrátili zpět.
5. Pokud se při úpravě vzorce spletete, stiskněte klávesu Ctrl+Z (Undo). Klepnutím na tlačítko Ok se vrátíte do dialogového okna Výběr zdroje dat, abyste mohli přidat názvy Prodej a Jednotky.
Vložení názvu rozsahu do vzorce Prodej a Jednotky grafu
Téměř stejným postupem nyní nahraďte vzorce osy Prodej a Jednotky názvy rozsahů.
1. Klepněte na tlačítko OK. V dialogovém okně Výběr zdroje dat klikněte na tlačítko Prodej a na tlačítko Upravit, čímž zobrazíte dialogové okno Upravit řadu.
Nahraďte odkaz na buňku Prodejdata jejím názvem rozsahu.
2. Klikněte na ikonu výběru vpravo od vzorce Hodnota řady, který odkazuje na řadu Prodej.
3. Klikněte uvnitř vzorce, stiskněte klávesu F2 pro úpravu a změňte odkazy na buňky pro data Sales na název rozsahu, rngVSales.
Graf nyní používá dynamický název rozsahu pro odkaz na data Sales.
.