MENU
 
Home
Nieuwsbrief
GC in de media
SiteMap
 
 
Tips en Tricks
Excel
Word
Photoshop
PowerPoint
Surf Tips
Gmail
Android
iPad
Excel ClipTips
 
cursus Office 2016
Access
Excel
Outlook
PowerPoint
Word
 
cursus Office 2013
Excel
PowerPoint
Word
Nieuw in Excel 2013
Nieuw in Word 2013
Nieuw in PowerPoint 2013
 
cursus Office 2010
Access
Nieuw in Excel 2010
Excel automatiseren
Excel
OneNote
Outlook
PowerPoint
Word
 
cursus Office 2007
Access
Excel
Outlook
PowerPoint
Publisher
Visio
Word
 
cursus Office
2000-2002-2003-XP
Access
Excel
PowerPoint
Outlook
Word
 
cursus Photoshop
CC -3D
Animatie
CS6
CS5
CS4
CS2 - CS3
Lightroom 3
Elements 6
Mask Pro
Nik Collection
 
cursus Dreamweaver
CS3
 
cursus Illustrator
CS4
 
cursus Flash
CS4
 
cursus Fireworks
CS4
 
cursus Paint Shop Pro
X en X2
 
cursus Premiere
Elements 7-8
 
cursus Joomla
Joomla 1.5
 
Sociale Netwerk sites
Facebook
LinkedIn
Twitter
 
iPad
Apps
 
Diversen
Celtx
CSS
DropBox
Firefox 3.6
GIMP
Internet Explorer 9
LIME
Linux
OpenSUZE
PREZI
ProShow Producer
YouTube
 
cursus Google
Agenda
Analytics
Gmail
Zoeken
Picasa 3
SketchUp
Chrome
Street View
 
cursus Windows
Live Movie Maker
Windows 10
Windows 8
Windows 7
XP
Vista
 
cursus Office '97
Word
Excel
 
 

 

Tips Excel   go to : Index
       

Tip 196

   
Deze tip werd geschreven door Excel teacher
 

Het gegevensmodel en kubusformules.

Deze tip heeft betrekking op draaitabellen gemaakt met Excel 2010 PowerPivot, Excel 2013 PowerPivot en draaitabellen gemaakt van het Gegevensmodel Excel 2013.
(Opgelet! Voorbeeldbestand werkt alleen in Excel 2013 wegens andere benaming voor het gegevensmodel, Excel 2013 = ThisWorkbookDatamodel, Excel 2010 = “PowerpivotData”.)
Wens je het bestand te downloaden, klik dan hier.
 
De Kubusfuncties
We hebben in Excel 7 kubusfuncties waarvan we in deze tip de functies 1 tot en met 5 van onderstaande lijst gaan verkennen.
  • 1. Kubuset
  • Kubuslid
  • Kubusgerangschiktlid
  • Kubusaantal
  • Kubuswaarde
  • Kubuslideigenschap (olap databases)
  • Kubuskpilid (olap databases)

Om gegevens te analyseren met kubusformules hebben we zogenaamde “Measures” (Berekende metingen) nodig,
deze zijn reeds voorhanden als we kunnen gebruik maken van data afkomstig van Olap databases of als je gebruik maakt van
PowerPivot dan kan je de Measures eerst aanmaken.
In dit voorbeeld hebben we geen data van een olap database en maken we eerst onze measures door een draaitabel te
maken met de data volgens de beschrijving van Excel tip 193 met de velden “SalesAmount” en “SalesQuantity” van de tabel “Verkoopcijfers” naar het gebied “Waarden” (1).

Ter info.
Je kan ook gebruik maken van kubusformules van één enkele tabel, voorwaarde is wel dat de tabel toegevoegd is aan het
gegevensmodel.

 
Vervolgens gaan we op het lint naar “Analyseren/Olap/Converteren naar formules”(2).
 
De functie =KUBUSLID
Selecteren we nu de cel met tekst “Som van SalesAmount” (voorbeeldbestand Blad1 cel E2) dan zie we in de formulebalk
de formule:
=KUBUSLID("ThisWorkbookDataModel";"[Measures].[Som van SalesAmount]")
Voor Excel 2010 ThisWorkbookDataModel = PowerPivotData.
Dit is de kubusfunctie KUBUSLID die drie argumenten heeft:
  • verbinding    Vereist. Een tekenreeks die de naam van de verbinding met de kubus aangeeft.
  • lid_expressie    Vereist. Een tekenreeks die een multidimensionale expressie (MDX) aangeeft die een uniek lid in
    de kubus oplevert. lid_expressie kan ook een tupel zijn, opgegeven als cellenbereik of matrixconstante.
  • bijschrift    Optioneel. Een tekenreeks die in de cel wordt weergegeven in plaats van het bijschrift van de kubus,
    als die is opgegeven.
 
Het eerste argument “Verbinding” is de gegevensbron van waaruit we de gegevens voor onze analyse halen, in ons voorbeeld
is dit het “Gegevensmodel”, in de kubusfunctie moet dit echter ingegeven worden in het Engels nl. “ThisWorkbookDataModel”.
Wanneer we de formules van nul opbouwen dan zult u merken dat dit met een enkele muisklik kan ingegeven worden
 
De tweede argument “Lid expressie” verwijst steeds naar een lid van de kolom in een tabel van het gegevensmodel of een
berekende meting.
 
Het derde parameter “Bijschrift” is een tekenreeks die wordt weergegeven in de plaats van het lid.
 
Wanneer we Excel de draaitabel laten omzetten naar kubusformules wordt automatisch de parameter “Bijschrift” niet ingevuld,
Excel weet immers niet hoe we deze willen noemen.
We kunnen dus de volgende kubusformule aanpassen om een andere tekst weer te geven, we typen voor het laatste sluitende
haakje ;”Som” en drukken Enter. =KUBUSLID("ThisWorkbookDataModel";"[Measures].[Som van SalesAmount]";”Som”) En de formule =KUBUSLID("ThisWorkbookDataModel";"[Measures].[Som van SalesQuantity]") wijzigen we in =KUBUSLID("ThisWorkbookDataModel";"[Measures].[Som van SalesQuantity]";"Aantal") maar zoals vermeld de derde parameter is optioneel, je kan deze dus weglaten.
 
De functie =KUBUSWAARDE
Selecteer nu de onderliggende cel (voorbeeldbestand Blad1 cel E3) dan geeft dit in de formulebalk: =KUBUSWAARDE("ThisWorkbookDataModel";E$2)
De argumenten van KUBUSWAARDE:
  • verbinding Vereist. Een tekenreeks of de naam van de verbinding met de kubus.
  • lid_expressie Optioneel. Een tekenreeks of een multidimensionale expressie (MDX) die een lid of tupel van
    de kubus evalueert. lid_expressie kan daarnaast ook een set zijn die met de functie KUBUSSET is gedefinieerd.
    Gebruik lid_expressie als een slicer om het gedeelte van de kubus te definiëren waarvan de samengestelde waarde wordt geretourneerd. Als er in lid_expressie geen waarde is gedefinieerd, wordt de standaardwaarde van de kubus gebruikt.
 
De kubusfunctie KUBUSWAARDE geeft de waarde aan van een kubuslid en heeft als eerste argument de gegevensbron.
Het tweede argument “Lid expressie” is een verwijzing naar een kubuslid, in ons voorbeeld de cel met KUBUSLID("ThisWorkbookDataModel";"[Measures].[Som van SalesAmount]";”Som”) met andere woorden het resultaat
van de berekende meting “Measures Som van SalesAmount” wordt opgehaald uit het gegevensmodel.
We kunnen de formule om de waarde van Som van SalesAmount op te halen uit het gegevensmodel ook anders formuleren (voorbeeldbestand Blad1 cel B1) en wel als volgt, typ in een willekeurige cel =KUBUSWAARDE(“ en na het typen
van het verschijnt de verwijzing naar de data waaruit we gegevens willen ophalen nl. het Gegevensmodel, dubbelklik op het vak
“ThisWorkbookDataModel” (3) en de formule wordt aangevuld =KUBUSWAARDE("ThisWorkbookDataModel
 
Vervolgens typen we een dubbel aanhalingsteken gevolgd door puntkomma en weer een dubbel aanhalingsteken, er
verschijnt weer een lijst waarin we nu dubbelklikken op [Measures] (4).
 
De formule is nu =KUBUSWAARDE("ThisWorkbookDataModel";"[Measures]
We typen een punt en er verschijnt nogmaals een lijst waarin we nu dubbelkikken op [Measures].[Som van SalesAmount (5).
 
De functie KUBUSSET (voorbeeldbestand blad1 cel B3).
Deze functie heeft 5 argumenten:
  • verbinding Vereist. Een tekenreeks die de naam van de verbinding met de kubus aangeeft.
  • setexpressie Vereist. Een tekenreeks van een setexpressie waarmee een ledenset of tupelset wordt opgehaald.
    setexpressie kan ook een celverwijzing zijn naar een Excel-bereik met een of meer leden, tupels of sets.
  • bijschrift Optioneel. Een tekenreeks die in de cel wordt weergegeven in plaats van het bijschrift van de kubus,
    als die is opgegeven.
  • sorteervolgorde Optioneel. Het type sortering dat moet worden toegepast. Kies uit een van de volgende opties:
    1. De set wordt in de volgorde weergegeven zoals ze in de tabel weergegeven worden. 1: De set wordt in
      oplopend volgorde weergegeven maar volgens een sorteercriterium
    2. Het Product dat het meest verkocht is eerst.
    3. De set wordt in aflopende volgorde weergegeven maar volgens een sorteercriterium bv. Het product dat
      het minst verkocht is eerst.
    4. De set wordt in oplopende alfabetische volgorde gesorteerd.
    5. De set wordt in aflopende alfabetische volgorde gesorteerd.
    6. De set wordt in oplopende natuurlijke volgorde gesorteerd.
    7. De set wordt in oplopende natuurlijke volgorde gesorteerd
  • sorteercriterium Optioneel. Een tekenreeks van de waarde waarop moet worden gesorteerd. Als u bijvoorbeeld
    de stad met de hoogste verkoopcijfers wilt zien, verwijst setexpressie naar de set steden en sorteercriterium
    naar het verkoopresultaat. Of als u de stad met de meeste inwoners wilt zien, verwijst setexpressie naar een
    set steden en sorteercriterium naar het inwonersaantal.
 
Met de functie KUBUSSET kun je de leden van een set opvragen uit een kolom van een tabel, om deze leden weer te
geven maken we gebruik van de kubusformule KUBUSSET in combinatie met MDX (Multidimensional Expressions).
Mdx functie Children geeft alle leden weer van een kolom
Mdx functie Members geeft alle leden weer van een kolom +1, de plus 1 refereert naar ALL d.w.z. Alle leden samen
(dit wordt meer duidelijk wanneer we de waarde van de leden opvragen). We typen in een cel
(voorbeeldbestand blad1 cel B3) de volgende formule =KUBUSSET("ThisWorkbookDataModel";"[ProductSubcategory].[ProductSubcategoryName].Members";
"ProducSubcategory")
we geven voor de sorteervolgorde en het sorteercriterium voorlopig geen waarde in,
deze komen later
aan de orde. Wanneer we voor sorteervolgorde niets ingeven wordt dit door Excel geïnterpreteerd als 0.
 
De functie =KUBUSSETAANTAL
Om te weten uit hoeveel leden een set bestaat nemen we de functie =KUBUSSETAANTAL We typen in een cel
(voorbeeldbestand blad1 cel A3) de formule: =KUBUSSETAANTAL(B3)
B3 verwijst naar de kubusset en geeft het aantal leden van deze met de mdx functie Members (45 leden), hadden
we de mdx functie Children gebruikt dan was het aantal leden één minder dus 44.
 
De functie =KUBUSGERANGSCHIKTLID
We typen in een cel (voorbeeldbestand blad1 cel B3)
=KUBUSGERANGSCHIKTLID ("ThisWorkbookDataModel";$B$3;RIJ(A1))
De verwijzing B3 verwijst naar de kubusset waaruit we de gegevens willen halen
De verwijzing Rij(A1) verwijst naar het eerste lid van de kubusset, Rij(A1) retourneert het rijnummer van de celverwijzing,
dus Rij(A1) is 1. Waarom niet schrijven 1 i.p.v. Rij(A1), wel als we de formule naar beneden kopiëren zal het nummer
automatisch oplopen. (Voorbeeldbestand Blad1 cellen B4 tot en met B48)
 
De volgende functie is een functie die we al gezien hebben nl. =KUBUSWAARDE. We typen in een cel
(voorbeeldbestand blad1 cel C3) de volgend formule:
=KUBUSWAARDE("ThisWorkbookDataModel";
vervolgens selecteren we de eerste lidexpressie die we willen opnemen om de waarde op te halen van het kubuslid,
we klikken in de cel met het Kubuslid die verwijst naar de Measure van dat lid nl Som, dit is in ons voorbeeld cel $E$2
(absolute celverwijzing), vervolgens typen we een puntkomma en klikken we in cel B4.
Dit geeft de waarde (Som) van alle leden van de set ProductSubcategoryName. Vervolgens kopiëren we de formule
naar beneden (voorbeeldbestand Blad1 cellen C4 tot en met C48.
 
Kubusset sorteren volgens sorteervolgorde en criterium (Voorbeeldbestand Blad2 cel B3).
Om de kubusset gesorteerd volgens een criterium weer te geven gebruiken we de functie KUBUSSET waar we
de argumenten sorteervolgorde en sorteercriterium ingeven, de eerste drie argumenten kennen we al nl.
“Verbinding”, “Setexpressie” en “Bijschrift”.
Voor “Setexpressie nemen we de Mdx functie “Children”. Willen we nu bv. de set aflopend sorteren
gebaseerd op de verkoopcijfers dan nemen we voor argument “Sorteervolgorde” het getal 2 en voor
sorteercriterium [Som van SalesAmount],
we kunnen dit zo ingeven in de formule of met een celverwijzing naar het kubuslid =KUBUSLID("ThisWorkbookDataModel";"[Measures].[Som van SalesAmount]";"Som") cel C3.
De formule voor cel B3: =KUBUSSET("ThisWorkbookDataModel";"[ProductSubcategory].
[ProductSubcategoryName].Children"; "ProducSubcategory";2;C3)
 
Top X opvragen (voorbeeldbestand Blad2 cel G3).
Om de top X weer te geven nemen we de functie KUBUSSET in combinatie met twee MDX functies
nl. “TOPCOUNT” en “CHILDREN” en de X waarde (in dit voorbeeld X = 10) en de Measure
(Som van SalesAmount) waarop de top x moet berekend worden.
De functie TOPCOUNT heeft 3 argumenten:
  • De setverwijzing.
  • De X waarde.
  • De numerieke expressie waarop gesorteerd moet worden.
 
=KUBUSSET("ThisWorkbookDataModel";"TOPCOUNT([ProductSubcategory].[ProductSubcategoryName].
Children,10,
([Measures].[Som van SalesAmount]))"
;"ProductSubcategoryName")
De sorteervolgorde en sorteercriterium laten we weg.
In het voorbeeldbestand gebruik ik een celverwijzing voor de X waarde. =KUBUSSET("ThisWorkbookDataModel";"TOPCOUNT([ProductSubcategory].[ProductSubcategoryName].
Children,"&G2&", ([Measures].[Som van SalesAmount]))";"ProductSubcategoryName")
 
Datumfilter (voorbeeldbestand Blad3)
Om te filteren tussen twee datums gebruiken we de volgende formule: =ALS.FOUT(KUBUSWAARDE
("ThisWorkbookDataModel";KUBUSSET("ThisWorkbookDataModel";
"{[Kalender].[DateKey].[1/01/2007]:[Kalender].[DateKey].[5/01/2007]}");"[Measures].
[Som van SalesAmount]") We geven een begindatum en einddatum in gescheiden door
een dubbelpunt en omsluiten de kubusset met gekrulde haakjes {}. In het voorbeeldbestand
gebruik ik een celverwijzing voor de datums en voor de Measures van SalesAmount.
=ALS.FOUT(KUBUSWAARDE("ThisWorkbookDataModel";KUBUSSET("ThisWorkbookDataModel";
"{[Kalender].[DateKey].["&$J$1&"]:[Kalender].[DateKey].["&$J$2&"]}");F1);"")
 
Slicers gebruiken om gegevens te filteren (voorbeeldbestand Blad4).
Voor Excel 2010: de nodige slicers invoegen bij het maken van draaitabel en voor het omzetten naar formules. Voor Excel 2013 hebben we twee mogelijkheden,
mogelijkheid 1: de nodige slicers invoegen bij het maken van draaitabel en voor het omzetten
naar formules.
Mogelijkheid 2: We voegen eerst een slicer in voor ProductSubcategoryName We gaan op het lint naar Invoegen/Slicer. In het dialoogvenster “Bestaande verbindingen” kiezen we voor “Gegevensmodel”(7), “Tabellen in een werkmapgegevensmodel(8)” en klikken op “Openen”.
In het dialoogvenster “Slicer invoegen” kiezen we voor “Alles”, selecteren we
“ProductSubcategoryName” en klikken we OK.
 
Voor de formule KUBUSWAARDE hebben we ingegeven de “Verbinding” en een eerste en tweede lidexpressie nl. de verwijzing naar de cel met Som van SalesAmount (cel C3) en de cel van het kubusgerangschiktlid “Catalog” (cel B4) als bijkomend lidexpressie (lidexpressie3)verwijzen we naar
de slicer “ProductSubcategoryName.
We kunnen eventueel nog meerdere slicers invoegen
bv. Kalender Jaar en opnemen als bijkomende lidexpressies. =KUBUSWAARDE("ThisWorkbookDataModel";C$3;$B4;Slicer_ProductSubcategoryName)
 
Slicerselectie weergeven (voorbeeldbestand Blad5).
Om de geselecteerde items van een slicer weer te geven hebben we verschillende mogelijkheden.
  • Een kubusset definiëren met sortering en sorteercriterium (blad5 cel G8) en vervolgens de items opvragen met kubusgerangschiktlid (Blad4 cellen G9 tot en met G53).
  • Een kubusset definiëren zonder sortering en zonder sorteercriterium (Blad5 cel L8) en vervolgens de items opvragen met kubusgerangschiktlid (Blad4 cel L9 tot en met L53).
  • Items opvragen met kubusgerangschiktlid (Blad5 cel P9 tot en met P53).
 
Formule voor blad5 cel G8:
=KUBUSSET("ThisWorkbookDataModel";Slicer_ProductSubcategoryName;"Som ProductSubcategoryName";2;G3)
Als setexpressie verwijzen we dus naar de slicer “Slicer_ProductSubcategoryName”
 

Op de bladen 6, 7, 8 vind je nog enkele voorbeelden van kubusformules.

Meer weten over MDX: http://msdn.microsoft.com/en-us/library/ms145595.aspx

 
 
GratisCursus en zijn lezers denken aan jullie, denk aan GratisCursus
 
 
 
Index
 
 
 
 

All courses now available in English:
www.swotster.com

copyright © 2012 - Swotster Ltd - Hong Kong - China