Наша студентка курса "Excel бизнес-анализ и прогнозирование"   написала в чат поддержки и попросила помочь ей разобраться с выпадающим списком. Эта сложность преследует многих. Давайте разберёмся вмес

Классический выпадающий список в ячейке Excel создается с помощью команды главного меню Данные – блок «Работа с данными» Проверка (Data - Validation). С помощью этого инструмента пользователь может сэкономить время на заполнение повторяющих значений выбирая уже готовый аргумент из подготовленного списка. Выпадающий список может быть неплохим помощником для фильтрации данных, если он состоит из небольшого количества позиций, каждая из которых представляет из себя слово или сочетание из двух слов. В случае если список большой или его элементами есть предложение или словосочетание из более чем 2 слов пользователь сталкивается с проблемой – выпадающий список в ячейке Microsoft Excel не дает возможности фильтровать по первым символам, то есть результатом отбора нельзя получить те строки, которые содержат заданные первые символы элемента выпадающего списка. Давайте попробуем обойти этот недостаток. В качестве примера возьмём перечень стран со столицами и континентам, на которых они находятся. Нашей задачей будет сделать так, чтобы в выпадающий список попали только те позиции, которые будут содержать в себе значение, указанное в ячейке E2:

Алгоритм действий:

1. Для начала давайте создадим выпадающий список, который будет отображаться в ячейке E2. Для этого в нужно выбрать в главному меню Microsoft Excel команду «Данные» - «Проверка» (Data - Validation) и в открывшемся окне на закладке «Параметры» выбрать тип данных – «Список» и в качестве «Источника» указать диапазон, который будет отображаться:

На закладке «Сообщение об ошибке» исключаем команду «Выводит сообщение об ошибках»:

2. Теперь давайте зададим в ячейку E2 значение, по которому будем отбирать данные из исходной таблицы. Для того, чтобы определить какие строки из исходной таблицы содержат значение указано в ячейке E2 используем функцию ПОИСК (SEARCH), которая ищет заданную подстроку в тексте и как результат выдает либо порядковый номер символа, где он был обнаружен, либо ошибку, если его там нет:

3. Теперь давайте используем результат предыдущего действия в качестве опции проверки ЕЧИСЛО (ISNUMBER), которая в ячейках, содержащих числовые значения выдает ИСТИНУ (TRUE), а в тех, что содержат ошибки или отличающиеся от цифр значения - ЛОЖЬ (FALSE):

С полученными данными необходимо осуществить еще такие действия: значение «ЛОЖЬ (FALSE)» превратим в 0, а вместо «ИСТИНА (TRUE)» укажем последовательность чисел (последнее значение последовательности укажет на количество позиций со значением «ИСТИНА (TRUE)»). Для этого нам помогут функции ЕСЛИ (IF) и МАКС (MAX). Наша конструкция из формул будет иметь следующий вид:

ЕСЛИ (результат функции ЕЧИСЛО (ISNUMBER)); - условие

          МАХ () +1; - значение если истина

1. – значение если лож

Таким образом когда условие функции исполняться, то она выводит максимальное значение из всех вышестоящих чисел + 1, в обратном случае то выводит 0:

5. Теперь создадим поле куда должны попасть отобранные позиции исходной таблицы (в нашем случае это будет таблица из двух колонок - порядковый номер значения и само значение) и с помощью функции ВПР (VLOOKUP) подтянем нужны нам поля.

  После этого можно протестировать как работает наша формула для других критериях отбора, вводя в ячейку E2 разные слова чтобы увидеть будут ли меняться значения в поле отбора:

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

В главному меню Microsoft Excel на вкладке «Формулы» команду «Диспетчер имен» - «Создать» (Formulas - Name Manager - Create) и в окне, что появиться указываем имя диапазона и ссылку на ячейки, из которых диапазон должен состоять:

Так как область отбора будет меняться в зависимости от значения ячейки E2, то для того, чтобы задать диапазон используем функцию СМЕЩ (OFFSET), которая имеет такие аргументы:

     СМЕЩ (OFFSET):

1. ячейка от которой будем делаться отсчет (в нашем случае используем ячейку H3 – первое значение полученного отбора);

2. количество ячеек, на которое нам нужно сдвинуться вниз (у нас это 0, так как сдвига вниз быть не должно);

3. количество ячеек, на которое нам нужно сдвинуться вправо (у нас это 0, так как сдвига вправо быть не должно);

4. высота (равно максимальному значению полученного в результате предыдущего использования функции ЕСЛИ ((IF));

5. ширина (у нас это 1 столбец).

6. И финальным этапом будет создание выпадающего списка.  Выделим ячейку E2 и проделаем действия указание в пункте 1 нашего алгоритма, но в поле «Источник» Укажем название создано нами именованного диапазона со знаком равно перед ним:

Все, задание выполнено!

       В связи с тем, что в арсенале Microsoft Excel появилась новая функция ФИЛЬТР (FILTER), которая может отобрать из нашей исходной таблицы только те сроки, которые содержат критерий из ячейки E2. Попросту говоря ФИЛЬТР (FILTER) заменяет пункты 2-5 описаны выше. При создании именуемого диапазона нам нужно будет в поле «Источник» указать ссылку на первый элемент отбора добавив к нему знак # чтобы захватить весь отобранный массив.

Это один из вариантов оптимизации своей работы в Excel. Каждый такой маленький шаг к автоматизации своей работы избавляет от рутины и делает работу проще. Глубоко изучить Excel поможет 27 часовой курс "Excel бизнес-анализ и прогнозирование"  

Узнайте больше про полную программу "EXCEL: бизнес-анализ и прогнозирование"