Примеры использования функции НАЙТИ в Excel формулах

Как сделать поиск в Экселе

В документах Microsoft Excel, которые состоят из большого количества полей, часто требуется найти определенные данные, наименование строки, и т.д. Очень неудобно, когда приходится просматривать огромное количество строк, чтобы найти нужное слово или выражение. Сэкономить время и нервы поможет встроенный поиск Microsoft Excel. Давайте разберемся, как он работает, и как им пользоваться.

Поисковая функция в Excel

Поисковая функция в программе Microsoft Excel предлагает возможность найти нужные текстовые или числовые значения через окно «Найти и заменить». Кроме того, в приложении имеется возможность расширенного поиска данных.

) поиска номер четыре — это макрос VBA для поиска (перебора значений)

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

Sub Poisk()

‘ ruexcel.ru макрос проверки значений (поиска)

Dim keyword As String

keyword = «Искомое слово» ‘присвоить переменной искомое слово

On Error Resume Next ‘при ошибке пропустить

For Each cell In Selection ‘для всх ячеек в выделении (выделенном диапазоне)

If cell.Value = «» Then GoTo Line1 ‘если ячейка пустая перейти на «Line1″

If InStr(StrConv(cell.Value, vbLowerCase), keyword) > 0 Then cell.Interior.Color = vbRed ‘если в ячейке содержится слово окрасить ее в красный цвет (поиск)

Line1:

Next cell

End Sub

Видео

Пример использования НАЙТИ и ПСТР в формуле Excel

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

Как видно на рисунке формула сначала ищет номер позиции для символа с помощь функции НАЙТИ. А после найденный номер позиции использует в своих аргументах функция ПСТР.

Функция НАЙТИ требует заполнить минимум 2 из 3-х аргументов:

1. Искомый_текст – здесь необходимо указать текст, который следует найти и получить его порядковый номер (позицию) в исходной текстовой строке.
2. Просматриваемый_текст – тут указываем ссылку на ячейку с исходной строкой, которая содержит искомый символ или текст.
3. Нач_позиция – это не обязательный аргумент. Здесь можно указать номер позиции символа в строке, с которого следует начинать поиск. Если строка содержит более одного найденного искомого символа, то с помощью данного необязательного аргумента можно указать номер символа с которого будет просматриваться остальная часть строки. Если он не указан в этом аргументе, то по умолчанию он равен = 1, то есть с первого, а значит целая строка.

На пример, в примере функция находит первый дефис в строке «PWR-16-Small». В результате своего вычисления она по умолчанию возвращает число 4. Так как первый дефис в и сходной строке находится на четвертой позиции.

Найти

Комбинация клавиш Ctrl+F позволяет быстро вызвать окно функции Найти. Также можно активировать инструмент через отдельную кнопку на Панели инструментов.

Расширенное диалоговое окно содержит поле для ввода искомой информации, параметры масштаба, например поиск по всем листам, а также варианты анализа информации по столбцу или строке.

Данная функция реализована и в виде формулы с одноименным названием, которая также позволяет осуществлять поиск по словам. Рассмотрим работу на примере списка фруктов. Вписываете формулу =НАЙТИ(«а»;$B$4:$B$11), применяете автозаполнение и получаете результат:

Замечания

• Функции НАЙТИ и НАЙТИБ работают с учетом регистра и не позволяют использовать подстановочные знаки. Если необходимо выполнить поиск без учета регистра или использовать подстановочные знаки, воспользуйтесь функцией ПОИСК или ПОИСКБ.

• Если в качестве аргумента «искомый_текст» задана пустая строка («»), функция НАЙТИ выводит значение, равное первому знаку в строке поиска (знак с номером, соответствующим аргументу «нач_позиция» или 1).

• Искомый_текст не может содержать подстановочные знаки.

• Если find_text не отображаются в within_text, find и FINDB возвращают #VALUE! значение ошибки #ЗНАЧ!.

• Если start_num не больше нуля, то найти и найтиБ возвращает значение #VALUE! значение ошибки #ЗНАЧ!.

• Если start_num больше, чем длина within_text, то поиск и НАЙТИБ возвращают #VALUE! значение ошибки #ЗНАЧ!.

• Аргумент «нач_позиция» можно использовать, чтобы пропустить нужное количество знаков. Предположим, например, что для поиска строки «МДС0093.МесячныеПродажи» используется функция НАЙТИ. Чтобы найти номер первого вхождения «М» в описательную часть текстовой строки, задайте значение аргумента «нач_позиция» равным 8, чтобы поиск в той части текста, которая является серийным номером, не производился. Функция НАЙТИ начинает со знака 8, находит искомый_текст в следующем знаке и возвращает число 9. Функция НАЙТИ всегда возвращает номер знака, считая от левого края текста «просматриваемый_текст», а не от значения аргумента «нач_позиция».

Различия

Проанализировав результаты, полученные при использовании инструментов ПОИСК и НАЙТИ, можно выделить несколько ключевых отличий:

1. Функция НАЙТИ чувствительна к регистру букв, то есть алгоритм распознает большие и маленькие символы. Это четко видно на примере фрукта Апельсин. ПОИСК вернул 1, а НАЙТИ выдало ошибку, поскольку маленькой буквы а нет в ячейке. Подобная ситуация и для Ананаса с Айвой

1. Дополнительным различием является то, что ПОИСК умеет работать с подстановочными символами типа вопросительный знак (?) и звездочка (*), которые используются для неточного формирования поискового запроса.

На заметку! Чтобы найти именно эти символы в тексте необходимо использовать тильду (~) перед аргументом.

1. Инструмент НАЙТИ реализован как в виде отдельной кнопки на главной панели, так и в виде встроенной функции редактора.

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

Функция ПСТР

ПСТР возвращает из указанной строки часть текста в заданном количестве символов, начиная с указанного символа.

Синтаксис: ПСТР(текст; начальная_позиция; количество_знаков)

Определения аргументов:

• текст – строка или ссылка на ячейку, содержащую текст;
• начальная_позиция – порядковый номер символа, начиная с которого необходимо вернуть строку;
• количество_знаков – натуральное целое число, указывающее количество символов, которое необходимо вернуть, начиная с позиции начальная_позиция.

Пример использования:

Из текста, находящегося в ячейке A1 необходимо вернуть последние 2 слова, которые имеют общую длину 12 символов. Первый символ возвращаемой фразы имеет порядковый номер 12.

Аргумент количество_знаков может превышать допустимо возможную длину возвращаемых символов. Т.е. если в рассмотренном примере вместо количество_знаков = 12, было бы указано значение 15, то результат не изменился, и функция так же вернула строку «функции ПСТР».

Для удобства использования данной функции ее аргументы можно подменить функциями «НАЙТИ» и «ДЛСТР», как это было сделано в примере с функцией «ЗАМЕНИТЬ».

Поиск символа в ячейке

Наиболее простой пример использования функции — осуществление поиска определенного символа в ячейке.

Логика проста — если поиск позиции символа не возвращает ошибку, значит, символ в ячейке присутствует:

Функция ПОИСКПОЗ() в MS EXCEL

​, чтобы найти значение​имеет две формы​ т.е. позицию значения​ с помощью подстановочных​представляет собой диапазон​​Эта формула должна быть​​FIND​ 8-й позиции слева.​Скопируйте образец данных из​ ниже образом.​ «;A3;ПОИСК(» «;A3;1)+1) –​ для даты и​​ ее от номера​​ — по два.​ сортировку, в нашем​#N/A​. В данном случае​​ веса и определить​​ – векторную и​​ 40.​​ знаков можно задать​ ячеек размещенный одновременно​ введена, как формула​(НАЙТИ) может найти​

