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

319831 02.10.2012 Скачать пример

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

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

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

Все! :)

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




SEA
03.10.2012 19:09:58
Отличная статья!!!
Было бы здорово добавить статейку изменения значений ячеек в зависимости от выбора значения в списке :) Или, например, присвоение некоторй переменной выбранного значения списке, или связь с автофильтром или создание подобие автофильтра ... :)
В общем - хорошая тема!
Хорошо что такие есть, СПАСИБО!!! .
igor
03.10.2012 19:11:16
можно ли в ячейке -с- получить и цену на телефон.
спасибо.
Стас
03.10.2012 19:12:04
Можно ли сделать в Excel выпадающий список просто из фото? Т.е. я должен нажать на список, а там вывалются фотографии. Я выбираю нужое фото и оно фиксируется в этой ячейке.
19.12.2014 12:00:33
аналогичный вопрос, можно ли сделать так, как спросил Стас?
Михаил
03.10.2012 19:13:11
Давно искал как это делать. Супер!!!
Максим
03.10.2012 19:13:43
Очень хороший пример я его доработал и сделал интрактивный тест.
15.02.2013 17:58:25
могли бы Вы поделиться конкретным примером:?
Константин
03.10.2012 19:14:19
Старенькая, но классная статья. Учимся с удовольствием.
27.12.2012 12:29:41
Делаю пример, но у меня не получается. Не могу сделать привязку фотографии (=Фото), потому как не вводится формула в строку формул при выделении рисунка.
У меня Excel 2007, но нет выпадающего списка под кнопкой Копировать. Я скопировала рисунок через Вставить - Как рисунок - Копировать как рисунок.
11.02.2013 17:58:48
Да, тоже не смог вставить скопированное изображение так чтобы написать формулу.
Но взял из скачанного примера картинку с уже прописанной формулой - изменил размер и у меня все стало работать. Хотя все-таки интересно как вставить как рисунок - чтобы можно было прописать (вставить) формулу в рисунок?
18.02.2013 12:26:38
Чую, надо видеоурок по этому приему записать - что-то много вопросов осталось. Займусь...
23.01.2013 13:39:37
А как фото вставить-то?:(
15.02.2013 17:16:23
Все конечно супер, но непонятно что и куда скопировать
если те, кто понял, не подставные, набивающие рейтинг, то скажите:
Копируем, переходим на Лист 2 к выпадающему списку и в любую пустую ячейку недалеко от него вставляем (меню Правка - Вставить или обычное CTRL+V).
это что за ячейка:? которая просто содержит данные или это ячейка, в которой потом будут выводиться фото:?
Шаг 5. Привязываем фотографию к ссылке

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

=Фото
не понятно совсем, если 5ть вото, то как быть:? точнее если 5ть моделей телефонов, то как:? 5ть именованных списков:? или что:? или автор просто перемутал наименование листа:? ведь на листе 2 у нас результат, а все данные на листе 1
15.02.2013 17:25:46
да и если выделить фотографвию, как говорит автор, то в строку формул после записи =фото и нажатии ентер, появляется диалоговое окно с сообщением: НЕ ВЕРНАЯ ССЫЛКА
18.02.2013 11:24:12
Для Вас и для всех:
Когда про выборе пункта "Копировать как рисунок" в Excel 2010 появляется диалоговое окно выбора типа рисунка, необходимо выбрать пункт "Растровый".
18.02.2013 17:48:58
Спасибо за уточнение, Иван! Дописал в статью этот момент.
25.02.2013 19:21:49
Еще один вариант: на шаге 4 вместо «хитрой» функции СМЕЩ можно использовать  «нехитрую» функцию ИНДЕКС: Таблицу с названиями моделей и фото преобразуем в «умную» и назовем, к примеру «Таб». Затем в источник данных для проверки внесем формулу: =ДВССЫЛ("Таб[Модель]"). Затем создадим в диспетчере имен ссылку на ячейки с фото (адрес ячейки с выпадающим списком к примеру F1): =ИНДЕКС(Таб;ПОИСКПОЗ(Лист1!$F$1;Таб[Модель];0);2). Ну и потом привязать фото к данной ссылке. Большое спасибо Вам за видеоурок!!!
19.03.2013 23:52:26
А я попробую теперь сделать бейджики своему отделу по аналогии
Ксатати, я так давно искала что-то похожее. Супер, когда невозможное становиться возможным:):):)
11.04.2013 10:47:18
Бейджики оптом по списку лучше делать в Word с помощью слияния.
25.03.2013 18:38:00
Статься интересная, спасибо! :)
В случае если размеры картинок находящиеся в именованном диапазоне "Фото" отличаются*, а возможности привести их к одному размеру по "высоте-ширине"  (чтобы логотип точно влезал в ячейку) нет, то при работе описанного алгоритма часть изображения логотипа режется под размер ячейки :-(
Как быть в этом случае?
Спасибо!

* - есть логотипы  с квадратной пропорцией сторон, а есть с пропорцией прямоугольной.
30.03.2013 20:27:27
Скажите, а почему меняющийся рисунок не выводится на печать?
11.04.2013 10:45:54
Се ля ви. Ничего не поделаешь - печатать его он не будет :(
01.08.2013 02:39:04
У меня печатает изменяемые строки и картинки!
11.02.2015 08:38:04
У меня печатается
03.06.2013 10:37:18
Добрый день, у меня такая же ситуация, как и у Anne: не могу сделать привязку фотографии - курсор отказывается вставать в строку формул, когда выделено изображение (копировала как растровый рисунок). Подскажите, в чем может быть дело?
04.07.2013 23:52:18
Друзья помогите! Посмотрел данный вариант - все круто, вот только одно НО. Мне необходимо что бы рисунок менялся не от значения выбранного в выпадающем списке, а от значения в ячейке, которое меняется автоматически от 1,2,3.... до 27! т.е. если в ячейке значение 1, то рисунок такой-то, если в этой же ячейке значение 2, то рисунок другой....

Заранее спасибо!
24.07.2013 15:37:28
Та же проблема что и у многих с шагом 5.
Курсор просто на становится в строку формул. Я заметил что после вставки фото, оно вставляется не с такими "ручками" как у вас в примере , а с ручками как у обычного рисунка (как после ctrl+c, ctrl+v).
Наверное я не правильно копирую? Но в моем офисе 2007 есть только такой вариант: из выпадающего списка, под кнопкой "Вставить" выбрать "Как рисунок" и затем "Копировать как рисунок"
Правильно ли это?    
06.08.2013 12:36:54
аналогично, не могу вставить =фото... что делаю не так? копирую как растовый рисунок все делаю так же и все равно не вставляется =фото
20.09.2013 13:29:20
Объясните пожалуйста, в примере какой-то особый формат рисунка, в этом наверно и собака зарыта.
Делал все как в примере, только со своими картинками и текстом, скопировать рисунок так, чтобы вписать формулу не получается, но скачав пример, то рисунок, который менялся с формулой совершенно другого формата, пока не знаю в чем дело, но буду эксперементировать
20.09.2013 15:49:43
Народ, я нашел в чем проблема!
суть данной функции, которая представлена в примере, это создать рисунок, который будет полностью повторять то, что написано или нарисовано в конкретной ячейке, другими словами скриншот ячейки.
Данную функцию (лично я в своем excel 2007) получил следующим путем:
1) выбираем ячейку, которую хотим, чтобы наш рисунок повторял
2) тисним CTRL+V или же просто копировать
3) в меню "Главное => буфер обмена => вставить.." нажимаем на стрелочку, так чтобы выскочило дополнительное меню и выбираем "Как рисунок => вставить связь с рисунком".

Тем самым получаем рисунок, который ссылается на конкретную ячейку, у которого можно вбивать формулу (по идее формула должна ссылаться только на какую то ячейку (чтобы было с чего делать скриншот), то есть если вбить формулу итог вычисления которой будет 16 или 9999, то выдаст ошибку неверная ссылка. Именно по этой причине надо использовать формулу, которую использует автор данной статьи (  =смещь....) а не, например =впр).
Данный рисунок, как правило, получаем поверх копируемой ячейки.

Надеюсь довольно понятно разъяснил.
26.09.2013 09:55:11
Спасибо,  Maksim Barilov! Всё работает, только  
2) тисним CTRL+V или же просто копировать
CTRL+С  :)
10.10.2013 19:14:59
Спасибо! Всё работает.Отличная статья.Подскажите,как сделать чтобы при выборе наименования товара из выпадающего списка  получать в соседних  ячейках В и С габаритные размеры изделия (на пример:В-ширина,С-длина). Спасибо!
03.11.2013 17:04:36
Добрый день! Совсем недавно открыл для себя этот замечательный сайт и теперь восторгаюсь :)  Посмотрел видео, сделал. Класс! Хочу сделать небольшой коммент:
Выпадающий список в ячейке это хорошо, но порой гораздо интереснее, когда этот список реализован с помощью кнопки ( через вкладку разработчик). В этом случае, нам не удастся воспользоваться функцией ПОИСКПОЗ, поскольку у нас не будет ячейки, а будет объект - выпадающий список. Что же делать?  Но все оказывается гораздо проще! Мы можно привязать объект выпадающий список к любой другой ячейке. В этом случае, в этой ячейке будет проставляться номер выбранной позиции относительно начала списка! А стало быть, можно ссылаться прямо на эту ячейку вместо использования не самой просто функции ПОИСКПОЗ. Надеюсь, кому-то будет полезно :)

И вопрос к аудитории:  На финише мы выделяли вставленную как картинка ячейку и в командной строке прописывали = Фото (динамический массив). Массив Фото по сути, задан формулой Смещ и т.д Но если в командной строке прописать эту самую формулу, заместо короткого "Фото" Excel ругается. Т.о сделать так, чтобы "протянуть" картинку вниз, чтобы, к примеру, она изменялась при изменении значения в соседнем столбце, у нас не получится? Или все же как то можно это сделать? Своеобразный аналог ВПР, но для картинок :)
04.11.2013 21:21:23
А у нас такое задание было на олимпиаде по ИКТ областного уровня за 10 класс.. За статью-урок спасибо, теперь знаем, как такое делать:D
29.11.2013 14:42:42
подскажите возможно ли изменить формулу если есть два листа - каталогов  с наименованиями и фотографиями товаров, что бы фотки также отображались через выпадающий список, но данные уже берутся с двух листов
04.12.2013 16:56:10
Подскажите, такая задача. В выпадающем списке написаны названия цветов, КРАСНЫЙ, СИНИЙ и т.д., как сделать что бы после выбора цвета соседняя ячейка закрашивалась в нужный цвет ? Или еще лучше было бы, что бы закрашивалась ячейка прямо в выпадающем списке в этот цвет (после выбора).
16.01.2014 07:14:01
условное форматирование вам в помощь (Ексел 2007 и старше)
06.01.2014 17:53:10
Добрый день!

Очень полезная статья и прием.

Однако я столкнулся пока с неразрешимой для меня проблемой-не могу сделать такой выпадающий список на новом листе книги, причем в ячейке, которая служит, так сказать, ориентиром для нахождения нужной картинки, находится не выпадающий список, как в примере, а формула, которая подтягивает это текстовое значение из другого листа.

Затык наступает на привязке динамического диапазона картинке. Выдает ошибку "НЕ ВЕРНАЯ ССЫЛКА". Пытался выше изложенными способами все сделать, но то ли руки кривые, то ли что, ошибка пока осталась.

Прошу у читателей форума и у Вас, Николай, помощи в решении вопроса.
15.01.2014 18:30:32
К сведению, если вдруг у кого-нибудь встретится аналогичная ситуация.

Если на Листе2 (там где выпадающий список) имеется объединение ячеек (допустим объеденены А5иВ5 или более или А5,А6,В5,В6) то изображения из Листа1 (из фотоальбом)  соответствующее этим строкам (в данном случае изображение в ячейке В5 фотоальбома), будут отображаться криво.

Лечится путем избавления от объединения ячеек, либо если не желательно избавляться от объединения, переносом изображений фотоальбома в другой столбец. В моем случае в нескольких строках были объединены ячейки от А до К. Перенес изображения в столбец L, и внес соответствующее изменение в именованный диапазон "фото":
=СМЕЩ(Лист1!$L$2;ПОИСКПОЗ(Выбор;Фотоальбом;0)-1;0;1;1)
и все заработало как надо.

P.S. Хочу сказать ОГРОМНЕЙШЕЕ СПАСИБО Автору сайта и форума за все эти приемы. Очень многое узнал из приемов, чего нет в приемах нашел или подсказали на форуме. Все это применил на практике в работе. Сэкономил сотни часов. То что раньше приходилось делать вручную часами, а то и днями, сейчас делается несколькими нажатиями мыши.  
01.04.2015 14:03:05
САКЕН!!!  СПАСИБО ПРЕОГРОМНЕЙШЕЕ!! за подсказку в каком направлении смотреть )), нигде больше не попадалась эта информация!!!

- избавление от объединенных ячеек - ничего не дало,
- перенос столбцов и строк фотоальбома - был неудобен;

решила проблему по аналогии - перенесен не фотоальбом из Листа1, а ячейка с выпадающим списком на Листе2,  
таким образом,  что бы адрес ячейки с выпадающим списком на Листе2, никак не пересекался с адресами ячеек фотоальбома на Листе1.))  

P.S. Так же С БОЛЬШУЩЕЙ БЛАГОДАРНОСТЬЮ Автору сайта и всем пользователям сайта, кто НЕ ЖАЛЕЕТ ВРЕМЕНИ, ПОДЕЛИТЬСЯ СВОИМ ОПЫТОМ!!!
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)В раскрывающемся списке выбираем "КАК РИСУНОК" и выбираем команду "ВСТАВИТЬ СВЯЗЬ С РИСУНКОМ"
.
Проверил все работает.

:)
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
Добрый день, это работает только если выбирать значение из одной ячейки, а нам необходимо чтобы можно выбрать из множества ячеек (несколько строк) и напротив каждой выводилась картинка