rss
  •  

Создание зависимых списков с изменяемым источником

| Категория: Приемы и советы, Работа с данными ячеек, Формулы и функции |

14

С помощью Проверки данных [Data Validation] можно организовать ввод данных путем выбора из предлагаемого списка, значения которого зависят от другого списка. Причем, используя функцию СМЕЩ [OFFSET] можно создать вариант, когда добавленные исходные значения будут отображаться в списках для выбора нужных значений. Если значения списка зависят от выбранного значения из другого списка, то можно создать связанный (зависимый) список нужных значений. Это позволит в значительной степени избежать не корректных комбинаций вводимых значений.

Например, в поле Европа происходит выбор одного из двух значений: Западная или Восточная, после этого в поле Страна предлагается список с соответствующими значениями.

Последовательность создания:

  • Выделить ячейку F2, где будет выбираться Европа.
    На вкладке Данные [Data], в группе Работа с данными [Data Tools], выбрать Проверка данных [Data Validation] и на вкладке Параметры [Option], задать Условие проверки [Validation criteria] – Список [List], в качестве источника выделить ячейки B2 и C2

dv2.png

  • Ячейкам значений стран (данные в столбцах B и C) необходимо присвоить имена – Западная и Восточная, с возможностью автоматического определения диапазона ячеек по мере изменения количества значений в соответствующих столбцах:
    На вкладке Формулы [Formulas] выбрать Диспетчер имен [Name Manager] или нажать клавиши Ctrl+F3.
    Создать имена с использованием функции СМЕЩ:

dv3.png

  • Выделить ячейку F3, где будет выбираться Страна.
    На вкладке Данные [Data], в группе Работа с данными [Data Tools], выбрать Проверка данных [Data Validation] и на вкладке Параметры [Option], задать Условие проверки [Validation criteria] – Список [List], в качестве источника ввести формулу:
    =ЕСЛИ($F$2=»Западная»;Западная;Восточная)
    [=IF ($F$2=»Западная»;Западная;Восточная)], где F2 – ячейка, которая содержит значение, выбираемого из первого списка.

dv4.png

При добавлении новых данных, они будут сразу показаны в выпадающем списке:

dv5.png

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




Оставьте комментарий!

На сообщение “Создание зависимых списков с изменяемым источником” комментариев 14

  1. НаталиКо:

    Полезная статья. Как себя будут вести эти списки при установке защиты листа?

  2. Ксения:

    ого как мощно
    а сколько значений может быть в списке?

  3. Женя:

    Интересно! Даже и не подозревал, что такое возможно. Конечно, не простая формула, но ведь возможно 🙂

  4. Василий:

    Интересное сочетание

  5. Ольга Кулешова:

    Ксения, считается, что список может содержать 32767 элементов. Но, полагаю, что вряд ли кто захочет столько значений просматривать 🙂

  6. Димка:

    Я правильно понял, что без использования имени обойтись нельзя?

  7. Алёна:

    Сильно! не просто, но сильно! Здорово!

  8. Александр:

    Для каких версий Excel работает? В 2010 ругается на ввод условия в ячейку F3, пишет, что нет таких диапазонов

  9. Konstantin:

    формулу можно написать: =ДВССЫЛ(F2)

  10. Ольга Кулешова:

    Konstantin, помниться мне, что в таких случаях ДВССЫЛ не работает, её можно использовать при зависимых списках с постоянным источником.

  11. Ольга Кулешова:

    Александр, это можно делать в любой версии Excel
    Обратите внимание, чтобы в ячейке F2 было выбрано значение

  12. Евгений:

    Я только недавно осилил создание списков, а тут и еще одно познание — создание подчиненных списков. Я в восторге!

  13. Владислав:

    Я правильно понял, что такого рода подчинений может быть много?)

  14. Ольга Кулешова:

    Да, кончено. Аналогично