Dynamischer Diagrammbereich mit INDIRECT: Diese Funktion ist ungültig (trotz hervorgehobenem Bereich)
Ich versuche, mit der Funktion INDIRECT ein Diagramm mit einem dynamisch erstellten Bereich zu erstellen. Excel erkennt den Bereich, den ich mit INDIRECT erstelle, indem es den entsprechenden Bereich auf dem Blatt hervorhebt:
Beim Speichern des Diagramms erhalte ich jedoch die Fehlermeldung, dass die Funktion ungültig ist:
Weiß jemand, wo das Problem liegt/wie man einen dynamischen Diagrammbereich von einem bestimmten Anfang bis zu einem bestimmten Endpunkt erstellt?
PS: Sie können die obige Tabelle hier herunterladen . Die Formel, die ich verwendete:=INDIRECT("sheet!"&E2&":"&E3)
Meine ist ähnlich wie Seans hervorragende Antwort, erlaubt aber einen Start- und Endtag. Erstellen Sie zunächst zwei benannte Bereiche, die Index-/Match-Formeln verwenden, um Anfangs- und Endtage basierend auf E2 und E3 auszuwählen:
rngDay
=INDEX(Sheet1!$A:$A,MATCH(Sheet1!$E$2,Sheet1!$A:$A,0)):INDEX(Sheet1!$A:$A,MATCH(Sheet1!$E$3,Sheet1!$A:$A,0))
rngValue
=INDEX(Sheet1!$B:$B,MATCH(Sheet1!$E$2,Sheet1!$A:$A,0)):INDEX(Sheet1!$B:$B,MATCH(Sheet1!$E$3,Sheet1!$A:$A,0))
Sie können dann auf die Serie im Diagramm klicken und die Formel wie folgt ändern:
=SERIES(Sheet1!$B$1,Sheet1!rngDay,Sheet1!rngValue,1)
Hier ist ein Nizza Chandoo-Beitrag zur Verwendung dynamischer Bereiche in Diagrammen.
Die Art und Weise, wie Sie es versuchen, ist nicht möglich. Der Kartendatenbereich muss eine feste Adresse haben.
Es gibt einen Weg, dies zu umgehen, und benannte Bereiche werden verwendet
Geben Sie die gewünschte Anzahl von Zeilen in Ihren Daten in eine Zelle ein (z. B. E1).
Also füge ich in Ihrem Beispiel Number of Rows in D1 und 6 in E1 ein.
Definieren Sie im Namensmanager die Namen für Ihre Daten und Titel
Ich verwendete xrange und yrange und definierte sie als:
xrange: = OFFSET (Sheet1! $ A $ 2,0,0, Sheet1! $ E $ 1)
yrange: = OFFSET (Sheet1! $ B $ 2,0,0, Sheet1! $ E $ 1)
jetzt zu Ihrem Diagramm - Sie müssen den Namen der Arbeitsmappe kennen (wenn Sie diese eingerichtet haben, sorgt Excel für die Nachverfolgung von Änderungen dafür, dass der Verweis unabhängig von der Umbenennung korrekt bleibt).
Lassen Sie den Chart data range leer
Geben Sie für Legend Entries (Series) wie üblich den Titel und dann den Namen ein, den Sie für die Daten definiert haben (beachten Sie, dass der Name der Arbeitsmappe erforderlich für die Verwendung benannter Bereiche ist).
geben Sie für Horizontal (Category) Axis Labels den Namen ein, den Sie für die Etiketten definiert haben
wenn Sie nun die Zahl in E1 ändern, sehen Sie die Diagrammänderung:
Nur eine weitere Antwort für Bits und Googles ..
Wenn Sie weiterhin auf Ihre Start- und Endzellen verweisen möchten, müssen Sie eine separate Formel für Ihren Tagesbereich und Ihren Wertebereich hinzufügen. Formeln sind unten und der Screenshot zeigt die verwendeten Formeln.
Tagesbereich:
="Sheet1!"&$F$2&":"&ADDRESS(ROW(INDIRECT($F$3)),COLUMN(INDIRECT($F$2)))
Wertebereich:
="Sheet1!"&ADDRESS(ROW(INDIRECT($F$2)),COLUMN(INDIRECT($F$3)))&":"&$F$3
Fügen Sie dann zwei Bereiche hinzu, die auf die INDIRECT Werte dieser Zellen verweisen
Drücken Sie Strg + F3, klicken Sie auf Neu, und fügen Sie einen neuen Bereich mit dem Namen "chart_days" hinzu, der sich auf =INDIRECT(Sheet1!$F$4) bezieht. und ein neuer Bereich mit dem Namen "chart_values", der sich auf =INDIRECT(Sheet1!$F$5) bezieht
Fügen Sie schließlich in Ihrem Diagramm eine Reihe hinzu, die auf =nameOfYourWorkbook!chart_values verweist.
und Bearbeiten Sie die Kategorie, um auf =nameOfYourWorkbook!chart_days zu verweisen.
Wenn der Bereich eines Liniendiagramms eine benannte Variable ist und die Variable INDIRECT () -Verweise durch eine Zelle auf einen Bereich enthält, muss die Variable mindestens 2 INDIRECT () -Verweise haben, die durch ein Komma getrennt sind.
Entsprechend der Formel, die Sie angezeigt haben: = INDIRECT ("Tabelle!" & E2 & ":" & E3).
Ich hätte gedacht, es wäre Sheet1! oder Sheet2! usw .. Ihre Formel löst sich auf = Blatt! E2: E3, was keine gültige Adresse ist. Die Fehlermeldung, die Sie erhalten, bedeutet, dass Excel die Eingabe in INDIRECT nicht auflösen kann. INDIRECT ist eine gültige Funktion, daher muss das Argument, das Sie anbieten, ungültig sein.
Alle obigen Antworten, die den Namen des Blatts angeben, haben Ihren Fehler korrigiert, aber nicht erwähnt ...;)
Ich verwende OFFSET, um eine definierte Namensformel zu erstellen, sodass ich alle Bereiche für die Daten definieren kann, sodass ich ein Anfangsdatum (oder die Anfangs- und Endposition eines Datensatzes) festlegen kann.
Für ein einfaches Diagramm definiere ich den Namen CategoryLabels wie folgt:
= OFFSET($A$5; (InicitialMonth-1); 0; LastMonth - (InitialMonth-1))
und DataCars wie folgt:
= OFFSET($B$5; (InicitialMonth-1); 0; LastMonth - (InitialMonth-1))
Sie müssen so viele Namen wie Serien definieren, die Sie hinzufügen möchten, und zwar nach demselben Verfahren. In diesem einfachen Fall habe ich nur Autoverkäufe eingeschlossen.
Anfangsmonat und letzter Monat sind Bereichsnamen, die für einzelne Zellen definiert werden, um anzugeben, welche Monate des Diagramms eingeschlossen werden (von Anfang bis Ende).
Denken Sie daran, wie von Sean Cheshire und anderen erläutert, dass zur Verwendung der Namen für die Diagrammwerte der Name der Tabelle enthalten sein muss.
Benannte Formeln mit indirekten Funktionen funktionieren nicht in Zeichensätzen. Es funktioniert in anderen Quellen, da Ihre gewünschte dynamische Quelle hervorgehoben wird. Wenn Sie sie jedoch im Diagramm anzeigen, wird sie nicht ausgewertet. Hoffe, Microsoft hat dieses Problem behoben.