Inhalte
Kommunikation ist eine sehr nützliche Funktion in Excel. Schließlich müssen Benutzer sehr oft Informationen aus anderen Dateien verwenden. Aber in manchen Situationen können sie mehr schaden als nützen. Denn wenn Sie diese Dateien beispielsweise per Mail versenden, funktionieren die Links nicht. Heute werden wir ausführlicher darüber sprechen, was zu tun ist, um ein solches Problem zu vermeiden.
Was sind Beziehungen in Excel?
Beziehungen werden in Excel sehr oft in Verbindung mit Funktionen wie verwendet VPRum Informationen aus einer anderen Arbeitsmappe zu erhalten. Es kann sich um einen speziellen Link handeln, der nicht nur die Adresse der Zelle enthält, sondern auch das Buch, in dem sich die Daten befinden. Als Ergebnis sieht ein solcher Link in etwa so aus: =VLOOKUP(A2;'[Sales 2018.xlsx]Report'!$A:$F;4;0). Oder stellen Sie die Adresse für eine einfachere Darstellung in der folgenden Form dar: ='[Verkäufe 2018.xlsx]Bericht'!$A1. Lassen Sie uns jedes der Link-Elemente dieses Typs analysieren:
- [Verkäufe 2018.xlsx]. Dieses Fragment enthält einen Link zu der Datei, aus der Sie Informationen erhalten möchten. Sie wird auch Quelle genannt.
- Fotos. Wir haben den folgenden Namen verwendet, aber das ist nicht der Name, der sein sollte. Dieser Block enthält den Namen des Blattes, in dem Sie Informationen finden müssen.
- $A:$F und $A1 – die Adresse einer Zelle oder eines Bereichs mit Daten, die in diesem Dokument enthalten sind.
Eigentlich wird das Erstellen eines Links zu einem externen Dokument als Verlinken bezeichnet. Nachdem wir die Adresse der in einer anderen Datei enthaltenen Zelle registriert haben, ändert sich der Inhalt der Registerkarte „Daten“. Es wird nämlich die Schaltfläche „Verbindungen ändern“ aktiv, mit deren Hilfe der Benutzer die bestehenden Verbindungen bearbeiten kann.
Das Wesen des Problems
Bei der Verwendung von Links entstehen in der Regel keine zusätzlichen Schwierigkeiten. Auch wenn eine Situation eintritt, in der sich die Zellen ändern, werden alle Verknüpfungen automatisch aktualisiert. Aber wenn Sie die Arbeitsmappe selbst bereits umbenennen oder an eine andere Adresse verschieben, wird Excel machtlos. Daher erzeugt es die folgende Meldung.
Dabei hat der Benutzer zwei Möglichkeiten, wie er in dieser Situation handeln soll. Er kann auf „Weiter“ klicken und dann werden die Änderungen nicht aktualisiert, oder er kann auf die Schaltfläche „Verknüpfungen ändern“ klicken, mit der er sie manuell aktualisieren kann. Nachdem wir auf diese Schaltfläche geklickt haben, erscheint ein zusätzliches Fenster, in dem es möglich ist, die Links zu ändern und anzugeben, wo sich die richtige Datei gerade befindet und wie sie heißt.
Darüber hinaus können Sie Links über die entsprechende Schaltfläche auf der Registerkarte „Daten“ bearbeiten. Der Benutzer kann auch feststellen, dass die Verbindung durch den #LINK-Fehler unterbrochen wurde, der angezeigt wird, wenn Excel nicht auf Informationen zugreifen kann, die sich an einer bestimmten Adresse befinden, weil die Adresse selbst ungültig ist.
Verknüpfung in excel aufheben
Eine der einfachsten Methoden, um die oben beschriebene Situation zu lösen, falls Sie den Speicherort der verknüpften Datei nicht selbst aktualisieren können, besteht darin, den Link selbst zu löschen. Dies ist besonders einfach, wenn das Dokument nur einen Link enthält. Dazu müssen Sie die folgende Schrittfolge ausführen:
- Öffnen Sie das Menü „Daten“.
- Wir finden den Abschnitt „Verbindungen“ und dort die Option „Verbindungen ändern“.
- Klicken Sie danach auf „Verknüpfung aufheben“.
Wenn Sie beabsichtigen, dieses Buch an eine andere Person zu schicken, wird dringend empfohlen, dies im Voraus zu tun. Schließlich werden nach dem Löschen der Verknüpfungen automatisch alle Werte, die in einem anderen Dokument enthalten sind, in die Datei geladen, in Formeln verwendet und statt der Zelladresse einfach die Informationen in den entsprechenden Zellen in Werte umgewandelt .
So heben Sie die Verknüpfung aller Bücher auf
Wenn die Anzahl der Links jedoch zu groß wird, kann das manuelle Löschen lange dauern. Um dieses Problem auf einmal zu lösen, können Sie ein spezielles Makro verwenden. Es befindet sich im VBA-Excel-Addon. Sie müssen es aktivieren und zur gleichnamigen Registerkarte gehen. Es wird einen Abschnitt „Links“ geben, in dem wir auf die Schaltfläche „Alle Links unterbrechen“ klicken müssen.
VBA-Code
Wenn es nicht möglich ist, dieses Add-On zu aktivieren, können Sie selbst ein Makro erstellen. Öffnen Sie dazu den Visual Basic-Editor, indem Sie die Tasten Alt + F11 drücken, und schreiben Sie die folgenden Zeilen in das Codeeingabefeld.
Sub UnlinkWorkBooks()
Schwache WbLinks
Schwach und so lang
Select Case MsgBox(„Alle Verweise auf andere Bücher werden aus dieser Datei entfernt, und Formeln, die sich auf andere Bücher beziehen, werden durch Werte ersetzt.“ & vbCrLf & „Sind Sie sicher, dass Sie fortfahren möchten?“, 36, „Verknüpfung aufheben?“ )
Fall 7′ Nr
Exit Sub
End Select
WbLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)
Wenn nicht IsEmpty(WbLinks) Then
Für i = 1 nach UBound(WbLinks)
ActiveWorkbook.BreakLink Name:=WbLinks(i), Typ:=xlLinkTypeExcelLinks
Weiter
sonst
MsgBox „Diese Datei enthält keine Links zu anderen Büchern.“, 64, „Links zu anderen Büchern“
End If
End Sub
So lösen Sie Bindungen nur im ausgewählten Bereich
Von Zeit zu Zeit ist die Anzahl der Links sehr groß und der Benutzer hat Angst, dass nach dem Löschen eines Links nicht alles zurückgegeben werden kann, wenn einiges überflüssig war. Aber das ist ein Problem, das leicht zu vermeiden ist. Dazu müssen Sie den Bereich auswählen, in dem Links gelöscht werden sollen, und sie dann löschen. Dazu müssen Sie die folgende Abfolge von Aktionen ausführen:
- Wählen Sie den Datensatz aus, der geändert werden muss.
- Installieren Sie das VBA-Excel-Add-On und wechseln Sie dann zur entsprechenden Registerkarte.
- Als nächstes finden wir das Menü „Links“ und klicken auf die Schaltfläche „Links in den ausgewählten Bereichen unterbrechen“.
Danach werden alle Verknüpfungen im ausgewählten Zellensatz gelöscht.
Was tun, wenn die Bindungen nicht gebrochen sind?
All das oben genannte klingt gut, aber in der Praxis gibt es immer einige Nuancen. Beispielsweise kann es eine Situation geben, in der Bindungen nicht gebrochen werden. In diesem Fall erscheint dennoch ein Dialogfenster mit dem Hinweis, dass eine automatische Aktualisierung der Verknüpfungen nicht möglich ist. Was tun in dieser Situation?
- Zunächst müssen Sie überprüfen, ob in den genannten Bereichen Informationen enthalten sind. Drücken Sie dazu die Tastenkombination Strg + F3 oder öffnen Sie den Reiter „Formeln“ – „Namensverwaltung“. Wenn der Dateiname vollständig ist, müssen Sie ihn nur bearbeiten oder ganz entfernen. Bevor Sie benannte Bereiche löschen, müssen Sie die Datei an einen anderen Speicherort kopieren, damit Sie zur ursprünglichen Version zurückkehren können, falls die falschen Schritte unternommen wurden.
- Wenn Sie das Problem nicht durch Entfernen von Namen lösen können, können Sie die bedingte Formatierung überprüfen. Zellen in einer anderen Tabelle können in bedingten Formatierungsregeln referenziert werden. Suchen Sie dazu den entsprechenden Eintrag auf der Registerkarte „Startseite“ und klicken Sie dann auf die Schaltfläche „Dateiverwaltung“.
Normalerweise gibt Ihnen Excel nicht die Möglichkeit, die Adresse anderer Arbeitsmappen in bedingter Formatierung anzugeben, aber Sie tun dies, wenn Sie mit einem Verweis auf eine andere Datei auf einen benannten Bereich verweisen. In der Regel bleibt der Link auch nach Entfernung des Links bestehen. Es ist kein Problem, einen solchen Link zu entfernen, da der Link tatsächlich nicht funktioniert. Daher wird nichts Schlimmes passieren, wenn Sie es entfernen.
Mit der Funktion „Datencheck“ können Sie auch feststellen, ob unnötige Links vorhanden sind. Links bleiben in der Regel bestehen, wenn der Datenvalidierungstyp „Liste“ verwendet wird. Aber was tun, wenn es viele Zellen gibt? Ist es wirklich notwendig, jeden von ihnen nacheinander zu überprüfen? Natürlich nicht. Schließlich wird es sehr lange dauern. Daher müssen Sie einen speziellen Code verwenden, um ihn erheblich zu speichern.
Option Explizit
'—————————————————————————————
' Autor: The_Prist (Schcherbakow Dmitry)
' Professionelle Entwicklung von Anwendungen für MS Office beliebiger Komplexität
' Durchführung von Schulungen zu MS Excel
' https://www.excel-vba.ru
' [E-Mail geschützt]
'WebMoney—R298726502453; Yandex.Geld — 41001332272872
' Zweck:
'—————————————————————————————
Unter FindErrLink()
'Wir müssen in den Link Data -Change links zur Quelldatei schauen
'und geben Sie die Schlüsselwörter hier in Kleinbuchstaben ein (Teil des Dateinamens)
'Asterisk ersetzt einfach eine beliebige Anzahl von Zeichen, sodass Sie sich keine Gedanken über den genauen Namen machen müssen
Const sToFndLink$ = „*Umsatz 2018*“
Dim rr als Bereich, rc als Bereich, rres als Bereich, s$
'definiere alle Zellen mit Datenvalidierung
On Error Resume Next
Setze rr = ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllValidation)
Wenn rr dann nichts ist
MsgBox „Es gibt keine Zellen mit Datenvalidierung auf dem aktiven Blatt“, vbInformation, „www.excel-vba.ru“
Exit Sub
End If
Bei Fehler GoTo 0
'Überprüfen Sie jede Zelle auf Links
Für jeden rc In rr
„Vorsichtshalber überspringen wir Fehler – auch das kann passieren
„aber unsere Verbindungen müssen ohne sie sein, und sie werden definitiv gefunden werden
s = «»
On Error Resume Next
s = rc.Validierung.Formel1
Bei Fehler GoTo 0
'gefunden – wir sammeln alles in einem eigenen Sortiment
Wenn LCase(s) wie sToFndLink Then
Wenn rres dann nichts ist
Setze rres = rc
sonst
Setze rres = Union(rc, rres)
End If
End If
Weiter
'wenn eine Verbindung besteht, alle Zellen mit solchen Datenprüfungen auswählen
Wenn nicht rres ist dann nichts
rres.Auswählen
' rres.Interior.Color = vbRed ' wenn Sie mit Farbe hervorheben möchten
End If
End Sub
Es ist notwendig, ein Standardmodul im Makroeditor zu erstellen und diesen Text dort einzufügen. Rufen Sie danach das Makrofenster mit der Tastenkombination Alt + F8 auf, wählen Sie dann unser Makro aus und klicken Sie auf die Schaltfläche „Ausführen“. Bei der Verwendung dieses Codes sind einige Dinge zu beachten:
- Bevor Sie nach einem nicht mehr relevanten Link suchen, müssen Sie zunächst feststellen, wie der Link aussieht, über den er erstellt wurde. Gehen Sie dazu in das Menü „Daten“ und suchen Sie dort den Punkt „Links ändern“. Danach müssen Sie sich den Dateinamen ansehen und ihn in Anführungszeichen angeben. Zum Beispiel so: Const sToFndLink$ = „*Umsatz 2018*“
- Es ist möglich, den Namen nicht vollständig zu schreiben, sondern einfach unnötige Zeichen durch ein Sternchen zu ersetzen. Und schreiben Sie in Anführungszeichen den Dateinamen in Kleinbuchstaben. In diesem Fall findet Excel alle Dateien, die eine solche Zeichenfolge am Ende enthalten.
- Dieser Code kann nur nach Links in dem Blatt suchen, das gerade aktiv ist.
- Mit diesem Makro können Sie nur die gefundenen Zellen auswählen. Sie müssen alles manuell löschen. Das ist ein Pluspunkt, denn Sie können alles noch einmal überprüfen.
- Sie können die Zellen auch in einer speziellen Farbe hervorheben. Entfernen Sie dazu den Apostroph vor dieser Zeile. rres.Interior.Color = vbRed
Nachdem Sie die oben beschriebenen Schritte ausgeführt haben, sollten normalerweise keine unnötigen Verbindungen mehr vorhanden sein. Wenn jedoch einige davon im Dokument vorhanden sind und Sie sie aus dem einen oder anderen Grund nicht entfernen können (ein typisches Beispiel ist die Sicherheit von Daten in einem Blatt), können Sie eine andere Abfolge von Aktionen verwenden. Diese Anleitung gilt nur für die Versionen 2007 und höher.
- Wir erstellen eine Sicherungskopie des Dokuments.
- Öffnen Sie dieses Dokument mit dem Archiver. Sie können jedes verwenden, das das ZIP-Format unterstützt, aber WinRar funktioniert auch, ebenso wie das in Windows integrierte.
- In dem angezeigten Archiv müssen Sie den xl-Ordner finden und dann externalLinks öffnen.
- Dieser Ordner enthält alle externen Links, die jeweils einer Datei der Form externalLink1.xml entsprechen. Alle sind nur nummeriert, und daher hat der Benutzer keine Möglichkeit zu verstehen, um welche Art von Verbindung es sich handelt. Um zu verstehen, um welche Art von Verbindung es sich handelt, müssen Sie den Ordner _rels öffnen und ihn sich dort ansehen.
- Danach entfernen wir alle oder bestimmte Links, basierend auf dem, was wir in der Datei externalLinkX.xml.rels erfahren.
- Danach öffnen wir unsere Datei mit Excel. Es werden Informationen zu einem Fehler wie „Fehler in einem Teil des Inhalts im Buch“ angezeigt. Wir geben Zustimmung. Danach erscheint ein weiterer Dialog. Wir schließen es.
Danach sollten alle Links entfernt werden.