Har du någonsin använt LETARAD för att hämta en kolumn i en tabell till en annan tabell? Nu när Excel har en inbyggd datamodell behövs inte längre LETARAD. 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.
-
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.
-
Gör något av följande: Formatera data som en tabell eller Importera externa data som en tabell i ett nytt kalkylblad.
-
Ge varje tabell ett meningsfullt namn: I Tabellverktyg klickar du på Design > Tabellnamn och anger ett namn.
-
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.
-
Klicka på Data > Relationer.
Om Relationer är nedtonad innehåller arbetsboken endast en tabell.
-
Klicka på Ny i rutan Hantera relationer.
-
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.
-
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.
-
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.
-
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.
-
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.
-
I en datamodell kan tabellrelationer vara 1:1 (varje passagerare har ett boardingkort) eller 1:N (varje flygning har många passagerare), men inte många till många. Många-till-många-relationer resulterar i cirkelberoendefel, till exempel "Ett cirkulärt beroende upptäcktes". Det här felet uppstår om du skapar en direkt anslutning mellan två tabeller som är många-till-många eller indirekta anslutningar (en kedja av tabellrelationer som är 1:N i varje relation, men många-till-många när de visas från slut till slutpunkt. Läs mer om 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.
-
Starta Power Pivot i Microsoft Excel-tilläggsprogrammet och öppna Power Pivot-fönstret.
-
Klicka på Hämta externa data > Från datatjänst > Från Microsoft Azure Marketplace. Hemsidan för Microsoft Azure Marketplace öppnas i tabellimportguiden.
-
Klicka på Gratis under Pris.
-
Klicka på Vetenskap & Statistik under Kategori.
-
Leta rätt på DateStream och klicka på Prenumerera.
-
Ange ditt Microsoft-konto och klicka sedan på Logga in. En förhandsöversikt över informationen visas i fönstret.
-
Rulla längst ned och klicka på Urvalsfråga.
-
Klicka på Nästa.
-
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.
-
Hämta ytterligare en datamängd genom att klicka på Hämta externa data > Från datatjänst > Från Microsoft Azure Marketplace.
-
Klicka på Data under Typ.
-
Klicka på Gratis under Pris.
-
Gå till Försenade flyg och klicka på Välj.
-
Rulla längst ned och klicka på Urvalsfråga.
-
Klicka på Nästa.
-
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.
-
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.
-
I Power Pivot-fönstret klickar du på Pivottabell för att skapa en pivottabell i ett nytt eller befintligt kalkylblad.
-
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.
-
Expandera Baskalender och klicka på MånadKalender så läggs den till i området Rader.
-
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.
-
Klicka på Skapa i "Relationer mellan tabeller kan behövas" i listan Fält.
-
Välj Enligt_Tidtabell i Relaterad tabell och Flygdatum i Relaterad kolumn (primär).
-
Välj Baskalender i Tabell och Datumnyckel i Kolumn (sekundär). Skapa relationen genom att klicka på OK.
-
Lägg märke till att summan av försenade minuter nu är olika för varje månad.
-
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.
-
Kontrollera att tabellen Baskalender är öppen i Power Pivot-fönstret.
-
Klicka på Sortera efter kolumn i hemtabellen.
-
Välj MånadKalender i Sortera
-
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.
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.
Obs!: 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.
Anta till exempel att du har en modell som innehåller produktförsäljning per territorium och att du sedan importerar demografiska data för att ta reda på om det finns korrelation mellan försäljning och demografiska trender i varje område. Eftersom demografiska data kommer från en annan datakälla isoleras tabellerna från resten av modellen. Om du vill integrera demografiska data med resten av modellen måste du hitta en kolumn i en av de demografiska tabellerna som motsvarar en som du redan använder. Om till exempel demografiska data är ordnade efter region, och dina försäljningsdata anger vilken region försäljningen ägde rum, kan du relatera de två datamängderna genom att hitta en gemensam kolumn, till exempel delstat, postnummer eller region, för att ge sökningen.
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 en datamodell motsvaras null-värden och tomma strängar av blankvärden, som är ett särskilt datavärde. 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.
