Excel создание списка для всех пользователей - Word и Excel

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

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

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

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

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

Вручную через «точку-с-запятой» в поле «Источник».

Ввести значения заранее. А в качестве источника указать диапазон ячеек со списком.

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

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

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

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

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

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

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

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

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

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

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

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

Сформируем именованный диапазон. Путь: «Формулы» — «Диспетчер имен» — «Создать». Вводим уникальное название диапазона – ОК.

Создаем раскрывающийся список в любой ячейке. Как это сделать, уже известно. Источник – имя диапазона: =деревья.
Снимаем галочки на вкладках «Сообщение для ввода», «Сообщение об ошибке». Если этого не сделать, Excel не позволит нам вводить новые значения.

Вызываем редактор Visual Basic. Для этого щелкаем правой кнопкой мыши по названию листа и переходим по вкладке «Исходный текст». Либо одновременно нажимаем клавиши Alt + F11. Копируем код (только вставьте свои параметры).

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

Сохраняем, установив тип файла «с поддержкой макросов».

Переходим на лист со списком. Вкладка «Разработчик» — «Код» — «Макросы». Сочетание клавиш для быстрого вызова – Alt + F8. Выбираем нужное имя. Нажимаем «Выполнить».

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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». Здесь нам нужна кнопка «Поле со списком» (ориентируемся на всплывающие подсказки).

Щелкаем по значку – становится активным «Режим конструктора». Рисуем курсором (он становится «крестиком») небольшой прямоугольник – место будущего списка.

Жмем «Свойства» – открывается перечень настроек.

Вписываем диапазон в строку ListFillRange (руками). Ячейку, куда будет выводиться выбранное значение – в строку LinkedCell. Для изменения шрифта и размера – Font.

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

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

Источник

Excel для Microsoft 365 Excel 2021 Excel 2019 Excel 2016 Excel 2013 Excel 2010 Excel 2007 Excel Starter 2010 Еще…Меньше

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

Добавление списка на лист

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

Примечание: Если вкладка Разработчик не отображается, на вкладке Файл выберите Параметры > Настроить ленту. В списке Основные вкладки установите флажок для вкладки Разработчик и нажмите кнопку ОК.
В разделе Элементы управления формы выберите элемент управления Список (элемент управления формы).
Щелкните ячейку, в которой нужно создать список.
Нажмите кнопку Свойства и на вкладке Элемент управления задайте необходимые свойства:
- В поле Формировать список по диапазону введите диапазон ячеек, содержащий список значений.
  
  Примечание: Если нужно отобразить в списке больше элементов, можно изменить размер шрифта для текста.
- В поле Связь с ячейкой введите ссылку на ячейку.
  
  Совет: Выбираемая ячейка содержит число, связанное с элементом, выбранным в списке. Его можно использовать в формуле для получения фактического элемента из входного диапазона.
- В группе Возможен выбор установите переключатель одинарного значения и нажмите кнопку ОК.
  
  Примечание: Если вы хотите выбрать параметр набора значений или списка значений, подумайте о том, чтобы использовать элемент ActiveX «Список».

Добавление поля со списком на лист

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

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

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

Примечание: Можно также создать список на другом листе той же книги.
На вкладке Разработчик нажмите кнопку Вставить.

Примечание: Если вкладка Разработчик не отображается, на вкладке Файл выберите Параметры > Настроить ленту. В списке Основные вкладки установите флажок для вкладки Разработчик и нажмите кнопку ОК.
Выберите тип поля со списком, которое нужно добавить:
- в разделе Элементы управления формы выберите элемент управления Поле со списком (элемент управления формы);
  
  ИЛИ:
- в разделе Элементы ActiveX выберите элемент управления Поле со списком (элемент ActiveX).
Щелкните ячейку, в которую нужно добавить поле со списком, и нарисуйте его с помощью перетаскивания.

Советы:

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

Форматирование элемента управления формы «Поле со списком»

Щелкните правой кнопкой мыши поле со списком и выберите команду Формат объекта.
Откройте вкладку Элемент управления и настройте следующие параметры.
- Формировать список по диапазону: введите диапазон ячеек, содержащий список элементов.
- Связь с ячейкой: поле со списком можно связать с ячейкой, где отображается номер элемента при его выборе из списка. Введите номер ячейки, где должен отображаться номер элемента.
  
  Например, в ячейке C1 отображается значение 3, если выбрать пункт Фруктовое мороженое, так как это третий элемент в списке.
  
  Совет: Чтобы вместо номера отображать сам элемент, можно воспользоваться функцией ИНДЕКС. В нашем примере поле со списком связано с ячейкой B1, а диапазон ячеек для списка — A1:A2. Если в ячейку C1 ввести формулу =ИНДЕКС(A1:A5;B1), то при выборе третьего пункта в ячейке C1 появится текст «Фруктовое мороженое».
- Количество строк списка: количество строк, которые должны отображаться, если щелкнуть стрелку вниз. Например, если список содержит 10 элементов и вы не хотите использовать прокрутку, вместо значения по умолчанию введите 10. Если ввести число, которое меньше количества элементов в списке, появится полоса прокрутки.
Нажмите кнопку ОК.

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

На вкладке Разработчик нажмите кнопку Режим конструктора.

Щелкните правой кнопкой мыши поле со списком и выберите пункт Свойства. Откройте вкладку Alphabetic (По алфавиту) и измените нужные свойства.

Вот как можно настроить свойства поля со списком на этом рисунке:

Настраиваемое свойство	Действие
Цвет заливки	Щелкните свойство BackColor (Цвет фона), щелкните стрелку вниз, откройте вкладку Pallet (Палитра) и выберите цвет.
Тип, начертание или размер шрифта	Щелкните свойство Font (Шрифт), нажмите кнопку … и выберите тип, размер или начертание шрифта.
Цвет шрифта	Щелкните свойство ForeColor (Цвет текста), щелкните стрелку вниз, откройте вкладку Pallet (Палитра) и выберите цвет.
Связь с ячейкой для отображения значения, выбранного в списке	Щелкните свойство LinkedCell (Связанная ячейка).
Связывание поля со списком и списка элементов	Щелкните поле рядом со свойством ListFillRange (Диапазон элементов списка) и укажите диапазон ячеек для списка.
Изменение количества отображаемых элементов списка	Щелкните поле ListRows и введите число элементов.

Закройте область Properties (Свойства) и нажмите кнопку Режим конструктора.
Завершив форматирование, можно щелкнуть правой кнопкой мыши столбец, который содержит список, и выбрать команду Скрыть.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

См. также

Обзор форм, элементов управления формы и ActiveX элементов управления на сайте

Добавление флажков и переключателей (элементы управления формы)

Нужна дополнительная помощь?

Источник

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

Вариант 1: Группировка существующего списка

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

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

Комьюнити теперь в Телеграм

Подпишитесь и будьте в курсе последних IT-новостей

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

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

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

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

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

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

Вариант 4: Создание выпадающего списка из таблицы

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

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

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

Источник

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

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

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

Как автозаполнить в Excel

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

Введите Sun (для воскресенья) в ячейку B2 . Затем поместите курсор на правый нижний угол ячейки B2, пока он не превратится в знак плюс, и перетащите вправо на ячейку H2 .

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

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

Как создать пользовательский список в Excel

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

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

Есть три способа создать свой собственный список.

1. Введите ваш список напрямую

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

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

2. Импортируйте ячейки из листа

Второй способ создать пользовательский список — это импортировать их из ячеек в одной из ваших таблиц.

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

3. Импортируйте список из именованного диапазона ячеек

Третий способ создания настраиваемого списка — это импорт списка из именованного диапазона ячеек.

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

3 примера пользовательских списков Excel

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

Если вы создаете электронные таблицы с данными для отделов в вашей компании, вы можете создать собственный список, содержащий названия отделов. Например, бухгалтерия, HR, маркетинг, разработка, техническая поддержка. Затем вы можете использовать автозаполнение для быстрого создания заголовков для столбцов или строк.
Может быть, вы отслеживаете инвентарь одежды. Таким образом, вам могут потребоваться списки для размеров (S, M, L, XL и XXL), стилей (шея экипажа, V-образный вырез, макет шеи), длины рукава (короткий рукав, длинный рукав, три четверти рукава, без рукавов) и цвета (черный, белый, серый, синий, красный, розовый, фиолетовый, зеленый, желтый). Из этих списков вы можете быстро создать согласованные раскрывающиеся списки, содержащие эти параметры.
Ты учитель? Вы можете создать собственный список всех имен ваших учеников и легко вставить этот список имен в строку или столбец электронной таблицы для отслеживания информации, такой как оценки или посещаемость.

