Excel úkol

1) Import a příprava dat jako „tabulky“

• Stáhněte soubor zde
• Otevři stažený soubor a na listu s objednávkami (Orders) označ libovolnou buňku v datech.
• Převeď data na Tabulku (Ctrl+T), zaškrtni „Tabulka obsahuje záhlaví“.
• Pojmenuj tabulku (Návrh tabulky → Název tabulky) třeba tOrders.
• Zkontroluj datové typy VŠECH! sloupců podle jejich obsahu (např. Order Date, Ship Date, … jako Datum; Sales, Profit jako Měna; Discount jako procenta, atd…).

2) Nastavení parametrů:

• Vytvoř nový list Nastavení a zadej:
  • B2: Kurz USD→CZK (např. 23,50)
  • B3: DPH (např. 21%)
  • B4: Prah „OK marže“ (např. 10%)
  • B5: Prah „Skvělá marže“ (např. 25%)
  • Do sloupce A přidejte záhlaví (tj. popisek hodnot, např. A3 bude „DPH „)
• Tyto buňky budeš ve vzorcích odkazovat absolutně (tj. tak aby při roztažení nedocházelo ke změně adresy).

3) Do tabulky tOrders přidej tyto nové sloupce a vyplň je pomocí vzorců:

• Rok objednávky
• Měsíc (číslo 1–12) objednávky
• Měsíc_klíč: první den měsíce – tj. pro objednávku uskutečněnou 17.11.1989 bude vypočtená hodnota 1.11.1989
• Dodání_dní: kolik dní trvala dodávka, tedy rozdíl data objednávky a dodání
• Tržby_CZK: hodnota tržeb (Sales) v korunách, použij absolutní odkaz na kurz v listu Nastavení
• Zisk_CZK: hodnota zisku (Profit) v korunách, opět stejný postup
• Marže_%: spočítej marži (poměr zisku a tržeb) a ošetři dělení nulou
• Ceníková_cena: cena za kus uvedená ve sloupci Unit Price je po slevě Discount – spočítej, jaká byla původní cena

4) Tvorba vlastní funkce (LAMBDA výraz)

• V Excelu vytvoř vlastní funkci přes Správce názvů:
  • Vzorce → Správce názvů → Nový
  • Název: TRIDA_MARZE
  • Odkazuje na (vzorec):
    =LAMBDA(m; KDYŽ(m<0;"Ztráta"; KDYŽ(m<Nastavení!$B$4;"Nízká"; KDYŽ(m<Nastavení!$B$5;"OK";"Skvělá"))))
• Do tabulky přidej sloupec Třída_marže a vyplň jej pomocí právě vytvořené funkce TRIDA_MARZE

5) Vyhledávání

• Na listu Users uprav mapovací tabulku „Region → Manažer“, tak že přepíšeš jména manažerů na česká jména
• Do tabulky objednávek přidej sloupec Manažer a doplň ho pomocí funkce XLOOKUP (nebo SVYHLEDAT) podle regionů z listu Users

6) Filtrování a třídění

• Zapni filtr v záhlaví tabulky (v tabulce už bude).
• Vytvoř list Filtry a kopíruj do něj vždy první tři řádky z tabulky po použití každého z těchto filtrů (vždy nech jeden řádek prázdný a napiš, o který filtr se jedná):
  1. Region = West a seřazeno podle Zisk_CZK od nejvyššího (tj. zkopíruješ 3 nejlepší objednávky na západě).
  2. Třída_marže = Ztráta a seřaď podle Tržby_CZK od nejvyššího (tj. zkopíruješ 3 největší „průšvihy“).
  3. Vyfiltruj Rok = nejnovější rok v datech a seřaď podle Sales (tj. zkopíruješ 3 největší tržby).

7) Kontingenční tabulka

• Vlož Kontingenční tabulku z tabulky objednávek na nový list Kontingenčka:
  • Řádky: Category, pod to Sub-Category
  • Sloupce: Region
  • Hodnoty: součet Tržby_CZK, součet Zisk_CZK, průměr Dodání_dní

8) Grafy

• Z kontingenční tabulky vlož kontingenční graf:
  • Např. sloupcový graf „Tržby_CZK podle Category“ (s filtrem přes Segment).
• Uprav graf (editace):
  • Přidej název grafu.
  • Naformátuj osu na měnu CZK (bez desetinných míst).
  • Zapni datové popisky aspoň u jedné datové řady.
  • Přesuň legendu (např. dolů) a uprav velikost grafu tak, aby se vešel na stránku pro tisk.

9) Tisk a předtisková příprava

• Vyfiltruj kontingenční tabulku jen na Corporate segment v regionu Central
• Nastav:
  • Oblast tisku tak, aby na jedné stránce byla tabulka a na druhé graf
  • Orientaci Na šířku, okraje „Úzké“.
  • Přizpůsobit měřítko: 2 stránky na šířku
  • Přidat Záhlaví: název „Superstore report“ tučně velkým vlevo a datum tisku malým vpravo.