Excel im betrieblichen Umfeld

Lern Video Automatisierung von Datenquellen Transformationen Es ist nicht zwingend notwendig, die mit Power Query bearbeiteten / bereinigten Daten in eine Excel Tabelle Zeile für Zeile zu laden. Man kann alternativ eine Datenverbindung erstellen (Schließen & Laden in) Auf diese Datenverbindung kann man anschließend zB direkt mit einer Pivot Tabelle zugreifen (externe Datenquelle verwenden, Power Query Verbindung). Das kann zB dann sinnvoll sein, wenn man in der Folge keine komplexen Kennzahlen mittels DAX Formeln in Power Pivot erstellen will:

Um Daten mit Pivot weiterbearbeiten oder mit Exel Power Pivot in ein Modell zur Kombination mit anderen Daten überführen zu können, bedarf es einer bestimmten Datenstruktur. Oftmals liegen die Ausgangsdaten aber bereits in einer pivotierten Struktur vor (siehe Beispiel) Für eine weitere  Bearbeitung wird für jede Materialgruppe / Datum Kombination eine Datenzeile benötigt, in diesem Beispiel also 12 (3 Materialgruppen x 4 Datumswerte) Datenzeilen. Mit Power Query ist dies sehr einfach umzusetzen: Tabelle in Abfrage-Editor laden (reiter Power Query -> von Tabelle) Einzelne Datumswerte mit CTRL markieren, rechte Maustaste -> Spalten entpivotieren über "Schließen & Laden" wird die entpivotierte Tabelle in ein Excel Tabellenblatt übernommen und kann weiter mit Pivottabelle oder Power Pivot als Datenquelle (Modell) verarbeitet werden. siehe Power Query Daten pivotieren mehrere Wertspalten

Standardmäßig ist die maximale Anzahl abzurufender Datensätze bei einem DrillDown (OLAP-Drillthrough) auf 1.000 Datensätze beschränkt. Ist dies bei großer Anzahl Datensätze nicht ausreichend, kann man diese Anzahl erhöhen

Nach einem Drill Down (Aufriß) eines measures (Pivottabelle) werden die Spaltenbezeichnungen in der Form Tabellenname.Feldname -> Beispiel [$Tabelle1].[Artikel] in einer neuen Excel Tabelle aufgelistet. Diese Spaltenbezeichnungen sind nicht sehr benutzerfreundlich. Wenn man die Spaltenbezeichnungen markiert und folgendes VBA Snippet anwendet, werden die Spaltenbezeichnung auf die Feldbezeichnungen Feldname -> Artikel reduziert. Public Sub Spalten_Bereinigung_DrillDown() Dim rng As Range For Each rng In Selection     intPunkt = InStr(1, rng.Value, ".") + 2     rng.Value = Mid(rng.Value, intPunkt, Len(rng.Value) - intPunkt) Next End Sub

Ab Excel 2007 (Dateiformat *.xlsx) können neben Berichtsfiltern auch sogenannte Slicer (Datenschnitte) zur Filterung von Pivottabellen zum Einsatz kommen. Kontext Pivottabelle, Tabellentools (Reiter Entwurf), Datenschnitte einfügen. Einem Berichtsfilter sieht man nicht an, welche Daten(elemente) gefiltert wurden, ganz anders bei einem Slicer. Slicer geben bessere, intuitive Einsichten in Daten(elemente), vor allem, wenn mehrere Daten(elemente) ausgewählt / gefiltert werden. Ob Daten(elemente) in der Grundgesamtheit der Kennzahl beinhaltet sind oder nicht kann man intuitiv auf einen Blick erkennen. Mehrfachauswahl von Daten(elementen) kann durch gedrückte STRG-Taste erfolgen. Des Weiteren kann das Format des Slicers angepasst werden (Größe, Position, Layout). Slicer können ebenfalls mit mehreren Pivottabellen verknüpft werden, so dass mit einem Klick auf mehrere Pivottabellen gleichzeitig gefiltert werden. Diese Eigenschaft ist ein Vorteil vor allem beim Erstellen eines ...

Eine (einstufige) Bruttobedarfsrechnung kann sehr einfach mit Excel Funktionalität erstellt werden. Man braucht dazu vorbereitend eine formatierte Tabelle, in welcher man das Endprodukt, dessen geplante Absatzmenge sowie die Komponenten und deren Produktionskoeffizient (mit wieviel Einheiten geht eine Komponente in das Endprodukt ein ?) einträgt. Abschließend bildet man das Produkt aus Absatzmenge und Produktionskoeffizient: Mit Hilfe der Pivottabelle kann man nun einen sogenannten Gozintographen (goes into) erstellen. Dadurch ist man in der Lage, neben der Darstellung des Bruttobedarfs auch eine eventuelle Mehrfachverwendung einer Komponente (hier: Baum, Wasser) zu erkennen. Dazu zieht man in der Pivottabelle das Feld Komponente in die Zeilen, das Feld Endprodukt in die Spalten. Im Wertfeld der Pivottabelle verwendet man das Feld Bruttobedarf (Aggregatfunktion Summe): Wie man schön erkennen kann, werden z.B. Bäume (Mehrfachverwendung) sowohl für Stühle als auch ...

Mit der Formel Klasse CUBE Funktionen (Reiter Formeln -> Funktion einfügen, Kategorie "Cube") können auf Basis eines bestehenden Power Pivot Modells / Datenverbindung gezielt Werte in einem spezifischen Filterkontext adressiert werden. =CUBEELEMENT("PowerPivot Data";"[qry_DLZ_Cockpit].[EK Team].&[ " & B1 & " ]" ) Im obigen Beispiel wird in Abhängigkeit des Zellinhalts in Zelle B1 die jeweilige Ausprägung (Element) des Feldes EK Team aus dem tabellarischen Modell herangezogen. Der erste Parameter der Cube Funktion CUBEELEMENT() steht für die Datenverbindung, der zweite für Tabelle.Feld.Ausprägung (Element) Will man Werte über alle Elemente auswerten (ALL), kann dies mit folgender verschachtelten WENN Funktion erfolgen: =WENN(B1=" All ";CUBEELEMENT("PowerPivot Data";"[qry_DLZ_Cockpit].[EK Team]. [All] ");CUBEELEMENT("PowerPivot Data";"[qry_DLZ_Cockpit].[EK Team].&[...

Deckungsbeitragsrechnung (NettoVK - Bezugspreis) kann mittels Pivottabelle (Kontext Menü Pivot Tabletools -> Analysieren) und berechnete Felder erfolgen. Exkurs Power Pivot : wenn der NettoVK über die Zeit gleichbleibend ist, kann man diesen alternativ in eine weitere Tabelle auslagern (Artikel, NettoVK, Normalisierung von Tabellen) und über Power Pivot eine Relation (1:N) zwischen beiden Tabellen herstellen (Modellierung, DAX Funktion RELATED() ). Auf Basis dieses Modells kann der DB über measures zeilenweise berechnet (Spaltenberechnung) und summiert werden (Feldberechnung). berechnetes Feld über Pivottabelle einfügen (Reiter Analysieren -> Felder, Elemente und Gruppen) Ergebnis: berechnetes Feld DB (Summe von DB) zeigt die Summe der DBs über einzelne Artikel (zeilenweise Berechnung von NettoVK-Bezugspreis) einer Gruppe (=Artikel = Erdbeere, Himbeere, Stachelbeere) an

Mit Pivottechnik und der Matrixfunktion HÄUFIGKEIT(Daten,Klassen) kann eine klassische ABC-Analyse (80%, 15%, 5%) erstellt werden. Hierzu einfach das Wert Feld (im Beispiel Wert je Artikel) zweimal in die Pivot ziehen, das erste Mal mit der Aggregat-Funktion SUMME (Wert zusammenfassen nach), das zweite Mal über die Werteinstellungen auf "Werte anzeigen als -> % von Ergebnis in" ändern. Ergebnis ist der kumulierte Anteil in % Im Folgenden kann die Anzahl der Artikel je Klasse mit der Matrixfunktion HÄUFIGKEIT(Daten, Klassen) ermittelt werden: Pareto Analyse mit Power Pivot