Hakut, kaaviot, tilastot ja kääntötaulukot

Tarkasteltuaan perustoiminnot, soluviittaukset ja päivämäärä- ja kellotoiminnot sukellamme nyt joitakin Microsoft Excelin kehittyneimmistä ominaisuuksista. Esitämme menetelmiä klassisten rahoitusongelmien, myyntiraporttien, kuljetuskustannusten ja tilastojen ratkaisemiseksi.

KOULUN NAVIGOINTI

1. Miksi tarvitset kaavoja ja toimintoja?
2. Kaavan määrittäminen ja luominen
3. Suhteellinen ja absoluuttinen soluviittaus ja alustaminen
4. Hyödyllisiä toimintoja, jotka sinun pitäisi tutustua
5. Hakut, kaaviot, tilastot ja kääntötaulukot

Nämä toiminnot ovat tärkeitä yrityksille, opiskelijoille ja niille, jotka haluavat vain oppia lisää.

VLOOKUP ja HLOOKUP

Tässä on esimerkki pystysuuntaisen haun (VLOOKUP) ja horisontaalisen haun (HLOOKUP) toiminnoista. Näitä toimintoja käytetään numeron tai muun arvon kääntämiseen ymmärrettäväksi. VLOOKUPin avulla voit esimerkiksi ottaa osanumeron ja palauttaa kohteen kuvauksen.

Tämän selvittämiseksi palataan takaisin osassa 4 olevaan "päätöksentekijä" -laskentataulukkoon, jossa Jane yrittää päättää, mitä pukeutua kouluun. Hän ei ole enää kiinnostunut siitä, mitä hän käyttää, koska hän on laskenut uuden poikaystävänsä, joten hän käyttää nyt satunnaisia ​​asuja ja kenkiä.

Jane'n laskentataulukossa hän luetteloi asuja pystysuoriin sarakkeisiin ja kenkiin, vaakasuoriin sarakkeisiin.

Hän avaa laskentataulukon ja toiminto RANDBETWEEN (1,3) luo numeron, joka on tai yhtä suuri kuin yksi ja kolme, joka vastaa kolmen tyyppisiä asuja, joita hän voi käyttää.

Hän käyttää RANDBETWEEN (1,5) -toimintoa valita viisi tyyppistä kenkiä.

Koska Jane ei voi käyttää numeroa, jonka tarvitsemme muuntaa sen nimeksi, joten käytämme hakutoimintoja.

Käytämme VLOOKUP-toimintoa, jos haluat kääntää asunumeron varusteeseen. HLOOKUP kääntää kengän numerosta rivin eri kengätyyppeihin.

Laskentataulukko toimii näin:

Excel poimii satunnaisluvun yhdestä kolmeen, koska hänellä on kolme varusteoptiota.

Seuraavaksi kaava muuntaa numeron tekstiksi käyttäen = VLOOKUP (B11, A2: B4,2), joka käyttää satunnaislukua B11: n arvosta alueelle A2: B4. Sitten se antaa tuloksen (C11) toisessa sarakkeessa luetelluista tiedoista.

Käytämme samaa tekniikkaa kenkien valintaan, paitsi että tällä kertaa käytämme VOOKUPia HLOOKUPin sijaan.

Esimerkki: Perustilastot

Lähes jokainen tietää yhden kaavan tilastosta - keskiarvo -, mutta yritykselle on myös toinen tärkeä tilasto: keskihajonta.

Esimerkiksi monet kollegaan menneet henkilöt ovat hämmentäneet SAT-pistemäärää. He saattavat haluta tietää, miten he sijoittuvat muihin opiskelijoihin verrattuna. Yliopistot haluavat tietää tämän myös siksi, että monet yliopistot, etenkin arvostetut, kääntävät matalan SAT-pistemäärän omaavia opiskelijoita.

Joten miten me tai yliopisto, mittaisimme ja tulkitsisimme SAT-pisteitä? Alla on SAT-pistemäärä viidelle opiskelijalle, jotka vaihtelevat 1,870–2.230.

Tärkeitä ymmärrettäviä numeroita ovat:

Keskiverto - Keskiarvoa kutsutaan myös "keskiarvoksi".

Standardipoikkeama (STD tai σ) - Tämä numero osoittaa, kuinka laajasti hajautettu joukko numeroita on. Jos standardipoikkeama on suuri, numerot ovat kaukana toisistaan ​​ja jos se on nolla, kaikki numerot ovat samat. Voisit sanoa, että keskihajonta on keskiarvon ja havaitun arvon, ts. 1,998 ja kunkin SAT-pisteen keskiarvo. Huomaa, että on yleistä lyhentää standardipoikkeamaa käyttämällä kreikkalaista symbolia sigma “σ”.

Prosenttiosuusluokka - Kun oppilas saa korkean pistemäärän, he voivat ylistää, että he ovat huippuluokan 99 prosentissa tai jotain sellaista. ”Prosenttiluku” tarkoittaa sitä, että pisteiden prosenttiosuus on pienempi kuin yksi tietty pisteet.

Standardipoikkeama ja todennäköisyys ovat läheisesti sidoksissa toisiinsa. Voit sanoa, että jokaisen standardipoikkeaman osalta todennäköisyys tai todennäköisyys, että kyseinen numero on siinä standardipoikkeamissa, on:

STD	Pisteiden prosenttiosuus	SAT-pistemäärä
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ä SAT-pistemäärä on 3 STD: n ulkopuolella, on käytännössä nolla, koska 99,73 prosenttia pisteistä on 3 STD: n sisällä.

Katsokaamme nyt laskentataulukkoa uudelleen ja selittäkää, miten se toimii.

Nyt selitämme kaavat:

= Keskiarvo (B2: B6)

Kaikkien pisteiden keskiarvo alueella B2: B6. Erityisesti kaikkien pisteiden summa jaettuna testin ottaneiden henkilöiden lukumäärällä.

= STDEV.P (B2: B6)

Standardipoikkeama alueella B2: B6. ".P" tarkoittaa STDEV.P: tä käytetään kaikkien pisteiden, eli koko väestön, eikä vain osajoukon yli.

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

Tämä laskee kumulatiivisen prosenttiosuuden alueella B2: B6 SAT-pistemäärän perusteella, tässä tapauksessa B2. Esimerkiksi 83 prosenttia pisteistä on Walkerin pistemäärän alapuolella.

Tulosten kartoittaminen

Tulosten sijoittaminen kaavioon helpottaa tulosten ymmärtämistä, ja voit näyttää sen esityksessä, jotta se olisi selkeämpi.

Opiskelijat ovat vaakasuoralla akselilla ja niiden SAT-pisteet näkyvät sinisen palkkikuvana mittakaavassa (pystysuora akseli) 1 600 - 2 300.

Prosenttilukema on oikeanpuoleinen pystysuora akseli 0 - 90 prosenttiin, ja sitä edustaa harmaa viiva.

Kartan luominen

Kartan luominen on aihe itselleen, mutta selitämme lyhyesti, miten yllä oleva kaavio luotiin.

Valitse ensin kaavion sisältämä solualue. Tässä tapauksessa A2 - C6, koska haluamme sekä numerot että opiskelijan nimet.

Valitse ”Insert” -valikosta ”Charts” -> “Recommended Charts”:

Tietokone suosittelee "Clustered-Column, Secondary Axis" -karttaa. Toissijaisen akselin osa tarkoittaa, että se vetää kaksi pystysuoraa akselia. Tässä tapauksessa tämä kaavio on se, jota haluamme. Meidän ei tarvitse tehdä mitään muuta.

Voit siirtää kaaviota ja muuttaa sen kokoa, kunnes se on haluamasi koko ja sijainti. Kun olet tyytyväinen, voit tallentaa kaavion laskentataulukkoon.

Jos napsautat hiiren kakkospainikkeella kuvaa, valitse ”Valitse data”, se näyttää, mitä tietoja valitaan alueelle.

"Suositellut kaaviot" -ominaisuus yleensä poistaa sinut siitä, että sinun ei tarvitse käsitellä tällaisia ​​monimutkaisia ​​tietoja, kuten määritellä, mitkä tiedot sisällytetään, miten osoitetaan tarroja ja miten määrittää vasen ja oikea pystysuuntainen akseli.

