Office
Aanmelden

Zelfstudie: draaitabelgegevens analyseren in Excel 2013 aan de hand van een gegevensmodel

In minder dan een uur kunt u in Excel een draaitabelrapport maken waarin gegevens uit meerdere tabellen worden gecombineerd. Het eerste gedeelte van deze zelfstudie gaat over het importeren en verkennen van gegevens. In het tweede gedeelte gebruikt u de invoegtoepassing Power Pivot om het gegevensmodel achter het rapport te verfijnen en leert u hoe u berekeningen, hiërarchieën en optimalisaties voor Power View-rapporten kunt toevoegen.

We gaan eerst enkele gegevens importeren.

  1. Download de voorbeeldgegevens (ContosoV2) voor deze zelfstudie. Zie Voorbeeldgegevens voor zelfstudies over DAX en gegevensmodellen ophalen voor meer informatie. Pak de gegevensbestanden uit en sla ze op een locatie op die gemakkelijk toegankelijk is, zoals de map Downloads of Mijn documenten.

  2. Open een lege werkmap in Excel.

  3. Klik op Gegevens > Externe gegevens ophalen > Uit Access.

  4. Ga naar de map die de bestanden met voorbeeldgegevens bevat en selecteer ContosoVerkoop.

  5. Klik op Openen. Aangezien u verbinding maakt met een databasebestand dat meerdere tabellen bevat, wordt het dialoogvenster Tabel selecteren weergegeven, zodat u kunt kiezen welke tabellen u wilt importeren.

    Dialoogvenster Tabel selecteren

  6. Schakel in Tabel selecteren het selectievakje Selectie van meerdere tabellen inschakelen in.

  7. Kies alle tabellen en klik op OK.

  8. Klik in Gegevens importeren op Draaitabelrapport en klik op OK.

    Notities: 

    • U wist het misschien nog niet, maar u hebt zojuist een gegevensmodel gemaakt. Dit model is een laag voor gegevensintegratie die automatisch wordt gemaakt wanneer u meerdere tabellen tegelijk importeert in een draaitabelrapport of met meerdere tabellen tegelijk werkt.

    • Het model is meestal transparant in Excel, maar u kunt het rechtstreeks bekijken en wijzigen met de invoegtoepassing Power Pivot . In Excel is een gegevensmodel aanwezig als u een verzameling tabellen ziet in de lijst met draaitabelvelden. U kunt op verschillende manieren een model maken. Zie Een gegevensmodel maken in Excel voor meer informatie.

Gegevens onderzoeken met een draaitabel

U kunt de gegevens eenvoudig onderzoeken door velden te slepen naar de gebieden Waarden, Kolommen en Rijen in de lijst met draaitabelvelden.

  1. Schuif omlaag in de veldenlijst totdat u bij FabVerkoop komt.

  2. Klik op Verkoopbedrag. Aangezien het numerieke gegevens betreft, wordt Verkoopbedrag automatisch in het gebied Waarden geplaatst.

  3. Sleep in DimDatum Kalenderjaar naar Kolommen.

  4. Sleep in DimProductsubcategorie NaamProductsubcategorie naar Kolommen.

  5. Sleep in DimProduct Merknaam naar Rijen en plaats het onder de subcategorie.

De draaitabel moet er ongeveer hetzelfde uitzien als in het volgende scherm.

Draaitabel met voorbeeldgegevens

U hebt nu met enkele gemakkelijke stappen een eenvoudige draaitabel gemaakt met velden uit vier verschillende tabellen. Deze taak is zo eenvoudig omdat de relaties tussen de tabellen al bestaan. Omdat de tabelrelaties al aanwezig zijn in de bron en u alle tabellen in één bewerking hebt geïmporteerd, konden die relaties opnieuw worden gecreëerd in het model in Excel.

Stel echter dat de gegevens afkomstig zijn uit verschillende bronnen of later worden geïmporteerd? Gewoonlijk kunt u nieuwe gegevens opnemen door relaties te creëren op basis van overeenstemmende kolommen. In de volgende stap importeert u aanvullende tabellen en leest u meer over de vereisten en stappen voor het maken van nieuwe relaties.

Meer tabellen toevoegen

Als u wilt leren hoe u tabelrelaties instelt, hebt u enkele extra, niet-verbonden tabellen nodig om mee te werken. In deze stap haalt u de resterende gegevens voor deze zelfstudie op door één aanvullend databasebestand te importeren en gegevens uit twee andere werkmappen te plakken.

Productcategorieën toevoegen

  1. Open een nieuw blad in de werkmap. U gaat dit blad gebruiken om aanvullende gegevens in op te slaan.

  2. Klik op Gegevens > Externe gegevens ophalen > Uit Access.

  3. Ga naar de map die de bestanden met voorbeeldgegevens bevat en selecteer Productcategorieën. Klik op Openen.

  4. Selecteer Tabel in Gegevens importeren en klik op OK.

Geografische gegevens toevoegen

  1. Voeg nog een werkmapblad in.

  2. Open het bestand Geography.xlsx met voorbeeldgegevens, plaats de cursor in A1 en druk vervolgens op Ctrl-Shift-End om alle gegevens te selecteren.

  3. Kopieer de gegevens naar het klembord.

  4. Plak de gegevens in het lege blad dat u zojuist hebt toegevoegd.

  5. Klik op Opmaken als tabel en kies een willekeurige stijl. Als u de gegevens indeelt in tabelvorm, kunt u het bestand een naam geven, wat goed van pas komt wanneer u in een latere stap relaties definieert.

  6. Controleer bij Opmaken als tabel of Mijn tabel heeft veldnamen is geselecteerd. Klik op OK.

  7. Geef de tabel de naam Geografie. Typ Geografie bij Tabelnaam in Hulpmiddelen voor tabellen > Ontwerpen.

  8. Sluit Geography.xlsx om het bestand uit uw werkruimte te verwijderen.

Winkelgegevens toevoegen

  • Herhaal de vorige stappen voor het bestand Stores.xlsx en plak de inhoud van het bestand in een leeg blad. Geef de tabel de naam Winkels.

Als het goed is, hebt u nu vier bladen. Blad1 bevat de draaitabel, Blad2 bevat Productcategorieën, Blad3 bevat Geografie en Blad4 bevat Winkels. Omdat u de tijd hebt genomen om elke tabel een naam te geven, is de volgende stap, het maken van relaties, veel eenvoudiger.

Velden uit de zojuist geïmporteerde tabellen gebruiken

U kunt de velden uit de zojuist geïmporteerde tabellen direct gebruiken. Als niet kan worden bepaald hoe een veld moet worden opgenomen in het draaitabelrapport, wordt u gevraagd een tabelrelatie te maken die de nieuwe tabel verbindt met een tabel die al deel uitmaakt van het model.

  1. Klik boven de draaitabelvelden op Alle om de volledige lijst met beschikbare tabellen weer te geven.

  2. Schuif naar de onderkant van de lijst. Hier vindt u de nieuwe tabellen die u zojuist hebt toegevoegd.

  3. Vouw Winkels uit.

  4. Sleep Winkelnaam naar het gebied Filters.

  5. U wordt gevraagd een relatie te maken. Deze melding wordt weergegeven omdat u velden hebt gebruikt uit een tabel die geen relatie heeft met het model.

  6. Klik op Maken om het dialoogvenster Relatie maken.

  7. Kies FabVerkoop in Tabel. De tabel FabVerkoop in de voorbeeldgegevens die u gebruikt, bevat verkoop- en kostengegevens over Contoso, evenals sleutels die verwijzen naar andere tabellen, inclusief winkelcodes die ook aanwezig zijn in het bestand Stores.xlsx dat u in de vorige stap hebt geïmporteerd.

  8. Kies Winkelcode in Kolom (Refererend).

  9. Kies Winkels in Gerelateerde tabel.

  10. Kies Winkelcode in Verwante kolom (Primair).

  11. Klik op OK.

In Excel wordt achter de schermen een gegevensmodel gebouwd dat kan worden gebruikt in de gehele werkmap, in een onbeperkt aantal draaitabellen, draaigrafieken of Power View-rapporten. Essentieel voor dit model zijn tabelrelaties die navigatie- en berekeningspaden bepalen die worden gebruikt in een draaitabelrapport. In de volgende taak gaat u handmatig relaties creëren om een koppeling te maken met de gegevens die u zojuist hebt geïmporteerd.

Relaties toevoegen

U kunt systematisch tabelrelaties maken voor alle nieuwe tabellen die u importeert. Als u de werkmap deelt met collega's, zullen deze prijs stellen op vooraf gedefinieerde relaties aangezien ze de gegevens niet zo goed kennen als u.

Als u handmatig relaties maakt, werkt u met twee tabellen tegelijk. Voor elke tabel moet u kolommen kiezen waaruit Excel kan opmaken hoe gerelateerde rijen in een andere tabel moeten worden opgezocht.

Uw browser biedt geen ondersteuning voor video. Installeer Microsoft Silverlight, Adobe Flash Player of Internet Explorer 9.

Productsubcategorie relateren aan Productcategorie

  1. Klik in Excel op Gegevens > Relaties > Nieuw.

  2. Kies DimProductsubcategorie in Tabel.

  3. Kies Productcategoriecode in Kolom (Refererend).

  4. Kies Table_ProductCategory.accdb in Gerelateerde tabel.

  5. Kies Productcategoriecode in Verwante kolom (Primair).

  6. Klik op OK.

  7. Sluit het dialoogvenster Relaties beheren.

Categorieën toevoegen aan de draaitabel

Het gegevensmodel is bijgewerkt met extra tabellen en relaties, maar deze worden nog niet gebruikt door de draaitabel. In deze taak voegt u Productcategorie toe aan de lijst met draaitabelvelden.

  1. Klik in de draaitabelvelden op Alle om de tabellen in het gegevensmodel weer te geven.

  2. Schuif naar de onderkant van de lijst.

  3. Verwijder Merknaam uit het gebied Rijen.

  4. Vouw Table_DimProductCategories.accdb uit.

  5. Sleep Productcategorienaam naar het gebied Rijen en plaats het boven Productsubcategorie.

  6. Klik in de draaitabelvelden op Actief om te controleren of de tabellen u zojuist hebt gebruikt, nu actief worden gebruikt in de draaitabel.

Controlepunt: overzicht van wat u hebt geleerd

U hebt nu een draaitabel gemaakt die gegevens uit meerdere tabellen bevat, waarvan u er een aantal in een volgende stap hebt geïmporteerd. Om ervoor te zorgen dat dit werkt, hebt u tabelrelaties gemaakt die door Excel worden gebruikt om de rijen aan elkaar te relateren. U hebt geleerd dat kolommen die overeenkomende gegevens bevatten essentieel zijn voor het opzoeken van gerelateerde gegevens. Alle tabellen in de bestanden met voorbeeldgegevens bevatten een kolom die hiervoor kan worden gebruikt.

Hoewel de draaitabel functioneel is, hebt u waarschijnlijk al opgemerkt dat er diverse zaken zijn die nog kunnen worden verbeterd. Het lijkt alsof de lijst met draaitabelvelden een extra tabel (DimEntiteit) en een extra kolom (ETLLoadID) bevat die niet zijn gerelateerd aan Contoso. En we hebben de gegevens uit Geografie nog steeds niet geïntegreerd.

Volgende taken: uw model weergeven en uitbreiden met Power Pivot

In de volgende reeks taken gaat u de Microsoft Office-invoegtoepassing Power Pivot in Microsoft Excel 2013 gebruiken om het model uit te breiden. U zult zien dat het maken van relaties met de diagramweergave van de invoegtoepassing eenvoudiger is. U gaat de invoegtoepassing ook gebruiken om berekeningen en hiërarchieën te maken, items te verbergen die niet in de veldenlijst moeten worden weergegeven en de gegevens te optimaliseren voor extra rapportage.

Opmerking:  De invoegtoepassing Power Pivot in Microsoft Excel 2013 is beschikbaar in Office Professional Plus. Zie De invoegtoepassing Power Pivot in Microsoft Excel 2013 voor meer informatie.

Power Pivot toevoegen aan het lint van Excel door de invoegtoepassing Power Pivot in te schakelen.

  1. Ga naar Bestand > Opties > Invoegtoepassingen.

  2. Klik in het vak Beheren op COM-invoegtoepassingen > Starten.

  1. Schakel het selectievakje Microsoft Office Power Pivot in Microsoft Excel 2013 in en klik op OK.

Het lint bevat nu het tabblad Power Pivot.

Een relatie toevoegen met de diagramweergave in Power Pivot

  1. Klik in Excel op Blad3 om dit het actieve werkblad te maken. Blad3 bevat de tabel Geografie die u eerder hebt geïmporteerd.

  2. Klik op het lint op Power Pivot > Toevoegen aan gegevensmodel. Met deze stap voegt u de tabel Geografie toe aan het model. Hiermee wordt ook de invoegtoepassing Power Pivot geopend, waarmee u de overige stappen in deze taak uitvoert.

  3. In het Power Pivot-venster worden alle tabellen in het model, waaronder Geografie, weergegeven. Klik door een aantal tabellen. In de invoegtoepassing kunt u alle gegevens in het model bekijken.

  4. Klik in het Power Pivot-venster op het tabblad Weergave op Diagramweergave.

  5. Gebruik de schuifregelaar om het formaat van het diagram te wijzigen, zodat alle objecten in het diagram worden weergegeven. U ziet dat twee tabellen niet aan de rest van het diagram zijn gerelateerd: DimEntiteit en Geografie.

  6. Klik met de rechtermuisknop op DimEntiteit en klik op Verwijderen. Deze tabel is artefact van de oorspronkelijke database en is niet nodig in het model.

  7. Zoom in op Geografie, zodat u alle velden kunt bekijken. U kunt de schuifregelaar gebruiken om het tabeldiagram groter maken.

  8. Zoals u ziet, bevat Geografie de kolom Geografiesleutel. Deze kolom bevat waarden die elke rij in de tabel Geografie op unieke wijze identificeren. Laten we eens kijken of andere tabellen in het model deze sleutel ook gebruiken. Als dat het geval is, kunnen we een relatie maken die Geografie verbindt met de rest van het model.

  9. Klik op Zoeken.

  10. Typ Geografiesleutel in Metagegevens zoeken.

  11. Klik meerdere keren op Volgende zoeken. U ziet dat Geografiesleutel wordt vermeld in de tabel Geografie en de tabel Winkels.

  12. Verplaats de tabel Geografie, zodat deze zich naast Winkels bevindt.

  13. Sleep de kolom Geografiesleutel in Winkels naar de kolom Geografiesleutel in Geografie. Power Pivot tekent een lijn tussen de twee kolommen die aangeeft dat er een relatie tussen de kolommen bestaat.

In deze taak hebt u een nieuwe techniek geleerd voor het toevoegen van tabellen en het maken van relaties. U hebt nu een volledig geïntegreerd model, waarin alle tabellen zijn gekoppeld en beschikbaar zijn in de draaitabel in Blad1.

Tip:  In de diagramweergave zijn diverse tabeldiagrammen helemaal uitgevouwen en bevatten ze kolommen zoals ETLLoadID, Laaddatum en Bijwerkdatum. Deze velden zijn artefacten uit de originele Contoso-datawarehouse die zijn toegevoegd ter ondersteuning van gegevensextractie en laadbewerkingen. U hebt ze niet nodig in uw model. U verwijdert ze door het veld te markeren, erop te klikken met de rechtermuisknop en te klikken op Verwijderen .

Een berekende kolom maken

In Power Pivot kunt u DAX (Data Analysis Expressions) gebruiken om berekeningen toe te voegen. In deze taak berekent u de totale winst en voegt u een berekende kolom toe die verwijst naar waarden uit andere tabellen. Daarna leest u hoe u kolomverwijzingen gebruikt om het model te vereenvoudigen.

  1. Ga in het Power Pivot-venster terug naar de gegevensweergave.

  2. Wijzig de naam van de tabel Table_ProductCategories.accdb in een eenvoudiger naam. U verwijst naar deze tabel in de volgende stappen en als u een kortere naam gebruikt, zijn de berekeningen gemakkelijker te lezen. Klik met de rechtermuisknop op de tabelnaam, klik op Naam wijzigen, typ Productcategorieën en druk op Enter.

  3. Selecteer de tabel FabVerkoop.

  4. Klik op Ontwerpen > Kolommen > Toevoegen.

  5. Typ de volgende formule in de formulebalk boven de tabel. Dankzij AutoAanvullen kunt u de volledige namen van kolommen en tabellen typen en worden de beschikbare functies weergegeven. U kunt ook op de kolom klikken, waarna de kolomnaam door Power Pivot wordt toegevoegd aan de formule.

    = [Verkoopbedrag] - [TotaleKosten] - [Retourbedrag]

  6. Nadat u de formule hebt gemaakt, drukt u op Enter om de formule te accepteren.

    Voor alle rijen in de berekende kolom worden waarden ingevuld. Als u omlaag schuift door de tabel, ziet u dat de rijen verschillende waarden voor deze kolom kunnen bevatten, op basis van de gegevens in elke rij.

  7. Wijzig de naam van de kolom door met de rechtermuisknop te klikken op CalculatedColumn1 en Kolomnaam wijzigen te selecteren. Typ Winst en druk op Enter.

  8. Selecteer nu de tabel DimProduct.

  9. Klik op Ontwerpen > Kolommen > Toevoegen.

  10. Typ in de formulebalk boven de tabel de volgende formule.

    = RELATED(Productcategorieën[Productcategorienaam])

    De functie RELATED retourneert een waarde uit een gerelateerde tabel. In dit geval bevat de tabel Productcategorieën de namen van productcategorieën, wat handig is voor de tabel DimProduct wanneer u een hiërarchie bouwt die categoriegegevens bevat. Zie RELATED, functie (DAX) voor meer informatie over deze functie.

  11. Nadat u de formule hebt gemaakt, drukt u op Enter om de formule te accepteren.

    Voor alle rijen in de berekende kolom worden waarden ingevuld. Als u omlaag schuift in de tabel, ziet u dat Productcategorienaam nu in elke rij wordt weergegeven.

  12. Wijzig de naam van de kolom door met de rechtermuisknop te klikken op CalculatedColumn1 en Kolomnaam wijzigen te selecteren. Typ Productcategorie en druk op Enter.

  13. Klik op Ontwerpen > Kolommen > Toevoegen.

  14. Typ de volgende formule op de formulebalk boven de tabel en druk op Enter om de formule te accepteren.

    = RELATED(DimProductsubcategorie[NaamProductsubcategorie])

  15. Wijzig de naam van de kolom door met de rechtermuisknop te klikken op CalculatedColumn1 en Kolomnaam wijzigen te selecteren. Typ Productsubcategorie en druk op Enter.

Een hiërarchie maken

De meeste modellen bevatten gegevens die inherent hiërarchisch zijn. Veelvoorkomende voorbeelden hiervan zijn kalendergegevens, geografische gegevens en productcategorieën. Het maken van hiërarchieën is handig omdat u dan één item (de hiërarchie) naar een rapport kunt slepen en niet dezelfde velden steeds opnieuw hoeft te verzamelen en te ordenen.

  1. Ga in Power Pivot naar de diagramweergave. Breid de tabel DimDatum uit, zodat u alle velden beter kunt bekijken.

  2. Houd Ctrl ingedrukt en klik op de kolommen Kalenderjaar, Kalenderkwartaal en Kalendermaand (u moet omlaag schuiven in de tabel).

  3. Klik met de rechtermuisknop op één van de drie geselecteerde kolommen en klik op Hiërarchie maken. Een bovenliggend knooppunt voor de hiërarchie (Hiërarchie 1) wordt onder aan de tabel geplaatst en de geselecteerde kolommen worden als onderliggende knooppunten onder de hiërarchie gekopieerd.

  4. Typ Datums als de naam voor de nieuwe hiërarchie.

  5. Voeg de kolom FullDateLabel toe aan de hiërarchie. Klik met de rechtermuisknop op FullDateLabel en selecteer Toevoegen aan hiërarchie. Kies Datum. FullDateLabel bevat een volledige datum, inclusief jaar, maand en dag. Controleer of FullDateLabel als laatste wordt weergegeven in de hiërarchie. U hebt nu een hiërarchie met meerdere niveaus die jaar, kwartaal, maand en afzonderlijke kalenderdagen bevat.

  6. Plaats in de diagramweergave de aanwijzer op de tabel DimProduct en klik vervolgens op de knop Hiërarchie maken in de tabelkop. Een leeg bovenliggend knooppunt voor de hiërarchie wordt onderaan de tabel weergegeven.

  7. Typ Productcategorieën als de naam voor de nieuwe hiërarchie.

  8. Sleep de kolommen Productcategorie en Productsubcategorie naar de hiërarchie om de onderliggende knooppunten te maken.

  9. Klik met de rechtermuisknop op Productnaam en selecteer Toevoegen aan hiërarchie. Kies Productcategorieën.

Nu u een aantal manieren kent om een hiërarchie te maken, gaat u deze manieren gebruiken in de draaitabel.

  1. Ga terug naar Excel.

  2. Verwijder in Blad1 (het blad dat de draaitabel bevat) de velden in het gebied Rijen.

  3. Vervang deze velden door de nieuwe hiërarchie Producthiërarchieën in DimProduct.

  4. Vervang op dezelfde manier Kalenderjaar in het gebied Kolommen door de hiërarchie Datums in DimDatum.

Wanneer u nu de gegevens bekijkt, ziet u direct de voordelen van het werken met hiërarchieën. U kunt verschillende gebieden van de draaitabel onafhankelijk van elkaar uitvouwen en sluiten, waardoor u meer controle hebt over het gebruik van de beschikbare ruimte. Bovendien kunt u, door één hiërarchie aan zowel Rijen als Kolommen toe te voegen, rechtstreekse en uitgebreid inzoomen op de gegevens, zonder dat u meerdere velden hoeft te stapelen om een vergelijkbaar effect te krijgen.

Kolommen verbergen

Nu u een hiërarchie van productcategorieën hebt gemaakt en deze in DimProduct hebt geplaatst, hoeft DimProductcategorie of DimProductsubcategorie niet langer in de lijst met draaitabelvelden te worden vermeld. In deze taak leert u hoe u irrelevante tabellen en kolommen kunt verbergen die onnodig ruimte innemen in de lijst met draaitabelvelden. Door de tabellen en kolommen te verbergen, verbetert u de rapportagefunctionaliteit, zonder het model te beïnvloeden dat voor de gegevensrelaties en berekeningen zorgt.

Uw browser biedt geen ondersteuning voor video. Installeer Microsoft Silverlight, Adobe Flash Player of Internet Explorer 9.

U kunt afzonderlijke kolommen, een reeks kolommen of een hele tabel verbergen. De namen van tabellen en kolommen worden grijs weergegeven om aan te geven dat de tabel of kolom wordt verborgen voor rapportclients die gebruikmaken van het model. Verborgen kolommen worden grijs weergegeven in het model om hun status aan te geven, maar blijven zichtbaar in de gegevensweergave zodat u ermee kunt blijven werken.

  1. Controleer in Power Pivot of de gegevensweergave is geselecteerd.

  2. Klik in de tabbladen aan de onderkant met de rechtermuisknop op DimProductSubcategory en selecteer Verbergen voor clienthulpprogramma's.

  3. Herhaal dit voor Productcategorieën.

  4. Open DimProduct.

  5. Klik met de rechtermuisknop op de volgende kolommen en klik op Verbergen voor clienthulpprogramma's:

    • Productcode

    • Productlabel

    • Productsubcategorie

  6. Selecteer meerdere naast elkaar liggende kolommen. Begin met KlasseID en ga door tot Productsubcategorie aan het eind. Klik met de rechtermuisknop om de kolommen te verbergen.

  7. Herhaal dit voor andere tabellen en verwijder id's, sleutels of andere details die u niet in dit rapport wilt gebruiken.

Schakel in Excel terug naar Blad1 met de lijst met draaitabelvelden om het verschil te bekijken. Het aantal tabellen is afgenomen en DimProduct bevat nu alleen die items waarvan het waarschijnlijk is dat u ze zult gebruiken voor verkoopanalyses.

Een Power View-rapport maken

Draaigrafiekrapporten zijn niet het enige rapporttype dat profiteert van een gegevensmodel. U kunt met hetzelfde model dat u zojuist hebt gebouwd een Power View-blad toevoegen om bepaalde indelingen die het model biedt uit te proberen.

  1. Klik in Excel op Invoegen > Power View.

    Opmerking:  Als dit de eerste keer is dat u Power View gebruikt op deze computer, wordt u gevraagd de invoegtoepassing in te schakelen en eerst Silverlight te installeren.

  2. Klik in Power View-velden op de pijl naast de tabel FabVerkoop en klik op Verkoopbedrag.

  3. Klik in Geografie op NaamRegioLand.

  4. Klik op het lint op Kaart.

  5. Er wordt een kaartrapport weergegeven. Sleep een hoek om het formaat te wijzigen. De in grootte variërende blauwe cirkels op de kaart geven de verkoopprestaties voor de verschillende landen of regio's aan.

Optimaliseren voor Power View-rapportages

Door enkele kleine wijzigingen aan te brengen in het model krijgt u intuïtievere antwoorden wanneer u een Power View-rapport ontwerpt. In deze taak voegt u URL's van websites van diverse fabrikanten toe en categoriseert u die gegevens als web-URL, zodat het URL-adres wordt weergegeven als koppeling.

Als eerste stap voegt u URL's toe aan uw werkmap.

  1. Open in Excel een nieuw blad en kopieer de volgende waarden:

URL_Fabrikant

ID_Fabrikant

http://www.contoso.com

Contoso, LTD

http://www.adventure-works.com

Adventure Works

http://www.fabrikam.com

Fabrikam, Inc.

  1. Maak de cellen op als tabel en noem de tabel URL.

  2. Maak een relatie tussen URL en de tabel met de namen van de fabrikanten, DimProduct:

    1. Klik op Gegevens > Relaties. Het dialoogvenster Relatie maken wordt weergegeven.

    2. Klik op Nieuw .

    3. Selecteer DimProduct voor Tabel.

    4. Selecteer Fabrikant voor Kolom.

    5. Selecteer URL voor Gerelateerde tabel.

    6. Selecteer ID_Fabrikant voor Gerelateerde kolom (primair).

Om het resultaat voor en na de aanpassing te kunnen vergelijken start u een nieuw Power View-rapport en voegt u FabVerkoop | Verkoopbedrag, dimProduct | Fabrikant en URL | URL_Fabrikant toe aan het rapport. Zoals u ziet, worden de URL's toegevoegd als statische tekst.

Als u een URL wilt weergeven als actieve hyperlink, is categorisatie vereist. U gebruikt Power Pivot om een kolom te categoriseren.

  1. In Power Pivot opent u URL.

  2. Selecteer URL_Fabrikant.

  3. Klik op Geavanceerd > Rapporteigenschappen > Gegevenscategorie: Niet-gecategoriseerd.

  4. Klik op de pijl-omlaag.

  5. Selecteer Web-URL.

  6. Klik in Excel op Invoegen > Power View.

  7. Selecteer bij de Power View-velden FabVerkoop | Verkoopbedrag, dimProduct | Fabrikant en URL | URL_Fabrikant. Ditmaal worden de URL's weergegeven als hyperlinks.

Andere manieren waarop Power View kan worden geoptimaliseerd, zijn een standaardveldenset voor elke tabel definiëren en eigenschappen instellen die bepalen of rijen met repeterende gegevens worden samengevoegd of afzonderlijk worden vermeld. Zie Standaardveldenset configureren voor Power View-rapporten en Eigenschappen voor tabelgedrag configureren voor Power View-rapporten voor meer informatie.

Berekende velden maken

In de tweede taak, Gegevens onderzoeken met een draaitabel, hebt u geklikt op het veld Verkoopbedrag in de lijst met draaitabelvelden. Aangezien Verkoopbedrag een numerieke kolom is, werd deze automatisch geplaatst in het gebied Waarden van de draaitabel. De som van Verkoopbedrag kon vervolgens worden gebruikt om de verkooptotalen van de toegepaste filters te berekenen. In dit geval werden eerst geen filters toegepast en daarna de filters Kalenderjaar, NaamProductsubcategorie en Merknaam.

In werkelijkheid maakte u een impliciet berekend veld, waarmee u verkoopbedragen uit de tabel FabVerkoop gemakkelijk kunt analyseren met behulp van andere velden zoals productcategorie, regio en datums. Impliciete berekende velden worden door Excel gemaakt wanneer u een veld naar het gebied Waarden sleept of wanneer u klikt op een numeriek veld zoals Verkoopbedrag. Impliciete berekende velden zijn formules die standaard aggregatiefuncties zoals SOM, AANTAL en GEMIDDELDE gebruiken en die automatisch voor u worden gemaakt.

Er zijn ook andere soorten berekende velden. U kunt expliciete berekende velden maken in Power Pivot. In tegenstelling tot een impliciet berekend veld, dat alleen kan worden gebruikt in de draaitabel waarin het is gemaakt, kunnen expliciet berekende velden worden gebruikt in elke draaitabel in de werkmap of door elk rapport dat het gegevensmodel als bron gebruikt. Bij expliciet berekende velden, die zijn gemaakt in Power Pivot, kunt u AutoSom gebruiken om automatisch berekende velden te maken via standaardaggregaties of kunt u uw eigen velden maken met een formule die is gemaakt met DAX (Data Analysis Expressions).

U kunt berekende velden gebruiken om uw gegevens op uitgebreide en krachtige manieren te analyseren. Daarom leert u hier hoe u ze maakt.

Berekende velden maken in Power Pivot is gemakkelijk als u AutoSom gebruikt.

  1. Klik in de tabel FabVerkoop op de kolom Winst.

  2. Klik op Berekeningen > AutoSom. Een nieuw berekend veld genaamd Som van Winst wordt automatisch gemaakt in de cel in het berekeningsgebied direct onder de kolom Winst.

  3. Klik in Excel, op Blad1, in de lijst met velden in FabVerkoop op Som van Winst.

Dat is alles! Meer hoeft u niet te doen om een berekend veld te maken met een standaardaggregatie in Power Pivot. Zoals u ziet, maakt u in slechts enkele minuten het berekende veld SOM van Winst en voegt u dit toe aan de draaitabel, zodat u winst gemakkelijk kunt analyseren, afhankelijk van de toegepaste filters. In dit geval is Som van Winst gefilterd op de hiërarchieën Productcategorie en Datums.

Stel echter dat u een meer gedetailleerde analyse wilt uitvoeren, zoals het aantal verkopen voor een kanaal, product of categorie? Daarvoor moet u nog een berekend veld maken dat het aantal rijen telt, één voor elke verkoop in de tabel FabVerkoop, afhankelijk van de toegepaste filters.

  1. Klik in de tabel FabVerkoop op de kolom Verkoopcode.

  2. Klik bij Berekeningen op de pijl-omlaag onder AutoSom > Aantal.

  3. Wijzig de naam van het nieuwe berekende veld door met de rechtermuisknop op Telling van Verkoopsleutel te klikken en Naam wijzigen te selecteren. Typ Aantal en druk op Enter.

  4. Klik in Excel, op Blad1, in de lijst met velden in FabVerkoop op Aantal.

Een nieuwe kolom, Aantal, wordt toegevoegd aan de draaitabel en vermeldt het aantal verkopen afhankelijk van de toegepaste filters. Net als bij het berekende veld Som van Winst wordt Aantal gefilterd op de hiërarchieën Productcategorie en Datums.

We gaan nog een berekend veld maken, ditmaal een veld dat het percentage van de totale verkoop voor een bepaalde context of een bepaald filter berekend. In tegenstelling tot de vorige berekende velden die u maakte met behulp van AutoSom, voert u ditmaal de formule handmatig in.

  1. Klik in de tabel FabVerkoop op een lege cel in het berekeningsgebied. Tip: de cel linksboven vormt een goed beginpunt voor de berekende velden. U kunt ze dan gemakkelijker terugvinden. U kunt elk berekend veld in het berekeningsgebied verplaatsen.

  2. Gebruik IntelliSense om de volgende formule in de formulebalk te typen: Percentage of All Products:=[Count]/CALCULATE([Count], ALL(DimProduct))

  3. Druk op Enter om de formule te accepteren.

  4. Klik in Excel, op Blad1, in de lijst met velden in FabVerkoop op Percentage van alle producten.

  5. Selecteer in de draaitabel de kolommen Percentage van alle producten.

  6. Klik op het tabblad Start op Getal > Percentage. Gebruik twee decimalen voor de notatie van elke nieuwe kolom.

Dit nieuwe berekende veld berekent het percentage van de totale verkoop voor een bepaalde filtercontext. In dit geval is de filtercontext nog steeds de hiërarchieën Productcategorie en Datums. Zo kunt u bijvoorbeeld zien dat het percentage computers in de totale productverkoop in de loop der jaren is toegenomen.

Formules maken voor zowel berekende kolommen als berekende velden is vrij eenvoudig als u weet hoe u Excel-formules maakt. Maar ongeacht of u wel of niet vertrouwd bent met Excel-formules, leert u de basisbeginselen van DAX-formules beter kennen door de lessen uit te voeren in Snelstartgids: Leer de basisbeginselen van DAX in 30 minuten.

Uw werk opslaan

Sla uw werkmap op zodat u deze kunt gebruiken met andere zelfstudies of om zelf te experimenteren.

Volgende stappen

Hoewel u eenvoudig gegevens uit Excel kunt importeren, is het vaak sneller om de gegevens met de invoegtoepassing Power Pivot te importeren. U kunt de gegevens die u importeert, filteren en kolommen uitsluiten die u niet nodig hebt. U kunt ook kiezen of u een ontwerpfunctie voor query's of een queryopdracht wilt gebruiken om de gegevens op te halen. In de volgende stap leert u meer over de volgende alternatieve benaderingen: Gegevens ophalen uit een gegevensfeed in Power Pivot en Gegevens importeren uit Analysis Services of Power Pivot.

Power View-rapportage is bedoeld om te werken met gegevensmodellen zoals het model dat u zojuist hebt gebouwd. De volgende onderwerpen bieden meer informatie over de uitgebreide gegevensvisualisaties die met Power View beschikbaar zijn in Excel: Power View in Excel 2013 starten en Power View: gegevens zoeken, visualiseren en presenteren.

Probeer uw gegevensmodel te verbeteren om zo betere Power View-rapporten te maken door deze zelfstudie te volgen: Zelfstudie: Uw gegevensmodel optimaliseren voor Power View-rapporten

Uw Office-vaardigheden uitbreiden
Training verkennen
Als eerste nieuwe functies krijgen
Deelnemen aan Office Insiders

Was deze informatie nuttig?

Bedankt voor uw feedback.

Hartelijk dank voor uw feedback! Het lijkt ons een goed idee om u in contact te brengen met een van onze Office-ondersteuningsagents.

×