Odkąd menu rozwijane Walidacja danych zostały dodane do programu Excel w 1997 r., Ludzie próbowali znaleźć sposób na zmianę drugiego menu rozwijanego w oparciu o wybór z pierwszego menu rozwijanego.
Na przykład, jeśli wybierzesz Owoce w A2, lista rozwijana w A4 wyświetli Apple, Banan, Cherry. Ale jeśli wybierzesz Zioła z A2, lista w A4 będzie zawierać Anyż, Bazylię, Cynamon. Na przestrzeni lat było wiele rozwiązań. Omówiłem to co najmniej dwa razy w Podcastu:
- Klasyczna metoda wykorzystywała wiele nazwanych zakresów, jak pokazano w odcinku 383.
- Inna metoda wykorzystywała formuły OFFSET w odcinku 1606.
Wraz z wydaniem nowych formuł tablic dynamicznych w podglądzie publicznym, nowa funkcja FILTER zapewni nam inny sposób przeprowadzania walidacji zależnej.
Powiedz, że to jest Twoja baza danych produktów:
Użyj wzoru =SORT(UNIQUE(B4:B23))w D4, aby uzyskać unikalną listę klasyfikacji. To zupełnie nowy rodzaj formuły. Jedna formuła w D4 zwraca wiele odpowiedzi, które rozleją się na wiele komórek. Aby odnieść się do zakresu Spiller, należy użyć =D4#zamiast =D4.
Wybierz komórkę, w której ma znajdować się menu Sprawdzanie danych. Wybierz Alt + DL, aby otworzyć Walidację danych. Zmień opcję Zezwól na „Lista”. Określ =D4#jako źródło listy. Zauważ, że Hashtag (#) to Spiller - oznacza to, że odnosisz się do całego zakresu Spiller.
Plan jest taki, że ktoś wybierze klasyfikację z pierwszego rozwijanego menu. Następnie formuła =FILTER(A4:A23,B4:B23=H3,"Choose Class First")w E4 zwróci wszystkie produkty w tej kategorii. Zwróć uwagę, że użycie opcji „Wybierz najpierw klasę” jako opcjonalnego trzeciego argumentu. Zapobiegnie to #VALUE! wystąpienie błędu.
W zależności od wybranej kategorii lista może zawierać różną liczbę elementów. Skonfigurowanie sprawdzania poprawności danych, na które wskazuje, =E4#spowoduje rozszerzenie lub skrócenie listy wraz z długością listy.
Obejrzyj wideo
Transkrypcja wideo
Learn Excel From, Podcast Episode 2248: Dependent Validation Using Arrays.
Więc hej. Zostało to omówione dwa razy wcześniej w podcastie, jak przeprowadzić walidację zależną i czym jest walidacja zależna, możesz najpierw wybrać kategorię, a następnie, w odpowiedzi na to, drugie menu zmieni się na tylko elementy z tej kategorii, a wcześniej było to skomplikowane, a wraz z nowymi tablicami dynamicznymi, które zostały ogłoszone we wrześniu 2018 r.… i są one wdrażane, więc musisz mieć Office 365. W tej chwili 10 października słyszałem że są u około 50% osób z wewnątrz Office, więc wprowadzają je bardzo powoli. Prawdopodobnie minie pierwsza połowa 2019 r., Zanim je otrzymasz, ale pozwoli nam to przeprowadzić zależną weryfikację w znacznie łatwiejszy sposób.
Mam tutaj dwie formuły. Pierwsza formuła to UNIQUE wszystkich klasyfikacji i wysłałem ją do polecenia SORT. To daje mi 1 formułę zwracającą 5 wyników i która znajduje się w D4. Więc tutaj, gdzie chcę wybrać walidację danych, ja (DL - 1:09)… ŹRÓDŁO będzie = D4 #. Że # - nazywamy to wyciekiem - upewnij się, że zwraca wszystkie wyniki z D4. Tak więc, jeśli dodam tutaj nową kategorię, a ta będzie rosła, D4 # podniesie tę dodatkową kwotę, dobrze? (= SORTUJ (UNIKALNE (B4: B23)))
Tak więc ta pierwsza walidacja jest dość prosta, ale teraz, gdy wiemy, że wybraliśmy CYTRUS - to będzie trudniejsze - chcę przefiltrować listę w kolumnie A, gdzie pozycja w kolumnie B jest równa wybranej pozycji , w porządku? Więc najpierw musimy pozwolić im coś wybrać, a potem, kiedy już wiem, że to CYTRUS, a potem daj mi LIMONKĘ, POMARAŃCZĘ i TANGERINE, wybiorą coś innego. JAGODA. Spójrz na to. Czasopisma naukowe podają, że banan to jagoda. Nie zgadzam się z tym. Nie wydaje mi się to jagodą, ale nie obwiniaj mnie. Po prostu, no wiesz, używam Internetu. BANAN, ELDERBERRY i RASPBERRY.
Wiesz, kłopot z tym jest taki, że ktoś początkowo przyjdzie tutaj bez wybrania czegokolwiek, więc w takim przypadku mamy WYBIERZ KLASĘ NAJPIERW, który jest trzecim argumentem, który mówi, że jeśli nic nie zostanie znalezione, dobrze? Więc wiesz, w ten sposób, jeśli zaczniemy od tego scenariusza, wybór będzie polegał na WYBIERZ KLASĘ NAJPIERW. Chodzi o to, że wybierają KLASĘ, WARZYWA, tę aktualizację, a następnie te elementy pochodzą z tej listy. WALIDACJA DANYCH tutaj, oczywiście, to kolejny spiller, = E4 #, aby to zadziałało, dobrze? Więc to jest fajne. (= FILTR (A4: A23, B4: B23 = H3, „Wybierz najpierw klasę”))
Sprawdź moją książkę Excel Dynamic Arrays. To jest… to będzie darmowe do końca 2018 roku. Sprawdź link w opisie YouTube, jak możesz go pobrać, dla tego samego przykładu plus 29 innych przykładów użycia tych elementów.
Cóż, zakończ dzisiejszy dzień. Tablice dynamiczne dają nam inny sposób na walidację zależną. Jeśli nie korzystasz z Office 365 i jeszcze ich nie masz, możesz wrócić do, jak przypuszczam, filmu 1606, który pokazuje stary sposób, aby to zrobić.
Chcę ci podziękować za zatrzymanie się. Do zobaczenia następnym razem z kolejnym netcastem od.
Pobierz plik Excel
Aby pobrać plik Excela: dependent-validation-using-arrays.xlsx
Aby dowiedzieć się więcej o tablicach dynamicznych, zapoznaj się z tematem Tablice dynamiczne programu Excel prosto do punktu.
Myśl dnia Excela
Poprosiłem moich znajomych z programu Excel Master o radę dotyczącą programu Excel. Dzisiejsza myśl do rozważenia:
„Nigdy nie usuwaj pliku Excel bez wcześniejszego utworzenia jego kopii zapasowej”.
Mike Alexander
