Wie mir ein Häkchen den SVERWEIS in Pivot-Auswertungen erspart

Frü­her konn­te ich beim Anle­gen einer Pivot-Aus­wer­tung nur auf die Daten einer ein­zi­gen Tabel­le zugrei­fen. Brauch­te ich Infor­ma­tio­nen aus einer wei­te­ren Tabel­le, muss­te ich bei­de Tabel­len mit­tels SVERWEIS kom­bi­nie­ren. Seit Excel 2013 geht das viel ein­fa­cher – und ganz ohne SVERWEIS!

 

Die Lösung bringt das Daten­mo­dell. Mit sei­ner Hil­fe kann ich belie­big vie­le Tabel­len als Daten­ba­sis für mei­ne Pivot-Aus­wer­tun­gen her­an­zie­hen. Das ist ein­fa­cher und weni­ger feh­ler­an­fäl­lig. Wie leicht die­se Lösung ist, zeigt mein kur­zes Video.

 

Meh­re­re Tabel­len aus­wer­ten ohne SVERWEIS aus Excel 2016/2013: Pivot-Berich­te mit Daten­mo­dell ver­bes­sern von Domi­nik Petri

 

Ihnen juckt es in den Fin­gern und Sie möch­ten es gleich selbst aus­pro­bie­ren? Dann laden Sie sich hier die Bei­spiel­da­tei herunter.

 

Wenn Sie lie­ber lesen anstatt zu schau­en, fin­den Sie eine detail­lier­te Schritt-für-Schritt Anlei­tung in mei­nem Blog­bei­trag auf office-kompetenz.de.

 

In Excel 2016 liefern berechnete Felder endlich richtige Ergebnisse

Wer zusätz­li­che Infor­ma­tio­nen aus einer Pivot-Tabel­le gewin­nen möch­te, kann die erfor­der­li­chen Berech­nun­gen über soge­nann­te berech­ne­te Fel­der rea­li­sie­ren. Die­se sind aller­dings nur bei bestimm­ten Berech­nun­gen ein­setz­bar. Eine Mul­ti­pli­ka­ti­on bei­spiels­wei­se kann unsin­ni­ge Ergeb­nis­se liefern.

 

Hier schaf­fen die leis­tungs­fä­hi­gen DAX-Mea­su­res Abhil­fe. Mit ihnen las­sen sich auch kom­ple­xe Berech­nun­gen kor­rekt durch­füh­ren. Bis Excel 2013 war zur For­mel­er­stel­lung noch das Add-In Power Pivot not­wen­dig. In Excel 2016 ist das Hin­zu­fü­gen von DAX-Mea­su­res auch ohne Power Pivot möglich.

 

Wo die Pro­ble­me berech­ne­ter Fel­dern lie­gen und wie ein­fach die Lösung mit DAX-Mea­su­res ist, zei­ge ich im fol­gen­den Beispiel.

Wei­ter­le­sen „In Excel 2016 lie­fern berech­ne­te Fel­der end­lich rich­ti­ge Ergeb­nis­se“

Bald als Video: Pivot-Berichte revolutionieren mit dem Excel Datenmodell

Wer Daten fle­xi­bel, kom­pakt und mit hoher Aus­sa­ge­kraft aus­wer­ten möch­te, fin­det in Pivot-Tabel­len und Pivot-Dia­gram­men die geeig­ne­ten Hilfsmittel.

 

Pivot-Aus­wer­tun­gen konn­ten bis­her nur aus den Daten einer ein­zi­gen Tabel­le erstellt wer­den. Sind dann wei­te­re Tabel­len hin­zu­ge­kom­men, wur­den die­se per SVERWEIS in die Daten­ba­sis geholt. Das war umständ­lich und feh­ler­an­fäl­lig. Zudem wur­de Excel bei gro­ßen Daten­men­gen spür­bar langsamer.

 

Mit den neu­en Mög­lich­kei­ten der Daten­ana­ly­se in Excel beherr­sche ich die Daten­flut noch bes­ser und ent­lo­cke den Daten mei­ner Kun­den die wirk­lich wich­ti­gen und ent­schei­dungs­re­le­van­ten Informationen.

 

Die revolutionäre Neuerung seit Excel 2013: Das Datenmodell

 

Im Daten­mo­dell füh­re ich Daten aus meh­re­ren Quel­len zusam­men und ver­knüp­fe die Tabel­len mit­ein­an­der wie in einer rela­tio­na­len Daten­bank. Das Daten­mo­dell ist eine Samm­lung von Tabel­len, die mit­ein­an­der in Bezie­hung ste­hen. Es stellt die Basis mei­ner Pivot-Tabel­len und Pivot-Dia­gram­me dar.

 

In mei­nen Excel-Semi­na­ren stel­le ich aber immer wie­der fest, dass vie­len das Daten­mo­dell gänz­lich unbe­kannt ist. Obwohl es seit Excel 2013 jedem zur Ver­fü­gung steht.

 

Grund genug für mich, ab heu­te bei Video2Brain in Graz ein ent­spre­chen­des Video-Trai­ning aufzuzeichnen!

 

Darum geht es in meinem neuen Video

 

Zuerst demons­trie­re ich, wie mir das Daten­mo­dell den umständ­li­chen SVERWEIS erspart und ich statt­des­sen mit nur einem Maus­klick belie­big vie­le Tabel­len als Daten­ba­sis für mei­ne Pivot-Berich­te heranziehe.

 

Auf­bau­end dar­auf zei­ge ich, wel­che neu­en Ana­ly­se­mög­lich­kei­ten sich mit dem Daten­mo­dell eröff­nen: Von der neu­en Aggre­ga­ti­ons­funk­ti­on Dis­kre­te Anzahl über die ein­fa­che Navi­ga­ti­on mit­tels Schnell­ein­blick bis hin zum Erstel­len leis­tungs­fä­hi­ger berech­ne­ter Fel­der, soge­nann­ter Mea­su­res, mit der For­mel­spra­che DAX.

 

Schließ­lich wer­de ich im Video vor­ma­chen, wie sich das Daten­mo­dell mit Hil­fe des Spe­zi­al­werk­zeugs Power Pivot noch ver­fei­nern, die Benut­zer­freund­lich­keit von Ana­ly­sen erhö­hen, die Repor­ter­stel­lung dank Hier­ar­chien schnel­ler erle­di­gen und Pivot-Dia­gram­me noch inter­ak­ti­ver machen lassen.

 

Und natür­lich gibt es auch noch jede Men­ge Tipps & Tricks, die ich in mei­ner täg­li­chen Arbeit mit Excel, dem Daten­mo­dell und Power Pivot gesam­melt habe.

Vereinfachte Verwaltung von Beziehungen im Datenmodell von Excel 2016

In der Office-Insi­der Ver­si­on gibt’s seit ges­tern eini­ge Neue­run­gen. Zwei gefal­len mir dabei beson­ders gut:

 

1. Die Dia­gramm­sicht des Daten­mo­dells lässt sich nun bequem als Bild speichern.
2. Das Dia­log­feld zum Bear­bei­ten von Bezie­hun­gen wur­de deut­lich verbessert.

 

Das sind die Vorteile des neuen Dialogfelds

Bis­her muss­te ich im Dia­log­feld die Spal­ten aus einer Drop­down­lis­te aus­wäh­len. Ohne Kennt­nis der jewei­li­gen Spal­ten­in­hal­te war das oft schwie­rig und führ­te zu fal­schen Beziehungen.

 

Jetzt wer­den die Spal­ten mit Daten ange­zeigt. Das macht das Fin­den pas­sen­der Spal­ten zum Kin­der­spiel. Ein­fach die jewei­li­gen Spal­ten ankli­cken. Fertig.

 

Beziehungen_verwalten_Excel_2016

Das Fin­den pas­sen­der Spal­ten wird dank des neu­en Dia­log­felds deut­lich ein­fa­cher, weil auch die Spal­ten­in­hal­te ange­zeigt werden

 

Alle Neue­run­gen zu Power Pivot in Excel 2016 kön­nen Sie hier nachlesen.

 

Smiley in Office 2016 weg: So lässt sich der Fehler beheben

Zum beque­men Star­ten von Makros über die Sym­bol­leis­te für den Schnell­zu­griff ver­wen­den vie­le Anwen­der ger­ne den Smi­ley. In Excel 2016 ist mir das Lächeln kürz­lich im wahrs­ten Sin­ne des Wor­tes ver­gan­gen: Statt des Smi­leys war in mei­ner Schnell­zu­griffs­leis­te nur ein Kreis zu sehen.

 

Excel_2016_QAT_ohne_Smiley

Anstatt des Smi­leys wird in der Sym­bol­leis­te für den Schnell­zu­griff von Office 2016 ledig­lich ein Kreis angezeigt

Das Design ist schuld

Nach weni­gen Maus­klicks war der Grund für das ver­schwun­de­ne Lächeln gefun­den. Bei den Office-Designs »Bunt« und »Dun­kel­grau« wird aus dem Smi­ley ein Kreis. Nur beim Design »Weiß« wird er kor­rekt angezeigt:

 

Happy_Face_2016

Nur im Office-Design »Weiß« wird der Smi­ley kor­rekt angezeigt

So kehrt das Lächeln in allen Designs zurück

Mir ist das wei­ße Design zu kon­trast­arm und die Arbeit damit auf Dau­er zu anstren­gend. Daher habe ich mir das Lächeln mit fol­gen­dem Trick zurückgeholt:

 

Per Rechts­klick auf die die Schnell­zu­griffs­leis­te las­se ich sie nun unter dem Menü­band anzeigen:

 

Happy_Face_2016_unter_dem_Menüband

Wird die Sym­bol­leis­te für den Schnell­zu­griff unter dem Menü­band ange­zeigt, ist der Smi­ley in allen Office-Designs wie­der sichtbar

Sind noch weitere Schaltflächen betroffen?

Sie haben noch wei­te­re Schalt­flä­chen ent­deckt, die in Office 2016 nicht kor­rekt dar­ge­stellt wer­den? Dann hin­ter­las­sen Sie bit­te einen ent­spre­chen­den Kom­men­tar. Vie­len Dank!

 

Warum erscheint in Excel 2016 der Befehl für Power View nicht?

Dass man Power Query in Excel 2016 ver­geb­lich sucht, hat­te ich in mei­nem vor­an­ge­gan­ge­nen Bei­trag berich­tet. Auch Power View – ein zwei­tes Mit­glied der BI-Fami­lie – scheint in Excel 2016 eben­falls ver­schwun­den zu sein.
In Excel 2013 kann ich über Ein­fü­gen > Berich­te > Power View inter­ak­ti­ve Dash­boards erstellen.
Doch in Excel 2016 klappt es mit die­ser Befehls­fol­ge nicht. Auch an ande­rer Stel­le im Menü­band kann ich die Schalt­flä­che Power View nicht aus­ma­chen. Und das, obwohl ich in Excel 2016 das COM Add-In für Power View extra akti­viert habe. Wei­ter­le­sen „War­um erscheint in Excel 2016 der Befehl für Power View nicht?“

Excel 2016: Wo ist Power Query abgeblieben?

Power Query macht das Impor­tie­ren, Auf­be­rei­ten und Berei­ni­gen von Daten leich­ter und schnel­ler. Daher hat es sich seit sei­ner Beta-Pha­se (damals hieß es noch Data Explo­rer) schnell zu mei­nem Lieb­lings­werk­zeug entwickelt.

Denn mit dem Text­kon­ver­tie­rungs-Assis­tent von Excel stieß ich häu­fig an unüber­wind­ba­re Gren­zen. Ich muss­te dann die Daten müh­sam von Hand nach­be­ar­bei­ten oder erst ein Makro pro­gram­mie­ren oder auf das in die Jah­re gekom­me­ne MS-Query zurückgreifen.

Für Excel 2010 und Excel 2013 gibt es Power Query als kos­ten­lo­se Ergän­zung. In bei­den Ver­sio­nen muss erst ein Add-In instal­liert wer­den, bevor die Befeh­le für den erleich­ter­ten Import aus loka­len Daten­quel­len oder aus dem Web auf der unten gezeig­ten Regis­ter­kar­te ver­füg­bar sind.

Excel 2013 - Registerkarte PowerQuery
Die Regis­ter­kar­te Power Query in Excel 2013

Was sich in Excel 2016 ändert

In Ver­si­on 2016 gehö­ren die leis­tungs­fä­hi­gen Werk­zeu­ge zum Impor­tie­ren, Auf­be­rei­ten und Berei­ni­gen von Daten nun zum Excel-Stan­dard. Sie ste­hen somit allen Anwen­dern sofort zur Ver­fü­gung. Das läs­ti­ge Nach­in­stal­lie­ren entfällt.

Aller­dings ist eine leich­te Umge­wöh­nung erfor­der­lich. Denn Micro­soft ver­wen­det a) nicht mehr den Namen Power Query und hat b) auch die gleich­na­mi­ge Regis­ter­kar­te entfernt.

Die Befeh­le, die mit Power Query in Excel 2010 und 2013 ver­füg­bar waren, sind jetzt auf der Regis­ter­kar­te Daten in der Grup­pe Abru­fen und trans­for­mie­ren zusammengefasst.

Excel 2016 - Power Query offen
Power Query in Excel 2016 auf der Regis­ter­kar­te Daten in der Grup­pe Abru­fen und transformieren

Tipp: So geht der Aufruf der Befehle für den Datenimport schneller von der Hand

Die Grup­pe Abru­fen und trans­for­mie­ren habe ich per Rechts­klick auf den Grup­pen­na­men in mei­ne Sym­bol­leis­te für den Schnell­zu­griff ein­ge­baut. So errei­che ich die Befeh­le für den Daten­im­port jeder­zeit mit nur einem Mausklick!