С тех пор, как в 1997 году в Excel были добавлены раскрывающиеся меню «Проверка данных», люди пытались разработать способ изменения второго раскрывающегося меню на основе выбора в первом раскрывающемся списке.
Например, если вы выберете «Фрукты» в A2, в раскрывающемся списке A4 будет предложено яблоко, банан, вишня. Но если вы выберете «Травы» из A2, в списке A4 появятся анис, базилик, корица. За эти годы было найдено множество решений. Я как минимум дважды освещал это в подкасте:
- Классический метод использовал много именованных диапазонов, как показано в серии 383.
- Другой метод использовал формулы СМЕЩЕНИЯ в Эпизоде 1606.
С выпуском новых формул динамических массивов в общедоступной предварительной версии новая функция FILTER предоставит нам еще один способ выполнения зависимой проверки.
Скажите, что это ваша база товаров:
Используйте формулу =SORT(UNIQUE(B4:B23))в D4, чтобы получить уникальный список классификаций. Это совершенно новый тип формулы. Одна формула в D4 возвращает множество ответов, которые распространяются на множество ячеек. Для обозначения диапазона Spiller вы должны использовать =D4#вместо =D4.
Выберите ячейку, в которой будет отображаться меню проверки данных. Выберите Alt + DL, чтобы открыть проверку данных. Измените Разрешить на «Список». Укажите =D4#как источник списка. Обратите внимание, что хэштег (#) - это Spiller - это означает, что вы имеете в виду весь диапазон Spiller.
Планируется, что кто-то выберет классификацию из первого раскрывающегося меню. Затем формула =FILTER(A4:A23,B4:B23=H3,"Choose Class First")в E4 вернет все продукты в этой категории. Обратите внимание, что в качестве третьего необязательного аргумента следует использовать «Сначала выберите класс». Это предотвратит ошибку #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». Это … он будет бесплатным до конца 2018 года. Проверьте ссылку внизу в описании YouTube, как вы можете его скачать, для этого самого примера плюс 29 других примеров того, как использовать эти элементы.
Что ж, подведем итоги на сегодня. Динамические массивы дают нам еще один способ выполнения зависимой проверки. Если вы не пользуетесь Office 365 и у вас их еще нет, не стесняйтесь вернуться, я полагаю, к видео 1606, в котором показан старый способ сделать это.
Я хочу поблагодарить вас за то, что заглянули. Увидимся в следующий раз на другой сетевой трансляции от.
Скачать файл Excel
Чтобы скачать файл Excel: зависимая-валидация-с использованием-массивов.xlsx
Чтобы узнать больше о динамических массивах, прочтите статью «Динамические массивы Excel прямо к делу».
Идея дня в Excel
Я попросил совета у моих друзей-мастеров Excel по поводу Excel. Сегодняшняя мысль задуматься:
«Никогда не удаляйте файл Excel без его резервной копии».
Майк Александр
