СМ 11 Функции ВПР, ГПР и Индекс + Поле со списком
Поле со списком: Элемент управления
Вставка на лист нового объекта - элемента управления "поле со списком" с последующей привязкой его к диапазонам на листе. Для этого:
В Excel 2007/2010 откройте вкладку Разработчик (Developer). В более ранних версиях - панель инструментов Формы (Forms) через меню Вид - Панели инструментов - Формы (View - Toolbars - Forms). Если этой вкладки не видно, то нажмите кнопку Офис - Параметры Excel - флажок Отображать вкладку Разработчик на ленте (Office Button - Excel Options - Show Developer Tab in the Ribbon)
Найдите значок выпадающего списка среди элементов управления форм (не ActiveX!). Ориентируйтесь по всплывающим подсказкам - Поле со списком:
Щелкните
по значку и нарисуйте небольшой
горизонтальный прямоугольник - будущий
список.
Щелкните по нарисованному списку правой кнопкой мыши и выберите команду Формат объекта (Format control). В появившемся диалоговом окне задайте
Формировать список по диапазону - выделите ячейки с наименованиями товаров, которые должны попасть в список
Связь с ячейкой - укажите ячейку куда нужно выводить порядковый номер выбранного пользователем элемента.
Количество строк списка - сколько строк показывать в выпадающем списке. По умолчанию - 8, но можно больше, чего не позволяет предыдущий способ.
После нажатия на ОК списком можно пользоваться.
Чтобы вместо порядкового номера элемента выводилось его название можно дополнительно использовать функцию ИНДЕКС (INDEX), которая умеет выводить содержимое нужной по счету ячейки из диапазона:
Создание выпадающего списка значений в ячейках Excel 2007
1 Июля 2010
При работе с таблицами Excel иногда возникает потребность заполнить большое количество ячеек определенными значениями, список которых заранее известен. Иногда для этого достаточного использовать функцию автозаполнения ячеек в excel. Но в случаях, когда количество возможных значений ячейки заметно меньше количества заполняемых ячеек, ввод каждого значения будет не оптимальным решением. Заметно быстрее будет создать выпадающий список и выбирать значение одним кликом мышки.
Для того, чтобы создать выпадающий список нужно создать источник данных со значениями для списка. Для этого на отдельном листе книги Excel создаем столбец с всеми нужными вариантами значений (Рис.1).
Рис.1
Теперь нужно выделить список значений (без заголовка) и на вкладке «Формулы», нажать кнопку «Присвоить имя» (Рис.2).
Рис.2
В появившемся окне заполняем поле «Имя» и жмем Ok (Рис.3).
Рис.3
Далее возвращаемся на лист с основными данными и выделяем ячейки, значения которых будут выбираться из выпадающего списка (Рис.4).
Рис.4
Теперь на вкладке «Данные» жмем кнопку «Проверка данных» (Рис.5).
Рис.5
В появившемся окне в поле «Тип данных» выбираем значение «Список», в поле «Источник:» вводим знак “=” (равно) и набираем имя списка назначенного нами на Рис.3. Далее жмем Ok (Рис.6).
Рис.6
Теперь, если вы выделите одну из ячеек, для которых создавался список, то справа от ячейки появится кнопка с маленьким треугольником. Нажав на этот треугольник вы увидите список значений для этой ячейки и сможете быстро выбрать нужное значение (Рис.7).
Рис.7
Функция ВПР (VLOOKUP)
Функции ВПР() и ГПР() часто используются для поиска данных в диапазонах, содержащих справочную информацию. Например, у нас есть таблица, содержащая в первом столбце наименование изделия, а во втором - его цену. Тогда с помощью функции ВПР(), задав наименование, можно найти строку, содержащую наименование изделия и в качестве результата получить цену.
Схематично, можно представить так:
