Bazaprogram.ru

Новости из мира ПК
4 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Функция search в excel

Функция SEARCH (ПОИСК) в Excel. Как использовать?

Функция ПОИСК (SEARCH) в Excel используется для определения расположения текста внутри какого-либо текста и указания его точной позиции.

Что возвращает функция

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

Синтаксис

=SEARCH(find_text, within_text, [start_num]) – английская версия

=ПОИСК(искомый_текст;просматриваемый_текст;[начальная_позиция]) – русская версия

Аргументы функции

  • find_text (искомый_текст) – текст или текстовая строка которую вы хотите найти;
  • within_text (просматриваемый_текст) – текст, внутри которого вы осуществляете поиск;
  • [start_num] ([начальная_позиция]) – числовое значение, обозначающее позицию, с которой вы хотите начать поиск. Если не указать этот аргумент, то функци начнет поиск с начала текста.

Дополнительная информация

  • Если стартовая позиция поиска не указана, то поиск текста осуществляется сначала текста;
  • Функция не чувствительна к регистру. Если вам нужна чувствительность к регистру, то используйте функцию НАЙТИ;
  • Функция может обрабатывать подстановочные знаки. В Excel существует три подстановочных знака – ?, *,

.

  • знак “?” – сопоставляет любой одиночный символ;
  • знак “*” – сопоставляет любые дополнительные символы;
  • знак “

” – используется, если нужно найти сам вопросительный знак или звездочку.

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

    Пример 1. Ищем слово внутри текстовой строки (с начала)

    На примере выше видно, что когда мы ищем слово “доброе” в тексте “Доброе утро”, функция возвращает значение “1”, что соответствует позиции слова “доброе” в тексте “Доброе утро”.

    Так как функция не чувствительна к регистру, нет разницы каким образом мы указываем искомое слово “доброе”, будь то “ДОБРОЕ”, “Доброе”, “дОброе” и.т.д. функция вернет одно и то же значение.

    Если вам необходимо осуществить поиск чувствительный к регистру – используйте функцию НАЙТИ в Excel.

    Пример 2. Ищем слово внутри текстовой строки (с указанием стартовой позиции поиска)

    Третий аргумент функции указывает на порядковый номер позиции внутри текста, с которой будет осуществлен поиск. На примере выше, функция возвращает значение “1” при поиске слова “доброе” в тексте “Доброе утро”, начиная свой поиск с первой позиции.

    Вместе с тем, если мы указываем функции, что поиск следует начинать со второго символа текста “Доброе утро”, то есть функция в этом случае видит текст как “оброе утро” и ищет слово “доброе”, то результатом будет ошибка.

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

    Пример 3. Поиск слова при наличии нескольких совпадений в тексте

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

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

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

    При поиске функция учитывает подстановочные знаки. На примере выше мы ищем текст “c*l”. Наличие подстановочного знака “*” в данном запросе обозначает что мы ищем любо слово, которое начинается с буквы “c” и заканчивается буквой “l”, а что между этими двумя буквами не важно. Как результат, функция возвращает значение “3”, так как в слове “Excel”, расположенном в ячейке А2 буква “c” находится на третьей позиции.

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

    Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке).

    В этой статье описаны синтаксис формулы и использование функций ПОИСК и ПОИСКБ в Microsoft Excel.

    Описание

    Функции ПОИСК И ПОИСКБ находят одну текстовую строку в другой и возвращают начальную позицию первой текстовой строки (считая от первого символа второй текстовой строки). Например, чтобы найти позицию буквы «n» в слове «printer», можно использовать следующую функцию:

    Эта функция возвращает 4, так как «н» является четвертым символом в слове «принтер».

    Можно также находить слова в других словах. Например, функция

    возвращает 5, так как слово «base» начинается с пятого символа слова «database». Можно использовать функции ПОИСК и ПОИСКБ для определения положения символа или текстовой строки в другой текстовой строке, а затем вернуть текст с помощью функций ПСТР и ПСТРБ или заменить его с помощью функций ЗАМЕНИТЬ и ЗАМЕНИТЬБ. Эти функции показаны в примере 1 данной статьи.

    Эти функции могут быть доступны не на всех языках.

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

    К языкам, поддерживающим БДЦС, относятся японский, китайский (упрощенное письмо), китайский (традиционное письмо) и корейский.

    Синтаксис

    Аргументы функций ПОИСК и ПОИСКБ описаны ниже.

    Искомый_текст Обязательный. Текст, который требуется найти.

    Просматриваемый_текст Обязательный. Текст, в котором нужно найти значение аргумента искомый_текст.

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

    Замечание

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

    В аргументе искомый_текст можно использовать подстановочные знаки: вопросительный знак ( ?) и звездочку ( *). Вопросительный знак соответствует любому знаку, звездочка — любой последовательности знаков. Если требуется найти вопросительный знак или звездочку, введите перед ним тильду (

    Если значение аргумента искомый_текст не найдено, #VALUE! возвращено значение ошибки.

    Если аргумент начальная_позиция опущен, то он полагается равным 1.

    Если Нач_позиция не больше 0 или больше, чем длина аргумента просматриваемый_текст , #VALUE! возвращено значение ошибки.

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

    Примеры

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

    4 способа поиска данных в таблице Excel

    Добрый день уважаемый читатель!

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

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

    Теперь на примерах рассмотрим все 4 способа поиска данных в таблице Excel и комбинаций работы функции ВПР с другими функциями:

    Используем функцию СУММПРОИЗВ

    Как я уже описывал ранее в своей статье о функции СУММПРОИЗВ, она является одной из мощнейших в арсенале Excel. И именно первый способ мы сделаем с помощью возможностей формулы при использовании функции СУММПРОИЗВ. Для наших целей формула будет выглядеть так:

    =СУММПРОИЗВ((C2:C11=G2)*(B2:B11=G3);D2:D11) Принцип работы формулы следующий: создается условная таблица, в которой значения ячеек «G2» сравнивается с диапазоном «C2:C11» и ячейка «G3» с диапазоном «B2:B11». После этого сравниваются и сопоставляются все эти два массива и переводятся в единицы и нули, где значение единицы ставится строке, где все условия формулы выполнены. Следующая операция – это умножения полученного условного массива на диапазон «D2:D11», а поскольку в массиве всего одна единичка то формула получит результат 146.

    Обращаю ваше внимание, если в диапазоне «D2:D11» будут найдены текстовые значения, формула откажется работать. Для более углублённого ознакомления с функцией СУММПРОИЗВ советую почитать мою статью.

    Применение функции ВЫБОР

    Я описывал уже функцию ВЫБОР, но в таком исполнении еще не упоминал. В нашем случае нужно создать новую таблицу, в которой будут совместными столбики «Период» и «Месяц», всё это виртуально создаст функция ВЫБОР. Формула для работы будет выглядеть так:

    <=ВПР(G2&G3;ВЫБОР(<1;2>;C2:C11&B2:B11;D2:D11);2;0)> Основная работа, которую проделывает функция ВЫБОР в своей части «ВЫБОР(<1;2>;C2:C11&B2:B11;D2:D11)» это объединение значений столбиков «Период» и «Город» в общий массив, значения в котором будут прописаны как: «МоскваЯнварь», «БрянскФевраль», …. и т.д. Получив такое объединённое значения столбиков мы сможем легко сделать просмотр и отбор нужного значения, вот теперь я думаю, формула стала ближе.

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

    Создаем дополнительные столбики

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

    Рассмотрим на стандартном примере, когда необходимо определить продажи по двум показателям: «Период» и «Город». В этом случае обыкновенное использование функции ВПР не будет нам подходить, так как функция может возвращать значение по одному условию. В таком случае нам необходимо создать дополнительный столбик, в котором произойдёт объединение двух критериев в один, поэтому в созданном столбике приписываем формулу слияния значений: =B2&C2. А вот теперь результат из столбика D, мы сможем использовать в ячейке H4 нашу формулу:

    =ВПР(H2&H3;D2:E11;2;0)

    Как видите, наши отдельные условия отбора значений также объединяются аргументом H2&H3 в один критерий. После поиска в указанном диапазоне D2:E11, формула вернёт найденное значение со столбика 2.

    Совмещаем функции ПОИСКПОЗ и ИНДЕКС для работы

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

    А для нашего поиска данных в таблице Excel будем использовать такую формулу:

    Что же она делает, такая большая и непонятная…. Рассмотрим ее в разрезе нескольких блоков или этапов. Формула для функции имеет такой вид ПОИСКПОЗ (1;(B2:B11=G3)*(C2:C11=G2);0) и происходит следующее, со значением в ячейке G3, последовательно сравниваются значения из диапазона B2:B11 и в случае совпадения условий получаем результат ИСТИНА, а если есть отличия получаем ЛОЖЬ. Такой же процесс происходит для значения G2 и диапазона C2:C11. После сравнения этих массивов, которые состоят из аргументов ИСТИНА и ЛОЖЬ, производится сравнения на соответствие значению 1, это ИСТИНА*ИСТИНА, все остальные комбинации будут проигнорированы.

    Теперь, когда функция ПОИСКПОЗ нашла в массиве значение, которое соответствует «1» и указала его позицию в шестой строке, а значит, в функцию ИНДЕКС был передан аргумент «6» для диапазона D2:D11.

    Ну, подведя итог можно ответить на закономерный вопрос: «а что же делать?» и «какой способ использовать?». Использовать вы можете абсолютно любой способ, но я бы рекомендовал выбрать вам наиболее удобный, простой и понятный. Я, к примеру, люблю использовать таблицы, которые просто изменять и просты для работы и понимания, чего советую и вам.

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

    Функции поиска и функция FIND в Microsoft Excel

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

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

    = ПОИСК («find_text», «inside_text», start_num)

    = ПОИСК, и результат будет равен 1. Чтобы продолжить этот упрощенный пример, слово «b» в слове будет = SEARCH («b», C2,1), и результат будет 6. Вы также можете использовать символы строки поиска. Например, если ячейка F2 содержит 1023- # 555-A123, формула = ПОИСК («A12», F2.1) получит ответ 11.

    FIND – это еще один способ найти символ или строку в другой ячейке и вернуть значение, связанное с начальной точкой, а также функцию SEARCH. Формат для этой функции:

    = НАЙТИ («find_text», «inside_text», start_num).

    C2 появится с = FIND («a», C2,1), и результатом будет 1. Найти «b» в ячейке C2 будет достигнуто с помощью = FIND (» 6. Продолжайте по пути подобия, если ячейка F2 содержит 1023- # 555-A123 (как и раньше), формула = FIND («A12», F2.1) получит 11 в ответ. Как вы можете видеть, оба пути дадут вам те же результаты.

    Примечание. Возможно, вы быстро заметили, что в слове, расположенном в ячейке C2, есть два. Помечая начальную точку в каждой из этих формул как 1, подсвечивается первый экземпляр буквы «a». Если бы мы решили выбрать другой экземпляр, мы могли бы иметь только часть формулы «start_num», которая была бы равной 2, пропуская первый экземпляр письма и получив ответ 5.

    Основное различие между SEARCH и FIND заключается в том, что FIND чувствителен к регистру, а SEARCH – нет. Поэтому, если вы использовали формулу = ПОИСК («A», C2.1) (обратите внимание на главу «A»), результат будет равен 1, как в предыдущем случае. Если вы использовали формулу = FIND («A», C2,1), вы получите #VALUE! ПОИСК чувствителен к регистру, а слово «алфавит» не содержит слова «А».

    Еще одно отличие заключается в том, что ПОИСК позволяет использовать подстановочные знаки, а FIND – нет. В этом контексте знак вопроса будет искать точное выражение или последовательность символов в ячейке, а звездочка будет искать начало символьной строки непосредственно перед звездочкой. Например, формула = SEARCH («a? P», C2,1) в нашем алфавитном примере даст ответ 1, поскольку он ищет точную группировку буквы «a» со всем рядом с ней с «p» сразу после. Поскольку оно находится в начале слова, возвращаемое значение равно 1. Продолжая в примере алфавита, формула = ПОИСК («h * t», C2,1) имеет значение 4. В этом случае подстановочный знак «*» может означать любое количество символов между «h» и «t», если есть строка, начинающаяся с двух букв, которые вы используете в формуле, и заканчивающихся. Если formula = SEARCH («h * q», C2,1), вы получите #VALUE!

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

    Читать еще:  Как установить проверку данных в excel
    Ссылка на основную публикацию
    Adblock
    detector