​Пусть в ячейке​ следующей таблицы и​Функция НАЙТИ при подсчете​ находит второй пробел.​ общий формат вместо​ телефона.​Чтобы воспользоваться функцией, необходимо​ случае это первый​(#Н/Д). Функция​ поменяется только номер​ стоимость обработки (Handling)​ массива, и может​Это можно сделать с​ поиск по шаблону,​ в нескольких столбцах​ массива, нажатием​ текст внутри текстовой​А2​

Синтаксис функции

​ вставьте их в​​ всегда рассматривает каждый​

​ Это будет конечная​​ денежного. Чтобы избежать​Пример 2. Есть таблица​ ввести следующую формулу:​​ столбец таблицы. Во​​IF​ столбца. Для​ и перевозки (Shipping)​ возвращать значение из​ помощью формулы =ПОИСКПОЗ(45;B31:B37;1)​ т.е. искомое_значение может​ и нескольких ячейках.​

​Ctrl+Shift+Enter​​ строки, учитывая регистр​введена строка Первый​ ячейку A1 нового​​ знак, как однобайтовый,​​ позиция отчества.​ подобного результата, применяется​ с текстовой информацией,​​=ПОИСК(нужный_текст;анализируемый_текст;[начальная_позиция]).​​ втором выберите​(ЕСЛИ) составлена так,​Shipping​​ партии товара такого​​ одного столбца, одной​Обратите внимание, что тип​ содержать знаки шаблона:​Тип_сопоставления​​.​​ символов. Например:​ канал — лучший.​ листа Excel. Чтобы​ так и двухбайтовый,​

​Далее из общей длины​​ функция ТЕКСТ. Она​ в которой слово​​В этой формуле задаваемые​​Values​ что если заданный​​– это значение​​ веса. Конечно же,​ строки или из​​ сопоставления =1 (третий​

• ​ звездочку (*) и​​— число -1,​​=IF(OR(ISNUMBER(FIND($E$2:$E$4,B2))),»Yes»,»No»)​Найти начальную позицию текста​ Формула =ПОИСК(СИМВОЛ(32);A2) вернет​ отобразить результаты формул,​​ как один знак,​​ строки отнимается количество​​ форматирует значения по​​ «маржа» нужно заменить​ значения определяются следующим​
• ​(Значения), а в​ размер ковра не​3​ вес большинства партий​ массива (аналог ВПР​​ аргумент функции).​​ знак вопроса (?).​ 0 или 1.​=ЕСЛИ(ЕЧИСЛО(НАЙТИ($E$2:$E$4;B2)));»Yes»;»No»)​ в текстовой строке.​ 7, т.к. символ​ выделите их и​​ независимо от выбранного​​ знаков с начала​ заданию пользователя.​ на «объем».​
• ​ образом.​​ третьем укажите порядок​​ будет соответствовать одному​:​ товара не будет​ и ГПР). Из​3. Поиск позиции в​​ Звездочка соответствует любой​​Тип_сопоставления​В следующем примере большинство​Найти точные данные на​ пробела (код 32)​ нажмите клавишу F2,​ по умолчанию языка.​

​ строки до второго​Формула «для даты» теперь​Откроем книгу Excel с​

​Искомый текст. Это числовая​ сортировки по возрастанию.​ из двух имеющихся​=VLOOKUP(B12,shipping_and_handling,3)​

Поиск позиции в массивах с текстовыми значениями

​ иметь такие же​ этих трёх функций,​ списке отсортированном по​​ последовательности знаков, знак​​указывает, как MS​

​ адресов в столбце​ листе.​ находится на 7-й​

​ а затем — клавишу​Функция НАЙТИБ при подсчете​

​ пробела. Результат –​ выглядит так:​ обрабатываемыми данными. Пропишем​ и буквенная комбинация,​ Если вместе с​ вариантов, то по​

​=ВПР(B12;shipping_and_handling;3)​ ровные значения, поэтому​ вероятнее всего, Вы​ убыванию выполняется аналогично,​ вопроса соответствует любому​

​ EXCEL сопоставляет​ B начинается с​Найти название улицы в​ позиции.​ ВВОД. При необходимости​ рассматривает каждый двухбайтовый​ число символов справа,​Второй аргумент функции –​​ формулу для поиска​​ позицию которой требуется​

​ данными, Вы выделили​ умолчанию будет взят​Функция​ мы используем в​

Поиск позиции в массиве констант

