Opplæring: Importere data til Excel og opprette en datamodell

Delene i denne opplæringen er som følger:

• Importere data fra en database

• Importere data fra et regneark

• Importere data ved hjelp av Kopier og Lim inn

• Opprette en relasjon mellom importerte data

• Kontrollpunkt og test

På slutten av denne opplæringen kan du ta en test for å kontrollere hva du har lært.

Denne opplæringsserien bruker data som beskriver olympiske medaljer, vertsland og ulike OL-øvelser. Vi foreslår at du går gjennom hver opplæring i rekkefølge. I opplæringene brukes Excel 2013 med Power Pivot aktivert. Hvis du vil ha mer Excel 2013, klikker du her. Hvis du vil ha veiledning om hvordan du aktiverer Power Pivot, kan du klikke her.

Importere data fra en database

Vi begynner denne opplæringen med en tom arbeidsbok. Målet i denne delen er å koble til en ekstern datakilde, og importere disse dataene til Excel for videre analyse.

La oss begynne med å laste ned noen data fra Internett. Dataene beskriver olympiske medaljer, og er en Microsoft Access-database.

1. Klikk koblingene nedenfor for å laste ned filer vi skal bruke i denne opplæringsserien. Last ned hver av de fire filene til en plassering som er lett tilgjengelig, for eksempel Nedlastinger eller Minedokumenter , eller til en ny mappe du oppretter:
   > OlympicMedals.accdb Access-database
   >OlympicSports.xlsx Excel arbeidsbok
   > Population.xlsx Excel arbeidsbok
   >DiscImage_table.xlsx Excel arbeidsbok

2. Åpne en tom arbeidsbok i Excel 2013.

3. Klikk DATA > Hent eksterne data > Fra Access. Båndet justeres dynamisk basert på bredden på arbeidsboken, så kommandoene på båndet kan se litt annerledes ut enn på de følgende skjermbildene. Det første skjermbildet viser båndet i en bred arbeidsbok, og det andre bildet viser en arbeidsbok som er skalert slik at den tar opp bare en del av skjermen.
   
   
   
   
   
    

4. Velg OlympicMedals.accdb-filen du lastet ned, og klikk Åpne. Følgende Velg tabell-vindu åpnes og viser tabellene i databasen. Tabeller i en database ligner på regneark eller tabeller i Excel. Merk av for Aktiver merking av flere tabeller, og velg alle tabellene. Klikk deretter OK.
   
   

5. Vinduet Importer data vises.

   Obs!: Legg merke til avmerkingsboksen nederst i vinduet som lar deg legge til disse dataene i datamodellen,som vises i følgende skjermbilde. En datamodell opprettes automatisk når du importerer eller arbeider med to eller flere tabeller samtidig. En datamodell integrerer tabellene og muliggjør omfattende analyser ved hjelp av pivottabeller, Power Pivot og Power View. Når du importerer tabeller fra en database, brukes de eksisterende databaserelasjonene mellom disse tabellene til å opprette datamodellen i Excel. Datamodellen er gjennomsiktig i Excel, men du kan vise og endre den direkte ved hjelp av Power Pivot tillegget. Datamodellen diskuteres mer detaljert senere i denne opplæringen.

   
   Velg alternativet Pivottabellrapport, som importerer tabellene til Excel og klargjør en pivottabell for å analysere de importerte tabellene, og klikk OK.
   
   

6. Når dataene er blitt importert, opprettes en pivottabell ved hjelp av de importerte tabellene.
   
   

Når dataene er importert til Excel, og datamodellen er automatisk opprettet, er du klar til å utforske dataene.

Utforske data ved hjelp av en pivottabell

Det er enkelt å utforske importerte data ved hjelp av en pivottabell. I en pivottabell drar du felt (ligner på kolonner i Excel) fra tabeller (for eksempel tabellene du akkurat har importert fra Access-databasen) til ulike områder i pivottabellen for å justere hvordan dataene presenteres. En pivottabell har fire områder: FILTRE, KOLONNER, RADER og VERDIER.

Det kan ta litt eksperimentering å finne ut hvilket område et felt skal dras til. Du kan dra så mange eller få felt fra tabellene som du vil, til pivottabellen presenterer dataene slik du vil se dem. Utforsk gjerne ved å dra felt til forskjellige områder av pivottabellen. De underliggende dataene påvirkes ikke når du ordner felt i en pivottabell.

