Mich haben Karten schon als kleiner Junge fasziniert… dazumal, weil ich Geschichtsbücher gelesen habe oder am Risiko spielen war. Aber auch heute schaue ich Informationen gerne noch auf einer Karte an.

Deshalb zeige ich in diesem Beitrag wie man Daten in Excel geographisch aufbereiten kann als «Heat Map» (auch Choroplethenkarte, Flächenkartogramm, oder Flächenwertstufenkarte genannt. Siehe: Wikipedia).

Zwei Methoden

Excel bietet seit der Version 2013 die Power Map Funktion an.

Vorher war dies in Excel zu machen (bis zur Version 2010) ein ziemlicher Aufwand!

Weil diese Methode auch heute noch relevant ist, denn nicht jedes Geographische Gebiet dass man zeigen möchte kann auch standardmässig durch Power Maps abgebildet werden, zeige ich auch die alt bewährte Methode!

Methode 1: Power Maps (ab Excel 2013)

Wer sich das Endresultat gleich selbst anschauen möchte, kann dies tun mit der Datei:

Excel bietet seit der Version 2013 die Power Map Funktion an. Je nach Version von Office die man hat muss man Power Maps als Add-In installiert werden.

In der 365 Version von Office ist es direkt in Excel integriert. Hier ist der Link zu Microsoft falls Ihr das noch herunterladen müsst: Power Maps von Microsoft.

Das Menü für Power Maps findet Ihr im «Einfügen» Menu>Karten:

Schritt 1: Daten Grundlage für Power Maps

Möchte man Daten wie die in dieser Tabelle jetzt graphisch auswerten, so wird einfach der gesamte Datensatz ausgewählt und danach auf Karte erstellen klickt.

Wichtig ist, dass die Datenspalten klar beschriftet sind. Excel benutzt diese Angaben um die geographischen Orte zu bestimmten!

Power Map Fehler

Power Maps birgt ein gewisses Frust potenzial, denn es erkennt nicht immer die gewünschten Gebiete!

Hier die gängigsten Fehler und wie man sie behebt:

Fehler 1: Region nicht erkannt

Power Map erkennt nur auf den Stufen Land/Region, Bundesland/Kanton, Verwaltungsbezirk oder Postleitzahl angezeigte geographische Werte!

Lösung: Daten in diese Kategorien zuteilen wo möglich oder dann auf die ältere Lösung die ich unten Beschreibe für Excel 2010 setzen.

Fehler 2: Daten mit hoher Vertrauenswürdigkeit gezeichnet

Was bedeutet das überhaupt? War will uns Excel damit sagen dass die Karte «Mit hoher Vertrauenswürdigkeit» gezeichnet wurde?

Ganz einfach: Der KI (Künstliche Intelligenz) Algorithmus bei Microsoft war sich unsicher ob wir wirklich die angezeigten Länger meinen. Im obengenannten Beispiel, habe ich sowohl Länder, wie auch Bundesländer und Schweizer Kantone gemischt.

Fehler 3: Gleich benannte Ortschaften oder Gebiete

Power Map weisst daraufhin, dass es zwei mögliche Ortschaften gibt und es nicht weiss welches, dass man haben möchte. Beispiel aus der Schweiz: die Ortschaften Rohr (Solothurn) und Rohr (Aargau)

Hier auch noch Microsofts eigene Aufklärung zu möglichen Fehler bei Power Maps: Microsoft Artikel

Schritt 2: Die Power Map Karte

Die Karte wird, insofern dass die oben erwähnten Probleme beseitigt wurden, perfekt erstellt und kommt graphisch gut aufgearbeitet daher:

Daten Sicherheit (Achtung: Daten werden an Microsoft geschickt zur Auswertung)

Eines muss man sich jedoch bewusst sein: wer die Power Map Funktion benutzt schickt seine Daten, egal was diese auch sein mögen, an einen Server von Microsoft der dann berechnet um welche Informationen es sich handelt und wie diese auf der Karte dargestellt werden sollen. Ein kleiner Hinweis dass dem so ist hat Microsoft selber gleich in jeder Karte unten rechts angebracht!

