Har du nogensinde brugt LOPSLAG til at hente en kolonne fra én tabel til en anden tabel? Nu hvor Excel har en indbygget datamodel, er LOPSLAG forældet. Du kan oprette en relation mellem to tabeller med data baseret på matchende data i hver tabel. Derefter kan du oprette Power View-ark og oprette pivottabeller og andre rapporter med felter fra hver tabel, selv når tabellerne er fra forskellige kilder. Hvis du f.eks. har kundesalgsdata, kan det være en god ide at importere og relatere time intelligence-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.
-
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.
-
Gør et af følgende: Formatér dataene som en tabel eller Importér eksterne data som en tabel i et nyt regneark.
-
Tildel de enkelte tabeller et beskrivende navn: i Tabelværktøjer, skal du klikke på Design > Tabelnavn > Angiv et navn.
-
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.
-
Klik på Data > Relationer.
Hvis Relationer er nedtonet, indeholder din projektmappe kun én tabel.
-
I feltet Administrer relationer skal du klikke på Ny.
-
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.
-
For Kolonne (Fremmed) skal du vælge den kolonne, der indeholder de data, der er relateret til Relateret kolonne (primær). Hvis du f.eks. havde en datokolonne i begge tabeller, ville du vælge den pågældende kolonne nu.
-
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.
-
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.
-
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 (hver passager har et boardingkort) eller en til mange (hver flyvning har mange passagerer), men ikke mange til mange. Mange til mange-relationer medfører fejl i cirkulær afhængighed, f.eks. "Der blev fundet 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 af tabelrelationer, der er en-til-mange inden for hver relation, men mange-til-mange, når de vises fra ende til anden. Læs mere om 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.
-
Start Power Pivot i Microsoft Excel-tilføjelsesprogrammet, og åbn Power Pivot-vinduet.
-
Klik på Hent eksterne data > Fra datatjeneste > Fra Microsoft Azure Marketplace. Startsiden for Microsoft Azure Marketplace åbnes i guiden Tabelimport.
-
Klik på Gratis under Pris.
-
Klik på Videnskab og statistik under Kategori.
-
Søg efter DateStream, og klik på Abonner.
-
Angiv din Microsoft-konto, og klik på Log på. Der vises et eksempel på dataene i vinduet.
-
Rul til bunden, og klik på Udvælgelsesforespørgsel.
-
Klik på Næste.
-
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.
-
Klik på Hent eksterne data > Fra datatjeneste > Fra Microsoft Azure Marketplace for at importere endnu et datasæt.
-
Klik på Data under Type.
-
Klik på Gratis under Pris.
-
Søg efter US Air Carrier Flight Delays, og klik på Vælg.
-
Rul til bunden, og klik på Udvælgelsesforespørgsel.
-
Klik på Næste.
-
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.
-
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.
-
Klik på Pivottabel i Power Pivot-vinduet for at oprette en pivottabel i et nyt eller eksisterende regneark.
-
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.
-
Udvid BasicCalendarUS, og klik på MonthInCalendar for at føje det til rækkeområdet.
-
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.
-
Klik på Opret ud for "Det kan være nødvendigt med relationer mellem tabeller" på feltlisten.
-
Vælg On_Time_Performance i Relateret tabel, og vælg FlightDate i Relateret kolonne (primær).
-
Vælg BasicCalendarUS i Tabel, og vælg DateKey i Kolonne (fremmed). Klik på OK for at oprette relationen.
-
Bemærk, at summen af forsinkede minutter nu varierer for hver måned.
-
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.
-
Kontrollér, at tabellen BasicCalendarUS er åben i Power Pivot-vinduet.
-
Klik på Sortér efter kolonne under fanen Hjem.
-
Vælg MonthInCalendar for Sortér.
-
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.
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.
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 sammenhæng mellem salg og demografiske tendenser i hvert distrikt. Da de demografiske data kommer fra en anden datakilde, isoleres dens tabeller til at begynde med fra resten af modellen. Hvis du vil integrere de demografiske data med resten af modellen, skal du finde en kolonne i en af de demografiske tabeller, der svarer til en, du allerede bruger. Hvis de demografiske data f.eks. er organiseret efter område, og dine salgsdata angiver, hvilket område salget fandt sted, kan du relatere de to datasæt ved at finde en fælles kolonne, f.eks. stat, postnummer eller område, hvor du kan levere 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.
