excel formler: uppslagningar, diagram, statistik och pivottabeller

Ha granskat grundläggande funktioner, cellreferenser och datum- och tidsfunktioner, vi nu dyka in några av de mer avancerade funktionerna i Microsoft Excel. Vi presenterar metoder för att lösa klassiska problem inom ekonomi, försäljningsrapporter, fraktkostnader, och statistik.

Dessa funktioner är viktiga för företag, studenter och de som bara vill lära sig mer.

Här är ett exempel för att illustrera vertikal lookup (LETARAD) och horisontell lookup (HLOOKUP) funktioner. Dessa funktioner används för att översätta ett nummer eller annat värde till något som är förståeligt. Till exempel kan du använda LETARAD att ta ett artikelnummer och returnera varan beskrivning.

För att undersöka detta, låt oss gå tillbaka till vår “beslutsfattare” kalkylblad i del 4, där Jane försöker bestämma vad som ska bära till skolan. Hon är inte längre intresserad av vad hon bär, eftersom hon har landat en ny pojkvän, så hon kommer nu bär slump kläder och skor.

I Janes kalkylblad, visar hon kläder i vertikala kolumner och skor, horisontella kolumner.

Hon öppnar kalkylbladet och funktionen RANDBETWEEN (1,3) genererar ett tal mellan eller lika med en och tre motsvarande de tre typer av kläder hon kan bära.

Hon använder funktionen RANDBETWEEN (1,5) för att plocka bland fem typer av skor.

Eftersom Jane inte kan bära ett antal vi behöver för att omvandla detta till ett namn, så vi använder lookup funktioner.

Vi använder funktionen LETARAD att översätta outfit numret kläder namn. HLOOKUP översätter från sko nummer till olika typer av skor i raden.

Kalkylbladet fungerar så här för kläder

Excel plockar ett slumptal från ett till tre, eftersom hon har tre dräkten alternativ.

Nästa formeln översätter tal till text med = LETARAD (B11, A2: B4,2) som använder slumptal värdet från B11 att titta i området A2: B4. Det ger då resultatet (C11) från de uppgifter som anges i den andra kolumnen.

Vi använder samma teknik för att plocka skor, utom den här gången använder vi VOOKUP istället för HLOOKUP.

Nästan alla vet en formel från statistik – genomsnittet – men det är en annan statistik som är viktig för företag: standardavvikelse.

Till exempel har många en person som har gått på college våndades över deras SAT poäng. De kanske vill veta hur de rankas jämfört med andra studenter. Universiteten vill veta detta också eftersom många universitet, särskilt prestigefyllda, stänga ned studenter med låga SAT poäng.

Så hur skulle vi, eller ett universitet, mäta och tolka SAT poäng? Nedan finns SAT poäng för fem studenter som sträcker sig från 1870 till 2230.

De viktiga siffror att förstå är

Genomsnitt – genomsnitt är också kallad “betyder.”

Standardavvikelse (STD eller σ) – Denna siffra visar hur spridd en uppsättning siffror är. Om standardavvikelsen är stort, än de siffror som är långt ifrån varandra, och om den är noll, alla nummer är desamma. Man kan säga att standardavvikelsen är den genomsnittliga skillnaden mellan medelvärdet och det observerade värdet, det vill säga 1998 och varje SAT poäng. Observera att det är vanligt att förkorta standardavvikelse med hjälp av den grekiska symbolen sigma “σ”.

Percentilen Rank – När en elev får en hög poäng, kan de skryta att de är i topp 99 percentilen eller något liknande. “Percentil rank”: den del av poängen är lägre än en viss poäng.

Standardavvikelse och sannolikhet är nära kopplade till varandra. Man kan säga att för varje standardavvikelse, sannolikheten eller sannolikheten för att detta nummer är inuti som antal standardavvikelser är

Som ni kan se, 3 STD: s är chansen att någon SAT poäng är utanför praktiskt taget noll, eftersom 99,73 procent av poängen är inom tre STD-talet.

Låt oss nu titta på kalkylbladet igen och förklara hur det fungerar.

