Excel: Nochmal die Kamera … how to build a simple GIS with Excel 2003

18. Oktober 2009 um 12:30 pm | Veröffentlicht in BI, Excel, Visualisierung | 8 Kommentare

Immer wieder äußern meinen Kunden den Wunsch, Exceldaten auf einer Landkarte zu visualisieren – z.B. die Umsatzentwicklung nach Bundesländern. Leider musste ich hier bisher passen: ein GIS – Geographisches Informations System – mit Excel-Standardmitteln aufzubauen, dass mehrere Kennzahlen dynamisch visualisiert, gelang mir nicht.

Hier zeige ich, wie es doch geht … mit einem Trick für die Excel-Kamera.

Zunächst gilt es eine Landkarte als Umriss in Excel zu importieren.
Viele im web verfügbaren Umrisskarten liegen als Vektorgrafiken im scalable vector graphics (svg-)Format vor. Deutschlandkarten finden sich z.B. bei wikimedia und dmaps.com. Leider kann aber eine svg-Grafik nicht direkt über Einfügen → Grafik → aus Datei … in ein Excel-Tabelle eingefügt werden. Ein direktes Öffnen der svg-Datei stellt die XML-Daten aus denen sich die Grafik zusammensetzt dar; auch nicht wirklich hilfreich.

In seinem von mir sehr geschätzten Blog clearlyandsimply.com zeigt Robert einen Weg, wie svg-Formate in das für Excel verständliche enhanced metafile(emf-)Format umgesetzt werden können:

  1. Zunächst die gewünschte Karte als scalable vector Grafik herunterladen.
  2. Anschließend die Grafik mit einem Editor für Vektorgrafiken wie z.B. Inkscape (Open Source) öffnen und als .emf (Enhanced Metafile) speichern: Datei Kopie speichern unter …
  3. Nun eine neue Excel-Arbeitsmappe öffnen und mit Einfügen → Grafik → aus Datei die emf-Datei importieren.
  4. Die Gruppierung der „shapes“ nun so häufig aufheben, bis für jede Region eine einzelne Form existiert.
  5. Die nicht benötigten Elemente für die Karte löschen.
  6. Zuletzt die Karte auf die gewünschte Größe skalieren und die Rahmen der Zellen im Hintergrund weiß formatieren.

Voilà: eine Landkarte in Excel. In meinem GIS-Beispiel ist eine Karte Deutschlands nach Bundesländern hinterlegt.
Wie aber wird nun aus dieser Karte ein einfaches GIS?

Eine Möglichkeit ist die Nutzung der Länderumrisse für eine Choropleth-Map. Eine was? Eine Choropleth-Map … am besten wohl mit Gebietsstufenkarten zu übersetzen. In einer Gebietsstufenkarte werden die einzelnen Regionen abhängig vom Wert einer Kennzahl für diese Region unterschiedlich eingefärbt.

Deutschland Choropleth-Map

Robert zeigt in einem weiteren Beitrag seines Blogs, wie solch eine Choropleth-Map in Excel mit ein bisschen VBA umgesetzt werden kann. Er nennt aber auch die Nachteile dieser Art der Darstellung. Zwei Aspekte sind aus meiner Sicht besonders ungünstig:

  • Eine zeitliche Entwicklung ist nicht darstellbar und
  • die unterschiedlichen Flächen der Länder in der choropleth-Grafik oben können zu Fehlinterpretation führen, wenn die Größe der Fläche die visuelle Wahrnehmung des Lesers dominiert.

Ich möchte an dieser Stelle eine Alternative beschreiben, die es ermöglicht, Grafiken via Excel-Kamera dynamisch – abhängig von der ausgewählten Kennzahl in der drop-down-box – auf einer Karte zu platzieren.
Die Grundlagen der Excel-Kamera beschreibe ich in meinem letzten Beitrag: hier.

Ich verzichte auf die Nutzung von sparklines/Wortgrafiken, obwohl sich diese perfekt für eine Anwendung in einem GIS eignen, sondern nutze nur Excel-Standardfunktionen für die Umsetzung:
Zunächst eine stark verkleinerte Säulengrafik zur Visualisierung der Umsatzentwicklung im jeweiligen Bundesland und ein Kuchendiagramm mit drei (Torten-)Stücken. Letzteres stellt die Verteilung der Anzahl Kunden nach Kundensegmenten (A – B – C) dar. Ich weiß, horizontale Balken bilden Anteile besser ab, aber für die Darstellung hier ist die Torte effektvoller. Ich bitte um Nachsicht … das Ergebnis sieht wie folgt aus:

Wie funktioniert das? Eigentlich ganz einfach … die drop-down-box (ein Kombinationsfeld aus der Symbolleiste Formular) spielt sich als Kameramann auf: in Abhängigkeit der ausgewählten Kennzahl zeigen Kameras in den Ländern die der Kennzahl zugeordnete Grafik.
Wenn Sie die folgenden Ausführungen in Excel nachvollziehen wollen, finden Sie die der Grafik oben zugrunde liegende Excel 2003-Datei hier.

Also Schritt für Schritt:

  1. Im Tabellenblatt „Control“ werden zunächst für jeden Bundesland Excelcharts angelegt. Achsen, Legenden und die sonstigen üblichen Verdächtigen fliegen raus … wichtig: die Rahmen der Zellen im Hintergrund sollten der Farbe mit dem das jeweilige Bundesland gefüllt ist entsprechen. Eine einheitliche Skalierung ist empfehlenswert.
  2. Anschließend wird jede dieser Grafiken fotografiert, zu jedem Bundesland abgelegt und so verkleinert, dass sie genau in eine Zelle passt.
  3. Die erste Graphik eines jeden Bundeslandes wird nun erneut mit der Kamera fotografiert und in das entsprechend Bundesland auf das Tabellenblatt „Karte“ abgelegt. Den Rahmen der Grafik auf die Farbe des Bundesland anpassen und den Hintergrund auf „Keine Füllung“ einstellen.
  4. Nun wird es tricky … die Kameragrafik selektieren und unter Einfügen → Namen → Definieren … der Grafik einen Namen geben:
    Namen definieren
    Unter „Bezieht sich auf:“ ist die Logik erkennbar: In Abhängigkeit des Zustands der drop-down-box – Control!$D$3 – wird die Zielzelle der Kamera ausgehend von Control!$D$25 mittels der Funktion „BEREICH.VERSCHIEBEN“ verändert.
  5. Zuletzt muss der gerade vergebene Name nur noch der Grafik des Bundesland zugeordnet werden. Dazu die Grafik anklicken und den Namen – hier: =’Excel GIS.xls‘!G_NS – in die Eingabezeile tippen.
    Namenzuordnung
  6. Die letzten beiden Schritte für alle Grafiken des GIS wiederholen, wobei natürlich die Zellverweise und Namen angepasst werden müssen.

So, das war es. Ich hoffe, das Vorgehen ist anhand der Beispieldatei gut nachvollziehbar.

Ich werde diese Art der Visualisierung sicher häufiger in Zukunft anwenden (müssen). Die beschriebene Lösung im Zusammenspiel mit Palo erlaubt den Aufbau von GIS-Dashboards ohne in teure GIS-Applikationen investieren zu müssen. Hat doch was …

PS: Auf die Idee zu diesem Trick bin ich beim Lesen eines Beitrags des vorzüglichen Excel-Blogs pointy haired dilbert gekommen. Vielen Dank, chandoo!

__
Creative Commons License
Dieser Beitrag bzw. Inhalt ist unter einer Creative Commons-Lizenz lizenziert.

Advertisements

8 Kommentare »

