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