Externe Tabellenbezüge: Das solltest du wissen! 16

Artikelbild-339
Wenn du diesen Artikel gelesen hast, wirst du zukünftig keine externen Verknüpfungen mehr einsetzen
 

Der heutige Artikel dreht sich um ein Thema, mit dem sich jeder Excel-Anwender beschäftigen sollte, der in seinen Arbeitsmappen Bezüge oder Verweise zu anderen Excel-Dateien verwendet.

Inspiriert dazu wurde ich durch die Anfrage einer Leserin, die sich über ein merkwürdiges Verhalten von Excel wunderte. Bei meinen Untersuchungen bin ich dabei über eine Sache gestolpert, die ich für sehr gravierend halte und die möglicherweise viele Excel-Anwender (unbewusst) betrifft.

Solltest du also zumindest gelegentlich Verknüpfungen zu externen Arbeitsmappen einsetzen, dann lies unbedingt diesen Artikel, es könnte wirklich wichtig für dich sein.

Die Ausgangslage

Was genau war also das Problem der oben erwähnten Leserin? Sie hatte in einer Arbeitsmappe SVERWEIS-Formeln, welche Informationen aus einer anderen Arbeitsmappe – also aus einer anderen Excel-Datei – auslesen und anzeigen. Bereits an dieser Stelle ziehe ich üblicherweise die Luft hörbar durch die Zähne ein: Zellenbezüge in andere Arbeitsmappen führen früher oder später zu Ärger. Jeder kennt vermutlich die Meldung zu irgendwelchen externen Verknüpfungen beim Öffnen einer Datei.

Wer mit externen Verknüpfungen kämpft, findet in diesen beiden Artikeln viele der gängigsten Verstecke, in denen sich solche Verknüpfungen verbergen können:

Nun schildert mir besagte Anwenderin aber folgendes Problem: Die Quelldatei wurde gelöscht, aber die SVERWEIS-Formeln liefern immer noch die gesuchten Werte. Wie kann das sein, wenn die referenzierten Daten doch nicht mehr existierten?

Die externen Daten werden also offensichtlich in der Datei mit den SVERWEIS-Formeln irgendwo gepuffert. Und man denkt sich vielleicht, ist doch eigentlich gar nicht so schlecht, oder? Schließlich kann man als Anwender dann ohne Fehlermeldungen weiterarbeiten…

Ist Excel noch ganz dicht?

Die Frage ist nun, was genau und wie viele Daten da eigentlich im Hintergrund gepuffert werden, ohne dass es irgendjemand mitbekommt?

Wenn man beispielsweise zu einer Artikelnummer einfach nur die passende Bezeichnung dazuholen möchte, dann erscheint das erst einmal als kein großes Problem – und ist es vielleicht auch nicht.

Wenn sich in der externen referenzierten Artikeltabelle aber auch die Einkaufspreise befinden, dann könnte es schon etwas anders aussehen. Und wenn statt einer Artikeltabelle auf eine Kundentabelle verwiesen wird, in der sich alle möglichen Informationen befinden, dann hat man möglicherweise ein ziemlich großes Problem. Spätestens dann, wenn die Arbeitsmappe an andere (externe?) Anwender geschickt wird.

Denn es könnte gut sein, dass man damit völlig unwissentlich vertrauliche Daten weitergibt!

Ich habe daher im Folgenden verschiedene Szenarien untersucht, um etwas besser abschätzen zu können, unter welchen Bedingungen möglicherweise ein Datenleck entstehen kann. Jedes Szenario besteht aus einer eigenen kleinen Arbeitsmappe mit den Verweisformeln – ich nenne sie die Szenario-Datei- und einer gemeinsamen externen Datei, auf die dann in den Formeln verwiesen wird. Diese externe Datei enthält 14.000 Rechnungsdatensätze.

Szenario 1: Externer Zugriff auf eine Liste mit SVERWEIS

Die Rechnungen befinden sich in einer Datei namens „Externe_Quelle.xlsx“ im ersten Tabellenblatt und verteilen sich auf 7 Spalten:

Eine sehr lange Rechnungsliste in einer eigenen Datei

Eine sehr lange Rechnungsliste in einer eigenen Datei


In einer zweiten Arbeitsmappe mit dem Namen „Szenario1.xlsx“ erfolgt dann der Zugriff auf diese Rechnungen. In Zelle A5 wird dazu eine Rechnungsnummer angegeben, in den Zelle B5:D5 sollen anschließend per SVERWEIS die passenden Daten angezeigt werden:
Die Datei für das erste Szenario

Die Datei für das erste Szenario

Bevor ich die Formeln eintrage, werfen wir einen kurzen Blick auf die Dateigrößen:

Dateigrößen vorher

Dateigrößen vorher


Meine Szenario-Datei hat also im Augenblick eine Größe von nur 10 KB – ist nicht verwunderlich, denn sie ist ja praktisch noch leer. Die Rechnungsdatei Externen_Quelle.xlsx kommt auf 562 KB.

Nun kommen die SVERWEIS-Formeln dazu:
=SVERWEIS($A$5;[Externe_Quelle.xlsx]Tabelle1!$A$1:$G$14001;3;FALSCH)

Dateiübergreifende SVERWEIS-Funktionen

Dateiübergreifende SVERWEIS-Funktionen

Soweit, so erwartbar. Nun schließe ich beiden Dateien und benenne die Rechnungstabelle um in „ICH-BIN-WEG.xlsx“ (ich hätte sich natürlich auch komplett löschen können). Da beiden Dateien geschlossen waren, „weiß“ die Szenario1-Datei nichts davon, dass die Rechnungstabelle jetzt anders heißt. Anschließend öffne ich nur die Szenario1-Datei wieder.

Wie zu erwarten, kommt ein Warnhinweis auf externe Verknüpfungen. Klickt man auf „Inhalte aktivieren“, wird man mit der nächsten Warnung beglückt. Excel erkennt, dass die Verknüpfungen nicht aktualisiert werden können.

Hinweis auf Probleme mit den externen Daten

Hinweis auf Probleme mit den externen Daten

Logisch, der Dateiname stimmt ja nicht mehr.

Und trotzdem liefern die SVERWEIS-Formeln immer noch Daten liefern, obwohl die Datei unter diesem Namen nicht mehr existiert!

Gut, du könntest jetzt sagen, das sind halt noch die Werte zum Zeitpunkt der letzten Speicherung. Dann lass uns jetzt mal ein paar andere Rechnungsnummere eingeben:

Die vorhandenen SVERWEIS-Formeln funktionieren trotzdem noch

Die vorhandenen SVERWEIS-Formeln funktionieren trotzdem noch


Egal, welche Rechnungsnummer ich eintippe, es werden die passenden Daten angezeigt.

WIE KANN DAS SEIN???

Ein kleiner Blick auf die aktuelle Dateigröße meiner Szenario-Datei lässt einen Verdacht aufkommen:

Dateigröße nachher

Dateigröße nachher


Die Datei ist von schlanken 10 KB auf 574 KB angewachsen, das entspricht etwa der Größe der Rechnungsdatei. Was wiederum erklärt, warum die SVERWEISE immer noch korrekte Daten liefern. Denn die Funktionen greifen nicht mehr auf die Ursprungsdatei zu, obwohl dies in der Formel eigentlich so aussieht. Sondern sie holen die Daten direkt aus einem versteckten „Puffer“ irgendwo tief in der Szenario-Datei.

Mit anderen Worten:
Die Szenario-Datei enthält jetzt offensichtlich auch meine komplette Rechnungstabelle!

Auch wenn die Tabelle direkt in Excel nicht unmittelbar zu sehen ist, kann man sie über ein paar SVERWEIS-Formeln ohne Probleme ganz leicht auslesen. Und zwar nicht nur die bereits angezeigten Felder für Belegart, Artikel und Menge. Sondern auch alle weiteren:

Auch neu hinzugefügte Formeln funktionieren

Auch neu hinzugefügte Formeln funktionieren


Ich kopiere dazu einfach eine vorhandene SVERWEIS-Formel und ändere dann nur die Index-Nummer. Nach dem Betätigen der Eingabetaste kommt zwar ein Dialogfenster, in der ich eine Datei auswählen soll. Diese Aufforderung kann aber über Abbrechen oder die ESC-Taste einfach ignoriert werden, die Formel liefert trotzdem das gewünschte Ergebnis.

Ich hoffe, du bist jetzt angemessen beunruhigt.
(nein, auch wenn dir das vielleicht ganz komfortabel erscheint, du willst ganz bestimmt NICHT deine kompletten Daten so einfach weitergeben. Schon gar nicht, wenn die Szenario-Datei vielleicht für externe Adressaten bestimmt ist…)

Ist vielleicht nur der SVERWEIS böse? Wie sieht’s denn mit dem XVERWEIS aus?

Szenario 2: Externer Zugriff auf eine Liste mit XVERWEIS

Also: Rechnungsdatei zunächst wieder zurückbenannt, dann das gleiche Spiel wie vorhin, nur eben mit der XVERWEIS-Funktion.

Szenario 2: Externe Verknüpfung mit XVERWEIS

Szenario 2: Externe Verknüpfung mit XVERWEIS

Anschließend beide Dateien geschlossen und die Rechnungsdatei wieder umbenannt in „ICH-BIN-WEG.xlsx“. Dann gab es die erste Überraschung bei der Dateigröße.
Die Szenario2-Datei ist auch von ursprünglich 10 KB angewachsen, aber „nur“ auf 329 KB:

Datei ist nicht ganz so stark angewachsen

Datei ist nicht ganz so stark angewachsen

Also die Datei wieder geöffnet und die vorhandenen XVERWEIS-Formeln mit anderen Rechnungsnummern getestet. Doch auch hier werden problemlos die entsprechenden Daten angezeigt. Nun möchte ich auch die weiteren Felder für Datum, Kunde und Einzel-VK abrufen. Dazu wieder die vorhandenen Formeln kopiert und diesmal die Spaltenbezüge angepasst:

Neue XVERWEIS-Funktionen führen zu #BEZUG!-Fehlern

Neue XVERWEIS-Funktionen führen zu #BEZUG!-Fehlern

Das ist interessant:
Während die bereits vorhandenen XVERWEIS-Formeln auch neue Daten angezeigt haben, liefern die neuen XVERWEIS-Funktionen nur #BEZUG!-Fehler. Wie es also aussieht und wie auch schon auf Grund der geringeren Dateigröße zu vermuten war, wurde hier nicht die komplette Rechnungstabelle gespeichert, sondern vermutlich nur die zuvor referenzierten Spalten. Da wir in Szenario 1 beim SVERWEIS die komplette Rechnungstabelle referenziert hatten, wurde dort natürlich auch alles abgespeichert.

Merke:
XVERWEIS ist in diesem Zusammenhang auch böse, aber nicht ganz so schlimm.

Szenario 3: Externer Zugriff auf eine formatierte Tabelle

Bisher habe ich als Quelle für die Rechnungsdaten eine einfache Liste verwendet. In diesem Szenario wird stattdessen auf eine formatierte („intelligente“) Tabelle zugegriffen mit dem Namen „tblRechnungen“. Alles andere bleibt gleich.

Szenario 3: SVERWEIS auf eine intelligente Tabelle

Szenario 3: SVERWEIS auf eine intelligente Tabelle

Große Überraschung beim anschließenden Blick auf die Dateigröße: Die Szenario3-Datei ist weiterhin extrem schlank geblieben! Ganz offensichtlich werden hier keine Daten gepuffert.

Datei bleibt praktisch unverändert klein

Datei bleibt praktisch unverändert klein

Beim erneuten Öffnen der Datei werden zunächst noch die alten Werte angezeigt. Sobald man aber die Rechnungsnummer ändert, gibt es nur noch #BEZUG!-Fehler:

SVERWEIS liefert keine neuen Daten mehr

SVERWEIS liefert keine neuen Daten mehr

Beim XVERWEIS ist das nicht anders, durch den Zugriff auf eine intelligente Tabelle werden keine zusätzlichen Daten gespeichert, beide Funktionen verhalten sich hier also gleich.

Merke:
Intelligente Tabellen sind sehr gut! (auch wenn das einer der wenigen Fälle sein dürfte, wo man sich über einen #BEZUG!-Fehler freuen kann)

Wie man mit diesem Problem umgehen kann

Man könnte sicherlich noch einige weitere Szenarien durchspielen (benannte Bereiche anstelle von Zellbezügen, Mischung von SVERWEIS und XVERWEIS in einer Datei…), aber das erspare ich uns an dieser Stelle. Ich denke, die Problematik wurde auch so deutlich genug.

Was kann man also tun, um nicht durch die Hintertür Daten weiterzugeben? Dazu fallen mir 4 Punkte ein:

Keine externen Verknüpfungen einsetzen
Ganz unabhängig von dem potenziellen Datenleck, das man sich damit einhandelt, sind externe Verknüpfungen zwischen verschiedenen Dateien meines Erachtens immer schon Teufelszeug und führen früher oder später fast zwangsläufig zu Ärger. Am besten man verzichtet komplett darauf. Und wenn man schon auf externe Daten zugreifen muss, dann gleich richtig und mit Power Query.

Verknüpfungen unterbrechen
Falls die Verbindungen zumindest während der Erstellung der Datei benötigt werden, dann sollte man sie spätestens dann entfernen, wenn die Datei an Dritte weitergegeben werden muss.
Über die Option „Verknüpfungen unterbrechen“ ist das schnell erledigt (in älteren Excel-Versionen „Verknüpfungen bearbeiten“ und dann „Verknüpfung löschen“):

Externe Verknüpfungen unterbrechen

Externe Verknüpfungen unterbrechen


Das hat im Falle von SVERWEIS- oder XVERWEIS-Funktionen zur Konsequenz, dass die Formeln durch Werte ersetzt werden. Auch wenn das vielleicht nicht für jedes Anwendungsfall die optimale Lösung ist, kann man dadurch zumindest sehr schnell den Stecker ziehen.

Excel-Optionen
In den Excel-Optionen gibt es sehr versteckt eine Einstellung, mit der sich verhindern lässt, dass diese externen Daten überhaupt erst irgendwo gepuffert werden:

Excel-Optionen für diese Arbeitsmappe anpassen

Externe Verknüpfungen unterbrechen


Entfernt man dieses standardmäßig gesetzte Häkchen, dann unterbleibt in der jeweiligen Arbeitsmappe diese Speicherung. Wenn – wie in Szenario 1 – bereits externe Daten zwischengespeichert wurden, dann werden diese wieder entfernt und die Dateigröße schrumpft auf das ursprüngliche Maß. Die Formeln und die Verknüpfungen an sich bleiben erhalten und führen dann beim nächsten Öffnen und Ändern der Rechnungsnummern eben zu #NV-Fehlern oder #BEZUG!-Fehlern.

Intelligente Tabellen verwenden
Wie wir gesehen haben, werden beim Zugriff auf formatierten/intelligenten Tabellen in externen Arbeitsmappen keine Daten zwischengespeichert. Das wäre ein weiteres, wenn auch vielleicht nur nebensächliches Argument, zukünftig noch mehr mit intelligenten Tabellen zu arbeiten. Zumindest werden dann keine Daten unbewusst weitergegeben.
 

Die hier beschriebenen Fälle erheben keinen Anspruch auf Vollständigkeit und beruhen ausschließlich auf meinen eigenen Tests. Aber sie haben bei dir hoffentlich das Bewusstsein für die Problematik geschärft – oder vermutlich erst geweckt. Und wenn ich dich dazu gebracht habe, zukünftig möglichst auf externen Verknüpfungen zu verzichten, dann freue ich mich ganz besonders!
 

Wie sind deine Erfahrungen mit externen Verknüpfungen?
War dir diese Problematik schon bekannt?
Lass es uns in den Kommentaren wissen!

 

Wenn dir der Artikel gefallen hat: Bitte weitersagen!
Das könnte dich auch interessieren:
Und immer daran denken: Excel beißt nicht!

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.



Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert

16 Gedanken zu “Externe Tabellenbezüge: Das solltest du wissen!

  • Avatar-Foto
    Jörg Weidanz

    Hallo Martin,
    danke für diesen Beitrag. Das war mir so noch nicht bewusst. Mit Verknüfungen auf exteren Daten arbeiten wir recht viel.
    Wenn Dateien allerdings das Haus verlassen, werde alle Werte in solchen Datein fest geschrieben.
    Es bleibt aber trotzdem ein Restrisko.

    Versteckte Verbindungen zwischen Tabellen sind ein echtes Ärgernis. Wir haben immer mal wieder das Problem:
    Tabelle, Aktualisierung über externe Quelle aus Access.
    Bei Aktualisierung werden dann MANCHMAL von Excel Tabellen geöffnet, die mit der Verknüpfung gar nichts zu tun haben. Warum das so ist haben wir noch nicht ermitteln können.

    Gruß
    Jörg Weidanz

    • Avatar-Foto
      Martin Weiß Autor des Beitrags

      Hallo Jörg,

      ja, versteckte Verbindungen können ziemlich nerven. Und wie man sieht, durchaus auch nicht ganz ungefährlich sein. Am besten ist, man verzichtet darauf.

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Rainer Linkenbach

    Auf ein ähnliches Phänomen bin ich seinerzeit schon bei Excel 5.0 und Power Point 5.0 gestoßen.
    Was waren wir froh, als wir endlich Excel-Übersichten in Power Point reinkopieren konnten – tolle Sache. Das Erstellen der Aufsichtsrats-Unterlagen beschleunigte sich ungemein. Aber huch – klickte man in PP ein Excel Sheet an, das aus einer Mappe mit mehreren Tabellen kam, öffnete sich die ganze Mappe, auch mit den internen Nebenkalkulationen UND Kommentaren. Das war doof. 😥
    Wir haben dann sehr schnell die Funktion „Als Graphik kopieren“ entdeckt, die das Problem löst. Damit arbeite ich in vielen Fällen heute noch.

    • Avatar-Foto
      Martin Weiß Autor des Beitrags

      Hallo Rainer,

      ja, das ist der Klassiker. Eingebettete Dokumente können hilfreich sein – oder eben zu den unerwünschten Nebeneffekten und Risiken führen.

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Rebekka

    Interessanter Beitrag – insbesondere nach meiner gestrigen Beobachtung: Bei der Verknüpfung auf eine externe formatierte Tabelle konnte Excel nur dann die aktuellen Werte ziehen, wenn die verknüpfte Datei geöffnet war; die Abfrage zur Aktualisierung beim Öffnen lieferte hier den Fehlerhinweis, dass die verknüpfte Datei nicht gefunden werden konnte (obwohl sie nicht umbenannt oder verschoben worden war). Im normalen Arbeitsalltag empfinde ich das als deutlichen Nachteil…

    • Avatar-Foto
      Martin Weiß Autor des Beitrags

      Hallo Rebekka,

      danke für deinen Kommentar. Und ich kann es nur bestätigen: Verknüpfungen zu externen Dateien sollte man tunlichst vermeiden, es führt in den meisten Fällen nur zu Ärger.

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Annette

    Sehr interessanter Beitrag.
    Selbst versuche ich externe Bezüge zu vermeiden, oder sie vielleicht nur temporär einzusetzen und dann wieder zu löschen.
    Aber ab und zu übersieht man vielleicht etwas.
    Vielleicht bekomme ich ja doch noch die eine oder andere Datei verkleinert…

  • Avatar-Foto
    Peter Nied

    Moin Martin,
    dieser Sachverhalt war mir nicht bekannt!
    In der Regel kopiere ich die Spalte und ersetze die Spalte durch die Werte,
    um die Rechenformel zu löschen, damit Excel nicht jedes mal rechnen muss.
    Wie es dann mit der Dateigröße aussieht muss ich mir noch ansehen.
    Danke für den Hinweis.

    • Avatar-Foto
      Martin Weiß Autor des Beitrags

      Hallo Peter,

      da geht es dir wahrscheinlich so wie den allermeisten Anwendern, die Problematik ist einfach nicht bekannt 😉

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Thomas Cramer

    Ich wusste es ja schon immer, aber Du hast es jetzt ausgesprochen: Externe Verknüpfungen sind Teufelswerk!

    Den Hinweis auf Power Query für den Umgang mit externen Daten finde ich richtig. Ich gebe nur zu Bedenken, dass das oft das Know-How des Anwenders übersteigt. (Dazu gibt es tolle Bücher von Martin Weiß…)

    • Avatar-Foto
      Martin Weiß Autor des Beitrags

      Hallo Thomas,

      es stimmt, dass viele Anwender mit Power Query noch überfordert sind. Aber wie du schreibst, da gibt es auch Bücher dafür. Oder sogar Online-Kurse… 😉

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Lutz Wörner

    Moin Martin,

    danke für den tollen Beitrag über die Probleme mit MS Programmen und die „merkwürdige“ Denke einiger Programmierer.

    Fazit: Wie schon von Dir erwähnt, entweder externe Verknüpfungen ganz vermeinten oder wenigsten dann nur mit intelligenten Tabellen.

    Gruß Lutz

    • Avatar-Foto
      Martin Weiß Autor des Beitrags

      Hallo Lutz,

      die Grundidee der Programmierer wäre ja vielleicht gar nicht so schlecht. Aber die Schattenseiten sollte man als Anwender auf jeden Fall kennen.

      Schöne Grüße,
      Martin