TOP

Interaktives Diagramm

Beschreibung

Die qualitativ hochwertige Visualisierung einer großen Menge an Informationen ist fast immer eine nicht triviale Aufgabe, daher führt die Anzeige aller Daten oft zu einer Überlastung des Diagramms, seiner Verwirrung und infolgedessen zu falschen Wahrnehmungen und Schlussfolgerungen.


Hier zum Beispiel Daten zu Wechselkursen für mehrere Monate:

Wie Sie sehen, ist es keine gute Idee, die gesamte Tabelle darzustellen. Eine schöne Lösung in einer ähnlichen Situation kann die Erstellung eines interaktiven Diagramms sein, das der Benutzer an sich und die Situation anpassen kann. Nämlich:

Es könnte etwa so aussehen:

Gefällt es dir? Dann gingen wir ...

Schritt 1. Wir erstellen eine zusätzliche Tabelle für das Diagramm

In den meisten Fällen wird eine einfache, aber leistungsstarke Technik verwendet, um die Interaktivität des Diagramms zu implementieren: Das Diagramm wird nicht nach dem Original erstellt, sondern nach einer separaten, speziell erstellten Tabelle mit Formeln, die nur die erforderlichen Daten anzeigt. In unserem Fall werden die Ausgabedaten nur für die Währungen, die der Benutzer über die Kontrollkästchen ausgewählt hat, in diese Zusatztabelle übertragen:

In Excel 2007/2010 können Sie einen Befehl auf die erstellten Bereiche anwenden Als Tabelle formatieren (Format as Table) aus der Registerkarte Hauptsächlich (Home) :

Dadurch ergeben sich für uns folgende Vorteile:

Schritt 2. Kontrollkästchen für Währungen hinzufügen

In Excel 2007/2010 müssen Sie die Registerkarte dafür anzeigen Entwickler (Developer) , und in Excel 2003 und älteren Versionen – die Symbolleiste Formen (Forms) . Dafür:

Was erschienen Symbolleisten oder Registerkarten? Entwickler (Developer) in der Dropdown-Liste Einfügen (Insert) Wählen Sie ein Werkzeug Flagge (Checkbox) und setzen Sie zwei Häkchen, um die einzelnen Währungen zu aktivieren/deaktivieren:

Sie können den Text der Flaggen ändern, indem Sie mit der rechten Maustaste darauf klicken und den Befehl auswählen Ändern Sie den Text (Edit text) .

Binden wir nun unsere Flags an beliebige Zellen, um festzustellen, ob das Flag aktiviert ist oder nicht (in unserem Beispiel sind dies die beiden gelben Zellen oben in der Zusatztabelle). Klicken Sie dazu nacheinander mit der rechten Maustaste auf jedes hinzugefügte Flag und wählen Sie einen Befehl aus Objektformat (Format Control) , und dann im Fenster einstellen Kommunikation mit der Zelle (Cell link) .

Unser Ziel ist es, dass jedes Kontrollkästchen mit der entsprechenden gelben Zelle über der Währungsspalte verknüpft ist. Wenn das Kontrollkästchen in der zugehörigen Zelle aktiviert ist, wird es angezeigt WAHRHEIT (TRUE) , beim Ausschalten - LÜGE (FALSE) . Dies ermöglicht es künftig, die verbundenen Zellen mit Hilfe von Formeln zu überprüfen und in einer zusätzlichen Tabelle oder den Wechselkurswert aus der Originaltabelle zur Erstellung eines Diagramms anzuzeigen, oder #N / A (#N/A) , so dass der Graph nicht erstellt wird.

Schritt 3. Wir übersetzen die Daten in eine zusätzliche Tabelle

Füllen wir nun die Sekundärtabelle mit einer Formel, die die Ausgabedaten aus der Haupttabelle übersetzt, wenn das entsprechende Währungsflag aktiviert ist und die zugehörige Zelle das Wort enthält WAHRHEIT (TRUE) :

Beachten Sie dies bei Verwendung des Befehls Als Tabelle formatieren (Format as Table) Im ersten Schritt muss die Formel den Namen der Tabelle und den Namen der Spalte verwenden. Im Falle eines regulären Bereichs wird die Formel bekannter aussehen:

=ЕСЛИ(F$1; B4; #Н/Д)

Beachten Sie die teilweise Verankerung des Verweises auf die gelbe Zelle (F$1), sodass er sich beim Kopieren der Formel in den gesamten Bereich nach rechts, aber nicht nach unten verschieben sollte.

Wenn wir nun die Flags überprüfen, wird unsere Zusatztabelle entweder mit Daten aus der Originaltabelle oder mit einem künstlich erzeugten Fehler # N/A gefüllt, der keine Linie im Diagramm ergibt.

Schritt 4. Wir erstellen Bildlaufleisten für die Zeitachse und Skalierung

Fügen wir nun dem Blatt Excel Bildlaufleisten hinzu, mit deren Hilfe der Benutzer das Diagramm einfach entlang der Zeitachse verschieben und den Maßstab seiner Vergrößerung ändern kann.

Scrollleiste (Scroll bar) Wir platzieren es an der gleichen Stelle wie die Flaggen – in der Symbolleiste Formen (Forms) oder auf einer Registerkarte Entwickler (Developer) :

Wir zeichnen an beliebiger geeigneter Stelle zwei Streifen nacheinander auf das Blatt – für eine zeitliche und maßstabsgetreue Verschiebung:

Jeder Bildlaufleiste muss eine eigene Zelle zugeordnet sein (blaue und grüne Zellen im Bild), in der der numerische Wert der Schiebereglerposition angezeigt wird. Wir werden es dann verwenden, um den Maßstab und den Versatz zu bestimmen. Klicken Sie dazu mit der rechten Maustaste auf den gezeichneten Streifen und wählen Sie einen Befehl aus dem Kontextmenü Objektformat (Format control) . Im Dialogfeld können Sie die zugehörige Zelle und das Minimum/Maximum innerhalb beliebiger Grenzen festlegen, in denen sich der Schieberegler bewegt:

Nachdem Sie alle oben genannten Schritte ausgeführt haben, sollten Sie beim Bewegen der Schieberegler zwei Bildlaufleisten haben, entlang derer sich die Werte in den entsprechenden Zellen im Bereich von 1 bis 307 ändern sollten.

Schritt 5: Erstellen Sie einen dynamischen benannten Bereich

Um Daten im Diagramm nur für ein bestimmtes Zeitintervall anzuzeigen, erstellen wir einen benannten Bereich, der sich nur auf die erforderlichen Zellen in der Zusatztabelle bezieht. Dieser Bereich wird durch zwei Parameter charakterisiert:

Wir werden diesen benannten Bereich später als Rohdaten zum Erstellen des Diagramms verwenden.

Um einen solchen Bereich zu erstellen, verwenden wir die Funktion СМЕЩ (OFFSET) aus der Kategorie Referenzen und Arrays (Lookup and Reference) – Diese Funktion kann einen Verweis auf einen Bereich einer bestimmten Größe an einer bestimmten Stelle im Blatt erstellen und verfügt über die folgenden Argumente:

Als Ausgangspunkt wird eine Startzelle genommen, dann wird eine Verschiebung relativ dazu um eine bestimmte Anzahl von Zeilen nach unten und Spalten nach rechts eingestellt. Die letzten beiden Argumente dieser Funktion sind die Höhe und Breite des benötigten Bereichs. Wenn wir beispielsweise auf einen Datenbereich mit Tarifen für 5 Tage ab dem 4. Januar verweisen möchten, könnten wir unsere Funktion verwenden СМЕЩ (OFFSET) mit folgenden Argumenten:

=СМЕЩ(A3;4;1;5;2)

Der Trick besteht darin, dass die Konstanten in dieser Formel durch Verweise auf Zellen mit variablem Inhalt ersetzt werden können – in unserem Fall die blauen und grünen Zellen. Sie können dies tun, indem Sie einen dynamischen benannten Bereich mit einer Funktion erstellen СМЕЩ (OFFSET) . Dafür:

Klicken Sie auf die Schaltfläche, um einen neuen benannten Bereich zu erstellen Erstellen (Create) und geben Sie den Bereichsnamen und die Zellbezüge in das Fenster ein.

Erstellen wir zunächst beispielsweise zwei einfache statische benannte Bereiche mit Namen Shift Und Zoom , was sich jeweils auf die blauen und grünen Zellen bezieht:

       

Jetzt ist es etwas komplizierter – erstellen wir einen Bereich mit einem Namen Euros , auf die von der Funktion verwiesen wird СМЕЩ (OFFSET) auf den Daten der Euro-Wechselkurse für den ausgewählten Zeitraum unter Verwendung der zuvor erstellten Spannen Schicht Und Zoom und eine Zelle E3 als Anhaltspunkt:

Beachten Sie, dass der Name des aktuellen Blatts vor dem Namen des Bereichs verwendet wird. Dadurch wird der Umfang des benannten Bereichs eingeschränkt, d. h., er wird innerhalb der Grenzen des aktuellen Blatts und nicht der gesamten Arbeitsmappe verfügbar gemacht. Dies ist notwendig, damit wir in Zukunft ein Diagramm erstellen können. In neueren Versionen von Excel können Sie die Dropdown-Liste verwenden, um einen lokalen Blattnamen zu erstellen Region .

Auf ähnliche Weise wird ein benannter Bereich erstellt Dollars für Daten zum Dollar-Wechselkurs:

Und das Sortiment rundet das Bild ab Labels , das die Signaturen auf der X-Achse angibt, also die Daten für das ausgewählte Segment:

Das allgemeine Bild sollte ungefähr wie folgt aussehen:

Schritt 6. Wir erstellen ein Diagramm

Wählen wir im oberen Teil der Hilfstabelle mehrere Zeilen aus, beispielsweise den Bereich E3:G10, und erstellen wir darauf basierend ein Typdiagramm Zeitplan (Line) . Dazu müssen Sie in Excel 2007/2010 auf die Registerkarte gehen Einfügen (Insert) und in der Gruppe Diagramm (Chart) Wählen Sie einen Typ aus Zeitplan (Line) , und in älteren Versionen wählen Sie aus dem Menü Einschub – Diagramm (Insert - Chart) . Wenn Sie eine der Linien im erstellten Diagramm markieren, wird die Funktion in der Bearbeitungsleiste angezeigt РЯД (SERIES) , das eine ausgewählte Reihe von Daten bereitstellt:

Diese Funktion legt die Datenbereiche und Beschriftungen für die ausgewählte Diagrammreihe fest. Unsere Aufgabe besteht darin, die statischen Bereiche in seinen Argumenten durch die dynamischen Bereiche zu ersetzen, die wir zuvor erstellt haben. Dies kann direkt in der Bearbeitungsleiste erfolgen, indem Sie Folgendes ändern:

=РЯД(Лист1!$F$3; Лист1!$E$4:$E$10 ; Лист1!$F$4:$F$10 ; 1)

An:

=РЯД(Лист1!$F$3; Лист1! Labels ; Лист1! Euros ; 1)

Nachdem wir diesen Vorgang nacheinander für die Datenreihen des Dollars und des Euro durchgeführt haben, erhalten wir, was wir wollten – das Diagramm wird nach dynamischen Bereichen erstellt Dollars Und Euros , und Signaturen zur X-Achse werden aus demselben Dynamikbereich übernommen Labels . Wenn Sie die Position der Schieberegler ändern, ändern sich die Bereiche und damit auch das Diagramm. Beim Ein- und Ausschalten der Flags werden nur die Währungen angezeigt, die wir benötigen.

Somit verfügen wir über ein vollständig interaktives Diagramm, in dem wir genau die Daten anzeigen können, die wir für die Analyse benötigen.

Artikel zum Thema: