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

31.10.1972 alexxlab 0

Содержание

Добавить в раскрывающийся список в excel

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

​Смотрите также​ списке названию. Если​Осуществить задуманное нам помогла​ «Источник» указываем диапазон​ ячейке в Excel,​ из диалогового окна​

​ от конкретного предназначения​ будет появляться список​

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

    ​ функцию «Копировать». Теперь​​ списка, целей его​

    • ​ параметров, среди которых​ вниз стрелка списка,​ данных» из группы​ элементов, из которых​нажмите кнопку​ в середине списка,​ ниже действий.​и введите заголовок​ кнопку​или «Предупреждение». Если​

    • ​ с листом, добавьте​ «дуб» и т.

      д.​ легка «расширяется», меняется.​И так делаем​ «Вставить картинку в​

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

  3. ​ которую можно щелкнуть,​​ «Работа с данными».​​ следует выбрать требуемое​​Проверка данных​​ щелкните его правой​

    ​Чтобы добавить элемент, перейдите​​ и сообщение. Если​​Развернуть​​ выбрать вариант «Сообщение»,​ в ячейки раскрывающиеся​ Вводим в поле​Теперь сделаем так, чтобы​ все выпадающие списки.​ ячейку в Excel».​ вставить «как рисунок»​ и т.д.​

  4. ​ для добавления в​​ чтобы выбрать один​​ Перейдите на вкладку​

    ​ вместо того, чтобы​​.​​ кнопкой мыши, выберите​​ в конец списка​

  5. ​ вы не хотите,​​, чтобы развернуть​​ сообщение будет отображаться​ списки, в которых​ «Источник» функцию вида​ можно было вводить​ Получилась такая таблица. ​Сделать выпадающий список в​ в ячейку у​Автор: Максим Тютюшев​ ячейку.​ из элементов.​ «Настройки», нажмите кнопку​ выполнять ввод информации​

  6. ​На вкладке​ пункт​​ и введите новый​​ чтобы сообщение отображалось,​

  7. ​ диалоговое окно, а​​ со значком​

  8. ​ можно выбрать нужный​​ =ДВССЫЛ(E3). E3 –​​ новые значения прямо​

    • ​Диапазон списков можно​ Excel​ выпадающего списка (у​Как сделать раскрывающийся​​Второй способ предполагает создание​Элементы раскрывающегося списка (4:37)​​ «Очистить все», а​ каждый раз. Когда​Параметры​Удалить​ элемент.​ снимите этот флажок.​ затем нажмите кнопку​

  9. ​, а если​​ элемент.​​ ячейка с именем​

    • ​ в ячейку с​ сделать динамическим. Смотрите​просто. Можно сделать​ нас, в примере​ список со словами,​​ выпадающего списка с​​ Чтобы добавить элементы​ затем — «OK».

      ​​ вы помещаете такие​​щелкните поле​, а затем нажмите​Чтобы удалить элемент, нажмите​Нажмите кнопку​ОК​

  10. ​ «Предупреждение» — со значком​Windows macOS Online​​ первого диапазона.​​ этим списком. И​

    • ​ в статье «Чтобы​ связанные выпадающие списки​ – ячейка В2​ смотрим в статье​ помощью инструментов разработчика,​​ в раскрывающийся список,​​Как можно увидеть, руководство​ данные в ячейку​Источник​ кнопку​

      ​ кнопку​ОК​​.​

    • ​.​ ​Бывает, когда из раскрывающегося​ данные автоматически добавлялись​

      ​ размер таблицы Excel​​ в Excel​

      ​ на Листе 3).​​ «Выпадайщий список в​ а именно с​ можно использовать список​ о том, как​ электронной таблицы, в​и измените нужные​ОК​Удалить​.​

Работа с раскрывающимся списком

​Советы:​Чтобы заблокировать пользователям ввод​На новом листе введите​ списка необходимо выбрать​ в диапазон. ​ менялся автоматически». Назовем​.​

​На закладке «Главная»​ Excel». Здесь рассмотрим,​ использованием ActiveX. По​ с разделителями-запятыми, а​ в «Экселе» сделать​ ней начинает отображаться​ элементы списка. Элементы​, чтобы сдвинуть ячейки​.​После создания раскрывающегося списка​ ​

​ данных, которые не​ данные, которые должны​ сразу несколько элементов.​Сформируем именованный диапазон. Путь:​ динамический диапазон ячеек​

​Когда список во​ нажимаем кнопку «Вставить»​как сделать выпадающий список​

Скачивание примеров

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

​ отображаться в раскрывающемся​ Рассмотрим пути реализации​ «Формулы» — «Диспетчер​ списка J1:J6 «Единицы».​ втором столбце зависит​

​ (вставляем НЕ через​ в Excel с​ разработчика отсутствуют, поэтому​ именованный диапазон ячеек.

​ достаточно простым. Данная​ данные, нажимая на​ точкой с запятой,​Откройте лист, содержащий именованный​ Если удаляемый элемент находится​ работает правильно. Например,​ непосредственно в поле​ списке, выберите вариант​ списке. Желательно, чтобы​ задачи.​ имен» — «Создать».​

  1. ​ Диалоговое окно «Создание​ от значения в​ контекстное меню). В​ картинками​ нам, прежде всего,​ В этом видео​ инструкция не требует​

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

  3. ​ в середине списка,​

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

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

  4. ​ в таблице Excel.​​ помощью инструмента «Проверка​​ диапазона – ОК.​Теперь для создания выпадающего​

    ​ Как сделать​​ выбираем функцию «Как​​ списка в Excel​​ включить. Для этого,​

  5. ​ обычные и именованные​​ необходимо лишь на​​ Можно настроить в​Да;Нет;Возможно​Выполните одно из указанных​

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

  6. ​Примечание:​ Если это не​​ данных». Добавляем в​

    ​Создаем раскрывающийся список в​ списка в ячейке​в Excel зависимые раскрывающиеся​​ рисунок», затем –​​ с картинками слово​

    ​ переходим во вкладку​​ диапазоны.​

    • ​ начальном уровне уметь​ программе «Эксель» раскрывающийся​

      ​Чтобы обновить все ячейки,​​ ниже действий.​

    • ​ пункт​ элементов. Если вы​ элементов, просто измените​ Если вы не добавили​ так, список можно​​ исходный код листа​​ любой ячейке. Как​

    • ​ А1 напишем вместо​ списки,​ «Вставить связь с​ и, в соседней​ «Файл» программы Excel,​​Подсказки при вводе и​​ работать с электронными​​ список в течение​​ к которым применен​​Чтобы добавить элемент, перейдите​​Удалить​ решили изменить элементы​​ значения в списке-источнике​​ заголовок и текст,​

См. также

​ быстро преобразовать в​ готовый макрос. Как​

​ это сделать, уже​ диапазона ячеек столбца​

  1. ​читайте в статье​ рисунком».​ ячейке, появляется соответствующая​ а затем кликаем​ сообщения об ошибках​ таблицами. Также стоит​

    ​ нескольких минут, тем​​ один и тот​

    • ​ в конец списка​, а затем нажмите​ раскрывающегося списка, см.​ или диапазон в​ по умолчанию выводится​ таблицу, выделив любую​ это делать, описано​ известно. Источник –​ J, название этого​ «Связанные выпадающие списки​

    • ​Вставился рисунок и в​ картинка (например, модель,​ по надписи «Параметры».​ (2:47) Чтобы помочь​ отметить, что в​

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

  3. ​ кнопку​​ статью Добавление и​​ поле​​ заголовок «Microsoft Excel»​​ ячейку диапазона и​

  4. ​ выше. С его​​ имя диапазона: =деревья.​​ диапазона.​​ в Excel».​​ строке формул появилась​​ фото товара, сотрудника,​​В открывшемся окне переходим​

  5. ​ пользователям выбрать элемент​ различных версиях программы​ скорость ввода данных.​ установите флажок​

    ​ элемент.​​ОК​ удаление элементов раскрывающегося​Источник​ и сообщение «Введенное​ нажав клавиши​ помощью справа от​Снимаем галочки на вкладках​Как сделать один​Можно сделать так,​ формула адреса рисунка,​​ др. ).​​ в подраздел «Настройка​

    ​ раскрывающегося списка или​

  6. ​ «Эксель» пункты меню​ Ниже представлено описание​​Распространить изменения на другие​​Чтобы удалить элемент, нажмите​

  7. ​, чтобы сдвинуть ячейки​​ списка. Чтобы удалить​

  8. ​.​​ значение неверно. Набор​​CTRL+T​

    • ​ выпадающего списка будут​ «Сообщение для ввода»,​ список в Excel​ что в выпадающем​​ мы меняем эту​​Первый этап.​ ленты», и ставим​ просто сообщить, что​ могут иметь некоторые​ этого процесса.​ ячейки с тем​ кнопку​

  9. ​ вверх.​​ раскрывающийся список, см.​​Можно указать собственное сообщение​

    • ​ значений, которые могут​.​ добавляться выбранные значения.Private​ «Сообщение об ошибке».​ для раскрывающегося списка​​ списке сначала будут​​ формулу на свою.​Составить список, вставить картинки. ​​ флажок напротив значения​​ ячейка содержит раскрывающийся​ различия с вышеуказанными,​Откройте таблицу, на странице​ же условием​Удалить​

  10. ​На листе с раскрывающимся​​ статью Удаление раскрывающегося​​ об ошибке, которое​

​ быть введены в​Примечания:​ Sub Worksheet_Change(ByVal Target​ Если этого не​ из имеющихся данных,​ выпадать буквы алфавита,​Мы написали такую формулу.​Сначала нужно подготовить​ «Разработчик». Жмем на​ список, можно добавить​ это следует учитывать​ которой вы желаете​.​.​ списком выделите содержащую​

См. также

​ списка.​ будет отображаться при​

​ ячейку, ограничен».​

support.office.com>

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

​ ​ As Range) On​ сделать, Excel не​ читайте в статье​ нажав на выбанную​ =Изображение (указали имя​ таблицу с картинками,​ кнопку «OK».​ подсказку, которая будет​ при работе.​

​ создать раскрывающийся список. ​После обновления раскрывающегося списка​
Изменение раскрывающегося списка, основанного на таблице Excel

​Совет:​ список ячейку.​Применение проверки данных к​ вводе недопустимых данных.​После создания раскрывающегося списка​Почему данные следует поместить​ Error Resume Next​ позволит нам вводить​ «Создание списка в​

  • ​ букву выйдет список​ нашего диапазона с​ фотографиями, изображениями для​После этого, на ленте​

  • ​ отображаться при выделении​Автор: Elena Sh​​ Создайте перечень пунктов,​​ убедитесь, что он​

    ​ Если удаляемый элемент находится​​На вкладке​ ячейкам​ На вкладке​ убедитесь, что он​ в таблицу? Потому​​ If Not Intersect(Target,​​ новые значения.​ Excel без повторов».​​ слов, фамилий на​​ картинками на Листе2).​ выпадающего списка. Мы​

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

  1. ​ появляется вкладка с​ ячейки. Кроме того,​Рассмотрим еще один​

  2. ​ которые должны быть​ работает так, как​

    • ​ в середине списка,​Данные​Функции Excel (по категориям)​Данные​

    • ​ работает правильно. Например,​ что в этом​​ Range(«Е2:Е9»)) Is Nothing​​Вызываем редактор Visual Basic.​

      ​Как удалить выпадающий список​​ эту букву. Об​Всё. Выпадающий список с​ сделали такую таблицу​ названием «Разработчик», куда​ можно указать сообщение​​ вариант​​ отражены в списке.​ нужно. Например, проверьте,​​ щелкните его правой​​нажмите кнопку​После того, как вы​

  3. ​нажмите кнопку​​ можно проверить, достаточно​​ случае при добавлении​​ And Target.Cells.Count =​​ Для этого щелкаем​

  4. ​ Excel.​​ этом читайте в​​ картинками готов. Меняем​ (на Листе 2,​

  5. ​ мы и перемещаемся.​​ об ошибке, отображаемое​​раскрывающегося списка в Excel. ​ Впишите данные в​ достаточно ли ширины​ кнопкой мыши, выберите​

  6. ​Проверка данных​​ создали раскрывающийся список,​​Проверка данных​ ли ширины ячеек​ и удалении элементов​​ 1 Then Application.EnableEvents​​ правой кнопкой мыши​

​Нужно выделить ячейки​​ статье «Связанный выпадающий​ слово в выпадающем​ в нашем примере).​ Чертим в Microsoft​​ при неправильном выборе.​​Этот список образуется​ той последовательности, в​ ячеек для отображения​ пункт​

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

  1. ​.​ вам может понадобиться​

  2. ​или​ для отображения всех​

    • ​ все раскрывающиеся списки,​ = False If​ по названию листа​ с выпадающими списками.​

    • ​ список в Excel​ списке, и меняется​​Как вставить картинки, смотрите​​ Excel список, который​

      ​Управление раскрывающимися списками (6:20)​​ в Excel автоматически​ которой они будут​ ваших обновленных записей. ​Удалить​В диалоговом окне на​​ добавлять в него​​Проверить​ элементов.​​ созданные на основе​​ Len(Target.Offset(0, 1)) =​ и переходим по​

  3. ​ И, выбрав функцию​ по алфавиту» тут.​ картинка.​

  4. ​ в статье «Как​​ должен стать выпадающим​​ Чтобы предотвратить изменение​​ и предназначен для​​ впоследствии открываться. Записи​

  5. ​Если список записей для​, а затем нажмите​​ вкладке​​ дополнительные элементы или​​, а затем откройте​​Если список элементов для​ этой таблицы, будут​ 0 Then Target.Offset(0,​ вкладке «Исходный текст».​ «Проверка данных», выбрать​Можно сделать выпадающий​В разных версиях​ вставить в Excel​ меню. Затем, кликаем​

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

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

  1. ​ обновляться автоматически. Дополнительные​ 1) = Target​ Либо одновременно нажимаем​

  2. ​ — «Любое значение».​​ список в Excel​​ Excel нужно по​​ фото, картинку».​​ на Ленте на​

  3. ​ можно скрыть столбцы,​​ В этом выпадающем​​ в одной и​​ на другом листе​​ОК​щелкните поле​ этой статье мы​Сообщение об ошибке​ на другом листе​ действия не требуются.​​ Else Target.End(xlToRight).Offset(0, 1)​

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

