Kun olet tutkinut perustoiminnot, soluviitteet ja päivämäärä- ja kellotoiminnot, voimme nyt sukeltaa Microsoft Excelin kehittyneempiin ominaisuuksiin. Esittelemme menetelmiä klassisten rahoitusongelmien, myyntiraporttien, toimituskustannusten ja tilastojen ratkaisemiseksi.
Koulun navigointiNämä toiminnot ovat tärkeitä yrityksille, opiskelijoille ja niille, jotka haluavat vain oppia lisää.
Tässä esimerkki havainnollistaa vertikaalinen haku (VLOOKUP) ja horisontaalinen haku (HLOOKUP). Näitä toimintoja käytetään kääntämään luku tai muu arvo ymmärrettäväksi. Voit esimerkiksi käyttää osanumeroa VLOOKUP: n avulla ja palauttaa kohteen kuvaus.
Tutkitsemaan tämä, palakaamme "Päättäjät" -taulukkoon osassa 4, jossa Jane yrittää päättää, mitä kouluttaa. Hän ei ole enää kiinnostunut siitä, mitä hän käyttää, koska hän on purkanut uuden poikaystävän, joten hän käyttää nyt satunnaisia asuja ja kenkiä.
Janeen laskentataulukossa hän laatii asusteita pystysarakkeisiin ja kenkiin, horisontaalisiin sarakkeisiin.
Hän avaa laskentataulukon ja toiminto RANDBETWEEN (1,3) luo numeron, joka on yhtä tai yhtä suuri kuin yksi ja kolme, jotka vastaavat kolmesta erilaisesta asusta, joita hän voi käyttää.
Hän käyttää toimintoa RANDBETWEEN (1,5) valita viidestä kengätyypistä.
Koska Jane ei voi käyttää numeroa, meidän täytyy muuntaa tämä nimeksi, joten käytämme hakutoimintoja.
Käytämme VLOOKUP-funktiota kääntäessämme asumisnumeron varusteen nimi. HLOOKUP kääntää kengänumerosta rivin eri kengätyyppeihin.
Taulukkolaskenta toimii näin:
Excel hakee satunnaisluvun yhdestä kolmeen, koska hänellä on kolme asennusvaihtoehtoa.
Seuraavaksi kaava kääntää numeron tekstiksi käyttämällä = VLOOKUP (B11, A2: B4,2), joka käyttää satunnaislukua B11: n arvo näyttää alueelta A2: B4. Sitten se antaa tuloksen (C11) toisessa sarakkeessa luetelluista tiedoista.
Käytämme samaa tekniikkaa poimimaan kengät, paitsi tällä kertaa, käytämme VOOKUP HLOOKUPin sijaan.
Lähes kaikki tietävät yhden kaavan tilastoista - keskimäärin - mutta yritykselle tärkeä tilastotieto: keskihajonta.
Esimerkiksi monet, jotka ovat menneet yliopistoon, ovat kärsineet SAT-pisteet. He saattavat haluta tietää, kuinka he luokittelevat muihin opiskelijoihin verrattuna. Yliopistot haluavat tietää tämän myös siksi, että monet korkeakoulut, erityisesti arvostetut, hylkäävät alhaiset SAT-tulokset.
Joten miten me tai yliopisto mitata ja tulkita SAT-pisteitä? Alla on SAT-tulokset viidelle opiskelijalle, jotka vaihtelevat 1,870: sta 2,230: een.
Tärkeät luvut on ymmärrettävä:
Keskiverto - Keskimäärää kutsutaan myös "keskiarvoksi".
Vakioero (STD tai σ) - Tämä numero kertoo, kuinka laaja joukko numeroita on. Jos keskihajonta on suuri, niin numerot ovat kaukana toisistaan ja jos se on nolla, kaikki numerot ovat samat. Voisit sanoa, että keskihajonta on keskiarvon ja havaitun arvon keskimääräinen ero eli 1,998 ja kunkin SAT-pisteen keskiarvo. Huomaa, että on tavallista lyhentää standardipoikkeama käyttäen kreikkalaista symbolia sigma "σ."
Prosenttiluku - Kun opiskelija saa korkean pistemäärän, he voivat ylpeillä, että he ovat huippuluokkaa 99 tai jotain sellaista. "Prosenttiluku" tarkoittaa, että pisteiden prosenttiosuus on pienempi kuin yksi piste.
Standardipoikkeama ja todennäköisyys ovat läheisessä yhteydessä. Voit sanoa, että jokaisen standardipoikkeaman osalta todennäköisyys tai todennäköisyys, että kyseinen luku on kyseisen standardipoikkeaman sisällä, on:
| STD | Tulosten prosenttiosuus | SAT-pisteiden valikoima |
| 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 |
Kuten näette, mahdollisuus, että kaikki SAT-pisteet ovat 3 STD: n ulkopuolella, on käytännöllisesti katsoen nolla, sillä 99,73 prosenttia pisteistä on 3 STD: n sisällä.
Katsokaamme uudelleen laskentataulukkoa ja kerrotaan, miten se toimii.
Nyt selitämme kaavat:
= Keskiarvo (B2: B6)
Kaikkien pisteiden keskiarvo B2: B6: n alueella. Erityisesti kaikkien pisteiden summa jaettuna testin vastaanottajamäärillä.
= STDEV.P (B2: B6)
Keskihajonta alueella B2: B6. ".P" tarkoittaa, että STDEV.P: tä käytetään kaikkiin pisteisiin, eli koko väestöön eikä vain osajoukkoon.
= PERCENTRANK.EXC ($ B $ 2: $ B $ 6 B2)
Tämä laskee kumulatiivinen prosenttiosuus B2: B6: n perusteella SAT-pisteet, tässä tapauksessa B2. Esimerkiksi 83 prosenttia pisteistä on Walkerin pistemäärän alapuolella.
Tulosten antaminen kaavion avulla tulosten ymmärtämisen helpottamiseksi, voit myös näyttää sen esityksessä, jotta pisteesi selkeytyy.
Opiskelijat ovat vaakasuoralla akselilla, ja niiden SAT-tulokset näytetään asteikolla (pystysuora akseli) asteikolla 1600-2300.
Prosenttiluku on oikeanpuoleinen pystysuora akseli 0 - 90 prosenttia, ja sitä edustaa harmaa viiva.
Kaavion luominen on itselleen aihe, mutta selitämme lyhyesti, kuinka yllä oleva kaavio luotiin.
Valitse ensin kaavion solualue. Tässä tapauksessa A2-C6, koska haluamme numerot sekä opiskelijan nimet.
Valitse "Insert" -valikosta "Charts" -> "Recommended Charts":
Tietokone suosittelee "Klusteroitu sarake, toissijainen akseli" kaavio. "Toissijainen akseli" tarkoittaa sitä, että se vetää kahta pystysuoraa akselia. Tässä tapauksessa tämä kaavio on haluamamme. Meidän ei tarvitse tehdä mitään muuta.
Voit siirtää kaavion ympärille ja koota sen uudelleen, kunnes se on kooltaan ja haluamaasi paikkaan. Kun olet tyytyväinen, voit tallentaa taulukon laskentataulukkoon.
Jos napsautat kaaviota hiiren kakkospainikkeella ja sitten "Valitse tiedot", se näyttää, mitä tietoja valitaan alueelle.
"Suositeltavat kaaviot" -ominaisuus ei yleensä välttämättä käsittele tällaisia monimutkaisia yksityiskohtia, jotka määrittävät, mitä tietoja se sisältää, miten etiketit ja miten vasen ja oikea pystysuora akseli voidaan määrittää.
Valitse Valitse tietolähde -valintaikkunan kohta "Pistemääräykset" kohdassa "Seliteosat (sarjat)" ja paina "Muokkaa" ja muuta se sanomaan "Pisteet".
Vaihda sitten sarja 2 ("prosenttipiste") prosentteiksi.
Palaa kaavioosi ja klikkaa "Kaavion otsikko" ja vaihda se "SAT-pisteisiin". Nyt meillä on täydellinen kaavio. Siinä on kaksi vaakasuoraa akselia: yksi SAT-pisteet (sininen) ja yksi kumulatiiviselle prosenttiosuudelle (oranssi).
Liikenneongelma on klassinen esimerkki matematiikan tyypistä, jota kutsutaan "lineaariseksi ohjelmoinniksi". Näin voit suurentaa tai pienentää arvoa, joka on tiettyjen rajoitusten alainen. Siinä on monia sovelluksia laajaan liiketoimintaongelmiin, joten on hyödyllistä oppia, miten se toimii.
Ennen kuin aloitamme tämän esimerkin, meidän on otettava käyttöön "Excel Solver".
Valitse "File" -> "Options" -> "Add-ins". Napsauta laajennusten lisäasetusten alaosassa "Hallitse: Excel-lisäosat" vieressä olevaa Siirry-painiketta.
Napsauta valikosta valintaruutua, niin ota käyttöön "Solver-apuohjelma" ja napsauta "OK".
Oletetaan, että lähetämme iPadeja ja yritämme täyttää jakelukeskuksemme mahdollisimman pienillä kuljetusmaksuilla. Meillä on sopimus rahti- ja lentoyhtiön kanssa, joka lähettää iPads Shanghaista, Pekingistä ja Hongkongista alla esitettyihin jakelukeskuksiin.
Hinta jokaiselle iPadille on etäisyys tehtaalta jakelukeskukseen kasviin jaettuna 20 000 kilometrillä. Esimerkiksi se on 8,024 km Shanghaista Melbournehin, joka on 8,024 / 20,000 tai 40 dollaria iPadia kohden.
Kysymys kuuluu siitä, kuinka toimitamme kaikki nämä kolme laitosta näihin neljään kohteeseen pienimmillä kustannuksilla?
Kuten voitte kuvitella, selvittää tämä voisi olla hyvin vaikeaa ilman kaavaa ja työkalua. Tässä tapauksessa meidän on toimitettava 462 000 (F12) koko iPads. Kasvien kapasiteetti on rajoitetusti 500 250 (G12).
Laskentataulukossa, jotta näet, miten se toimii, olemme kirjoittaneet 1 soluun B10 eli haluamme lähettää 1 iPad Shanghaista Melbourgiin. Koska tämän reitin kuljetuskustannukset ovat 0,40 dollaria iPadilla, kokonaiskustannukset (B17) ovat 0,40 dollaria.
Numero laskettiin käyttämällä funktiota = SUMPRODUCT (kustannukset, toimitukset) "kustannukset" ovat alueet B3: E5.
Ja "lähetetty" ovat alue B9: E11:
SUMPRODUCT kertoo "kustannukset" kertaa "lähetetty" (B14). Tätä kutsutaan "matriisikokinnaksi".
Jotta SUMPRODUCT toimisi kunnolla, molemmat matriisit - kustannukset ja toimitukset - on oltava samankokoisia. Voit rajata tämän rajoituksen tekemällä ylimääräisiä kustannuksia ja lähetyspylväitä ja rivejä nolla-arvolla siten, että taulukot ovat samankokoisia eivätkä vaikuta kokonaiskustannuksiin.
Jos meidän tarvitsee vain moninkertaistaa matriisit "kulut" kertaa "lähetetty", joka ei olisi liian monimutkainen, mutta meidän on myös käsiteltävä rajoituksia.
Meidän on toimitettava jokaisen jakelukeskuksen vaatimat tiedot. Asettamme tämän vakioilijaksi näin: $ B $ 12: $ E $ 12> = $ B $ 13: $ E $ 13. Tämä tarkoittaa summaa, joka lähetetään eli solujen summissa $ B $ 12: $ E $ 12, on oltava suurempi tai yhtä suuri kuin kunkin jakelukeskuksen tarvitsema arvo ($ B $ 13: $ E $ 13).
Emme voi lähettää enemmän kuin tuotamme. Kirjoitamme tällaisia rajoituksia: $ F $ 9: $ F $ 11 <= $ G $ 9: $ G $ 11. Toinen tapa, mitä toimitamme jokaisesta tehtaasta $ F $ 9: $ F $ 11 ei voi ylittää (ei saa olla pienempi tai yhtä suuri) kunkin kasvin kapasiteetti: $ G $ 9: $ G $ 11.
Siirry nyt "Data" -valikkoon ja paina "Solver" -painiketta. Jos "Solver" -painiketta ei ole, ota Solver-apuohjelma käyttöön.
Kirjoita kaksi aiemmin määriteltyä rajoitusta ja valitse "Lähetykset" -alue, joka on niiden numeroiden joukko, jotka haluamme Excelin laskemiseksi. Valitse myös oletusalgoritmi "Simplex LP" ja osoita, että haluamme "minimoida" solun B15 ("kokonaiskuljetuskustannukset"), jossa se sanoo "Aseta tavoitteeksi".
Paina "Ratkaise" ja Excel tallentaa tulokset laskentataulukkoon, mikä me haluamme. Voit myös tallentaa tämän, jotta voit pelata muiden skenaarioiden kanssa.
Jos tietokone sanoo, ettei se löydä ratkaisua, olet tehnyt jotain, joka ei ole looginen, esimerkiksi olet pyytänyt enemmän iPadeja kuin laitokset voivat tuottaa.
Tässä Excel sanoo löytäneensä ratkaisun. Pidä ratkaisu ja palaa laskentataulukkoon painamalla "OK".
Miten yritys päättää investoida uusi projekti? Jos "nettolukuarvo" (NPV) on positiivinen, he investoivat siihen. Tämä on useimpien taloudellisten analyytikkojen tavanomainen lähestymistapa.
Oletetaan esimerkiksi, että Codelcon kaivosyhtiö haluaa laajentaa Andinaksen kuparikaivon. Tavallinen lähestymistapa sen määrittämiseksi, onko hankkeen eteneminen eteenpäin, on nettoarvon laskeminen. Jos NPV on suurempi kuin nolla, hanke on kannattava, kun otetaan huomioon kaksi tuloa (1) aikaa ja (2) pääomakustannuksia.
Selkeänä englanninkielisenä pääoman kustannuksella tarkoitetaan sitä, kuinka paljon rahaa olisi ansaittava, jos ne jättäisivät sen pankissa. Käytät pääoman kustannuksia käteisarvon alentamiseksi nykyarvoon, toisin sanoen $ 100 viidessä vuodessa voi olla 80 dollaria tänään.
Ensimmäisenä vuonna 45 miljoonaa dollaria varataan pääomaksi hankkeen rahoittamiseen. Tilintarkastajat ovat päättäneet, että heidän pääomakustannuksensa on kuusi prosenttia.
Kun he alkavat kaivos, käteinen alkaa tulla, kun yritys löytää ja myy kuparin, jonka he tuottavat. On selvää, että mitä enemmän he ovat minulle, sitä enemmän rahaa he tekevät, ja niiden ennuste osoittaa kassavirran nousevan, kunnes se saavuttaa 9 miljoonaa dollaria vuodessa.
13 vuoden jälkeen NPV on $ 3,945,074 USD, joten projekti on kannattava. Rahoitusanalyytikkojen mukaan "takaisinmaksuajanjakso" on 13 vuotta.
"Kääntyvä pöytä" on periaatteessa raportti. Me kutsumme niitä pivot-taulukoiksi, koska voit helposti vaihtaa yhden raporttityyppiä toiseen ilman, että sinun tarvitsee tehdä kokonaan uutta raporttia. Joten he tappi paikallaan. Esittelemme perusnäytteen, joka opettaa peruskäsitteet.
Myyjät ovat erittäin kilpailukykyisiä (osaa olla myyjä), joten he luonnollisesti haluavat tietää, kuinka he kilpailevat toisiaan vastaan vuosineljänneksen ja loppuvuoden lopussa sekä kuinka paljon heidän palkkionsa ovat.
Oletetaan, että meillä on kolme myyntiä - Carlos, Fred ja Julie - kaikki myyvät öljyä. Niiden myynti dollareina verovuodelta 2014 vuodessa esitetään alla olevassa laskentataulukossa.
Luoda nämä raportit luomalla pivot-taulukon:
Valitse "Insert -> Pivot Table, se on työkalupalkin vasemmalla puolella:
Valitse kaikki rivit ja sarakkeet (mukaan lukien myyjän nimi) seuraavasti:
Pivot-taulukko-valintaikkuna näkyy laskentataulukon oikealla puolella.
Jos napsautamme kaikki neljä kenttää pivot-taulukon valintaikkunassa (Quarter, Year, Sales ja Salesperson) Excel lisää laskentataulukkoon raportin, jolla ei ole järkeä, mutta miksi?
Kuten näette, olemme valinneet kaikki neljä kenttää, jotka haluat lisätä raporttiin. Excelin oletuskäyttäytyminen on ryhmitellä rivit tekstikenttien avulla ja sitten summaamaan kaikki muut rivit.
Tässä se antaa meille vuoden 2014 + 2014 + 2014 + 2014 = 24 188 summan, mikä on hölynpölyä. Lisäksi se antoi neljäneljänneksen summan 1 + 2 + 3 + 4 = 10 * 3 = 3 0. Näitä tietoja ei tarvita, joten poistamme nämä kentät poistamalla ne pivottaulukosta.
"Myynnin summa" (kokonaismyynti) on kuitenkin asianmukainen, joten korjataan tämä.
Voit muokata "Myyntimäärää", joka sanoo "Kokonaismyynti", joka on selkeämpi. Voit myös muotoilla solut valuutan tapaan samalla tavalla kuin minkä tahansa muun solun muotoiluun. Napsauta ensin "Myyntimäärää" ja valitse "Arvokenttäasetukset".
Tuloksena olevasta valintaikkunasta vaihdamme nimen "Kokonaismyynti" ja napsautamme "Numeromuoto" ja vaihdamme sen "Valuutan arvoon".
Voit nähdä käsityönne pivottaulukossa:
Lisään nyt subtotals jokaiselle neljännekselle. Lisää subtotals vain hiiren kakkospainikkeella "Quarter" kenttään ja pidä sitä ja vedä se "rivit" -osaan. Tulokset näkyvät alla olevassa kuvakaappauksessa:
Kun olemme siinä, poistamme "Summa Quarter" -arvot. Napsauta nuolta ja valitse Poista kentät. Kuvakaappauksessa näet nyt, että olemme lisänneet neljänneksen rivit, jotka jakavat jokaisen myyjän myynnin neljänneksittäin.
Näiden taitojen tuoreessa mielessä voit nyt luoda pivot-taulukoita omilta tiedoistasi!
Kun olet lopettanut, olemme esittäneet joitain Microsoft Excelin kaavojen ja toimintojen ominaisuuksia, joita voit käyttää Microsoft Excel -ohjelmistoja yrityksesi, akateemisten tai muiden tarpeiden mukaan.
Kuten olette nähneet, Microsoft Excel on valtava tuote niin monilla ominaisuuksilla, että useimmat ihmiset, edes edistyneet käyttäjät, eivät tunne kaikkia niitä. Jotkut saattavat sanoa, että se tekee siitä monimutkaisen; meistä tuntuu, että se on kattavampi.
Toivottavasti esittelemällä teitä paljon todellisia esimerkkejä, olemme osoittaneet paitsi Microsoft Excelissä käytettävissä olevat toiminnot, mutta olemme opettaneet sinulle jotain tilastoista, lineaarisesta ohjelmoinnista, luomasta kaavioita, satunnaislukujen ja muiden ideoiden avulla, joita voit nyt ottaa käyttöön ja käytä koulussa tai työskentelet.
Muista, että jos haluat palata takaisin ja aloittaa luokan uudelleen, voit aloittaa oppitunnin 1!
