Как сделать выпадающий список в Excel
Если вам часто приходиться вводить одни и те же значения в ячейки, то выпадающий список существенно облегчит вам работу.
Подобный список так же может быть полезен когда вам очень важно, чтобы в ячейках были только определенные значения (без ошибок, пробелов и т.д). Это может потребоваться, когда у вас идет проверка на наличие того или иного значения в ячейки и любое неправильно написанное слово, лишний пробел и т.д будут выдавать ошибку.
Сегодня мы с вами и научимся создавать выпадающие списки в Excel
Если вы уже ввели в столбце определенные значения и вам необходимо в следующую ячейку внести какое-либо значение из предыдущих, то достаточно нажать горячие клавиши Alt+стрелка_вниз – появится выпадающий список из значений которые вы вводили выше.
В простых условиях данным способом удобно пользоваться, но у него существует несколько недостатков.
1. Выпадающий список состоит только из элементов, которые вы уже вносили
2. Если вам потребуется внести значения не следующую ячейку, а например через одну (то есть одну ячейку оставить пустой), то у вас ничего не получится.
Второй способ лишен этих недостатков. Давайте разберем его подробнее. Посмотрите на рисунок ниже. В желтых ячейках нам необходимо сделать выпадающий список из списка указанных синим (Страны) и зеленным (Города)
Для начала разберемся со странами. Вставляем курсов в желтую ячейку B4, далее переходим в панель инструментов:
В Excel 2007/2010 Данные | Работа с данными | Проверка данных (смотрите рисунок ниже)
В Excel 2003 вам необходимо перейти в раздел Данные | Проверка (смотрите рисунок)
После этого откроется окно проверки вводимых значений. Из раскрывающегося списка необходимо выбрать пункт Список,
После этого появится возможность ввести источник списка, где вам необходимо указать на исходный список значений. В нашем случае это синий диапазон G5:G11, заключенный в доллары, так как диапазон у нас постоянный и он не должен сдвигаться. После нажатия на ОК вы получите выпадающий список, который так же как формулу можно протянуть вниз для других ячеек.
Аналогично поступим с городами, но теперь источник укажем в виде именованного диапазона. Для этого выделим исходный диапазон H5:H9, после этого перейдем в поле имени (смотрите рисунок) и присвоим данному диапазону имя: города (имя должно начинаться с буквы и не должно содержать пробелы), обязательно нажимаем после этого Enter.
В дальнейшем для редактирования именованного диапазона вы можете использовать диспетчер имен. Который находится в разделе Формулы | Определенные имена | Диспетчер имен
После этого становимся во вторую желтую ячейку C4, выбираем Данные | Работа с данными | Проверка данных, в раскрывающимся списке выбираем список, а в качестве источника указываем =города. Нажимаем Ок – получаем выпадающий список с использованием именованного диапазона.
В разделе Видео Уроки Вы можете найти Видео на эту тему.
Спасибо за внимание.
Спасибо за качественную статью!
Добрый день! Подскажите пожалуйста. К примеру у меня имеется товар с наименованием, ценой и артикулом. Соответственно я хочу создать таблицу с тремя столбцами с соответствующими названиями. Возможно ли сделать так, чтобы при указании в столбце «Артикул» номера, оставшиеся два поля автоматически заполнились нужным наименованием и ценой, либо в любом ином порядке?
Заранее спасибо!
Конечно, можно. Для этого используйте ВПР https://sirexcel.ru/priemi-excel/funktsiya_vpr_v_excel_s_primerom/
Искомое значение будет значение будет Артикул
Спасибо большое! С функцией впр более-менее разобрался. А если мне необходимо подставить в эту функцию значение из Листа2 и т.д.. То как должна выглядеть функция?
Без примера не понятно о чем вы.
Есть бланк заказа и есть прайс лист с продукцией. Хотелось бы сделать в бланке заказа в строке «Модель:» выпадающий список с общим названием товаров и при выборе этого товара в самой таблице в столбце «Наименование» в выпадающем списке предлагались уже конкретные наименования с артикулом и ценой. Возможно ли такое в Excel? И спасибо что отвечаете!
И все же лучше приложить пример файла. В раскрывающем списке вы выбираете модель и хотите, чтобы в бланке подтянулась вся информация (артикул, цена) из прайс листа по данной модели?
Вложение -лист.xlsx
Добрый день! Да, примерно так. Вот файл. Есть лист с бланком заказа и есть листы с товаром (фасады, декоративка и т.д.). В листе фасады, перечислены все модели и все имеющиеся у этой модели позиции (отображаются при раскрытии). Необходимо в бланке заказа в строке «Модели» выбрать в выпадающем списке, одну из имеющихся моделей, а далее уже непосредственно в самой таблице в выпадающих списках должны появляться все существующие позиции этой модели с наименованием, артикулом и ценой. Основная проблема это объем имеющихся наименований позиций. Хотелось бы знать возможно это осуществить. Если да то каким способом? Может сделаете пример?) Заранее огромное спасибо!
То есть нужно выбрать, например фасад Монтебьянко Золото 04WG
и в таблице должна отобразиться все
FIP0000000QD04WG
FIP0000000HQ04WG
FIP0000000IQ04WG
FIP0000000HR04WG
и т.д?
В принципе если добавить еще выбор типа (фасады, декоративка и т.д). то можно с помощью ДВССЫЛ https://sirexcel.ru/priemi-excel/ssylki-na-odnu-i-tu-zhe-yachejku-s-raznyx-listov/ выбрать нужный лист.
Потом с помощью ПОИСКПОЗ найти нужную нам модель. То есть строку, где она находится. https://sirexcel.ru/priemi-excel/funkciya-poiskpoz-v-excel/
значит на следующей строке начинается список. Конец списка можно найти например, когда в ячейке столбца «С» заканчиваются цифры. зная позиции всех строк артикулом можно с помощью ИНДЕКС
https://sirexcel.ru/priemi-excel/funkcii/funkciya-indeks-angl-index-v-excel-s-primerami/
их вытащить на бланк
Далее остальные параметры можно подтянуть тем же ИНДЕКС или ВПР.
Единственная проблема это то, что размер строк в бланке нужно будет сделать заведомо большой с пустыми строками — один на всех и он не будет уменьшаться от модели к модели. То есть может быть такое, что будет много пустых строк. Иначе требуется делать макросом.
Еще вопрос с ДВССЫЛ — будет ли он корректно работать с вложенными в него поиск и индекс, но вроде должен.
Сам не возьмусь. Времени нет
Спасибо большое!
Вложение -учёт.xlsx
Вопрос такой. В прикреплённом файле во вкладке приход в выпадающем списке в столбце «Наименование» ссылка ведёт на вкладку «Номенклатура». Так как эта вкладка постоянно меняется диапазон задан с запасом и поэтому в выпадающем списке много пустых строчек. Если диапазон в «Номенклатуре» сделать меньше то при каждом изменении этой вкладки надо будет и менять ссылку в выпадающем списке, что не удобно. Как можно избавиться от пустых строчек в выпадающем списке, но чтобы при изменении вкладки «Номенклатура» добавлялись позиции и в выпадающем окне?
Вложение
Вы можете выделить столбец с точками учета вместе с шапкой «Наименование». Далее форматируем ее как умную таблицу
Главной (Home) вкладке нажмите кнопку Форматировать как таблицу (Home — Format as Table). Дизайн можно выбрать любой — это роли не играет
На появившейся после превращения в Таблицу вкладке Конструктор (Design) можно изменить стандартное имя таблицы на свое (без пробелов!). По этому имени мы сможем потом адресоваться к таблице на любом листе этой книги. Я сделал название «ТочкиУчета».
После этого список делаете следующим образом (см. рисунок)