Создайте свои собственные пользовательские списки

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

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

Кредит изображения: albertyurolaits / Depositphotos

Источник

Содержание

1 Как нам это может пригодиться?
2 Как работает выпадающий список в Excel
- 2.1 #1. Параметры
- 2.2 #2. Подсказка по вводу
- 2.3 #3. Сообщение об ошибке
3 Выпадающий список через контекстное меню
4 Создание списка с применением инструментов разработчика
5 1 — Самый быстрый способ.
- 5.1 2 — Используем меню.
- 5.2 3 — Создаем элемент управления.
- 5.3 4 — Элемент ActiveX
6 Как сделать в Экселе выпадающий список: 3 способа
- 6.1 1 Применение комбинации клавиш
- 6.2 2 Использование меню
- 6.3 3 Создание нового элемента управления
  - 6.3.1 Excel 2007-2013
  - 6.3.2 Excel Online
  - 6.3.3 Excel для Mac 2011
7 Как убрать (удалить) выпадающий список в Excel
8 Пробелы в названии при создании связанного выпадающего списка
9 Пример выпадающего списка в Excel
10 Зависимые раскрывающиеся списки
11 Создание дополнительного списка
12 Связанный список

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

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

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

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

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

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

Далее всплывает окно «Проверка вводимых значений».

#1. Параметры

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

Тип данных. Можно выбрать тип данных, который будет содержать список: диапазон целых или действительных чисел, текстовые выражения, даты и время. Можно задать ограничения по длине текста и различные формулы.
Игнорировать пустые значения — данный пункт означает, что Excel не будет проверять на правильность ячейки, в которых содержатся пустые значения.
Список допустимых значений. Этот флажок отображается только в том случае, если выбран тип данных «Список». Если убрать флажок, в ячейке будет происходить проверка на соответствие значений списку, но раскрывающее поле с выпадающими значениями будет отсутствовать.
Значение. Работает только с теми типами данных, в которых можно задать ограничения по числам или датам.
Источник. Здесь перечисляются значения для проверки данных или задается формула.
Распространить изменения на другие ячейки с тем же условием. Excel здесь находит все ячейки в книге, которые ссылаются на идентичное по свойствам условие и изменяет их согласно новых параметров. В случае, если флажок не будет установлен, условие будет изменено только для выделенных ячеек в таблице.
Очистить все — удаляет установленную проверку данных с выделенных ячеек.

#2. Подсказка по вводу

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

#3. Сообщение об ошибке

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

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

Смотрите также: “Как из Экселя перенести таблицу в Ворд”

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

Во вспомогательной таблице пишем перечень всех наименований – каждый с новой строки в отдельной ячейке. В итоге должен получиться один столбец с заполненными данными.
Затем отмечаем все эти ячейки, нажимаем в любом месте отмеченного диапазона правой кнопкой мыши и в открывшемся списке кликаем по функции “Присвоить имя..”.
На экране появится окно “Создание имени”. Называем список так, как хочется, но с условием – первым символом должна быть буква, также не допускается использование определенных символов. Здесь же предусмотрена возможность добавления списку примечания в соответствующем текстовом поле. По готовности нажимаем OK.
Переключаемся во вкладку “Данные” в основном окне программы. Отмечаем группу ячеек, для которых хотим задать выбор из нашего списка и нажимаем на значок “Проверка данных” в подразделе “Работа с данными”.
На экране появится окно “Проверка вводимых значений”. Находясь во вкладке “Параметры” в типе данных останавливаемся на опции “Список”. В текстовом поле “Источник” пишем знак “равно” (“=”) и название только что созданного списка. В нашем случае – “=Наименование”. Нажимаем OK.
Все готово. Справа от каждой ячейки выбранного диапазона появится небольшой значок со стрелкой вниз, нажав на которую можно открыть перечень наименований, который мы заранее составили. Щелкнув по нужному варианту из списка, он сразу же будет вставлен в ячейку. Кроме того, значение в ячейке теперь может соответствовать только наименованию из списка, что исключит любые возможные опечатки.

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

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

