Как сделать зависимые ячейки в Excel

Обновлено: 21.11.2024

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

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

Вот несколько примеров:

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

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

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

Пример зависимого раскрывающегося списка

В приведенном ниже примере столбец B содержит раскрывающееся меню для категории продуктов питания, а столбец C содержит параметры для выбранной категории. Если пользователь выбирает "Фрукты", он видит список фруктов, если выбирает "Орехи", то видит список орехов, а если выбирает "Овощи", то видит список овощей.

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

И проверка данных в столбце C использует эту пользовательскую формулу:

Где рабочий лист содержит следующие именованные диапазоны:

категория = E4:G4,
овощи = F5:F10,
орехи = G5:G9,
фрукты = E5:E11

Как это работает

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

Аналогично, ДВССЫЛ преобразует текст "A1:A10" в диапазон A1:A10 внутри другой функции:

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

Будьте уверены, у безумия есть метод :)

Преимущество ДВССЫЛ в том, что он позволяет использовать текст точно так же, как ссылку на ячейку. Это дает два ключевых преимущества:

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

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

B6 преобразуется в текст "орех", который преобразуется в диапазон G5:G9.

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

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

<р>1. Создайте необходимые списки. В примере создайте список фруктов, орехов и овощей на листе.

<р>2. Создайте именованные диапазоны для каждого списка: категория = E4:G4, овощи = F5:F10, орехи = G5:G9 и фрукты = E5:E11.

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

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

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

<р>4. Создайте правило проверки данных для зависимого раскрывающегося списка с пользовательской формулой на основе функции ДВССЫЛ:

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

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

Примечание. Применяемый нами подход не учитывает регистр. Именованный диапазон называется "орех", а значение в ячейке B6 – "орех", но функция ДВССЫЛ правильно разрешает указанный диапазон, даже если регистр различается.

Работа с пробелами

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

В этой формуле по-прежнему используется ДВССЫЛ, чтобы связать текстовое значение в ячейке A1 с именованным диапазоном, но перед запуском ДВССЫЛ функция ПОДСТАВИТЬ заменяет все пробелы символами подчеркивания. Если в тексте нет пробелов, ЗАМЕНА не действует.

Файл практики

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

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

Пользователь выбирает Pizza из раскрывающегося списка.

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

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

<р>1. На втором листе создайте следующие именованные диапазоны.

Имя	Адрес диапазона
Еда	A1:A3
Пицца	B1:B4
Блины	C1:C2
Китайские	D1:D3

< бр />

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

<р>3. На вкладке "Данные" в группе "Инструменты данных" нажмите "Проверка данных".

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

<р>4. В поле Разрешить нажмите Список.

<р>5. Нажмите в поле Источник и введите =Еда.

<р>7. Затем выберите ячейку E1.

<р>8. В поле Разрешить нажмите Список.

<р>9. Нажмите в поле Источник и введите =ДВССЫЛ($B$1).

Объяснение: функция ДВССЫЛ возвращает ссылку, заданную текстовой строкой. Например, пользователь выбирает китайский язык из первого раскрывающегося списка. =ДВССЫЛ($B$1) возвращает ссылку на китайский язык. В результате второй раскрывающийся список содержит элементы китайского языка.

Сэнди Риттенхаус

Сэнди Риттенхаус
Писатель

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

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

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

Начало работы

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Примечание. Функция ДВССЫЛ «возвращает ссылку, указанную текстовой строкой». Дополнительные сведения об этой функции см. на странице поддержки Microsoft.

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

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

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

› Как создать зависимый раскрывающийся список в Google Sheets
› Как создать простую форму в Microsoft Excel
› Худшее, что есть в телефонах Samsung, — это программное обеспечение Samsung.
› Почему прозрачные чехлы для телефонов желтеют?
› Что означает XD и как вы его используете?
› Как восстановить метки панели задач в Windows 11
›5 шрифтов, которые следует прекратить использовать (и лучшие альтернативы)
› Как установить Google Play Маркет в Windows 11

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

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

Например, если у вас есть таблица с группами расходов:

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

1. Создайте именованный диапазон (см. Создание и использование именованных диапазонов) для каждой группы расходов, например:

Примечание. Чтобы упростить настройку в будущем, укажите название группы расходов в качестве имени диапазона. Если название вашей группы состоит из двух или более слов, используйте символ "_" (как "Сборы и сборы").

2. Создайте раскрывающийся список для групп (см. Создание раскрывающегося списка в ячейке):

<р> 2.1. На вкладке "Данные" в группе "Инструменты данных" нажмите "Проверка данных":

или

<р> 2.2. В диалоговом окне "Проверка данных" на вкладке "Настройки":

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

Где ячейка categories!D10 содержит формулу:

= УНИКАЛЬНЫЙ (B10:B64):

Вы увидите раскрывающийся список:

3. Создайте зависимый раскрывающийся список для выбора типов расходов:

или

<р> 3.1. На вкладке "Данные" в группе "Инструменты данных" нажмите "Проверка данных".

<р> 3.2. В диалоговом окне "Проверка данных" на вкладке "Настройки":

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

= НЕПРЯМОЙ ( ).

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

ПОДСТАВИТЬ преобразует Сборы и сборы в правильное имя диапазона Сборы_сборов
ДВССЫЛ возвращает значения именованного диапазона для указанного имени диапазона.

4. Нажмите OK:

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

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

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

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

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

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

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