Du kennst vielleicht das Problem: Beim Öffnen einer Excel-Datei kommt der Hinweis, dass die Datei einige Verknüpfungen zu externen Quellen enthält. Und nun sollst du dich entscheiden, ob die Verknüpfungen aktualisiert werden sollen oder nicht. Weil du ein vorsichtiger Anwender bist („gebranntes Kind scheut das Feuer“), möchtest du natürlich erst einmal wissen, zu welchen Dateien hier eigentlich verknüpft wird.
An dieser Stelle macht es Excel einem nicht gerade einfach. Wäre es nicht schön, wenn man übersichtlich in einem Tabellenblatt eine Auflistung aller verknüpften Dateien einschließlich der kompletten Pfade bekommen könnte? Gut, dass du fragst. Denn genau darum dreht es sich im heutigen Artikel!
Und so geht’s:
Die Ausgangslage
Du öffnest eine Arbeitsmappe und erhältst folgenden Hinweis:
Hinweis beim Öffnen der Arbeitsmappe
Wie eingangs geschrieben, wählst du im Zweifelsfall erst einmal die Option „Nicht aktualisieren“. Über das Menü „Daten | Verknüpfungen bearbeiten“ kann man sich die Übersicht der verknüpften Dateien anzeigen und bekommt ein Bild der folgenden Art:
Liste der Verknüpfungen
In meinem Beispiel sieht man, dass 6 Dateien als externe Quellen mit der aktuellen Arbeitsmappe verbunden sind. Markiert man einen der Einträge, wird unterhalb der Liste der dazugehörige Speicherort angezeigt. Das ist zwar zumindest ein Anfang, aber alles andere als komfortabel oder übersichtlich. Denn zum einen muss man der Reihe nach alle Dateien in der Liste anklicken, um unten den Pfad zu sehen. Noch ärgerlicher ist aber, dass diese Pfadangabe bei tief verschachtelten Verzeichnisstrukturen nur unvollständig ist, wie man an den drei Punkten sieht:
Unvollständige Pfadangabe
Natürlich könnte man jetzt die Quelldatei über die entsprechende Schaltfläche öffnen, aber man weiß trotzdem nicht genau, wo diese Datei eigentlich gespeichert ist. Also ist guter Rat teuer…
…sofern man nicht den folgenden Trick kennt!
Die Lösung
Und diesen Trick habe ich selbst erstmalig bei Leila Gharani gesehen, einer wahren Excel-Meisterin. Sie greift dafür auf eine alte Excel-4-Makrofunktion zurück. Keine Angst, wie fangen jetzt hier nicht mit der Makroprogrammierung an. Aber einige dieser alten Funktionen können ganz ohne Programmierung in einer Excel-Datei verwendet werden – wenn man weiß, wie.
Die Funktion, um die es hier geht, heißt VERKNÜPFTE.DATEIEN (englisch: LINKS)
Und sie macht genau, was der Name schon andeutet, nämlich eine Liste der verknüpften Dateien zu erstellen. Wenn du jetzt allerdings versuchst, diese Funktion direkt in eine Zelle einzutippen, erhältst du nur eine Fehlermeldung:
Funktion kann nicht direkt in Zelle eingegeben werden
Da es sich, wie oben erwähnt, um eine alte Makrofunktion handelt, kann man sie nicht direkt in einer Zelle verwenden und sie taucht auch nirgendwo in der Funktionsübersicht auf. Aber sie lässt sich über den Namensmanager nutzen. Dazu legen wir einfach über das Menü „Formeln | Namen definieren“ einen neuen Namen an.
Als Namen vergebe ich der Einfachheit halber „Verknüpfungen“. Und in das Feld „Bezieht sich auf“ wird jetzt die Funktion eingetippt:
=VERKNÜPFTE.DATEIEN()
Einen neuen Namen definieren
Und das war es auch schon, was die Vorbereitungen angeht. Abhängig von deiner Excel-Version sind die nächsten Schritte etwas unterschiedlich.
Office 365 / Microsoft 365
Falls du eine aktuelle Abo-Variante nutzt, hast du es besonders leicht. Denn die Funktion läuft automatisch in die benachbarten Zellen über, sodass gleich sämtliche Dateiverknüpfungen in jeweils einer eigenen Zelle angezeigt werden:
Automatischer Überlauf in Office 365
Um eine vertikale Liste zu erhalten, muss die Formel nur noch mit der MTRANS-Funktion umschlossen werden:
Vertikale Anordnung mit MTRANS
Falls du bisher noch nie von MTRANS gehört hast, empfehle ich dir diesen Artikel zur Lektüre.
Excel 2019 und früher
Wenn man hier die Formel =VERKÜPFUNGEN in eine Zelle tippt, wird als Ergebnis erst einmal nur ein einziger Name angezeigt:
Nur der erste Dateiname wird angezeigt
Der Grund dafür ist, dass die zugrunde liegende Funktion VERKNÜPFTE.DATEIEN ein Array zurückliefert, welche alle Dateinamen enthält. Office 365 löst dieses Array automatisch auf, daher der oben beschriebene Überlauf. Um in anderen Excel-Versionen alle Namen aus dem Array zu erhalten, müssen wir in jedem Fall zusätzlich die umschließende MTRANS-Funktion verwenden.
Da wir eingangs über das Menü die Anzahl der verknüpften Dateien schon gesehen haben, markiere ich jetzt 5 Zellen (falls die Zahl nicht bekannt ist, kannst du einfach auf Verdacht ein paar Zellen mehr markieren). Erst dann tippe ich in die oberste Zelle ein
=MTRANS(VERKNÜPFUNGEN)
und schließe die Eingabe unbedingt mit der Tastenkombination Strg+Umschalt+Eingabe ab, damit die Formel als Array-Funktion in alle markierten Zellen übertragen wird. Man erkennt das dann an den umschließenden geschweiften Klammern:
Eingabe von MTRANS als Array-Funktion
In der weiter oben beschriebenen Office-365-Variante ist diese Tastenkombination nicht mehr nötig, da hier die Arrays automatisch in so viele Zellen aufgelöst werden, wie eben gerade benötigt werden.
Falls du jetzt mehr Zellen markiert hattest, als das Array Dateinamen enthält, wirst du in den überflüssigen Zellen einen #NV-Fehler angezeigt bekommen:
#NV-Fehler bei zu vielen markierten Zellen
Das ist zwar nicht ganz so schön, gibt dir aber zumindest die Gewissheit, dass du alle verknüpften Dateien angezeigt bekommst.
Hinweis:
Da es sich bei der beschriebenen Funktion VERKNÜPFTE.DATEIEN um eine alte Makrofunktion handelt, muss die Arbeitsmappe auch im Makroformat (*.xlsm oder *.xlsb) abgespeichert werden. Eine entsprechende Warnung beim Speichern weist einen auf diesen Umstand auch ganz dezent hin:
Speichern zwingend im Makroformat
Wie du an diesem Beispiel gesehen hast, können manchmal ein paar längst vergessene Funktion auch heute noch hilfreich sein. Allerdings gibt es keine Garantie dafür, dass Microsoft diese nützlichen Dinosaurier irgendwann einmal aus zukünftigen Excel-Versionen entfernt. Der Einsatz erfolgt daher immer auf eigenes Risiko.
P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)
P.P.S. Das Problem sitzt meistens vor dem Computer.
Du erwähnst die MTRANS-Funktion, woraufhin ich den betreffenden Artikel gelesen habe. Dort heißt es, dass man MTRANS eine Matrix-Formel sei. Hat man das irgendwann geändert? Denn als Matrix funktioniert sie bei mir nicht, aber als normale Formel.
Aber auch mal wieder mit etwas, was bei Auswertungen schnell kritisch werden kann: In der mit MTRANS transponierten Tabelle werden ursprünglich leere Felder zu 0 (null). Aus Missings werden so schnell verzerrende Daten.
Hallo Tim,
die MTRANS-Funktion muss in allen Versionen vor Office-365 als Matrix-Formel eingegeben werden, damit sie wie gewünscht funktioniert. Erst in der aktuellen 365er-Version geht es auch ohne Matrix. Dein Hinweis auf die leeren Felder und die Nullen ist berechtigt und lässt sich leider nicht wirklich umgehen (das Ausblenden von Nullwerten blendet natürlich auch „echte“ Nuller aus).
Schöne Grüße,
Martin
Ah, OK. Gut zu wissen, danke. Dann kann ich das nur zu Hause verwenden und bei der Arbeit muss ich mit der Matrix-Formel arbeiten.
Die Funktion ist ja ganz gut, aaaaaber: man muss sie entweder in jeder Excel-Datei neu definieren oder sie in die Mappe.xlt (bis 2007) oder Mappe.xltx (ab 2007) hinterlegen.
Den Hinweis auf die „zentrale“ Definition würde ich noch aufnehmen 😉
Hallo Hans,
guter Tipp mit der Vorlage. In diesem Fall müsste allerdings eine Makrovorlage gewählt werden, also *.XLTM
Schöne Grüße,
Martin
Eine feine Lösung!
Auch in Excel 2019 muss man noch die Array-Funktion verwenden.
Also nicht erst ab Excel 2016 und früher.
Microsoft möchte wohl mit Zwang“ alle User in die Abo-Version ziehen…?
Hallo Michael,
du hast Recht, das gilt natürlich auch für Excel 2019. Habe ich jetzt im Artikel oben geändert. Und ja, Microsoft hätte sicherlich gerne alle Schäfchen auf der Office-365-Weide 🙂
Schöne Grüße,
Martin
Ganz oft werden Verknüpfungen zu anderen Dateien in dem Dialog gar nicht angezeigt (bspw. bei Datenüberprüfungen, Formeln in Diagrammen, etc.)
Darum nutze ich lieber das Gratis Add-In namens „Find Links“. Einfach „find links excel add-in“ in Google eingeben und dem ersten Link folgen. Hier werden zumindest zuverlässig alle Links gefunden und sauber in einer Liste ausgegeben.
Schöne Grüße
Marcel
Hallo Marcel,
danke für den Tipp!
Schöne Grüße,
Martin