Výuka a školení Excelu Výuka a školení Excelu Výuka a školení Excelu
Výuka a školení Excelu Výuka a školení Excelu

pondělí 29. září 2014

Excel a nelineární regrese

V Excelu existuje řada způsobů, jak pracovat s lineární regresí - věnuje se jim tento článek.
Lineární regrese je vztah, kdy závislost dvou proměnných je v grafu vyjádřena přímkou, jinými slovy y = a * x.
Problém ale je, když je závislost nelineární. Tedy např. logaritmická, exponenciální atd.
Je sice možné, podobně jako u lineární regrese, použít doplnění křivky a rovnice do grafu, ale to není příliš přesné a pokrývá to jen vybrané typy závislostí.
V tomto článku si ukážeme, jak vypočítat koeficienty nelineárních závislostí. Ten postup by měl fungovat víceméně na většinu běžných typů závislostí, my si jej ukážeme na závislosti logaritmické.

Příklad

Ve firmě sledují závislost tržeb na investicích do reklamy. Data za dosavadní období jsou popsaná v této tabulce:

a v tomto grafu:

Je patrné, že závislost je v zásadě logaritmická. Tedy že tržby sice rostou s investicemi do reklamy, ale tento růst je pomalejší a pomalejší a od určité úrovně už investice do reklamy nemají na tržby téměř žádný vliv.
Logaritmická závislost je běžně popsaná rovnicí:
y = a * ln(x) + b
V našem případě jsou y tržby a x investice do reklamy. Abychom popsali závislost tržeb na investicích do reklamy, je nutné zjistit hodnoty a a b.
Soubor s daty a nastaveným řešitelem je ke stažení tady.

Návod

Na nalezení koeficientů použijeme nástroj Řešitel. Necháme ho hledat koeficienty a a b tak, aby byl co nejmenší rozdíl mezi hodnotami spočítanými regresní funkcí a skutečnými hodnotami.Tedy aby křivka funkce byla co nejblíže bodům v grafu.
Mimo tabulku si připravíme místo pro koeficienty a a b (žluté buňky) a z nich vypočteme pomocí logaritmické funkce nový sloupec. Ten je zatím prázdný, ale až budou vyplněné hodnoty ve žlutých buňkách, bude ukazovat odpovídající hodnoty.

Do nového sloupce pak spočteme rozdíl skutečných a dopočtených hodnot.

Tento rozdíl za všechny hodnoty chceme minimalizovat - tak, aby nalezená funkce byla co nejblíž bodům ze zadání.. Do nové buňky tedy sečteme druhé mocniny rozdílů.

A jdeme nastavit Řešitele. Je to jednoduché. Minimalizujeme součet mocnin rozdílů tím, že měníme žluté buňky, a nastavíme metodu řešení GRG Nonlinear.
Klikneme na Solve a je hotovo.

Ve žlutých buňkách jsou teď koeficienty rovnice.
O tom, že jsou správné, se můžeme přesvědčit, když do grafu zahrneme původní i dopočítané hodnoty. Vidíme, že dopočítané červené body jsou přibližně na stejné křivce jako skutečné modré.


  • Tento výsledek má zcela praktické využití. Pokud bych chtěl v budoucím období investovat do reklamy např. 180, mohu očekávat tržby ve výši 799 * ln (180) + 2408, tedy 1742.
  • Se znalostí této závislosti a se znalostí podílu zisku na tržbách by teď už bylo snadné určit, která velikost investic do reklamy maximalizuje zisk firmy.
  • Je dobré uvědomit si, že hodnota v buňce D12, součet čtverců rozdílů, je zajímavá nejen pro řešitele. Umožnila by totiž porovnat, jak moc vhodné jsou různé typy závislostí. Nejlepší bude zřejmě ta závislost, která bude mít v této buňce nejnižší hodnotu.
  • Koeficienty, které jsme v tomto případě zjistili řešitelem, lze získat i kombinací funkcí.
    Koeficient "a" zjistíme vzorcem =INDEX(LINREGRESE(B2:B11;LN(A2:A11));1;2)
    Koeficient "b" zjistíme vzorcem =INDEX(LINREGRESE(B2:B11;LN(A2:A11));1)
    Je ale zajímavé, že funkce nám dá sice podobné výsledky jako řešitel, ale přeci jen o trošku méně přesné. Přeci jen je to ale přesnější než to, co se ukáže po přidání spojnice v grafu.
  • Pokud bychom chtěli najít koeficient determinace (to, co se u grafu zobrazí jako R2), použijeme vzorec =RKQ(B2:B11;LN(A2:A11))
