Bildsuche in Excel mit benannten Bereichen

Es gibt viele Nachschlagefunktionen in Excel (wie SVERWEIS, VERWEIS, INDEX/MATCH, XVERWEIS), die einen Wert aus einer Liste abrufen können.

Mit diesen Formeln können Sie jedoch keine Bilder nachschlagen.

Beispielsweise. Wenn ich eine Liste mit Teamnamen und deren Logos habe und das Logo basierend auf dem Namen nachschlagen möchte, kann ich dies nicht mit der integrierten Excel-Funktion tun.

Aber das heißt nicht, dass es nicht möglich ist.

In diesem Tutorial zeige ich Ihnen, wie Sie eine Bildsuche in Excel durchführen.

Es ist einfach und lässt Sie wie ein Excel-Magier aussehen (alles, was Sie brauchen, ist dieses Tutorial und Ihre Tastatur).

Klicken Sie hier, um die Beispieldatei herunterzuladen.

Unten sehen Sie ein Video der Bildsuchtechnik (falls Sie lieber ein Video ansehen als lesen).

Bildsuche in Excel

Ich habe eine Liste der 20 Teams der englischen Premier League (in alphabetischer Reihenfolge) zusammen mit dem Vereinslogo in der angrenzenden Zelle.

Jetzt möchte ich einen Teamnamen aus der Dropdown-Liste auswählen und das Logo dieses ausgewählten Teams sollte erscheinen.

Etwas wie unten gezeigt:

Es gibt vier Teile, um diese Bildsuche in Excel zu erstellen:

1. Bereiten Sie den Datensatz vor.
2. Erstellen einer Dropdown-Liste zum Anzeigen von Elementnamen (in diesem Beispiel Clubnamen).
3. Erstellen eines benannten Bereichs
4. Erstellen eines verknüpften Bildes.

Lassen Sie uns diese Schritte jetzt im Detail durchgehen.

Daten vorbereiten

1. Lassen Sie die Namen aller Elemente (Teamnamen) in einer Spalte stehen.
2. Fügen Sie in der angrenzenden Spalte das Bild für den Artikel ein (in diesem Beispiel das Vereinslogo).

Stellen Sie sicher, dass die Logos gut in die Zelle passen. Sie können die Größe der Bilder so ändern, dass sie sich innerhalb der Zelle befinden, oder Sie können die Zellen erweitern.

Erstellen der Dropdown-Liste

1. Wählen Sie die Zelle aus, in der das Dropdown-Menü angezeigt werden soll (E3 in diesem Beispiel).
2. Klicken Sie auf die Registerkarte Daten.
3. Klicken Sie auf die Option Datenvalidierung (in der Kategorie Datentools).
4. Stellen Sie im Dialogfeld Datenvalidierung auf der Registerkarte Einstellungen sicher, dass Liste in der Dropdown-Liste Zulassen ausgewählt ist (falls nicht bereits ausgewählt).
5. Klicken Sie im Feld Quelle auf das nach oben zeigende Pfeilsymbol. Auf diese Weise können Sie die Zellen auswählen, in denen Sie die Liste für das Dropdown-Menü haben.
6. Wählen Sie den Bereich mit den Clubnamen (B3:B22 in diesem Beispiel).
7. Drücke Enter.
8. OK klicken.

Mit den obigen Schritten erhalten Sie eine Dropdown-Liste in Zelle E3.

Erstellen eines verknüpften Bildes

In diesem Teil erstellen wir ein verlinktes Bild mit einem der vorhandenen Bilder/Logos.

So erstellen Sie ein verknüpftes Bild:

1. Wählen Sie eine beliebige Zelle mit dem Logo aus. Stellen Sie sicher, dass Sie die Zelle ausgewählt haben, nicht das Logo/Bild.
2. Kopieren Sie die Zelle (verwenden Sie Strg + C oder klicken Sie mit der rechten Maustaste und wählen Sie Kopieren).
3. Klicken Sie mit der rechten Maustaste auf die Zelle, in der Sie das verknüpfte Bild erhalten möchten (es kann eine beliebige Zelle sein, da wir dies später anpassen können).
4. Gehen Sie zur Option Inhalte einfügen und klicken Sie auf den kleinen nach rechts zeigenden Pfeil, um weitere Optionen zu erhalten.
5. Klicken Sie auf das Symbol Verknüpftes Bild einfügen.

Mit den obigen Schritten erhalten Sie ein verknüpftes Bild der kopierten Zelle. Dies bedeutet, dass Änderungen in der kopierten Zelle auch im verknüpften Bild widergespiegelt werden.

Im obigen Bild habe ich die Zelle C3 kopiert und ein verknüpftes Bild eingefügt. Beachten Sie, dass dies derzeit nicht mit dem Dropdown-Menü verbunden ist.

Wenn Sie das verknüpfte Bild einfügen, wird außerdem ein Bild erstellt. Sie können es also an eine beliebige Stelle im Arbeitsblatt verschieben.

Erstellen eines benannten Bereichs

Jetzt haben wir alles an seinem Platz und der letzte Schritt besteht darin, sicherzustellen, dass das verknüpfte Bild aktualisiert wird, wenn die Auswahl geändert wird. Ab sofort ist das verknüpfte Bild nur mit einer Zelle verknüpft.

Wir können es mit der Dropdown-Auswahl verbinden, indem wir einen benannten Bereich verwenden.

Hier sind die Schritte, um dies zu tun:

1. Wechseln Sie zur Registerkarte Formeln.
2. Klicken Sie auf die Option Name definieren. Dadurch wird das Dialogfeld „Neuer Name“ geöffnet.
3. Nehmen Sie im Dialogfeld Neuer Name die folgenden Einträge vor:
   • Name: ClubLogoLookup
   • Bezieht sich auf: =INDEX($C$3:$C$22,MATCH($E$3,$B$3:$B$22,0))
4. OK klicken.
5. Wählen Sie das verknüpfte Bild aus, das wir im vorherigen Schritt erstellt haben. Sie werden einen Zellbezug in der Bearbeitungsleiste bemerken (zum Beispiel = $ C $ 3). Diesen Zellbezug löschen und eingeben =ClubLogoLookup.

Das ist es!! Ändern Sie den Clubnamen aus der Dropdown-Liste und das Bild ändert sich entsprechend.

Wie funktioniert diese Bildsuchtechnik?

Als wir ein verknüpftes Bild erstellten, bezog es sich auf die ursprüngliche Zelle, aus der es kopiert wurde. Wir haben diese Referenz mit dem benannten Bereich geändert.

Dieser benannte Bereich ist von der Dropdown-Liste abhängig und wenn wir die Auswahl in der Dropdown-Liste ändern, wird die Referenz der Zelle neben dem Namen des ausgewählten Teams zurückgegeben. Wenn ich beispielsweise Arsenal auswähle, wird C3 zurückgegeben, und wenn ich Chelsea auswähle, wird C6 zurückgegeben.

Da wir dem verlinkten Bild den benannten Bereich zugewiesen haben (indem wir die Referenz auf =ClubLogoLookup geändert haben), verweist es jetzt auf die neuen Zellreferenzen und gibt daher ein Bild dieser Zelle zurück.

Damit dieser Trick funktioniert, sollte der definierte Name nur einen Zellbezug zurückgeben. Dies wird durch die Kombination von INDEX- und MATCH-Funktionen erreicht.

Hier ist die Formel:

=INDEX($C$3:$C$22,MATCH($E$3,$B$3:$B$22,0)).

Der MATCH-Teil in der Formel gibt die Position des Clubnamens im Dropdown-Menü zurück. Wenn es zum Beispiel Arsenal ist, würde die MATCH-Formel 1 zurückgeben, wenn es Chelsea dann 4 ist. Die INDEX-Funktion lokalisiert die Zellbezug das das Logo hat (basierend auf der von MATCH zurückgegebenen Position).

Probieren Sie es selbst aus… Laden Sie die Beispieldatei hier herunter