Как сделать зависимый выпадающий список в excel?

Б. Ввод элементов списка в диапазон (на том же листе, что и выпадающий список)

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

Предположим, что элементы списка шт;кг;кв.м;куб.м введены в ячейки диапазона A1:A4, тогда поле Источник будет содержать =лист1!$A$1:$A$4

Преимущество: наглядность перечня элементов и простота его модификации. Подход годится для редко изменяющихся списков. Недостатки: если добавляются новые элементы, то приходится вручную изменять ссылку на диапазон. Правда, в качестве источника можно определить сразу более широкий диапазон, например, A1:A100. Но, тогда выпадающий список может содержать пустые строки (если, например, часть элементов была удалена или список только что был создан). Чтобы пустые строки исчезли необходимо сохранить файл.

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

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

Способ 3 — как в excel сделать выпадающий список с использованием ActiveX

Чтобы воспользоваться этим способом, необходимо чтобы у вас была включена вкладка «РАЗРАБОТЧИК». По умолчанию эта вкладка отсутствует. Чтобы ее включить:

1. Нажмите на «Файл» в левом верхнем углу приложения.
2. Выберите пункт «Параметры» и нажмите на него.
3. В окне настройки параметров Excel во вкладке «Настроить ленту» поставьте галочку напротив вкладки «Разработчик».

Включение вкладки «РАЗРАБОТЧИК»

Теперь вы сможете воспользоваться инструментом «Поле со списком (Элемент ActiveX)». Во вкладке «РАЗРАБОТЧИК» нажмите на кнопку «Вставить» и найдите в элементах ActiveX кнопку «Поле со списком (Элемент ActiveX)». Нажмите на нее.

Нарисуйте данный объект в excel выпадающий список в ячейке, где вам необходим выпадающий список.

Теперь необходимо настроить данный элемент. Чтобы это сделать, необходимо включить «Режим конструктора» и нажать на кнопку «Свойства». У вас должно открыться окно свойств (Properties).

С открытым окном свойств нажмите на ранее созданный элемент «Поле со списком». В списке свойств очень много параметров для настройки и вы сможете изучив их, настроить очень много начиная от отображения списка до специальных свойств данного объекта.

Но нас на этапе создания интересуют только три основных:

1. ListFillRange — указывает диапазон ячеек, из которых будут браться значения для выпадающего списка. В моем примере я указал два столбца (A2:B7 — дальше покажу как это использовать). Если необходимо только одни значения указывается A2:A7.
2. ListRows — количество данных в выпадающем списке. Элемент ActiveX отличается от первого способа тем, что можно указать большое количество данных.
3. ColumnCount — указывает сколько столбцов данных указывать в выпадающем списке.

В строке ColumnCount я указал значение 2 и теперь в списке выпадающие данные выглядят вот так:

Как видите получился выпадающий список в excel с подстановкой данных из второго столбца с данными «Поставщик».

Поделиться «3 способа как в экселе сделать выпадающий список»

Делаем выпадающий список в ячейке эксель с выбором по первой букве

Быстро понимаешь, что хорошо бы иметь возможность выбора из списка по первой букве, тогда процесс идет гораздо быстрее. Следующий способ познакомит вас с разработкой в эксель. Сначала Включим в настройках Excel режим конструктора через «Параметры».

В режиме конструктора вставим в наш документ элемент управления Active X «Поле со списком«:

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

• ListFillRange — сюда вставляем наш именованный диапазон из «диспетчера имен»;
• LinkedCell — связанная ячейка, куда будет выводиться выбранный из списка элемент — нужно, если данные ее будут использоваться в других таблицах, формулах и т.д;
• ListRows — количество отображаемых строк в списке;
• Font — выбираем шрифт, размер, стиль;
• ForeColor и BackColor — цвет текста и фона;
• ListRow — количество возвращаемых строк в списке;
• PrintObject — определяет — выводить элемент управления на печать или нет (истина или ложь)

Пока активен режим конструктора — вы не сможете работать со списком как пользователь. Но вы можете изменять его размеры , свойства , положение на листе. После окончания настроек нужно выключить режим конструктора.

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

Способ 2 — самый удобный, простой и наиболее гибкий

Данный способ предполагает создание отдельных данных для списка. При чем данные могут находится как на листе с таблицей, так и на другом листе файла Excel.

1. Сперва необходимо создать список данных, который будет источником данных для подстановки в выпадающий список в excel. Выделите данные и нажмите правой кнопкой мыши. В выпадающем списке выберите пункт «Присвоить имя…».

Создание набора данных для списка

Введите имя для набора данных

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

Для создания проверки вводимых значений введите имя ранее созданного списка

При попытке ввести значение, которого нет в заданном списке, эксель выдаст ошибку.

Кроме списка можно вводить данные вручную. Если введенные данные не совпадут с одним из данных — программа выдаст ошибку

А при нажатии на кнопку выпадающего списка в ячейке вы увидите перечень значений из созданного ранее.

ЧаВо

— Как удалить данные из ячейки, если в ней выпадающий список, а нужно сделать пустую ячейку.

— Нужно щелкнуть по ячейке и нажать клавишу DEL.

— Как добавить в список новые значения для выбора или удалить ненужные?

— Для этого нужно отредактировать список значений списка (в нашем случае в столбце Н) и заново выбрать диапазон значений в окне Настройка вводимых значений.

— Как вообще удалить выпадающий список, а не только одно значение?

— Зайдите Данные — Проверка данных — Тип данных: Любое значение.

— Как сделать выпадающий список с данными, расположенными на другой странице?

— Ранее мы говорили, что удобно использовать несколько листов Excel для разных данных, например, для основной таблицы использовать Лист1, а для всех данных, из которых формируются выпадающие списки, можно использовать Лист 2.

Как сделать выпадающий список из данных, расположенных на другом листе? Нужно диапазону с вариантами выпадающего списка присвоить имя (Выделить диапазон — Формулы — Присвоить имя).

А затем на Шаге 3 в качестве источника указать это имя диапазона.

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

Как создать связанный список Excel?

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

Это можно сделать разными методами.

Метод 1: Функция ДВССЫЛ

В первом случае надо применить функцию ДВССЫЛ, способную преобразовывать в ссылку любой текст. Например, если написано A1, то функция вернет ссылку на ячейку, имеющую такой же адрес. Если же в ячейке написан текст «Маша», то эта формула сможет сделать линк на именованный диапазон с таким названием. Проще говоря, она может менять ссылку и название местами.

Предположим, нам надо составить такой перечень моделей марок Toyota, Ford, Nissan.

Модельный ряд Toyota нужно выделить, начиная ячейкой A2 вплоть до конца перечня и назвать его аналогично. Если используется древняя версия Excel, эта функция реализуется через меню «Вставка – Имя – Присвоить». Если версия Excel не самая старая (начиная с 2007), то нужно перейти во вкладку «Формулы», где отыскать «Диспетчер имен». Аналогичная операция проводится и с перечнем модельного ряда других производителей автомобилей.

Во время присвоения имен важно не забывать: нельзя составлять имена диапазонов с пробелами и знаками препинания. Также нельзя начинать их с цифры

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

Теперь нужно сгенерировать перечень автомобилей. Нужно нажать мышью (левой кнопкой) по пустой ячейке и открыть меню Данные – Проверка для Excel 2003 версии и кнопку «Проверка данных» на вкладке «Данные» для Excel 2007 версии и более современных. Затем в перечне «Тип данных» нужно выбрать Список, и в поле «Источник» написать ячейки с названиями марок.

Теперь следует сформировать второй список, где будут перечисляться машины из модельного ряда. Последовательность действий такая же, но с тем лишь исключением, что в поле Источник нужно указать формулу =ДВССЫЛ(F3). Естественно, в скобках нужно указать адрес подходящей именно в вашем случае ячейки. Все, после того, как вы нажмете ОК, формирование второго перечня будет осуществляться, исходя из данных в первом.

Но такой метод имеет ряд существенных недостатков:

1. Нельзя использовать в качестве зависимых списков те, которые задаются функциями типа СМЕЩ.
2. Названия элементов первичного выпадающего диапазона возможно указывать исключительно без пробелов, поэтому их придется заменять на нижнее подчеркивание.
3. Необходимость создания большого количества именованных диапазонов. особенно это неудобно, если есть множество марок автомобилей.

Использование функции СМЕЩ и ПОИСКПОЗ

Чтобы использовать этот метод формирования зависимых списков, нужно иметь отсортированный перечень соответствий марка-модель, как показано на данном примере.

Для формирования первого списка, который будет влиять на содержимое второго, подойдет и стандартный способ, описанный выше, то есть:

