Hebt u VERT.ZOEKEN wel eens gebruikt om een kolom van een tabel over te brengen naar een andere tabel? Nu Excel een ingebouwd gegevensmodel bevat, is VLOOKUP verouderd. U kunt een relatie tussen twee gegevenstabellen maken, gebaseerd op overeenkomstige gegevens in elke tabel. Vervolgens kunt u Power View-bladen maken en draaitabellen en andere rapporten maken met velden uit elke tabel, zelfs wanneer de tabellen uit verschillende bronnen afkomstig zijn. Als u bijvoorbeeld klantverkoopgegevens hebt, wilt u mogelijk time intelligence-gegevens importeren en koppelen om de verkooppatronen per jaar en maand te analyseren.
Alle tabellen in een werkmap worden weergegeven in de veldenlijsten van de draaitabel en Power View.
Wanneer u gerelateerde tabellen uit een relationele database importeert, kan Excel vaak die relaties maken in het gegevensmodel dat achter de schermen wordt gemaakt. In alle andere gevallen moet u de relaties handmatig maken.
-
Zorg dat de werkmap ten minste twee tabellen bevat en dat elke tabel een kolom heeft die kan worden toegewezen aan een kolom in een andere tabel.
-
Voer een van de volgende handelingen uit: De gegevens opmaken als een tabel of Externe gegevens importeren als een tabel in een nieuw werkblad.
-
Geef elke tabel een duidelijke naam: Klik in Hulpmiddelen voor tabellen op Ontwerpen > Tabelnaam > voer een naam in.
-
Controleer of de kolom in een van de tabellen unieke gegevenswaarden heeft zonder duplicaten. Excel kan de relatie alleen maken als één kolom unieke waarden bevat.
Als u bijvoorbeeld klantenverkoop aan time intelligence wilt koppelen, moeten de datums van beide tabellen op dezelfde manier zijn genoteerd (bijvoorbeeld als 01-01-2012) en moet in ten minste één tabel (time intelligence) elke datum slechts eenmaal in de kolom voorkomen.
-
Klik op Gegevens> Relaties.
Als Relaties grijs wordt weergegeven, bevat uw werkmap slechts één tabel.
-
Klik in het vak Relaties beheren op Nieuw.
-
Klik in het vak Relatie maken op de pijl bij Tabel en selecteer een tabel in de lijst. In een een-op-veelrelatie moet deze tabel zich bevinden aan de veel-zijde. Als we weer uitgaan van ons voorbeeld met klanten en time intelligence, kiest u eerst de tabel met klantverkopen, omdat veel verkopen hoogstwaarschijnlijk op een willekeurige dag plaatsvinden.
-
Selecteer bij Kolom (extern) de kolom met de gegevens die gerelateerd zijn aan Gerelateerde kolom (primair). Als u bijvoorbeeld in beide tabellen een datumkolom hebt, kiest u nu deze kolom.
-
Selecteer bij Gerelateerde tabel een tabel die minimaal één kolom met gegevens heeft die gerelateerd is aan de tabel die u zojuist hebt geselecteerd bij Tabel.
-
Selecteer bij Gerelateerde kolom (primair) een kolom met unieke waarden die overeenkomen met de waarden in de kolom die u hebt geselecteerd bij Kolom.
-
Klik op OK .
Meer informatie over relaties tussen tabellen in Excel
Opmerkingen over relaties
-
U weet of er een relatie bestaat wanneer u velden van verschillende tabellen naar de veldenlijst van de draaitabel sleept. Als u niet wordt gevraagd om een relatie te maken, heeft Excel al de relatiegegevens die nodig zijn om de gegevens aan elkaar te koppelen.
-
Het maken van relaties lijkt op het gebruik van VLOOKUPs: u hebt kolommen nodig die overeenkomende gegevens bevatten zodat er in Excel een kruisverwijzing kan ontstaan tussen rijen in de ene tabel en rijen in een andere tabel. In het voorbeeld van de time intelligence heeft de tabel Klant gegevenswaarden nodig die ook aanwezig zijn in een time intelligence-tabel.
-
In een gegevensmodel kunnen tabelrelaties een-op-een (elke passagier heeft één instapkaart) of een-op-veel zijn (elke vlucht heeft vele passagiers), maar niet veel-op-veel. Veel-op-veel-relaties leiden tot circulaire afhankelijkheidsfouten, zoals ‘Er is een circulaire afhankelijkheid gedetecteerd’. Deze fout treedt op wanneer u een rechtstreekse verbinding aanbrengt tussen twee tabellen die veel-op-veel zijn of indirecte verbindingen aanbrengt (een keten met tabelrelaties die een-op-veel zijn binnen elke relatie, maar veel-op-veel wanneer deze end-to-end worden bekeken. Zie Relaties tussen tabellen in een gegevensmodel voor meer informatie.
-
De gegevenstypen in de twee kolommen moeten compatibel zijn. Zie Gegevenstypen in Excel-gegevensmodellen voor meer informatie.
-
Andere manieren om relaties te maken zijn mogelijk intuïtiever, vooral als u niet zeker weet welke kolommen u moet gebruiken. Zie Een relatie maken in de diagramweergave in Power Pivot.
Voorbeeld: time intelligence-gegevens koppelen aan vluchtgegevens van een luchtvaartmaatschappij
U kunt meer leren over tabelrelaties en time intelligence met de gratis gegevens op Microsoft Azure Marketplace. Een aantal van deze gegevenssets is zeer groot en u hebt een snelle internetverbinding nodig om de gegevensdownload binnen een redelijke hoeveelheid tijd te voltooien.
-
Start Power Pivot in Microsoft Excel-invoegtoepassing en open het Power Pivot-venster.
-
Klik op Externe gegevens ophalen > Uit gegevensservice > Uit Microsoft Azure Marketplace. De startpagina van Microsoft Azure Marketplace wordt geopend in de wizard Tabel importeren.
-
Klik onder Prijs op Gratis.
-
Klik onder Categorie op Wetenschap en statistiek.
-
Zoeken naar DateStream en klikt u op abonneren.
-
Geef uw Microsoft-account op en klik op Aanmelden. Er wordt een voorbeeld van de gegevens in het venster weergegeven.
-
Schuif naar onderen en klik op Query selecteren.
-
Klik op Volgende.
-
Kies BasicCalendarUS en klik op Voltooien om de gegevens te importeren. Met een snelle internetverbinding duurt dit ongeveer een minuut. Nadat de gegevensoverdracht is voltooid, moet u een statusrapport zien met 73.414 overgebrachte rijen. Klik op Sluiten.
-
Klik op Externe gegevens ophalen > Uit gegevensservice > Uit Microsoft Azure Marketplace om een tweede gegevensset op te halen.
-
Klik onder Type op Gegevens.
-
Klik onder Prijs op Gratis.
-
Zoek US Air Carrier Flight Delays en klik op Selecteren.
-
Schuif naar onderen en klik op Query selecteren.
-
Klik op Volgende.
-
Klik op Voltooien om de gegevens te importeren. Met een snelle internetverbinding kan de import vijftien minuten in beslag nemen. Nadat de gegevensoverdracht is voltooid, moet u een statusrapport zien met 2.427.284 overgebrachte rijen. Klik op Sluiten. Het gegevensmodel moet nu twee tabellen bevatten. We hebben in elke tabel compatibele kolommen nodig om de tabellen aan elkaar te koppelen.
-
U ziet dat DateKey in BasicCalendarUS de notatie 1/1/2012 12:00:00 AM heeft. De tabel On_Time_Performance heeft ook een datum/tijd-kolom, FlightDate, met waarden die op dezelfde manier zijn genoteerd, namelijk 1/1/2012 12:00:00 AM. De twee kolommen bevatten overeenkomstige gegevens, van hetzelfde gegevenstype, en ten minste een van de kolommen (DateKey) bevat alleen unieke waarden. In de volgende stappen gebruikt u deze kolommen om de tabellen aan elkaar te koppelen.
-
Klik in het Power Pivot-venster op Draaitabel om een draaitabel te maken in een nieuw of bestaand werkblad.
-
Vouw in de veldenlijst On_Time_Performance uit en klik op ArrDelayMinutes om deze toe te voegen aan het onderdeel Waarden. In de draaitabel moet u het totale aantal vertraagde vluchten, gemeten in minuten zien.
-
Vouw BasicCalendarUS uit en klik op MonthInCalendar om deze toe te voegen aan het onderdeel Rijen.
-
U ziet dat in de draaitabel nu maanden worden vermeld, maar dat de som van het totale aantal minuten voor elk maand hetzelfde is. Nogmaals, identieke waarden geven aan dat er een relatie nodig is.
-
Klik in de velden lijst in 'Mogelijk zijn er relaties tussen tabellen nodig' op Maken.
-
Selecteer bij Gerelateerde tabel On_Time_Performance en bij Gerelateerde kolom (Primair) FlightDate.
-
Selecteer bij Tabel BasicCalendarUS en bij Kolom (extern) DateKey. Klik op OK om de relatie te maken.
-
U ziet dat de som van de vertraging in minuten nu voor elke maand verschillend is.
-
Klik in BasicCalendarUS en sleep YearKey naar het onderdeel Rijen, boven MonthInCalendar.
U kunt nu vertraagde aankomsten indelen op jaar en maand, of op andere waarden in de kalender.
Tips: Standaard worden maanden in alfabetische volgorde vermeld. Met behulp van de Power Pivot-invoegtoepassing kunt u zo sorteren dat de maanden in chronologische volgorde worden weergegeven.
-
Controleer of de tabel BasicCalendarUS is geopend in het Power Pivot-venster.
-
Klik in de tabel Home op Sorteren op kolom.
-
Kies bij Sorteren MonthInCalendar
-
Kies bij Op MonthOfYear.
De draaitabel sorteert nu elke maand-jaarcombinatie (oktober 2011, november 2011) op het maandgetal binnen een jaar (10, 11). U kunt de sorteervolgorde gemakkelijk wijzigen omdat de feed DateStream alle benodigde kolommen bevat om dit scenario mogelijk te maken. Als u een andere time intelligence-tabel gebruikt, is de stap anders.
’Mogelijk zijn er relaties tussen tabellen nodig’
Wanneer u velden toevoegt aan een draaitabel, wordt u geïnformeerd dat er een tabelrelatie is vereist om inzicht te krijgen in de velden die u in de draaitabel hebt geselecteerd.
Hoewel u in Excel kunt worden geïnformeerd dat er een relatie nodig is, kunt u niet worden geïnformeerd over de tabellen en kolommen die u moet gebruiken, of dat zelfs een tabelrelatie mogelijk is. Probeer de volgende stappen te volgen om de benodigde antwoorden te krijgen.
Stap 1: bepalen welke tabellen moeten worden opgegeven in de relatie
Als uw model slechts een aantal tabellen bevat, is het mogelijk direct duidelijk welke tabellen u moet gebruiken. Voor grotere modellen kunt u waarschijnlijk wat hulp gebruiken. Eén benadering is het gebruik van de diagramweergave in de Power Pivot-invoegtoepassing. De diagramweergave geeft een visuele voorstelling van alle tabellen in het gegevensmodel. Met de diagramweergave kunt u snel bepalen welke tabellen losstaan van de rest van het model.
Opmerking: Het is mogelijk om dubbelzinnige relaties te maken die ongeldig zijn wanneer ze worden gebruikt in een draaitabel- of Power View-rapport. Stel dat al uw tabellen op een bepaalde manier zijn verwant met andere tabellen in het model, maar wanneer u velden uit verschillende tabellen probeert te combineren, het bericht 'Mogelijk zijn er relaties tussen tabellen nodig' wordt weergegeven. De meest voor de hand liggende oorzaak is de aanwezigheid van een veel-op-veelrelatie. Als u de keten van tabelrelaties volgt die verbinding maken met de tabellen die u gebruikt, ontdekt u mogelijk dat u twee of meer een-op-veel relaties hebt. Er is geen gemakkelijke oplossing die voor elke situatie uitkomst biedt, maar u kunt proberen om berekende kolommen te maken om de kolommen te consolideren die u in één tabel wilt gebruiken.
Stap 2: kolommen zoeken die kunnen worden gebruikt om een pad van de ene tabel naar de volgende te maken
Nadat u de tabel hebt geïdentificeerd die losstaat van de rest van het model, controleert u de kolommen om te bepalen of een andere kolom elders in het model overeenkomstige waarden bevat.
Stel dat u een model hebt met productverkopen op rayon en dat u daarom demografische gegevens wilt importeren om te kijken of er een correlatie is tussen de verkopen en de demografische trends in de verschillende rayons. Aangezien de demografische gegevens afkomstig zijn uit een andere gegevenbron, zijn de tabellen aanvankelijk geïsoleerd van de rest van het model. Voor de integratie van de demografische gegevens met de rest van uw model, moet u een kolom vinden in een van de demografische tabellen die overeenkomt met een kolom die u al gebruikt. Als de demografische gegevens bijvoorbeeld zijn gerangschikt op rayon, en in uw verkoopgegevens wordt aangegeven in welk rayon de verkoop heeft plaatsgevonden, kunt u de twee gegevenssets aan elkaar koppelen door voor de opzoekkolom een gemeenschappelijke kolom te vinden, zoals Provincie, Postcode of Rayon.
Naast overeenkomstige waarden zijn er een aantal aanvullende vereisten voor het maken van een relatie:
-
Gegevenswaarden in de opzoekkolom moeten uniek zijn. Met andere woorden, de kolom mag geen duplicaten bevatten. In een gegevensmodel zijn null-waarden en lege tekenreeksen gelijk aan een lege cel, die een unieke gegevenswaarde vertegenwoordigt. Dit betekent dat er niet meerdere nulwaarden in de opzoekkolom aanwezig mogen zijn.
-
Gegevenstypen van zowel de bronkolom als de opzoekkolom moeten compatibel zijn. Zie Gegevenstypen in gegevensmodellen voor meer informatie over gegevenstypen.
Zie Relaties tussen tabellen in een gegevensmodel voor meer informatie over tabelrelaties.
