Вкладка сообщение для ввода

Обновлено: 21.07.2024

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

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

Простой выпадающий список

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

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



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











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





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

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

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



  1. Задать диапазонам с перечислением блюд имя в соответствии с его категорией. Общее название каждого диапазона должно четко совпадать с тем, что записано в первой ячейке каждого столбца.







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



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

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

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

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

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

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

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

Список с автозаполнением

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

Способ 1



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



Способ 2

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

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









Способ 3

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



  1. Обозначить готовую таблицу как источник, вписать формулу =ДВССЫЛ("Таблица1[Горячее]"), где таблица1 – автоматически присвоенное ей имя, [Горячее] – название столбца.



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



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

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



В результате должно получиться следующее:



Для вертикального отображения можно воспользоваться кодом:



В результате получится так:



Для того чтобы элементы накапливались в той же самой ячейке, подойдет код:

Выпадающий список в excel 2010

Этот способ считается стандартным, поскольку он прост и удобен.

1. На свободном месте листа запишите все элементы раскрывающегося списка в столбик, каждый элемент — в своей ячейке.

2. Задайте имя диапазону ячеек. Для этого:

Выпадающий список в excel 2010

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

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

Выпадающий список в excel 2010

Выпадающий список в excel 2010

Способ 2. Быстрое создание выпадающего списка

Выпадающий список в Excel 2010 можно создавать одним нажатием сочетания клавиш, но он может быть расположен только в одном месте — в ячейке под элементами списка.

1. Перечислите в столбик все элементы будущего выпадающего списка.

Выпадающий список в excel 2010

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

1. Перечислите элементы будущего списка в столбик.

Выпадающий список в excel 2010

Выпадающий список в excel 2010

Выпадающий список в excel 2010

Способ 4. Создание выпадающего списка как элемента ActiveX

Самый сложный метод, но обладающий максимально гибкими настройками.

1. Создайте список вышеописанным способом.

Выпадающий список в excel 2010

3. Нарисуйте на листе будущий раскрывающийся список.

Выпадающий список в excel 2010

6. Большинство настроек можно оставить по умолчанию, а самое необходимое перечислено ниже.

Выпадающий список в excel 2010

7. Сохраните настройки и деактивируйте режим конструктора нажатием на одноименную кнопку. Выпадающий список будет создан, а вы сможете проверить, как он работает.

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

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

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


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

Теперь создадим Выпадающий список для ввода фамилий в ведомость:

  • выделите ячейки ведомости, куда будут вводиться фамилии сотрудников, например А2:А5 ;
  • вызовите инструмент Проверка данных ( Данные/ Работа с данными/ Проверка данных );
  • установите условие проверки Список ;
  • в поле Источник введите =Сотрудники ;


Теперь при выделении любой ячейки из диапазона А2:А5 , справа от ячейки будет появляться кнопка со стрелкой, нажав на которую можно выбрать необходимую фамилию.



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

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

Кликните по кнопке ниже для загрузки файла с примерами выпадающих списков в Excel:

Видеоурок

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

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

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

Проверка данных в Excel

Проверка вводимых значений в Excel

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

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

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

Проверка данных в Excel

Проверка вводимых значений в Excel

Да - Нет

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

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

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

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

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

Для того чтобы сделать выпадающий список с помощью формулы СМЕЩ необходимо сделать следующее:

Проверка данных в Excel

Проверка вводимых значений в Excel

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

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

На примере выше мы использовали формулу =СМЕЩ(ссылка;смещ_по_строкам;смещ_по_столбцам;[высота];[ширина]).

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

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

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

Для создания списка потребуется:

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

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

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

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

выпадающий список с автоматиеской подстановкой в эксель

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

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

  • Из раскрывающегося меню выбираем стиль оформления таблицы:

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

Автоматическая подстановка данных в Excel

Присвоить имя таблицы в Excel

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

Проверка данных в Excel

Проверка вводимых значений в Excel

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

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

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

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

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

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

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

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

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

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

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

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

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

выпадающий список в excel

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

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

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

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

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

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

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

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

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

Предположим, что у нас есть списки городов двух стран Россия и США:

Функция Indirect (ДВССЫЛ) в Excel

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

зависимый выпадающий список в Excel

зависимый-выпадающий-список-в-excel

функция INDIRECT (ДВССЫЛ) в Excel

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

Проверка вводимых значений в Excel

  • В разделе “Источник” укажите ссылку: =INDIRECT(D2) или =ДВССЫЛ(D2);

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

выпадающий-список-в-excel-6

Еще больше полезных приемов в работе со списками данных и функциями в Excel вы узнаете в практическом курсе «От новичка до мастера Excel«. Успей зарегистрироваться по ссылке!

Читайте также: