Vor einiger Zeit stellt jemand im Office Lösung Forum die Frage, wie Bezüge in einer Excel Datei zu einer anderen Datei dynamisch aufgebaut werden können. Folgende Lösung basiert darauf, die Verknüpfung auf den externen Bereich anhand der Formeln BEREICH.VERSCHIEBEN(), INDIREKT() und ADRESSE() aufzubauen.
Nehmen wir mal an, es liegen uns verschiedene Dateien vor, die vom Aufbau her, alle gleich strukturiert sind. Folgende Abbildung zeigt 4 Dateien, die jeweils fiktive Tageswerte pro Woche enthalten. Die Werte sind in den Zeilen C4 bis C10 abgelegt.
Nehmen wir nun an, eine weitere Datei soll eine Verknüpfung zu einer dieser Dateien enthalten und aus den dort enthaltenen Werte holen eine Summe bilden. Üblicherweise wird dann folgende Formel verwendet:
=SUMME([KW.02.xlsx]Wochenwerte!$C$3:$C$9)
Nachteil wäre hierbei, dass wenn die Werte aus einer der anderen Dateien verwendet werden sollen, die Formel manuell angepasst werden müsste.
Um die Formel dynamisch aufzubauen, habe ich zunächst in der Tabelle ein paar Zellen mit Konfigurationswerten belegt. Dies sind im Einzelnen die Kalenderwoche in D3, der Name der Quelltabelle in D5, die Start- und Endzeile des Bereichs in den Quelldaten in D6 und D7 sowie die Quellspalte in Zelle D8. Den Dateinamen ermittle ich anhand der Formel:
="KW"&"."&TEXT($D$3;"00")&"."&"xlsx"
Die Excel Formel BEREICH.VERSCHIEBEN() liefert als Rückgabewerte den Bereich, der, bezogen auf einen Referenzbereich, um „x“ Zeilen und „y“ Spalten mit einer Höhe von „h“ Zeilen und „w“ Spalten verschoben ist. Wenn also als Referenzzelle die Zelle C4 aus den Quelldaten verwendet wird, kann der Bereich C4:C10 wie folgt ermittelt werden:
=BEREICH.VERSCHIEBEN($C$4; 0; 0; 7; 1) mit x = 0, y = 0, h = 7 und w = 1
Um nun den vollständigen Bezug zur Zelle C4 in den Quelldaten zu ermitteln, kann die Formel ADRESSE() verwendet werden, welche als Argumente unter anderem die Zeilen- und Spaltennummer sowie den Namen der Tabelle erwartet. Die Zeilen- und Spaltennummer haben wir bereits in den Zellen D6 = 3 und D8 = 3 abgelegt. Da sich die Tabelle in der externen Datei befindet, müssen wir den Dateinamen dem Namen in der Tabelle voranstellen. Und natürlich muss auf eine korrekte Excel Syntax geachtet werden. Wir erhalten somit:
= ADRESSE($D$6;$D$8;;;"["&$D$4&"]"&$D$5)
Die Höhe des verschobenen Bereiches kann über die Formel „Endzeile – Startzeile + 1“ berechnet werden. Schließlich müssen wir noch aus dem Rückgabewert der Formel ADRESSE() über die Formel INDIREKT() einen Bezug erstellen und das Ganze anschließend in die Formel SUMME() verschachteln. Als Endergebnis erhalten wir:
=SUMME(BEREICH.VERSCHIEBEN(INDIREKT( ADRESSE($D$6;$D$8;;;"["&$D$4&"]"&$D$5));0;0;$D$7-$D$6+1;1))
Somit kann über den Parameter zur Woche relativ komfortabel gesteuert werden, welche Woche zur Berechnung der Summe verwendet werden soll. Allerdings ist hier zu beachten, dass die entsprechende Datei auch geöffnet sein muss. Ist dies nicht der Fall, liefert die Formel den Fehlerwert #BEZUG zurück. Beispielsweise könnte die Formel jedoch über eine WENN(ISTFEHLER(Formelergebnis); „Bitte Datei öffnen“; Formelergebnis) so erweitert werden, dass im Fehlerfall eine Meldung angezeigt würde. Abschließend die Beispieldateien zum Download, die hier jedoch Excel 2007 oder höher voraussetzen, da diese im XLSX abgespeichert sind.
Dieser Artikel wurde auch in der englischen Verson vom Excel Ticker publiziert.
Danke hat geklapt.
Musste nur ein Skript entwickeln zum starten damit alle Excel Tabellen gestartet werden.
Gruß