Как сделать выпадающий список в Excel

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

Подобный список так же может быть полезен когда вам очень важно, чтобы в ячейках были только определенные значения (без ошибок, пробелов и т.д. ). Это может потребоваться, когда у вас идет проверка на наличие того или иного значения в ячейки и любое неправильно написанное слово, лишний пробел и т.д. будут выдавать ошибку.

Сегодня мы с вами и научимся создавать выпадающие списки в Excel

Если вы уже ввели в столбце определенные значения и вам необходимо в следующую ячейку внести какое-либо значение из предыдущих, то достаточно нажать горячие клавиши Alt+стрелка_вниз – появится выпадающий список из значений которые вы вводили выше.

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

1. Выпадающий список состоит только из элементов, которые вы уже вносили

2. Если вам потребуется внести значения не следующую ячейку, а например через одну (то есть одну ячейку оставить пустой), то у вас ничего не получится.

Второй способ лишен этих недостатков. Давайте разберем его подробнее. Посмотрите на рисунок ниже. В желтых ячейках нам необходимо сделать выпадающий список из списка указанных синим (Страны) и зеленным (Города)

spisok-dannye

Для начала разберемся со странами. Вставляем курсов в желтую ячейку B4, далее переходим в панель инструментов:

В Excel 2007/2010 Данные | Работа с данными | Проверка данных (смотрите рисунок ниже)

spisok-panel

В Excel 2003 вам необходимо перейти в раздел Данные | Проверка (смотрите рисунок)

spisok-panel_2003

После этого откроется окно проверки вводимых значений. Из раскрывающегося списка необходимо выбрать пункт Список,

spisok-proverka

После этого появится возможность ввести источник списка, где вам необходимо указать на исходный список значений. В нашем случае это синий диапазон G5:G11, заключенный в доллары, так как диапазон у нас постоянный и он не должен сдвигаться. После нажатия на ОК вы получите выпадающий список, который так же как формулу можно протянуть вниз для других ячеек.

spisok-istochnik

Аналогично поступим с городами, но теперь источник укажем в виде именованного диапазона. Для этого выделим исходный диапазон H5:H9, после этого перейдем в поле имени (смотрите рисунок) и присвоим данному диапазону имя: города (имя должно начинаться с буквы и не должно содержать пробелы), обязательно нажимаем после этого Enter.

 

spisok-istochnik2

В дальнейшем для редактирования именованного диапазона вы можете использовать диспетчер имен. Который находится в разделе Формулы | Определенные имена | Диспетчер имен

dispetcher-imen

После этого становимся во вторую желтую ячейку C4, выбираем  Данные | Работа с данными | Проверка данных, в раскрывающимся списке выбираем список, а в качестве источника указываем =города. Нажимаем Ок – получаем выпадающий список с использованием именованного диапазона.

spisok-istochnik3

В разделе Видео Уроки Вы можете найти Видео на эту тему.

Спасибо за внимание.

Поделиться:
11 Комментариев
  • Дмитрий says:

    Добрый день! Подскажите пожалуйста. К примеру у меня имеется товар с наименованием, ценой и артикулом. Соответственно я хочу создать таблицу с тремя столбцами с соответствующими названиями. Возможно ли сделать так, чтобы при указании в столбце «Артикул» номера, оставшиеся два поля автоматически заполнились нужным наименованием и ценой, либо в любом ином порядке?

    Заранее спасибо!

    • excel says:

      Конечно, можно. Для этого используйте ВПР sirexcel.ru/priemi-excel/...xcel_s_primerom/

      Искомое значение будет значение будет Артикул

      • Дмитрий says:

        Спасибо большое! С функцией впр более-менее разобрался. А если мне необходимо подставить в эту функцию значение из Листа2 и т.д. .. То как должна выглядеть функция?

        • excel says:

          Без примера не понятно о чем вы.

          • Дмитрий says:

            Есть бланк заказа и есть прайс лист с продукцией. Хотелось бы сделать в бланке заказа в строке "Модель: " выпадающий список с общим названием товаров и при выборе этого товара в самой таблице в столбце «Наименование» в выпадающем списке предлагались уже конкретные наименования с артикулом и ценой. Возможно ли такое в Excel? И спасибо что отвечаете!

            • excel says:

              И все же лучше приложить пример файла. В раскрывающем списке вы выбираете модель и хотите, чтобы в бланке подтянулась вся информация (артикул, цена) из прайс листа по данной модели?

              • Дмитрий says:

                Вложение  -лист.xlsx

                Добрый день! Да, примерно так. Вот файл. Есть лист с бланком заказа и есть листы с товаром (фасады, декоративка и т.д. ). В листе фасады, перечислены все модели и все имеющиеся у этой модели позиции (отображаются при раскрытии). Необходимо в бланке заказа в строке «Модели» выбрать в выпадающем списке, одну из имеющихся моделей, а далее уже непосредственно в самой таблице в выпадающих списках должны появляться все существующие позиции этой модели с наименованием, артикулом и ценой. Основная проблема это объем имеющихся наименований позиций. Хотелось бы знать возможно это осуществить. Если да то каким способом? Может сделаете пример?) Заранее огромное спасибо!

                • excel says:

                  То есть нужно выбрать, например фасад Монтебьянко Золото 04WG

                  и в таблице должна отобразиться все

                  FIP0000000QD04WG

                  FIP0000000HQ04WG

                  FIP0000000IQ04WG

                  FIP0000000HR04WG

                  и т.д. ?

                  В принципе если добавить еще выбор типа (фасады, декоративка и т.д. ). то можно с помощью ДВССЫЛ sirexcel.ru/priemi-excel/...s-raznyx-listov/ выбрать нужный лист.

                  Потом с помощью ПОИСКПОЗ найти нужную нам модель. То есть строку, где она находится. sirexcel.ru/priemi-excel/...oiskpoz-v-excel/

                  значит на следующей строке начинается список. Конец списка можно найти например, когда в ячейке столбца «С» заканчиваются цифры. зная позиции всех строк артикулом можно с помощью ИНДЕКС

                  sirexcel.ru/priemi-excel/...cel-s-primerami/

                  их вытащить на бланк

                  Далее остальные параметры можно подтянуть тем же ИНДЕКС или ВПР.

                  Единственная проблема это то, что размер строк в бланке нужно будет сделать заведомо большой с пустыми строками — один на всех и он не будет уменьшаться от модели к модели. То есть может быть такое, что будет много пустых строк. Иначе требуется делать макросом.

                  Еще вопрос с ДВССЫЛ — будет ли он корректно работать с вложенными в него поиск и индекс, но вроде должен.

                  Сам не возьмусь. Времени нет

                  • Дмитрий says:

                    Спасибо большое!

  • Максим says:

    Вложение  -учёт.xlsx

    Вопрос такой. В прикреплённом файле во вкладке приход в выпадающем списке в столбце «Наименование» ссылка ведёт на вкладку «Номенклатура». Так как эта вкладка постоянно меняется диапазон задан с запасом и поэтому в выпадающем списке много пустых строчек. Если диапазон в «Номенклатуре» сделать меньше то при каждом изменении этой вкладки надо будет и менять ссылку в выпадающем списке, что не удобно. Как можно избавиться от пустых строчек в выпадающем списке, но чтобы при изменении вкладки «Номенклатура» добавлялись позиции и в выпадающем окне?

    • excel says:

      Вложение

      Вы можете выделить столбец с точками учета вместе с шапкой «Наименование». Далее форматируем ее как умную таблицу

      Главной (Home) вкладке нажмите кнопку Форматировать как таблицу (Home — Format as Table). Дизайн можно выбрать любой — это роли не играет

      На появившейся после превращения в Таблицу вкладке Конструктор (Design) можно изменить стандартное имя таблицы на свое (без пробелов!). По этому имени мы сможем потом адресоваться к таблице на любом листе этой книги. Я сделал название «ТочкиУчета».

      После этого список делаете следующим образом (см. рисунок)

Добавить комментарий

Ваш e-mail не будет опубликован. Все поля обязательны для заполнения.