Manažerská informatika
Domů  |  3MA112  |  3MA191  |  3MA381  |  3MA382  |  3MA481   
Osnova cvičení Úvodní informace Podklady ke cvičením Semestrální práce Samostatné aktivity Hodnocení Odkazy

Funkce POSUN - příklady

Funkce POSUN se dá dobře použít pro adresování dynamických oblastí. Zde je několik jednoduchých příkladů pro pochopení její funkčnosti.

Sečtení pěti posledních čísel ze sloupce

Dejme tomu, že do sloupce A se nám automaticky načítají data z externího zdroje. Na jiné místo v listu bychom potřebovali sečíst posledních pět čísel z tohoto sloupce.

      A B
1 23 227
2 12  
3 95  
4 27  
5 36  
6 50  
7 50  
8 28  
9 94  
10 34  
11 18  
12 67  
13 18  
14 21  
15 26  
16 95  
17    

Protože neznáme adresu poslední načtené buňky, můžeme si pomoci následovně:

  1. Pomocí funkce POČET zjistíme počet čísel ve sloupci A (pro zjednodušení budeme předpokládat, že jde o kontinuální oblast hodnot).
  2. Od získaného počtu čísel odečteme číslo 5, abychom určili horní levý roh sčítané oblasti.
  3. Funkci POSUN, která nám vrací oblast pěti buněk zadáme jako jediný argument funkce SUMA.

Vzorec pro součet posledních pěti čísel ve sloupci A by mohl vypadat následovně:

=SUMA(POSUN(A1;POČET(A:A)-5;0;5;1))

Sečtení pěti čísel nad aktivní buňkou

Pokud např. často potřebujeme zapisovat vzorec pro součet pěti buněk, které se nacházejí nad buňkou, do které chceme výsledek zapsat, můžeme si zápis zjednodušit.

  1. Definujeme název (nabídka Vložit - Název  - Definovat).
  2. Zapíšeme nějaký název oblasti - jednoduše půjde např. zapsat VBN, protože klávesy jsou na klávesnici vedle sebe (VBN může znamenat V jako římská pětka, B jako buněk, N jako nad, takže je název snadný i na zapamatování).
  3. Do pole Odkaz na zapíšeme funkci pro adresování oblasti pěti buněk nad aktivní buňkou
    =SUMA(POSUN(A1;-5;0;5;1))
  4. Kamkoliv do šestého řádku nebo níže zapíšeme vzorec =VBN a výsledkem bude součet odpovídajících pěti buněk.

V definici názvu bylo zadáno sčítání pěti buněk nad buňkou A1, což asi není to co od funkce očekáváme. Pokud je ale odkaz na buňku v prvním argumentu funkce POSUN zadán relativně, pak bude relativně adresována i sčítaná oblast buněk. Pokud bychom zapsali vzorec =VBN kamkoli do prvních pěti řádků, pak by funkce sčítala oblast na samotném konci listu, kde žádné údaje pravděpodobně nemáme.

Dynamické adresování grafu

Pokud máme tabulku s více údaji než je vhodné zobrazit v jednom grafu, pak můžeme chtít použít pro graf pouze část takových dat. Jednou z možností je měnit zdrojová data grafu ručně, ale možná pohodlnější bude adresovat zdrojová data grafu pomocí pojmenovaných oblastí a ty pak jednoduše ovládat.

Vyjdeme ze zdrojové tabulky:

Dynamický graf - zdroj

V buňkách D15 a F15 je naznačen počáteční a koncový měsíc, který chceme zahrnout do grafu.

Pro zkonstruování dynamického grafu budeme potřebovat nejprve nadefinovat názvy oblastí, jejichž adresa se bude měnit právě podle hodnoty v buňkách D15 a F15.

Definované názvy:

Nyní již můžeme sestrojit graf. Jako zdrojová data grafu můžeme použít jakoukoli oblast dat. Následně zdrojová data grafu přepíšeme definovanými názvy.

Dynamický graf - nastavení

Názvy musíme zapsat včetně adresy sešitu (v tomto případě dg.xls). Pokud chceme generovat i popisek grafu, pak si do libovolné buňky (v tomto případě M4 na listu graf) připravíme odpovídající funkci.

="Rozmezí "&POSUN(A1;D15;0)&" - "&POSUN(A1;F15;0)

Dynamický graf - měsíce

Alternativně bychom mohli názvy za adresy substituovat v řádku vzorců po označení datové řady grafu. V tomto případě by měla funkce SADA tyto argumenty:

=SADA(graf!$M$4;dg.xls!popis_osy;dg.xls!rozmezi;1)

Kombinace funkce POSUN a POZVYHLEDAT

V tomto příkladě vyjdeme z obdobné tabulky jako v minulém příkladu s tím rozdílem, že je tabulka transponovaná. Chceme vytvořit graf, který bude zobrazovat dvě datové řady - Aktuální měsíc a Vybraný měsíc.

Dynamický graf - zdroj

Postup

Vytvoříme si pomocnou tabulku, která bude mít dva sloupce

Položky aktuálního měsíce dosadíme pomocí vzorce:

=POSUN(A1;1;MĚSÍC(DNES());1;1)

Při použití relativně adresované buňky A1 můžeme vzorec zkopírovat i do zbylých osmi buněk.

Do záhlaví sloupce Vybraný měsíc naimportujeme např. pomocí ověření dat seznam měsíců pro snadné přepínání mezi měsíci.

Dynamický graf - pomocná tabulka

Pomocí funkce POZVYHLEDAT doplníme do pomocné tabulky hodnoty z příslušného sloupce z původní tabulky. Pokud je název vybraného měsíce v buňce C13, pak bude vzorec pro nalezení příslušných hodnot vypadat následovně:

=POSUN(A1;1;POZVYHLEDAT($C$13;$B$1:$N$1;0);1;1)

Do dalších buněk sloupce Vybraný měsíc nakopírujeme vzorec pro vypsání ostatních hodnot. Na základě pomocné tabulky vytvoříme graf.

Dynamický graf - výsledek

Ke stažení

 

© 2007–2013 Miroslav Lorenc

Převzetí, publikování, kopírování, či jiné užití než pro osobní potřebu je možné pouze s písemným svolením autora.