Schaut man genau hin so steht dort folgendes:

Dies mag für die meisten Unternehmen keine Rolle spielen, jedoch kann es für Branchen wie das Bank Wesen oder andere die Kunden Daten sensitiv behandeln müssen, zu einem Verstoss gegen geltende Gesetze oder Interne Regeln führen!

Methode 2: Heat Map (Excel 2010)

Auch hier kann das Endresultat direkt in der Beispiel Datei angeschaut werden (Download weiter oben).

Wie die Heat Map in Excel 2010 erstellt wird

Wie funktioniert das?

In einem Satz zusammengefasst: Die Karte besteht aus einzelnen gezeichneten «Formen» die per VBA Code eine entsprechende Hintergrundfarbe bekommen (basierend auf dem zugewiesenen Wert).

Hier nun noch eine ausführlichere Erklärung…

Schritt 1: Die Geographischen Formen

Karten Zeichnen

Im Wesentlichen wird bei dieser Methode für jedes geographische Gebiet eine Form erstellt (via Einfügen Menü>Formen>Freihand: Skizze).

Wie anfangs erwähnt, kann man so auch seine eigenen Gebiete erstellen, welche nicht in auswählbar sind in Power Maps… z.B. Nordwestschweiz, oder Süd Deutschland, etc.

Karten Importieren

Natürlich gibt es die Möglichkeit die Geographischen Gebiete aus einer anderen Datei zu importieren. Jedoch muss man zuerst eine Datei mit kompletten kartographischen Informationen finden. Wikipedia ist hierfür keine schlechte Anlaufstelle da die Daten immer ohne Gebühr benutzt werden dürfen und zudem oft in einem nützlichen Format vorhanden sind!

Aus meiner Erfahrung kann ich sagen, dass sich SVG Dateien gut eigenen. Jedoch muss auch hier meistens noch mit viel manuellem Aufwand gerechnet werden bis die Karten dann wirklich zu gebrauchen sind!

Form Elemente benennen

Die Form Elemente sollten benannt werden.

Am besten macht man dies mit sprechenden Namen, so dass es später einfach ist ein Form Element korrekt anzusteuern.

Ein Beispiel wie dies in meiner Datei gemacht wurde sieht man hier:

Hinweis: Das Kartenmaterial zu erstellen ist bereits eine nicht zu unterschätzende Hürde die zu beachtlicher Arbeit führen kann!

Schritt 2: Daten Aufbereitung

Letztendlich handelt es sich bei der Datenaufbereitung lediglich um eine Liste! Also nichts sonderlich kompliziertes.

Der einzige wichtige Punkt ist, dass die oben erstellten Formen jeweils mit dem Form Namen aufgeführt werden. Z.B. obj_AG

Dieser Name, muss in der Liste vermerkt werden, so dass wir im Schritt 3 die Formen (Geograpischen Elemente) mit dem jeweilig korrekten Datenpunkt verbinden können!

Schritt 3: VBA Code

Damit die Farben nun dem entsprechenden Wert zugewiesen werden benötigen wir ein wenig VBA Code.

Der Code wir einfach in einem neuen Modul eingefügt.

Hier der Code den ich in meiner Beispiel Mappe habe:

Und hier noch als Text zum kopieren:

Sub RecolorMap()
Dim i As Long

For i = 2 To 29
With wsValues
ChangeObjectColor .Cells(i, 1), .Cells(i, 3)
End With
Next i
wsMap.Range("A1").Select
End Sub

Sub ChangeObjectColor(objName As String, col As String)
Dim r As String, g As String, b As String
r = Split(col, ",")(0)
g = Split(col, ",")(1)
b = Split(col, ",")(2)

Dim canton
Set canton = wsMap.Shapes.Range(Array(objName))
canton.Select
'wsMap.Shapes.Range(Array(objName)).Select
With Selection.ShapeRange.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(r, g, b)
.Solid
End With

End Sub