Откакто падащите менюта за проверка на данните бяха добавени към Excel през 1997 г., хората се опитват да изработят начин за промяна на второто падащо меню въз основа на избора в първото падащо меню.
Например, ако изберете Fruit in A2, падащото меню A4 ще предложи Apple, Banana, Cherry. Но ако изберете Билки от А2, списъкът в А4 ще предлага анасон, босилек, канела. През годините имаше много решения. Покрих го поне два пъти в подкаста:
- Класическият метод използва много именувани диапазони, както е показано в епизод 383.
- Друг метод използва формули OFFSET в епизод 1606.
С пускането на новите формули на динамичен масив в Public Preview, новата функция FILTER ще ни даде още един начин да направим Dependent Validation.
Кажете, че това е вашата база данни с продукти:
Използвайте формула от =SORT(UNIQUE(B4:B23))в D4, за да получите уникален списък с класификациите. Това е чисто нов тип формула. Една формула в D4 връща много отговори, които ще се разлеят в много клетки. За да се обърнете към диапазона Spiller, бихте използвали =D4#вместо =D4.
Изберете клетка, която да съдържа менюто за проверка на данните. Изберете Alt + DL, за да отворите проверка на данните. Променете Разрешаване на "Списък". Посочете =D4#като източник на списъка. Имайте предвид, че Hashtag (#) е Spiller - това означава, че имате предвид цялата гама Spiller.
Планът е някой да избере класификация от първото падащо меню. След това формула от =FILTER(A4:A23,B4:B23=H3,"Choose Class First")в Е4 ще върне всички продукти от тази категория. Обърнете внимание, че използването на „Изберете първо клас“ като незадължителен трети аргумент. Това ще предотврати #VALUE! появата на грешка.
В списъка може да има различен брой елементи в зависимост от избраната категория. Настройването на проверка на данни, сочещо към, =E4#ще се разшири или свие с дължината на списъка.
Гледам видео
Видео транскрипция
Научете Excel от, Подкаст Епизод 2248: Зависима проверка с помощта на масиви.
Е, хей. Това е било разглеждано два пъти преди в подкаста, как да направите зависимо валидиране и какво зависи валидиране е да изберете първо категория и след това, в отговор на това, второто падащо меню ще се промени само на елементи от тази категория и преди това беше сложно и с новите динамични масиви, които бяха обявени през септември 2018 г. … и те се пускат, така че трябва да имате Office 365. Точно сега 10 октомври, чух че те са на около 50% от вътрешните хора на Office, така че те ги пускат много бавно. Вероятно ще премине през първата половина на 2019 г., преди да ги получите, но ще ни позволи да направим зависима проверка по много по-лесен начин.
И така, тук имам две формули. Първата формула е УНИКАЛНАТА от всички класификации и я изпратих в командата SORT. Това ми дава 1 формула, връщаща 5 резултата и която живее в D4. И така, тук, където искам да избера проверка на данните, ще (DL - 1:09) … ИЗТОЧНИКЪТ ще бъде = D4 #. Този # - ние го наричаме разпръсквач - уверете се, че връща всички резултати от D4. Така че, ако добавя нова категория тук и това расте, D4 # ще вземе тази допълнителна сума, нали? (= СОРТИРАНЕ (УНИКАЛНО (B4: B23)))
И така, тази първа проверка е доста проста, но сега, след като знаем, че сме избрали CITRUS - това ще бъде по-трудно - искам да филтрирам списъка в колона A, където елементът в колона B е равен на избрания елемент , добре? И така, първо трябва да им позволим да изберат нещо и след това, след като разбера, че това е ЦИТЪР, след това ми дайте ВАРЯ, ОРАНЖЕВ и ТАНГЕРИН, те биха избрали нещо друго. ЯГОДА. Виж това. Научните списания казват, че бананът е зрънце. Не съм съгласен с това. Не ми се струва зрънце, но не ме обвинявайте. Просто, знаете ли, използвам Интернет. БАНАНА, БЪЗЪЦИ И МАЛИНА.
Знаете ли, караницата с това е, че някой първоначално ще дойде тук, без да е избрал нищо, и, така че в този случай имаме ИЗБЕРЕТЕ КЛАС ПЪРВИЯ, което е третият аргумент, който казва, че ако нищо не бъде намерено, нали? Така че, знаете ли, по този начин, ако започнем в този сценарий, изборът ще бъде ПЪРВИ ИЗБЕРЕТЕ КЛАС. Идеята е, че те избират КЛАСА, РАСТИТЕЛНИ, тези актуализации и след това тези елементи идват от този списък. ВАЛИДИРАНЕТО НА ДАННИТЕ тук, разбира се, добре, това е друг разпръсквач, = E4 #, за да работи, нали? Така че, това е страхотно. (= ФИЛТЪР (A4: A23, B4: B23 = H3, „Изберете първо клас“))
Вижте моята книга Excel Dynamic Arrays. Това е … ще бъде безплатно до края на 2018 г. Проверете връзката долу в описанието на YouTube, как можете да го изтеглите, точно за този пример плюс 29 други примера за това как да използвате тези елементи.
Е, приключи за днес. Динамичните масиви ни дават друг начин да направим зависимо валидиране. Ако не сте в Office 365 и все още нямате такива, не се колебайте да се върнете към, предполагам, видео 1606, което показва стария начин за това.
Искам да ви благодаря, че се отбихте. Ще се видим следващия път за поредното излъчване от.
Изтеглете Excel файла
За да изтеглите файла на Excel: depend-validation-using-arrays.xlsx
За да научите повече за динамичните масиви, разгледайте динамичните масиви на Excel направо до точката.
Мисъл на деня в Excel
Помолих приятелите си в Excel Master за съвети относно Excel. Днешната мисъл за размисъл:
„Никога не изтривайте файл на Excel, без първо да го архивирате.“
Майк Александър
