Перейти до основного матеріалу

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

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

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

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


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

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

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

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

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

примітки: A2: A100 це список даних, на основі якого ви створите перший спадний список. Якщо у вас великі дані, просто змініть потрібне посилання на комірку.

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

примітки: B2: B100 це список даних, на основі якого ви створите залежний розкривний список. Якщо у вас великі дані, просто змініть потрібне посилання на клітинку.

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

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

=INDEX(Category,MATCH(0,COUNTIF($D$1:D1,Category),0))
примітки: У наведеній вище формулі, Категорія це ім’я діапазону, яке ви створили на кроці 2, і D1 є наведеною вище клітинкою вашої клітинки формули, будь ласка, змініть їх відповідно до ваших потреб.

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

примітки: D2: D100 це список унікальних значень, який ви щойно витягли, якщо у вас є великі дані, просто змініть потрібне посилання на клітинку.

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

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

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

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

=INDEX(Food,MATCH(0,COUNTIF($E$1:E1,Food)+(Category<>$H$2),0))
примітки: У наведеній вище формулі, харчування це ім’я діапазону, яке ви створили для залежних даних спадного списку, Категорія це ім’я діапазону, яке ви створили для даних першого розкривного списку, і E1 це клітинка вище вашої клітинки формули, H2 це комірка, яку ви вставили в перший спадний список, змініть їх відповідно до своїх потреб.

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

примітки: E2: E100 це вторинний список унікальних значень, який ви щойно витягли, якщо у вас є великі дані, просто змініть потрібне посилання на клітинку.

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

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


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

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

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

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

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

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

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

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


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

  • Створити випадаючий список із зображеннями в Excel
  • В Excel ми можемо швидко і легко створити випадаючий список зі значеннями комірок, але чи намагалися ви коли-небудь створювати випадаючий список із зображеннями, тобто, коли ви натискаєте одне значення зі спадного списку, його відносний зображення буде відображатися відразу, як показано нижче. У цій статті я розповім про те, як вставити випадаючий список із зображеннями в Excel.
  • Створіть розкривний список із кількома прапорцями в Excel
  • Багато користувачів Excel, як правило, створюють випадаючий список із декількома прапорцями, щоб вибирати кілька елементів зі списку за раз. Насправді ви не можете створити список із кількома прапорцями за допомогою перевірки даних. У цьому підручнику ми покажемо вам два способи створення випадаючого списку з декількома прапорцями в Excel.
  • Створити багаторівневий залежний випадаючий список в Excel
  • В Excel ви можете швидко і легко створити залежний випадаючий список, але чи намагалися ви коли-небудь створювати багаторівневий залежний випадаючий список, як показано на наступному знімку екрана? У цій статті я розповім про те, як створити багаторівневий залежний випадаючий список в Excel.
  • Створити випадаючий список, але показати різні значення в Excel
  • На аркуші Excel ми можемо швидко створити випадаючий список за допомогою функції перевірки даних, але чи намагались ви коли-небудь показувати інше значення, натискаючи випадаючий список? Наприклад, у мене є такі дані двох стовпців у стовпці A та стовпці B, тепер мені потрібно створити випадаючий список зі значеннями у стовпці Ім'я, але, коли я вибираю ім'я зі створеного випадаючого списку, відповідний значення у стовпці Число відображається, як показано на наступному скріншоті. Ця стаття представить деталі для вирішення цього завдання.

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

🤖 Kutools AI Aide: Революціонізуйте аналіз даних на основі: Інтелектуальне виконання   |  Згенерувати код  |  Створення спеціальних формул  |  Аналізуйте дані та створюйте діаграми  |  Викликати функції Kutools...
Популярні функції: Знайдіть, виділіть або визначте дублікати   |  Видалити порожні рядки   |  Об’єднайте стовпці або клітинки без втрати даних   |   Раунд без Формули ...
Супер пошук: VLookup за кількома критеріями    Багатозначний VLookup  |   VLookup на кількох аркушах   |   Нечіткий пошук ....
Розширений розкривний список: Швидке створення випадаючого списку   |  Залежний спадний список   |  Виберіть розкривний список, що вибирається ....
Менеджер колонок: Додайте конкретну кількість стовпців  |  Перемістити стовпці  |  Перемкнути статус видимості прихованих стовпців  |  Порівняйте діапазони та стовпці ...
Особливості: Фокус сітки   |  Перегляд дизайну   |   Велика панель формул    Диспетчер робочих книг і аркушів   |  Бібліотека ресурсів (автотекст)   |  Вибір дати   |  Об’єднайте робочі аркуші   |  Шифрування/розшифрування клітинок    Надсилайте листи за списком   |  Супер фільтр   |   Спеціальний фільтр (фільтр жирний/курсив/закреслений...) ...
Топ-15 наборів інструментів12 текст Tools (додати текст, Видалити символи, ...)   |   50 + Графік типи (діаграма Ганта, ...)   |   40+ Практичний Формули (Розрахуйте вік на основі дня народження, ...)   |   19 вставка Tools (Вставте QR-код, Вставити зображення зі шляху, ...)   |   12 Перетворення Tools (Числа до слів, Валютна конверсія, ...)   |   7 Злиття та розділення Tools (Розширені комбіновані ряди, Розділені клітини, ...)   |   ... і більше

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

Опис


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

  • Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
  • Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
  • Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations