Ganze Datenbereiche aus einer Excel Datei in eine andere zu verlinken kann einem schnell so vorkommen als müsste man die Logistik für die beladung eines Panamax Frachtschiffes übernehmen.
Problem:
Man hat zwei Excel Dateien. Man möchte einen ganzen Datenbereich verlinken.
Ein kleines Beispiel aus der Praxis veranschaulicht die Sache:
In der ersten Excel Datei, nenne wir sie doch Quelldatei, werden z.B. Marketing Daten gesammelt und in einer Liste eingetragen.
In der zweiten Datei, nennen wir sie Zieldatei, möchte man die gesamte Liste aus der Quelldatei reinladen in dem man diese Verlinkt, um danach Auswertungen zu erstellen im Berichtswesen.
Die auf dem Netz oft vorgeschlagene Lösung man soll einfach Paste Special>Link verwenden funktioniert nicht wirklich, denn dies ist nichts anderes als eine automatisierte Erstellung von vielen =A1 Verlinkungen.
Weshalb eine einfache Verlinkung keine gute Lösung ist merkt man spätestens dann wenn die Verkäufer anrufen und nachfragen wieso denn Kunde XY oder der Verkaufbetrag Z nicht in der Auswertung drin sind.
Wenn die Liste grösser wird (mehr Einträge bekommt) dann würden wir dies nicht sehen bei einer A1 Verlinkung, es sei denn wir kopieren die A1 Referenz bis ganz nach unten… was aber zu einer aufgeblähten Dateigrösse führt. Auch würden wir beim Löschen von Zeilen in der Quelldatei schnell Fehler bekommen. Diese Probleme könnte man natürlich mit viel Aufwand umgehen, aber…
…es geht auch einfacher!
Wie man ganze Tabellen (Listen) in Excel verlinkt:
- In der Quelldatei wird sichergestellt dass die zu Importierende Liste als Benannter Bereich gespeichert ist (in unserem Beispiel heisst der Bereich ). Dieser ist idealerweise dynamisch oder zumindest so erstellt dass wenn neue Zeilen eingeschoben werden der Bereich mitwachst.
Hinweis: Wenn die Liste auf einem eigenen Arbeitsblatt gespeichert ist und nur die Liste mit den Spaltenbeschriftungen vorhanden ist (also kein Titel oder andere benutzte Zellen auf dem Arbeitsblatt, etc.) kann man auch direkt das ganze Arbeitsblatt importieren ohne einen Benannten Bereich daraus machen zu müssen. Nicht auswählbar hingegen sind Tabellen die durch das Tabellenwerkzeug erstellt wurden. Dazu weiter unten noch Infos.
- In der Zieldatei ein frisches Arbeitsblatt erstellen in der die Verlinkte Tabelle eingetragen werden soll.
- Jetzt in der Zieldatei im Menü „Daten“ unter Externe Daten „von Access“ importieren (geht auch mit Andere Datenquelle>XML Daten) auswählen.
- Im geöffneten Fenster werden nun nur XML Dateien angezeigt. Hier auf „Alle Dateien“ umstellen damit auch Excel Dateien angezeigt werden. Jetzt kann man die Excel Quelldatei auslesen.
- Im geöffneten Fenster muss nun nur noch der Name des Bereiches (von Schritt 1) ausgewählt werden und dieser als Tabelle (Standardeinstellung) importiert werden.
Die verlinkte Tabelle kann jetzt automatisch aktualisiert werden. Entweder durch „Aktualisieren“ drücken oder in dem man gleich ein Haken setzt bei „Aktualisieren beim Öffnen der Datei“ im entsprechenden Menü: Daten>Alle aktualisieren>Einstellungen!
Excel 2010 bis 2016
Tatsächlich gibt es in neueren Versionen von Excel mehr Möglichkeiten Daten zu importieren und / oder zu verlinken. Die in Excel 2016 direkt enthaltene “Abrufen und Transformieren” Funktionen sind extrem flexibel was Datenquellen und Möglichkeiten anbelangt um Daten zu importieren.
Glücklicherweise ist das Power Query Add-In bei der 2013 Version bereits von Haus aus dabei und muss lediglich noch aktiviert werden als COM Add-In im Entwickler Menü.
Das Power Query Add-In ist auch bereits verfügbar für Excel 2010, jedoch nur als Add-In zum download, welches man jedoch direkt bei Microsoft gratis herunterladen kann.
Vielen Dank für diesen hilfreichen Tipp. Damit wurde mein Arbeitsalltag gerade massiv vereinfacht ;-)
Es werden scheinbar nur die werte verlinkt – gibt es hier die Möglichkeit auch die Formeln aus der Quelldatei zu übernehmen?
Hallo Ralf, nein die Formeln werden so nicht übernommen. Letztendlich, wenn Du die ganze Formel portieren möchtest, musst Du die Formel kopieren so dass die Referenzen in der Formel automatisch von Excel upgedatet werden.
Schade, ich hoffte irgendwie das manuelle Bearbeiten (kopieren) abzulösen, denn momentan erstelle ich die Tabelle komplett über S-Verweise und die Quelldatei vergrößert sich ständig.
Genau danach habe ich gesucht!
Vielen Dank!
Ich hatte gehofft, dass dieser Artikel mich endlich ans Ziel bringt, da ich heute schon mehrere Stunden mit einer zu großen Tabelle und mehreren Abstürzen in Excel zugebracht habe. Nun scheitert es wohl daran, dass ich einen Mac besitze und keinen PC, denn das Power Query Add-In scheint es für Apple nicht zu geben, richtig? Über hilfreiche Tipps, wie ich meinen gespeicherten Bereich doch noch importieren kann, wäre ich sehr, sehr dankbar!
(Office 365 – Version 16.16.4 auf mac OS High Sierra 10.13.6)
Hallo Astrid, leider habe ich selber kein Mac und kann Dir nicht mit 100% Sicherheit sagen ob es ein Add-In gibt oder nicht. Aber da Du Office 365 hast, müsste es eigentlich schon integriert sein… dort dann einfach direkt in Excel und nicht als Add-In. In der Windows Version wäre es unter dem “Daten” Menü>”Daten abrufen”>”Power Query Editor starten”!
Hallo Lukas
Nun bin ich hier endlich auf mein Problem gestossen. Ich habe verschiedene Excel Dateien, aus denen teilweise einzelne Daten gezogen werden mit der INDEX & VERGLEICH Funktion. Nun funktioniert das plötzlich nicht, weil ich die Quelldatei als Tabelle formatiert habe. Oben schriebst du: ” Nicht auswählbar hingegen sind Tabellen die durch das Tabellenwerkzeug erstellt wurden”. Kannst du mir sagen wieso? Oder hast du einen Tipp dieses Problem zu umgehen?
Hallo Lukas
Super, genau was ich schon seit langem suche.
Was mich erstaunt ist, dass das Datum nicht korrekt angezeigt wird. Beide Tabellen sind im 1904-Format angelegt und trotzdem zeigt es mir in der Zieldatei ein Datum um genau 4 Jahre später. Verstehe ich nicht. Hast Du mir hier einen Tipp was ich falsch mache?
Gruss Robert