Diagramm nach ausgewählter Zelle

Angenommen, Sie und ich müssen Daten aus der folgenden Tabelle mit Autoverkaufswerten nach verschiedenen Ländern im Jahr 2021 visualisieren (echte Daten stammen übrigens von hier):

Da die Anzahl der Datenreihen (Länder) groß ist, führt der Versuch, alle auf einmal in ein Diagramm zu packen, entweder zu einem schrecklichen „Spaghetti-Diagramm“ oder zum Erstellen separater Diagramme für jede Reihe, was sehr umständlich ist.

Eine elegante Lösung für dieses Problem kann darin bestehen, ein Diagramm nur auf den Daten der aktuellen Zeile zu zeichnen, dh der Zeile, in der sich die aktive Zelle befindet:

Die Implementierung ist sehr einfach – Sie benötigen nur zwei Formeln und ein winziges Makro in 3 Zeilen.

Schritt 1. Aktuelle Zeilennummer

Das erste, was wir brauchen, ist ein benannter Bereich, der die Zeilennummer auf dem Blatt berechnet, in der sich unsere aktive Zelle jetzt befindet. Öffnen auf einem Tab Formeln – Namensmanager (Formeln – Namensmanager)Klicken Sie auf die Schaltfläche Erstellen (Schaffen) und trage dort folgende Struktur ein:

Schritt 2. Link zum Titel

Um das ausgewählte Land im Titel und in der Legende des Diagramms anzuzeigen, müssen wir einen Verweis auf die Zelle mit ihrem (Länder-) Namen aus der ersten Spalte erhalten. Dazu erstellen wir ein weiteres lokales (dh Gebiet = aktuelles Blatt, nicht Buch!) einen benannten Bereich mit folgender Formel:

Hier wählt die INDEX-Funktion aus einem gegebenen Bereich (Spalte A, wo unsere unterzeichnenden Länder liegen) eine Zelle mit der Zeilennummer aus, die wir zuvor bestimmt haben.

Schritt 3. Link zu Daten

Lassen Sie uns nun auf ähnliche Weise einen Link zu einem Bereich mit allen Verkaufsdaten aus der aktuellen Zeile erhalten, in der sich jetzt die aktive Zelle befindet. Erstellen Sie einen weiteren benannten Bereich mit der folgenden Formel:

Hier bewirkt das dritte Argument, das Null ist, dass INDEX nicht einen einzelnen Wert zurückgibt, sondern die gesamte Zeile als Ergebnis.

Schritt 4. Ersetzen von Links im Diagramm

Wählen Sie nun den Tabellenkopf und die erste Zeile mit Daten (Bereich) aus und erstellen Sie anhand dieser ein Diagramm Einfügen – Diagramme (Einfügen — Diagramme). Wenn Sie im Diagramm eine Zeile mit Daten auswählen, wird die Funktion in der Bearbeitungsleiste angezeigt REIHE (SERIE) ist eine spezielle Funktion, die Excel beim Erstellen eines beliebigen Diagramms automatisch verwendet, um auf die ursprünglichen Daten und Beschriftungen zu verweisen:

Lassen Sie uns das erste (Signatur) und dritte (Daten) Argument in dieser Funktion vorsichtig durch die Namen unserer Bereiche aus den Schritten 2 und 3 ersetzen:

Das Diagramm beginnt mit der Anzeige von Verkaufsdaten aus der aktuellen Zeile.

Schritt 5. Neuberechnungsmakro

Der letzte Schliff bleibt. Microsoft Excel berechnet Formeln nur dann neu, wenn sich die Daten auf dem Blatt ändern oder wenn eine Taste gedrückt wird F9, und wir möchten, dass die Neuberechnung erfolgt, wenn sich die Auswahl ändert, dh wenn die aktive Zelle über das Blatt bewegt wird. Dazu müssen wir unserer Arbeitsmappe ein einfaches Makro hinzufügen.

Klicken Sie mit der rechten Maustaste auf die Registerkarte Datenblatt und wählen Sie den Befehl aus Quelle (Quellcode). Geben Sie in dem sich öffnenden Fenster den Code des Makrohandlers für das Ereignis der Auswahländerung ein:

Wie Sie sich leicht vorstellen können, löst es lediglich eine Blattneuberechnung aus, wenn sich die Position der aktiven Zelle ändert.

Schritt 6. Hervorheben der aktuellen Zeile

Zur Verdeutlichung können Sie auch eine bedingte Formatierungsregel hinzufügen, um das aktuell im Diagramm angezeigte Land hervorzuheben. Dazu die Tabelle auswählen und auswählen Startseite — Bedingte Formatierung — Regel erstellen — Verwenden Sie die Formel, um zu formatierende Zellen zu bestimmen (Startseite – Bedingte Formatierung – Neue Regel – Verwenden Sie eine Formel, um zu bestimmen, welche Zellen formatiert werden sollen):

Hier prüft die Formel für jede Zelle in der Tabelle, ob ihre Zeilennummer mit der in der TekRow-Variablen gespeicherten Nummer übereinstimmt, und wenn es eine Übereinstimmung gibt, wird die Füllung mit der ausgewählten Farbe ausgelöst.

Das ist es – einfach und schön, oder?

Notizen

• Auf großen Tabellen kann all diese Schönheit langsamer werden – die bedingte Formatierung ist eine ressourcenintensive Sache, und die Neuberechnung für jede Auswahl kann auch schwer sein.
• Um zu verhindern, dass Daten im Diagramm verschwinden, wenn eine Zelle versehentlich über oder unter der Tabelle ausgewählt wird, können Sie dem TekRow-Namen eine zusätzliche Überprüfung hinzufügen, indem Sie verschachtelte IF-Funktionen des Formulars verwenden:

  =WENN(ZELLE("Zeile")<4,WENN(ZELLE("Zeile")>4,ZELLE("Zeile")))
  

• Hervorheben bestimmter Spalten in einem Diagramm
• So erstellen Sie ein interaktives Diagramm in Excel
• Koordinatenauswahl