Nakon pregleda osnovnih funkcija, referenci za ćeliju i funkcije datuma i vremena, sada se zaronimo u neke naprednije značajke programa Microsoft Excel. Prikazujemo metode za rješavanje klasičnih problema u financijama, izvješćima o prodaji, troškovima dostave i statistici.
NAVIGACIJA ŠKOLEOve su funkcije važne za poslovne, učenike i one koji samo žele saznati više.
Evo primjera za ilustriranje funkcija vertikalnog pretraživanja (VLOOKUP) i horizontalnog pretraživanja (HLOOKUP). Ove se funkcije koriste za prevođenje broja ili druge vrijednosti u nešto što je razumljivo. Na primjer, možete upotrijebiti VLOOKUP da biste preuzeli dio broja i vratili opis stavke.
Da biste to istražili, idemo natrag u našu "Priručnik za izradu" proračunske tablice u 4. dijelu, gdje Jane pokušava odlučiti što nosi u školu. Ona više nije zainteresirana za ono što nosi, jer je sletjela novog dečka, pa će sada nositi slučajne odjeće i cipele.
U Janeovoj proračunskoj tablici navodi odjeću u vertikalnim stupovima i cipelama, horizontalnim stupcima.
Otvara proračunsku tablicu, a funkcija RANDBETWEEN (1,3) generira broj između ili jednaki jedan i tri koji odgovaraju tri vrste odjeće koje ona može nositi.
Koristi funkciju RANDBETWEEN (1,5) za odabir između pet vrsta cipela.
Budući da Jane ne može nositi broj, moramo ga pretvoriti u ime, stoga koristimo funkcije pretraživanja.
Koristimo funkciju VLOOKUP za prevođenje broja odjeće u ime odijela. HLOOKUP prevodi od broja cipela do različitih vrsta cipela u redu.
Proračunska tablica radi ovako za odjeću:
Excel odabire slučajni broj od jedan do tri, jer ima tri opcije odijela.
Sljedeća formula prevodi broj u tekst pomoću = VLOOKUP (B11, A2: B4,2) koji koristi slučajni broj vrijednosti iz B11 da bi pogledao u rasponu A2: B4. Ona zatim daje rezultat (C11) iz podataka navedenih u drugom stupcu.
Koristimo istu tehniku kako bismo odabrali cipele, osim ako ovaj put koristimo VOOKUP umjesto HLOOKUP-a.
Gotovo svatko zna jednu formulu iz statistike - prosjek - ali postoji još jedna statistika koja je važna za poslovanje: standardna devijacija.
Na primjer, mnoga osoba koja je otišla na koledž je agonizirala tijekom svog SAT rezultata. Mogli bi znati kako se rangiraju u usporedbi s ostalim studentima. Sveučilišta žele to i znati jer mnoga sveučilišta, posebice prestižni, odbacuju 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 naziva i "prosjek".
Standardna odstupanja (STD ili σ) - Ovaj broj pokazuje koliko su rasprostranjeni skup brojeva. Ako je standardna devijacija velika, onda su brojevi daleko razdvojeni i ako je nula, svi brojevi su isti. Možete reći da je standardna devijacija prosječna razlika između prosječne vrijednosti i promatrane vrijednosti, tj. 1,998 i svakog SAT rezultata. Napominjemo da je uobičajeno skratiti standardno odstupanje pomoću grčkog simbola sigma "σ".
Postotak postotka - Kada student dobije visok rezultat, može se pohvaliti da su u najboljem 99 percentilu ili nešto slično. "Stupanj postotka" znači da je postotak rezultata niži od jednog određenog rezultata.
Standardna devijacija i vjerojatnost usko su povezani. Možete reći da za svaku standardnu devijaciju, vjerojatnost ili vjerojatnost da je taj broj unutar tog broja standardnih odstupanja je:
| 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, šansa da je bilo koji SAT rezultat izvan 3 STD-a praktički je nula, jer je 99,73 posto rezultata unutar 3 STD-a.
Pogledajmo sada proračunsku tablicu i objasnite kako to funkcionira.
Sada objašnjavamo sljedeće formule:
= Srednja (B2: B6)
Prosjek svih rezultata preko raspona B2: B6. Naime, zbroj svih rezultata podijeljen s brojem ljudi koji su testirali.
= STDEV.P (B2: B6)
Standardno odstupanje od raspona B2: B6. ".P" znači da se STDEV.P koristi za sve rezultate, tj. Cijelu populaciju, a ne samo podskup.
= PERCENTRANK.EXC (B $ $ 2: $ B $ 6 B2)
To izračunava kumulativni postotak u rasponu B2: B6 na temelju SAT rezultata, u ovom slučaju B2. Na primjer, 83 posto rezultata nalazi se ispod Walkerove ocjene.
Stavljanje rezultata u grafikonu olakšava razumijevanje rezultata, a možete ga prikazati u prezentaciji kako biste jasnije odredili točku.
Studenti su na horizontalnoj osi i njihovi SAT rezultati prikazani su kao plavi grafikoni na skali (vertikalna os) od 1.600 do 2.300.
Rang percentila je desna okomita osi od 0 do 90 posto, a zastupa ga siva linija.
Stvaranje grafikona temu je za sebe, ali ćemo kratko objasniti kako je gore grafikon stvoren.
Najprije odaberite raspon ćelija koje se nalaze na grafikonu. U ovom slučaju A2 do C6 jer želimo brojeve, kao i imena učenika.
Iz izbornika "Insert" odaberite "Charts" -> "Recommended Charts":
Računalo preporučuje grafikon "Clustered-Column, Secondary Axis". Dio "Sekundarne osovine" znači da privlači dvije okomite osi. U ovom slučaju, ovaj grafikon je onaj kojeg želimo. Ne moramo ništa drugo učiniti.
Možete upotrijebiti pomicanje grafikona i ponovno ga veličinu sve dok ne postignete veličinu i položaj koji želite. Kada budete zadovoljni, možete spremiti grafikon u proračunsku tablicu.
Ako desnom tipkom miša kliknete grafikon, a zatim "Odaberi podatke", prikazuje se koji su podaci odabrani za raspon.
Značajka "preporučene karte" obično vam otkriva da se morate nositi s takvim kompliciranim pojedinostima kao što su određivanje podataka koji će biti uključeni, kako dodijeliti naljepnice i kako dodijeliti lijevu i desnu okomite osi.
U dijaloškom okviru "Odabir izvora podataka" kliknite "rezultat" u odjeljku "Unesi oznake (serija)" i pritisnite "Uredi" te je promijenite da biste odgovorili "Ocjena".
Zatim promijenite seriju 2 ("percentil") u "Percentile".
Vratite se na svoj grafikon i kliknite "Naslov grafikona" i promijenite ga na "SAT rezultate". Sada imamo cjeloviti grafikon. Ima dvije horizontalne osovine: jednu za SAT rezultat (plavo) i jednu za kumulativni postotak (narančasto).
Problem prijevoza klasičan je primjer vrste matematike pod nazivom "linearno programiranje". To vam omogućuje da maksimaliziramo ili minimizirate vrijednost koja je podložna određenim ograničenjima. Ima mnogo aplikacija za široku lepezu poslovnih problema pa je korisno naučiti kako to funkcionira.
Prije nego što počnemo s ovim primjerom moramo omogućiti "Excel Solver".
Odaberite "File" -> "Options" -> "Add-ins". Na dnu opcija dodataka kliknite gumb "Idi" pored stavke "Upravljanje: dodacima za Excel".
Na rezultirajućem izborniku kliknite potvrdni okvir da biste omogućili "Dodatak za Solver" i kliknite "U redu".
Pretpostavimo da šaljemo iPade i pokušavamo ispuniti naše distribucijske centre uz najmanju moguću cijenu prijevoza. Imamo ugovor s transportnom i zrakoplovnom tvrtkom za slanje iPada iz Šangaja, Pekinga i Hong Konga u centre distribucije prikazane u nastavku.
Cijena za slanje svakog ipad je udaljenost od tvornice do distribucijskog centra do biljke podijeljena s 20.000 kilometara. Na primjer, to je 8,024 km od Šangaja do Melbourna koji iznosi 8.024 / 20.000 ili $ .40 po iPadu.
Pitanje je kako ćemo sve ove iPade s tih tri postrojenja isporučiti na ta četiri odredišta po najnižoj mogućoj cijeni?
Kao što možete zamisliti, shvatiti da ovo može biti vrlo teško bez neke formule i alata. U ovom slučaju moramo poslati 462.000 (F12) ukupno iPada. Biljke imaju ograničeni kapacitet od 500.250 (G12) jedinica.
U proračunskoj tablici, kako biste vidjeli kako to funkcionira, upisali smo 1 u B10, što znači da želimo isporučiti 1 iPad iz Šangaja u Melbourne. Budući da troškovi prijevoza na tom putu iznose 0,40 USD po iPadu, ukupni trošak (B17) iznosi 0,40 USD.
Broj je izračunat pomoću funkcije = SUMPRODUCT (troškovi, isporučeni) "troškovi" su rasponi B3: E5.
I "isporučen" su raspon B9: E11:
SUMPRODUCT umnožava "troškove" puta u rasponu "isporučen" (B14). To se naziva "množenje matrica".
Da bi SUMPRODUCT ispravno radio, dvije matrice - troškovi i isporuke - moraju biti iste veličine. Možete se zaokružiti ovim ograničavanjem tako da dodatni troškovi i dostave stupaca i redaka s nultom vrijednošću, tako da polja imaju istu veličinu i nema utjecaja na ukupne troškove.
Ako bismo samo morali umnožiti matrice "troškovima" vremena "isporučenog", to ne bi bilo previše komplicirano, ali moramo se nositi s ograničenjima tamo kao dobro.
Moramo brod što svaki distribucijski centar zahtijeva. Stavili smo tu konstatu u razrješivač ovako: $ B $ 12: $ E $ 12> = $ B $ 13: $ E $ 13. To znači zbroj onoga što se isporučuje, tj. Iznosi u ćelijama $ B $ 12: $ E $ 12, moraju biti veći ili jednaki onome što svaki distribucijski centar zahtijeva ($ B $ 13: $ E $ 13).
Ne možemo više otpremiti nego što proizvodimo. Napisali smo da ograničenja ovako: $ F $ 9: $ F $ 11 <= $ G $ 9: $ G $ 11. Drugo, ono što isporučujemo iz svake tvornice $ F $ 9: $ F $ 11 ne smije premašiti (mora biti manje ili jednako) kapacitet svake biljke: $ G $ 9: $ G $ 11.
Sada idite na izbornik "Podaci" i pritisnite gumb "Solver". Ako gumb "Solver" ne postoji, morate omogućiti dodatak Solver.
Unesite dva ograničenja detaljno ranije i odaberite opciju "Šalji", što 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 "Riješite" i Excel sprema rezultate u proračunsku tablicu, što je ono što želimo. Možete ga spremiti kako biste se mogli igrati s drugim scenarijima.
Ako računalo kaže da ne može pronaći rješenje, učinili ste nešto što nije logično, na primjer, možda ste zatražili više iPada od onih koje biljke mogu proizvesti.
Ovdje Excel kaže da je pronašao rješenje. Pritisnite "OK" da zadržite rješenje i vratite se na proračunsku tablicu.
Kako tvrtka odlučuje hoće li uložiti u novi projekt? Ako je "neto sadašnja vrijednost" (NPV) pozitivna, ulagat će u nju. Ovo je standardni pristup koji se poduzima kod većine financijskih analitičara.
Na primjer, pretpostavimo da tvrtka Codelco rudarstvom želi proširiti rudnik bakra Andinas. Standardni pristup kako bi se utvrdilo hoće li krenuti naprijed s projektom je izračun neto sadašnje vrijednosti. Ako je NPV veći od nule, projekt će biti profitabilan s obzirom na dva ulaza (1) vremena i (2) trošak kapitala.
U običnom engleskom, trošak kapitala znači koliko će taj novac zaraditi ako ga jednostavno napuste u banci. Koristite trošak kapitala kako biste uštedjeli gotovinske vrijednosti na sadašnju vrijednost, drugim riječima 100 $ u pet godina moglo bi danas biti 80 $.
U prvoj godini, 45 milijuna dolara izdvaja se kao kapital za financiranje projekta. Računovođe su utvrdile da njihov trošak kapitala iznosi šest posto.
Kad započnu s miniranjem, novac počinje dolaziti dok tvrtka pronađe i prodaje bakar koji proizvode. Očito, što su više mine, to više novca, a njihova prognoza pokazuje da se novčani tok povećava sve dok ne dosegne 9 milijuna dolara godišnje.
Nakon 13 godina, NPV je $ 3.945.074, tako da će projekt biti profitabilan. Prema financijskim analitičarima "razdoblje isplate" je 13 godina.
"Pivotna tablica" u osnovi je izvješće. Zovemo ih zaokretne tablice jer ih možete jednostavno prebaciti na jednu vrstu izvješća drugom bez potrebe za sastavljanjem čitavog novog izvješća. Pa su stožer na mjestu. Pokazat ćemo osnovni primjer koji uči osnovne pojmove.
Prodajni ljudi su vrlo konkurentni (to je dio prodavača) pa oni, naravno, žele znati kako se na kraju četvrtine i kraja godine međusobno zbližavaju, plus koliko će im biti provizije.
Pretpostavimo da imamo tri prodajne osobe - Carlos, Fred i Julie - svi koji prodaju naftu. Njihova prodaja u dolarima po fiskalnom tromjesečju za godinu 2014 prikazana je u tablici ispod.
Da bismo generirali ova izvješća, izradili smo tablicu zaokreta:
Odaberite "Insert -> Pivot Table, on je na lijevoj strani alatne trake:
Odaberite sve retke i stupce (uključujući ime prodavača) kako je prikazano u nastavku:
Dijaloški okvir pivotne tablice pojavljuje se na desnoj strani proračunske tablice.
Ako kliknemo sva četiri polja u dijaloškom okviru Pivot tablice (kvartal, godina, prodaja i prodavač) Excel dodaje izvješće proračunskoj tablici koja nema smisla, ali zašto?
Kao što vidite, odabrali smo sva četiri polja za dodavanje izvješća. Zadano ponašanje programa Excel je grupiranje redaka po tekstnim poljima, a zatim zbroj svih ostalih redaka.
Ovdje nam daje zbroj 2014 + 2014 + 2014 + 2014 = 24.168, što je glupost. Također je dao zbroj kvartala 1 + 2 + 3 + 4 = 10 * 3 = 3 0. Ne trebamo te podatke, pa poništimo odabir tih polja kako bismo ih uklonili iz naše stožerne tablice.
Međutim, "zbroj prodaje" (ukupna prodaja) je bitan, pa ćemo to popraviti.
Možete urediti "zbroj prodaje" koji kažete "ukupna prodaja", što je jasnije. Također, možete oblikovati ćelije kao valutu baš kao i oblikovati druge ćelije. Prvo kliknite "Zbroj prodaje" i odaberite "Vrijednost polja postavki".
Na dobivenom dijaloškom okviru promijenimo naziv na "Ukupna prodaja", a zatim kliknite "Format brojeva" i promijenimo ga u "Valuta".
Zatim možete vidjeti vaš ručni rad u pivotnoj tablici:
Sada dodajmo subtotalove za svako tromjesečje. Da biste dodali podzbrojeve samo kliknite lijevu tipku na polje "Četvrtina" i držite je i povucite je u odjeljak "retke". Rezultat možete vidjeti na snimci zaslona u nastavku:
Dok smo na njemu, uklonimo vrijednosti "Sum od četvrtine". Jednostavno kliknite strelicu i kliknite "Ukloni polje". Na snimci zaslona sada možete vidjeti da smo dodali retke "Četvrt", što svrstava prodaju svakog prodavača za četvrtinu.
S tim svježim vještinama svjesno, sada možete izraditi pivotne tablice iz vlastitih podataka!
Završavajući, pokazali smo vam neke od značajki Microsoft Excelovih formula i funkcija koje Microsoft Excel možete primijeniti na vaše poslovne, akademske ili druge potrebe.
Kao što ste vidjeli, Microsoft Excel je ogroman proizvod s toliko mnogo značajki koje većina ljudi, pa čak i napredni korisnici, ne znaju sve od njih. Neki bi ljudi mogli reći da je to komplicirano; osjećamo da je sveobuhvatnija.
Nadamo se, predstavljanjem mnogih primjera u stvarnom životu, pokazali smo ne samo funkcije dostupne u programu Microsoft Excel, već smo vas podučili nešto o statistici, linearnom programiranju, stvaranju grafikona, korištenju nasumičnih brojeva i drugih ideja koje sada možete usvojiti i koristiti u vašoj školi ili gdje radite.
Zapamtite, ako se želite vratiti natrag i ponovo podići razred, možete početi sa svježim sa lekcijom 1!
