З тих пір, як у 1997 році в Excel були додані випадаючі меню Перевірка даних, люди намагалися розробити спосіб змінити друге спадне меню на основі вибору в першому спадному меню.
Наприклад, якщо ви виберете Фрукти у форматі A2, у спадному меню A4 буде запропоновано яблуко, банан, вишню. Але якщо ви виберете Трави з А2, список у А4 запропонує Аніс, Василь, Корицю. За ці роки було багато рішень. Я висвітлював це принаймні двічі в Подкасті:
- Класичний метод використовував багато іменованих діапазонів, як показано в епізоді 383.
- Інший метод використовував формули OFFSET в епізоді 1606.
З випуском нових формул динамічного масиву в загальнодоступному попередньому перегляді нова функція ФІЛЬТР дасть нам ще один спосіб зробити незалежну перевірку.
Скажіть, що це ваша база даних про товари:
Використовуйте формулу =SORT(UNIQUE(B4:B23))в D4, щоб отримати унікальний список класифікацій. Це абсолютно новий тип суміші. Одна формула в D4 повертає багато відповідей, які розлиються на багато клітин. Для посилання на діапазон Spiller ви б використовували =D4#замість =D4.
Виділіть клітинку, де буде розташовано меню Перевірка даних. Виберіть Alt + DL, щоб відкрити перевірку даних. Змінити Дозволити на "Список". Вкажіть =D4#як джерело списку. Зверніть увагу, що Хештег (#) - це Spiller - це означає, що ви маєте на увазі весь діапазон Spiller.
План полягає в тому, що хтось вибере класифікацію з першого спадного меню. Тоді формула =FILTER(A4:A23,B4:B23=H3,"Choose Class First")в Е4 поверне всі товари цієї категорії. Зверніть увагу, що використання "Виберіть клас першим" як необов’язковий третій аргумент. Це запобіжить #VALUE! помилка при появі.
У списку може бути різна кількість елементів залежно від обраної категорії. Налаштування перевірки даних, вказуючи на =E4#, розширюватиметься або стискатиметься з довжиною списку.
Переглянути відео
Стенограма відео
Дізнайтеся Excel From, Podcast Episode 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. Це … це буде безкоштовно до кінця 2018 року. Перевірте посилання там, в описі YouTube, як ви можете його завантажити, саме для цього прикладу та ще 29 прикладів того, як використовувати ці предмети.
Ну, заверніть на сьогодні. Динамічні масиви дають нам ще один спосіб зробити залежну перевірку. Якщо ви ще не працюєте в Office 365 і у вас їх ще немає, сміливо повертайтеся до відео 1606, яке показує старий спосіб зробити це.
Я хочу подякувати вам, що завітали. Ми побачимось наступного разу для чергової трансляції від.
Завантажте файл Excel
Щоб завантажити файл excel: depend-validation-using-arrays.xlsx
Щоб дізнатись більше про динамічні масиви, перегляньте динамічні масиви Excel прямо до точки.
Думка дня в Excel
Я попросив своїх друзів Excel Master порадити їх щодо Excel. Сьогоднішня думка поміркувати:
"Ніколи не видаляйте файл Excel, не створивши попередню копію."
Майк Олександр
