Ieškos, diagramos, statistika ir posūkio lentelės

Peržiūrėję pagrindines funkcijas, ląstelių nuorodas ir datos ir laiko funkcijas, mes dabar pasineriame į kai kurias pažangesnes „Microsoft Excel“ funkcijas. Pateikiame metodus, kaip išspręsti klasikines finansų, pardavimo ataskaitų, pristatymo išlaidų ir statistikos problemas.

MOKYKLOS NAVIGACIJA

1. Kodėl jums reikia formulių ir funkcijų?
2. Formulės apibrėžimas ir kūrimas
3. Santykinė ir absoliuti ląstelių nuoroda ir formatavimas
4. Naudingos funkcijos, kurias turėtumėte žinoti
5. Ieškos, diagramos, statistika ir posūkio lentelės

Šios funkcijos yra svarbios verslui, studentams ir tiems, kurie tik nori daugiau sužinoti.

VLOOKUP ir HLOOKUP

Čia pateikiamas pavyzdys, kaip parodyti vertikalios paieškos (VLOOKUP) ir horizontalios paieškos (HLOOKUP) funkcijas. Šios funkcijos naudojamos skaičiui ar kitai vertei versti į suprantamą. Pavyzdžiui, galite naudoti „VLOOKUP“, kad galėtumėte paimti dalies numerį ir grąžinti elemento aprašymą.

Norėdami tai ištirti, grįžkime prie „Sprendimų kūrėjo“ skaičiuoklės 4 dalyje, kur Jane bando nuspręsti, ką dėvėti mokykloje. Ji nebėra suinteresuota, ką ji nešioja, nes ji nusileido nauju draugu, todėl dabar ji dėvės atsitiktinius drabužius ir batus.

Jane'o skaičiuoklėje ji nurodo drabužius vertikaliuose stulpeliuose ir batuose, horizontaliuose stulpeliuose.

Ji atveria skaičiuoklę ir funkcija RANDBETWEEN (1,3) sukuria skaičių nuo vieno ar trijų, atitinkančių trijų rūšių drabužius, kuriuos ji gali dėvėti..

Ji naudoja funkciją RANDBETWEEN (1,5), kad pasiektų penkių rūšių batus.

Kadangi „Jane“ negali nešioti skaičiaus, kurį turime konvertuoti į pavadinimą, mes naudojame paieškos funkcijas.

Mes naudojame „VLOOKUP“ funkciją, jei norite išversti aprangos numerį į aprangos pavadinimą. HLOOKUP iš batų numerio paverčia eilę įvairių rūšių batų.

Skaičiuoklė veikia taip, kaip numatyta:

„Excel“ renka atsitiktinį skaičių nuo vieno iki trijų, nes ji turi tris aprangos parinktis.

Toliau formulė perskaičiuoja skaičių į tekstą, naudodama = VLOOKUP (B11, A2: B4,2), kuri naudoja atsitiktinį skaičių nuo B11 reikšmės, kad matytų intervalą A2: B4. Tada jis pateikia rezultatą (C11) iš antrame stulpelyje išvardytų duomenų.

Mes naudojame tą pačią techniką, kad galėtume pasirinkti batus, išskyrus tai, kad šį kartą vietoj HLOOKUP naudojame VOOKUP.

Pavyzdys: pagrindinė statistika

Beveik visi žino vieną statistinę formulę - vidurkį, tačiau yra dar vienas svarbus verslui svarbus statistinis rodiklis: standartinis nuokrypis.

Pavyzdžiui, daugelis koledžo nuvykusio asmens pasipiktino savo SAT rezultatu. Jie gali norėti sužinoti, kaip jie lyginami su kitais studentais. Universitetai taip pat nori tai žinoti, nes daugelis universitetų, ypač prestižinių, atsisako studentų, turinčių mažą SAT balą.

Taigi, kaip mes, ar universitetas, matuojame ir interpretuojame SAT balus? Žemiau yra SAT balai penkiems studentams nuo 1,870 iki 2,230.

Svarbūs suprantami numeriai yra:

Vidutinis - Vidutinis taip pat vadinamas „vidurkiu“.

Standartinis nuokrypis (STD arba σ) - Šis skaičius rodo, kaip plačiai išsklaidyti numeriai. Jei standartinis nuokrypis yra didelis, skaičiai yra toli vienas nuo kito ir, jei jis yra nulis, visi skaičiai yra vienodi. Galima sakyti, kad standartinis nuokrypis yra vidutinis skirtumas tarp vidutinės vertės ir stebimos vertės, t. Y. Atkreipkite dėmesį, kad yra įprasta sutrumpinti standartinį nuokrypį naudojant graikų simbolį sigma „σ“.

Procentinis reitingas - Kai studentas gauna aukštą rezultatą, jie gali pasigirti, kad jie yra viršutiniame 99 procentilyje arba kažką panašaus. „Procentinis rangas“ reiškia, kad balų procentas yra mažesnis nei vienas konkretus balas.

Standartiniai nuokrypiai ir tikimybė yra glaudžiai susiję. Galima sakyti, kad kiekvienam standartiniam nuokrypiui tikimybė ar tikimybė, kad šis skaičius yra tame standartiniame nuokrypyje, yra:

STD	Vertinimų procentas	SAT balų diapazonas
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

Kaip matote, tikimybė, kad bet kuris SAT rezultatas yra už 3 STD, yra praktiškai nulis, nes 99,73 proc. Balų yra 3 STD.

Dabar pažiūrėkime į skaičiuoklę ir paaiškinsime, kaip jis veikia.

Dabar mes paaiškiname formules:

= VIDUTINIS (B2: B6)

Visų balų vidurkis intervale B2: B6. Konkrečiai, visų balų suma padalinta iš žmonių, kurie paėmė testą.

= STDEV.P (B2: B6)

Standartinis nuokrypis nuo B2: B6. „.P“ reiškia STDEV.P naudojamas visuose baluose, t.y., visai populiacijai, o ne tik pogrupiui.

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

Šiuo atveju apskaičiuojamas kumuliacinis procentas per intervalą B2: B6, remiantis SAT rezultatu, šiuo atveju B2. Pavyzdžiui, 83 proc. Balų yra mažesni už Walker rezultatą.

Rezultatų grafika

Rezultatų pateikimas grafike palengvina rezultatų suvokimą, taip pat galite jį parodyti pateiktyje, kad jūsų taškas būtų aiškesnis.

Studentai yra horizontalioje ašyje, o jų SAT balai rodomi kaip mėlynos juostos diagrama skalėje (vertikali ašis) nuo 1600 iki 2300.

Procentilio reitingas yra dešinysis vertikali ašis nuo 0 iki 90 proc. Ir yra vaizduojama pilka linija.

Kaip sukurti diagramą

Diagramos kūrimas yra tema pati, tačiau trumpai paaiškinsime, kaip buvo sukurta pirmiau pateikta diagrama.

Pirma, pasirinkite langelių diapazoną. Šiuo atveju A2 - C6, nes norime, kad numeriai ir studento vardai.

Meniu „Įterpti“ pasirinkite „Charts“ -> „Recommended Charts“:

Kompiuteris rekomenduoja „Clustered-Column, Secondary Axis“ diagramą. „Antrinės ašies“ dalis reiškia, kad ji traukia dvi vertikalias ašis. Šiuo atveju ši diagrama yra ta, kurią norime. Mums nereikia nieko daryti.

Galite naudoti judėti diagramą ir perkelti ją iki tol, kol jį matysite kaip dydį ir norimą vietą. Kai esate patenkinti, galite išsaugoti diagramą skaičiuoklėje.

Jei dešiniuoju pelės klavišu spustelėsite diagramą, tada „Select Data“, tai parodys, kokie duomenys yra pasirinkti diapazonui.

„Rekomenduojamos diagramos“ funkcija paprastai pašalina jus nuo tokių sudėtingų detalių, kaip nustatyti, kokie duomenys turi būti įtraukti, kaip priskirti etiketes ir kaip priskirti vertikaliąsias kairias ir dešines ašis.

Dialogo lange „Pasirinkti duomenų šaltinį“ spustelėkite „rezultatas“, esantį „Legend įrašai (serija)“, ir paspauskite „Redaguoti“ ir pakeiskite, kad būtų pasakyta „Rezultatas“.

