Vyhledávání, grafy, statistiky a kontingenční tabulky

Po přezkoumání základních funkcí, odkazů na buňky a funkcí data a času se nyní ponoříme do některých pokročilejších funkcí aplikace Microsoft Excel. Představujeme metody řešení klasických problémů v oblasti financí, prodejních zpráv, přepravních nákladů a statistik.

ŠKOLNÍ NAVIGACE

1. Proč potřebujete vzorce a funkce?
2. Definování a vytváření vzorce
3. Relativní a absolutní odkaz na buňky a formátování
4. Užitečné funkce, které byste měli vědět
5. Vyhledávání, grafy, statistiky a kontingenční tabulky

Tyto funkce jsou důležité pro firmy, studenty a ty, kteří se jen chtějí dozvědět víc.

VLOOKUP a HLOOKUP

Zde je příklad, který ilustruje funkce vertikálního vyhledávání (VLOOKUP) a horizontální vyhledávání (HLOOKUP). Tyto funkce se používají k překladu čísla nebo jiné hodnoty do něčeho, co je srozumitelné. Můžete například použít VLOOKUP, abyste získali číslo součásti a vrátili popis položky.

Abychom to prozkoumali, vraťme se do tabulky "Rozhodovací makléř" v Části 4, kde se Jane snaží rozhodnout, co se bude školám nosit. Ona se už nezajímá o to, co nosí, protože přivezla nového přítele, a tak bude nosit náhodné oblečení a boty.

V tabulce Janea uvádí seznam oblečení ve svislých sloupcích a botách, horizontálních sloupcích.

Otevírá tabulku a funkce RANDBETWEEN (1,3) generuje číslo mezi nebo rovno jednomu a třem odpovídajícím třem typům oblečení, které může nosit.

Používá funkci RANDBETWEEN (1,5) pro výběr z pěti typů obuvi.

Vzhledem k tomu, že Jane nemůže nosit číslo, musíme to převést na jméno, takže používáme vyhledávací funkce.

Funkci VLOOKUP používáme k překladu čísla výstroje na jméno výstroje. HLOOKUP přechází od čísla boty k různým typům obuvi v řadě.

Tabulka funguje takto pro oblečení:

Excel vybírá náhodné číslo od jednoho do tří, protože má tři možnosti oblečení.

Dále vzorec převede číslo na text pomocí = VLOOKUP (B11, A2: B4,2), který používá číslo náhodného čísla z B11 pro zobrazení v rozsahu A2: B4. Výsledkem je výsledek (C11) z údajů uvedených ve druhém sloupci.

Stejnou techniku ​​používáme k výběru obuvi, s výjimkou případů, kdy používáme VOOKUP namísto HLOOKUP.

Příklad: Základní statistiky

Téměř všichni zná jeden vzorec ze statistik - průměr - ale existuje další statistika důležitá pro podnikání: standardní odchylka.

Například mnoho lidí, kteří šli na vysokou školu, agonizovalo své skóre SAT. Mohli by chtít vědět, jak se hodí ve srovnání s ostatními studenty. Univerzity to chtějí znát také proto, že mnohé univerzity, zejména prestižní, odmítají studenty s nízkými skóre SAT.

Tak jak bychom my, nebo univerzita, měřili a interpretovali SAT skóre? Níže jsou skóre SAT pro pět studentů v rozmezí 1870 až 2230.

Důležitá čísla, která je třeba pochopit, jsou:

Průměrný - Průměr je také označován jako "střední".

Standardní odchylka (STD nebo σ) - Toto číslo ukazuje, jak široce rozptýlená je řada čísel. Pokud je standardní odchylka velká, pak čísla jsou daleko od sebe a pokud je nula, všechna čísla jsou stejná. Mohlo by se říci, že směrodatná odchylka je průměrný rozdíl mezi průměrnou hodnotou a pozorovanou hodnotou, tj. 1 998 a každým skóre SAT. Upozorňujeme, že je obvyklé zkratovat směrodatnou odchylku pomocí řeckého symbolu sigma "σ."

Percentil Rank - Když student obdrží vysoké skóre, mohou se chlubit, že jsou ve vrcholu 99 percentilu nebo něco takového. "Percentil rank" znamená, že procento bodů je nižší než jedno konkrétní skóre.

Standardní odchylka a pravděpodobnost jsou úzce spojeny. Můžete říci, že pro každou směrodatnou odchylku pravděpodobnost nebo pravděpodobnost, že se toto číslo nachází uvnitř tohoto počtu standardních odchylek, je:

STD	Procento bodů	Rozsah skóre SAT
1	68%	1,854-2,142
2	95%	1,711-2,285
3	99,73%	1,567-2,429
4	99,994%	1,424-2,572

Jak můžete vidět, šance, že jakékoliv skóre SAT je mimo 3 STD, je prakticky nulové, protože 99,73 procent skóre je v rámci 3 STD.

Nyní se znovu podíváme na tabulku a vysvětlíme, jak to funguje.

Nyní vysvětlujeme vzorce:

= Průměrná (B2: B6)

Průměr všech bodů v rozmezí B2: B6. Konkrétně součet všech bodů dělených počtem lidí, kteří test provedli.

= STDEV.P (B2: B6)

Směrodatná odchylka v rozsahu B2: B6. ".P" znamená, že STDEV.P se používá ve všech výsledcích, tj. V celé populaci, a ne pouze v podskupině.

= PERCENTRANK.EXC ($ B $ 2: $ B $ 6, B2)

Toto vypočítá kumulativní procento v rozsahu B2: B6 na základě skóre SAT, v tomto případě B2. Například 83 procent skóre je pod Walkerovým skóre.

Grafování výsledků

Umístění výsledků do grafu usnadňuje porozumění výsledkům a navíc je můžete zobrazit v prezentaci, aby se vaše vyjasnění jasněji ukázalo.

Studenti jsou na vodorovné ose a jejich skóre SAT je zobrazeno jako modrý sloupcový graf na stupnici (vertikální osa) od 1 600 do 2 300.

Percentilní pořadí je pravá svislá osa od 0 do 90 procent a je reprezentována šedou čárou.

Jak vytvořit graf

Vytvoření grafu je samo o sobě téma, nicméně budeme stručně vysvětlovat, jak byl vytvořen výše uvedený graf.

Nejprve vyberte rozsah buněk, které mají být v grafu. V tomto případě A2 až C6, protože chceme jména, stejně jako jména studentů.

V nabídce "Vložit" vyberte "Grafy" -> "Doporučené grafy":

Počítač doporučuje graf "Clustered-Column, Secondary Axis". Část "Sekundární os" znamená, že čerpá dvě vertikální osy. V tomto případě je tento graf ten, který chceme. Nemusíme dělat nic jiného.

Pomocí grafu můžete přesouvat grafiku a měnit ji znovu, dokud ji nemáte jako velikost a požadovanou pozici. Jakmile jste spokojeni, můžete tabulku uložit do tabulkového procesoru.

Pokud klepnete pravým tlačítkem myši na graf a potom na "Vybrat data", zobrazí se, jaké údaje jsou pro tento rozsah vybrány.

Funkce "Doporučené grafy" obvykle vylučuje, že se musí vypořádat s tak obtížnými detaily, jako je určení toho, jaké údaje mají být zahrnuty, jak přiřadit štítky a jak přiřadit levou a pravou svislou osou.

V dialogovém okně "Vybrat zdroj dat" klikněte na položku "Skóre" v položce "Legend Entries (Series)" a stiskněte tlačítko "Upravit" a změňte jej tak, aby bylo uvedeno "Skóre".

Pak změňte řadu 2 ("percentil") na "Percentile".

Vraťte se do grafu a klikněte na "Název grafu" a změňte jej na "SAT skóre". Nyní máme kompletní tabulku. Má dvě horizontální osy: jedno pro skóre SAT (modré) a jedno pro kumulativní procento (oranžové).

Příklad: Dopravní problém

Dopravní problém je klasickým příkladem typu matematiky nazývaného "lineární programování". To vám umožní maximalizovat nebo minimalizovat hodnotu podléhající určitým omezením. Má mnoho aplikací na široké spektrum obchodních problémů, takže je užitečné učit se, jak to funguje.

Než začneme s tímto příkladem, musíme povolit "řešení Excel".

Povolit doplněk Solver

Vyberte "Soubor" -> "Možnosti" -> "Doplňky". Ve spodní části doplňků klepněte na tlačítko "Přejít" vedle položky "Spravovat: Doplňky aplikace Excel".

Ve výsledné nabídce klikněte na zaškrtávací políčko k povolení "Add-in Solver" a klikněte na "OK".

Příklad: Vypočítejte nejnižší náklady na dopravu iPad

Předpokládejme, že přepravujeme iPady a snažíme se naplňovat naše distribuční centra za použití nejnižších možných přepravních nákladů. Máme dohodu s nákladní a leteckou společností o dodávkách iPadů z Šanghaje, Pekingu a Hongkongu do distribučních středisek uvedených níže.

Cena za každý iPad je vzdálenost od továrny k distribučnímu centru k zařízení, dělená 20 000 kilometry. Například to je 8,024 km od Šanghaje do Melbourne, což je 8,024 / 20,000 nebo $ .40 za iPad.

Otázkou je, jak přenášíme všechny tyto iPady z těchto tří zařízení do těchto čtyř destinací za co nejnižší možné náklady?

Jak si dokážete představit, to by mohlo být velmi obtížné bez nějakého vzorce a nástroje. V tomto případě musíme odeslat celkem 462 000 (F12) celkem iPadů. Rostliny mají omezenou kapacitu 500 250 (G12) jednotek.

V tabulce, abyste viděli, jak to funguje, jsme napsali 1 do buňky B10, což znamená, že chceme odeslat 1 iPad ze Šanghaje do Melbourne. Vzhledem k tomu, že přepravní náklady na této trase jsou 0,40 USD za iPad, celkové náklady (B17) činí 0,40 USD.

Číslo bylo vypočteno pomocí funkce = SUMPRODUCT (náklady, expedováno) "náklady" jsou rozsahy B3: E5.

A "dodáváno" je rozsah B9: E11:

Nástroj SUMPRODUCT násobí "náklady" v rozmezí dodaného zboží (B14). To se nazývá "násobení matice".

Aby mohla SUMPRODUCT pracovat správně, musí být obě matice - náklady a odeslány - stejné velikosti. Toto omezení můžete obdržet tak, že nabudete dodatečných nákladů a přepravních sloupců a řádků s nulovou hodnotou, takže pole jsou stejné velikosti a nemají žádný vliv na celkové náklady.

Použití nástroje Řešitel

Pokud bychom museli pouze vynásobit "náklady", které by byly "dodány", které by nebyly příliš komplikované, musíme se vypořádat také s omezeními.

Musíme dopravit to, co každé distribuční středisko vyžaduje. Tuto konstantu nastavíme takto: $ B $ 12: $ E $ 12> = $ B $ 13: $ E $ 13. To znamená součet toho, co je dodáno, tj. Součty v buňkách $ B $ 12: $ E $ 12, musí být větší nebo rovné tomu, co vyžaduje každé distribuční středisko ($ B $ 13: $ E $ 13).

Nemůžeme loď více, než produkujeme. Napsali jsme tato omezení takto: $ F $ 9: $ F $ 11 <= $G$9:$G$11. Put another way, what we ship from each plant $F$9:$F$11 cannot exceed (must be less than or equal to) the capacity of each plant: $G$9:$G$11.

Nyní přejděte do nabídky "Data" a stiskněte tlačítko "Solver". Pokud tlačítko "Solver" neexistuje, musíte povolit doplněk Solver.

Zadejte dvě výše popsaná omezení a vyberte řadu "Zásilky", což je rozsah čísel, které chceme vypočítat. Také si vyberte výchozí algoritmus "Simplex LP" a naznačte, že chceme "minimalizovat" buňku B15 ("celkové náklady na dopravu"), kde se uvádí "Set Objective".

Stiskněte tlačítko "Řešit" a aplikace Excel uloží výsledky do tabulky, což je to, co chceme. Můžete také uložit, abyste si mohli zahrát s jinými scénáři.

Pokud počítač říká, že nemůže najít řešení, pak jste udělali něco, co není logické, například můžete požádat o další iPady, než mohou rostliny vyrábět.

Zde Excel říká, že našel řešení. Stiskněte tlačítko "OK" pro uložení řešení a návrat do tabulky.

Příklad: Čistá současná hodnota

Jak se společnost rozhodne, zda investovat do nového projektu? Pokud je "čistá současná hodnota" (NPV) kladná, investují do ní. To je standardní přístup většiny finančních analytiků.

Předpokládejme například, že těžební společnost Codelco chce rozšířit měděný důl Andinas. Standardní přístup k určení, zda se má projekt posunout dopředu, je vypočítat čistou současnou hodnotu. Pokud je NPV větší než nula, projekt bude ziskový vzhledem k dvěma vstupům (1) času a (2) k nákladům na kapitál.

V obyčejné angličtině náklady na kapitál znamenají, kolik by tyto peníze vydělaly, kdyby je opustily v bance. Využijete náklady na kapitál pro diskontování peněžních hodnot na současnou hodnotu, jinými slovy $ 100 za pět let by dnes mohlo činit 80 dolarů.

V prvním roce je 45 milionů dolarů vyčleněno jako kapitál na financování projektu. Účtovníci zjistili, že jejich kapitálová cena činí šest procent.

Jakmile začnou těžby, začnou přicházet hotovosti, když společnost vyhledá a prodává měď, kterou vyrábějí. Je zřejmé, že čím více mine, tím více peněz dělají a jejich prognóza ukazuje, že jejich peněžní tok stoupá, až dosáhne 9 milionů dolarů ročně.

Po 13 letech činí NPV 3 945 074 USD, takže projekt bude ziskový. Podle finančních analytiků je doba návratnosti 13 let.

Vytvoření kontingenční tabulky

"Kontingenční tabulka" je v podstatě zpráva. Říkáme jim kontingenční tabulky, protože je můžete jednoduše přepínat mezi jednotlivými typy zpráv, aniž byste museli vytvořit celou novou zprávu. Takže oni pivot na místě. Ukážeme základní příklad, který učí základní pojmy.

Příklad: Přehledy prodeje

Prodejní lidé jsou velmi konkurenceschopní (to je součástí prodeje), takže přirozeně chtějí vědět, jak se na konci čtvrtletí a konce roku navzájem oproti sobě navzájem vyrovnají a kolik budou jejich provize.

Předpokládejme, že máme tři prodejce - Carlos, Fred a Julie - všichni prodávají ropu. Jejich prodej v USD za fiskální čtvrtletí za rok 2014 je uveden v tabulce níže.

Chcete-li generovat tyto přehledy, vytvoříme kontingenční tabulku:

Vyberte "Vložit -> Kontingenční tabulka, je umístěna na levé straně panelu nástrojů:

Vyberte všechny řádky a sloupce (včetně názvu prodávajícího), jak je uvedeno níže:

Na pravé straně tabulky se zobrazí dialogové okno kontingenční tabulky.

Pokud klikneme na všechna čtyři pole v dialogovém okně kontingenční tabulky (Quarter, Year, Sales a Salesperson), Excel přidá do tabulky tabulku, která nemá smysl, ale proč?

Jak vidíte, vybrali jsme všechna čtyři pole, která se do sestavy přidávají. Výchozím chováním programu Excel je seskupení řádků pomocí textových polí a součet všech zbývajících řádků.

Zde nám dává součet roku 2014 + 2014 + 2014 + 2014 = 24 168, což je nesmysl. Také to udává součet čtvrtletí 1 + 2 + 3 + 4 = 10 * 3 = 3 0. Tyto informace nepotřebujeme, a proto je zrušíme výběrem těchto polí, abychom je odstranili z naší kontingenční tabulky.

"Součet prodeje" (celkový prodej) je relevantní, nicméně, tak to opravíme.

Příklad: Prodej prodejcem

Můžete upravit položku "Součet prodeje", která říká "Celkový prodej", což je jasnější. Také můžete formátovat buňky jako měnu, stejně jako byste vytvořili libovolné jiné buňky. Nejprve klikněte na položku "Součet prodeje" a vyberte "Nastavení pole hodnot".

Na výsledném dialogovém okně změníme název na "Celkové prodeje" a pak na "Formát čísla" a změníme jej na "Měna".

Potom můžete vidět svou práci v kontingenční tabulce:

Příklad: Prodej prodejcem a čtvrtletí

Nyní přidáme součet pro každý čtvrtletí. Chcete-li přidat mezisoubory, klikněte pravým tlačítkem myši na pole "Čtvrť" a podržte jej a přetáhněte do části "Řádky". Výsledek můžete vidět na následující obrazovce:

Zatímco jsme na tom, vyjměte hodnoty "Sum of Quarter". Jednoduše klikněte na šipku a klikněte na tlačítko "Odstranit pole". Na obrazovce obrazovky nyní vidíte, že jsme přidali řádky "čtvrtletí", které rozdělují prodej každého prodejce o čtvrtletí.

S těmito novými dovednostmi můžete nyní vytvářet kontingenční tabulky z vlastních dat!

Závěr

Začali jsme vám ukázat některé funkce formulí a funkcí programu Microsoft Excel, které můžete aplikovat na své podnikání, akademické nebo jiné potřeby.

Jak jste viděli, Microsoft Excel je obrovský produkt s tolika funkcemi, které většina lidí, i pokročilí uživatelé, nezná všechny. Někteří lidé by mohli říci, že to komplikuje; Cítíme, že je komplexnější.

Doufejme, že když vám představíme spousty příkladů z reálného života, ukázali jsme nejen funkce dostupné v aplikaci Microsoft Excel, ale naučil vám něco o statistikách, lineárním programování, vytváření grafů, použití náhodných čísel a dalších nápadů, které nyní můžete přijmout a ve škole nebo kde pracujete.

Nezapomeňte, že pokud se chcete vrátit zpět do třídy, můžete začít s lekcí 1 znovu!