Kategorie: Datenlisten

Einfacher SVERWEIS – Teil 1

Die Excel-Funktion SVERWEIS (senkrechter Verweis) gehört mit zu den meist genutzten Funktionen in Excel und wird auch in vielen kaufmännischen Lehrgängen behandelt.

Syntax:

=SVERWEIS(Suchkriterium; Matrix; Spaltenindex; Bereich_Verweis)

Beim einfachen SVERWEIS wird geprüft, ob ein Wert (das Suchkriterium) in genau der gesuchten Schreibweise in einer Datenliste (die Matrix) vorkommt. Gesucht wird dabei in Spalte 1 dieser Matrix (dem Suchvektor). Gibt es eine exakte Übereinstimmung (Die exakte Suche muss mit FALSCH definiert werden), kann der Wert einer bestimmten Spalte der Matrix (der Spaltenindex) ausgegeben werden. Alternativ kommt die Meldung #NV.

E797.jpg

Formel in Zelle M9: Das Suchkriterium in Zelle M5 wird in der ersten Spalte (blau) der Matrix B5:J236 gesucht. Da genau das Suchkriterium gefunden werden soll, muss Bereich_Verweis mit FALSCH hinterlegt werden. Das Alter steht dann hier in Spalte 4 der Matrix.

Dieser einfache SVERWEIS hat noch ein paar Schwachstellen:

  • Was passiert, wenn die Spaltensortierung verändert wird?
  • Was passiert, wenn neue Daten unter die Liste kopiert werden?
  • Was passiert, wenn das Suchkriterium nicht gefunden werden kann?
  • Was passiert, wenn das Suchkriterium mehrfach im Suchvektor vorkommt?
  • Was muss man machen, wenn der Suchvektor nicht ganz links in der Matrix steht?
  • Wie muss die Formel umgestellt werden, wenn das Suchkriterium aus zwei oder mehr Zellinhalten erstellt wird?
  • Wie muss die Formel modifiziert werden, wenn es nicht einen, sondern zwei oder mehr Suchvektoren gibt?
  • Was könnte die Fehlerursache dafür sein, wenn z.B. nach der Zahl 10000 gesucht wird, diese zwar im Suchvektor steht aber doch nicht gefunden wird?
  • Was muss man machen, wenn mehrere Treffer möglich sind und diese auch aufgezeigt werden sollen?

Alle diese Punkte werden in den kommenden SVERWEIS-/INDEX-/AGGREGAT-Videos demonstriert.

Hier die Datei zum Download:

E797 SVERWEIS einfach

Hier geht es zum Video:

Videolink: https://youtu.be/qkES78Q6XIw

Viel Spaß beim Nachbasteln
Andreas

 

Excel – Kreuztabelle per VBA in Liste umwandeln

Per VBA soll eine Kreuztabelle in eine strukturierte Liste gewandelt werden.
Der Code steht auch in der Excel-Tabelle zur Verfügung. Wie der Code funktioniert, kann im unten aufgeführten Video betrachtet werden. Wichtig ist allerdings, dass die erste Datenspalte der Kreuztabelle angeklickt wird, ansonsten findet die Umwandlung nicht korrekt statt.

Die Kreuztabelle wurde bewusst in Spalte B aufgehängt, da ich in Spalte A die Angaben zu den zusätzlichen Überschriften eintrage. Man hätte hier natürlich auch ein neues Register ins Menüband integrieren können.

Option Explicit

Sub KreuztabelleListe()
'Erstellt von Andreas Thehos, 2014
'zur Erstellung einer Datenliste ausgehend von kreuztabellierten Daten

Dim wksKreuztabelle As Worksheet 'Kreuztabelle - Ausgangsdaten
Dim wksZieltabelle As Worksheet 'Zieltabelle - wird erzeugt
Dim intStartspalte As Integer 'Spalte der ersten kreuztabellierten DATEN
Dim intAuswertungAntwort As Integer 'Antwort auf Frage der MsgBox
Dim lngLetzteZeile As Long 'Letzte Zeile
Dim intLetzteSpalte As Integer 'Letzte Spalte
Dim j As Long 'Zähler für Zeilen aus Kreuztabelle
Dim k As Long 'Zähler für Zeilen in Zieltabelle
Dim i As Integer 'Zähler für Spalten aus Kreuztabelle
Dim m As Integer 'Zähler für Spaltenwerte

On Error GoTo ErrorHandler

Application.ScreenUpdating = False 'Bildschirmaktualisierung aus
Set wksKreuztabelle = ActiveSheet 'Das aktuelle Blatt wird auf wksKreuztabelle gelegt

'Ermittlung und Prüfung, wo die Daten der Kreuztabelle beginnen
intStartspalte = ActiveCell.Column

intAuswertungAntwort = MsgBox("Kreuztabellierte Daten beginnen in Spalte " & _
intStartspalte - 1 & " der Kreuztabelle?", vbInformation + vbYesNo)

Select Case intAuswertungAntwort
Case vbYes
Case vbNo
MsgBox "Markieren Sie bitte die erste Datenzelle der Kreuztabelle und " & _
"führen Sie das Makro erneut aus.", vbInformation, "Bitte neu Markieren"
Exit Sub
End Select

'Ermittlung der letzten Spalte und Zeile der Kreuztabelle
intLetzteSpalte = wksKreuztabelle.Range("IV1").End(xlToLeft).Column
lngLetzteZeile = wksKreuztabelle.Range("B65536").End(xlUp).Row

Set wksZieltabelle = Sheets.Add(After:=Worksheets(Worksheets.Count))

wksKreuztabelle.Activate
k = 2

'Hier werden die Daten aber Zeile 2 in die Zieltabelle gesetzt
For i = intStartspalte To intLetzteSpalte
For j = 2 To lngLetzteZeile
If wksKreuztabelle.Cells(j, i).Value "" Then
With wksZieltabelle
For m = 2 To intStartspalte - 1
.Cells(k, m - 1).Value = wksKreuztabelle.Cells(j, m).Value
Next m
'Hier wird die Überschriftenspalte der Daten in die vorletzte Spalte geschrieben
.Cells(k, intStartspalte - 1).Value = "'" & wksKreuztabelle.Cells(1, i).Value
'Hier werden die Daten in die letzte Spalte geschrieben
.Cells(k, intStartspalte).Value = wksKreuztabelle.Cells(j, i).Value
k = k + 1
End With
End If
Next j
Next i

'Hier werden die Überschriften der Zieltabelle gesetzt
For m = 2 To intStartspalte - 1
wksZieltabelle.Cells(1, m - 1).Value = wksKreuztabelle.Cells(1, m).Value
Next m

'Es folgen die Überschriften der Daten, die nicht direkt ersichtlich sind
wksZieltabelle.Cells(1, intStartspalte - 1).Value = wksKreuztabelle.Cells(12, 1).Value
wksZieltabelle.Cells(1, intStartspalte).Value = wksKreuztabelle.Cells(15, 1).Value

Set wksZieltabelle = Nothing
Set wksKreuztabelle = Nothing

Application.ScreenUpdating = True
Exit Sub

'Im Falle des Fehlerfalles
ErrorHandler:
MsgBox "Ein Fehler ist aufgetreten."
Application.ScreenUpdating = True
End Sub

Hier ist die XLSM-Datei zur Umwandlung einer Kreuztabelle in eine strukturierte Liste:
Kreuztabellierte_Daten

Hier ist das Video:

Videolink: http://youtu.be/1H-L80AJflU

Excel 2013 – PowerView – Daten auf Karte darstellen

Sobald mir Daten mit geografischen Informationen vorliegen, können diese nicht nur mit Pivot oder PowerPivot ausgewertet werden. Diese geografischen Informationen können auch mit PowerView in Form von Kreisdiagrammen und der Hilfe von BING-Karten visualiert werden.

Über die Angabe von Städtenamen, Bundesländernamen oder Ländern sucht PowerView passende Orte und platziert Mini-Diagramme direkt dort auf der Karte. Es können auch Daten über die Angabe von Längen- und Breiteninformationen platziert werden.

In der folgenden Liste (noch keine Tabelle) befinden sich drei Städtenamen in Spalte B. Sollte diese Tabelle weder als Tabelle formatiert worden sein, noch ins PowerPivot Data Modell übernommen worden sein, kann man bei einfachen Listen auch einfach in die Liste klicken (1) und dann über Einfügen (2) auf PowerView klicken. Hinweis: Ich würde es vorab bevorzugen, wenn die Daten schon vorher als Tabelle formatiert (z.B. mit STRG + T) und über das Register PowerPivot ins Datenmodell übernommen werden.