Valitse ”Valitse tietolähde” -valintaikkunassa ”Pisteet” kohdassa ”Legend Entries (Series)” ja paina ”Edit” ja muuta sitä sanoaksesi “Score”.

Vaihda sitten sarja 2 ("prosenttipiste") kohtaan "Prosentti".

Palaa kaavioon ja klikkaa "Chart Title" ja muuta se "SAT Scoresiksi". Nyt meillä on täydellinen kaavio. Siinä on kaksi vaakasuuntaista akselia: yksi SAT-pisteelle (sininen) ja toinen kumulatiiviseen prosenttiosuuteen (oranssi).

Esimerkki: kuljetusongelma

Kuljetusongelma on klassinen esimerkki matematiikan tyypistä, jota kutsutaan ”lineaariseksi ohjelmoinniksi”. Tämän avulla voit maksimoida tai minimoida arvon, johon liittyy tiettyjä rajoituksia. Siinä on monia sovelluksia moniin liiketoimintaongelmiin, joten on hyödyllistä oppia, miten se toimii.

Ennen kuin aloitamme tämän esimerkin, sinun on otettava käyttöön Excel-ratkaisija.

Ota käyttöön Solver-apuohjelma

Valitse "File" -> "Options" -> "Add-ins". Lisää apuohjelmien vaihtoehtojen alareunassa "Siirry" -painiketta "Hallitse: Excel-apuohjelmat" -kohdan vieressä.

Napsauta tuloksena olevasta valikosta valintaruutua ja valitse "Solver Add-in" ja napsauta "OK".

Esimerkki: Laske pienimmät iPadin toimitusmaksut

Oletetaan, että toimitamme iPadit ja yritämme täyttää jakelukeskuksemme käyttämällä mahdollisimman pieniä kuljetuskustannuksia. Olemme sopineet kuljetus- ja lentoyhtiöyhtiöstä lähettämään iPadit Shanghaissa, Pekingissä ja Hongkongissa alla oleviin jakelukeskuksiin.

Kunkin iPadin lähettämisen hinta on etäisyys tehtaalta jakelukeskukseen laitokseen, joka on jaettu 20 000 kilometrillä. Esimerkiksi Shanghaisesta Melbourneen on 8 024 km, joka on 8 024/20 000 tai 0,40 dollaria iPadia kohti.

Kysymys kuuluu, miten lähetämme kaikki nämä iPadit näistä kolmesta laitoksesta näihin neljään kohteeseen mahdollisimman alhaisin kustannuksin?

Kuten voitte kuvitella, tämä voi olla hyvin vaikeaa ilman kaavaa ja työkalua. Tässä tapauksessa meidän on lähetettävä 462 000 (F12) iPadia. Kasvien kapasiteetti on rajoitettu 500 250 (G12) yksikköön.

Laskentataulukossa, jotta voit nähdä, miten se toimii, olemme kirjoittaneet 1 soluun B10, mikä tarkoittaa, että haluamme lähettää yhden iPadin Shanghaissa Melbourneen. Koska kuljetuskustannukset tällä reitillä ovat 0,40 dollaria iPadia kohti, kokonaiskustannukset (B17) ovat 0,40 dollaria.

Numero laskettiin käyttäen funktiota = SUMPRODUCT (kustannukset, lähetetyt) ”kustannukset” ovat B3: E5-alueita.

Ja ”lähetetään” ovat B9: E11:

SUMPRODUCT kertoo "kustannukset" kertaa "lähetetty" (B14). Tätä kutsutaan "matriisikertoimeksi".

Jotta SUMPRODUCT toimisi oikein, näiden kahden matriisin - kustannusten ja toimitusten - on oltava samankokoisia. Voit kiertää tämän rajoituksen tekemällä ylimääräisiä kustannuksia ja kuljettamalla sarakkeita ja rivejä, joiden arvo on nolla, jotta taulukot ovat samankokoisia ja ei ole vaikutusta kokonaiskustannuksiin.

Solverin käyttäminen

Jos meidän tarvitsee vain kertoa "kustannukset" -matriiseja, jotka eivät olleet liian monimutkaisia, mutta meidän on käsiteltävä myös rajoituksia.

Meidän on toimitettava, mitä jokainen jakelukeskus vaatii. Panimme tämän vakion ratkaisijaan näin: $ B $ 12: $ E $ 12> = $ B 13 dollaria: $ E 13 dollaria. Tämä tarkoittaa sitä, mitä summa lähetetään, ts. Solujen $ B $ 12: $ E $ 12 kokonaismäärien on oltava suurempia tai yhtä suuria kuin mitä jakelukeskus vaatii ($ B $ 13: $ E $ 13).

Emme voi lähettää enemmän kuin tuotamme. Kirjoitamme tämänkaltaiset rajoitukset: $ 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.

Siirry nyt “Data” -valikkoon ja paina ”Solver” -painiketta. Jos "Solver" -painike ei ole käytössä, sinun on otettava käyttöön Solver-apuohjelma.

Kirjoita aiemmin määritellyt kaksi rajoitusta ja valitse "Lähetykset" -alue, joka on sellaisten numeroiden valikoima, joita Excel haluaa laskea. Valitse myös oletusalgoritmi "Simplex LP" ja ilmoita, että haluamme "minimoida" solun B15 ("kokonaiskuljetuskustannukset"), jossa se sanoo "Aseta tavoite".

Paina ”Ratkaise” ja Excel tallentaa tulokset taulukkolaskentaan, jota haluamme. Voit myös tallentaa tämän, jotta voit toistaa muita skenaarioita.

Jos tietokone sanoo, että se ei löydä ratkaisua, niin olet tehnyt jotain, joka ei ole looginen, esimerkiksi olet ehkä pyytänyt enemmän iPadia kuin kasvit voivat tuottaa.

Tässä Excel kertoo löytäneensä ratkaisun. Paina “OK”, jos haluat säilyttää ratkaisun ja palata laskentataulukkoon.

Esimerkki: Nettoarvo

Miten yritys päättää investoida uuteen hankkeeseen? Jos "netto nykyarvo" (NPV) on positiivinen, ne sijoittavat siihen. Tämä on useimpien rahoitusanalyytikoiden tavanomainen lähestymistapa.

Oletetaan esimerkiksi, että Codelcon kaivosyhtiö haluaa laajentaa Andinan kuparikaivosta. Tavallinen lähestymistapa sen määrittämiseksi, siirrytäänkö eteenpäin, on laskea nykyinen nettoarvo. Jos NPV on suurempi kuin nolla, hanke on kannattavaa, kun otetaan huomioon kaksi tuloa (1) ja (2) pääomakustannukset.

Pelkkä englanninkielinen pääoman hinta tarkoittaa sitä, kuinka paljon rahat ansaitsisivat, jos he vain jättävät sen pankkiin. Käytät pääomakustannuksia diskontataksesi käteisarvoja nykyarvoon, toisin sanoen 100 dollaria viidessä vuodessa voi olla 80 dollaria tänään.

Ensimmäisenä vuonna 45 miljoonaa dollaria on varattu pääomaksi hankkeen rahoittamiseen. Tilintarkastajat ovat päättäneet, että heidän pääomakustannuksensa on kuusi prosenttia.

Kun ne alkavat kaivostoimintaa, käteisellä alkaa tulla, kun yritys löytää ja myy kuparia, jonka ne tuottavat. Ilmeisemmin, mitä enemmän he minun kaivataan, sitä enemmän rahaa he tekevät, ja niiden ennuste osoittaa niiden kassavirran kasvavan, kunnes se saavuttaa 9 miljoonaa dollaria vuodessa.

13 vuoden jälkeen NPV on $ 3,945,074 USD, joten hanke on kannattavaa. Talousanalyytikkojen mukaan "takaisinmaksuaika" on 13 vuotta.

Pivot-taulukon luominen

”Pivot-pöytä” on pohjimmiltaan raportti. Me kutsumme niitä pivot-taulukoiksi, koska voit helposti vaihtaa ne yhdentyyppiseen raporttiin toiselle ilman, että tarvitset uuden raportin. Joten he tappi paikallaan. Näytetään perustavanlaatuinen esimerkki, joka opettaa peruskäsitteet.

Esimerkki: Myyntiraportit

