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

Выпадающий список через контекстное меню

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

1. Во вспомогательной таблице мы записываем список всех имен, каждое в новой строке в отдельной ячейке. В результате у вас должен получиться столбец с заполненными данными.
2. Затем мы отмечаем все эти ячейки, щелкаем в любом месте отмеченного диапазона правой кнопкой мыши и в открывшемся списке нажимаем на функцию «Присвоить имя..”.
3. На экране появится окно «Создать имя». Мы называем список по желанию, но с оговоркой, что первым символом должна быть буква, а некоторые символы не допускаются. Он также предоставляет возможность добавлять заметки в список в соответствующем текстовом поле. Когда все будет готово, нажмите ОК.
4. Переходим во вкладку «Данные» в главном окне программы. Мы отмечаем группу ячеек, для которой хотим установить выбор из нашего списка, и нажимаем на иконку «Контроль данных» в подразделе «Работа с данными”.
5. На экране появится окно «Проверить введенные значения». Находясь во вкладке «Параметры» в типе данных, останавливаемся на опции «Список». В текстовом поле «Источник» напишите знак «равно» («=») и название только что созданного списка. В нашем случае — «= Имя». Щелкните ОК.
6. Все готово. Справа от каждой ячейки выбранного диапазона появится небольшой значок со стрелкой вниз, нажав на которую, вы сможете открыть список имен, который мы составили заранее. При нажатии на желаемый вариант из списка он будет сразу вставлен в ячейку. Кроме того, значение в ячейке теперь может соответствовать только имени из списка, что исключает любые опечатки.

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

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

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

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

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

Использование именованного диапазона Давайте создадим диапазон с именем List_of_elements, содержащий список элементов в раскрывающемся списке (ячейки A 1: A 4 на листе списка). Из-за этого:

• выберите A1: A4,
• щелкните Формулы / Определенные имена / Присвоить имя
• в поле Name введите List_Elements, в поле Scope выберите Book;

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

• вызвать Data Check;
• в поле Источник введите ссылку на созданное имя: = Element_List.

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

Чтобы исключить пустые строки и рассмотреть новые элементы списка, Dynamic Range позволяет. Для этого при создании имени List_Elements в поле Range напишите формулу = OFFSET (List! $ A $ 1 ;;; COUNT (List! $ A: $ A))

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

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

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

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

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

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

• в книге Source xlsx создайте необходимый список пунктов;
• в книге Source.xlsx присвойте Имя диапазону ячеек, содержащему список элементов, например ListExternal;
• откройте книгу, в которой вы хотите разместить ячейки с выпадающим списком;
• выделите требуемый диапазон ячеек, вызовите инструмент проверки данных, в поле Source укажите = INDIRECT («Source.xlsx sheet1! ListExternal») ;

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

Если вы не хотите называть диапазон в файле Source.xlsx, вам необходимо изменить формулу на = INDIRECT («Source.xlsx sheet1! $ A $ 1: $ A $ 4»)

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

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

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

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

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

Допустим, у нас есть список фруктов:

Чтобы создать выпадающий список, вам необходимо выполнить следующие действия:

• Выберите ячейку, в которой мы хотим создать раскрывающийся список;
• Перейдите на вкладку «Данные» => раздел «Работа с данными» на панели инструментов => выберите пункт «Проверка данных“.
• Во всплывающем окне «Проверка введенных значений» во вкладке «Параметры» выберите «Список» в типе данных“:
• В поле «Источник» введите диапазон названий фруктов = $ A $ 2: $ A $ 6 или просто поместите курсор мыши в поле ввода значения «Источник» и затем выберите диапазон данных с помощью мыши:

Если вы хотите создать раскрывающиеся списки в нескольких ячейках одновременно, выберите все ячейки, в которых вы хотите их создать, а затем выполните указанные выше действия. Важно убедиться, что ссылки на ячейки являются абсолютными (например, $ A $ 2), а не относительными (например, A2 или A $ 2 или $ A2).

Выбор массива с именем

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

1. Выберите ячейки, которые будут заключены в единый массив, т.е те, значения которых должны отображаться в раскрывающемся списке. В левом верхнем углу вы увидите название первой ячейки, по которой нужно щелкнуть.
2. Измените имя массива на любое, начинающееся с буквы.
3. Создайте раскрывающийся список в ячейке листа, снова войдя в то же меню, предварительно выбрав саму ячейку.
4. В качестве источника укажите созданный массив, написав его имя после «‎=».
5. В результате вы должны получить список, включающий все значения в массиве.