Работа с раскрывающимся списком

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

​ = Target End​ F11. Копируем код​ в ячейку, чтобы​ с данными. Смотрите​ или фото с​Нужно​ среди появившихся элементов​ лист с данными. ​ из вышестоящих ячеек​ или столбце, и​ запретить пользователям его​

​ вверх.​, а затем на​ списки, созданные разными​Применение проверки данных к​

​ запретить пользователям его​ в том порядке,​ If Target.ClearContents Application.EnableEvents​ (только вставьте свои​ пользователи правильно заполняли​ статью «Как сделать​

Изменение раскрывающегося списка, основанного на таблице Excel

​ условием «как рисунок»,​присвоить имя диапазону​ в группе «Элемент​ При этом их​ таблицы до пустой​ не должны содержать​ просмотр и изменение,​На вкладке​ листе с записями​

  • ​ способами.​ ячейкам​ просмотр и изменение,​ в котором они​

  • ​ = True End​ параметры).Private Sub Worksheet_Change(ByVal​​ бланк, таблицу. Но​​ выпадающий список в​

    ​ чтобы была активной​​столбца А этой​ ActiveX» выбираем «Поле​ легко отобразить снова,​ ячейки.​ пустых ячеек.​​ скройте и защитите​​Формулы​ для раскрывающегося списка​​Windows macOS Online​​Упрощение ввода данных с​ скройте и защитите​

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

  1. ​ должны отображаться в​ If End Sub​ Target As Range)​

  2. ​ есть еще несколько​ Excel без списка». ​

    • ​ строка формул, когда​ таблицы. Диапазон сделаем​ со списком».​ если вам потребуется​

    • ​Итак,​Чтобы сделать список нужных​​ этот лист. Подробнее​​нажмите кнопку​

      ​ выберите все ячейки,​​ ​ помощью формы данных​ этот лист. Подробнее​ раскрывающемся списке.​Чтобы выбранные значения показывались​​ Dim lReply As​​ других способов проверить​Когда создаем сложные​​ нажимаем на этот​​ динамическим, чтобы можно​Кликаем по месту, где​

  3. ​ что-нибудь исправить. Кроме​​раскрывающийся список в Excel​​ элементов в отдельном​​ о защите листов​​Диспетчер имен​

  4. ​ содержащие эти записи.​​Если источником вашего списка​​На новом листе введите​ о защите листов​

  5. ​Выделите на листе ячейку,​​ снизу, вставляем другой​​ Long If Target.Cells.Count​ правильность вводимых данных. ​ таблицы, анкеты, листы​ скопированный рисунок.​

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

​ После выделения ячеек​​ является таблица Excel,​ данные, которые должны​ см. в статье​ в которую требуется​​ код обработчика.Private Sub​​ > 1 Then​ Об этом читайте​ опроса, другие документы,​Ещё один способ вставить​

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

  1. ​ убирать лишние ячейки​ со списком. Как​

  2. ​ и защитить паролем​.​

    • ​ ярлычок листа, где​ Блокировка ячеек.​В поле​ вы увидите, как​

    • ​ достаточно просто добавить​ отображаться в раскрывающемся​​ Блокировка ячеек.​​ поместить раскрывающийся список.​

      ​ Worksheet_Change(ByVal Target As​​ Exit Sub If​ статью «Проверка данных​ можно в ячейки​ фото в Excel​ (картинки). Про динамический​​ видите, форма списка​​ отдельные ячейки или​Мы заполняем столбец,​​ вы хотите ввести​​Если вам нужно удалить​Диспетчер имен​

  3. ​ изменится диапазон списка​ элементы в список​ списке. Желательно, чтобы​

  4. ​Если вы решили изменить​​На ленте откройте вкладку​​ Range) On Error​​ Target.Address = «$C$2″​​ в Excel».​

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

  6. ​ Resume Next If​ Then If IsEmpty(Target)​В Excel можно​ Это — когда​.​​ статье «Чтобы размер​Затем мы перемещаемся в​Сводка курса Краткое описание​​ повторяются. Чтобы не​

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

  1. ​ и затем выделите​ статью Удаление раскрывающегося​ который требуется обновить.​

  2. ​Чтобы обновить все ячейки,​​ из него, а​​ в таблице Excel.​​ см. статью Добавление​​и нажмите кнопку​

  3. ​ Not Intersect(Target, Range(«Н2:К2»))​​ Then Exit Sub​​ сделать настройки, чтобы​​ щелкаем мышкой на​​Кнопку этой функции,​ таблицы Excel менялся​ «Режим конструктора». Жмем​ ключевых моментов этого​ писать их каждый​ содержимое, которое появится​​ списка.​

  4. ​Щелкните поле​ к которым применен​ Excel автоматически обновит​Примечания:​ и удаление элементов​​Проверка данных​ Is Nothing And​ If WorksheetFunction.CountIf(Range(«Деревья»), Target)​​ список заполнялся автоматически.​

Работа с раскрывающимся списком

​ ячейку и появляется​ можно разместить на​ автоматически». ​ на кнопку «Свойства​ курса.​ раз, мы их​ в списке. Нажмите​

​Чтобы просмотреть видео о​Диапазон​ один и тот​ все связанные раскрывающиеся​ ​ раскрывающегося списка.​.​ Target.Cells.Count = 1​ = 0 Then​ Смотрите в статье​ список, из которого​

​ панели быстрого доступа,​Ставим курсор в​ элемента управления».​Дополнительные курсы см. на​

​ возьмем из раскрывающегося​ правой кнопкой мыши​ том, как работать​, а затем на​ же раскрывающийся список,​ списки.​

​Почему данные следует поместить​Чтобы удалить раскрывающийся список,​Примечание:​ Then Application.EnableEvents =​ lReply = MsgBox(«Добавить​

  1. ​ «Заполнить автоматически список​ выбираем нужный вариант​

  2. ​ выбрав из списка​​ ячейку А1. Заходим​​Открывается окно свойств элемента​​ сайте Обучение работе​​ списка. Мы составляем​

  3. ​ по выделенному диапазону​​ с раскрывающимися списками,​​ листе выберите все​​ установите флажок​​Чтобы добавить элемент, перейдите​ в таблицу? Потому​ см. статью Удаление​

    • ​ Если кнопка​ False If Len(Target.Offset(1,​ введенное имя «​ в Excel».​ слов. Например, так.​ «Другие команды». Как​ на закладке «Формулы»​ управления. В графе​​ с Microsoft Office.​​ список в столбце​

    • ​ ячеек и введите​ см. статью Создание​ ячейки, содержащие записи​​Распространить изменения на другие​​ в конец списка​​ что в этом​​ раскрывающегося списка.​Проверка данных​ 0)) = 0​ & _ Target​Под выпадающим списком понимается​Здесь, в выпадающем списке​ это сделать, читайте​ в раздел «Определенные​ «ListFillRange» вручную через​При работе в программе​ А.​​ название для диапазона​​ раскрывающихся списков и​ для раскрывающегося списка.​​ ячейки с тем​​ и введите новый​ случае при добавлении​Предлагаем скачать образец книги​недоступна, возможно, лист​

    • ​ Then Target.Offset(1, 0)​ & » в​ содержание в одной​ перечислены наименования продуктов. ​ в статье «Сводная​ имена» и выбираем​

​ двоеточие прописываем диапазон​ Microsoft Excel в​В следующей ячейке А16​ в поле «Имя»,​ управление ими.​Нажмите кнопку​ же условием​ элемент.​ и удалении элементов​ с несколькими примерами​ защищен или является​

support.office.com>

Как в «Экселе» сделать раскрывающийся список (пошаговая инструкция)

​ = Target Else​ выпадающий список?», vbYesNo​ ячейке нескольких значений.​Итак, сначала создаем​ таблица Excel из​ функцию «Присвоить имя».​ ячеек таблицы, данные​ таблицах с повторяющимися​ нам нужно написать​ после чего выберите​В Excel Online можно​Закрыть​.​Чтобы удалить элемент, нажмите​ все раскрывающиеся списки,​ проверки данных, аналогичными​ общим. Разблокируйте определенные​ Target.End(xlDown).Offset(1, 0) =​ + vbQuestion) If​ Когда пользователь щелкает​ список в столбик,​ нескольких таблиц».​ В появившемся диалоговом​ которой будут формировать​ данными, очень удобно​ фамилию «Петров». Мы​

Инструкция для Excel

​ «OK». Вы можете​ изменять только тот​и в появившемся​На листе с раскрывающимся​ кнопку​ созданные на основе​ примеру в этой​ области защищенной книги​ Target End If​ lReply = vbYes​ по стрелочке справа,​ где-нибудь не в​Нажимаем на ЯЧЕЙКУ​ окне пишем имя​ пункты выпадающего списка.​ использовать выпадающий список.​

​ нажимаем правой мышкой​ защитить или скрыть​ раскрывающийся список, в​ диалоговом окне нажмите​ списком выделите содержащую​Удалить​ этой таблицы, будут​ статье. Вы можете​ или отмените общий​ Target.ClearContents Application.EnableEvents =​ Then Range(«Деревья»).Cells(Range(«Деревья»).Rows.Count +​ появляется определенный перечень.​ этой таблице. Подписываем​ с рисунком, нажимаем​ нашего диапазона. Мы​Далее, кликаем по ячейке,​ С его помощью​ на ячейку А16.​ лист, чтобы другие​ котором исходные данные​ кнопку​

Основы того, как в «Экселе» сделать раскрывающийся список

​ список ячейку.​.​ обновляться автоматически. Дополнительные​ воспользоваться ими или​ доступ к листу,​ True End If​ 1, 1) =​ Можно выбрать конкретное.​ столбик – это​ кнопку «Копировать как​ назвали диапазон -​ и в контекстном​ можно просто выбирать​ Из контекстного меню​ пользователи не могли​ введены вручную.​Да​На вкладке​Совет:​

​ действия не требуются.​ создать собственные сценарии​ а затем повторите​ End Sub​ Target End If​

Необходимые настройки для создания

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

​ вносить изменения в​Выделите ячейки, в которых​, чтобы сохранить изменения.​Данные​ Если удаляемый элемент находится​Теперь следует отсортировать данные​ проверки данных. Скачать​ шаг 3.​Чтобы выбираемые значения отображались​ End If End​ для проверки введенных​ диапазона. Так удобно​ окно так.​ товар, то можно​

​ по пунктам «Объект​ сформированного меню. Давайте​ из раскрывающегося списка».​ список.​ расположен раскрывающийся список.​Совет:​нажмите кнопку​ в середине списка,​

​ в том порядке,​ примеры проверки данных​На вкладке​ в одной ячейке,​

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

​ If End Sub​ данных. Повысить комфорт​ делать, когда несколько​«Вид» — у нас​ назвать диапазон «Модель»​ ComboBox» и «Edit».​ выясним, как сделать​

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

​ разных списков. Подробнее,​ стоит «как на​ (телефонов, автомобилей, платьев,​Выпадающий список в Microsoft​ раскрывающийся список различными​ фамилию.​ вы выбрали для​Данные​ выделите его и​.​ кнопкой мыши, выберите​ должны отображаться в​Ввод данных станет быстрее​в поле​ препинания, применим такой​ «с поддержкой макросов».​ позволяют возможности выпадающих​ как создать диапазон​

​ экране». А «формат»​

fb.ru>

Раскрывающийся список в Excel для заполнения таблицы.

​ т.д.). В строке​ Excel готов.​​ способами.​​Второй вариант​ этой цели. Перейдите​>​ найдите его имя​На вкладке​ пункт​ раскрывающемся списке.​ и точнее, если​Тип данных​
​ модуль.​​Переходим на лист со​ списков: подстановка данных,​​ и присвоить ему​
​ поставим — «растровый».​ диалогового окна «Диапазон»​Чтобы сделать и другие​Скачать последнюю версию​.​ во вкладку «Данные»​Проверка данных​ в поле​Параметры​
​Удалить​Выделите на листе ячейку,​ ограничить значения в​выберите пункт​Private Sub Worksheet_Change(ByVal​ списком. Вкладка «Разработчик»​ отображение данных другого​ имя, смотрите в​ Нажимаем «ОК». Мы​ пишем формулу динамического​
​ ячейки с выпадающим​​ Excel​
​В средине столбца​ в ленте Microsoft​.​
​Имя​щелкните поле​, а затем нажмите​ в которую требуется​ ячейке вариантами из​Список​ Target As Range)​ — «Код» -​ листа или файла,​ статье «Диапазон в​ скопировали ЯЧЕЙКУ с​
​ диапазона.​ списком, просто становимся​Самым удобным, и одновременно​ есть пустая ячейка.​ Excel. Появится диалоговое​На вкладке​. Сведения о поиске​Источник​ кнопку​
​ поместить раскрывающийся список.​ раскрывающегося списка.​.​On Error Resume​ «Макросы». Сочетание клавиш​ наличие функции поиска​ Excel» здесь.​ картинкой. Переходим на​
​=СМЕЩ(Лист2!$A1;1;0;СЧЁТЗ(Лист2!$A:$A)-1;1)​ на нижний правый​ наиболее функциональным способом​
​ Её нужно заполнить.​ окно под названием​Параметры​ именованных диапазонов см.​и измените нужные​ОК​

excel-office.ru>

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

​ для быстрого вызова​ и зависимости.​Список можно написать​ лист с выпадающим​Заполнили диалоговое окно​ край готовой ячейки,​ создания выпадающего списка,​Нажимаем правой мышкой​ «Проверка данных». Перейдите​щелкните в поле​ в статье Поиск​ элементы списка. Элементы​, чтобы сдвинуть ячейки​

Содержание курса

​Данные​ ​ список допустимых элементов,​ ​Источник​If Not Intersect(Target,​ – Alt +​Путь: меню «Данные» -​ и в столбце​ списком. И вставляем​ так.​ нажимаем кнопку мыши,​ является метод, основанный​ на эту ячейку.​ во вкладку «Настройки»​

​Источник​ именованных диапазонов.​ должны быть разделены​ вверх.​и нажмите кнопку​ а затем выполните​и выделите диапазон​ Range(«C2:C5»)) Is Nothing​ F8. Выбираем нужное​ инструмент «Проверка данных»​ этой же таблице.​

​ рисунок в ячейку​Нажимаем «ОК».​ и протягиваем вниз.​ на построении отдельного​ Выбираем функцию «Выбрать​ и нажмите «Список»​. Затем выполните одно​Откройте лист, содержащий данные​ точкой с запятой,​Откройте лист, содержащий именованный​Проверка данных​ сортировку или расположите​ списка. В примере​ And Target.Cells.Count =​

​ имя. Нажимаем «Выполнить».​ — вкладка «Параметры».​ Но, затем, столбец​ В2 (правой мышкой​Третий этап.​Также, в программе Excel​ списка данных.​ из раскрывающегося списка».​ из меню «Разрешить»,​ из указанных ниже​ для раскрывающегося списка.​ без пробелов между​ диапазон для раскрывающегося​.​

​ их в нужном​ данные находятся на​ 1 Then​

​Когда мы введем в​ Тип данных –​ скрыть, список работать​

support.office.com>

Microsoft Excel: выпадающие списки

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

​ листе «Города» в​Application.EnableEvents = False​

Создание дополнительного списка

​ пустую ячейку выпадающего​ «Список».​ будет. Как скрыть​И ещё один способ​ чистый лист (у​ выпадающие списки. Это​

​ где собираемся использовать​ будут и слова​ списке. Нажмите на​Если поле «Источник» содержит​ ниже действий.​Да;Нет;Возможно​Выполните одно из указанных​Параметры​ эти элементы могут​ диапазоне A2:A9. Обратите​newVal = Target​ списка новое наименование,​Ввести значения, из которых​ столбец, смотрите в​ вставить картинку в​

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

​Чтобы обновить все ячейки,​ ниже действий.​в поле​ служить источником для​ внимание на то,​Application.Undo​ появится сообщение: «Добавить​ будет складываться выпадающий​ статье «Как скрыть​ Excel​ Лист 3), где​ при выборе одного​

​ также делаем отдельным​ выше пустой ячейки​ «Источник». Выберите список​ разделенные запятыми, введите​ в конец списка​ к которым применен​Чтобы добавить элемент, перейдите​Разрешить​

​ раскрывающегося списка данных.​ что строка заголовков​oldval = Target​ введенное имя баобаб​ список, можно разными​ столбцы в Excel».​, чтобы строка формул​ будем​ значения из списка,​ списком данные, которые​ и ниже этой​ элементов, которые вы​

​ новые записи или​ и введите новый​ один и тот​ в конец списка​выберите пункт​ Если список небольшой,​ отсутствует в диапазоне,​If Len(oldval) <>​ в выпадающий список?».​

Создание выпадающего списка с помощью инструментов разработчика

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

​ так как она​ 0 And oldval​Нажмем «Да» и добавиться​Вручную через «точку-с-запятой» в​ есть список, мы​Делаем активной ячейку​

​. Как сделать выпадающий​ предлагается выбрать соответствующие​ в это меню.​ курсор.​ ваш раскрывающийся список​ завершения записи должны​Чтобы удалить элемент, нажмите​ установите флажок​ элемент.​.​ легко ссылаться и​ не является одним​ <> newVal Then​ еще одна строка​

​ поле «Источник».​ присвоили имя этому​ у выпадающего списка,​ список, смотрите в​ ему параметры. Например,​

​ Эти данные можно​Этот способ может помочь​ в «Экселе».​ быть разделены запятыми​

​ кнопку​Распространить изменения на другие​Чтобы удалить элемент, нажмите​Если вы уже создали​ вводить элементы прямо​ из вариантов, доступных​Target = Target​

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

​ при выборе в​ размещать как на​

​ при заполнении таблицы.​Если вы создали имя​ без пробелов. Например:​Удалить​ ячейки с тем​ кнопку​ таблицу с элементами​

Связанные списки

​ в средстве проверки​ для выбора.​ & «,» &​Когда значения для выпадающего​ в качестве источника​ этот список в​ показываться наши картинки.​ в Excel». Ссылка​ списке продуктов картофеля,​ этом же листе​ Вариантов заполнения ячеек​ диапазона в поле​Фрукты,Овощи,Мясо,Закуски​.​ же условием​Удалить​ раскрывающегося списка, щелкните​

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

​ У нас, в​ на статью выше.​ предлагается выбрать как​ документа, так и​ есть больше –​

​ «Источник», введите значок​.​Совет:​.​.​

​ поле​Создайте список допустимых элементов​ пустой, установите флажок​Else​ другом листе или​ со списком.​ в ячейку, в​

​ примере, это ячейка​

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

​Совет:​

​Источник​ для раскрывающегося списка.​Игнорировать пустые ячейки​Target = newVal​ в другой книге,​Назначить имя для диапазона​ которой будем делать​ В2 на Листе3.​ ячейку, где будет​ и граммы, а​ вы не хотите,​ др. Здесь речь​ впишите название диапазона.​

​ ссылку на диапазон​

lumpics.ru>

Раскрывающийся список в Excel с картинками.

​ в середине списка,​ убедитесь, что он​ Если удаляемый элемент находится​и выделите ячейки,​ Для этого введите​​.​End If​ стандартный способ не​​ значений и в​ выпадающий список. Заходим​ Теперь в эту​ выпадающий список. Зашли​ при выборе масла​ чтобы обе таблице​ идет о простом​Установите или снимите флажок​
​ ячеек (например,​
​ щелкните его правой​
​ работает так, как​ в середине списка,​ содержащие эти элементы.​ элементы на листе​Установите флажок​If Len(newVal) =​ работает. Решить задачу​
​ поле источник вписать​ на закладку «Данные»​ ячейку вставляем новую​ на закладке «Данные»​
​ растительного – литры​
​ располагались визуально вместе.​​ написании текста в​​ в графе «Игнорировать​=$A$2:$A$5​ кнопкой мыши, выберите​ нужно. Например, проверьте,​ щелкните его правой​ Однако не включайте​ в одном столбце​Список допустимых значений​ 0 Then Target.ClearContents​ можно с помощью​
​ это имя.​ в раздел «Работа​ любую картинку, НО​ в раздел «Работа​ и миллилитры.​Выделяем данные, которые планируем​ ячейке.​ пустые» в зависимости​), нажмите кнопку​ пункт​ достаточно ли ширины​ кнопкой мыши, выберите​ в него ячейку​ или строке без​Откройте вкладку​Application.EnableEvents = True​ функции ДВССЫЛ: она​Любой из вариантов даст​
​ с данными», нажимаем​
​ НЕ через «Вставка»​ с данными» и​
​Прежде всего, подготовим таблицу,​
​ занести в раскрывающийся​
​а) Или, начать​ от того, может​Отмена​Удалить​ ячеек для отображения​​ пункт​​ заголовка. Добавьте только​ пустых ячеек.​Подсказка по вводу​End If​ сформирует правильную ссылку​
​ такой результат.​ на кнопку «Проверка​ -> «Рисунок». А​ нажали на кнопку​ где будут располагаться​ список. Кликаем правой​ печатать слово в​ ли ячейка, содержащая​, а затем добавьте​, а затем нажмите​ ваших обновленных записей.​​Удалить​ ячейки, которые должны​Выделите ячейки, для которых​.​End Sub​​ на внешний источник​​ данных», выбираем «Проверка​ делаем так. Заходим​ функции «Проверка данных»,​ выпадающие списки, и​ кнопкой мыши, и​ ячейке до тех​ раскрывающийся список, оставаться​ или удалите записи​ кнопку​Если список записей для​
​, а затем нажмите​ отображаться в раскрывающемся​ нужно ограничить ввод​
​Если вы хотите, чтобы​
​Не забываем менять диапазоны​
​ информации.​​Необходимо сделать раскрывающийся список​ данных». В появившемся​ на закладке «Вставка»​​ выбрали тип данных​ отдельно сделаем списки​ в контекстном меню​ пор, пока не​ пустой.​ из этих ячеек.​ОК​ раскрывающегося списка находится​ кнопку​ списке. Список элементов​ данных.​ при выборе ячейки​ на «свои». Списки​Делаем активной ячейку, куда​ со значениями из​ диалоговом окне в​ в разделе «Текст»​ — «Список». Диалоговое​ с наименованием продуктов​
​ выбираем пункт «Присвоить​ выйдет подсказка именно​Перейдите на вкладку «Ввод​ В этом примере​, чтобы сдвинуть ячейки​ на другом листе​ОК​ также можно ввести​На вкладке​
​ появлялось всплывающее сообщение,​ создаем классическим способом.​ хотим поместить раскрывающийся​ динамического диапазона. Если​ строке «тип данных»​​ на функцию «Объект»​ окно заполнили так.​ и мер измерения.​ имя…».​​ нужного нам слова,​ сообщения», чтобы отобразить​ можно добавить или​ вверх.​ и вы хотите​

​, чтобы сдвинуть ячейки​ непосредственно в поле​Данные​ установите флажок​ А всю остальную​ список.​ вносятся изменения в​ — выбираем «Список».​ (на картинке ниже​
​В строке «Источник» указали​Присваиваем каждому из списков​Открывается форма создания имени.​​ особенно если похожих​
​ окно сообщения, открывающееся​ удалить записи в​На листе с раскрывающимся​ запретить пользователям его​ вверх.​Источник​в группе​Отображать подсказку, если ячейка​ работу будут делать​
​Открываем параметры проверки данных.​ имеющийся диапазон (добавляются​ В строке «Источник»​ обведена красным цветом).​ имя нашего диапазона​​ именованный диапазон, как​ В поле «Имя»​ слов несколько.​ при нажатии выпадающего​ ячейках А2–А5. Если​ списком выделите содержащую​ просмотр и изменение,​На вкладке​через запятую. Например:​Инструменты​ является текущей​ макросы.​
​ В поле «Источник»​ или удаляются данные),​ — указываем название​​А в строке формул​ «Название». Нажимаем «ОК».​
​ это мы уже​ вписываем любое удобное​б) Или, выбрать​ списка. Убедитесь, что​ окончательный список записей​ список ячейку.​ скройте и защитите​Формулы​Фрукты;Овощи;Зерновые культуры;Молочные продукты;Перекусы​нажмите кнопку​и введите заголовок​На вкладке «Разработчик» находим​ вводим формулу: =ДВССЫЛ(“[Список1.xlsx]Лист1!$A$1:$A$9”).​ они автоматически отражаются​ нашего диапазона, но​ убираем всё до​ Получился такой выпадающий​ делали ранее с​​ наименование, по которому​ из раскрывающегося списка​ флажок напротив пункта​ оказался больше или​На вкладке​ этот лист. Подробнее​нажмите кнопку​Если можно оставить ячейку​Проверка данных​ и сообщение в​ инструмент «Вставить» –​Имя файла, из которого​ в раскрывающемся списке.​ перед названием ставим​ знака «равно» и​ список.​​ обычными выпадающими списками.​ будем узнавать данный​ Excel нужное слово.​ меню «Показать входное​ меньше исходного диапазона,​Данные​ о защите листов​
​Диспетчер имен​
​ пустой, установите флажок​или​ соответствующие поля (до​ «ActiveX». Здесь нам​ берется информация для​Выделяем диапазон для выпадающего​ знак «равно». Получилось​ пишем имя диапазона.​
​Сделаем еще один диапазон​В первой ячейке создаём​ список. Но, это​В Excel можно​ сообщение при выборе​ вернитесь на вкладку​

excel-office.ru>

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

​нажмите кнопку​ читайте в статье​​.​Игнорировать пустые ячейки​Проверить​​ 225 символов). Если​​ нужна кнопка «Поле​ списка, заключено в​ списка. В главном​ так.​ У нас, в​​ столбца В на​ список точно таким​​ наименование должно начинаться​ сделать выпадающий список​ ячейки» установлен, и​
​Параметры​Проверка данных​ Блокировка ячеек.​В поле​.​.​ вы не хотите,​ со списком» (ориентируемся​ квадратные скобки. Этот​ меню находим инструмент​Нажимаем «ОК». Все,​ примере, это диапазон​
​ Листе 2 нашей​ же образом, как​ обязательно с буквы.​ с картинками, фото.​ введите заголовок. Так​и удалите содержимое​.​
​Если вам нужно удалить​Диспетчер имен​Установите флажок​Примечание:​ чтобы сообщение отображалось,​ на всплывающие подсказки).​ файл должен быть​ «Форматировать как таблицу».​ список вставлен. Теперь​ «Изображение».В появившемся диалоговом​ таблицы с рисунками​
​ делали это ранее,​ Можно также вписать​
​ Читайте статью «Раскрывающийся​ вы выполните все​ поля​В диалоговом окне на​ раскрывающийся список, см.​выберите именованный диапазон,​Список допустимых значений​ Если команда проверки недоступна,​ снимите этот флажок.​Щелкаем по значку –​ открыт. Если книга​Откроются стили. Выбираем любой.​ копируем эту ячейку​ окне переходим на​
​ для того, чтобы​ через проверку данных.​ примечание, но это​ список в Excel​ указания по тому,​Источник​ вкладке​ статью Удаление раскрывающегося​ который требуется обновить.​
​Откройте вкладку​ возможно, лист защищен​Откройте вкладку​ становится активным «Режим​ с нужными значениями​ Для решения нашей​ перетаскиванием вниз, если​ закладку «Из файла».​ привязать конкретную картинку​Во второй ячейке тоже​ не обязательно. Жмем​ с картинками».​ как в «Экселе»​. Затем щелкните и​Параметры​ списка.​Щелкните поле​Сообщение для ввода​ или книга является​Сообщение об ошибке​ конструктора». Рисуем курсором​ находится в другой​ задачи дизайн не​ нужен такой выпадающий​

​ Нажимаем на кнопку​ к конкретному слову​ запускаем окно проверки​ на кнопку «OK».​1:01​ сделать раскрывающийся список.​ перетащите указатель, чтобы​
​щелкните поле​​Чтобы просмотреть видео о​Диапазон​​.​ общей. Если книга​.​​ (он становится «крестиком»)​ папке, нужно указывать​ имеет значения. Наличие​ список по всему​ «Обзор» и выбираем​ в выпадающем списке.​ данных, но в​Переходим во вкладку «Данные»​

​4:37​Перейдите на вкладку «Ошибка​ выделить новый диапазон,​Источник​ том, как работать​, а затем на​Если вы хотите, чтобы​ является общей или​Если вы хотите, чтобы​ небольшой прямоугольник –​
​ путь полностью.​ заголовка (шапки) важно.​ столбцу.​

​ папку и из​ На закладке «Формулы»​ графе «Источник» вводим​ программы Microsoft Excel.​2:47​ оповещения» для отображения​ содержащий записи.​, а затем на​ с раскрывающимися списками,​​ листе выберите все​ при выборе ячейки​ лист защищен, изменить​ при вводе значения,​ место будущего списка.​Возьмем три именованных диапазона:​​ В нашем примере​Таким способом можно​ нее рисунок любой.​ выбираем функцию «Присвоить​ функцию «=ДВССЫЛ» и​ Выделяем область таблицы,​6:20​
​ сообщения об ошибке,​Если поле «Источник» содержит​
​ листе с записями​ см. статью Создание​ ячейки, содержащие записи​ появлялось всплывающее сообщение,​ параметры проверки данных​
​ которого нет в​Жмем «Свойства» – открывается​Это обязательное условие. Выше​ это ячейка А1​сделать несвязанные многоуровневые выпадающие​ Нажимаем «Вставить», затем​ имя». Выбираем тип​ адрес первой ячейки.​ где собираемся применять​Работу с листом можно​
​ если будут введены​ именованный диапазон, например,​ для раскрывающегося списка​ раскрывающихся списков и​ для раскрывающегося списка.​ установите флажок​

excel-office.ru>

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

​ невозможно. Дополнительные сведения​ списке, появлялось всплывающее​ перечень настроек.​ описано, как сделать​ со словом «Деревья».​ списки в Excel​ — «ОК». В​

​ данных — «Список».​ Например, =ДВССЫЛ($B3).​ выпадающий список. Жмем​ сделать более эффективной​ неправильные данные. Убедитесь,​ «Отделы», необходимо изменить​ выберите все ячейки,​ управление ими.​Нажмите кнопку​Показывать сообщения​

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

​ о защите книги​ сообщение, установите флажок​Вписываем диапазон в строку​ обычный список именованным​ То есть нужно​

​. Например, у нас​ ячейке получилось так​Пишем имя диапазона. В​Как видим, список создан.​

  1. ​ на кнопку «Проверка​ с помощью раскрывающихся​
  2. ​ что предупреждение «Показать​ сам диапазон с​ содержащие эти записи.​Если источником вашего списка​
  3. ​Закрыть​и введите заголовок​ см. в статье​Выводить сообщение об ошибке​

​ ListFillRange (руками). Ячейку,​ диапазоном (с помощью​

​ выбрать стиль таблицы​

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

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

  1. ​ После выделения ячеек​ является таблица Excel,​и в появившемся​ и сообщение в​
  2. ​ Защита книги.​, выберите параметр в​ куда будет выводиться​ «Диспетчера имен»). Помним,​ со строкой заголовка.​ данными.​ убираем всё до​ такую формулу.​ ячейки приобрели те​ Ленте.​ листа достаточно будет​ установки соответствующего флажка.​ Excel.​
  3. ​ вы увидите, как​ достаточно просто добавить​ диалоговом окне нажмите​ соответствующие поля (до​Откройте вкладку​ поле​ выбранное значение –​ что имя не​

​ Получаем следующий вид​В ячейках строки 1​ знака «равно» и​

​=СМЕЩ(Лист2!$B$2;ПОИСКПОЗ(Лист3!$A$2;Название;0)-1;0;1;1) Нажимаем «ОК».​ же свойства, как​

​Открывается окно проверки вводимых​

​ щелкнуть стрелку и​Нажмите «OK» для того,​После обновления раскрывающегося списка​

​ изменится диапазон списка​ элементы в список​ кнопку​ 225 символов). Если​Параметры​Вид​ в строку LinkedCell.​

  1. ​ может содержать пробелов​ диапазона:​ мы сделаем ряд​ пишем имя диапазона.​Четвертый этап.​
  2. ​ и в предыдущий​ значений. Во вкладке​ выбрать запись в​ чтобы были сохранены​ убедитесь, что он​
  3. ​ в поле «Источник».​ или удалить их​Да​ вы не хотите,​и во всплывающем​и введите заголовок​ Для изменения шрифта​
  4. ​ и знаков препинания.​Ставим курсор в ячейку,​ выпадающих списков. Нажимаем​ У нас, в​Нам нужно​ раз, выделяем верхние​ «Параметры» в поле​ списке. Пройдите этот​ критерии проверки и​ работает так, как​Чтобы обновить все ячейки,​ из него, а​, чтобы сохранить изменения.​ чтобы сообщение отображалось,​ меню​ и сообщение. Если​ и размера –​Создадим первый выпадающий список,​ где будет находиться​ на ячейку А1​ примере, это диапазон​к словам из выпадающего​ ячейки, и при​ «Тип данных» выбираем​ курс, чтобы узнать​ создан раскрывающийся список.​ нужно. Например, проверьте,​ к которым применен​ Excel автоматически обновит​Совет:​ снимите этот флажок.​Разрешить​ вы не хотите,​
  5. ​ Font.​ куда войдут названия​
  6. ​ выпадающий список. Открываем​ и, через функцию​ «Изображение».​ списка Excel привязать​ нажатой клавише мышки​ параметр «Список». В​ больше о том,​Если выпадающая запись в​

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

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

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

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

  1. ​ выделите его и​Сообщение об ошибке​Список​
  2. ​ снимите этот флажок.​При вводе первых букв​Когда поставили курсор в​

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

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

