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

Как найти нужную функцию в Microsoft Excel

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

Доступ к функциональному инструменту вставки

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

• Нажмите значок функции вставки (FX) в левой части панели формул.

• Перейдите на вкладку Формулас и нажмите «Вставить функцию» на левой стороне ленты.

Как найти функцию в Excel

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

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

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

Логично

, Текст и многое другое. Выбирая категорию, вы увидите все доступные функции в нем.

СВЯЗАННЫЕ С:




Как использовать логические функции в Excel: если, и, или, хор, не

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

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

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

Если после того, как вы уменьшите выбор функции и просмотрите описания, вы все еще не уверены, если это правильная функция для вас, вы можете

Получить дополнительную помощь

Отказ Выберите функцию в списке и нажмите «Справка по этой функции» на нижнем левом углу окна.

Это приведет вас к веб-сайту поддержки Microsoft для функции, которая предоставляет дополнительную информацию и примеры применения.

СВЯЗАННЫЕ С:




Как получить помощь в Windows 10

Используйте функцию, которую вы найдете

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

Затем вы увидите, что инструмент «Функция» открывается для вас

Установить формулу

используя функцию.

СВЯЗАННЫЕ С:




Определение и создание формулы

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

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

Давайте посмотрим на простой пример. Здесь, мы выбрали

Функция суммы

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

Когда вы завершаете добавление всех деталей для формулы, нажмите «ОК», и формула появится в вашу клетку своими результатами.

СВЯЗАННЫЕ С:




Как рассчитать сумму клеток в Excel

Попробуйте формула автозаполнения

Еще один способ найти функцию в Excel — использование AutoComplete Formula. Это полезно, если вы знаете, что вы хотите, но не уверены в необходимых вариантах.

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

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

= Счет

и увидеть раскрывающийся список вариантов. Описание для

Кончил

Давайте узнаем, что это тот, который нам нужен.

Мы дважды щелкнули «CONTIFIF» в списке, а затем посмотрите аргументы, необходимые для формулы. Это позволяет нам просто ввести то, что просят и завершит формулу.

Работаю с

Функции и формулы в Excel

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

Надстройка «Поиск решения» в Microsoft Excel

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

Как раз об этой опции и пойдет речь далее.

Используемый пример для поиска решения

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

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

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

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

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

Для этого я сначала добавляю функцию СУММ для суммы счетов и считаю сумму каждого в последнем цикле.

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

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

Возможно, текстом описать принцип работы этой таблицы сложно, но я постарался сделать это максимально доходчиво. В итоге получил таблицу с двумя счетами с разными процентами начислений и разными целями. Общая сумма довложений не должна быть более 500, а цель является общей, поскольку предполагается, что весь баланс с депозитных счетов все равно будет выведен на один. Поэтому далее я сделаю так, чтобы баланс к концу всех циклов получился 32500 (7500 + 25000, это предполагаемые цели первого и второго счета). При этом количество довложений должно быть минимальным, чтобы не тратить личные средства, и, соответственно, не превышать установленное ограничение в 500 условных единиц. Теперь давайте разберемся с тем, как реализовать это при помощи рассматриваемой надстройки.

Комьюнити теперь в Телеграм

Подпишитесь и будьте в курсе последних IT-новостей

Подписаться

Включение надстройки «Поиск решения»

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

1. В таблице перейдите на вкладку «Файл».

2. Откройте раздел «Другие».

3. Из появившегося меню выберите пункт «Параметры».

4. Откройте категорию настроек «Надстройки» и отыщите пункт с названием «Поиск решения», после чего выделите его нажатием левой кнопки мыши.

5. Кликните по кнопке «Перейти», находящейся внизу окна.

6. Активируйте галочку возле пункта «Поиск решения» и нажмите «ОК», чтобы выйти из данного окна.

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

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

Настройка «Поиска решений» для таблицы

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

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

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

3. Теперь обратите внимание на «В соответствии с ограничениями». У нас есть ограничения, поэтому нужно указать их, чтобы программа понимала, какие значения может использовать и к какому результату ей стремиться. Нажмите «Добавить», чтобы создать первое ограничение.

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

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

6. Сейчас это были все ограничения, но, если у вас их больше, продолжайте добавление в таком же ключе. По завершении убедитесь в том, что метод решения выбран как ОПГ, после чего запустите «Найти решение».

7. Расчет происходит буквально за несколько секунд, после чего мы видим оптимальное решение. В моем случае каждый цикл на балансы начислялось меньше 250, в один месяц даже 0, а в конце всех циклов получилось достичь нужной суммы с точностью до сотых. «Найти решение» показало, как мне действовать каждый цикл, чтобы вкладывать минимальную сумму, но дойти до нужного результата в конце. У вас решение может быть совершенно другим.

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

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

Я ставил цель показать вам, как работает программа «Поиск решения» в Microsoft Excel, чтобы вы узнали, как можно автоматически найти оптимальные значения для большой таблицы, избегая ручной переборки значений. Надеюсь, все объяснения и примеры были вам понятны, и теперь вы освоили еще одну очень удобную функцию, упрощающую взаимодействие с электронными таблицами, созданными в Экселе.

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

В учебном пособии объясняется синтаксис функций ПОИСК и ПОИСК в Excel и приводятся примеры формул для расширенного нетривиального использования.

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

• Функция ПОИСК Excel
• Функция ПОИСК Excel
• Различия между функциями НАЙТИ и ПОИСК
• Как использовать НАЙТИ и ПОИСК в Excel — примеры формул
  • Поиск строки, предшествующей заданному символу или следующей за ним
  • Найти N-е вхождение данного символа в ячейку
  • Извлечь N символов после определенного символа
  • Поиск и извлечение текста между скобками

Функция ПОИСК Excel

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

Синтаксис функции поиска Excel следующий:

НАЙТИ(найти_текст, внутри_текста, [начальный_номер])

Первые 2 аргумента являются обязательными, последний необязателен.

• Find_text — символ или подстрока, которую вы хотите найти.
• Within_text — текстовая строка для поиска. Обычно он предоставляется как ссылка на ячейку, но вы также можете ввести строку непосредственно в формулу.
• Start_num — необязательный аргумент, указывающий, с какого символа начинать поиск. Если опущено, поиск начинается с 1 ^st символа строки внутри_текста.

Если функция НАЙТИ не находит символ(ы) find_text, возникает ошибка #ЗНАЧ! возвращается ошибка.

Например, формула =НАЙТИ("d", "найти") возвращает 4, потому что «d» является 4-й ^-й буквой в слове « найти «. Формула =НАЙТИ("а", "найти") возвращает ошибку, потому что в « найти » нет «а».

Функция ПОИСК в Excel — о чем нужно помнить!

Чтобы правильно использовать формулу НАЙТИ в Excel, имейте в виду следующие простые факты:

1. Функция НАЙТИ чувствительна к регистру . Если вы ищете совпадение без учета регистра, используйте функцию ПОИСК.
2. Функция НАЙТИ в Excel не позволяет использовать подстановочных знаков .
3. Если аргумент find_text содержит несколько символов, функция НАЙТИ возвращает позицию 9 символов. 0040 первый символ . Например, формула НАЙТИ(«ап»,»счастливый») возвращает 2, потому что «а» в 2 ^и букве в слове «счастливый».
4. Если внутри_текста содержится несколько вхождений искомого текста, возвращается первое вхождение. Например, FIND(«l», «hello») возвращает 3, что является позицией первого символа «l» в слове «hello».
5. Если find_text представляет собой пустую строку «», формула Excel НАЙТИ возвращает первый символ в строке поиска.
6. Функция НАЙТИ в Excel возвращает значение #ЗНАЧ! ошибка , если происходит одно из следующих событий:
   • Find_text не существует в пределах_текста.
   • Start_num содержит больше символов, чем внутри_текста.
   • Start_num равен 0 (ноль) или отрицательному числу.

Функция ПОИСК в Excel

Функция ПОИСК в Excel очень похожа на НАЙТИ тем, что она также возвращает местоположение подстроки в текстовой строке. Синтаксис и аргументы аналогичны FIND: 9. 0005

ПОИСК(найти_текст, внутри_текста, [начальный_номер])

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

А вот пара основных формул ПОИСКА в Excel:

=ПОИСК("рынок", "супермаркет") возвращает 6, потому что подстрока «рынок» начинается с 6-го ^-го символа слова «супермаркет».

=ПОИСК("e", "Excel") возвращает 1, так как «e» является первым символом в слове «Excel» без учета регистра.

Как и НАЙТИ, функция ПОИСК Excel возвращает #ЗНАЧ! ошибка, если:

• Значение аргумента find_text не найдено.
• Аргумент start_num больше, чем длина внутри_текста.
• Start_num равен или меньше нуля.

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

ПОИСК в Excel и ПОИСК в Excel

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

1. ПОИСК с учетом регистра и ПОИСК без учета регистра

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

Например, ПОИСК(«e», «Excel») возвращает 1, поскольку игнорирует регистр «E», а ПОИСК(«e», «Excel») возвращает 4, поскольку учитывает регистр.

2. Поиск с подстановочными знаками

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

• Знак вопроса (?) соответствует одному символу, а
• Звездочка (*) соответствует любой последовательности символов.

Чтобы увидеть, как это работает на реальных данных, рассмотрим следующий пример:

Как видно на снимке экрана выше, формула ПОИСК(«функция*2013», A2) возвращает позицию первого символа («f» ) в подстроке, если текстовая строка, указанная в аргументе inside_text, содержит как «функция», так и «2013», независимо от того, сколько других символов находится между ними.

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

Примеры формул ПОИСК и ПОИСК в Excel

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

Пример 1. Найти строку, предшествующую или следующую за заданным символом

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

Предположим, у вас есть столбец имен (столбец A), и вы хотите вывести Имя и Фамилию в отдельные столбцы.

Чтобы получить имя, вы можете использовать НАЙТИ (или ПОИСК) в сочетании с функцией ЛЕВЫЙ:

=ЛЕВЫЙ(A2, НАЙТИ(" ", A2)-1)

или

=ЛЕВЫЙ(A2,ПОИСК(" ",A2)-1)

Как вы, наверное, знаете, функция Excel ВЛЕВО возвращает указанное количество крайних левых символов в строке. И вы используете функцию НАЙТИ, чтобы определить позицию пробела («»), чтобы сообщить функции ВЛЕВО, сколько символов нужно извлечь. При этом вы вычитаете 1 из позиции пробела, потому что не хотите, чтобы возвращаемое значение включало пробел.

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

=ВПРАВО(A2,ДЛСТР(A2)-НАЙТИ(" ",A2))

или

=ВПРАВО(A2,ДЛСТР(A2)-ПОИСК(" ",A2))

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

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

Пример 2. Найти N-е вхождение заданного символа в текстовую строку

Предположим, что у вас есть несколько текстовых строк в столбце A, скажем, список артикулов, и вы хотите найти положение 2 ^nd тире в строке. Следующая формула отлично работает:

=НАЙТИ("-", A2, НАЙТИ("-",A2)+1)

Первые два аргумента легко интерпретировать: найти тире («-«) в ячейке А2. В третий аргумент (start_num) вы встраиваете еще одну функцию НАЙТИ, которая сообщает Excel, что поиск нужно начинать с символа, следующего за первым вхождением тире (НАЙТИ(«-«,A2)+1).

Чтобы вернуть позицию 3 ^rd вхождения , вы вставляете приведенную выше формулу в аргумент start_num другой функции НАЙТИ и добавляете 2 к возвращаемому значению:

=НАЙТИ("-",A2, НАЙТИ("-", A2, НАЙТИ("-",A2 )+1) +2)

Другой и, вероятно, более простой способ найти N-е вхождение заданного символа — использовать функцию Excel НАЙТИ в сочетании с СИМВОЛ и ПОДСТАВИТЬ:

=НАЙТИ(СИМВОЛ(1), ПОДСТАВИТЬ(A2,"-",CHAR(1),3))

Где «-» — рассматриваемый символ, а «3» — N-е вхождение, которое вы хотите найти.

В приведенной выше формуле функция ПОДСТАВИТЬ заменяет третье вхождение дефиса («-«) на СИМВОЛ(1), который является непечатаемым символом «Начало заголовка» в системе ASCII. Вместо CHAR(1) вы можете использовать любой другой непечатаемый символ от 1 до 31. Затем функция НАЙТИ возвращает позицию этого символа в текстовой строке. Итак, общая формула выглядит следующим образом:

НАЙТИ(СИМВОЛ(1),ПОДСТАВИТЬ( ячейка , символ ,СИМВОЛ(1), N-е вхождение ))

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

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

Пример 3. Извлечь N символов после определенного символа

Чтобы найти подстроку заданной длины в любой текстовой строке, используйте ПОИСК или ПОИСК в Excel в сочетании с функцией СРЕДН. В следующем примере показано, как можно использовать такие формулы на практике.

В нашем списке SKU, предположим, вы хотите найти первые 3 символа после первого тире и вытащить их в другой столбец.

Если группа символов, предшествующая первому дефису, всегда содержит одинаковое количество элементов (например, 2 символа), это будет тривиальной задачей. Вы можете использовать функцию MID для возврата 3 символов из строки, начиная с позиции 4 (пропуская первые 2 символа и тире):

=MID(A2, 4, 3)

В переводе на английский язык формула гласит: «Посмотрите в ячейку A2, начните извлечение с символа 4 и верните 3 символа».

Однако в реальных рабочих листах подстрока, которую нужно извлечь, может начинаться в любом месте текстовой строки. В нашем примере вы можете не знать, сколько символов предшествует первому тире. Чтобы справиться с этой проблемой, используйте функцию НАЙТИ, чтобы определить начальную точку подстроки, которую вы хотите получить.

Формула НАЙТИ для возврата позиции тире 1 ^st выглядит следующим образом:

=НАЙТИ("-",A2)

Поскольку вы хотите начать с символа, следующего за тире, добавьте 1 к возвращаемому значению и вставьте указанную выше функцию во второй аргумент (start_num) функции MID:

=MID(A2, FIND("-",A2)+1, 3)

В этом сценарии Функция ПОИСК в Excel работает одинаково хорошо:

=СРЕДН(A2, ПОИСК("-",A2)+1, 3)

Это здорово, но что, если группа символов после первого дефиса содержит другое количество символов? Хм. .. это может быть проблемой:

Как вы видите на скриншоте выше, формула отлично работает для строк 1 и 2. В строках 4 и 5 вторая группа содержит 4 символа, но только первые 3 символа возвращаются. В строках 6 и 7 во второй группе всего 2 символа, и поэтому наша формула поиска в Excel возвращает после них тире.

Если вы хотите вернуть все символы между 1 ^st и 2 ^nd появления определенного символа (тире в этом примере), как бы вы поступили? Вот ответ:

=СРЕД(A2, НАЙТИ("-",A2)+1, НАЙТИ("-", A2, НАЙТИ("-",A2)+1) - НАЙТИ("-", A2)-1)

Для лучшего понимания этой формулы MID рассмотрим ее аргументы один за другим:

• 1 ^st аргумент (текст). Это текстовая строка, содержащая символы, которые вы хотите извлечь, ячейка A2 в этом примере.
• 2 ^nd аргумент (start_position). Определяет позицию первого символа, который вы хотите извлечь. Вы используете функцию НАЙТИ, чтобы найти первый дефис в строке и добавить 1 к этому значению, потому что вы хотите начать с символа, следующего за дефисом: НАЙТИ(«-«,A2)+1.
• 3 ^rd аргумент (num_chars). Указывает количество символов, которое вы хотите вернуть. В нашей формуле это самая сложная часть. Вы используете две функции НАЙТИ (или ПОИСК), одна из которых определяет позицию первого тире: НАЙТИ («-«, A2). А другой возвращает позицию второго тире: НАЙТИ(«-«, A2, НАЙТИ(«-«,A2)+1). Затем вы вычитаете первое из второго, а затем вычитаете 1, потому что вы не хотите включать ни одно тире. В результате вы получите количество символов между 1 ^st и 2 ^nd тире, это именно то, что мы ищем. Итак, вы передаете это значение аргументу num_chars функции MID.

Аналогичным образом можно вернуть 3 символа после дефиса 2 ^nd :

=MID(A2, НАЙТИ("-",A2, НАЙТИ("-", A2, НАЙТИ("-", A2)+1) +2), 3)

Или извлеките все символы между тире 2 ^nd и 3 ^rd :

=MID(A2, FIND("-", A2, FIND ("-",A2)+1)+1, НАЙТИ("-",A2, НАЙТИ("-", A2, НАЙТИ("-",A2)+1) +2) - НАЙТИ("-", A2, НАЙТИ("-",A2)+1)-1)

Пример 4.

Поиск текста в скобках

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

Для этого вам понадобится функция MID, чтобы вернуть желаемое количество символов из строки, и функция Excel FIND или SEARCH, чтобы определить, с чего начать и сколько символов нужно извлечь.

=СРЕДН(A2,ПОИСК("(",A2)+1, ПОИСК(")",A2)-ПОИСК("(",A2)-1)

Логика этой формулы аналогична тем, которые мы обсуждали в предыдущем примере. И снова самая сложная часть — это последний аргумент, который сообщает формуле, сколько символов нужно вернуть. Это довольно длинное выражение в аргументе num_chars делает следующее:

• Сначала вы находите положение закрывающей скобки: SEARCH(")",A2)
• После этого вы определяете позицию открывающей скобки: ПОИСК("(",A2)
• Затем вы вычисляете разницу между положениями закрывающей и открывающей круглых скобок и вычитаете 1 из этого числа, потому что вы не хотите, чтобы в результате были скобки: ПОИСК(")",A2)-ПОИСК("( ",А2))-1

Естественно, ничто не мешает вам использовать функцию Excel НАЙТИ вместо ПОИСК, потому что регистрозависимость или нечувствительность к регистру в этом примере не имеют значения.

Надеемся, что это руководство пролило свет на то, как использовать функции ПОИСК и НАЙТИ в Excel. В следующем уроке мы подробно рассмотрим функцию REPLACE, поэтому следите за обновлениями. Спасибо за чтение!

Загрузить учебное пособие

Примеры формул ПОИСК и ПОИСК

Вас также может заинтересовать

Формула ПОИСК в 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; перейдите на Sheet3, где пользователь хочет получить имена всех сотрудников.

Создайте один заголовок столбца для SEARCH Result, чтобы отобразить результат функции в столбце B.

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

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

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

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

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

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

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

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

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

Краткое изложение Примера 3:

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

Что следует помнить о формуле ПОИСК в Excel

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