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

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

​ введенное имя баобаб​ со строкой заголовка.​

​ то два варианта.​ там, это сделала​

​ вводимых данных. Находясь​.​ любой ячейки. В​ имеющимся списочным элементам​​ ввести в ячейку​​после символа​

​ использовать перечень, если​​«Список»​​ из имеющихся данных,​​ как создать диапазон​​, а затем —​

​Ненужный раскрывающийся список на​​ If End Sub​​ в выпадающий список?».​​ Получаем следующий вид​​1. В поле​

​ сотрудница, которая работала​​ в любом разделе​​Также вместо этого действия​

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

​ к полю​ «Создание списка в​ имя, смотрите в​или​Windows macOS Online​ снизу, вставляем другой​

​ еще одна строка​Ставим курсор в ячейку,​

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

​ то данное действие​​ оператора –​​ такой таблице будет​​«Источник»​​ Excel без повторов».​

​ статье «Диапазон в​​Этих же​​ ​​ код обработчика.Private Sub​​ со значением «баобаб».​

​ где будет находиться​​ Просто добавляете новое​​ она это сделала.​

​ действие — нажать​ кнопкой мышки и​Что же делать, если​ можно выбрать, чтобы​ заблокируется. При этом,​«ДВССЫЛ»​ легче менять значения,​​. Тут нужно указать​​Как удалить выпадающий список​ Excel» здесь.​​. Далее повторите действия,​​Выделите ячейку, в которой​ Worksheet_Change(ByVal Target As​​Когда значения для выпадающего​​ выпадающий список. Открываем​​ значение в список.​​ А через проверку​​ на кнопку​​ остановиться на варианте​​ значения добавлялись в​​ вставить в элемент​ если вы вносили​

​. После этого в​ тем самым автоматически​ группу наименований, предназначенную​ Excel.​Список можно написать​ описанные выше.​

​ есть раскрывающийся список.​ Range) On Error​

​ списка расположены на​​ параметры инструмента «Проверка​​2. В поле​​ данных не получается,​​«Очистить всё»​

​«Копировать»​​ окно проверки данных​​ таблицы.​​ сообщение предупреждения в​​ скобках в качестве​

​ изменяя списочные элементы.​​ для использования в​​Нужно выделить ячейки​

​ и в столбце​Если вместо удаления раскрывающегося​Если вы хотите удалить​ Resume Next If​ другом листе или​ данных» (выше описан​

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

Для примера возьмем историю взаиморасчетов с контрагентами, как показано на рисунке:

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

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

1. Выделите первый столбец таблицы A1:A19.
2. Выберите инструмент: «ДАННЫЕ»-«Сортировка и фильтр»-«Дополнительно».
3. В появившемся окне «Расширенный фильтр» включите «скопировать результат в другое место», а в поле «Поместить результат в диапазон:» укажите $F$1.
4. Отметьте галочкой пункт «Только уникальные записи» и нажмите ОК.

В результате мы получили список данных с уникальными значениями (фамилии без повторений).

Теперь нам необходимо немного модифицировать нашу исходную таблицу. Выделите первые 2 строки и выберите инструмент: «ГЛАВНАЯ»-«Ячейки»-«Вставить» или нажмите комбинацию горячих клавиш CTRL+SHIFT+=.

У нас добавилось 2 пустые строки. Теперь в ячейку A1 введите значение «Клиент:».

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

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

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

Выборка ячеек из таблицы по условию в Excel:

1. Выделите табличную часть исходной таблицы взаиморасчетов A4:D21 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило»-«Использовать формулу для определения форматируемых ячеек».
2. Чтобы выбрать уникальные значения из столбца, в поле ввода введите формулу: =$A4=$B$1 и нажмите на кнопку «Формат», чтобы выделить одинаковые ячейки цветом. Например, зеленым. И нажмите ОК на всех открытых окнах.

Как работает выборка уникальных значений Excel? При выборе любого значения (фамилии) из выпадающего списка B1, в таблице подсвечиваются цветом все строки, которые содержат это значение (фамилию). Чтобы в этом убедится в выпадающем списке B1 выберите другую фамилию. После чего автоматически будут выделены цветом уже другие строки. Такую таблицу теперь легко читать и анализировать.

Принцип действия автоматической подсветки строк по критерию запроса очень прост. Каждое значение в столбце A сравнивается со значением в ячейке B1. Это позволяет найти уникальные значения в таблице Excel. Если данные совпадают, тогда формула возвращает значение ИСТИНА и для целой строки автоматически присваивается новый формат. Чтобы формат присваивался для целой строки, а не только ячейке в столбце A, мы используем смешанную ссылку в формуле =$A4.

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

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

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

В Excel 2003 расширенный фильтр находится в разделе Данные | Фильтр | Расширенный фильтр

В Excel 2007 и 2010 расширенный фильтр находится в меню Данные | Дополнительно

После этого появится предупреждающее окно “Приложению Microsoft Excel не удается определить, в какой строке списка или выделенного диапазона содержатся названия столбцов, необходимые для выполнения этой команды.”, которое можно проигнорировать нажав “Ок”.

После этого откроется меню расширенного фильтра.

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

1. Переставить переключатель в поле скопировать результаты в другое место (можно не переставлять, но в этом случае ваш старый список будет заменен)
2. Проверить, что исходный диапазон выбран правильно – исходный диапазон списка всех телефонов.
3. Поставить галочку в поле Только уникальные значения
4. В поле Поместить результат в диапазон выбрать начальную ячейку куда вы хотите поместить уникальный список. (на рисунке мы выбрали ячейку B1)
5. Нажать ОК

Полученный результат Вы можете посмотреть во вложении ниже.

Office 365 ProPlus переименован в Майкрософт 365 корпоративные приложения. Для получения дополнительной информации об этом изменении прочитайте этот блог.

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

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

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

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

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

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

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

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

Способ 2 – Элемент управления.

В этом способе рассмотрен вариант добавления нового объекта и привязка его к конкретному диапазону в екселе. Какие необходимо выполнить шаги:

Если у вас версия ексель 2007 года и позже, тогда выбираем меню Разработчик. Если версия ранняя тогда Вид — Панели инструментов – Формы.

Открываем панель элементов

В появившемся диалоговом окне элементов ActiveX, находим значок со списком «Поле со списком».

Выносим элемент на страницу документа.

Формат элемента «Список»

В данном меню заносим диапазон значений и привязываем к ячейке данные.

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

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

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

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

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

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

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

Шаг пятый и последний. Формируем на основе полученного перечня источник для выпадающего списка.

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

1. начальная ячейка, относительно которой будем двигаться. В нашем случае это ячейка Е2 на листе проверка. Не забываем ее закрепить.

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

3. сдвиг по колонкам. Его тоже не будет.

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

5. Количество столбцов в формируемом диапазоне. У нас будет только одна колонка.

В итоге получаем

=СМЕЩ(проверка!$E$2;;;СЧЁТЗ(проверка!$E:$E)-СЧЁТ(проверка!$E:$E);1)

Рисунок 9

Рисунок 10

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

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

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

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

Наличие заголовка (шапки) важно. В нашем примере это ячейка А1 со словом «Деревья»

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

Протестируем. Вот наша таблица со списком на одном листе:

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

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

Осуществить задуманное нам помогла «умная таблица», которая легка «расширяется», меняется.

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

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

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

Нажмем «Да» и добавиться еще одна строка со значением «баобаб».

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

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

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

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

В открывшемся окне, в разделе Параметры нужно найти «Тип данных» и выбрать Список. Чуть ниже появится поле «Источник», где нужно будет указать диапазон значений. Для этого нужно выделить данные (в нашем случае это весь список фамилий с А2 по А10). Выделять данные можно прямо с открытым окном «Проверка вводимых значений», предварительно поставив курсор в поле «Источник». После этого можно нажать ОК.

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

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

Теперь нужно переключиться на вкладку Данные, выбрать «Проверка данных», в «Тип данных» выбрать «Список», в появившемся поле «Источник» прописать =Авторы (у вас будет свое название листа).

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

Если такой вкладки нет, то ее можно включить, нажав кнопку Office, выбрав Параметры Excel.

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

Во вкладке Разработчик нажать на команду «Вставить» и выбрать «Поле со списком (элемент управления формы)».

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

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

В поле « Связь с ячейкой » нужно указать ячейку для вывода порядкового номера выбранного элемента списка. То есть, если мы выбираем «Пушкин», то в указанную ячейку выводится порядковый номер — 1. Выбираем «Лермонтов», выводится 2. И так далее. Можно эту ячейку и не указывать.

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

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

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

Как убрать выпадающий список в эксель

Чтобы удалить выпадающий список в excel сразу со всех ячеек находящихся в одной колонке, нужно будет нажать клавишу Ctrl и удерживая её выделить все ячейки в колонке. Затем перейдите на вкладку Данные, а в ней нажмите на пункт Проверка данных. Когда откроется окно, то в нём нажмите сначала на пункт Очистить всё, а затем на кнопку с надписью ОК.

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

Электронную таблицу можно превратить в инструмент для анализа данных

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

Зависимые элементы

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

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

Это будет название города.

При именовании Санкт-Петербурга и Нижнего Новгорода Вы получите ошибку, так как имя не может содержать пробелов, символов подчеркивания, специальных символов и т.д.

Поэтому переименуем эти города, поставив нижнее подчеркивание.

Первый элемент в ячейке A9 создаем обычным образом.

А во втором пропишем формулу:

=ДВССЫЛ(A9)

Сначала Вы увидите сообщение об ошибке. Соглашайтесь.

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