Tada pakeiskite 2 seriją („procentilė“) į „procentinę“.

Grįžkite į savo diagramą ir spustelėkite „Diagramos pavadinimas“ ir pakeiskite jį į „SAT balus“. Dabar turime pilną diagramą. Joje yra dvi horizontalios ašys: viena SAT taškui (mėlyna) ir viena sukauptam procentui (oranžinė).

Pavyzdys: transporto problema

Transporto problema yra klasikinis matematikos tipo, vadinamo „linijiniu programavimu“, pavyzdys. Tai leidžia maksimaliai padidinti arba sumažinti vertę, kuriai taikomi tam tikri apribojimai. Ji turi daug programų, skirtų įvairioms verslo problemoms spręsti, todėl naudinga sužinoti, kaip ji veikia.

Prieš pradėdami naudoti šį pavyzdį turime įjungti „Excel Solver“.

Įgalinti „Solver“ priedą

Pasirinkite „File“ -> „Options“ -> „Add-ins“. Priedų parinkčių apačioje spustelėkite mygtuką „Eiti“, esantį šalia „Tvarkyti:„ Excel “priedus“.

Pasirinktame meniu spustelėkite žymės langelį, kad įjungtumėte „Solver Add-in“ ir spustelėkite „OK“.

Pavyzdys: Apskaičiuokite mažiausias „iPad“ pristatymo išlaidas

Tarkime, mes pristatome „iPad“ ir stengiamės užpildyti mūsų platinimo centrus naudojant mažiausias transportavimo išlaidas. Mes turime susitarimą su krovinių vežimo ir oro linijų bendrove, kad „iPad“ iš Šanchajaus, Pekino ir Honkongo išsiųstume į žemiau pateiktus platinimo centrus.

Kiekvienos iPad pristatymo kaina yra atstumas nuo gamyklos iki skirstymo centro iki 20 000 kilometrų. Pavyzdžiui, ji yra 8,024 km nuo Šanchajaus iki Melburno, kuris yra 8,024 / 20 000 arba $ 0,40 už iPad.

Kyla klausimas, kaip mes tiekiame visus šiuos „iPad“ iš šių trijų įrenginių į šias keturias paskirties vietas už mažiausią įmanomą kainą?

Kaip galite įsivaizduoti, tai gali būti labai sunku be formulės ir įrankio. Tokiu atveju turime siųsti 462 000 (F12) visų „iPad“. Augalų talpa yra ribota 500 250 (G12) vienetų.

Skaičiuoklėje, kad galėtumėte matyti, kaip jis veikia, 1 langelį įvedėme į langelį B10, ty norime siųsti 1 „iPad“ iš „Shanghai“ į Melburną. Kadangi pervežimo išlaidos šiame maršrute yra 0,40 JAV dolerių už iPad, bendra kaina (B17) yra 0,40 USD.

Skaičius apskaičiuotas naudojant funkciją = SUMPRODUCT (išlaidos, pristatytos) „sąnaudos“ yra intervalai B3: E5.

Ir „išsiųsti“ yra intervalas B9: E11:

„SUMPRODUCT“ padaugina „sąnaudas“ kartų intervalu „išsiųstas“ (B14). Tai vadinama „matricos dauginimu“.

Kad SUMPRODUCT tinkamai veiktų, dvi matricos - išlaidos ir siuntos - turi būti tokio paties dydžio. Šį apribojimą galite gauti papildomų išlaidų ir pristatymo stulpelių bei eilučių, kurių vertė yra nulinė, kad matricos būtų vienodo dydžio ir nebūtų jokios įtakos bendroms išlaidoms..

Solverio naudojimas

Jei viskas, ką turėjome padaryti, buvo padauginta „sąnaudų“ matricų „išsiųstų“, kurios nebūtų pernelyg sudėtingos, tačiau taip pat turime spręsti ten esančius suvaržymus.