Myyntihenkilöstö on erittäin kilpailukykyinen (se on osa myyjää), joten he tietysti haluavat tietää, miten he maksavat toisiaan vastaan ​​neljänneksen lopussa ja vuoden lopussa, ja kuinka paljon heidän palkkionsa ovat.

Oletetaan, että meillä on kolme myyntihenkilöä - Carlos, Fred ja Julie - jotka kaikki myyvät öljyä. Niiden myynti dollareina vuosineljänneksittäin vuodelta 2014 näkyy alla olevassa taulukossa.

Luodaksesi nämä raportit luomme kääntöpöydän:

Valitse ”Insert -> Pivot Table, se on työkalurivin vasemmalla puolella:

Valitse kaikki rivit ja sarakkeet (mukaan lukien myyjän nimi) alla esitetyllä tavalla:

Pivot-taulukon valintaikkuna avautuu laskentataulukon oikealle puolelle.

Jos napsautamme kaikkia neljää kenttää pivot-taulukon valintaikkunassa (Quarter, Year, Sales ja Salesperson), Excel lisää raportin laskentataulukkoon, joka ei ole järkevää, mutta miksi?

Kuten näette, olemme valinneet kaikki neljä raporttia lisäävää kenttää. Excelin oletuskäyttäytyminen on rivien ryhmittäminen tekstikentillä ja summaamalla kaikki muut rivit.

Täällä se antaa meille summan vuoden 2014 + 2014 + 2014 + 2014 = 24,168, joka on hölynpölyä. Se antoi myös neljännen vuosineljänneksen 1 + 2 + 3 + 4 = 10 * 3 = 3 0 summan. Emme tarvitse näitä tietoja, joten poistamme nämä kentät poistaaksesi ne pivot-taulukostamme.

Myynnin summa (kokonaismyynti) on kuitenkin asianmukaista, joten korjaamme sen.

Esimerkki: Myyjän myynti

Voit muokata “Myynnin summaa”, joka on selkeämpi ”Total Sales”. Voit myös alustaa solut valuutaksi aivan kuten muokkaat muita soluja. Napsauta ensin ”Myynnin summa” ja valitse ”Arvokenttäasetukset”.

Tuloksena olevassa valintaikkunassa muutamme nimen "Total Sales" ja sitten "Number Format" ja vaihdat sen "Currency".

Sitten voit nähdä käsityösi pivot-taulukossa:

Esimerkki: Myyjän ja neljänneksen myynti

Lisätään nyt välilliset summat jokaiselle vuosineljännekselle. Jos haluat lisätä osavertailuja, klikkaa hiiren kakkospainikkeella "Quarter" -kenttää ja pidä ja vedä se rivien osaan. Voit nähdä tuloksen alla olevassa kuvassa:

Vaikka olemme siinä, poistetaan "Quarter" -arvot. Napsauta nuolta ja valitse Poista kenttä. Kuvakaappauksessa näet nyt, että olemme lisänneet "Quarter" -rivit, jotka erottavat kunkin myyjän myynnin vuosineljänneksittäin.

Kun nämä taidot ovat tuoreita, voit nyt luoda omia tietojasi kääntyviä taulukoita!

johtopäätös

Kääriminen on osoittanut sinulle joitakin Microsoft Excelin kaavojen ja toimintojen ominaisuuksia, joita voit soveltaa Microsoft Exceliin yrityksesi, akateemisten tai muiden tarpeiden mukaan.

Kuten olet nähnyt, Microsoft Excel on valtava tuote, jossa on niin paljon ominaisuuksia, että useimmat ihmiset, jopa edistyneet käyttäjät, eivät tiedä kaikkia niitä. Jotkut saattavat sanoa, että se tekee siitä monimutkaisen; mielestämme se on kattavampi.

Toivottavasti esittelemällä sinulle paljon todellisia esimerkkejä olemme osoittaneet Microsoft Excelissä käytettävissä olevat toiminnot, mutta olemme opettaneet teille jotain tilastoista, lineaarisesta ohjelmoinnista, kaavioiden luomisesta, satunnaislukuja käyttäen ja muista ideoista, jotka voit nyt hyväksyä ja käyttää koulussa tai missä työskentelet.

Muista, että jos haluat mennä takaisin ja ottaa luokan uudelleen, voit aloittaa tuoreen oppitunnilla 1!