Wie kann ich in Excel Zellen verschieben, ohne dass sich die Bezüge auf diese Zelle ändern?
Ich habe eine Excel Datei, In der eine Übersicht existiert, in der spaltenweise aufeinander folgende Jahre aufgelistet sind. in den jeweiligen Spalten finden sich Werte wie Steuereinnahmen, Einwohnerzahlen etc. Jedes Jahr wird die älteste Spalte gelöscht und dafür eine neue zukünftige hinzugefügt. Ich habe also dauerhaft 9 Spalten, mit denen gearbeitet wird.
Diese Spalten geben ihre Werte an ein Arbeitsblatt mit weiteren Berechnungen des jeweiligen Jahres ab. Auch hier wird jedes Jahr ein altes gegen ein neues getauscht.
Ist es möglich ein Arbeitsblatt mit einer Spalte so zu verknüpfen, dass meinetwegen die Werte für Blatt 1 immer aus Spalte B, für Blatt 2 immer aus Spalte C usw. genommen werden? So könnte ich ja die erste Spalte löschen, sodass z.B. 2016 anstelle von 2015 steht und ich nur die Benennung der 9 Arbeitsblätter jährlich ändern muss, anstatt alle Verlinkungen neu zu machen...
Ich hoffe jemand kann mir helfen :) Danke schonmal im Voraus, LG Flori
4 Antworten
Mittels Indirekt kannst du dir Formel und Bezüge zusammenbasteln.
Aber das wäre für den Fall pure Excelvergewaltigung.
Viel sinniger ist doch ein automatismus.
Der Erste Spalte bildet aktuelles Jahr - 9 Jahre ab und die Bezüge erschließen sich mit Indirekt aus diesem Jahr.
Da musst du doch nichts löschen und jährlich anpassen.
Alternativ kann man natürlich auch ein Datensammelblatt verwenden, dass von allen Jahrestabellen die Daten sammelt und die Gesamtübersicht macht von diesem Datenblatt lediglich den Datenbankauszug
Für konkrete Formeln müsste man den Aufbau deiner Tabelle genauer kennen.
Erstens, erklärst du dass Jahre "spaltenweise" sind und die Kategorien "in den jeweiligen Spalten". Ich ignoriere das erstmal und erkläre, wie das mit
Jahren in Zeilen und Kategorien in Spalten funktioniert. Entsprechend deiner Erklärung:
Annahme:
- Blatt "summary", Spalte B hat Steuern
- Blatt "summary", Spalte C hat Einwohner
- Blatt "summary", Zeile 2 hat 2010
- Blatt "summary", Zeile 3 hat 2011
- Blatt "summary", Zeile 4 hat 2012
Du möchtest jetzt im Blatt "Jahr 2015" die Information von 2015 sammeln. In dem Fall würde ich vorschlagen:
- A1 = "Jahr"
- B1 = 2015
- 2 = "Zeile"
- B1 = "MATCH(B1;"summary!A2:A20)+1"
- B1 ist in dem Fall 7 und du weißt, dass 2015 in Zeile 7 ist.
2.) Dort, wo du Einwohner (Spalte C) des Jahres 2015 (Zeile 7) haben willst, kannst du nun mit INDRECT deine eigene fixe Referenz erzeugen:
- A3 = "Einwohner"
- B3 = "=INDIRECT("summary!C"&B2)"
Ich habe eine englische Version von Excel, "MATCH" und "INDIRECT" sind also vermutlich "SUCHE, FINDE, ..." und "INDIREKT", falls du die deutsche Version verwendest.
Nachtrag:
Desweiteren sehe ich einige andere Lösungen, die man sich überlegen könnte. Du musst auch wissen, wo deine echten Daten liegen (in der Übersicht oder in den einzelnen Datenblättern)
Ich habe jetzt die Frage der Performance ignoriert, da aus der Frage zu schließen war, dass es sich nicht um hunderte von Jahren bzw. Eigenschaften handelt.
Bei Indirekt hat man den Vorteil, dass man absolute Zellen eines Arbeitsblattes ansprechen kann, wohingegen bei bei INDEX realtive Zellen einer Matrix angeben kann. Wenn die Tabelle also nicht oben-links in A1 anfängt, sondern beliebig auf dem Arbeitsblatt verteilt sein kann, ist in dem Fall INDEX sinnvoller.
Dafür verwendest du den INDIREKT-Befehl. Also statt z.B.
=Übersicht!B2
verwendest du
=INDIREKT("Übersicht!B2")
Dann ist es dann egal, ob Spalten oder Zeilen hinzugefügt oder gelöscht werden, der Bezug gilt immer für B2. INDIREKT kann bei großen Tabellen und häufiger Verwendung die Verarbeitung verlangsamen.
Eleganter wäre die Verwendung der INDEX-Funktion in Kombination mit VERGLEICH. Wenn z.B. im Quell-Arbeitsblatt jede Spalte mit der Jahreszahl (z.B. 2016) in der 1. Zeile überschrieben ist, kannst du auf dem Ziel-Arbeitsblatt irgendwo die Jahreszahl eintragen (im Beispiel in A1). Wenn du jetzt z.B. den Wert der 5. Zeile einer Jahresspalte haben möchtest, verwendest du die Formel
=INDEX(Übersicht!B1:H99;5;VERGLEICH(A1;Übersicht!B1:H1;0))
Die Formel bedeutet: Gib aus dem Bereich B1:H99 des Blattes "Übersicht" den Wert der 5. Zeile und jener Spalte, wo in der 1. Zeile die Jahreszahl aus A1 steht.
Hallo Flori,
du musst die Zellbezüge absolut definieren. Wenn du sie relativ festlegst, werden sie bei einer Änderung mit verschoben.
Du gibst also nicht das an:
=A1
Sondern so (Tastenkomination: =A1 markieren und F4 drücken):
=$A$1
Hier sind noch ein paar Infos dazu:
http://www.studium-und-pc.de/relativer-und-absoluter-bezug.htm
Hoffe, ich konnte dir helfen. VIel Erfolg!
DH! Allerdings einige Anmerkungen dazu:
MATCH heißt in der dt. Version VERGLEICH. Man kann sich ganze Formeln recht einfach mit dem http://de.excel-translator.de/translator/ übersetzen lassen.
Auch wenn die Funktionen INDIREKT und INDEX häufig gegenseitig austauschbar sind, würde ich gerade mit VERGLEICH die Funktion INDEX bevorzugen, da sie perfomanter ist und auch der Spaltenindex leichter bearbeitet werden kann (bei INDIREKT müsste man da auf ADRESSE zurückgreifen).