E374_1

Es öffnet sich das Fenster PowerView. Die Daten werden als Tabelle dargestellt. Die Spalten befinden sich in der Sortierung der Spaltenüberschriften. Zunächst werden alle Daten übernommen. Ein Vorteil gegenüber der ursprünglichen Liste ist noch nicht zu erkennen.

E374_2

Die Liste wird dann ins PowerPivot Data Model übernommen, was man durch einen Klick auf PowerPivot – Verwalten prüfen kann.

E374_3

Die Auswahl der Einträge sollte reduziert werden. Zudem lässt sich die Anordnung der Felder durch Verschieben ändern. Im Register Entwurf steht die Schaltfläche Karte zur Verfügung.

Die ausgewählten Daten werden im Kartenausschnitt dargestellt.

E374_4

Link zum YouTube-Video:

Excel 2013 – CUBEELEMENT und CUBEWERT – MDX

Mit OLAP-Cubes und den Cube-Funktionen CUBEELEMENT und CUBEWERT konnte ich bislang nicht so viel anfangen. Mir fehlten die passenden Daten dazu… dachte ich. Doch über das PowerPivot Data Model kann ich auch diese Funktionen nutzen.

Entweder wurden die als Tabelle formatierten Daten bereits dem PowerPivot Data Model hinzugefügt oder man setzt die Verbindung zur Tabelle während der Erstellung der Pivot-Tabelle.

Um eine Tabelle zunächst ins Data Model zu übernehmen, klicken Sie zunächst in die Datenliste ´(1) und formatieren diese über STRG + T als Tabelle. Anschließend wechseln Sie in das Register PowerPivot (2) und klicken Zu Datenmodell hinzufügen (3).

E373_0

Ist die Tabelle noch nicht im PowerPivot Data Model, klicken Sie im Register Einfügen auf die Befehlsschaltfläche PivotTable (1). Anschließend wählen Sie Externe Datenquelle verwenden (2) – Datenquelle auswählen (3). Im Register Tabelle stehen die im Data Model abgelegten Tabellen und Excel-Tabellen (4) zur Auswahl. Bestätigen Sie mit der Schaltfläche Öffnen (5).

E373_1

Anschließend erzeuge ich eine normale Pivot-Tabelle. Da die Daten über PowerPivot Data Modell gezogen wurden, stehen nun unter PivotTable-Tools Analysieren die OLAP-Tools zur Verfügung. Dort klicken Sie auf In Formeln konvertieren.

E373_2

Anschließend wird die Pivot-Tabelle aufgelöst. Die beiden Funktionen CUBEELEMENT (engl. CUBEMEMBER) und CUBEWERT (engl. CUBEVALUE) verweisen nun direkt auf die Daten im PowerPivot Data Model.

E373_3

Die Struktur der Tabelle ist nun nicht mehr an die Restriktionen der Pivot-Tabelle gebunden. Die Zellen können völlig neu organisiert bzw. verschoben werden.

Zum YouTube-Video:

Excel – Tabellen verbinden – Data Explorer Preview

Das kostenlose COM-Add-In „Data Explorer“ Preview ermöglicht die Verknüpfung von Daten aus unterschiedlichen Datenquellen.

E371_1

Oftmals liegen Daten ohne Verknüpfung untereinander auf getrennten Servern. Liegt mir dann eine monatliche Auswertung vor, kann ich die Daten noch mit den Informationen aus anderen Tabellen anreichern.

E371

Zunächst importiere ich die Datenquellen über den Data Explorer. Anschließend kann im Register Data Explorer oder im Register Query auf Merge gedrückt werden.

Beim Merging wähle ich zunächst die beiden zu verknüpfenden Tabellen aus und wähle anschließend die übereinstimmenden Felder. Die Feldnamen müssen dabei nicht identisch sein.

E371_2

Anschließend stehen die verbundenen Daten über den Eintrag Table zur Verfügung, hier muss noch ausgewählt werden, welche Spalten man aus der zweiten Liste ausgelesen bekommen möchte.

E371_3

Unnötige SVERWEISE können damit verhindert werden. Ändert sich die Struktur der Daten, müssen Tabellen mit Formeln nicht angepasst werden. Änderungen in den Originaldaten führen auch nicht permanent zu Änderungen in der aktuellen Datei. Erst über einen Klick auf Refresh werden alle Infos neu eingelesen. In PowerPivot habe ich zwar auch die Möglichkeit der Verknüpfung von Tabellen. PowerPivot steht allerdings meines Wissens nach bei Excel 2013 nicht für alle Office-Pakete zur Verfügung.

Zum Video:

Excel – Data Explorer Preview – CSV importieren und matchen

Mehrere csv-Dateien (comma separated value-files) werden monatlich aktualisiert und müssen anschließend gemeinsam ausgewertet werden. Das kostenlose Add-In „Data Explorer“ Preview bietet die Möglichkeit, csv-Dateien zu importieren. Diese über den Data Explorer importierten Dateien können dann anschließend kombiniert werden.

Vorab ist aber beim Import der csv-Datei zu beachten, dass die Felder mit identischen Datentypen und der gleichen Spaltenzahl importiert werden.

Spätere Aktualisierungen der csv-Dateien führen über den Schalter Refresh zum erneuten Import und der Aktualisierung der kompletten Tabelle.

Hier bekommt man einen kleinen Einblick in den Aufbau der csv-Dateien. Die Werte sind jeweils mit einem Semikolon von einander getrennt.

E370_1

Im Register Data Explorer findet man unter From File den Punkt From CSV.

E370_2

Anschließend wählt man in einem Datei-Explorer die Datei aus (ohne Bild) und klickt auf Öffnen. Der Import würde hier in nur eine Spalte erfolgen.

E370_3

Mit einem rechten Mausklick auf Column1 kann man einstellen, dass es ein Trennzeichen (Split) gibt. Parallel kann man zudem mitverfolgen, wie die einzelnen Schritte des Imports (Steps) aufgebaut werden. Wenn man rechts auf einen Step klickt, wird in der oberen Befehlszeile der Befehl inklusive Parametern angezeigt.

E370_4

Hier soll bei einem Trennzeichen (Delimiter) in mehrere Spalten getrennt werden. Ich wähle das Semikolon als Trennzeichen.

E370_5

Anschließend klicke ich mit rechts auf den Namen der Abfrage und dann auf Rename. Unter dem Namen dieser Abfrage identifiziere ich später die Tabelle.

E370_6

Der Datentyp des Datumsfeldes wird noch auf Date verändert.

E370_7

Anschließend importiere ich die erste csv-Datei mit dem Schalter Done. Für die zweite Tabelle gehe ich exakt genau nach diesem Muster vor, wähle aber dabei natürlich einen anderen Query-Namen.

Wenn beide Tabellen geladen sind, kann ich im Register Data Explorer oder im Register Query auf Append klicken.

E370_8

Die beiden Tabellen stehen zur Auswahl.

E370_9

Ich kann die Combine-Abfrage noch umbenennen.

E370_9a

Sollten später neuere csv-Dateien unter gleichem Namen im Zielverzeichnis liegen, kann ich über einen Klick auf den Schalter Refresh die Daten erneut einladen.

Zum YouTube-Video:

Excel 2013 – PowerView – Visualisierung von Daten

Ein im Zeitverlauf animiertes Blasendiagramm ist in wenigen Augenblicken erzeugt. Die Daten liegen im Data Model oder über intelligente Tabellen vor? Excel 2013 ist installiert? Perfekt… Im Register Einfügen finden wir die kleine Gruppe Bericht, dort steht einsam und allein PowerView.

Ein animiertes Diagramm in weniger als 60 Sekunden!

Warum nicht? Excel mausert sich immer mehr zu einem Tool, große Datenbestände anzuzapfen und auszuwerten. Die Datenhaltung würde ich natürlich einer Datenbank überlassen.

Das Handling innerhalb von PowerView erstreckt sich über zwei Register. PowerView selbst und daneben das Register Entwurf. Meine ersten Versuche mit PowerView waren gescheitert, da ich zum einen meine Daten nicht als Intelligente Tabelle (Strg + T) formatiert hatte, und später dann auch nicht auf Entwurf geklickt hatte.

Im Video sind meine (fast) ersten Gehversuche mit PowerView dargestellt. Und wenn das meine ersten Versuche sind… was wartet noch auf mich, wenn ich PowerView genauer kennengelernt habe?

Übungsdatei zum Download:
E369_PowerView

Video bei YouTube: