Как создать зависимые выпадающие списки с уникальными значениями в Excel?
Зависимые выпадающие списки — это мощный инструмент в Excel для проверки данных и обеспечения согласованного ввода информации. Когда к этому добавляется требование отображать только уникальные значения, они становятся еще более ценными. Чтобы исключить повторяющиеся значения и оставить только уникальные при создании зависимого выпадающего списка, это руководство предоставляет пошаговые инструкции по созданию зависимых выпадающих списков в Excel с уникальными значениями.
Создание зависимых выпадающих списков с уникальными значениями с помощью функций Excel
- Шаг 1: Создайте имена диапазонов для данных первого и второго выпадающего списка
- Шаг 2: Извлеките уникальные значения и создайте первый выпадающий список
- Шаг 3: Извлеките уникальные значения и создайте зависимый выпадающий список
Создание зависимых выпадающих списков с уникальными значениями с помощью Kutools для Excel
Создание зависимых выпадающих списков с уникальными значениями с помощью функций Excel
Довольно сложно создать зависимые выпадающие списки только с уникальными значениями в Excel. Необходимо выполнить следующие шаги один за другим:
Шаг 1: Создайте имена диапазонов для данных первого и второго выпадающего списка
1. Нажмите «Формулы» > «Присвоить имя», см. скриншот:
2. В диалоговом окне «Новое имя» введите имя диапазона Категория в текстовое поле «Имя» (вы можете ввести любое другое имя, которое вам нужно), а затем введите эту формулу =OFFSET($A$2,0,0,COUNTA($A$2:$A$100)) в текстовое поле «Ссылается на» и наконец нажмите кнопку ОК:
3. Продолжайте создание имени диапазона для второго выпадающего списка, нажмите «Формулы» > «Присвоить имя», чтобы открыть диалоговое окно «Новое имя», введите имя диапазона Еда в текстовое поле «Имя» (вы можете ввести любое другое имя, которое вам нужно), а затем введите эту формулу =OFFSET($B$2,0,0,COUNTA($B$2:$B$100)) в текстовое поле «Ссылается на», наконец, нажмите кнопку ОК:
Шаг 2: Извлеките уникальные значения и создайте первый выпадающий список
4. Теперь вам нужно извлечь уникальные значения для данных первого выпадающего списка, введя следующую формулу в ячейку, нажав клавиши Ctrl + Shift + Enter одновременно, затем перетащите маркер заполнения вниз до тех пор, пока не начнут появляться ошибочные значения, см. скриншот:
5. Затем создайте имя диапазона для этих новых уникальных значений, нажмите «Формулы» > «Присвоить имя», чтобы открыть диалоговое окно «Новое имя», введите имя диапазона Уникальная_категория в текстовое поле «Имя» (вы можете ввести любое другое имя, которое вам нужно), а затем введите эту формулу =OFFSET($D$2, 0, 0, COUNT(IF($D$2:$D$100="", "", 1)), 1) в текстовое поле «Ссылается на». Наконец, нажмите кнопку ОК, чтобы закрыть диалоговое окно.
6. На этом шаге вы можете вставить первый выпадающий список. Щелкните ячейку, куда вы хотите вставить выпадающий список, затем нажмите «Данные» > «Проверка данных» > «Проверка данных», и в диалоговом окне «Проверка данных» выберите «Список» из раскрывающегося меню «Разрешить», затем введите эту формулу: =Уникальная_категория в текстовое поле «Источник», см. скриншот:
7. Затем нажмите кнопку ОК, первый выпадающий список без повторяющихся значений создан, как показано на скриншоте ниже:
Шаг 3: Извлеките уникальные значения и создайте зависимый выпадающий список
8. Извлеките уникальные значения для вторичного выпадающего списка, скопируйте и вставьте следующую формулу в ячейку, затем нажмите клавиши Ctrl + Shift + Enter одновременно, затем перетащите маркер заполнения вниз до тех пор, пока не начнут появляться ошибочные значения, см. скриншот:
9. Затем продолжайте создание имени диапазона для этих вторичных уникальных значений, нажмите «Формулы» > «Присвоить имя», чтобы открыть диалоговое окно «Новое имя», введите имя диапазона Уникальная_еда в текстовое поле «Имя» (вы можете ввести любое другое имя, которое вам нужно), а затем введите эту формулу =OFFSET($E$2, 0, 0, COUNT(IF($E$2:$E$100="", "", 1)), 1) в текстовое поле «Ссылается на». Наконец, нажмите кнопку ОК, чтобы закрыть диалоговое окно.
10. После создания имени диапазона для вторичных уникальных значений теперь вы можете вставить зависимый выпадающий список. Пожалуйста, нажмите «Данные» > «Проверка данных» > «Проверка данных», и в диалоговом окне «Проверка данных» выберите «Список» из раскрывающегося меню «Разрешить», затем введите эту формулу: =Уникальная_еда в текстовое поле «Источник», см. скриншот:
11. Нажмите кнопку ОК, зависимые выпадающие списки с уникальными значениями успешно созданы, как показано в демонстрации ниже:
Создание зависимых выпадающих списков с уникальными значениями с помощью Kutools для Excel
Вышеупомянутый метод, хотя и эффективен, может быть довольно трудоемким и сложным для большинства из нас, особенно при работе с большими наборами данных или если вы не знакомы с продвинутыми функциями Excel, такими как именованные диапазоны или динамические формулы. К счастью, с Kutools для Excel этот процесс становится намного проще и быстрее. Kutools предоставляет удобный интерфейс и мощные инструменты, которые позволяют создавать зависимые выпадающие списки с уникальными значениями всего за несколько кликов, исключая необходимость ручной настройки или сложных формул.
1. Нажмите «Kutools» > «Выпадающий список» > «Динамический выпадающий список», см. скриншот:
2. В диалоговом окне «Зависимый выпадающий список» выполните следующие действия:
- Выберите «Режим B: 2-5 уровней зависимого выпадающего списка» в разделе «Режим»;
- Выберите данные, на основе которых вы хотите создать зависимый выпадающий список, из поля «Диапазон данных»;
- Затем выберите диапазон вывода, куда вы хотите поместить зависимый выпадающий список, из поля «Область размещения списка».
- Наконец, нажмите кнопку ОК.
3. Теперь зависимые выпадающие списки вставлены в выбранный диапазон, при этом повторяющиеся значения исключены. См. демонстрацию ниже:
Совет: С помощью этой функции вы можете легко отсортировать содержимое выпадающего списка в алфавитном порядке, делая данные более организованными и удобными для пользователя. В диалоговом окне нажмите кнопку «Расширенные настройки», а затем выберите «Сортировать по алфавиту». Теперь, когда вы нажимаете на выпадающий список, элементы сортируются по алфавиту.
Создание зависимых выпадающих списков с уникальными значениями в Excel может значительно повысить точность и удобство использования данных. Будь то использование собственных инструментов Excel или расширенного дополнения, такого как Kutools, зависимые выпадающие списки с уникальными значениями являются бесценным дополнением к любому рабочему процессу управления данными, обеспечивая эффективность и точность в вашей работе. Если вы заинтересованы в изучении дополнительных советов и приемов Excel, наш сайт предлагает тысячи учебных материалов.
Больше связанных статей:
- Создание выпадающего списка с изображениями в Excel
- В Excel мы можем быстро и легко создать выпадающий список со значениями ячеек, но пробовали ли вы создать выпадающий список с изображениями? То есть, когда вы выбираете значение из выпадающего списка, соответствующее изображение сразу отображается, как показано в демонстрации ниже. В этой статье я расскажу, как вставить выпадающий список с изображениями в Excel.
- Создание выпадающего списка с несколькими флажками в Excel
- Многие пользователи Excel предпочитают создавать выпадающий список с несколькими флажками, чтобы выбирать несколько элементов из списка за один раз. На самом деле, вы не можете создать список с несколькими флажками с помощью проверки данных. В этом руководстве мы покажем вам два метода создания выпадающего списка с несколькими флажками в Excel.
- Создание многоуровневого зависимого выпадающего списка в Excel
- В Excel вы можете быстро и легко создать зависимый выпадающий список, но пробовали ли вы создать многоуровневый зависимый выпадающий список, как показано на следующем скриншоте? В этой статье я расскажу, как создать многоуровневый зависимый выпадающий список в Excel.
- Создание выпадающего списка, но с отображением разных значений в Excel
- На листе Excel мы можем быстро создать выпадающий список с помощью функции проверки данных, но пробовали ли вы отобразить другое значение при выборе из выпадающего списка? Например, у меня есть следующие два столбца данных в столбце A и столбце B, теперь мне нужно создать выпадающий список со значениями из столбца Имя, но, когда я выбираю имя из созданного выпадающего списка, соответствующее значение из столбца Номер отображается, как показано на следующем скриншоте. Эта статья представит подробности решения этой задачи.
Лучшие инструменты для повышения продуктивности работы с Office
Ускорьте работу в Excel с Kutools для Excel и ощутите новую степень эффективности. Kutools для Excel предлагает более300 расширенных функций для повышения продуктивности и экономии времени. Нажмите здесь, чтобы выбрать нужную вам функцию...
Office Tab добавляет вкладки в Office и делает вашу работу намного проще
- Включите редактирование и чтение с вкладками в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна, а не в отдельных окнах.
- Увеличьте свою продуктивность на50% и сократите сотни лишних кликов мышью каждый день!