1. Открыть диспетчер имен и дать имя соответствующему диапазону.
2. Нажать по вкладке «Данные», где выбрать команду «Проверка данных».
3. Выбрать, а в качестве диапазона, который служит источником, указать соответствующее имя или выделить необходимые ячейки.

А вот для второго списка, на который будет влиять первый, придется выполнять другие действия, а именно создать именованный диапазон с функцией =СМЕЩ, создающей ссылку на ячейку, где указаны исключительно модели определенной марки. А для этого осуществляются следующие действия:

1. Надо нажать комбинацию Ctrl + F3. Также можно открыть диспетчер имен и присвоить имя соответствующему диапазону.
2. Создать новый диапазон вместе с функцией СМЕЩ в ссылке и присвоить ему имя. Оно может быть каким-угодно, например, «Модели».

В нашей ситуации формула будет следующей:

=СМЕЩ($A$1;ПОИСКПОЗ($G$7;$A:$A;0)-1;1;СЧЁТЕСЛИ($A:$A;$G$7);1)

Важно указывать только абсолютные ссылки. То есть, в них надо указать знак $

После нажатия клавиши Enter, Excel автоматически добавит имена листов. Это не должно вас пугать.

Результат будет следующий:

Наконец, нужно вставить выпадающий список, базируясь на вышеприведенной формуле. Для этого, в описываемом нами примере, нужно выполнить следующие действия:

1. Выделить ячейку G8.
2. Выбрать команду «Проверка данных» на вкладке «Данные».
3. Там, где источник списка, указать имя диапазона: =Модели.

И все!

B. Ввод элементов списка в диапазон (на любом листе)

В правилах Проверки данных (также как и Условного форматирования) нельзя впрямую указать ссылку на диапазоны другого листа (см. Файл примера ):

Пусть ячейки, которые должны содержать Выпадающий список, размещены на листе Пример,

а диапазон с перечнем элементов разместим на другом листе (на листе Список в файле примера ).

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

Используем именованный диапазон Создадим Именованный диапазон Список_элементов, содержащий перечень элементов выпадающего списка (ячейки A1:A4 на листе Список). Для этого:

• выделяем А1:А4,
• нажимаем Формулы/ Определенные имена/ Присвоить имя
• в поле Имя вводим Список_элементов, в поле Область выбираем Книга;

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

• вызываем Проверку данных;
• в поле Источник вводим ссылку на созданное имя: =Список_элементов .

Примечание Если предполагается, что перечень элементов будет дополняться, то можно сразу выделить диапазон большего размера, например, А1:А10. Однако, в этом случае Выпадающий список может содержать пустые строки.

Избавиться от пустых строк и учесть новые элементы перечня позволяет Динамический диапазон. Для этого при создании Имени Список_элементов в поле Диапазон необходимо записать формулу = СМЕЩ(Список!$A$1;;;СЧЁТЗ(Список!$A:$A))

Использование функции СЧЁТЗ() предполагает, что заполнение диапазона ячеек (A:A), который содержит элементы, ведется без пропусков строк (см. файл примера , лист Динамический диапазон).

Используем функцию ДВССЫЛ()

Альтернативным способом ссылки на перечень элементов, расположенных на другом листе, является использование функции ДВССЫЛ() . На листе Пример, выделяем диапазон ячеек, которые будут содержать выпадающий список, вызываем Проверку данных, в Источнике указываем =ДВССЫЛ(«список!A1:A4») .

Недостаток: при переименовании листа – формула перестает работать. Как это можно частично обойти см. в статье Определяем имя листа.

Ввод элементов списка в диапазон ячеек, находящегося в другой книге

Если необходимо перенести диапазон с элементами выпадающего списка в другую книгу (например, в книгу Источник.xlsx), то нужно сделать следующее:

• в книге Источник.xlsx создайте необходимый перечень элементов;
• в книге Источник.xlsx диапазону ячеек содержащему перечень элементов присвойте Имя, например СписокВнеш;
• откройте книгу, в которой предполагается разместить ячейки с выпадающим списком;
• выделите нужный диапазон ячеек, вызовите инструмент Проверка данных, в поле Источник укажите = ДВССЫЛ(«лист1!СписокВнеш») ;

При работе с перечнем элементов, расположенным в другой книге, файл Источник.xlsx должен быть открыт и находиться в той же папке, иначе необходимо указывать полный путь к файлу. Вообще ссылок на другие листы лучше избегать или использовать Личную книгу макросов Personal.xlsx или Надстройки.

Если нет желания присваивать имя диапазону в файле Источник.xlsx, то формулу нужно изменить на = ДВССЫЛ(«лист1!$A$1:$A$4»)

СОВЕТ: Если на листе много ячеек с правилами Проверки данных, то можно использовать инструмент Выделение группы ячеек ( Главная/ Найти и выделить/ Выделение группы ячеек ). Опция Проверка данных этого инструмента позволяет выделить ячейки, для которых проводится проверка допустимости данных (заданная с помощью команды Данные/ Работа с данными/ Проверка данных ). При выборе переключателя Всех будут выделены все такие ячейки. При выборе опции Этих же выделяются только те ячейки, для которых установлены те же правила проверки данных, что и для активной ячейки.

Примечание : Если выпадающий список содержит более 25-30 значений, то работать с ним становится неудобно. Выпадающий список одновременно отображает только 8 элементов, а чтобы увидеть остальные, нужно пользоваться полосой прокрутки, что не всегда удобно.

В EXCEL не предусмотрена регулировка размера шрифта Выпадающего списка. При большом количестве элементов имеет смысл сортировать список элементов и использовать дополнительную классификацию элементов (т.е. один выпадающий список разбить на 2 и более).

Например, чтобы эффективно работать со списком сотрудников насчитывающем более 300 сотрудников, то его следует сначала отсортировать в алфавитном порядке. Затем создать выпадающий список, содержащий буквы алфавита. Второй выпадающий список должен содержать только те фамилии, которые начинаются с буквы, выбранной первым списком. Для решения такой задачи может быть использована структура Связанный список или Вложенный связанный список.

Как назначать горячие клавиши для создания собственного набора

Создавать собственные клавиши быстрого доступа в программе Excel нельзя. Это не касается макросов, для написания которых нужно разбираться в коде, правильно выставить их на панель быстрого доступа. Из-за этого пользователю доступны только базовые команды, которые были описаны выше. Из комбинаций клавиш нужно выбрать те команды, которые используются или будут применяться очень часто. После этого их желательно добавить на панель быстрого доступа. В нее можно вынести любой инструмент с различных блоков, чтобы не искать его в дальнейшем. Процесс назначения горячих клавиш состоит из нескольких этапов:

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

1. На экране должно появиться окно настройки для назначения, изменения быстрых клавиш. Среди предложенных команд нужно выбрать “VBA-Excel”.

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

После этого на панели быстрого доступа появится быстрая клавиша выбранной команды. Для того чтобы активировать добавленную команду, проще всего кликнуть по ней ЛКМ. Однако есть и другой способ. Можно воспользоваться комбинацией клавиш, где первая кнопка ALT, следующая кнопка – цифра команды, как она идет по счету в панели быстрого доступа.

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

Как в экселе сделать выпадающий список в ячейке с выбором нескольких данных

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

Сначала сделаем в листе «база» наши будущие списки. У нас будет три категории товара — полуфабрикаты, рыба и хлебобулочные. И мы заполним ими три графы.

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

Нажимаем клавишу Enter. Убеждаемся, что в нашей таблице на другом листе в нужных выделенных графах появилась возможность выбора наших категорий из списка.

Кстати, получившийся результат можно копировать в нижние ячейки -как обычные формулы. Работаем далее. Сначала займемся доработкой наших трех списков на листе «База». Доработаем сначала графу «Полуфабрикаты». Сделаем это, чтобы позже можно было добавлять в «базу» новые позиции, а они автоматом отображались бы в списках.

Ставим курсор в строку из «Полуфабрикаты, затем открываем вкладку «Главная» и жмем на кнопку «Форматировать как таблицу». Дизайн выбираем любой. Задаем по столбцу нужный вертикальный диапазон, включая только строки с наименованиями; ничего кроме диапазона не меняем.

В процессе этих действий нам стал доступен «конструктор таблиц». Обязательно присваиваем нашей( как бы созданной) таблице имя (в левом верхнем углу) «Полуфабрикаты».

Повторяем действо для граф «Рыба» и «Хлебобулочные».

Завешающий этап. Встаем в нужную строку таблицы, как обычно идем в «Данные»-«Проверка данных», выбираем тип «Список» и в качестве диапазона указываем на соседнюю ячейку , т.е на ранее созданный список, но…

… с использованием в «Источнике» функции ДВССЫЛ. «=ДВССЫЛ($Е$6)». Эта функция возвращает ссылку на наши диапазоны т.е. предыдущие списки, а не содержимое ячейки. Мы работаем с данными, в функцию просто их подставили. Наименование вводится вручную.

Обратите внимание на «источник». Указана абсолютная адресация столбца и ячейки (через знаки «$»)

Чтобы распространить в таком виде на остальные строки, нужно будет указывать для каждой строки свой источник данных. Это долго. Можно аккуратно убрать абсолютную адресацию руками из «источника» и после скопировать ячейку на остальные строки ниже. Тогда «формула» распространится корректно.

Попробуйте добавить в базу новые позиции; проверьте — появляются ли они теперь автоматически в списке после указании категорий? Должно получиться.

Связанный выпадающий список

1. Подготовить таблицу. В первой строке каждого столбца должна быть категория меню (для удобства).
2. Задать диапазонам с перечислением блюд имя в соответствии с его категорией. Общее название каждого диапазона должно четко совпадать с тем, что записано в первой ячейке каждого столбца.
3. На отдельной области создать раскрывающийся список через пункт меню «Данные» — «Проверка данных». Источник – первая ячейка каждого столбца.
4. Далее перейти на формирование перечисления блюд одной из категорий. В этом поможет функция ДВССЫЛ (на англ. INDIRECT), которая преобразовывает текст в обычную экселевскую ссылку и источник данных для него. Нужно выбрать категорию, повторно открыть «Проверку данных» и написать функцию =ДВССЫЛ(H2).

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

Пробелы в названии при создании связанного выпадающего списка

Именуя диапазон значений, важно использовать вместо пробела нижнее подчеркивание «_». Например, «Мясные_блюда»

Однако есть вероятность упустить данное правило в одном из названий, и в результате будет ошибка при выполнении операции. Можно в самих названиях использовать обычный пробел, а в момент подстановки в список, поменять его на подчеркивание, используя формулу ПОДСТАВИТЬ:

=ПОДСТАВИТЬ(F3;» «;»_») без кавычек.

Итоговая формула выглядит так:

=ДВССЫЛ(ПОДСТАВИТЬ($F$3;» «;»_»)) без кавычек.

Стоит обратить внимание на отсутствие пробелов в названии в начале и в конце, чтобы избежать некорректного вывода заголовков. Автоматизировать данный процесс при построении имени также можно посредством функции:. =ДВССЫЛ(ПОДСТАВИТЬ(СЖПРОБЕЛЫ($F$3);» «;»_»)) без кавычек

=ДВССЫЛ(ПОДСТАВИТЬ(СЖПРОБЕЛЫ($F$3);» «;»_»)) без кавычек.

Работа с файлами

С помощью всего нескольких комбинаций клавиш можно открыть, сохранить или создать файл.

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

Также вам могут быть интересны такие статьи:

• Формулы EXCEL с примерами — Инструкция по применению
• Сводные таблицы Excel — Примеры создания
• Округление в Excel — Пошаговая инструкция

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

Также пользователи табличного процессора могут использовать горячие клавиши для функции поиска. Поиск в экселе стилизован под специальную клавишу на главной панели инструментов.

Меню выделения и поиска по документу

Окно поиска с последующей заменой поможет сократить время на редактирование документа.

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

Чтобы найти или заменить элементы, следуйте инструкции:

• Нажмите CTRL+F или меню поиска на панели инструментов;
• В открывшемся перейдите на вкладку поиска, если вам просто нужно найти объект или на вкладку «найти-заменить», если необходимо осуществить поиск в документе с последующей заменой найденных данных;

Диалоговое окно «найти-заменить»

• С помощью дополнительных функций можно указать особые параметры поиска, к примеру, формат ячеек, место и область поиска;
• После того, как ячейка с исходными данными была найдена, кликните на клавишу заменить.

Стандартный метод

1. Выделите ячейки, где указаны данные, которые будут источником для выпадающего списка (в случае с нашей таблицей, это – наименования товаров).
2. Дальнейшие действия зависят от версии Excel. Если она 2003 года (да, ею еще до сих пор пользуются), нужно пройти по пути «Вставка – Имя – Присвоить». В случае использования более новой версии программы нужно нажать по вкладке «Формулы», где находится Диспетчер имен. После клика по ней нужно найти кнопку «Создать» и также нажать левой кнопкой мыши по ней. После этого нужно ввести имя, соответствующее следующим требованиям:
   1. отсутствие пробелов.
   2. начинается со знака !.
3. Подтвердите настройки путем нажатия по клавише «ОК».
4. Выберите диапазон, который будет использоваться в качестве источника данных для списка, и нажмите по кнопке «Данные» на ленте, где вам нужно нажать на пункт «Проверить». Появится список, в котором вам нужно выбрать тип данных. В нашем примере это «Список». Затем укажите в поле символ “=”, и название диапазона. В нашем случае =Товары.

Далее нужно кликнуть по кнопке «ОК», и ячейка превратится в список, появляющийся при нажатии по ней.

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

Тогда когда появляются новые элементы, они автоматом добавляются к перечню.

Списки этого типа могут связываться с другими.

Выбор нескольких значений из выпадающего списка Excel

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

1. Создаем стандартный список с помощью инструмента «Проверка данных». Добавляем в исходный код листа готовый макрос. Как это делать, описано выше. С его помощью справа от выпадающего списка будут добавляться выбранные значения.
2. Чтобы выбранные значения показывались снизу, вставляем другой код обработчика.
3. Чтобы выбираемые значения отображались в одной ячейке, разделенные любым знаком препинания, применим такой модуль. Private Sub Worksheet_Change( ByVal Target As Range) On Error Resume Next If Not Intersect(Target, Range( «C2:C5» )) Is Nothing And Target.Cells.Count = 1 Then Application.EnableEvents = False newVal = Target Application.Undo oldval = Target If Len(oldval) 0 And oldval newVal Then Target = Target & «,» & newVal Else Target = newVal End If If Len(newVal) = 0 Then Target.ClearContents Application.EnableEvents = True End If End Sub

Не забываем менять диапазоны на «свои». Списки создаем классическим способом. А всю остальную работу будут делать макросы.

Примеры выпадающих списков в Excel

#1. Стандартный

Выделяем ячейку (диапазон ячеек), где должен всплывать выпадающий список (в нашем примере это вкладка «Проект», диапазон ячеек A2:A25), переходим в раздел «Проверка данных» (описано выше), выбираем тип данных «Список», в поле «Источник» вставляем диапазон с источника. 

Если вам нужно ввести подсказки и настроить вывод сообщения об ошибке, переходим в соответствующие разделы и прописываем необходимые свойства. Затем нажимаем «ОК».

Как видим, при выделении ячейки в диапазоне A2:A25 во вкладке «Проект», у нас появился список значений.

#2. Список с подстановкой данных

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

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

Настроим проверку данных иначе. Для автодополнения списков рассмотрим 2 варианта:

#1. Умная таблица. Выделяем диапазон с источником, переходим во вкладку на панели инструментов «Главная», раздел «Стили», раскрываем меню «Форматировать как таблицу» и выбираем понравившийся стиль умной таблицы Excel. 

Подробнее о том, что такое  «Умные таблицы» и как с ними работать — на наших курсах. 

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

Чтобы выпадающий список в Excel стал динамическим, выделяем любую из ячеек, где он находится, переходим в раздел «Проверка данных». Нам подтянется текущее условие проверки. 

В строке с источником прописываем ссылку на столбец таблицы с использованием функции ДВССЫЛ: =ДВССЫЛ(«Товары»). Далее отмечаем «Распространить изменения на другие ячейки с тем же условием», и нажимаем «ОК».

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

#2. Диспетчер имен. Этот способ аналогичный предыдущему, только с тем отличием, что имя будет присвоено диапазону без преобразования его в умную таблицу. 

Имя диапазона так же, как и в умной таблице, не должно содержать пробелы и знаки препинания. Выделяем диапазон ячеек с запасом пустых строк. Например, в нашем случае, мы понимаем, что в списке больше 25 значений содержаться не будет. Переходим во вкладку «Формулы», раздел «Определенные имена», меню «Диспетчер имен», нажимаем «Создать».

Называем будущий список, при необходимости корректируем диапазон значений.

Возвращаемся на лист «Проект», выделяем ячейку, в которой должен быть выпадающий список, переходим в меню «Проверка данных» и в поле Источник ссылаемся на созданный диапазон, нажимаем «ОК».

Похожие записи:

Как узнать mac адрес компьютера в windows xp, 7, 8, 10? Топ 15 формул в excel Поиск в «ворде». как выполнить поиск в «ворде» по слову Как построить диаграмму в эксель по данным таблицы Как обновить драйвера видеокарты на windows 10 Как создать точку восстановления в windows 10 и откатить систему