​ будете использовать​ но с типом​ одиночному знаку.​искомое_значение​ номера. При помощи​ адресе.​Формула =ПОИСК(«#???#»;»Артикул #123# ID»)​

Поиск позиции с использованием подстановочных знаков

​ измените ширину столбцов,​ знак как два​ которые нужно вернуть.​ формат. Где брать​ нужного слова «маржа»:​ найти.​ шапку таблицы, не​ большой размер (L).​ГПР​ качестве последнего аргумента​ВПР​ сопоставления = -1.​Предположим, что имеется перечень​

​со значениями в​ формулы в столбце​Функция​ будет искать в​ чтобы видеть все​ знака, если включена​Формула «для отчества» строится​

​ строку формата? Щелкаем​Теперь дополним формулу функцией​Анализируемый текст. Это тот​ забудьте поставить галочку​На практике желательно сделать​работает точно таким​TRUE​

​гораздо чаще, чем​ В этом случае​ товаров и мы​ аргументе​​ C мы проверяем,​​FIND​ строке «Артикул #123#​

Поиск позиций ВСЕХ текстовых значений, удовлетворяющих критерию

​ данные.​ поддержка языка с​ по тем же​ правой кнопкой мыши​ ЗАМЕНИТЬ:​ фрагмент текстовой информации,​

​My data has headers​ так, чтобы пользователь​​ же образом. Точнее​​(ИСТИНА), либо вовсе​ остальные. Именно на​

​ функция ПОИСКПОЗ() находит​ не знаем точно​просматриваемый_массив.​ является ли первый​

​(НАЙТИ) имеет вот​

​ ID» последовательность из​Данные​ БДЦС и такой​ принципам:​ по ячейке со​Функция ПОИСК очень схожа​ из которого требуется​​(Мои данные содержат​​ гарантированно вводил правильное​ говоря, она также​ не указываем его.​ ней я сфокусируюсь​ наименьшее значение, которое​ как записана товарная​

​Если​ символ цифрой. Если​

​ такой синтаксис:​

​ 5 символов, которая​Владимир Егоров​ язык установлен по​Для объединения значений из​ значением. Нажимаем «Формат​

Поиск позиции в массивах с Числами

​ с функцией НАЙТИ​ вычленить искомую букву​ заголовки). Нажмите​ название ковра и​​ использует искомое значение​​ В таком случае​

​ в этой статье.​ больше либо равно​ позиция относящаяся к​

​тип_сопоставления​ это цифра, то​FIND(find_text,within_text,[start_num])​

​ начинается и заканчивается​​Формула​​ умолчанию. В противном​ нескольких ячеек в​ ячеек». В открывшемся​ по принципу действия.​

​ или сочетание и​ОК​ его размер. Вы​ и диапазон данных,​​ наша формула найдёт​​ В целом, если​

​ чем искомое значение.​ яблокам: яблоки или​равен 0, то​ функция​НАЙТИ(искомый_текст;просматриваемый_текст;[нач_позиция])​ на знак #.​Описание​ случае функция НАЙТИБ​ одну строку используется​ окне выбираем «все​ Более того у​ вернуть позицию.​.​ можете реализовать это,​

​ но вместо номера​ результат, даже без​

​ Вы поймете и​Функции ПОИСКПОЗ() и ИНДЕКС()​ яблоко.​

​ функция ПОИСКПОЗ() находит​FIND​find_text​Чтобы найти позицию второго​Результат​ рассматривает каждый знак​ оператор амперсанд (&)​ форматы». Копируем нужный​ них фактически одинаковые​Начальная позиция. Данный фрагмент​

Функции ПОИСКПОЗ() и ИНДЕКС()

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

​(НАЙТИ) находит первый​(искомый_текст) – текст,​ вхождения буквы «а»​=НАЙТИ(«В»;A2)​

​ как один знак.​

​ или функция СЦЕПИТЬ.​ в строке «Тип».​ аргументы. Только лишь​ необязателен для ввода.​

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

​ВПР​ т.к. позволяют по​ задать»яблок*» и формула​ в​ символ пробела, а​ который Вы ищете.​

Функция ЗАМЕНИТЬ

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

Синтаксис: ЗАМЕНИТЬ(старый_текст; начальная_позиция; количество_знаков; новый_текст)

Определения аргументов:

• старый_текст – строка либо ссылка на ячейку, содержащую текст;
• начальная_позиция – порядковый номер символа слева направо, с которого нужно производить замену;
• количество_знаков – количество символов, начиная с начальная_позиция включительно, которые необходимо заменить новым текстом;
• новый_текст – строка, которая подменяет часть старого текста, заданного аргументами начальная_позиция и количество_знаков.

Пример использования:

Здесь в строке, содержащейся в ячейке A1, подменяется слово «старый», которое начинается с 19-го символа и имеет длину 6 символов, на слово «новый».

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

• Аргумент «начальная_позиция» подменим функцией «НАЙТИ»;
• В место аргумент «количество_знаков» вложим функцию «ДЛСТР».

В результате получим формулу: =ЗАМЕНИТЬ(A1;НАЙТИ(«старый»;A1);ДЛСТР(«старый»);»новый»)

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

Поиск и замена данных

Данные обычно ищутся не просто так, а для каких-то целей. Такой целью часто является замена искомой комбинации (или формата) на другую. Чтобы найти и заменить в выделенном диапазоне Excel одни значения на другие, в окне Найти и заменить необходимо выбрать вкладку Замена. Либо сразу выбрать на ленте команду Главная → Редактирование → Найти и выделить → Заменить.

Еще удобнее применить сочетание горячих клавиш найти и заменить в Excel – Ctrl+H.

Теги