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

Выпадающий список в excel
На чтение
61 мин.
Просмотров
107
Дата обновления
25.10.2024
Выпадающий список в 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 – ячейка с именем первого диапазона.
  • Бывает, когда из раскрывающегося списка необходимо выбрать сразу несколько элементов. Рассмотрим пути реализации задачи.

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

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

    Выпадающий список с поиском
  • На вкладке «Разработчик» находим инструмент «Вставить» – «ActiveX». Здесь нам нужна кнопка «Поле со списком» (ориентируемся на всплывающие подсказки).
  • Щелкаем по значку – становится активным «Режим конструктора».

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

  • Жмем «Свойства» – открывается перечень настроек.
  • Вписываем диапазон в строку ListFillRange (руками). Ячейку, куда будет выводиться выбранное значение – в строку LinkedCell.

    Для изменения шрифта и размера – Font.

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

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

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

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

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

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

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

    Для этого, в ячейки А1:А7 вписываем данные, которые будут отображаться в списке. Теперь выделим ячейку, в которой создадим выпадающий список – В2.

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

    На вкладке «Параметры» в поле «Тип данных» выбираем «Список». В поле «Источник» можно ввести значения различными способами:

    1 – вводим значения для списка вручную, через точку с запятой;

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

    3 – выделяем ячейки с именами, кликаем по ним правой кнопкой мыши и выбираем из меню «Присвоить имя».

    Дальше впишите «Имя» для выделенных данных.

    Выделяем ячейку В2 и в поле «Источник» ставим «=», затем пишем созданное имя.

    Таким образом, мы создали простой выпадающий список в Excel.

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

    Добавляем значения в выпадающий список – динамический список

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

    Выделяем диапазон ячеек – D1:D8, затем на вкладке «Главная» нажимаем «Форматировать как таблицу» и выбираем любой стиль.

    Подтверждаем расположение данных и ставим галочку в поле «Таблица с заголовками».

    Вверху пишем заголовок таблицы – «Сотрудники», и заполняем ее данными.

    Выделяем ячейку, в которой будет выпадающий список и кликаем по кнопочке «Проверка данных». В следующем окне, в поле «Источник», пишем следующее: =ДВССЫЛ(«Таблица1[Сотрудники]»). У меня одна таблица на листе, поэтому пишу «Таблица1», если будет вторая – «Таблица2», и так далее.

    Теперь добавим новое имя сотрудника в наш список: Ира. В выпадающем списке оно появилось. Если мы удалим любое имя из таблицы, из списка оно тоже удалится.

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

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

    На Листе 2, выделяем одну ячейку или диапазон ячеек, затем кликаем по кнопочке «Проверка данных».

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

    Теперь можно дописывать имена на Листе 1, они будут добавляться в выпадающие списки на Листе 2.

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

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

    Первый называем «Имя», второй – «Фамилия», третий – «Отч».

    Сделаем еще один диапазон, в котором будут прописаны присвоенные имена. Назовем его «Сотрудники».

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

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

    Важно

    Первый выпадающий список создан. Теперь в ячейке F2 создадим второй список, который должен зависеть от первого. Если в первом выберем «Имя», во втором отобразится список имен, если выберем «Фамилия» – список фамилий.

    Выделяем ячейку и кликаем по кнопочке «Проверка данных». В поле «Тип данных» выбираем «Список», в поле источник прописываем следующее: =ДВССЫЛ($Е$1). Здесь Е1 – это ячейка с первым выпадающим списком.

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

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

    Теперь в имени диапазона выбираем «Фамилия», и внизу, вместо последней ячейки С3, напишите С10. Нажмите галочку.

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

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

    (1

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

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

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

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

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

    Для этого, в ячейки А1:А7 вписываем данные, которые будут отображаться в списке. Теперь выделим ячейку, в которой создадим выпадающий список – В2.

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

    На вкладке «Параметры» в поле «Тип данных» выбираем «Список». В поле «Источник» можно ввести значения различными способами:

    1 – вводим значения для списка вручную, через точку с запятой;

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

    3 – выделяем ячейки с именами, кликаем по ним правой кнопкой мыши и выбираем из меню «Присвоить имя».

    Дальше впишите «Имя» для выделенных данных.

    Выделяем ячейку В2 и в поле «Источник» ставим «=», затем пишем созданное имя.

    Таким образом, мы создали простой выпадающий список в Excel.

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

    Добавляем значения в выпадающий список – динамический список

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

    Выделяем диапазон ячеек – D1:D8, затем на вкладке «Главная» нажимаем «Форматировать как таблицу» и выбираем любой стиль.

    Подтверждаем расположение данных и ставим галочку в поле «Таблица с заголовками».

    Вверху пишем заголовок таблицы – «Сотрудники», и заполняем ее данными.

    Выделяем ячейку, в которой будет выпадающий список и кликаем по кнопочке «Проверка данных». В следующем окне, в поле «Источник», пишем следующее: =ДВССЫЛ(«Таблица1»). У меня одна таблица на листе, поэтому пишу «Таблица1», если будет вторая – «Таблица2», и так далее.

    Теперь добавим новое имя сотрудника в наш список: Ира. В выпадающем списке оно появилось. Если мы удалим любое имя из таблицы, из списка оно тоже удалится.

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

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

    На Листе 2, выделяем одну ячейку или диапазон ячеек, затем кликаем по кнопочке «Проверка данных».

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

    Теперь можно дописывать имена на Листе 1, они будут добавляться в выпадающие списки на Листе 2.

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

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

    Первый называем «Имя», второй – «Фамилия», третий – «Отч».

    Сделаем еще один диапазон, в котором будут прописаны присвоенные имена. Назовем его «Сотрудники».

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

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

    Важно

    Первый выпадающий список создан. Теперь в ячейке F2 создадим второй список, который должен зависеть от первого. Если в первом выберем «Имя», во втором отобразится список имен, если выберем «Фамилия» – список фамилий.

    Выделяем ячейку и кликаем по кнопочке «Проверка данных». В поле «Тип данных» выбираем «Список», в поле источник прописываем следующее: =ДВССЫЛ($Е$1). Здесь Е1 – это ячейка с первым выпадающим списком.

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

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

    Теперь в имени диапазона выбираем «Фамилия», и внизу, вместо последней ячейки С3, напишите С10. Нажмите галочку.

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

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

    Поделитесь статьёй с друзьями:

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

    Спасибо, всё получилось.

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

    Автору спасибо за хорошую статью! Как оказалось, есть масса возможностей их создания!

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

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

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

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

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

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

    Совет

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

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

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

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

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

    Зависимый выпадающий список подкатегорий

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

    Рабочая исходная таблица Excel

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

    Можно было бы также использовать таблицы с первого изображения. Разумеется, формулы были бы разными.

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

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

    Ну хорошо. Теперь, по очереди я опишу шаги создания зависимого выпадающего списка.

    1. Имена диапазонов ячеек

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

    Присвоим имена двум диапазонам. Список всех категорий и рабочий список категорий. Это будут диапазоны A3:A5 (список категорий в зеленой таблице на первом изображении) и G3:G15 (список повторяющихся категорий в фиолетовой рабочей таблице).

    Для того чтобы назвать список категорий:

  • Выберите диапазон A3:A5.
  • В поле имени (поле слева от строки формулы) введите название «Категория».
  • Подтвердите с помощью клавиши Enter.
  • Такое же действие совершите для диапазона рабочего списка категорий G3:G15, который вы можете вызвать «Рабочий_Список». Этот диапазон мы будем использовать в формуле.

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

    Это будет просто:

  • Выберите ячейку, в которую вы хотите поместить список. В моем случае это A12.
  • В меню «ДАННЫЕ» выберите инструмент «Проверка данных». Появится окно «Проверка вводимых значений».
  • В качестве типа данных выберите «Список».
  • В качестве источника введите: =Категория (рисунок ниже).
  • Подтвердите с помощью OK.
  • Проверка вводимых значений – Категория.

    Результат следующий:

    Раскрывающийся список для категории.

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

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

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

    В источник списка введите следующую формулу:

    Вид окна «Проверка вводимых значений»:

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

    Как видите, весь трюк зависимого списка состоит в использовании функции СМЕЩ. Ну хорошо, почти весь. Помогают ей функции ПОИСКПОЗ и СЧЕТЕСЛИ. Функция СМЕЩ позволяет динамически определять диапазоны. Вначале мы определяем ячейку, от которой должен начинаться сдвиг диапазона, а в последующих аргументах определяем его размеры.

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

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

    Например, для категории Питание мы хотим отобразить диапазон H6:H11, для Транспорта — диапазон H12: H15 и т. д.

    Обратите внимание

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

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

    Проще будет понять на примере: диапазон для категории Питание перемещен на 4 ячейки вниз относительно ячейки H2 (начинается с 4 ячейки от H2). В 4-ой ячейке столбца Подкатегория (не включая заголовок, так как речь идет о диапазоне с именем Рабочий_Список), есть слово Питание (его первое появление).

    Мы используем этот факт собственно для определения начала диапазона. Послужит нам для этого функция ПОИСКПОЗ (введенная в качестве второго аргумента функции СМЕЩ):

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

    Конечно же, обе функции уже включены в функцию СМЕЩ, которая описана выше. Кроме того, обратите внимание, что как в функции ПОИСКПОЗ, так и в СЧЕТЕСЛИ, есть ссылка на диапазон названный Рабочий_Список. Как я уже упоминал ранее, не обязательно использовать имена диапазонов, можно просто ввести $H3: $H15. Однако использование имен диапазонов в формуле делает ее проще и легко читаемой.

    Вот и все:

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

    Одна формула, ну не такая уж и простая, но облегчающая работу и защищает от ошибок при вводе данных!

    Как в экселе сделать выпадающий список – 3 разных способа создания

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

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

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

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

    Этот же пункт меню можно запустить сочетанием клавиш Alt+»Стрелка вниз» и программа автоматически предложит в выпадающем списке значения ячеек, которые вы ранее заполняли данными.

    На изображении ниже программа предложила 4 варианта заполнения (дублирующиеся данные Excel не показывает).

    Важно

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

    Использование горячих клавиш для раскрытия выпадающего списка данных

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

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

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

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

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

    Создание набора данных для списка

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

    Введите имя для набора данных

  • Выделите ячейки (можно сразу несколько ячеек), в которых планируется создать выпадающий список. Во вкладке «ДАННЫЕ» вверху документа нажмите на «Проверка данных».

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

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

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

    Кроме списка можно вводить данные вручную. Если введенные данные не совпадут с одним из данных — программа выдаст ошибку

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

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

  • Нажмите на «Файл» в левом верхнем углу приложения.
  • Выберите пункт «Параметры» и нажмите на него.
  • В окне настройки параметров Excel во вкладке «Настроить ленту» поставьте галочку напротив вкладки «Разработчик».
  • Включение вкладки «РАЗРАБОТЧ

    0 Комментариев
    Комментариев на модерации: 0
    Оставьте комментарий