La oss utforske dataene for olympiske medaljer i pivottabellen, og vi starter med de olympiske medaljevinnerne organisert etter disiplin, medaljetype og idrettsutøverens land eller område.

1. Utvid Medaljer-tabellen under Pivottabellfelt ved å klikke pilen ved siden av tabellen. Finn NOC_CountryRegion-feltet i den utvidede Medaljer-tabellen, og dra feltet til KOLONNER-området. NOC står for Nasjonale olympiske komiteer, som er organisasjonsenheten for et land eller område.

2. Deretter drar du en disiplin fra Disipliner-tabellen til RADER-området.

3. La oss filtrere disipliner for å vise bare fem idretter: bueskyting, stup, fekting, kunstløp og skøyteløp. Du kan gjøre dette fra Pivottabellfelt-området eller fra filteret Radetiketter i selve pivottabellen.

   1. Klikk hvor som helst i pivottabellen for å sikre Excel pivottabellen er valgt. Hold pekeren over Disiplin-feltet i Pivottabellfelt-listen, der Disipliner-tabellen er utvidet, og det vises en rullegardinpil til høyre for feltet. Klikk rullegardinlisten, klikk (Merk alle)for å fjerne alle valgene, og rull deretter nedover og velg Bueskyting, Dykking, Fekting, Kunstløp og Hurtigløp. Klikk på OK.

   2. Du kan også klikke rullegardinlisten ved siden av Radetiketter i pivottabellen i Radetiketter-delen av pivottabellen for å fjerne alle valgene, rulle nedover og velge Bueskyting, Dykking, Fekting, Kunstløp og Hurtigløp. Klikk på OK.

4. I Pivottabellfelt drar du en medalje fra Medaljer-tabellen til VERDIER-området. Siden verdier må være numeriske, endrer Excel automatisk medaljen til medaljeantall.

5. Velg en medalje på nytt fra Medaljer-tabellen, og dra den til FILTRE-området.

6. La oss filtrere pivottabellen slik at bare de landene eller områdene med mer enn totalt 90 medaljer vises. Slik gjør du det.

   1. Klikk rullegardinpilen til høyre for Kolonneetiketter i pivottabellen.

   2. Velg Verdifiltre, og velg Større enn….

   3. Skriv inn 90 i det siste feltet (til høyre). Klikk OK.
      

Pivottabellen ser ut som følgende skjermbilde.

Med minimal innsats har du nå en enkel pivottabell som inneholder felt fra tre forskjellige tabeller. Det som gjorde denne oppgaven så enkel, var relasjonene mellom tabellene som fantes på forhånd. Siden det fantes tabellrelasjoner i kildedatabasen, og siden du importerte alle tabellene i én enkelt operasjon, kunne Excel gjenopprette disse tabellrelasjonene i datamodellen.

Men hva om dataene kommer fra flere kilder eller importeres senere? Du kan vanligvis opprette relasjoner med nye data basert på samsvarende kolonner. I det neste trinnet skal du importere flere tabeller og lære hvordan du oppretter nye relasjoner.

Importere data fra et regneark

La oss nå importere data fra en annen kilde, denne gangen fra en eksisterende arbeidsbok, og angi relasjonene mellom de eksisterende dataene og de nye dataene. Relasjoner gjør det mulig å analysere samlinger med data i Excel, og opprette interessante og engasjerende visualiseringer fra dataene du importerer.

La oss begynne med å opprette et tomt regneark og deretter importere data fra en Excel-arbeidsbok.

1. Sett inn et nytt Excel-regneark, og gi regnearket navnet Idretter.

2. Bla til mappen som inneholder de nedlastede eksempeldatafilene, og åpne OlympicSports.xlsx.

3. Merk og kopier dataene i Ark1. Hvis du velger en celle med data, for eksempel celle A1, kan du trykke Ctrl+A for å merke alle tilstøtende data. Lukk OlympicSports.xlsx-arbeidsboken.

4. Plasser markøren i celle A1 i Idretter-regnearket, og lim inn dataene.

5. Med dataene fremdeles uthevet, trykker du Ctrl + T for å formatere dataene som en tabell. Du kan også formatere dataene som en tabell fra båndet ved å velge HJEM > Formater som tabell. Siden dataene har overskrifter, velger du Tabellen har overskrifter i Opprett tabell-vinduet som åpnes, som vist her.
   
   
   
   Formatering av dataene som en tabell har mange fordeler. Du kan tilordne et navn til en tabell, noe som gjør det enkelt å identifisere den. Du kan også opprette relasjoner mellom tabeller, noe som muliggjør utforsking og analyse i pivottabeller, Power Pivot og Power View.

6. Gi tabellen et navn. Finn Tabellnavn-feltet i TABELLVERKTØY > UTFORMING > Egenskaper, og skriv inn Idretter. Arbeidsboken ser ut som følgende skjermbilde.
   

7. Lagre arbeidsboken.

Importere data ved hjelp av Kopier og Lim inn

Nå som vi har importert data fra en Excel-arbeidsbok, la oss importere data fra en tabell vi finner på en nettside, eller en annen kilde som vi kan kopiere og lime inn i Excel fra. I de neste trinnene skal du legge til de olympiske vertsbyene fra en tabell.

1. Sett inn et nytt Excel-regneark, og gi regnearket navnet Verter.

2. Merk og kopier den følgende tabellen, inkludert tabelloverskriftene.

1. Plasserer markøren i celle A1 i Verter-regnearket i Excel, og lim inn dataene.

2. Formater dataene som en tabell. Som beskrevet tidligere i denne opplæringen, må du trykke Ctrl + T for å formatere dataene som en tabell, eller du kan velge HJEM > Formater som tabell. Siden dataene har overskrifter, velger du Tabellen har overskrifter i Opprette tabell-vinduet som vises.

3. Gi tabellen et navn. Finn Tabellnavn-feltet i TABELLVERKTØY > UTFORMING > Egenskaper, og skriv inn Verter.

4. Velg utgavekolonnen, og formater den på HJEM-fanen som Tall med 0 desimaler.

5. Lagre arbeidsboken. Arbeidsboken ser ut som følgende skjermbilde.

Nå som du har en Excel-arbeidsbok med tabeller, kan du opprette relasjoner mellom dem. Når du oppretter relasjoner mellom tabellene, kan du blande dataene fra de to tabellene.

Opprette en relasjon mellom importerte data

Du kan umiddelbart begynne å bruke felt i pivottabellen fra de importerte tabellene. Hvis Excel ikke kan finne ut hvordan et felt skal innlemmes i pivottabellen, må det opprettes en relasjon med den eksisterende datamodellen. I de neste trinnene vil du lære hvordan du oppretter en relasjon mellom dataene du har importert fra forskjellige kilder.

1. Klikk Alle øverst i pivottabellfelt i Ark1for å vise hele listen over tilgjengelige tabeller, som vist i følgende skjermbilde.
   

2. Bla gjennom listen for å se de nye tabellene du akkurat har lagt til.

3. Utvid Idretter, og velg Idrett for å legge den til i pivottabellen. Legg merke til at Excel ber deg om å opprette en relasjon, som vist i følgende skjermbilde.
   
    

   Dette varselet vises fordi du har brukt felt fra en tabell som ikke er en del av den underliggende datamodellen. Én måte å legge til en tabell i datamodellen på, er å opprette en relasjon til en tabell som allerede finnes i datamodellen. For å opprette relasjonen, må én av tabellene ha en kolonne med unike, ikke-gjentatte verdier. I eksempeldataene inneholder Disipliner-tabellen som er importert fra databasen, et felt med idrettskoder, kalt SportID. De samme idrettskodene vises som et felt i Excel-dataene vi importerte. La oss opprette relasjonen.

4. Klikk OPPRETT... i det uthevede Pivottabellfelt-området for å åpne dialogboksen Opprett relasjon, som vist i følgende skjermbilde.
   
   

5. Velg Disipliner fra rullegardinlisten under Tabell.

6. Velg SportID under Kolonne (sekundær).

7. Velg Idretter under Relatert tabell.

8. Velg SportID under Relatert kolonne (primær).

9. Klikk OK.

Pivottabellen endres for å gjenspeile den nye relasjonen. Men pivottabellen ser ikke helt riktig ut ennå, på grunn av rekkefølgen på feltene i RADER-området. Disiplin er en underkategori av en gitt idrett, men siden vi plasserte Disiplin over Idrett i RADER-området, er det ikke riktig organisert. Det følgende skjermbildet viser denne uønskede rekkefølgen.

