SCHOOL NAVIGASJON
- Hvorfor trenger du formler og funksjoner?
- Definere og opprette en formel
- Relativ og Absolutt Cellreferanse, og Formatering
- Nyttige funksjoner du bør bli kjent med
- Oppslag, diagrammer, statistikk og pivottabeller
Disse funksjonene er viktige for bedrifter, studenter og de som bare vil lære mer.
VLOOKUP og HLOOKUP
Her er et eksempel for å illustrere vertikale oppslag (VLOOKUP) og horisontale oppslag (HLOOKUP) -funksjoner. Disse funksjonene brukes til å oversette et tall eller en annen verdi til noe som er forståelig. For eksempel kan du bruke VLOOKUP til å ta et delenummer og returnere elementbeskrivelsen.
For å undersøke dette, la oss gå tilbake til vår "Decision Maker" regneark i del 4, der Jane prøver å bestemme hva som skal brukes på skolen. Hun er ikke lenger interessert i det hun har, siden hun har landet en ny kjæreste, så hun vil nå ha tilfeldige antrekk og sko.
I Janes regneark lister hun antrekk i vertikale kolonner og sko, horisontale kolonner.
Hun bruker funksjonen RANDBETWEEN (1,5) for å velge blant fem typer sko.
Siden Jane ikke kan bruke et nummer, må vi konvertere dette til et navn, så vi bruker oppslagsfunksjoner.
Vi bruker VLOOKUP-funksjonen til å oversette antrekknummeret til antrekknavnet. HLOOKUP oversetter fra skonnummer til de ulike typer sko på rad.
Regnearket fungerer som dette for antrekk:
Deretter oversetter formelen tallet til tekst ved hjelp av = VLOOKUP (B11, A2: B4,2) som bruker tilfeldig tall verdien fra B11 til å se i området A2: B4. Det gir da resultatet (C11) fra dataene som er oppført i den andre kolonnen.
Vi bruker samme teknikk for å velge sko, bortsett fra denne gangen bruker vi VOOKUP i stedet for HLOOKUP.
Eksempel: Grunnleggende statistikk
Nesten alle vet en formel fra statistikk - gjennomsnittlig - men det er en annen statistikk som er viktig for virksomheten: standardavvik.
For eksempel har mange en person som har gått på høgskolen agonized over sin SAT score. De vil kanskje vite hvordan de rangerer i forhold til andre studenter. Universiteter vil også vite dette fordi mange universiteter, særlig prestisjetunge, slår ned studenter med lave SAT-poeng.
Så hvordan skal vi, eller et universitet, måle og tolke SAT-score? Nedenfor er SAT-poengsum for fem studenter fra 1,870 til 2,230.
Gjennomsnitt - Gjennomsnitt er også referert til som "mean".
Standardavvik (STD eller σ) - Dette tallet viser hvor stort spredt et sett med tall er. Hvis standardavviket er stort, er tallene langt fra hverandre, og hvis det er null, er alle tallene det samme. Du kan si at standardavviket er den gjennomsnittlige forskjellen mellom gjennomsnittsverdien og den observerte verdien, dvs. 1,998 og hver SAT-poengsum. Vær oppmerksom på at det er vanlig å forkorte standardavviket ved hjelp av det greske symbolet sigma "σ."
Prosentlig rangering - Når en student får en høy poengsum, kan de skryte at de er i topp 99 prosentilen eller noe sånt. "Percentile rang" betyr prosentandelen av poengene er lavere enn en bestemt score.
Standardavvik og sannsynlighet er nært knyttet. Du kan si at for hver standardavvik er sannsynligheten eller sannsynligheten for at tallet innenfor det antall standardavviket:
| STD | Andel av poeng | Utvalg av SAT-poeng |
| 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 |
Som du kan se, er sjansen for at noen SAT-score er utenfor 3 STD'er praktisk talt null, fordi 99,73 prosent av resultatene er innenfor 3 STDs.
La oss nå se på regnearket igjen og forklare hvordan det fungerer.
= Gjennomsnitt (B2: B6)
= STDEV.P (B2: B6)
= PERCENTRANK.EXC ($ B $ 2: $ B $ 6, B2)
Grafering av resultatene
Å sette resultatene i en graf gjør det lettere å forstå resultatene, pluss at du kan vise det i en presentasjon for å gjøre poenget ditt tydeligere.
Den prosentvise rangering er den høyre vertikale aksen fra 0 til 90 prosent, og representeres av den grå linjen.
Slik lager du et diagram
Å lage et diagram er et tema for seg selv, men vi vil kort forklare hvordan diagrammet ovenfor ble opprettet.
Først velger du rekkevidden av celler som skal være i diagrammet. I dette tilfellet A2 til C6 fordi vi vil ha tallene så vel som studentens navn.
Funksjonen "Anbefalte diagrammer" gjør deg vanligvis utilgjengelig for å håndtere så kompliserte detaljer som å bestemme hvilke data som skal inkluderes, hvordan du tilordner etiketter og hvordan du tilordner venstre og høyre vertikale akser.
I dialogboksen "Velg datakilde" klikker du på "poeng" under "Legend Entries (Series)" og trykker "Rediger", og endrer den for å si "Score."
Eksempel: Transportproblemet
Transportproblemet er et klassisk eksempel på en type matematikk kalt "lineær programmering." Dette lar deg maksimere eller minimere en verdifag til visse begrensninger. Den har mange applikasjoner til mange forretningsproblemer, så det er nyttig å lære hvordan det fungerer.
Før vi begynner med dette eksemplet, må vi aktivere "Excel Solver".
Aktiver Solver Add-In
Velg "File" -> "Options" -> "Add-ins". På bunnen av tilleggsalternativene klikker du på "Gå" -knappen ved siden av "Administrer: Excel-tillegg".
Eksempel: Beregn de laveste iPad-fraktkostnadene
Anta at vi leverer iPads, og vi prøver å fylle distribusjonsstedene våre ved å bruke de laveste transportkostnadene. Vi har en avtale med et lastebil og flyselskap for å sende iPads fra Shanghai, Beijing og Hong Kong til distribusjonssentrene vist nedenfor.
Prisen som skal sendes hver iPad er avstanden fra fabrikken til distribusjonssenteret til anlegget dividert med 20.000 kilometer. For eksempel er det 8.024 km fra Shanghai til Melbourne, som er 8.024 / 20.000 eller $.40 per iPad.
Som du kan forestille deg, kan du finne ut at dette kan være veldig vanskelig uten noen formel og verktøy. I dette tilfellet må vi sende 462 000 (F12) totalt iPads. Anleggene har en begrenset kapasitet på 500 250 (G12) enheter.
Bruk av Solver
Hvis alt vi hadde å gjøre var å multiplisere matrisene "kostnader" ganger "sendt" det ville ikke være for komplisert, men vi må også håndtere begrensninger der.
Vi må sende hva hver distribusjonssenter krever. Vi legger det konstant inn i løsningen som dette: $ B $ 12: $ E $ 12> = $ B $ 13: $ E $ 13. Dette betyr summen av det som sendes, dvs. summene i celler $ B $ 12: $ E $ 12, må være større enn eller lik det som hvert distribusjonssenter krever ($ B $ 13: $ E $ 13).
Skriv inn de to begrensningene som er detaljert tidligere, og velg "Sendinger" -området, som er rekkevidden av tall som vi ønsker at Excel skal beregne. Også velg standard algoritmen "Simplex LP" og indikere at vi vil "minimere" cellen C15 ("totale fraktkostnader"), der det står "Set Objective."
Hvis datamaskinen sier at den ikke finner en løsning, har du gjort noe som ikke er logisk, for eksempel kan du ha bedt om flere iPads enn plantene kan produsere.
Her forteller Excel at den fant en løsning. Trykk "OK" for å holde løsningen og gå tilbake til regnearket.
Eksempel: Netto nåverdi
Hvordan bestemmer et selskap om å investere i et nytt prosjekt? Hvis "Netto nåverdi" (NPV) er positiv, vil de investere i den. Dette er en standard tilnærming tatt av de fleste finansielle analytikere.
For eksempel, antar at Codelco-gruveselskapet ønsker å utvide Andinas kobbergruve. Standardmetoden for å avgjøre om prosjektet skal gå videre er å beregne netto nåverdi. Hvis NPV er større enn null, vil prosjektet være lønnsomt gitt to innganger (1) tid og (2) kapitalkostnad.
På vanlig engelsk betyr kostnaden for kapital hvor mye de pengene ville tjene hvis de bare forlot det i banken. Du bruker kapitalkostnaden til å redusere kontantverdier til nåverdi, med andre ord $ 100 på fem år kan være $ 80 i dag.
I det første året er 45 millioner dollar avsatt som kapital for å finansiere prosjektet. Revisorene har fastslått at deres kapitalkostnader er seks prosent.
Etter 13 år er NPV $ 3 945 074 USD, så prosjektet vil være lønnsomt. Ifølge Finansanalytikere er "tilbakebetalingstiden" 13 år.
Opprette en pivottabell
Et "pivottabell" er i utgangspunktet en rapport. Vi kaller dem pivottabeller fordi du enkelt kan bytte dem en type rapport til en annen uten å måtte lage en helt ny rapport. Så de dreie på plass. La oss vise et grunnleggende eksempel som lærer de grunnleggende konseptene.
Eksempel: Salgsrapporter
Salgsfolk er svært konkurransedyktige (det er en del av å være selger), så de vil naturligvis vite hvordan de går mot hverandre i slutten av kvartalet og slutten av året, pluss hvor mye provisjonene deres vil bli.
Anta at vi har tre selgere - Carlos, Fred og Julie - alle selger petroleum. Deres salg i dollar per regnskapsår for året 2014 er vist i regnearket under.
Velg "Insert -> Pivot Table, den er på venstre side av verktøylinjen:
Hvis vi klikker på alle fire feltene i pivottabelldialogboksen (kvartal, år, salg og selger) legger Excel til en rapport i regnearket som ikke gir mening, men hvorfor?
Her gir det oss summen av 2014 + 2014 + 2014 + 2014 = 24.168, noe som er tull. Det er også gitt summen av kvartalet 1 + 2 + 3 + 4 = 10 * 3 = 3 0. Vi trenger ikke denne informasjonen, så vi fjerner disse feltene for å fjerne dem fra vårt pivottabell.
Eksempel: Salg av selger
Du kan redigere "Sum of Sales" som å si "Total Sales", som er klarere. Du kan også formatere cellene som valuta, akkurat som du ville formatere noen andre celler. Først klikk på "Sum of Sales" og velg "Value Field Settings."
Eksempel: Salg av selger og kvartal
La oss nå legge til subtotaler for hvert kvartal. For å legge til subtotaler bare venstre-klikk på "Quarter" -feltet og hold og dra det til "rader" -delen. Du kan se resultatet på skjermbildet nedenfor:
Konklusjon
Innpakning, vi har vist deg noen av funksjonene i Microsoft Excels formler og funksjoner som du kan bruke Microsoft Excel til din virksomhet, akademiske eller andre behov.
Som du har sett, er Microsoft Excel et enormt produkt med så mange funksjoner at de fleste, selv avanserte brukere, ikke kjenner dem alle. Noen mennesker kan si det gjør det komplisert; vi føler det er mer omfattende.
Forhåpentligvis, ved å presentere deg mange eksempler på virkelige eksempler, har vi ikke bare demonstrert funksjonene som er tilgjengelige i Microsoft Excel, men har lært deg noe om statistikk, lineær programmering, lage diagrammer, bruk av tilfeldige tall og andre ideer som du nå kan vedta og bruk i skolen din eller hvor du jobber
Husk at hvis du vil gå tilbake og ta klassen igjen, kan du begynne å friske med leksjon 1!