Как сделать выпадающий список в Excel с подстановкой данных (с использованием функции СМЕЩ)

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

Для создания списка вам понадобятся:

• Выберите ячейку, в которой мы хотим создать раскрывающийся список;
• Перейдите на вкладку «Данные» => раздел «Работа с данными» панели инструментов => выберите пункт «Проверить данные“;
• Во всплывающем окне «Проверка введенных значений» на вкладке «Параметры» выберите «Список» в типе данных“;
• В поле «Источник» введите формулу: = СМЕЩЕНИЕ (A $ 2 $; 0; 0; СЧЁТЕСЛИ ($ A $ 2: $ A $ 100;”<>”))
• Нажмите «ОК“

В этой формуле в аргументе «высота» мы указываем в качестве аргумента, обозначающего высоту списка с данными — формулу СЧЁТЕСЛИ, которая вычисляет количество непустых ячеек в заданном диапазоне A2: A100.

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

Выпадающий список в Excel с данными с другого листа/файла

Когда значения для раскрывающегося списка находятся на другом листе или в другой книге, стандартный метод не работает. Решить проблему можно с помощью функции КОСВЕННЫЙ — это будет правильная ссылка на внешний источник информации.

1. Сделаем активную ячейку, в которой мы хотим разместить выпадающий список.
2. Открытие параметров проверки данных. В поле «Источник» введите формулу: = КОСВЕННО («List1.xlsx Sheet1! $ A $ 1: $ A $ 9”).

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

Создание списка с применением инструментов разработчика

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

1. Прежде всего, эти инструменты нужно найти и активировать, так как по умолчанию они отключены. Зайдите в меню «Файл”.
2. В списке слева находим запись «Параметры» внизу и нажимаем на нее.
3. Перейдите в раздел «Настроить ленту» и в области «Основные вкладки» поставьте галочку напротив пункта «Разработчик». Инструменты разработчика будут добавлены в ленту программы. Нажмите ОК, чтобы сохранить настройки.
4. В программе появилась новая вкладка «Разработчик». Мы будем работать через нее. Во-первых, давайте создадим столбец с элементами, которые будут источниками значений для нашего раскрывающегося списка.
5. Перейдите на вкладку «Разработчик». В подразделе «Элементы управления» нажмите кнопку «Вставить». В открывшемся списке в функциональном блоке «Элементы управления ActiveX» щелкните значок «Поле со списком”.
6. Затем нажмите на нужную ячейку, после чего появится окно со списком. Отрегулируйте его размер по краям ячейки. Если список выделен мышью, на панели инструментов будет активен «Режим дизайна». Нажмите кнопку «Свойства», чтобы продолжить настройку списка.
7. В открытых параметрах находим строку «ListFillRange». В столбце рядом с ним мы записываем координаты диапазона ячеек, составляющих наш ранее созданный список, через двоеточие. Закройте окно с параметрами, нажав на крестик.
8. Затем щелкните правой кнопкой мыши поле со списком, затем щелкните элемент «Объект ComboBox» и выберите «Изменить”.
9. В результате мы получаем выпадающий список с заранее заданным списком.
10. Чтобы вставить его в несколько ячеек, поместите курсор в нижний правый угол ячейки со списком и, как только его вид изменится на крест, удерживайте левую кнопку мыши и перетащите вниз до последней строки, где нам нужен аналогичный список.

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

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

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

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

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

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

При именовании диапазона значений важно использовать подчеркивание «_» вместо пробела. Например, «Мясные тарелки». Однако возможно, что это правило пропущено в одном из имен и, как следствие, при выполнении операции возникает ошибка. Вы можете использовать обычный пробел в самих именах, а при замене в списке изменить его, чтобы подчеркнуть его, используя формулу REPLACE:

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

Окончательная формула выглядит так:

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

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

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

Подстановка динамических данных Excel

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

Выделите его и во вкладке «Главная» выберите любой стиль таблицы.

Убедитесь, что вы установили флажок ниже.

Вы получите такой дизайн.

Создайте активный элемент, как описано выше. Введите формулу как источник

= КОСВЕННО («Таблица1 Город»)

Чтобы узнать название таблицы, перейдите на вкладку «Дизайн» и посмотрите на нее. Вы можете изменить название на любое другое.
Функция ДВССЫЛ создает ячейку или ссылку на диапазон. Теперь ваш элемент в ячейке связан с массивом данных.

Попробуем увеличить количество городов.

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

= Cell_address

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

= D1

Как создать раскрывающийся список в Эксель с помощью функции СМЕЩ

Наряду с методами, описанными выше, вы также можете использовать формулу СМЕЩЕНИЕ для создания раскрывающихся списков.

Например, у нас есть список со списком фруктов:

Чтобы создать раскрывающийся список с использованием формулы СМЕЩЕНИЕ, вам необходимо сделать следующее:

• Выберите ячейку, в которой мы хотим создать раскрывающийся список;
• Перейдите на вкладку «Данные» => раздел «Работа с данными» на панели инструментов => выберите пункт «Проверка данных“:
• Во всплывающем окне «Проверка введенных значений» во вкладке «Параметры» выберите «Список» в типе данных“:
• В поле «Источник» введите формулу: = СМЕЩЕНИЕ (A $ 2 $; 0; 0; 5)
• Нажмите «ОК“

Система создаст выпадающий список со списком фруктов.

Как эта формула работает?

В приведенном выше примере мы использовали формулу = OFFSET (ref; row_offset; column_offset; height</a>; width).

Эта функция содержит пять аргументов. Аргумент ссылки ($ A $ 2 в примере) указывает, с какой ячейки начинать смещение. В аргументах «offset_by_strings» и «offset_by_columns» (в примере указано значение «0») — сколько строк / столбцов необходимо переместить для отображения данных. Аргумент высота указывает значение «5», которое обозначает высоту диапазона ячеек. Мы не указываем аргумент «ширина», поскольку в нашем примере диапазон состоит из одного столбца.

Используя эту формулу, система возвращает в качестве данных для раскрывающегося списка диапазон ячеек, начинающийся с ячейки $ A $ 2, состоящий из 5 ячеек.

Ручное добавление элементов списка

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

1. Выделите заранее подготовленную область, на которой хотите разместить список. Перейдите на вкладку «Данные» и откройте «Проверка данных».
2. Выберите список в качестве типа данных и в списке «Источник» значения, которые вы хотите добавить к нему. СОЕДИНЕННЫЕ ШТАТЫ АМЕРИКИ «;» чтобы отделить одно значение от другого.
3. Подтвердите изменения и вернитесь к таблице. Теперь, когда вы развернете список, вы сможете выбрать одну из добавленных опций.

Как настроить зависимые выпадающие списки в Excel с поиском

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

Для второго списка нужно ввести формулу:

= СМЕЩЕНИЕ ($ A $ 1; ПОИСК ($ E $ 6; $ A: $ A; 0) -1; 1; СЧЁТЕСЛИ ($ A: $ A, $ E $ 6); 1)

Функция OFFSET возвращает ссылку на диапазон, который смещен от первой ячейки на указанное количество строк и столбцов: = OFFSET (начало; вниз; вправо; size_in_lines; size_in_columns)

ПОИСКПОЗ возвращает номер ячейки с городом, выбранным в первом списке (E6) в указанной области SA: $ A.
СЧЁТЕСЛИ подсчитывает количество совпадений в диапазоне со значением в указанной ячейке (E6).

У нас есть связанные выпадающие списки в Excel с условием совпадения и поиском по диапазону.

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

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

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

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

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

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

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

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

Повторяем действие для столбцов «Рыба» и «Выпечка».

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

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

Остерегайтесь «источника». Указывается абсолютная адресация столбцов и ячеек (с помощью знаков «$»). Чтобы распространить эту форму на остальные строки, вам нужно будет указать другой источник данных для каждой строки. Это занимает много времени. Вы можете вручную вручную удалить абсолютную адресацию из «источника», а затем скопировать ячейку в остальные строки ниже. Тогда «формула» размножится правильно.

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

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

Иногда невозможно отдать предпочтение только одному значению, поэтому вам нужно выбрать более одного. Затем вам нужно добавить макрос в код страницы. Использование комбинации клавиш Alt + F11 открывает редактор Visual Basic. И код вводится туда.

Частный вторичный рабочий лист_Изменение (значение ByVal как диапазон)

В случае ошибки Продолжить Далее

Если не пересекается (Target, Range («E2: E9»)) Is Nothing и Target.Cells.Count = 1, тогда

Application.EnableEvents = False

Если Len (Target.Offset (0, 1)) = 0, то

Target.Offset (0, 1) = Цель

Другой

Target.End (xlToRight) .Offset (0, 1) = Цель

Конец, если

