Blog

Excel mit Datenschnitt – Slicer in VBA erstellen

Für interaktive Karten und BI-Systeme ist es einfach direkt in Excel mit Datenschnitt – Slicer in VBA zu arbeiten.

In Zusammenhang mit PowerPivot und externen Datenquellen, z.B. aus CRM-Systemen, ist es einfacher direkt mit den internen Excel-Werkzeugen zu arbeiten, als aufwendige und kostenintensive BI-Systeme anzuschaffen (z.B. MS Dynamics, Qlik, etc.).

Vorschau Lösung: Excel mit Datenschnitt – Slicer in VBA erstellen

Excel mit Datenschnitt - Slicer in VBA erstellen für KPI-Dashboards mit Vertriebsanalysen und Interaktive Weltkarte in Excel

Beispiel KPI-Dashboard in Excel mit Weltkarte und Datenschnitt – Slicer VBA Programmierung

 

Excel mit Datenschnitt – Slicer in VBA erstellen

Mit einfachen VBA Codes lassen sich die Slicer (deutsch: Datenschnitt) Objekte direkt in Excel Tabellen ansprechen und geben dem Anwender die Möglichkeit verschiedene Filter-Kriterien einzusetzen.

Der wesentliche Vorteil beruht darauf, dass sich mehrere Pivot-Tabellen und Datenbanken verknüpft werden können.

Einsatzmöglichkeit: Excel-Karte mit Pivot-Tabelle und Datenschnitt

Excel Weltkarte, Excel Europa-Karte mit Slicer für VBA erstellt

Beispiel: Slicer einsetzen in Excel mit VBA-Code für Weltkarte und Europa-Karte

 

Wie erstelle ich einen Datenschnitt (Slicer) ?

Für den Einsatz eines Excel-Datenschnitt (Slicer) muss die Tabelle als solches in Excel erstellt bzw. von Excel erkannt werden.

Markieren Sie den gewünschten Bereich und wählen Sie unter dem Menü-Punkt „Einfügen“  “Tabelle” aus.

Sie erkennen anhand des abwechselnden Farbverlaufs in den Zeilen, dass die Tabelle als solche erkannt und definiert wurde.

Excel Weltkarte alle Länder mit Datenschnitt und Slicer optionen in VBA

Tabelle definieren in Excel für den einsatz von Slicer-Objekten in VBA

Klicken Sie anschließend auf den je nach Datenaufbereitung (Range) auf Datenschnitt einfügen.


TIPP:

Datenschnitt als Zeitachse für den Einsatz oder für die Einfärbung von Kartenmaterial macht wenig Sinn, da sich die Postleitzahlen-Nummern i.d.R. monatlich ändern können. Ebenso werden i.d.R. jährlich auch die Verwaltungsgebiete (z.B. Landkreise, Gemeinde oder NUTS-Regionen) angepasst.


 

Excel-Datenschnitt-Zeitachse für PowerPivot

 

In unserem Beispiel (Excel-Weltkarte Dashboard für PowerPivot) haben wir die ISO-Liste, das Land, die Produktionseinheit und die Anzahl und zeigen lassen.

Um mehrere Filter im Slicer zu verwenden, halten Sie die <Shift-Taste> gedrückt, dann können Sie gleichzeitig mehrere Einträge auswählen.

Arbeiten mit Datenschnitt in Excel, Filterauswahl mit Länder und SQL

Mehrere Filter verwenden in Excel mit Slicer ohne VBA

 

Excel VBA Slicer ansteuern und programmieren

Für Dashboards und KPI-Systeme, können Sie die Slicer mit VBA-Code ansteuern. Im Objekt-Katalog findet man alle wichtigen Methoden und Eigenschaften.

Wenn Sie einen Slicer markieren, können Sie mit der „rechten“-Maustaste einen individuellen Namen festlegen und ist hilflich für die VBA-Programmierung, wenn mehrere Datenschnitte berücksichtigt werden müssen.

Slicer Umbenennen in Excel - Interaktive Filter-aktionen in VBA

Excel mit Datenschnitt – Slicer in VBA erstellen und umbennen

 

Mit unserem Weltkarten Dashboard würde der VBA-Code wie folgt aussehen:

Dim sc As SlicerCache
 Set sc =.SlicerCaches(“ISO”)

 

Oft werden wir gefragt, wie man feststellen kann, welches Element im Slicer angeklickt wurde.

Der VBA-Code könnte z.B. wie folgt aussehen:

Dim sc As SlicerCache
 Dim sl As SlicerCacheLevel
 Set sc = ActiveWorkbook.SlicerCaches(“ISO”)

For Each sl In sc.SlicerCacheLevels
  Debug.Print “Ebene ” + CStr(sl.Ordinal) + ” –> ” + sl.Name
Next

Wie sieht Änderung in einem Slicer-Filter aus, wenn mehrere Datenschnitte vorkommen?

Die Slicer Aktionen können mit einem Ereignis verbunden werden. Ein On-Klick Ereignis löst jedoch das Workbook_SheetPivotTableUpdate Event aus.

Im Tabellenblatt gibt es ein ähnliches Ereignis, das auf ein PivotTable Update reagiert.

PrivateSub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)
End Sub

 

In unserem Beispiel mit der Excel-Weltkarte und den 4 Slicer Objekten sieht die Variablen-Definition wie folgt

Dim oScISO As SlicerCache
Dim oScLAND As SlicerCache
Dim oScProduktion As SlicerCache
Dim oScUmsatz As SlicerCache

 Dim xSc As SlicerCache
 Dim xPT As PivotTable
 Dim xSi As SlicerItem

 

Welcher Slicer wurde nun gefiltert bzw. angeklickt?

For Each xSc In .SlicerCaches
   For Each xPT In xSC.PivotTables
             If xPT.Name = Target.Name And xPT.Parent.Name = Target.Parent.Name Then
                 If xSc.Name Like "*ISO*" Then
                     Set xSc.ISO = xSc
                 ElseIf xSc.Name Like "*Land*" Then
                     Set oScLAND = xSc
                 ElseIf xSc.Name Like "*Produktion*" Then
                     Set oScProduktion = xSc
                 ElseIf xSc.Name Like "*Umsatz*" Then
                     Set oScUmsatz = xSc
                 End If
                 Exit For
             End If
   Next
   If Not oScISO  Is Nothing And Not oScLAND Is Nothing And Not oScProduktion Is Nothing And Not oScUmsatz Then Exit For
 Next

 

Dies ist nur ein Beispiel, wie man die Datenschnitte und Slicer für Dashboards ansprechen kann.

Grundsätzlich ist unsere Empfehlung, die Slicer mit „Sorgfalt“ einzusetzen, um hier übersichtlich zu bleiben und „Performance“ von Excel oder den Datenbanken nicht zu beinträchtigen.

 

Für weitere Einsatzmöglichkeiten von Slicer, Datenfilter in Excel und VBA-Programmierungen stehen wir gerne zur Verfügung nutzen Sie bitte unser Kontaktformular.

Oder bei Auftragsprogrammierungen und VBA-Schulungen für MS Access, Excel-Addin Karten oder für CRM-Systeme und Datenbanken, wenden Sie sich an unser Beratungsunternehmen stallwanger IT.

 

 

0
  Related Posts
  • No related posts found.
  • GIS Consulting
  • 0