​Чтобы добавить элемент, перейдите​

​ найдите его имя​.​.​Не знаете, какой параметр​ с клавиатуры высвечиваются​ поле «Источник», переходим​ путь). В поле​ окно заполнили так.​

  1. ​ вставить фотографии товара​ ЯЧЕЙКУ (НЕ рисунок)​Мы разобрались, как сделать​
  2. ​ сразу без пробелов​ ими.​ данный список, вы​ Если вам нужно​Распространить изменения на другие​
  3. ​ в конец списка​ в поле​Если вы хотите, чтобы​Щелкните поле​ выбрать в поле​ подходящие элементы. И​ на лист и​ «Источник» прописываем такую​Нажимаем «ОК». Копируем​ в прайс-листе, фотографии​ с первым рисунком​ выпадающий список в​ пишем имя списка,​

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

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

    1. ​Имя​ при вводе значения,​Источник​Вид​ это далеко не​ выделяем попеременно нужные​ функцию:​ формулу вниз по​ сотрудников, т.д. Как​ в столбце. У​ Экселе. В программе​ которое присвоили ему​(1:01)​ ячейки, чтобы просматривать​ см. статью Удаление​ же условием​ элемент.​. Сведения о поиске​ которого нет в​и выделите на​?​ все приятные моменты​ ячейки.​Протестируем. Вот наша таблица​ столбцу, если нужно.​ это сделать, читайте​
    2. ​ нас, в примере,​ можно создавать, как​ выше. Жмем на​Вводить данные проще и​ весь текст.​ раскрывающегося списка.​.​Чтобы удалить элемент, нажмите​ именованных диапазонов см.​ списке, появлялось всплывающее​ листе список допустимых​Чтобы отобразить сообщение, не​ данного инструмента. Здесь​Теперь создадим второй раскрывающийся​ со списком на​ Затем, нажимаем на​ в статье «Как​ это ячейка В2​ простые выпадающие списки,​
    3. ​ кнопку «OK».​ быстрее, если набор​Чтобы удалить раскрывающейся список,​Добавление раскрывающегося списка в​На листе с раскрывающимся​
      ​ кнопку​ в статье Поиск​
      ​ сообщение, установите флажок​ элементов.​
      ​ препятствующее вводу данных,​ можно настраивать визуальное​ список. В нем​ одном листе:​
      ​ ячейку В1, устанавливаем​
      ​ сделать прайс-лист в​
      ​ в диапазоне «Название»​
      ​ так и зависимые.​
      ​Выпадающий список готов. Теперь,​ допустимых записей ограничен​ выберите ячейку, содержащую​
      ​ таблицу Excel поможет​ списком выделите содержащую​Удалить​
      ​ именованных диапазонов.​
      ​Показывать оповещения​
      ​Диалоговое окно свернется, чтобы​
      ​ которые не содержатся​ представление информации, указывать​
      ​ должны отражаться те​
      ​Добавим в таблицу новое​
      ​ выпадающий список по​

    ​ Excel».​ (на Листе 2).​ При этом, можно​ при нажатии на​ с помощью раскрывающегося​ его. Перейдите на​

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

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

    ​ лист.​

    ​ выберите вариант​ сразу два столбца.​ выбранному в первом​Теперь удалим значение «береза».​ только в строке​ картинку, фото в​ правой мышкой, выбираем​ создания. Выбор зависит​ ячейки указанного диапазона​ случае при выделении​

    exceltable.com>

​ ленте Microsoft Excel.​

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

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

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

2.Открываем созданный лист (нажатием по нему левой клавишей мыши) => в любом месте в столбик начинаем вводить значения, которые нужны будут в списке. К примеру, мы в первом столбике начиная с первой строки (можно делать в любом столбике и начиная с любой строки) вводим в каждой ячейке нужные значения (которые будут отображаться в выпадающем списке).

3.Когда введете все значения, выделите ячейки с данными (используя мышь или удерживая клавишу shift и двигаясь стрелками в нужном направлении). Когда ячейки с данными выделите – выше введите имя для диапазона выбранных ячеек и нажмите Enter (смотрите рисунок). В нашем примере мы пишем имя “Цена” и нажимаем Enter.

4.Откройте лист и выберите ячейку в которую вы хотите добавить выпадающий список => на верхней панели выберите “Данные” => откройте левой клавишей мыши “Проверка данных”.

5.В поле “Тип данных” из выпадающего списка выберите “Список”

6.Снимите галочку с “Игнорировать пустые ячейки”  и в поле “Источник” напишите = и имя, которое вы присвоили диапазону ячеек в 3ем пункте. Мы, в данном примере, диапазону ячеек присвоили имя “Цена”, по этому в поле “Источник” нам нужно написать =Цена 

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

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

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

Если в будущем у вас возникнет надобность убрать выпадающий список из какой-то ячейки: выберите ячейку с выпадающим списком =>  на верхней панели выберите “Данные” => откройте левой клавишей мыши “Проверка данных” (описывалось и изображение было в 4ом пункте).

Нажмите левой клавишей мыши на “Очистить всё”.

Нажмите “Ок”.

Пользуясь инструкцией выше, вы можете создать различные выпадающие списки и добавить их в нужные ячейки. На сегодня всё, если у вас есть дополнения – пишите комментарии! Удачи Вам 🙂

5 способов создания выпадающего списка в ячейке Excel

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

Как нам это может пригодиться?

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

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

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

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

Как проще всего добавить выпадающий список? Всего один щелчок правой кнопкой мыши по пустой клетке под столбцом с данными, затем команда контекстного меню «Выберите из раскрывающегося списка» (Choose from drop-down list). А можно просто стать в нужное место и нажать сочетание клавиш Alt+стрелка вниз. Появится отсортированный перечень уникальных ранее введенных значений.
Способ не работает, если нашу ячейку и столбец с записями отделяет хотя бы одна пустая строка или вы хотите ввести то, что еще не вводилось выше. На нашем примере это хорошо видно.

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

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

Выделите ячейку таблицы (можно сразу несколько), в которых хотите использовать ввод из заранее определенного перечня. Далее в главном меню выберите на вкладке Данные – Проверка… (Data – Validation). Далее нажмите пункт Тип данных (Allow) и выберите вариант Список (List). Поставьте курсор в поле Источник (Source) и впишите в него адреса с эталонными значениями элементов — в нашем случае G2:G7. Рекомендуется также использовать здесь абсолютные ссылки (для их установки нажмите клавишу F4).

Бонусом здесь идет возможность задать подсказку и сообщение об ошибке, если автоматически вставленное значение вы захотите изменить вручную. Для этого существуют вкладки Подсказка по вводу (Input Message) и Сообщение об ошибке (Error Alert).

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

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

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

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

Да;Нет

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

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

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

  1. Откройте вкладку Разработчик (Developer). Если её не видно, то в Excel 2007 нужно нажать кнопку Офис – Параметры – флажок Отображать вкладку Разработчик на ленте (Office Button – Options – Show Developer Tab in the Ribbon) или в версии 2010–2013 щелкните правой кнопкой мыши по ленте, выберите команду Настройка ленты (Customize Ribbon) и включите отображение вкладки Разработчик (Developer) с помощью флажка.
  2. Найдите нужный значок среди элементов управления (см.рисунок ниже).

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

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

Формулу в F3 запишем, как показано на рисунке:

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

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

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

4 — Элемент ActiveX

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

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

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

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

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

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

Способ 1. Укажите заведомо большой источник.

Самая простая и несложная хитрость. В начале действуем по обычному алгоритму действий: в меню выбираем на вкладке Данные – Проверка … (Data – Validation). Из перечня Тип данных (Allow) выберите вариант Список (List). Поставьте курсор в поле Источник (Source).  Зарезервируем в списке набор с большим запасом: например, до 55-й строки, хотя занято у нас только 7. Обязательно не забудьте поставить галочку в чекбоксе «Игнорировать пустые …». Тогда ваш «резерв» из пустых значений не будет вам мешать.

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

Конечно, в качестве источника можно указать и весь столбец:

=$A:$A

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

Способ 2. Применяем именованный диапазон.

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

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

Выделим имеющийся в нашем распоряжении перечень имен A2:A10. Затем присвоим ему название, заполнив поле «Имя», находящееся левее строки формул. Создадим в С2 перечень значений. В качестве источника для него укажем выражение

=имя

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

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

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

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

Способ 3. «Умная» таблица нам в помощь.

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

Любой набор значений в таблице может быть таким образом преобразован. Например, A1:A8. Выделите их мышкой. Затем преобразуйте в таблицу, используя меню Главная — Форматировать как таблицу (Home — Format as Table). Укажите, что в первой строке у вас находится название столбца. Это будет «шапка» вашей таблицы. Внешний вид может быть любым: это не более чем внешнее оформление и ни на что больше оно не влияет.

Как уже было сказано выше, «умная» таблица хороша для нас тем, что динамически меняет свои размеры при добавлении в нее информации. Если в строку ниже нее вписать что-либо, то она тут же присоединит к себе её. Таким образом, новые значения можно просто дописывать. К примеру, впишите в A9 слово «кокос», и таблица тут же расширится до 9 строк.

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

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

=Таблица1[Столбец1]

и не считает его формулой. Хотя в обычных выражениях на листе вашей рабочей книги это вполне будет работать. Эта конструкция обозначает ссылку на первый столбец. Но в поле «Источник» она почему-то игнорируется.

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

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

=ДВССЫЛ(«Таблица5[Продукт]»)

Таблица5 — имя, автоматически присвоенное «умной таблице». У вас оно может быть другим. На вкладке меню Конструктор (Design) можно изменить стандартное имя на свое (но без пробелов!). По нему мы сможем потом адресоваться к нашей таблице на любом листе книги.

«Продукт» — название нашего первого и единственного столбца, присвоено по его заголовку.

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

Теперь если в A9 вы допишете еще один фрукт (например, кокос), то он тут же автоматически появится и в нашем перечне. Аналогично будет, если мы что-то удалим. Задача автоматического увеличения выпадающего списка значений решена.

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

[the_ad_group]

А вот еще полезная для вас информация:

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

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

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

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

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

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

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

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

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



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

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

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

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

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

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

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

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

  1. Сформируем именованный диапазон. Путь: «Формулы» — «Диспетчер имен» — «Создать». Вводим уникальное название диапазона – ОК.
  2. Создаем раскрывающийся список в любой ячейке. Как это сделать, уже известно. Источник – имя диапазона: =деревья.
  3. Снимаем галочки на вкладках «Сообщение для ввода», «Сообщение об ошибке». Если этого не сделать, Excel не позволит нам вводить новые значения.
  4. Вызываем редактор Visual Basic. Для этого щелкаем правой кнопкой мыши по названию листа и переходим по вкладке «Исходный текст». Либо одновременно нажимаем клавиши Alt + F11. Копируем код (только вставьте свои параметры).
    5. Private Sub Worksheet_Change(ByVal Target As Range)
 
