Abstrakt: Det här är den första självstudiekursen i en serie som utformats för att lära dig bekanta dig med excel och dess inbyggda datakombinations- och analysfunktioner. I de här självstudiekurserna får du bygga och förfina en Excel-arbetsbok från grunden, skapa en datamodell och sedan skapa otroliga, interaktiva rapporter med Power View. Självstudiekurserna är utformade för att visa Microsoft Business Intelligence-funktioner i Excel, pivottabeller, Power Pivot och Power View.
I de här självstudiekurserna lär du dig hur du importerar och utforskar data i Excel, bygger och förfinar en datamodell med Power Pivot och skapar interaktiva rapporter med Power View som du kan publicera, skydda och dela.
Följande är självstudiekurserna i den här serien:
- Importera data till Excel 2016 och skapa en datamodell
- Utöka datamodellrelationer med Excel, Power Pivot och DAX
- Skapa kartbaserade Power View-rapporter
- Införliva Internet-data och ange standardinställningar för Power View-rapporter
- Hjälp för Power Pivot
- Skapa fantastiska Power View-rapporter - del 2
I den här självstudiekursen börjar du med en tom arbetsbok i Excel.
Följande är avsnitten i den här självstudiekursen:
- Importera data från en databas
- Importera data från ett kalkylblad
- Importera data genom att kopiera och klistra in
- Skapa en relation mellan importerade data
- Kontrollpunkt och test
I slutet av självstudiekursen finns ett test du kan ta för att testa vad du har lärt dig.
I den här serien används data som beskriver olympiska medaljer, värdländer och olika olympiska sporthändelser. Vi föreslår att du går igenom kurserna i tur och ordning.
Importera data från en databas
Vi börjar den här självstudiekursen med en tom arbetsbok. Målet i det här avsnittet är att ansluta till en extern datakälla och importera dess data till Excel för ytterligare analys.
Vi börjar med att hämta data från Internet. Dessa data består av information om olympiska medaljer och är en Microsoft Access-databas.
Klicka på följande länkar för att hämta filer som vi ska använda i den här självstudiekursen. Ladda ned var och en av de fyra filerna till en plats som är lättillgänglig, till exempel Hämtade filer eller Mina dokument, eller till en ny mapp som du skapar:
> OlympicMedals.accdb Access-databas
> OlympicSports.xlsx Excel-arbetsbok
> Population.xlsx Excel-arbetsbok
> DiscImage_table.xlsx Excel-arbetsbokÖppna en tom arbetsbok i Excel.
Klicka på Data > Hämta data > från databas > från Microsoft Access-databas. Menyfliksområdet justeras dynamiskt baserat på bredden på arbetsboken, så kommandona i menyfliksområdet kan se lite annorlunda ut jämfört med följande skärm.
Markera filen OlympicMedals.accdb som du laddade ned och klicka på Importera. Följande navigatörsfönster visas med tabellerna i databasen. Tabeller i en databas liknar kalkylblad eller tabeller i Excel. Markera kryssrutan Markera flera tabeller och markera alla tabeller. Klicka sedan på Läs in > till.
Fönstret Importera data visas.
Obs
Observera kryssrutan längst ned i fönstret som gör att du kan lägga till dessa data i datamodellen, som visas på följande skärm. En datamodell skapas automatiskt när du importerar eller arbetar med två eller flera tabeller samtidigt. En datamodell integrerar tabellerna, vilket möjliggör omfattande analyser med pivottabeller, Power Pivot och Power View. När du importerar tabeller från en databas används de befintliga databasrelationerna mellan tabellerna för att skapa datamodellen i Excel. Datamodellen är transparent i Excel, men du kan visa och ändra den direkt med powerpivot-tillägget. Datamodellen diskuteras mer detaljerat senare i den här självstudiekursen.
Välj alternativet Pivottabellrapport , som importerar tabellerna till Excel och förbereder en pivottabell för analys av de importerade tabellerna, och klickar på OK.
När alla data har importerats skapas en pivottabell med hjälp av de importerade tabellerna.
Med de data som importerats till Excel och datamodellen som skapas automatiskt är du redo att utforska alla data.
Utforska data med hjälp av en pivottabell
Det är lätt att utforska importerade data med hjälp av en pivottabell. I en pivottabell drar du bara fält (ungefär som kolumnerna i Excel) från tabeller (t.ex. de tabeller som du precis importerat från Access-databasen) till olika områden i pivottabellen för att anpassa hur dina data ska presenteras. En pivottabell består av fyra områden: FILTER, KOLUMNER, RADER och VÄRDEN.
Det kan ta lite att experimentera för att avgöra vilket område ett fält ska dras till. Du kan dra så många eller få fält från tabellerna som du vill tills pivottabellen visar dina data som du vill. Utforska gärna genom att dra fält till olika områden i pivottabellen. underliggande data påverkas inte när du ordnar fält i en pivottabell.
Låt oss utforska data om olympiska medaljer i pivottabellen, och vi börjar med att sortera medaljerna efter gren, valör och utövarens hemland eller region.
I pivottabellen expanderar du tabellen Medaljer genom att klicka på pilen bredvid. Leta reda på fältet NOC_CountryRegion i den utökade tabellen Medaljer och dra det till området KOLUMNER. NOC står för National Olympic Committees som är organisationsenheten för ett land eller en region.
Från tabellen Grenar drar du sedan Gren till området RADER.
Låt oss nu filtrera Grenar så att endast fem sporter visas: bågskytte, dykning, fäktning, konståkning och skridskoåkning. Du kan göra detta i området Pivottabellfält eller från filtret Radetiketter i själva pivottabellen.
- Klicka någonstans i pivottabellen för att säkerställa att Excel-pivottabellen är markerad. I listan Pivottabellfält , där tabellen Discipliner är expanderad, hovrar du över fältet Gren och en listrutepil visas till höger om fältet. Klicka på listrutan, klicka på **(Markera alla)**om du vill ta bort alla markeringar. Rulla sedan nedåt och välj Bågskytte, Dykning, Fäktning, Konståkning och Skridskoåkning. Klicka på OK.
- Eller så kan du i avsnittet Radetiketter i pivottabellen, klicka på listrutan bredvid Radetiketter i pivottabellen, och klicka på (Markera alla) för att ta bort alla markeringar, och därefter bläddra nedåt och välja Bågskytte, Dykning, Fäktning, Konståkning och Skridskoåkning. Klicka på OK.
I Pivottabellfält, från tabellen Medaljer tabell drar du medaljen till området VÄRDEN. Eftersom värdena måste vara numeriska ändrar Excel automatiskt Medalj till Antal medaljer.
Från tabellen Medaljer markerar du Medalj igen och drar den till området FILTER.
Låt oss filtrera pivottabellen så att den endast visar de länder eller regioner som vunnit mer än 90 medaljer totalt. Gör så här:
- I pivottabellen klickar du på listrutan till höger om Kolumnetiketter.
- Välj Värdefilter och välj Större än ...
- Skriv 90 i det sista fältet (till höger). Klicka på OK.
Pivottabellen ser ut som på skärmen nedan.
Nu har du med minimal ansträngning skapat en enkel pivottabell med fält från tre olika tabeller. Anledningen till att det gick så lätt är de fördefinierade relationerna mellan tabellerna. Eftersom det förekommer tabellrelationer i källan och du importerade alla tabeller genom en enda åtgärd, kunde relationerna återskapas i datamodellen.
Men vad händer om dina data kommer från olika källor eller importeras senare? Då kan du oftast infoga nya data genom att skapa relationer som baseras på överensstämmande kolumner. I nästa steg får du importera fler tabeller och lära dig att skapa nya relationer.
Importera data från ett kalkylblad
Nu får du prova att importera data från en annan källa – från en befintlig arbetsbok, och sedan får du lära dig att ange relationerna mellan befintliga data och nya data. Med hjälp av relationer kan du analysera datasamlingar i Excel och skapa intressanta och spännande visualiseringar baserat på de data som du importerar.
Börja med att skapa ett tomt kalkylblad och importera sedan data från en Excel-arbetsbok.
Infoga ett nytt Excel-kalkylblad och ge det namnet Sport.
Bläddra till mappen som innehåller de hämtade exempeldatafilerna och öppna OlympicSports.xlsx.
Markera och kopiera alla data till Blad1. Om du markerar en cell med data, t.ex. cell A1, kan du trycka på Ctrl + A för att markera alla angränsande data. Stäng arbetsboken OlympicSports.xlsx.
På kalkylbladet Sport placerar du markören i cell A1 och klistrar in dina data.
Medan informationen fortfarande är markerad trycker du på Ctrl + T för att formatera den som en tabell. Du kan också formatera data som en tabell i menyfliksområdet genom att välja HOME > Format as Table. Eftersom data har rubriker väljer du Tabellen har rubriker i fönstret Skapa tabell, så som visas här.
Formatering av data som en tabell har många fördelar. Du kan ge tabellen ett namn som gör den lätt att identifiera. Du kan också skapa relationer mellan tabeller, vilket möjliggör utforskning och analys i pivottabeller, Power Pivot och Power View.
Ge tabellen ett namn. Leta reda på fältet Tabellnamn i EGENSKAPER FÖR TABELLDESIGN >och skriv Sport. Arbetsboken ser ut som på följande skärm.
Spara arbetsboken.
Importera data genom att kopiera och klistra in
Nu när du har importerat data från en Excel-arbetsbok fortsätter du med att importera data från en tabell på en webbsida, eller från en annan källa som du kan kopiera och klistra in i Excel. I de följande stegen ska du lägga till den olympiska värdstaden från en tabell.
- Infoga ett nytt Excel-kalkylblad och ge det namnet Värdar.
- Markera och kopiera följande tabell, inklusive tabellrubrikerna.
- Placera markören i cell A1 i kalkylbladet Värdar i Excel och klistra in informationen.
- Formatera data som en tabell. Som beskrivits tidigare i den här självstudiekursen trycker du på Ctrl + T för att formatera data som en tabell eller från HOME > Format som tabell. Eftersom data har rubriker väljer du Tabellen har rubriker i fönstret Skapa tabell som visas.
- Ge tabellen ett namn. I TABELLDESIGN-egenskaper > letar du reda på fältet Tabellnamn och skriver Värdar.
- Välj kolumnen Årgång och från fliken START formaterar du den som Tal med 0 decimaler.
- Spara arbetsboken. Arbetsboken ser ut som på skärmen nedan.
När du nu har en Excel-arbetsbok med tabeller kan du skapa relationer mellan dem. När du skapar relationer mellan tabeller kan du sammanställa data från två tabeller.
Skapa en relation mellan importerade data
Du kan börja använda fält i din pivottabell från importerade tabeller direkt. Om Excel inte kan avgöra hur ett fält ska infogas i pivottabellen, måste en relation upprättas med den befintliga datamodellen. I stegen nedan får du lära dig att skapa en relation mellan data du har importerat från olika källor.
På Blad1, högst upp iPivottabellfält, klickar du påAlla för att visa hela listan med tillgängliga tabeller, så som visas på bilden nedan.
Bläddra genom listan för att se de nya tabellerna som du precis har lagt till.
Utöka Sport och välj Sport för att lägga till den i pivottabellen. Observera att du i Excel uppmanas att skapa en relation, så som det visas på skärmen nedan.
Meddelandet visas på grund av att du har använt fält från en tabell som inte ingår i den underliggande datamodellen. Ett sätt att lägga till en tabell i datamodellen är att skapa en relation till en tabell som redan finns i datamodellen. Om du vill skapa en relation måste en av tabellerna ha en kolumn med unika, ej upprepade värden. I exempeldata innehåller tabellen Grenar som importerades från databasen ett fält med sportkoder som vi kallar SportID. Dessa sportkoder finns som ett fält i den importerade Excel-informationen. Nu ska du skapa relationen.Klicka på SKAPA ... i det markerade området Pivottabellfält så öppnas dialogrutan Skapa relation, så som det visas på skärmen nedan.
I Tabell väljer du Datamodelltabell: Discipliner i listrutan.
I Kolumn (sekundär) väljer du SportID.
I Relaterad tabell väljer du Datamodelltabell: Sport.
I Relaterad kolumn (primär) väljer du SportID.
Klicka på OK.
Pivottabellen ändras för att återspegla den nya relationen. Pivottabellen ser dock inte rätt ut än på grund av fältordningen i området RADER . Gren är en underkategori till en viss sport, men eftersom vi ordnade Gren ovanför Sport i området RADER är den inte korrekt organiserad. På följande skärm visas den här oönskade ordningen.
I området RADER flyttar du Sport så att den finns ovanför Gren. Nu ser det mycket bättre ut och pivottabellen visar informationen så som du vill ha den, och det visas på skärmen nedan.
I bakgrunden bygger Excel en datamodell som kan användas i hela arbetsboken, i valfri pivottabell, pivotdiagram, i Power Pivot eller i power view-rapporter. Tabellrelationer utgör grunden för en datamodell och är vad som avgör navigering och sökvägar.
I nästa självstudiekurs, Utöka datamodellrelationer med Excel, Power Pivot**och DAX**, bygger du på det du lärde dig här och går igenom hur du utökar datamodellen med hjälp av ett kraftfullt och visuellt Excel-tillägg som kallas Power Pivot. Du lär dig också hur du beräknar kolumner i en tabell och använder den beräknade kolumnen så att en annars orelaterad tabell kan läggas till i datamodellen.
Kontrollpunkt och test
Gå igenom det du lärt dig
Nu har du en Excel-arbetsbok som innehåller en pivottabell med dataåtkomst i flera tabeller, varav några har importerats separat. Du har fått lära dig att importera från en databas, från en annan Excel-arbetsbok, och att kopiera och klistra in informationen i Excel.
För att informationen skulle kunna samverka var du tvungen att skapa en tabellrelation som Excel använder för att referera raderna. Du fick även lära dig vikten av att ha kolumner i en tabell som refererar till data i en annan tabell för att skapa relationer, och för att leta upp relaterade rader.
Du är nu klar för nästa självstudiekurs i den här serien. Här är en genväg:
Självstudiekurs: Utöka datamodellrelationer med Excel, Power Pivot och DAX
TEST
Vill du ta reda på hur väl du kommer ihåg det du lärt dig? Nu har du chansen. Följande test tar upp de funktioner, möjligheter och krav du har läst om i den här självstudiekursen. Du hittar svaren längst ned på sidan. Lycka till!
Fråga 1: Varför är det viktigt att konvertera importerade data till tabeller?
A: Du behöver inte konvertera data till tabeller, eftersom alla importerade data automatiskt omvandlas till tabeller.
B: Om du konverterar importerade data till tabeller kommer de att uteslutas från datamodellen. Endast när de är undantagna från datamodellen är de tillgängliga i pivottabeller, Power Pivot och Power View.
C: Om du konverterar importerade data till tabeller kan de ingå i datamodellen och göras tillgängliga för pivottabeller, Power Pivot och Power View.
D: Du kan inte konvertera importerade data till tabeller.
Fråga 2: Vilka av följande datakällor kan importeras till Excel och inkluderas i datamodellen?
A: Access-databaser och många andra databaser.
B: Befintliga Excel-filer.
C: Allt som du kan kopiera och klistra in i Excel och formatera som en tabell, inklusive datatabeller på webbplatser, dokument eller något annat som kan klistras in i Excel.
D: Allt ovanstående.
Fråga 3: Vad händer i en pivottabell när du ändrar ordning för fälten i de fyra områdena i Pivottabellfält?
A: Inget. Du kan inte ändra ordningen för fält efter att du har placerat dem i områdena i Pivortabellfält.
B: Pivottabellformatet ändras så att det speglar layouten, men underliggande data påverkas inte.
C: Pivottabellformatet ändras så att det speglar layouten, och alla underliggande data ändras permanent.
D: Underliggande data ändras, vilket resulterar i nya datauppsättningar.
Fråga 4: Vad krävs när en relation ska skapas mellan tabeller?
A: Ingen av tabellerna kan ha en kolumn med unika, ej upprepade värden.
B: En av tabellerna får inte ingå i Excel-arbetsboken.
C: Kolumnerna får inte konverteras till tabeller.
D: Inget av ovanstående är rätt.
Testsvar
- Rätt svar: C
- Rätt svar: D
- Rätt svar: B
- Rätt svar: D
Obs
Data och bilder i den här självstudiekursserien bygger på följande:
- Olympics-datauppsättningen från Guardian News & Media Ltd.
- Flaggbilder från CIA Factbook (cia.gov)
- Befolkningsdata från Världsbanken (worldbank.org)
- Piktogram över olympiska grenar av Thadius856 och Parutakupiu