Archiv rubriky: Excel

xl02 Absolutní adresování

Moodle 2013, aktualizace 8. 2. 2021, absolutní adresa, smíšené adresování, Excel

Absolutní adresování

Pokud kopírujeme výraz do jiné buňky je vždy odkaz na nové pozici upraven o patřičný počet řádků a sloupců. Proto vyplněním (zkopírováním) do buněk směrem dolů je odkaz na buňku A4 upraven na A5 a odkaz na B1 je upraven na B2. V případě buněk ze sloupce A je to vítaná změna. Ale v případě B1 to nechceme. Chtěli bychom buňky vyplnit, abychom nemuseli psát vzorec pokaždé znovu. Použili jsme proto ve vzorci absolutní adresu $B$1.

  • Napíšeme vzorec =A4*B1 do buňky B4, ukončíme ENTER.
  • Dvojklikem vyplníme. Ejhle chybová hlášení nebo nesmysly.
  • Smazat.
  • Pomocí F4 upravíme relativní B1 na absolutní adresu $B$1.
  • Dvojklikem vyplníme. 
  • Výsledek je v pořádku v celé tabulce. Ve všech vzorcích je $B$1.

Pro názornost si můžete prohlédnout videonávod s příkladem převodu měny € na Kč.

Zápis symbolu $

Abychom nemuseli psát symbol dolaru na klávesnici je v Excelu i v Calcu připravena pomůcka, která přidává symbol $ .

EXCEL … klávesa F4
CALC … klávesy SHIFT+F4

Opakovaným stiskem F4 (SHIFT+F4) se cyklicky stále dokola mění
B1 ~ $B$1 ~ B$1 ~ $B1 ~ B1

B1     relativní adresa
$B$1 absolutní adresa
B$1   smíšená: sloupec relativní, řádek absolutní
$B1   smíšená: sloupec absolutní, řádek relativní


Smíšené adresování

V buňce B4 kurzovního lístku by mohl být výraz A4*B$1.
Tento zápis považujeme za smíšené adresování.
Protože vyplňujeme (kopírujeme) pouze směrem dolů, nemá absolutní či relativní odkazování na sloupec B vliv na výsledek.

V následujím případě však je nejvhodnější a nejefektivnější forma zápisu pomocí smíšeného adresování.
Proč?

  • Výraz v buňce D3 zapíšeme jen jedenkrát a vyplníme doprava do dalších dvou buněk E3 a F3.
  • Výpočet se provádí vždy z původní plné ceny.
  • Potřebujeme neměnný odkaz na tomto řádku na cenu v C3, ale relativní při plnění dolů C4 atd. Proto smíšená adresa $C3.
  • Nyní, dokud mám označeny 3 buňky D3, E3 a F3 dvojklikem vyplním potřebnou délku všech tří sloupců. Právě teď však potřebuji absolutní odkaz na slevu v prvním řádku. Proto smíšená adresa D$1 (taky E$1 a F$1).
  • Ceník může mít mnoho set řádků, tažení není vhodné. Dvojklik!
  • Výraz $C3-$C3*D$1 se mění na témže řádku na $C3-$C3*E$1 a $C3-$C3*F$1
  • Vyplněním dolů např. ve sloupci F na $C4-$C4*F$1, pak na $C5-$C5*F$1 atd.

xl Soupis učiva

Aktualizováno 7. 2. 2021

Motto: “Cílem není ovládat Excel, ale použít tento nástroj v životních situacích.” 
Pokusím se o vylepšení vaší finanční gramotnosti a o naučit dovednost vizualizovat a prezentovat data. V souvislosti s tím rozpoznat Fake News – zneužití upravených grafů. 

Soupis učiva Excel

Soupis je určen pro začátečníky, tj. pro 1. ročníky SŠ. Po úžasném zjištění a zděšení je vhodný i pro VOŠ. Co asi dělali na střední škole?

Dokument se bude dále vyvíjet. Je šitý horkou jehlou. Pořadí v textu není pořadí ve výuce. Některé dovednosti a užití funkcí budeme postupem času opakovat a rozšiřovat.  

Excel bude také ve 2. ročníku, budou v plánu kontingenční tabulky a grafy, složitější grafy, pokročilé filtrování, makra ve Visual Basicu a další. 

Snaha o soupis všech probíraných funkcí: 

Zde jsou patrně všechny funkce, co jsme letos probrali nebo budeme probírat: 

  • SUMA, MAX, MIN, PRŮMĚR, POČET, POČET2 
  • NYNÍ, DNES, PI
  • ZAOKROUHLIT, CELÁ.ČÁST, OPAKOVAT, RANDBETWEEN 
  • SIN, COS apod. 
  • KDYŽ 
  • POWER, ODMOCNINA, A (AND), NEBO (OR), 
    PRAVDA, NEPRAVDA, FALSE, TRUE 
  • BUDHODNOTA, PLATBA 

