Skapa KPI:er den enkla vägen…

Det är inne med dashboards just nu. Vill du också skapa snygga överblickbara rapporter så kanske du har noterat att det dykt upp en väldig massa funktionalitet för detta i Excel på senare tid. En av funktionerna som gjordes avsevärt mer bekväm i 2007 är Villkorsstyrd formatering som du kan använda till mycket.

I detta exempel visar jag hur du kan göra KPI symboler med hjälp av Villkorsstyrd formatering. Själva övningen går egentligen ut på att du skall kunna dölja bakomliggande formler och värden för att göra den visuella bilden enklare och snyggare.

Vi skall alltså åstadkomma följande utseende:

Snygga stoppljus

Iden är så här: Vi har en kolumn som visar försäljning och en annan som visar månadens mål. För att enklare se om vi ligger i nivå med målen så vill vi skapa de små cirklarna till höger om kolumnen mål som visar status.

Gäsp, tänker du. Det där var väl enklast i stan. (Det är det också men när vi är klara kommer du att ha snygga interaktiva kalkyler som andra avundas dig.)

För att räkna ut ett gränsvärde så har jag delat försäljningen med målet. Det ger mig 1 om dom överensstämmer eller tal under 1 om jag inte ligger i fas.

Detta är uträknat i kolumnen som heter status och ser ut så här (och har procentformaterat värdena):

Snygga stoppljus

Inte riktigt lika kul som den första bilden. Därmed lägger vi på Villkorsstyrd formatering:

  1. Markera alla värden i kolumnen status
  2. Välj Start, Villkorsstyrd formatering
  3. Klicka på Ny regel och välj Ikonuppsättning vid Ikonformat.
    villkorsstyrd 2
  4. Ange dina intervall vid Värde och se till att prickarna är i rätt ordning (det finns en knapp för omvänd ordning).
  5. Väljer du OK nu så visas dina ikoner men även talen:
    KPI2
  6. Vill du ha lite mindre siffror på bladet och låta stoppljusen tala sitt eget språk så väljer du nu knappen Villkorsstyrd formatering igen. Väljer Hantera regler och klickar på den regel du har skapat. Välj Redigera och sätt en bock i alternativet Visa endast ikon. Välj därefter OK.
    Villkorsstyrd

När du är tillbaka i dokumentet visas nu endast dina ikoner och ger en klar bild över nuläget!

Excel 2013 videoutbildning via Office Store

Nu finns ett antal gratis videoklipp för hur du kommer igång med Excel 2013 tillgängliga från Microsoft:
http://office.microsoft.com/en-us/store/excel-video-tutorials-WA104114678.aspx

sortera

Du kan använda dessa om du har Office 2013 (eller Office 365 ProPlus) och de ger tillgång till filmer direkt via knappen Program för Office i Excel.

video tutorials

Samtliga filmer är på engelska och gratis men skulle du vilja utveckla något liknande på svenska i din organisation så hjälper vi på Learningpoint gärna till.

Dolda men användbara funktioner i Excel 2013 – Inquire

Excel 2013 kommer med rejäla och otroligt snygga uppdateringar. En av dessa är tillägget Inquire som adderar en hel del efterfrågad funktionalitet.

Tillägget Inquire

Tillägget Inquire

Jag kommer att lägga upp ett litet clip om Inquire men i stora drag vad du hittar på detta är:

Workbook Analysis – denna funktion går igenom din arbetsbok och visar statistik. Du kan se om du har dolda flikar, trasiga formler, cirkelreferenser och annat. Helt enkelt en hälsocheck på din Excelfil.

Hela området Diagram (eller Workbook, Worksheet och Cell Relationships) skapar kartor över hur länkar i cellerna är kopplade till andra platser. Väljer du Workbook relationship så skapas en karta över alla filer som länkas ihop från den filen du befinner dig i. Det är otroligt praktiskt om du vill hitta beroende filer som använder information i den fil du befinner dig.

