Pretraživanja, grafikoni, statistika i stožerne tablice
Pregledavši osnovne funkcije, reference na ćelije i funkcije datuma i vremena, sada ulazimo u neke od naprednijih značajki programa Microsoft Excel. Predstavljamo metode rješavanja klasičnih problema u financijama, izvješćima o prodaji, troškovima dostave i statistici.
ŠKOLSKA NAVIGACIJA- Zašto trebate formule i funkcije?
- Definiranje i stvaranje formule
- Relativna i apsolutna referenca ćelije i oblikovanje
- Korisne funkcije koje trebate upoznati
- Pretraživanja, grafikoni, statistika i stožerne tablice
Te su funkcije važne za poslovne subjekte, studente i one koji samo žele naučiti više.
VLOOKUP i HLOOKUP
Evo primjera za ilustraciju funkcija vertikalnog traženja (VLOOKUP) i horizontalnog traženja (HLOOKUP). Ove se funkcije koriste za prevođenje broja ili druge vrijednosti u nešto što je razumljivo. Na primjer, možete koristiti VLOOKUP za uzimanje broja dijela i povrat opisa stavke.
Da bismo to istražili, vratimo se našoj “donositelju odluka” u četvrtom dijelu, gdje Jane pokušava odlučiti što će nositi u školi. Više nije zainteresirana za ono što nosi, jer je dobila novog dečka pa će sada nositi slučajne odjeće i cipele.
U Janeinoj proračunskoj tablici, ona navodi odjeću u vertikalnim stupcima i cipelama, horizontalne stupce.
Ona otvara proračunsku tablicu i funkcija RANDBETWEEN (1,3) generira broj između ili jednak jednom i tri koji odgovaraju trima vrstama odjeće koje može nositi.
Koristi funkciju RANDBETWEEN (1,5) za odabir između pet vrsta cipela.
Budući da Jane ne može nositi broj koji trebamo pretvoriti u ime, koristimo funkcije pretraživanja.
Koristimo funkciju VLOOKUP kako bismo preveli broj odjeće u ime odjeće. HLOOKUP se prevodi od broja cipela do različitih vrsta cipela u retku.
Proračunska tablica radi ovako za odjeću:
Excel odabire slučajni broj od jedan do tri, budući da ima tri opcije odijela.
Zatim formula prevodi broj u tekst koristeći = VLOOKUP (B11, A2: B4,2) koji koristi slučajni broj od B11 da bi gledao u rasponu A2: B4. Zatim daje rezultat (C11) iz podataka navedenih u drugom stupcu.
Koristimo istu tehniku za biranje cipela, osim što ovaj put koristimo VOOKUP umjesto HLOOKUP.
Primjer: Osnovna statistika
Gotovo svatko poznaje jednu formulu iz statistike - prosječnu - ali postoji još jedna statistika koja je važna za poslovanje: standardna devijacija.
Na primjer, mnogi koji su otišli na koledž agonizirali su zbog svog SAT rezultata. Možda žele znati kako se rangiraju u usporedbi s drugim učenicima. Sveučilišta to žele znati i zato što mnoga sveučilišta, osobito ona prestižna, odbijaju studente s niskim SAT bodovima.
Pa kako bismo mi, ili sveučilište, mjerili i interpretirali SAT rezultate? Ispod su SAT rezultati za pet studenata u rasponu od 1.870 do 2.230.
Važni brojevi za razumijevanje su:
prosječan - Prosjek se također naziva "srednja vrijednost".
Standardno odstupanje (STD ili σ) - Ovaj broj pokazuje koliko su široko raspršeni skupovi brojeva. Ako je standardna devijacija velika, onda su brojevi udaljeni i ako je nula, svi brojevi su isti. Moglo bi se reći da je standardna devijacija prosječna razlika između prosječne vrijednosti i promatrane vrijednosti, tj. 1,998 i svakog SAT rezultata. Imajte na umu da je uobičajeno skraćivanje standardne devijacije pomoću grčkog simbola sigma “σ.”
Postotni rang - Kada učenik dobije visoku ocjenu, mogu se pohvaliti da su u vrhu 99 posto ili nešto slično. “Postotni rang” znači postotak bodova manji od jednog određenog rezultata.
Standardna devijacija i vjerojatnost su usko povezane. Možete reći da je za svaku standardnu devijaciju vjerojatnost ili vjerojatnost da je taj broj unutar tog broja standardnih devijacija:
| STD | Postotak bodova | Raspon SAT rezultata |
| 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 |
Kao što možete vidjeti, vjerojatnost da je bilo koji SAT rezultat izvan 3 STD-a je praktički nula, jer je 99,73 posto bodova unutar 3 STD-a..
Pogledajmo ponovno proračunsku tablicu i objasnimo kako ona funkcionira.
Sada ćemo objasniti formule:
= Srednja (B2: B6)
Prosjek svih rezultata u rasponu B2: B6. Konkretno, zbroj svih rezultata podijeljen s brojem ljudi koji su položili test.
= STDEV.P (B2: B6)
Standardna devijacija u rasponu B2: B6. "P" znači STDEV.P se koristi za sve rezultate, tj. Za cijelu populaciju, a ne samo za podskup..
= PERCENTRANK.EXC (B $ $ 2: $ B $ 6 B2)
To izračunava kumulativni postotak iznad raspona B2: B6 na temelju SAT rezultata, u ovom slučaju B2. Na primjer, 83 posto bodova je ispod Walkerovog rezultata.
Grafički prikaz rezultata
Postavljanje rezultata u grafikon olakšava razumijevanje rezultata, a možete ga prikazati u prezentaciji kako biste jasnije izrazili svoje mišljenje.
Studenti su na horizontalnoj osi, a njihovi SAT rezultati prikazani su kao plavi stupčasti grafikon na skali (vertikalna os) od 1.600 do 2.300.
Rangiranje postotaka je desna okomita os od 0 do 90 posto i predstavljena je sivom linijom.
Kako stvoriti grafikon
Stvaranje grafikona tema je za sebe, ali ukratko ćemo objasniti kako je grafikon izrađen.
Najprije odaberite raspon ćelija koje će se nalaziti u grafikonu. U ovom slučaju A2 do C6 jer želimo brojeve kao i imena učenika.
Iz izbornika "Umetni" odaberite "Grafikoni" -> "Preporučene karte":
Računalo preporučuje grafikon "Clustered-Column, Secondary Axis". Dio "sekundarne osi" znači da izvlači dvije vertikalne osi. U ovom slučaju, ova karta je ona koju želimo. Ne moramo ništa drugo raditi.
Možete pomicati grafikon okolo i mijenjati veličinu dok ne dobijete veličinu i željeni položaj. Kada budete zadovoljni, grafikon možete spremiti u proračunsku tablicu.
Ako desnom tipkom miša kliknete na grafikon, a zatim odaberete "Odaberi podatke", prikazat će se koji su podaci odabrani za raspon.
Značajka "preporučene karte" obično vas nagovara da se ne morate nositi s tako složenim detaljima kao što je određivanje podataka koje želite uključiti, kako dodijeliti oznake i kako dodijeliti lijevu i desnu vertikalnu os.
U dijaloškom okviru "Select Data Source" (Odabir izvora podataka), kliknite "score" (Ocjena) u odjeljku "Legend Entries (Series) (" Legenda unosi (serije) "i pritisnite" Edit "(Uređivanje) i promijenite je kako biste izgovorili" Score "(Ocjena).
Zatim promijenite seriju 2 (“percentil”) u “Percentile”.
Vratite se na svoj grafikon i kliknite na "Chart Title" i promijenite ga na "SAT Scores". Sada imamo kompletan dijagram. Ima dvije horizontalne osi: jednu za SAT rezultat (plavu) i jednu za kumulativni postotak (narančastu).
Primjer: Problem prijevoza
Problem prijevoza je klasičan primjer vrste matematike koja se naziva "linearno programiranje". To vam omogućuje da povećate ili minimizirate vrijednost pod određenim ograničenjima. Ima mnogo aplikacija za širok raspon poslovnih problema, pa je korisno naučiti kako funkcionira.
Prije nego što počnemo s ovim primjerom moramo omogućiti "Excel Solver".
Omogući dodatak za Solver
Odaberite "Datoteka" -> "Opcije" -> "Dodaci". Na dnu opcija dodataka kliknite gumb "Idi" pored opcije "Upravljanje: Excelovi dodaci".
Na rezultirajućem izborniku kliknite potvrdni okvir da biste omogućili "Solver Add-in" i kliknite "OK".
Primjer: Izračunajte najniže IPad troškove prijevoza
Pretpostavimo da isporučujemo iPads i pokušavamo ispuniti naše distribucijske centre pomoću najnižih mogućih troškova prijevoza. Imamo ugovor s tvrtkom za prijevoz i prijevoz zrakoplovom da isporučujemo iPads iz Šangaja, Pekinga i Hong Konga u distribucijske centre prikazane u nastavku.
Cijena isporuke svakog iPad-a je udaljenost od tvornice do distributivnog centra do postrojenja podijeljenog s 20.000 kilometara. Na primjer, to je 8.024 km od Šangaja do Melbournea što iznosi 8.024 / 20.000 ili $ .40 po iPad-u.
Pitanje je kako ćemo isporučiti sve ove iPad-ove iz ova tri postrojenja na ove četiri destinacije po najnižoj mogućoj cijeni?
Kao što možete zamisliti, shvatiti da to može biti vrlo teško bez neke formule i alata. U ovom slučaju moramo poslati 462.000 (F12) ukupnih iPada. Postrojenja imaju ograničen kapacitet od 500.250 (G12) jedinica.
U proračunskoj tablici, tako da možete vidjeti kako radi, upisali smo 1 u ćeliju B10, što znači da želimo poslati 1 iPad iz Šangaja u Melbourne. Budući da su troškovi prijevoza duž te rute 0,40 USD po uređaju iPad, ukupni trošak (B17) je 0,40 USD.
Broj je izračunat pomoću funkcije = SUMPRODUCT (troškovi, otpremljeni) "troškovi" su rasponi B3: E5.
A "isporučen" je raspon B9: E11:
SUMPRODUCT umnožava "troškove" puta raspona "isporučen" (B14). To se naziva "množenje matrica."
Da bi SUMPRODUCT radio ispravno, dvije matrice - troškovi i isporuke - moraju biti iste veličine. To ograničenje možete zaobići tako da napravite dodatne troškove i dostavljajte stupce i retke s nultom vrijednosti tako da su polja iste veličine i da nema utjecaja na ukupne troškove.
Korištenje Solver
Ako je sve što smo trebali učiniti, pomnožiti matrice "troškova" puta "otpremljenih" koje ne bi bile previše komplicirane, ali moramo se nositi s ograničenjima tamo.
Moramo poslati ono što svaki distribucijski centar zahtijeva. Tu konstantu stavljamo u solver ovako: $ B $ 12: $ E $ 12> = $ B $ 13: $ E $ 13. To znači da zbroj isporučenog, tj. Ukupnih iznosa u ćelijama $ B $ 12: $ E $ 12, mora biti veći ili jednak onome što svaki distribucijski centar zahtijeva ($ B $ 13: $ E $ 13).
Ne možemo slati više nego što proizvodimo. Pišemo ograničenja poput ovog: $ 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.
Sada idite na izbornik “Data” i pritisnite gumb “Solver”. Ako ne postoji gumb "Solver", morate omogućiti dodatak Solver.
Upišite dva ranije opisana ograničenja i odaberite raspon "Pošiljke", koji je raspon brojeva koje želimo Excel izračunati. Također odaberite zadani algoritam “Simplex LP” i naznačite da želimo “minimizirati” ćeliju B15 (“ukupni troškovi isporuke”), gdje piše “Postavi cilj”.
Pritisnite “Solve” i Excel sprema rezultate u proračunsku tablicu, što i želimo. Možete ga spremiti tako da se možete igrati s drugim scenarijima.
Ako računalo kaže da ne može pronaći rješenje, onda ste učinili nešto što nije logično, na primjer, možda ste zatražili više iPada nego što biljke mogu proizvesti.
Ovdje Excel kaže da je pronašao rješenje. Pritisnite “OK” da zadržite rješenje i vratite se u proračunsku tablicu.
Primjer: neto sadašnja vrijednost
Kako tvrtka odlučuje hoće li ulagati u novi projekt? Ako je “neto sadašnja vrijednost” (NPV) pozitivna, oni će u nju ulagati. To je standardni pristup koji je većina financijskih analitičara.
Na primjer, pretpostavimo da rudarska tvrtka Codelco želi proširiti rudnik bakra Andinas. Standardni pristup za određivanje hoće li se nastaviti s projektom je izračunati neto sadašnju vrijednost. Ako je NPV veća od nule, tada će projekt biti profitabilan s obzirom na dva ulaza (1) vrijeme i (2) trošak kapitala.
U običnom engleskom jeziku, trošak kapitala znači koliko bi taj novac zaradio da su ga ostavili u banci. Trošak kapitala koristite da biste diskontirali novčane vrijednosti na sadašnju vrijednost, drugim riječima 100 dolara u pet godina može biti danas 80 dolara.
U prvoj godini, 45 milijuna dolara izdvojeno je kao kapital za financiranje projekta. Računovođe su utvrdili da je njihov trošak kapitala šest posto.
Kao što su počeli rudarstvo, novac počinje dolaziti kao tvrtka pronalazi i prodaje bakra koje proizvode. Očito, što su više mojih, to više novca zarađuju, a njihova prognoza pokazuje da se njihov novčani tok povećava dok ne dosegne 9 milijuna dolara godišnje.
Nakon 13 godina, NPV iznosi 3.945.074 USD, tako da će projekt biti profitabilan. Prema financijskim analitičarima, “razdoblje povrata” je 13 godina.
Izrada stožerne tablice
“Stožerna tablica” je u osnovi izvješće. Zovemo ih stožerne tablice jer ih možete jednostavno prebaciti na jednu vrstu izvješća na drugu bez potrebe za izradom cijelog novog izvješća. Pa su stožer na mjestu. Pokažimo osnovni primjer koji podučava osnovne pojmove.
Primjer: Izvješća o prodaji
Prodajni ljudi su vrlo konkurentni (to je dio prodavača) pa prirodno žele znati kako se slažu jedni s drugima na kraju tromjesečja i kraja godine, plus koliko će im biti provizija.
Pretpostavimo da imamo tri prodavača - Carlosa, Freda i Julie - koji prodaju naftu. Njihova prodaja u dolarima po fiskalnom kvartalu za 2014. godinu prikazana je u donjoj tablici.
Da bismo generirali ta izvješća, izrađujemo stožernu tablicu:
Odaberite “Umetni -> Pivotna tablica, nalazi se na lijevoj strani alatne trake:
Odaberite sve retke i stupce (uključujući ime prodavača) kao što je prikazano u nastavku:
Dijaloški okvir stožerne tablice pojavljuje se na desnoj strani proračunske tablice.
Ako kliknemo sva četiri polja u dijaloškom okviru stožerne tablice (Četvrtina, godina, prodaja i prodavač), Excel u proračunsku tablicu dodaje izvješće koje nema smisla, ali zašto?
Kao što možete vidjeti, odabrali smo sva četiri polja za dodavanje u izvješće. Excelovo zadano ponašanje je grupirati redove prema tekstualnim poljima, a zatim zbrajati sve ostale retke.
Ovdje nam daje sumu godine 2014 + 2014 + 2014 + 2014 = 24.168, što je besmislica. Također je dala zbroj četvrtina 1 + 2 + 3 + 4 = 10 * 3 = 3 0. Ove informacije ne trebamo, pa poništavamo odabir tih polja kako bismo ih uklonili iz naše stožerne tablice..
"Zbroj prodaje" (ukupna prodaja) je bitan, pa ćemo to popraviti.
Primjer: Prodaja prodavača
Možete urediti “Zbroj prodaje”, što znači “Ukupna prodaja”, što je jasnije. Također, možete formatirati ćelije kao valutu kao što biste formatirali bilo koje druge ćelije. Najprije kliknite na "Zbroj prodaje" i odaberite "Postavke vrijednosti polja".
U dijaloškom okviru koji slijedi, promijenimo naziv u "Ukupna prodaja", zatim kliknemo "Format brojeva" i promijenimo ga u "Valuta".
Zatim možete vidjeti ručni rad u stožernoj tablici:
Primjer: Prodaja prodavača i tromjesečja
Sada dodajmo podzbrojeve za svako tromjesečje. Da biste dodali podzbrojeve, kliknite lijevom tipkom miša na polje "Četvrtina" i držite ga i povucite u odjeljak "redaka". Rezultat možete vidjeti na slici ispod:
Dok smo na tome, uklonimo vrijednosti "Sum of Quarter". Jednostavno kliknite strelicu i kliknite "Ukloni polje". Na snimci zaslona sada možete vidjeti da smo dodali retke "Četvrtina", koji raščlanjuje prodaju svakog prodavača za četvrtinu.
S obzirom na te svježe vještine, sada možete stvarati stožerne tablice iz vlastitih podataka!
Zaključak
Završili smo, pokazali smo vam neke od značajki formula i funkcija programa Microsoft Excel koje možete primijeniti u Microsoft Excelu na poslovne, akademske ili druge potrebe.
Kao što ste vidjeli, Microsoft Excel je ogroman proizvod s toliko značajki da većina ljudi, čak i napredni korisnici, ne znaju sve. Neki bi ljudi mogli reći da je to komplicirano; smatramo da je sveobuhvatniji.
Nadajmo se da ćemo vam predstaviti mnogo primjera iz stvarnog života, ali smo pokazali ne samo funkcije dostupne u programu Microsoft Excel, već smo vas naučili nešto o statistici, linearnom programiranju, stvaranju dijagrama, korištenjem slučajnih brojeva i drugih ideja koje sada možete usvojiti i koristiti u svojoj školi ili na mjestu rada.
Zapamtite, ako se želite vratiti i uzeti nastavu, možete početi svježe s Lekcijom 1!
