Rechtstreeks in Excel Voer het interne datamodel van Excel in - dummies

U kunt een combinatie van draaitabellen en Excel-gegevensverbindingen gebruiken om rechtstreeks te communiceren met het interne gegevensmodel, zonder de Power Pivot-invoegtoepassing. Dit is handig als u versies van Excel gebruikt die niet worden geleverd met de Power Pivot-invoegtoepassing, bijvoorbeeld wanneer u Microsoft Office gebruikt, thuis- of kleine zakelijke versies. Elk Excel 2013 en 2016 werkboek wordt geleverd met een intern gegevensmodel.

Stel je voor dat je de tabel Transacties hebt die je hier ziet, en op een ander werkblad heb je een tabel Werknemers die informatie over de werknemers bevat.

Deze tabel toont transacties op personeelsnummer.

Deze tabel geeft informatie over werknemers: voornaam, achternaam en functie.

U moet een analyse maken waarin de omzet per functienaam wordt weergegeven. Dit zou normaal gesproken moeilijk zijn gezien het feit dat de verkoop en functie in twee afzonderlijke tabellen staan. Maar met het interne gegevensmodel kunt u deze eenvoudige stappen volgen:

1. Klik in de gegevenstabel Transacties en start een nieuwe draaitabel door Insert ➪ Pivot Table uit het lint te kiezen.
2. Selecteer in het dialoogvenster Draaitabel maken de optie Deze gegevens toevoegen aan de gegevensmodeloptie.

   

   Wanneer u een nieuwe draaitabel maakt in de tabel Transacties, moet u ervoor zorgen dat u Deze gegevens toevoegen aan het gegevensmodel selecteert.

3. Klik in de gegevenstabel Werknemers en start een nieuwe draaitabel.

   Nogmaals, selecteer de optie Deze gegevens toevoegen aan de gegevensmodeloptie, zoals weergegeven.

   U ziet dat de draaipictogram Maak draaischabel verwijst naar benoemde bereiken. Dat wil zeggen dat elke tabel een specifieke naam kreeg. Wanneer u gegevens toevoegt aan het interne gegevensmodel, is het een goed gebruik om de gegevenstabellen een naam te geven. Op deze manier kunt u eenvoudig uw tabellen herkennen in het interne gegevensmodel. Als u uw tabellen niet een naam geeft, worden ze in het interne gegevensmodel weergegeven als bereik1, bereik2, enzovoort.

   

   Maak een nieuwe draaitabel vanuit de tabel Werknemers en selecteer Deze gegevens toevoegen aan het gegevensmodel.
4. Als u de gegevenstabel een naam wilt geven, markeert u eenvoudig alle gegevens in de tabel en selecteert u vervolgens Formules → Opdracht definiëren op het lint. Voer in het dialoogvenster een naam voor de tabel in. Herhaal dit voor alle andere tabellen.
5. Nadat beide tabellen zijn toegevoegd aan het interne gegevensmodel, opent u de draaitabelveldenlijst en kiest u de ALL selector. Deze stap toont beide bereiken in de veldlijst.

   

   Selecteer ALL in de lijst PivotTable Fields om beide tabellen in het interne gegevensmodel te bekijken.
6. Bouw de draaitabel zoals normaal uit.In dit geval wordt Job_Title in het gebied Rij geplaatst en gaat Sales_Amount naar het gebied Waarden.

   Zoals u hier ziet, herkent Excel onmiddellijk dat u twee tabellen uit het interne gegevensmodel gebruikt en wordt u gevraagd om een ​​relatie tussen deze tabellen te maken. U kunt Excel de relaties tussen uw tabellen automatisch laten detecteren of op de knop Maken klikken. Creëer altijd de relaties zelf, om te voorkomen dat Excel het fout doet.

   

   Wanneer Excel u vraagt, kiest u om de relatie tussen de twee tabellen te maken.
7. Klik op de knop Maken.

   Excel opent het dialoogvenster Relatie maken, hier weergegeven. Daar selecteert u de tabellen en velden die de relatie definiëren. U kunt zien dat de tabel Transacties een veld Sales_Rep bevat. Het is gerelateerd aan de tabel Werknemers via het veld Employee_Number.

Bouw de juiste relatie op met behulp van de vervolgkeuzelijsten Tabel en Kolom.

Nadat u de relatie hebt gemaakt, hebt u één draaitabel die effectief gegevens uit beide tabellen gebruikt om de analyse te maken die u nodig hebt. De volgende afbeelding illustreert dat u met het Excel interne gegevensmodel het doel hebt bereikt om de omzet per functienaam weer te geven.

U heeft uw doel bereikt om de omzet per functietitel weer te geven.

U ziet dat de vervolgkeuzelijst met de rechten rechtsonder Gerelateerde kolom (primair) is. De term primair betekent dat het interne gegevensmodel dit veld uit de bijbehorende tabel gebruikt als de primaire sleutel.

Een primaire sleutel is een veld dat alleen unieke niet-nulwaarden bevat (geen duplicaten of lege cellen). Primaire sleutelvelden zijn nodig in het gegevensmodel om aggregatiefouten en duplicaties te voorkomen. Elke relatie die u maakt, moet een veld hebben dat is aangewezen als de primaire sleutel.

De tabel Werknemers moet alle unieke waarden bevatten in het veld Employee_Number, zonder lege waarden of ongeldige waarden. Dit is de enige manier waarop Excel de integriteit van gegevens kan waarborgen bij het samenvoegen van meerdere tabellen.

Nadat u tabellen hebt toegewezen aan het interne gegevensmodel, moet u mogelijk de relaties tussen de tabellen aanpassen. Als u wijzigingen in de relaties in een intern gegevensmodel wilt aanbrengen, klikt u op het tabblad Gegevens op het lint en selecteert u de opdracht Verhoudingen. Het dialoogvenster Relaties beheren, hier weergegeven, wordt geopend.

In het dialoogvenster Relaties beheren kunt u wijzigingen aanbrengen in de relaties in het interne gegevensmodel.

Hier vindt u de volgende opdrachten:

• Nieuw: Maak een nieuwe relatie tussen twee tabellen in het interne gegevensmodel.
• Bewerken: De geselecteerde relatie wijzigen.
• Activeren: De geselecteerde relatie afdwingen, door Excel te laten nadenken over de relatie bij het aggregeren en analyseren van de gegevens in het interne gegevensmodel.
• Deactiveren: Schakel de geselecteerde relatie uit en zeg Excel om de relatie te negeren bij het aggregeren en analyseren van de gegevens in het interne gegevensmodel.
• Wissen: Verwijder de geselecteerde relatie.