Выбор фото из выпадающего списка

Постановка задачи

Необходимо сделать так, чтобы в одной из ячеек листа был выпадающий список с наименованиями, при выборе из которого, товар отображался бы рядом в виде фотографии:

dropdown-picture.gif

Видео

Шаг 1. Создаем каталог с фото и даем ему имя

Создаем на Листе 1 мы каталог с наименованиями и фотографиями товаров, состоящий из двух столбцов (Модель и Фото):

show_pics1.gif

Теперь надо дать имя нашему каталогу, чтобы ссылаться на него в будущем. В Excel 2003 и старше для этого идем в меню Вставка - Имя - Присвоить (Insert - Name - Define), а в Excel 2007 и новее - жмем на кнопку Диспетчер имен (Name Manager) на вкладке Формулы (Formulas). Создаем диапазон - вводим имя (например Фотоальбом) и в качестве адреса указываем формулу:

=СМЕЩ(Лист1!$A$1;1;0;СЧЁТЗ(Лист1!$A:$A)-1;1)

=OFFSET(Лист1!$A$1;1;0;COUNTA(Лист1!$A:$A)-1;1)

dropdown-pics1.png

Эта формула определяет последнюю занятую ячейку в столбце А и выдает на выходе диапазон с А2 до этой найденной ячейки. Такая относительно сложная конструкция нужна, чтобы впоследствии дописывать новые модели к нашему списку и не думать об исправлении диапазона. Если дописывать точно ничего не придется, то можете вместо ввода этой страшноватой формулы просто указать =A2:A5

Шаг 2. Выпадающий список для выбора модели

Перейдем на Лист 2 и создадим там ячейку с выпадающим списком для выбора пользователем модели телефона (пусть это будет A1). Выделяем ячейку и идем в меню Данные - Проверка (Data - Validation) или в новых версиях Excel - на вкладку Данные - Проверка данных (Data - Data Validation). Далее в поле Тип данных (Allow) выбираем Список (List), а в качестве Источника (Source) указываем наш Фотоальбом (не забудьте перед ним добавить знак равенства):

dropdown-pics2.png

Кроме того этой ячейке удобно дать имя - снова меню Вставка - Имя - Присвоить и далее вводим имя (например Выбор) и ОК.

Шаг 3. Копируем фотографию

Перенесем первую фотографию из фотоальбома к выпадающему списку. Выделите ячейку с первой фотографией (не сам рисунок, а ячейку!) и

в Excel 2003 и старше -  удерживая Shift, откройте меню Правка (Edit). Там должен появиться невидимый ранее пункт Копировать рисунок (Copy as Picture):

show_pics5.gif

В Excel 2007 и новее можно просто развернуть выпадающий список под кнопкой Копировать (Copy) на Главной (Home) вкладке:

show_pics7.gif

В Excel 2010 появится еще одно дополнительное окно с выбором типа создаваемого изображения:

show_pics8.png

В нем нужно выбрать варианты "как на экране" и "растровый".

Копируем, переходим на Лист 2 к выпадающему списку и в любую пустую ячейку недалеко от него вставляем наш мини-скриншот ячейки с фотографией (меню Правка - Вставить или обычное CTRL+V).

Шаг 4. Создаем динамическую ссылку на выбранную фотографию

Теперь необходимо сделать ссылку, которая будет указывать на ячейку с выбранной фотографией. Открываем меню Вставка - Имя - Присвоить (Insert - Name - Define) или Диспетчер имен (Name Manager) на вкладке Формулы (Formulas) и создаем еще один именованный диапазон:

dropdown-pics3.png

Имя нашей ссылки, допустим, будет Фото, а формула

=СМЕЩ(Лист1!$B$2;ПОИСКПОЗ(Выбор;Фотоальбом;0)-1;0;1;1)

=OFFSET(Лист1!$B$2;MATCH(Выбор;Фотоальбом;0)-1;0;1;1)

Технически, функция ПОИСКПОЗ (MATCH) находит ячейку с нужной моделью в каталоге по названию, а функция СМЕЩ (OFFSET) затем выдает ссылку на соседнюю справа от найденного названия ячейку, т.е. ячейку с фотографией товара.

Шаг 5. Привязываем фотографию к ссылке

Осталось выделить скопированную фотографию на Листе 2 и вписать в строку формул

=Фото

и нажать Enter

show_pics6.gif

Все! :)

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




Страницы: 1  2  
18.03.2014 11:47:52
Добрый день. Отличный урок, да и не только этот. Спасибо большое, очень много узнал. Хотелось бы еще проконсультироваться:
А как может быть решение у следующей задачи(приведу значения по примеру данной статьи):
Имеется тот же список, что и в пункте шаг 1. Как можно сделать нижеприведенную таблицу?
Рисунок Nokia 8800Рисунок Voxtel W420Рисунок Motorola RAZR V3i
Nokia 8800Voxtel W420Motorola RAZR V3i
Все наименования добавляются из выпадающего списка и могут меняться.При смене марки меняется и рисунок. Спасибо
30.03.2014 01:22:14
Отличная статья))
Возможно ли создать таблицу с выпадающим списком, а напротив каждой ячейки со списком - фотография?
Спасибо заранее за ответ)
07.07.2014 08:32:36
Спасибо за статью.
Ещё один хороший вариант - ТУТ.

Также можно просто вставить примечание к ячейке - формат примечания - заливка - способы заливки - рисунок.
Но это уже не соответствует теме "Выбор фото из выпадающего списка".
23.07.2014 17:27:34
Та же проблема. Excel 2007 - нет стрелочки возле кнопки копировать!!!! (может что-то в настройках не включено?)
3) в меню "Главное => буфер обмена => вставить.." нажимаем на стрелочку, так чтобы выскочило дополнительное меню и выбираем "Как рисунок => вставить связь с рисунком".
этот пункт есть, но он не активен.
Изображение из ячейки вставляется через Вставить => Как рисунок => Копировать как рисунок... Но ему нельзя присвоить =Фото, строка формул не активна при выбранном изображении.
Николай, помогите пожалуйста!
23.07.2014 18:13:48
Открыл файл 2010-ом, все получается. Дело в или в версии или в настройках Excel
08.10.2014 11:24:17
Если у кого то ругается на =фото, то внимательно смотрите видео про четвертый шаг
а именно  про написание вот этой строчки
=СМЕЩ(Лист1!$B$2;ПОИСКПОЗ(Выбор;Фотоальбом;0)-1;0;1;1)
и тогда все будет хорошо


Автору огромное спасибо за шикарные уроки!!!
29.11.2014 00:32:34
Здравствуйте Николай! Спасибо за уроки.Очень помогают.
Вопрос ?  Почему-то с формулами =OFFSET(Лист1!$A$1;1;0;COUNTA(Лист1!$A:$A)-1;1) и =OFFSET(Лист1!$B$2;MATCH(Выбор;Фотоальбом;0)-1;0;1;1) выбор фото не работает .В чем причина? Заранее спасибо.
02.12.2014 16:15:34
Скачал файл с примером и провел эксперимент.В предложенном файле удалил на втором листе картинку с телефоном ,затем провел операцию с копированием по выше приведенной методике в Excel-2007.Привязка формулы "=фото" не работает.

02.12.2014 21:10:53
Для того , чтобы строка формул была активная,нужно проделать следующую операцию: (для Excel -2007).

1)Выделяем ячейку с картинкой.(не картинку, а ячейку!)

2) Копируем ячейку с помощью команды "КОПИРОВАТЬ".
3) Выделяем  нужную ячейку.
4)Заходим во вкладку"ВСТАВИТЬ".
5)В раскрывающемся списке выбираем "КАК РИСУНОК" и выбираем команду "ВСТАВИТЬ СВЯЗЬ С РИСУНКОМ"
.
Проверил все работает.

:)
СПАСИБО ОГРОМНОЕ! Тоже не получалось, пока не прочла ваш комент:D
19.12.2014 12:18:50
вопрос, можно ли сделать выпадающий список более длинным? там влазит всего 8 строчек и ниже нужно лазить ползунком, что не совсем удобно.
можно ли сделать выпадающий список например из 30 строчек?
Всё замечательно работает! Спасибо автору.
Вопрос такой. Почему, когда я запустил "Фото на выбор" у меня макросы стали выполняться очень медленно?
Удалил всё из диспетчера имён, всё стало нормально. Хотелось бы, чтобы всё работало.
26.08.2015 09:32:08
Добрый день. Это всё конечно отлично, но есть одно НО. У меня в файле порядка 40 картинок изменяются в зависимости изменения других ячеек (без выпадающего списка). И что на домашнем, что на рабочих компьютерах файл начинает ужасно тормозить. Как только убираешь эти автоматмо изменяющиеся картинки начинает работать превосходно. Подскажите, в чем проблема?
23.11.2015 13:58:45
Можно ли подобное сделать в Таблицах Гугл?
25.11.2015 18:12:33
Добрый день, Николай.
Помогите решить проблему завершающего шага.
Выделил картинку, а в строке формул написать "= Фото"  не возможно. При выделенной картинке - строка не работает
05.05.2016 14:42:00
Помогите разобраться - можно ли при помощи подобного фокуса обойти проблему с тем,
что автофильтр при его применении скрывает строки, но не вставленные в них картинки.
как сделать, чтобы в оставшихся строках оставались только нужные картинки,
а не все какие были на листе? (2010)
аналогичная проблема и с группировкой - при свертывании группы картинка остается на месте.
пробовал вставлять как вектор, не помогает
24.01.2017 12:27:46
На 5 шаге при введении ссылки =Фото появляется сообщение - Неверная ссылка. Что могло быть сделано не так? Спасибо.
01.07.2017 17:49:17
Добрый день, это работает только если выбирать значение из одной ячейки, а нам необходимо чтобы можно выбрать из множества ячеек (несколько строк) и напротив каждой выводилась картинка
01.01.2018 13:36:29
Скачал Ваш пример. Он не соответствует Вашему описанию.
САМЫЙ ЛУЧШИЙ САЙТ ПО excel Спасибо огромное
21.01.2018 13:19:07
Здравствуйте, а можно ли как-то сделать, чтобы при фильтрации таблицы лишние картинки тоже фильтровались, а не накладывались кучей?



28.01.2018 13:07:15
Нашел, нужно добавить в свойствах изменять вместе с ячейкой...
31.01.2018 17:26:00
А как сделать второй выпадающий список на этом же листе независящие друг от друга, но ниже и так же при выборе из которого, товар отображался бы рядом в виде фотографии.
01.02.2018 13:59:39
Вопрос! А если строк с выпадающими ячейками много, то как расширить формулы и на эти другие ячейки? Или к каждой новой ячейке заново прописывать весь путь?
17.03.2018 21:51:19
получилось сделать только в одной ячейке, когда копируешь и меняешь название, фото не меняется - остается то, которое в первой ячейке. Что не так сделала? =СМЕЩ('2 квартал 2018'!$S$1;ПОИСКПОЗ('2 квартал 2018'!$J$3;'2 квартал 2018'!$S:$S;0)-1;1;1;1)
31.12.2020 13:52:00
Здравствуйте, тема очень интересная.
Но я считаю excel не лучшее место для хранения фото.
Можно ли сделать, что-то подобное но с подтягиванием фото из каталога (папки) формата "C:\Users\Admin\Desktop\Фото"? Пускай и средствами VBA .
Например: Имена файлов вытягиваются в массив, которые далее используются для выпадающего списка. Ну и в другой ячейке само фото.

Думаю это было бы очень интересно, как для изучения конкретной темы, так и для VBA в целом.
03.05.2021 18:27:52
Тема конечно интересная, фотку, прежде всего необходимо вставить в списке контактов, часто мы не можем вспомнить абонента.
Проблема сохранения и/или создания исторической базы данных телефонных контактов.
Nokia Suite и её последователи позволяли скидывать контакты из телефона в ПК разные форматы в том числе *.xls*
Хорошо бы создать подобную программу для Android/ iOS
Они предлагают нам сохранение контактов только в облако.
22.07.2021 00:34:07
Урра, заработала!!! Исключительно замечательно:)

Спасибо!
Страницы: 1  2  
Наверх