Создание зависимых списков с изменяемым источником
| Категория: Приемы и советы, Работа с данными ячеек, Формулы и функции | Опубликовано 13-11-2015
15
С помощью Проверки данных [Data Validation] можно организовать ввод данных путем выбора из предлагаемого списка, значения которого зависят от другого списка. Причем, используя функцию СМЕЩ [OFFSET] можно создать вариант, когда добавленные исходные значения будут отображаться в списках для выбора нужных значений. Если значения списка зависят от выбранного значения из другого списка, то можно создать связанный (зависимый) список нужных значений. Это позволит в значительной степени избежать не корректных комбинаций вводимых значений.
Например, в поле Европа происходит выбор одного из двух значений: Западная или Восточная, после этого в поле Страна предлагается список с соответствующими значениями.
Последовательность создания:
Выделить ячейку F2, где будет выбираться Европа.
На вкладке Данные [Data], в группе Работа с данными [Data Tools], выбрать Проверка данных [Data Validation] и на вкладке Параметры [Option], задать Условие проверки [Validation criteria] – Список [List], в качестве источника выделить ячейки B2 и C2
- Ячейкам значений стран (данные в столбцах B и C) необходимо присвоить имена – Западная и Восточная, с возможностью автоматического определения диапазона ячеек по мере изменения количества значений в соответствующих столбцах:
На вкладке Формулы [Formulas] выбрать Диспетчер имен [Name Manager] или нажать клавиши Ctrl+F3.
Создать имена с использованием функции СМЕЩ:
- Выделить ячейку F3, где будет выбираться Страна.
На вкладке Данные [Data], в группе Работа с данными [Data Tools], выбрать Проверка данных [Data Validation] и на вкладке Параметры [Option], задать Условие проверки [Validation criteria] – Список [List], в качестве источника ввести формулу:
=ЕСЛИ($F$2=”Западная”;Западная;Восточная)
[=IF ($F$2=”Западная”;Западная;Восточная)], где F2 – ячейка, которая содержит значение, выбираемого из первого списка.
При добавлении новых данных, они будут сразу показаны в выпадающем списке:
Примечание: подразумевается, что элементы списка вводятся последовательно, т.е. элемент списка не содержит пустые ячейки.
15.11.2015 (13:53)
Полезная статья. Как себя будут вести эти списки при установке защиты листа?
21.11.2015 (14:19)
ого как мощно
а сколько значений может быть в списке?
26.11.2015 (19:29)
Интересно! Даже и не подозревал, что такое возможно. Конечно, не простая формула, но ведь возможно 🙂
27.11.2015 (18:55)
Интересное сочетание
29.11.2015 (22:20)
Ксения, считается, что список может содержать 32767 элементов. Но, полагаю, что вряд ли кто захочет столько значений просматривать 🙂
15.12.2015 (17:54)
Я правильно понял, что без использования имени обойтись нельзя?
23.12.2015 (13:20)
Сильно! не просто, но сильно! Здорово!
23.12.2015 (16:49)
Для каких версий Excel работает? В 2010 ругается на ввод условия в ячейку F3, пишет, что нет таких диапазонов
24.12.2015 (12:13)
формулу можно написать: =ДВССЫЛ(F2)
31.12.2015 (20:42)
Konstantin, помниться мне, что в таких случаях ДВССЫЛ не работает, её можно использовать при зависимых списках с постоянным источником.
31.12.2015 (20:45)
Александр, это можно делать в любой версии Excel
Обратите внимание, чтобы в ячейке F2 было выбрано значение
13.01.2016 (19:02)
Я только недавно осилил создание списков, а тут и еще одно познание – создание подчиненных списков. Я в восторге!
08.02.2016 (12:58)
Я правильно понял, что такого рода подчинений может быть много?)
09.02.2016 (23:46)
Да, кончено. Аналогично
19.02.2019 (10:13)
А можно ли сделать так, чтобы при выборе значения из “главного” списка, значение в ячейке, где выбирается “зависимое” значение сразу выбирался какой-нибудь элемент из списка?
А то получается так, что ты в главном списке выбрал другое значение, а в зависимом до сих пор осталось значение от прежнего значения в главном списке.