Article #33945

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

 

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

Путь: меню «Данные» — инструмент проверки данных — вкладка «Параметры». Тип данных — «Список».

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

 

1. Вручную разделяются точкой с запятой в поле «Происхождение».
2. Введите значения заранее. А в качестве источника укажите диапазон ячеек со списком.
3. Назовите диапазон значений и введите это имя в поле источника.

Любой вариант даст такой результат.

Выпадающий список в Excel с подстановкой данных

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

 

1. Выберите диапазон в раскрывающемся списке. Найдите в главном меню инструмент «Форматировать как таблицу».
2. Откроются стили. Выбираем любой. Дизайн не имеет отношения к нашей задаче. Заголовок (заголовок) важен. В нашем примере это ячейка A1 со словом «Деревья». То есть вам нужно выбрать стиль таблицы со строкой заголовка. Получаем такое отображение диапазона:
3. Ставим курсор в ячейку, где будет располагаться выпадающий список. Откройте параметры инструмента «Проверить данные» (путь описан выше). В поле «Источник» пишем следующую функцию:

Давайте проверим это. Вот наша таблица со списком на одном листе:

Добавим в таблицу новое «древовидное» значение».

Теперь уберем значение «береза».

«Умный стол», который легко «расширяется» и меняется, помог нам реализовать задуманное.

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

1. Формируем именованный ассортимент. Путь: Формулы — Диспетчер имен — Создать .. Введите уникальное имя для диапазона — ОК.
2. Создайте раскрывающийся список в любой ячейке. Как это сделать, уже известно. Источник — название диапазона: = деревья.
3. Снимите флажки «Вставить сообщение», «Сообщение об ошибке». В противном случае Excel не позволит нам вводить новые значения.
4. Мы вызываем редактор Visual Basic. Для этого щелкните правой кнопкой мыши имя листа и перейдите на вкладку «Исходный текст». Или одновременно нажмите Alt + F11. Скопируйте код (просто вставьте параметры).
5. Сохраним, установив тип файла «с поддержкой макросов».
6. Перейдите на вкладку списка. Вкладка разработчика — Код — Макрос. Сочетание клавиш для сочетания клавиш — Alt + F8. Выбираем желаемое имя. Щелкните «Выполнить».

Когда мы вводим новое имя в пустую ячейку выпадающего списка, будет отображаться сообщение: «Добавить введенное имя баобаба в выпадающий список?».

Нажмите «Да», и будет добавлена ​​еще одна строка со значением «баобаб».

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

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

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

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

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

Возьмем три именованных диапазона:

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

 

1. Давайте создадим первый раскрывающийся список, который будет включать имена диапазонов.
2. Когда вы ставите курсор в поле «Источник», переходите к листу и поочередно выбираете нужные ячейки.
3. Теперь давайте создадим второе раскрывающееся меню. Он должен отражать те слова, которые соответствуют имени, выбранному в первом списке. Если «Деревья», то «Граб», «Дуб» и так далее. Введите функцию формы = КОСВЕННО (E3) в поле «Источник». E3 — это ячейка с именем первого диапазона.

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

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

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

Частный вторичный рабочий лист_Изменение (значение 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
Конец, если
Конец подзаголовка

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

Выпадающий список с поиском

1. Во вкладке «Разработчик» находим инструмент «Вставка» — «ActiveX». Здесь нам понадобится кнопка «Combo Box» (акцент на предложениях).
2. Щелкаем по иконке — «Режим дизайна» становится активным. Нарисуйте курсором небольшой прямоугольник (он станет «крестиком») — место будущего списка.
3. Нажимаем на «Свойства» — открывается список настроек.
4. Вставляем диапазон в строку ListFillRange (вручную). Ячейка, в которой будет отображаться выбранное значение, находится в строке LinkedCell. Чтобы изменить шрифт и размер — Шрифт.

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

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

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

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

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

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

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

• Во всплывающем окне «Проверка введенных значений» во вкладке «Параметры» выберите «Список» в типе данных “:

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

 

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

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

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

Например, предположим, что мы хотим отразить два слова «Да» и «Нет» в раскрывающемся меню. Для этого нам понадобятся:

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

 

• Во всплывающем окне «Проверка введенных значений» во вкладке «Параметры» выберите «Список» в типе данных “:

 

• В поле «Источник» введите значение «Да; Нет”.
• Нажмите «ОК “

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

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

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

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

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

 

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

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

 

• Во всплывающем окне «Проверка введенных значений» во вкладке «Параметры» выберите «Список» в типе данных “:

 

• В поле «Источник» введите формулу: = СМЕЩЕНИЕ (A $ 2 $; 0; 0; 5)
• Нажмите «ОК “

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

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

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

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

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

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

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

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

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

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

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

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

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

• Создайте список данных для отображения в раскрывающемся списке. В нашем случае это список цветов. Выделите список левой кнопкой мыши:

• На панели инструментов нажмите «Форматировать как таблицу “:

• В раскрывающемся меню выберите стиль таблицы:

• Нажимая кнопку «ОК» во всплывающем окне, подтверждаем выбранный диапазон ячеек:

• Затем выберите диапазон данных таблицы для раскрывающегося списка и назовите его в левом поле над столбцом «A”:

Таблица с данными готова, теперь мы можем создать выпадающий список. Это требует:

• Выберите ячейку, в которой мы хотим создать список;
• Перейдите на вкладку «Данные» => раздел «Работа с данными» на панели инструментов => выберите пункт «Проверка данных “:

 

• Во всплывающем окне «Проверка введенных значений» во вкладке «Параметры» выберите «Список» в типе данных “:

 

• В поле источника укажите = «название вашей таблицы». В нашем случае мы назвали его «Список “:

• Готовый! Выпадающий список создан, в нем отображаются все данные из указанной таблицы:

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

• Таблица автоматически расширит свой диапазон данных. Выпадающий список будет заполнен новым значением из таблицы:

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

В Excel есть возможность копировать созданные раскрывающиеся списки. Например, в ячейке A1 у нас есть раскрывающийся список, который мы хотим скопировать в диапазон ячеек A2: A6 .

Чтобы скопировать раскрывающийся список с текущим форматированием:

• щелкните левой кнопкой мыши по ячейке с выпадающим списком, который вы хотите скопировать;
• нажмите сочетание клавиш CTRL + C ;
• выберите ячейки в диапазоне A2: A6, в которые вы хотите вставить раскрывающийся список;
• нажмите сочетание клавиш CTRL + V .

Затем вы скопируете раскрывающийся список, сохранив исходный формат списка (цвет, шрифт и т.д.). Если вы хотите скопировать / вставить раскрывающийся список без сохранения формата, тогда:

• щелкните левой кнопкой мыши по ячейке с выпадающим списком, который вы хотите скопировать;
• нажмите сочетание клавиш CTRL + C ;
• выберите ячейку, в которую вы хотите вставить раскрывающийся список;
• нажмите правую кнопку мыши => вызовите выпадающее меню и нажмите «Специальная вставка “;

• В появившемся окне в разделе «Вставка» выберите «условия для значений “:

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

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

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

• Щелкните вкладку «Главная» на панели инструментов;
• Нажмите «Найти и выбрать» и выберите «Выбрать группу ячеек “:

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

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

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

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

Допустим, у нас есть списки городов двух стран, России и США:

Для создания зависимого выпадающего списка нам понадобятся:

• Создайте два именованных диапазона для ячеек «A2: A5» с именем «Россия» и для ячеек «B2: B5» с именем «США». Для этого нам нужно выделить весь диапазон данных для выпадающих списков:

ТАКЖЕ ЧИТАЙТЕ: Обзор Amd Phenom II X6 1055T

• Перейдите на вкладку «Формулы» => щелкните в разделе «Определенные имена» на элементе «Создать из выбранного “:

• Во всплывающем окне «Создать имена из выбранного диапазона» установите флажок «в строке выше». Таким образом, Excel создаст два диапазона с названиями «Россия» и «США» со списками городов:

• Нажмите «ОК “
• В ячейке «D2» создайте раскрывающийся список для выбора стран «Россия» или «США». Затем мы создадим первый раскрывающийся список, в котором пользователь сможет выбрать одну из двух стран.

Теперь, чтобы создать зависимое раскрывающееся меню:

• Выберите ячейку E2 (или любую другую ячейку, в которой вы хотите создать зависимый раскрывающийся список);
• Щелкните вкладку «Данные» => «Проверить данные ”;
• Во всплывающем окне «Проверить входные значения» на вкладке «Параметры» в типе данных выберите «Список “:

 

• В разделе «Источник» укажите ссылку: = КОСВЕННО ($ D $ 2) или = КОСВЕННО ($ D $ 2);

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

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

Способ 1 — горячие клавиши и раскрывающийся список в excel

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

Тот же пункт меню можно запустить с помощью комбинации клавиш Alt + Стрелка вниз, и программа автоматически предложит значения ячеек, которые вы ранее заполнили данными в раскрывающемся списке. На изображении ниже программа предложила 4 варианта заполнения (в Excel не отображаются повторяющиеся данные). Единственное условие для работы этого инструмента — отсутствие пустых ячеек между ячейкой, в которую вы вводите данные из списка, и самим списком.

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

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

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

 

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

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

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

 

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

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

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

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

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

Теперь вам нужно настроить этот элемент. Для этого активируйте «Режим дизайна» и нажмите кнопку «Свойства». Вы должны открыть окно свойств).

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

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

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

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

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