Wie kann man in Excel nachschlagen, um einen aktiven Hyperlink zurückzugeben?
Im täglichen Umgang mit Excel ist es üblich, die VLOOKUP-Funktion zu verwenden, um Werte zu finden und zurückzugeben, die mit bestimmten Einträgen in Ihren Daten verknüpft sind. Ein häufiges Problem entsteht jedoch, wenn Ihre Suche Hyperlinks zurückgibt: Anstatt klickbarer, aktiver Links erhalten Sie nur einfache Text-URLs. Wenn beispielsweise Ihre Quelldaten einen Weblink mit einem eingebetteten Hyperlink enthalten, wird das Ergebnis nach einer typischen VLOOKUP nur als unformatierter Text angezeigt, wie unten dargestellt.
In solchen Fällen stellt die Sicherstellung, dass die Suche einen aktiven Hyperlink – auf den Sie klicken können und der sich im Browser öffnet – zurückgibt, eine Verbesserung der Benutzerfreundlichkeit dar, spart Zeit und ist wichtig für Datensätze, die Webadressen, Dateipfade oder andere klickbare Ressourcen umfassen.
Dieses Tutorial stellt mehrere praktische Lösungen zur Rückgabe aktiver Hyperlinks durch Suchvorgänge vor und analysiert deren Szenarien, anwendbare Datentypen und potenzielle Einschränkungen. Sie lernen auch wichtige Vorsichtsmaßnahmen, Tipps zur Fehlerbehebung und Empfehlungen zur Auswahl der besten Methode kennen, um Ihren Arbeitsblattbedarf gerecht zu werden.
- Nachschlagen, um einen aktiven Hyperlink mit Formel zurückzugeben
- VBA-Code – Rückgabe und Einfügen eines aktiven Hyperlinks über Nachschlagen (fortgeschrittene Szenarien)
Nachschlagen, um einen aktiven Hyperlink mit Formel zurückzugeben
Um nachzuschlagen und einen Wert als aktiven Hyperlink zurückzugeben, können Sie die HYPERLINK- und VLOOKUP-Funktionen kombinieren. Dieser Ansatz ist einfach und geeignet für Quelldaten, in denen Hyperlinks sauber als Text-URL-Adressen gespeichert sind (wie „https://www.beispiel.com“ oder Netzwerkdateipfade). Dadurch wird der zurückgegebene Wert in Ihrem Arbeitsblatt klickbar.
Angenommen, Sie haben eine Tabelle mit zwei Spalten: eine für den Suchwert (z. B. einen Namen) und eine für eine URL als reiner Text oder Hyperlink. Um den entsprechenden aktiven Hyperlink basierend auf einem vom Benutzer eingegebenen Wert abzurufen, folgen Sie diesen Schritten:
1. Geben Sie die folgende Formel in eine leere Zelle ein, in der Sie das Ergebnis anzeigen möchten:
=HYPERLINK(VLOOKUP(D2, $A$1:$B$8,2, FALSE))2. Drücken Sie die Eingabetaste, um zu bestätigen. Die Zelle zeigt nun den Hyperlink als aktiven, klickbaren Link an, wie unten gezeigt:
Parameter und Verwendungshinweise:
- D2: Die Zelle, die den Wert enthält, nach dem Sie suchen möchten.
- $A$1:$B$8: Der Datenbereich, in dem die erste Spalte die Suchwerte und die zweite die Hyperlinks enthält. Verwenden Sie absolute Referenzen, wenn Sie planen, die Formel zu kopieren.
- 2: Gibt an, dass sich der Hyperlink in der zweiten Spalte Ihres Bereichs befindet.
Tipps:
- Wenn Ihr Suchwert nicht gefunden wird, gibt die Formel einen Fehler (#NV) zurück. Überprüfen Sie, ob Ihre Suchwerte eine exakte Übereinstimmung im Tabellenbereich haben.
- Wenn Sie möchten, dass der angezeigte Text sich vom eigentlichen Hyperlink unterscheidet (z. B. einen Namen statt der URL anzeigen), können Sie dem HYPERLINK:
=HYPERLINK(VLOOKUP(D2,$A$1:$B$8,2,FALSE),D2)
Dies zeigt den Wert von D2 als Linktext an. - Dieser Ansatz funktioniert nur, wenn Hyperlinks als Standard-URL oder Dateipfad gespeichert sind. Er stellt keine in Excel eingefügten Hyperlinks wieder her, bei denen sich der Anzeigetext und die Hyperlinkadresse unterscheiden, oder „freundliche“ Anzeigenamen ohne eine rohe URL in der Zelle.
Häufige Probleme und Fehlerbehebung:
- Wenn das Ergebnis nicht klickbar ist, stellen Sie sicher, dass Ihre Daten eine vollständige gültige Web-URL enthalten (einschließlich "http://" oder "https://").
- Wenn Sie falsche oder fehlende Ergebnisse erhalten, überprüfen Sie Ihren Suchbereich und stellen Sie sicher, dass der Spaltenindex mit der Spalte übereinstimmt, die die Hyperlinks enthält.
- Für lokale Dateien stellen Sie sicher, dass Ihr Hyperlink-Pfad das richtige Format verwendet (z. B. "C:\Ordner\Datei.xlsx").
Vorteile: Einfach einzurichten, Formel kann gezogen werden, um auf mehrere Zeilen angewendet zu werden, am besten für Tabellen, in denen Hyperlinks als reine Text-URIs gespeichert sind.
Einschränkungen: Unterstützt nicht das Abrufen von Anzeigetext und Hyperlinkadresse getrennt, falls sie unterschiedlich sind, noch erkennt es manuell eingefügte Hyperlinks, bei denen nur der Anzeigetext in der Zelle sichtbar ist.
VBA-Code – Rückgabe und Einfügen eines aktiven Hyperlinks über Nachschlagen (fortgeschrittene Szenarien)
Wenn Sie einen bestimmten Wert in einem Bereich nachschlagen und seinen ursprünglichen aktiven Hyperlink zurückgeben müssen – einschließlich des angezeigten Textes und des klickbaren Links selbst – anstatt nur die reine URL, bietet VBA eine zuverlässige Lösung. Dieser Code sucht den Zielwert in Ihrem ausgewählten Bereich, und wenn eine Übereinstimmung gefunden wird, kopiert er den genauen Hyperlink (Text + Adresse) in die von Ihnen angegebene Zelle. Dies ist besonders nützlich, wenn sich der angezeigte Text und die Hyperlinkadresse unterscheiden oder wenn formelbasierte Methoden den tatsächlichen Link nicht erfassen können.
Diese Methode ist besonders nützlich, wenn Ihre Daten „freundliche“ Anzeigenamen mit zugrunde liegenden Hyperlinks, Hyperlinks zu Dateien oder Ordnern oder nicht standardmäßige Hyperlinkformate umfassen. Mit VBA können Sie sowohl den sichtbaren Linktext als auch die zugrunde liegende Hyperlinkadresse kopieren oder einen Hyperlink an einem neuen Ort mithilfe eines Suchergebnisses neu einfügen.
Vorsichtsmaßnahmen: Stellen Sie sicher, dass Makros in Ihrer Excel-Umgebung aktiviert sind. Sichern Sie immer Ihr Arbeitsblatt, bevor Sie VBA-Skripte ausführen, insbesondere wenn Sie mit wichtigen Daten arbeiten.
Vorteile: Behandelt komplexe Fälle – wie zelleneingefügte Hyperlinks und die Trennung von Anzeigetext und Hyperlinkadresse. Ermöglicht es Ihnen, Stapel von Hyperlinks zu verarbeiten oder Ergebnisse anzupassen.
Einschränkungen: Erfordert grundlegende VBA-Kenntnisse und wird nicht in allen eingeschränkten oder webbasierten Excel-Umgebungen unterstützt.
1. Öffnen Sie den VBA-Editor, indem Sie auf Entwickler > Visual Basic klicken. Klicken Sie im neu geöffneten Fenster auf Einfügen > Modul, und fügen Sie den folgenden Code in das Modul ein:
Sub LookupAndInsertHyperlink()
Dim LookupValue As String
Dim LookupRange As Range
Dim ResultCell As Range
Dim cell As Range
Dim hyperlinkFound As Boolean
Dim linkAddress As String
Dim linkText As String
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set LookupRange = Application.InputBox("Select the lookup range (must include display text/cell and hyperlink)", xTitleId, Selection.Address, Type:=8)
Set ResultCell = Application.InputBox("Select the cell to output the hyperlink", xTitleId, "", Type:=8)
LookupValue = Application.InputBox("Enter the value to lookup", xTitleId, "", Type:=2)
hyperlinkFound = False
For Each cell In LookupRange
If cell.Value = LookupValue Then
If cell.Hyperlinks.Count > 0 Then
linkAddress = cell.Hyperlinks(1).Address
linkText = cell.Value
ResultCell.Hyperlinks.Add Anchor:=ResultCell, Address:=linkAddress, TextToDisplay:=linkText
hyperlinkFound = True
Exit For
End If
End If
Next
If Not hyperlinkFound Then
ResultCell.Value = "No matching hyperlink found"
End If
End Sub2. Um das Skript auszuführen, drücken Sie mit Ihrem geöffneten Arbeitsblatt Alt + F8, wählen Sie LookupAndInsertHyperlink aus, und klicken Sie auf Ausführen.
3. In den erscheinenden Dialogfeldern:
- Wählen Sie Ihren Suchdatenbereich aus (einschließlich der Werte und ihrer Hyperlinks).
- Wählen Sie die Zelle für den Ausgabehyperlink aus.
- Geben Sie den Suchwert ein, nach dem Sie suchen möchten. Das Makro findet den passenden Wert, extrahiert seinen Hyperlink (auch wenn sich der Anzeigetext von der zugrunde liegenden Verknüpfung unterscheidet) und fügt ihn als aktiven Hyperlink an der von Ihnen gewählten Stelle ein.
Praktische Tipps und Fehlerhinweise:
- Wenn der Wert nicht gefunden wird oder kein Hyperlink in der Zelle vorhanden ist, wird im Ziel „Kein übereinstimmender Hyperlink gefunden“ angezeigt.
- Wenn Sie mehrere Suchvorgänge gleichzeitig verarbeiten möchten, sollten Sie den VBA-Code erweitern oder das Skript bei Bedarf mehrmals ausführen.
- Dieses Skript ist auch dann effektiv, wenn der Hyperlink nicht einfach eine URL-Zeichenfolge in der Zelle ist, und kopiert sowohl Adresse als auch Anzeigetext.
Fehlerbehebungsvorschläge:
- Überprüfen Sie, ob Ihr Eingabebereich die Spalte mit den tatsächlichen Hyperlinks umfasst.
- Wenn das VBA-Makro nicht ausgeführt wird, überprüfen Sie, ob Makros in Ihren Excel-Einstellungen aktiviert sind.
- Wenn „Kein übereinstimmender Hyperlink gefunden“ erscheint, überprüfen Sie, ob Ihr Suchwert korrekt ist und entsprechende Hyperlinks in dieser Zeile existieren.
- Sichern Sie immer Ihr Arbeitsblatt, bevor Sie Makros ausführen, falls Sie Änderungen rückgängig machen müssen.
Zusammenfassung:
- Verwenden Sie die Formelmethode für Standard-, textbasierte Hyperlinks und schnelle Nachschlagungen.
- Verwenden Sie die VBA-Methode für fortgeschrittenere Anforderungen – wie das Wiederherstellen manuell eingefügter Hyperlinks, das Abrufen von Anzeigetext und Linkadresse oder die dynamische Anwendung von Ergebnissen über Bereiche hinweg.
Die besten Produktivitätstools für das Büro
Stärken Sie Ihre Excel-Fähigkeiten mit Kutools für Excel und genießen Sie Effizienz wie nie zuvor. Kutools für Excel bietet mehr als300 erweiterte Funktionen, um die Produktivität zu steigern und Zeit zu sparen. Klicken Sie hier, um die Funktion zu erhalten, die Sie am meisten benötigen...
Office Tab bringt die Tab-Oberfläche in Office und macht Ihre Arbeit wesentlich einfacher
- Aktivieren Sie die Tabulator-Bearbeitung und das Lesen in Word, Excel, PowerPoint, Publisher, Access, Visio und Project.
- Öffnen und erstellen Sie mehrere Dokumente in neuen Tabs innerhalb desselben Fensters, statt in neuen Einzelfenstern.
- Steigert Ihre Produktivität um50 % und reduziert hunderte Mausklicks täglich!
Alle Kutools-Add-Ins. Ein Installationspaket
Das Kutools for Office-Paket bündelt Add-Ins für Excel, Word, Outlook & PowerPoint sowie Office Tab Pro und ist ideal für Teams, die mit mehreren Office-Anwendungen arbeiten.
- All-in-One-Paket — Add-Ins für Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Ein Installationspaket, eine Lizenz — in wenigen Minuten einsatzbereit (MSI-kompatibel)
- Besser gemeinsam — optimierte Produktivität in allen Office-Anwendungen
- 30 Tage kostenlos testen — keine Registrierung, keine Kreditkarte erforderlich
- Bestes Preis-Leistungs-Verhältnis — günstiger als Einzelkauf der Add-Ins