ПОИСК, ПОИСКБ (функции ПОИСК, ПОИСКБ)
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще…Меньше
В этой статье описаны синтаксис формулы и использование функций ПОИСК и ПОИСКБ в Microsoft Excel.
Описание
Функции ПОИСК И ПОИСКБ находят одну текстовую строку в другой и возвращают начальную позицию первой текстовой строки (считая от первого символа второй текстовой строки). Например, чтобы найти позицию буквы «n» в слове «printer», можно использовать следующую функцию:
=ПОИСК(«н»;»принтер»)
Эта функция возвращает 4, так как «н» является четвертым символом в слове «принтер».
Можно также находить слова в других словах. Например, функция
=ПОИСК(«base»;»database»)
возвращает 5, так как слово «base» начинается с пятого символа слова «database». Можно использовать функции ПОИСК и ПОИСКБ для определения положения символа или текстовой строки в другой текстовой строке, а затем вернуть текст с помощью функций ПСТР и ПСТРБ или заменить его с помощью функций ЗАМЕНИТЬ и ЗАМЕНИТЬБ. Эти функции показаны в примере 1 данной статьи.
Важно:
-
Эти функции могут быть доступны не на всех языках.
-
Функция ПОИСКБ отсчитывает по два байта на каждый символ, только если языком по умолчанию является язык с поддержкой БДЦС. В противном случае функция ПОИСКБ работает так же, как функция ПОИСК, и отсчитывает по одному байту на каждый символ.
К языкам, поддерживающим БДЦС, относятся японский, китайский (упрощенное письмо), китайский (традиционное письмо) и корейский.
Синтаксис
ПОИСК(искомый_текст;просматриваемый_текст;[начальная_позиция])
ПОИСКБ(искомый_текст;просматриваемый_текст;[начальная_позиция])
Аргументы функций ПОИСК и ПОИСКБ описаны ниже.
-
Искомый_текст Обязательный. Текст, который требуется найти.
-
Просматриваемый_текст Обязательный. Текст, в котором нужно найти значение аргумента искомый_текст.
-
Начальная_позиция Необязательный. Номер знака в аргументе просматриваемый_текст, с которого следует начать поиск.
Замечание
-
Функции ПОИСК и ПОИСКБ не учитывают регистр. Если требуется учитывать регистр, используйте функции НАЙТИ и НАЙТИБ.
-
В аргументе искомый_текст можно использовать подстановочные знаки: вопросительный знак (?) и звездочку (*). Вопросительный знак соответствует любому знаку, звездочка — любой последовательности знаков. Если требуется найти вопросительный знак или звездочку, введите перед ним тильду (~).
-
Если значение find_text не найдено, #VALUE! возвращается значение ошибки.
-
Если аргумент начальная_позиция опущен, то он полагается равным 1.
-
Если start_num больше нуля или больше, чем длина аргумента within_text, #VALUE! возвращается значение ошибки.
-
Аргумент начальная_позиция можно использовать, чтобы пропустить определенное количество знаков. Допустим, что функцию ПОИСК нужно использовать для работы с текстовой строкой «МДС0093.МужскаяОдежда». Чтобы найти первое вхождение «М» в описательной части текстовой строки, задайте для аргумента начальная_позиция значение 8, чтобы поиск не выполнялся в той части текста, которая является серийным номером (в данном случае — «МДС0093»). Функция ПОИСК начинает поиск с восьмого символа, находит знак, указанный в аргументе искомый_текст, в следующей позиции, и возвращает число 9. Функция ПОИСК всегда возвращает номер знака, считая от начала просматриваемого текста, включая символы, которые пропускаются, если значение аргумента начальная_позиция больше 1.
Примеры
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.
|
Данные |
||
|
Выписки |
||
|
Доход: маржа |
||
|
маржа |
||
|
Здесь «босс». |
||
|
Формула |
Описание |
Результат |
|
=ПОИСК(«и»;A2;6) |
Позиция первого знака «и» в строке ячейки A2, начиная с шестого знака. |
7 |
|
=ПОИСК(A4;A3) |
Начальная позиция строки «маржа» (искомая строка в ячейке A4) в строке «Доход: маржа» (ячейка, в которой выполняется поиск — A3). |
8 |
|
=ЗАМЕНИТЬ(A3;ПОИСК(A4;A3);6;»объем») |
Заменяет слово «маржа» словом «объем», определяя позицию слова «маржа» в ячейке A3 и заменяя этот знак и последующие пять знаков текстовой строкой «объем.» |
Доход: объем |
|
=ПСТР(A3;ПОИСК(» «;A3)+1,4) |
Возвращает первые четыре знака, которые следуют за первым пробелом в строке «Доход: маржа» (ячейка A3). |
марж |
|
=ПОИСК(«»»»;A5) |
Позиция первой двойной кавычки («) в ячейке A5. |
5 |
|
=ПСТР(A5;ПОИСК(«»»»;A5)+1;ПОИСК(«»»»;A5;ПОИСК(«»»»;A5)+1)-ПОИСК(«»»»;A5)-1) |
Возвращает из ячейки A5 только текст, заключенный в двойные кавычки. |
босс |
Нужна дополнительная помощь?
Skip to content
В статье показано, как выполнять быстрый поиск с несколькими условиями в Excel с помощью ИНДЕКС и ПОИСКПОЗ.
Хотя Microsoft Excel предоставляет специальные функции для вертикального и горизонтального поиска, опытные пользователи обычно заменяют их комбинацией функций ПОИСКПОЗ и ИНДЕКС, которая во многих отношениях превосходит ВПР и ГПР. К примеру, можно искать два или более критерия в столбцах и строках.
Мы постараемся подробно объяснить синтаксис и логику вычислений, чтобы вы могли легко создать формулы для своих конкретных нужд.
- Общая формула поиска по нескольким критериям
- Пример формулы массива
- Поиск по нескольким условиям без формулы массива
- ИНДЕКС ПОИСКПОЗ с несколькими условиями в нескольких строках и столбцах
Как сделать поиск с несколькими условиями
При работе с большими базами данных вы можете иногда оказаться в ситуации, когда нужно что-то найти, но нет уникального идентификатора для поиска. В этом случае поиск с несколькими условиями является единственным решением.
Чтобы найти значение на основе нескольких критериев в отдельных столбцах, используйте эту общую формулу:
{=ИНДЕКС( диапазон_возврата; ПОИСКПОЗ (1; ( критерий1 = диапазон1 ) * ( критерий2 = диапазон2 ) * (…); 0))}
Где:
- Диапазон_возврата — это диапазон, из которого возвращается значение.
- Критерии1 , критерии2 , … – это условия, которые необходимо выполнить.
- Диапазон1 , диапазон2 , … — это диапазоны, на которых должны проверяться соответствующие критерии.
Важное замечание! Это формула массива , и она должна быть введена через Ctrl + Shift + Enter. Появятся {фигурные скобки}, что является визуальным признаком формулы массива в Excel. Не пытайтесь вводить фигурные скобки вручную, это не сработает!
Эта формула представляет собой расширенную версию комбинации ИНДЕКС+ПОИСКПОЗ, которая возвращает совпадение на основе одного критерия. Чтобы оценить несколько условий, мы используем операцию умножения, которая работает как оператор «И» в формулах массива . Ниже вы найдете реальный пример и подробное пошаговое объяснение логики расчетов.
ИНДЕКС ПОИСКПОЗ по нескольким столбцам – пример формулы
В этом примере мы будем использовать таблицу с комбинацией критериев (в нашем случае регион-месяц-товар) в каждой строке. Наша цель — получить данные о продажах определенного товара в данном регионе и в нужном месяце.
Вот наши исходные данные и критерии:
- Диапазон_возврата (продажи) — D2:D13
- Критерий1 (целевой регион) – G1
- Критерий2 (целевой месяц) – G2
- Критерий 3 (целевой товар) — G3
- Диапазон1 (регионы) – A2:A13
- Диапазон2 (месяцы) – B2:B13
- Диапазон3 (товары) – C2:C13
Формула принимает следующий вид:
=ИНДЕКС(D2:D13; ПОИСКПОЗ(1; (G1=A2:A13)*(G2=B2:B13)*(G3=C2:C13); 0))
Запишем формулу в G4, завершим ее, нажав Ctrl+Shift+Enter. Получаем следующий результат:
Как это работает? Разберем пошагово.
Самая сложная часть — это функция ПОИСКПОЗ, так что давайте пошагово рассмотрим логику ее работы:
ПОИСКПОЗ(1; (G1=A2:A13)*(G2=B2:B13)*(G3=C2:C13)
Как вы помните, ПОИСКПОЗ ищет заданное значение в массиве и возвращает относительное его положение в этом массиве.
В нашей формуле аргументы следующие:
- Искомое_значение : 1
- Массив поиска : (G1=A2:A13) * (G2=B2:B13) * (G3=C2:C13)
- Тип_соответствия : 0
Шаг 1.
Первый аргумент предельно ясен — функция ищет число 1. Третий аргумент, установленный в 0, означает «точное совпадение», т.е. формула возвращает первое найденное значение, которое точно равно искомому значению.
Вопрос в том, почему мы ищем «1»? Чтобы получить ответ, давайте внимательнее посмотрим на массив поиска, где мы сравниваем каждый наш критерий с соответствующим диапазоном: целевой регион в ячейке G1 со всеми регионами (A2: A13), целевой месяц в G2 со всеми месяцами (B2: B13), и товар в G3 в колонке товаров (C2:C13). В результате этих сравнений мы имеем 3 массива значений ИСТИНА и ЛОЖЬ, где ИСТИНА представляет значения, соответствующие условию. Чтобы визуализировать это, вы можете выбрать отдельное выражение в формуле и нажать клавишу F9 , чтобы увидеть, что оно возвращает:
=ИНДЕКС(D2:D13; ПОИСКПОЗ(1; {ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ИСТИНА:ИСТИНА:ИСТИНА:ИСТИНА:ИСТИНА}*{ЛОЖЬ:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ИСТИНА:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ}*{ЛОЖЬ:ИСТИНА:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ИСТИНА}; 0))
Шаг 2.
Операция умножения преобразует значения ИСТИНА и ЛОЖЬ в 1 и 0 соответственно:
{0:0:0:0:0:0:1:1:1:1:1:1}*{0:0:1:0:0:1:1:1:0:0:0:0}*{0:1:0:1:0:1:0:1:0:1:0:1}
Шаг 3.
А поскольку умножение на 0 всегда дает 0, результирующий массив содержит 1 только в тех строках, которые соответствуют всем трём условиям:
{0:0:0:0:0:0:0:1:0:0:0:0}
Приведенный выше массив переходит в аргумент массив_поиска функции ПОИСКПОЗ. Функция возвращает порядковый номер позиции с цифрой 1, для которой все критерии имеют значение ИСТИНА (строка 8 в нашем случае). Если в массиве будет несколько единиц, будет определена позиция только первой из них.
Шаг 4.
Число, возвращаемое функцией ПОИСКПОЗ, поступает непосредственно в аргумент номер_строки функции ИНДЕКС(массив, номер_строки, [номер_столбца]):
=ИНДЕКС(D2:D13, 
И это дает результат 680, что является восьмым по счету значением в массиве D2:D13.
Формула ИНДЕКС ПОИСКПОЗ без массива с несколькими критериями
Формула массива, рассмотренная в предыдущем примере, хорошо работает для опытных пользователей. Но если вы строите формулу для кого-то другого, и этот кто-то не знает о работе с формулами массива в Excel, то он может непреднамеренно ее сломать. Например, пользователь может щелкнуть вашу формулу, чтобы изучить ее, а затем нажать Enter вместо Ctrl + Shift + Enter.
В таких случаях было бы разумно избегать массивов и использовать обычную формулу, которая является более сложной, но зато и более защищенной от случайной «поломки»:
ИНДЕКС( диапазон_возврата; ПОИСКПОЗ (1; ИНДЕКС(( критерий1 = диапазон1 ) * ( критерий2 = диапазон2 ) * (..); 0; 1); 0))
Для нашего набора данных формула поиска одновременно по нескольким столбцам выглядит следующим образом:
=ИНДЕКС(D2:D13; ПОИСКПОЗ(1; ИНДЕКС((G1=A2:A13) * (G2=B2:B13) * (G3=C2:C13); 0; 1); 0))
Как работает эта формула?
Поскольку функция ИНДЕКС умеет работать с массивами, мы добавляем еще один ИНДЕКС для обработки массива единиц и нулей, созданного путем умножения двух или более массивов ИСТИНА/ЛОЖЬ. Второй ИНДЕКС настроен с аргументом номер_строки равным нулю, чтобы формула возвращала весь столбец, а не одно значение. Поскольку в любом случае это массив из одного столбца, мы можем безопасно поставить 1 для номер_столбца :
ИНДЕКС({0:0:0:0:0:0:0:1:0:0:0:0};0, 1) возвращает {0:0:0:0:0:0:0:1:0:0:0:0}
Этот массив передается в функцию ПОИСКПОЗ:
ПОИСКПОЗ(1; {0:0:0:0:0:0:0:1:0:0:0:0}; 0)
ПОИСКПОЗ находит номер позиции, для которого все условия – ИСТИНА (точнее, ищет относительное положение цифры «1» в указанном массиве), и передает номер этой позиции в аргумент номер_строки первого ИНДЕКС:
=ИНДЕКС(D2:D13,
Ну и далее получаем нужный результат.
ИНДЕКС ПОИСКПОЗ с несколькими условиями в нескольких строках и столбцах
В этом примере показано, как выполнять поиск, проверяя два или более критерия в строках и столбцах. На самом деле это более сложный случай так называемого «матричного поиска» или «двустороннего поиска» с более чем одной строкой заголовка.
Вот общая формула ИНДЕКС ПОИСКПОЗ с несколькими критериями в строках и столбцах:
{=ИНДЕКС( массив_таблицы ; ПОИСКПОЗ( значение_поиска ; столбец_поиска ; 0); ПОИСКПОЗ( значение_поиска1 & значение_поиска2 ; строка_поиска1 & строка_поиска2 ; 0))}
где:
Массив таблицы — область для поиска, т. е. все значения таблицы, кроме заголовков столбцов и строк.
Значение_поиска — то, что вы ищете по вертикали в столбце.
Столбец_поиска — диапазон столбцов для поиска, обычно это заголовки строк.
Значение_поиска1, значение_поиска2, … — то, что вы ищете по горизонтали в строках.
Строка_поиска1, строка_поиска2, … — диапазоны строк для поиска, обычно это заголовки столбцов.
Важное замечание! Чтобы формула работала корректно, ее нужно вводить как формулу массива при помощи комбинации Ctrl + Shift + Enter.
Это разновидность классической формулы двустороннего поиска в массиве, которая ищет значение на пересечении определенной строки и столбца. Разница в том, что вы объединяете несколько значений и диапазонов поиска для оценки нескольких заголовков столбцов. Чтобы лучше понять логику ее работы, рассмотрим небольшой пример.
В приведенной ниже таблице мы будем искать значение на основе заголовков строк (элементы) и заголовков двух столбцов (регионы и поставщики). То есть, ищем по одному условию по строкам и по двум условиям – по столбцам. Чтобы упростить построение формулы, давайте сначала определим все критерии и диапазоны:
- Массив_таблицы — B3:E4
- Значение_поиска — H1
- Столбец_поиска (заголовки строк: товары) — A3:A5
- Значение_поиска1 (целевой регион) — H2
- Значение_поиска 2 (целевой продавец) — H3
- Строка_поиска1 (заголовки столбцов 1: регионы) — B1:E1
- Строка_поиска2 (заголовки столбцов 2: продавцы) — B2:E2
А теперь подставьте аргументы в общую формулу, описанную выше, и вы получите такой результат:
=ИНДЕКС(B3:E5; ПОИСКПОЗ(H1;A3:A5;0); ПОИСКПОЗ(H2&H3; B1:E1&B2:E2; 0))
Не забудьте завершить формулу, нажав комбинацию клавиш Ctrl + Shift + Enter, после чего поиск по матрице с несколькими условиями будет выполнен успешно:
Пошагово рассмотрим, как работает эта формула.
Поскольку мы ищем и по вертикали, и по горизонтали, то нужно указать номера строк и столбцов для функции ИНДЕКС (массив, номер_строки, номер_столбца).
Шаг 1.
Номер_строки предоставляется функцией ПОИСКПОЗ(H1;A3:A5;0), которая сравнивает целевой элемент (бананы) в H1 с заголовками строк в A3:A5.
ПОИСКПОЗ(«Бананы»;{«Апельсины»:»Бананы»:»Лимоны»};0)
Это дает результат 2, потому что «Бананы» — это второй элемент в указанном списке.
Шаг 2.
Номер_столбца вычисляется путем объединения двух значений поиска и двух массивов поиска: ПОИСКПОЗ(H2&H3; B1:E1&B2:E2; 0)
Необходимым условием является то, что значения поиска должны точно соответствовать заголовкам столбцов и быть объединены в том же порядке. Чтобы проследить процесс поиска, выберите первые два аргумента в формуле ПОИСКПОЗ, затем нажмите F9, и вы увидите, что оценивает каждый аргумент:
ПОИСКПОЗ(«СеверПродавец 2»; {«СеверПродавец 1″;»СеверПродавец 2″;»ЮгПродавец 1″;»ЮгПродавец 2»}; 0)
Поскольку « СеверПродавец 2» является вторым элементом в массиве, функция возвращает 2.
Шаг 3.
После этого наша длинная двумерная формула ИНДЕКС ПОИСКПОЗ превращается в такую простую:
=ИНДЕКС(B3:E5; 2; 2)
Шаг 4.
Она возвращает значение на пересечении 2-й строки и 2-го столбца в диапазоне B3:E5, что является значением в ячейке C4.
Вот как формула ИНДЕКС ПОИСКПОЗ помогает выполнить поиск по нескольким условиям в Excel. Я благодарю вас за чтение и надеюсь вновь увидеть вас в нашем блоге.
Еще несколько статей по теме:
Функция
ПОИСК(
)
, английский вариант SEARCH(),
находит первое вхождение одной текстовой строки в другой строке и возвращает начальную позицию найденной строки.
Синтаксис функции
ПОИСК
(
искомый_текст
;
просматриваемая_строка
;[нач_позиция])
Искомый_текст
— текст, который требуется найти.
Просматриваемая_строка
— текст, в которой ищется
Искомый_текст
.
Нач_позиция
— позиция знака в просматриваемой_строке, с которой должен начинаться поиск. Если аргумент
нач_позиция
опущен, то предполагается значение 1.
В аргументе
искомый_текст
можно использовать
подстановочные знаки
— вопросительный знак (?) и звездочку (*). Вопросительный знак соответствует любому знаку; звездочка — любой последовательности знаков. Если нужно найти в тексте вопросительный знак или звездочку, следует поставить перед ними тильду (~).
Если искомый_текст не найден, возвращается значение ошибки #ЗНАЧ!
Функция
ПОИСК()
не учитывает РЕгиСТР букв. Для поиска с учетом регистра следует воспользоваться функцией
НАЙТИ()
.
Примеры
Формула
=ПОИСК(«к»;»Первый канал»)
вернет 8, т.к. буква
к
находится на 8-й позиции слева.
Пусть в ячейке
А2
введена строка
Первый канал — лучший
. Формула
=ПОИСК(СИМВОЛ(32);A2)
вернет 7, т.к. символ пробела (код 32) находится на 7-й позиции.
Формула
=ПОИСК(«#???#»;»Артикул #123# ID»)
будет искать в строке »
Артикул #123# ID
» последовательность из 5 символов, которая начинается и заканчивается на знак #.
Чтобы найти позицию второго вхождения буквы «а» в строке «мама мыла раму» используйте формулу
=ПОИСК(«а»;»мама мыла раму»;ПОИСК(«а»;»мама мыла раму»)+1).
Чтобы определить есть ли третье вхождение буквы «м» в строке «мама мыла раму» используйте формулу
=ЕСЛИ(ДЛСТР(ПОДСТАВИТЬ(«мама мыла раму»;»м»;»»;3))=ДЛСТР(«мама мыла раму»);»Нет третьего вхождения м»;»Есть третье вхождение м»)
Формула
=ПОИСК(«клад?»;»докладная»)
вернет 3, т.е. в слове «докладная» содержится слово из 5 букв, первые 4 из которых
клад
(начиная с третьей буквы слова
докладная
).
Функция
НАЙТИ()
vs
ПОИСК()
Функция
НАЙТИ()
учитывает РЕгиСТР букв и не допускает использование подстановочных знаков. Для поиска без учета регистра, а также для поиска с использованием
подстановочных знаков
пользуйтесь функцией
ПОИСК()
.
Связь с функциями
ЛЕВСИМВ()
,
ПРАВСИМВ()
и
ПСТР()
Функция
ПОИСК()
может быть использована совместно с функциями
ЛЕВСИМВ()
,
ПРАВСИМВ()
и
ПСТР()
.
Например, в ячейке
А2
содержится фамилия и имя «Иванов Иван», то формула
=ЛЕВСИМВ(A2;ПОИСК(СИМВОЛ(32);A2)-1)
извлечет фамилию, а
=ПРАВСИМВ(A2;ДЛСТР(A2)-ПОИСК(СИМВОЛ(32);A2))
— имя. Если между именем и фамилией содержится более одного пробела, то для работоспособности вышеупомянутых формул используйте функцию
СЖПРОБЕЛЫ()
.
В приложении Excel предусмотрено большое разнообразие инструментов для обработки текстовых и числовых данных. Одним из наиболее востребованных является функция ПОИСК. Она позволяет определять в строке, ячейке с текстовой информацией позицию искомой буквенной или числовой комбинации и записывать ее с помощью чисел.
Примеры использования функции ПОИСК в Excel
Для нахождения позиции текстовой строки в другой аналогичной применяют ПОИСК и ПОИСКБ. Расчет ведется с первого символа анализируемой ячейки. Так, если задать функцию ПОИСК “л” для слова «апельсин» мы получим значение 4, так как именно такой по счету выступает заданная буква в текстовом выражении.
Функция ПОИСК работает не только для поиска позиции отдельных букв в тексте, но и для целой комбинации. Например, задав данную команду для слов «book», «notebook», мы получим значение 5, так как именно с этого по счету символа начинается искомое слово «book».
Используют функцию ПОИСК наряду с такими, как:
- НАЙТИ (осуществляет поиск с учетом регистра);
- ПСТР (возвращает текст);
- ЗАМЕНИТЬ (заменяет символы).
Важно помнить, что рассматриваемая команда ПОИСК не учитывает регистра. Если мы с помощью нее станем искать положение буквы «а» в слове «Александр», в ячейке появится выражение 1, так как это первый символ в анализируемой информации. При задании команды НАЙТИ «а» в том же отрезке текста, мы получим значение 6, так как именно 6 позицию занимает строчная «а» в слове «Александр».
Кроме того, функция ПОИСК работает не для всех языков. От команды ПОИСКБ она отличается тем, что на каждый символ отсчитывает по 1 байту, в то время как ПОИСКБ — по два.
Чтобы воспользоваться функцией, необходимо ввести следующую формулу:
=ПОИСК(нужный_текст;анализируемый_текст;[начальная_позиция]).
В этой формуле задаваемые значения определяются следующим образом.
- Искомый текст. Это числовая и буквенная комбинация, позицию которой требуется найти.
- Анализируемый текст. Это тот фрагмент текстовой информации, из которого требуется вычленить искомую букву или сочетание и вернуть позицию.
- Начальная позиция. Данный фрагмент необязателен для ввода. Но, если вы желаете найти, к примеру, букву «а» в строке со значением «А015487.Мужская одежда», то необходимо указать в конце формулы 8, чтобы анализ этого фрагмента проводился с восьмой позиции, то есть после артикула. Если этот аргумент не указан, то он по умолчанию считается равным 1. При указании начальной позиции положение искомого фрагмента все равно будет считаться с первого символа, даже если начальные 8 были пропущены в анализе. То есть в рассматриваемом примере букве «а» в строке «А015487.Мужская одежда» будет присвоено значение 14.
При работе с аргументом «искомый_текст» можно использовать следующие подстановочные знаки.
- Вопросительный знак (?). Он будет соответствовать любому знаку.
- Звездочка (*). Этот символ будет соответствовать любой комбинации знаков.
Если же требуется найти подобные символы в строке, то в аргументе «искомый_текст» перед ними нужно поставить тильду (~).
Если искомый текст не был найден приложением или начальная позиция установлена меньше 0, больше общего количества присутствующих символов, в ячейке отобразиться ошибка #ЗНАЧ.
Если «искомый_текст» не найден, возвращается значение ошибки #ЗНАЧ.
Пример использования функции ПОИСК и ПСТР
Пример 1. Есть набор текстовой информации с контактными данными клиентов и их именами. Информация записана в разных форматах. Необходимо найти, с какого символа начинается номер телефона.
Введем исходные данные в таблицу:
В ячейке, которая будет учитывать данные клиентов без телефона, введем следующую формулу:
=ПОИСК(“, тел.”;адрес_анализируемой_ячейки).
Нажмем Enter для отображения искомой информации:
Далее мы можем использовать любые другие функции для отображения представленной информации в удобном формате:
На рисунке видно, как с помощью формулы из двух функций ПСТР и ПОИСК мы вырезаем фрагмент текста из строк разной длины. Притом разделяем текстовый фрагмент в нужном месте так, чтобы отделить ее от номера телефона.
Пример формулы ПОИСК и ЗАМЕНИТЬ
Пример 2. Есть таблица с текстовой информацией, в которой слово «маржа» нужно заменить на «объем».
Откроем книгу Excel с обрабатываемыми данными. Пропишем формулу для поиска нужного слова «маржа»:
Теперь дополним формулу функцией ЗАМЕНИТЬ:
Чем отличается функция ПОИСК от функции НАЙТИ в Excel?
Функция ПОИСК очень схожа с функцией НАЙТИ по принципу действия. Более того у них фактически одинаковые аргументы. Только лишь названия аргументов отличаются, а по сути и типам значений – одинаковые:
Но опытный пользователь Excel знает, что отличие у этих двух функций очень существенные.
Отличие №1. Чувствительность к верхнему и нижнему регистру (большие и маленькие буквы). Функция НАЙТИ чувствительна к регистру символов. Например, есть список номенклатурных единиц с артикулом. Необходимо найти позицию маленькой буквы «о».
Теперь смотрите как ведут себя по-разному эти две функции при поиске большой буквы «О» в критериях поиска:
Отличие №2. В первом аргументе «Искомый_текст» для функции ПОИСК мы можем использовать символы подстановки для указания не точного, а приблизительного значения, которое должно содержаться в исходной текстовой строке. Вторая функция НАЙТИ не умеет использовать в работе символы подстановки масок текста: «*»; «?»; «~».
Для примера попробуем в этих же исходных строках столбца «наименования» найти приблизительный текст. Для этого укажем следующий вид критерия поиска используя символы подстановки: «н*ая».
Скачать примеры функций ПОИСК и НАЙТИ в Excel
Как видим во втором отличии функция НАЙТИ совершенно не умеет работать и распознавать спецсимволы для подстановки текста в критериях поиска при неточном совпадении в исходной строке.
Поиск и подстановка по нескольким условиям
Постановка задачи
Если вы продвинутый пользователь Microsoft Excel, то должны быть знакомы с функцией поиска и подстановки ВПР или VLOOKUP (если еще нет, то сначала почитайте эту статью, чтобы им стать). Для тех, кто понимает, рекламировать ее не нужно 
— без нее не обходится ни один сложный расчет в Excel. Есть, однако, одна проблема: эта функция умеет искать данные только по совпадению одного параметра. А если у нас их несколько?
Предположим, что у нас есть база данных по ценам товаров за разные месяцы:
Нужно найти и вытащить цену заданного товара (Нектарин) в определенном месяце (Январь), т.е. получить на выходе152, но автоматически, т.е. с помощью формулы. ВПР в чистом виде тут не поможет, но есть несколько других способов решить эту задачу.
Способ 1. Дополнительный столбец с ключом поиска
Это самый очевидный и простой (хотя и не самый удобный) способ. Поскольку штатная функция ВПР (VLOOKUP) умеет искать только по одному столбцу, а не по нескольким, то нам нужно из нескольких сделать один!
Добавим рядом с нашей таблицей еще один столбец, где склеим название товара и месяц в единое целое с помощью оператора сцепки (&), чтобы получить уникальный столбец-ключ для поиска:
Теперь можно использовать знакомую функцию ВПР (VLOOKUP) для поиска склеенной пары НектаринЯнварь из ячеек H3 и J3 в созданном ключевом столбце:
Плюсы: Простой способ, знакомая функция, работает с любыми данными.
Минусы: Надо делать дополнительный столбец и потом, возможно, еще и прятать его от пользователя. При изменении числа строк в таблице — допротягивать формулу сцепки на новые строки (хотя это можно упростить применением умной таблицы).
Способ 2. Функция СУММЕСЛИМН
Если нужно найти именно число (в нашем случае цена как раз число), то вместо ВПР можно использовать функцию СУММЕСЛИМН (SUMIFS), появившуюся начиная с Excel 2007. По идее, эта функция выбирает и суммирует числовые значения по нескольким (до 127!) условиям. Но если в нашем списке нет повторяющихся товаров внутри одного месяца, то она просто выведет значение цены для заданного товара и месяца:
Плюсы: Не нужен дополнительный столбец, решение легко масштабируется на большее количество условий (до 127), быстро считает.
Минусы: Работает только с числовыми данными на выходе, не применима для поиска текста, не работает в старых версиях Excel (2003 и ранее).
Способ 3. Формула массива
О том, как спользовать связку функций ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH) в качестве более мощной альтернативы ВПР я уже подробно описывал (с видео). В нашем же случае, можно применить их для поиска по нескольким столбцам в виде формулы массива. Для этого:
- Выделите пустую зеленую ячейку, где должен быть результат.
- Введите в строке формул в нее следующую формулу:
- Нажмите в конце не Enter, а сочетание Ctrl+Shift+Enter, чтобы ввести формулу не как обычную, а как формулу массива.
Как это на самом деле работает:
Функция ИНДЕКС выдает из диапазона цен C2:C161 содержимое N-ой ячейки по порядку. При этом порядковый номер нужной ячейки нам находит функция ПОИСКПОЗ. Она ищет связку названия товара и месяца (НектаринЯнварь) по очереди во всех ячейках склеенного из двух столбцов диапазона A2:A161&B2:B161 и выдает порядковый номер ячейки, где нашла точное совпадение. По сути, это первый способ, но ключевой столбец создается виртуально прямо внутри формулы, а не в ячейках листа.
Плюсы: Не нужен отдельный столбец, работает и с числами и с текстом.
Минусы: Ощутимо тормозит на больших таблицах (как и все формулы массива, впрочем), особенно если указывать диапазоны «с запасом» или сразу целые столбцы (т.е. вместо A2:A161 вводить A:A и т.д.) Многим непривычны формулы массива в принципе (тогда вам сюда).
Ссылки по теме
- Как искать и подставлять данные с помощью функции ВПР (VLOOKUP)
- Что такое формулы массива и как их использовать
- Как использовать связку функций ИНДЕКС и ПОИСКПОЗ вместо ВПР
- Как извлечь сразу все значения, а не только первое с помощью ВПР