Im Download-Archiv unter www.excel-berater-online.de können Sie das Archiv K407.zip herunterladen. Das Archiv beinhaltet die beiden Arbeitsmappen K407.xlsx und K407-Ergebnis.xlsx sowie einen Ordner, der die Beispielgrafiken enthält. Die Mappe K407.xlsx enthält nur die Tabelle zur Kundenumfrage und ermöglicht Ihnen, die Erläuterungen in diesem Artikel Schritt für Schritt nachzuvollziehen.
Individuelle Symbole dynamisch verwenden.
Seit Excel 2007 können Sie über die bedingte Formatierung auch Symbolsätze verwenden. Aber es steht nur eine eingeschränkte Auswahl an Symbolen zur Verfügung und es ist nicht möglich, eigene Symbolsätze einzubinden.
In diesem Artikel erfahren Sie, wie sich dennoch eigene Symbole dynamisch in Abhängigkeit von Zellwerten anzeigen lassen. In einem ersten Schritt legen Sie Grafiken als Liste in einem Arbeitsblatt ab. Danach erstellen Sie eine Formel, um auf die Symbolliste zu verweisen. Schließlich verknüpfen Sie einzelne Symbole mit der Formel.
In der Beispieldatei K407.xls sind die Ergebnisse zu einer fiktiven Kundenumfrage abgelegt (siehe Abbildung auf der nächsten Seite).
Ziel ist es, pro Thema in der Spalte D ein Symbol anzuzeigen, das das Ergebnis visualisiert. Für eine sehr gute Bewertung sollen hierbei drei Sterne als Symbol angezeigt werden, für eine gute Bewertung zwei Sterne und für eine befriedigende Bewertung nur ein Stern.
Symbole ganz einfach in die Arbeitsmappe einfügen.
Externe Grafiken bzw. Symbole fügen Sie ganz einfach in ein Arbeitsblatt ein: Öffnen Sie die Beispieldatei K407.xlsx und erstellen Sie ein neues Arbeitsblatt, das Sie in Symbole umbenennen.
Wählen Sie anschließend in der Registerkarte Einfügen in Excel 2013 den Befehl Bilder oder in Excel 2010 den Befehl Grafik.
Navigieren Sie im darauf erscheinenden Dialogfeld Grafik einfügen zu dem Ordner, in dem sich die Grafiken Stern-0.png bis Stern-4.png aus dem Beispielarchiv befinden. Klicken Sie die Grafiken bei gedrückter S-Taste an und bestätigen Ihre Auswahl mit Einfügen.
Alle vier Grafiken werden in das Arbeitsblatt eingefügt. Gleich nach dem Einfügen sind die Symbole markiert. Zie-hen Sie die Symbole mit der Maus in einen leeren Bereich rechts, sodass die Spalten A bis B frei bleiben.
Markieren Sie die Zeilen 1 bis 5 und setzen Sie die Zeilenhöhe auf den Wert 20. Stellen Sie die Spaltenbreite der Spalte A auf den Wert 20 und die Spaltenbreite der Spalte B auf den Wert 10 ein.
Geben Sie in den Zellen A1 und B1 die Überschriften "Bewertung" und "Symbol" ein. Tragen Sie in den Zellen A2 bis A5 die jeweiligen Bewertungen ein, wobei Sie in Zelle A2 den Text Keine angeben.
Positionieren Sie nun die einzelnen Symbole so, dass sich diese innerhalb der Zellen der Spalte B befinden und nicht in andere Zellen überlappen. Setzen Sie abschließend die Hintergrundfarbe des Bereichs A2:B5 auf Weiß, da sonst später die Gitternetzlinien in den dynamischen Symbolen mit angezeigt würden.
Per Formel auf die Symbolliste verweisen und Name erstellen
Über normale Verweisfunktionen lassen sich die Symbole aus der Symbolliste nicht direkt ansprechen. Deshalb verwenden Sie einen Trick, der anhand eines kleinen Umwegs über den Namens-Manager führt.
Zunächst bauen Sie die Formel auf, die auf ein Symbol verweisen wird. Betrachten Sie dazu die dritte Zeile im Arbeitsblatt zur Kundenumfrage: Der Verkaufsservice wurde in der Umfrage als "Sehr gut" bewertet.
Im Arbeitsblatt mit der Symbolliste entspricht diese Bewertung dem Wert in der Zelle A5. Das entsprechende Symbol, drei Sternchen, ist dort in Zelle B5 zu finden.
Eine Formel für ein dynamisches Symbol, das in Zelle D3 der Tabelle zur Kundenumfrage angezeigt würde, müsste somit zunächst in der Symbolliste im Bereich A2:A5 nach dem Wert "Sehr gut" suchen und dessen Position als Zahl ermitteln. Dann müsste die Formel die gefundene Position um eine Zelle nach rechts verschieben, um die Spalte B mit den Symbolen anzusprechen.
Um in einem Bereich nach einem Wert zu suchen und dessen Position im Bereich zu ermitteln, lässt sich die Funktion VERGLEICH verwenden, in unserem Fall:
=VERGLEICH(Kundenumfrage!$C$3;
Symbole!$A$2:$A$5;0).
Als erstes Argument erwartet diese Funktion die Angabe des gesuchten Werts, als zweites Argument den zu durchsuchen den Bereich und als drittes Argument einen Vergleichstyp.
Letzterer steht auf null und besagt, dass nach dem ersten zutreffenden Wert gesucht wird. Um einen Bereich dynamisch zu verschieben, steht die Funktion BEREICH.VERSCHIEBEN zur Verfügung. Ein Beispiel:
=BEREICH.VERSCHIEBEN(Symbole!$A$2;2;0;1;1)
Im ersten Argument der Funktion ist der Bezug anzugeben, von wo aus die Verschiebung stattfinden soll. Das zweite und dritte Argument geben an, um wie viele Zeilen und Spalten der Bezug verschoben werden soll. Die letzten zwei Argumente geben die Höhe und Breite des neuen Bezugs an. Im obigen Beispiel wird somit auf den Wert der Zelle A4 aus dem Arbeitsblatt Symbole referenziert, da der Bezug A2 in diesem Arbeitsblatt um zwei Zeilen verschoben wurde.
Wenn Sie beide Excel-Funktionen miteinander kombinieren, lässt sich die Zelle mit dem Symbol auf einfache Weise dynamisch adressieren:
=BEREICH.VERSCHIEBEN (Symbole!$A$2; VERGLEICH(Kundenumfrage!$C$3; Symbole!$A$2:$A$5;0)-1;1;1;1)
Beachten Sie, dass im zweiten Argument der Funktion BEREICH.VERSCHIEBEN zur Ermittlung der Zeile der Wert 1 abgezogen wird. Das liegt daran, dass die Funktion VERGLEICH einen Positionswert beginnend mit 1 liefert, eine Bereichsverschiebung ohne eine Zeilenänderung jedoch mit 0 beginnt.
Nachdem der Aufbau der Excel-Formel feststeht, weisen Sie dieser einen Namen zu. Rufen Sie dazu den Befehl Namen definieren aus der Registerkarte Formeln auf.
Geben Sie im Dialogfeld Neuer Name im Eingabefeld Name die Bezeichnung Symbol Verkaufsservice ein. Tragen Sie anschließend die Formel in das Eingabefeld Bezieht sich auf ein. Beenden Sie das Dialogfeld mit OK.
Dynamischen Symbolen einen Namen zuordnen
Nachdem Sie nun die Formel erstellt und einem Namen zugewiesen haben, folgt der letzte Schritt: das Zuweisen des definierten Namens zu einem dynamischen Symbol.
Kopieren Sie aus dem Arbeitsblatt mit der Symbolliste das Symbol in der Zelle B2, indem Sie dieses anklicken und die Tastenkombination S+c drücken. Fügen Sie das Symbol anhand der Tastenkombination S+v in das Arbeitsblatt zur Kundenumfrage ein. Positionieren Sie das eingefügte Symbol in Zelle D3. Klicken Sie das kopierte Symbol an und geben Sie in der Bearbeitungszeile den angelegten Namen Symbol Verkaufsservice inklusive eines vorangestellten Gleichheitszeichens ein.
Bestätigen Sie Ihre Eingabe mit der Ü-Taste. Sofort werden als Symbol drei Sterne angezeigt.
Um die restlichen Symbole zu den weiteren Themen der Umfrage anzuzeigen, wiederholen Sie mit leichten Anpassungen pro Symbol die zuvor erläuterten Schritte: Erstellen Sie einen neuen Namen für das anzuzeigende Symbol, z. B. Symbol Lieferzeiten, und geben Sie die Formel zu den Namen ein. Passen Sie in der Formel dem Namen entsprechend die Zellbezüge an. Duplizieren Sie ein bereits erstelltes Symbol und weisen Sie ihm den neuen Namen zu.
Zusammenfassung
In nur wenigen Schritten lassen sich in Excel individuell gestaltete Symbole dynamisch zur Visualisierung von Daten verwenden. Hierzu legen Sie in einem ersten Schritt innerhalb einer Tabelle eine Liste der gewünschten Symbole an, indem Sie die Symbole in Zellen einer Spalte untereinander einfügen.
Eine zusätzliche Spalte mit Suchwerten ermöglicht das Auffinden einer Zeile innerhalb der Tabelle für die Symbolliste. In einem zweiten Schritt erstellen Sie eine Formel, die dynamisch anhand eines Suchkriteriums die Zelle mit dem gewünschten Symbol ermittelt. Dieser Formel weisen Sie einen Namen zu und verknüpfen diesen mit einem dynamischen Symbol.
