Skapa en relation mellan tabeller i Excel

Har du någonsin använt VLOOKUP till att hämta en kolumn i en tabell till en annan tabell? Nu när Excel 2013 har en inbyggd datamodell är VLOOKUP föråldrad. Du kan skapa en relation mellan två tabeller med data, som baseras på överensstämmande data i vardera tabell. Sedan kan du skapa Power View-blad, pivottabeller och andra rapporter med fält från varje tabell, även om tabellerna kommer från olika källor. Om du t.ex. har kundförsäljningsdata kanske du vill importera och relatera tidsinformation för analys av försäljningsmönster per år och månad.

Alla tabeller i arbetsboken visas i pivottabellen och Power View-fältlistor.

När du importerar relaterade tabeller från en relationsdatabas skapas ofta de här relationerna i bakgrunden av Excel i den datamodell som skapas. I alla andra fall måste du skapa relationer manuellt.

  1. Kontrollera att arbetsboken innehåller två eller fler tabeller och att varje tabell har en kolumn som kan mappas till en kolumn i en annan tabell.
  2. Formatera data som en tabell eller

Importera externa data som en tabell i ett nytt kalkylblad.

  1. Ge varje tabell ett meningsfullt namn: I Tabellverktyg klickar du på Design > Tabellnamn och anger ett namn.
  2. Kontrollera att kolumnen i en av tabellerna har unika datavärden och inga dubbletter. Excel kan bara skapa relationen om en kolumn innehåller unika värden.

Om du till exempel vill relatera kundförsäljning och tidsinformation måste båda tabellerna innehålla data i samma format (t. ex. 1/1/2012) och minst en tabell (tidsinformation) listar varje datum endast en gång i kolumnen.

  1. Klicka på Data > Relationer.

Om Relationer är nedtonad innehåller arbetsboken endast en tabell.

  1. Klicka på Ny i rutan Hantera relationer.
  2. Klicka på pilen vid Tabell i rutan Skapa relation och välj en tabell i listan. Den här tabellen bör finnas på n-sidan i en 1:n-relation. I exemplet med kund- och tidsinformation skulle du välja kundförsäljningstabellen först eftersom flera försäljningar förmodligen äger rum på samma dag.
  3. Välj den kolumn vid Kolumn (sekundär) som innehåller de data som är relaterade till Relaterad kolumn (primär). Om du till exempel hade en datumkolumn i båda tabellerna, skulle du välja den kolumnen nu.
  4. Välj en tabell vid Relaterad tabell som innehåller minst en kolumn med data som är relaterade till den tabell du valde vid Tabell.
  5. Välj en kolumn vid Relaterad kolumn (primär) som innehåller unika värden som matchar värdena i den kolumn du valde för Kolumn.
  6. Klicka på OK.
Mer information om relationer mellan tabeller i Excel


Anmärkningar om relationer

  • Du vet om en relation finns när du drar fält från de olika tabellerna till fältlistan för pivottabellen. Om du inte uppmanas att skapa en relation har Excel redan den relationsinformation som krävs för att relatera data.
  • Att skapa relationer påminner om att använda VLOOKUP: du behöver kolumner som innehåller överensstämmande data, så att rader i en tabell kan korsrefereras med rader i en annan tabell i Excel. I exemplet med tidsinformation skulle kundtabellen behöva innehålla datumvärden som också finns i en tidsinformationstabell.
  • Tabellrelationer i en datamodell kan vara 1:1 (varje passagerare har ett boardingpass) eller 1:n (varje flyg har många passagerare), men aldrig n:n. n:n-relationer resulterar i cirkelsambandsfel, t. ex. "Ett cirkelsamband upptäcktes". Felet uppstår om du gör en direkt koppling mellan två tabeller som är n:n, eller indirekta kopplingar (en kedja av tabellrelationer som är 1:n i varje relation men n:n när de visas slutpunkt till slutpunkt). Läs mer i Relationer mellan tabeller i en datamodell.
  • Datatyperna i de två kolumnerna måste vara kompatibla. Mer information finns i Datatyper i datamodeller i Excel.
  • Andra sätt att skapa relationer kan vara mer intuitiva, särskilt om du är osäker på vilka kolumner du ska använda. Mer information finns i Skapa en relation i diagramvyn i Power Pivot.

Exempel: Relatera tidsinformation till ett flygbolags flygdata

Du kan lära dig mer om både tabellrelationer och tidsinformation med hjälp av data som finns fritt tillgänglig på Microsoft Azure Marketplace. Vissa datamängder är mycket stora och det krävs snabb Internetuppkoppling för att kunna slutföra datahämtningen inom rimlig tid.

  1. Starta Power Pivot i Microsoft Excel 2013-tilläggsprogrammet och öppna Power Pivot-fönstret.
  2. Klicka på Hämta externa data > Från datatjänst > Från Microsoft Azure Marketplace. Hemsidan för Microsoft Azure Marketplace öppnas i tabellimportguiden.
  3. Klicka på Gratis under Pris.
  4. Klicka på Vetenskap & Statistik under Kategori.
  5. Gå till StrömmaDatum och klicka på Prenumerera. Mer information finns i datafeed för tidsinformation.
  6. Ange ditt Microsoft-konto och klicka sedan på Logga in. En förhandsöversikt över informationen visas i fönstret.
  7. Rulla längst ned och klicka på Urvalsfråga.
  8. Klicka på Nästa.
  9. Välj Baskalender och klicka på Slutför om du vill importera data. Med en snabb Internetanslutning tar importen ungefär en minut. När den är slutförd visas en statusrapport med 73,414 överförda rader. Klicka på Stäng.
  10. Hämta ytterligare en datamängd genom att klicka på Hämta externa data > Från datatjänst > Från Microsoft Azure Marketplace.
  11. Klicka på Data under Typ.
  12. Klicka på Gratis under Pris.
  13. Gå till Försenade flyg och klicka på Välj.
  14. Rulla längst ned och klicka på Urvalsfråga.
  15. Klicka på Nästa.
  16. Importera data genom att klicka på Slutför. Med en snabb Internetanslutning kan importen ta 15 minuter. När den är slutförd visas en statusrapport med 2,427,284 överförda rader. Klicka på Stäng. Nu ska du ha två tabeller i datamodellen. För att de ska kunna relateras måste det finnas kompatibla kolumner i varje tabell.
  17. Lägg märke till att Datumnyckel i Baskalender är i formatet 1/1/2012 12:00:00 AM. Tabellen Enligt_Tidtabell har också en datumkolumn, Flygdatum, där värdena är angivna i samma format: 1/1/2012 12:00:00 AM. De två kolumnerna innehåller överensstämmande data, av samma datatyp, och minst en av kolumnerna (Datumnyckel) innehåller bara unika värden. I de följande stegen kommer du att använda de här kolumnerna för att relatera tabellerna.
  18. I Power Pivot-fönstret klickar du på Pivottabell för att skapa en pivottabell i ett nytt eller befintligt kalkylblad.
  19. Expandera Enligt_Tidtabell i listan Fält och klicka på FörseningMinuter så läggs den till i området Värden. Nu ska pivottabellen visa den totala tid uppmätt i minuter som flygningar varit försenade.
  20. Expandera Baskalender och klicka på MånadKalender så läggs den till i området Rader.
  21. Lägg märke till att pivottabellen nu visar månader, men summan av det totala antalet minuter är samma för varje månad. Upprepade identiska värden tyder på att en relation är nödvändig.
  22. Klicka på Skapa i "Relationer mellan tabeller kan behövas" i listan Fält.
  23. Välj Enligt_Tidtabell i Relaterad tabell och Flygdatum i Relaterad kolumn (primär).
  24. Välj Baskalender i Tabell och Datumnyckel i Kolumn (sekundär). Skapa relationen genom att klicka på OK.
  25. Lägg märke till att summan av försenade minuter nu är olika för varje månad.
  26. Dra Årsnyckel till området Rader, ovanför MånadKalender i Baskalender.

Nu kan du dela upp förseningarna efter år och månad eller andra värden i kalendern.

 Tips    Månader visas som standard i alfabetisk ordning. Med Power Pivot -tilläggsprogrammet kan du ändra ordningen så att månader visas kronologiskt.

  1. Kontrollera att tabellen Baskalender är öppen i Power Pivot -fönstret.
  2. Klicka på Sortera efter kolumn i hemtabellen.
  3. Välj MånadKalender i Sortera
  4. Välj MånadPåÅret i Efter.

I pivottabellen sorteras nu varje kombination av månad och år (oktober 2011, november 2011) efter månadens nummer under året (10, 11). Det är lätt att ändra sorteringsordningen, eftersom StrömmaDatum -feeden levererar alla nödvändiga kolumner som behövs i det här exemplet. Om du använder en annan tidsinformationstabell behöver du göra på ett annat sätt.

"Relationer mellan tabeller kan behövas"

När du lägger till fält i en pivottabell visas information om ifall en tabellrelation behövs för att fälten du markerar i pivottabellen ska vara begripliga.

Om en relation behövs visas knappen Skapa

Du kan få information i Excel om att en relation behövs, men du får inte veta vilka tabeller och kolumner som ska användas eller om en tabellrelation ens är möjlig. För att få svar på det du behöver veta kan du gå igenom följande steg.

Steg 1: Bestäm vilka tabeller som ska anges i relationen

Om modellen bara innehåller ett fåtal tabeller är det kanske självklart vilka du ska använda. Men när det gäller större modeller kan du behöva lite hjälp. Ett sätt är att använda diagramvyn i Power Pivot-tilläggsprogrammet. Diagramvyn ger en visuell representation av alla tabeller i datamodellen. Med hjälp av diagramvyn kan du snabbt avgöra vilka tabeller som skiljer sig från resten av modellen.

Diagramvy med frånkopplade tabeller

 Anteckning    Det går att skapa tvetydiga relationer som är ogiltiga i en pivottabell eller Power View-rapport. Föreställ dig att alla tabeller är relaterade till andra tabeller i modellen på något sätt, men när du försöker kombinera fält från olika tabeller visas meddelandet "Relationer mellan tabeller kan behövas". Den troligaste orsaken är att du stött på en n:n-relation. Om du följer kedjan av tabellrelationer som sammanlänkar de tabeller du vill använda, upptäcker du förmodligen att det finns två eller fler n:n-tabellrelationer. Det finns ingen enkel lösning som passar i alla lägen, men du kan försöka med att skapa beräknade kolumner för att konsolidera kolumnerna du vill använda till en tabell.

Steg 1: Hitta kolumner som kan användas för att skapa en väg från en tabell till nästa

När du har identifierat vilken tabell som är frånkopplad från den övriga modellen, går du igenom tabellens kolumner för att avgöra om någon annan kolumn någonstans i modellen innehåller överensstämmande värden.

Föreställ dig att du t. ex. har en modell som innehåller försäljning per område, och att du sedan importerar demografiska data för att undersöka om det finns ett samband mellan försäljning och demografiska förändringar i varje område. Eftersom dina demografidata kommer från en annan datakälla, är tabellerna först separerade från resten av modellen. Om du vill integrera demografidata med den övriga modellen, behöver du hitta en kolumn i någon av demografitabllerna som överensstämmer med en som du redan använder. Om dina demografiska data t. ex. är ordnade efter område och dina försäljningsdata anger i vilket område en försäljning ägt rum, kan du relatera de båda datamängderna genom att hitta en gemensam kolumn som t. ex. stad, postnummer eller område, som kan användas som uppslag.

Förutom överensstämmande värden finns det ytterligare några villkor för att en relation ska kunna skapas:

  • Datavärdena i uppslagskolumnen måste vara unika. Kolumnen kan alltså inte innehålla dubletter. I datamodeller är null-värden och tomma strängar likvärdiga med tomma värden, som i sig är distinkta datavärden. Det innebär att du inte kan ha flera null-värden i uppslagskolumnen.
  • Datatyperna i både källkolumnen och uppslagskolumnen måste vara kompatibla. Mer information om datatyper finns i Datatyper i datamodeller.

Mer information om tabellrelationer finns i Relationer mellan tabeller i en datamodell.

Överst på sidan Överst på sidan

 
 
Gäller:
Excel 2013, Power Pivot in Excel 2013