Dim lReply As Long
 
    If Target.Cells.Count > 1 Then Exit Sub
    If Target.Address = "$C$2" Then
     If IsEmpty(Target) Then Exit Sub
       If WorksheetFunction.CountIf(Range("Деревья"), Target) = 0 Then
          lReply = MsgBox("Добавить введенное имя " & _
                         Target & " в выпадающий список?", vbYesNo + vbQuestion)
          If lReply = vbYes Then
              Range("Деревья").Cells(Range("Деревья").Rows.Count + 1, 1) = Target
          End If
       End If
     End If
End Sub
 
  5. Сохраняем, установив тип файла «с поддержкой макросов».
  6. Переходим на лист со списком. Вкладка «Разработчик» — «Код» — «Макросы». Сочетание клавиш для быстрого вызова – Alt + F8. Выбираем нужное имя. Нажимаем «Выполнить».

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

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

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

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

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

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

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

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

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

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

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

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

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

      4. 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

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

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

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

    Скачать пример выпадающего списка

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

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

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

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

Процесс создания списка

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

1

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

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

    2

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

    3

  3. Указание именованного диапазона. Метод, повторяющий прошлый, но только необходимо предварительно назвать диапазон.

    4

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

На основе данных из перечня

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

5

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

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

    6

  3. Найти пункт «Тип данных» и переключить значение на «Список».

    7

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

    8

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

С ручной записью данных

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

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

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

    9

  3. Снова выбираем тип «Список».

    10

  4. Здесь в качестве источника необходимо ввести “Да;Нет”. Видим, что информация при ручном вводе вводится с использованием точки с запятой для перечисления.

После нажатия «ОК» у нас появился следующий результат.

11

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

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

Кроме классического метода возможно применение функции СМЕЩ, чтобы генерировать выпадающие меню.

Откроем лист.

12

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

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

    13

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

Потом программой создастся меню с перечнем фруктов.

Синтаксис этой такой:

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

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

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

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

Чтобы создать динамический перечень с поддержкой ввода новой информации, необходимо:

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

Здесь содержится функция СЧЕТЕСЛИ, чтобы сразу определять, сколько ячеек заполнено (хотя у нее есть значительно большее количество применений, просто мы записываем ее здесь для конкретной цели).

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

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

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

  1. Активировать ячейку, где размещаем перечень.
  2. Открываем уже знакомое нам окно. В том же месте, где мы ранее указывали источники на другие диапазоны, указывается формула в формате =ДВССЫЛ(“[Список1.xlsx]Лист1!$A$1:$A$9”). Естественно, вместо Список1 и Лист1 можно вставлять свои имена книги и листа соответственно. 

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

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

Создание зависимых выпадающих списков

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

24

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

  1. Создать 1-й перечень с именами диапазонов.

    25

  2. В месте ввода источника один за одним выделяются требуемые показатели.

    26

  3. Создать 2-й перечень, зависящий от типа растений, который предпочел человек. Как вариант, если в первом указать деревья, то информацией во втором списке станет «дуб, граб, каштан» и дальше. Необходимо записать в месте ввода источника данных формулу =ДВССЫЛ(E3). E3 – ячейка содержащая название диапазона 1.=ДВССЫЛ(E3). E3 – ячейка с наименованием списка 1.

Теперь все готово.

27

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

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

Private Sub Worksheet_Change(ByVal Target As Range)

    On Error Resume Next

    If Not Intersect(Target, Range(“Е2:Е9”)) Is Nothing And Target.Cells.Count = 1 Then

        Application.EnableEvents = False

        If Len(Target.Offset(0, 1)) = 0 Then

            Target.Offset(0, 1) = Target

        Else

            Target.End(xlToRight).Offset(0, 1) = Target

        End If

        Target.ClearContents

        Application.EnableEvents = True

    End If

End Sub 

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

Private Sub Worksheet_Change(ByVal Target As Range)

    On Error Resume Next

    If Not Intersect(Target, Range(“Н2:К2”)) Is Nothing And Target.Cells.Count = 1 Then

        Application.EnableEvents = False

        If Len(Target.Offset(1, 0)) = 0 Then

            Target.Offset(1, 0) = Target

        Else

            Target.End(xlDown).Offset(1, 0) = Target

        End If

        Target.ClearContents

        Application.EnableEvents = True

    End If

End Sub

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

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

Диапазоны редактируемы.

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

В этом случае надо изначально использовать другой тип перечня. Открывается вкладка «Разработчик», после чего надо кликнуть или тапнуть (если экран сенсорный) на элемент «Вставить» – «ActiveX». Там есть «Поле со списком». Будет предложено нарисовать этот список, после чего он добавится в документ.

28

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

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

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

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

    14

  2. Далее его необходимо отформатировать, как таблицу. Нужно нажать одноименную кнопку и осуществить выбор стиля таблицы. 15

    16

Далее нужно подтвердить этот диапазон путем нажатия клавиши «ОК».

17

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

18

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

  1. Выбрать ячейку, где перечень располагается.
  2. Открыть диалог «Проверка данных».

    19

  3. Тип данных выставляем «Список», а как значения даем имя таблицы через знак =. 20

    21

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

22

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

23

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

Для копирования достаточно использовать комбинацию клавиш Ctrl + C и Ctrl + V. Так выпадающий список будет скопирован вместе с форматированием. Чтобы убрать форматирование, нужно воспользоваться специальной вставкой (в контекстном меню такая опция появляется после копирования списка), где выставляется опция «условия на значения».

Выделение всех ячеек, содержащих выпадающий список

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

29

После этого откроется диалоговое окно, где следует в меню «Проверка данных» выбрать пункты «Всех» и «Этих же». Первый пункт выделяет все списки, а второй – только похожие на определенные.

Оцените качество статьи. Нам важно ваше мнение:

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

Автор Архипов Егор На чтение 4 мин. Просмотров 224 Опубликовано

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

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

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

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

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

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

В разделе «Инструменты данных» на вкладке «Данные» нажмите кнопку «Проверка данных».

Откроется диалоговое окно «Проверка данных». На вкладке «Настройки» выберите «Список» в раскрывающемся списке «Разрешить» (см. Раскрывающиеся списки везде!).

Теперь мы собираемся использовать имя, которое мы присвоили диапазону ячеек, содержащих опции для нашего выпадающего списка. Введите = Age в поле «Source» (если вы назвали свой диапазон ячеек как-то еще, замените «Age» этим именем). Убедитесь, что флажок «In-cell dropdown» установлен.

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

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

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

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

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

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

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

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

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

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

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

2. Выделите его и кликните по этой области левой кнопкой мыши. В меню найдите «Присвоить имя».

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

3. Выделите ячейку, в которую хотите вставить выпадающий список. На панели главного меню вверху документа перейдите во вкладку «Данные». И найдите на ней окошко «Работа с данными» -> «Проверка данных».

4. Появится новое окно, в котором нужно найти параметр «Тип данных». В нем выберите пункт «Список».

5. Ниже укажите «Источник». Это можно сделать несколькими способами:

  • С помощью команды «равно». Напишите «=» и название диапазона. В нашем случае команда будет выглядеть так: «=список».

  • Выбрав «Источник» вручную. Нажмите на кнопку в конце строки и выделите диапазон с данными будущего списка.

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

7. Также для удобства можно добавить «Сообщение для ввода». Такую вкладку вы найдете в том же окне по адресу: «Данные» -> «Работа с данными» -> «Проверка данных». Здесь можно ввести «Заголовок» и «Сообщение», которые будут предварять список и подсказывать пользователю, что нужно сделать.


Другие полезные советы по Excel:

Фото: pixabay.com

Видео: CHIP

Видео: создание раскрывающихся списков и управление ими

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

Создать раскрывающийся список

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

  1. Выберите ячейки, в которых вы хотите разместить списки.

  2. На ленте щелкните ДАННЫЕ > Проверка данных .

  3. В диалоговом окне установите Разрешить на Список .

  4. Щелкните Source , введите текст или числа (разделенные запятыми, для списка с разделителями-запятыми), которые вы хотите в раскрывающемся списке, и щелкните OK .

Хотите больше?

Создать раскрывающийся список

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

Удалить раскрывающийся список

Блокируйте клетки, чтобы защитить их

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

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

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

На ленте щелкните вкладку ДАННЫЕ и щелкните Проверка данных .

В диалоговом окне установите Разрешить на Список .

Щелкните в Source .

В этом примере мы используем список с разделителями-запятыми.

Текст или числа, которые мы вводим в поле Source , разделяются запятыми.

И нажмите ОК . Теперь у ячеек есть раскрывающийся список.

Далее, Настройки раскрывающегося списка .

Видео: настройки раскрывающегося списка — Excel

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

Хотите больше?

Создать раскрывающийся список

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

Удалить раскрывающийся список

Блокируйте клетки, чтобы защитить их

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

В предыдущем видео мы использовали список с разделителями-запятыми.

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

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

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

Список с разделителями-запятыми также чувствителен к регистру.

Это может быть проблемой, когда кто-то набирает запись, а не выбирает ее из списка.

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

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

Выберите ячейку, в которой требуется раскрывающийся список.

Щелкните вкладку DATA и щелкните Data Validation .

В диалоговом окне Data Validation установите Allow на List ; это включает список в ячейке.

Оставьте В раскрывающемся списке в ячейке выбранным; это включает раскрывающийся список в ячейке.

Оставить Игнорировать пустым выбран; мы расскажем об этом в следующем видео.

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

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

Диапазон должен быть одной строкой или столбцом, нажмите ОК .

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

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

Выберите ячейку.

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

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

Вы можете использовать сочетание клавиш Ctrl + C, чтобы скопировать ячейку.

Затем выберите целевые ячейки и нажмите Ctrl + V, чтобы вставить их.

Теперь у этих ячеек есть раскрывающийся список.

Именованный диапазон, например Fruits, легче запомнить, чем диапазон ячеек, например A2: A37.

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

Выберите диапазон ячеек, который хотите назвать.

В поле имени введите желаемое имя для диапазона. Например, «_Veggies».

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

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

Имя не может содержать пробелов.

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

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

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

Выберите ячейку, в которой требуется раскрывающийся список. Щелкните Data Validation , выберите List , щелкните Source , нажмите F3, выберите имя, щелкните OK и снова щелкните OK .

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

Вперед, Сообщения о вводе и ошибках .

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

Редактировать раскрывающийся список на основе таблицы Excel

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

  • Чтобы добавить элемент, перейдите в конец списка и введите новый элемент.

  • Чтобы удалить элемент, нажмите Удалить .

    Совет: Если элемент, который вы хотите удалить, находится где-то в середине вашего списка, щелкните его ячейку правой кнопкой мыши, выберите Удалить , а затем нажмите ОК , чтобы переместить ячейки вверх.

Редактировать раскрывающийся список на основе именованного диапазона

  1. Выберите рабочий лист с именованным диапазоном для раскрывающегося списка.

  2. Выполните одно из следующих действий:

    • Чтобы добавить элемент, перейдите в конец списка и введите новый элемент.

    • Чтобы удалить элемент, нажмите Удалить .

      Совет: Если элемент, который вы хотите удалить, находится где-то в середине вашего списка, щелкните его ячейку правой кнопкой мыши, выберите Удалить , а затем нажмите ОК , чтобы переместить ячейки вверх.

  3. Перейти к Формулы > Диспетчер имен .

  4. В поле Name Manager щелкните именованный диапазон, который нужно обновить.

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

  6. Нажмите Закрыть , а затем нажмите Да , чтобы сохранить изменения.

Совет: Если вы не знаете, как называется именованный диапазон, вы можете выбрать диапазон и найти его имя в поле Имя . Чтобы найти именованный диапазон, см. Поиск именованных диапазонов.

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

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

  2. Выполните одно из следующих действий:

    • Чтобы добавить элемент, перейдите в конец списка и введите новый элемент.

    • Чтобы удалить элемент, щелкните Удалить .

      Совет: Если элемент, который вы хотите удалить, находится где-то в середине вашего списка, щелкните его ячейку правой кнопкой мыши, выберите Удалить , а затем нажмите ОК , чтобы переместить ячейки вверх.

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

  4. Перейти к Data > Data Validation .

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

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

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

  1. На листе, к которому вы применили раскрывающийся список, выберите ячейку с раскрывающимся списком.

  2. Перейти к Data > Data Validation .

  3. На вкладке Настройки щелкните поле Источник , а затем при необходимости измените элементы списка. Каждый элемент должен быть разделен запятой без пробелов, например: Да, Нет, Возможно .

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

Работа с раскрывающимся списком

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

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

Если вы хотите удалить раскрывающийся список, см. Удаление раскрывающегося списка.

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

Редактировать раскрывающийся список на основе таблицы Excel

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

  • Чтобы добавить элемент, перейдите в конец списка и введите новый элемент.

  • Чтобы удалить элемент, нажмите Удалить .

    Совет: Если элемент, который вы хотите удалить, находится где-то в середине вашего списка, щелкните его ячейку правой кнопкой мыши, выберите Удалить , а затем нажмите ОК , чтобы переместить ячейки вверх.

Редактировать раскрывающийся список на основе именованного диапазона

  1. Выберите рабочий лист с именованным диапазоном для раскрывающегося списка.

  2. Выполните одно из следующих действий:

    • Чтобы добавить элемент, перейдите в конец списка и введите новый элемент.

    • Чтобы удалить элемент, нажмите Удалить .

      Совет: Если элемент, который вы хотите удалить, находится где-то в середине вашего списка, щелкните его ячейку правой кнопкой мыши, выберите Удалить , а затем нажмите ОК , чтобы переместить ячейки вверх.

  3. Перейти к Формулы > Диспетчер имен .

  4. В поле Name Manager щелкните именованный диапазон, который нужно обновить.

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

  6. Нажмите Закрыть , а затем нажмите Да , чтобы сохранить изменения.

Совет: Если вы не знаете, как называется именованный диапазон, вы можете выбрать диапазон и найти его имя в поле Имя . Чтобы найти именованный диапазон, см. Поиск именованных диапазонов.

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

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

  2. Выполните одно из следующих действий:

    • Чтобы добавить элемент, перейдите в конец списка и введите новый элемент.

    • Чтобы удалить элемент, щелкните Удалить .

      Совет: Если элемент, который вы хотите удалить, находится где-то в середине вашего списка, щелкните его ячейку правой кнопкой мыши, выберите Удалить , а затем нажмите ОК , чтобы переместить ячейки вверх.

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

  4. Перейти к Data > Data Validation .

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

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

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

  1. На листе, к которому вы применили раскрывающийся список, выберите ячейку с раскрывающимся списком.

  2. Перейти к Data > Data Validation .

  3. На вкладке Настройки щелкните поле Источник , а затем при необходимости измените элементы списка. Каждый элемент должен быть разделен запятой без пробелов, например: Да, Нет, Возможно .

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

Работа с раскрывающимся списком

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

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

Если вы хотите удалить раскрывающийся список, см. Удаление раскрывающегося списка.

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

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

  1. Выберите ячейки, в которых есть раскрывающийся список.

  2. Перейти к Data > Data Validation .

  3. На вкладке Настройки щелкните в поле Источник . Затем выполните одно из следующих действий:

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

    • Если поле «Источник» содержит ссылку на диапазон ячеек (например, = $ A $ 2: $ A $ 5 ), щелкните Отменить , а затем добавьте или удалите записи из этих ячеек. В этом примере вы должны добавить или удалить записи в ячейках с A2 по A5. Если список записей оказывается длиннее или короче исходного диапазона, вернитесь на вкладку Settings и удалите содержимое поля Source .Затем щелкните и перетащите, чтобы выбрать новый диапазон, содержащий записи.

    • Если поле «Источник» содержит именованный диапазон, например «Отделы», вам необходимо изменить сам диапазон с помощью настольной версии Excel.

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

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

Создать раскрывающийся список | Разрешить другие записи | Добавить / удалить элементы | Динамический раскрывающийся список | Удалить раскрывающийся список | Зависимые раскрывающиеся списки | Стол Magic

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

Создать раскрывающийся список

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

1. На втором листе введите элементы, которые должны появиться в раскрывающемся списке.

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

2. На первом листе выберите ячейку B1.

3. На вкладке «Данные» в группе «Работа с данными» щелкните «Проверка данных».

Откроется диалоговое окно «Проверка данных».

4. В поле Разрешить щелкните Список.

5. Щелкните в поле «Источник» и выберите диапазон A1: A3 на листе Sheet2.

6. Щелкните OK.

Результат:

Примечание: чтобы скопировать / вставить раскрывающийся список, выберите ячейку с раскрывающимся списком и нажмите CTRL + c, выберите другую ячейку и нажмите CTRL + v.

7. Вы также можете вводить элементы непосредственно в поле «Источник» вместо использования ссылки на диапазон.

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

Разрешить другие записи

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

1. Во-первых, если вы введете значение, которого нет в списке, Excel покажет предупреждение об ошибке.

Чтобы разрешить другие записи, выполните следующие шаги.

2. На вкладке «Данные» в группе «Работа с данными» щелкните «Проверка данных».

Откроется диалоговое окно «Проверка данных».

3. На вкладке «Предупреждение об ошибке» снимите флажок «Показывать предупреждение об ошибке после ввода неверных данных».

4. Щелкните OK.

5. Теперь вы можете ввести значение, которого нет в списке.

Добавить / удалить элементы

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

1. Чтобы добавить элемент в раскрывающийся список, перейдите к элементам и выберите элемент.

2. Щелкните правой кнопкой мыши и выберите Вставить.

3. Выберите «Сдвинуть ячейки вниз» и нажмите ОК.

Результат:

Примечание. Excel автоматически изменил ссылку на диапазон с Sheet2! $ A $ 1: $ A $ 3 на Sheet2! $ A $ 1: $ A $ 4. Вы можете проверить это, открыв диалоговое окно «Проверка данных».

4. Введите новый элемент.

Результат:

5. Чтобы удалить элемент из раскрывающегося списка, на шаге 2 нажмите «Удалить», выберите «Сдвинуть ячейки вверх» и нажмите «ОК».

Динамический раскрывающийся список

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

1. На первом листе выберите ячейку B1.

2. На вкладке «Данные» в группе «Работа с данными» щелкните «Проверка данных».

Откроется диалоговое окно «Проверка данных».

3. В поле Разрешить щелкните Список.

4. Щелкните поле «Источник» и введите формулу: = СМЕЩЕНИЕ (Sheet2! $ A $ 1,0,0, COUNTA (Sheet2! $ A: $ A), 1)

Объяснение: функция СМЕЩЕНИЕ принимает 5 аргументов.Ссылка: Sheet2! $ A $ 1, строки для смещения: 0, столбцы для смещения: 0, высота: COUNTA (Sheet2! $ A: $ A) и ширина: 1. COUNTA (Sheet2! $ A: $ A) подсчитывает число значений в столбце A на Листе 2, которые не являются пустыми. Когда вы добавляете элемент в список на Sheet2, COUNTA (Sheet2! $ A: $ A) увеличивается. В результате диапазон, возвращаемый функцией СМЕЩЕНИЕ, расширяется, и раскрывающийся список будет обновлен.

5. Щелкните OK.

6. На втором листе просто добавьте новый элемент в конец списка.

Результат:

Удалить раскрывающийся список

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

1. Выберите ячейку в раскрывающемся списке.

2. На вкладке «Данные» в группе «Работа с данными» щелкните «Проверка данных».

Откроется диалоговое окно «Проверка данных».

3. Щелкните Очистить все.

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

4. Щелкните OK.

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

Хотите узнать больше о раскрывающихся списках в Excel? Узнайте, как создавать зависимые раскрывающиеся списки.

1. Например, если пользователь выбирает Пиццу из первого раскрывающегося списка.

2. Второй раскрывающийся список содержит пункты «Пицца».

3. Но если пользователь выбирает китайский язык из первого раскрывающегося списка, второй раскрывающийся список содержит китайские блюда.

Стол Magic

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

1. На втором листе выберите элемент списка.

2. На вкладке Вставка в группе Таблицы щелкните Таблица.

3. Excel автоматически выбирает данные за вас. Щелкните ОК.

4. Если вы выберете список, Excel покажет структурированную ссылку.

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

Объяснение: функция ДВССЫЛ в Excel преобразует текстовую строку в действительную ссылку.

6.На втором листе просто добавьте новый элемент в конец списка.

Результат:

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

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

Примечание: эта функция динамического массива, введенная в ячейку F1, заполняет несколько ячеек. Вау! Такое поведение в Excel 365 называется разливом.

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

Объяснение: всегда используйте первую ячейку (F1) и символ решетки для обозначения диапазона разлива.

Результат:

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

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

  • Выпадающий список в Excel можно редактировать несколькими способами.
  • Выпадающий список легко редактировать, если он основан на таблице Excel, поскольку таблица должна автоматически обновляться вместе с вашими изменениями.
  • Если вы хотите отредактировать раскрывающийся список на основе диапазона ячеек, именованного диапазона или введенных вручную записей, процесс потребует еще нескольких шагов.
  • Посетите домашнюю страницу Business Insider, чтобы узнать больше.
Идет загрузка.

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

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

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

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

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

Ознакомьтесь с продуктами, упомянутыми в этой статье:
MacBook Pro (от 1299 долларов США.99 в Best Buy)
Lenovo IdeaPad 130 (от 299,99 долларов США в Best Buy)

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

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

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

В меню «Данные» дважды щелкните «Проверка данных». Мелани Вейр / Business Insider

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

Настройте диапазон данных в разделе Источник.Мелани Вейр / Business Insider

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

1. Добавьте новый элемент в свой список или удалите тот, который вам не нужен.

2. Перейдите на вкладку «Формулы» и выберите «Диспетчер имен».

Щелкните Диспетчер имен.Мелани Вейр / Business Insider

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

Отредактируйте формулу «Относится к» вашего именованного диапазона.Мелани Вейр / Business Insider

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

1. На вкладке «Данные» щелкните «Проверка данных».

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

Добавляйте или удаляйте элементы по мере необходимости, разделяя их запятыми. Мелани Вейр / Business Insider

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

Excel — как создавать, редактировать и удалять списки проверки данных

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

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

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

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

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

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

Это самый быстрый трехэтапный способ создания раскрывающегося списка во всех версиях Excel 2016, 2013, 2010, 2007 и 2003.

1. Выберите ячейку или диапазон для раскрывающегося списка.

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

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

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

На ленте Excel перейдите на вкладку Данные> Группа инструментов для работы с данными и щелкните Проверка данных .

3. Введите элементы списка и выберите параметры.

В окне Проверка данных на вкладке Настройки выполните следующие действия:

  • В поле Разрешить выберите Список .
  • В поле Источник введите элементы, которые должны отображаться в раскрывающемся меню, через запятую (с пробелами или без них).
  • Убедитесь, что установлен флажок в раскрывающемся списке в ячейке; в противном случае стрелка раскрывающегося списка не появится рядом с ячейкой.
  • Установите или снимите флажок Игнорировать пустой в зависимости от того, как вы хотите обрабатывать пустые ячейки.
  • Нажмите ОК, и все готово!

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

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

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

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

1. Введите записи для раскрывающегося списка.

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

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

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

2. Создайте именованный диапазон.

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

  • Выберите все записи, которые вы хотите включить в раскрывающийся список, щелкните их правой кнопкой мыши и выберите Определить имя из контекстного меню.Кроме того, вы можете щелкнуть Диспетчер имен на вкладке Формулы или нажать Ctrl + F3.
  • В диалоговом окне «Диспетчер имен» щелкните Новый .
  • В поле Имя введите имя для своих записей, убедитесь, что в поле Относится к отображается правильный диапазон, а затем нажмите OK. Убедитесь, что в названии диапазона нет пробелов и дефисов, используйте вместо них символы подчеркивания (_).