1. Flytt Idrett ovenfor Didiplin i RADER-området. Det er mye bedre, og pivottabellen viser dataene slik de skal se ut, som vist i følgende skjermbilde.
   
   

I bakgrunnen bygger Excel en datamodell som kan brukes i hele arbeidsboken, i alle pivottabeller, pivotdiagrammer, i Power Pivot eller i en hvilken som helst Power View-rapport. Tabellrelasjonene er grunnlaget for en datamodell, og det er de som bestemmer navigasjons- og beregningsbanene.

I den neste opplæringen utvider du datamodellrelasjoner ved hjelp av Excel 2013,Power Pivotog DAX, bygger du på det du har lært her, og går gjennom å utvide datamodellen ved hjelp av et kraftig og visuelt Excel-tillegg kalt Power Pivot. Du lærer også hvordan du beregner kolonner i en tabell, og bruker den beregnede kolonnen slik at en ellers ikke-relatert tabell kan legges til i datamodellen.

Kontrollpunkt og test

Gjennomgå det du har lært

Du har nå en Excel arbeidsbok som inneholder en pivottabell som har tilgang til data i flere tabeller, hvorav flere du har importert separat. Du lærte å importere fra en database, fra en annen Excel arbeidsbok og kopiere data og lime dem inn i Excel.

For å få dataene til å fungere sammen, måtte du opprette en tabellrelasjon som brukes til å koordinere radene i Excel. Du har også lært at det å ha kolonner i en tabell som samsvarer med data i en annen tabell, er avgjørende når du skal opprette relasjoner og når du skal slå opp relaterte rader.

Du er klar for neste opplæring i denne serien. Her er en kobling:

Utvide datamodellrelasjoner ved hjelp av Excel 2013, Power Pivot og DAX

PRØVE

Vil du se hvor godt du husker hva du har lært? Her får du en mulighet til det. Følgende test belyser funksjoner eller krav du har lært om i denne opplæringen. Nederst på siden finner du svarene. Lykke til!

Spørsmål 1: Hvorfor er det viktig å konvertere importerte data til tabeller?

A: Du trenger ikke å konvertere dem til tabeller, fordi alle importerte data blir automatisk gjort om til tabeller.

B: Hvis du konverterer importerte data til tabeller, blir de utelatt fra datamodellen. Bare når de er utelatt fra datamodellen er de tilgjengelige i pivottabeller, Power Pivot og Power View.

C: Hvis du konverterer importerte data til tabeller, kan de inkluderes i datamodellen og gjøres tilgjengelig for pivottabeller, Power Pivot og Power View.

D: Du kan ikke konvertere importerte data til tabeller.

Spørsmål 2: Hvilke av følgende datakilder kan du importere til Excel og ta med i datamodellen?

A: Access-databaser og mange andre databaser.

B: Eksisterende Excel-filer.

C: Alt du kan kopiere og lime inn i Excel og formatere som en tabell, inkludert datatabeller på nettsteder, dokumenter eller annet som kan limes inn i Excel.

D: Alle svarene ovenfor

Spørsmål 3: Hva skjer i en pivottabell når du endrer rekkefølgen på felt i de fire pivottabellfeltområdene?

A: Ingenting, du kan ikke endre rekkefølgen på feltene når du har plassert dem i pivottabellfeltområdene.

B: Pivottabellformatet endres for å gjenspeile oppsettet, men de underliggende dataene blir ikke berørt.

B: Pivottabellformatet endres for å gjenspeile oppsettet, og de underliggende dataene endres permanent.

D: De underliggende dataene endres, noe som resulterer i nye datasett.

Spørsmål 4: Hvilke krav gjelder når du oppretter en relasjon mellom tabeller?

A: Ingen tabell kan ha en kolonne som inneholder unike, ikke-gjentatte verdier.

B: Én tabell må ikke være en del av Excel-arbeidsboken.

C: Kolonnene må ikke konverteres til tabeller.

D: Ingen av de ovennevnte er riktig.

Svar på prøve

1. Riktig svar: C

2. Riktig svar: D

3. Riktig svar: B

4. Riktig svar: D