Связанные выпадающие списки
Выпадающие списки в Excel
Упражнение 1
Создание выпадающего списка в ячейке
1. Переименуйте один из рабочих листов в «Список» и создайте на нем список товаров (10-15 наименований).
2. Выделите ячейки с данными, которые должны попасть в выпадающий список (например, наименованиями товаров).
3. Выберите в меню Вставка - Имя - Присвоить (Insert - Name - Define) и введите имя (можно любое, но обязательно без пробелов!) для выделенного диапазона (например Товары). Нажмите ОК.
4. Выделите ячейки (можно сразу несколько), в которых хотите получить выпадающий список и выберите в меню Данные - Проверка (Data - Validation). На первой вкладке Параметры из выпадающего списка Тип данных выберите вариант Список и введите в строчку Источник знак равно и имя диапазона (т.е. =Товары).
Нажмите ОК.
Проверьте, действительно ли в выделенных диапазонах появились выпадающие списки.
Упражнение 2
Создание выпадающего списка в ячейке когда, когда данные для списка находятся в другом файле.
Для решения этой проблемы можно использовать функцию ДВССЫЛ (INDIRECT), чтобы сформировать правильную ссылку на внешний файл.
Например, если необходимо поместить в список содержимое ячеек А1:А10 из файла Товары.xls, нужно в поле Источник ввести следующую конструкцию:
=ДВССЫЛ("[Товары.xls]Список!$A$1:$A$10")
Функция ДВССЫЛ преобразует текстовую строку аргумента в реальный адрес, используемый для ссылки на данные. Обратите внимание, что имя файла заключается в квадратные скобки, а восклицательный знак служит разделителем имени листа и адреса диапазона ячеек.
Если файл с исходными данными для списка лежит в другой папке необходимо указать полный путь к файлу, например, следующим образом:
=ДВССЫЛ("'C:\TEMP\[Товары.xls]Список'!$A$1:$A$10")
В данном случае не забудьте заключить в апострофы полный путь к файлу и имя листа.
Создайте и продемонстрируйте работу такого списка.
Упражнение 3
Связанные выпадающие списки
Функция ДВССЫЛ (INDIRECT), умеет делать одну простую вещь - преобразовывать содержимое любой указанной ячейки в адрес диапазона, который понимает Excel. То есть, если в ячейке лежит текст "А1", то функция выдаст в результате ссылку на ячейку А1. Если в ячейке лежит слово "Маша", то функция выдаст ссылку на именованный диапазон с именем Маша и т.д. Такой, своего рода, "перевод стрелок" ;).
Помните!!! Ссылка на именованный диапазон доступна с любого листа книги..
Возьмем, например, вот такой список моделей автомобилей Toyota, Ford и Nissan:
Выделим весь список моделей Тойоты (с ячейки А2 и вниз до конца списка) и дадим этому диапазону имя Toyota в меню Вставка - Имя - Присвоить (Insert - Name - Define). Затем повторим то же самое со списками Форд и Ниссан, задав соответственно имена диапазонам Ford и Nissan.
Теперь создадим первый выпадающий список для выбора марки автомобиля. Выделите пустую ячейку (у меня выделена F3) и откройте меню Данные - Проверка (Data - Validation), затем из выпадающего списка Тип данных выберите вариант Список и в поле Источник - выделите ячейки с названиями марок (желтые ячейки в нашем примере, т.е. диапазон для списка А1:С1). После нажатия на ОК первый выпадающий список готов:
Теперь создадим второй выпадающий список, в котором будут отображаться модели выбранной в первом списке марки. Точно так же, как в предыдущем случае, выделите пустую ячейку и откройте меню Данные - Проверка - далее Список. В поле Источник нужно будет ввести вот такую формулу:
=ДВССЫЛ(F3)
где F3 - адрес ячейки с первым выпадающим списком - замените на свой.
Все. После нажатия на ОК содержимое второго списка будет выбираться по имени диапазона, выбранного в первом списке.
Создайте и продемонстрируйте работу такого списка.
Упражнение 4
Создайте и продемонстрируйте работу списка аналогичного созданному в упражнении 3, но данные для списка и выпадающие списки должны находиться на разных листах.