Istnieje wiele funkcji wyszukiwania w programie Excel (takich jak WYSZUKAJ.PIONOWO, WYSZUKAJ, INDEKS/MATCH, WYSZUKAJ.X), które mogą pobierać wartości z listy.
Ale nie możesz wyszukiwać obrazów za pomocą tych formuł.
Na przykład. jeśli mam listę nazw drużyn i ich logo, a chcę wyszukać logo na podstawie nazwy, nie mogę tego zrobić za pomocą wbudowanej funkcji Excela.
Ale to nie znaczy, że nie da się tego zrobić.
W tym samouczku pokażę, jak wykonać wyszukiwanie obrazu w programie Excel.
To proste, ale sprawi, że będziesz wyglądać jak magik Excela (wystarczy tylko ten samouczek i sztuczka na klawiaturze).
Kliknij tutaj, aby pobrać przykładowy plik.
Poniżej znajduje się film przedstawiający technikę wyszukiwania obrazów (na wypadek, gdybyś preferował oglądanie filmu zamiast czytania).
Wyszukiwanie obrazów w programie Excel
Mam listę 20 drużyn z angielskiej Premier League (ułożone w kolejności alfabetycznej) wraz z logo klubu w sąsiedniej komórce.
Teraz chcę móc wybrać nazwę zespołu z rozwijanego menu, a logo wybranego zespołu powinno się pojawić.
Coś, jak pokazano poniżej:
Tworzenie tego wyszukiwania obrazu w programie Excel składa się z czterech części:
- Przygotowanie zestawu danych.
- Tworzenie listy rozwijanej, aby pokazać nazwy przedmiotów (w tym przykładzie nazwy klubów).
- Tworzenie nazwanego zakresu
- Tworzenie połączonego obrazu.
Przejdźmy teraz szczegółowo przez te kroki.
Przygotowanie danych
- Mieć nazwy wszystkich przedmiotów (nazwy drużyn) w kolumnie.
- W sąsiedniej kolumnie wstaw zdjęcie przedmiotu (w tym przykładzie logo klubu).
Upewnij się, że logo ładnie pasuje do komórki. Możesz zmienić rozmiar obrazów, aby znajdowały się w komórce, lub możesz rozwinąć komórki.
Tworzenie listy rozwijanej
- Wybierz komórkę, w której chcesz wyświetlić listę rozwijaną (w tym przykładzie E3).
- Kliknij kartę Dane.
- Kliknij opcję Data Validation (znajduje się w kategorii narzędzi danych).
- W oknie dialogowym Sprawdzanie poprawności danych na karcie Ustawienia upewnij się, że na liście rozwijanej Zezwalaj jest wybrana opcja Lista (jeśli nie została jeszcze wybrana).
- W polu Źródło kliknij ikonę strzałki skierowanej w górę. To pozwoli ci wybrać komórki, w których masz listę rozwijaną.
- Wybierz zakres, który zawiera nazwy klubów (w tym przykładzie B3:B22).
- Wciśnij Enter.
- Kliknij OK.
Powyższe kroki dadzą ci listę rozwijaną w komórce E3.
Tworzenie połączonego obrazu
W tej części tworzymy połączony obraz, używając dowolnego z istniejących obrazów/logo.
Oto kroki, aby utworzyć połączony obraz:
- Wybierz dowolną komórkę, która ma logo. Upewnij się, że wybrałeś komórkę, a nie logo/obraz.
- Skopiuj komórkę (użyj Control + C lub kliknij prawym przyciskiem myszy i wybierz kopiuj).
- Kliknij prawym przyciskiem myszy komórkę, w której chcesz uzyskać połączony obraz (może to być dowolna komórka, ponieważ możemy to później dostosować).
- Przejdź do opcji Wklej specjalnie i kliknij małą strzałkę skierowaną w prawo, aby uzyskać więcej opcji.
- Kliknij ikonę Wklej połączony obraz.
Powyższe kroki dadzą Ci połączony obraz skopiowanej komórki. Oznacza to, że jeśli w skopiowanej komórce zajdą jakiekolwiek zmiany, zostaną one również odzwierciedlone w połączonym obrazie).
Na powyższym obrazku, ponieważ skopiowałem komórkę C3 i wkleiłem połączony obraz. Zauważ, że na razie nie jest to połączone z listą rozwijaną.
Ponadto po wklejeniu połączonego obrazu tworzy obraz. Możesz więc przenieść go w dowolne miejsce w arkuszu.
Tworzenie nazwanego zakresu
Teraz mamy wszystko na swoim miejscu, a ostatnim krokiem jest upewnienie się, że połączony obraz jest aktualizowany po zmianie zaznaczenia. Obecnie połączony obraz jest połączony tylko z jedną komórką.
Możemy połączyć go z wyborem rozwijanym za pomocą nazwanego zakresu.
Oto kroki, aby to zrobić:
- Przejdź do zakładki Formuły.
- Kliknij opcję Zdefiniuj nazwę. Spowoduje to otwarcie okna dialogowego „Nowa nazwa”.
- W oknie dialogowym Nowa nazwa wprowadź następujące wpisy:
- Nazwa: ClubLogoLookup
- Odnosi się do: = INDEKS ($ C $ 3: $ C $ 22, DOPASUJ (3 $ E $, $ B $ 3: $ B $ 22,0))
- Kliknij OK.
- Wybierz połączony obraz, który stworzyliśmy w poprzednim kroku. Zauważysz odwołanie do komórki na pasku formuły (na przykład = $ C $ 3). Usuń to odwołanie do komórki i wpisz =KlubLogoWyszukaj.
Otóż to!! Zmień nazwę klubu z rozwijanego menu, a obraz zostanie odpowiednio zmieniony.
Jak działa ta technika wyszukiwania obrazów?
Kiedy tworzyliśmy połączony obraz, odnosił się do oryginalnej komórki, z której został skopiowany. Zmieniliśmy to odniesienie na nazwany zakres.
Ten nazwany zakres jest zależny od listy rozwijanej i kiedy zmieniamy zaznaczenie na liście rozwijanej, zwraca on odwołanie do komórki obok nazwy wybranego zespołu. Na przykład, jeśli wybiorę Arsenal, zwraca C3, a kiedy wybieram Chelsea, zwraca C6.
Ponieważ przypisaliśmy nazwany zakres do połączonego obrazu (zmieniając odwołanie na =ClubLogoLookup), teraz odnosi się on do nowych odwołań do komórki, a zatem zwraca obraz tej komórki.
Aby ta sztuczka zadziałała, zdefiniowana nazwa powinna zwracać tylko odwołanie do komórki. Osiąga się to dzięki połączeniu funkcji INDEX i PODAJ.POZYCJĘ.
Oto wzór:
=INDEKS($C$3:$C$22;DOPASUJ($E$3;$B$3:$B$22,0)).
Część MATCH w formule zwraca pozycję nazwy klubu z listy rozwijanej. Na przykład, jeśli jest to Arsenal, formuła PODAJ.POZYCJĘ zwróci 1, jeśli jest to Chelsea, to 4. Funkcja INDEX lokalizuje odwołanie do komórki który ma logo (na podstawie pozycji zwróconej przez MATCH).
Spróbuj sam… Pobierz przykładowy plik stąd
