Як створити залежні розкривні списки з унікальними значеннями лише в Excel?

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

Створюйте залежні розкривні списки з унікальними значеннями лише за допомогою функцій Excel

• Крок 1: Створіть назви діапазонів для даних першого та другого розкривного списку
• Крок 2: витягніть унікальні значення та створіть перший розкривний список
• Крок 3: витягніть унікальні значення та створіть залежний спадний список

Створюйте залежні розкривні списки з унікальними значеннями лише за допомогою дивовижної функції

Створюйте залежні розкривні списки з унікальними значеннями лише за допомогою функцій Excel

Дещо складно створювати залежні розкривні списки з унікальними значеннями лише в Excel, вам слід застосувати наведену нижче операцію крок за кроком:

Крок 1: Створіть назви діапазонів для даних першого та другого розкривного списку

1. Клацання Формули > Визначте ім’я, див. скріншот:

2, в Нове ім'я у діалоговому вікні введіть назву діапазону Категорія в Назва текстове поле (ви можете ввести будь-яке інше потрібне ім’я), а потім введіть цю формулу =ЗМІЩЕННЯ($A$2,0,0;COUNTA($A$2:$A$100)) в Відноситься до текстове поле й нарешті натисніть OK Кнопка:

3. Перейдіть до створення назви діапазону для другого спадного меню, натисніть Формули > Визначте ім’я щоб відкрити діалогове вікно «Нове ім’я», введіть назву діапазону харчування в Назва текстове поле (ви можете ввести будь-яке інше потрібне ім’я), а потім введіть цю формулу =ЗМІЩЕННЯ($B$2,0,0;COUNTA($B$2:$B$100)) в Відноситься до текстове поле й нарешті натисніть OK Кнопка:

Крок 2: витягніть унікальні значення та створіть перший розкривний список

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

5. Потім створіть назву діапазону для цих нових унікальних значень, клацніть Формули > Визначте ім’я відкрити Нове ім'я у діалоговому вікні введіть назву діапазону Унікальна категорія в Назва текстове поле (ви можете ввести будь-яке інше потрібне ім’я), а потім введіть цю формулу =ЗМІЩЕННЯ($D$2; 0; 0; КІЛЬКІСТЬ(ЯКЩО($D$2:$D$100="", "", 1)), 1) в Відноситься до текстове поле, нарешті, натисніть OK , щоб закрити діалогове вікно.

6. На цьому кроці ви можете вставити перший розкривний список. Клацніть клітинку, куди потрібно вставити розкривний список, а потім клацніть дані > Перевірка достовірності даних > Перевірка достовірності даних, і в Перевірка достовірності даних діалогове вікно, виберіть список від дозволяти спадне меню, а потім введіть цю формулу: =Унікальна категорія в джерело текстове поле, див. знімок екрана:

7. Потім натисніть кнопку OK буде створено перший розкривний список без повторюваних значень, як показано на зображенні нижче:

Крок 3: витягніть унікальні значення та створіть залежний спадний список

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

9. Потім перейдіть до створення назви діапазону для цих вторинних унікальних значень, натисніть Формули > Визначте ім’я відкрити Нове ім'я у діалоговому вікні введіть назву діапазону Унікальна їжа в Назва текстове поле (ви можете ввести будь-яке інше потрібне ім’я), а потім введіть цю формулу =ЗМІЩЕННЯ($E$2; 0; 0; КІЛЬКІСТЬ(ЯКЩО($E$2:$E$100="", "", 1)), 1) в Відноситься до текстове вікно. Нарешті натисніть OK , щоб закрити діалогове вікно.

10. Після створення назви діапазону для вторинних унікальних значень тепер ви можете вставити залежний спадний список. Будь ласка, натисніть дані > Перевірка достовірності даних > Перевірка достовірності даних, і в Перевірка достовірності даних діалогове вікно, виберіть список від дозволяти спадне меню, а потім введіть цю формулу: =Унікальна їжа в Джерелоe текстове поле, дивіться знімок екрана:

11. Клацання OK залежні розкривні списки лише з унікальними значеннями створюються успішно, як показано нижче:

Створюйте залежні розкривні списки з унікальними значеннями лише за допомогою дивовижної функції

Наведений вище метод може бути головним болем для більшості з нас, тут я розповім про зручний інструмент- Kutools для Excel, З його Динамічний розкривний список функцію, ви можете легко вирішити цю проблему.

після установки Kutools для Excel, будь ласка, зробіть так:

1. Клацання Кутулс > Випадаючий список > Динамічний розкривний список, див. скріншот:

2, в Залежний випадаючий список у діалоговому вікні виконайте такі дії:

• вибрати РежимB: 2-5 Рівні, що розкривний список від режим розділ;
• Виберіть дані, на основі яких потрібно створити залежний спадний список Діапазон даних коробка;
• Потім виберіть вихідний діапазон, у якому потрібно розмістити залежний розкривний список Вихідний діапазон коробка

3. Потім натисніть Ok кнопку, залежні розкривні списки вставляються у вибір, а повторювані значення також виключаються. Дивіться демонстрацію нижче:

Натисніть, щоб завантажити Kutools для Excel зараз!

Більше відносних статей:

Найкращі інструменти для підвищення продуктивності офісу

Kutools для Excel вирішує більшість ваших проблем і збільшує продуктивність на 80%

• Повторне використання: Швидко вставте складні формули, діаграми і все, що ви використовували раніше; Шифрувати комірки з паролем; Створити список розсилки та надсилати електронні листи ...
• Супер формула бар (легко редагувати кілька рядків тексту та формули); Макет читання (легко читати та редагувати велику кількість комірок); Вставте у відфільтрований діапазон...
• Об’єднати клітинки / рядки / стовпці без втрати даних; Вміст розділених комірок; Об'єднати повторювані рядки / стовпці... Запобігання дублюючим клітинам; Порівняйте діапазони...
• Виберіть Повторюваний або Унікальний Рядки; Виберіть Пусті рядки (усі клітинки порожні); Супер знахідка та нечітка знахідка у багатьох робочих зошитах; Випадковий вибір ...
• Точна копія Кілька клітинок без зміни посилання на формулу; Автоматичне створення посилань на кілька аркушів; Вставте кулі, Прапорці та інше ...
• Витяг тексту, Додати текст, Видалити за позицією, Видаліть пробіл; Створення та друк проміжних підсумків підкачки; Перетворення вмісту комірок та коментарів...
• Супер фільтр (зберегти та застосувати схеми фільтрів до інших аркушів); Розширене сортування за місяцем / тижнем / днем, частотою та іншим; Спеціальний фільтр жирним, курсивом ...
• Поєднайте робочі зошити та робочі аркуші; Об’єднати таблиці на основі ключових стовпців; Розділіть дані на кілька аркушів; Пакетне перетворення xls, xlsx та PDF...
• Понад 300 потужних функцій. Підтримує Office / Excel 2007-2019 та 365. Підтримує всі мови. Простота розгортання на вашому підприємстві чи в організації. Повна функція 30-денної безкоштовної пробної версії. 60-денна гарантія повернення грошей.

Детальніше ... Завантажити безкоштовно... Придбати ... 

---

Вкладка Office забезпечує інтерфейс з вкладками для Office і значно спрощує вашу роботу

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

Детальніше ... Завантажити безкоштовно... Придбати ...