Excel ändert beim Einfügen neuer Zeilen die Bezüge

[-]Dr.OeTz![-]

Hallo Zusammen,

ich stecke gerade bei einem in meinen Augen vollkommen trivialen Problem fest und komme nicht weiter:

Ich habe zwei Tabellenblätter A und B
In Tabelle A habe ich viele Datensätze untereinander
In Tabelle B will ich diese Auswerten

Problem: Sobald sich in Tabelle A eine oder mehrere Zeilen eingefügt werden, ändern sich die Zellbezüge in Tabelle B zu Tabelle A entsprechend. Ob ich die Zellbezüge relative oder mit $ absolut setze ist dabei gänzlich irrelevant.

Wie bekomme ich Excel dazu die Bezüge in Tabelle B auf bestimmte Zellen in Tabelle A fest zu definieren.


Bei meiner Suche bin ich mehrfach über die Funktion "INDIREKT" gestolpert. Funktioniert auch aber ich so müsste ich bei allen Zellen in Tabelle B die einen Bezug zu Tabelle A haben das zusätzliche "INDIREKT" per Hand setzen, da "ausfüllen" nicht funktioniert. Also leider keine Lösung, es sei denn, jemand kann mir sagen, wie ich die Funktion INDIREKT auf alle Zellbezüge in Tabelle B gleichzeitig anwenden kann...

Ich hoffe ich stehe hier nur mächtig auf dem Schlauch - es kann doch nicht so schwer sein,Excel dazu zu bringen die Zellbezüge wirklich absolut zu setzen.

 

[Snooty]

INDIREKT ist doch eine normale Funktion - da muss auch das Ausfüllen klappen.

Andererseits sollte Excel auch die Bezüge anpassen, wenn du Zeilen/Spalten einfügst. Das dürfte aber nicht mehr gehen, wenn die Zeilen per Makro eingefügt werden.

 

[halwe]

Wenn die Bezüge nicht, wie sonst üblich, an die Quelle angepasst werden sollen, könnte es reichen, zwei getrennte Excel-Dateien zu verwenden. Die sollten sich nicht gegenseitig verändern.

 

[MickH74]

Mit dieser Funktion sollte es gehen:
=INDEX(Tabelle1!A:A;ZEILE(A1))
Damit wird immer in Tabelle1 die Zelle A1 ausgegeben, auch wenn man dort Zeilen einfügt.

 

[Sir Humpton]

Dein „Auswerten“ in Tabelle B kann nahezu alles sein. Wie bereitest Du diese denn auf und wie sind beide Tabellen verknüpft? Wenn Du eine flexible Zuordnung brauchst, hilft eventuell Index-Vergleich weiter oder eine Indirekt-Kombination. Hängt alles vom Problem ab.

 

[-]Dr.OeTz![-]

Vielen Dank für euren Input. Habe viel probiert und jetzt eine Lösung gefunden, die auf den ersten Blick etwas umständlich anmutet aber scheinbar bestens funktioniert.

Ich habe mal meinen Weg zur funktionierenden Lösung dokumentiert - vielleicht nützt es jemandem.

Folgende Aufgabenstellung:
Und zwar muss ich die Daten aus zwei Selektionen (welche ich mit der Excel-Datenabfrage importiere) zusammenführen und auswerten.



Ansatz 1 - relative Bezüge
Ich habe 3 Tabellen
Tabelle A = Import A
Tabelle B = Import B
Tabelle C = Zusammenführen aus Import A und B sowie Auswertung

Die Bezüge in Tabelle C setze ich relativ ='Tabelle A'!A1 und hole mir so die einzelnen Datensätze aus Tabelle A und B.

Das Problem daran:
Verwendet man relative (oder auch absolute Bezüge) ist Excel leider etwas zu schlau. Denn wenn in einer der Tabelle A oder B neue Datensätze/Zeilen hinzukommen, passt Excel in Tabelle C automatisch die Bezüge an. In wohl 95% der Fälle ist das ÜBERAUS praktisch, aber in genau diesem speziellen Fall absoluter Käse



Ansatz 2 - Funktion INDIREKT
Der Aufbau ist analog zu Ansatz 1, nur verwende ich die Funktion INDIREKT

Problem:
Bei INDIREKT funktioniert das Ausfüllen nicht. Ich müsste also alle Zellen manuell definieren. Bei mehren Tausend Datensätzen mit ~40 Spalten ist das unmöglich zu realisieren



Ansatz 3 - strukturierte Verweise
Auch hier wäre der Aufbau analog zu Ansatz 1. Die Syntax lautet: ='Tabelle A'[@Spalte1]. Vorteil gegenüber Ansatz 2 ist, dass das Ausfüllen funktioniert ABER

Problem:
Im Gegensatz zu Ansatz 2, kann ich die Datensätze aus Tabelle A und B nicht übereinander in Tabelle C auflisten, denn diese Syntax bezieht sich immer auf die aktuelle Zeile. Ich kann also in Zeile 10 von Tabelle C nicht sagen, er soll sich auf Zeile 1 in Tabelle B beziehen.



Lösung - eine Kombination aus strukturierten Verweisen und relativen Bezügen
Ich baue anstatt bisher drei nun fünf Tabellen:
Tabelle A1 = Import A
Tabelle A2 = Aufbereitung Import A1
Tabelle B1 = Import B
Tabelle B2 = Aufbereitung Import B1
Tabelle C = Zusammenführen aus Aufbereitung A2 und B2 sowie Auswertung

In Tabelle A2/B2 bereite ich die Daten aus den jeweiligen Imports mit strukturierten Verweisen zu den Tabellen A1 und B1 auf, formatiere sie und fange Fehler (#WERT) mit der Funktion "WENNFEHLER" ab. Syntax: =WENNFEHLER('Tabelle A1'[@Spalte1];"-"). Somit habe ich das Problem mit wechselnder Anzahl an Datensätzen nach einem Import gelöst

In Tabelle C setze ich nun relative Bezüge zu den Tabellen A2 und B2.
Zeile1 - 5.000 = relative Bezüge auf Tabelle A2 Zeile 1 - 5.000
Zeile 5.001 - 10.000 = relative Bezüge auf Tabelle B2 Zeile 1 - 5.000
Dabei funktioniert das Ausfüllen wie gewohnt

Daneben/Darunter kann ich nun einfach meine Berechnungen durchführen bzw. die Auswertung vornehmen