Compare files är en liknande funktion som Jämför Dokument i Word. Denna låter dig jämföra två liknande filer med varandra och ger dig en lista över all platser där innehållet skiljer sig åt mfellan filerna.

Har du tillgång till Excel 2013 så missa inte Inquire. Du får fram tillägget enligt följande:

  1. Välj Arkiv, Alternativ och Tillägg
  2. Välj Com TIllägg vid hantera och tryck Kör
  3. Markera Inquire i listan (jag har några ytterligare som kanske inte finns i din Excelversion här men Inquire skall finnas med i denna lista).
  4. Välj OK. Nu skall Inquire finnas med bland dina flikar.

Lägga till Inquire

 

Skapa en projektplan/kalender i Excel

Egentligen så är ju både Project, Visio och SharePoint bättre på att skapa aktiviteter och Ganttscheman men det vi skall titta på nu är en av de vanligaste önskemålen jag får oavsett om jag håller kurs i Excel, Word eller PowerPoint.

Med nedanstående steg skall vi skapa en Excelbaserad kalender. Iden är att få fram ett
schema så snabbt och enkelt som möjligt om de vanliga Officeprogrammen är de du har att tillgå. När vi är klara skall vi ha skapat det som bilden visar och helst bara på ett par minuter:

Det här är vad vi behöver göra:

  1. Skapa datumområdet med veckonummer, rätt formaterade datum och borttagna lördagar och söndagar.
  2. Ange lite aktiviteter
  3. Låsa rubrikerna så att det går att bläddra åt alla håll
  4. Ställa in utskriften så att kalendern kan skrivas ut om så önskas

Skapa datum som ser ut som du vill ha dem

  1. För att åstadkomma exemplet ovan så ställer du dig i B2 och skriver in ett datum som är måndagen i den veckan som din kalender skall börja med. Det är viktigt att det är just en måndag så kolla i kalendern och skriv t ex in 3 jun och tryck Enter.
  2. Nu omvandlar Excel detta till 03-jun vilket visar att Excel förstår att det är ett datum du håller på med.
  3. Markera cellen med datumet och tryck CTRL+1 för att komma till Formatera Celler. Här visas ditt datum vid alternativet Exempel och en kod som visar hur Excel skriver detta datum. Det är just denna kod som vi skall fixa till så att den gör som vi vill. Istället för DD-MMM so står i rutan nu så tar du bort bindestrecket och skriver DD MMM och väljer OK. Nu visas istället 03 jun. På raden nedanför skall vi visa samma datum men som en dag istället = måndag.
Datum i Excel formateras med kod enligt följande:
D = 3
DD = 03
DDD = mån
DDDD = måndag 

M = 6
MM = 06
MMM = jun
MMMM = juni

Å = 13
ÅÅ = 13
ÅÅÅ = 2013

Detta innebär att om du vill visa:
3 jun så skriver du D MMM
130603 så skriver du ÅÅMMDD
måndag den 03 juni så skriver du DDDD "den" DD MMMM

Visa veckodagen i B3

  1. För att spara plats så lägger vi måndagen under datumet så att den som fyller i kalendern skall ha en bekväm och stilig kalender att fylla i. För att få rätt dag här så ställer du dig nu i B3 och skriver ett likhetstecken. Klicka sedan på B2 och välj Enter så gör vi en länk till detta innehåll. Nu har du troligtvis samma datum i båda rutorna.
  2. Markera B3 och välj CTRL-1 ytterligare en gång och ange DDD för att visa mån istället i denna ruta. Välj OK.

Skapa fler datum

  1. För att skapa fler datum så markerar du nu B2 och B3 och pekar sedan på den lilla svarta fyrkanten i markeringens nedre högra hörn. Dra med en jämn och bestämd rörelse åt höger för att fylla ut datum så långt bort du önskar. Det är viktigt att du inte släpper upp och sedan drar vidare. Tappar du markören så börja om och dra om alla datumen en gång till. 
  2. När du tror att du dragit ut lite mer än vad du behöver så släpper du upp musknappen och nu har du skapat en kalender.

Ta bort lördagar och söndagar ur din datumserie

  1. För att inte se lördagar och söndagar i din datumserie så pekar du på den lilla smarta etikett som en kort stund visas till höger om det sista datumet. Öppna denna knapp och välj Fyll veckodagar. Nu tas lördagar och söndagar bort i datumserien.
  2. Radera överflödiga datum och sluta med en fredag.

Skapa veckor

  1. För att skapa veckorna så skall vi också använda autofyll och det här är egentligen lite fusk men fungerar finfint förutom när året är slut då man kan behöva göra följande en extra gång.
  2. Markera nu B1 till F1 vilket borde vara rutorna ovanför den första förekomsten av en måndag till fredag. Oftast är det alltså 5 eller 7 rutor som skall markeras beroende på om du har med lördagar och söndagar eller inte.
  3. Sammanfoga dessa celler till en genom att trycka på knappen Centrera över kolumner i fliken Start. Skriv in Vecka 23 (eller det nummer som är lämpligt i din kalender) och tryck på Enter. Hoppa tillbaka till denna cell och dra nu i autofyllpricken (längst ner till höger i cellen) åt höger för att skapa fler veckor. Dra längs med hela din kalender och nu skapas Vecka 24, 25, 26 o s v.

Minska ner kolumnbredden för att få överblick

  1. Markera nu rad 2 och/eller 3 och dra ner teckenstorleken så mycket som du tycker är läsbart men ändå litet.
  2. Tryck CTRL-A en eller två gånger för att markera hela bladet.
  3. Peka på ett av strecken där du ändrar kolumnbredd (mellan A och B t ex) och dubbelklicka. Nu väljer du bästa val varpå kolumnerna minskas till minsta bredd med synligt innehåll.
  4. Bredda nu kolumn A och ange några aktiviteter eller skriv in Aktivitet 1 i A2 och använd autofyll för att få Aktivitet 1, 2, 3 o s v.

Lås rubrikerna på skärmen så att du kan bläddra obehindrat nedåt och åt höger

  1. Ställ markören i den första cellen som inte är en rubrik – i vårt exempel så är det B4.
  2. Gå till fliken Visa och välj knappen Lås fönsterrutor och därefter alternativet Lås fönsterrutor.
  3. Nu låser du rad 1,2,3 och kolumn A så att dessa ligger still när du bläddrar.

För att få med rubrikerna på alla sidor om du vill göra en utskriftsvänlig version så behöver du också göra s k utskriftsrubriker. Detta gör du på följande sätt:

  1. Klicka på fliken Sidlayout och knappen Utskriftsrubriker
  2. Placera markören vid Rader överst och dra därefter utanför dialogrutan direkt på det bakomliggande Excelbladet över de rader som är rubrikrader. Nu visas en myrgång över dessa och området skrivs in i rutan vid Rader Överst. I vårt exempel borde det stå $1:$3. Klicka sedan vid Kolumner till vänster och markera de kolumner som är rubriker. I vårt fall visas $A:$A för att indikera att kolumn A skall repeteras på varje sia.
  3. Kanske vill du också passa på och klicka på knappen Orientering för att välja liggande utskrift.

Det var det. Lycka till

Spela in eller skapa makron i Excel (eller Word)

Om du ofta utför en åtgärd så kan du automatisera den (eller de) genom att skapa makron. Ett makro är en sparad serie kommandon och/eller instruktioner som du kan aktivera genom t ex en knapptryckning eller ett kortkommando.

Makron är användbara t ex när du vill:

  • Jobba snabbare med rutinmässiga saker som formatering
  • Kombinera flera åtgärder i ett ”klick”
  • Välja ett val i en dialogruta lite snabbare än att leta upp den och välja ett kommando.

För att skapa ett makro så kan du starta makroinspelaren i Office och spela in det du gör och sedan återaktivera det du spelade in när du behöver det.

Det som spelas in när du använder makroinspelaren visas upp som en programkod i Excels VBA fönster och där kan det redigeras om du tar några steg till och lär dig programmera i Visual Basic for Applications.

Hitta funktionerna för makron genom att aktivera fliken Utvecklare

  1. Högerklicka på någon av knapparna i menyfliksområdet
  2. Välj Anpassa Menyfliken
  3. Bocka för Utvecklare till höger vid Primära flikar

Spela in ett makro

  1. Klicka på fliken Utvecklare
  2. Välj Spela in makro
  3. Ange ett namn för makrot och en bokstav eller siffra vid kortkommando om du vill kunna återanvända makrot med ett kortkommando.
  4. Välj var makrot skall sparas.
    Väljer du Arbetsboken Egna Makron så skapas en dold Excelfil som öppnas varje gång du öppnar Excel vilken innehåller alla dina makron. Genom att välja detta val så har du tillgång till det du vill att makrot skall göra i alla arbetsböcker. Väljer du den öppna arbetsboken så fungerar makrot enbart när du använder just detta dokument.
  5. Utför det som du vill att makrot skall göra (tänk på att makrot även lagrar förflyttning och markering så vill du kunna använda ditt makro till olika områden så markerar du innan du startar inspelaren).
  6. Välj Stoppa inspelning.

Använda ett färdigt makro

Har du sparat ditt makro så kan du se det (och övriga sparade) om du väljer knappen Makron i fliken Utvecklare. Här visas dina makron och här kan du även peka på ett makro och välja Kör för att använda det färdiga makrot.

Du kan också prova att använda det kortkommando du eventuellt lagrat.

Koppla ett makro till en knapp

Om du vill ha en knapp att trycka på för att använda ditt makro så kan du enkelt lösa detta genom att skapa en anpassad knapp i snabbåtkomstfältet.

  1. Högerklicka på snabbåtkomstfältet (den lilla verktygsrad som brukar innehålla spara och ångra och ligger över (eller precis under) menyfliksområdet i Office.
  2. Välj Anpassa Verktygsfältet Snabbåtkomst.
  3. Vid Välj kommandon från öppnar du listan och väljer Makron.
  4. Nu visas de makron som är lagrade.
  5. Klicka på det makro som skall kopplas till en knapp och välj Lägg till.
  6. Klicka på Ändra för att skapa en anpassad ikon till knappen och ändra eventuellt namnet så att visningstexten när du pekar på knappen visas snyggare.
  7.  Välj OK när du är klar.

Nu visas en knapp i snabbåtkomstfältet via vilken du kan använda ditt makro.

Använda färdig makrokod

Om du hittar ett makro du vill prova (t ex här nedan) så kan du kopiera och klistra in makron till VBA editorn (programfönstret för Visual Basic).

Detta gör du genom att välja knappen Makro på fliken Utvecklare, ange ett namn och en plats för makrot enligt ovan men sedan trycka på Skapa. Då flyttas du direkt in i VBA Editorn. I det högra fönstret kan du klistra in ditt makro för att skapa ett makro. Ett makro kännetecknas av att det börjar med texten Sub och slutar med End Sub. Mellan dessa rader står själva instruktionen som makrot utför. Det som står överst intill Sub blir också det namn som makrot kopplas till så namnet du skrev först bör överensstämma med detta.

Sub Makronamn() 
XXX
XXX
XXX
End Sub

Stäng Visual Basic Editorn med krysset när du är klar. Dina makron hanteras nu enligt ovan och du får frågan om du vill spara ändringarna som vanligt när du stänger Excel. Har du lagrat till Personliga makron så får du frågan efter att du sparat samtliga öppna arbetsböcker.

Nästa steg vad gäller makron är VBA programmering. Mer om detta hittar du hos min kollega som skriver något om detta på Officedev.se.

Lycka till.

Hoppande Excelkommentarer

Har fått en fråga kring varför Excelkommentarer ibland flyttar sig ”magiskt” och att de ibland ändrar storlek och inte alltid visar hela kommentarens innehåll.

För att lösa detta kan ett par makron behövas så prova gärna makrona nedan om du vill få möjlighet att kontrollera redan inlagda kommentarer:

Det första makrot – Kommentarposition använder du för att justera kommentarens position om den flyttat sig över arket. Den hamnar då i närheten av den cell den tillhör.

Sub Kommentarposition()
Dim cmt As Comment
For Each cmt In ActiveSheet.Comments
   cmt.Shape.Top = cmt.Parent.Top + 5
   cmt.Shape.Left = _
      cmt.Parent.Offset(0, 1).Left + 5
Next
End Sub

Detta makro använder du om du behöver justera dina kommentarer för att se den text som är inskriven i dem.

Sub Kommentarstorlek()
  Dim MyComments As Comment
  Dim lArea As Long
  For Each MyComments In ActiveSheet.Comments
    With MyComments
      .Shape.TextFrame.AutoSize = True
      If .Shape.Width > 300 Then
        lArea = .Shape.Width * .Shape.Height
        .Shape.Width = 200
        .Shape.Height = (lArea / 200) * 1.1
      End If
    End With
  Next
End Sub

Vill du veta hur du lägger in och använder ett makro i Excel? Läs nästa inlägg så kommer en kort beskrivning där.

Liggande staplar i Excel i omvänd ordning

En fråga som dyker upp med jämna mellanrum är att liggande staplar visar staplarna i omvänd ordning mot hur en korrelerande tabell ser ut. I nedanstående bild – som är vad Excel föreslår så vill man kanske visa det bra alternativet högst upp (som tabellen visar) snarare än att göra ”omvänd ordning” på diagrammet.

I axelinställningarna finns ett val som visar diagrammet i omvänd ordning men då flyttas även själva axeln till andra sidan. Följ instruktionerna nedan för att visa diagrammet i omvänd ordning utan att behövas skriva om din tabell:

  1. När du skapat ditt diagram så högerklickar du på axeln och väljer Formatera Axel.
  2. Markera kategorier i omvänd ordning och ange att Vågrät axel korsar vid maxkategori (se bild)
  3. Välj OK och ditt diagram visas så här:

 

 

Smidigare flikhantering i Excel

Arbetar du en del i Excel så lär du ha dribblat en del med flikarna i arbetsboken. Här kommer några tips för enklare flikhantering:

Kopiera flikar

Du kan kopiera flikar  på olika sätt men håller du ner CTRl när du drar i en flik så skapas en kopia som du kan placera någon annanstans i arbetsboken.

Flytta flik

Drar du en flik åt vänster eller höger så flyttas fliken till lämplig plats

Flytta eller Kopiera flikar till annan arbetsbok

Du kan kopiera en flik till en annan arbetsbok om du högerklickar på den och väljer Flytta eller kopiera. Här kan du flytta/kopiera fliken till andra öppna arbetsböcker så innan du gör detta så öppnar du även den plats som fliken skall till.

Du kan också lägga dina arbetsböcker intill varandra och hålla ner ALT tangent och dra fliken in till ett annat dokument. Håller du ner både ALT och CTRl så skapas en kopia.

Bläddra enklare bland många flikar

Högerklickar du på de små pilspetsarna som visas till vänster om flikarna i arbetsboken så får du upp en lista med de 16 första flikarna. Har du ännu fler så väljer du Fler blad… för att hitta övriga.

Bläddra med kortkommando

Vill du hoppa mellan flikar med kortkommandon så använder du CTRL+Page up eller CTRL+Page down

Använda gruppläge i Excel

Om du behöver göra en ändring till flera flikar samtidigt eller kanske ändra utskriftsinställningar till flera flikar på en gång så kan du aktivera gruppläget. Detta innebär att du markerar flera flikar och därefter gör ändringen i en flik. När du befinner dig i gruppläge så sker ändringen i samtliga flikar direkt. För att släppa gruppläget så klickar du på en av de flikar som inte ingår i markeringen.

Markera flikarna genom att välja CTRl och klicka på de flikar som skall ändras. Så snart flikarna är markerade så visas grupp uppe på titelraden i Excel. Vill du markera en stor mängd flikar så kan du markera ett intervall genom att klicka på den första. Hålla ner Shift och sedan klicka på sen sista fliken. Nu blir samtliga flikar som ingår i intervallet inkluderade i gruppläget.

Transponera – en användbar doldis i Excel

Har du någon gång upptäckt att du skrivit in värden i fel ledd i Excel? Om du upptäcker att du borde lagt rubrikerna i omvänd ordning så behöver du inte skriva om en hel tabell utan kan använda den verkligt användbara funktionen Transponera.

I tabellen ovan så visas effekten av en transponering. Du kan helt enkelt vända på rubrikerna och värden i en inskriven lista så att x-rubriker hamnar i y-led och tvärtom.

Transponera data enligt följande:

  1. Markera den tabell du vill vända på – ta med både rubriker och innehåll i markeringen.
  2.  Välj Kopiera.
    Obs! Det är viktigt att du tar just Kopiera, funktionen fungerar inte med Klipp ut. Vill du inte ha kvar det gamla datat så raderar du det när du är klar med transponeringen.
  3. Placera markören där du vill ha dina data (översta vänstra hörnet av den tänkta nya tabellen) och klicka på listpilen vid knappen Klistra in. Nu visas ett antal alternativ. Här väljer du alternativet som heter Transponera (visas i bilden nedan).
  4. Du kan även välja transponera via Klistra in special eller via den lilla etiketten som visas i nedre hörnet av något inklistrat när du klistrat in som vanligt.
  5. Nu visas din information ”omvänd” och du kan därmed radera den första tabellen om du vill.

Kopiera rader och kolumner utan att få med dig dolda celler

Många gånger när du kopierar ett filtrerat område och klistrar in det på ett annat ställe så förstår Excel att du enbart vill ha med dig de rader som syns. Använder du däremot funktionen Dölj för att dölja rader och kolumner och därefter vill kopiera ett
synligt område till ett annat blad så tar Excel ibland med sig lite för mycket och får med sig även de rader som är dolda.

Den tekniska anledningen till detta är att funktionen Dölj egentligen inte döljer raderna rent faktiskt utan sätter kolumnbredd eller radhöjd till 0. Detta gör att de fortfarande på sätt och vis ingår i området men har en väldigt liten plats.

För att undvika att kopiera med dolda rader och kolumner så finns en jättebra funktion i Excel som heter Markera synliga celler vilken gör att allt dolt avmarkeras innan du kopierar eller klipper ut..

Markera synliga celler

Det enklaste sättet att använda denna funktion är att plocka fram den till Snabbåtkomstfältet längst upp. Detta gör du så här:

  1. Högerklicka på Snabbåtkomstfältet
  2. Välj Anpassa Verktygsfältet Snabbåtkomst
  3. Vid alternativet Välj kommandon från väljer du Alla kommandon
  4. Bläddra dig ner till ett kommando som heter Markera synliga celler och välj knappen Lägg tillför att lägga till kommandot i listan till höger och visa det på snabbåtkomstfältet.Markera synliga celler
  5. Välj OK för att avsluta
  6. Nu visas funktionen Markera synliga celler i Snabbåtkomstfältet
För att använda knappen så gör du så här:
  1. Markera ett område som innehåller dolda rader eller kolumner
  2. Klicka på knappen Markera synliga celler i Snabbåtkomstfältet – nu avmarkeras alla dolda rader eller kolumner.
  3. Kopiera och klistra in som vanligt.