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

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

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

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

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

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

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

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

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

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

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

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

Для применения этого метода включите отображение вкладки «Разработчик»: откройте меню «Файл» — «Параметры» — «Настройка ленты». В столбце «Основные вкладки» отметьте галочкой пункт «Разработчик». Подтвердите действие нажатием «ОК» — вкладка будет создана.

2. Из меню «Вставить» вкладки «Разработчик» выберите пункт «Элементы управления формы» — «Поле со списком».

3. Нарисуйте на листе курсором ваш будущий раскрывающийся список. Кликните по нему правой кнопкой мыши и выберите из меню пункт «Формат объекта».

5. Подтвердите ввод и нажмите «ОК». Список будет создан.

Теперь установите TRUE и FALSE соответственно на 1 и 0. Мы делаем это с двойным отрицанием. Проще говоря, это двойной минус, который не меняет величину числа, но позволяет по возможности получать действительные числа:

Как в Экселе сделать выпадающий список, Excel 2007, 2010, 2013 и 2016

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

Затем выполняется серия операций деления, в которых одна делится на каждую цифру этой матрицы. Это преобразует все неуникальные значения в дробные числа, соответствующие количеству повторений. Например, если число или текст появляется в списке 2 раза, в массиве создаются 2 элемента, равных 0,5 (1/2 = 0,5). А если он встречается 3 раза, то в массиве создаются 3 элемента из 0,333333.

Параметры и функции поиска в списке

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

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

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

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

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

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

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