Функция НАЙТИ в Excel. Как использовать?
Функция НАЙТИ (FIND) в Excel используется для поиска текстового значения внутри строчки с текстом и указать порядковый номер буквы с которого начинается искомое слово в найденной строке.
Возвращает числовое значение, обозначающее стартовую позицию текстовой строчки внутри другой текстовой строчки.
Функция НАЙТИ в Excel. Как использовать? ЭКСЕЛЬ ХАК
- Если стартовое число не указано, то функция начинает поиск искомого текста с начала строки;
- Функция НАЙТИ чувствительна к регистру. Если вы хотите сделать поиск без учета регистра, используйте функцию SEARCH в Excel;
- Функция не учитывает подстановочные знаки при поиске. Если вы хотите использовать подстановочные знаки для поиска, используйте функцию SEARCH в Excel;
- Функция каждый раз возвращает ошибку, когда не находит искомый текст в заданной строке.
На примере выше мы ищем слово «Доброе» в словосочетании «Доброе Доброе утро». Когда мы начинаем поиск слова «Доброе» с начала текстовой строки, то функция выдает число «1», так как первое слово «Доброе» начинается с первой буквы в словосочетании «Доброе Доброе утро».
Первый вариант выпадающего списка с поиском (динамические массивы)
Итак, есть исходная таблица в два столбца. Первый содержит список имен, а второй телефоны. В еще одну из ячеек первой строки введем набор символов, который мы хотим найти, то есть смоделируем поисковое поле (введем » сми «).
Ниже этой ячейки мы должны сформировать список только из тех данных исходной таблицы, которые содержат введенные выше символы.
Поможет в этом функция ПОИСК ( SEARCH ), которая ищет заданный набор символов и выдает либо порядковый номер первого символа в тексте, либо ошибку, если символы обнаружены не были.
В качестве искомого текста указываем ссылку на ячейку выше, а искать мы будем в первой ячейке столбца с именами. Последний аргумент функции необязателен и в нашем случае указывать его не будем.
Цифра «1» указывает нам позицию введенных символов в выбранном нами тексте (строка начинается с этих символов).
Если введем « але », то получим цифру 9, которая указывает на девятую позицию. Именно девятым будет первый символ искомого текста в этом имени (пробел также учитывается).
Если же введем « ива », то получим ошибку ЗНАЧ! , которая указывает на то, что такой комбинации символов в тексте не найдено.
Динамические массивы в Excel появились несколько лет назад, но до сих пор они доступны лишь пользователям Microsoft Office 365 и в онлайн-версии офисного пакета. В других версиях и редакциях Excel динамических массивов пока нет и вряд ли они там появится. Скорее всего в коробочной редакции Excel их стоит ждать лишь в следующей версии офисного пакета в 22-ом году.
Итак, мы получили динамический массив. Если в его ячейках отражается любая цифра, то это означает, что искомая комбинация символов была найдена, а ошибка свидетельствует об обратном.
Фактически нас интересуют только ячейки с числами, при этом сами значения неважны. Поэтому можем переконвертировать полученные значения в формат ИСТИНА/ЛОЖЬ .
Для этого воспользуемся функцией ЕЧИСЛО ( ISNUMBER ) и обернем ей созданную ранее формулу. В результате если в ячейке будет находиться число, то функция выдаст ИСТИНУ , иначе ЛОЖЬ .
Осталось лишь отфильтровать значения, ведь нас интересует только ИСТИНА . Воспользуемся функцией ФИЛЬТР ( FILTER ), которая позволяет работать с динамическими массивами.
Обернем созданную ранее формулу функцией ФИЛЬТР .
Первый аргумент функции — массив . Укажем диапазон с именами А2:А30. Далее нужно указать то, что нужно включить в новый диапазон, а это вычисляет ранее созданная формула. В качестве третьего аргумента зададим значение, которое будет выводиться в случае отсутствия искомых символов в тексте. Например, напишем « не найдено ».
Вот так просто мы получили список из имен, которые удовлетворяют условиям поиска. Осталось лишь создать выпадающий список. Для этого откорректируем формулу и подставим в функцию ПОИСК ссылку на ячейку, в которой будет выпадающий список. В моем примере эти ячейки находятся на втором листе.
Мануал по решению типизированных задач в Microsoft Excel / Хабр
Как видим, выборку в таблице Excel можно произвести, как с помощью автофильтра, так и применив специальные формулы. В первом случае результат будет выводиться в исходную таблицу, а во втором – в отдельную область. Имеется возможность производить отбор, как по одному условию, так и по нескольким. Кроме того, можно осуществлять случайную выборку, использовав функцию СЛЧИС.
Функция ИНДЕКС
Функция ИНДЕКС возвращает значение, которое находится в указанном номере строки выделенного диапазона.
Например, найдем имя «Ольга» в соответствующем столбце.
Первое, что мы указываем — это где будет производиться поиск. Нас будет интересовать только один столбец, поэтому выбираем его.
Далее мы должны указать номер строки в выделенном диапазоне, из которого будет возвращено значение. Здесь все по аналогии с функцией ВПР — указывается номер строки в выделенном диапазоне, а не номер строки таблицы Excel.
В мое примере это третья строка. Аргументы в квадратных скобках являются необязательными, поэтому их можно не указывать.
Если будет выбран диапазон, состоящий из нескольких столбцов, то нужно будет указать и номер строки, и номер столбца в диапазоне, на пересечении которых находится ячейка с нужным значением.
Например, если мы выделим диапазон со всеми данными и нам нужен будет адрес Ольги, то укажем, что он находится в третьей строке и втором столбце выделенного диапазона.
Функция ИНДЕКС возвращает нужное нам значение, но в отличии от ВПР координаты этой ячейки приходится задавать вручную. Чтобы автоматизировать процесс воспользуемся функцией ПОИСКПОЗ.
Как в экселе сделать выборку из таблицы
— Интервальный просмотр — Может принимать параметр 0 или ЛОЖЬ, что обозначает что совпадение между искомым значением и значением в первом столбце таблицы должен быть точным; либо 1 или ИСТИНА, соответственно совпадение должно быть неточным. Настоятельно рекомендую использовать только параметр ЛОЖЬ, иначе можно получать непредсказуемые результаты.
Как найти слово в экселе — способы фильтрации
Excel содержит в себе 3 способа текстовой фильтрации, иными словами, фильтровать можно по 3 критериям вхождения.
Ячейка содержит, начинается с, либо заканчивается на — какой то конкретной паттерн.
Критерий номер 2. — Строка заканчивается на наше слово. В этом случае она будет заканчиваться наше слово, и перед ним обязательно должен быть пробел, допустим. У нас будет рот и в конце строки не может быть слово крот. Такая строка не должна фильтроваться. Нужно чтобы перед словом рот был пробел.
И критерий номер 3 — ячейка должна начинаться на наше слово — в таком случае пробел должен быть после нашего слова, по аналогичной причине.
Однако, формула «СУММПРОИЗ» позволяет проводить такие расчеты внутри формулы.
Вывод результата в ячейку B4:
Excel найти ячейки содержащие текст — Все о Windows 10
Прежде, чем ответить на вопрос, как в таблице excel найти нужное слово, нужно понять, каким будет формат вывода — мы хотим напротив каждой ячейки в диапазоне проставить статус — есть слово в ячейке, или нет, или хотим просто узнать, есть ли слово в любой из ячеек диапазона?
Мануал по решению типизированных задач в Microsoft Excel
Поиск выдал мне всего одну статью на Хабре по схожей тематике — «Талмуд по формулам в Google SpreadSheet». В ней дано хорошее описание базовых вещей для работы в excel (хотя он и не 100% про сам excel).
Таким образом, накопив определенный пул запросов/задач, появилась идея их типизировать и предложить возможные решения (пусть не все возможные, но быстро дающие результат).
Речь пойдет о решении наиболее распространенных задач, с которыми сталкиваются пользователи.
- Лог_выражение — выражение, дающее в результате значение ИСТИНА или ЛОЖЬ.
- Значение_если_истина — значение, которое выводится, если логическое выражение истинно
- Значение_если_ложь — значение, которое выводится, если логическое выражение ложно
- если значение в столбце «Количество» больше 5, а значение в колонке «Тип» равно «А»
- то нужно вывести в колонке «Результат» значение «1», в обратном случае «0».
- Логическое_значение1-2 и т.д. — проверяемое условие, вычисление которого дает значение ИСТИНА или ЛОЖЬ
Таким образом, используя сочетание 2-х формул, мы находим решение нашей задачи и получаем результат:
- если значение в столбце «Количество» равно 10, а значение в колонке «Тип» равно «А»
- или же значение в столбце «Количество» больше или равно 5, а значение «Тип» равен «Б»
- то нужно вывести в колонке «Результат» значение «1», в обратном случае «0».
Как видно из записи, в формулу «ЕСЛИ» включено одно условие «ИЛИ» (OR) и два условия с использованием формулы «И», включенных в него. Если хотя бы одно из условий 2-го уровня имеет значение «ИСТИНА», то в колонку «Результат» будет выведен результат «1», в противном случае будет «0».
Результат:
- Номер_индекса — номер выбираемого аргумента-значения. Номер индекса должен быть числом от 1 до 254, формулой или ссылкой на ячейку, содержащую число в диапазоне от 1 до 254.
- Значение1, значение2,… — значение от 1 до 254 аргументов-значений, из которых функция «ВЫБОР», используя номер индекса, выбирает значение или выполняемое действие. Аргументы могут быть числами, ссылками на ячейки, определенными именами, формулами, функциями или текстом.
- 1 = А
- 2 = Б
- 3 = В
- 4 = Г
Как видно, значение «4» в формуле нам приходится оставить пустым и перенести результат «Г» на порядковый номер «5».
- Искомое_значение – значение, поиск которого осуществляется функцией.
- Таблица – диапазон ячеек, содержащий данные. Именно в этих ячейках будет происходить поиск. Значения могут быть текстовыми, числовыми или логическими.
- Номер_столбца — номер столбца в аргументе «Таблица», из которого будет выводиться значение в случае совпадения. Важно понимать, что отсчет столбцов происходит не по общей сетке листа (A.B,C,D и т.д.), а внутри массива, указанного в аргументе «Таблица».
- Интервальный_просмотр — определяет, какое совпадение должна найти функция — точное или приблизительное.
Затем в рабочей части таблицы уже прописывается формула со ссылкой на справочник, заполненный ранее. Т.е. в справочнике в столбце «D» происходит поиск значения из столбца «А» и при нахождении соответствия выводится значение из столбца «Е» в столбец «В».
Синтаксис формулы:
Вывод результата в ячейку B2:
Теперь представим ситуацию, когда необходимо подтянуть данные в одну таблицу из другой, при этом таблицы не идентичны. См. пример ниже
Видно, что строки в столбцах «Продукт» обеих таблиц не совпадают, однако, это не является препятствием для использования функции «ВПР».
Вывод результата в ячейку B2:
то функция выведет номер текущего столбца (в ячейке которого написана формула).
В результате получается число, которое можно использовать в функции «ВПР», чем мы и воспользуемся и получаем следующую запись формулы:
Вывод результата в ячейку B2:
Функция «СТОЛБЕЦ» определит номер текущего столбца, который будет использоваться аргументом «Номер_столбца» для определения номера столбца поиска в справочнике.
Кроме того, можно использовать конструкцию:
Вместо числа «1» можно использовать любое число (а также не только вычитать его, но и прибавлять к полученному значению), для получения желаемого результата, если нет желания ссылаться на определенную ячейку в столбце с нужным нам номером.
Получившийся результат:
Вариант решения, который сразу приходит на ум, следующий:
Вывод результата в ячейку C3:
Плюсы: наименование справочника может быть любым (текст, цифры и их сочетание), минусы – плохо подходит, если вариантов более 3-х.
Если же номера справочников всегда представляют собой числа, имеет смысл использовать следующее решение:
Вывод результата в ячейку C3:
Плюсы: формула может включать до 254 наименований справочников, минусы – их наименование должно быть строго числовым.
Результат для формулы с использованием функции «ВЫБОР»:
- Представим, что у нас как всегда есть массив данных в табличной форме (если нет, то мы к нему приводим данные), из массива по определенным признакам необходимо получить значения и поместить их в другую табличную форму.
Как видно из табличных форм, каждая позиция имеет не только наименование (которое не является уникальным), но также и относится к определенному классу и имеет свой вариант фасовки.
Используя сочетание имени и класса и фасовки, мы можем создать новый признак, для этого в таблице с данными создаем дополнительный столбец «Доп.признак», который заполняем при помощи следующей формулы:
Используя символ «&», объединяем три признака в один (разделитель между словами может быть любым, как и не быть вовсе, главное использовать аналогичное правило и для поиска)
Аналогом формулы может быть функция «СЦЕПИТЬ» (CONCATENATE), в этом случае она будет выглядеть следующим образом:
После того, как дополнительный признак создан для каждой записи в таблице с данными, приступаем к написанию функции поиска по этому признаку, которая будет иметь вид:
Вывод результата в ячейку D3:
Данный прием можно использовать и для большего количества признаков, единственное условие – уникальность получаемых комбинаций, если она не соблюдается, то результат будет некорректным.
Кейс_3 Поиск значения в массиве, или когда ВПР не в силах нам помочь
Как мы видим, функция «ВПР» тут бессильна, т.к. мы ищем не точное совпадение, а именно наличие в ячейке нужного нам значения.
Для решения задачи необходимо использовать комбинацию нескольких функций, а именно:
«ЕСЛИ»
«ЕСЛИОШИБКА»
«СТРОЧН»
«НАЙТИ»
По порядку обо всех, «ЕСЛИ» мы уже разобрали ранее, потому перейдем к функции «ЕСЛИОШИБКА» (IFERROR)
- Значение — аргумент, проверяемый на возникновение ошибок.
- Значение_при_ошибке — значение, возвращаемое при ошибке при вычислении по формуле. Возможны следующие типы ошибок: #Н/Д, #ЗНАЧ!, #ССЫЛКА!, #ДЕЛ/0!, #ЧИСЛО!, #ИМЯ? и #ПУСТО!.
- Искомый_текст — текст, который необходимо найти.
- Просматриваемый_текст — текст, в котором нужно найти искомый текст.
- Нач_позиция — знак, с которого нужно начать поиск. Первый знак в тексте «просматриваемый_текст» имеет номер 1. Если номер не указан, он по умолчанию считается равным 1.
- СТРОЧН(A2) – преобразует аргумент «Искомый_текст» в ячейке в А2 в текст с нижним регистром
- Функция «НАЙТИ» начинает поиск преобразованного аргумента «Искомый_текст» в массиве «Просматриваемый_текст», который преобразовывается функцией «СТРОЧН(E2)», также в текст с нижним регистром.
- В случае если, функция находит совпадение, т.е. возвращает порядковый номер первого символа совпадающего слова/значения, срабатывает условие ИСТИНА в формуле «ЕСЛИ», т.к. полученное значение не равно нулю. Как результат, в столбце «Результат» будет выведено значение «Bingo!»
- Если же, функция не находит совпадение т.е. порядковый номер первого символа совпадающего слова/значения не указывается и вместо значения возвращается ошибка, срабатывает условие, заложенное в формулу «ЕСЛИОШИБКА» и возвращается значение равное «0», что соответствует условию ЛОЖЬ в формуле «ЕСЛИ», т.к. полученное значение равно «0». Как результат, в столбце «Результат» будет выведено значение «fail».
Кейс_4 Поиск значения в массиве по нескольким условиям, или когда ВПР тем более не в силах нам помочь
Представим ситуацию, когда необходимо найти значение из «Таблица с результатом» в двумерном массиве «Справочник» по нескольким условиям, а именно по значению «Наименование» и «Месяц».
Табличная форма задания будет иметь следующий вид:
- Массив — диапазон ячеек, из которого будут показываться значения в случае совпадения условий их поиска.
- Если массив содержит только одну строку или один столбец, аргумент «номер_строки» или «номер_столбца» соответственно не является обязательным.
- Если массив занимает больше одной строки и одного столбца, а из аргументов «номер_строки» и «номер_столбца» задан только один, то функция «ИНДЕКС» возвращает массив, состоящий из целой строки или целого столбца аргумента «массив».
- Номер_строки — номер строки в массиве, из которой требуется возвратить значение.
- Номер_столбца — номер столбца в массиве, из которого требуется возвратить значение.
- Искомое_значение — значение, которое сопоставляется со значениями в аргументе просматриваемый_массив. Аргумент искомое_значение может быть значением (числом, текстом или логическим значением) или ссылкой на ячейку, содержащую такое значение.
- Просматриваемый_массив — диапазон ячеек, в которых производится поиск.
- Тип_сопоставления — необязательный аргумент. Число -1, 0 или 1.
возвращает число по оси Y, в данном случае оно будет равно 1, т.к. значение «А» присутствует в искомом диапазоне и имеет относительную позицию «1» в этом диапазоне.
часть формулы:
возвращает значение #Н/Д, т.к. значение «1» отсутствует в просматриваемом диапазоне.
Таким образом, мы получили координаты точки (1; #Н/Д) которые функция «ИНДЕКС» использует для поиска в аргументе «Массив».
Полностью написанная функция для ячейки B4 будет иметь следующий вид:
Поскольку, аргумент «Номер_столбца» имеет значение «#Н/Д», то результат для ячейки «B4» будет соответствующий.
Как видно из получившегося результата не все значения в таблице с результатом находят совпадение со справочником и в итоге мы видим, что часть значений в таблице выводится в виде «#Н/Д», что затрудняет использование данных для дальнейших расчетов.
Результат:
Что бы нейтрализовать этот негативный эффект используем функцию «ЕСЛИОШИБКА», о которой мы читали ранее, и заменяем значение, возвращающееся при ошибке на «0», тогда формула будет иметь вид:
Вывод результата в ячейку B4:
Как видно на картинке, значения «#Н/Д» более не мешают нам в последующих вычислениях с использованием значений в таблице с результатом.
Функция ПРОСМОТР (LOOKUP) возвращает значение из строки, столбца или массива. Функция имеет две синтаксических формы: векторную и форму массива.
- Искомое_значение — значение, которое функция ПРОСМОТР ищет в первом векторе. Искомое_значение может быть числом, текстом, логическим значением, именем или ссылкой на значение.
- Просматриваемый_вектор — диапазон, состоящий из одной строки или одного столбца. Значения в аргументе просматриваемый_вектор могут быть текстом, числами или логическими значениями.
- Значения в аргументе просматриваемый_вектор должны быть расположены в порядке возрастания: . -2, -1, 0, 1, 2, . A-Z, ЛОЖЬ, ИСТИНА; в противном случае функция ПРОСМОТР может возвратить неправильный результат. Текст в нижнем и верхнем регистрах считается эквивалентным.
- Вектор_результатов — диапазон, состоящий из одной строки или столбца. Вектор_результатов должен иметь тот же размер, что и просматриваемый_вектор.
Аргументы «Просматриваемый_вектор» и «Вектор_результата» можно записать в форме массива – в этом случае не придется выводить их в отдельную таблицу на листе Excel.
В этом случае функция будет выглядеть следующим образом:
Вывод результата в ячейку B3:
=ПРОСМОТР(E3;;)
- Массив1 — первый массив, компоненты которого нужно перемножить, а затем сложить результаты.
- Массив2, массив3… — от 2 до 255 массивов, компоненты которых нужно перемножить, а затем сложить результаты.
- Найти общую сумму по стоимости отгрузок по каждому из продуктов за определенный период:
Как видно из таблицы с данными, что бы посчитать стоимость необходимо цену умножить на количество, а полученное значение, применив условия отбора переносить в таблица с результатом.
Однако, формула «СУММПРОИЗ» позволяет проводить такие расчеты внутри формулы.
Вывод результата в ячейку B4:
– задаем условие по отбору в столбце «Наименование» таблицы с данными по столбцу «Наименование» в таблице с результатом
– задаем условие по временным рамкам, дата больше или равна первого числа текущего месяца, но меньше первого числа месяца следующего. Аналогично – условие в таблице с результатом, массив – в таблице с данными.
– перемножаем столбцы «Количество» и «Цена» в таблице с данными.
Несомненным плюсом данной функции является свободный порядок записи условий, их можно записывать в любом порядке, на результат это не повлияет.
Результат:
Теперь усложним условие и добавим требование, что бы отбор по наименованию «печеньки» происходил только по классам «малые» и «большие», а по наименованию «булки» все, кроме по классу «с джемом»:
– как видно, два или более условия по одному столбцу выделяются в отдельную группу при помощи символа «+» и заключения условий в дополнительные скобки.
В формуле для отбора по булкам также добавилось новое условие:
Что ж, вот мы и подошли к концу нашего краткого мануала, который на самом деле мог бы быть намного больше, но целью было все-таки дать решение наиболее встречающихся ситуаций, а не описывать решение частных (но гораздо более интересных случаев).
Надеюсь, что мануал поможет кому-нибудь в решении задач при помощи Excel, ведь это будет значить, что мой труд не пропал зря!
Функции ИНДЕКС и ПОИСКПОЗ в Excel. Отличная замена ВПР (VLOOKUP) | Андрей Сухов | Яндекс Дзен
Если речь идет о поиске текстовых данных, то всегда выбираем точное совпадение . Другие параметры актуальны только при работе с числовыми данными. Поэтому указываем ноль и в итоге получаем цифру 3, что соответствует третьей строке в выделенном диапазоне.