Выбор фото из выпадающего списка
Постановка задачи
Необходимо сделать так, чтобы в одной из ячеек листа был выпадающий список с наименованиями, при выборе из которого, товар отображался бы рядом в виде фотографии:
Видео
Шаг 1. Создаем каталог с фото и даем ему имя
Создаем на Листе 1 мы каталог с наименованиями и фотографиями товаров, состоящий из двух столбцов (Модель и Фото):
Теперь надо дать имя нашему каталогу, чтобы ссылаться на него в будущем. В 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)
Эта формула определяет последнюю занятую ячейку в столбце А и выдает на выходе диапазон с А2 до этой найденной ячейки. Такая относительно сложная конструкция нужна, чтобы впоследствии дописывать новые модели к нашему списку и не думать об исправлении диапазона. Если дописывать точно ничего не придется, то можете вместо ввода этой страшноватой формулы просто указать =A2:A5
Шаг 2. Выпадающий список для выбора модели
Перейдем на Лист 2 и создадим там ячейку с выпадающим списком для выбора пользователем модели телефона (пусть это будет A1). Выделяем ячейку и идем в меню Данные - Проверка (Data - Validation) или в новых версиях Excel - на вкладку Данные - Проверка данных (Data - Data Validation). Далее в поле Тип данных (Allow) выбираем Список (List), а в качестве Источника (Source) указываем наш Фотоальбом (не забудьте перед ним добавить знак равенства):
Кроме того этой ячейке удобно дать имя - снова меню Вставка - Имя - Присвоить и далее вводим имя (например Выбор) и ОК.
Шаг 3. Копируем фотографию
Перенесем первую фотографию из фотоальбома к выпадающему списку. Выделите ячейку с первой фотографией (не сам рисунок, а ячейку!) и
в Excel 2003 и старше - удерживая Shift, откройте меню Правка (Edit). Там должен появиться невидимый ранее пункт Копировать рисунок (Copy as Picture):
В Excel 2007 и новее можно просто развернуть выпадающий список под кнопкой Копировать (Copy) на Главной (Home) вкладке:
В Excel 2010 появится еще одно дополнительное окно с выбором типа создаваемого изображения:
В нем нужно выбрать варианты "как на экране" и "растровый".
Копируем, переходим на Лист 2 к выпадающему списку и в любую пустую ячейку недалеко от него вставляем наш мини-скриншот ячейки с фотографией (меню Правка - Вставить или обычное CTRL+V).
Шаг 4. Создаем динамическую ссылку на выбранную фотографию
Теперь необходимо сделать ссылку, которая будет указывать на ячейку с выбранной фотографией. Открываем меню Вставка - Имя - Присвоить (Insert - Name - Define) или Диспетчер имен (Name Manager) на вкладке Формулы (Formulas) и создаем еще один именованный диапазон:
Имя нашей ссылки, допустим, будет Фото, а формула
=СМЕЩ(Лист1!$B$2;ПОИСКПОЗ(Выбор;Фотоальбом;0)-1;0;1;1)
=OFFSET(Лист1!$B$2;MATCH(Выбор;Фотоальбом;0)-1;0;1;1)
Технически, функция ПОИСКПОЗ (MATCH) находит ячейку с нужной моделью в каталоге по названию, а функция СМЕЩ (OFFSET) затем выдает ссылку на соседнюю справа от найденного названия ячейку, т.е. ячейку с фотографией товара.
Шаг 5. Привязываем фотографию к ссылке
Осталось выделить скопированную фотографию на Листе 2 и вписать в строку формул
=Фото
и нажать Enter
Все! :)
Ссылки по теме
- Создание выпадающего списка в ячейках листа
- Создание зависимых выпадающих списков
- Автоматическое создание выпадающих списков при помощи инструментов надстройки PLEX
- Выпадающий список с автоматическим удалением уже использованных элементов
- Выпадающий список с автоматическим добавлением недостающих элементов
Было бы здорово добавить статейку изменения значений ячеек в зависимости от выбора значения в списке
В общем - хорошая тема!
Хорошо что такие есть, СПАСИБО!!! .
спасибо.
У меня Excel 2007, но нет выпадающего списка под кнопкой Копировать. Я скопировала рисунок через Вставить - Как рисунок - Копировать как рисунок.
Но взял из скачанного примера картинку с уже прописанной формулой - изменил размер и у меня все стало работать. Хотя все-таки интересно как вставить как рисунок - чтобы можно было прописать (вставить) формулу в рисунок?
если те, кто понял, не подставные, набивающие рейтинг, то скажите:
Осталось выделить скопированную фотографию на Листе 2 и вписать в строку формул
=Фото
Когда про выборе пункта "Копировать как рисунок" в Excel 2010 появляется диалоговое окно выбора типа рисунка, необходимо выбрать пункт "Растровый".
Ксатати, я так давно искала что-то похожее. Супер, когда невозможное становиться возможным:)
В случае если размеры картинок находящиеся в именованном диапазоне "Фото" отличаются*, а возможности привести их к одному размеру по "высоте-ширине" (чтобы логотип точно влезал в ячейку) нет, то при работе описанного алгоритма часть изображения логотипа режется под размер ячейки
Как быть в этом случае?
Спасибо!
* - есть логотипы с квадратной пропорцией сторон, а есть с пропорцией прямоугольной.
Заранее спасибо!
Курсор просто на становится в строку формул. Я заметил что после вставки фото, оно вставляется не с такими "ручками" как у вас в примере , а с ручками как у обычного рисунка (как после ctrl+c, ctrl+v).
Наверное я не правильно копирую? Но в моем офисе 2007 есть только такой вариант: из выпадающего списка, под кнопкой "Вставить" выбрать "Как рисунок" и затем "Копировать как рисунок"
Правильно ли это?
Делал все как в примере, только со своими картинками и текстом, скопировать рисунок так, чтобы вписать формулу не получается, но скачав пример, то рисунок, который менялся с формулой совершенно другого формата, пока не знаю в чем дело, но буду эксперементировать
суть данной функции, которая представлена в примере, это создать рисунок, который будет полностью повторять то, что написано или нарисовано в конкретной ячейке, другими словами скриншот ячейки.
Данную функцию (лично я в своем excel 2007) получил следующим путем:
1) выбираем ячейку, которую хотим, чтобы наш рисунок повторял
2) тисним CTRL+V или же просто копировать
3) в меню "Главное => буфер обмена => вставить.." нажимаем на стрелочку, так чтобы выскочило дополнительное меню и выбираем "Как рисунок => вставить связь с рисунком".
Тем самым получаем рисунок, который ссылается на конкретную ячейку, у которого можно вбивать формулу (по идее формула должна ссылаться только на какую то ячейку (чтобы было с чего делать скриншот), то есть если вбить формулу итог вычисления которой будет 16 или 9999, то выдаст ошибку неверная ссылка. Именно по этой причине надо использовать формулу, которую использует автор данной статьи ( =смещь....) а не, например =впр).
Данный рисунок, как правило, получаем поверх копируемой ячейки.
Надеюсь довольно понятно разъяснил.
Выпадающий список в ячейке это хорошо, но порой гораздо интереснее, когда этот список реализован с помощью кнопки ( через вкладку разработчик). В этом случае, нам не удастся воспользоваться функцией ПОИСКПОЗ, поскольку у нас не будет ячейки, а будет объект - выпадающий список. Что же делать? Но все оказывается гораздо проще! Мы можно привязать объект выпадающий список к любой другой ячейке. В этом случае, в этой ячейке будет проставляться номер выбранной позиции относительно начала списка! А стало быть, можно ссылаться прямо на эту ячейку вместо использования не самой просто функции ПОИСКПОЗ. Надеюсь, кому-то будет полезно
И вопрос к аудитории: На финише мы выделяли вставленную как картинка ячейку и в командной строке прописывали = Фото (динамический массив). Массив Фото по сути, задан формулой Смещ и т.д Но если в командной строке прописать эту самую формулу, заместо короткого "Фото" Excel ругается. Т.о сделать так, чтобы "протянуть" картинку вниз, чтобы, к примеру, она изменялась при изменении значения в соседнем столбце, у нас не получится? Или все же как то можно это сделать? Своеобразный аналог ВПР, но для картинок
Очень полезная статья и прием.
Однако я столкнулся пока с неразрешимой для меня проблемой-не могу сделать такой выпадающий список на новом листе книги, причем в ячейке, которая служит, так сказать, ориентиром для нахождения нужной картинки, находится не выпадающий список, как в примере, а формула, которая подтягивает это текстовое значение из другого листа.
Затык наступает на привязке динамического диапазона картинке. Выдает ошибку "НЕ ВЕРНАЯ ССЫЛКА". Пытался выше изложенными способами все сделать, но то ли руки кривые, то ли что, ошибка пока осталась.
Прошу у читателей форума и у Вас, Николай, помощи в решении вопроса.
Если на Листе2 (там где выпадающий список) имеется объединение ячеек (допустим объеденены А5иВ5 или более или А5,А6,В5,В6) то изображения из Листа1 (из фотоальбом) соответствующее этим строкам (в данном случае изображение в ячейке В5 фотоальбома), будут отображаться криво.
Лечится путем избавления от объединения ячеек, либо если не желательно избавляться от объединения, переносом изображений фотоальбома в другой столбец. В моем случае в нескольких строках были объединены ячейки от А до К. Перенес изображения в столбец L, и внес соответствующее изменение в именованный диапазон "фото":
=СМЕЩ(Лист1!$L$2;ПОИСКПОЗ(Выбор;Фотоальбом;0)-1;0;1;1)
и все заработало как надо.
P.S. Хочу сказать ОГРОМНЕЙШЕЕ СПАСИБО Автору сайта и форума за все эти приемы. Очень многое узнал из приемов, чего нет в приемах нашел или подсказали на форуме. Все это применил на практике в работе. Сэкономил сотни часов. То что раньше приходилось делать вручную часами, а то и днями, сейчас делается несколькими нажатиями мыши.
- избавление от объединенных ячеек - ничего не дало,
- перенос столбцов и строк фотоальбома - был неудобен;
решила проблему по аналогии - перенесен не фотоальбом из Листа1, а ячейка с выпадающим списком на Листе2,
таким образом, что бы адрес ячейки с выпадающим списком на Листе2, никак не пересекался с адресами ячеек фотоальбома на Листе1.))
P.S. Так же С БОЛЬШУЩЕЙ БЛАГОДАРНОСТЬЮ Автору сайта и всем пользователям сайта, кто НЕ ЖАЛЕЕТ ВРЕМЕНИ, ПОДЕЛИТЬСЯ СВОИМ ОПЫТОМ!!!