Цель: удалить контент

Application.EnableEvents = True

Конец, если

Конец подзаголовка

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

Частный вторичный рабочий лист_Изменение (значение ByVal как диапазон)

В случае ошибки Продолжить Далее

Если не пересекается (Target, Range («H2: K2»)) Is Nothing и Target.Cells.Count = 1, тогда

Application.EnableEvents = False

Если Len (Target.Offset (1, 0)) = 0, то

Target.Offset (1, 0) = Цель

Другой

Target.End (xlDown) .Offset (1, 0) = Цель

Конец, если

Цель: удалить контент

Application.EnableEvents = True

Конец, если

Конец подзаголовка

Наконец, этот код используется для записи в ячейку.

Частный вторичный рабочий лист_Изменение (значение ByVal как диапазон)

В случае ошибки Продолжить Далее

Если не пересекается (Target, Range («C2: C5»)) равно Nothing и Target.Cells.Count = 1, тогда

Application.EnableEvents = False

newVal = Цель

Отмена заявки

oldval = Цель

Если Len (oldval) <> 0 И oldval <> newVal Тогда

Target = Target & «,» & newVal

Другой

Target = newVal

Конец, если

Если Len (newVal) = 0, тогда Target.ClearContents

Application.EnableEvents = True

Конец, если

Конец подзаголовка

Интервалы доступны для редактирования.

Как выделить все ячейки, содержащие выпадающий список в Экселе

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

• Щелкните вкладку «Главная» на панели инструментов;
• Нажмите «Найти и выбрать» и выберите «Выбрать группу ячеек“:
• В диалоговом окне выберите «Проверить данные». В этом поле можно выбрать пункты «Все» и «Эти же». «Все» выберет все раскрывающиеся списки на листе. Запись «равно» отобразит раскрывающиеся списки с аналогичными данными в содержимом раскрывающегося меню. В нашем случае мы выбираем «все“:
• Нажмите «ОК“

Нажав «ОК», Excel выберет все ячейки в раскрывающемся списке на листе. Таким образом вы можете одновременно привести все списки в общий формат, выделить края и т.д.

Как убрать (удалить) выпадающий список в Excel

Откройте выпадающее окно настроек и выберите «Любое значение» в разделе «Тип данных».

Ненужный элемент исчезнет.

Зависимые раскрывающиеся списки

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

1. Во-первых, вам нужно создать именованный диапазон. Перейдите на вкладку «Формулы», затем выберите «Диспетчер имен» и «Создать».Выделите диапазон ячеек со значением, откройте вкладку «Формулы», нажмите «Диспетчер имен»
   Нажмите «Создать»
2. Введите имя и диапазон, затем нажмите ОК. Если вы ранее выделяли мышью нужные ячейки, диапазон будет указан автоматически. Также обратите внимание, что имя диапазона должно быть уникальным и не должно содержать знаков препинания с пробелами.Пишем название «Деревья», нажимаем «ОК»
3. Используйте тот же метод для создания всех именованных диапазонов, для которых вы хотите создать логические зависимости. В этом примере есть еще два диапазона: Кустарники и Травы».Остальные интервалы создаем таким же образом
4. Откройте вкладку «Данные» (путь указан в первом способе) и укажите имена именованных диапазонов в источнике, как показано на скриншоте.В поле «Источник» укажите ячейки с названиями диапазонов, нажмите «ОК»
   Выпадающий список с названием диапазона ячеек
5. Теперь вам нужно таким же образом создать дополнительное выпадающее меню. В этом списке будут показаны слова, соответствующие названию. Например, если вы выберете «Древесина», это будет «береза», «липа», «клен» и так далее. Для этого повторите предыдущие шаги, но в поле ввода «Источник» введите функцию «= ДВССЫЛ (E1)». В этом случае «E1» — это адрес ячейки с именем первого диапазона. Точно так же вы можете создать столько взаимосвязанных списков, сколько вам нужно.
   В поле ввода «Источник» введите функцию «= ДВССЫЛ (E1)», нажмите «ОК»
   Раскрывающийся список связанных результатов

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

Самый быстрый способ.

Как проще всего добавить раскрывающийся список? Просто щелкните правой кнопкой мыши по пустой ячейке под столбцом с данными, затем выберите команду контекстного меню «Выбрать из раскрывающегося списка» (Выбрать из раскрывающегося списка). Или вы можете просто встать в нужном месте и нажать комбинацию клавиш Alt + Стрелка вниз. Отображается упорядоченный список ранее введенных уникальных значений.
Метод не работает, если наша ячейка и столбец с записями разделены хотя бы одной пустой строкой или если вы хотите вставить что-то, что еще не было вставлено выше. Это хорошо видно на нашем примере.

