Centrala Gränsvärdessatsen(CGS) är central inom ämnet Statistik. I denna arbetsbok visas tekniskt hur man kan kan använda Excel’s inbyggda funktioner för att bättre förstå en sannolikhetsfördelning och genomföra simmuleringar. Längst ned på sidan finns en länk till excelfilen du kan ladda ner.
Förenklart kan man säga att vad CGS visar, är att ju fler stickprov vi drar, beräknar dess medelvärdet, ju mer ”klockformad” kommer sannolikhetsfördelningen att bli. Medelvärdena från stickproven i sannolikhetsfördelningen kommer att närma sig den s.k. Normalfördelningen. Medelvärdena från stickproven kommer också att närma sig det förväntade, ”sanna” värdet vid tillräckligt många stickprov.
De funktioner som användas från Excel är:
Funktionen FREKVENS, notera att där den används implementeras den som en Array Formel
Funktionen MEDEL
Funktionen AVERAGEA – i princip samma som funktionen MEDEL. Används endast i testsyfte.
Funktionen SLUMP.MELLAN – används för att ta fram slumptalen.
Se i Excel-hjälpen för mer information om funktionerna.
OBS! Detta är inget kursmoment i ämnet Statistik, utan syftet är att visa de rent tekniska tillämpningarna i Excel.
Ladda ner excelfilen som visar simuleringar med Centrala gränsvärdessatsen genom att klicka här!
Referenser
Nedan finns länkar som bättre förklara Centrala gränsvärdessatsen.
Excel har en del mycket bra, inbyggda funktioner, inom olika områden. Ett område är Statistik. Visst, det går inte att mäta Excel mot ett professionella statistikprogram som t.ex STATA eller SPSS, för att nämna några exempel, men för att lösa mindre uppgifter snabbt fungerar Excel utmärkt.
I den här bloggposten visar jag några statistikfunktionaliteter med Excel. Längre ned på sidan kan du ladda ner excelfilen med olika exempel på statistiska tillämpningar.
I Excelfilen visar jag beräkningar för följande statistikfunktioner:
Poisson-fördelningen, här används Excel-funktionerna Poisson.Förd
Hypergeometrisk fördelning, här används Excel-funktionen Hypergeom.Förd
Binominal-fördelning, här används Excel-funktionen Binom.Förd eller Binom.Dist
Friskrivning: Notera att detta är absolut ingen skola i ämnet Statistik, utan statistikkunskaperna får du införskaffa på annat håll, om du inte redan har dem. Syftet är att visa på olika statistiska tillämpningar och Excels teknikaliteter.
Oftast har man data på olika ställen, och man vill slå samman dessa data till ett gemensamt kalkylark. I videoklippet visas principen för hur man länkar slår samman data till ett kalkyl ark.
Ibland vill kan det vara användbart att kunna göra ett ett massutskick till sina kunder, klienter osv. från Excel. I denna bloogpost visar jag principen för hur man skapar ett massutskick från Excel, med hjälp av Outlook som e-postklient.
Förutsättningar
Detta exempel kräver Outlook och Excel på datorn. Jag har testat nedanstående kod i miljöerna Windows 7 och Windows XP, med Outlook-versionerna Outlook 2013 och Outlook 2003 och koden fungerar för båda plattformarna. I den mån du fortfarande använder Windows XP måste du också har servicepack 3(SP3) installerat, gäller även för Outlook 2003, där du måste ha SP3. Notera att nedanstående kodexempel endast fungerar för Outlook.
Exempel
Anta att vi har en Excel-arbetsbok och ett ark med e-postadresser. Vill kunna skicka ett massutskick till dessa e-postadresser direkt från Excel.
Starta Excel
Det första vi gör är att ge kolumnerna namn. Första kolumnen kallas E-mail(Kolumn A), First Name(Kolumn B), Last Name(Kolumn C). Vi skapar en kolumn för rubriken, Mejlrubrik, för våra mejl, (Kolumn H) och slutligen kolumnen Meddelandet som skickas (Kolumn J). Här kommer själva texten att läggas in.
Kommentar: Detta upplägg ger en ganska fast struktur, och vi behöver endast ändra i mejlrubriken och meddelandetexten i Excel-arket. Vi kan också fylla på med e-postadresser, var efter listan växer. Det som återstår är att skriva kod så vi kan skicka iväg vår e-post.
Hur layouten i Excel-arket ser ut.
Koden
Gå in i Visual Basic Editor (Alt+F11).
Det första som måste göras är att sätta referenserna. Detta är mycket viktigt. Använder du t.ex Excel 2013, sätter du referenserna till Microsoft Outlook 15.0 Object Library. Använder du t.ex Outlook 2003, sätter du referenserna till Microsoft Outlook 11.0 Object Library.
Skriv sedan in följande kod i editorn (se bilden nedan)
Visual Basic koden som ska skrivas in.
Kommentar till koden: Vi använder oss av Outlook-objektet MailItem. Det är den som gör så att vi kan göra följande i detta fall:
skicka iväg mejlet till en mottagare,
ange en mejlrubrik,
och en meddelandetext.
I detta fall har vi gjort så att användaren som tar emot mejlet kommer att se att det har angetts med Hög Prioritet (se raden olMail.Importance = olImportanceHigh). Det är inte nödvändigt att använda nämnda egenskap. Om man inte använder den sätts prioriteten till Normal.
Det översta kodstycket är en funktion som vi kommer att anropa varje gång vi skall skicka ut mejl.
Det nedre kodstycket är den kod vi skriver, för det specifika fallet. I detta fall, vill vi mejla ut information från Excel-arket som vi skapade först. Först anges (ROW_NUMBER = 1) att vi skall börja leta på raden efter den första e-postadressen. Sedan skall vi leta igenom, via en Do-loop, fyra rader med e-postadresser som skall skickas ut. På raden som börjar med Call SendEmail, anropar vi funktionen ovan. Det är den raden med kod som gör det möjligt att skicka iväg mejlen. Vi ser att det står ”A”, vilket anger att vi ska börja leta i kolumn A på Excelarket, där e-postadresserna finns, därefter kolumn H, på rad 2, dvs H2, där mejlrubriken står. Slutligen skall mejlet ha ett meddelande som vi skriver in och det står i kolumn J, rad 2.
Begränsningar i denna kod är att vi måste ändra i Loopen varje gång vi lägger till nya e-postadresser, eftersom loopen är statisk. Den förutsätter att vi talar om hur många e-postadresser som finns i kolumn A. Vi måste också ändra ROW_NUMBER beroende på hur många mer e-postadresser som läggs till. För att få det mer funktionell, skulle man behöva skriva en kod som letar upp första tomma raden och sedan arbetar sig uppåt. Man kan också tänka sig att man lägger in olika kontroller i koden, så att man kontrollerar att det verkligen är giltiga e-postadresser som finns registrerade. Skälet till att det inte är med är att öka förståelsen och läsbarheten i exemplet.
Innan man kan börjar att räkna på sina data, måste man ha korrekt formaterad data. Enklare uttryckt: det man ska räkna på måste vara siffror. Egentligen en självklarhet, men det visar sig i praktiken att man ofta måste lägga tid på att rensa sina data innan man kan börja räkna på dem. I den här bloggposten visar jag hur man rensar data i Excel med funktionen Snabbfyllning. Proceduren att rensa sin data, vissa kallar det att tvätta data, kan vara mycket tidskrävande. En annan sak man måste vara observant på är att man inte vill förlora datavärden när man rensar datan.
Funktionen Snabbfyllning
Nytt för Excel version 13 är det finns en mycket smart, inbyggd funktion som heter Snabbfyllning (på enkelska Flash Fill), som gör detta till en barnlek. För över 10 år sedan fick man programmera procedurer och funktioner i Excel som man sedan använde till att rensa data. Min erfarenhet är att funktionen Snabbfyllning fungerar utmärkt på mindre datamaterial, säg ett datamaterial på ungefär några 100 poster. Har man datamaterial på över tio tusentals poster som ska rensas, är funktioen Snabbfyllning ingen snabb lösning och man måste man söka andra vägar. (Behöver du hjälp med detta kan du kontakta mig, exempelvis!)
I korthet fungerar funktionen Snabbfyllning på så sätt att den försöker hitta ett mönster i det som skall rensas ut. Man kan säga att man lär Excel vilka mönster den skall känna igen. I nedanstående Youtube-filmklipp visas ett exempel på hur man använder Snabbfyllning, med bra resultat:
Quality-Adjusted Life Years(Kvalitetsjusterade levnadsår), förkortas QALY, används inom Hälsoekonomin och kan förenklat sammanfattas som ett mått, med vilket man kan väga olika medicinska insatser mot varandra. Det här exemplet på hur man kan beräkna QALY-vikter är genomfört med hjälp av statistikprogrammet STATA. Jag utvecklar inte QALY-begreppet i denna bloggpost, utan hänvisar till andra källor på nätet. En del förklaringar måste dock göras, men fokus läggs på det tekniska, att beräkna QALY-vikter i STATA.
Den mest använda förteckningen för EQ-5D togs fram av en grupp forskare i England på 90-talet. (Någon svenska motsvarighet finns ännu inte.) Förenklat kan man säga, att man vad man gjorde, var att ta fram en standardpopulation, som olika vikter beräknades för.
Förteckningen över variabler ser ut på följande sätt (de engelska begreppen används) Mobility, Self-care, Usual activities, Pain/discomfort, Anxiety/depression. För respektive område (dimension) har man tre olika tillstånd som man kan befinna sig i: om vi tar Mobility som exempel, finns tillstånden 1. No problems walking; 2. Some problem walking about; 3. Confined to bed. Likvärdigt gäller för de andra variablerna.
I Stata sätter man upp programmeringen i Do-filen på följande sätt:
Programmering i STATA.
Kommentar till programkoden: Variabelnamnen är i samma ordning som i tabellen. Exempelvis är variabelnamnet för Mobility, mo. Variabeln mo är svaret på hur en individ besvarat frågan. Vad som görs i programsatsen är att en ny variabel skapas, dit aktuellt värde läggs in. Om vi tar variabeln Mobility som exempel, läggs värdet för variabeln mo i en ny variabel som skapas, mot. Antag att personen besvarat frågan om Mobility med alternativet 2. I variabeln mot räknas värdet om utifrån standardpopulationens vikt ochvärdetläggs sedan in i variabeln: 1-0.062=0.938. (Se högst upp i programkoden ovan)
Färdiga resultatet
I tabellen nedan syns de färdiga beräkningarna som do-filen utfört i STATA. Alla variabler är inte med på bilden nedan, men notera att till exempel variabeln eq5d är den sammanlagda, vägda QALY-vikten. Variabeln eq5d1 är egentligen samma som eq5d, eller rättare sagt, ska vara lika. Den är med som en kontrollvariabel så att det är riktigt uträknat.
Nedan listas aktuella sajter och resurser jag hittat för att arbeta med Excel. Nedanstående länkar till sidorna, fungerade då jag lade upp dem på bloggen, 2015-09-29. (Länkarna öppnas i nytt fönster)
GINI-koefficienten och Lorenz-kurvan används för att analysera ekonomisk ojämlikhet. Jag skall inte fördjupa mig så mycket i detta utan jag rekommenderar dig som är intresserad av att veta mer, att googla på dessa nyckelord. Det finns mycket information om GINI-koefficienten och Lorenz-kurvor på nätet. I den här bloggposten visar jag hur man kan beräkna Lorenz-kurvan och GINI-koefficienten i Excel.
Efter en del googlande på detta tema hittade jag tillslut några utmärkta Youtube-klipp, som visar hur man använder Excel för detta. Tyvärr hittade jag inga Excel-arbetsböcker att ladda ner, så jag konstruerade en Excel-arbetsbok utifrån Youtube-klippen. Mitt exempel är konstruerat i Excel version 2007 och du hittar länken till Excel-filen för nedladdning under rubriken Ladda ner.
Referenser
Som jag sade så skall all kredd gå till de som gjort Youtube-klippen, som jag gjort Excel-filen utifrån. Om du vill se Youtube-klippen är adressen till:
Importera textfiler till en Excel-arbetsbok? Hur gör man då? Läs vidare i den här bloggposten, så visar jag hur du importerar textfiler till en Excel-arbetsbok med hjälp av makro. Textfilerna kan i princip importeras på två sätt:
Man kan lägga varje textfil som en kolumn i ett Excel-ark
Man kan lägga varje data från en textfil i en ny flik i ett Excel-ark
Vilket sätt man använder beror på vad man vill göra med de data man importerar. Jag skall visa exempel på bägge metoderna och hur man går tillväga.
Textfiler
Något om textfilerna. Oftast har textfilerna samma format som man vill importera. De är uppställda på samma sätt och följer samma mall. Det viktigaste är att datan är avskilda på ett likvärdigt sätt. Excel har många olika sätt att hantera textfilsdata . För ett tag sedan genomförde jag en dataimport på över 3000 textfiler med data till en Excel-arbetsbok. I detta fall ville man ha in data enligt alternativ 1 ovan. Skall man importera stora mängder textfiler, måste man skriva ett makro som utför jobbet.
VBA-kod
Vi ska ta titta på ett makro i Excel som importerar data från textfiler. Först tittar vi på hur en import ser ut enligt alternativ 1. Som vanligt, starta Excel. Gå vi in i VBA-editorn med tangenterna Alt+F11, infoga en modul och lägg in följande kod:
Sub ImporteraTextFil()
Dim idx As Integer
Dim fpath As String
Dim fname As String
Dim Counter As Integer
idx = 0
fpath = "C:\Test\MyTextFiles\"
fname = Dir(fpath & "*.txt")
While (Len(fname) > 0)IsEmpty (ActiveCell.Value)
With ActiveSheet.QueryTables.Add(Connection:="TEXT;" _
& fpath & fname, Destination:=Range("A1"))
.Name = "a" & idx
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = "|"
.TextFileColumnDataTypes = Array(1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
fname = Dir
End With
Wend
End Sub
Kommentar till VBA-koden i Excel ovan
Alternativ 1
Koden ovan importerar textfiler, enligt vad som kallas alternativ 1. Den markerade koden efter variabeln fpathär sökvägen till katalogen på enhet C, där textfilerna finns lagrade. Koden kör en While-loopvilket innebär att i detta fall skall alla filer i katalogen läggas in i Excelboken, därav villkoret som följer. Den inbyggda funktionen IsEmpty hjälper oss att lägga in nya textfiler i nästa tomma kolumn. Därefter kommer olika formateringsinställningar, för hur textfilerna är formaterade som används vid importen. Koden har en begränsning och det är att den kraschar om den stöter på en textfil som är tom och inte har data. För att det ska vara korrekt, får man skriva en felhanterare som fixar detta. Det är inte gjort i detta exempel.
Alternativ 2
Om vi vill ha att textfilerna läggs in enligt alternativ 2, byt ut raden med koden för IsEmpty, med följande kod: Sheets.Add.Name = fname . Det är allt som behöver göras! Nu läggs varje textfil in på en ny flik i Excel!
Begränsningar
Exemplet är utfört på Excel version 2007. I Excel version 2007 kan ett kalkylblad innehålla 1 048 576 rader och 16 384 kolumner.