RSS feed for comments on this post. TrackBack URI

  1. Hallo Lars,

    zunächst vielen Dank für Deinen Artikel und vor allem vielen Dank für die Links zu den posts auf meinem Blog.

    Dein Artikel zeigt ein sehr schönes, kreatives und anschauliches Beispiel, wie flexibel man die Kamera in Excel einsetzen kann.

    Allerdings finde ich – ganz im Gegensatz zu Deinen anderen Beispielen hier auf openBI – die Visualisierung an sich nicht gelungen. Warum? Ich kann in der Darstellung der Balken und Torten-Diagramme auf einer darunter liegenden Karte keinen zusätzlichen Nutzen erkennen. Wenn man die Diagramme unter- und / oder nebeneinander positioniert (ohne Karte) und mit den Namen der Bundesländer beschriftet, spart man eine Menge Platz bzw. kann die Diagramme wesentlich größer gestalten, mit Achsen und Achsbeschriftungen bzw. mit Datenbeschriftungen versehen und so die Aussagekraft der Visualisierungen deutlich erhöhen. Die Positionierung der Diagramme auf der Karte ist ein netter visueller Effekt, liefert aber meines Erachtens keinerlei Beitrag für die Analyse und Interpretation der Daten.

    Die von mir verwendeten Choropleth Maps haben eine Menge Nachteile und einen sehr beschränkten Einsatzbereich, das sehe ich genauso wie Du: Die direkte Vergleichbarkeit der Regionen ist nur sehr eingeschränkt möglich, die Darstellung ist statisch auf einen Zeitpunkt bezogen (keine Trends), die exakten Werte sind nicht sichtbar, es gibt Nachteile hinsichtlich der Wahrnehmung (unterschiedlich große Regionen) und die Darstellung benötigt viel Platz auf Dashboards.

    Allerdings sind diese Karten ein Visualisierungskonzept in sich und für eine sehr beschränkte Menge an Anwendungsbeispielen auch der richtige Weg. Es gibt durchaus Situationen, in denen Choropleth Maps viel zu einer professionellen Datenanalyse und zum Verständnis der Daten beitragen können. Beispiel: Nehmen wir an, wir haben eine regional gegliederte Vertriebsorganisation und wollen die Neuabschlüsse / Erfolge des Vertriebs für einen bestimmten Zeitraum hinsichtlich der geographischen Verteilung analysieren. Hier sind Choropleth Maps von unschätzbarem Wert, vor allem dann, wenn die Anzahl der verwendeten Regionen groß genug ist. Eine Karte gegliedert nach den ersten 2 Ziffern der Postleitzahlen (insgesamt 95 Gebiete), beispielsweise, würde uns auf einen Blick die „Hot Spots“ zeigen bzw. die Gebiete in denen der Vertrieb weniger erfolgreich ist. Wir könnten sofort sehen, dass zum Beispiel der Vertrieb in München und Oberbayern zwar vergleichsweise erfolgreich ist, im Rest von Bayern aber deutliche schlechtere Ergebnisse liefert als Baden-Württemberg oder Sachsen. Schnelle Einblicke in die Struktur der Daten, die man mit einer hohen Aggregationsstufe (nach Ländern) oder mit Balkendiagrammen so nicht erzielen kann. Dafür muss aber m.E. – wie erwähnt – die Granularität der Regionen groß genug sein. Eine Choropleth Map nach Bundesländern ist m.E. nicht wirklich hilfreich, wenn man aber auf die Ebene Regierungsbezirk / Landkreis oder eben PLZ2-Ebene geht, dann kann eine solche Karte viel zur Datenanalyse und Visualisierung beitragen.

    Mein Lösungsansatz ist grundsätzlich folgender: Wann immer ich Choropleth Maps einsetze (das ist eher selten), versuche ich die Nachteile dieser Karten durch zusätzliche Visualisierungen zu kompensieren (ein zusätzliches Balkendiagramm, Tooltips um die exakten Werte darzustellen, etc.). Ich plane übrigens noch ein bis zwei weitere Artikel zu diesem Thema auf meinem Blog in den nächsten Wochen.

    Wie gesagt: Ich finde Deinen Artikel sehr gut als konkretes Beispiel, was man mit der Kamera alles erreichen kann. Ich würde allerdings das Ergebnis (Balken- und Tortendiagramme auf einer Karte) nicht für professionelle Anwendungen im Bereich der Datenanalyse / Visualisierungen empfehlen.

    Viele Grüße

    Robert

  2. Hallo Robert,

    vielen Dank für Deinen ausführlichen Kommentar.
    Vorab: ich stimme in allen Deinen (Kritik-)Punkten völlig mit Dir überein!

    Die Säulen und Torten machen überhaupt keinen Sinn, da gibt es nix zu Rütteln. Mir ging es in erster Linie um die Darstellung des Excel-Tricks anhand eines sehr einfachen Beispiels. Ursprünglich wollte ich die wunderbare Lösung für den bullet graph, die in einem (Gast-) Beitrag auf Deinem Blog beschrieben ist, verwenden, habe mich aber dann für den schnelleren Weg entschieden. Nicht gut …

    Auch in Bezug auf die Positionierung von Grafiken auf Bundesländern habe ich meine Vorbehalte – wo ist der Mehrwert von Säulen auf Flächen, wenn die Säulen dadurch nicht mehr auf gleicher Höhe liegen und kaum mehr für das Auge zu vergleichen sind? Nun: der Kunde ist König und der visuelle Effekt der Landkarte kommt gut an! Ich musste lange mit Engelszungen reden, um eine Tachometerdarstellung abwenden zu können.

    Deinen Vorschlag, Choropleth Maps bei einer großen Anzahl von Regionen zu nutzen, finde ich richtig gut. Und … ja, an dieser Stelle kann ich mir diese Form der Visualiserung gut vorstellen. Würde ich gerne noch in meine – produktive – Lösung, in der ich keine frei herumhängenden Säulen und Torten nutze :-), unterbringen.

    Schönen Sonntag, Lars

  3. Hallo Lars,

    ich habe mir schon gedacht, dass es Dir primär um die Möglichkeiten der Kamera ging. Soweit ist Dein Beitrag ja auch sehr gut und anschaulich. Meine Kritik richtete sich eher gegen das Ergebnis an sich, nicht gegen die Technik. Ich denke, wir stimmen durchaus darin überein, dass Dein Beispiel nicht ideal gewählt war. Ist ja auch kein Problem.

    Wenn Du für Deine produktive Lösung eine Vorlage benötigst für eine Deutschland-Karte nach PLZ2, sag kurz per Email Bescheid, dann kann ich Dir das gerne zur Verfügung stellen.

    Viele Grüße

    Robert

  4. […] Wohl kaum … 6. Januar 2010 at 3:52 | In Visualisierung | Leave a Comment In meinem Beitrag über ein Geographisches Informationssystem (GIS) auf Basis von Excel erwähnte ich die […]

  5. […] Wie man die Kamera für den Aufbau eines einfachen geographischen Informationsystem (GIS) in Excel nutzen kann, zeige ich hier. […]


Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s

Erstelle eine kostenlose Website oder Blog – auf WordPress.com.
Entries und Kommentare feeds.

%d Bloggern gefällt das: