Шаг 5. Создаем выпадающий список в ячейках

Осталось выделить ячейки B2:B8 нашего графика и добавить в них выпадающий список с элементами диапазона Имена. Для этого

• в Excel 2003 и старше - откроем меню Данные - Проверка (Data - Validation),
• в Excel 2007 и новее - жмем кнопку Проверка данных (Data Validation) на вкладке Данные (Data)

В открывшемся окне выберем в списке допустимых значений вариант Список (List) и укажем Источник (Source) данных:

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

Выпадающий список с данными из другого файла

 

 

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

Например, если необходимо создать выпадающий список с содержимым ячеек А1:А10 с листа Список из файла Товары.xls, нужно в открыть окно проверки данных через меню или вкладку Данные - Проверка (Data - Validation) данных и в поле Источник (Source) ввести следующую конструкцию:

=ДВССЫЛ("[Товары.xls]Список!$A$1:$A$10")

Функция ДВССЫЛ преобразует текстовую строку аргумента в реальный адрес, используемый для ссылки на данные. Обратите внимание, что имя файла заключается в квадратные скобки, а восклицательный знак служит разделителем имени листа и адреса диапазона ячеек. Если имя файла содержит пробелы, то его надо заключить в апострофы.

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

=ДВССЫЛ("'C:\TEMP\[Товары.xls]Список'!$A$1:$A$10")

В данном случае не забудьте заключить в апострофы полный путь к файлу и имя листа.

P.S.

Минус всей этой системы только один - выпадающий список будет корректно работать только в том случае, если файл Товары.xls открыт

 

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

https://www.youtube.com/watch?v=itrkg9o9pAc

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

Видео

Шаг 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