Tabellen kopieren und externe Bezüge automatisch anpassen

Wie Sie dafür sorgen, dass Bezüge in kopierten Tabellen sich nach Ihren Wünschen verändern:

Von der täglichen Arbeit mit Excel werden Sie die Möglichkeit kennen, Formeln kopieren zu können, wobei sich der relative Bezug dann automatisch anpasst. Bedauerlicherweise funktioniert das nicht, wenn Bezüge auf mehrere andere Tabellen zugreifen und sich der Tabellenname anpassen soll.

Stellen Sie sich folgende Situation vor: Sie wollen aus verschiedenen Tabellen mit den Namen 01.xls, 02.xls usw. Werte aus immer der gleichen Zelle auslesen. Die Bezüge haben dann den folgenden Aufbau: 

=’c:\Eigene Dateien\[01.xls]Tabelle1’!A1 

=’c:\Eigene Dateien\[02.xls]Tabelle1’!A1 

Wenn Sie einen solchen Bezug in Ihrer Tabelle kopieren, wird nur der hinten stehende Bezug auf die Zelladresse angepasst. Falls Sie aber den gesamten Bezug einfach und komfortabel verändern möchten, arbeiten Sie nach der folgenden Methode: 

Zuerst legen eine Spalte an, in die der variable Teil des Dateinamens geschrieben werden kann. Daraus setzen Sie dann die gewünschte Formel zusammen, die Sie kopieren können. Das geht so:

Legen Sie in Ihrem Tabellenblatt eine Spalte an, in die der variable Teil (im vorliegenden Fall der Dateiname) eingegeben wird.

Diese Spalte müssen Sie mit dem Text-Zahlenformat einrichten. Tragen Sie dann die variablen Teile des Dateinamens in die Spalte ein, wie die folgende Abbildung zeigt:


 








In Zelle B1 tragen Sie dann die folgende Formel ein:

="='C:\Eigene Dateien\["&A1&".xls] Tabelle1' !$A$1"

Diese Formel kopieren Sie in die darunter liegenden Zellen. Das Ergebnis in der Tabelle zeigt die folgende Abbildung:


 





















Excel interpretiert das Ergebnis der Formel zunächst als Text. Um mit diesen Formeln auch rechnen zu können, müssen Sie sie umwandeln.

Markieren Sie alle Zellen mit kopierten Formeln – im vorliegenden Beispiel den Zellbereich B1:B20. Mit dem Befehl "Bearbeiten – Kopieren" speichern Sie die Zellinhalte in der Zwischenablage. Lassen Sie die Zellen markiert und rufen Sie im Menü "Bearbeiten" den Befehl "Inhalte einfügen" auf. In dieser Box aktivieren Sie die Option "Werte". Das bestätigen Sie mit der Schaltfläche "OK". Anstelle der Formeln stehen nun die Ergebnisse in den Zellen.

Lassen Sie die Zellen wiederum markiert und rufen Sie im Menü "Daten" den Befehl "Text in Spalten" auf, um den Textkonvertierungs-Assistenten zu starten. Hier bestätigen Sie direkt den Startbildschirm mit der Schaltfläche "Fertig stellen".

Damit sind die Formeln in der gewünschten Form zusammengesetzt und zeigen das entsprechende Ergebnis.



Mehr exklusive Excel-Tipps erhalten Sie im neuen Spezialdienst "Excel Formel Ticker ". Klicken Sie hier für weitere Informationen!


letzte Änderung am 24.07.2018
Quelle:  Excel Praxis für Controller

RSS
Literaturhinweise
Excel-Tools

Weitere Fachbeiträge zum Thema

Premium-Stellenanzeigen


Knauf Insulation GmbH
Simbach am Inn
Meteor GmbH
Bockenem
HUK-COBURG Versicherungsgruppe
Coburg
Gebr. Brasseler GmbH & Co. KG
Lemgo
Captrain Deutschland GmbH
Berlin

Eigenen Fachbeitrag veröffentlichen? 

Sie sind Autor einer Fachpublikation oder Entwickler einer Excel-Vorlage? Gern können Sie sich an der Gestaltung der Inhalte unserer Fachportale beteiligen! Wir bieten die Möglichkeit Ihre Fachpublikation (Fachbeitrag, eBook, Diplomarbeit, Checkliste, Studie, Berichtsvorlage ...) bzw. Excel-Vorlage auf unseren Fachportalen zu veröffentlichen bzw. ggf. auch zu vermarkten. Mehr Infos >>

Kommentar zum Fachbeitrag abgeben

Nur registrierte Benutzer können Kommentare posten!