Turime išsiųsti, ko reikalauja kiekvienas platinimo centras. Šį pastovumą mes įdėjome į tokį sprendimą: $ B $ 12: $ E $ 12> = $ B $ 13: $ E $ 13. Tai reiškia, kad sumos, kurios yra išsiųstos, t. Y. Bendrosios sumos, nurodytos $ B $ 12: $ E $ 12, turi būti didesnės arba lygios kiekvienam platinimo centrui ($ B $ 13: $ E $ 13).

Mes negalime išsiųsti daugiau nei gaminame. Mes rašome, kad tokie apribojimai: $ 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.

Dabar eikite į „Duomenų“ meniu ir paspauskite „Solver“ mygtuką. Jei „Solver“ mygtukas nėra, turite įjungti „Solver“ priedą.

Įveskite du anksčiau aprašytus apribojimus ir pasirinkite „Siuntų“ diapazoną, ty skaičių, kurį norime Excel apskaičiuoti. Taip pat pasirinkite numatytąjį algoritmą „Simplex LP“ ir nurodykite, kad norime „mažinti“ langelį B15 („bendros siuntimo išlaidos“), kur sakoma „Nustatyti tikslą“.

Paspauskite „Solve“ ir „Excel“ išsaugo rezultatus į skaičiuoklę, kuri yra tai, ko norime. Taip pat galite išsaugoti, kad galėtumėte žaisti su kitais scenarijais.

Jei kompiuteris sako, kad negali rasti sprendimo, tai padarėte kažką, kas nėra logiška, pavyzdžiui, galbūt paprašėte daugiau iPad, nei augalai gali gaminti.

Čia „Excel“ sako, kad rado sprendimą. Paspauskite „OK“, kad išlaikytumėte sprendimą ir grįžtumėte į skaičiuoklę.

Pavyzdys: grynoji dabartinė vertė

Kaip įmonė nusprendžia, ar investuoti į naują projektą? Jei „grynoji dabartinė vertė“ (NPV) yra teigiama, jie investuos į ją. Tai yra standartinis požiūris, kurį laikosi dauguma finansų analitikų.

Pavyzdžiui, tarkime, kad „Codelco“ kasybos įmonė nori išplėsti Andino vario kasyklą. Standartinis metodas, kuriuo siekiama nustatyti, ar įgyvendinti projektą, yra apskaičiuoti grynąją dabartinę vertę. Jei NPV yra didesnis už nulį, projektas bus pelningas atsižvelgiant į dvi sąnaudas (1) ir (2) kapitalo sąnaudas.

Paprastai anglų kalba kapitalo sąnaudos reiškia, kiek pinigų būtų uždirbta, jei jie tiesiog paliktų banke. Jūs naudojate kapitalo kainą diskontuoti pinigų vertes dabartinei vertei, kitaip tariant, $ 100 per penkerius metus gali būti 80 JAV dolerių.

Pirmaisiais metais 45 mln. JAV dolerių skiriama kaip kapitalas projektui finansuoti. Buhalteriai nustatė, kad jų kapitalo kaina yra šeši procentai.

Kai jie pradeda kasybą, grynieji pinigai prasideda, kai bendrovė suranda ir parduoda gaminamą varį. Akivaizdu, kad kuo daugiau jie mano, tuo daugiau pinigų jie gauna, o jų prognozė rodo, kad jų pinigų srautai didėja iki 9 mln..

Po 13 metų NPV yra $ 3,945,074 USD, todėl projektas bus pelningas. Pasak finansų analitikų, „atsipirkimo laikotarpis“ yra 13 metų.

Sukimo lentelės kūrimas

„Pivot“ lentelė iš esmės yra ataskaita. Mes juos vadiname „stumdomomis“ lentelėmis, nes jūs galite lengvai perjungti vieną ataskaitos tipą į kitą, neatlikus viso naujo pranešimo. Taigi jie posūkis vietoje. Parodykime pagrindinį pavyzdį, kuris moko pagrindines sąvokas.

Pavyzdys: pardavimų ataskaitos

Pardavimų žmonės yra labai konkurencingi (tai yra pardavėjo dalis), todėl jie natūraliai nori sužinoti, kaip ketvirčio pabaigoje ir metų pabaigoje jie tarnauja vienas kitam, ir kiek jų komisiniai bus.

Tarkime, mes turime tris pardavimų žmones - Carlosą, Fredą ir Juliją - visus parduodant naftą. Jų pardavimai doleriais už 2014 m. Fiskalinį ketvirtį pateikiami žemiau esančioje skaičiuoklėje.

Jei norite sukurti šias ataskaitas, mes sukursime stalo lentelę:

Pasirinkite „Insert -> Pivot Table“, ji yra kairėje įrankių juostos pusėje:

Pasirinkite visas eilutes ir stulpelius (įskaitant pardavėjo pavadinimą), kaip parodyta toliau:

Atsidariusio stalo dialogo langas rodomas skaičiuoklės dešinėje pusėje.

Jei spustelėjome visus keturis laukus, esančius dialogo lango (ketvirčio, ​​metų, pardavimų ir pardavėjo), Excel prideda ataskaitą į skaičiuoklę, kuri neturi prasmės, bet kodėl?

Kaip matote, mes pasirinkome visus keturis laukus, kuriuos norite pridėti prie ataskaitos. Numatytasis „Excel“ elgesys yra grupuoti eilutes pagal teksto laukus ir tada susumuoti visas likusias eilutes.

Čia mums pateikiama 2014 + 2014 + 2014 + 2014 = 24,168 metų suma, kuri yra nesąmonė. Be to, tai buvo 1 + 2 + 3 + 4 = 10 * 3 = 3 0 ketvirčių suma. Mums šios informacijos nereikia, todėl panaikinome šiuos laukus, kad juos pašalintume iš mūsų stalo.

Tačiau „Pardavimų suma“ (bendra pardavimų suma) yra tinkama, todėl mes ją išspręsime.

Pavyzdys: Pardavėjo pardavimai

Jūs galite redaguoti „Pardavimų sumą“, kad būtų galima pasakyti „Iš viso pardavimų“. Taip pat galite formatuoti ląsteles kaip valiutą kaip ir bet kokias kitas ląsteles. Pirmiausia spustelėkite „Pardavimų suma“ ir pasirinkite „Vertės lauko parametrai“.

Atsidariusiame dialogo lange pavadinimą pakeisime į „Total Sales“, tada spustelėkite „Number Format“ ir pakeiskite jį į „Valiuta“.

Tada galite matyti savo rankų darbus stulpelyje:

Pavyzdys: pardavėjas ir ketvirtis

Dabar pridėkime kiekvieno ketvirčio tarpines sumas. Jei norite pridėti dalinių sumų, spustelėkite kairįjį pelės klavišą lauke „Ketvirtis“ ir palaikykite ir vilkite jį į „eilutes“. Galite pamatyti rezultatą žemiau esančioje ekrano nuotraukoje:

Kol mes esame, pašalinkime „Ketvirčio sumą“. Tiesiog spustelėkite rodyklę ir spustelėkite „Pašalinti lauką“. Ekrane, dabar galite matyti, kad pridėjome eilutes „Ketvirtis“, kurios suskirsto kiekvieno pardavėjo pardavimus pagal ketvirtį.

Turėdami omenyje tuos įgūdžius, dabar galite kurti savo duomenis iš stalo!

Išvada

Supakavę parodėme kai kurias „Microsoft Excel“ formulių ir funkcijų funkcijas, kurias galite taikyti „Microsoft Excel“ savo verslo, akademiniams ar kitiems poreikiams.

Kaip matėte, „Microsoft Excel“ yra milžiniškas produktas su tiek daug funkcijų, kad dauguma žmonių, net ir patyrusių vartotojų, ne visi žino. Kai kurie žmonės gali pasakyti, kad tai tampa sudėtinga; mes manome, kad tai išsamesnė.

Tikimės, kad pateikdami daug realių pavyzdžių, mes parodėme ne tik „Microsoft Excel“ funkcijas, bet ir išmokėme jums kažką apie statistiką, linijinį programavimą, diagramų kūrimą, naudojant atsitiktinius numerius ir kitas idėjas, kurias dabar galite priimti ir naudoti savo mokykloje arba kur dirbate.

Atminkite, kad jei norite vėl grįžti ir vėl imtis klasės, galite pradėti šviežią pamoką 1!