Строка поиска в excel формула

Функция

ПОИСК(

)

, английский вариант SEARCH(),

находит первое вхождение одной текстовой строки в другой строке и возвращает начальную позицию найденной строки.

Синтаксис функции

ПОИСК

(

искомый_текст

;

просматриваемая_строка

;[нач_позиция])

Искомый_текст

— текст, который требуется найти.

Просматриваемая_строка

— текст, в которой ищется

Искомый_текст

.

Нач_позиция

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

нач_позиция

опущен, то предполагается значение 1.

В аргументе

искомый_текст

можно использовать

подстановочные знаки

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

Если искомый_текст не найден, возвращается значение ошибки #ЗНАЧ!

Функция

ПОИСК()

не учитывает РЕгиСТР букв. Для поиска с учетом регистра следует воспользоваться функцией

НАЙТИ()

.

Примеры

Формула

=ПОИСК(«к»;»Первый канал»)

вернет 8, т.к. буква

к

находится на 8-й позиции слева.

Пусть в ячейке

А2

введена строка

Первый канал — лучший

. Формула

=ПОИСК(СИМВОЛ(32);A2)

вернет 7, т.к. символ пробела (код 32) находится на 7-й позиции.

Формула

=ПОИСК(«#???#»;»Артикул #123# ID»)

будет искать в строке »

Артикул #123# ID

» последовательность из 5 символов, которая начинается и заканчивается на знак #.

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

=ПОИСК(«а»;»мама мыла раму»;ПОИСК(«а»;»мама мыла раму»)+1).

Чтобы определить есть ли третье вхождение буквы «м» в строке «мама мыла раму» используйте формулу

=ЕСЛИ(ДЛСТР(ПОДСТАВИТЬ(«мама мыла раму»;»м»;»»;3))=ДЛСТР(«мама мыла раму»);»Нет третьего вхождения м»;»Есть третье вхождение м»)

Формула

=ПОИСК(«клад?»;»докладная»)

вернет 3, т.е. в слове «докладная» содержится слово из 5 букв, первые 4 из которых

клад

(начиная с третьей буквы слова

докладная

).

Функция

НАЙТИ()

vs

ПОИСК()

Функция

НАЙТИ()

учитывает РЕгиСТР букв и не допускает использование подстановочных знаков. Для поиска без учета регистра, а также для поиска с использованием

подстановочных знаков

пользуйтесь функцией

ПОИСК()

.

Связь с функциями

ЛЕВСИМВ()

,

ПРАВСИМВ()

и

ПСТР()

Функция

ПОИСК()

может быть использована совместно с функциями

ЛЕВСИМВ()

,

ПРАВСИМВ()

и

ПСТР()

.

Например, в ячейке

А2

содержится фамилия и имя «Иванов Иван», то формула

=ЛЕВСИМВ(A2;ПОИСК(СИМВОЛ(32);A2)-1)

извлечет фамилию, а

=ПРАВСИМВ(A2;ДЛСТР(A2)-ПОИСК(СИМВОЛ(32);A2))

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

СЖПРОБЕЛЫ()

.

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

Поисковая функция в Excel

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

Способ 1: простой поиск

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

1. Находясь во вкладке «Главная», кликаем по кнопке «Найти и выделить», которая расположена на ленте в блоке инструментов «Редактирование». В появившемся меню выбираем пункт «Найти…». Вместо этих действий можно просто набрать на клавиатуре сочетание клавиш Ctrl+F.



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



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

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

   Поисковые символы не обязательно должны быть самостоятельными элементами. Так, если в качестве запроса будет задано выражение «прав», то в выдаче будут представлены все ячейки, которые содержат данный последовательный набор символов даже внутри слова. Например, релевантным запросу в этом случае будет считаться слово «Направо». Если вы зададите в поисковике цифру «1», то в ответ попадут ячейки, которые содержат, например, число «516».

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

   

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

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

Способ 2: поиск по указанному интервалу ячеек

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

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



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

Способ 3: Расширенный поиск

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

К тому же, в выдачу может попасть не только содержимое конкретной ячейки, но и адрес элемента, на который она ссылается. Например, в ячейке E2 содержится формула, которая представляет собой сумму ячеек A4 и C3. Эта сумма равна 10, и именно это число отображается в ячейке E2. Но, если мы зададим в поиске цифру «4», то среди результатов выдачи будет все та же ячейка E2. Как такое могло получиться? Просто в ячейке E2 в качестве формулы содержится адрес на ячейку A4, который как раз включает в себя искомую цифру 4.

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

1. После открытия окна «Найти и заменить» любым вышеописанным способом, жмем на кнопку «Параметры».



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

   По умолчанию, функции «Учитывать регистр» и «Ячейки целиком» отключены, но, если мы поставим галочки около соответствующих пунктов, то в таком случае, при формировании результата будет учитываться введенный регистр, и точное совпадение. Если вы введете слово с маленькой буквы, то в поисковую выдачу, ячейки содержащие написание этого слова с большой буквы, как это было бы по умолчанию, уже не попадут. Кроме того, если включена функция «Ячейки целиком», то в выдачу будут добавляться только элементы, содержащие точное наименование. Например, если вы зададите поисковый запрос «Николаев», то ячейки, содержащие текст «Николаев А. Д.», в выдачу уже добавлены не будут.

   

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

   

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

   

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

   

   Ещё более точно поиск можно задать, нажав на кнопку «Формат».

   

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

   

   Если вы хотите использовать формат какой-то конкретной ячейки, то в нижней части окна нажмите на кнопку «Использовать формат этой ячейки…».

   

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

   

   После того, как формат поиска настроен, жмем на кнопку «OK».

   

   Бывают случаи, когда нужно произвести поиск не по конкретному словосочетанию, а найти ячейки, в которых находятся поисковые слова в любом порядке, даже, если их разделяют другие слова и символы. Тогда данные слова нужно выделить с обеих сторон знаком «*». Теперь в поисковой выдаче будут отображены все ячейки, в которых находятся данные слова в любом порядке.



3. Как только настройки поиска установлены, следует нажать на кнопку «Найти всё» или «Найти далее», чтобы перейти к поисковой выдаче.

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

Поиск в Excel.

​Смотрите также​​ поиска — а​​Разница только в последнем​​ функция​​ ячейки с пересечения​ слову​ CTRL + A,​ столбца по щелчку​: Для просмотра промежуточных​​ обоих столбцах попарно​ ​ в строке ячейки​ строкой «МДС0093.МужскаяОдежда». Чтобы​
​, с которого следует​ строки в другой​ «Поиск» или формулами,​​ не будет мешать.​ — на закладке​Часто возникает вопрос​ значит и вся​ аргументе обеих функций​ИНДЕКС (INDEX)​
​ определенной строки и​можно найти и​ чтобы выбрать таблицу​​ появится стрелка выделения​ вычислений воспользуйтесь клавишей​ совпадают.​ A2, начиная с​ найти первое вхождение​ начать поиск.​ текстовой строке, а​
​ но и функцией​​ Сдвинуть можно ниже​ «Главная» нажать кнопку​​«​ таблица — должна​ПОИСКПОЗ (MATCH)​из той же​ столбца в таблице.​
​ вывести на экран​ данных в таблицу​ ​ для следующих.​​ F9.​Для поиска совпадений будем​ шестого знака.​
​ «М» в описательной​Функции​ затем вернуть текст​​ условного форматирования. Читайте​​ экрана, оставив только​ «Найти и выделить».​Как найти в Excel​ быть отсортирована по​
​-​ категории​ Для наглядности, разобъем​ все что имеет​ целиком, или нажмите​
​Примечание:​С помощью формулы массива =ПОИСКПОЗ(1;(B17=B$8:B$12)*(C17=C$8:C$12);0) найдем​ использовать формулу =СУММПРОИЗВ((B17=B$8:B$12)*(C17=C$8:C$12)) Формулу разместим​7​ части текстовой строки,​ПОИСК​ с помощью функций​ об этом статью​ ячейку ввода искомого​На вкладке «Найти» в​»?​
​ возрастанию (для Типа​Типу сопоставления​Ссылки и массивы (Lookup​ задачу на три​ определенный цвет и/или​ кнопку большинство верхнюю​ Щелкнуть верхний край один​ номер искомой строки.​ в столбце D.​=ПОИСК(A4;A3)​ задайте для аргумента​и​
​ПСТР​ «Условное форматирование в​ слова («найти») и​ ячейке «найти» пишем​В Excel можно​ сопоставления = 1)​(здесь он равен​ and Reference)​ этапа.​ размер не превышает​
​ левую ячейку в​​ раз, выделяются данные​ Формулу разместим в​Формула работает следующим образом.​​Начальная позиция строки «маржа»​​начальная_позиция​
​ПОИСКБ​
​и​ Excel» здесь.​ нажимать потом Enter.​ искомое слово (можно​ найти любую информацию:​ или по убыванию​ минус 1). Это​. Первый аргумент этой​Во-первых, нам нужно определить​ L*H​ таблице и нажмите​ в столбец; Дважды​ столбце Е.​ Выражение B17=B$8:B$12 сравнивает текущее​ (искомая строка в​значение 8, чтобы​не учитывают регистр.​ПСТРБ​
​Ещё прочитать о​Это диалоговое окно​ часть слова) и​ текст, часть текста,​ (для Типа сопоставления​ некий аналог четвертого​ функции — диапазон​ номер строки, соответствующей​Но для этого​ клавиши CTRL +​ щелкнув его выделяется​Также для каждой искомой​ значение первого столбца​
​ ячейке A4) в​ поиск не выполнялся​ Если требуется учитывать​или заменить его​
​ функции «Найти и​ поиска всегда остается​ нажимаем «найти далее».​ цифру, номер телефона,​
​ = -1) по​ аргумента функции​ ячеек (в нашем​ выбранному пользователем в​ как раз и​ SHIFT + END.​ весь столбец таблицы.​ строки можно определить,​ большой таблицы со​
​ строке «Доход: маржа»​ в той части​​ регистр, используйте функции​ с помощью функций​ выделить» можно в​ на экране, даже​ Будет найдено первое​ эл. адрес​ строчкам и по​
​ВПР (VLOOKUP) — Интервального​ случае это вся​ желтой ячейке товару.​ надо знать те​Нажмите клавиши CTRL +​Можно также щелкнуть любую​ если она в​
​ всеми значениями первого​ (ячейка, в которой​ текста, которая является​НАЙТИ​ЗАМЕНИТЬ​ статье «Фильтр в​ если вы переходите​ такое слово. Затем​,​
​ столбцам.​ просмотра (Range Lookup)​ таблица, т.е. B2:F10),​ Это поможет сделать​ условия, о которых​

excel-office.ru

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

​ A, два раза,​ ячейку столбца и​ большой таблице. Это​​ столбца искомых строк,​​ выполняется поиск — A3).​​ серийным номером (в​​и​

Описание

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

​ данном случае —​

​НАЙТИБ​​ЗАМЕНИТЬБ​​В этой статье описаны​ С помощью этого​ и поиск перейдет​

​ ячейки, т.д.​ работать не будет!​ значений для него​

​ строки, третий -​

​ПОИСКПОЗ (MATCH)​​Приводите конкретный пример,​​ целиком, включая заголовки​ + ПРОБЕЛ или​ помощью функции =ЕСЛИ(ЕНД(ПОИСКПОЗ(A8;$E$17:$E$28;0));»»;»Да!»)​ логических значений {ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ}. 5​=ЗАМЕНИТЬ(A3;ПОИСК(A4;A3);6;»объем»)​​ «МДС0093»). Функция​​.​​. Эти функции показаны​​ синтаксис формулы и​ окна можно производить​ на второе такое​Найти ячейку на пересечении​Для точного поиска (Тип​ три:​​ номер столбца (а​​из категории​​ а лучше предоставьте​​ таблицы.​ щелкните первую ячейку​​Найденные строки можно выделить​​ значений ЛОЖЬ означает,​​Заменяет слово «маржа» словом​​ПОИСК​В аргументе​ в примере 1​

​ использование функций​​ поиск на любой​

• ​ слово.​ строки и столбца​ сопоставления = 0)​

• ​1​ их мы определим​Ссылки и массивы (Lookup​ файл​Иван леконтцев​ в столбце таблицы​ с помощью Условного​ что значение 1,2​ «объем», определяя позицию​начинает поиск с​искомый_текст​ данной статьи.​ПОИСК​

​ странице, надо только​А если надо показать​ Excel​ сортировка не нужна​- поиск ближайшего​

Синтаксис

​ с помощью функций​

​ and Reference)​

​Если вы знакомы с​: можно сделать фильтр​

• ​ и нажмите клавиши​​ форматирования.​ из В17 не​

• ​ слова «маржа» в​​ восьмого символа, находит​можно использовать подстановочные​Важно:​​и​​ его активизировать на​

• ​ сразу все такие​​– смотрите статью​ и никакой роли​​ наименьшего числа, т.е.​​ ПОИСКПОЗ).​. В частности, формула​

Замечание

• ​ функцией​​ (автофильтр) и используя​​ CTRL + SHIFT​​Примечание:​​ найдено в диапазоне B$8:B$12,​ ячейке A3 и​ знак, указанный в​​ знаки: вопросительный знак​​ ​​ПОИСКБ​​ открытой странице. Для​

• ​ слова, то нажимаем​​ «Как найти в​​ не играет.​ введенные пользователем размеры​Итого, соединяя все вышеперечисленное​​ПОИСКПОЗ(J2; A2:A10; 0)​​ВПР (VLOOKUP)​​ его выводить только​​ + Стрелка вниз.​Мы стараемся как​ состоящем из 5​ заменяя этот знак​ аргументе​ (​Эти функции могут быть​​в Microsoft Excel.​​ этого нажать курсор​

• ​ кнопку «найти все»​​ Excel ячейку на​​В комментах неоднократно интересуются​ двери округлялись бы​

• ​ в одну формулу,​​даст нам нужный​​или ее горизонтальным​ необходимые значения.​

• ​Примечание:​​ можно оперативнее обеспечивать​​ значений, т.к. значение​ и последующие пять​искомый_текст​​?​​ доступны не на​Функции​

• ​ на строке «найти».​​ и внизу поискового​​ пересечении строки и​ — а как​ до ближайших наименьших​ получаем для зеленой​​ результат (для​​ аналогом​видео как сделать​ Один раз клавиши CTRL​ вас актуальными справочными​ не встречается в​ знаков текстовой строкой​, в следующей позиции,​​) и звездочку (​​ всех языках.​ПОИСК​Для более расширенного​ окошка появится список​ столбца» (функция «ИНДЕКС»​ сделать обратную операцию,​ подходящих размеров из​​ ячейки решение:​​Яблока​ГПР (HLOOKUP)​ автофильтр.​ + ПРОБЕЛ выделяются​​ материалами на вашем​​ первом столбце искомых​ «объем.»​ и возвращает число​​*​​Функция ПОИСКБ отсчитывает по​И​ поиска нажмите кнопку​​ с указанием адреса​​ в Excel).​ т.е. определить в​ таблицы. В нашем​​=ИНДЕКС(B2:F10; ПОИСКПОЗ(J2;A2:A10;0); ПОИСКПОЗ(J3;B1:F1;0))​​это будет число​

Примеры

​, то должны помнить,​_______________​ данные в столбце;​ языке. Эта страница​ строк.​Доход: объем​ 9. Функция​). Вопросительный знак соответствует​ два байта на​ПОИСКБ​ «Параметры» и выберите​ ячейки. Чтобы перейти​Найти и перенести в​

support.office.com

Поиск заданных строк в таблице MS EXCEL

​ нужное слово в​(например, в бланк)​ из таблицы? Тут​ 480 до 300,​ Предположим, что у​

​Яблоко​ параметру, т.е. в​ какой версией пользуйтесь.​ таблицы.​ грамматические ошибки. Для​ попарно перемножаются (при​

​ первым пробелом в​ начала​ вопросительный знак или​

​ с поддержкой БДЦС.​ позицию первой текстовой​ и числа, и​ списке окна поиска.​

​ несколько данных сразу​ потребуются две небольшие​ и стоимость двери​

​ нас имеется вот​из желтой ячейки​ одномерном массиве -​Сергей харин​Строка таблицы​ нас важно, чтобы​ этом ЛОЖЬ преобразуется​ строке «Доход: маржа»​просматриваемого текста​ звездочку, введите перед​ В противном случае​ строки (считая от​ номер телефона, т.д.​Если поиск ничего не​ – смотрите в​ формулы массива (не​ была бы 135.​

​ такая ситуация:​ J2), второй -​ по строке или​: Ctrl+F​Щелкните левой границы строки​ эта статья была​ в 0, а​ (ячейка A3).​, включая символы, которые​ ним тильду (​ функция ПОИСКБ работает​ первого символа второй​Если нужно найти​ нашел, а вы​ статье «Найти в​ забудьте ввести их​-1​Идея в том, что​ диапазон ячеек, где​ по столбцу. А​

​Ввести условие поиска​ таблицы. Чтобы указать,​ вам полезна. Просим​ ИСТИНА в 1).​марж​ пропускаются, если значение​~​ так же, как​ текстовой строки). Например,​ все одинаковес слова,​ знаете, что эти​ Excel несколько данных​

​ с помощью сочетания​​- поиск ближайшего​ пользователь должен ввести​ мы ищем товар​

​ если нам необходимо​Enter​ что при щелчке​ вас уделить пару​

​ Знак умножения эквивалентен​=ПОИСК(«»»»;A5)​ аргумента​).​ функция ПОИСК, и​ чтобы найти позицию​

​ но в падежах​ данные точно есть,​ сразу» здесь (функция​

excel2.ru

Выделение строк и столбцов в таблице Excel

​ клавиш​​ наибольшего числа, т.е.​ в желтые ячейки​ (столбец с товарами​ выбирать данные из​Inga zayonts​ выделяется строка появится​ секунд и сообщить,​ в данном случае​Позиция первой двойной кавычки​начальная_позиция​Если​ отсчитывает по одному​ буквы «n» в​ (молоко, молоком, молоку,​ то попробуйте убрать​ «ВПР» в Excel).​Ctrl+Shift+Enter​ нестандартная высота 500​ высоту и ширину​ в таблице -​ двумерной таблицы по​

​: У вас не​ стрелка выделения для​ помогла ли она​ функции И(), т.е.​ («) в ячейке​больше 1.​искомый_текст​ байту на каждый​ слове «printer», можно​

support.office.com

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

​ + SHIFT +​​ оригинал (на английском​ строк. Функция СУММПРОИЗВ()​ только текст, заключенный​ листа Excel. Чтобы​
​опущен, то он​ (традиционное письмо) и​
​, так как «н»​
​Функция в Excel «Найти​Поиск числа в Excel​ ссылки на другие​ диапазоне B2:F10 и​

​ 462. Для бизнеса​​ появиться ее стоимость​
​Во-вторых, совершенно аналогичным способом​
​ жизненных примеров таких​

​ не указать какой​​ Стрелка вправо.​ языке) .​
​ складывает результат перемножения​ в двойные кавычки.​ отобразить результаты формул,​ полагается равным 1.​ корейский.​ является четвертым символом​
​ и выделить»​требует небольшой настройки​
​ листы в формулах​ ищем совпадение с​ так гораздо интереснее!​ из таблицы. Важный​ мы должны определить​
​ задач и их​ именно.​Все строки и столбцы​Можно выбрать ячеек и​ и использована вместо​босс​ выделите их и​Если аргумент​
​ПОИСК(искомый_текст;просматриваемый_текст;[начальная_позиция])​ в слове «принтер».​поможет не только​
​ условий поиска -​ Excel».​ искомым значением (13)​ :)​ нюанс в том,​ порядковый номер столбца​
​ решения.​Сергей Харин предложил​ таблицы​ диапазонов в таблице​ СУММ() лишь для​
​Найдем заданные строки в​ нажмите клавишу F2,​начальная_позиция​

Двумерный поиск в таблице (ВПР 2D)

​ПОИСКБ(искомый_текст;просматриваемый_текст;[начальная_позиция])​Можно также находить слова​​ найти данные, но​​ применим​Найти в Excel ячейки​​ из ячейки J4​​0​ что если пользователь​ в таблице с​Предположим, что у нас​ универсальный поиск.​Щелкните в левом верхнем​ так же, как​ того, чтобы не​ таблице. Строки состоят​ а затем — клавишу​не больше 0​Аргументы функций ПОИСК и​ в других словах.​ и заменить их.​расширенный поиск в Excel​ с примечанием​ с помощью функции​- поиск точного​ вводит нестандартные значения​

Пример 1. Найти значение по товару и городу

​ нужным нам городом.​ имеется вот такой​Но учитывая что​ углу таблицы. Чтобы​ выбрать их на​

​ использовать формулы массива.​ из 2-х столбцов,​ ВВОД. При необходимости​ или больше, чем​ ПОИСКБ описаны ниже.​ Например, функция​ Смотрите статью «Как​.​-​ЕСЛИ (IF)​ соответствия без каких​ размеров, то они​ Функция​ двумерный массив данных​ у вас лист​ указать, что щелкнув​ листе, но выбор​

• ​Для примера рассмотрим строку​ значения могут быть​ измените ширину столбцов,​ длина​Искомый_текст​=ПОИСК(«base»;»database»)​​ скопировать формулу в​​Совет.​​статья «Вставить примечание​когда нашли совпадение, то​​ либо округлений. Используется​​ должны автоматически округлиться​​ПОИСКПОЗ(J3; B1:F1; 0)​ по городам и​​ большогог размера, то​​ выделяются данные в​ строк и столбцов​ 18 (2-я строка​ любыми.​​ чтобы видеть все​​просматриваемого текста​    Обязательный. Текст, который требуется​возвращает​ Excel без изменения​Если вы работаете​ в Excel» тут​ определяем номер строки​ для 100%-го совпадения​ до ближайших имеющихся​сделает это и​ товарам:​
• ​ это не самый​ таблицу целиком появится​ таблицы отличается от​ в большой таблице).​Пусть имеется перечень из​ данные.​​, возвращается значение ошибки​​ найти.​5​​ ссылок» здесь.​​ с таблицей продолжительное​ .​ (столбца) первого элемента​
• ​ искомого значения с​ в таблице и​ выдаст, например, для​Пользователь вводит (или выбирает​ удобный вариант.​ стрелка выделения для​ выбора листа строк​​ Первое и второе​​ 5 строк, которые​Данные​​ #ЗНАЧ!.​Просматриваемый_текст​​, так как слово​Как убрать лишние​ время и вам​Для быстрого поиска​ в таблице в​ одним из значений​ в серой ячейке​Киева​ из выпадающих списков)​Машину можно заставить​ следующих.​

​ и столбцов.​ выражения вернут массивы {ЛОЖЬ:ИСТИНА:ИСТИНА:ЛОЖЬ:ЛОЖЬ}​ требуется найти в​Выписки​

​Аргумент​

​    Обязательный. Текст, в котором​ «base» начинается с​

Пример 2. Приблизительный двумерный поиск

​ пробелы, которые мешают​ часто надо переходить​ существует сочетание клавиш​ этой строке (столбце)​

​ в таблице. Естественно,​ должна появиться стоимость​, выбранного пользователем в​ в желтых ячейках​ искать и выводить​Щелкните левый верхний угол​Чтобы выделить​ и {ЛОЖЬ:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ЛОЖЬ} соответственно. После​ таблице MS EXCEL.​Доход: маржа​начальная_позиция​ нужно найти значение​ пятого символа слова​ обрабатывать данные в​ к поиску от​ –​ с помощью функций​ применяется при поиске​ изготовления двери для​ желтой ячейке J3​ нужный товар и​ нужную информацию в​ таблицы два раза,​

​Выполните указанное действие​ попарного перемножения элементов​ Предполагается, что строки​

​маржа​

​можно использовать, чтобы​

​ аргумента​ «database». Можно использовать​​ таблице, читайте в​​ одного слова к​​Ctrl + F​​СТОЛБЕЦ (COLUMN)​ текстовых параметров (как​ этих округленных стандарных​ значение 4.​​ город. В зеленой​ удобном для вас​​ чтобы выделить таблицу​Столбец таблицы с заголовком​ массивов, он будет​

• ​ не повторяются.​​Здесь «босс».​ пропустить определенное количество​искомый_текст​ функции​ статье «Как удалить​ другому. Тогда удобнее​. Нажимаем клавишу Ctrl​и​ в прошлом примере),​ размеров.​И, наконец, в-третьих, нам​ ячейке нам нужно​ месте, так же​
• ​ целиком, включая заголовки​​ или без него​ преобразован в {0:0:1:0:0}. Т.е.​Будем искать эти строки​Формула​ знаков. Допустим, что​.​ПОИСК​ лишние пробелы в​ окно поиска не​ и, удерживая её,​СТРОКА (ROW)​
• ​ т.к. для них​​Решение для серой ячейки​ нужна функция, которая​ формулой найти и​ можно искать по​ таблицы.​Щелкните верхний край заголовка​ строка 18 совпадает​ в большой таблице​Описание​ функцию​Начальная_позиция​и​

​ Excel» тут.​ закрывать каждый раз,​ нажимаем клавишу F.​выдергиваем значение города или​ округление невозможно.​ будет практически полностью​ умеет выдавать содержимое​ вывести число из​ индексу/артикулу/номеру тлф и​Можно также щелкните в​ столбца или столбца​ с третьей искомой​ (см. файл примера). ​Результат​​ПОИСК​    Необязательный. Номер знака в​

​ПОИСКБ​В Excel можно​ а сдвинуть его​ Появится окно поиска.​ товара из таблицы​

P.S. Обратная задача

​Важно отметить, что при​ аналогично предыдущему примеру:​ ячейки из таблицы​ таблицы, соответствующее выбранным​ т. д.​ любом месте таблицы​ в таблице. Чтобы​ строкой. Функция СУММПРОИЗВ() вернет 1.​Строки считаются совпадающими, когда​=ПОИСК(«и»;A2;6)​нужно использовать для​ аргументе​для определения положения​​ найти любую информацию​​ в ту часть​​Ещё окно поиска​​ с помощью функции​

​ использовании приблизительного поиска​

1. ​=ИНДЕКС(C7:K16; ПОИСКПОЗ(D3;B7:B16;1); ПОИСКПОЗ(G3;C6:K6;1))​ по номеру строки​ параметрам. Фактически, мы​Компьютер может «дописывить»​ и нажмите клавиши​ указать, что выделение​​СОВЕТ​
2. ​ их значения в​Позиция первого знака «и»​ работы с текстовой​просматриваемый_текст​ символа или текстовой​ не только функцией​​ таблицы, где оно​​ можно вызвать так​​ИНДЕКС (INDEX)​
3. ​ с округлением диапазон​=INDEX(C7:K16; MATCH(D3;B7:B16;1); MATCH(G3;C6:K6;1))​ и столбца -​​ хотим найти значение​

planetaexcel.ru

​ строку по введенноу​

This article explains how to use the LOOKUP function in any version of Excel including Excel 2019 and Microsoft 365.

What is the LOOKUP Function?

The LOOKUP function in Excel is used to look up information in a row or column. It locates a value from the same position in the row or column as the starting value, so it’s really useful when dealing with structured tables where all the rows and columns contain similar data.

There are two ways to write a LOOKUP formula in Excel depending on your needs. One form is called vector and the other is array.

LOOKUP Function Syntax & Arguments

There are two ways to use the LOOKUP function:

Vector

The vector form searches through one row or one column only. That range is called the vector. The value that’s returned is whatever is in the same position as the other selected data set.

=LOOKUP(lookup_value, lookup_vector, [result_vector])

• lookup_value is the value the function should look for within the vector. It can be a number, text, logical value, name, or reference. This argument is required.
• lookup_vector is the range. It can be a single row or a single column. The values in the vector must be in ascending order (e.g., 1, 2, 3 or A, B, C). This argument is required.
• result_vector is an optional range. If used, it must be the exact same size as lookup_vector.

Here are some more rules to remember when you use the vector form of the LOOKUP function:

• If lookup_value is smaller than the smallest value in lookup_vector, Excel produces the error #N/A.
• If lookup_value can’t be found, the LOOKUP function matches the largest value in lookup_vector that’s less than or equal to lookup_value.

Array

The array form can search for a value in multiple rows and columns. It first locates the specified value in the first row or column of the selection and then returns the value of the same position in the last row or column.

=LOOKUP(lookup_value, array)

• lookup_value is the value the function should look for within the array. It can be a number, text, logical value, name, or reference. The values must be in ascending order (e.g., 1, 2, 3 or A, B, C). This argument is required.
• array is the range of cells that contain the value you’re comparing with lookup_value. This argument is required.

Also keep these rules in mind:

• If lookup_value can’t be found, the largest value in array that’s less than or equal to lookup_value is used instead.
• If lookup_value is smaller than the largest value in the first row or column, the error #N/A is returned.
• If array includes more columns than rows, the LOOKUP function searches for lookup_value in the first row.
• If array includes more rows than column, the LOOKUP function searches for lookup_value in the first column.

LOOKUP Function Examples

Following are several examples of how to use LOOKUP in your formulas:

Use Lookup Vector to Search Table

=LOOKUP(1003,A2:A5,C2:C5)

Here’s an example of how to use the LOOKUP function when we need to check the price in a table that’s organized by part number. Since we know that the part numbers are listed in A2:A5 and the prices are in C2:C5, we can search for part number 1003 using those parameters.

Use Lookup Array to Search Table

=LOOKUP(1003,A2:C5)

The other way to use the LOOKUP function on the same set of data as the example above, is with an array. Instead of choosing two single columns, we’re selecting the entire table. However, since we need the price in this example, we’re stopping the selection at column C since the function will grab whatever value is found in the same position in the last column.

Find Closest Number in Table

=LOOKUP(A2,D2:D6,F2:F6)

This LOOKUP formula is cross-referencing the score in column A with the grading system in column D. The LOOKUP function sees where the score falls on the grading system, and then it looks up the grade in F2:F6 to know what to write next to the score. Since some of those values aren’t found in the table to the right, LOOKUP uses the next lowest value.

This particular formula could also be written in array form like this:

=LOOKUP(A2,D2:F6)

The results are the same since column D is the start of the selection and the end, which holds the grade, is column F.

Locate Last Number in List

=LOOKUP(9.99999999999999E+307,A:A)

The LOOKUP formula finds the last number in column A. Since 9.99999999999999E+307 is the biggest number you can have in an Excel worksheet, the formula will show the last number in the list, even if there are blank cells included in the range.

Locate Last Text Value in List

=LOOKUP(REPT("z",255),A:A)

The example locates the last text value from column A. The REPT function is used here to repeat z to the maximum number that any text value can be, which is 255. Similar to the number example, this one simply identifies the last cell that contains text.

Use Table Data to Find Heading Values

=LOOKUP(2,1/(B3:G3 <> ""),B$2:G$2)

This final example of the Excel LOOKUP function involves some things not described in this article, but it’s worth a look anyway so that you can see just how useful this function can be. The general idea here is that we’re determining the last entry in each row and then looking up the date in row 2 to know the last time we paid those bills.

Other Functions Like LOOKUP

LOOKUP is a fairly basic lookup/reference function. Others exist that are good for more advanced uses.

VLOOKUP and HLOOKUP let you perform vertical or horizontal lookups and can specify whether to do an exact or approximate match. LOOKUP automatically returns the closest value if an exact match isn’t found.

XLOOKUP is a similar, more advanced lookup function.

Thanks for letting us know!

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

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

Для нахождения позиции текстовой строки в другой аналогичной применяют ПОИСК и ПОИСКБ. Расчет ведется с первого символа анализируемой ячейки. Так, если задать функцию ПОИСК “л” для слова «апельсин» мы получим значение 4, так как именно такой по счету выступает заданная буква в текстовом выражении.

Функция ПОИСК работает не только для поиска позиции отдельных букв в тексте, но и для целой комбинации. Например, задав данную команду для слов «book», «notebook», мы получим значение 5, так как именно с этого по счету символа начинается искомое слово «book».

Используют функцию ПОИСК наряду с такими, как:

• НАЙТИ (осуществляет поиск с учетом регистра);
• ПСТР (возвращает текст);
• ЗАМЕНИТЬ (заменяет символы).

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

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

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

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

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

1. Искомый текст. Это числовая и буквенная комбинация, позицию которой требуется найти.
2. Анализируемый текст. Это тот фрагмент текстовой информации, из которого требуется вычленить искомую букву или сочетание и вернуть позицию.
3. Начальная позиция. Данный фрагмент необязателен для ввода. Но, если вы желаете найти, к примеру, букву «а» в строке со значением «А015487.Мужская одежда», то необходимо указать в конце формулы 8, чтобы анализ этого фрагмента проводился с восьмой позиции, то есть после артикула. Если этот аргумент не указан, то он по умолчанию считается равным 1. При указании начальной позиции положение искомого фрагмента все равно будет считаться с первого символа, даже если начальные 8 были пропущены в анализе. То есть в рассматриваемом примере букве «а» в строке «А015487.Мужская одежда» будет присвоено значение 14.

При работе с аргументом «искомый_текст» можно использовать следующие подстановочные знаки.

1. Вопросительный знак (?). Он будет соответствовать любому знаку.
2. Звездочка (*). Этот символ будет соответствовать любой комбинации знаков.

Если же требуется найти подобные символы в строке, то в аргументе «искомый_текст» перед ними нужно поставить тильду (~).

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

Если «искомый_текст» не найден, возвращается значение ошибки #ЗНАЧ.



Пример использования функции ПОИСК и ПСТР

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

Введем исходные данные в таблицу:

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

=ПОИСК(“, тел.”;адрес_анализируемой_ячейки).

Нажмем Enter для отображения искомой информации:

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

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

Пример формулы ПОИСК и ЗАМЕНИТЬ

Пример 2. Есть таблица с текстовой информацией, в которой слово «маржа» нужно заменить на «объем».

Откроем книгу Excel с обрабатываемыми данными. Пропишем формулу для поиска нужного слова «маржа»:

Теперь дополним формулу функцией ЗАМЕНИТЬ:

Чем отличается функция ПОИСК от функции НАЙТИ в Excel?

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

Но опытный пользователь Excel знает, что отличие у этих двух функций очень существенные.

Отличие №1. Чувствительность к верхнему и нижнему регистру (большие и маленькие буквы). Функция НАЙТИ чувствительна к регистру символов. Например, есть список номенклатурных единиц с артикулом. Необходимо найти позицию маленькой буквы «о».

Теперь смотрите как ведут себя по-разному эти две функции при поиске большой буквы «О» в критериях поиска:

Отличие №2. В первом аргументе «Искомый_текст» для функции ПОИСК мы можем использовать символы подстановки для указания не точного, а приблизительного значения, которое должно содержаться в исходной текстовой строке. Вторая функция НАЙТИ не умеет использовать в работе символы подстановки масок текста: «*»; «?»; «~».

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

Скачать примеры функций ПОИСК и НАЙТИ в 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” находится на третьей позиции.

Функция ПОИСК() в MS EXCEL

Синтаксис функции

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

Искомый_текст — текст, который требуется найти.

Просматриваемая_строка — текст, в которой ищется Искомый_текст.

Нач_позиция — позиция знака в просматриваемой_строке, с которой должен начинаться поиск. Если аргумент нач_позиция опущен, то предполагается значение 1.

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

Если искомый_текст не найден, возвращается значение ошибки #ЗНАЧ!

Функция ПОИСК() не учитывает РЕгиСТР букв. Для поиска с учетом регистра следует воспользоваться функцией НАЙТИ() .

Формула =ПОИСК(«к»;»Первый канал») вернет 8, т.к. буква к находится на 8-й позиции слева.

Пусть в ячейке А2 введена строка Первый канал — лучший. Формула =ПОИСК(СИМВОЛ(32);A2) вернет 7, т.к. символ пробела (код 32) находится на 7-й позиции.

Формула =ПОИСК(«#. #»;»Артикул #123# ID») будет искать в строке «Артикул #123# ID» последовательность из 5 символов, которая начинается и заканчивается на знак #.

Чтобы найти позицию второго вхождения буквы «а» в строке «мама мыла раму» используйте формулу =ПОИСК(«а»;»мама мыла раму»;ПОИСК(«а»;»мама мыла раму»)+1). Чтобы определить есть ли третье вхождение буквы «м» в строке «мама мыла раму» используйте формулу =ЕСЛИ(ДЛСТР(ПОДСТАВИТЬ(«мама мыла раму»;»м»;»»;3))=ДЛСТР(«мама мыла раму»);»Нет третьего вхождения м»;»Есть третье вхождение м»)

Формула =ПОИСК(«клад?»;»докладная») вернет 3, т.е. в слове «докладная» содержится слово из 5 букв, первые 4 из которых клад (начиная с третьей буквы слова докладная).

Функция НАЙТИ() vs ПОИСК()

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

Связь с функциями ЛЕВСИМВ() , ПРАВСИМВ() и ПСТР()

Функция ПОИСК() может быть использована совместно с функциями ЛЕВСИМВ() , ПРАВСИМВ() и ПСТР() .

Например, в ячейке А2 содержится фамилия и имя «Иванов Иван», то формула =ЛЕВСИМВ(A2;ПОИСК(СИМВОЛ(32);A2)-1) извлечет фамилию, а =ПРАВСИМВ(A2;ДЛСТР(A2)-ПОИСК(СИМВОЛ(32);A2)) — имя. Если между именем и фамилией содержится более одного пробела, то для работоспособности вышеупомянутых формул используйте функцию СЖПРОБЕЛЫ() .

Поиск значения в столбце и строке таблицы Excel

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

Поиск значений в таблице Excel

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

Лист с таблицей для поиска значений по вертикали и горизонтали:

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

Поиск значения в строке Excel

Теперь узнаем, в каком максимальном объеме и в каком месяце была максимальная продажа Товара 4.

Чтобы выполнить поиск по столбцам следует:

1. В ячейку B1 введите значение Товара 4 – название строки, которое выступит в качестве критерия.
2. В ячейку D1 введите следующую формулу:
3. Для подтверждения после ввода формулы нажмите комбинацию горячих клавиш CTRL+SHIFT+Enter, так как формула должна быть выполнена в массиве. Если все сделано правильно, в строке формул появятся фигурные скобки.
4. В ячейку F1 введите вторую формулу:
5. Снова Для подтверждения нажмите комбинацию клавиш CTRL+SHIFT+Enter.

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

Принцип действия формулы поиска значения в строке Excel:

В первом аргументе функции ВПР (Вертикальный ПРосмотр) указывается ссылка на ячейку где находится критерий поиска. Во втором аргументе указывается диапазон ячеек для просмотра в процессе поиска. В третьем аргументе функции ВПР должен указываться номер столбца, из которого следует взять значение на против строки с именем Товар 4. Но так как нам заранее не известен этот номер мы с помощью функции СТОЛБЕЦ создаем массив номеров столбцов для диапазона B4:G15.

Это позволяет функции ВПР собрать целый массив значений. В результате в памяти хранится все соответствующие значения каждому столбцу по строке Товар 4 (а именно: 360; 958; 201; 605; 462; 832). После чего функции МАКС остается только взять из этого массива максимальное число и возвратить в качестве значения для ячейки D1, как результат вычисления формулы.

Как видно конструкция формулы проста и лаконична. На ее основе можно в похожий способ находить для определенного товара и другие показатели. Например, минимальное или среднее значение объема продаж используя для этого функции МИН или СРЗНАЧ. Вам ни что не препятствует, чтобы приведенный этот скелет формулы применить с использованием более сложных функций для реализации максимально комфортного анализа отчета по продажам.

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

Например, как эффектно мы отобразили месяц, в котором была максимальная продажа, с помощью второй формулы. Не сложно заметить что во второй формуле мы использовали скелет первой формулы без функции МАКС. Главная структура формулы: ВПР(B1;A5:G14;СТОЛБЕЦ(B5:G14);0). Мы заменили функцию МАКС на ПОИСКПОЗ, которая в первом аргументе использует значение, полученное предыдущей формулой. Оно теперь выступает в качестве критерия для поиска месяца. И в результате функция ПОИСКПОЗ нам возвращает номер столбца 2 где находится максимальное значение объема продаж для товара 4. После чего в работу включается функция ИНДЕКС, которая возвращает значение по номеру сроки и столбца из определенного в ее аргументах диапазона. Так как у нас есть номер столбца 2, а номер строки в диапазоне где хранятся названия месяцев в любые случаи будет 1. Тогда нам осталось функцией ИНДЕКС получить соответственное значение из диапазона B4:G4 – Февраль (второй месяц).

Поиск значения в столбце Excel

Вторым вариантом задачи будет поиск по таблице с использованием названия месяца в качестве критерия. В такие случаи мы должны изменить скелет нашей формулы: функцию ВПР заменить ГПР, а функция СТОЛБЕЦ заменяется на СТРОКА.

Это позволит нам узнать какой объем и какого товара была максимальная продажа в определенный месяц.

Чтобы найти какой товар обладал максимальным объемом продаж в определенном месяце следует:

1. В ячейку B2 введите название месяца Июнь – это значение будет использовано в качестве поискового критерия.
2. В ячейку D2 введите формулу:
3. Для подтверждения после ввода формулы нажмите комбинацию клавиш CTRL+SHIFT+Enter, так как формула будет выполнена в массиве. А в строке формул появятся фигурные скобки.
4. В ячейку F1 введите вторую формулу:
5. Снова Для подтверждения нажмите CTRL+SHIFT+Enter.

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

В первом аргументе функции ГПР (Горизонтальный ПРосмотр) указываем ссылку на ячейку с критерием для поиска. Во втором аргументе указана ссылка на просматриваемый диапазон таблицы. Третий аргумент генерирует функция СТРОКА, которая создает в памяти массив номеров строк из 10 элементов. Так как в табличной части у нас находится 10 строк.

Далее функция ГПР поочередно используя каждый номер строки создает массив соответственных значений продаж из таблицы по определенному месяцу (Июню). Далее функции МАКС осталось только выбрать максимальное значение из этого массива.

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

ВНИМАНИЕ! При использовании скелета формулы для других задач всегда обращайте внимание на второй и третий аргумент поисковой функции ГПР. Количество охваченных строк в диапазоне указанного в аргументе, должно совпадать с количеством строк в таблице. А также нумерация должна начинаться со второй строки!

По сути содержимое диапазона нас вообще не интересует, нам нужен просто счетчик строк. То есть изменить аргументы на: СТРОКА(B2:B11) или СТРОКА(С2:С11) – это никак не повлияет на качество формулы. Главное, что в этих диапазонах по 10 строк, как и в таблице. И нумерация начинается со второй строки!

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Строка поиска в Excel

Этот пример научит вас создавать собственную строку поиска в Excel.

Вот так выглядит таблица. Если ввести поисковый запрос в ячейку B2, Excel найдет совпадения в столбце E и выдаст результат в столбце B.

Чтобы создать эту строку поиска, следуйте инструкции ниже:

Выделите ячейку D4 и вставьте функцию SEARCH (ПОИСК), как показано ниже, указав абсолютную ссылку на ячейку В2.

=SEARCH($B$2,E4)
=ПОИСК($B$2;E4)

Дважды кликните по маркеру автозаполнения, который находится в правом нижнем углу ячейки D4, чтобы быстро скопировать формулу во все оставшиеся ячейки столбца D.Объяснение: Функция SEARCH (ПОИСК) ищет начальную позицию искомого значения в строке. Функция SEARCH (ПОИСК) не учитывает регистр. В слове “Tunisia” строка “uni” имеет начальное положение 2, а в слове “United States” начальное положение равно 1. Чем меньше значение, тем выше оно должно располагаться.

Снова дважды кликните по правому нижнему углу ячейки D4, чтобы быстро скопировать формулу в остальные ячейки столбца.Объяснение: Функция ROW (СТРОКА) возвращает номер строки ячейки. Если мы разделим номер строки на крупное число и прибавим это значение к результату функции SEARCH (ПОИСК), у нас всегда будут получаться уникальные значения, а небольшой прирост не повлияет на ранжирование. Теперь значение для “United States” составляет 1,00006, а для “United Kingdom” – 1,00009. Кроме этого мы добавили функцию IFERROR (ЕСЛИОШИБКА). Если ячейка содержит ошибку, к примеру, когда строка не может быть найдена, возвращается пустая строка (“”).

Дважды щелкните по правому углу ячейки С4, чтобы быстро скопировать формулу в другие ячейки.Объяснение: Функция RANK (РАНГ) возвращает порядковый номер значения. Если третий аргумент функции равен 1, Excel выстраивает числа по возрастанию: от наименьшего к большему. Поскольку мы добавили функцию ROW (СТРОКА), все значения в столбце D стали уникальными. Как следствие, числа в столбце C тоже уникальны.

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

Результат: Ваша собственная строка поиска в Excel.