В предыдущих заметках я подробно разобрал два способа создания выпадающих списков с возможностью поиска.
Первый вариант хоть и максимально упрощает задачу, но недоступен большинству пользователей Эксель, так как использует динамические массивы и функции по работе с ними, которые есть только в Microsoft Office 365.
Второй вариант более универсален и в нем используются стандартные функции Excel, но и он имеет существенный недостаток - с его помощью нельзя сделать отдельный выпадающий список с поиском для каждой ячейки таблицы. Однако обычно желание создать выпадающий список с возможностью поиска возникает как раз при заполнении таблиц, то есть когда подставляются данные из некоторого значительного массива и есть необходимость упростить ввод данных, а также ограничить возможность ошибки из-за неправильного их заполнения.
В такой ситуации можно найти компромиссное решение - само поисковое поле вынесем за пределы таблицы, но выпадающее меню, содержимое которого этим полем будет определяться, по-прежнему будет доступно в каждой ячейке таблицы. Кроме этого, данные в выпадающем списке будут отсортированы по алфавиту.
Итак, давайте решим эту задачу.
Выпадающий список с поиском
Есть некоторая табличная база данных товаров и необходимо на ее основе сформировать другую таблицу, например, счет для оплаты. То есть нужно в такую таблицу подтянуть данные из основной таблицы и посчитать стоимость заказа.
Действуем также, как и в предыдущем примере - создадим вспомогательный столбец в самом начале исходной таблицы и с помощью функции ПОИСК будем искать значение, которое вводится в поисковом поле. Пока для удобства это поле размещу на этом же листе. Получим либо числовые значения, указывающие на позицию искомых символов в тексте конкретной ячейки, либо ошибку, говорящую нам о том, что искомая комбинация символов не была найдена.
Далее с помощью функции ЕЧИСЛО переконвертируем результат в формат ИСТИНА/ЛОЖЬ, а затем с помощью функции ЕСЛИ расставим значения по порядку. То есть если полученное в результате значение ИСТИНА, то к максимальному значению из столбца прибавим единицу. Иначе выводим ноль. Таким образом мы сформируем нумерацию строк для ячеек, удовлетворяющих условиям поиска.
Осталось лишь перенести эти данные в новый столбец. Для этого создадим еще один вспомогательный столбец D с нумерацией. Будем основываться на значениях этого столбца и с помощью функции ВПР подтянем данные по порядковому номеру из основной таблицы.
Получим список значений, которые будем использовать в выпадающем списке.
Так как размер списка будет зависеть от введенных в поисковое поле символов, то мы не можем указать его четкие границы. Поэтому воспользуемся небольшой хитростью - создадим именованный диапазон (товары) и с помощью функции СМЕЩ определим границы этого списка.
Первая ячейка диапазона нам всегда известна. Смещение по вертикали и горизонтали отсутствуют, а вот по высоте диапазон будет равен максимальному значению из первого вспомогательного столбца. Ширина диапазона равна единице, то есть мы хотим получить лишь один столбец со значениями.
Подробно о функции СМЕЩ и ее аргументах смотрите в предыдущей заметке.
Все готово для создания выпадающего списка. На новом листе сформируем поисковое поле. Изменим первоначальную формулу, подставив в нее ссылку на эту ячейку.
Теперь создадим таблицу, в которой нужно применять выпадающий список для заполнения данных.
Выделим столбец Товар и создадим выпадающий список. В качестве источника указываем вновь созданные именованный диапазон и не забываем отключить сообщение об ошибке на соответствующей вкладке.
Все готово.
При заполнении товара мы вводим в поисковое поле необходимые символы и получаем в выпадающем списке только соответствующие запросу значения.
В данный момент выпадающий список будет одинаков для всех ячеек таблицы. Перейдя к следующей строке можем изменить поисковый запрос и введем другой интересующий нас товар.
С помощью функции ВПР подтянем цену, а затем укажем количество и рассчитаем сумму.
Пока в выпадающем списке товары идут в той последовательности, в которой они были в основной таблице.
Я уже записывал видео по сортировке данных с помощью формул и тогда оно не нашло своего зрителя, так как многие усомнились в рациональности таких действий.
Сейчас как раз созрела та ситуация, когда сортировка с помощью формул будет более чем уместным и единственно возможным решением.
Как я упомянул, подробно этот способ я изложил в отдельном видео, поэтому сейчас не буду углубляться в детали, а с минимальными пояснениями реализую сортировку значений.
Нам понадобится еще один вспомогательный столбец. В нем с помощью функции СЧËТЕСЛИ мы рассчитаем порядковые номера для каждого слова в будущем отсортированном списке.
Эта формула будет замечательно работать в том случае, если в диапазоне не будет повторяющихся значений, иначе нужно будет формулу немного доработать.
В итоге мы получим две последовательности цифр - для наименований товаров и для ошибочных значений.
Второй список для нас неважен, так как он находится ниже, а функции просмотра выдают первое найденное значение. То есть до него функция просто не дойдет.
В данной ситуации нам нужно производить поиск влево от столбца, а значит функцию ВПР здесь применить не получится, но можно воспользоваться ее полноценной заменой - связкой функций ИНДЕКС и ПОИСКПОЗ. О них есть подробная заметка на канале, поэтому вновь не буду подробно останавливаться на этом моменте.
Выбираем функцию ИНДЕКС и в качестве массива данных указываем ранее созданный именованный диапазон с товарами. Ну а номер строки будем определяться функцией ПОИСКПОЗ. Искомое значение берем из ранее нами созданного вспомогательного диапазона с нумерацией, ведь нам просто нужно расставить названия товаров по порядку, опираясь на только что рассчитанные порядковые номера. Указываем этот диапазон и фиксируем его с помощью клавиши F4. Тип сопоставления - точное совпадение, то есть ноль.
Мы получим отсортированный список.
Ниже него идут ошибки, которые мы исключим из нашего диапазона. Для этого создадим еще один именованный диапазон и точно также как и раньше с помощью функции СМЕЩ определим его границы.
Осталось лишь отредактировать выпадающий список в таблице и поменять в источниках именованный диапазон.
Теперь все работает как надо и данные в выпадающем списке отсортированы по алфавиту.
Ссылки на мои ресурсы по Excel
★ YouTube-канал по Excel и Word
★ Телеграм