Используем меню

Давайте рассмотрим небольшой пример, в котором нам нужно последовательно вводить одни и те же названия продуктов в таблицу. Напишите в столбце данные, которые мы будем использовать (например, названия продуктов). В нашем примере в диапазоне G2: G7.

Выберите ячейку таблицы (у вас может быть несколько ячеек одновременно), в которой вы хотите использовать ввод из предопределенного списка. Затем в главном меню выберите вкладку «Данные» — на вкладке «Проверка». Затем щелкните элемент «Разрешить» и выберите параметр «Список». Установите курсор в поле Source и введите адреса с эталонными значениями элементов внутри него — в нашем случае G2: G7. Также рекомендуется использовать здесь абсолютные ссылки (для их установки нажмите F4).

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

Вы также можете использовать именованный диапазон в качестве источника.

Например, интервал I2: I13, содержащий названия месяцев, можно назвать «месяцами». Затем вы можете ввести имя в поле «Источник».

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

Однако вы можете не использовать диапазоны или ссылки, а просто определить возможные параметры непосредственно в поле «Источник». Например, напишите там —

Не совсем

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

Создаем элемент управления.

Вставляем на лист новый объект — элемент управления «Поле со списком» с последующей его привязкой к данным листа Excel. Мы делаем:

1. Щелкните вкладку Разработчик. Если он не отображается, в Excel 2007 вам нужно нажать кнопку «Office» — «Параметры» — установить флажок «Показать вкладку разработчика на ленте» (кнопка «Office» — «Параметры» — «Показать вкладку разработчика на ленте») или в версии 2010-2013 гг., Щелкнуть правой кнопкой мыши щелкните ленту, выберите команду «Настроить ленту» и включите отображение вкладки «Разработчик» с помощью флажка.
2. Найдите нужный значок среди элементов управления (см. Изображение ниже).

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

Но нам нужен не этот номер, а соответствующее слово. Мы используем функцию ИНДЕКС (ИНДЕКС на английском языке). Это позволяет вам найти одно в списке значений по порядковому номеру. Укажите диапазон ячеек (F5: F11) и адрес с полученным порядковым номером (F2) в качестве аргументов ИНДЕКС).

Записываем формулу в F3, как показано на рисунке:

= ИНДЕКС (F5: F11; F2)

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

Также обратите внимание, что здесь мы не привязаны к какому-либо конкретному месту в таблице. Таким списком удобно пользоваться, так как его можно беспрепятственно «перетащить» мышкой в ​​любое удобное место. Для этого во вкладке «Разработчик» нужно активировать режим дизайна.

Элемент ActiveX

Действуем аналогично предыдущему способу, но выбираем иконку чуть ниже — в разделе «ActiveX Controls».

Определите список допустимых значений (1). Обратите внимание, что здесь можно выбрать одновременно несколько столбцов для отображения. Затем мы выбираем из списка (2) адрес, по которому будет вставлена ​​желаемая позиция, мы указываем количество столбцов, которые будут использоваться в качестве исходных данных (3), и номер столбца, из которого будет сделан выбор для вставка в лист (4). Если вы укажете номер в столбце 2, A5 будет включать не фамилию, а должность. Вы также можете указать количество строк, которые будут отображаться в списке. По умолчанию — 8. Остальное можно пролистывать мышью (5).

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

Создание раскрывающегося списка при помощи функции СМЕЩ

В дополнение к классическому методу можно использовать функцию СМЕЩЕНИЕ для создания раскрывающихся меню.

Открываем лист.

12

Чтобы использовать раскрывающийся список, вам необходимо сделать следующее:

1. Выберите интересующую ячейку, в которую вы хотите поместить будущий список.
2. Последовательно откройте вкладку «Данные» и окно «Проверить данные».
   13
3. Настроим «Список». Это делается так же, как и в предыдущих примерах. Наконец, используется следующая формула: = СМЕЩЕНИЕ (A $ 2 $; 0; 0; 5). Вставляем его туда, где указаны ячейки, которые будут использоваться в качестве аргумента.

Затем программа создаст меню со списком фруктов.

Синтаксис для этого:

= СМЕЩЕНИЕ (ref; line_offset; column_offset; высота</a>; ширина)

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