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

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 Aachen, 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

Hallo Joachim, schön, dass Du eine Lösung gefunden hast. Gruß, Mourad

Hallo Mourad.
Ich erstelle eine fortlaufende Statistik, die auf externe Dateien zugreift, welche wöchentlich unter dem Namen der jeweiligen KW abgelegt werden. In einer Spalte habe ich ein Suchkriterium und wenn dieses erfüllt ist, zieht sich meine Formel die Summe aus allen entsprechenden Zellen in einer anderen Spalte. Das ganze gilt für etwa 20 verschiedene Sichkritierien. SUMMEWENN hat leider nicht funktioniert (externe Dateien=geschlossen) und daher musste ich eine Matrixformel verwenden mit SUMME(WENN()) und den geschweiften Klammern. Jetzt möchte ich meine Formeln für die Zukunft anlegen und den Namen der KW fortlaufen lassen. Suchen & Ersetzen hilft leider nicht, da immer das Fenster ‚Werte aktualisieren‘ aufploppt und somit nur eine von vielen Zellen aktualisiert wird.

Hast du eine Lösung für mich?

LG Paul

Mourad Louha

Hallo Paul,

welche Excel Version hast Du im Einsatz? Falls 2013 oder 2016 würde ich das nicht mehr auf diese Art machen, sondern PowerQuery dazu verwenden. PowerQuery ist in Excel 2013 als Add-In verfügbar (wird von MS mit ausgeliefert) und muss nur aktiviert werden. In Excel 2016 ist PowerQuery vollständig integriert. Reiter Daten / Abrufen & Transformieren / Neue Abfrage.

Mit PowerQuery kannst Du einen ganzen Ordner mit Dateien importieren. Diese Dateien sollten dann aber alle gleich aufgebaut sein. In PowerQuery gibt’s einen Editor, der z.B. zum filtern, sortieren, umwandeln von Werten usw. Die Ausgabe landet in einer Tabelle in einem Arbeitsblatt. Auf diese kannst Du dann prima Formeln anwenden. Neue Dateien kopierst Du wieder in den Ordner und aktualisierst die Abfrage. Fertig.

Viele Grüße,
Mourad

Guten Morgen,

gibt es eine Möglichkeit in PowerQuery den Pfad zur Quelldatei dynamisch aufzubauen? Ich habe es schon über Namen und INDIREKT versucht, aber die Felder interpretieren keine Formeln.

Vielen Dank!

Hallo Jonas,

Ja, das geht z.B. über Parameter. Anbei zwei Links in English. Das bezieht sich z.B. im ersten Artikel auf SQL-Abfragen, das Prinzip ist aber z.B. für Dateien dasselbe: erst eine Datei einlesen und Pfad statisch setzen. Dann den Parameter erstellen und schließlich im M-Code den Pfad ersetzen.

Viele Grüße,
Mourad

Hallo Herr Louha,

ich habe folgende Schwierigkeit. Zur Vervollständigung einer Tabelle habe ich bei Eingabe einer Bearbeitungsnummer in die restlichen Spalten SVerweise zu einer anderen Exceldatei hinterlegt. So füllen sich die Spalten automatisch aus. Soweit so gut. Das Problem ist folgendes: die Quelldatei, auf die sich meine SVerweise beziehen, funktioniert nach dem Großvater-Vater-Sohn-Prinzip, d.h., die Datei wird immer unter dem aktuellen Datum neu abgespeichert. Jetzt sollen sich meine SVerweise natürlich immer auf die neueste Datei beziehen. Gibt es die Möglichkeit das irgendwie hinzubekommen?

Gern können Sie mir über die hinterlegte Emailadresse antworten.

Vielen Dank schonmal im Voraus!

Grüße aus Berlin

Mourad Louha

Hallo Rafa,

Nein, das geht nicht automatisiert mit Formeln, wenn denn z.B. die Datei geschlossen bleiben soll. Sie können aber durch Suchen & Ersetzen einfach den Dateinamen austauschen, das wäre die einfachste Variante.

Sollten Sie Excel 2016 haben, könnten Sie auch versuchen, PowerQuery dazu zu verwenden: Daten / Daten abrufen & Transformieren / Daten abrufen / Aus Datei / Aus Ordner aufrufen. Ordner angeben, wo genau die Quelldatei drin liegt. Dann Kombinieren und bearbeiten wählen. Anschließend die Tabelle mit den Daten. Im Abfrageeditor ggf. die Spalte zu den Quelldaten löschen. Und die Daten per Schließen und Laden in importieren. Legt neue Tabellen an, ggf. unnötige löschen. Dann den SVERWEIS auf diese Importdaten legen. Bei jeder Aktualisierung (manuell unter Daten machen), werden neue oder ausgetauschte Dateien im Order erkannt. Voraussetzung: die sehen genauso aus wie der Import.

Viele Grüße,
Mourad

Vielen Dank,

das ist ein hervorragender Vorschlag, hätte man auch selber draufkommen können ^^

Danke für die superschnelle Antwort!

Gruß aus Berlin

Hallo,

gibt es eine Möglichkeit Zellbezüge mit mehreren Dateien zu machen, ohne dass der Inhalt der vorherigen Datei nach dem Schließen der vorherigen Datei verschwindet?

Gruß aus Hamburg

Hallo Hamza,

kann erst jetzt antworten: so ganz verstehe ich nicht, was Du meinst. Der Artikel hier oben setzt voraus, dass die Dateien geöffnet sind. Ob eine oder mehrere ist dann erstmal egal. Generell gehen auch (statische) Verknüpfungen mit mehreren Dateien. Da sollte eigentlich nichts verschwinden.

Viele Grüße,
Mourad

Leave a Reply