Oprette relationer mellem tabeller i Excel

Har du nogensinde brugt LOPSLAG til flytte en kolonne fra én tabel til en anden tabel? Nu, hvor Excel 2013 har et indbygget datamodel, er LOPSLAG forældet. Du kan oprette en relation mellem to tabeller med data, der er baseret på tilsvarende data i hver tabel. Du kan derefter oprette Power View-ark og oprette pivottabeller og andre rapporter med felter fra de enkelte tabeller, selvom tabellerne stammer fra forskellige kilder. Hvis du f.eks. har importeret kundesalgsdata, kan du tilføje og relatere tidsintelligente data for at analysere salgsmønstre efter år og måned.

Alle tabellerne i en projektmappe er angivet i listerne pivottabel og Power View-felter.

Hvis du importerer relaterede tabeller fra en relationsdatabase, kan Excel ofte oprette disse relationer i en datamodel, programmet opbygger i baggrunden. I alle andre tilfælde skal du oprette relationer manuelt.

  1. Sørg for, at projektmappen indeholder mindst to tabeller, og at hver tabel har en kolonne, som kan knyttes til en kolonne i en anden tabel.
  2. Formatere dataene som en tabel, eller

Importere eksterne data som en tabel i et nyt regneark.

  1. Tildel de enkelte tabeller et beskrivende navn: i Tabelværktøjer, skal du klikke på Design > Tabelnavn > Angiv et navn.
  2. Bekræfte , at kolonnen i en af tabellerne har entydige dataværdier uden dubletter. Excel kan kun oprette relationen, hvis én kolonne indeholder unikke værdier.

Hvis du f.eks. vil relatere kundesalg til tidsintelligens, skal begge tabeller indeholde datoer i det samme format (f.eks. 01-01-2012) og mindst én tabel (tidsintelligens) skal angive de enkelte datoer én gang i kolonnen.

  1. Klik på Data > Relationer.

Hvis Relationer er nedtonet, indeholder din projektmappe kun én tabel.

  1. I feltet Administrer relationer skal du klikke på Ny.
  2. Klik på pilen for Tabel i boksen Opret relation, og vælg en tabel på listen. I en en-til-mange-relation skal denne tabel være på mange-siden. Hvis vi bruger vores eksempel med kunder og tidsintelligens, ville du vælge kundesalgstabellen først, fordi meget af salget højst sandsynligt vil ske en bestemt dag.
  3. Vælg under Kolonne (fremmed) den kolonne, der indeholder de data, som er relateret til Relateret kolonne (primær). Hvis du f.eks. havde en datokolonne i begge tabeller, ville du vælge denne kolonne nu.
  4. Vælg en tabel under Relateret tabel, der har mindst én kolonne med data, som er relateret til den tabel, du lige har valgt for Tabel.
  5. Vælg under Relateret kolonne (primær) en kolonne, der har entydige værdier, som matcher værdierne i den kolonne, du valgte for Kolonne.
  6. Klik på OK.
Mere om relationer mellem tabeller i Excel


