(C) 2009 - 2014 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

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 :-)

Hallo Mourad,

gibt es auch eine Lösung dafür, ohne das die Dateien erst geöffnet werden müssen. Marko, VBA ?

LG

Julien

Hallo Julien,

Thomas Ramel (auch Excel MVP) hatte mal eine VBA-Funktion geschrieben, zum Einlesen von Werten aus geschlossenen Dateien. Die Lösung oder andere habe ich hier für diese Aufgabenstellung nicht ausprobiert und weiss auch nicht ob’s passt; also z.B. unter Excel 2010 läuft. Anbei zwei Links mit ähnlichen Themen. Google Gruppe, Beitrag von Thomas und Beitrag bei Online Excel

Viele Grüße, Mourad

Lieber Herr Louha,

Sie sind meine letzte Hoffnung. Unsere IT-Trainerin befindet sich im Urlaub und ich bin so richtig verzweifelt. Ich benötige einen dynamischen Verweis von einem Tabellenblatt auf das nächste (wenn dort „XY“ steht, soll in der nächsten Spalte der selbe Wert stehen, wie der, den man beim selben Namen auf dem anderen Tabellenblatt erkennt). Dazu habe ich folgende Formel eingegeben: =VERWEIS(‚Auflistung Rechnungen’!H6;Vergleichsquote!A:A;Vergleichsquote!B$1:B$23). Aber aus einem mir nicht bekannten Grund verrutscht das. Ich kann den Fehler einfach nicht finden. Können Sie mir bitte helfen? Gerne auch per E-Mail.

Das wäre echt super.

Hallo Kerstin,

denke, so ganz sicher bin ich mir nicht, ob ich das Problem richtig verstanden habe. Helfe aber gerne und habe Ihnen deshalb eine Mail an die Adresse gesendet, die Sie hier im Blog beim Kommentar angegeben haben.

Gruß

Jürgen Soppa

Sehr geehrter Herr Louha,
ich habe folgendes Problem: aus einer Tabelle sollen Werte in eine andere Tabelle geschrieben werden. Das ist eigentlich trivial.
Es handelt sich bei Tabelle1 um einen fortlaufend hochgezählten Fragebogen, ich benötige nur 6 Werte aus jedem Fragebogen, und zwar Personendaten und die Ergebnisse der Befragungen (je 2 pro Datei). Die Fragebogendateien werden aus einer xltx-Vorlage erzeugt und erhalten dann (manuell) die Dateinamen Patientxx.xlsx.
In der ersten Spalte erzeuge ich jeweils den Dateinamen, der beim Abspeichern der Mappe gespeichert wird.
In die Auswertungstabelle sollen über diesen Dateinamen (erste Spalte der Datei AWT.xlsx) die 6 Werte aus der Patientxx. in die gleiche Zeile geschrieben werden. Das funktioniert mit dem folgenden SVERWEIS: SVERWEIS($B5;’E:\Training\TPatienten\[Patient01.xlsx]Befragung’!$A$4:$H$4;2;FALSCH) auch super. Aber um die ATW vernünftig für die Daten vorzubereiten, möchte ich die ersten 200 Einträge schon vornehmen. Wenn ich aber den Verweis zeilenmässig nach unten ziehe, wird alles richtig, außer dem Dateinamen: Patient01 ist statisch. Wie funktioniert der Verweis richtig mit einem dynamischen Dateinamen (der ja in Spalte/Zelle B schon vorhanden ist?
Leider hat die einfache Variante (direkter Bezug in jeder Zelle zur gleichen in der Patientendatei) zum gleichen Ergebnis geführt. Auch eine Hilfstabelle, in der der gesamte Ausdruck als Text kopiert wird, funktioniert nicht, die Aktualisierung funktioniert dann erst durch Doppelklick auf die Zelle (das sah so aus): ‚E:\MKT\Fragebogen\[PTQ01.xlsm]Patient_1’!$C$24 (anderer Versuch).
Habe auch versucht, das Problem mit INDIREKT zu lösen, aber das funktioniert auch nicht. Wissen Sie Rat? Schöne Grüße, Jürgen Soppa

Hallo Herr Soppa,

wenn ich jetzt die Problematik richtig verstanden habe; mit der einfachen Variante wird’s nicht gehen, da können Sie keine variablen Pafd- und/oder Dateinamen einsetzen. Die Funktion BEREICH.VERSCHIEBEN() von hier oben im Artikel kann auch dazu dienen, nur eine einzelne Zelle ansprechen, Dann wären in der Funktion Höhe und Breite gleich 1 und der Zellversatz gleich Null. Dann müsste das wie hier ober erläutert auch gehen. Wobei die Unter-Dateien dann offen sein sollten, damit die Werte gelesen werden.

Der Artikel hier oben ist von 2010, also schon was älter. Falls Sie Excel 2010 oder gar 2013 verwenden, liesse sich vielleicht eher PowerQuery anwenden, das ist nach ein wenig Einarbeiten einfacher und vor allem dynamischer. Google Sie mal nach PowerQuery (für Excel 2010 separat als Add-In zu downloaden und in Excel 2013 schon enthalten, Sie müssen es nur aktivieren). Über PowerQuery können Sie mit einer Abfrage z.B. einen ganzen Unterordner einlesen. Wenn neue Dateien hinzukommen, nur Abfrage aktualisieren. Wie sowas geht, schauen Sie bitte beispielhaft hier bei Chris Webb’s BI Blog rein. Tipp: in Englisch lässt sich viel mehr finden.

Viele Grüße, Mourad

Jürgen Soppa

Vielen Dank, Herr Louha, das werde ich mal versuchen, Ich bin ja schon etwas beruhigt, weil ich es nicht hinbekomme, aber wenn es mit den Bordmitteln nicht geht, kann ich das eher annehmen. PowerQuery ist mir unbekannt (Office 2010), aber ich werde Ihrer Empfehlung folgen, das mal anzuschauen. Danke! Gruß, Jürgen

Hallo, gern geschehen :-)

Trackbacks for this post