Har du noen gang brukt FINN.RAD til å hente en kolonne fra én tabell til en annen? Nå som Excel har en innebygd datamodell, er FINN.RAD foreldet. Du kan opprette en relasjon mellom to tabeller med data basert på samsvarende data i hver tabell. Deretter kan du opprette Power View-ark og lage pivottabeller og andre rapporter med felt fra hver tabell, selv når tabellene kommer fra forskjellige kilder. Hvis du for eksempel har kundesalgsdata, ønsker du kanskje å importere og relatere tidsintelligensdata for å analysere salgsmønster etter år og måned.
Alle tabellene i en arbeidsbok vises i pivottabellisten og Power View-feltlisten.
Når du importerer relaterte tabeller fra en relasjonsdatabase, kan Excel ofte opprette disse relasjonene i datamodellen som bygges i bakgrunnen. I alle andre tilfeller må du opprette relasjoner manuelt.
-
Sørg for at arbeidsboken inneholder minst to tabeller, og at hver tabell har en kolonne som kan tilordnes til en kolonne i en annen tabell.
-
Gjør ett av følgende: Formater dataene som entabell , eller Importer eksterne data som en tabell i et nytt regneark.
-
Gi hver tabell et meningsfullt navn: I Tabellverktøy klikker du Utforming > Tabellnavn > skriv inn et navn.
-
Kontroller at kolonnen i én av tabellene har unike verdier uten duplikater. Excel kan bare opprette relasjonen hvis én kolonne inneholder unike verdier.
Hvis du for eksempel vil relatere kundesalg med tidsintelligens, må begge tabellene inneholde datoer i samme format (for eksempel 1/1/2012), og minst én tabell (tidsintelligens) viser hver dato bare én gang i kolonnen.
-
Klikk Data> Relasjoner.
Hvis Relasjoner vises nedtonet, inneholder arbeidsboken bare én tabell.
-
Klikk Ny i Behandle relasjoner-boksen.
-
Klikk pilen for Tabell i Opprett relasjon-boksen, og velg en tabell fra listen. Denne tabellen må være på mange-siden i en én-til-mange-relasjon. I eksempelet vårt på kunder og tidsintelligens ville du valgt kundesalgstabellen først fordi mange salg er sannsynlig hvilken som helst dag.
-
For Kolonne (sekundær) velger du kolonnen som inneholder dataene som er relatert til Relatert kolonne (primær). Hvis du for eksempel hadde en datokolonne i begge tabellene, ville du brukt denne kolonnen nå.
-
For Relatert tabell velger du en tabell som har minst én kolonne med data som er relatert til tabellen du nettopp valgte for Tabell.
-
For Relatert kolonne (primær) velger du en kolonne som inneholder unike verdier som samsvarer med verdiene i kolonnen du valgte for Kolonne.
-
Klikk OK.
Mer om relasjoner mellom tabeller i Excel
Notater om relasjoner
-
Du ser om det finnes relasjoner når du drar felt fra forskjellige tabeller til feltlisten i pivottabellen. Hvis du blir spurt om du vil opprette en relasjon, har Excel allerede relasjonsinformasjonen som trengs for å koble sammen dataene.
-
Oppretting av relasjoner ligner på bruk av FINN.RAD: Du trenger kolonner med samsvarende data, slik at Excel kan kryssreferere radene i én tabell med radene i en annen tabell. I eksemplet med tidsintelligens ville kundetabellen måtte hatt datoverdier som også finnes i en tidsintelligenstabell.
-
I en datamodell kan tabellrelasjoner være én-til-én (hver passasjer har ett ombordstigningskort) eller én-til-mange (hver flight har mange passasjerer), men ikke mange-til-mange. Mange-til-mange-relasjoner fører til sirkelavhengighetsfeil, for eksempel “En sirkelavhengighet ble oppdaget”. Denne feilen vil oppstå hvis du lager en direkte tilkobling mellom to tabeller som er mange-til-mange, eller indirekte tilkoblinger (en kjede med tabellrelasjoner som er én-til-mange innen hver relasjon, men mange-til-mange når vist samlet). Les mer om Relasjoner mellom tabeller i en datamodell.
-
Datatypene i de to kolonnene må være kompatible. Du finner mer informasjon i Datatyper i Excel-datamodeller.
-
Andre måter å opprette relasjoner på kan være mer intuitive, spesielt hvis du ikke er sikker på hvilke kolonner du bør bruke. Se Opprette en relasjon i diagramvisning i Power Pivot.
Eksempel: Relatere tidsintelligensdata til flight-data fra et flyselskap
Du kan lære om både tabellrelasjoner og tidsintelligens ved hjelp av gratis data på Microsoft Azure Marketplace. Enkelte av disse datasettene er svært store, noe som krever en rask Internett-tilkobling for å laste ned dataene innen et rimelig tidsrom.
-
Starte Power Pivot i Microsoft Excel-tillegget og åpne Power Pivot-vinduet.
-
Klikk Hent eksterne data > Fra datatjeneste > Fra Microsoft Azure Marketplace. Hjemmesiden Microsoft Azure Marketplace åpnes i veiviseren for tabellimport.
-
Klikk Gratis under Pris.
-
Klikk Forskning og statistikk under Kategori.
-
Finn DateStream, og klikk Abonner.
-
Angi Microsoft-kontoen din, og klikk Logg på. En forhåndsvisning av dataene skal vises i vinduet.
-
Bla ned til bunnen, og klikk Velg spørring.
-
Klikk Neste.
-
Velg BasicCalendarUS, og klikk deretter Fullfør for å importere dataene. Med en rask Internett-tilkobling, tar importeringen omtrent et minutt. Når den er ferdig, skal du se en statusrapport om at 73 414 rader er overført. Klikk Lukk.
-
Klikk Hent eksterne data > Fra datatjeneste > Fra Microsoft Azure Marketplace for å importere et nytt datasett.
-
Klikk Data under Type.
-
Klikk Gratis under Pris.
-
Finn US Air Carrier Flight Delays, og klikk Velg.
-
Bla ned til bunnen, og klikk Velg spørring.
-
Klikk Neste.
-
Klikk Fullfør for å importere dataene. Med en rask Internett-tilkobling kan importeringen ta omtrent 15 minutter. Når den er ferdig, skal du se en statusrapport om at 2 427 284 rader er overført. Klikk Lukk. Du skal nå ha to tabeller i datamodellen. Vi trenger kompatible kolonner i hver tabell for å relatere dem.
-
Legg merke til at DateKey i BasicCalendarUS er i formatet 1/1/2012 12:00:00 AM. Tabellen On_Time_Performance har også en kolonne for dato/klokkeslett, FlightDate, der verdiene er angitt i samme format: 1/1/2012 12:00:00 AM. De to kolonnene inneholder samsvarende data, av samme datatype, og minst én av kolonnene (DateKey) inneholder bare unike verdier. I de neste trinnene skal du bruke disse kolonnene til å relatere tabellene.
-
Klikk Pivottabell i Power Pivot-vinduet for å opprette en pivottabell i et nytt eller eksisterende regneark.
-
Utvid On_Time_Performance i feltlisten, og klikk ArrDelayMinutes for å legge det til i verdiområdet. I pivottabellen skal du se hvor lenge flighter har vært forsinket totalt, målt i minutter.
-
Utvid BasicCalendarUS, og klikk MonthInCalendar for å legge det til i radområdet.
-
Legg merke til at måneder vises i pivottabellen nå, men den totale summen av minutter er den samme for hver måned. Gjentagende, identiske verdier indikerer at det er nødvendig med en relasjon.
-
Klikk Opprett under “Relasjoner mellom tabeller kan være obligatorisk” i feltlisten.
-
Velg On_Time_Performance i Relatert tabell, og velg FlightDate i Relatert kolonne (primær).
-
Velg BasicCalendarUS i Tabell, og velg DateKey i Kolonne (sekundær). Klikk OK for å opprette relasjonen.
-
Legg merke til at summen av minutter forsinket nå varierer for hver måned.
-
Gå til BasicCalendarUS, og dra YearKey til radområdet, over MonthInCalendar.
Du kan nå dele inn ankomstforsinkelser etter år og måned eller andre verdier i kalenderen.
Tips!: Som standard er måneder oppført i alfabetisk rekkefølge. Ved hjelp av Power Pivot-tillegget kan du endre sorteringen slik at månedene vises i kronologisk rekkefølge.
-
Kontroller at tabellen BasicCalendarUS er åpen i Power Pivot-vinduet.
-
Klikk Sort etter kolonne i Hjem-tabellen.
-
Velg MonthInCalendar i Sorter.
-
Velg MonthOfYear i Etter.
Pivottabellen sorterer nå hver kombinasjon av måned-år (oktober 2011, november 2011) etter månedsnummeret i et år (10, 11). Det er enkelt å endre sorteringsrekkefølgen fordi DateStream-feeden sørger for alle nødvendige kolonner for å få dette scenariet til å virke. Hvis du bruker en annen tidsintelligenstabell, vil trinnet du må utføre, være forskjellig fra dette.
"Relasjoner mellom tabeller kan være nødvendig"
Etter hvert som du legger til felt i en pivottabell, blir du informert om en tabellrelasjon er nødvendig for at feltene du har valgt i pivottabellen, skal gi mening.
Selv om Excel kan fortelle deg når det er nødvendig med en relasjon, kan det ikke fortelle deg hvilke tabeller og kolonner du bør bruke, eller om det i det hele tatt er mulig med en tabellrelasjon. Prøv å følge disse trinnene for å få svarene du trenger.
Trinn 1: Bestemme hvilke tabeller som skal angis i relasjonen
Hvis modellen bare inneholder noen få tabeller, kan det være opplagt med én gang hvilke du må bruke. For større modeller kan du imidlertid trenge litt hjelp. Én fremgangsmåte er å bruke diagramvisning i Power Pivot-tillegget. Med diagramvisning får du en visuell gjengivelse av alle tabellene i datamodellen. Ved hjelp av diagramvisning kan du raskt bestemme hvilke tabeller som skiller seg ut fra resten av modellen.
Obs!: Det er mulig å opprette tvetydige relasjoner som er ugyldige når de brukes i en pivottabell eller en Power View-rapport. Anta at alle tabellene dine er relatert på en eller annen måte til andre tabeller i modellen, men når du prøver å kombinere felt fra forskjellige tabeller, får du meldingen “Relasjoner mellom tabeller kan være obligatorisk”. Den mest sannsynlige årsaken er at du har støtt på en mange-til-mange-relasjon. Hvis du følger kjeden med tabellrelasjoner som kobler til tabellene du vil bruke, oppdager du sannsynligvis at du har to eller flere én-til-mange-relasjoner. Det finnes ingen enkel løsning som virker for enhver situasjon, men du kan forsøke å opprette beregnede kolonner for å konsolidere kolonnene du vil bruke, i én tabell.
Trinn 2: Finne kolonner som kan brukes til å opprette en bane fra én tabell til neste
Når du har funnet ut hvilken tabell som er koblet fra resten av modellen, kan du se gjennom kolonnene i tabellen for å finne ut om en annen kolonne et annet sted i modellen inneholder samsvarende farger.
Anta for eksempel at du har en modell som inneholder produktsalg etter distrikt, og at du deretter importerer demografiske data for å finne ut om det er sammenheng mellom salg og demografiske trender i hvert distrikt. Siden de demografiske dataene kommer fra en annen datakilde, er tabellene i utgangspunktet isolert fra resten av modellen. Hvis du vil integrere de demografiske dataene med resten av modellen, må du finne en kolonne i en av de demografiske tabellene som svarer til en du allerede bruker. Hvis de demografiske dataene for eksempel er organisert etter område, og salgsdataene spesifiserer hvilket område salget fant sted i, kan du relatere de to datasettene ved å finne en felles kolonne, for eksempel poststed eller område, for å ha et utgangspunkt for oppslaget.
I tillegg til samsvarende verdier er det noen andre krav til å opprette en relasjon:
-
Dataverdier i oppslagskolonnen må være unike. Kolonnen kan med andre ord ikke inneholde duplikater. I en datamodell tilsvarer nullverdier og tomme strenger en tom verdi, som er en distinkt dataverdi. Det betyr at du ikke kan ha flere nullverdier i oppslagskolonnen.
-
Datatypene både i kildekolonnen og oppslagskolonnen må være kompatible. Hvis du vil ha mer informasjon om datatyper, kan du se Datatyper i datamodeller.
Hvis du vil ha mer informasjon om tabellrelasjoner, kan du se Relasjoner mellom tabeller i en datamodell.