Noter om relationer

  • Du kan se, om der findes en relation, når du trækker felter fra forskellige tabeller til pivottabelfeltlisten. Hvis du ikke bliver bedt om at oprette en relation, har Excel allerede de oplysninger om relationen, der skal bruges for at oprette en relation til dataene.
  • Oprettelse af relationer svarer til at bruge LOPSLAG: Du skal bruge kolonner, der indeholder matchende data, så Excel kan krydsreferere rækker i én tabel til rækker i en anden tabel. I eksemplet med tidsintelligens skal tabellen have datoværdier, der også findes i tabellen med tidsintelligens.
  • I en datamodel, kan tabelrelationer være en-til-en (de enkelte passagerer har ét boardingpas) eller en-til-mange (hvert fly har mange passagerer), men ikke mange-til-mange. Mange-til-mange-relationer resulterer i en cirkulær afhængighedsfejl, f.eks. "Der blev registreret en cirkulær afhængighed". Denne fejl opstår, hvis du opretter en direkte forbindelse mellem to tabeller, der er mange-til-mange, eller indirekte forbindelser (en kæde med tabelrelationer, der er en-til-mange i de enkelte relationer, men mange-til-mange, når de ses som slutpunkt-til-slutpunkt. Du kan få mere at vide om relationer under Relationer mellem tabeller i en datamodel.
  • Datatyperne i to kolonner skal være kompatible. Se Datatyper i Excel-datamodeller for at få flere oplysninger.
  • Der er andre måder at oprette relationer på, som muligvis er mere intuitive, især hvis du er i tvivl om, hvilke kolonner du skal bruge. Se Oprette en relation i diagramvisning i Power Pivot.

Eksempel: Relatering af tidsintelligensdata med flydata

Du kan både få mere at vide om tabelrelationer og tidsintelligens ved at bruge gratis data på Microsoft Azure Marketplace. Nogle af disse dataark er meget store og kræver en hurtig internetforbindelse, for at dataene kan downloades inden for en rimelig tidsperiode.

  1. Start Power Pivot i Microsoft Excel 2013-tilføjelsesprogrammet, og åbn Power Pivot-vinduet.
  2. Klik på Hent eksterne data > Fra datatjeneste > Fra Microsoft Azure Marketplace. Startsiden for Microsoft Azure Marketplace åbnes i guiden Tabelimport.
  3. Klik på Gratis under Pris.
  4. Klik på Videnskab og statistik under Kategori.
  5. Søg efter DateStream, og klik på Abonner. Du kan finde flere oplysninger om dette tidsintelligente datafeed.
  6. Angiv din Microsoft-konto, og klik på Log på. Der vises et eksempel på dataene i vinduet.
  7. Rul til bunden, og klik på Udvælgelsesforespørgsel.
  8. Klik på Næste.
  9. Vælg BasicCalendarUS, og klik derefter på Udfør for at importere dataene. Hvis du bruger en hurtig internetforbindelse, tager importen cirka et minut. Når du er færdig, får du vist en statusrapport over 73.414 overførte rækker. Klik på Luk.
  10. Klik på Hent eksterne data > Fra datatjeneste > Fra Microsoft Azure Marketplace for at importere endnu et datasæt.
  11. Klik på Data under Type.
  12. Klik på Gratis under Pris.
  13. Søg efter US Air Carrier Flight Delays, og klik på Vælg.
  14. Rul til bunden, og klik på Udvælgelsesforespørgsel.
  15. Klik på Næste.
  16. Klik på Udfør for at importere dataene. Hvis du har en hurtig internetforbindelse, kan det tage 15 minutter at importere dataene. Når du er færdig, får du vist en statusrapport over 2.427.284 overførte rækker. Klik på Luk. Du har nu to tabeller i datamodellen. Hvis de skal relateres, skal der være kompatible kolonner i begge tabeller.
  17. Bemærk, at DateKey i BasicCalendarUS er i formatet 1/1/2012 12:00:00 AM. Tabellen On_Time_Performance indeholder også en kolonne med dato/klokkeslæt, FlightDate, hvis værdier er angivet i det samme format: 1/1/2012 12:00:00 AM. De to kolonner indeholder tilsvarende data af samme datatype, og mindst én af kolonnerne (DateKey) indeholder kun entydige værdier. I de næste trin bruger du disse kolonner til at relatere tabellerne.
  18. Klik på Pivottabel i Power Pivot-vinduet for at oprette en pivottabel i et nyt eller eksisterende regneark.
  19. På feltlisten skal du udvide On_Time_Performance og klikke på ArrDelayMinutes for at føje det til området Værdier. I pivottabellen kan du se det samlede antal forsinkede fly målt i minutter.
  20. Udvid BasicCalendarUS, og klik på MonthInCalendar for at føje det til rækkeområdet.
  21. Bemærk, at der nu vises måneder i pivottabellen, men det samlede antal minutter er det samme for hver måned. Igen angiver identiske værdier, at en relation er nødvendig.
  22. Klik på Opret ud for "Det kan være nødvendigt med relationer mellem tabeller" på feltlisten.
  23. Vælg On_Time_Performance i Relateret tabel, og vælg FlightDate i Relateret kolonne (primær).
  24. Vælg BasicCalendarUS i Tabel, og vælg DateKey i Kolonne (fremmed). Klik på OK for at oprette relationen.
  25. Bemærk, at summen af forsinkede minutter nu varierer for hver måned.
  26. Træk YearKey til rækkeområdet i BasicCalendarUS over MonthInCalendar.

Du kan nu dele forsinkede ankomster op efter år og måned eller andre værdier i kalenderen.

 Tip!    Måneder vises som standard i alfabetisk rækkefølge. Ved hjælp af Power Pivot -tilføjelsesprogrammet kan du ændre sorteringen, så måneder vises i kronologisk rækkefølge.

  1. Kontrollér, at tabellen BasicCalendarUS er åben i Power Pivot -vinduet.
  2. Klik på Sortér efter kolonne under fanen Hjem.
  3. Vælg MonthInCalendar for Sortér.
  4. Vælg MonthOfYear for Efter.

Pivottabellen sorterer nu de enkelte kombinationer af måned/år (oktober 2011, november 2011) efter månedsnummer i et år (10, 11). Det er nemt at ændre sorteringsrækkefølgen, fordi feed'et DateStream indeholder alle de kolonner, der skal bruges, for at dette scenario fungerer. Hvis du bruger en anden tidsintelligent tabel, skal du udføre andre trin.

"Det kan være nødvendigt med relationer mellem tabeller"

Efterhånden som du føjer felter til en pivottabel, får du besked, hvis der kræves en tabelrelation, for at de felter, du har valgt i pivottabellen, skal give mening.

Knappen Opret vises, når en relation er nødvendig

Selvom Excel kan fortælle dig, hvornår der kræves en relation, kan det ikke fortælle dig, hvilke tabeller og kolonner du skal bruge, eller om det er muligt at oprette en tabelrelation. Prøv at udføre disse trin for at få de ønskede svar.

Trin 1: Find ud af, hvilke tabeller der skal angives i relationen

Hvis din model kun indeholder få tabeller, kan det være helt tydeligt, hvilke du skal bruge. Men hvis du har større modeller, kunne du sikkert godt bruge lidt hjælp. En måde er at bruge diagramvisning iPower Pivot-tilføjelsesprogrammet. Diagramvisningen indeholder en visuel repræsentation af alle tabellerne i datamodellen. Ved hjælp af diagramvisningen kan du hurtigt se, hvilke tabeller der skiller sig ud fra resten af modellen.

Diagramvisning med frakoblede tabeller

 Bemærk!    Du kan oprette tvetydige relationer, der er ugyldige, når de bruges i en pivottabel eller Power View-rapport. Antag, at alle dine tabeller er relateret til andre tabeller i modellen, men når du forsøger at kombinere felter fra forskellige tabeller, får du meddelelsen "Det kan være nødvendigt med relationer mellem tabeller". Den mest sandsynlige årsag er, at du er stødt på en mange-til-mange-relation. Hvis du følger kæden af tabelrelationer, der er knyttet til de tabeller, du vil bruge, finder du sikkert ud af, at du har to eller flere en-til-mange-tabelrelationer. Der findes ikke en nem løsning, som kan bruges i alle situationer, men du kan prøve at oprette beregnede kolonner for at samle de kolonner, du vil bruge, i én tabel.

Trin 2: Find kolonner, der kan bruges til at oprette en sti fra én tabel til den næste

Når du har identificeret, hvilken tabel der er koblet fra resten af modellen, kan du gennemse tabellens kolonner for at finde ud af, om en anden kolonne, der findes et andet sted i modellen, indeholder tilsvarende værdier.

Antag f.eks., at du har en model, der indeholder produktsalg efter distrikt, og at du efterfølgende importerer demografiske data for at finde ud af, om der er korrelation mellem salg og demografiske tendenser i de enkelte distrikter. Da de demografiske data er hentet fra en anden datakilde, isoleres de tilhørende tabeller som udgangspunkt fra resten af modellen. Hvis du vil integrere de demografiske data med resten af din model, skal du finde en kolonne i en af de demografiske tabeller, der svarer til den, du allerede bruger. Hvis de demografiske data f.eks. er organiseret efter område, og dine salgsdata angiver, hvilket område salget er sket i, kan du relatere de to datasæt ved at finde en fælles kolonne, f.eks. Land, Postnummer eller Område, du kan bruge til opslaget.

Ud over tilsvarende værdier er der et par andre krav, når du opretter en relation:

  • Dataværdier i opslagskolonnen skal være entydige. Kolonnen må med andre ord ikke indeholde dubletter. I en datamodel svarer null-strenge og tomme strenge til en tom værdi, som er en entydig dataværdi. Det betyder, at opslagskolonnen ikke kan indeholde flere null-værdier.
  • Datatyperne i både kildekolonnen og opslagskolonnen skal være kompatible. Du kan finde flere oplysninger om datatyper under Datakilder i datamodeller.

Du kan få mere at vide om tabelrelationer under Relationer mellem tabeller i en datamodel.

Tilbage til toppen Tilbage til toppen

 
 
Gælder for:
Excel 2013, Power Pivot in Excel 2013