У Excel є багато функцій пошуку (таких як VLOOKUP, LOOKUP, INDEX/MATCH, XLOOKUP), які можуть переходити та отримувати значення зі списку.
Але ви не можете шукати зображення за допомогою цих формул.
Наприклад. якщо у мене є список назв команд та їх логотипів, і я хочу шукати логотип на основі назви, я не можу цього зробити за допомогою вбудованої функції Excel.
Але це не означає, що це неможливо зробити.
У цьому уроці я покажу вам, як шукати зображення в Excel.
Це просто, але зроблять вас схожими на чарівника Excel (все, що вам потрібно, це цей підручник та хитрість практичної роботи на клавіатурі).
Натисніть тут, щоб завантажити файл прикладу.
Нижче наведено відео техніки пошуку зображення (у разі, якщо ви віддаєте перевагу перегляду відео, а не читання).
Пошук зображень у Excel
У мене є список 20 команд англійської Прем’єр -ліги (в алфавітному порядку) разом із логотипом клубу в сусідній клітці.
Тепер я хочу мати можливість вибрати назву команди зі спадного меню, і повинен з'явитися логотип цієї вибраної команди.
Щось, як показано нижче:
Створення цього пошуку зображення в Excel складається з чотирьох частин:
- Підготовка набору даних.
- Створення розкривного списку для відображення назв предметів (назви клубів у цьому прикладі).
- Створення іменованого діапазону
- Створення зв’язаного зображення.
Давайте зараз детально пройдемо ці кроки.
Підготовка даних
- Назвіть усі назви (назви команд) у стовпці.
- У сусідній колоні вставте зображення для предмета (логотип клубу в цьому прикладі).
Переконайтеся, що логотипи добре вписуються в клітинку. Ви можете змінити розмір зображень так, щоб вони були всередині комірки, або розгорнути клітинки.
Створення розкривного списку
- Виберіть осередок, у якому ви хочете розкрити список (E3 у цьому прикладі).
- Перейдіть на вкладку Дані.
- Натисніть на опцію Перевірка даних (вона знаходиться в категорії інструменти даних).
- У діалоговому вікні «Перевірка даних» на вкладці «Налаштування» переконайтеся, що у спадному меню «Дозволити» вибрано «Список» (якщо ще не вибрано).
- У полі Джерело натисніть на значок стрілки вгору. Це дозволить вам вибрати клітинки, у яких у вас є список, для випадаючого списку.
- Виберіть діапазон із назвами клубів (B3: B22 у цьому прикладі).
- Натисніть Enter.
- Натисніть OK.
Наведені вище кроки дадуть вам розкривний список у комірці E3.
Створення зв’язаного зображення
У цій частині ми створюємо пов’язане зображення, використовуючи будь -яке з існуючих зображень/логотипів.
Нижче описано, як створити зв’язане зображення:
- Виберіть будь -яку клітинку з логотипом. Переконайтеся, що вибрано клітинку, а не логотип/зображення.
- Скопіюйте клітинку (використовуйте клавіші Control + C або клацніть правою кнопкою миші та виберіть копіювати).
- Клацніть правою кнопкою миші по осередку, де потрібно отримати пов’язане зображення (це може бути будь-яка клітинка, оскільки це можна змінити пізніше).
- Перейдіть до опції "Вставити спеціально" та натисніть на маленьку стрілку вправо, щоб отримати додаткові параметри.
- Натисніть на значок Вставити пов'язане зображення.
Наведені вище кроки дадуть вам зв’язане зображення комірки, яку ви скопіювали. Це означає, що якщо в комірці, яку ви скопіювали, відбудуться будь -які зміни, це також відобразиться у зв’язаному зображенні).
На зображенні вище, оскільки я скопіював клітинку С3 і вставив пов’язане зображення. Зауважте, що наразі це не пов’язано зі спадним меню.
Крім того, коли ви вставляєте пов’язане зображення, воно створює зображення. Таким чином, ви можете перемістити його будь -де на аркуші.
Створення іменованого діапазону
Тепер у нас все на місці, і останній крок - переконатися, що пов’язане зображення оновлюється при зміні виділення. Наразі пов’язане зображення пов’язане лише з однією клітиною.
Ми можемо підключити його до випадаючого вибору за допомогою іменованого діапазону.
Нижче наведено кроки для цього.
- Перейдіть на вкладку Формули.
- Натисніть на опцію Визначити назву. Відкриється діалогове вікно "Нова назва".
- У діалоговому вікні Нове ім'я внесіть такі записи:
- Назва: ClubLogoLookup
- Зсилається: = ІНДЕКС ($ C $ 3: $ C $ 22, МАТЧ ($ E $ 3, $ B $ 3: $ B $ 22,0))
- Натисніть OK.
- Виберіть пов'язане зображення, яке ми створили на попередньому кроці. Ви побачите посилання на клітинку у рядку формул (наприклад = $ C $ 3). Видаліть цю посилання на клітинку та введіть її = ClubLogoLookup.
Це воно!! Змініть назву клубу зі спадного меню, і це відповідно змінить зображення.
Як працює ця техніка пошуку зображень?
Коли ми створювали зв’язане зображення, воно посилалося на вихідну клітинку, з якої воно було скопійоване. Ми змінили це посилання з іменованим діапазоном.
Цей іменований діапазон залежить від випадаючого списку, і коли ми змінюємо виділення у випадаючому списку, він повертає посилання на клітинку поруч із назвою вибраної команди. Наприклад, якщо я вибираю Арсенал, він повертає C3, а коли я вибираю Челсі, повертає C6.
Оскільки ми присвоїли іменований діапазон пов'язаному зображенню (змінивши посилання на = ClubLogoLookup), тепер він посилається на нові посилання на клітинки, а отже, повертає зображення цієї комірки.
Щоб цей трюк спрацював, визначене ім'я повинно повертати лише посилання на клітинку. Це досягається за допомогою комбінації функцій INDEX та MATCH.
Ось формула:
= ІНДЕКС ($ C $ 3: $ C $ 22, МАТЧ ($ E $ 3, $ B $ 3: $ B $ 22,0)).
Частина MATCH у формулі повертає позицію назви клубу у випадаючому списку. Наприклад, якщо це "Арсенал", формула MATCH поверне 1, якщо його "Челсі", то 4. Функція INDEX знаходить посилання на клітинку з логотипом (на основі позиції, повернутої MATCH).
Спробуйте самі… Завантажте файл прикладу тут