В первую очередь, эти инструменты нужно найти и активировать, так как по умолчанию они выключены. Переходим в меню “Файл”.
В перечне слева находим в самом низу пункт “Параметры” и щелкаем по нему.
Переходим в раздел “Настроить ленту” и в области “Основные вкладки” ставим галочку напротив пункта “Разработчик”. Инструменты разработчика будут добавлены на ленту программы. Кликаем OK, чтобы сохранить настройки.
Теперь в программе есть новая вкладка под названием “Разработчик”. Через нее мы и будем работать. Сначала создаем столбец с элементами, которые будут источниками значений для нашего выпадающего списка.
Переключаемся во вкладу “Разработчик”. В подразделе “Элементы управления” нажимаем на кнопку “Вставить”. В открывшемся перечне в блоке функций “Элементы ActiveX” кликаем по значку “Поле со списком”.
Далее нажимаем на нужную ячейку, после чего появится окно со списком. Настраиваем его размеры по границам ячейки. Если список выделен мышкой, на панели инструментов будет активен “Режим конструктора”. Нажимаем на кнопку “Свойства”, чтобы продолжить настройку списка.
В открывшихся параметрах находим строку “ListFillRange”. В столбце рядом через двоеточие пишем координаты диапазона ячеек, составляющих наш ранее созданный список. Закрываем окно с параметрами, щелкнув на крестик.
Затем кликаем правой кнопкой мыши по окну списка, далее – по пункту “Объект ComboBox” и выбираем “Edit”.
В результате мы получаем выпадающий список с заранее определенным перечнем.
Чтобы вставить его в несколько ячеек, наводим курсор на правый нижний угол ячейки со списком, и как только он поменяет вид на крестик, зажимаем левую кнопку мыши и тянем вниз до самой нижней строки, в которой нам нужен подобный список.

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. Делаем:

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

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

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

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

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

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

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

4 — Элемент ActiveX

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

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

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

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

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

1 Применение комбинации клавиш

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

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

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

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

2 Использование меню

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

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

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

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

Автоматическое заполнение, скорее всего, будет некорректным. Но при выборе значений вручную они будут выбираться из нужного диапазона — это становится возможным благодаря знакам доллара перед каждой частью адреса источника ($I$6: $I$8).

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

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

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

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

Включить режим разработчика. Для это следует кликнуть правой кнопкой мыши по ленте Excel, открыть настройки и поставить галочку напротив соответствующего пункта.
Перейти к появившейся новой вкладке. В меню «Вставить» выбрать поле со списком.
Кликнуть по любому месту таблицы. После того, как там появилось нужное поле, открыть его контекстное меню и выбрать «Формат объекта». Здесь следует указать связь с ячейкой, в которой будет находиться номер значения в списке (48/96 — второй, поэтому в ячейке G2 находится «2»). А еще — диапазон, откуда берутся данные.

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

Способ является сравнительно сложным, и его не стоит рассматривать в качестве основного. Зато после создания готового выпадающего списка в Экселе его можно будет расположить в любом месте таблицы. Для перетаскивания придется включить режим «Конструктора» на вкладке «Разработчик» и использовать правую кнопку мыши.

Excel 2007-2013

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

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

Excel Online

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

В Excel Online щелкните «Открыть в Excel» для открытия файла в классическом приложении Excel.
В классическом приложении создайте раскрывающийся список.
Теперь сохраните вашу книгу.
В Excel Online откройте книгу для просмотра и использования раскрывающегося списка.

Excel для Mac 2011

Выберите ячейки, в которой должен отображаться список.
На вкладке «Данные» в разделе «Инструменты» щелкните «Проверить».
Щелкните вкладку «Параметры», а затем во всплывающем меню «Разрешить» выберите пункт «Список».
Щелкните в поле «Источник» и введите текст или числа (разделенные запятыми), которые должны появиться в списке.
Чтобы закрыть диалоговое окно, в щелкните «ОК».

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

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

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

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

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

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

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

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

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

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

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

Пример выпадающего списка в Excel

В этом случае у Источника в значении стоит небольшая кнопка «Вниз», на которую можно кликнуть.

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

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

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

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

Для начала вам нужно создать именованный диапазон. Перейдите во вкладку «Формулы», затем выберите «Диспетчер имён» и «Создать».
Выделяем диапазон ячеек со значением, открываем вкладку «Формулы», нажимаем «Диспетчер имен»

Нажимаем «Создать»
Введите имя и диапазон, затем нажмите «ОК». Если вы перед этим выделяли нужные ячейки мышкой, то диапазон будет указан автоматически. Также примите к сведению, что имя диапазона должно быть уникальным и не должно содержать знаков препинания с пробелами.

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

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

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

Результат выпадающего связанного списка

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

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

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

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

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

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

Переходим во вкладку «Данные» программы Microsoft Excel. Выделяем область таблицы, где собираемся применять выпадающий список. Жмем на кнопку «Проверка данных», расположенную на Ленте.

Открывается окно проверки вводимых значений. Во вкладке «Параметры» в поле «Тип данных» выбираем параметр «Список». В поле «Источник» ставим знак равно, и сразу без пробелов пишем имя списка, которое присвоили ему выше. Жмем на кнопку «OK».

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

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

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

Для этого нужно подготовить как минимум три столбца. В первом будут заполнены наименования товаров, а во втором и третьем – их возможные единицы измерения. Столбцов с возможными вариациями единиц измерения может быть и больше.
Сначала создаем один общий список для всех наименований продуктов, выделив все строки столбца “Наименование”, через контекстное меню выделенного диапазона.
Задаем ему имя, например, “Питание”.
Затем таким же образом формируем отдельные списки для каждого продукта с соответствующими единицами измерения. Для большей наглядности возьмем в качестве примера первую позицию – “Лук”. Отмечаем ячейки, содержащие все единицы измерения для этого продукта, через контекстное меню присваиваем имя, которое полностью должно совпадать с наименованием.
Таким же образом создаем отдельные списки для всех остальных продуктов в нашем перечне.
После этого вставляем общий список с продуктами в верхнюю ячейку первого столбца основной таблицы – как и в описанном выше примере, через кнопку “Проверка данных” (вкладка “Данные”).
В качестве источника указываем “=Питание” (согласно нашему названию).
Затем кликаем по верхней ячейке столбца с единицами измерения, также заходим в окно проверки данных и в источнике указываем формулу “=ДВССЫЛ(A2)“, где A2 – номер ячейки с соответствующим продуктом.
Списки готовы. Осталось его только растянуть их все строки таблицы, как для столбца A, так и для столбца B.

Источники

https://mister-office.ru/funktsii-excel/dropdown-list-auto-excel.html
https://l-a-b-a.com/blog/1715-kak-stroit-vypadayushchie-spiski-v-excel
https://MicroExcel.ru/vypadayushhie-spiski/
https://ichip.ru/sovety/ekspluataciya/kak-sdelat-v-eksele-vypadayushchij-spisok-3-sposoba-762462
https://support.microsoft.com/ru-ru/office/%D1%81%D0%BE%D0%B7%D0%B4%D0%B0%D0%BD%D0%B8%D0%B5-%D1%80%D0%B0%D1%81%D0%BA%D1%80%D1%8B%D0%B2%D0%B0%D1%8E%D1%89%D0%B5%D0%B3%D0%BE%D1%81%D1%8F-%D1%81%D0%BF%D0%B8%D1%81%D0%BA%D0%B0-%D0%B2-excel-0a5b17bf-a950-e676-d759-131d9c9de9a4
https://WindowsTips.ru/vypadayushhij-spisok-v-excel
https://sysadmin-note.ru/article/kak-v-excel-sdelat-vypadayushhij-spisok/
https://SeoPulses.ru/kak-sdelat-vipadayushiy-spisok-excel/
https://pc-consultant.ru/rabota-na-komp-yutere/vypadajushhij-spisok-v-excel/
https://lumpics.ru/how-to-make-a-dropdown-list-in-excel/

Источник

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

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

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

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

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

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

Добавление списка на лист

Добавление поля со списком на лист

Форматирование элемента управления формы «Поле со списком»

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

Дополнительные сведения

См. также

Нужна дополнительная помощь?

Вариант 1: Группировка существующего списка

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

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

Вариант 4: Создание выпадающего списка из таблицы

Как автозаполнить в Excel

Как создать пользовательский список в Excel

1. Введите ваш список напрямую

2. Импортируйте ячейки из листа

3. Импортируйте список из именованного диапазона ячеек

3 примера пользовательских списков Excel

Создайте свои собственные пользовательские списки

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

#1. Параметры

#2. Подсказка по вводу

#3. Сообщение об ошибке

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

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

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

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

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

4 — Элемент ActiveX

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

1 Применение комбинации клавиш

2 Использование меню

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

Excel 2007-2013

Excel Online

Excel для Mac 2011

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

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

Пример выпадающего списка в Excel

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

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

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

А вот еще интересные статьи: