Note: The other languages of the website are Google-translated. Back to English

Як створити залежні розкривні списки з унікальними значеннями лише в 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. На цьому кроці ви можете вставити перший розкривний список. Клацніть клітинку, куди потрібно вставити розкривний список, а потім клацніть дані > Перевірка достовірності даних > Перевірка достовірності даних, і в Перевірка достовірності даних діалогове вікно, виберіть список від дозволяти спадне меню, а потім введіть цю формулу: =Унікальна категорія в джерело текстове поле, див. знімок екрана:

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, в Залежний випадаючий список у діалоговому вікні виконайте такі дії:

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

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

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


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

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

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

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

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

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

  • Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
  • Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
  • Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!
дно офісної таблиці
Коментарі (0)
Поки немає оцінок. Оцініть першим!
There are no comments posted here yet
Залишайте свої коментарі
Публікація як гість
×
Оцініть цю публікацію:
0   Персонажі
Рекомендовані місця