Търсене на картини в Excel, използвайки именовани диапазони

Има много функции за търсене в Excel (като VLOOKUP, LOOKUP, INDEX/MATCH, XLOOKUP), които могат да отидат и да извлекат стойност от списък.

Но не можете да търсите изображения, използвайки тези формули.

Например. ако имам списък с имена на екипи и техните лога и искам да потърся логото въз основа на името, не мога да направя това с помощта на вградената функция Excel.

Но това не означава, че не може да се направи.

В този урок ще ви покажа как да търсите картини в Excel.

Това е просто, но ще ви накара да изглеждате като магьосник на Excel (всичко, от което се нуждаете, е този урок и ловкост на клавиатурата ви).

Щракнете тук, за да изтеглите примерния файл.

По -долу е видео с техниката за търсене на картина (в случай, че предпочитате да гледате видео, вместо да четете).

Търсене на картини в Excel

Имам списък с 20 -те отбора в английската Висша лига (подредени по азбучен ред) заедно с логото на клуба в съседната клетка.

Сега това, което искам, е да мога да избера име на екип от падащото меню и логото на този избран екип трябва да се появи.

Нещо, както е показано по -долу:

Създаването на това търсене на картина в Excel има четири части:

1. Подготовка на набора от данни.
2. Създаване на падащ списък за показване на имена на елементи (имена на клубове в този пример).
3. Създаване на именован диапазон
4. Създаване на свързана картина.

Нека сега преминем през тези стъпки подробно.

Подготовка на данните

1. Имената на всички елементи (имена на екипи) да бъдат в колона.
2. В съседната колона вмъкнете картината за елемента (логото на клуба в този пример).

Уверете се, че логата се вписват добре в клетката. Можете да преоразмерите изображенията, така че да са в клетката, или да разширите клетките.

Създаване на падащ списък

1. Изберете клетката, в която искате падащото меню (E3 в този пример).
2. Щракнете върху раздела Данни.
3. Кликнете върху опцията Проверка на данни (тя е в категорията инструменти за данни).
4. В диалоговия прозорец Проверка на данни в раздела Настройки се уверете, че е избран Списък в падащото меню Разрешаване (ако вече не е избрано).
5. В полето Източник кликнете върху иконата със стрелка, насочена нагоре. Това ще ви позволи да изберете клетките, в които имате списъка за падащото меню.
6. Изберете диапазона с имената на клубовете (B3: B22 в този пример).
7. Натиснете Enter.
8. Щракнете върху OK.

Горните стъпки ще ви дадат падащ списък в клетка E3.

Създаване на свързана картина

В тази част създаваме свързана картина, използвайки някое от съществуващите изображения/лога.

Ето стъпките за създаване на свързана картина:

1. Изберете всяка клетка с логото. Уверете се, че сте избрали клетката, а не логото/изображението.
2. Копирайте клетката (използвайте Control + C или щракнете с десния бутон и изберете копиране).
3. Щракнете с десния бутон върху клетката, където искате да получите свързаната картина (тя може да бъде всяка клетка, тъй като можем да коригираме това по-късно).
4. Отидете на опцията Специално поставяне и щракнете върху малката стрелка, насочена надясно, за да получите повече опции.
5. Кликнете върху иконата Поставяне на свързана картина.

Горните стъпки ще ви дадат свързана картина на клетката, която сте копирали. Това означава, че ако в клетката, която сте копирали, се появят промени, те също ще бъдат отразени в свързаната картина).

В горното изображение, тъй като копирах клетката C3 и поставих свързана снимка. Имайте предвид, че това не е свързано с падащото меню към момента.

Също така, когато поставите свързаната картина, тя създава изображение. Така че можете да го преместите навсякъде в работния лист.

Създаване на именован диапазон

Сега имаме всичко на място и последната стъпка е да се уверим, че свързаната картина се актуализира, когато се промени селекцията. Към момента свързаната картина е свързана само с една клетка.

Можем да го свържем с падащия списък, като използваме именован диапазон.

Ето стъпките за това:

1. Отидете в раздела Формули.
2. Кликнете върху опцията Определяне на име. Това ще отвори диалоговия прозорец „Ново име“.
3. В диалоговия прозорец Ново име направете следните записи:
   • Име: ClubLogoLookup
   • Отнася се до: = INDEX ($ C $ 3: $ C $ 22, MATCH ($ E $ 3, $ B $ 3: $ B $ 22,0))
4. Щракнете върху OK.
5. Изберете свързаното изображение, което създадохме в предишната стъпка. Ще забележите препратка към клетка в лентата с формули (например = $ C $ 3). Изтрийте тази препратка към клетката и въведете = ClubLogoLookup.

Това е!! Променете името на клуба от падащото меню и това ще промени съответно картината.

Как работи тази техника за търсене на картини?

Когато създадохме свързана картина, тя се отнасяше до оригиналната клетка, от която е копирана. Променихме тази справка с именования диапазон.

Този именован диапазон зависи от падащото меню и когато променим селекцията в падащото меню, той връща препратката на клетката до името на избрания екип. Например, ако избера Арсенал, той връща C3, а когато избирам Челси, връща C6.

Тъй като сме присвоили именования диапазон на свързаната картина (чрез промяна на препратката към = ClubLogoLookup), сега той се позовава на новите препратки към клетки и следователно връща изображение на тази клетка.

За да работи този трик, дефинираното име трябва да връща само препратка към клетка. Това се постига чрез използване на комбинацията от функции INDEX и MATCH.

Ето формулата:

= ИНДЕКС ($ C $ 3: $ C $ 22, MATCH ($ E $ 3, $ B $ 3: $ B $ 22,0)).

Частта MATCH във формулата връща позицията на името на клуба в падащото меню. Например, ако това е Арсенал, формулата MATCH ще върне 1, ако Челси е след това 4. Функцията INDEX локализира справка за клетка който има логото (въз основа на позицията, върната от MATCH).

Опитайте сами … Изтеглете Примерния файл от тук