exceltips

Hela siten är i och för sig full med smarta exceltips, men på den här sidan samlar jag tips som just är snabba (att beskriva eller lära) och som inte passar in på någon annan av mina sidor. En grundläggande beskrivning av hur du gör ett diagram läggar jag till exempel normalt i fliken exceldiagram, medan ett smart kortkommando eller något annat tricks om diagram kan hamna här. Många av tipsen finns också beskrivna mer i detalj i något blogginlägg. Dessutom läggar jag ofta bonustips i kommentatorsfälten i mina blogginlägg som en bonus för den som tar sig dit. Är det något speciellt du vill veta så kan du alltid använda sökrutan till höger…



Innehåll

Tips 1: Vrida data från kolumn till rad och vice versa (transponera)
Tips 2: villkorsstyrd formatering baserad på formel
Tips 3: Skapa slumptal
Tips 4: Hitta det n:te största värde i en lista
Tips 5: Skriv in samma data i flera celler samtidigt
Tips 6: Avrunda uppåt eller nedåt till närmaste tusental
Tips 7: Markera alla formler i ett kalkylblad
Tips 8: Snabbkommando för Gå till special
Tips 9: Skydda dina formler
Tips 10: Låsa upp ett lösenordsskyddat kalkylblad eller arbetsbok
Tips 11: Ett makro som lägger sökväg i sidfoten
Tips 12: Ta bort text i sidhuvuden och sidfötter i ett kalkylblad
Tips 13: Ta bort text i sidhuvuden och sidfötter alla kalkylblad i hela samtidigt
Tips 14: Skapa arbetsboken Egna
Tips 15: Ta fram fliken utvecklare
Tips 16: Skapa ett eget talformat som visar m²
Tips 17: Räkna antal tecken i en cell utan mellanslag
Tips 18: Formatera celler så tusental visas utan nollor
Tips 19: Konvertera plusvärden till minusvärden och vice versa
Tips 20: Markera alla formler i ett kalkylblad
Tips 21: Dölj felmeddelanden med OMFEL
Tips 22: Koppla ett makro till en knapp i verktygsfältet snabbåtkomst
Tips 23: Dölj Nollvärden i enstaka celler
Tips 24: Dagens datum
Tips 25: Räkna ut svenskt veckonummer med VECKONR
Tips 26: Om du vill summera data från flera kalkylblad
Tips 27: Räkna fram nRoten
Tips 28: Korrigera felaktiga datumformat
Tips 29: Formel som räknar ut kön baserat på personnummer
Tips 30: Villkorsstyrt format på varannan rad
Tips 31: Räkna med sekunder
Tips 32: Skapa en egen Excel mall
Tips 33: Räkna ut ålder från personnummer i Excel
Tips 34: Beräkna skillnaden mellan två tider som kan eller inte kan gå över midnatt
Tips 35: Byta namn på ett kalkylblad
Tips 36: Kopiera ett kalkylblad till en annan arbetsbok
Tips 37: Beräkna nuvärdet av ett belopp som utbetalas någon gång i framtiden
Tips 38: Skapa en talserie som räknas upp automatiskt
Tips 39: Kopiera relativa referenser som absoluta
Tips 40: Korrigera negativa tal med efterföljande minus
Tips 41: Räkna med exponent (upphöjd med) i Excel
Tips 42: Räkna endast synliga rader (eller celler) i ett Excelblad

Tips 1: Vrida data från kolumn till rad och vice versa (transponera)

Problem: Jag har en massa data i kolumner. Hur gör jag för att vrida om dem så att de ligger i en rad istället.

Lösning: Kopiera dina kolumner. Ställ dig i första cellen där du vill att raden skall börja. Välj: Klistra in special -> Transponera (Fliken Start – gruppen Urklipp). Notera att det finns en knapp för detta direkt under Klistra in special.

Tips 2: villkorsstyrd formatering baserad på formel

Problem: Jag skulle vilja ha en villkorsstyrd formatering som markerar dubbletter oavsett om talet är positivt eller negativt. D.v.s. 10 och -10 skall markeras som en dubblett.

Lösning: Om området du vill a formatet i ligger i A1 till A8 så löser formeln =ANTAL.OM($A$1:$A$8;A1)+ANTAL.OM($A$1:$A$8;-A1)>1 detta. Om du bara vill få en markering i en cell som (enbart) ar sin motsvarighet i ett negativt tal (dvs 10 och -10 eller -10 och 10) så kan du istället använda formeln =ANTAL.OM($A$1:$A$8;-A1)>0. Läs mer om ” Använda en formel för att avgöra vilka celler som ska formateras”.

Tips 3: Skapa slumptal

Du kan använda funktionen Slump för att generera slumptal. Med funktionen SLUMP.MELLAN (RANDBETWEEN) kan du generera ett sluhttp://andersexcel.se/wp-admin/post.php?post=407&action=edit#mpat heltal mellan intervall du själv anger.

Följande funktion slumpar ett heltal mellan 0 och 100: =SLUMP.MELLAN(0;100)

I mitt blogginlägg ”Slumpa med Excel” lär du dig mer om att slumpa. Till exempel hur du kan göra ett slumpmässigt urval med SLUMP. Där finns även en film att titta på som visar hur du gör.

Tips 4: Hitta det n:te största värde i en lista

Med funktionen STÖRSTA kan du hitta det n:te största värdet i en mängd data. Du kan använda den här funktionen för att markera ett värde baserat på dess relativa position. Du kan exempelvis använda STÖRSTA för att returnera det högsta, det näst högsta eller det tredje högsta resultatet.

I detta exempel ska vi söka efter det näst högsta värdet i en stor lista (A2:D1000). Vi gör detta med kalkylbladsfunkionen STÖRSTA. Funktionen tillhör kategorin Statistik

Syntaxen är: =STÖRSTA(Matris;n)

Formeln blir då:=STÖRSTA(A2:D1000;2)

  • Matris är det cellområde där du söker.
  • n är den position från det största värdet i matrisen som du vill söka efter. Anger du 2 så får du det näst högsta, anger du 3 får du det tredje högsta etc.

Vill du leta efter det näst lägsta värdet använder du funktionen MINSTA istället.

Tips 5: Skriv in samma data i flera celler samtidigt

Om du vill skriva in samma innehåll eller formler i flera celler samtidigt kan du genom ett enkelt kommando fylla alla markerade celler. Cellerna behöver inte vara sammanhängande.

Gör så här:

  1. Markera de celler som du vill skriva in data i.
  2. Skriv in det önskade innehållet i den aktiva cellen.
  3. Bekräfta med Ctrl+Enter.

Alla celler som var markerade fylls nu med det innehåll som du skrev in.

Tips 6: Avrunda uppåt eller nedåt till närmaste tusental

Nedanstående formel avrundar ett tal i cell A1 uppåt eller nedåt till närmaste tusental beroende på om talets hundratal är större än eller lika med 500.

=AVRUNDA(A1;-3)

Engelsk:

=ROUND(A1,-3)

OBS! Vill du avrunda till närmaste hundratal anger du istället argumentet -2

Tips 7: Markera alla formler i ett kalkylblad

Du kan på ett väldigt enkelt sätt att markera alla formler i ett kalkylblad. Det är praktiskt om du vill visa alla formler med fet stil eller en viss färg. Detta gör det tydligare att dela upp bladet i områden som man skall skriva in värden i och områden där dessa beräknas (god kalkylbladsprincip).

  1. Fliken Start, grupp Redigering, Sök och markera, välj Gå till special.
  2. Markera alternativet Formler.
  3. Klicka på OK
  4. Välj sedan de format du önskar, t.ex. fyllningsfärg (se gruppen Tecken i startfliken)

Tips 8: Snabbkommando för Gå till special

(se tidigare tips om Gå till special)

  1. Tryck F5
  2. Klicka på Special


Tips 9: Skydda dina formler

Det är viktigt att skydda formler i ett kalkylblad från att raderas av misstag. Vissa tycker att detta är komplicerat, men det finns en logik även i detta. Samtliga celler är som standard låsta, men låset aktiveras först när kalkylbladet skyddas. Följ dessa instruktioner för att skydda dina formler:

  1. Markera de celler som ska kunna redigeras, d.v.s de celler som INTE ska vara låsta.
  2. Ta fram dialogrutan formatera celler (kortkommando Ctrl+1)
  3. Välj bladfliken Skydd och ta bort bocken i rutan Låst.
  4. Klicka på OK. Du har nu ändrat egenskapen Låst till falskt för de markerade cellerna.
  5. Gå till fliken Granska och klicka på Skydda blad (gruppen ändringar).
  6. Ange ett lösenord och bekräfta detta om du vill ha ett lösenord. Detta är dock valfritt. Tänk på att lösenordet inte är så svårt att lirka upp… (se tips 10)

Tips 10: Låsa upp ett lösenordsskyddat kalkylblad eller arbetsbok

Skydda blad och arbetsböcker i Excel för att undvika att man av misstag skriver över eller förändrar en arbetsbok. Utgå från att de som verkligen vill låsa upp ett skyddat blad kan göra det.

Har du låst ett blad i en arbetsbok glömt det så finns det gott om VBA-kod som kan lirka fram lösenordet åt dig. Principen med dessa program är rätt enkelt. En snurra testar alla tänkbara teckenkombinationer tills man hittar rätt, och det brukar gå rätt snabbt. Oavsett vilket lösenord du skrivit så översätter nämligen Excel denna till en teckenserie med 12 tecken, där de 11 första bara är A eller B och det sista kan ha 95 olika tecken, dvs 2^11 * 96 = 194650. Att testa 194650 teckenkombinationer går rätt snabbt för en bra dator.

Här har du en textfil som innehåller VBA-kod som låser upp skyddet på alla skyddade arbetsblad och även arbetsbokens skydd: remove_password.txt

Klistra in VBA-koden i en modul i en arbetsbok. Gå till din låsta arbetsbok och starta VBA-koden därifrån.

Här har du en film på YouTube som visar hur du gör. OBS! Använd bara detta på arbetsböcker som du äger. Svenskspråkig film, Engelskspråkig film

Andersexcel tar inget ansvar för kodens funktion och jag kan inte svara på några frågor om den (se hänvisningen i koden).

Tips 11: Ett makro som lägger sökväg i sidfoten

Om du lägger sökvägen till ett Exceldokument i sidfoten på dina utskrifter så kan du enkelt se var det dokument du skrivit ut kan hittas igen bland alla dina mappar i datorn (t.ex. C:\Users\Documents\Excel\Sidfotexempel.xlsx). Så här gör du ett makro som gör att du kan lägga in (och ta bort) sökvägen i sidfoten med en knapptryckning. Genom att spara makrot i arbetsboken Egna.xlsb (eller Personal.xlsb) så kommer du nå makrot varje gång du öppnar Excel.

Tryck Alt+F11 för att öppna en VBEditorn

Markera Egna.xls i projektfönstret och öppna ett modulfönster (dubbelklicka på Module, alt välj Infoga, Modul om du vill lägga koden i en ny modul)

Skriv in följande procedur i en modul för att infoga sökvägen på det aktiva bladet:

Sub VisaSökväg ()

ActiveSheet.PageSetup.RightFooter=ActiveWorkbook.FullName

End Sub

Om du vill ha sökvägen på samtliga blad i arbetsboken ser proceduren ut så här:

Sub VisaSökvägAllaBlad()

Dim intBlad as Integer

For intBlad = 1 to Sheets.Count

Sheets(intBlad).PageSetup.RightFooter=ActiveWorkbook.FullName

Next intBlad

End Sub

Koppla sedan makrot till en knapp (se annat tips)

OBS! Om inte Egna.xls finns i projektfönstret måste den skapas först (se annat tips)

 

Tips 12: Ta bort text i sidhuvuden och sidfötter i ett kalkylblad

  1. Klicka på Sidhuvud och sidfot i gruppen Text på fliken Infoga.
  2. Kalkylbladet visas i vyn Sidlayout.
  3. Välj Gå till sidhuvud eller Gå till Sidfot i gruppen Navigering i fliken Design (Verktyg för sidhuvud och sidfot).
  4. Markera sidfoten/sidhuvudet du vill ta bort, klicka på Delete eller Backsteg.
  5. Du växlar till vyn Normal genom att klicka på Visa -> Arbetsboksvyer -> Normal.

 

Tips 13: Ta bort text i sidhuvuden och sidfötter alla kalkylblad i hela samtidigt

  1. Högerklicka på en bladflik och klicka sedan på Markera alla blad på snabbmenyn.
  2. Klicka på dialogruteikonen i gruppen Utskriftsformat (gruppens nedre högra hörn) och ta fram Utskriftsformat.
  3. Välj fliken Sidhuvud/Sidfot, klicka på Anpassa sidfot (eller sidhuvud).
  4. Markera den text du vill ta bort den text du vill.
  5. Klicka på OK.
  6. Gå till fliken Visa och välj Normal (i gruppen Arbetsboksvyer) för att visa arbetsboken i normal vy.
  7. Avmarkera ”alla-blad-markeringen” genom att klicka på en bladflik (en annan än den som du klickade på när du markerade alla blad).

Utskriftsformat kan du också ta fram genom Arkiv -> Skriv ut, och klicka på Utskriftsformat.

Tips 14: Skapa arbetsboken Egna

Arbetsboken Egna.xlsb (eller Personal) är en dold arbetsbok som man bland annat kan lagra VBA-kod i som man alltid vill kunna komma åt, t.ex. i form av att makro som skall kunna köras varje gång du öppnar Excel. Arbetsboken Egna kan tas fram och döljas från Fliken Visa -> Ta fram resp. Dölj. Om du inte har någon Egna.xlsb kan du skapa den så här:

  1. Välj Utvecklare -> Spela in makro (om du inte har fliken Utvecklare se Tips: Ta fram fliken utvecklare).
  2. I rutan Lagra makrot i, Välj Arbetsboken Egna makronKlicka på OK
  3. Klicka på knappen Stoppa inspelning.
  4. Nu finns arbetsboken Egna.

Tips 15: Ta fram fliken utvecklare

Fliken Utvecklare syns inte om standard i Excel.

Så här tar du fram den i Excel 2010

  1. Välj fliken Arkiv, Alternativ
  2. Välj Anpassa menyfliksområdet.
  3. Markera Utvecklare i listan över primära flikar

Tips 16: Skapa ett eget talformat som visar m²

  1. Gå till Infoga Symbol och leta upp tecknet för upphöjt i 2 (Unicode (hex) kod 00B2) och infoga den i en cell.
  2. Kopiera tecknet i cellen.
  3. Öppna format celler (Ctrl 1) och skapa ett eget anpassat talformat med koden, t.ex. 0” m²”.
    Har gjort en film om detta: http://www.youtube.com/watch?v=xNP4PGijfps

Tips 17: Räkna antal tecken i en cell utan mellanslag

Med funktionen LÄNGD kan du räkna hur många tecken som finns i en cell inklusive mellanslag.

Exempel: Om cell A1 innehåller texten ”qwert qwert qwert” så kommer =LÄNGD(A1) ge svaret 17.

Men om du vill räkna hur många tecken som finns i cellen exklusive mellanslag kan du komplettera funktionen LÄNGD med funktionen BYT.UT. Syntaxen för funktionen är:

BYT.UT(text; gammal_text; ny_text; [antal_förekomster])

  • Text. Detta är den text (eller en referens till en cell med den text) som du vill byta tecken i.
  • Gammal_text . Detta är den text som du vill byta ut.
  • Ny_text. Detta är den text som du vill ersätta gammal_text med.
  • Antal_förekomster. (Valfritt heltal) anger vilken förekomst av gammal_text som du vill ersätta med ny_text. Om du anger antal_förekomster kommer endast den angivna förekomsten av gammal_text att bytas ut. Annars ändras alla förekomster av gammal_text i text till ny_text.

Nu ska vi alltså räkna ut hur många tecken det finns i en cell utan mellanslag så vi använder funktionen BYT.UT för att ersätta alla mellanslag med en tom sträng och låter funktionen LÄNGD beräkna resultatet.

=BYT.UT(A1;” ”;””) kommer i vårt exempel ge resultatet ”qwertqwertqwert”
Och =LÄNGD(BYT.UT(A1;” ”;””)) kommer ge värdet 15, dvs antal tecken i cell A1 utan mellanslag.

Tips 18: Formatera celler så tusental visas utan nollor

Anta att du har en tabell där cellerna innehåller värden med många siffror. Om du exempelvis har värdet 15000 i en cell vill du visa den som 15 istället för att spara kolumnbredd. Nedanstående exempel döljer de tre sista siffrorna och avrundar talet.

  1. Markera cellen eller cellerna som ska formateras
  2. Klicka på menyn Format – Celler
  3. Välj kategori Anpassat
  4. I rutan, typ skriv 0 samt ett mellanslag

Vill du istället att talet ska visas med en decimal, ex: 15100 ska bli 15,1 använder du följande format: ,0

Tips 19: Konvertera plusvärden till minusvärden och vice versa

Om du har en kolumn med bara minusvärden och vill att dessa ska visas som absoluta värden istället kan du göra på följande sätt:

  1. Skriv in värdet -1 i en cell
  2. Kopiera cellen
  3. Markera hela kolumnen med de tal som ska konverteras
  4. Välj fliken Start, Klistra in – Klistra in special
  5. Välj Åtgärd Multiplicera
  6. Klicka på OK

<
Tips 20: Markera alla formler i ett kalkylblad

När man skapar en kalkyl kan det vara smart att göra det tydligt vilka delar i kalkylen som består av formler och vilka delar som är värden. Om du på ett snabbt och enkelt sätt vill markera alla formler i ett kalkylblad kan du göra på följande sätt:

  1. Välj Start -> Sök och markera (grupp Redigering) -> Gå till special
  2. Välj alternativet Formler
  3. Klicka OK

Notera att i kryssrutorna under Formler kan du ange vilken typ av formel som du vill markera, tal, text, logiska eller felvärden.

Tips 21: Dölj felmeddelanden med OMFEL

Har du sett följande felmeddelande i en cell någon gång? #Division/0!
Det har du säkert. Det finns ett enkelt sätt att dölja dessa felmeddelanden. Tänk dig att du vill dividera värdet i cell A1 med värdet i cell A2 och visa resultatet i cell A3. Så länge cell A2 är tom eller innehåller ett nollvärde visas felmeddelandet #Division/0!. Det finns ett enkelt sätt att lösa detta. Sedan Excel 2007 finns funktionen OMFEL, med den kan du täcka upp alla tänkbara fel.

Med funktionen =OMFEL(A1/A2;””) så kommer alla felmeddelanden ersättas med ””, dvs en blank cell.

OMFEL har två argument värde och värde_om_fel, där värde är den beräkning du vill göra och värde_om_fel är vad som skall hända om värde ger ett felmeddelande så som #SAKNAS!, #VÄRDEFEL!, #REFERENS!, #DIVISION/0!, #OGILTIGT!, #NAMN? och #SKÄRNING!.

Tips 22: Koppla ett makro till en knapp i verktygsfältet snabbåtkomst

Om du gjort ett makro kan det vara smidigt att koppla det till en knapp. Enklast är att koppla makrot till en kapp i verktygsfältet snabbåtkomst (raden med knappar som ligger högst upp till vänster i programfönstret). Gör bara så här:

  1. Klicka på pilen Anpassa verktygsfältet snabbåtkomst.
  2. Välj Fler kommandon.
  3. Välj kommandon från Makron.
  4. I listan över makron, välj det du vill koppla till en knapp och klicka på Lägg till.

Vill du ändra namn eller knappfigur kan du klicka på Ändra, annars OK. Färdigt!

Tips 23: Dölj Nollvärden i enstaka celler

Som du kanske vet kan du dölja alla Nollvärden i ett kalkylblad genom att välja Arkiv -> Alternativ -> Avancerat och avmarkera rutan ”Visa en nolla i celler som har ett nollvärde (under gruppen Visa alternativ för det här kalkylbladet).
Men om du i vissa celler vill visa noll och i andra vill du dölja dessa kan du formatera cellen med
följande format: 0;-0;;@
Gör så här:

  1. Markera cellen eller cellerna som ska formateras
  2. Högerklicka och välj Formatera celler (eller kortkommando Ctrl + 1)
  3. Välj kategori Anpassat
  4. I rutan Typ, skriv in talformatet 0;-0;;@
  5. Klicka OK

Den generella strukturen för talformat är: Plusvärden;Minusvärden;Nollvärden;Text, vilket således innebär för vårt exempel att:

  • Första nollan anger att plusvärden ska visas som heltal
  • Andra nollan (-0) anger att minusvärden ska visas med minustecken och i heltal.
  • Tredje värdet är nollvärden och där utelämnar vi argumentet (vilket innebär att nollvärden döljs).
  • Sista argumentet är text och där har vi en textplatshållare.

 

Tips 24: Dagens datum

Funktionen =IDAG() ger dagens datum [=TODAY() ].

Kortkommandot Ctrl+Shift+; (tryck ner tangenten Ctrl och tangenten Shift och tangenten för semikolon) ger också dagens datum i aktiv cell.

Skillnaden är att funktionen =IDAG alltid ger dagens datum, medan kortkommandot ger det datum som är aktuellt när man skrev det (i morgon kommer det att visa gårdagens datum).

Tips 25: Räkna ut svenskt veckonummer med VECKONR

Att räkna ut veckonummer är lättare sagt än gjort. Det finns nämligen många olika sätt att räkna på. Det finns visserligen en europeisk standard (ISO8601) men alla följer ju inte den, så man bör vara lite försiktig använda veckonummer i internationella sammanhang. Vad som krånglar till det är främst när vecka nummer 1 skall börja, men även hur man hanterar sista veckan (finns det en vecka 53, eller kanske en vecka 54?). Som ett exempel kan man ta måndag den 31 december 2012 som är vecka 1 i Sverige, vecka 53 i USA och vecka 54 i en del andra länder. Krångligheten syns även i Excels funktion VECKONR som har tio olika sätt att räkna ut veckonummer på (s.k. returtyper).

VECKONR har följande syntax: (Tal;Returtyp). Där Tal är det datum (tidsserienummer) som man vill veta veckonummer på och Returtyp är vilken veckonummertyp som skall gälla. I Sverige (tillsammans med större delen av Europa och de länder som ansluter sig till ISO 8601) använder typ 21.

Vill du veta dagens datums veckonummer kan du lägga in funktionen IDAG() som Tal, mao:

=VECKONR(IDAG();21) ger svenskt veckonummer.

Notera att Excels hjälpfunktion inte är så behjälplig i detta fall, innehåller både fel och otydligheter.

Jag reder ut detta mer i mitt blogginlägg: http://andersexcel.se/visa-svenska-veckonummer-med-veckonr/

Tips 26: Om du vill summera data från flera kalkylblad

Tänk dig att du samlar varje månadsrapport i ett arbetsblad och att du i slutet av året vill summera alla månader till en årsrapport. Det är i sådana situationer som man kan hamna i en situation när man vill summera samma cell i många blad. Säg till exempel att du vill summera cell A1 från Blad1 med A1 från Blad2 med cell A1 från Blad3 osv… Så här gör du för att smidigast skapa en formel som borrar sig genom hela din arbetsbok:

  1. Markera den cell där du vill att resultatet skall hamna
  2. Klicka på Summa-knappen
  3. Aktivera det första bladet i den grupp som ska summeras
  4. Markera den första cellreferens som ska summeras
  5. Håll ned Skift-tangenten och klicka på det sista bladet i gruppen
  6. Bekräfta med Enter-tangenten.


Tips 27: Räkna fram nRoten

Kvadratroten ur ett tal är det tal som gånger sig självt blir ett visst tal.

Ex. Roten ur 16 = 4, Roten ur 64 = 8

nRoten ur ett tal är ett okänt tal som upphöjt till ett känt tal blir ett känt tal.

Ex. Vad är det som upphöjt till 6 blir 64

  • Formeln för denna beräkning är: =64^(1/6)
  • Formeln i Excel skulle bli =B2^(1/C2),
  • Där X upphöjt i C2 blir lika med B2

Tips 28: Korrigera felaktiga datumformat

Det är vanligt att man ibland hämtar in kolumner där datumen visas som heltal, ex: 20010501. Detta går inte att korrigera med en vanlig datumformatering. För att korrigera, gör så här:

  1. Markera hela kolumnen med felaktiga datum. OBS! Endast en kolumn i taget.
  2. Klicka på fliken Data – Text till kolumner (i gruppen Dataverktyg)
  3. Klicka på knappen Nästa två gånger utan att ändra någonting.
  4. Klicka på alternativknappen Datum och välj önskat datumformat.
  5. Klicka på slutför

Tips 29: Formel som räknar ut kön baserat på personnummer

I ett svenskt personnummer visar den näst sista siffran på vilket kön personen har. Om siffran är udda är det en man och om siffran är jämn är det en kvinna.

Nedanstående formel räknar fram könet baserat på den näst sista siffran i personnumret om personnumret står i cell A1.

=OM(ÄRJÄMN(VÄNSTER(HÖGER(A1;2)));”Kvinna”;”Man”)

Engelsk: =IF(ISEVEN(LEFT(RIGHT(A1;2)));”Kvinna”;”Man”)

Genom att använda kombinationen VÄNSTER  och HÖGER spelar det inte någon roll med hur många tecken personnumret är skrivet (med eller utan bindestreck eller med ÅÅ eller ÅÅÅÅ), så länge som teckensträngen slutar med de två sista siffrorna i kontrollnumret. Om du vet exakt hur många tecken personnumret är skrivet med kan du istället använda EXTEXT. I följande exempel antas att personnumret är tio tecken, och kön bestäms då av tecken nummer nio:

=OM(ÄRJÄMN(EXTEXT(A1;9;1));”Kvinna”;”Man”)

Engelsk: =IF(ISEVEN(MID(A1;9;1));”Kvinna”;”Man”)

Tips 30: Villkorsstyrt format på varannan rad

Om du infogar eller tar bort rader från en lista som du formaterat med olika format varannan rad så kan formatet förstöras. Detta kan du komma runt genom att skapa ett villkorsstyrt format som automatiskt formaterar varannan rad olika. Så här gör du för att lägga till ett villkorsstyrt format som ger olika format på varannan rad:

  1. Markera området som du vill formatera.
  2. Välj Startfliken -> Villkorsstyrd formatering -> Ny regel.
  3. om ”Ny formateringsregel” välj ”Bestäm vilka celler som skall formateras genom att använda en formel”.
  4. I rutan för ”Formatera värden där den här formeln är sann” skriv in formeln =REST(RAD();2)=0
  5. Klicka på knappen Format och ställ in det format (tal, tecken, kantlinje, fyllning) du vill ha på alla rader med jämnt radnummer.
  6. Klicka på OK.

 

Nu kommer alla rader på jämna radnummer vara formaterade och udda radnummer oformaterade (vita). Vill du ha ett annat format på udda radnummer kan du göra detta på två sätt:

  • Antingen: Formatera hela området på vanligt sätt (Ctrl+1) innan du skapar det villkorsstyrda formatet. Det villkorsstyrda formatet på de jämna raderna kommer sedan täcka det vanliga formatet som därför enbart kommer synas på de udda raderna.
  • Eller: Välj Villkorsstyrd formatering -> Ny regel, välj ”Bestäm vilka celler som skall formateras genom att använda en formel”. Lägg in formeln =REST(RAD();2)=1 och ställ in önskat format på udda rader.


Tips 31: Räkna med sekunder

Om du vill göra om sekunder till tt:mm:ss-format så behöver du förstå grundprincipen med att räkna med tid. Se mitt blogginlägg om detta.

  1. Om du summar celler som innehåller sekunder i form av heltal kan du konvertera dem till formatet tt:mm:ss på följande sätt:
  2. Dela summan med 86400 (antal sekunder på ett dygn), tex =A1/86400
  3. Välj Formatera celler (kortkommando Ctrl+1) och Tal kategori Anpassat skriv in det anpassade talformatet [t]:mm:ss

Det går ju 3600 sekunder på en timme och 5400 sekunder på en och en halv timme. Om du formaterar resultatet av =5400/86400 med [t]:mm:ss kommer du få resultatet 1:30:00.

Notera att detta inte är det samma som decimaltalet 1,3 utan tidsseriekoden för en minut och trettio sekunder. D.v.s. om du multiplicerar detta med två blir resultatet 3:00:00 (inte 2,6 som det skulle bli om det var ett decimaltal).

Tips 32: Skapa en egen Excel mall

För att skapa en egen Excel-mall börjar du med att göra den mall du vill ha i en vanlig arbetsbok.

Välj därefter Spara som och spara den i filformatet Excel-mallar.

Färdigt! Nu finns mallen bland ”Mina mallar”. Se även blogg inlägg om detta

Tips 33: Räkna ut ålder från personnummer i Excel

För att räkna ut antal år mellan två datum kan man använda funktionen DATEDIF. Den är en lite speciell funktion som inte finns officiellt i Excel, men ändå går att använda. Jag beskriver detta i ett blogginlägg.
Hur man räknar ut ålder från ett personnummer påverkas av hur personnumret är skrivet i Excel. Man kan tänka sig flera varianter.

  1. som ett datum t.ex. 1995-01-05 (utan kontrollnummer)
  2. en tiosiffrig teckensträng ÅÅMMDDXXXX
  3. en tolvsiffrig teckensträng ÅÅÅÅMMDDXXXX

Dessutom kan man tänka sig varianter med ett streck mellan datumserien och kontrollsiffrorna. Detta spelar dock ingen roll för detta exempel.

Om personnumret ligger i cell A1 så ger följande funktioner med DATDIF antal fyllda år. För de tre olika varianterna:

  1. =DATEDIF(A1;IDAG();”y”)
  2. =DATEDIF(DATUM(VÄNSTER(A1;2);EXTEXT(A1;3;2);EXTEXT(A1;5;2));IDAG();”y”)
  3. =DATEDIF(DATUM(VÄNSTER(A1;4);EXTEXT(A1;5;2);EXTEXT(A1;7;2));IDAG();”y”)

och motsvarande i engelskt Excel:

  1. =DATEDIF(A1;TODAY();”y”)
  2. =DATEDIF(DATE(LEFT(A1;2);MID(A1;3;2);MID(A1;5;2));TODAY();”y”)
  3. =DATEDIF(DATE(LEFT(A1;4);MID(A1;5;2);MID(A1;7;2));TODAY();”y”)

Se även mitt blogginlägg där jag beskriver DATEDIF mer utförligt. En begränsning med exempel 2 är att beräkningen antar födelseår på 1900-talet (110822 = 101 år gammal).

 

Tips 34: Beräkna skillnaden mellan två tider som kan eller inte kan gå över midnatt

Om du vill räkna ut antal timmar mellan två tider som går över midnatt (t.ex. från 22:00 till 02:00) fungerar inte en enkel formel som sluttid minus starttid. Om Din starttid är i cell A1 och sluttid är i cell B1 så kommer följande formel ge rätt avstånd oavsett om tiden går över midnatt eller inte:
=B1-A1+(B1<A1)

 

Tips 35: Byta namn på ett kalkylblad

1. Dubbelklicka på blandfliken för kalkylbladet du vill byta namn på.
2. Skriv ett nytt namn och tryck enter.

 

Tips 36: Kopiera ett kalkylblad till en annan arbetsbok

1. Högerklicka på bladfliken
2. Välj Flytta eller kopiera
3. Välj vilken arbetsbok du vill flytta bladet till i rutan Till Bok, bocka för Skapa en kopia och klicka på OK.

Tips 37: Beräkna nuvärdet av ett belopp som utbetalas någon gång i framtiden

Med funktionen NUVÄRDE kan du räkna ut vad ett belopp i framtiden är värt idag. NUVÄRDE har syntaxen NUVÄRDE(ränta; periodantal; betalning; [slutvärde]; [typ]).

Om det är ett enstaka belopp du skall nuvärdeberäkna anger du det för argumentet Slutvärde och lämnar argumentet Betalning blank. Argumentet Typ kan du också hoppa över (antar att betalningen sker i slutet av perioden).

Om du t.ex. vill räkna ut vad 1000 kronor om fem år med en kalkylränta på 5 procent är värt idag kan du skriva =NUVÄRDE(10%;5;;-1000) och få svaret 620,92 kr. Notera minustecknet före slutvärdet (funktionen antar betalningar är utbetalningar vilket jag korrigerar med ett minustecken).

 

Tips 38: Skapa en talserie som räknas upp automatiskt

Om du vill ha en talserie som räknar upp med 2 heltal per steg, tex 2, 4, 6, 8 osv till ett tal som du önskar så kan du göra så här:
Skriv in 2 i en cell och 4 i nästa cell. Markera bägge cellerna och kopiera markeringen genom att dra i kopieringshantaget (den svarta fyrkanten i cellens nedre högre hörn). Cellerna du kopierar kommer då automatisk att räknas upp med den naturliga följden av de två talen. Detta fungerar oavsett vilka två tal du skrivit in. 1 och 3 ger t.ex. serien 5, 7, 9, 11, 13, 15 osv. Se mitt blogginlägg där jag visar fler smarta sätt att skapa talserier.

Tips 39: Kopiera relativa referenser som absoluta

Vill du kopiera relativa referenser utan att de förändras relativt kan du göra följande:

  1. Markera området du skall kopiera. Sök och ersätt alla lika med tecken med ett specialtecken (förslagsvis paragraf) (t.ex. så att =$N4*O$3 blir §$N4*O$3).
  2. Kopiera området och klistra in det där du vill ha det.
  3. Gör sedan en omvänd sök och ersätt, dvs sök efter paragraf och ersätt med lika med tecken.

Se mitt blogginlägg som mer detaljerat beskriver hur du kan överlista de relativa referenserna vid kopiering.

Tips 40: Korrigera negativa tal med efterföljande minus

Vissa system genererar minustecknet efter talen för negativa tal, dvs -9 visas som 9-. Detta gillar inte Excel som kommer att läsa talet som ej beräkningsbar text. Det snabbate sättet att fixa detta är följande: Markera dina celler som innehåller dessa värden. I datafliken väljer du sedan ”Text till kolumner” i gruppen dataverktyg. Klicka på slutför. Klart!

Förklaring: I steg 3 i Guiden Omvandla text till kolumner finns en knapp för avancerade inställningar. Där finns bland annat ett val att korrigera ”Efterföljande minus för negativa tal”. Denna är markerad som default. Därför räcker det med att bara kicka ”Slutför” i steg 1.

Jag har även en film som visar detta: Korrigera negativa tal med efterföljande minus

Tips 41: Räkna med exponent (upphöjd med) i Excel

Vill du räkna vad ett värde blir om det blir upphöjt med något, så använder du ett fristående cirkumflex (”tak”-tecken mellan bas och exponent, t.ex. 10^5. Du kan naturligtvis även skapa formler där värdet i en cell är upphöjt med värdet i en annan cell, t.ex. =A1^A2
För att skriva in ett fristående cirkumflex brukar det vara enklast att trycka ett blanksteg efter att du klickat på tangenten för cirkulflex annars kommer det skrivas som som en accent över nästa bokstav som skriv in.
Förutom ^ så brukar den tangenten även ha tecknen ~ och ¨. På engelska heter cirkumflex caret men kan även kallas för hat, control eller uparrow. Själv brukar jag alltid kalla det för tak-tecken.

Tips 42: Räkna endast synliga rader (eller celler) i ett Excelblad

Om du har tabell där du har dolt vissa rader (antingen med ett filter eller manuellt dolda rader) och vill räkna antal synliga rader kan du använda funktionen =DELSUMMA(102;A5:A22). Här räknas antal celler i området A4:A22 som innehåller tal (vilket blir detsamma som antalet rader). Vill du istället räkna antal celler som inte är tomma (innehåller både text och tal) ändrar du funktionsnumret i funktionen till 103, dvs =DELSUMMA(103;A5:A22).

Mer tips kommer om ni vill….

Har du något förslag på ett tips som skulle passa på denna sida, skriv en kommentar nedan.
En del av tipsen i denna lista har sitt ursprung i Kent Schederin’s tips från hans site kentexcel.com. Kent var en stor Excelguru som tyvärr loggade ut allt för tidigt. Tack Kent för ditt bidrag. Tror du skulle upskattat att dina tips lever vidare.
Bookmark and Share