Hur du fastställer en persons kön utifrån dennes personnummer i Excel

Nivå: avancerat

I två tidigare bloggposter har jag beskrivit hur man tar fram en persons kön utifrån den näst sista siffran i personnumret i Access. Jag fullbordar detta, genom att publicera en tredje variant på samma tema och hur man kan göra detta i Excel. I Excel använder jag tre av de inbyggda funktionera. Funktionerna som används är: OM, REST och EXTEXT. Skillnaden mot Access är att man i Excel nästlar in funktionerna i en cell för att lösa uppgiften.

 Steg 1

I nedanstående bild finns två påhittade personnummer som vi ska hitta personens kön för. Som vanligt ska vi fokusera på den 11 siffran i personnumret. Den 11 siffran visar som bekant om det är man eller kvinna. Om du tittar i den markerade cellen på bilden nedan ser du hur du sätter uppfunktionerna i Excel (klicka på bilden för att den ska bli större!).

Funktioner i Excel

 

 

 

 

 

 

De andra två bloggposterna om personnummer och Access hittar du under kategorin ”Access”

Referenser:

 

 

Share

Hur du fastställer en persons kön utifrån dennes personnummer i Access – version 2

Nivå: avancerat

Jag har i en tidigare bloggpost, beskrivit hur man tar fram en persons kön utifrån den näst sista siffran i personnumret. I denna bloggpost visar jag ett enklare sätt att göra detta på. Upplägget är i princip på samma sätt som tidigare, jag kommer att använda en utav Access inbyggda funktioner. Denna gång är det funktionen Mid i Access som kommer att användas, även IIF-funktionen kommer att användas.

Personnumret skrivs vanligtvis efter formatet 121212121212 och är tolv tecken långt. Ibland kan man se att det skrivs med ett skiljetecken, enligt formatet 12121212-1212. I detta exempel, är skiljetecknet borta och vi skriver personnumret med tolv tecken. Jag försöker ofta ha mina data i textformat när jag manipulerar data i Access, så även siffror. Ett viktigt skäl är att jag tycker det går lättare att manipulera s.k. textsträngar, eftersom det finns mer funktioner för textmanipulering.

Steg 1

Funktionen MID har syntaxen Mid( sträng, start [, längd ] ). Syntaxen innebär följande:

  • I första steget(sträng) talar vi om i vilken kolumn våra personnummer finns.
  • I andra steget(start) måste vi tala steg vid vilken position i textsträngen där vi ska börja. Vi vill som bekant, ha position 11 räknat från vänster, eftersom det är den näst sista siffran i personnumret.
  • I det tredje(längd) och sista steget måste vi tala hur många positioner vi vill ha med från textsträngen och det är ju som bekant endast 1 position vi vill ha, den näst sista.

I Utrycksverktyget i Access, ser det ut på följande sätt. (Se nedanstående skärmdump):

Funktionen MID i Access

Funktionen MID i Access Uttrycksverktyget.

 

 

 

 

Testa nu och kör frågan du skapat. Har du gjort riktigt, kommer du att se att i det nya fältet PNRSCHECK finns jäma eller udda tal (1 position) där.

Steg 2

Nu är det bara ett steg kvar: du måste översätta de udda och jämna talen till man eller kvinna. Jämn siffra är som bekant kvinna och udda siffra är man. Ett förslag är att du går till min tidigare bloggpost (se högst upp för länken!) och följer det som kallas steg 3 – IIF-satsen för hur du kan göra.

Referens:

Share

Centrala gränsvärdessatsen Excel

nivå: avancerat

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.

https://sv.wikipedia.org/wiki/Centrala_gränsvärdessatsen

 

Share

Statistikfunktioner Excel

nivå:  avancerat

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.

Ladda ner excelfilen, genom att klicka här!

Share

Hur man skapar ett massutskick med hjälp av Excel via Outlook

Nivå: Avancerad

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 arket ser ut i Excel

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)

VB-koden du skriver in

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.

Relevanta referenser till exemplet:

http://www.rondebruin.nl/win/s1/outlook/tips.htm

 

Share

Hur man rensar data i Excel med funktionen Snabbfyllning

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:

Så här gör man!

 

 

Share

Beräkna QALY-vikter i STATA

QALY

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.

Enligt forskargruppen togs följande vikter fram, (enligt nedan). Tabellen är hämtad ur Michael Drummond’s bok ”Methods for the Economic Evaluation of Health Care Programmes”. Second edition 1997:

DIMENSION COEFFICIENT
Constant 0.081
Mobility
-level 2 0.069
-level 3 0.314
 Self-care 0.104
 -level 2 0.104
 -level 3 0.214
 Usual activities
 -level 2 0.036
-level 3 0.094
Pain/discomfort
-level 2 0.123
-level 3 0.386
Anxiety/depression
-level 2 0.071
-level 3 0.236
N3 0.269

 

STATA DO-file

I Stata sätter man upp programmeringen i Do-filen på följande sätt:

Programmering i STATA

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 och värdet lä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.

Beräkningen av EQ5D utförd i STATA

Beräkningen av EQ5D utförd i STATA

Share

Skapa nya ark automatiskt i Excel

Nivå: medelsvår

I det är exemplet visas ett enkelt makro på hur du automatisk skapar 5 st. nya kalkylark i Excel. För att testa exemplet gör följande:

Gå in i Excel’s Visual Basic Editor eller tryck tangterna Ctrl+F11 samtidigt. Skriv in nedanstående kod i en kodmodul.

Sub SkapaArk()
‘Skapa 5 Excelark…
For counter = 1 To 5
‘Ett nytt ark läggs till…
Worksheets.Add
‘Gör nu detta 5 gånger,
Next counter
End Sub

Gå ur Visual Basic Editor och kör proceduren SkapaArk. Om du gjort rätt, skall det nu finnas fem stycken kalkylark i Excelarbetsboken.

Share

Resurser för Excel

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)

 

Share