Выпадающий список с показом изображений

Необходимое предисловие

Если у вас Excel 2010 или древнее, то можно почитать вот эту статью, где было описано как реализовать такой выпадающий список с помощью функции СМЕЩ (OFFSET).

Если же у вас Excel 2013, 2016, 2019 или новее, то описанная техника в этих версиях, к сожалению, уже не работает и нужен другой подход, разобранный в этой статье далее.

Шаг 1. Готовим каталог изображений

Для начала нужно создать таблицу-справочник, где будет храниться информация о наших объектах (товарах, людях и т.д.) и их изображения. Выглядеть это должно примерно так:

Каталог изображений

При создании имеет смысл держать в голове несколько моментов:

  • Если в будущем планируется добавление новых строк (товаров), то лучше сразу оформить такой каталог в виде "умной" динамической таблицы с помощью сочетания клавиш Ctrl+T или кнопки Форматировать как таблицу на вкладке Главная (Home - Format as Table). Имя созданной "умной" таблицы можно задать на вкладке Конструктор (Design) - я назвал её в этом примере Каталог.
  • Картинки должны полностью вписываться в соответствующие ячейки и не выходить за их границы (иначе они будут обрезаны). Для упорядочивания изображений после их выделения удобно использовать команды выравнивания и распределения на вкладке Формат (Format). Там же можно задать размеры (высоту и ширину) изображений, чтобы быстро вписать их в ячейки:

    Команды выравнивания

  • В отдельном столбце рядом с изображениями (в нашем примере он называется ИД) нужно придумать и ввести имена диапазонов, по которым мы впоследствии будем адресоваться к картинкам. Эти имена должны быть уникальными, не содержать пробелов и не быть похожими на адреса ячеек (A1, B3 и т.п.)

Шаг 2. Создаем именованные диапазоны для ячеек с картинками

Чтобы массово и быстро присвоить ячейкам с изображениями в столбце Картинка придуманные имена из столбца ИД используем следующий трюк:

  1. Выделяем столбцы ИД и Картинка (диапазон C2:D7 в нашем примере)
  2. Открываем вкладку Формулы (Formulas)
  3. Выбираем команду Создать из выделенного (Create from selection).
  4. Ставим флажок В столбце слева (Left column), остальные флажки выключаем и жмем ОК.

Присваиваем имена ячейкам с картинками

Проверить получившиеся результаты можно в Диспетчере имен там же на вкладке Формулы (Formulas - Name manager).

Шаг 3. Создаем выпадающий список товаров

Теперь нам нужно создать выпадающий список с названиями товаров, откуда пользователь впоследствии будет их выбирать. Он может быть на том же или на другом листе (но в этой же книге!).

Создаем выпадающий список

Техника тут простая:

  1. Выделяем ячейку для выпадающего списка.
  2. Выбираем на вкладке Данные команду Проверка данных (Data - Data Validation).
  3. Из выпадающего списка Тип (Allow) выбираем вариант Список (List) и в поле Источник (Source) вводим формулу:

=ДВССЫЛ("Каталог[Название]")

=INDIRECT("Каталог[Название]")

Здесь "Каталог[Название]" - это текстовая (в кавычках!) ссылка на содержимое столбца с названиями товаров в нашей "умной" таблице-каталоге, а функция ДВССЫЛ (INDIRECT) нужна, чтобы превратить эту текстовую ссылку в настоящую.

Делать так приходится потому, что в поле Источник, к сожалению, нельзя прописывать прямые ссылки на строки или столбцы "умных" таблиц. Однако с помощью "костыля" в виде ДВССЫЛ мы это ограничение успешно обходим.

Шаг 4. Вытаскиваем имя диапазона для выбранного товара

Здесь же давайте получим имя диапазона из столбца ИД для выбранного в выпадающем списке товара. Проще всего это сделать классической функцией ВПР (VLOOKUP):

Извлекаем имя диапазона с картинкой

В английской версии это будет:

=VLOOKUP(C2;Каталог;3;FALSE)

Здесь:

  • С2 - адрес ячейки с выпадающим списком, содержимой которой мы ищем
  • Каталог - имя нашей "умной" таблицы
  • 3 - порядковый номер столбца ИД в "умной" таблице
  • ЛОЖЬ (или 0) - точный режим поиска

Шаг 5. Создаем именованный диапазон с ДВССЫЛ

Как вы уже, наверное, догадались, мы будем использовать содержимое ячейки E2 как ссылку на нужное нам изображение. Однако напрямую сделать это не получится, т.к. для Excel её содержимое формально является текстом. Чтобы оживить текстовую ссылку и превратить её в настоящую - используем уже знакомую нам функцию ДВССЫЛ (INDIRECT). Для этого:

  1. Открываем Диспетчер имён на вкладке Формулы и жмём кнопку Создать (Formulas - Name Manager - Create).
  2. Вводим любое подходящее имя для создаваемого диапазона, например Фото.
  3. В поле Ссылка (Reference) вводим формулу: =ДВССЫЛ(Лист2!$E$2)

Создаем диапазон со ссылкой на выбранную картинку

Шаг 6. Добавляем картинку и привязываем её к списку

Остался последний шаг:

  1. Выделим в нашей "умной" таблице любую ячейку с картинкой (например D2).
  2. На вкладке Главная развернём выпадающий список Копировать и выберем опцию Копировать как рисунок (Home - Copy - Copy as Picture). В появившемся затем окне можно выбрать вариант Как на экране (As on screen) и Растовый (Bitmap):

    Копируем как рисунок

    Нужно сделать именно так, чтобы скопировались не отдельно ячейка и картинка, а всё содержимое ячейки вместе с картинкой как единое целое изображение.

  3. Вставим скопированное обычным образом (например через Ctrl+V) рядом с нашим выпадающим списком.
  4. Для вставленной (и выделенной) картинки пропишем в строке формул ссылку на созданный ранее именованный диапазон (=Фото) и нажмём на Enter:

    Вешаем на изображение ссылку на диапазон Фото

Вот, собственно, и всё :) Можно наслаждаться результатом:

Готовый выпадающий список с изображением

Примечания

  • Ячейку С2, где мы с помощью ВПР получали имя нужного нам изображения можно, само-собой, скрыть.
  • Чтобы не было видно уродливых рамок вокруг картинки - нужно отключить все границы и убрать заливку на листе с каталогом. Для "умных" таблиц это делается на вкладке Конструктор (Design). Также можно отключить на листе каталога и тонкие серые линии сетки через Вид - Сетка (View - Gridlines).
  • Если в будущем вы будете дописывать новые товары и добавлять новые изображения в каталог - не забудьте присвоить новым ячейкам имена через команду Формулы - Создать из выделенного.

Ссылки по теме


29.10.2020 11:40:14
спасибо вам из израиля !!! interesno  i polezno!
31.12.2020 13:13:19
bilo b kruto pri videlenii odnovo imeni on vidal telii list (kak primer videleaisi nazvanie clasa i vivoditea list ucenicov) 8-)
01.02.2021 15:10:59
Добрый день!
Помогите плиз.
В последнем шаге, где вводишь формулу на фото, выдает ошибку - недопустимая ссылка.
Все делал как показано на видео.
14.03.2021 19:25:07
Добрый день. Интересная статья.
При добавлении новых строк в "умную" (исходную таблицу) столкнулся с необходимостью повторного добавления имени в столбец "D" в новой строке, где находятся фотографии, из столбца "C", содержащего ID, иначе фотография при выборе новой позиции не обновляется. Подскажите, как то можно автоматизировать процесс присвоения имени в столбце "D"? Спасибо
11.04.2021 15:32:58
Как у одного прошлого комментатора = В последнем шаге, где вводишь формулу на фото, выдает ошибку - недопустимая ссылка. В любых вариантах. В примере который скачал - работает. Во вновь созданном нет. Всегда ошибка на последнем шаге.
07.05.2021 14:35:11
Я сделала этим методом картинки, которые меняются не по значению из выпадающего списка, а подвязаны к срезу.  Задаю  столько областей показа рисунков, сколько максимально входит в срез, т.е., N. Показывается одновременно от 0 до N картинок.
Тормозит немного, но ничего.:)
Чтобы получить данные среза, вычисляю, выдает ли функция ПОЛУЧИТЬ.ДАННЫЕ... ошибку или нет. Если да - срез этот параметр отсекает. Если нет, срез включает его.
14.05.2021 13:44:22
в MS Office XP (2003) была возможность "привязать к сетке", тогда рисунок ложился в ячейку, предложенная функция "распределить" самостоятельно делает это не корректно.
Хорошо бы иметь перекличку с Office 2019 (Win10) vc Office 2003 (XP)
16.05.2021 12:00:13
... некоторые проблемы можно избежать, ваш пример работает и в Excel 2003 (XP)
Необходимо создать динамическую ссылку как учил Карлсберг
(Бизнес-анализ с использованием Excel, Карлберг К., 2014)
https://obuchalka.org/20201201127330/biznes-analiz-s-ispolzovaniem-excel-karlberg-k-2014.html
Добрый день! Николай, а можно данный подход применить к отфильтрованным данным? Если да то желательно, если 2 и более элемента, то и они были бы отображены картинкой. Заранее Спасибо!
20.07.2021 14:15:14
Добрый день ! Николай , на последнем шаге, где вводишь формулу на фото, выдает ошибку - недопустимая ссылка.
У меня Excel 2019/
01.10.2021 14:44:26
Добрый день.
при применении этого способа, в выпадающем списке пропадает галочка в ячейке. Она отображается при нажатии на правый верхний угол ячейки. т.е если в обычном списке она закрепляется при нажатии на саму ячейку, то тут приходится ловить её.  Как это исправить?
Наверх