Kategorie: Datenlisten

Von der API bis zur Python-Grafik in Excel

Grafik zu einem Excel-Tutorial über Live-Daten per API mit Schritten für API, Power Query, Verbindung und Python

Du kennst das: Die Daten liegen in irgendeiner Web-Anwendung, und wer sie in Excel auswerten will, klickt auf Exportieren, speichert eine CSV-Datei, öffnet sie, kopiert, formatiert – und nächste Woche das Ganze von vorn. Die eigentliche Frage ist aber eine andere: Wie bekomme ich die Daten so in Excel, dass ein Klick auf „Alle aktualisieren“ reicht – und die Auswertung samt Grafik sich danach von selbst erneuert?

Genau das zeige ich Dir in diesem Artikel und im zugehörigen YouTubeVideo. Als Beispiel dient mein digitales Bücherregal – eine kleine Web-App, in der ich meine Bücher per ISBN erfasse. Aktuell stehen dort 148 Bücher im Bestand und 9 auf der Wunschliste. Die App bietet eine Excel-Schnittstelle: einen persönlichen Link, der den Bestand als Datenfeed ausliefert.

Der Weg dorthin besteht aus fünf Schritten:

  1. Die API-Schnittstelle in Excel einbinden
  2. Die Daten im ETL-Tool Power Query aufbereiten
  3. Das Ergebnis nur als Verbindung laden
  4. Die Daten mit Python in Excel auswerten
  5. Eine Grafik mit Python erstellen

Das Prinzip funktioniert mit jeder Anwendung, die ihre Daten über eine URL bereitstellt – egal ob Shop-System, Ticket-Tool oder Zeiterfassung.

Flussdiagramm zeigt den Datenweg von der Lovable-App über API, Power Query und Python-Auswertung bis zur Python-Grafik in Excel.

Was du brauchst

  • Microsoft 365 mit einer aktuellen Excel-Version (Desktop)
  • Python in Excel – im Menüband unter Formeln sollte die Gruppe Python mit der Schaltfläche Python einfügen zu sehen sein
  • Einen API-Link Deiner Anwendung (meist im Profil oder in den Einstellungen unter „Schnittstelle“, „API“ oder „Export“ zu finden)
  • Optional: Copilot in Excel, wenn Du Dir den Python-Code für die Grafik erzeugen lassen möchtest

Wichtig: Python in Excel läuft nicht auf Deinem Rechner, sondern in der Microsoft-Cloud. Das ist für die meisten Auswertungen kein Problem, sollte aber bei sensiblen Daten bedacht werden.

Schritt 1: Die API-Schnittstelle in Excel einbinden

In meiner Bücher-App finde ich die Schnittstelle unter Mein Profil → Excel-Schnittstelle. Dort steht sogar schon die Anleitung: Daten → Daten abrufen → Aus dem Web, Link einfügen. Darunter liegt der persönliche Link. Er sieht ungefähr so aus:

https://isbn-bookkeeper.lovable.app/api/public/inventory-feed?key=DEIN_SCHLÜSSEL

Hier nimmst Du natürlich Deinen eigenen API-Schlüssel zu Deiner Datenbank! Wenn Du Lovable noch nicht kennst oder dort noch keinen Account hast, guck doch mal rein. Über meinen Einladungslink https://lovable.dev/invite/HV9NO8L profitiere ich auch ein wenig davon.

Der Teil hinter key= ist Dein API-Schlüssel. Mit ihm kann jeder Deine Daten abrufen, der den Link kennt. Behandle ihn also wie ein Kennwort: nicht in Screenshots zeigen, nicht per Mail verschicken. Gute Anwendungen bieten – wie hier – die Schaltflächen Neuen Schlüssel erzeugen und Schlüssel widerrufen. Damit ist ein versehentlich geteilter Link sofort wertlos.

So bindest Du den Link ein:

  1. Klicke auf Link kopieren.
  2. Wechsle in eine leere Excel-Mappe und wähle Daten → Aus dem Web (Gruppe Daten abrufen und transformieren).
  3. Füge den Link ein und bestätige mit OK.

Excel erkennt das Format selbst. In meinem Fall liefert der Feed eine Textdatei mit Unicode (UTF-8) als Dateiursprung und Semikolon als Trennzeichen. Im Vorschaufenster siehst Du bereits Titel, Autor, Verlag und Erscheinungsjahr.

Jetzt kommt die wichtigste Entscheidung des ganzen Tutorials: Klicke nicht auf „Laden“, sondern auf „Daten transformieren“. Damit landest Du im Power-Query-Editor – und genau dort wollen wir hin.

Schritt 2: Die Daten in Power Query aufbereiten

Power Query ist das ETL-Werkzeug in Excel: Extract (Daten holen), Transform (aufbereiten), Load (laden). Das Schöne daran: Jeder Klick wird als angewendeter Schritt gespeichert. Beim nächsten Aktualisieren läuft die komplette Kette automatisch noch einmal ab – Du musst nichts wiederholen.

Der Feed liefert 17 Spalten und 157 Zeilen. Zwei Schritte hat Power Query bereits selbst erledigt: Höher gestufte Header (die erste Zeile wird zur Überschrift) und Geänderter Typ (Text, Zahl usw.). Den Rest machen wir von Hand.

Leere Seitenzahlen ersetzen

In der Spalte Seiten stehen bei einigen Büchern keine Werte, sondern null. Markiere die Spalte und wähle Start → Werte ersetzen. Als zu suchenden Wert gibst Du null ein, als Ersatz 0. In der Bearbeitungsleiste steht danach:

= Table.ReplaceValue(#"Geänderter Typ",null,0,Replacer.ReplaceValue,{"Seiten"})

Nur den Bestand behalten

Der Feed enthält auch die Bücher auf meiner Wunschliste. Die habe ich (noch) nicht im Regal, also fliegen sie raus: Klicke auf den Filterpfeil der Spalte Wunschliste und entferne den Haken bei Ja. Übrig bleiben 148 Zeilen – genau der Bestand, den auch die App anzeigt.

Nur die benötigten Spalten behalten

Für die Auswertung brauche ich nicht alle 17 Spalten. Über Start → Spalten auswählen setze ich nur die Haken bei Titel, Autor, Seiten, ISBN, Kategorie und Gelesen. Power Query nennt diesen Schritt „Andere entfernte Spalten“. Der Vorteil: Kommt in der App später eine neue Spalte dazu, bleibt Deine Abfrage trotzdem stabil.

Das Erscheinungsjahr lasse ich bewusst weg. Ein Blick in die Spalte zeigt warum: Dort steht mal 2024, mal Dezember 2023, mal Jun 10, 2019 oder [2018]. Das sauber zu bereinigen wäre ein eigenes Tutorial.

Der Abfrage einen sprechenden Namen geben

Zum Schluss benennst Du die Abfrage rechts unter Eigenschaften → Name um. Aus dem kryptischen inventory-feed?key=… wird books. Diesen Namen brauchen wir gleich in Python – halte ihn also kurz und ohne Leerzeichen.

Angewendeter SchrittWas passiert
QuelleDaten werden über den API-Link abgerufen
Höher gestufte HeaderErste Zeile wird zur Überschrift
Geänderter TypDatentypen werden festgelegt
Ersetzter Wertnull in „Seiten“ wird zu 0
Das wird später allerdings statistische Probleme bereiten
Gefilterte ZeilenWunschliste wird ausgeblendet (157 → 148 Zeilen)
Andere entfernte SpaltenNur 6 von 17 Spalten bleiben

Schritt 3: Nur als Verbindung laden

Jetzt würden die meisten auf Schließen & laden klicken. Dann landet eine Tabelle mit 148 Zeilen auf einem neuen Blatt. Brauchen wir die? Nein. Wir wollen die Daten nicht ansehen, sondern auswerten.

Klicke deshalb auf den kleinen Pfeil unter Schließen & laden und wähle Schließen & laden in…. Im Dialog Daten importieren markierst Du Nur Verbindung erstellen und bestätigst mit OK.

Im Bereich Abfragen und Verbindungen erscheint jetzt die Abfrage books mit dem Hinweis Nur Verbindung. Die Mappe bleibt schlank, und trotzdem stehen die aufbereiteten Daten jederzeit bereit.

Der eigentliche Vorteil: Python in Excel kann direkt auf Power-Query-Abfragen zugreifen – auch wenn sie nur als Verbindung geladen sind. Eine Zwischentabelle im Blatt ist nicht nötig.

Schritt 4: Auswerten mit Python in Excel

Eine Python-Zelle legst Du auf einem dieser Wege an:

  1. Tippe in eine leere Zelle =PY( – das grüne PY-Symbol erscheint links in der Zelle.
  2. Oder drücke Strg + Alt + Umschalt + P.
  3. Oder wähle Formeln → Python einfügen.

Bestätigt wird eine Python-Zelle nicht mit Enter, sondern mit Strg + Enter. Enter allein erzeugt nur eine neue Zeile im Code.

Tipp: Unter Formeln → Editor öffnest Du den Python-Editor am rechten Rand. Dort siehst Du alle Python-Zellen der Mappe untereinander und kannst längeren Code deutlich bequemer schreiben als in der Bearbeitungsleiste.

Daten holen und Kategorien auflisten

In Zelle A1 hole ich die Daten aus der Verbindung in einen DataFrame – das ist eine Art Tabelle in Python. Sobald Du xl(" tippst, schlägt Excel die Abfrage books bereits vor.

df = xl("books")
df.Kategorie.sort_values().unique()

Die zweite Zeile sortiert die Spalte Kategorie alphabetisch und gibt jede Kategorie nur einmal aus. Ergebnis: 14 Kategorien von Belletristik bis Wirtschaft, die als Überlaufbereich nach unten ausgegeben werden.

Der DataFrame df steht nun in allen weiteren Python-Zellen zur Verfügung. Python in Excel rechnet Zeile für Zeile von links nach rechts und von oben nach unten. Zellen, die df nutzen, müssen also rechts oder unterhalb von A1 liegen.

Statistik auf Knopfdruck

In Zelle C1 reicht eine einzige Zeile:

df.describe()

describe() liefert für alle Zahlenspalten – hier die Seitenzahl – die wichtigsten Kennzahlen:

KennzahlBedeutungWert (Seiten)
countAnzahl Bücher148
meanMittelwert243,6
stdStandardabweichung238,7
minKleinster Wert0
25 %Unteres Quartil125,25
50 %Median220,5
75 %Oberes Quartil320
maxGrößter Wert1.748

In Excel bräuchtest Du dafür acht verschiedene Funktionen. Achte auf das Minimum von 0 – dazu gleich mehr bei den typischen Fehlern.

Wie viele Bücher pro Kategorie?

In Zelle F1 zähle ich die Bücher je Kategorie:

df["Kategorie"].value_counts().sort_index().reset_index()

Das liest sich fast wie ein Satz: Nimm die Spalte Kategorie, zähle die Werte, sortiere nach dem Namen und mache daraus wieder eine Tabelle mit zwei Spalten.

Zunächst steht in F1 nur [PY] DataFrame. Das ist das Python-Objekt. Um die Werte im Blatt zu sehen, stellst Du die Ausgabe links in der Bearbeitungsleiste von Python-Objekt auf Excel-Wert um (Tastenkürzel: Strg + Alt + Umschalt + M). Jetzt läuft die Tabelle über: Spitzenreiter ist Psychologie mit 23 Büchern, gefolgt von Selbstbehauptung (21) und Japan (18). In Summe sind es wieder genau 148.

Schritt 5: Eine Grafik mit Python erstellen

Im Video habe ich für die Grafik einen Umweg über Copilot genommen. Mein Prompt lautete sinngemäß:

Erzeuge mir aus der Datenverbindung books per Python ein ISO-24896-konformes Diagramm. Wie häufig ist welche Kategorie besetzt, wie viele Bücher sind komplett gelesen oder noch ungelesen?

Copilot hat in sechs Schritten ein neues Blatt Buchauswertung angelegt: eine Tabelle mit der Anzahl je Kategorie (Summe 148) und ein horizontales Balkendiagramm – allerdings ein klassisches Excel-Diagramm, kein Python. Den Python-Code hat Copilot nur als Text ab Zelle A30 mitgeliefert, mit dem Hinweis, ihn per Python einfügen zu übernehmen.

Das habe ich gemacht – und das Ergebnis ist lehrreich. Der linke Teil der Grafik mit den Kategorien passt. Der rechte Teil „Nach Lesestatus“ bleibt aber leer, nur der Hinweis Statusspalte und Werte prüfen steht dort. Der Grund: Copilot hat eine Spalte Lesestatus mit Werten wie „komplett gelesen“ vermutet. In meinen Daten heißt die Spalte aber Gelesen und enthält nur Ja und Nein.

Copilot ist ein guter Startpunkt, aber er kennt Deine Daten nicht so gut wie Du. Prüfe immer, ob Spaltennamen und Werte im Code zu Deiner Abfrage passen.

Der Code – passend zu unseren Daten

Hier eine schlanke Version, die direkt auf die Abfrage books zugreift. Lege eine neue Python-Zelle an, füge den Code ein und bestätige mit Strg + Enter:

import matplotlib.pyplot as plt
df = xl("books")
# 1. Kennzahlen berechnen
kategorien = df["Kategorie"].fillna("ohne Kategorie").value_counts().sort_values()
status = (df["Gelesen"]
.map({"Ja": "Gelesen", "Nein": "Ungelesen"})
.fillna("ohne Angabe")
.value_counts())
# 2. Zeichenfläche mit zwei Bereichen
fig, (ax1, ax2) = plt.subplots(
1, 2, figsize=(12, 6), gridspec_kw={"width_ratios": [3, 1]})
fig.suptitle(f"Buchbestand: {len(df)} Bücher", x=0.01, ha="left",
fontsize=14, fontweight="bold")
# 3. Links: Bücher je Kategorie
balken = ax1.barh(kategorien.index, kategorien.values, color="#404040")
ax1.bar_label(balken, padding=3)
ax1.set_title("Nach Kategorie", loc="left")
# 4. Rechts: Lesestatus
balken = ax2.bar(status.index, status.values,
color=["#404040", "#A6A6A6", "#D9D9D9"][:len(status)])
ax2.bar_label(balken, padding=3)
ax2.set_title("Nach Lesestatus", loc="left")
# 5. Unnötiges entfernen
for ax in (ax1, ax2):
ax.spines[["top", "right"]].set_visible(False)
ax.tick_params(length=0)
ax1.xaxis.set_visible(False)
ax2.yaxis.set_visible(False)
fig.tight_layout()
fig

Was der Code macht

  • Abschnitt 1 – Kennzahlen: value_counts() zählt die Bücher je Kategorie, sort_values() sortiert aufsteigend – dadurch steht die größte Kategorie im Diagramm oben. Mit map() werden aus Ja/Nein lesbare Beschriftungen.
  • Abschnitt 2 – Zeichenfläche: Zwei Diagramme nebeneinander, das linke dreimal so breit wie das rechte. Die Überschrift nennt die Gesamtzahl.
  • Abschnitt 3 und 4 – Balken: bar_label() schreibt die Werte direkt an die Balken. Eine Achse mit Skala brauchen wir dann nicht mehr.
  • Abschnitt 5 – Aufräumen: Rahmenlinien, Teilstriche und überflüssige Achsen verschwinden. Neutrales Grau statt bunter Farben – Farbe hebe ich mir für Abweichungen auf, ganz im Sinne der Notation aus meinem letzten Artikel.
  • Die letzte Zeile fig gibt die Grafik an Excel zurück. Ohne sie bleibt die Zelle leer.

Die Grafik ins Blatt holen

In der Zelle steht zunächst nur [PY] Image. Schalte die Ausgabe auf Excel-Wert um, dann erscheint die Grafik in der Zelle. Über das kleine Kartensymbol neben der Zelle kannst Du sie auch über den Zellen platzieren. Dann lässt sie sich frei auf dem Blatt verschieben und in der Größe anpassen – und bleibt trotzdem mit der Python-Zelle verknüpft.

Der Praxistest

Erfasse in der App ein neues Buch – bei mir geht das bequem über den ISBN-Scanner per Kamera. Zurück in Excel klickst Du auf Daten → Alle aktualisieren. Power Query holt den neuen Stand, Python rechnet neu, und die Grafik zeigt einen Balken mehr. Kein Export, kein Kopieren, kein Nacharbeiten.

Typische Fehler und schnelle Lösungen

  • #PYTHON! – Fast immer ein Tippfehler im Code oder ein falscher Spaltenname. Klicke auf das Fehlersymbol neben der Zelle, dort steht die genaue Meldung. Groß- und Kleinschreibung zählt: kategorie ist nicht Kategorie.
  • KeyError – Die Spalte gibt es in der Abfrage nicht. Genau das ist Copilot mit „Lesestatus“ passiert. Prüfe die Spaltennamen in Power Query.
  • xl(„books“) findet nichts – Die Abfrage heißt anders. Schau im Bereich Abfragen und Verbindungen nach dem genauen Namen.
  • Die Grafik ist leer – Die letzte Zeile fig fehlt.
  • Mittelwert wirkt zu niedrig – Achtung, Denkfalle! Wir haben in Power Query null durch 0 ersetzt. Diese Nullen ziehen den Mittelwert der Seitenzahl nach unten, deshalb zeigt describe() ein Minimum von 0. Für Statistiken ist es oft besser, leere Werte leer zu lassen – pandas ignoriert sie dann automatisch.
  • Daten sind veraltet – Der API-Schlüssel wurde widerrufen oder neu erzeugt. Neuen Link kopieren und unter Datenquelleneinstellungen in Power Query austauschen.

Python oder klassisches Excel?

Klassisches ExcelPython in Excel
EinstiegSofort, ohne CodeEtwas Code nötig
StatistikEine Funktion je Kennzahldescribe() liefert alles auf einmal
DiagrammInteraktiv, per Klick formatierbarAls Bild, über Code gestaltet
WiederverwendungDiagramm kopieren und anpassenCode kopieren, fertig
RechenortLokal auf Deinem PCMicrosoft-Cloud

Mein Fazit: Für eine schnelle Einzelgrafik reicht das klassische Diagramm. Sobald Du dieselbe Auswertung regelmäßig brauchst oder mehrere Kennzahlen kombinierst, spielt Python seine Stärken aus. Du solltest das ganze unter Copilot auch über einen Agenten automatisieren und entsprechende Skills definieren.

Zum Schluss…

API, Power Query, Python, Copilot – das klingt nach viel Technik. Im Kern sind es aber fünf überschaubare Schritte. Und am Ende steht eine Auswertung, die sich mit einem einzigen Klick selbst aktualisiert.

Das komplette Vorgehen siehst Du auch im Video auf meinem YouTube-Kanal:
https://www.youtube.com/watch?v=r2_gYzBz-dY

Wenn es Dir gefällt: Abonnieren, Hypen, Liken und gern die Glocke aktivieren.

Danke und ciao!
Andreas

Spreadsheet Day 2019 & Excel-Aufgabe

Hey!

Nächste Woche ist der Spreadsheet-Day am 17. Oktober 2019! Und dann geht es auch schon mit den Excel-Tagen 2019 in München weiter https://www.munich-office-group.de/

Doch bis dahin gibt es noch eine kleine Aufgabe von mir für meine angehenden at Excel Experts. Heute auf dem Excel-Stammtisch in Essen können wir die Aufgabe auch gerne praktisch durchgehen… https://www.meetup.com/de-DE/Excel-Stammtisch-NRW/

Hier das Szenario:

Für ein Projekt wurden drei Firmen beauftragt.

E1026_1

Die drei Firmen werden für 200-220 Tage beauftragt, haben klare moderate Tagessätze  und sind für bestimmte Teilprojekte vorgesehen. Das erste Unternehmen hier z.B. für die Teilprojfekte 1 bis 5.

Die Projektvorgangscodes aus der mittleren Spalte werden dann auch bei den Buchungen der einzelnen Mitarbeiter der Firmen eingetragen.

In der letzten Spalte ist noch zu sehen, wie viele Personentage für den Projektvorgang in Summe vorgesehen sind.

E1026_2

Die Projektplanung und die Buchung der Firmen umfasst somit komplett jeweils 640 Personentage.

Die folgenden 15 Mitarbeiter aus den drei Unternehmen werden dann auch tatsächlich für drei Monate eingesetzt. Jede Firma stellt 5 Mitarbeiter.

E1026_3

Nun kommen die tatsächlichen Buchungen für die drei Monate. Jeder Monat wird auf einem Tabellenblatt ausgewiesen.

Die Daten stehen platt im Datenblatt – keine Tabelle. Erst nur das Kürzel, dann das Datum pro Person und entsprechend ein Vorgang. Auf halbe Tage habe ich hier mal verzichtet.

E1026_4

Egal wie, sollen die folgenden Fragen bzw. Aufgaben geklärt werden:

  1. Passen die Buchungen der Mitarbeiter aus den drei Monaten zu den Beauftragungen. Wurden die Buchungen für die Firmen gut geplant?
  2. Passen die Plan-Zahlen der Projektvorgänge zu den Buchungen? Wurden die Projektvorgänge richtig eingeschätzt?
  3. Wie hoch fallen die monatlichen Rechnungen der drei Unternehmen aus?
  4. Stellen Sie tabellarisch in einer Kreuztabelle pro Unternehmen dar, wie viele Tage die einzelnen Mitarbeiter pro Monat gebucht haben.
  5. Gab es Fehlbuchungen auf Projekte, die einem Unternehmen nicht zugewiesen waren?
  6. Liegen vereinzelt Buchungen auf Freitagen? Dies war nicht gestattet!
  7. … fallen Dir noch Sachen auf?

Die XLSX-Dateien mit den Daten der Buchungen: E1026 Buchungsdaten

Und die ersten drei Tabellen: E1026 Buchungsliste Aufgabe Excel Experts

Ich bin auf euer Feedback gespannt.

Viel Spaß beim Basteln und bis bald
Andreas

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:
Videolink: http://youtu.be/UKRr1ubgbHo

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:
Videolink: http://youtu.be/zXHgKkLQXYo

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:
Videolink: http://youtu.be/QUdnghNApq8

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:
Videolink: http://youtu.be/SmMlU92j3Do