Excel im betrieblichen Umfeld

Die Kenntnis über das Datum der letzten Aktualisierung ist zwingend notwendig, um Kennzahl richtig interpretieren zu können. Falls ein solches Datum nicht vorhanden ist, kann dieses mit folgendem Trick aus dem Datenmodell bezogen werden: In folgendem Beispiel liegt das Belegdatum der Bestellungen vor. Mittels einer DAX Funktion kann ein measure gebildet werden, welches das letzte Belegdatum ermittelt Mit Hilfe einer CUBE Funktion kann dieses ausgelesen werden: =CUBEWERT("ThisWorkbookDataModel";"[Measures].[letztes_Belegdatum]") Mittels TEXT Funktion kann das Datum im richtigen Fomat dargestellt werden: ="letztes Bestelldatum: " & TEXT(CUBEWERT("ThisWorkbookDataModel";"[Measures].[letztes_Belegdatum]");"tt.MM.jjjj")

Wenn in einem Excel-Tabellenblatt Hyperlinks auf andere Datei-Mappen im Verzeichnis vorhanden sind, kann es zu Problemen kommen, wenn die Datei mit den enthaltenen Links gespeichert und später in einen anderen Ordner verschoben wird. Hyperlinks auf andere Mappen werden von Excel in der Regel als „relative Verweise“ (z. B. Ordnername\Datei.xls) eingefügt. Nach dem Verschieben verlieren diese Links dann ihren Bezug. Daher ist es wichtig, Excel dazu zu bringen, „absolute Verweise“ (z. B. \\Computername\Ordnername\Datei.xls) zu verwenden. Ein Eintrag in den Datei-Eigenschaften zwingt Excel dazu, in der Mappe absolute Hyperlinks zu verwenden. Vorgehensweise: 1        Nach dem Einfügen der Hyperlinks Datei speichern 2        Reiter Datei -> Informationen 3        Eigenschaften -> erweiterte Eigenschaften 4        Im Dialogfenster auf Reiter Zusammenfassung wechseln 5      ...

Um ausgeblendete Zeilen und Spalten je Tabelle wieder einzublenden, kann folgender VBA Code verwendet werden. Hierzu den VBA Code in ein neues Modul der VBA Entwicklungsumgebung kopieren und über Reiter Entwicklungstools -> Makro starten: --- SCHNIPP --- Public Sub Zeilen_Spalten_einblenden()     Dim i As Integer         On Error Resume Next         For i = 1 To ActiveWorkbook.Worksheets.Count                 With ActiveWorkbook             .Worksheets(i).Cells.EntireColumn.Hidden = False             .Worksheets(i).Cells.EntireRow.Hidden = False         End With     Next i         Call MsgBox("Alle Zeilen und Spalten" + vbCrLf + "je Tabelle" + vbCrLf + "sind eingeblendet") End Sub --- SCHNAPP ---

Ausgangslage: Wenn man die Spalten M1, M2, M3 addiert, führen leere oder NULL Werte in der Ausgangstabelle dazu, dass das Ergebnis leer oder NULL ist. Verwendet man indes List.Sum() wird die Zeilensumme korrekt berechnet: = Table.AddColumn(Quelle, "Summe", each List.Sum({[M1],[M2],[M3]})) Dies gilt auch für alle anderen Aggregatfunktionen. Alternative = List.Sum(List.Range(Record.ToList(_),1)) weiterführender link Lern Video

Im Folgenden wird eine Methode mit Power Pivot und den DAX Funktionen PATH(), PATHITEM(), LOOKUPVALUE() beschrieben, um ein Merkmal / Dimension zu hierarchisieren 1 Tabelle 1, Tabelle 2 zum Datenmodell hinzufügen (Power Pivot) 2 Tabelle 1, Spalte hinzufügen DAX Funktion =PATH([Warengruppe];[Parent]) 3 weitere Spalten für Ebenen hinzufügen (hier: Spalten Ebene1, Ebene2, Ebene3, Ebene4) =LOOKUPVALUE(Tabelle2[Warengruppe_Text];Tabelle2[Warengruppe];PATHITEM(Tabelle1[Pfad];1)) Exkurs LOOKUPVALUE() 4 in Diagrammsicht wechseln und neue Hierarchie erstellen erweiterter Ansatz Unschön sind dabei leere (Unter-) Zweige (gelb markiert) Diese können durch folgende Erweiterung des Modells vermieden werden berechnete Spalte anlegen (Name HierarchieTiefe): = Pathlength ( Tabelle2[Pfad] ) 3 neue Measures anlegen: BaumTiefe:=ISFILTERED ( Tabelle2[Ebene1] ) + ISFILTERED ( Tabelle2[Ebene2] )     + ISFILTERED ( Tabelle2[Ebene3] ) + ISFI...

Im Folgenden wird eine Methode mit Power Query beschrieben, wie man ausgehend von Startdatum, Startuhrzeit / Enddatum, Enduhrzeit zeitliche Differenzen (Kalender-, Arbeitstagen, Stunden, Minuten) berechnen kann Wie man optional eine Liste mit Feiertagen erstellen kann sehen Sie hier Language M Code --- SCHNIPP --- let fxArbeitstage = (start as date, end as date, optional Feiertage as list) as number =>         let            Liste_Feiertage = if Feiertage = null then {} else Feiertage,            Liste_Tage = {Number.From(start)..Number.From(end)},            Liste_Differenz  = List.Difference(Liste_Tage, Liste_Feiertage),            Liste_Mod = List.Transform(Liste_Differenz, each Number.Mod(_, 7)),            Liste_Sel = List.Select(Liste_Mod, each _>1),       ...

Im Folgenden wird eine Methode beschrieben, wie man anhand eines Datums ein relatives Datum ableiten kann 1 Liste mit Datumswerten als Abfrage abbilden 2 benutzerdefinierte Spalte hinzufügen, Name [relativer_Monat]    =(Date.Year([Datum])-1)*12+Date.Month([Datum])-((Date.Year(DateTime.LocalNow())-1)*12+ Date.Month(DateTime.LocalNow())) 3 benutzerdefinierte Spalte hinzufügen, Name [Datumsfilter]   =if [relativer_Monat] > 0 then "zukünftig" else if [relativer_Monat] = 0 then "laufender Monat" else if [relativer_Monat] >= -1 then "letzter Monat" else if [relativer_Monat] >= -3 then "letzte 1-3 Monate" else if [relativer_Monat] >= -6 then "letzte 4-6 Monate" else if [relativer_Monat] >= -9 then "letzte 7-9 Monate" else if [relativer_Monat] >= -12 then "letzte 10-12 Monate" else "1 Jahr und länger zurück" weiterführende Informationen siehe hier Lern Video