Excel im betrieblichen Umfeld

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

Youtube Channel Excel Power BI Ganz still und leise führte Microsoft mit Office 2010 eine Reihe von Excel AddIns ( Power Query , Power Pivot, Power View, Power Map) ein, die ab Office 2013 als Power BI bezeichnet werden. Diese tools können vor allem im Bereich des Self Service BI (Fachbereich beschafft, erstellt, analysiert und visualisiert Kennzahlen eigenständig) sinnvoll eingesetzt werden. Im folgenden soll eine kurzer Auflistung der Funktionen von Excel Power Pivot erfolgen. Ein schematisches Self Service BI Beispiel im Einkaufscontrolling finden Sie auf dem data-science-blog. 1 heterogene Datenquellen anbinden, laden und transformieren siehe auch Power Query (ETL Komponente) verschiedene, verteilte Datei-Formate - Flat files (Text-, CSV-Dateien) - Excel Dateien - Access Tabellen - SQL Server Tabellen - Datenfeeds usw. können mit Excel Power Pivot direkt an ein tabellarisches, In-Memory-Modell angebunden werden. Ändern sich die Dateninhalte der Quelldate

Alternative zu STRG - C (kopieren) und STRG - V (einfügen) mit Einsatz der Maus. siehe auch Artikel zu shortcuts 1. Zeile oder Spalte markieren, die kopiert werden soll. 2. Strg+Umschalt drücken und beide Tasten gedrückt halten. 3. Mauszeiger an den Rand der Markierung bewegen und die linke Maustaste drücken. 4. Während man die Maustaste gedrückt hält und die Maus nach oben/unten bzw. links/rechts bewegt, kann ein grauen Balken bewegt und damit die Position der kopierten Zeile/Spalte bestimmt werden. 5. Strg+Umschalt sowie die linke Maustaste loslassen, um die Kopie einzufügen.

Wenn man 2 Tabellen mit jeweils 2 gleichen Spalten hat (zB Nachnahme, Vorname) und eine 3te Spalte (zB Telefon) von einer Tabelle in eine andere übernehmen will, stellt die Funktion SVERWEIS() keinen adäquaten Lösungsansatz mehr dar. Mit einer Kombination der Funktionen INDEX() und VERGLEICH() ist es dennoch möglich, diese Aufgabe mit Excel Formeln (Matrixformel) zu lösen. =INDEX(Tabelle1!$A$2:$C$5;VERGLEICH(A2&B2;Tabelle1!$A$2:$A$5&Tabelle1!$B$2:$B$5;0);3) wobei gilt: Tabelle 1 Spalte A zB Nachname Tabelle 1 Spalte B zB Vorname Tabelle 1 Spalte C zB Telefon (Wert welcher in Tabelle 2 bei inhaltlicher Gleichheit Spalten A, B übernommen werden soll) Formel in Tabelle 2 Spalte C aufbauen und mit STRG-SHIFT-RETURN (Matrixformel) abschließen und runterkopieren. Ein weiteres Beispiel zu Matrixformeln siehe folgenden Blog Beitrag seit Office 365 -> Microsoft 365 gibt es eine weitere, neue Alternative, Funktion XVERWEIS()

Einsatz von Shortcuts Eine Liste mit shortcuts finden Sie hier persönliche Arbeitsmappe PERSONAL.XLSB häufig verwendete, benutzerdefinierte Funktionen können in der PERSONAL.XSLB verfügbar gemacht werden. formatierte Tabellen verwenden (shortcut STRG - T) formatierte Tabellen machen aus einem statischen Bereich einen dynamischen Bereich. Das bedeutet, neue Spalten und / oder Zeilen (strukturelle Veränderungen) werden automatisch von Excel erkannt und berücksichtigt. Voraussetzung für die Verwendung einer formatierten Tabelle ist ein zusammenhängender Bereich, die Spalten müssen benamt sein. Der Einsatz von formatierten Tabellen ist vor allem dann sinnvoll, wenn auf deren Basis Filter, Pivottabellen und / oder Grafiken erstellt werden sollen. Fehlerbehandlung bei formatierten Tabellen siehe hier Namen für Bereiche vergeben (Reiter Formeln -> Namen vergeben) Namen können als Referenz in Formeln verwendet werden. Sie machen vor allem Berechnungen in Excel für D

Die Anzahl eindeutiger Werte kann mit Hilfe einer sog. Matrixformel ermittelt werden. Matrixformeln werden mit der Tastenkombination STRG-SHIFT-RETURN abgeschlossen. Erkennbar ist eine gültige Matrixformeln an den geschweiften Klammern um die Formel: {=SUMME(1/ZÄHLENWENN(BEREICH;BEREICH))} Alternativ kann die Anzahl der eindeutigen Werte mit Power Pivot unter Einsatz der DAX Formel DISTINCTCOUNT() ermittelt werden.

So wie Datumswerte als Zahlen werden Uhrzeiten als Bruchteile von Zahlen gespeichert. Die Zahl 1 steht dabei für 24 Stunden, 0,5 für 12 Stunden usw. So wird z.B. das Datum 10.07.2015 durch die Zahl 26124, die Uhrzeit 12:00 durch 0,5 repräsentiert. Die Zeitrechnung beginnt für Excel mit dem Tag 01.01.1900 = 1. Überprüfen kann man das, in dem man der Excel Funktion WERT() als Parameter 01.01.1900 übergibt, der Funktionswert ist 1. Standardmäßig wird Excel bei der Aufsummierung von Zeitdauern in Stunden (z.B. 05:00; 10:00; 22:00) nicht erkennen, wenn die Summe größer als 24 Stunden ist. Setzt man um die Stunden des  benutzerdefinierten Stunden-Formats hh:mm eckige Klammern [ hh ] :mm erkennt Excel eine eventuelle Überschreitung von 24 Stunden und rechnet erwartungsgemäß. Umrechnen von Uhrzeiten im Format [hh]:mm:ss in Tage, Stunden, Minuten und Sekunden Alternativen mit Excel Formeln GANZZAHL(), STUNDE(), MINUTE(), ABRUNDEN(), SEKUNDE() GANZZAHL(), REST()

Standardmäßig sind Excel Grafiken statisch, das bedeutet, der Anwender kann z.B. nicht durch die Zeit navigieren (Startpunkt verändern) oder interaktiv Einfluß auf die darzustellende Anzahl der Werte nehmen (strecken, stauchen).  Mit der Funktion BEREICH.VERSCHIEBEN() kann man diese Restriktionen auflösen. Sinnvolle Szenarien finden sich hierzu zB im Reporting (cockpit oder dashboard), um mehrere Grafiken mit einem Klick zu synchronisieren. Vorbereitend wählt man eine Zelle als Start ( 0 = Referenzpunkt; Name Start ), eine andere für die Anzahl der darzustellenden Werte (Name Anzahl_Monate ) aus und  vergibt für diese einen Namen. Dann baut man über den Namensmanager die dynamischen Bereiche auf: Name Zeitachse =BEREICH.VERSCHIEBEN(Tabelle1[[#Kopfzeilen];[Jan]];0;Start;1;Anzahl_Monate) Name Werte =BEREICH.VERSCHIEBEN(Tabelle1[Jan];0;Start;1;Anzahl_Monate) Mit Hilfe dieser Namen wird anschließend die Grafik aufgebaut. Vervollständigt wird das ganze durch Scroll

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

Um eine laufende Summe zu erhalten, einfach die Startzelle mit der Funktionstaste F4 arretieren.

Georg Möller , Auf Kurs bringen ( Wiley Verlag ) Inhalt , Index und Probekapital unter  Georg Möller & Partner  abrufbar. Erscheinungstermin 16.09.2015 Zusätzlich in ausgewählten Buchläden ( Buchhandlung Rupprecht , Moritz und Lux ) in Bad Mergentheim erhältlich. folgen Sie Georg Möller auf XING Ein Fallbeispiel, wie man Kennzahlen aus Rohdaten in entscheidungsrelevante Informationen überführen kann, finden Sie auf dem Data-Science-Blog

Mit folgendem VBA Code kann man Leerzeilen in einer Tabelle entfernen. Mit ALT-F11 in die VBA Entwicklungsumgebung wechseln und folgenden Code Schnipsel einfügen: ---- Bereich ab Public Sub Leerzeilen_löschen() in Zwischenablage kopieren --- Public Sub Leerzeilen_loeschen() Dim lngSpalte As Long Dim rngZelle As Long '** Variabel lngSpalte = Spalte(nindex), die/der auf Leerzeichen geprüft werden soll lngSpalte = 1 For rngZelle = ActiveSheet.Cells(Rows.Count, lngSpalte).End(xlUp).Row To 1 Step -1     If Trim(ActiveSheet.Cells(rngZelle, 1).Value) = "" Then         Rows(rngZelle).Delete shift:=xlUp     End If Next rngZelle End Sub siehe auch Daten bereinigen

Eine Ampel-Steuerung für den Reife- / Zielerreichungsgrad von Aufgaben oder Kennzahlen kann man sehr einfach mit einer sog. bedingten Formatierung realisieren (Reiter Start -> bedingte Formatierung -> Symbolsätze) Über die Funktion "Regeln verwalten" (Reiter Start -> bedingte Formatierung -> Regeln verwalten) den Typ auf Zahl umstellen. Nun kann man anschließend über die Ziffern 3 (grün), 2 (gelb), 1(rot) die Farbe der Ampel steuern Will man die Ziffern nicht anzeigen, einfach die Textfarbe auf Weiß umstellen.

Funktionstasten F1 := Excel Hilfe F2 := Zelle aktivieren F4 (in Formelleiste) := Zellen arettieren (absoluter, relativer Zellbezug) F5 := Menü "Gehe zu" F10 := Menüleiste aktivieren F11 := wenn Datenbereich markiert ist wird ein Standard-Chart erstellt (neue Tabelle) STRG - Taste STRG - 1 Zellen formatieren STRG - . (Punkt) := aktuelles Datum STRG - + := neue Zeile einfügen STRG - BILD AUF, AB := zwischen Tabellenblättern wechseln STRG - C kopieren STRG - F := Suchen und Ersetzen STRG - G := Menü "Gehe zu" STRG - K := Hyperlink einfügen STRG - N := neue Arbeitsmappe STRG - P := Druck Dialog anzeigen STRG - O := Datei öffnen STRG - R := Wert links der markierten Zelle übernehmen STRG - S := Datei speichern STRG - T := formatierte Tabelle erstellen STRG - U := Wert der darüberliegenden, markierten Zelle übernehmen STRG - V := einfügen STRG - ALT- V := einfügen mit Optionen STRG - X := ausschneiden STRG - Z := let

Youtube Channel Excel Power BI Eines der wichtigsten Konzepte bei DAX (Data Analysis Expressions) ist das der berechneten Spalten (Zeilenkonext) und Felder (measures). Des Weiteren unterscheidet man zwischen impliziten und expliziten measures (berechnete Felder). Implizite measures sind im Power Pivot Modell nicht sichtbar, sie werden durch Ziehen eines Feldes in den Wertebereich einer Pivottabelle gebildet. Implizite measures können nur mit den Funktionen Summe, Anzahl, Min, Max, Mittelwert und DistinctCount verwendet werden. Explizite measures müssen selbst im Berechnungsbereich des tabellarischen Modells erstellt werden. berechnete Spalte (row context, current row) Wenn man ein Datenmodell in Power Pivot erstellt kann man einzelne Tabellen um berechnete Spalten erweitern. Der Inhalt wird  Zeile für Zeile berechnet. Man nennt diese auch deshalb berechnete Spalten, um sie von nativen Quellspalten unterscheiden zu können. Wie jede andere Spalte können diese in Berichten ver

Anstatt mehrere IF() >Verschachtelungen mit DAX zu verwenden, kann man alternativ die DAX Funktion SWITCH() verwenden. Der Vorteil gegenüber IF() besteht in einer besseren  Lesbarkeit bei vielen Bedingungen. Verknüpft man das ganze mit der booleanschen Funktion TRUE() können sogar komplexe Szenarien mit DAX umgesetzt werden. Zur Veranschaulichung anhand eines Beispiels aus der Praxis siehe folgenden Blog-Beitrag: Lieferantenbewertung, Kennzahlenbereichen Noten zuweisen

Mittels einer Kombination aus den DAX Funktionen CALCULATE() und FILTER() ist es möglich, mehrere Filterbedingungen auf eine Tabelle anzuwenden. Im gewählten Beispiel ermittelt das measure "Anzahl QABs" (= COUNTROWS() ) die Anzahl der Zeilen, für die gilt: Feld(inhalt) "Status" ist nicht "gelöscht" UND Feld(inhalt) "Fehler" = "JA"

Wenn man zwei Tabellen in Power Pivot in Relation setzen will, müssen die Werte des Primärschlüssel Feldes der einen Tabelle eindeutig sein (uniqueness). Was aber, wenn das bei angebundenen Tabellen nicht der Fall ist ? Wie finde ich die Dubletten mit DAX Funktionen innerhalb meines Power Pivot Modells ?  =COUNTROWS(FILTER(Tabelle1;Tabelle1[Objekt]=EARLIER(Tabelle1[Objekt])))

Manchmal wird z.B. im Lieferanten Controlling gefordert, dass Kennzahlen in bestimmten Wertebereichen Noten oder Punkte zugewiesen werden sollen. Ziel dieser Übung ist es, eine quantitative Größe (Kennzahl) zu qualifizieren (gut - schlecht), um deren Interpretation zu vereinfachen und zu standardisieren. Dies ist zB bei einer Lieferantenbewertung regelmäßig der Fall. Eine Notenzuweisung kann mit den verschachtelten DAX Funktionen SWITCH(), TRUE() und AND() erfolgen. SWITCH() leitet eine Fallunterscheidung ein, TRUE() ist ein booleanscher Wert (JA,NEIN), mit AND() kann man Wertebereiche abgrenzen (Intervalle). Die komplette Funktion (measure) lautet: Note:=SWITCH(TRUE();[Anzahl QAB]=0;1;AND([Anzahl QAB]>=1;[Anzahl QAB]<=2);2;AND([Anzahl QAB]>2;[Anzahl QAB]<=4);3;6) Erläuterung: Anzahl QAB = 0 := Note 1 Anzahl QAB größer 1 und kleiner 2 := Note 2 Anzahl QAB >2 und kleiner 4 := Note 3 Anzahl > 4 := Note 6

"Power Pivot erlaubt das blitzschnelle Analysieren von Tabellen mit vielen Millionen Zeilen" Diese Aussage trifft zu, wenn man die 64 Bit Version von Power Pivot verwendet und mehr als 8 GB Arbeitsspeicher zur Verfügung hat. Oftmals läßt die Umgebung aber nur das Arbeiten mit der 32 Bit Version zu. Unabhängig davon, wieviel Arbeitsspeicher zur Verfügung steht, kann Power Pivot in solchen Fällen nur mit einem Bruchteil des Speichers arbeiten. Der Import / Laden der Daten wird daher oftmals mit Fehlermeldungen abgebrochen. "Memory error: Allocation failure : Not enough storage is available to process this command. . If using a 32-bit version of the product, consider upgrading to the 64-bit version or increasing the amount of memory available on the machine."  (Excel 2010, englisch) "Die Verbindung konnte nicht aktualisiert werden. Rufen Sie die vorhandenen Verbindungen auf, und überprüfen Sie, ob eine Verbindung mit der Datei bzw. dem Server b