Excel-Diagramm dynamische range-Auswahl
Ich habe einen client, der eine einfache und doch komplizierte Anfrage für ein excel-Blatt setup, und ich kann nicht für die Welt, was, wo zu beginnen. Ich zeichne eine leere.
Haben wir einen Daten-Bereich. Beispiel:
Quarter Data
2010Q1 1
2010Q2 3
2010Q3 4
2010Q4 1
Ich habe eine Grafik gebaut, oben auf, dass. Ändern Sie Daten, Diagramm änderungen, schützen Arbeitsblatt halten die anderen idi... äh... von Benutzern geändert werden, die alten Daten. Einfach.
Was ich geschehen lassen will: Wenn ich das nächste Q1 unten Q4, die Karte "automatisch" wählt die jüngsten 4Q. Also, wenn ich ein update der Daten:
Quarter Data
2010Q1 1
2010Q2 3
2010Q3 4
2010Q4 1
2011Q1 7
Zeigt das Diagramm die Daten für die letzten 4 Quartale (2010Q2 thru 2011Q1). Das Ziel: halten Sie "alt" - Daten auf dem gleichen Blatt, aber die charts update zum letzten Viertel.
Ich mir denke: "fixed" data-Standorte, umkehren Sie die Daten (neue Daten oben), und legen Sie einfach Zeile jedes neuen Quartals:
Quarter Data
2011Q2 9
2011Q1 7
2010Q4 1
2010Q3 4
2010Q2 3
2010Q1 1
Aber diese beinhalten eine Menge von änderungen an der bereits vorhandenen excel-sheets und ich hatte gehofft, dass es vielleicht einen einfacheren/besseren "fix".
BEARBEITEN:
@Lance Roberts ~ Läuft mit Ihrem Vorschlag:
- Etwas mehr detail... Die Daten-setup, so dass die Spalte Informationen in Einem, aber die Daten für mehrere Tabellen in B+. Tabelle 1 ist B/C. Table2 D/E. Etc.
- Daten auch auf einem anderen Blatt als die Tabellen.
Gehen: Dieser Offset Beschreibung, was ich versucht habe zu tun ist Anpassung ähnlich wie:
NAME FORMULA OFFSET(range, rows, columns, height, width )
DATA0 =OFFSET('DATASHEET'!$A$2, COUNTA('DATASHEET'!$A:$A - 8, 0, 8, 1)
DATA1 =OFFSET('DATASHEET'!$A$2, COUNTA('DATASHEET'!$A:$A - 8, 1, 8, 1)
DATA2 =OFFSET('DATASHEET'!$A$2, COUNTA('DATASHEET'!$A:$A - 8, 2, 8, 1)
Ziel zu binden, die Länge/Position für B/C/usw Daten zu A. Also, wenn ich eine Spalte hinzuzufügen, die auf Einem, Zeug gebunden Data1/2 entsprechend anzupassen (oder 3/4/5/etc., die verschiedenen Diagramme in verschiedenen sheets
)
Möchte ich Daten Zellen aufgehoben werden, indem die erste Zeile, und dann eine offset-Zahl zum abrufen von Daten x Spalten über. Variationen über die Formel scheint nicht zu funktionieren.
1 Problem habe ich nicht gelöst, aber: die Daten sind nicht korrekt ausgerichtet:
"Daten" ist immer, letzten Spalte unter 2. zum letzten Quartal. Im letzten Quartal ist immer leer. Daten verlagert sich nach rechts (in diesem Beispiel unter 3Q10 - NICHT unter der richtigen Spalte. 11 sollte unter 4Q10. 9.5 sollte unter 2Q10).
Ich weiß, ich bin immer etwas einfach falsch...
Scheint zu funktionieren. Erste Sache, die ich ändern musste war CountA - 9 (nicht CountA - 8). Weiter wurde die Spalte "offset" (0, 1, 2, 3,...). Auch split ein paar Sachen zu machen, mehr voneinander (ich habe jemand anderen trainieren, wie Sie dies für Ihre reporting-Anforderungen).
Dank Lance 🙂
- 0 ist der offset, der behält die gleiche Zelle verweisen, so auf deine 3 Formeln, die Sie benötigen zum ändern der Spalten-parameter 1,2,3 jeweils (auch Sie ließ ein Klammern aus der COUNTA-Anweisung.
- Yeah... werd ich update meine Antwort, weil ich umgestrickt, ein paar Sachen wieder geholt und es funktioniert.
Du musst angemeldet sein, um einen Kommentar abzugeben.
Wenn das Diagramm auf demselben Arbeitsblatt wie die Daten:
Namen der ersten Zelle der Daten (A2) als einen benannten Bereich, sagen TESTRANGE.
Erstellt einen benannten Bereich MYDATA als die folgende Formel:
Nun, gehen Sie auf die Registerkarte REIHE der Diagramm-Quelldaten", und ändern Sie Ihre WERTE Aussage zu:
Nun jedes mal, wenn Sie fügen Sie eine neue Zeile, es wird ändern Sie das Diagramm.
Ich weiß, das ist eine alte Frage, aber ich wollte eine alternative, die möglicherweise leichter.
Ändern Sie Ihre Quartal-Datenbereich in eine Excel-Tabelle. Wählen Sie den Bereich, und drücken Sie Strg+T. In der Tabelle Einfügen, stellen Sie sicher, dass der richtige Datenbereich ausgewählt ist, und dass Meine Tabelle Hat Überschriften aktiviert ist, und drücken Sie OK. Dieser wandelt die einfache Palette in einer speziellen Datenstruktur mit magischen Eigenschaften.
Dann eine neue Reihe, die links zu den letzten vier Zeilen dieser Tabelle, und erstellen Sie ein Diagramm auf der Grundlage dieser neuen Reihe. Dies ist unten dargestellt. Die Tabelle ist die speziell formatierten Bereich in A1:B9 (Sie können wählen, ein weniger in-your-face-Stil), und der Plot-Auswahl ist D1:E5.
Die Formeln in die Zellen D2 durch D5 sind unten. Kopiere D2:D5 und einfügen in E2:E5 zum abschließen der Formeln in unserer Plotten-Bereich.
D2: =INDEX(Tabelle1[Quartal],ZEILEN(Tabelle1[Quartal])-3)
D3: =INDEX(Tabelle1[Quartal],ZEILEN(Tabelle1[Quartal])-2)
D4: =INDEX(Tabelle1[Quartal],ZEILEN(Tabelle1[Quartal])-1)
D5: =INDEX(Tabelle1[Quartal],ZEILEN(Tabelle1[Quartal]))
Table1 ist der name der Tabelle, und Quartal ist der name der ersten Spalte der Tabelle (und auch die Spalten-header). Sie brauchen nicht zu geben, nur wählen Sie die Spalte in der Tabelle. Wie die Tabelle erweitert oder Verträge, Table1[Quartal] verfolgt die Veränderungen.
Fügen Sie nun eine neue Daten zeigen. Die Tabelle erweitert, und unsere kleine staging-Bereich, in D1:E5-links zu den neuen letzten vier Zeilen der Tabelle.
Und, wie wir hinzufügen, Jahre im Wert von Daten, die Formeln und das Diagramm stellen.