Soubor s daty a nastaveným řešitelem je ke stažení tady.

TRIM / PROČISTIT

Nadbytečné mezery jsou věc, která umí jít v Excelu docela pěkně na nervy.
Mezery před texty, vícenásobné mezery mezi texty a hlavně mezery za texty, které nejsou vidět.

Příklad

Nám je např. zřejmé, že slova "Ústí nad Labem" a "    Ústí   nad Labem   " označují stejné město. Excel to ale jako jednu hodnotu samozřejmě nerozezná - kvůli mezerám.

Návod

Naštěstí existuje funkce TRIM / PROČISTIT, která provádí následující tři kroky:
  • Odstraní všechny mezery na začátku textu
  • Odstraní všechny mezery na konci textu
  • Pokud se mezi částmi textu vyskytuje několik mezer za sebou, jsou změněny na jednu mezeru.
Z "    Ústí   nad Labem   " tak funkce udělá spořádané "Ústí nad Labem".
Funkce má jen jeden parametr - text, který "čistím". Zápis je tedy velmi jednoduchý:
=TRIM(A1)




neděle 28. září 2014

Pokladna v Excelu

Příklad

Na kurzech pro začátečníky občas řešíme, jak v Excelu vytvořit něco jako pokladní knihu, přehled položek na účtu, skladovou evidenci... zkrátka tabulku s příjmy, výdaji a automaticky spočteným aktuálním stavem.

Návod 

Není nic snadnějšího. Data, příjmy a výdaje se prostě vypisují. Jde o výpočet stavu. Do první buňky stavu napíšu:
  • =B2
    protože první řádek stavu je vlastně počáteční stav.

Do další buňky pak napíšu a dolů roztáhnu:
  • =D2+B3-C3
    protože každý další řádek se spočte tak, že se k řádku nad buňkou připočte příjem a odečte se výdej. Protože v praxi bude vždy v řádku zapsaný jen výdej nebo příjem, provede se buď přičtení nebo odečtení.


sobota 27. září 2014

Funkce NEPŘÍMÝ.ODKAZ / INDIRECT

Funkce NEPŘÍMÝ.ODKAZ mění textový řetězec na odkaz. A to buď na odkaz definovaný souřadnicemi, nebo na odkaz definovaný názvem oblasti.

Definice souřadnic odkazu

Například zde:

Chci, aby uživatel v modrých buňkách zadával souřadnice buňky, ze které se má získat hodnota. V šedé buňce je pak vzoreček:
=NEPŘÍMÝ.ODKAZ(CONCATENATE(F1;F2))
Tedy nejprve pomocí funkce CONCATENATE získám z textů "B" a "3" text "B3". Ten pak funkce NEPŘÍMÝ.ODKAZ převede na odkaz, a ve výsledku je osmička - hodnota z buňky B3.

Definice názvu oblasti

Funkce NEPŘÍMÝ.ODKAZ funguje i když je odkaz definovaný názvem oblasti.
Například v tomto příkladu, si mohu dvě oblasti čísel pojmenovat "modrá" a "zelená".

Pak je možné do růžové buňky napsat název oblasti a pomocí funkce suma vysčítat tu oblast, která je zapsaná.

Oba způsoby použití funkce NEPŘÍMÝ.ODKAZ jsem se snažil vysvětlit tak, aby bylo jasné, jak to funguje. Na druhou stranu - osobně jsem je myslím nikdy nepoužil a nezdá se mi to úplně praktické. Existuje ale jeden velmi zajímavý způsob použití funkce NEPŘÍMÝ.ODKAZ - a to pro vytváření kaskádových vybíracích menu.

pátek 26. září 2014

Generování náhodných čísel s funkcí RAND / NÁHČÍSLO

Příklad

Potřebuji vygenerovat zcela náhodné číslo mezi nulou a jedničkou - například pro nějakou simulační úlohu.

Návod

Použiji funkci NÁHČÍSLO, anglicky RAND.
Funkce má zápis:
  • =NÁHČÍSLO()

Platí, že:

  • Čísla generovaná funkcí NÁHČÍSLO jsou změněna při každém přepočítání sešitu
  • Náhodné číslo má patnáct desetinných míst - tedy pracuje s přesností miliardtin milionů.
  • Někdy se víc než funkce náhodné číslo hodí funkce RANDBETWEEN, která generuje celá čísla v zadaném rozsahu
  • Čísla jsou generovaná v rovnoměrném rozložení - neměli bychom je tedy v simulačních metodách používat pro simulaci veličin popsaných např. normálním rozložením.

čtvrtek 25. září 2014

Zajímavost s porovnáváním číselných kódů

V tabulce máte tato dvě čísla naformátovaná jako text:
  • 1111111111111119999
  • 1111111111111110000
Myslíte, že tato čísla nejsou stejná? Excel je jiného názoru.


Vida, Excel obě čísla vyhodnotil jako duplicitní.
Stejně se zachová třeba funkce COUNTIF (nebo SUMIF) - tvrdí, že první číslo je v tabulce použité dvakrát.


To už nám může způsobit docela problém - protože funkce v našem případě bude házet naprosto nesmyslné hodnoty a je těžké si toho všimnou.
Je zajímavé, že např. funkce SVYHLEDAT/VLOOKUP nebo POZVYHLEDAT/MATCH fungují správně (na obrázku MATCH ukazuje, že hledané je na druhé pozici)....


...a současně přímé porovnání výsledků také správně ukáže, že hodnoty jsou odlišné.


Pro COUNTIF Excel očividně sleduje jen prvních 15 znaků (pokud se čísla liší na prvních 15 znacích, tak se problém neprojeví). 
Uvedený problém vypadá jako maličkost, ale např. u kódů zboží, kde se běžně jedná o dlouhé číselné řetězce, může vést k naprosto nemyslným výsledkům - o to zákeřnějším, že se projeví jen na některých položkách.
Náprava je přitom snadná - stačí vytvořit a pro porovnání použít (třeba funkcí CONCATENATE) pomocný sloupec, kde budou čísla změněna na:
  • 1111111111111119999x
  • 1111111111111110000x
a vše funguje tak jak má.

sobota 20. září 2014

DZÍSKAT (DGET)

V jednom z minulých článků jsme se seznámili s databázovými funkcemi. Většina z nich funguje podobně jako funkce typu SUMIFS, COUNTIFS apod. (tedy použijí určitou agregující funkci nad položkami odpovídajícími filtru), jen se v nich jinak definují kritéria.
Existuje ale jedna databázová funkce, která se vymyká, a to funkce DZÍSKAT (anglicky DGET).
Tato funkce neagreguje (nesčítá, neprůměruje, nepočítá) hodnoty, ale vybere konkrétní hodnotu. Tedy její fungování je spíše podobné funkci SVYHLEDAT.

Příklad

Chci najít jméno zaměstnance, který je muž a pracuje v oddělení HR, v následující tabulce:

Návod

Připravím si tabulku kritérií (více o nich v kapitole o rozšířených filtrech):


