Как сделать зависимый выпадающий список в excel?

Вложенные условия

Если для решения задачи недостаточно использования составных условий, то тогда применяют более сложную конструкцию вложенных условий. Смотрите схему.

В данном примере вторая функция ЕСЛИ() является вложенной и записывается на месте действия, которое вызывается при не выполнении условия первой функции. В Excel последних версий допускается делать до 64 вложений.

Задание: С целью уменьшения текучести кадров администрация решила выплачивать надбавку за непрерывный стаж работы на своем предприятии. Рассчитайте надбавку за непрерывный стаж работы в соответствии с таблицей Надбавка.

1. Определите стаж работы сотрудников. Стаж = 2018- Год приема на работу.

2. Используя вложенные функции Если, рассчитайте надбавку для сотрудников. Надбавка (руб.) = Надбавка(%)* Оклад

Решение:

Для проведения расчетов необходимо вписать следующие формулы:

В ячейке D9: =2018-B9
В ячейке E9: =ЕСЛИ(D9>=B$6;C9*C$6;ЕСЛИ(D9>=B$5;C9*C$5;0))

и скопировать по соответствующим столбцам до 19 строки включительно.

Дополнительная информация:

PS: Удивительные факты

Составление функций с несколькими условиями

После того как пользователю удалось задать несколько простых логических функций, можно переходить к более сложным вариантам, когда создаётся функция ЕСЛИ в Excel с несколькими условиями.

Условное форматирование ячеек

Создать логические формулы, в которых должно проверяться не одно требование, а сразу несколько, в электронной таблице также можно. Для этого применяются ещё дополнительные функции, среди которых распространёнными является функция «И» (требует обязательного выполнения заданных критериев), «ИЛИ» (допускает частичное выполнение требований).

Результат выполнения, состоящих из нескольких критериев, может отображаться в числовом виде, текстовом, а также «подсвечиваться» заданными цветовыми оттенками, что позволяет мгновенно выявить значения, которые так важны тому, кто анализирует данные.

Для того чтобы создать электронную таблицу, которая будет не просто выдавать результат, а сопровождать выполнение критериев различными цветовыми оттенками, следует разобраться, как закрасить ячейку в Excel по условию.

Закрасить определённую область таблицы будет несложно, если пользователь полностью понял принцип, как задать выражение, рассматривающее одновременно два требования.

Если бы нужно было просто задать такую формулу с несколькими требованиями, то она выглядела бы так: =ЕСЛИ(И(СУММ(A1:A2)=5;А3=8);ИСТИНА;ЛОЖЬ). Вводя такую формулу в А4, нажав Enter, мгновенно отобразится либо «истина», если условие выполняется, либо «ложь».

Когда же нужно вывести не текстовый ответ, а закрасить ячейку в нужный цвет, необходимо кликнуть по А4, сделав её активной, после чего зайти в «Формат», перейти на строку «Условное форматирование», в открывшемся окне выбрать из раскрывающегося списка строку «формула», ввести в открывшуюся чистую строку созданную ранее формулу в таком виде: =И(СУММ(A1:A2)=5;А3=8). Теперь следует кликнуть по кнопке «Формат», откроется новое окно, где можно выбирать, как должна измениться А4, если будет выполняться требование. На вкладке «Вид» выбирается цвет заливки ячейки при выполнении требования.

Итак, научиться создавать логические формулы, сопровождаемые несколькими условиями, можно, если проявить усидчивость, скопировать готовые примеры, рассмотреть их на новом листе электронной таблице, после чего составить несколько подобных. Освоив логические формулы, электронная таблица Excel понравится ещё больше, поскольку она будет облегчать выполнение многих задач.

Основные операции

Создание формулы в Excel не сопровождается особыми сложностями, если пользователь полноценно освоит некоторые азы работы с табличным редактором. Начинать изучение этого офисного приложения лучше всего с тех вычислительных примеров, которые являются самыми простыми и максимально распространёнными. Это позволит понять суть и важные правила работы с табличным редактором.

Освоив простейшие азы, можно приступать к созданию логических формул.

Ведение простейших подсчётов

Для начала работы следует запустить само офисное приложение

Далее важно определиться, что должно посчитаться автоматически. Чаще всего к работе приступают, когда уже имеется числовая информация, на основе которой приходится осуществлять множественные операции при помощи калькулятора

Теперь эту счётную машинку нужно отложить в сторону и полностью довериться электронной таблице.

Запись любого числового выражения совершается со знака «=». Не выставив его, Excel воспримет введённый запрос, как текстовую запись, отобразит её, но не осуществить подсчёт.

Часто необходимо не просто осуществлять подсчёты, используя данные в таблице, а проводить анализ, сопровождаемый несколькими условиями. Опять-таки всё это проделывать самостоятельно вручную неразумно, когда имеются такие уникальные возможности Excel, способной выполнять любые требования пользователя.

В категории «Логические» самой распространённой является функция ЕСЛИ. Эта функция рассматривает предложенные варианты, а далее определяет, выполняется это условие или нет. Результат функции ЕСЛИ зависит от того, что в итоге желает видеть пользователь. А для того чтобы результат правильно отображался, следует знать, как задать условие в Excel.

Функция ЕСЛИ состоит из трёх аргументов:

Логического (основного) выражения (требование, которое пользователь желает проверить в таблице);
значения, отображающегося, если условие будет полностью соблюдаться;
значения, если заданное требование не выдерживается.

Итак, можно попробовать создать простейшую вычислительную таблицу, взяв за основу пример, где главной будет логическая функция ЕСЛИ.

В частности, в ячейку А1 ввести числовой показатель «5», а в ячейку А2 показатель «8». Если пользователю необходимо сравнить два показателя и выдать автоматический итог произведённых действий, составляется выражение, которое, безусловно, начинается со знака «равно»: =ЕСЛИ(А1>А2; «Больше»; «Меньше»). После введения этой записи в любую свободную ячейку, нажатия Enter, в ней сразу будет демонстрироваться ответ. Если пользователь заменит значения в А1 или А2, то ответ также автоматически перестроится.

В тех случаях, когда важно выявить несовпадение двух показателей, введённых в соседние столбцы, первоначально нужно вникнуть, как в Excel правильно записать условие неравно, после чего создать запрос, используя логические функции. Знак «неравно» в Excel обозначается двумя операторами — «»

Соответственно, нужно записать так: =ЕСЛИ(А1А2; «Значения разные»; «Значения одинаковые»).

Простые примеры выполнения.

Функция ЕСЛИ в Excel позволяет автоматически совершать вычисления согласно заданных условий. Представим, что нам надо выдать премию в 20% за сумму реализации свыше 50000. Не будем уточнять конкретно, 50000 чего – долларов, рублей или тенге, в данном случае это несущественно. Представим, что проверяемая сумма находится в ячейке Е3. Тогда формула будет такой:

=ЕСЛИ(E3>50000;(E3-50000)*20%;0)

Для понимания получившейся надписи достаточно просто ее прочитать. Получаем следующую фразу:

«Если в ячейке Е3 значение выше 50000, тогда надо от этого значения отнять 50000 результат умножить на 20%, иначе записать нуль»

Заметьте, что первый разделитель в виде «;» читается как слово тогда, а второй – как слово иначе.Теперь давайте добавим коэффициент в 20% только за второе полугодие. Даты находятся в столбце “F” начиная с 3 строчки.

Как сделать расширяемый выпадающий список в Excel

После создания раскрывающегося списка вас может прежде всего беспокоить то, что происходит, когда вы добавляете новые элементы в исходные данные. Будет ли выпадающий список обновляться автоматически? Если ваши исходные данные отформатированы как таблица Excel, тогда да, динамический раскрывающийся список, рассмотренный в предыдущих примерах, будет расширяться автоматически без каких-либо усилий с вашей стороны, поскольку таблицы Excel расширяемы по своей природе.

Если по какой-то причине использование таблицы Excel невозможно, вы можете сделать свой раскрывающийся список расширяемым следующим образом:

Чтобы автоматически включать новые данные по мере их добавления в исходный список, добавьте несколько дополнительных ячеек в массивы, на которые ссылаются ваши формулы.
Чтобы исключить пустые ячейки, настройте формулы так, чтобы они игнорировали пустые ячейки, пока они не будут заполнены.

Помня об этих двух моментах, давайте уточним формулы в нашей таблице подготовки данных. Правила проверки данных вообще не требуют каких-либо корректировок.

Формула для основного раскрывающегося списка

С именами фруктов в A3:A15 мы добавляем в массив 5 дополнительных ячеек, чтобы учесть возможные новые записи. Кроме того, мы встраиваем функцию FILTER в UNIQUE, чтобы извлекать уникальные значения без пробелов.

Учитывая вышеизложенное, формула в G3 принимает следующий вид:

=УНИКАЛЬНЫЙ(ФИЛЬТР(A3:A20, A3:A20«»))

Формула зависимого раскрывающегося списка

Формулу в G3 не нужно сильно настраивать — просто дополните массивы еще несколькими ячейками:

=ФИЛЬТР(B3:B20, A3:A20=D3)

Результатом является полностью динамический расширяемый зависимый выпадающий список:

Общее определение и задачи

Пример 2. Использование вложенной функции IF (ЕСЛИ) для проверки условия выражения

Функция может принимать до 64 условий одновременно. Несмотря на то, что создавать длинные вложенные функции нецелесообразно, то в редких случаях вы можете создать формулу, которая множество условий последовательно.

В приведенном ниже примере мы проверяем два условия.

Первое условие проверяет, сумму баллов не меньше ли она чем 35 баллов. Если это ИСТИНА, то функция вернет “Не сдал”;
В случае, если первое условие – ЛОЖЬ, и сумма баллов больше 35, то функция проверяет второе условие. В случае если сумма баллов больше или равна 75. Если это правда, то функция возвращает значение “Отлично”, в других случаях функция возвращает “Сдал”.

Пример 3. Вычисляем сумму комиссии с продаж с помощью функции IF (ЕСЛИ) в Excel

Функция позволяет выполнять вычисления с числами. Хороший пример использования – расчет комиссии продаж для торгового представителя.

В приведенном ниже примере, торговый представитель по продажам:

не получает комиссионных, если объем продаж меньше 50 тыс;
получает комиссию в размере 2%, если продажи между 50-100 тыс
получает 4% комиссионных, если объем продаж превышает 100 тыс.

Рассчитать размер комиссионных для торгового агента можно по следующей формуле:

В формуле, использованной в примере выше, вычисление суммы комиссионных выполняется в самой функции ЕСЛИ . Если объем продаж находится между 50-100K, то формула возвращает B2 * 2%, что составляет 2% комиссии в зависимости от объема продажи.

Пример 4. Используем логические операторы (AND/OR) (И/ИЛИ) в функции IF (ЕСЛИ) в Excel

Вы можете использовать логические операторы (AND/OR) (И/ИЛИ) внутри функции для одновременного тестирования нескольких условий.

Например, предположим, что вы должны выбрать студентов для стипендий, основываясь на оценках и посещаемости. В приведенном ниже примере учащийся имеет право на участие только в том случае, если он набрал более 80 баллов и имеет посещаемость более 80%.

Вы можете использовать функцию AND (И) вместе с функцией IF (ЕСЛИ) , чтобы сначала проверить, выполняются ли оба эти условия или нет. Если условия соблюдены, функция возвращает “Имеет право”, в противном случае она возвращает “Не имеет право”.

Формула для этого расчета:

=IF(AND(B2>80,C2>80%),”Да”,”Нет”) – английская версия

=ЕСЛИ(И(B2>80;C2>80%);”Да”;”Нет”) – русская версия

Пример 5. Преобразуем ошибки в значения “0” с помощью функции IF (ЕСЛИ)

С помощью этой функции вы также можете убирать ячейки содержащие ошибки. Вы можете преобразовать значения ошибок в пробелы или нули или любое другое значение.

Формула для преобразования ошибок в ячейках следующая:

=IF(ISERROR(A1),0,A1) – английская версия

=ЕСЛИ(ЕОШИБКА(A1);0;A1) – русская версия

Формула возвращает “0”, в случае если в ячейке есть ошибка, иначе она возвращает значение ячейки.

ПРИМЕЧАНИЕ. Если вы используете Excel 2007 или версии после него, вы также можете использовать функцию IFERROR для этого.

Точно так же вы можете обрабатывать пустые ячейки. В случае пустых ячеек используйте функцию ISBLANK, на примере ниже:

=IF(ISBLANK(A1),0,A1) – английская версия

=ЕСЛИ(ЕПУСТО(A1);0;A1) – русская версия

Назначение функции ЕСЛИ и ее синтаксис.

Функция Если в Excel предназначена для вывода одного из двух вариантов в зависимости от выполнения заданного условия. Рассмотрим особенности использования данной функции на практике. Применение функции ЕСЛИ позволяет выбрать нужное значение автоматически. Если условие выполняется, то срабатывает первый вариант, а если не выполняется, то второй.В этом материале мы постараемся разобраться с тем, как правильно использовать функцию ЕСЛИ в Excel. Рассмотрим на практике примеры работы данной функции как с одним, так и с несколькими условиями.

Общий синтаксис функции ЕСЛИ, то есть правило написания:

=ЕСЛИ(А;Б;В)

А – условие. Можно применить любое логическое выражение. Чаще всего используют простые варианты, например А2>10. В то же время никто не запрещает использовать и более сложные варианты. Приведу несколько примеров

- МЕСЯЦ(А2)>6 – в ячейке А2 должна содержаться именно дата, причем номер месяца которой более 6. Проще говоря, дата должна быть за 2 полугодие;
- ЕТЕКСТ(А2) – в ячейке А2 должен быть текст;
- ПРАВСИМВ(А2;2)=”KZ” – последние 2 знака в содержимом ячейки А2 должны равняться «А2»;
- И(МЕСЯЦ(А2)>6;МЕСЯЦ(А2)<10);

Самое главное, указать условие, которое может либо выполняться, либо нет. Нельзя указать вариант, для которого точного ответа не существует. Нельзя просто написать А2>. Почему? Да потому что неясно – больше чего должно быть значение ячейки А2.

Б – действие, если условие выполняется. Могут быть указаны:

- Явное значение в виде конкретного текста или числового значения;
- Адрес ячейки с нужным значением;
- Математическая или другая формула;
- Функция.
В – действие, если условие не выполняется. Могут быть указаны:

- Явное значение в виде конкретного текста или числового значения;
- Адрес ячейки с нужным значением;
- Математическая или другая формула;
- Функция.

Как сделать динамический выпадающий список в Excel

В этом примере демонстрируется общий подход к созданию каскадного раскрывающегося списка в Excel с использованием новых функций динамического массива.

Предположим, у вас есть список фруктов в столбце A и экспортеров в столбце B. Дополнительная сложность заключается в том, что названия фруктов не сгруппированы, а разбросаны по всему столбцу. Цель состоит в том, чтобы поместить уникальные названия фруктов в первый раскрывающийся список и, в зависимости от выбора пользователя, показать соответствующих экспортеров во втором раскрывающемся списке.

Чтобы создать динамический зависимый раскрывающийся список в Excel, выполните следующие действия:

1. Получить элементы для основного выпадающего списка

Для начала мы извлечем все различные названия фруктов из столбца A. Это можно сделать с помощью функции UNIQUE в ее простейшей форме — укажите список фруктов в качестве первого аргумента (множество) и опустите оставшиеся необязательные аргументы, так как их значения по умолчанию прекрасно подходят для нас:

=УНИКАЛЬНЫЙ(A3:A15)

Формула переходит в G3, и после нажатия клавиши Enter результаты автоматически переносятся в следующие ячейки.

2. Создайте основной раскрывающийся список

Чтобы сделать основной раскрывающийся список, настройте правило проверки данных Excel следующим образом:

Выберите ячейку, в которой вы хотите отобразить раскрывающийся список (в нашем случае D3).
На Данные вкладка, в Инструменты данных щелкните Проверка данных.
в Проверка данных диалоговом окне выполните следующие действия:
- Под Разрешатьвыберите Список.
- в Источник введите ссылку на вывод диапазона разлива по формуле UNIQUE. Для этого введите хештег сразу после ссылки на ячейку, например: =$G$3#
  Это называется ссылкой на диапазон разлива, и этот синтаксис относится ко всему диапазону, независимо от того, насколько он расширяется или сжимается.
- Нажмите ХОРОШО чтобы закрыть диалог.

Ваш основной раскрывающийся список готов!

3. Получить элементы для зависимого выпадающего списка

Чтобы получить записи для вторичного раскрывающегося меню, мы отфильтруем значения в столбце B на основе значения, выбранного в первом раскрывающемся списке. Это можно сделать с помощью другой функции динамического массива под названием FILTER:

=ФИЛЬТР(B3:B15, A3:A15=D3)

Где B3:B15 — исходные данные для зависимого раскрывающегося списка, A3:A15 — исходные данные для основного раскрывающегося списка, а D3 — основная ячейка раскрывающегося списка.

Чтобы убедиться, что формула работает правильно, вы можете выбрать какое-либо значение в первом раскрывающемся списке и посмотреть результаты, возвращаемые ФИЛЬТРОМ. Идеальный!

4. Сделайте зависимый раскрывающийся список

Чтобы создать второй раскрывающийся список, настройте критерии проверки данных точно так же, как вы делали это для первого раскрывающегося списка на шаге 2. Но на этот раз укажите диапазон разлива, возвращаемый функцией ФИЛЬТР: =$H$3#

Вот и все! Ваш зависимый раскрывающийся список Excel готов к использованию.

Советы и примечания:

Чтобы новые записи автоматически включались в раскрывающийся список, отформатируйте исходные данные как таблицу Excel. Или вы можете включить несколько пустых ячеек в свои формулы, как показано в .
Если ваши исходные данные содержат какие-либо пробелы, вы можете отфильтровать пробелы, используя .
Чтобы отсортировать элементы раскрывающегося списка по алфавиту, оберните свои формулы функцией SORT, как описано в .

Дополнительная информация

В функции IF (ЕСЛИ) может быть протестировано 64 условий за один раз;
Если какой-либо из аргументов функции является массивом – оценивается каждый элемент массива;
Если вы не укажете условие аргумента FALSE (ЛОЖЬ) value_if_false (значение_если_ложь) в функции, т.е. после аргумента value_if_true (значение_если_истина) есть только запятая (точка с запятой), функция вернет значение “0”, если результат вычисления функции будет равен FALSE (ЛОЖЬ). На примере ниже, формула =IF(A1> 20,”Разрешить”) или =ЕСЛИ(A1>20;”Разрешить”) , где value_if_false (значение_если_ложь) не указано, однако аргумент value_if_true (значение_если_истина) по-прежнему следует через запятую. Функция вернет “0” всякий раз, когда проверяемое условие не будет соответствовать условиям TRUE (ИСТИНА).

Если вы не укажете условие аргумента TRUE(ИСТИНА) (value_if_true (значение_если_истина)) в функции, т.е. условие указано только для аргумента value_if_false (значение_если_ложь), то формула вернет значение “0”, если результат вычисления функции будет равен TRUE (ИСТИНА); На примере ниже формула равна = IF (A1>20;«Отказать») или =ЕСЛИ(A1>20;”Отказать”) , где аргумент value_if_true (значение_если_истина) не указан, формула будет возвращать “0” всякий раз, когда условие соответствует TRUE (ИСТИНА).

Особенности создания вложенного «ЕСЛИ».

Учтите следующие особенности:

Общее количество применяемых функций если будет на единицу меньше количества условий. В предыдущем примере у меня было 4 условия. Три – для конкретных городов, четвертое – для всех остальных. Я применил в формуле «Если» 3 раза;
Для каждого условия, кроме последнего, делаем ОТДЕЛЬНУЮ проверку с помощью «ЕСЛИ», не забывая указывать скобку после названия функции;
В самом конце указываем такое значение, которое должно появиться в ситуации, когда не сработало ни одно из указанных условий. Для этого значения указывать «ЕСЛИ» не надо;
В конце указываем столько скобок, сколько функций «ЕСЛИ» задано в строке вложенной функции;

И еще. Внимательно следите, чтобы предыдущее указанное вами условие не включало в себя следующие за ним. В приведенном ниже примере Excel выведет для любого числа меньше 100 значение 10, хотя в формуле вроде бы указано, что при значении меньше 50 надо выводить число 5. Проверка на значение меньше 100 стоит первой, а любое число меньше 50 априори меньше 100!

На этом наш небольшой урок можно закончить. Мы убедились, что использование функции ЕСЛИ в Excel хорошо помогает в вычислениях с условиями для выбора. Предлагаю вам самим додумать варианты применения «ЕСЛИ» для автоматизации вычислений в Excel в зависимости от выполнения нужного условия.

Всем хороших дней и успешной работы!

Усложняем задание.

Прежде всего открою страшную тайну

Для Excel не важно, что вы напишете в условии для функции «ЕСЛИ». Главное, чтобы результатом была ИСТИНА или ЛОЖЬ

Поэтому если в ячейке просто написать слово ИСТИНА и указать на нее, то сработает первый вариант. Если написать ЛОЖЬ, то второй вариант.

Посмотрите, как изменился результат работу при изменении предыдущей функции. Учтите, что все значения, кроме нуля, а так же любой текст рассматриваются в Excel как ИСТИНА.

В частности, можно добавить флажок на лист с вкладки «разработчик», привязать ее к ячейке. Тогда при установке флажка в ячейке будет появляться ИСТИНА, а при снятии – ЛОЖЬ.

Результат работы такой.

Создание вложенной функции “ЕСЛИ”.

Рассмотрим подробнее варианты написания второй и третьей части написания функции. Приведу их еще раз:

- - Явное значение в виде конкретного текста или числового значения;
  - Адрес ячейки с нужным значением;
  - Математическая или другая формула;
  - Функция.

Обратите внимание на четвертый вариант. Можно указать любую функцию, в том числе и саму функцию «ЕСЛИ»

К примеру, проверим ячейку А2. Если в ней дата, то пусть Excel пусть выведет 25% для второго полугодия и 50% для первого полугодия, иначе покажет сообщение об ошибке.

Используем формулу:

=ЕСЛИ(ЯЧЕЙКА(“формат”;A2)=”D1″;ЕСЛИ(МЕСЯЦ(A2)>6;25%;50%);”Введена не дата!”).

Для проверки формата используется функция Ячейка. Она выводит все необходимые сведения. В данном случае проверяется формат, который должен быть в виде даты с номером месяца. Об этом говорит сравнение результата работы функции «ЯЧЕЙКА» со значением «D1».

Более того, при применении функции «Если» в блоке «ЛОЖЬ» можно проверить по очереди несколько условий. Как только одно из условий сработает, выведется результат варианта «ИСТИНА» для него и работа закончится. Дальше условия проверяться уже не будут. Если не выполнится ни одно из указанных условий, выведется результат, указанный в самом конце. В результате в Excel функция ЕСЛИ позволяет использовать несколько условий в заданном порядке.

Рассмотрим такой вариант. Пусть стоимость отправки зависит от города. Если это Алматы, то она равна 2500, если Актау, то 5000, если это Усть-Каменогорск, то 4000. По остальным городам стоимость будет 1500.

Если название города указывается в ячейке В2, то получаем такую формулу.

=ЕСЛИ(B2=”Алматы”;2500;ЕСЛИ(B2=”Актау”;5000;ЕСЛИ(B2=”Усть-Каменогорск”;4000;1500))).

Обратите снимание, что города Туркестан в списке нет, поэтому выведено значение 1500, а вот Актау присутствует, и выведено значение именно для Актау, то есть 5000.

Так образуется вложенный вариант для функции «Если». Каждое новое добавление происходит в области значения при ошибке предыдущей в списке функции.

Как отсортировать выпадающий список по алфавиту

Хотите упорядочить выпадающий список в алфавитном порядке, не обращаясь к исходным данным? В новом динамическом Excel для этого тоже есть специальная функция! В таблице подготовки данных просто оберните функцию SORT вокруг существующих формул.

Правила проверки данных настроены точно так же, как описано в предыдущих примерах.

Для сортировки от А до Я

Поскольку порядок сортировки по возрастанию является параметром по умолчанию, вы можете просто вложить существующие формулы в множество аргумент SORT, опуская все остальные аргументы, которые являются необязательными.

Для основного раскрывающегося списка (формула в G3):

=СОРТИРОВКА(УНИКАЛЬНЫЙ(ФИЛЬТР(A3:A20, A3:A20«»)))

Для зависимого раскрывающегося списка (формула в H3):

=СОРТИРОВАТЬ(ФИЛЬТР(B3:B20, A3:A20=D3))

Сделанный! Оба выпадающих списка сортируются в алфавитном порядке от А до Я.

Сортировать от Я до А

Для сортировки по убыванию нужно задать 3-й аргумент (Порядок сортировки) функции SORT на -1.

Для основного раскрывающегося списка (формула в G3):

=СОРТИРОВАТЬ(УНИКАЛЬНЫЙ(ФИЛЬТР(A3:A20, A3:A20«»)), 1, -1)

Для зависимого раскрывающегося списка (формула в H3):

=СОРТИРОВКА(ФИЛЬТР(B3:B20, A3:A20=D3), 1, -1)

Это отсортирует как данные в таблице подготовки, так и элементы в раскрывающихся списках от Z до A:

Вот как можно создать динамический выпадающий список в Excel с помощью новых функций динамического массива. В отличие от традиционных методов, этот подход отлично работает для записей, состоящих из одного или нескольких слов, и заботится о любых пустых ячейках

Спасибо за внимание и надеемся увидеть вас в нашем блоге на следующей неделе!

Как создать несколько зависимых выпадающих списков в Excel

В предыдущем примере мы сделали выпадающий список в зависимости от другой ячейки. Но что, если вам нужна многоуровневая иерархия, т.е. 3-й выпадающий список в зависимости от 2-го списка или даже 4-й выпадающий список в зависимости от 3-го списка. Это возможно? Да, вы можете настроить любое количество зависимых списков (конечно, разумное количество :).

Для этого примера мы поместили штаты/провинции в столбец C и теперь пытаемся добавить соответствующее раскрывающееся меню в G3:

Чтобы сделать несколько зависимых выпадающих списков в Excel, вам нужно сделать следующее:

1. Настройте первое раскрывающееся меню

Основной выпадающий список создается точно так же, как и в предыдущем примере (см. шаги 1 и 2 выше). Единственным отличием является указание диапазона разлива, которое вы вводите в Источник коробка.

На этот раз формула UNIQUE находится в E8, а основной выпадающий список будет в E3. Итак, вы выбираете E3, нажимаете Проверка данныхи предоставьте эту ссылку: =$E$8#

2. Настройте второй раскрывающийся список

Как вы могли заметить, теперь столбец B содержит несколько вхождений одних и тех же экспортеров. Но вы же хотите, чтобы в выпадающем списке были только уникальные имена, верно? Чтобы исключить все повторяющиеся вхождения, оберните функцию UNIQUE формулой FILTER и введите эту обновленную формулу в F8:

=УНИКАЛЬНЫЙ(ФИЛЬТР(B3:B15, A3:A15=E3))

Где B3:B15 — исходные данные для второго раскрывающегося списка, A3:A15 — исходные данные для первого раскрывающегося списка, а E3 — первая ячейка раскрывающегося списка.

После этого используйте следующую ссылку диапазона разливов для критериев проверки данных: =$F$8#

3. Настройте третий раскрывающийся список

Чтобы собрать элементы для третьего раскрывающегося списка, используйте формулу ФИЛЬТР с несколькими критериями. Первый критерий проверяет весь список фруктов на соответствие значению, выбранному в 1-м раскрывающемся списке (A3:A15=E3), а второй критерий проверяет список экспортеров на соответствие выбранному во 2-м раскрывающемся списке (B3:B15=F3). Полная формула идет к G8:

=ФИЛЬТР(C3:C15, (A3:A15=E3) * (B3:B15=F3))

Если вы собираетесь добавить больше зависимых раскрывающихся списков (4-й, 5-й и т. д.), то, скорее всего, столбец C будет содержать несколько вхождений одного и того же элемента. Чтобы предотвратить попадание дубликатов в подготовительную таблицу и, следовательно, в 3-й выпадающий список, вложите формулу FILTER в функцию UNIQUE, как мы это делали на предыдущем шаге:

=УНИКАЛЬНЫЙ(ФИЛЬТР(C3:C15, (A3:A15=E3) * (B3:B15=F3)))

Последнее, что вам нужно сделать, это создать еще одно правило проверки данных с этим Источник ссылка: =$G$8#

Ваш множественный зависимый выпадающий список готов к работе!

Кончик. Аналогичным образом вы можете получить элементы для последующих раскрывающихся списков. Предполагая, что столбец D содержит исходные данные для вашего 4-го раскрывающегося списка, вы можете ввести следующую формулу в H8, чтобы получить соответствующие элементы:

=УНИКАЛЬНЫЙ(ФИЛЬТР(D3:D15, (A3:A15=E3) * (B3:B15=F3) * (C3:C15=G3)))