Создание зависимых раскрывающихся списков (Microsoft Excel)
Кэрол спросила, есть ли в Excel способ создавать раскрывающиеся списки, чтобы второй раскрывающийся список зависел от выбора, сделанного в первом раскрывающемся списке.
На самом деле есть несколько способов выполнить эту задачу, от простых формул до сложных макросов. Выбранный вами метод напрямую зависит от типа раскрывающихся списков, которые вы хотите создать. На самом деле в Excel можно создать три типа раскрывающихся списков:
Списки проверки . * Если вы хотите ограничить ввод в определенные ячейки, вы можете создать раскрывающиеся списки проверки.
Forms lists . * Вы можете использовать инструменты Forms (link: / excelribbon-Displaying_Excels_Developer_Tab [Developer tab]
ленты, Controls group, Insert tool) для создания раскрывающихся списков. Это замечательно, если вы собираетесь создать защищенную форму Excel.
Userforms . * Это диалоговые окна, созданные в редакторе VBA. Вы «запускаете» пользовательскую форму, вызывая ее из макроса. Это наиболее универсальная форма пользовательского интерфейса, поскольку она дает вам максимальную свободу в том, что видит пользователь. (Для создания этого также требуются самые передовые знания Excel.)
Вместо того, чтобы обсуждать, как создавать зависимые раскрывающиеся списки на основе каждого из этих типов раскрывающихся списков, я выберу простейший метод, который будет достаточным для большинства людей. Если вы используете функцию ДВССЫЛ вместе со списками проверки данных, довольно легко получить желаемый результат:
-
На пустом листе в вашей книге создайте список элементов, которые будут в первом раскрывающемся списке. Например, создайте список отделов в вашей компании, таких как продажи, исследования, исполнительный, производственный и т. Д. (В списке должны быть записи из одного слова).
-
Выберите список элементов, созданный на шаге 1, и назовите диапазон, используя такое имя, как «Отделы».
-
На том же листе создайте список элементов, которые могут появиться во вторичном раскрывающемся списке. Должен быть один список для каждой записи в списке, который вы создали на шаге 1. Например, вы можете создать список торгового персонала, список исследовательского персонала и т. Д.
-
Список по списку, выберите списки, которые вы создали на шаге 3. Дайте каждому списку имя из одного слова, которое соответствует именам, используемым в списке на шаге 1, например, Продажи, Исследования, Исполнительный и т. Д.
-
Переключитесь на рабочий лист, на котором вы хотите отобразить раскрывающиеся списки.
-
Выберите ячейки, в которые пользователи должны иметь возможность вводить элементы из вашего первого списка, созданного на шаге 1.
-
Откройте вкладку «Данные» на ленте.
-
Щелкните параметр «Проверка данных» в группе «Инструменты для работы с данными». Excel отображает диалоговое окно «Проверка данных». (См. Рис. 1.)
-
В раскрывающемся списке Разрешить выберите Список.
-
В поле Источник введите знак равенства, а затем имя, созданное на шаге 2. Например, = Отделы.
-
Щелкните ОК. Теперь вы указали, что в ячейки, выбранные на шаге 6, можно вводить только информацию из вашего первого списка.
-
Выберите ячейки, в которые пользователи должны иметь возможность вводить элементы из зависимых списков. Например, выберите ячейки справа от ячеек, выбранных на шаге 6.
-
Откройте вкладку «Данные» на ленте.
-
Щелкните параметр «Проверка данных» в группе «Инструменты для работы с данными». Excel снова отображает диалоговое окно «Проверка данных».
-
В раскрывающемся списке Разрешить выберите Список.
-
В поле Источник введите формулу, в которой используется функция КОСВЕННО. Если первая ячейка диапазона, выбранного на шаге 11, является ячейкой B3, и вы хотите, чтобы эта первая ячейка зависела от того, что выбрано в ячейке A3, вы должны использовать следующую формулу:
-
Щелкните ОК.
Вот и все. Теперь люди могут выбирать только из вашего основного списка, если они используют одну из ячеек, указанных на шаге 6, и из соответствующих зависимых списков, если они выбирают одну из ячеек на шаге 12.
Существует множество различных вариантов этого подхода (с использованием проверки данных). Вы можете найти дополнительную информацию о некоторых из этих подходов, посетив эти веб-страницы:
http://www.ozgrid.com/download/ (download the MatchingLists.zip file) http://www.contextures.com/xlDataVal02.html
ExcelTips — ваш источник экономичного обучения Microsoft Excel.
Этот совет (10545) относится к Microsoft Excel 2007 и 2010. Вы можете найти версию этого совета для более старого интерфейса меню Excel здесь:
link: / excel-Creating_Dependent_Drop-Lists [Создание зависимых Drop-Lists]
.