Поиск ТЕКСТовых значений в EXCEL с выводом их в отдельный перечень. Часть1. Обыденный поиск

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

Пусть Начальный перечень значений (к примеру, список инструментов) находится в спектре A10:A19 (см. Файл примера ).

Задачка

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

А. Отыскать значения, которые содержат аспект

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

Выведем в отдельный спектр все значения Начального перечня, в которых содержится текст-критерий (к примеру, слово дрель ). Аспект вводится в ячейку С6 .

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

=ИНДЕКС(Перечень; НАИМЕНЬШИЙ( ЕСЛИ(ЕСЛИОШИБКА(ПОИСК($C$6;Перечень);0)>0;СТРОКА(Перечень)-СТРОКА($A$9);30); СТРОКА(ДВССЫЛ(«A1:A»&ЧСТРОК(Перечень)))))

Метод работы формулы последующий (для просмотра промежных шагов работы формулы воспользуйтесь кнопкой F9 ):

Функция ПОИСК() , перебирая элементы начального перечня, описывает, содержится ли в нем значение-критерий. Если значение не содержится, то ворачивается ошибка #ЗНАЧ! В неприятном случае ворачивается числовое значение, соответственное номеру исходной позиции вхождения аспекта в значение из перечня (тут нам не важен номер позиции, принципиально, что это число);
Функция ЕСЛИОШИБКА() употребляется для угнетения ошибки #ЗНАЧ! заменяя ее на число 0;
Функция ЕСЛИ() подменяет числовые значения, возвращенные функцией ПОИСК() , на номер позиции значения в списке. Если значение =0 (соответствует ошибке #ЗНАЧ!), то ворачивается число 30. В принципе, заместо 30 можно указать хоть какое число, которое больше номера крайней заполненной позиции Начального перечня (это необходимо для правильной сортировки функцией НАИМЕНЬШИЙ() );
Функция НАИМЕНЬШИЙ() сортирует массив номеров строк по возрастанию;
Функция ДВССЫЛ() возвращает массив поочередных чисел ;
Функция ИНДЕКС() возвращает текстовые значения из Начального перечня, из строк, номера которых были получены на прошлом шаге.

В предельном случае м.б. найдено столько же значений, сколько содержится в начальном списке (когда все значения удовлетворяют аспекту). Потому формулу массива необходимо распространять на спектр той же размерности, что и начальный перечень. Вышеуказанная формула массива будет возвращать несколько значений , потому перед вводом формулы необходимо выделить сходу весь спектр, т.е. ячейки С10:С19 , ввести формулу в Строке формул и надавить CRTL+SHIFT+ENTER .

Для скрытия ошибок #ССЫЛКА!, возвращаемой формулой массива, к спектру С10:С19 использовано правило Условного форматирования .

Б. Отыскать значения, которые совпадают с аспектом (четкое совпадение)

В этом случае будут выведены все значения, которые совпадают с аспектом (без учета РЕгиСТра ). Аспект вводится в ячейку E 6 .

Для сотворения перечня, содержащего отысканные значения, воспользуемся формулой массива : =ИНДЕКС(Перечень; НАИМЕНЬШИЙ( ЕСЛИ($E$6=Перечень;СТРОКА(Перечень)-СТРОКА($A$9);30); СТРОКА(ДВССЫЛ(«A1:A»&ЧСТРОК(Перечень)))))

B. Отыскать значения, которые начинаются с аспекта

В этом случае будут выведены все значения, которые начинаются либо совпадают с аспектом. Аспект вводится в ячейку G 6 .

Для сотворения перечня, содержащего отысканные значения, воспользуемся формулой массива : = ИНДЕКС(Перечень;НАИМЕНЬШИЙ( ЕСЛИ(ЕСЛИОШИБКА(ПОИСК($G$6;Перечень);0)=1;СТРОКА(Перечень)-СТРОКА($A$9);30); СТРОКА(ДВССЫЛ(«A1:A»&ЧСТРОК(Перечень)))))

Г. Отыскать значения, которые завершаются на аспект

В этом случае будут выведены все значения, которые завершаются либо совпадают с аспектом. Аспект вводится в ячейку I 6 .

Для сотворения перечня, содержащего отысканные значения, воспользуемся формулой массива : =ИНДЕКС(Перечень;НАИМЕНЬШИЙ( ЕСЛИ($I$6=ПРАВСИМВ(Перечень;ДЛСТР($I$6));СТРОКА(Перечень)-СТРОКА($A$9);30); СТРОКА(ДВССЫЛ(«A1:A»&ЧСТРОК(Перечень)))))

СОВЕТ: О поиске текстовых значений с внедрением подстановочных символов читайте в статье Поиск текстовых значений в перечнях. Часть2. Подстановочные знаки . В статье Выделение ячеек c ТЕКСТом с применением Условного форматирования приведено решение аналогичной задачки с внедрением Условного форматирования .

Интересно почитать: Генератор паролей в excel

Выпадающий перечень с резвым поиском

Традиционный выпадающий перечень в ячейке листа Excel, изготовленный через Данные — Проверка (Data — Validation) — обычная и комфортная штука, которую раз в день используют весьма почти все юзеры. Но, у этого перечня есть один очень суровый недочет — в нём нет резвого поиска по первым символам, т.е. фильтрации (отбора) лишь тех значений, куда введённый фрагмент заходит как подстрока. Это серьезно усугубляет удобство использования даже если в списке всего пара-тройка 10-ов позиций, а при нескольких сотках убивает юзабилити напрочь.

Давайте разглядим как всё же воплотить схожий трюк. В качестве подопытного зайчика возьмём перечень 250 наилучших кинофильмов по версии IMDb:

Выпаюащий список с поиском

Конечная цель — сделать выпадающий перечень (ячейка G3), в котором можно будет стремительно отыскивать нужные киноленты, введя лишь жанр, год либо фрагмент наименования, к примеру «гамп».

Шаг 1. Определяем, кто нам нужен

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

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

Сейчас создадим так, чтоб ЛОЖЬ перевоплотился в 0, а заместо ИСТИНА в столбце возникли поочередно растущие индексы-числа 1,2,3. и т.д. Это можно создать при помощи прибавления к нашей же формуле ещё парочки функций:

Тут функция ЕСЛИ (IF) инспектирует что мы имеем (ИСТИНУ либо ЛОЖЬ), и

если была ИСТИНА, то выводит наибольшее значение из всех вышестоящих чисел + 1
если была ЛОЖЬ, то выводит 0

Шаг 2. Отбираем в отдельный перечень

Далее — проще. Сейчас очевидной функцией ВПР (VLOOKUP) просто выведём все отысканные наименования (я добавил столбец с порядковыми номерами для удобства):

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

Шаг 3. Создаем именованный спектр

Сейчас сделаем именованный спектр, который будет ссылаться на отобранные киноленты. Для этого выбрем на вкладке Формулы команды Диспетчер имен — Сделать (Formulas — Name Manager — Create) :

Имя спектра быть может хоть каким (к примеру, Киноленты), а самое основное — это функция СМЕЩ (OFFSET) , которая и делает всю работу. Напомню её синтаксис, если вы подзабыли:

=СМЕЩ( начальная_ячейка ; сдвиг_вниз ; сдвиг_вправо ; высота ; ширина )

В качестве исходной ячейки задаём первую ячейку перечня отобранных частей (E2).
Сдвиги вниз и на право у нас отсутствуют, т.е. равны нулю.
Высота спектра у нас соответствует наибольшему значению индекса из столбца А.
Ширина спектра — 1 столбец.

Осталось создать выпадающий перечень.

Шаг 4. Создаем выпадающий перечень

Выделим жёлтую ячейку (G2) и выберем на вкладке Данные команду Проверка данных (Data — Validation) . В открывшемся окне выбрем Перечень (List) в поле Тип данных (Allow) , а в качестве источника введем имя нашего сделанного спектра со знаком равно перед ним:

Интересно почитать: Символ абзаца в excel

Чтоб Excel не бранился при вводе на некорректное совпадение наших фраз с начальным перечнем, на вкладке Сообщение о ошибке (Error Alert) в этом окне необходимо выключить флаг Выводить сообщение о ошибке (Show error alert) :

Вот и всё. Можно нажимать на ОК и услаждаться результатом:

Для пущего удобства при вводе с клавиатуры можно употреблять Ctrl + Enter заместо Enter опосля ввода текста (так активная ячейка не уходит вниз) и сочетание кнопок Alt + стрелка вниз , чтоб развернуть выпадающий перечень без мыши.

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

Всё, что мы делали на Шагах 1-3 заменяется одной(!) формулой, где новенькая функция ФИЛЬТР (FILTER) отбирает из начального спектра A2:A251 лишь те киноленты, которые содержат заданную подстроку.

А далее остается при разработке выпадающего перечня указать в качестве источника первую ячейку спектра отобранных кинофильмов (C2) и добавить к ней символ #, чтоб получить ссылку на весь динамический массив:

И всё. Никаких именованных диапазонов и неспешных СМЕЩ, никаких танцев с доп столбцами и формулами. Песня!

Как на языке M в Power Query находить необходимое нам значение в начальном списке с возможностью опции собственных правил поиска (сопоставления). Функция List.Contains

Приветствую Вас, дорогие друзья, с Вами Будуев Антон. В данной статье мы побеседуем про функцию List.Contains, входящую в состав редактора запросов Power Query (языка M) для Excel (Эксель) и Power BI.

Данная функция показывает нам, содержит ли проверяемый Перечень необходимое для нас значение. Проверка делается с возможностью задавать правила данной самой проверки. По другому говоря, при помощи List.Contains мы можем в Списке производить поиск подходящего для нас значения с учетом наших правил поиска. В случае удачного поиска функция возвращает TRUE, если значение не было найдено, то FALSE.

Синтаксис функции List.Contains

<Список>— хоть какой перечень, элементы которого методом поочередного перебора необходимо проверить на соответствие ИскомомуЗначению;
ИскомоеЗначение — то значение, которое нам нужно отыскать посреди значений в Списке;
Функция(x, y) — необязательный параметр «equationCriteria». Пользовательская, или иная функция из состава языка M (Power Query), которая задает порядок проверки частей Перечня на соответствие ИскомомуЗначению. Так именуемый, параметр «equationCriteria». В пользовательскую функцию необходимо передать два параметра «x» и «y», где List.Contains в «x» будет передавать поочередно любой элемент Перечня, а в «y» будет передано ИскомоеЗначение.

Разбор работы функции List.Contains на практике без третьего параметра «equationCriteria»

Давайте попробуем c помощью List.Contains отыскать значение «3» в списке из цифр от 1 до 10. Формула в Power Query будет обычный:

Данная формула вернет значение TRUE, потому что вправду значение «3» имеется посреди данного перечня:

Сейчас попробуем отыскать посреди этого же перечня значение «11»:

Естественно, результатом выполнения данной функции будет FALSE, потому что такового значения в начальном Списке нет:

Отлично, сейчас давайте найдем значение «Хлеб» посреди Перечня товаров:

Результатом, естественно, будет TRUE:

А сейчас давайте снова найдем значение «хлеб», но уже в малом регистре:

И вот здесь уже возникает неувязка. Как бы, разыскиваемое значение есть в начальном Списке, но функция List.Contains языка M в Power Query возвращает FALSE, другими словами, она не отыскала его:

А все дело в том, что язык M в Power Query чувствителен к регистру и чтоб все-же отыскать значение в Списке, и не зависеть от задачи регистра, мы как раз таки и можем пользоваться третьим параметром в List.Contains «equationCriteria» и прописать собственное правило поиска искомого значения.

Разбор работы функции List.Contains на практике с учетом третьего параметра «equationCriteria»

Итак, как я уже писал выше (в синтаксисе List.Contains), в 3-ем параметре мы можем прописать свою пользовательскую либо же какую-то другую функцию Power Query для задания правил поиска. В нашем рассматриваемом примере появилась неувязка учета регистра букв при поиске.

Данную делему мы просто можем решить с помощью собственной пользовательской функции, где приведем все значения из начального Перечня, также ИскомоеЗначение к малому регистру, и лишь опосля этого функция List.Contains будет ассоциировать данное ИскомоеЗначение в малом регистре со Перечнем, в котором также весь текст будет приведет к малому регистру.

Код будет последующий:

В данной формуле мы прописали свою пользовательскую функцию (x, y) => Text.Lower(x) = Text.Lower(y), в которую передали два параметра «x» и «y», где List.Contains в «x» будет передавать поочередно любой элемент Перечня, а в «y» будет передано ИскомоеЗначение.

Ну и сама наша функция ассоциирует «x» и «y» меж собой опосля того, как эти значения будут приведены к малому регистру с помощью функции языка M в Power Query — Text.Lower.

В текущей редакции нашей формулы результатом уже будет TRUE, другими словами, List.Contains с помощью наших собственных правил сопоставления, отыщет значение «хлеб» в начальном списке, невзирая на то, что регистр искомого значения и значений в Списке различается:

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

На этом, с разбором функции List.Contains, входящую в состав редактора запросов Power Query (языка M) для Excel (Эксель) и Power BI, в данной статье все. Фурроров Для вас, друзья!

Пожалуйста, оцените статью:

Фурроров Для вас, друзья!
С почтением, Будуев Антон.
Проект «BI — это просто»

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

Также, делитесь данной статьей со своими знакомыми в соц сетях, может быть, этот материал кому-то будет весьма полезен.

Приглянулся материал статьи?
Добавьте эту статью в закладки Вашего браузера, чтоб возвратиться к ней снова. Для этого, прямо на данный момент нажмите на клавиатуре комбинацию кнопок Ctrl+D