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

Guten Tag,
ich versuche aus einem Pfad ‚N:\Abteilung\Statistik\[Weekly_KW01.xls]Overview‘!$C$7 der Wert zu übertragen. Dies funktioniert, solange ich den kompletten String (er ist eigentlich länger) händisch eintrage. Ich habe den Dateinamen dreimal kopiert, die KW angepasst. Geht. Dann habe ich die 4 Zellen markiert und mittels des Punktes rechts unten nach unten gezogen. Statt hochzuzählen ( 1,2,3,4,5,6,7,8 usw ) wiederholt Excel2010 nur ( 1,2,3,4,1,2,3,4,1,2,3,4 usw.). Nun habe ich in einer zusätzlichen Spalte halt „mal eben“ alles 52 erzeugt und versucht, den Pfad zusammenzubasteln.
A1 enthält ‚N:\Abteilung\Statistik\[Weekly_KW
A2 enthält .xls]Overview‘!$C$7
B1 bis B52 halten die generierten KW’s
Sowohl ein VERKETTEN Befehl als auch ein einfaches “ =(A1&B1&A2) “ erzeugt nur einen Text. Excel holt sich nicht aus den anderen Dateien die Werte.
Nun die Frage: Wie sage ich Excel, dass es mit dem erzeugt String arbeite soll, statt ihn nur anzuzeigen? Indirekt geht ja nur bei offenen Dateien.

vielen Dank im Voraus und freundlich grüßend
ULI

Guten Morgen Ulrich

das kann leider nicht funktionieren, denn eine Formel ist was anderes als ein String bzw. Zeichenkette. Ohne es selbst mit externen Links ausprobiert zu haben, kannst Du folgendes versuchen: =“#“&A1&A2 usw. Dann kopieren und als Werte in Nachbarspalte einfügen. Du hättest als Wert in der Zelle z.B. #N:\Abteilung\Statistik\[Weekly_KW01.xls]Overview‘!$C$7. Dann mit Suchen und Ersetzen die # durch ein = ersetzen.

Viele Grüße, Mourad

Hallo Herr Louha,

bei den o.s. Lösungen geht es immer um Daten aus externen Quellen. Ich soll jedoch eine Tabelle in eine (Vorlagen-)Exceldatei einkopieren und diese dann in der selben Datei auswerten (der Nachverfolgbarkeit wegen). D.h. ich habe ein Tabellenblatt, welches von einer externen Anwendung erzeugt wird, dieses soll in eine Exceldatei kopiert und dann dort ausgewertet werden. dazu muss ich in der Auswertetabelle immer auf die gleichen Zellen in der Datentabelle zugreifen. Leider „merkt“ Excel, dass ich das Tabellenblatt mit den EIngangsdaten austausche und die Verlinkungen gehen verloren. Gibt es dazu einen Trick?

Ich würde mich sehr über eine Anregung freuen.
Herzliche Grüße aus Nürnberg, Tim

Hallo Tim,

ausgehend davon, dass vielleicht die Datentabelle immer gleich aufgebaut ist, könntest Du statt des Tabellenblatts aus der externen Tabelle nur den Bereich kopieren und die Werte im Datenblatt Deiner Auswertedatei übernehmen. Wäre am einfachsten. Ansonsten bliebe die Verwendung der Funktion INDIREKT, wo Du (wie oben) den Tabellennamen dynamisierst, ohne dabei den Dateinamen anzugeben. Z.B. liefert in X1 aus Tabelle1 …

X1=INDIREKT("Tabelle2"&"!"&"A1")

… den Wert in A1 aus Tabelle2. Wenn Du dann z.B. Tabelle2 löschst erhälst Du einen Formelfehler. Benennst Du dann aber z.B. Tabelle3 in Tabelle2 um, verschwindet auch der Fehler und die Werte werden aus der ehemals Tabelle3 gezogen. Hoffe, das hilft Dir weiter.

Herzliche Grüße, Mourad

Mion Mourad,

ich habe es versucht, dass ist die Lösung.

Vielen Dank, da hab ich schon so lange daran „rumgedoktert“.

Gruß
Tim

Moin Tim,

freut mich, dass es geklappt hat :-)

Viele Grüße, Mourad

Hallo Mourad,

ich habe nun noch mal gesehen ob ich etwas handliches finde und bin auf die Funktion „Bereich.verschieben“ gekommen, damit komme ich noch besser zu meinem Ergebnis, da ich die Anzahl der zu betrachtenden Spalten mit Faktoren versehen kann. Vielleicht habe ich auch nur den Tipp mit der Indirect Funktion nicht richtig umgesetzt.

Danke noch mal für die Anregung und Gruß
Egbert

Hallo Egbert,

im Gegenteil, freut mich, dass Du eine Lösung für Dich gefunden hast; schließlich ist jedes Problem speziell ;-)

Viele Grüße, Mourad

Hallo Mourad,
sorry für die späte Rückmeldung, aber es hat so funktioniert, wie du es vorgeschlagen hast.
Vielen Dank!!!!

Hallo Ulrich, kein Problem, freut mich, dass ich weiterhelfen konnte. Viele Grüße, Mourad

Moin Ulrich,
ich habe vergeblich versucht mithilfe deiner Formel rumzuprobieren. Bin allerdings ein totaler einsteiger und wollte mir nur zur Arbeitserleicherung etwas basteln. Vielleicht kannst Du mir ja helfen:

Ich möchte immer eine komplette Zeile aus einer anderen Excel Datei ausgeben (sind ca. 6 Spalten), die vorher jedoch gesucht werden muß.
An sich bekomme ich das hin, mein Problem ist nur, dass die zu durchsuchende Tabelle automatisch erzeugt wird und immer eine variable Zahl enthält. Ich muß zum suchen also vorher immer die entsprechende Zahl einpflegen.

aktuell sieht meine Formel so aus, was auch problemlos funktioniert
=VLOOKUP(A145;[datei_92.xls]Sheet1!$A$2:$O$26;3;FALSE)

Jedoch heißt die Datei morgen „datei_93.xls“
Wie kann ich der Formel also einfach sagen, dass ich dort eine andere Datei haben möchte?

Ich hoffe meine Frage ist einigermaßen verständlich formuliert!
Gruß,
Mads

jetzt habe ich bei den ganzen Kommentaren glatt die falsche Person angeschrieben…

Meinte natürlich Mourad (wobei mir natürlich jeder gerne helfen darg)

Viel rumprobieren hat mich zu der Lösung gebracht, ob die Lösung schön ist weiß ich nicht, aber sie funktioniert :)

=VLOOKUP(A27;INDIRECT(CONCATENATE(„‚[„;D27;“]“;“Sheet1‘!$A$2:$S$60000″));3;FALSE)

Hallo Mads,

prima, dass Du selbst eine Lösung gefunden hast. Eine ähliche Lösung hätte ich auch vorgeschlagen. CONCATENATE kannst Du in der Kurzform verwenden, z.B. statt…

CONCATENATE("Hallo";A1")

…geht auch…

"Hallo"&A1.

CONCATENATE zu verwenden ist aber jetzt nicht falsch.

Viele Grüße, Mourad

Guten Tag,
vielleicht können Sie mir helfen. In einem regelmäßigen automatischen Export werden die aktuellsten CSV-Dateien mit Zahlen angelegt. Dabei verändert sich der Ordnername immer wieder. Ist es möglich Excel zu sagen „Suche in diesem Verzeichnis nach der neuesten Datei“?

Wenn nein: Die Ordner haben den gleichen Namen und werden daher automatisch, wenn importiert, vom Namen her erweitert (name, name(1), name(2)).

Könnte ich darüber evtl. Excel sagen verschieb bei jeder Datenabfrage den Zahl nach dem Tabellennamen um 1?

Ich danke bereits jetzt für Ihre Mühe!

Hallo Viktor

mit einer reinen Formellösung dürfte dies m.E. nicht gehen. Somit wäre eine VBA-Lösung in Betracht zu ziehen, wo per VBA der Ordner abgefragt würde und die CSV’s importiert werden würden. Excel müsste sich dann merken, welche Dateien bereits importiert worden sind. Also, etwas Aufwand.

Wenn es die Möglichkeit gäbe, alle CSV-Dateien in einem Ordner abzulegen und die Dateien unterschiedliche Namen haben, aber alle denselben Aufbau (nur dann), könnte man PowerQuery ab Excel 2013 für den Import verwenden und bräuchte auch kein Makro. PowerQuery kann Ordner importieren und aktualisiert neue Dateien automatisch bei Abfrageaktualisierung.

Viele Grüße, Mourad

Vielen Dank für die schnelle Antwort! Ich werde schauen, ob ich vielleicht doch eine alternative Lösung finde.

Trackbacks for this post