Dovednosti, ovládání, pojmy, formátování: 

  • Panely nástrojů, stavový řádek, řádek vzorců/vstupní řádka, … 
  • Formátování buněk zalomit text, přizpůsobit, … 
  • Skrýt sloupce a zobrazit skryté sloupce (nebo řádky) 
  • Slučování buněk
  • Průvodce funkcemi a nápověda 
  • V buňce je: číslo, text nebo výraz, text zarovnán vlevo a čísla vpravo 
  • Operátory (aritmetické, logické, relační, …): + – / * ^ % & <> <=… 
  • Klávesové zkratky pro pohyb v tabulce a označení souvislé či nesouvislé oblasti 
  • Editace výrazu v buňce, klávesou F2, ukončení Enter nebo klik myší?
  • Operátor mocniny a funkce druhá ODMOCNINA(číslo) nebo taky =A2^(1/2) 
  • Obecný zápis jakékoliv funkce, argumenty, oblast buněk, výčet buněk, žádný argument, výraz jako argument 
  • Ohraničení, barva písma a pozadí 
  • Vložit/odstranit sloupce/řádky, výška/šířka, dvojklik na přizpůsobení 
  • Absolutní a relativní adresa (F4), typické příklady použití
    Příklad: násobilka, vyplňování, kopírování výrazů, relativní adresy, výhody a použití dvojkliku při více sloupcích 
  • Nejjednodušší výraz =adresa_buňky například =A1 použitá ke zobrazení obsahu jiné buňky 
    složitější varianty – zobrazit buňku z jiného listu či souboru 
  • Formátování čísel, datum, čas, měna, procenta, … 
  • Grafické znázornění průběhu funkce 
  • Rozdělit okno, ukotvit okno 
  • Práce s listy, zejména kopírovat/přesunout do/z jiného souboru 
  • Vložit tabulku z Excelu do Wordu, převádět text na tabulku a naopak, 
    vložit jinak .. (3x jinak: neformátovaný a formátovaný text, jako objekt Excelu) 
  • Zásadní rozdíl „zaokrouhlení“ pouze formátem a skutečným zaokrouhlením pomocí funkce ZAOKRUHLIT(co; počet deset. míst)
  • Seřadit podle abecedy nebo čísel 
  • Chybová hlášení 
  • Automatické tvary, SmartArty, obrázek apod. 
  • Poznámky (komentáře) 
  • Ověření vstupních hodnot – seznamy
  • Standardní, automatický a rozšířený filtr 
  • Hledání řešení na příkladu s funkcí budoucí hodnota, příklady na funkci budoucí hodnota pravidelného spoření + hledání řešení 
  • Funkce „pro opakování“ OPAKOVAT (co;kolikrát), 
  • ASCII tabulka, Webdings, Wingdings 
  • Ovládací prvky formulářů (posuvníky, zaškrtávací pole, pole se seznamem apod.
  • Záhlaví a zápatí, předtisková příprava, náhled 

Jak na to:

  • Jak na výpočty průměrů známek, jak počty na žáků a počty známek (POČET x POČET2), jak na nejlepší a nejhorší ve třídě 
  • Jak na výpočty „s datumy“ Kolik dní, hodin a minut …? 
  • Jak na grafické znázornění jednoduchých matematických výrazů (číselná řada, vhodný graf, vhodné vstupní údaje)
  • Jaký grafický průběh funkce má výraz y = 5 sin(x) + 3 x2 + 8
  • Jak na kalendář, vyplňování buněk 
  • Jak vysoký nájem si můžu dovolit, když mám jen 10000 Kč? Jaký jsou rozměry, když chci objem nádrže 100 litrů? (hledání řešení – reverzní výpočty) 
  • Jak dlouho budu spořit 20000 Kč? Jakou částku mám spořit abych našetřil za 2 roky 20000 Kč? 
  • Jakou částku bych naspořil s úrokem 2 % p.a., když bych přestal kouřit?

xl01 Relativní adresování

únor 2013, Moodle, Excel, adresování buněk

Před sto lety, když jsem učil na obchodní akademii, studentky 2. ročníku začaly zapisovat do tabulky čísla – výsledky výpočtů. Chvíli mi trvalo, než jsem to rozchodil.

Adresování buněk a princip práce

Tabulkové procesory jsou excelentní programy a jsou pravou rukou každého, kdo pracuje hlavou. Zejména v ekonomické a technické oblasti činností člověka. Základem jsou obyčejné „kupecké počty“.

Aby vůbec bylo možno v tabulkovém procesoru pracovat, jsou sloupce označeny písmeny a řádky čísly. Jednotlivé buňky pak jejich souřadnicemi jako v křížovkách nebo ve hře na lodě.
Například A1 obsahuje text malá násobilka a buňka B2 číslo 5.
Zápisu A1 nebo B2 se říká odkaz na buňku nebo adresa buňky.

Pokud je buňka vybraná – bylo na ni kliknuto myší a je orámovaná,
říká se jí taky aktivní a její adresa je v levém horním rohu tabulky.

Co může být zapsáno v buňkách?

  • text
  • číslo
  • výraz (kombinace funkcí, čísel, textů a operátorů)
Nepište výsledky ani čísla. Pište adresy buněk. Všude, kde to dává smysl. To je základ!
A1text: malá násobilka
A2 a B2čísla 1 a 5
C2výraz: násobení buněk A1 a B2

Princip práce tabulkového procesoru

V žádném případě nepište do buňky C2 číslo s výsledkem 5 nebo výraz s čísly =1*5
Zásadně pište jen odkazy na buňky A2 a B2. Zobrazí se výsledek.
Pokud změním v buňkách A2 nebo B2 hodnotu, v C2 se výsledek automaticky přepočítá.
Toto je PRINCIP práce tabulkových procesorů.

Relativní adresování

Na příkladu tabulky s malou násobilkou bude vysvětlen pojem relativní adresa buňky (relativní odkaz).

  • Sledujte řádek pro editaci nahoře vpravo
  • Vyberte buňku C2, vybraná je aktivní
  • Zkopírujeme do schránky obsah C2 (CTRL+C)
  • Posuneme se o jeden řádek dolů
  • Vložíme obsah schránky do C3 (CTRL+V)
  • Neobjeví se „očekávané“ =A2*B2, ale =A3*B3
  • Je to správně, násobí čísla na témže řádku
  • Odkaz nezůstal zafixován na původním místě
  • Odkaz se posunul o jeden řádek
  • Jedná se o RELATIVNÍ ODKAZ (ADRESU)

Je to správný a vítaný způsob adresování buněk. Pokud zkopíruji a někam vložím výraz, tak se změní stejným způsobem i odkazy v něm obsažené.
Jinými slovy o kolik řádků a sloupců se posunu a vložím, o tolik řádků a sloupců se patřičně upraví vkládaný výraz.
Takto normálně pracuje tabulkový procesor.

Pro lepší pochopení ještě přikládám videonávod s příkladem tabulky pro malou násobilku.

Ukázka – malá násobilka

xl00 Odkazy Excel

Odkazy pro Excel

Úžasný zdroj nápadů a informací je na webu office.lasakovi.com. Pan Lasák Pavel, autor webu, je jako jediný v ČR Microsoftem certifikován jako expert (podrobnosti na jeho webu).
Web zabírá celou problematiku MS Office včetně Visual Basicu (dále jen VBA). A právě jsem objevil další oblast Power BI – Business intelligence

RSS Lasák Pavel

Dříve jsem používal dataspectrum.cz pana Jiřího Čihaře. Web doznal značných změn a stejně jako já přešel pod křídla WordPressu. Je specializovaný pouze na Excel.

Echt zajímavý obrázek (Lasákovi) jako ukázka pro semestrální práci.

Pár odkazů


Přidejte smysluplný komentář.

Například odkazy na zajímavé stránky o Excelu.

xl21 Kontingenční tabulky

Lasákovi, grafy, taháky, pivot table

Kontingenční tabulky a grafy

Většina zdrojů pochází z excelentního webu office.lasakovi.com. Cituji je a odkazy vedou většinou na tento web.

K čemu to bude dobré?

Pomocí kontingenční tabulky můžete data analyzovat, provádět souhrny, třídění, výpočty, tvořit kontingenční grafy. Prezentovaná data budou snáze pochopitelná a nejen pro prezentování, ale také pro následné rozhodování. [cit. Lasákovi, Jak na Excel].
Zejména umožní snadno vizualizovat velký počet záznamů (údajů).
BTW anglicky pivot table a česky kontingenční tabalka.

Pro začátek…

Struktura kontingenční tabulky má jednu volitelnou a tři povinné oblasti:

  • Oblast dat
  • Oblast sloupců
  • Oblast řádků
  • Oblast filtr (volitelná)

Příkladem kontingenční tabulky typu 2×2 může být následující smyšlený průzkum zastoupení leváků a praváků mezi ženami a muži.
Wikipedie

pravácilevácicelkem
muži43952
ženy44448
celkem8713100
Wikipedie, kontingenční tabulka

Soubor programy.xlsx obsahuje asi 10 000 záznamů získaných v roce 2005 z přílohy CD časopisu COMPUTER. Data poskytují informace o všech programech z předchozích let s přiřazením typu, jazyka a kdy číslo vyšlo v poli MEDIUM (rok/číslo). Tabulka neobsahuje sice žádná čísla (pro aritmetické použití), ale souhrny, počty podle typu nebo jazyka poskytne velmi přehledně.

Snímek listu ze sešitu PROGRAMY.xlsx

Taháky a soubory s daty

Lasákovy taháky: PDF tahák základy a PDF tahák pokročilé,
Lasákovy data: sešit XLS a řešení.

Soubory z jiných zdrojů k procvičení:
programy.xlsx a sladkosti.xlsx (s čísly)

A je na čase pustit se do procvičování a já přestanu nosit dříví do lesa.


Rozpracováno, doplnit ukázky SS, možná video a hlavně grafy.