(C) 2009 - 2014 by Mourad Louha · Alle Rechte vorbehalten
Impressum

Excel Bezüge zu externen Dateien dynamisch aufbauen

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.

Excel 2010

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.

Excel 2010

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.

Mourad Louha

Über den Autor · Mourad Louha

Mourad arbeitet seit 2005 als selbständiger Softwareentwickler und Trainer und lebt in Herzogenrath, gelegen am Dreiländereck Belgien, Deutschland und Niederlande. In seiner Freizeit engagiert er sich in Online Communities rund um Office, schreibt beim Excel Ticker Artikel zu Office & Co. und betreibt weitere Community Projekte in Zusammenarbeit mit Freunden und Partnern, wie das Forum zu Microsoft Office 365 und Office 2013, den Excel Soccer oder den Excel Formel Übersetzer. Für sein außergewöhnliches Engagement wurde er seit Januar 2011 jährlich von Microsoft als Microsoft Most Valuable Professional (MVP) ausgezeichnet.

 
Comments

Danke hat geklapt.

Musste nur ein Skript entwickeln zum starten damit alle Excel Tabellen gestartet werden.

Gruß

Hallo vipimp…
schön, dass es Dir geholfen hat :-)
Gruß

Hallo!
Die INDIREKT-Funktion benutzte ich seit Jahren fehlerfrei, aber jetzt liefert der externe Bezug (selbstverständlich korrekt aufgebaut) Bezugsfehler. Habe ich evtl. Einschränkungen in den Excelversionen nicht mitbekommen?
Grüße

Hallo Frank…

Sind die Mappen, auf die sich die Formeln beziehen auch geöffnet?

Viele Grüße :-)

Trackbacks for this post