Как найти значение в тексте эксель

ПОИСК, ПОИСКБ (функции ПОИСК, ПОИСКБ)

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 только текст, заключенный в двойные кавычки.

босс

Нужна дополнительная помощь?

Нужны дополнительные параметры?

Изучите преимущества подписки, просмотрите учебные курсы, узнайте, как защитить свое устройство и т. д.

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

Программа Excel ориентирована на ускоренные расчеты. Зачастую документы здесь состоят из большого ко…

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

Как искать в Excel слова, текст, ячейки и значения в таблицах

Поиск слов

Документы часто имеют много страниц, тогда встает вопрос о том, как в Еxcel найти слово. Сделать это  иногда становится проблематично. Для упрощения этой задачи существует специальная функция поиска. Чтобы ею воспользоваться, необходимо выполнить следующий алгоритм действий:

  • запустить программу Excel;
  •  проверить активность таблицы, щелкнув по любой из ячеек;
  •  нажать комбинацию клавиш «Ctrl + F»;
  •  в строке «Найти» появившегося окна ввести искомое слово;
  •  нажать «Найти».

как искать в экселе

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

Существует также способ нестрогого поиска, который подходит для ситуаций, когда искомое слово помнится частично. Он предусматривает использование символов-заменителей (джокерные символы). В Excel их всего два:

  •  «?» – подразумевает любой отдельно взятый символ;
  •  «*» – обозначает любое количество символов.

 Примечательно, при поиске вопросительного знака или знака умножения дополнительно впереди ставится тильда («~»). При поиске тильды, соответственно – две тильды.

как в excel найти слово

 Алгоритм неточного поиска слова:

  •  запустить программу;
  •  активировать страницу щелчком мыши;
  •  зажать комбинацию клавиш «Ctrl + F»;
  •  в строке «Найти» появившегося окна ввести искомое слово, используя вместо букв, вызывающих сомнения, джокерные символы;
  •  проверить параметр «Ячейка целиком» (он не должен быть отмеченным);
  •  нажать «Найти все».

 Все слова, подходящие под параметры поиска, подсветятся, поэтому их легко будет увидеть и проанализировать.

как найти слово в таблице в excel

Поиск нескольких слов

Не зная, как найти слово в таблице в Еxcel, следует также воспользоваться функцией раздела «Редактирование» – «Найти и выделить». Далее нужно отталкиваться от искомой фразы:

  •  если фраза точная, введите ее и нажмите клавишу «Найти все»;
  •  если фраза разбита другими ключами, нужно при написании ее в строке поиска дополнительно проставить между всеми словами «*».

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

как искать по словам в excel

Поиск ячеек

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

Для поиска ячеек с формулами выполняются следующие действия.

  1. В открытом документе выделить ячейку или диапазон ячеек (в первом случае поиск идет по всему листу, во втором – в выделенных ячейках).
  2. Во вкладке «Главная» выбрать функцию «Найти и выделить».
  3. Обозначить команду «Перейти».
  4. Выделить клавишу «Выделить».
  5. Выбрать «Формулы».
  6. Обратить внимание на список пунктов под «Формулами» (возможно, понадобится снятие флажков с некоторых параметров).
  7. Нажать клавишу «Ок».

как искать в excel

 Для поиска объединенных ячеек потребуется выполнение следующих манипуляций.

  1. Перейти во вкладку «Главная».
  2. Выбрать функцию «Найти и выделить».
  3. Нажать на команду «Найти».
  4. Перейти в «Параметры» и выбрать «Формат».
  5. Здесь выделить функцию «Выравнивание», поставить отметку «Объединить ячейки».
  6. Нажать на «Ок».
  7. Нажать на кнопку «Найти все» и проанализировать список ячеек, которые объединены на соответствующем листе.

При нажимании кнопкой мыши на элемент в списке происходит выделение объединенной ячейки на листе. Дополнительно доступна функция «Отменить объединение ячеек».

как найти текст в excel

 Выполнение представленных выше действий приводит к нахождению всех объединенных ячеек на листе и при необходимости отмене данного свойства. Для поиска скрытых ячеек проводятся следующие действия.

  1. Выбрать лист, требующий анализа на присутствие скрытых ячеек и их нахождения.
  2. Нажать клавиши «F5_гт_
    Special».
  3. Нажать сочетание клавиш «CTRL + G_гт_ Special».

Можно воспользоваться еще одним способом для поиска скрытых ячеек:

  1. Открыть функцию «Редактирование» во вкладке «Главная».
  2. Нажать на «Найти».
  3. Выбрать команду «Перейти к разделу». Выделить «Специальные».
  4. Попав в группу «Выбор», поставить галочку на «Только видимые ячейки».
  5. Нажать кнопку «Ок».

как искать слово в excel

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

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

Чтобы найти ячейки, для которых применено условное форматирование:

  • нажать на ячейку, не предусматривающую условное форматирование;
  • выбрать функцию «Редактирование» во вкладке «Главная»;
  • нажать на кнопку «Найти и выделить»;
  • выделить категорию «Условное форматирование».

Как искать в Excel слова, текст, ячейки и значения в таблицах

    Чтобы найти ячейки, для которых применено одинаковое условное форматирование:

    • выбрать ячейку, предусматривающую условное форматирование, требующую поиска;
    • выбрать группу «Редактирование» во вкладке «Главная»;
    • нажать на кнопку «Найти и выделить»;
    • выбрать категорию «Выделить группу ячеек»;
    • установить свойство «Условные форматы»;
    • напоследок нужно зайти в группу «Проверка данных» и установить аналогичный пункт.

    Как искать в Excel слова, текст, ячейки и значения в таблицах

    Поиск через фильтр

    Чтобы узнать, как в Еxcel найти слово при использовании фильтра, потребуется изучить следующий алгоритм действий:

    • выделить заполненную ячейку;
    • во вкладке «Главная» выбрать функцию «Сортировка»;
    • нажать на кнопку «Фильтр»;
    • открыть выпадающее меню;
    • ввести искомый запрос;
    • нажать кнопку «Ок».

    Как искать в Excel слова, текст, ячейки и значения в таблицах

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

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

    Как работает функция ПОИСК в Excel

    Синтаксис данной функции выглядит следующим образом:

    Синтаксис ПОИСК

    У нас есть слово «Excel». В этом примере нужно найти положение буквы «Х» в слове. Функция возвратит значение 2, поскольку буква находится по счету на втором месте в искомых данных:

    буква 2

    Несмотря на то, что искомая буква «Х» находится в верхнем регистре, функция нашла ее аналог в нижнем регистре и выдала результат. В этом и есть отличие с функцией НАЙТИ – она обращает внимание на соответствие регистров.

    Также мы можем искать часть слова или слово в искомой области, например найти слово «Excel» в «Exceltable» и «table» во фразе «Excel table». В первом случае в результате мы получим 1, потому что слово «Excel» начинается с первого символа. Во втором случае у нас будет результат 7, потому что «table» начинается с седьмого символа:

    table 7

    Аргумент начальная_позиция используем, когда нужно отсчитать положение символа, начиная с которого возвратится искомое значение. Например, нужно отследить с какой позиции начинается буквенное значение кода. Если не указать номер позиции, у нас возвратится число «2», поскольку буква «о» в ячейке А8 находится второй по порядку:

    буквенное значение кода 2

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

    

    Функция ПОИСКа значения в столбце Excel

    В данном примере будет использоваться формула комбинации функции ПОИСК с функциями: ЛЕВСИМВ, ПРАВСИМВ, ДЛСТР. Рассмотрим поэтапно пример, где мы сможем извлекать части фраз с текста, из которого получим искомое значение. У нас есть товар и артикул товара. Наше задание – возвратить только буквенную часть названия артикула. Для этого в ячейке C12 начинаем писать формулу. Для получения результата нам нужна функция ЛЕВСИМВ.

    1. Первый аргумент – текст, в котором происходит поиск (ячейка B12).
    2. Второй аргумент – нужна длина искомого слова. В первом артикуле она равна 3, а в последующих меняется, поэтому используем формулу ПОИСК(«_»;B12).

    Формула с аргументами («_»;B12) указала, что будут возвращаться те символы, которые расположены перед символом нижнего подчеркивания. Проверим наш результат:

    символы расположены перед _

    Функция ПОИСК возвратила число 4 (порядочное положение знака нижнего подчеркивания), и в качестве второго аргумента функции ЛЕВСИМВ указала какие символы будут находиться в ячейке С12. Пока что это не совсем то, что необходимо получить – знак «_» в идеале должен отсутствовать. Для этого немного подкорректируем формулу: перед вторым аргументом (формулой ПОИСК) отнимаем единицу, этим мы указали что вывод символов будет без знака нижнего подчеркивания (4-1):

    формула ПОИСК

    Поскольку формула у нас динамическая, копируем ее до конца столбца и наблюдаем результат нашей работы – по каждому артикулу мы получили буквенное значение независимо от количества букв:

    результат работы

    Если вдруг вам понадобится в этой же таблице изменить артикул – функция среагирует на изменения корректно и автоматически возвратит текстовое значение заменённого артикула. Например для товара «Бокал» будет буквенная часть «К», для «Тарелки» – «М», для «Стола» – ADCDE:

    текстовое значение

    Теперь рассмотрим пример, где будем извлекать символы не ПЕРЕД нижним подчеркиванием, а ПОСЛЕ. В этом нам поможет функция ДЛСТР. Она помогает узнать длину текстовой строки. В ячейке С20 пишем формулу:

    функция ДЛСТР

    В результате она возвратила нам длину артикула товара «Бокал». Скопируем формулу до конца столбца и в следующем этапе в ячейке Е20 напишем формулу ПОИСК. Нижнее подчеркивание – это искомое значение (аргумент 1), возвращаемое значение – позиция порядковый номер положения нижнего подчеркивания. Копируем до конца столбца:

    порядковый номер символа _

    Затем нам нужен столбец, где мы от длины строки отнимаем позицию нижнего подчеркивания (9 – 4) и копируем формулу до конца столбца. То есть этот столбец содержит длину числового значения артикула (значения ПОСЛЕ нижнего подчеркивания):

    значения после символа _

    Теперь в ячейке F20 пишем функцию ПРАВСИМВ, которая возвратит нам текст, часть фразы, которую мы запрашимаем. Первый аргумент функции – это ячейка, которую проверяет формула, а второй – длина возвращаемого значения:

    формула с ПРАВСИМВ

    Функция возвратила нам числовое значение артикула товара. Копируем до конца столбца и получаем результат по каждому товару:

    результат 2

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

    пример поэтапно

    В этом примере мы сделали то же самое, что и ранее, только все операции сделали в одной формуле: нашли длину текста, отняли длину текста после знака «_» и возвратили эту длину функцией ПРАВСИМВ.

    Как использовать функцию ПОИСК с функцией ЕСЛИ

    Рассмотрим пример, где будем проверять частичное совпадение текста в проверяемом тексте. У нас есть несколько адресов и нам нужно знать локальные они или нет:

    как проверять частичное совпадение

    Если почтовый индекс начинается с СВ2, тогда это локальный адрес. Мы не можем использовать функцию ЕСЛИ в её обычном выражении, потому что в ячейках много другого текста. Нам нужно знать содержит ли ячейка часть «СВ2». Чтобы лучше понимать логику построения функции, будем делать это от простого к более сложному выражению:

    1. Строим функцию ПОИСК: 1-й аргумент – «СВ2», 2-й аргумент – ячейка А37, копируем до конца столбца. Так мы указали что и где будем искать. Там, где нет текста СВ2, возвратилась ошибка. Но сейчас у нас есть только номер позиции текста СВ2. А функция ЕСЛИ будет искать идентичное совпадение. То есть она будет искать число 11 в тексте ячейки А37, которого у нас, конечно, не будет нигде.
    2. формула с ЕСЛИ

    3. Формулу функции ПОИСК вкладываем в функцию ЕЧИСЛО (имеет только 1 аргумент), которая будет указателем в дальнейшем для ЕСЛИ, что результатом функции ПОИСК является число (как сейчас, у нас 11):
    4. формула с ЕЧИСЛО

    5. Полученную формулу вкладываем в ЕСЛИ, указывая, что для значения ИСТИНА у нас будет слово «Местный», а для значения ЛОЖЬ – «Далеко». Копируем до конца столбца:
    6. значения ИСТИНА Местный ЛОЖЬ Далеко

    download file Скачать примеры формул с функцией ПОИСК в Excel

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


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

    Пусть Исходный список значений (например, перечень инструментов) находится в диапазоне

    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 – примеры

    Что делает функция ПОИСК?

    Эта функция аналогична функции НАЙТИ и так же ищет подстроку в строке. Когда искомое найдено, отображается его позиция в тексте в виде числа.

    Отличие от функции НАЙТИ в том, что ПОИСК не принимает в расчет регистр текста. Как искомого, так и того, в котором мы ищем. Также она поддерживает подстановочные операторы.

    У обеих функций есть процедура-аналог Найти и Заменить – как у процедуры, у нее есть свои преимущества и недостатки.

    Синтаксис

    =ПОИСК(ИскомыйТекст;СтрокаВКоторойИщем;[СтартоваяПозиция])
    • ИскомыйТекст — символ или сочетание, которое ищем
    • СтрокаВКоторойИщем — ячейка, текстовое значение или любое возвращаемое другой функцией выражение.
    • Стартовая позиция — опциональный параметр, при отсутствии поиск происходит с первого символа

    Если текст содержит более одного вхождения, возвращается позиция первого.
    Третий (опциональный) параметр используется для поиска с определенной позиции в тексте и по умолчанию равен 1.

    Если искомое не найдено в тексте, функция возвращает ошибку #ЗНАЧ.

    Форматирование

    При поиске дат функция ПОИСК, как и все текстовые функции, воспринимает их как числа, поэтому для корректного поиска может понадобиться функция ТЕКСТ.

    При этом логические значения ИСТИНА и ЛОЖЬ конвертируются в текст, соответствующий их написанию.

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

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

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

    =НЕ(ЕОШ(ПОИСК(паттерн;текст)))
    
    поиск символа в ячейке - пример таблицы
    Поиск символа в ячейке формулой ПОИСК

    Извлечь первое слово

    В этом простейшем примере извлекаем первое слово из ячейки с помощью комбинации — функция ЛЕВСИМВ + функция ПОИСК. Поскольку пробел — регистронезависимый символ, для этого случая можно использовать и функцию НАЙТИ.

    Функция поиск в формуле извлечения текста до пробела
    Определяем номер позиции первого пробела и возвращаем символы до этой позиции

    Таблица выше была использована для извлечения имени из строки с именем и фамилией.

    1. ПОИСК возвращает позицию пробела между именем и фамилией.
    2. Длина имени вычисляется как позиция пробела минус 1.
    3. Функция ЛЕВСИМВ извлекает имя на основе его длины.

    Другие примеры использования

    Найти первую цифру в ячейке:

    =МИН(ЕСЛИОШИБКА(ПОИСК({0:9:8:7:6:5:4:3:2:1};A1);1000))
    

    Найти первую цифру в ячейке и вернуть все, что перед ней:

    =ЛЕВСИМВ(A1,МИН(ЕСЛИОШИБКА(ПОИСК({0:9:8:7:6:5:4:3:2:1};A1);1000))-1)
    

    Узнать, содержит ли ячейка латиницу. Формула вернет «ИСТИНА» или «ЛОЖЬ»:

    =СЧЁТ(ПОИСК({"a":"b":"c":"d":"e":"f":"g":"h":"i":"j":"k":"l":"m":"n":"o":"p":"q":"r":"s":"t":"u":"v":"w":"x":"y":"z"};A1))>0
    

    Найти кириллицу в тексте аналогичным путем:

    =СЧЁТ(ПОИСК({"а":"б":"в":"г":"д":"е":"ё":"ж":"з":"и":"й":"к":"л":"м":"н":"о":"п":"р":"с":"т":"у":"ф":"х":"ц":"ч":"ш":"щ":"ъ":"ы":"ь":"э":"ю":"я"};A1))>0
    

    Функция ПОИСК в формуле массива

    Примеры выше, где буквы перечислены явно в строковом массиве, занимает довольно много места. Буквы при этом идут подряд, что наводит на мысль, что их можно как-то иначе выразить как диапазон.

    И действительно, это возможно с помощью комбинации с функциями СТРОКА и ПОИСК:

    {=СЧЁТ(ПОИСК(СИМВОЛ(СТРОКА(65:90));A1))>0}
    

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

    Что происходит в этой формуле?

    • Функция СТРОКА с численным аргументом “65:90” возвращает массив чисел с 65 по 90 включительно. Как раз в этом диапазоне в таблице ASCII находятся все символы латиницы;
    • Функция СИМВОЛ возвращает для каждого числового значения в этом массиве его символ, таким образом создавая массив латинских символов;
    • Функция ПОИСК производит поиск каждого из этих символов в строке и возвращает либо число, либо ошибку, таким образом создавая массив чисел и ошибок
    • Функция СЧЁТ считает числовые значения в полученном массиве. Если результат больше нуля, значит, хотя бы один символ латиницы был найден. Если нет (все поиски вернули ошибку), значит, не был

    Аналогичная формула для кириллицы:

    {=СЧЁТ(ПОИСК(СИМВОЛ(СТРОКА(192:223));A1))>0}
    

    Подробнее о поиске и извлечении кириллицы и латиницы в Excel можно почитать тут:

    Найти латиницу или кириллицу в тексте

    Есть еще множество комбинаций функции ПОИСК с другими функциями Excel, смотрите разделы:
    Функция ИЛИ
    Функция И
    Функция ЗНАЧЕН
    Удалить первое слово в ячейке Excel


    Смотрите также по теме:

    ОБНАРУЖИТЬ (функционал надстройки !SEMTools)

    Регулярные выражения в Excel

    Найти определенные символы в Excel

    Найти слова в ячейках

    Найти числа в текстовых ячейках

    Понравилась статья? Поддержите ее автора!
    Набор инструментов !SEMTools поможет решить множество задач без ввода сложных формул и сэкономит ваше время. И вам полезно, и автору приятно!

    Добавить комментарий