Hur man använder OM-funktionen i Excel för att hantera och räkna med tomma celler.

OM-funktionen i Excel är kraftfull och användbar. När man väl lärt sig behärska den inser man snart att man kan utföra kraftfulla utvärderingar och beräkningar.

I denna bloggpost visar jag ett exempel på hur man hantera tomma celler i ett kalkylark. Tomma celler indikerar att man har så kallade ’missing values’ eller uppgift saknas. I korthet menas att där det ska finnas ett värde, så är cellen tom och cellen innehåller inte några data. Hanterar man inte detta utan fortsätter att beräkna så uppstår ett systematiskt fel. Hur ska man hantera detta i Excel? Innan man kan utföra beräkningar måste man först utvärdera om cellen innehåller data eller inte. För att lösa uppgiften används den inbyggda funktionen OM i Excel.

 

Vi har ett exempel: I ett kalkylark har vi timlönen för några anställda. Vad vi vill testa är hur många anställda som har en timlön lägre än 135 kronor. Vi måste också undersöka om det finns tomma celler: där det borde finnas ett värde och det är tomt, är annars ett ’missing value’. I ett kalkylark har vi ett fält som heter Timlön och ett fält som heter Timlön HÖG. Innan vi skriver vad som ska göras, gör vi en beskrivning över vad som ska inträffa;

Steg 1 – Utvärdera cellen: om cellen i kolumn A är tom och inte har något värde, ska vi inte göra något; vi låter då cellen vara tom.

Steg 2 – Utvärdera cellens värde: Om det finns ett värde ska vi utvärdera för att se om det är mindre än 135; om det är sant, skriv siffran 0; är värdet större än 135, skriv siffran 1 i fältet Timlön Hög (se nedanstående skärmbild!)

 

Kommentar: I uttrycket ovan har vi två OM-funktioner; den första utvärderar om cellen är tom i fältet Timlön, om cellen är tom ska inget föras. Detta visas genom tecknet ””, som är ett annat sätt att skriv att det är tomt. Om det finns ett värde, letar den andra OM-funktionen efter om värdet i cellen är större eller mindre än 135. Om det är större, skriv siffran 1, annars siffran 0.

När vi utvärderat samtliga celldata i fältet Timlön ser det nu ut så här:

Om funktion för att hantera tomma celler

Kommentar: I den färdiga beräkningen ser vi att där det är tomma celler (’missing values’), utförs ingen beräkning och där det finns ett värde, görs en utvärdering om det är större eller mindre än 135.

 

Share

Hur man tar ut en bokstav från en cell med olika tecken i Excel

Nivå: Avancerad

Att man kan ut en speciell bokstav från en cell som innehåller olika tecken är användbart. I denna bloggpost presenterar jag en lösning med hjälp av tre inbyggda funktioner i Excel. Funktionen är OM, ÄRTAL och SÖK.  Nedan visas ett exempel:

Vi har ett kalkylark med data i kolumner, fältet heter Kolumn. I varje cell vill vi leta efter en bokstav, säg bokstaven A; om vi hittar bokstaven A, skall vi markera detta genom att skriva siffran 1 i fältet som heter Kolumn A. För att tydligare göra, så antar att vi att kalkylarket ser ut på följande sätt:

Alltså: i Kolumn, vill först leta efter bokstaven A. Hittar vi A, skriva siffran 1 i Kolumn A. Finns inte bokstaven A, skrivs siffran 0.

I cell C4, skriver vi nu följande uttryck: =OM(ÄRTAL(SÖK(”B”;B4));1;).

I bilden nedan framgår hur de enskilda funktionerna är kopplade till varandra. (Viktigt är att omsluta funktionerna med parenteser, så att de blir korrekt skrivna!)

Vi fyller ut resten av kolumnen med data, med hjälp av Autofyll. I nedanstående bild visas hur vi letar på andra bokstäver som bokstaven B, C och AB. Vi använder samma uppsättning som i exemplet ovan, med enda skillnaden att vi ändrar vad vi ska söka efter.

Share

Hur du ändrar formatering på data i Excel

Oftast när man ta ut data från olika databassystem i Excelformat, kan man få data i olika formatering. Denna formatering kan ställa till det, om den data du exporterar är så kallade kolumner som är nycklar. Nycklar brukar användas när man skall matcha två tabeller. Ett exempel:

Vi har en kolumn med siffror i en cell och formateringen i cellen är på följande sätt:

12 1212120123

Totalt innehåller sifferserien 12 tecken. Det finns ett mellanslag efter siffran 12, innan sifferserien fortsätter. Mellanslaget i sifferserien ska tas bort, så att formateringen håller ihop, utan mellanslag. Så här vill vi att det ska se ut:

121212120123

Det enklaste och snabbaste sättet att formatera detta är att använda den inbyggda funktionen Ersätt. Funktionen Ersätt har följande syntax: Ersätt(Gammal_text;startpos;antal_tecken;ny_text)

Antag att vi har siffrorna som vi vill formatera i fältet C2 i ett kalkylark. Vi ställer oss i fältet C3 och skriver in följande kod:

=ERSÄTT(C2;3;1;””)

Kommentar till funktionen: I fältet C2, ska mellanslaget tas bort (Gammal_text).
Mellanslaget finns som position 3 räknat från vänster i fältet C2 (startpos)
Mellanslaget som ska tas bort är 1 position långt (antal_tecken)
Vi skall inte ersätta mellanslaget med någonting, därför anger vi tecknet ”” (ny_text).

Använd sedan autofyll för att ändra formateringen i återstående celler.

Share

Hur du beräknar och fördelar tid över en tidsperiod i Excel

Hur du beräknar och fördelar tid för t.ex. en arbetsdag är användbart i många sammanhang. Antag att du jobbar som konsult på ett företag och du har  tilldelats tre uppdragsgivare. För enkelhetens skull kan vi kalla uppdragsgivarna för Företag 1, Företag 2 och Företag 3.

Ekonomiavdelningen har tagit fram en budget åt dig och du får följande uppgifter om hur du ska fördela din arbetsdag:

  • Företag 1 skall du förlägga 40 % av din arbetstid
  • Företag 2 skall du förlägga 10 % av din arbetstid
  • Företag 3 skall du förlägga 50% av din arbetstid.

Nu är din uppgift att beräkna hur många timmar och minuter du skall fördela på respektive företag.  Hur gör man detta i Excel? Här beskriver jag en metod som jag tycker fungerar bäst. Nyckeln är att först beräkna allt i minuter och sedan formatera tiden i Excel.

Vi kan börja med vad vi vet;

  • 1 dygn = 24 timmar;
  • 24 timmar = 1440 minuter.
  • En vanlig kontorsarbetsdag består vanligtvis av 8 timmar. Sedan skall det vara 1 timmas lunchrast inräknad.

Nu har vi all information för att sätta upp beräkningen i Excel! (Längre ned på sidan har jag lagt upp en Excel-fil som du kan ladda ner och där jag visar hur du gör.) Det centrala i Excel-filen du laddar ner, är kolumnen Tidsformat, som du får fram genom att formatera beräkningen under avsnitt Tal, som finns på Start-fliken i Excel.

Klicka länken för att ladda ner Excel-filen Fördela_arbetstid

Share

Hur man kopierar en sammanställning gjord med DELSUMMA till nytt kalkylark i Excel.

Nivå: Mellansvår

Delsumma

Delsumma är ett kraftfullt verktyg i Excel. Delsumma är användbart när man vill sammanställa och sammanfatta data. Sammanställningarna kan exempelvis vara av typen deskriptiv statistik, där man beräknar medelvärden, summor, antal osv.

När man gjort en sammanställning med Delsumma vill man oftast flytta data till ett nytt kalkylark. Det kan vara för att man t.ex. vill göra en snyggare tabell eller diagram.

I denna bloggpost visar jag hur man gör detta på ett enkelt sätt. (I en annan bloggpost visar jag hur man skapar en Delsumma). Nedan visas resultatet från ett exempeldataset och hur en sammanfattning ser ut med Delsumma (se skärmbilden nedan)

Sammanställning med Delsumma

Av sammanställningen ser vi t.ex att de som arbetar i Butik 1 har en medelålder på 44 år och att man under den senaste månaden sålt 138 stycken dricksglas osv. (Just nu bryr oss inte om att det är en massa decimaler!)

Principen med Delsumma, är att Excel döljer vissa data och enbart visar  sammanfattande värden som exempelvis medelvärden för Butik 1, Butik 2, Butik 3 osv (Titta på bilden ovan igen!). Den övriga datan finns kvar där, fast den har gömts undan. Om vi skulle kopiera över området i bilden ovan, rakt av, till ett nytt kalkylark skulle vi få med oss hela dataarket med 200 poster, och det vill vi ju inte! Vi vill enbart kopiera över de rader och celler som vi ser från sammanställningen. För att lösa uppgiften får vi använda kommandot enbart synliga celler .

Kopiera sammanställningen till ett nytt ark – Enbart synliga celler

Nedan visas hur du använder kommandot enbart synliga celler, tillsammans med Delsumma. Markera först upp det område som är aktuellt för kopiering(se bilden, Sammanställning med Delsumma ovan).

  1. Klicka fliken Start i menyraden > Klicka ikonen Sök och markera, längst till höger. Klicka på piltangenten…

När dialogrutan kommer upp, välj Gå till special. (se bilden nedan)

I dialogrutan Gå till special, markera Enbart synliga celler klicka därefter OK.

Klicka sedan på Kopiera.

Gå till ett tomt, nytt kalkylark. Markera cellen längst upp till vänster på det nya kalkylarket och klicka på Klistra in. (Vi gör en standardinklistring.)

Klart! Vi har nu bara tagit med oss de celler med data som vi ville ha. Beronde på vad vi vill göra, kan vi nu snygga till tabellen.

Nedan, den överförda sammanställningen är nu en tabell, som vi kan formatera och göra snyggare beroende på vad som ska presenteras.

Share

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.

 

Du kan ladda ner en Excel-filen till exemplet, genom att klicka Villkorshantering_Excel.

 

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