Наконечник. Более быстрый способ создать именованный диапазон в Excel — выбрать ячейки и ввести имя диапазона непосредственно в поле имени .По завершении нажмите Enter, чтобы сохранить вновь созданный именованный диапазон. Для получения дополнительной информации, пожалуйста, посмотрите, как определить имя в Excel.
3. Применить проверку данных.

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

  • В поле Разрешить выберите Список .
  • В поле Source введите имя, которое вы дали вашему диапазону, со знаком равенства, например = Ingredients .
  • Убедитесь, что установлен флажок в раскрывающемся списке в ячейке.
  • Щелкните OK.

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

Если исходный список содержит более 8 элементов, в раскрывающемся списке будет полоса прокрутки, подобная этой:

Список проверки данных Excel на основе таблицы

Вместо использования обычного именованного диапазона вы можете преобразовать данные в полнофункциональную таблицу Excel ( Insert> Table или Ctrl + T), а затем создать список проверки данных из этой таблицы. Вот как:

  1. Создайте именованный диапазон для столбца данных, не включая ячейку заголовка в таблице.Чтобы обратиться к ячейкам данных, вы можете использовать один из следующих методов:
    • Наведите указатель мыши на верхнюю часть ячейки заголовка, и когда появится черная стрелка вниз, щелкните, чтобы выбрать все ячейки в столбце таблицы без заголовка. Затем введите имя выбранного диапазона в поле Имя .
    • Откройте Диспетчер имен и введите структурированную ссылку, например = Таблица [столбец] в , поле Относится к :
  2. Создайте список проверки данных на основе именованного диапазона, как описано в предыдущем примере.

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

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

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

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

Создание динамического (автоматически обновляемого) раскрывающегося списка Excel

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

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

  1. Вы начинаете с создания обычного раскрывающегося списка на основе именованного диапазона, как описано выше.
  2. На шаге 2 при создании имени вы помещаете следующую формулу в поле Относится к .

    = СМЕЩЕНИЕ (Sheet1! $ A $ 1,0,0, COUNTA (Sheet1! $ A: $ A), 1)

    Где:

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

Как видите, формула состоит из 2 функций Excel — СМЕЩЕНИЕ и СЧЁТ. Функция COUNTA считает все непустые пробелы в указанном столбце. СМЕЩЕНИЕ принимает это число и возвращает ссылку на диапазон, который включает только непустые ячейки, начиная с первой ячейки, указанной в формуле.

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

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

В Microsoft Excel функция СМЕЩЕНИЕ (ссылка, строки, столбцы, [высота], [ширина]) используется для возврата ссылки на диапазон, состоящий из указанного количества строк и столбцов.Чтобы заставить его возвращать динамический, т.е. непрерывно изменяющийся диапазон, мы указываем следующие аргументы:

  • ссылка — ячейка $ A $ 1 в Sheet1, которая является первым элементом вашего раскрывающегося списка;
  • строк и столбцы равны 0, потому что вы не хотите сдвигать возвращаемый диапазон ни по вертикали, ни по горизонтали;
  • высота — количество непустых ячеек в столбце A, возвращаемое функцией СЧЁТ;
  • ширина — 1, т.е.е. один столбец.

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

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

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

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

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

1. Создайте именованный диапазон для исходного списка.

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

2. Создайте именованную ссылку в основной книге.

Откройте книгу, в которой должен отображаться раскрывающийся список, и создайте имя, которое будет ссылаться на исходный список. В этом примере заполненная ссылка = SourceBook.xlsx! Source_list

Примечание. Вы должны заключить имя книги в апострофы (‘), если оно содержит пробелы. Например: = 'Source Book.xlsx'! Source_list

3. Применить проверку данных

В основной книге выберите ячейки для раскрывающегося списка, щелкните Данные> Проверка данных и введите имя, созданное на шаге 2, в поле Источник .

Динамический раскрывающийся список из другой книги

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

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

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

Параметр «Проверка данных» неактивен или отключен? Это может произойти по нескольким причинам:

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

Дополнительные параметры для раскрывающегося списка Excel

В большинстве случаев параметров вкладки Settings , которые мы обсуждали выше, абсолютно достаточно. Если это не так, на других вкладках диалогового окна Data Validation доступны еще два параметра.

Отображать сообщение при щелчке по ячейке с раскрывающимся списком

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

  • В диалоговом окне Проверка данных (вкладка Данные> Проверка данных ) перейдите на вкладку Входное сообщение .
  • Убедитесь, что опция Показывать входное сообщение при выделенной ячейке отмечена флажком.
  • Введите заголовок и сообщение в соответствующие поля (до 225 символов).
  • Нажмите кнопку OK , чтобы сохранить сообщение и закрыть диалоговое окно.

Результат в Excel будет выглядеть примерно так:

Разрешить пользователям вводить свои данные в поле со списком

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

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

  1. В диалоговом окне Data Validation (вкладка Data> Data Validation ) перейдите на вкладку Error Alert .
  2. Установите флажок «Показать предупреждение об ошибке после ввода недопустимых данных », если вы хотите отображать предупреждение, когда пользователь пытается ввести некоторые данные, которых нет в раскрывающемся меню.Если вы не хотите показывать какие-либо сообщения, снимите этот флажок.
  3. Чтобы отобразить предупреждающее сообщение, выберите один из вариантов в поле Style и введите заголовок и сообщение. Либо информация , либо предупреждение позволит пользователям вводить свой собственный текст в поле со списком.
    • Сообщение Информация рекомендуется, если ваши пользователи часто вводят свой собственный выбор.
    • Предупреждение Сообщение побудит пользователей выбрать элемент из раскрывающегося списка, а не вводить свои собственные данные, хотя это не запрещает пользовательский ввод.
    • Остановить (по умолчанию) запретит людям вводить какие-либо данные, которых нет в раскрывающемся списке Excel.

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

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

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

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

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

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

  1. Выберите ячейку или ячейки, которые ссылаются на ваш список проверки данных Excel, i.е. ячейки, содержащие раскрывающийся список, который вы хотите отредактировать.
  2. Щелкните Проверка данных (лента Excel> вкладка «Данные»).
  3. Удалите или введите новые элементы в поле Источник .
  4. Щелкните OK, чтобы сохранить изменения и закрыть окно Excel Data Validation .

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

Редактирование раскрывающегося меню на основе диапазона ячеек

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

  1. Перейдите к электронной таблице, содержащей элементы, отображаемые в раскрывающемся списке, и отредактируйте список по своему усмотрению.
  2. Выберите ячейку или ячейки, содержащие раскрывающийся список.
  3. Щелкните Data Validation на вкладке Data .
  4. В окне Excel Data Validation на вкладке «Параметры» измените ссылки на ячейки в поле «Источник». Вы можете отредактировать их вручную или щелкнуть значок Свернуть диалоговое окно .
  5. Нажмите кнопку OK , чтобы сохранить изменения и закрыть окно.
Редактирование раскрывающегося списка Excel на основе именованного диапазона

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

  1. Добавьте или удалите элементы в названном диапазоне.
    Откройте рабочий лист, содержащий ваш именованный диапазон, удалите или введите новые записи. Не забудьте расположить элементы в том порядке, в котором они должны отображаться в раскрывающемся списке Excel.
  2. Измените ссылку на именованный диапазон.
    • На ленте Excel перейдите на вкладку Формулы> Диспетчер имен . Либо нажмите Ctrl + F3, чтобы открыть окно Name Manager .
    • В окне диспетчера имен выберите именованный диапазон, который нужно обновить.
    • Измените ссылку в поле Относится к , щелкнув значок Свернуть диалоговое окно и выбрав все записи в раскрывающемся списке.
    • Нажмите кнопку Закрыть , а затем в появившемся подтверждающем сообщении нажмите Да , чтобы сохранить изменения.

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

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

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

Удаление раскрывающегося меню из выбранных ячеек

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

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

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

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

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

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

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

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

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

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

Вас также может заинтересовать

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

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

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

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

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

Таблица на рис. 1 — это отформатированная таблица. Это означает, что диапазон таблицы автоматически расширяется по мере добавления данных в таблицу, и наше решение также будет масштабируемым и будет корректироваться по мере добавления новых продуктов в таблицу.

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

Мы создадим список для раскрывающегося списка ячейки F2 в столбце K.

Формула для ячейки K2:

= УНИКАЛЬНЫЙ (tblProd [продукт])

tblProd — это имя, которое я дал форматированной таблице. Квадратные скобки используются для определения столбца в таблице.

На рис. 3 показано, как формула в ячейке K2 расширяется (расширяется), чтобы включить два продукта.

Это часть функции динамического массива, включенной в версию Excel для подписки.

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

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

В открывшемся диалоговом окне выберите Список в раскрывающемся списке Разрешить. Щелкните в поле «Источник» и с помощью мыши щелкните ячейку K2, затем введите # (см. , рис. 4, ). Добавление символа # в конец относится к диапазону разлива, который начинается в ячейке K2.

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

Раскрывающийся список «Размер» (ячейка G2) зависит от продукта, выбранного в ячейке F2.Я создам список в ячейке L2 в два шага. Исходная формула будет использовать функцию ФИЛЬТР. В ячейке L2 введите следующую формулу:

= ФИЛЬТР (tblProd [Размер], tblProd [Продукт] = F2, «Нет»)

В этой формуле перечислены все размеры (включая дубликаты), относящиеся к продукту, выбранному в ячейке F2 (см. , рис. 5, ). Параметр «Нет» в формуле указывает, что отображать, если условие не выполняется — в данном случае, если ячейка F2 пуста.

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

= УНИКАЛЬНЫЙ (ФИЛЬТР (tblProd [Размер], tblProd [Продукт] = F2, «Нет»))

На рисунке 6 показан результат.

Теперь мы можем создать раскрывающийся список для ячейки G2. Нажмите Alt A V V. Используйте список в раскрывающемся списке Разрешить, щелкните в поле Источник, щелкните ячейку L2, добавьте # и нажмите ОК.

Список для раскрывающегося списка ячейки h3 более сложен, потому что нам нужно использовать два условия в функции ФИЛЬТР.Формула для ячейки M2:

= ФИЛЬТР (tblProd [Color], (tblProd

[Продукт] = F2) * (tblProd [Размер] = G2), «Нет»)

Как справиться с двумя условиями

Чтобы обработать два условия, мы заключаем каждое условие в квадратные скобки и умножаем их вместе. В Excel ИСТИНА = 1 и ЛОЖЬ = 0. Когда вы умножаете результаты вместе, они преобразуются в соответствующие значения. Это создает один результат для каждой строки, либо 1, либо 0.

Excel будет рассматривать 1 как ИСТИНА и 0 как ЛОЖЬ, чтобы определить, какие строки отображать.Таблица на рис. 7 показывает четыре возможных результата при анализе двух условий.

Нам не нужно использовать УНИКАЛЬНУЮ функцию в ячейке M2, поскольку записи уже уникальны. Однако вы можете отсортировать список с помощью новой функции SORT. Пересмотренная формула:

= СОРТИРОВАТЬ (ФИЛЬТР (tblProd [Color], (tblProd

[Продукт] = F2) * (tblProd [Размер] = G2), «Нет»))

Теперь мы можем создать раскрывающийся список для ячейки h3. Нажмите Alt A V V, используйте список в раскрывающемся списке Разрешить, щелкните в поле Источник, щелкните ячейку M2, добавьте # и нажмите ОК.

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

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

Формула для ячейки I2:

= XLOOKUP (F2 & G2 & h3, tblProd

[Продукт] и таблица [Размер] и таблица

[Цвет], tblProd [Цена], 0)

Мы используем &, чтобы объединить три записи вместе.Затем мы используем &, чтобы объединить три столбца в таблице, чтобы создать объединенный список для поиска. Столбец Цена возвращается на основе найденных комбинированных записей. Если запись не найдена, возвращается ноль.

На рисунке 8 показана окончательная структура.

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

Leave a comment