Als Wortwolke, Schlagwortwolke oder Stichwortwolke (engl. tag cloud) wird die Informationsvisualisierung bezeichnet, bei der eine Liste mit unterschiedlichen Stichwörtern (tags) analysiert wird und abhängig von der Häufigkeit der vorkommenden Begriffe die Wortgröße entsprechend dargestellt wird.

Wortwolken werden häufig in Webblogs oder Webseiten eingesetzt mit dem Ziel, die häufigsten Stichworte (tags) auf der Seite bzw. in den Beiträgen des Blogs darzustellen. Damit spiegeln Wortwolken das Ziel und den Informationsschwerpunkt einer Homepage wider.

In unserem News-Blog Excel-Live.de ist ebenfalls eine Wortwolke in der rechten Seitenleiste zu sehen.

Ich habe nun eine Wortwolke entwickelt, welche direkt in Excel erstellt werden kann. Damit steht diese Funktion zentral für Auswertungen und Berichte in Excel zur Verfügung. So können beispielweise die Kundenliste, die Liste der Lieferanten oder ähnliche Daten aufbereitet werden.

Funktionsweise der Wortwolke

Auf einem beliebigen Tabellenblatt (in der Beisipieldatei auf dem Blatt "Daten") werden die auszuwertenden Begriffe eingetragen, siehe Abbildung 1. Diese Begriffe dienen als Basis für die Erzeugung der tag cloud.

Abbildung 1:

Ermittlung und Analyse der Begriffe

Nachdem die relevanten Begriffe für die Wortwolke eingegeben wurden, müssen diese analysiert und ausgewertet werden. Dies findet auf dem Tabellenblatt "Calc" statt.

Im ersten Schritt wird geprüft, ob es sich bei einem Begriff um ein Unikat oder ein Duplikat handelt. Dies ist wichtig, da davon abhängig eine Liste mit Unikaten erzeugt wird. Das bedeutet, alle doppelten Einträge, die zu einer Veränderung der Schriftgröße führen, werden zunächst eleminiert. Diese Unikatsliste beinhaltet damit nur noch jeden Begriff ein einziges mal und wird für die Darstellung der tag cloud verwendet.

Im der folgenden Abbildung 2 sehen Sie Logik zur die Berechnung der Wortwolken-Logik. Diese beinhaltet neben der Unikats-Liste auch die Auswertung der Häufigkeit einzelner Begriffe, sowie die Zuweisung der Schriftgröße für die einzelnen tags.

Abbildung 2:

Die folgende Tabelle gibt einen Überglick über die Formeln in den jeweiligen Zellen:

Zelle A2: =MAX((Daten!A1:A999<>"")*ZEILE(1:999))

Zelle B2: =ZEILE(Daten!A2)
Zelle B3: =WENN(UND(NICHT(ISTLEER(Daten!A3)); Daten!A3<>""; ISTNV(VERGLEICH(Daten!A3; BEREICH.VERSCHIEBEN( Daten!$A$2; 0;0;ZEILE(Daten!A3)-$B$2;1);0))); ZEILE(Daten!A3);0) 
→ Diese Formel bis zur Zeile 1000 nach unten kopieren

Zelle C2: =WENN(B2<>0;"U";"D")
→ Diese Formel bis zur Zeile 1000 nach unten kopieren

Zelle D2: =B2
Zelle D3: =WENN(ODER(D2=MAX(B:B);D2=0);0; BEREICH.VERSCHIEBEN($B$2 ; D2-2;0;1;1); VERGLEICH("U"; BEREICH.VERSCHIEBEN($C$2;D2-1; 0;$A$2-D2+1;1);0);0;1;1))  
→ Diese Formel bis zur Zeile 1000 nach unten kopieren

Zelle E2: =WENN(D2<>0;INDEX(BEREICH.VERSCHIEBEN( Daten!$A$2;0; 0;$A$2;1);Calc!D2-1);"") 
→ Diese Formel bis zur Zeile 1000 nach unten kopieren

Zelle F2: =WENN(E2<>""; ZÄHLENWENN(Daten!$A$2:$A$100; Calc!E2);"")
→ Diese Formel bis zur Zeile 1000 nach unten kopieren

Zelle G2: =WENN(F2<>"";RANG(F2;$F$2:$F$51;-1);"")
→ Diese Formel bis zur Zeile 1000 nach unten kopieren

Zelle H2: 1
Zelle H3: =WENN(G3<>"";I2+1;"")
→ Diese Formel bis zur Zeile 1000 nach unten kopieren

Zelle I2: =LÄNGE(E2)+3
Zelle I3: =WENN(H3<>"";LÄNGE(E3)+3+I2;"") 
→ Diese Formel bis zur Zeile 1000 nach unten kopieren

Zelle J2: =WENN(G4<>"";RUNDEN($L$7+((G4-1)*$M$10);0);"")
→ Diese Formel bis zur Zeile 1000 nach unten kopieren

Im Bereich L2:M10 wird der Faktor zur Darstellung der Schriftgröße für die einzelnen Wörter, abhängig von deren Bedeutung (Häufigkeit) ermittelt.

Dynamisierung der Wortwolke

Damit die tag cloud wie gewünscht arbeitet, sind noch ein paar Zeilen VBA-Code notwendig. Erfassen Sie dazu den nachfolgenden Code in die entsprechenden Codemodule:

Code-Modul:

Dieser VBA-Code erzeugt die Wortwolke und stellt die Schriftgröße entsprechend pro Begriff ein.

Sub make_cloud()
'** Build the cloud
Set wscalc = ThisWorkbook.Sheets("calc")
Set wscloud = ThisWorkbook.Sheets("cloud")

[wolke].Font.Size = 10

For a = 2 To wscalc.Cells(Rows.Count, 7).End(xlUp).Row

  If wscalc.Cells(a, 7).Value <> "" Then
    
    cloud = cloud & wscalc.Cells(a, 5).Value & "   "
  
  End If

Next a

[wolke].Value = cloud

'** Word-Size definieren
For a = 2 To wscalc.Cells(Rows.Count, 7).End(xlUp).Row
  If wscalc.Cells(a, 7).Value <> "" Then
    s = wscalc.Cells(a, 8).Value
    l = wscalc.Cells(a, 9).Value
  
    With [wolke].Characters(Start:=s, Length:=l).Font
      .Size = wscalc.Cells(a, 10).Value
    End With
  End If
Next a
End Sub

Tabellenblatt "Daten"

Der folgende VBA-Code ruft nach Eingabe eines neuen Begriffs die Prozedur "make_cloud" auf und erstellt sofort die Wortwolke.

Private Sub Worksheet_Change(ByVal Target As Range)
'** Aufruf Prozedur nach Eingabe neuer Begriffe
If Not Application.Intersect(Target, Range("A2:A100")) Is Nothing Then
  make_cloud
End If
End Sub

Tabellenlbatt "Cloud"

Mit Hilfe der folgenden 5 Prozeduren werden die Einstellungsmöglichkeiten über die Schieberegler gesteuert.

Private Sub CommandButton1_Click()
'** Button "Reset"
With ActiveSheet
  .Range("G6").Value = 8
  .Range("G8").Value = 50
  .Range("G10").Value = 200
End With
make_cloud
End Sub

Private Sub ScrollBar1_Change()
'** Aktualisieren der Cloud nach Änderung der Schriftgröße
make_cloud
End Sub

Private Sub ScrollBar2_Change()
'** Cloud high
c = [wolke].Column
Columns(c).ColumnWidth = ThisWorkbook.Sheets("Cloud").Range("G8").Value
End Sub

Private Sub ScrollBar3_Change()
'** Cloud witdh
r = [wolke].Row
Rows(r).RowHeight = ThisWorkbook.Sheets("Cloud").Range("G10").Value
End Sub

Private Sub ToggleButton1_Click()
'** Ein- und Ausblenden der Kurzanleitung über einen ToggleButton
If ToggleButton1.Value = True Then
  ToggleButton1.Caption = "Ausblenden"
  ActiveSheet.Rows("20:30").EntireRow.Hidden = False
Else
  ToggleButton1.Caption = "Einblenden"
  ActiveSheet.Rows("20:30").EntireRow.Hidden = True
End If
End Sub

Einstellungen für die Wortwolke

Die Excel-tag cloud besitzt auch verschiedene Einstellungsmöglichkeiten. So kann neben der relativen Schriftgröße auch die Höhe und Breite der Wortwolke verändert werden. Die Scrollbalken dafür finden Sie auf dem Blatt "Cloude", siehe auch nachfolgende Abbildung 3.

Abbildung 3:

Die Beispieldatei können Sie über den folgenden Link herunterladen und in Ihren Kalkulationsmodellen verwenden.