Kontrollera om ett eller flera villkor är uppfyllda i Excel?

Anta att vi har ett kalkylark med data och vi vill kontrollera om ett eller flera villkor är uppfyllda. Om ett villkor är uppfyllt, skall vi skriva data i en cell, om det inte uppfyllt skall vi säga att det inte är uppfyllt. Hur går vi tillväga då?

I denna bloggpost presenteras en lösning med hjälp av den inbyggda funktionen OM som finns i Excel. Vi tar ett exempel:

Vi har två kolumner med data som vi kallar för Data1 respektive Data2. Om åtminstone en kolumn innehåller siffran 1, skriver vi siffran 1 i kolumn D( även kallad Ett villkor). Innehåller ingen kolumn siffran 1 skriver vi FALSKT. I nedanstående skärmbild visas hur det ser ut:

Kalkylark Excel med OM-formler

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Kommentar: I statusraden högst upp syns formeluttrycket i kolumn D, rad 2. Där skrivs formeln som att vi ska leta efter siffran 1 i kolumn B (Data1) rad 2. Likaså letar formeln efter siffran 1 i kolumn C (Data2). Om vi hittar siffran 1 åtminstone en gång, i kolumn B eller kolumn C, är villkoret sant. Värdet som kommer att skrivas ut i kolumn D, rad 2 blir således siffran 1.

Share

Hur man använder dataområde för att ta reda på flest antal observationer i ett Excel-kalkylark

Nivå: Avancerat

Säg att du har sammanställt mätvärden i ett kalkylark, med olika variabler. Nu är frågan, kan jag ta reda på hur många observationer jag har med mätvärden för 1, 5, 10 osv. i hela materialet? Javisst, går det! Jag skall visa en enkel metod för hur man besvarar den frågan. Hela materialet i detta fall, är lika med ett kalkylark med data.   För att bättre förstå förutsättningar för exemplet, se nedanstående bild:

Excel kalkylark med data

Kommentar till bilden: Vi har ett kalkylark med data och variabler. Frågan är alltså, hur många mätvärden med siffran 1 finns det totalt i materialet? Hur många med mätvärdet 5 finns det osv.?

Lösningen: Vi skall använda den inbyggda Excel-funktionen ANTAL.OM och definiera ett dataområde.

Steg 1 – Definiera ett dataområde

Det första steget är att definiera området med data och ge det ett namn. I bilden nedan framgår det gråa området. Detta område kommer att bli vårt dataområde. Vi kan se att dataområdet sträcker sig från cell B3 till cell K22. På Excel-språk skrivs detta B3:K22. För att göra det enklare namnger vi området Dataområde. Vad vi sedan gör är att ställa markören ovanför kolumn A och skriva Dataområde och sedan trycker ENTER. Klart! Vi har nu definierat vårt Dataområde.

Excel dataområde

Steg 2 – Bestäm vad vi skall leta efter

Nu när dataområdet är definierat, skall vi tala om för Excel vad vi ska leta efter. Detta gör vi genom att använda den inbyggda Excel-funktionen ANTAL.OM. I en kolumn ställer vi upp de värden vi skall leta efter. Du ser dessa värden under kolumnen med namnet Värden.

Excel dataområde

Steg 3 – Skriv in formeluttrycket

Slutligen skall vi skriva in formeluttrycket. Vi gör detta i kolumn N och namnger kolumnen Antal obs. Se bilden nedan.

Excel formelbeskrivning

Kommentar till bilden: I kolumnen Antal obs. visas beräkningar från dataområdet. Tittar vi den kolumnen framgår det tydligt vad som händer. Först säger vi åt Excel att vi vill leta efter värdet 1 hela dataområdet (den blåfärgade ytan). När vi hittar värdet 1, som står angivet i cell M3 (rödmarkerat) skall vi skriva antalet vi hittar till cellen N3, osv. Jag har här bara visat hur vi räknar fram värdet i första cellen N3. De återstående beräkningarna får vi fram genom att använda funktionen Autofyll i Excel.

Referenser

https://support.office.com

Share

Hur man konverterar textsträngar och siffror till korrekta datum i Excel.

Alla som har arbetat med Excel, vet att tid och datum är speciellt att hantera. Ibland kan det upplevas som svårt och krångligt. I den här bloggposten går igenom ett sätt att på ett relativt okomplicerat sätt, konvertera textsträngar och siffror till korrekt, formaterade datum i Excel. Öppnar man en fil i Excel, så försöker Excel att känna igen datum och tid automatiskt. Ibland går det inte så bra, eftersom data kan vara formaterad som text, även om det är siffror.

Ett exempel på hur man kan hantera detta:

Vi har en kolumn med textdata. Textdatat är skrivet, enligt svensk datumstandard: ÅÅÅÅMMDD. Ett exempel, uttryckt i siffror är, 20170701.  Problemet är att det inte är ett korrekt, formaterat datum. Hur löser man detta? Det enklaste sättet jag hittat, om man nu inte vill fördjupa sig i VB-kod och skriva funktioner, är att använda Excel-funktionerna; VÄNSTER, EXTEXT och HÖGER. (På engelska heter dessa funktioner Left, Mid och Right). Man använder dessa funktioner tillsammans, för att konvertera textdata till ett korrekt datum tillsammans med DATUM(Date)-funktionen.

Vi ska titta på ett exempel så blir det mer tydligt. I en tabell har vi följande data i Excel (se bilden nedan):

Konvertera text till datum

 

Kommentar:

  • Datum-funktionen sätter slutligen ihop ett korrekt datum. Uttrycket är skrivet i sin helhet nedan.
  • =DATUM(VÄNSTER(A2;4);EXTEXT(A2;5;2);HÖGER(A2;2))
  • Funktionen VÄNSTER(Left), letar i cell A2 och tar de 4 första tecknen från vänster räknat och skapar ”2015”, som ett konverterat datum.
  • Funktionen EXTEXT(Mid) letar i cell A2. Funktionen börjar vid det femte tecknet och tar sedan två tecken åt höger. Detta skapar ”04”, som är månaden.
  • Funktionen HÖGER tar de två första tecknen, med start från höger, riktning vänster. Detta skapar dagen “01”, som en del av datumet.

Använder du en engelsk version av Excel ser uttrycket ut på följande sätt:

=DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2))

Sammanfattningsvis: vad vi har gjort är att delat upp textsträngen i kolumnen DateString i olika delar och sedan satt ihop delarna till ett korrekt formaterat datum I Excel, kolumnen DateFormat.

Share

Skapa ett candlestick-diagram i Excel

I den här bloggposten visar jag hur man kan skapa ett candlestick-diagram i Excel. Enkelt uttryck är ett candlestick-diagram en grafisk analysteknik som används för att analysera börsutvecklingen för en aktie eller tillgång.

(Syftet är inte att beskriva eller förklara candlestick-diagram i sig, utan hur man tekniskt gör dem i Excel. Vill du veta mer om candlestick-diagram föreslår jag att du söker på nätet. Det finns många hemsidor som beskriver hur man använder dem.)

Candelstick diagram

 

Med detta sagt, är det dags att se hur man går tillväga i Excel. Det svåraste med att tillverka dem, är att ha rätt formaterad data. När man väl har detta, är det andra relativt enkelt.

För att tillverka ett candlestick-diagram måste data vara formaterat enligt följande ordning i Excel: Datum, Open, High, Low, Close. Notera att ordningen är viktig! För varje börsdag visas råoljans pris i ordningen Open, High, Low, Close. Först finns aktuell börsdag, Date. Diagrammet hittar du i diagramguiden (se bilden nedan).

Diagramguiden som visar diagram av typen candlestick

Nedan finns en Excel-fil som du kan ladda ner för att testa på egen hand.

Share

Beräkna nettoarbetsdagar i Excel

Hur beräknar man antalet arbetsdagar, eller nettoarbetsdagar som det kallas i Excel? Hur många arbetsdagar är det under ett år? Hur många arbetsdagar är det mellan Påsk och Midsommar? Ja, ibland är det bra att kunna beräkna antalet arbetsdagar för en given tidsperiod. I den här bloggposten går jag igenom hur man gör i Excel med den inbyggda funktionen Nettoarbetsdagar. Nettoarbetsdagar skall förstås som antalet arbetsdagar, när man justerat för veckoslut(lördag-söndag) och våra helgdagar under året.  (Längre ned finns en länk där du kan ladda ner en excelfil och testa själv!)

Uppställning beräkning Nettoarbetsdagar i Excel (klicka bilden för att göra den större!)

 

 

 

 

 

 

 

Kommentar: Det gulmarkerade området på bilden ovan, visar datumen för de svenska helgdagarna, 2017. I kolumn B3:B10 är datumen inskrivna och i kolumnen C3:C10 finns en förklaring till vilken helgdag det handlar om. Vad funktionen Nettoarbetsdagar gör är att den sedan exkluderar lördag och söndag, samt de definierade helgdagarna som vi angivit i området B3:B10. Klickar du på cellen D14, så framgår hur många arbetsdagar vi har totalt under 2017. Notera att cellområdet B3:B10 har absoluta cellreferenser, eftersom vi inte vill att området skall ändras utan användas som ett sorts uppslag mot de datum vi kontrollerar mot. Sedan är det bara att ange ett startdatum och ett slutdatum och vi kan kontrollera hur många arbetsdagar vi har för en aktuell tidsperiod.

Nettoarbetsdagar2017

 

Share

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