Excel im betrieblichen Umfeld

Die persönliche Arbeitsmappe (PERSONAL.XLSB) ist der ideale zentrale Sammelplatz für benutzerdefinierte Funktionen, welche regelmäßig bei der Arbeit mit Excel benötigt werden zB Kopf- und Fusszeile automatisch erstellen Sie wird im XLSTART Ordner abgelegt und automatisch bei jedem Start von Excel im ausgeblendeten Zustand gestartet. Dadurch stehen allen Arbeitsmappen die benutzerdefinierten Funktionen zur Verfügung. Wie ermittelt man den Speicherort von PERSONAL.XLSB ? Mit Hilfe des Direktfensters der VBA Entwicklungsumgebung (ALT - F11) findet man den Pfad der Datei: Direktfenster öffnen (STRG - G) folgende Anweisung eingeben:    ?Application.StartupPath Pfad kopieren und in der Adressleiste des Win Explorers einfügen

Im Menü DATEN - VERBINDUNGEN - VERKNÜPFUNGEN BEARBEITEN kann man sehen, ob und wenn ja zu welchen Dateien eine Excel-Arbeitsmappe verknüpft ist. Was das Menü leider nicht zeigt, sind die Zellen, in denen diese Verknüpfungen stehen. Diese Lücke kann man mit folgender VBA Funktion schließen: --- BEGINN Code Schnipsel --- Public Sub ExterneVerknuepfungen()     On Error Resume Next       Dim intFarbcode As Long ' RGB-Code der Farbe     Dim shtBlatt As Worksheet ' Blatt     Dim rngZelle As Range     ' Zelle     Dim intZähler As Integer       intFarbcode = 49407 ' leichtes Orange     intZähler = 0       For Each shtBlatt In ActiveWorkbook.Sheets         shtBlatt.Select         For Each rngZelle In shtBlatt.UsedRange             rngZelle.Select             If InStr(rngZelle.Formula, ".xls") > 0 Then                 rngZelle.Interior.Color = intFarbcode                 intZähler = intZähler + 1             End If         Next rngZell

Um Kopf- und Fusszeile nicht manuell für jedes Blatt eintragen zu müssen, kann man folgenden VBA Code verwenden. Im Beispiel wird in der Kopfzeile der Blattname, in der Fusszeile links der Dateipfad sowie Dateiname (Fusszeile rechs) für jedes Blatt in der aktiven Arbeitsmappe eingetragen. Die Inhalte können natürlich individuell angepasst werden. --- BEGINN VBA Code --- Public Sub Kopf_Fusszeile_eintragen() Dim intNSheets As Integer Dim intZähler As Integer For intZähler = 1 To ActiveWorkbook.Worksheets.Count               With Sheets(intZähler).PageSetup                               .LeftHeader = ""                 .CenterHeader = ActiveSheet.Name                 .RightHeader = ""                 .LeftFooter = ActiveWorkbook.Path                 .CenterFooter = ""                 .RightFooter = ActiveWorkbook.Name                   End With           Next intZähler   End Sub --- ENDE VBA Code ---

Es ist in Excel Standard nicht möglich, ein Inhaltsverzeichnis der Blätter / Sheets zu erstellen. Folgender VBA Code schließt diese Lücke, alle Blätter werden aufgelistet und mit Hyperlinks versehen --- BEGINN CODE Schnipsel --- Sub Blattliste() ' Inhaltsverzeichnis (Auflistung Blätter) '     Dim Blatt As Worksheet     For Each Blatt In ActiveWorkbook.Sheets           ' Blattnamen einsetzen mit Hyperlink         ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", _             SubAddress:="'" & Blatt.Name & "'!A1", TextToDisplay:=Blatt.Name           ' Blatt sichtbar oder nicht ?         If Blatt.Visible = -1 Then             ActiveCell.Offset(0, 1) = "sichtbar"         ElseIf Blatt.Visible = 0 Then             ActiveCell.Offset(0, 1) = "unsichtbar"         ElseIf Blatt.Visible = 2 Then             ActiveCell.Offset(0, 1) = "sehr unsichtbar"         Else        

Ein Zahlenformat-Code in Excel kann aus bis zu vier Abschnitten bestehen, zwischen denen jeweils ein Semikolon steht. Die einzelnen Abschnitte definieren jeweils das Format für positive Zahlen, negative Zahlen, Nullwerte und Text Syntax: <POSITIV>;<NEGATIV>;<NULL>;<TEXT> Zusätzlich zum Zahlenformat-Code können noch Farben angegeben werden. (siehe Beispiel Verwendung Kontext Chart  , Uhrzeiten ) Der Name der Farbe wird dabei in eckige Klammern [] und vor den Zahlenformat-Code gesetzt. Die wichtigsten Format-Symbole sind dabei: #  :=   Ziffernplatzhalter Hat eine Zahl mehr Stellen hinter dem Dezimalzeichen als #-Zeichen im Format angegeben wurden, rundet Excel den Wert auf die Anzahl der zur Rechten des Dezimalzeichens angegebenen #-Zeichen. Hat eine Zahl vor dem Dezimalzeichen mehr Stellen als #-Zeichen im Format angegeben, zeigt Excel die zusätzlichen Stellen an. 0 (Null)  :=   Ziffernplatzhalter Es gelten im Wesentlichen die Regeln fü

Die SQL Funktion IN ist nützlich, wenn man zB eine Gruppe von Werten testen / auswerten will. Jedoch existiert eine solche Funktion nicht in der Formelsprache DAX. SQL-Statement := SELECT DISTINCT MaterialgruppeName FROM Materialgruppe WHERE Materialgruppe IN ('Zahnrad', 'Ritzel', 'Steckhuelse' ) Man kann die SQL IN Funktion stattdessen aber mit verschachtelten OR Funktionen i nDAX abbilden CALCULATETABLE (     VALUES ( Materialgruppe[MaterialgruppeName] ),     OR (         OR (             Materialgruppe[MaterialgruppeName] = "Zahnrad",             Materialgruppe[MaterialgruppeName] = "Ritzel"         ),         Materialgruppe[MaterialgruppeName] = "Steckhuelse"     ) ) Als Alternative kann man auch den logischen Operator || für OR verwenden VALUES ( Materialgruppe[MaterialgruppeName] ),     Materialgruppe[MaterialgruppeName] = "Zahnrad"     || Materialgruppe[MaterialgruppeName]= "Ri

Der BLANK Wert ist ein spezieller Wert, welchem man vor allem bei Vergleichen besondere Aufmerksamkeit schenken sollte. BLANK in Power Pivot DAX ist nicht gleichbedeutend mit dem NULL Wert in SQL. Was ist ein BLANK Wert in DAX ? Jede Datentyp außer der BOOLEANsche (TRUE / FALSE) in DAX kann einen BLANK Wert enthalten. BLANK wird zugewiesen, wenn die Datenquelle einen NULL Wert enthält. Wenn man einen DAX Ausdruck verwendet wird ein BLANK Wert immer zu 0 oder Leerstring konvertiert, je nachdem welchen Datentyp der Ausdruck erwartet. Man erhält / erzwingt einen BLANK Wert in DAX, wenn man die DAX BLANK() Funktion verwendet. Die folgende Tabelle zeigt das Ergebnis mehrerer DAX Ausdrücke, die einen BLANK Wert enthalten: Ausdruck Ergebnis BLANK() BLANK BLANK()=0 TRUE BLANK() && TRUE FALSE BLANK || TRUE TRUE BLANK()+1 -1 BLANK()-1 -1 BLANK()/4 BLANK INT(BLANK()) BLANK Vergleiche mi

Vor allem in einem Reporting Szenario mit Excel können folgende Datumsfunktionen sinnvoll eingesetzt werden: siehe auch dynamischen Jahreskalender erstellen Wochenende anhand Datum ermitteln YTD (Year to Date) kurze Einführung in Power Pivot Kalenderwoche anhand Datum ableiten Quartal / Geschäftsjahr anhand Datum ableiten

Lern Video Mittels Datenüberprüfung (Validierung) können Fehleingaben abgefangen und somit verhindert werden. Dynamische Eingaberestriktionen können dabei mit der Kategorie benutzerdefiniert realisiert werden. Reiter Daten -> Datenüberprüfung Will man zB in der Zelle A1 kein Datum in der Zukunft zulassen, folgende Gültigkeitsbedingungen hinterlegen: Wenn nur ein Datum innerhalb des aktuellen Jahres akzeptiert werden soll, folgende Gültigkeitsbedingungen hinterlegen:

Bei einer sehr breiten Tabelle (viele Spalten) ist es zuweilen schwierig auf einen Blick zu erkennen, ob auf eine oder mehrere Spalten ein Filter angewandt wird oder nicht. Abhilfe hierzu kann ein kleiner VBA Code schaffen. Führt man diesen aus, so werden die angewandten Filter in einem Excel Dialogbox ausgegeben: --- START Code Schnipsel Public Sub Tabellenfilter() 'Filterbedingungen einer Tabelle auslesen und zurückgeben  Dim intCol As Integer  Dim intFilter As Integer  Dim rngFilter As Range  Dim strFilter As String  Dim WS As Worksheet  Application.Volatile  Set WS = ActiveSheet  If WS.FilterMode And WS.AutoFilterMode Then     Set rngFilter = WS.AutoFilter.Range         For intCol = 1 To rngFilter.Columns.Count             With WS.AutoFilter.Filters(intCol)                 If .On Then                     If strFilter <> "" Then strFilter = strFilter & vbLf                         strFilter = strFilter & rngFilter.Cells(1, i