Nu ska vi förklara formlerna

#REF!

Genomsnittet av alla poäng över området B2: B6. Specifikt, summan av alla poängen dividerat med antalet människor som tog testet.

#REF!

Standardavvikelsen över intervallet B2: B6. Den “.P” betyder STDEV.P används över alla poängen, dvs. hela befolkningen och inte bara en delmängd.

#REF!

Detta beräknar den kumulativa procent över området B2: B6 baserad på SAT poäng, i det här fallet B2. Till exempel 83 procent av poängen är under Walker poäng.

Att sätta resultaten i en graf gör det lättare att förstå resultaten, plus att du kan visa den i en presentation för att göra din punkt tydligare.

Studenter är på den horisontella axeln och deras SATT tjog visas som en blå stapeldiagram på en skala (vertikal axel) från 1600 till 2300.

Den percentilen ranking är den högra vertikala axeln från 0 till 90 procent, och representeras av den grå linjen.

Att skapa ett diagram är ett ämne i sig själv, men vi kommer att förklara kortfattat hur tabellen ovan skapades.

Först väljer de olika cellerna att vara i diagrammet. I det här fallet A2 till C6, eftersom vi vill att siffrorna samt studentens namn.

Från menyn “Infoga” välj “diagram” -> “Rekommenderade diagram”

Datorn rekommenderar “Clustered-kolonn, sekundär axel” diagram. Den “Sekundär Axis” del innebär det drar två vertikala axlar. I detta fall är det här diagrammet den vi vill ha. Vi behöver inte göra något annat.

Du kan använda flytta diagrammet runt och ändra storlek tills du har det som storlek och i den position du vill. När du är nöjd kan du spara diagrammet i kalkylbladet.

Om du högerklickar på diagrammet, sedan “Select Data,” det visar vilka data som valts för intervallet.

Den “Rekommenderade diagram” funktionen bails vanligtvis dig från att behöva ta itu med sådana komplicerade detaljer som att bestämma vilka data som ska inkludera, hur du tilldelar etiketter, och hur du tilldelar den vänstra och högra vertikala axlar.

I “Välj datakälla” dialogrutan, klicka på “score” under “Legend Inlägg (serie)” och tryck på “Redigera” och ändra det till säga “Score”.

Ändra sedan serie 2 ( “percentilen”) till “percentilen.”

Gå tillbaka till diagrammet och klicka på “Diagramrubrik” och ändra det till “SAT Scores.” Nu har vi en komplett diagram. Den har två horisontella axlar: en för SAT poäng (blå) och en för kumulativ procentsats (orange).

Den transportproblem är ett klassiskt exempel på en typ av matematik som kallas “linjär programmering.” Detta låter dig maximera eller minimera ett värde på vissa begränsningar. Det har många ansökningar till brett utbud av affärsproblem, så det är bra att lära sig hur det fungerar.

Innan vi sätter igång med det här exemplet har vi aktivera “Excel Solver.”

Välj “File” -> “Alternativ” -> “Add-ins”. Längst ner i tilläggen alternativ, klicka på “Go” knappen bredvid “Hantera: Excel-tillägg ins.”

På menyn som visas genom att klicka på kryssrutan för att aktivera “Lexikon tillägget” och klicka på “OK”.

Antag att vi skickar iPads och vi försöker fylla våra distributionscentraler med hjälp av de lägsta transportkostnader möjligt. Vi har ett avtal med en lastbilstransporter och flygbolaget att leverera iPads från Shanghai, Peking och Hongkong till fördelningCentrum visas nedan.

Priset för att leverera varje iPad är avståndet från fabriken till distributionscentralen till anläggningen dividerat med 20.000 kilometer. Till exempel är det 8024 km från Shanghai till Melbourne som är 8024/20000 eller $ 0,40 per iPad.

Frågan är hur vi skickar alla dessa iPads från dessa tre anläggningar till dessa fyra destinationer till lägsta möjliga kostnad?

Som ni kan föreställa er, kan räkna ut detta vara mycket svårt utan någon formel och verktyg. I det här fallet har vi att leverera 462,000 (F12) totalt iPads. Anläggningarna har en begränsad kapacitet på 500,250 (G12) enheter.

I tabell så att du kan se hur det fungerar, har vi skrivit en i cell B10 vilket innebär att vi vill skicka en iPad från Shanghai till Melbourne. Eftersom transportkostnader längs den vägen är $ 0,40 per iPad den totala kostnaden (B17) är $ 0,40.

Antalet beräknades med hjälp av funktionen = SUMPRODUCT (kostnader, levereras) “kostnader” är intervall B3: E5.

Och “levereras” är området B9: E11

SUMPRODUCT multiplicerar “kostnader” gånger så lång räckvidd “levereras” (B14). Det kallas “matrismultiplikation.”

För SUMPRODUCT att fungera, de två matriserna – kostnader och levereras – måste vara av samma storlek. Du kan komma runt denna begränsning genom att extra kostnader och frakt kolumner och rader med nollvärde, så att uppsättningarna har samma storlek och det finns ingen inverkan på de totala kostnaderna.

Om allt vi hade att göra var multiplicera matriser “kostnader” gånger “levereras” som inte skulle vara alltför komplicerat, men vi måste ta itu med begränsningar där.

Vi måste leverera vad varje distributionscentral kräver. Vi sätter att konstant i solver så här: $ B $ 12: $ E $ 12> = $ B $ 13: $ E $ 13. Detta betyder summan av vad som levereras, dvs summorna i cellerna $ B $ 12: $ E $ 12, måste vara större än eller lika med vad varje distributionscentral kräver ($ B $ 13: $ E $ 13).

Vi kan inte leverera mer än vi producerar. Vi skriver att begränsningar så här: $ F $ 9: $ F $ 11 <= $ G $ 9: $ G $ 11. Med andra ord, vad vi skickar från varje planta $ F $ 9: $ F $ 11 kan inte överstiga (måste vara mindre än eller lika med) kapacitet varje anläggning: $ G $ 9: $ G $ 11. Nu gå till menyn "Data" och tryck på "Solver" -knappen. Om "Solver" -knappen är inte där, måste du aktivera Solver-tillägg. Skriv in de två begränsningar som beskrivs tidigare och välj "Transporter" -serien, som är den nummerserie som vi vill Excel att beräkna. plocka även standardalgoritmen "Simplex LP" och indikerar att vi vill "minimera" cell B15 ( "total fraktkostnader"), där det står "Set mål." Tryck på "Lös" och Excel sparar resultaten i kalkylbladet, vilket är vad vi vill ha. Du kan också spara så att du kan leka med andra scenarier. Om datorn säger att det inte kan hitta en lösning, då du har gjort något som inte är logiskt, till exempel, kan du ha begärt mer iPads än växterna kan producera. Här Excel säger att det hittat en lösning. Tryck på "OK" för att hålla lösningen och återgå till kalkylbladet. Hur ett företag besluta om att investera i ett nytt projekt? Om "nuvärdet" (NPV) är positiv, kommer de att investera i det. Detta är en vanlig strategi som de flesta finansanalytiker. Anta till exempel att det Codelco gruvföretaget vill utöka Andinas koppargruva. Standardförfarandet för att avgöra om att gå vidare med ett projekt är att beräkna nuvärdet. Om NPV är större än noll, då projektet kommer att vara lönsamt med tanke på två ingångar (1) gång och (2) kapitalkostnaden. På ren svenska, kapitalkostnader betyder hur mycket skulle det pengar skulle tjäna om de bara lämnade det i banken. Du använder kapitalkostnaden för att diskontera kassa värden till nuvärde, med andra ord $ 100 på fem år skulle vara $ 80 i dag. Under det första året, $ 45.000.000 är avsatt som kapital för att finansiera projektet. Revisorerna har bestämma att deras kapitalkostnaden är sex procent. När de börjar gruvdrift, startar kontanter kommer in som bolaget finner och säljer koppar de producerar. Självklart, ju mer de bryta, desto mer pengar de gör, och deras prognos visar sitt kassaflöde ökar tills den når 9.000.000 $ per år. Efter 13 år är NPV $ 3,945,074 dollar, så projektet kommer att vara lönsamt. Enligt Finansanalytiker "pay-back period" är 13 år. En "pivottabell" är i grunden en rapport. Vi kallar dem pivottabeller eftersom du enkelt kan växla mellan dem en typ av rapport till en annan utan att behöva göra en helt ny rapport. Så de svänga på plats. Låt oss visa en grundläggande exempel som lär de grundläggande begreppen. Säljare är mycket konkurrenskraftiga (som är en del av att vara en försäljare) så de naturligtvis vill veta hur de klarar sig mot varandra i slutet av kvartalet och i slutet av året, samt hur mycket deras uppdrag kommer att bli. Antag att vi har tre säljare - Carlos, Fred, och Julie - alla säljer petroleum. Deras försäljning i dollar per räkenskapskvartal för år 2014 visas i tabellen nedan. För att generera dessa rapporter, skapar vi en pivottabell Välj "Infoga -> Pivot Table, är det på den vänstra sidan av verktygsfältet

Markera de alla rader och kolumner (inklusive försäljaren namn) som visas nedan

Pivottabellen dialogrutan visas till höger i kalkylbladet.

Om vi ​​klicka på alla fyra fält i pivottabellen dialogrutan (kvartal, år, försäljning och säljare) Excel lägger till en rapport till kalkylblad som är meningslöst, men varför?

Som ni ser har vi valt alla fyra fält för att lägga till rapporten. Excel standardbeteende är att gruppera rader av textfält och sedan summera alla de övriga raderna.

Här ger oss summan av år 2014 + 2014 + 2014 + 2014 = 24.168, vilket är nonsens. Också det gav är summan av kvartal 1 + 2 + 3 + 4 = 10 * 3 = 3 0. Vi behöver inte den här informationen, så vi välja bort dessa fält för att ta bort dem från vår pivottabellen.

“Summan av försäljning” (total försäljning) är relevant, dock så vi fixar det.

Du kan redigera “Sum of Sales” det vill säga “Total försäljning”, som är tydligare. Dessutom kan du formatera cellerna som valuta precis som du skulle formatera andra celler. klicka först på “Sum of Sales” och välj “Standard inställningar.”

På dialogrutan ändrar vi namn till “Total Sales” klicka sedan på “numeriskt format” och ändra det till “valuta”.

Du kan sedan se din skapelse i pivottabellen

Låt oss nu lägga till delsummor för varje kvartal. För att lägga till delsummor bara vänsterklicka på “Quarter” -fältet och håll och dra den till avsnittet “rader”. Du kan se resultatet på skärmdumpen nedan

Medan vi ändå håller på, låt oss ta bort “Summan av Quarter” värden. Klicka bara på pilen och klicka på “Ta bort fält.” I skärmdump, kan du nu se att vi har lagt till “Quarter” rader, som bryter ner varje säljare försäljning per kvartal.

Med dessa kunskaper färska i åtanke, kan du nu skapa pivottabeller från dina egna data!

Inslagning, har vi visat dig några av funktionerna i Microsoft Excel formler och funktioner som du kan använda Microsoft Excel för ditt företag, akademiska eller andra behov.

Som ni har sett, är Microsoft Excel en enorm produkt med så många funktioner som de flesta människor, även avancerade användare, inte vet dem alla. Vissa människor kan säga som gör det komplicerat, vi känner att det är mer omfattande.

Förhoppningsvis genom att presentera dig massor av verkliga exempel, har vi visat inte bara de funktioner som finns i Microsoft Excel, men har lärt dig något om statistik, linjär programmering, skapa diagram, med hjälp av slumptal, och andra idéer som du nu kan anta och användning i din skola eller där du arbetar.

Kom ihåg att om du vill gå tillbaka och ta klassen igen, kan du börja om på nytt med lektion 1!

På 1980-talet och början av 1990 (till 1993), Nintendo försökt hålla videospel kvalitet högt via strikta tillståndsregler som begränsade förlag till endast fem godkända Nintendo Entertainment System titlar per år.