Vyhľadávanie, grafy, štatistiky a kontingenčné tabuľky
Po preskúmaní základných funkcií, odkazov na bunky a funkcií dátumu a času sa teraz ponoríme do niektorých pokročilejších funkcií programu Microsoft Excel. Predstavujeme metódy riešenia klasických problémov v oblasti financií, obchodných správ, nákladov na dopravu a štatistiky.
ŠKOLSKÁ NAVIGÁCIA- Prečo potrebujete vzorce a funkcie?
- Definovanie a vytváranie vzorca
- Relatívna a absolútna bunková referencia a formátovanie
- Užitočné funkcie, ktoré by ste mali vedieť
- Vyhľadávanie, grafy, štatistiky a kontingenčné tabuľky
Tieto funkcie sú dôležité pre podniky, študentov a tých, ktorí sa chcú dozvedieť viac.
VLOOKUP a HLOOKUP
Tu je príklad na ilustráciu funkcií vertikálneho vyhľadávania (VLOOKUP) a horizontálneho vyhľadávania (HLOOKUP). Tieto funkcie sa používajú na preklad čísla alebo inej hodnoty do niečoho, čo je pochopiteľné. Napríklad môžete použiť VLOOKUP, aby ste dostali číslo dielu a vrátili popis položky.
Ak to chceme preskúmať, vráťme sa do našej tabuľky "Rozhodovateľa" v časti 4, kde sa Jane pokúša rozhodnúť, čo má škola používať. Ona už sa nezaujíma o to, čo nosí, pretože odišla na nového priateľa, takže teraz bude nosiť náhodné oblečenie a obuv.
V tabuľke Jane uvádza výstroj vo vertikálnych stĺpoch a topánkach, horizontálne stĺpce.
Otvára tabuľku a funkcia RANDBETWEEN (1,3) generuje číslo medzi alebo rovným jednému a troch zodpovedajúcim trom typom oblečenia, ktoré môže nosiť.
Používa funkciu RANDBETWEEN (1,5) na výber z piatich typov obuvi.
Pretože Jane nemôže nosiť číslo, musíme to previesť na meno, takže používame vyhľadávacie funkcie.
Funkciu VLOOKUP používame na preloženie čísla výbavy na meno oblečenia. HLOOKUP sa prenáša z čísla topánky na rôzne typy obuvi v rade.
Tabuľkový procesor funguje takto pre oblečenie:
Excel vyberie náhodné číslo od jedného do troch, pretože má tri možnosti výbavy.
Nasledujúci vzorec prekladá číslo do textu pomocou príkazu VLOOKUP (B11, A2: B4,2), ktorý používa náhodné číslo pre hodnotu B11 na zobrazenie v rozsahu A2: B4. Potom dá výsledok (C11) z údajov uvedených v druhom stĺpci.
Na výber topánok používame rovnakú techniku, s výnimkou toho, že používame VOOKUP namiesto HLOOKUP.
Príklad: Základné štatistiky
Takmer každý pozná jeden vzorec zo štatistiky - priemer - ale existuje ďalšia štatistika, ktorá je dôležitá pre podnikanie: štandardná odchýlka.
Napríklad mnohí ľudia, ktorí šli na vysokú školu, agonizovali svoje skóre SAT. Možno chcú vedieť, ako sa hodia v porovnaní s ostatnými študentmi. Univerzity to tiež chcú vedieť, pretože mnohé univerzity, najmä prestížne, odmietajú študentov s nízkymi skóre SAT.
Tak ako by sme my, alebo univerzita, merali a interpretovali SAT skóre? Nižšie sú uvedené skóre SAT pre piatich študentov v rozmedzí 1870 až 2230.
Dôležité čísla, ktoré je potrebné pochopiť, sú:
priemerný - Priemer sa tiež označuje ako "priemer".
Štandardná odchýlka (STD alebo σ) - Toto číslo zobrazuje, ako veľmi rozptýlená je skupina čísel. Ak je štandardná odchýlka veľká, potom čísla sú ďaleko od seba a ak je nula, všetky čísla sú rovnaké. Mohli by ste povedať, že štandardná odchýlka je priemerný rozdiel medzi priemernou hodnotou a pozorovanou hodnotou, t. J. 1 998 a každým skóre SAT. Upozorňujeme, že je obvyklé skrátiť štandardnú odchýlku pomocou gréckeho symbolu sigma "σ."
Percentil Rank - Keď študent dostane vysoké skóre, môže sa chváliť, že je v hornej 99 percentile alebo niečo také. "Percentil rank" znamená percento skóre je nižšie ako jedno konkrétne skóre.
Štandardná odchýlka a pravdepodobnosť sú úzko prepojené. Môžete povedať, že pre každú štandardnú odchýlku pravdepodobnosť alebo pravdepodobnosť, že toto číslo je v tomto počte štandardných odchýlok, je:
| STD | Percento bodov | 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 |
Ako vidíte, šanca, že akékoľvek skóre SAT je mimo 3 STD je prakticky nulové, pretože 99,73 percenta skóre je v rámci 3 STD.
Teraz sa pozrime na tabuľku znova a vysvetlíme, ako to funguje.
Teraz vysvetľujeme vzorce:
= Stredná (B2: B6)
Priemer všetkých bodov v rozsahu B2: B6. Konkrétne súčet všetkých bodov vydelený počtom ľudí, ktorí vykonali test.
= STDEV.P (B2: B6)
Štandardná odchýlka v rozsahu B2: B6. ".P" znamená, že STDEV.P sa používa nad všetkými skóre, t.j. celú populáciu a nie iba podmnožinu.
= PERCENTRANK.EXC ($ B $ 2: $ B $ 6 B2)
Toto vypočíta kumulatívne percento v rozsahu B2: B6 na základe skóre SAT, v tomto prípade B2. Napríklad 83 percent skóre je pod hodnotením Walkera.
Grafovanie výsledkov
Uvedenie výsledkov do grafu uľahčuje pochopenie výsledkov a navyše ich môžete prezentovať v prezentácii, aby ste to jasnejšie vysvetlili.
Študenti sú na horizontálnej osi a ich skóre SAT je zobrazené ako modrý stĺpcový graf na stupnici (vertikálna os) od 1 600 do 2 300.
Percentilové hodnotenie je pravá vertikálna os 0 až 90 percent a je reprezentovaná šedou čiarou.
Ako vytvoriť graf
Vytvorenie grafu je samo osebe, ale stručne to vysvetlíme tým, ako sme vytvorili uvedený graf.
Najprv vyberte rozsah buniek v grafe. V tomto prípade A2 až C6, pretože chceme čísla aj meno študenta.
V ponuke "Vložiť" zvoľte "Grafy" -> "Odporúčané grafy":
Počítač odporúča graf "Klastrovaný stĺpec, sekundárna os". Časť "Sekundárna os" znamená, že čerpá dve vertikálne osi. V tomto prípade je tento graf ten, ktorý chceme. Nemusíme robiť nič iné.
Môžete použiť pohybovať graf okolo a re-veľkosť, kým ju máte ako veľkosť a na požadovanú pozíciu. Akonáhle ste spokojní, môžete graf uložiť do tabuľky.
Ak kliknete pravým tlačidlom myši na graf a potom na "Vybrať údaje", zobrazí sa, aké údaje sa pre tento rozsah vybrali.
Funkcia Odporúčané grafy zvyčajne vylučuje, že musíte riešiť takéto zložité detaily ako určiť, aké údaje majú zahrnúť, ako priradiť menovky a ako priradiť ľavú a pravú vertikálnu os.
V dialógovom okne "Vybrať zdroj údajov" kliknite na položku "Skóre" v časti "Legend Entries (Series)" a stlačte "Upraviť" a zmeňte ho na "Skóre".
Potom zmeňte sériu 2 ("percentil") na "Percentile".
Vráťte sa do grafu a kliknite na "Názov grafu" a zmeňte ho na "SAT skóre". Teraz máme kompletný graf. Má dve horizontálne osi: jedno pre skóre SAT (modré) a jedno pre kumulatívne percento (oranžové).
Príklad: Dopravný problém
Dopravný problém je klasický príklad typu matematiky s názvom "lineárne programovanie". To vám umožní maximalizovať alebo minimalizovať hodnotu podliehajúcu určitým obmedzeniam. Má mnoho aplikácií pre široké spektrum obchodných problémov, takže je užitočné zistiť, ako to funguje.
Skôr než začneme s týmto príkladom, musíme povoliť riešenie "Excel Solver".
Povoliť doplnok Solver
Vyberte položku "Súbor" -> "Možnosti" -> "Doplnky". V dolnej časti doplnkov kliknite na tlačidlo "Prejsť" vedľa položky "Spravovať: Doplnky programu Excel".
Na výslednej ponuke kliknite na začiarkavacie políčko, ak chcete povoliť doplnok Solver, a kliknite na tlačidlo OK.
Príklad: Vypočítajte najnižšie náklady na dopravu iPad
Predpokladajme, že prepravujeme iPad a snažíme sa naplniť naše distribučné centrá s použitím najnižších možných nákladov na dopravu. Máme dohodu s nákladnou a leteckou spoločnosťou o preprave iPadov zo Šanghaja, Pekingu a Hongkongu do distribučných centier uvedených nižšie.
Cena za prepravu každého iPadu je vzdialenosť od továrne k distribučnému centru k zariadeniu vydelená 20 000 kilometrami. Napríklad je to 8 024 km od Šanghaja do Melbourne, čo je 8 024/20 000 alebo 40 USD za iPad.
Otázkou je, ako dodáme všetky tieto iPady z týchto troch zariadení do týchto štyroch destinácií za najnižšiu možnú cenu?
Ako si viete predstaviť, zistenie, že to môže byť veľmi ťažké bez nejakého vzorca a nástroja. V tomto prípade musíme odoslať 462 000 (F12) celkom iPadov. Rastliny majú obmedzenú kapacitu 500 250 (G12) jednotiek.
V tabuľke, aby ste mohli vidieť, ako to funguje, sme zadali 1 do bunky B10, čo znamená, že chceme poslať 1 iPad zo Šanghaja do Melbourne. Keďže prepravné náklady na tejto trase predstavujú 0,40 USD za iPad, celková cena (B17) je 0,40 USD.
Číslo bolo vypočítané pomocou funkcie = SUMPRODUCT (náklady, dodané) "náklady" sú rozsahy B3: E5.
A "odoslané" sú rozsah B9: E11:
SUMPRODUCT násobí "náklady" v rozmedzí "dodávané" (B14). To sa nazýva "násobenie matice".
Aby mohla SUMPRODUCT fungovať správne, musia byť tieto dva matice - náklady a expedované - rovnakej veľkosti. Toto obmedzenie môžete obísť tým, že dodatočné náklady a prepravné stĺpce a riadky s nulovou hodnotou, takže sú polia rovnakej veľkosti a nemajú vplyv na celkové náklady.
Používanie nástroja Solver
Ak všetko, čo sme museli urobiť, bolo vynásobiť "náklady", ktoré boli "odoslané", ktoré by neboli príliš komplikované, ale musíme tu riešiť aj obmedzenia.
Musíme dodať to, čo každé distribučné centrum vyžaduje. Túto konštantu kladieme takto: $ B $ 12: $ E $ 12> = $ B $ 13: $ E $ 13. Znamená to, že súčet toho, čo sa dodáva, t. J. Súčty v bunkách $ B $ 12: $ E $ 12, musí byť väčšie alebo rovné tomu, čo vyžaduje každé distribučné centrum ($ B $ 13: $ E $ 13).
Nemôžeme plaviť viac, než produkujeme. Napíšeme tieto obmedzenia: $ 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.
Teraz prejdite do ponuky "Data" a stlačte tlačidlo "Solver". Ak tlačidlo "Solver" neexistuje, musíte aktivovať doplnok Solver.
Zadajte dve obmedzenia, ktoré boli podrobne popísané vyššie, a vyberte rozsah "Zásielky", čo je rozsah čísel, ktoré chceme vypočítať. Tiež si zvoľte predvolený algoritmus "Simplex LP" a uveďte, že chceme "minimalizovať" bunku B15 ("celkové náklady na dopravu"), kde sa uvádza "Nastaviť cieľ".
Stlačte tlačidlo "Riešenie" a program Excel uloží výsledky do tabuľky, čo je to, čo chceme. Môžete tiež uložiť, aby ste mohli hrať s inými scenárami.
Ak počítač hovorí, že nemôže nájsť riešenie, urobili ste niečo, čo nie je logické, napríklad môžete požiadať o viac iPadov, než môžu rastliny vyrábať.
Tu Excel hovorí, že našiel riešenie. Stlačte tlačidlo "OK", aby ste udržali riešenie a vrátili sa do tabuľky.
Príklad: Čistá súčasná hodnota
Ako sa spoločnosť rozhodne, či investovať do nového projektu? Ak je "čistá súčasná hodnota" (Čistá súčasná hodnota) kladná, investuje do neho. Ide o štandardný prístup väčšiny finančných analytikov.
Predpokladajme napríklad, že banská spoločnosť Codelco chce rozšíriť medený banský závod Andinas. Štandardným prístupom k určeniu, či sa má projekt posunúť dopredu, je vypočítať čistú súčasnú hodnotu. Ak je NPV väčšia ako nula, projekt bude ziskový vzhľadom na dva vstupy (1) čas a (2) náklady na kapitál.
V bežnej angličtine, náklady na kapitál znamená to, koľko by tieto peniaze zarobili, keby ich práve opustili v banke. Využívate náklady na kapitál na zľavu hotovostných hodnôt na súčasnú hodnotu, inými slovami 100 dolárov za päť rokov môže byť dnes 80 dolárov.
V prvom roku je 45 miliónov dolárov vyčlenených ako kapitál na financovanie projektu. Účtovníci určili, že ich kapitálová cena je šesť percent.
Pri začatí ťažby začnú prísť hotovosti, keď spoločnosť nájde a predá medu, ktorú vyrábajú. Je zrejmé, že čím viac ľudí, tým viac peňazí robí, a ich prognóza ukazuje, že ich peňažný tok stúpa, kým nedosiahne 9 miliónov dolárov ročne.
Po 13 rokoch je NPV 3 945 074 USD, takže projekt bude ziskový. Podľa finančných analytikov je "doba spätného odkúpenia" 13 rokov.
Vytvorenie kontingenčnej tabuľky
"Kontingenčná tabuľka" je v podstate správa. Nazývame ich kontingenčné tabuľky, pretože ich môžete jednoducho prepnúť do jedného typu správy bez toho, aby ste museli vytvoriť celú novú správu. Tak oni otočný čap na mieste. Ukážme základný príklad, ktorý učí základné pojmy.
Príklad: Správy o predaji
Predajcovia sú veľmi konkurencieschopní (to je súčasť predaja), takže prirodzene chcú vedieť, ako sa navzájom vyrovnajú na konci štvrťroka a konca roka, plus koľko ich provízií bude.
Predpokladajme, že máme tri predajcov - Carlos, Fred a Julie - všetci predávajú ropu. Ich predaj v dolároch za fiškálny štvrťrok za rok 2014 je uvedený v tabuľke nižšie.
Na generovanie týchto prehľadov vytvoríme kontingenčnú tabuľku:
Vyberte "Vložiť -> Kontingenčný stôl, nachádza sa na ľavej strane panelu nástrojov:
Vyberte všetky riadky a stĺpce (vrátane mena predajcu), ako je uvedené nižšie:
V pravom hornom rohu tabuľky sa zobrazí dialógové okno kontingenčnej tabuľky.
Ak klikneme na všetky štyri polia v dialógovom okne kontingenčnej tabuľky (Quarter, Year, Sales a Salesperson), Excel pridá správu do tabuľky, ktorá nemá zmysel, ale prečo?
Ako môžete vidieť, vybrali sme všetky štyri polia, ktoré sme pridali do prehľadu. Predvolené správanie programu Excel je zoskupiť riadky textovými poľami a potom sumarizovať všetky ostatné riadky.
Tu nám dáva súčet roku 2014 + 2014 + 2014 + 2014 = 24 168, čo je nezmysel. Taktiež dal súčet štvrťrokov 1 + 2 + 3 + 4 = 10 * 3 = 3 0. Tieto informácie nepotrebujeme, a preto ich zrušíme ich odstránením z našej kontingenčnej tabuľky.
"Suma predajov" (celkový predaj) je však relevantná, preto to napravíme.
Príklad: Predaj podľa predajcu
Môžete upraviť položku "Suma predaja", ktorá hovorí "Celkový predaj", čo je jasnejšie. Tiež môžete formátovať bunky ako meny, rovnako ako akékoľvek iné bunky. Najprv kliknite na položku "Suma predaja" a zvoľte "Nastavenia poľa hodnoty".
Na výslednom dialógovom okne zmeníme názov na hodnotu "Celkové predajné" a následne na položku "Formát čísel" a zmeníme ho na hodnotu "Mena".
Potom môžete vidieť svoje pracovné úlohy v kontingenčnej tabuľke:
Príklad: Predaj podľa predajcu a štvrťroka
Teraz pridáme medzisúčty za každý štvrťrok. Ak chcete pridať medzisúčty, kliknite ľavým tlačidlom myši na pole "Štvrťrok" a podržte ho a presuňte do sekcie "riadky". Výsledok môžete vidieť na nasledujúcom obrázku:
Kým sme na tom, odstráňte hodnoty "Sum of Quarter". Jednoducho kliknite na šípku a kliknite na "Odstrániť pole". Na snímke obrazovky teraz môžete vidieť, že sme pridali riadky "Quarter", ktoré rozdeľujú predaj každého predajcu o štvrťrok.
S týmito novými zručnosťami môžete teraz vytvoriť kontingenčné tabuľky z vlastných údajov!
záver
Zbaliť sme vám ukázali niektoré funkcie formulárov a funkcií programu Microsoft Excel, ktoré môžete aplikovať aplikáciu Microsoft Excel na vaše podnikateľské, akademické alebo iné potreby.
Ako ste videli, program Microsoft Excel je obrovský produkt s toľkými funkciami, ktoré väčšina ľudí, dokonca aj pokročilí používatelia, nepozná všetky. Niektorí ľudia by mohli povedať, že to komplikuje; cítime, že je to komplexnejšie.
Dúfame, že vám prinášame veľa príkladov z reálneho života, preukázali sme nielen funkcie dostupné v programe Microsoft Excel, ale naučili sme vám niečo o štatistikách, lineárnom programovaní, vytváraní tabuliek, používaní náhodných čísel a iných nápadoch, ktoré teraz môžete prijať a používať vo svojej škole alebo kde pracujete.
Nezabudnite, že ak sa chcete vrátiť späť a zobrať si triedu znova, môžete začať čerstvé s lekciou 1!
