Досега в тази поредица за валидиране на данни се научихме да създаваме нормален падащ списък и динамичен падащ списък, използвайки различни техники с валидиране на данни в Excel.
И днес, в тази глава, ще ви покажем как да създадете зависим падащ списък в Microsoft Excel, като използвате различни методи.
Зависимият падащ списък е известен също като каскадно валидиране на данни и ограничава избора в падащ списък в зависимост от стойността, избрана в другата клетка, която съдържа проверка на данни. С други думи, това зависи от стойността, избрана в първия падащ списък, който определя стойностите, които да се показват във втория падащ списък.
Това е много често срещан сценарий за работа с големи данни или някои динамични отчети, където искате да имате 2nd клетка показва списък, който зависи от елемента от списъка, избран в първото падащо меню.
Тъй като знаем, че в excel има много начини за извършване на определена задача и по подобен начин има много начини за създаване на зависими проверки на данни в Excel. И днес ще демонстрираме 5 различни техники за създаване на зависим списък за валидиране на данни.
Необработените данни могат да бъдат в произволен ред или формат и всеки път не можете да промените данните или формата, за да получите това, което търсите.
И така, взехме един набор от данни, но в 3 различни формата, за да получим зависимия падащ списък. Както можете да видите, нашите данни са от лявата страна, която е от колона А до колона Е, и ще имаме очаквания изход от дясната страна, която е в колона J & K. Колона J ще има първична валидация списък, докато колоната K ще бъде зависима и ще показва стойностите в зависимост от стойността, избрана в колоната J.
1ул Пример:-
2nd Пример:-
3rd Пример:-
1ул Пример:-
Имаме списък с продукти за всеки код на продукт от колони A8 до E13. И искаме да изберем кода на продукта в J10, след което в зависимост от избрания код на продукта, името на продукта в клетка K10.
Първият метод:-
Първият метод е много прост и кратък и изисква само 3 стъпки, за да получите зависимия падащ списък. Той обаче работи успешно само докато не направите промени в обхвата си. След като промените данните си, първо ще трябва да промените именования диапазон, за да получите актуализираното каскадно валидиране на данни.
Следвайте стъпките, дадени по -долу:-
- Изберете цялата таблица от A8 до E13
- След това отидете в раздела „Формули“, след това в категорията „Определени имена“ щракнете върху „Създаване от селекция“
- Можете също да използвате клавишната комбинация CTRL + SHIFT + F3
- Ще се появи диалогов прозорец Създаване на имена от селекции
- Той иска да потвърди кои редове и колони да се използват за създаване на имената за други редове и колони. Потвърждаваме, че използваме „горния ред“ за създаване на имената и премахваме отметката от 2nd опция и след това щракваме върху OK
Забележка: - Пробелите и други специални символи, освен долната черта и точка, не се допускат като имена. По подразбиране той ще бъде преобразуван в долна черта. Така че, използвайте знак за подчертаване и точка, за да разделите думите. Също така първата буква не може да бъде число; трябва да е буква, долна черта или обратна черта.
- Сега, за да потвърдим, че всеки диапазон има име, отиваме в „Мениджър на имена“ (натиснете CTRL + F3)
- Там можем да видим всичките 5 именовани диапазона
- Също така можем да видим, че всяко име на диапазон има подчертаване вместо празно място в средата на низа
Сега ще създадем падащ списък:-
- Изберете клетка J10 и натиснете ALT ++ D+L, за да отворите диалоговия прозорец Валидиране на данни
- Изберете Списък> и след това въведете диапазона A8: E8 в раздела Източник
- Щракнете върху OK
- Сега ще създадем зависим списък в клетка K10
- Отворете диалоговия прозорец Проверка на данни, като натиснете клавиша ALT+D+L
- Изберете списък, в източника въведете тази функция:- = INDIRECT (SUBSTITUTE ($ J $ 10, "", "_"))
При валидирането на данни, за да създадем зависимия списък, използвахме функцията INDIRECT, за да върнем стойността въз основа на списъка за валидиране на първичните данни. И за да заменим подчертаването с интервал, ще използваме функцията SUBSTITUTE вътре във функцията INDIRECT.
- Щракнете върху OK
Когато изберем произволен код на продукт в клетка J10, списъкът с продукти на избрания код на продукта ще се появи в клетка K10. Например: - Избрахме ETV 501, сега можете да видите списък с зависими продукти, който се появява в клетка K10
Забележка: - Когато добавяте името на продукта и кода на продукта, които няма да се появят в списъка.
Например: - Добавихме Продукт 26 под кода на продукта ETV 505, но когато изберем продукт ETV 505, добавеният продукт не се появява в падащия списък.
Така че, ето как можете да създадете зависим падащ списък, използвайки проста техника само в 3 прости стъпки.
2nd Пример:-
В този пример ще видим как да получите зависим падащ списък, когато имате данните си, както е показано в тази вертикална таблица.
Ще използваме два различни метода за създаване на зависим падащ списък. И двете са почти сходни техники. Единият обаче е без посочения диапазон, а другият ще има посочения диапазон.
1ул Метод:-
За да направим същото, ще използваме функциите OFFSET, MATCH & COUNTIF заедно.
Тъй като знаем, че функцията OFFSET се използва за създаване на динамичния диапазон, следователно, за да създадем списък „Валидиране на динамични данни“, използваме функцията OFFSET за връщане на динамичния диапазон.
MATCH се използва за връщане на относителната позиция на елемент в списък в Excel. И тук ще ни помогне да съпоставим категорията, избрана в основния падащ списък в нашия диапазон на листа, и ще върне число.
COUNTIF се използва за получаване на броя на клетките, които отговарят на критериите. И тук ще използваме това, за да преброим броя редове, които да се покажат с помощта на функцията COUNTIF.
Следвайте стъпките, дадени по -долу:-
- Изберете клетката J21, в която ще създадем нашия списък за валидиране на първични данни
- Натиснете клавиша ALT+D+L, за да отворите диалоговия прозорец Валидиране на данни
- Изберете списък от разрешената категория
- Щракнете в раздела Източник и изберете диапазона от B20: B24
- И кликнете върху OK
- Отидете в клетка K21 и отворете отново диалоговия прозорец за потвърждаване на данните
- След това избираме Списък и в източника въвеждаме функцията по -долу:
- = OFFSET ($ E $ 19, MATCH ($ J $ 21, $ D $ 20: $ D $ 32,0), 0, COUNTIF ($ D $ 20: $ D $ 32, $ J $ 21))
- Щракнете върху OK
- В клетка K21 можем да видим всички съответни стойности на избрания код на продукта:-
Така че, по този начин можете да получите зависимия списък, като вземете препратки към клетки във функцията.
2nd Метод:-
В следващия метод ще използваме именован диапазон в същата функция, за да получим валидиране на каскадни данни. Първо трябва да създадем динамичния списък за продуктов код. В случай на добавяне на нов продукт към данните, падащото меню трябва да се актуализира, за да се покаже същото.
За да направите същото, следвайте стъпките, дадени по -долу:-
- Изберете B19, след това натиснете CTRL + F3, за да отворите прозореца „Мениджър на имена“
- Сега кликваме върху „Ново“ и се появява диалоговият прозорец „Определяне на име“
- Можем да видим, че името вече се появява в полето за име -това е така, защото сме избрали B9, преди да отворим прозореца „Мениджър на имена“. И тъй като B19 има текст в него, ако искаме, можем да го променим на друго име.
- Въведете формулата за споменаване по-долу:-
= OFFSET ('DependentDropDownList'! $ B $ 20,0,0, COUNTA ('DependentDropDownList'! $ B $ 20: $ B $ 32))
- Щракнете върху OK
Тъй като създадохме динамичен списък за уникални продукти, сега ще създадем динамичен диапазон за диапазон от кодове на продукти, който е в колона D.
Следвайте същите стъпки, които сме следвали за уникалния продукт:-
- Изберете клетката D19, отворете диалоговия прозорец Определяне на име
- Ще откриете, че името вече е там
- При позоваване въведете следната формула:-
= OFFSET ('Зависим падащ списък'! $ D $ 20,0,0, COUNTA ('Зависим падащ списък'! $ D $ 20: $ D $ 35))
- Щракнете върху OK
- Сега и двата динамични диапазона са готови. И така, отиваме в J22 и натискаме „ALT + D + L“ и избираме „Списък“
- В източника ще имаме именования диапазон, който сме го определили за „Уникален продуктов код“, така че натискаме F3, за да видим всички налични именовани диапазони
- Можем да видим именувания диапазон „Уникален продуктов код“, затова кликваме върху него и след това върху OK и натискаме enter
- В момента, в който натиснем enter, получаваме падаща стрелка в клетка J22, която съдържа списъка с уникални кодове на продукти
- Изберете клетка K22 и отворете диалоговия прозорец „Проверка на данни“
- Ще използваме същата функция, която сме използвали в последния метод, но с именен диапазон
- Изберете списък и след това в източника въведете формулата по-долу:-
= OFFSET ($ E $ 19, MATCH ($ J $ 22, Product_Code, 0), 0, COUNTIF (Product_Code, J22))
- Щракнете върху OK
- Сега имаме основното падащо меню, както и дъщерния списък с продукти
- Изберете продукт „ETV-101“ от J22, а в K22 можем да видим само имената, които попадат под този продукт „ETV-101“. И когато сменим всеки продукт („ETV-103) в J22, K22 показва съответните стойности за този код
Сега ще видим какво ще се случи, когато добавим нов продуктов код в списъка? Ще бъдат ли актуализирани тези падащи списъци?
Нека добавим нов продукт в списъка; Следвайте стъпките, дадени по-долу:-
- Добавете продуктов код в списъка на Unique_Prod_Code
- Също така добавете Product_Code и Product_Name в данните:-
- Сега проверете кода и името на продукта, добавен от падащия списък
3rd Пример:-
Имаме динамичните заглавия директно от таблицата и ще добавим нови продукти към асортимента. Таблицата е в същия формат, който използвахме за 1ул метод.
4th Метод:-
Следвайте стъпките, дадени по -долу:-
- Изберете заглавието A40: E40
- Първо създайте динамичния диапазон за заглавия, отворете диалоговия прозорец „Определяне на име“
- Напишете „Заглавие“ на мястото на името, а след това в „препраща към“ въведете формулата по-долу:-
- Въведете следната функция:-
- = OFFSET ('Зависим падащ списък'! $ A $ 40 ,,,, COUNTA ('Зависим падащ списък'! $ 40: $ 40))
- Щракнете върху OK
- Динамичният диапазон „Заглавие“ вече е готов
И сега ще създадем именования диапазон за всяко заглавие, следвайте стъпките, дадени по-долу:-
- Изберете таблицата от A40 до E50
- CTRL + SHIFT + F3 клавишна комбинация
- Премахваме отметката от 2nd опция
- И преди да кликнете върху OK, уверете се, че 1ул е избрана опция „Най -горния ред“
- Сега сме готови и с двата диапазона
Сега ще подготвим падащия списък на родителите
- Изберете клетката J42
- Отворете диалоговия прозорец Проверка на данни
- След това, след като изберете „Списък“, натискаме F3 в източника, за да получим именувания диапазон за заглавия. Кликваме върху „Заглавие“ и след това върху OK и натискаме enter. В момента имаме списък с родители в J42
- За да създадете списъка с подробности за елемента, изберете клеткатаK42
- Отворете диалоговия прозорец Валидиране на данни, като натиснете клавиша ALT+D+L
- Изберете Списък, след което въведете функцията по-долу в раздела Източник:-
- = OFFSET (ИНДИРЕКТЕН (СУБСТИТУТ ($ J $ 42, "", "_")) ,,, COUNTA (ИНДИРЕКТЕН (СУБСТИТУТ ($ J $ 42, "", "_")))
- Щракнете върху OK
Сега изберете някой елемент в J42, да речем, че избираме „Елемент 01“ и погледнете падащия списък K42. Както и предишните 3 метода, и тук имаме зависим списък.
Е какво ново? В първия пример не можете да добавите нито един продукт към списъка, но тук можете да добавите всеки нов продукт. И така, да кажем, че добавяме нов продукт към този артикул. Отиваме до A45 и въвеждаме „ETV-501 Prod 05“ и след това се връщаме към K42 и ето ви. Можете да видите, че новият продукт е добавен.
- Сега добавете няколко продукта под новия елемент
Когато избираме „Позиция 06“, отиваме до K42 и кликваме върху падащия списък. Изненадващо, нищо не се случва, когато щракнем върху падащата стрелка. Това е така, защото създадохме всичко динамично и забравихме да създадем динамичен диапазон за таблица, поради което продуктите не се показват в дъщерния списък.
За да направим това, трябва да използваме различни техники. Има два метода за това. Можете или да създадете таблицата, или просто да използвате само функцията OFFSET. В следващия метод ще използваме функцията OFFSET и ще видим трика за разширяване на диапазона на таблиците.
- И така, първо отиваме в J43 и натискаме „ALT + D + L“
- Избираме „Списък“ и след това в източника натискаме F3 и избираме „Заглавие“ и натискаме OK и след това натискаме enter
- Сега отиваме на K43 и след като избираме „Списък“, отиваме на „Източник“ и влизаме по -долу споменатата функция
= OFFSET ($ A $ 40,1, MATCH ($ J $ 43, $ 40: $ 40,0) -1, COUNTA (OFFSET ($ A $ 40,1, MATCH ($ J $ 43, $ 40: $ 40,0) -1,1000 , 1)))
- Щракнете върху OK
Сега се връщаме и избираме „Елемент 06 в клетката J43 и се връщаме към K43 и кликваме върху стрелката за падащо меню. Но този списък показва продукти, които сме добавили за нов артикул. И ние избираме първия продукт „ETV-506 Prod 01“.
Ето как можете да създадете зависим падащ списък, използвайки различни методи за всеки тип формат на данни.
Видео: Как да създадете зависим (каскаден) падащ списък в Excel с помощта на 5 различни техники в Microsoft Excel
Кликнете върху видео връзката за бърза справка за използването му. Абонирайте се за новия ни канал и продължете да учите с нас!
Ако ви харесаха нашите блогове, споделете го с приятелите си във Facebook. Можете също така да ни следвате в Twitter и Facebook.
Ще се радваме да чуем от вас, уведомете ни как можем да подобрим, допълним или обновим работата си и да я подобрим. Пишете ни на имейл сайта