A zapíšu funkci:
=DZÍSKAT(A:E;"Jméno";G1:H2)


Prvním parametrem je umístění databáze, druhým parametrem označení sloupce který mě zajímá, a třetím parametrem tabulka kritérií.
Ve výsledku zjistím, že hledaným zaměstnancem je Karel:

Pozor. Pokud funkce DZÍSKAT najde více hodnot odpovídajích filtru, vrátí chybovou hodnotu - narozdíl od funkce SVYHLEDAT, která vrátí první nalezenou hodnotu.

Hromadné pojmenování oblastí

V Excelu lze pojmenovat oblast buněk tak, abychom se na ni pak mohli jednodušeji odkazovat. Pokud např. pojmenujete oblast mezi A1 a B10 názvem "moje oblast", můžete ji sčítat vzorcem "=SUMA(mojeoblast)" místo vzorce "=SUMA(A1:A10)". To je v některých případech jednodušší, navíc se to často hodí při navazujícím použití funkce NEPŘÍMÝ.ODKAZ(INDIRECT), třeba pro tvorbu kaskádových vybíracích menu.
V některých případech ale chceme pojmenovat více oblastí a nechceme to dělat postupně. Pak je možné použít hromadné pojmenování.

Příklad

V těchto datech chceme např. označit sloupce "jméno", "příjmení", "telefon", "oddělení".


Návod

Označíme všechny sloupce v celé délce, do které mají sahat pojmenované oblasti.

V kartě Vzorce klikneme na Vytvořit z výběru.



V našem případě zaškrtneme "Horní řádek".


A je hotovo. To, že se oblasti skutečně vytvořili, můžeme ověřit v roletce po kliknutí na šipečku s názvy:




pondělí 8. září 2014

Vytvoření seznamu unikátních náhodních celých čísel

Excel používá pro generování náhodných čísel v zásadě dvě funkce - NÁHČÍSLO/RAND (generuje náhodné mezi nulou a jedničkou s mnoha desetinnými místy) a RANDBETWEEN (generuje celá čísla v rozsahu, který zadáme).
Někdy ale potřebujeme v každé buňce jiné náhodné celé číslo - resp. určitý počet čísel v náhodném pořadí.

Návod

Je možné v jednom sloupci použít funkci NÁHČÍSLO (v anglické verzi RAND).


Ve dluhém sloupci pak pomocí funkce RANK zjistit pořadí tohoto čísla.


A ve výsledku máme čísla od jedné do deseti v náhodném pořadí.
Na základě tohoto sloupce je samozřejmě možné i řadit - ale předtím je ještě nutné v tomto sloupci převést sloupce na hodnoty, aby se čísla stále neměnila.

středa 3. září 2014

Kontingenční tabulka počítající unikátní, jedinečné hodnoty

V jednom reportu jsem narazil na zajímavý problém. Měl jsem seznam jazykových kurzů ve škole. Ve sloupečcích bylo jméno lektora, jazyk kurzu a nějaký identifikátor kurzu.
Vypadalo zhruba takto:

Úkolem bylo zjistit, kolik jazyků který lektor vyučuje. A zjistit to kontingenční tabulkou. Zjistit, kolik kurzů lektor vyučuje, nebo které jazyky vyučuje, by zabralo asi tak vteřinu a půl. Pokud ale chci, aby tabulka vypadala takto:

tak už to taková legrace není. Prošel jsem nějaké návody na webu a následující postup se mi zdál nejsnazší.
Je třeba vytvořit si a do tabulky s daty použít nový sloupec, a tam zapsat takovýto (nebo obdobný) vzorec:
=IF(COUNTIFS($B$1:B2;B2;$C$1:C2;C2)=1;1;0)
Vysvětlení:
Výstupem je 0 nebo 1, což závisí na dvou kritériích - na jméně lektora a na jazyce. Protože tato kritéria mají ve funkci COUNTIFS mezi sebou vztah AND (tedy musí platit obě), je výstupem funkce COUNTIFS v určitém řádku celkový počet řádků tohoto lektora na tomto jazyce. U buňky Novák / Angličtina je to počet buněk Novák / Angličtina ve sledované oblasti.
Všimněte si, že oblast funkce COUNTIFS je díky šikovným absolutnm odkazům "roztahovací" a sama funkce tedy zjišťuje, kolikrát se tato kombinace lektora a jazyka objevila pouze odshora až k příslušnému řádku (tedy ne v celé oblasti, ale jen od buňky nahoru).
S použitím samotné funkce COUNTIFS by byl výsledek takovýto:
My si ale pro kontingenční tabulku potřebujeme připravit data tak, aby se za každou kombinaci lektora  a jazyka "pričetla" jen jednička.
Proto z funkce COUNTIFS vytvoříme podmínku - její výsledek budeme porovnávat s jedničkou. Funkce tedy bude zobrazovat 1 v případě, že se jedná o první výskyt, a 0 v ostatních případech.
Tím zaručíme, že v novém sloupci bude 1 pouze u prvního výskytu, a jinde budou nuly.
Výsledek bude vypadat takto:

Když pak poslední sloupec dáme do pole hodnot kontingenční tabulky, číslo bude ukazovat, kolik jazyků lektor vyučuje.

úterý 2. září 2014

Tři způsoby zápisu odkazu na buňku v Excelu

V každém vzorci nebo funkci v Excelu se potřebujeme odkazovat na buňku. Existuje více způsobů, jak na buňku odkazovat - v tomto článku si ukážeme tři z nich.

Příklad:

V následující tabulce mám několik druhů zboží. 

 U každého mám počet kusů a cenu za kus, v posledním sloupci potřebuju získat celkovou cenu. Výpočetně se jedy jedná o naprosto jednoduchou věc - ve sloupci D chci vynásobit to, co je ve sloupci B a to, co je ve sloupci C.
Takto nějak bude vypadat výsledek, ať použijeme jakýkoliv způsob zápisu:


1. Odkaz na jednu buňku s roztažením vzorce

První způsob je nejčastěji používaný. Do buňky D2 napíšeme:
=B2*C2
a vzorec roztáhneme.

Místo zapsání "B2" a "C2" samozřejmě můžeme také na buňky kliknout.

2. Odkaz na celý sloupec

Do buňky D2 můžeme také zapsat:
=B:B*C:C
a vzorec roztáhneme.

Místo zapsání "B:B" a "C:C" můžeme také kliknout na záhlaví sloupců.
Tím říkáme, že se ve sloupci D mají pronásobit hodnoty ze sloupce B a C. Excel to chápe tak, že do každé buňky ve sloupci D spočte součin buněk v B a C, které jsou na stejném řádku.
Výhodou je větší přehlednost - nemusím hlídat a sledovat, jestli odkazuji na stejný řádek - což téměř vždy odkazuji. Neměla by se pak vyskytovat chyba, že se ukliknu a omylem odkazuji o řádek níž nebo výš.
Nevýhodou je, že se nemohu odkázat na buňku v jiném řádku než v tom, do kterého zapisuji. 

3. Tabulkové odkazy

Je také možné vložit tabulku a odkazy pak vytvářet v tabulce.
Pak zapíšeme (v praxi téměř vždy spíše naklikáme) toto:
=[@Cena]*[@[Počet prodaných kusů]]

Všechny uvedené způsoby mohu kombinovat - tedy např. i v tabulce mohu používat běžné odkazy. Jen nestačí buňky naklikat, odkazy se musí "ručně" zapsat.
Při vložení tabulky se obvykle změní i barvy buněk tak, jako v ukázce. Není to ale nutné - barvy lze odstranit.
Občas se ještě používá odkaz typu R1C1 - ale osobně si nemyslím, že je nutné jej znát.