Поиск эксель функция: ПОИСК, ПОИСКБ (функции ПОИСК, ПОИСКБ)

Поиск с конца строки в Excel




Готовые решения »


Надстройка VBA-Excel »
Поиск с конца строки

Функция КОНЕЦСТРОКИ из надстройки поможет быстро вернуть конец строки после заданного текста.

Добавить в Excel

Описание функции

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

Вот этот набор функций, не очень то очевидно, согласитесь:


 =ПРАВСИМВ(A1;ДЛСТР(A1)-НАЙТИ(«Заданный текст»;A1;1)-ДЛСТР(«Заданный текст»))

Для удобства в надстройку VBA-Excel добавлена функция КОНЕЦСТРОКИ. Она выполняет поиск заданного текста с конца строки и возвращает текст от искомой позиции до конца строки. И интерфейс у нее намного понятнее:

Функция =КОНЕЦСТРОКИ(ТЕКСТ; НАЙТИ; [Старт]; [Регистр]) имеет 4 аргумента:

  • ТЕКСТ — Исходный текст, в котором необходимо найти начало строки.

  • НАЙТИ — Подстрока, до которой будет возвращаться начало строки

  • [Старт] — Необязательный параметр. Позиция внутри исходного текста, с которой начинается поиск подстроки. По умолчанию параметр равен 1.

  • [Регистр] — Необязательный аргумент, указывающий необходимость учета регистра. По умолчанию равен 1 — регистр учитывается. Укажите значение 0 если не хотите учитывать регистр при сравнении.

Пример 1

Найти текст до слова СТОП. Обратите внимание не важно в какой части текста находится заданное слово.

Пример 2

Чтобы вернуть текст без первого слова укажите пробел в параметре НАЙТИ.

Пример 3

Можно начинать поиск с заданной позиции (аргумент Старт). Может понадобиться в случае если стоп-слово встречается не один раз.

Пример 4

В предыдущих примерах регистр искомого текста учитывался. Для того чтобы заглавные и строчные буквы НЕ учитывались при поиске, установите параметр Регистр = 0.

Код на VBA


Function КОНЕЦСТРОКИ(ByVal ТЕКСТ As String, ByVal НАЙТИ As String, _
                    Optional ByVal Старт As Long = 1, Optional ByVal Регистр As Long = 1) As String
    If Регистр = 1 Then
        КОНЕЦСТРОКИ = Right(ТЕКСТ, Len(ТЕКСТ) - InStr(Старт, ТЕКСТ, НАЙТИ) - Len(НАЙТИ) + 1)
    Else
        КОНЕЦСТРОКИ = Right(ТЕКСТ, Len(ТЕКСТ) - InStr(Старт, ТЕКСТ, НАЙТИ, vbTextCompare) - Len(НАЙТИ) + 1)
    End If
End Function

Надстройка

VBA-Excel

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

Добавить в Excel

Рекомендуем к прочтению

Формула СЦЕПИТЬДИАПАЗОН

Подсчет количества определенных символов ячейке excel

Сравнение текста с шаблоном в Excel

Как разделить текст в Excel на подстроки

Извлечь текст из строки Excel

Вернуть текст до найденного слова или символа

Комментарии:

Please enable JavaScript to view the comments powered by Disqus. comments powered by Disqus

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

Excel – программа для работы с электронными таблицами. Документы в этой программе могут быть объемными с множеством листов. Поэтому для удобного поиска нужного слова, фразы, таблицы, ячейки или формулы существует специальный инструмент, пользоваться которым несложно.

Поиск в Excel

Самый простой и быстрый способ искать по слову, фразе, формуле и т.д в документе это использовать комбинацию клавиш Ctrl и F одновременно. Появится следующее окно.

Здесь понадобится ввести искомое слово, фразу и т.д.

Второй вариант поиска в Excel — на главной странице нажать вкладку «Найти и выделить». Выбираем «Найти», вводим нужные символы и запускается поиск.

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

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

В поле можно использовать подстановочные знаки * и ?.

Обычно их применяют в таких ситуациях:

  1. * — может быть использована для замены букв. К примеру, если ввести «к*т», то найдутся все слова, которые начинаются на букву «к» и заканчиваются на «т», кит, кот и т.д. Звездочка заменяет как один символ, так и любое количество. Т.е. в нашем примере найдутся и слова кабинет, кабриолет и т.д.
  2. ? — такой подстановочный знак заменяет только одну букву. Например, «к?т», выйдут слова, которые состоят из трех букв «кот», «кит».

Настройки поиска

Если необходимо установить определенные настройки, то в окне необходимо кликнуть «Параметры».

Тут можно задать следующие настройки:

  • в строке «Искать» можно ограничить поиск только листом;
  • в строке «Просматривать» можно выбрать вариант просмотра по строкам или по столбцам;
  • строка «Область» позволяет выбрать искомые символы: формула либо значение;
  • отметив галкой «Учитывать регистр» производится нахождение искомой комбинации с учетом регистра;
  • если необходимо увидеть ячейку, которая содержит только заданные символы, нужно отметить «Ячейка целиком»;
  • во вкладке «Формат» можно выбрать искомое форматирование.

Нажимая кнопку «Заменить», можно сразу заменить найденное на указанные значения.

В строку «Найти» нужно ввести искомую комбинацию значений, а в строке «Заменить» те символы, на которые следует заменить найденные.

Поиск по указанному интервалу

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

Этим способом ограничивается диапазон для нахождения комбинации.

Порядок действий:

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

Поиск по нескольким условиям

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

Приведем пример поиска нескольких значений одновременно. В таблице указана разная цена различного гранита. Необходимо с помощью функции ВПР определить цену гранита 7.

Составим таблицу.

Формула будет в ячейке Е4, но можно использовать любую другую. Затем придерживаемся алгоритма:

  1. В ячейке ставим знак равенства и прописываем функцию: «=ВПР(».
  2. Аргумент 1 это Гранит7. Пишем «ВПР(«Гранит7»».
  3. Аргумент 2 – это ячейки А4:А20. Формула получается такой: «ВПР(«Гранит7»; А4:А20;».
  4. Аргумент 3, цифра 2, т.к. ищем значение во втором столбце. «ВПР(«Гранит7»; А4:А20;2».
  5. Аргумент 4, дает понять функции, какое значение нужно искать, точное или приблизительное. Оно может быть ИСТИНА или ЛОЖЬ. ИСТИНА – приблизительно совпавшие значения, а ЛОЖЬ – точные. Мы ищем точные. Выглядит запись так: «ВПР(«Гранит7»; А4:А20;2;ЛОЖЬ)» или «=ВПР(A10;A4:B20;2;ЛОЖЬ)».
  6. Кликаем по Enter и получаем искомое.

Аналогичным способом можно добавлять условия в более сложных таблицах.

К примеру, добавим в таблицу столбец с месяцем. Попробуем увидеть в каком месяце  Гранит17 стоит 1016.

Составляем формулу, добавляя новые значения. Аргументом 3 будет цифра 3.

Нажимаем ввод и получаем  месяц «май».

Функция поиск в Excel

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

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

Пример: найдем положение буквы «а» в слове Гранит6. Введем формулу =НАЙТИ(«а»;A9;3) и получим 3, т.е. «а» третья буква.

Теперь рассмотрим функцию ПОИСК. В данной формуле можно использовать подстановочные символы (? — один, * — несколько). Например, найдем в той же ячейке положение цифры 6. Введем формулу =ПОИСК(«6»;A9), получаем 8.

Формула поиска в Excel | Как использовать формулу поиска в Excel?

Формула поиска в Excel (оглавление)

  • Формула ПОИСКА в Excel
  • Как использовать формулу ПОИСК в Excel?
  • Функция поиска является одной из наиболее важных встроенных функций MS Excel. Он используется для поиска одной строки во второй строке; простыми словами, он найдет искомый текст в строке. Функция поиска позволяет использовать подстановочные знаки (например: ??, *, ~) и не учитывает регистр.
  • Например, если первой строкой является «база», а второй строкой является «база данных», функция поиска вернет число 5, начиная с пятой буквы во второй строке.

Синтаксис формулы поиска в Excel

ПОИСК ()  — возвращает целочисленное значение, представляющее позицию строки поиска во второй строке. Есть три параметра — find_text, inside_text, [start_num] .

Аргумент в функции ПОИСК

  • find_text : Это обязательный параметр, строка, которую пользователь хочет ПОИСК.
  • внутри_текста : Это обязательный параметр, строка, в которой пользователь хочет ПОИСК.
  • find_text : Это необязательный параметр, номер символа, из которого пользователь хочет выполнить поиск в в пределах_текста.

Как использовать формулу ПОИСКА в Excel?

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

Вы можете скачать этот шаблон Формулы ПОИСКА Excel здесь – Формула ПОИСКА Шаблон Excel

Пример №1 – Использование формулы ПОИСК в тексте

Откройте MS Excel, перейдите на Лист1, где пользователь хочет ПОИСК по тексту.

Создайте один заголовок столбца для результата ПОИСК, чтобы отобразить результат функции в столбце C.

Нажмите на ячейку C2 и примените формулу ПОИСК.

Теперь запросит поиск текста; выберите текст для поиска, доступный в ячейке B2.

Теперь он будет запрашивать в тексте, откуда пользователь хочет искать текст, доступный в ячейке B2.

Теперь он запросит начальное число, которое по умолчанию равно 1, поэтому мы дадим 1>> запись в ячейке C2.

Нажмите клавишу Enter.

Перетащите ту же формулу в другую ячейку столбца C, чтобы узнать результат ПОИСКА.

Резюме примера 1:

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

Пример 2. Использование функции ПОИСК для подстановочных знаков

В таблице есть некоторые данные, содержащие подстановочные знаки (например: ??, *, ~), а также задана строка поиска, которую пользователь хочет найти в приведенные данные.

Давайте посмотрим, как функция ПОИСК может решить эту проблему.

Откройте MS Excel, перейдите на Лист2, где пользователь хочет ИСКАТЬ текст.

Создайте один заголовок столбца для результата ПОИСК, чтобы отобразить результат функции в столбце C.

Нажмите на ячейку C2 и примените формулу ПОИСК.

Теперь запросит поиск текста; выберите текст для поиска, доступный в ячейке B2.

Теперь он будет запрашивать в тексте, откуда пользователь хочет искать текст, доступный в ячейке B2.

Теперь он запросит начальное число, которое по умолчанию равно 1, поэтому мы дадим 1 >> запись в ячейку C2.

Нажмите клавишу Enter.

Перетащите ту же формулу в другую ячейку столбца C, чтобы узнать результат ПОИСКА.

Резюме примера 2:

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

Пример #3 – Использование функции ПОИСК с помощью функции ВЛЕВО

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

Открыть MS Excel; перейдите на лист 3, где пользователь хочет получить имена всех сотрудников.

Создайте один заголовок столбца для результата ПОИСКА, чтобы отобразить результат функции в столбце B.

Щелкните ячейку B2 и примените первую левую формулу.

Теперь выберите ячейку B2, где будет применяться ЛЕВАЯ формула.

Теперь примените формулу ПОИСКА.

Теперь запросит поиск текста; пользователь будет искать первый пробел.

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

Теперь он запросит начальное число, которое по умолчанию равно 1, поэтому мы дадим 1 >> запись в ячейку B2.

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

Нажмите клавишу Enter.

Перетащите ту же формулу в другую ячейку столбца B, чтобы узнать результат ПОИСКА.

Резюме примера 3:

Поскольку пользователь хочет узнать имя сотрудника. Того же он добился левой и функцией поиска, которая доступна в колонке D как результат поиска.

Что нужно помнить о формуле поиска в Excel

  • Функция поиска возвращает целочисленное значение. Пользователь может использовать эту функцию с другими формулами и функциями.
  • Функция поиска позволяет использовать подстановочные знаки (например: ??, *, ~) без учета регистра. Если пользователь хочет выполнить поиск с учетом регистра, используйте функцию НАЙТИ.
  • Если нет соответствия для find_search в пределах_поиска, будет выдано сообщение #VALUE! Ошибка.
  • Если start_num больше длины строки inside_serach или не больше нуля, то будет возвращено #VALUE! Ошибка.

Рекомендуемые статьи

Это руководство по формулам поиска в Excel. Здесь мы обсуждаем, как использовать формулы поиска в Excel, а также практические примеры и загружаемый шаблон Excel. Вы также можете просмотреть другие наши рекомендуемые статьи —

  1. Функция ПОИСКА Excel
  2. Окно поиска Excel
  3. Поиск текста в Excel
  4. Базовые формулы Excel

ПОИСК в Excel (формула, примеры)

Функция ПОИСК в Excel (оглавление)

  • ПОИСК в Excel
  • Как использовать функцию ПОИСК в Excel?

Функция поиска работает по тому же принципу, что и функция «Найти», где она возвращает местоположение или позицию текстовой строки в другой ячейке. Если мы увидим синтаксис функции поиска, мы увидим, что она возвращает позицию текста, начинающуюся с чего-либо в пределах выбранного текстового диапазона. Поэтому нам просто нужно выбрать текст, который мы хотим найти, и текст в пределах диапазона и начальной позиции (если требуется).

Формула ПОИСКА в Excel

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

Find_text: Это текст, который вы хотите найти или Символ или текст, который вы ищете

Или

Это подстрока, которую вы хотите найти или выполнить поиск в строке или полном тексте

Примечание: В некоторых случаях подстановочные знаки (? *), т. е. вопросительный знак и звездочка, будут присутствовать в клетки; в этом случае используйте тильду (~) перед ~? & ~* искать? & *

  • Within_text: Укажите позицию символа или подстроки, с которой или где вы хотите начать поиск
  • Start_num ( Необязательно): с какой позиции или где вы хотите начать поиск в тексте

Примечание: Если этот параметр (START_NUM) опущен, функция поиска по умолчанию считает его равным 1.

Как использовать функцию ПОИСК в Excel?

Функция ПОИСК в Excel очень проста и удобна. Давайте разберемся с работой функции ПОИСК в Excel на нескольких примерах.

Вы можете скачать этот шаблон функции ПОИСК Excel здесь – Шаблон функции ПОИСК Excel

Пример № 1

Для поиска определенной позиции слова в текстовой строке

В приведенном ниже примере формула = ПОИСК( A5, B5,1) или =SEARCH(“n”, B5,1) возвращает значение 4, поскольку n — это 4-й символ справа, начиная слева, или первый символ текстовой строки (оранжевый)

Пример #2

Для поиска определенной позиции подстроки в текстовой строке.

В приведенном ниже примере формула =ПОИСК(A10, B10,1) или =ПОИСК(«рынок», B10,1) возвращает значение 5, так как подстрока «рынок» начинается с 5-го символа слово (рыбный рынок)

Пример № 3

Функция ПОИСК – найти позицию подстановочного знака ((*) ЗВЕЗДОЧКА) в ячейке

При выполнении функции поиска Первоначально без использования (~) тильды перед (*) звездочкой то есть =ПОИСК(«*»,B15,1). ФУНКЦИЯ ПОИСКА возвращает 1 в качестве вывода, потому что мы не можем найти ни одну позицию подстановочного знака без использования тильды. Здесь тильда (~) действует как маркер, указывающий, что следующий символ является буквальным; поэтому мы будем вставлять тильду (~) перед звездочкой (*).

Когда мы выполняем функцию поиска, используя (~) тильду перед (*) звездочкой, то есть =ПОИСК(«~*»,B16,1), , она возвращает правильное положение (*) звездочки, т.е. 13 в качестве вывода

Пример #4

Функция поиска – найти позицию подстановочного знака {(?) ВОПРОСИТЕЛЬНЫЙ ЗНАК} в ячейке

Здесь давайте попробуем найти позицию вопросительного знака в ячейке

Когда мы выполняем функцию поиска, используя (~) тильду перед (?) Вопросительный знак, т.е. =ПОИСК(«~?»,B21,1) возвращает правильную позицию (?) Вопросительного знака, т.е. 14 в качестве вывода

Пример #5

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

Формула для разделения имени

= ЛЕВЫЙ (ячейка_ссылка, ПОИСК (» «, ячейка_ссылка) – 1)

Первоначально функция НАЙТИ или ПОИСК используется для получения позиции пробела (» «) в клетке; позже в формуле используется -1 для удаления лишнего пробела или запятой в конце текстовой строки.

Формула для разделения фамилии

= ПРАВО (cell_ref,LEN(cell_ref)-SEARCH(» «, Cell_Ref,1))

Функция «ДЛСТР» возвращает длину текстовой строки; в функции «ДЛСТР» Пробелы считаются символами в строке

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

Примечание: символы указываем слева или справа

Что следует помнить

a) Start_NumArgument в функции поиска не может быть отрицательным или равным нулю

Start_Num Аргумент или параметр в функции поиска не должны упоминаться как «0» или отрицательное значение; если указано нулевое или отрицательное значение, возвращается ошибка значения

b.

Читайте также: