Excel-Dashboards - Interaktive Steuerelemente
Wenn Sie mehr Daten im Dashboard anzeigen möchten, die nicht in einen einzelnen Bildschirm passen, können Sie Excel-Steuerelemente verwenden, die Teil von Excel Visual Basic sind. Die am häufigsten verwendeten Steuerelemente sind Bildlaufleisten, Optionsfelder und Kontrollkästchen. Indem Sie diese in das Dashboard integrieren, können Sie es interaktiv gestalten und dem Benutzer ermöglichen, die verschiedenen Facetten der Daten durch mögliche Auswahlen anzuzeigen.
Sie können interaktive Steuerelemente wie Bildlaufleisten, Kontrollkästchen und Optionsfelder in Ihren Dashboards bereitstellen, damit die Empfänger die verschiedenen Facetten der Daten, die als Ergebnisse angezeigt werden, dynamisch anzeigen können. Sie können zusammen mit den Empfängern ein bestimmtes Layout des Dashboards festlegen und dann dasselbe Layout verwenden. Interaktive Excel-Steuerelemente sind einfach zu verwenden und erfordern keine Excel-Kenntnisse.
Die interaktiven Excel-Steuerelemente sind auf der Registerkarte ENTWICKLER in der Multifunktionsleiste verfügbar.
Wenn Sie die Registerkarte ENTWICKLER auf der Multifunktionsleiste nicht finden, gehen Sie wie folgt vor:
- Klicken Sie im Feld Excel-Optionen auf Multifunktionsleiste anpassen.
- Wählen Sie im Feld "Multifunktionsleiste anpassen" die Option "Hauptregisterkarten".
- Aktivieren Sie das Kontrollkästchen Entwickler in der Liste der Hauptregisterkarten.
- Klicken Sie auf OK. Sie finden die Registerkarte ENTWICKLER auf der Multifunktionsleiste.
Bildlaufleisten in Dashboards
Eines der Merkmale eines Dashboards ist, dass jede Komponente im Dashboard so kompakt wie möglich ist. Angenommen, Ihre Ergebnisse sehen wie folgt aus:
Wenn Sie diese Tabelle mit einer Bildlaufleiste wie unten angegeben darstellen können, ist es einfacher, die Daten zu durchsuchen.
Sie können auch eine dynamische Ziellinie in einem Balkendiagramm mit Bildlaufleiste haben. Wenn Sie die Bildlaufleiste nach oben und unten bewegen, bewegt sich die Ziellinie nach oben und unten, und die Balken, die die Ziellinie überqueren, werden hervorgehoben.
In den folgenden Abschnitten erfahren Sie, wie Sie eine Bildlaufleiste erstellen und eine dynamische Ziellinie erstellen, die mit einer Bildlaufleiste verknüpft ist. Außerdem erfahren Sie, wie Sie dynamische Beschriftungen in Bildlaufleisten anzeigen.
Erstellen einer Bildlaufleiste
Um eine Bildlaufleiste für eine Tabelle zu erstellen, kopieren Sie zuerst die Überschriften der Spalten in einen leeren Bereich auf dem Blatt, wie unten gezeigt.
Fügen Sie eine Bildlaufleiste ein.
Klicken Sie auf der Multifunktionsleiste auf die Registerkarte ENTWICKLER.
Klicken Sie in der Gruppe Steuerelemente auf Einfügen.
Klicken Sie in der Dropdown-Liste der Symbole unter Formularsteuerelemente auf das Symbol für die Bildlaufleiste.
Bewegen Sie den Cursor auf die Spalte I und ziehen Sie sie nach unten, um eine vertikale Bildlaufleiste einzufügen.
Passen Sie die Höhe und Breite der Bildlaufleiste an und richten Sie sie an der Tabelle aus.
Klicken Sie mit der rechten Maustaste auf die Bildlaufleiste.
Klicken Sie in der Dropdown-Liste auf Format Control.
Das Dialogfeld Formatsteuerung wird angezeigt.
Klicken Sie auf die Registerkarte Steuerung.
Geben Sie Folgendes in die angezeigten Felder ein.
Klicken Sie auf die Schaltfläche OK. Die Bildlaufleiste ist einsatzbereit. Sie haben die Zelle O2 als Zellenverknüpfung für die Bildlaufleiste ausgewählt, die die Werte 0 bis 36 annimmt, wenn Sie die Bildlaufleiste nach oben und unten bewegen. Als Nächstes müssen Sie eine Kopie der Daten in der Tabelle mit einer Referenz erstellen, die auf dem Wert in der Zelle O2 basiert.
Geben Sie in die Zelle K3 Folgendes ein:
= OFFSET (Zusammenfassung [@ [S. Nr.]], $ O $ 2,0).
Drücken Sie die Eingabetaste. Füllen Sie die Zellen in der Spalte aus, in der die Formel kopiert wird.
Füllen Sie die Zellen in den anderen Spalten aus, in denen die Formel kopiert wird.
Ihre dynamische und scrollbare Tabelle kann in Ihr Dashboard kopiert werden.
Bewegen Sie die Bildlaufleiste nach unten.
Wie Sie sehen können, ändert sich der Wert in der Zellenverknüpfung der Zellen-Bildlaufleiste, und die Daten in der Tabelle werden basierend auf diesem Wert kopiert. Es werden jeweils 12 Datenzeilen angezeigt.
Ziehen Sie die Bildlaufleiste nach unten.
Die letzten 12 Zeilen der Daten werden angezeigt, da der aktuelle Wert 36 ist (wie in der Zelle O2 gezeigt) und 36 der Maximalwert ist, den Sie im Dialogfeld Formularsteuerung festgelegt haben.
Sie können die relative Position der dynamischen Tabelle ändern, die Anzahl der gleichzeitig anzuzeigenden Zeilen, den Zellenlink zur Bildlaufleiste usw. entsprechend Ihren Anforderungen ändern. Wie Sie oben gesehen haben, müssen diese im Dialogfeld Formatsteuerung festgelegt werden.
Erstellen einer dynamischen und interaktiven Ziellinie
Angenommen, Sie möchten den Umsatz in den letzten 6 Monaten regional anzeigen. Sie haben auch Ziele für jeden Monat festgelegt.
Sie können Folgendes tun:
- Erstellen Sie ein Säulendiagramm mit all diesen Informationen.
- Erstellen Sie eine Ziellinie über die Spalten.
- Machen Sie die Ziellinie mit einer Bildlaufleiste interaktiv.
- Machen Sie die Ziellinie dynamisch und legen Sie die Zielwerte in Ihren Daten fest.
- Markieren Sie Werte, die das Ziel erreichen.
Erstellen Sie ein Säulendiagramm mit all diesen Informationen
Wählen Sie die Daten aus. Fügen Sie ein gruppiertes Säulendiagramm ein.
Erstellen Sie eine Ziellinie über die Spalten
Ändern Sie den Diagrammtyp in Combo. Wählen Sie den Diagrammtyp als Linie für die Zielserie und Clustered Column für den Rest der Serie.
Erstellen Sie eine Basistabelle für die Ziellinie. Sie werden dies später dynamisieren.
Ändern Sie die Datenreihenwerte für die Ziellinie in die Spalte Ziel in der obigen Tabelle.
Klicken Sie auf die Schaltfläche OK.
Ändern Sie das Farbschema für die gruppierte Spalte. Ändern Sie die Ziellinie in eine grün gepunktete Linie.
Machen Sie die Ziellinie mit einer Bildlaufleiste interaktiv
Fügen Sie eine Bildlaufleiste ein, platzieren Sie sie unter dem Diagramm und passen Sie sie von Januar bis Juni an.
Geben Sie die Parameter der Bildlaufleiste in das Dialogfeld Formatsteuerung ein.
Erstellen Sie eine Tabelle mit zwei Spalten - Monat und Ziel.
Geben Sie die Werte basierend auf der Datentabelle und dem Zellenlink der Bildlaufleiste ein.
In dieser Tabelle werden der Monat und das entsprechende Ziel basierend auf der Position der Bildlaufleiste angezeigt.
Machen Sie die Ziellinie dynamisch und legen Sie die Zielwerte in Ihren Daten fest
Jetzt können Sie Ihre Ziellinie dynamisch gestalten.
Ändern Sie die Zielspaltenwerte in der Basistabelle, die Sie für die Zielzeile erstellt haben, indem Sie in alle Zeilen = $ G $ 12 eingeben.
Wie Sie wissen, zeigt die Zelle G12 den Zielwert dynamisch an.
Wie Sie sehen können, bewegt sich die Ziellinie basierend auf der Bildlaufleiste.
Markieren Sie Werte, die das Ziel erreichen
Dies ist der letzte Schritt. Sie möchten die Werte hervorheben, die zu jedem Zeitpunkt das Ziel erreichen.
Fügen Sie der rechten Seite Ihrer Datentabelle Spalten hinzu - Ost-Ergebnisse, Nord-Ergebnisse, Süd-Ergebnisse und West-Ergebnisse.
Geben Sie in die Zelle H3 die folgende Formel ein:
= IF (D3> = $ G $ 12, D3, NA ())
Kopieren Sie die Formel in die anderen Zellen in der Tabelle. Ändern Sie die Größe der Tabelle.
Wie Sie sehen können, ändern sich die Werte in den Spalten - Ost-Ergebnisse, Nord-Ergebnisse, Süd-Ergebnisse und West-Ergebnisse dynamisch basierend auf der Bildlaufleiste (dh Zielwert). Werte größer oder gleich dem Ziel werden angezeigt und die anderen Werte sind nur # N / A.
Ändern Sie den Diagrammdatenbereich, um die neu hinzugefügten Spalten in die Datentabelle aufzunehmen.
Klicken Sie auf Diagrammtyp ändern.
Stellen Sie die Zielserie als Linie und die restliche gruppierte Spalte ein.
Wählen Sie für die neu hinzugefügten Datenreihen Sekundärachse aus.
Formatieren Sie Datenreihen so, dass die Reihen Ost, Nord, Süd und West die Füllfarbe Orange und die Reihen Ost-Ergebnisse, Nord-Ergebnisse, Süd-Ergebnisse und West-Ergebnisse die Füllfarbe Grün haben.
Geben Sie eine Datenbezeichnung für die Ziellinie ein und machen Sie sie dynamisch, wobei sich die Zelle auf den Monatswert in der dynamischen Datentabelle bezieht.
Ihr Diagramm mit dynamischer Ziellinie kann in das Dashboard aufgenommen werden.
Sie können die Sekundärachse löschen, da dies nicht erforderlich ist. Wenn Sie die Bildlaufleiste verschieben, wird die Ziellinie verschoben und die Balken werden entsprechend hervorgehoben. Die Ziellinie hat auch eine Beschriftung mit dem Monat.
Excel-Optionsschaltflächen (Optionsfelder)
Optionsfelder werden normalerweise verwendet, um eine Option aus einem bestimmten Satz von Optionen auszuwählen. Es wird immer durch einen kleinen Kreis dargestellt, der bei Auswahl einen Punkt enthält. Wenn Sie über mehrere Optionsfelder verfügen, können Sie nur eines davon auswählen.
In Excel werden Optionsfelder als Optionsschaltflächen bezeichnet.
Sie können Excel-Optionsschaltflächen in Diagrammen verwenden, um die Datenspezifikationen auszuwählen, die der Leser anzeigen möchte. Im Beispiel im vorherigen Abschnitt haben Sie beispielsweise eine Bildlaufleiste erstellt, um eine dynamische Ziellinie mit Zielwerten basierend auf Monat zu erhalten. Mit den Optionsschaltflächen können Sie einen Monat und damit den Zielwert auswählen und die Ziellinie auf dem Zielwert basieren. Es folgen die Schritte -
- Erstellen Sie ein Säulendiagramm mit all diesen Informationen.
- Erstellen Sie eine Ziellinie über die Spalten.
- Machen Sie die Ziellinie mit Optionsschaltflächen interaktiv.
- Machen Sie die Ziellinie dynamisch und legen Sie die Zielwerte in Ihren Daten fest.
- Markieren Sie Werte, die das Ziel erreichen.
Die Schritte 1 und 2 sind dieselben wie im vorherigen Fall. Am Ende des zweiten Schritts haben Sie die folgende Tabelle.
Machen Sie die Ziellinie mit Optionsschaltflächen interaktiv
Fügen Sie eine Optionstaste ein.
Klicken Sie auf der Multifunktionsleiste auf die Registerkarte ENTWICKLER.
Klicken Sie in der Gruppe Steuerelemente auf Einfügen.
Klicken Sie in der Dropdown-Liste der Symbole unter Formularsteuerelemente auf das Optionsschaltflächensymbol.
Platzieren Sie es in der oberen rechten Ecke des Diagramms.
Klicken Sie mit der rechten Maustaste auf die Schaltfläche Option. Klicken Sie in der Dropdown-Liste auf die Option Formatsteuerung.
Geben Sie die Parameter der Optionsschaltfläche im Dialogfeld Objekt formatieren auf der Registerkarte Steuerung ein.
Die Zelle F10 ist mit der Optionstaste verbunden. Erstellen Sie 5 Kopien der Optionstaste vertikal.
Wie Sie sehen können, haben alle Optionsschaltflächen denselben Namen, der als Beschriftungsnamen bezeichnet wird. Intern hat Excel jedoch unterschiedliche Namen für diese Optionsschaltflächen, die Sie entweder im Feld Name anzeigen können. Da Optionstaste 1 so eingestellt wurde, dass sie mit der Zelle F10 verknüpft ist, beziehen sich alle Kopien auch auf dieselbe Zelle.
Klicken Sie auf eine der Optionsschaltflächen.
Wie Sie sehen können, ändert sich die Nummer in der verknüpften Zelle in die Seriennummer der Optionstaste. Benennen Sie die Optionsschaltflächen in Januar, Februar, März, April, Mai und Juni um.
Erstellen Sie eine Tabelle mit zwei Spalten - Monat und Ziel. Geben Sie die Werte basierend auf der Datentabelle und dem Zellenlink der Bildlaufleiste ein.
In dieser Tabelle werden der Monat und das entsprechende Ziel basierend auf der ausgewählten Optionstaste angezeigt.
Machen Sie die Ziellinie dynamisch und legen Sie die Zielwerte in Ihren Daten fest
Jetzt können Sie Ihre Ziellinie dynamisch gestalten.
Ändern Sie die Zielspaltenwerte in der Basistabelle, die Sie für die Zielzeile erstellt haben, indem Sie in alle Zeilen = $ G $ 12 eingeben.
Wie Sie wissen, zeigt die Zelle G12 den Zielwert dynamisch an.
Wie Sie sehen können, wird die Ziellinie basierend auf der ausgewählten Optionstaste angezeigt.
Markieren Sie Werte, die das Ziel erreichen
Dies ist der letzte Schritt. Sie möchten die Werte hervorheben, die zu jedem Zeitpunkt das Ziel erreichen.
Fügen Sie der rechten Seite Ihrer Datentabelle Spalten hinzu - Ost-Ergebnisse, Nord-Ergebnisse, Süd-Ergebnisse und West-Ergebnisse.
Geben Sie in die Zelle H3 die folgende Formel ein:
= IF (D3> = $ G $ 12, D3, NA ())
Kopieren Sie die Formel in die anderen Zellen in der Tabelle. Ändern Sie die Größe der Tabelle.
Wie Sie sehen können, ändern sich die Werte in den Spalten - Ost-Ergebnisse, Nord-Ergebnisse, Süd-Ergebnisse und West-Ergebnisse dynamisch basierend auf der Bildlaufleiste (dh Zielwert). Werte größer oder gleich dem Ziel werden angezeigt und die anderen Werte sind nur # N / A.
Ändern Sie den Diagrammdatenbereich, um die neu hinzugefügten Spalten in die Datentabelle aufzunehmen.
Klicken Sie auf Diagrammtyp ändern.
Stellen Sie die Zielserie als Linie und die restliche gruppierte Spalte ein.
Wählen Sie für die neu hinzugefügten Datenreihen Sekundärachse aus.
Formatieren Sie Datenreihen so, dass die Reihen Ost, Nord, Süd und West die Füllfarbe Orange und die Reihen Ost-Ergebnisse, Nord-Ergebnisse, Süd-Ergebnisse und West-Ergebnisse die Füllfarbe Grün haben.
Fügen Sie der Zielzeile eine dynamische Datenbeschriftung mit einem Wert aus der Zelle $ G $ 12 hinzu.
Löschen Sie die Sekundärachse, da dies nicht erforderlich ist.
Deaktivieren Sie auf der Multifunktionsleiste auf der Registerkarte ANSICHT das Kontrollkästchen Gitternetzlinien.
Ändern Sie die Option "Beschriftung" in den Optionen "Achse formatieren" in "Hoch". Dadurch werden die Beschriftungen der vertikalen Achse nach rechts verschoben, sodass die Beschriftung der Zielliniendaten auffällt.
Ihr Diagramm mit dynamischen Ziellinien- und Optionsschaltflächen kann in das Dashboard aufgenommen werden.
Wenn Sie eine Optionstaste auswählen, wird die Ziellinie gemäß dem Zielwert des ausgewählten Monats angezeigt und die Balken werden entsprechend hervorgehoben. Die Ziellinie hat auch eine Datenbeschriftung, die den Zielwert anzeigt.
Excel-Kontrollkästchen
Kontrollkästchen werden normalerweise verwendet, um eine oder mehrere Optionen aus einem bestimmten Satz von Optionen auszuwählen. Kontrollkästchen werden immer durch kleine Quadrate dargestellt, die bei Auswahl ein Häkchen setzen. Wenn Sie über mehrere Kontrollkästchen verfügen, können Sie eine beliebige Anzahl von Kontrollkästchen auswählen. Zum Beispiel,
Sie können Excel-Kontrollkästchen in Diagrammen verwenden, um die Datenspezifikationen auszuwählen, die der Leser anzeigen möchte. Im Beispiel im vorherigen Abschnitt haben Sie beispielsweise ein Säulendiagramm erstellt, in dem die Daten von 4 Regionen angezeigt werden - Ost, Nord, Süd und West. Mit Kontrollkästchen können Sie die Regionen auswählen, für die Daten angezeigt werden. Sie können beliebig viele Regionen gleichzeitig auswählen.
Sie können mit dem letzten Schritt des vorherigen Abschnitts beginnen -
Fügen Sie ein Kontrollkästchen ein.
Klicken Sie auf der Multifunktionsleiste auf die Registerkarte ENTWICKLER.
Klicken Sie in der Gruppe Steuerelemente auf Einfügen.
Klicken Sie in der Dropdown-Liste der Symbole unter Formularsteuerelemente auf das Kontrollkästchensymbol.
Platzieren Sie es in der oberen linken Ecke des Diagramms.
Ändern Sie den Namen des Kontrollkästchens in Ost.
Klicken Sie mit der rechten Maustaste auf das Kontrollkästchen. Klicken Sie in der Dropdown-Liste auf Format Control.
Geben Sie die Kontrollkästchenparameter im Dialogfeld Formatsteuerung auf der Registerkarte Steuerung ein.
Klicken Sie auf die Schaltfläche OK. Sie können beobachten, dass in der verknüpften Zelle C19 TRUE angezeigt wird, wenn Sie das Kontrollkästchen aktivieren, und FALSE angezeigt wird, wenn Sie das Kontrollkästchen deaktivieren.
Kopieren Sie das Kontrollkästchen und fügen Sie es dreimal horizontal ein.
Ändern Sie die Namen in Nord, Süd und West.
Wie Sie sehen können, bleibt beim Verknüpfen eines Kontrollkästchens die verknüpfte Zelle auch für das kopierte Kontrollkästchen dieselbe. Da Kontrollkästchen jedoch mehrere Auswahlen haben können, müssen Sie die verknüpften Zellen unterschiedlich machen.
Ändern Sie die verknüpften Zellen für Nord, Süd und West in $ 20, $ 21 und $ 22.
Der nächste Schritt besteht darin, nur die Daten der ausgewählten Regionen im Diagramm zu haben.
Erstellen Sie eine Tabellenstruktur wie folgt:
- Geben Sie = IF ($ C $ 19, H3, NA ()) in die Zelle C21 ein.
- Geben Sie = IF ($ D $ 19, I3, NA ()) in die Zelle D21 ein.
- Geben Sie = IF ($ E $ 19, J3, NA ()) in die Zelle E21 ein.
- Geben Sie = IF ($ F $ 19, K3, NA ()) in die Zelle F21 ein.
- Füllen Sie andere Zeilen in der Tabelle aus.
Fügen Sie die Spalte Ziel hinzu.
Ändern Sie die Diagrammdaten in diese Tabelle.
Das Diagramm zeigt die Daten für die ausgewählten Regionen an, die über dem für den ausgewählten Monat festgelegten Zielwert liegen.