Перейти к содержимому

Kutools для Office — один пакет. Пять инструментов. Выполняйте больше.

Как создать зависимые выпадающие списки с уникальными значениями в Excel?

Author Xiaoyang Last modified

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

create dependent drop down lists with unique values

Создание зависимых выпадающих списков с уникальными значениями с помощью функций Excel

Создание зависимых выпадающих списков с уникальными значениями с помощью Kutools для Excel

Создание зависимых выпадающих списков с уникальными значениями с помощью функций Excel

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

Шаг 1: Создайте имена диапазонов для данных первого и второго выпадающего списка

1. Нажмите «Формулы» > «Присвоить имя», см. скриншот:

Click Formulas > Define Name

2. В диалоговом окне «Новое имя» введите имя диапазона Категория в текстовое поле «Имя» (вы можете ввести любое другое имя, которое вам нужно), а затем введите эту формулу =OFFSET($A$2,0,0,COUNTA($A$2:$A$100)) в текстовое поле «Ссылается на» и наконец нажмите кнопку ОК:

Примечание: A2:A100 — это список данных, на основе которого вы создадите первый выпадающий список. Если у вас большой объем данных, просто измените ссылку на ячейку по необходимости.

set options for the first drop down

3. Продолжайте создание имени диапазона для второго выпадающего списка, нажмите «Формулы» > «Присвоить имя», чтобы открыть диалоговое окно «Новое имя», введите имя диапазона Еда в текстовое поле «Имя» (вы можете ввести любое другое имя, которое вам нужно), а затем введите эту формулу =OFFSET($B$2,0,0,COUNTA($B$2:$B$100)) в текстовое поле «Ссылается на», наконец, нажмите кнопку ОК:

Примечание: B2:B100 — это список данных, на основе которого вы создадите зависимый выпадающий список. Если у вас большой объем данных, просто измените ссылку на ячейку по необходимости.

set options for the second drop down

Шаг 2: Извлеките уникальные значения и создайте первый выпадающий список

4. Теперь вам нужно извлечь уникальные значения для данных первого выпадающего списка, введя следующую формулу в ячейку, нажав клавиши Ctrl + Shift + Enter одновременно, затем перетащите маркер заполнения вниз до тех пор, пока не начнут появляться ошибочные значения, см. скриншот:

=INDEX(Категория,MATCH(0,COUNTIF($D$1:D1,Категория),0))
Примечание: В приведенной выше формуле Категория — это имя диапазона, которое вы создали на шаге 2, а D1 — это ячейка над вашей формульной ячейкой, пожалуйста, измените их по необходимости.

enter a formula to extract the first type unique values

5. Затем создайте имя диапазона для этих новых уникальных значений, нажмите «Формулы» > «Присвоить имя», чтобы открыть диалоговое окно «Новое имя», введите имя диапазона Уникальная_категория в текстовое поле «Имя» (вы можете ввести любое другое имя, которое вам нужно), а затем введите эту формулу =OFFSET($D$2, 0, 0, COUNT(IF($D$2:$D$100="", "", 1)), 1) в текстовое поле «Ссылается на». Наконец, нажмите кнопку ОК, чтобы закрыть диалоговое окно.

Примечание: D2:D100 — это список уникальных значений, которые вы только что извлекли. Если у вас большой объем данных, просто измените ссылку на ячейку по необходимости.

create a range name for the new unique values

6. На этом шаге вы можете вставить первый выпадающий список. Щелкните ячейку, куда вы хотите вставить выпадающий список, затем нажмите «Данные» > «Проверка данных» > «Проверка данных», и в диалоговом окне «Проверка данных» выберите «Список» из раскрывающегося меню «Разрешить», затем введите эту формулу: =Уникальная_категория в текстовое поле «Источник», см. скриншот:

set options to insert the first drop-down list

7. Затем нажмите кнопку ОК, первый выпадающий список без повторяющихся значений создан, как показано на скриншоте ниже:

the first drop-down list without duplicate values is created

Шаг 3: Извлеките уникальные значения и создайте зависимый выпадающий список

8. Извлеките уникальные значения для вторичного выпадающего списка, скопируйте и вставьте следующую формулу в ячейку, затем нажмите клавиши Ctrl + Shift + Enter одновременно, затем перетащите маркер заполнения вниз до тех пор, пока не начнут появляться ошибочные значения, см. скриншот:

=INDEX(Еда,MATCH(0,COUNTIF($E$1:E1,Еда)+(Категория<>$H$2),0))
Примечание: В приведенной выше формуле Еда — это имя диапазона, которое вы создали для данных зависимого выпадающего списка, Категория — это имя диапазона, которое вы создали для данных первого выпадающего списка, E1 — это ячейка над вашей формульной ячейкой, H2 — это ячейка, где вы вставили первый выпадающий список, пожалуйста, измените их по необходимости.

enter a formula to extract the second type unique values

9. Затем продолжайте создание имени диапазона для этих вторичных уникальных значений, нажмите «Формулы» > «Присвоить имя», чтобы открыть диалоговое окно «Новое имя», введите имя диапазона Уникальная_еда в текстовое поле «Имя» (вы можете ввести любое другое имя, которое вам нужно), а затем введите эту формулу =OFFSET($E$2, 0, 0, COUNT(IF($E$2:$E$100="", "", 1)), 1) в текстовое поле «Ссылается на». Наконец, нажмите кнопку ОК, чтобы закрыть диалоговое окно.

Примечание: E2:E100 — это список вторичных уникальных значений, которые вы только что извлекли. Если у вас большой объем данных, просто измените ссылку на ячейку по необходимости.

create a range name for this secondary unique values

10. После создания имени диапазона для вторичных уникальных значений теперь вы можете вставить зависимый выпадающий список. Пожалуйста, нажмите «Данные» > «Проверка данных» > «Проверка данных», и в диалоговом окне «Проверка данных» выберите «Список» из раскрывающегося меню «Разрешить», затем введите эту формулу: =Уникальная_еда в текстовое поле «Источник», см. скриншот:

set options to insert the second drop-down list

11. Нажмите кнопку ОК, зависимые выпадающие списки с уникальными значениями успешно созданы, как показано в демонстрации ниже:

Создание зависимых выпадающих списков с уникальными значениями с помощью Kutools для Excel

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

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

1. Нажмите «Kutools» > «Выпадающий список» > «Динамический выпадающий список», см. скриншот:

click Dynamic Drop-down List feature of kutools

2. В диалоговом окне «Зависимый выпадающий список» выполните следующие действия:

  • Выберите «Режим B: 2-5 уровней зависимого выпадающего списка» в разделе «Режим»;
  • Выберите данные, на основе которых вы хотите создать зависимый выпадающий список, из поля «Диапазон данных»;
  • Затем выберите диапазон вывода, куда вы хотите поместить зависимый выпадающий список, из поля «Область размещения списка».
  • Наконец, нажмите кнопку ОК.

set the options in the dialog box

3. Теперь зависимые выпадающие списки вставлены в выбранный диапазон, при этом повторяющиеся значения исключены. См. демонстрацию ниже:

Совет: С помощью этой функции вы можете легко отсортировать содержимое выпадающего списка в алфавитном порядке, делая данные более организованными и удобными для пользователя. В диалоговом окне нажмите кнопку «Расширенные настройки», а затем выберите «Сортировать по алфавиту». Теперь, когда вы нажимаете на выпадающий список, элементы сортируются по алфавиту.
select Sort Alphabetically option items are sorted alphabetically in the drop down list

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

Больше связанных статей:

  • Создание выпадающего списка с изображениями в Excel
  • В Excel мы можем быстро и легко создать выпадающий список со значениями ячеек, но пробовали ли вы создать выпадающий список с изображениями? То есть, когда вы выбираете значение из выпадающего списка, соответствующее изображение сразу отображается, как показано в демонстрации ниже. В этой статье я расскажу, как вставить выпадающий список с изображениями в Excel.
  • Создание выпадающего списка с несколькими флажками в Excel
  • Многие пользователи Excel предпочитают создавать выпадающий список с несколькими флажками, чтобы выбирать несколько элементов из списка за один раз. На самом деле, вы не можете создать список с несколькими флажками с помощью проверки данных. В этом руководстве мы покажем вам два метода создания выпадающего списка с несколькими флажками в Excel.
  • Создание многоуровневого зависимого выпадающего списка в Excel
  • В Excel вы можете быстро и легко создать зависимый выпадающий список, но пробовали ли вы создать многоуровневый зависимый выпадающий список, как показано на следующем скриншоте? В этой статье я расскажу, как создать многоуровневый зависимый выпадающий список в Excel.
  • Создание выпадающего списка, но с отображением разных значений в Excel
  • На листе Excel мы можем быстро создать выпадающий список с помощью функции проверки данных, но пробовали ли вы отобразить другое значение при выборе из выпадающего списка? Например, у меня есть следующие два столбца данных в столбце A и столбце B, теперь мне нужно создать выпадающий список со значениями из столбца Имя, но, когда я выбираю имя из созданного выпадающего списка, соответствующее значение из столбца Номер отображается, как показано на следующем скриншоте. Эта статья представит подробности решения этой задачи.

Лучшие инструменты для повышения продуктивности в Office

🤖 Kutools AI Aide: Совершенно новый подход к анализу данных благодаря: Интеллектуальное выполнение |  Генерация кода  |  Создание пользовательских формул |  Анализ данных и построение диаграмм  |  Вызов Kutools Functions…
Популярные функции: Поиск, выделение или отметка дубликатов | Удалить пустые строки | Объединить столбцы или адреса без потери данных | Округлить ...
Супер ПОИСК: VLOOKUP по нескольким критериям | VLOOKUP по нескольким значениям | Многолистовой поиск | Распознавание нечетких соответствий ...
Расширенный раскрывающийся список: Быстро создать раскрывающийся список | Зависимый раскрывающийся список | Множественный выбор в раскрывающемся списке ...
Менеджер столбцов: Добавить определённое количество столбцов | Переместить столбцы | Переключить видимость скрытых столбцов | Сравнить диапазоны и столбцы ...
Рекомендуемые функции: Сетка фокусировки | Дизайн листа | Улучшенная строка формулы | Управление книгой и листами | Библиотека автотекста | Выбор даты | Объединить данные | Зашифровать/расшифровать ячейки | Отправить письмо по списку | Супер фильтр | Специальный фильтр (фильтр жирный/курсив/зачеркнутый...) ...
Топ15 наборов инструментов:12 текстовых инструментов (Добавить текст, Удалить определенные символы, ...) |50+ типов диаграмм (Диаграмма Ганта, ...) |40+ полезных формул (Расчет возраста на основе даты рождения, ...) |19 инструментов для вставки (Вставить QR-код, Вставить изображение по пути, ...) |12 инструментов преобразования (Преобразовать в слова, Конвертация валюты, ...) |7 инструментов объединения и разделения (Расширенное объединение строк, Разделить ячейки, ...) | ... и многое другое
Используйте Kutools на предпочитаемом вами языке — поддерживает Английский, Испанский, Немецкий, Французский, Китайский и более40 других языков!

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

Office Tab добавляет вкладки в Office и делает вашу работу намного проще

  • Включите режим вкладок для редактирования и чтения в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
  • Открывайте и создавайте несколько документов во вкладках одного окна вместо новых отдельных окон.
  • Увеличьте свою продуктивность на50% и уменьшите количество щелчков мышью на сотни ежедневно!

Все надстройки Kutools. Один установщик

Пакет Kutools for Office включает надстройки для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro — идеально для команд, работающих в разных приложениях Office.

Excel Word Outlook Tabs PowerPoint
  • Комплексный набор — надстройки для Excel, Word, Outlook и PowerPoint плюс Office Tab Pro
  • Один установщик, одна лицензия — настройка занимает считанные минуты (MSI-совместимо)
  • Совместная работа — максимальная эффективность между приложениями Office
  • 30-дневная полнофункциональная пробная версия — без регистрации и кредитной карты
  • Лучшее соотношение цены и качества — экономия по сравнению с покупкой отдельных надстроек