Poizvedbe, grafikoni, statistika in vrtilne tabele
Pregledali smo osnovne funkcije, reference celic ter funkcije datuma in časa, zdaj pa se potopimo v nekatere naprednejše funkcije programa Microsoft Excel. Predstavljamo metode za reševanje klasičnih problemov na področju financ, prodajnih poročil, stroškov pošiljanja in statistike.
ŠOLSKA NAVIGACIJA- Zakaj potrebujete formule in funkcije?
- Določanje in ustvarjanje formule
- Relativna in absolutna referenca celic ter oblikovanje
- Uporabne funkcije, ki jih morate poznati
- Poizvedbe, grafikoni, statistika in vrtilne tabele
Te funkcije so pomembne za podjetja, študente in tiste, ki se želijo le naučiti več.
VLOOKUP in HLOOKUP
Tukaj je primer, ki ponazarja funkcije navpičnega iskanja (VLOOKUP) in horizontalnih iskanj (HLOOKUP). Te funkcije se uporabljajo za prevajanje številke ali druge vrednosti v nekaj, kar je razumljivo. Na primer, lahko uporabite VLOOKUP, da vzamete številko dela in vrnete opis izdelka.
Da bi to raziskali, se vrnimo k naši razpredelnici za "Odločiteljeve" v 4. delu, kjer se Jane skuša odločiti, kaj naj nosi v šoli. Ne zanima jo več, kaj je oblečena, ker je pristala na novega fanta, tako da bo zdaj nosila naključne obleke in obutev..
V Janeovi preglednici navaja oblačila v navpičnih stolpcih in čevljih, horizontalnih stolpcev.
Odpre preglednico in funkcija RANDBETWEEN (1,3) ustvari število, ki je enako ali enako eni in trije, kar ustreza trem vrstam oblačil, ki jih lahko nosi..
S funkcijo RANDBETWEEN (1,5) izbere pet vrst čevljev.
Ker Jane ne more nositi številke, ki jo moramo pretvoriti v ime, uporabljamo funkcije iskanja.
S funkcijo VLOOKUP prevedemo številko obleke v ime obleke. HLOOKUP se iz številke čevljev prevede v različne vrste čevljev v vrsti.
Preglednica deluje tako za obleke:
Excel izbere naključno število od enega do treh, saj ima tri možnosti obleke.
Nato formula prevede številko v besedilo z uporabo = VLOOKUP (B11, A2: B4,2), ki uporablja naključno število vrednost iz B11 za pogled v območju A2: B4. Nato poda rezultat (C11) iz podatkov, navedenih v drugem stolpcu.
Uporabljamo isto tehniko, da izbiramo čevlje, razen da tokrat uporabimo VOOKUP namesto HLOOKUP.
Primer: Osnovna statistika
Skoraj vsakdo pozna eno formulo iz statistike - povprečno - vendar obstaja še ena statistika, ki je pomembna za podjetja: standardni odklon.
Na primer, mnogi ljudje, ki so šli na kolidž, so se mučili nad svojim SAT rezultatom. Morda želijo vedeti, kako se uvrščajo v primerjavi z drugimi študenti. Univerze želijo to vedeti tudi zato, ker številne univerze, zlasti prestižne, zavračajo študente z nizko oceno SAT.
Kako bi torej mi ali univerza merili in interpretirali rezultate SAT? Spodaj so rezultati SAT za pet študentov, ki segajo od 1.870 do 2.230.
Pomembne številke, ki jih je treba razumeti, so:
Povprečje - Povprečje imenujemo tudi »srednja«.
Standardno odstopanje (STD ali σ) - Ta številka kaže, kako široko je razpršeno število številk. Če je standardno odstopanje veliko, so številke daleč narazen in če je nič, so vse številke enake. Lahko bi rekli, da je standardna deviacija povprečna razlika med povprečno vrednostjo in opazovano vrednostjo, tj. Upoštevajte, da je standardno odstopanje običajno skrajšati z grškim simbolom sigma “σ.”
Percentilni položaj - Ko študent prejme visoko oceno, se lahko pohvali, da so v vrhu 99 odstotkov ali kaj takega. "Odstotek procentila" pomeni odstotek točk, ki je nižji od enega določenega rezultata.
Standardno odstopanje in verjetnost sta tesno povezana. Lahko rečemo, da je za vsako standardno odstopanje verjetnost ali verjetnost, da je to število znotraj tega števila standardnih odstopanj:
| STD | Odstotek rezultatov | Razpon SAT rezultatov |
| 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 |
Kot lahko vidite, je možnost, da je katerikoli SAT rezultat zunaj 3 STD, praktično nič, saj je 99,73 odstotkov rezultatov v 3 STD..
Zdaj pa si poglejmo preglednico in razložimo, kako deluje.
Zdaj bomo pojasnili formule:
= POVPREČINA (B2: B6)
Povprečje vseh rezultatov v razponu B2: B6. Natančneje, vsota vseh rezultatov deljena s številom ljudi, ki so opravili test.
= STDEV.P (B2: B6)
Standardno odstopanje v območju B2: B6. ".P" pomeni STDEV.P se uporablja za vse rezultate, tj. Za celotno populacijo in ne samo za podskupino..
= PERCENTRANK.EXC ($ B $ 2: $ B $ 6, B2)
To izračuna kumulativni odstotek v razponu B2: B6 na podlagi rezultata SAT, v tem primeru B2. Na primer, 83 odstotkov rezultatov je pod Walkerjevo oceno.
Grafiranje rezultatov
Dajanje rezultatov v graf omogoča lažje razumevanje rezultatov, poleg tega pa ga lahko prikažete v predstavitvi, da boste bolj jasno izrazili svoje mnenje.
Študenti so na vodoravni osi in njihovi SAT rezultati so prikazani kot modri stolpični graf na lestvici (navpična os) od 1.600 do 2.300.
Razvrstitev v odstotkih je desna navpična os od 0 do 90 odstotkov in je prikazana s sivo črto.
Kako ustvariti grafikon
Ustvarjanje grafikona je sama po sebi tema, vendar bomo na kratko razložili, kako je bil zgornji grafikon ustvarjen.
Najprej izberite obseg celic v grafikonu. V tem primeru A2 do C6, ker želimo tako številke kot imena učencev.
V meniju »Vstavi« izberite »Grafikoni« -> »Priporočene tabele«:
Računalnik priporoča grafikon »Clustered-Column, Secondary Axis«. Del "sekundarne osi" pomeni, da črpa dve navpični osi. V tem primeru je ta karta tista, ki jo želimo. Ni nam treba storiti ničesar drugega.
Uporabite lahko premikanje grafikona in ga spremenite, dokler ga ne dobite kot velikost in na želenem mestu. Ko ste zadovoljni, lahko graf shranite v preglednico.
Če z desno tipko miške kliknete grafikon in nato izberete »Podatki«, se prikaže, kateri podatki so izbrani za območje.
Funkcija »Priporočene grafikone« vas ponavadi izogiba temu, da bi se morali ukvarjati s tako zapletenimi podrobnostmi, kot je določanje, katere podatke je treba vključiti, kako dodeliti oznake in kako dodeliti levo in desno navpično os.
V pogovornem oknu »Izberi vir podatkov« v razdelku »Vnosi iz legende (serije)« kliknite »rezultat« in pritisnite »Uredi« in spremenite besedo »Rezultat«.
Nato spremenite niz 2 (»percentil«) v »Percentile«.
Vrnite se na grafikon in kliknite na “Naslov grafikona” in ga spremenite na “Ocene SAT”. Zdaj imamo celoten grafikon. Ima dve horizontalni osi: eno za rezultat SAT (modro) in eno za kumulativni odstotek (oranžno).
Primer: Problem transporta
Problem transporta je klasičen primer matematike, imenovane »linearno programiranje«. To vam omogoča, da povečate ali zmanjšate vrednost pod določenimi omejitvami. Ima veliko aplikacij za široko paleto poslovnih težav, zato je koristno, da se naučite, kako deluje.
Preden začnemo s tem primerom, moramo omogočiti »Excel Solver«.
Omogoči Solver Add-In
Izberite »Datoteka« -> »Možnosti« -> »Dodatki«. Na dnu možnosti dodatkov kliknite gumb »Pojdi« poleg možnosti »Upravljanje: Excelovi dodatki«.
V izbranem meniju kliknite potrditveno polje, da omogočite, »Solver Add-in« in kliknite »OK«.
Primer: Izračunajte najnižje stroške dostave iPad
Recimo, da prodajamo iPads in poskušamo izpolniti naše distribucijske centre z najnižjimi možnimi stroški prevoza. S podjetjem za prevoz tovora in letalskega prevoznika imamo dogovor o pošiljanju iPadov iz Šanghaja, Pekinga in Hong Konga v spodaj prikazane distribucijske centre..
Cena vsakega iPada je razdalja med tovarno in distribucijskim centrom do obrata, ki je razdeljena na 20.000 kilometrov. Od Šanghaja do Melbourna je na primer 8.024 km, kar je 8.024 / 20.000 ali $ 40 na iPad.
Vprašanje je, kako bomo vse te iPad-e iz teh treh naprav poslali na te štiri destinacije z najnižjimi možnimi stroški?
Kot si lahko predstavljate, je to lahko zelo težko brez neke formule in orodja. V tem primeru moramo poslati 462.000 (F12) skupnih iPad-ov. Obrati imajo omejeno zmogljivost 500.250 (G12) enot.
V preglednici, da lahko vidite, kako deluje, smo v celico B10 vnesli 1, kar pomeni, da želimo poslati 1 iPad iz Šanghaja v Melbourne. Ker so stroški prevoza po tej poti 0,40 USD na iPad, skupni stroški (B17) znašajo 0,40 $.
Število je bilo izračunano z uporabo funkcije = SUMPRODUCT (stroški, dobavljeni) »stroški« so razponi B3: E5.
"Pošiljka" je območje B9: E11:
SUMPRODUCT pomnoži »stroške« krat v razponu »odpremljeno« (B14). To se imenuje »množenje matrik«.
Da bi SUMPRODUCT deloval pravilno, morata biti dve matrici - stroški in dobavljeni - enake velikosti. To omejitev lahko odpravite tako, da ustvarite dodatne stroške in pošiljate stolpce in vrstice z ničelno vrednostjo, tako da so polja enake velikosti in ne vplivajo na skupne stroške..
Uporaba Solverja
Če bi vse, kar smo morali storiti, pomnožili matrike "stroški" krat "dobavljeni", ki ne bi bili preveč zapleteni, moramo pa se ukvarjati tudi z omejitvami tam..
Vsako distribucijsko središče moramo dostaviti. To konstanto vnesemo v reševalec takole: $ B $ 12: $ E $ 12> = $ B $ 13: $ E $ 13. To pomeni, da je vsota pošiljk, tj. Skupnih zneskov v celicah $ B $ 12: $ E $ 12, večja ali enaka vrednosti, ki jo zahteva vsak distribucijski center ($ B $ 13: $ E $ 13).
Ne moremo dostaviti več, kot proizvajamo. Te omejitve zapišemo takole: $ 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.
Zdaj pojdite v meni »Podatki« in pritisnite gumb »Solver«. Če gumba »Solver« ni na voljo, morate omogočiti dodatek Solver.
Vnesite dve prej opisani omejitvi in izberite obseg »Pošiljke«, ki je obseg številk, ki jih želimo Excel izračunati. Prav tako izberite privzeti algoritem »Simplex LP« in navedite, da želimo »zmanjšati« celico B15 (»skupni stroški pošiljanja«), kjer piše »Nastavi cilj«.
Pritisnite »Solve« in Excel shrani rezultate v preglednico, kar želimo. To lahko shranite, da se lahko igrate z drugimi scenariji.
Če računalnik pravi, da ne more najti rešitve, potem ste naredili nekaj, kar ni logično, na primer, morda ste zahtevali več iPads, kot jih lahko proizvedejo rastline.
Excel pravi, da je našel rešitev. Pritisnite »OK«, da ohranite rešitev in se vrnete v preglednico.
Primer: neto sedanja vrednost
Kako se podjetje odloči, ali bo vlagalo v nov projekt? Če je »neto sedanja vrednost« (NPV) pozitivna, bodo vlagala v to. To je standardni pristop večine finančnih analitikov.
Recimo, da rudarsko podjetje Codelco želi razširiti rudnik bakra Andinas. Standardni pristop za določitev, ali naj se projekt nadaljuje, je izračun neto sedanje vrednosti. Če je NPV večja od nič, bo projekt dobičkonosen ob dveh vnosih (1) časa in (2) stroškov kapitala..
Na preprostem angleškem jeziku stroški kapitala pomenijo, koliko bi ta denar zaslužil, če bi ga le zapustili v banki. Stroške kapitala uporabite za diskontiranje gotovinskih vrednosti na sedanjo vrednost, kar pomeni, da bi danes 100 dolarjev v petih letih lahko znašali 80 dolarjev.
V prvem letu je za financiranje projekta namenjenih 45 milijonov USD. Računovodje so ugotovili, da je njihov strošek kapitala šest odstotkov.
Ko začnejo rudarjenje, denar začne prihajati, ko podjetje najde in proda bakra, ki ga proizvajajo. Očitno je, da bolj ko so moje, več denarja, in njihova napoved kaže, da se njihov denarni tok povečuje, dokler ne doseže 9 milijonov dolarjev na leto..
Po 13 letih je NPV 3 945 074 USD, zato bo projekt donosen. Po mnenju finančnih analitikov je obdobje izplačevanja 13 let.
Ustvarjanje vrtilne tabele
»Pivot tabela« je v bistvu poročilo. Imenujemo jih vrtilne tabele, ker jih lahko preprosto zamenjate za eno vrsto poročila v drugo, ne da bi morali narediti celotno novo poročilo. Torej oni pivot na mestu. Pokažimo osnovni primer, ki uči osnovne koncepte.
Primer: poročila o prodaji
Prodajalci so zelo konkurenčni (to je del prodajalca), zato seveda želijo vedeti, kako se na koncu četrtletja in konca leta gibljejo drug proti drugemu, in koliko bodo njihove provizije.
Recimo, da imamo tri prodajalce - Carlosa, Freda in Julie - vsi prodajajo nafto. Njihova prodaja v dolarjih po fiskalnem četrtletju za leto 2014 je prikazana v spodnji preglednici.
Če želite ustvariti ta poročila, izdelamo vrtilno tabelo:
Izberite »Vstavi -> vrtilna tabela, ki je na levi strani orodne vrstice:
Izberite vse vrstice in stolpce (vključno z imenom prodajalca), kot je prikazano spodaj:
Pogovorno okno vrtilne tabele se prikaže na desni strani preglednice.
Če v pogovornem oknu vrtilne tabele kliknemo vsa štiri polja (četrtletje, leto, prodaja in prodajalec), Excel v preglednico doda poročilo, ki nima nobenega smisla, ampak zakaj?
Kot lahko vidite, smo za dodajanje poročila izbrali vsa štiri polja. Excelovo privzeto obnašanje je združevanje vrstic z besedilnimi polji in nato vsota preostalih vrstic.
Tukaj nam daje vsoto leta 2014 + 2014 + 2014 + 2014 = 24.168, kar je nesmisel. Prav tako je dala vsoto četrtin 1 + 2 + 3 + 4 = 10 * 3 = 3 0. Te informacije ne potrebujemo, zato jih odstranimo iz pivotne tabele..
»Vsota prodaje« (skupna prodaja) je primerna, zato jo bomo popravili.
Primer: Prodaja prodajalca
Lahko uredite »vsoto prodaje«, da bi rekli »skupna prodaja«, kar je bolj jasno. Prav tako lahko celice oblikujete kot valuto, tako kot bi formatirali druge celice. Najprej kliknite na “Sum of Sales” in izberite “Value Field Settings”.
V nastalem pogovornem oknu spremenite ime v »Celotna prodaja«, nato kliknite »Oblika številk« in ga spremenite v »Valuta«.
Nato si lahko ogledate ročno delo v vrtilni tabeli:
Primer: Prodaja prodajalca in četrtletja
Sedaj dodamo delne vsote za vsako četrtletje. Če želite dodati vmesne vsote, samo kliknite z levim klikom na polje »Četrtina« in ga pridržite in povlecite v razdelek »vrstice«. Rezultat si lahko ogledate na spodnjem posnetku zaslona:
Ko smo že pri tem, odstranimo vrednosti »Sum of Quarter«. Preprosto kliknite puščico in kliknite »Odstrani polje«. Na posnetku zaslona lahko zdaj vidite, da smo dodali vrstice »Četrtina«, ki razčlenijo prodajo vsakega prodajalca za četrtino.
Ker so te veščine sveže v mislih, lahko zdaj ustvarjate vrtilne tabele iz lastnih podatkov!
Zaključek
Dokončali smo vam predstavitev nekaterih značilnosti formul in funkcij programa Microsoft Excel, ki jih lahko uporabite v Microsoft Excelu za svoje poslovne, akademske ali druge potrebe..
Kot ste videli, je Microsoft Excel ogromen izdelek s toliko funkcijami, da večina ljudi, tudi naprednih uporabnikov, ne pozna vseh. Nekateri ljudje bi lahko rekli, da je to zapleteno; menimo, da je bolj celovit.
Upajmo, da vam predstavljamo veliko primerov iz resničnega življenja, vendar smo pokazali ne le funkcije, ki so na voljo v programu Microsoft Excel, temveč so vas naučili nekaj o statistiki, linearnem programiranju, ustvarjanju grafikonov, z uporabo naključnih števil in drugih idej, ki jih lahko zdaj sprejmete in v vaši šoli ali na delovnem mestu.
Zapomnite si, če se želite vrniti nazaj in ponovno vzeti razred, lahko začnete novo s 1. lekcijo!
