KutoolsforOffice — Одно решение — пять мощных инструментов.Меньше усилий — больше результата.

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

АвторСяоянДата изменения

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

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

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

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

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

Создание Динамический список только с уникальными значениями в Excel довольно затруднительно; вам следует выполнить следующие действия по порядку:

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

1. Нажмите «Формулы» > «Определить имя» (см. снимок экрана):

Выберите Формулы > Присвоить имя

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

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

настройка параметров для первого выпадающего списка

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

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

настройка параметров для второго выпадающего списка

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

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

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

введите формулу для извлечения уникальных значений первого типа

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

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

создайте имя диапазона для новых уникальных значений

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

настройка параметров для вставки первого выпадающего списка

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

первый выпадающий список без дублирующихся значений создан

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

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

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

введите формулу для извлечения уникальных значений второго типа

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

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

создайте имя диапазона для этих вторичных уникальных значений

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

настройка параметров для вставки второго выпадающего списка

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

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

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

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

1. Нажмите «Kutools» > «Раскрывающийся список» > «Динамический выпадающий список» (см. снимок экрана):

нажмите функцию Динамический выпадающий список Kutools

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

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

настройте параметры в диалоговом окне

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

Совет: С помощью этой функции вы легко отсортируете содержимое раскрывающегося списка по алфавиту — так данные станут более организованными и удобными для пользователя. В диалоговом окне нажмите кнопку «Расширенные настройки», затем выберите «Сортировать по алфавиту». Теперь при открытии выпадающего списка элементы будут отсортированы по алфавиту.
элементы отсортированы по алфавиту в выпадающем списке элементы отсортированы по алфавиту в выпадающем списке

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

Другие связанные статьи:

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

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

🤖KUTOOLS AI Помощник: Преобразуйте Анализ данных с помощью:Интеллектуального выполнения   |  Генерации кода|  Создания пользовательские формулы  |  Анализа данных и построения диаграмм|  Вызова Расширенные функции…
Популярные функции:Поиск, выделение или Отметить дубликаты   |  Удалить пустые строки   |  Объединить столбцы или ячеек без потери данных   |  Округление без использования формул…
Супер ПОИСК:VLookup по нескольким критериям  |  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.

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