Skapa en Excel-instrumentpanel (förklaras med exempel och mallar)

En Excel-instrumentpanel kan vara ett fantastiskt verktyg när det gäller att spåra KPI, jämföra datapunkter, och få datastödda vyer som kan hjälpa ledningen att fatta beslut.

I den här självstudien lär du dig hur du skapar en Excel-instrumentpanel, de bästa metoderna att följa när du skapar en, funktioner och verktyg du kan använda i Excel , saker att undvika till varje pris och rekommenderat utbildningsmaterial.

Vad är en Excel-instrumentpanel och hur skiljer den sig från en rapport?

Låt oss först förstå vad som är en Excel-instrumentpanel.

En Excel-instrumentpanel är en personsökning (mestadels men inte alltid nödvändig) som hjälper chefer och företagsledare att spåra viktiga KPI: er eller mätvärden och ta ett beslut baserat på det. Den innehåller diagram / tabeller / vyer som stöds av data.

En instrumentpanel kallas ofta en rapport, men inte alla rapporter är instrumentpaneler.

Här är skillnaden:

• En rapport skulle bara samla in och visa data på en enda plats. Till exempel, om en chef vill veta hur försäljningen har ökat under den senaste perioden och vilken region som var mest lönsam, skulle en rapport inte kunna svara på den. Det skulle helt enkelt rapportera all relevant försäljningsinformation. Dessa rapporter används sedan för att skapa instrumentpaneler (i Excel eller PowerPoint) som hjälper till att fatta beslut.
• En instrumentpanel å andra sidan skulle omedelbart svara på viktiga frågor som vilka regioner som presterar bättre och vilka produkter bör ledningen fokusera på. Dessa instrumentpaneler kan vara statiska eller interaktiva (där användaren kan göra val och ändra vyer och data uppdateras dynamiskt).

Nu när vi har förståelse för vad en instrumentpanel är, låt oss dyka in och lära dig hur du skapar en instrumentpanel i Excel.

Hur man skapar en Excel-instrumentpanel?

Att skapa en Excel-instrumentpanel är en process i flera steg och det finns några viktiga saker du måste tänka på när du skapar den.

Redan innan du startar Excel måste du vara tydligt om instrumentpanelens mål.

Om du till exempel skapar en KPI-instrumentpanel för att spåra finansiella KPI: er för ett företag, skulle ditt mål vara att visa jämförelsen mellan den aktuella perioden och den senaste perioden (s).

På samma sätt, om du skapar en instrumentpanel för personalavdelningen för att spåra anställdas utbildning, skulle målet vara att visa hur många anställda som har utbildats och hur många som behöver utbildas för att nå målet.

Saker att göra innan du till och med börjar skapa en Excel-instrumentpanel

Många människor börjar arbeta på instrumentpanelen så snart de får tag på uppgifterna.

Och i de flesta fall tar de med sig elände att omarbeta på instrumentpanelen eftersom klientens / intressentmålen inte uppnås.

Nedan följer några av de du måste ha svarat innan du börjar bygga en Excel-instrumentpanel:

F: Vad är syftet med instrumentpanelen?

Det första du ska göra så snart du får data ( eller till och med innan du får data), är att få klarhet i vad din intressent vill ha. Var tydlig i vilket syfte instrumentpanelen behöver fungera.

Är det att spåra KPI bara en gång eller regelbundet? Behöver det spåra KPI: er för hela företaget eller divisionsmässigt ?. Att ställa rätt frågor skulle hjälpa dig att förstå vilka data du behöver och hur du utformar instrumentpanelen.

F: Vilka är datakällorna?

Vet alltid var data kommer ifrån och i vilket format. I ett av mina projekt tillhandahölls uppgifterna som PDF-filer på spanska. Detta ändrade helt omfånget och det mesta av vår tid sugs upp genom att manuellt avlägsna data. Här är de frågor du bör ställa: Vem äger uppgifterna? I vilket format får du informationen? Hur ofta uppdateras datan?

F: Vem kommer att använda den här Excel-instrumentpanelen?

En chef skulle förmodligen bara vara intresserad av insikterna som instrumentpanelen ger, men vissa dataanalytiker i hans team kan behöva en mer detaljerad vy. Baserat på vem som använder din instrumentpanel måste du strukturera data och den slutliga utdata.

F: Hur ofta behöver Excel Dashboard uppdateras?

Om dina instrumentpaneler ska uppdateras varje vecka eller varje månad, är det bättre att du skapar en plug-and-play-modell (där du bara kopierar och klistrar in data och den uppdateras automatiskt). Om det bara är en engångsövning kan du utesluta lite automatisering och göra det manuellt.

F: Vilken version av Office använder klienten / intressenten?

Det är bättre att anta inte att klienten / intressenten har den senaste versionen av MS Office. Jag skapade en gång en instrumentpanel för att veta att min intressent använde Excel 2003.Detta ledde till en del omarbetningar eftersom IFERROR-funktionen inte fungerar i Excel 2003-versionen (som jag använt mycket när jag skapade instrumentpanelen).

Få data i Excel

När du ha en bra uppfattning om vad du behöver skapa, nästa steg är att få tag på data och få den i Excel.

Ditt liv är enkelt när din klient ger dig data i Excel, dock om så inte är fallet måste du räkna ut ett effektivt sätt att få det i Excel.

Om du levereras med CSV-filer eller textfiler kan du enkelt konvertera dessa i Excel. Om du har tillgång till en databas som lagrar data kan du skapa en anslutning och uppdatera den indirekt.

När du har fått data måste du rengöra den och standardisera den.

Du kan till exempel behöva bli av med ledande, efterföljande eller dubbla mellanslag, hitta och ta bort dubbletter, ta bort blanksteg och fel och så vidare.

I vissa fall kan du till och med behöva omstrukturera data (till exempel säg att du måste skapa en pivottabell).

Dessa steg beror på projektet och hur dina data ser ut i Excel.

Skisserar strukturen på instrumentpanelen

När du har data i Excel vet du exakt vad du kan och inte kan använda i din Excel-instrumentpanel.

I detta skede är det en bra idé att ringa tillbaka med din intressenter med en översikt över Excel-instrumentpanelen.

Som en bästa praxis skapar jag en enkel disposition i PowerPoint tillsammans med ytterligare anteckningar. Syftet med detta steg är att se till att din intressent förstår vilken typ av instrumentpanel han / hon kan förvänta sig med tillgängliga data.

Det hjälper också eftersom intressenten kan föreslå ändringar som skulle ge honom mer värde.

Här är ett exempel på ett exempel på en översikt som jag skapade för en av KPI-instrumentpanelerna:

När konturen är klar är det dags att börja skapa Excel-instrumentpanelen.

Som en bästa praxis delar du upp din Excel-arbetsbok i tre delar (dessa är kalkylblad som jag skapar med samma namn):

• Data – Detta kan vara ett eller flera kalkylblad som innehåller rådata.
• Beräkningar – Detta är där du gör alla beräkningar. Återigen kan du ha ett eller flera ark för beräkningar.
• Instrumentpanel – Detta är arket som har instrumentpanelen. I de flesta fall är det en enda sidvy som visar analys / insikter som stöds av data.

Excel-tabell – Den hemliga såsen av en effektiv Excel-instrumentpanel

Det första jag gör med rådata är att konvertera den till en Excel-tabell.

Excel-tabellen erbjuder många fördelar som är avgörande när du skapar en Excel-instrumentpanel.

För att konvertera tabelldata till en Excel-tabell, välj data och gå till fliken Infoga och klicka på tabellikonen.

Här är fördelarna med att använda en Excel-tabell för din instrumentpanel:

• När du konverterar en tabelluppsättning till en Excel-tabell , du behöver inte oroa dig för att data ändras i ett senare skede. Även om du får ytterligare data kan du helt enkelt lägga till den i tabellen utan att oroa dig för att formlerna blir skruvade. Det här är verkligen användbart när jag skapar plug-and-play-instrumentpaneler.
• Med en Excel-tabell kan du använda namnen på kolumnerna istället för referensen. Istället är till exempel C2: C1000, du kan använda Försäljning.

Viktiga Excel-funktioner för instrumentpaneler

Du kan skapa många bra interaktiva Excel-instrumentpaneler genom att bara med hjälp av Excel-formler.

När du gör ett val från en listruta eller använder ett rullningsfält eller markerar en kryssruta finns det formler som uppdateras baserat på resultaten och ger dig de uppdaterade uppgifterna / Visa i instrumentpanelen.

Här är mina fem bästa Excel-funktioner för Excel-instrumentpaneler:

• SUMPRODUCT-funktion: Det är min favoritfunktion när jag skapar en interaktiv Excel-instrumentpanel. Det gör att jag kan göra komplexa beräkningar när det finns många variabler. Antag till exempel att jag har en försäljningsdashboard och jag vill veta vad försäljningen gjordes av repen Bob under tredje kvartalet i östra regionen. Jag kan helt enkelt skapa en SUMPRODUCT-formel för detta.
• INDEX / MATCH-funktion: Jag är en stor förespråkare för att använda kombinationen av INDEX och MATCH-formel för att leta upp data i Excel Dashboards. Du kan också använda VLOOKUP-funktionen, men jag tycker att INDEX / MATCH är ett bättre val.
• FELFUNKTION: När du gör beräkningar på rådata får du ofta fel. Jag använder IFERROR i stor utsträckning för att dölja fel i instrumentpanelen (och många gånger också i rådata).
• TEXT-funktion: Om du vill skapa dynamiska rubriker eller titlar måste du använda TEXT-funktionen för det.
• RADER / KOLONN Funktion: Jag använder dessa ofta när jag måste kopiera en formel och ett av argumenten måste ökas när vi går ner / till höger om cellen.

Saker att tänka på när du arbetar med formler i en Excel-instrumentpanel:

• Undvik att använda flyktiga formler (som OFFSET, NU, I DAG och så på..). Dessa kommer att sakta ner din arbetsbok.
• Ta bort onödiga formler. Om du har några ytterligare formler i beräkningsarket tar du bort dessa medan du slutar instrumentpanelen.
• Använd hjälpkolumner eftersom det kan hjälpa dig att undvika långa formelformler.

Interaktiv Verktyg för att göra din Excel-instrumentpanel fantastisk

Det finns många interaktiva verktyg som du kan använda för att göra din Excel-instrumentpanel dynamisk och användarvänlig.

Här är några av dessa jag använder regelbundet:

• Bläddringsfält: Använd rullningslister för att spara din arbetsbok fastigheter. Till exempel, om du har 100 rader med data kan du använda en rullningslist med endast 10 rader i instrumentpanelen. En användare kan använda rullningsfältet om han / hon vill titta på hela datamängden.
• Kryssrutor: Med kryssrutorna kan du göra val och uppdatera instrumentpanelen. Antag till exempel att jag har en träningsinstrumentpanel och att jag är företagets VD, jag skulle vilja titta på företagets övergripande instrumentpanel. Men om jag är försäljningschef skulle jag bara vilja titta på min avdelnings resultat. I ett sådant fall kan du skapa en instrumentpanel med kryssrutor för olika divisioner i företaget.
• Listruta: En rullgardinslista tillåter en användare att göra ett exklusivt val. Du kan använda dessa listrutor för att uppdatera instrumentpanelen. Om du till exempel visar data efter avdelning kan du ha avdelningsnamnen i rullgardinsmenyn.

Använda Excel-diagram för att visualisera data i en Excel-instrumentpanel

Diagram gör inte bara din Excel-instrumentpanel visuellt tilltalande utan gör det också lätt att konsumera och tolka.

Här är några tips när du använder diagram i en Excel-instrumentpanel:

• Välj rätt diagram: Excel ger dig många kartläggningsalternativ och du måste använda rätt diagram. Om du till exempel måste visa en trend måste du använda ett linjediagram, men om du vill markera de faktiska värdena kan ett stapel- / kolumndiagram vara rätt val. Medan många experter avråder från att använda ett cirkeldiagram, föreslår jag att du använder ditt gottfinnande. Om din målgrupp är van att se cirkeldiagram kan du lika gärna använda dessa.
• Använd kombinationsdiagram: Jag rekommenderar starkt att du använder kombinationsdiagram eftersom de gör det möjligt för användaren att jämföra värden och rita betydelseinsikter. Du kan till exempel visa försäljningssiffran som ett kolumndiagram och tillväxten som ett linjediagram.

• Använd dynamiska diagram: Om du vill tillåta användaren att göra val och vill att diagrammet ska uppdateras med det, använd dynamiska diagram. Nu är ett dynamiskt diagram inget annat än ett vanligt diagram vars data uppdateras i backend när du gör val.

• Använd Sparklines för att göra dina uppgifter mer meningsfulla: Om du har mycket data i instrumentpanelen / rapporten kan du överväga att använda Sparklines för att göra den visuell. En sparkline är ett litet diagram som finns i en cell och kan skapas med hjälp av en datamängd. Dessa är användbara när du vill visa en trend över tiden och samtidigt spara utrymme på din instrumentpanel. Du kan lära dig mer om Excel Sparklines här.
• Använd kontrasterande färger för att markera data: Detta är ett generellt diagramtips där du bör markera data i ett diagram så att det är lätt att förstå. Om du till exempel har försäljningsdata kan du markera året med lägsta försäljningsvärde i rött.

Du kan bläddra bland några av mina kartläggningshandledningar här.

Om du vill bli mer avancerad i Excel-kartläggning rekommenderar jag att du besöker bloggen av Excel-kartexpert Jon Peltier.

Excel Dashboards Dos and Donts

Låt oss först börja med Donts!

Här är några av de saker jag rekommenderar att du undviker när du skapar en Excel-instrumentpanel. Återigen skulle dessa variera beroende på ditt projekt och intressent men är giltiga i de flesta fall.

• Stör inte dina instrumentpaneler: Bara för att du har data och diagram betyder det inte att det ska gå i din instrumentpanel. Kom ihåg att instrumentpanelens mål är att hjälpa till att identifiera ett problem eller hjälpa till att fatta beslut. Så håll det relevant och ta bort allt som inte hör hemma där. Jag frågar ofta mig själv om något bara är bra att ha till absolut måste-ha. Sedan fortsätter jag och tar bort alla bra-till-haves.
• Använd inte flyktiga formler: eftersom det kommer att sakta ner beräkningarna.
• Håll inte extra data i din arbetsbok: Om du behöver den informationen skapar du en kopia av instrumentpanelen och behåller den som säkerhetskopia.

Låt oss nu titta på några Dos (eller bästa metoder)

• Numrering av dina diagram / avsnitt: Din instrumentpanel är inte bara en slumpmässig uppsättning diagram och datapunkter. Istället är det en berättelse där en sak leder till den andra.Du måste se till att din publik följer stegen i rätt ordning och därför är det bäst att numrera dessa. Även om du kanske kan vägleda dem när du presenterar live, är det till stor hjälp när någon använder din instrumentpanel i ett senare skede eller tar en utskrift ur den.
• Begränsa rörelsen i instrumentpanelområdet: Dölj alla rader / kolumner för att se till att användaren inte råkar rulla bort.
• Frysa Viktiga rader / kolumner: Använd frysrutor när du vill att några rader / kolumner alltid ska vara synliga på instrumentpanelen.
• Gör former / diagram Stick: Se till att dina former / diagram eller interaktiva kontroller inte döljer eller ändrar storlek när någon gömmer / ändrar storlek på cellerna. Du kan också använda Excel Camera-verktyget för att ta en ögonblicksbild av diagram / tabeller och placera den i Excel-instrumentpanelen (dessa bilder är dynamiska och uppdateras när backend-diagrammet / tabellen uppdateras).
• Ge en Användarhandbok: Om du har en komplex instrumentpanel är det en bra idé att skapa ett separat kalkylblad och markera stegen. Det hjälper människor att använda din instrumentpanel även när du inte är där.
• Spara utrymme med kombinationsdiagram: Använd kombinationsdiagram (som punktdiagram, termometerdiagram och faktiska kontra måldiagram) för att spara ditt kalkylblad mellanslag.
• Använd symboler & Villkorlig formatering: Använd symboler (t.ex. upp / ner-pilar eller bock / korsmärke) och villkorlig formatering för att lägga till en analyslager till din instrumentpanel (men överdriv inte heller).

Vill du skapa professionella instrumentpaneler i Excel? Kolla in Online Excel Dashboard-kursen där jag visar allt om hur du skapar en Excel-instrumentpanel i världsklass.

Exempel på instrumentpanelen

Här är några coola Excel-instrumentpanel som du kan ladda ner och spela med.

Excel KPI Dashboard

Du kan använda denna instrumentpanel för att spåra KPI: er från olika företag och använd sedan punktkartor för att fördjupa dig i det enskilda företagets resultat.

Klicka här för att ladda ner den här KPI-instrumentpanelen.

Om du är intresserad av att lära dig hur du skapar denna KPI-instrumentpanel – klicka här.

Instrumentpanel för callcenterprestanda

Du kan använda den här instrumentpanelen för att spåra viktiga KPI: er för en callcenter. Från denna instrumentpanel kan du lära dig hur du skapar kombinationsdiagram, hur du markerar specifika datapunkter i diagram, hur man sorterar med hjälp av alternativknappar osv.

Klicka här för att ladda ner instrumentpanelen.

Du kan läsa mer om denna instrumentpanel här.

EPL Season visualized in a Excel Dashboard

I den här instrumentpanelen lär du dig hur du använder VBA i Excel-instrumentpaneler. I denna instrumentpanel uppdateras detaljerna i spelen när du dubbelklickar på cellerna till vänster. Den använder också VBA för att visa en hjälpmeny som hjälper användaren att använda den här instrumentpanelen.

Klicka här för att ladda ner EPL-instrumentpanelen.

Du kan läsa mer om denna instrumentpanel här.

Rekommenderad Dashboard-utbildning

Om du vill lära dig att skapa professionella instrumentpaneler i världsklass i Excel, kolla in min GRATIS online-dashboardkurs.