Как в excel отобрать строки с определенным словом

Обновлено: 07.07.2024

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

Описание

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

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

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

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

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

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

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

Синтаксис

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

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

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

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

Замечание

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

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

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

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

Примеры

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

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

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

Базовая информация об ИНДЕКС и ПОИСКПОЗ

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

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

ИНДЕКС – синтаксис и применение функции

Функция INDEX (ИНДЕКС) в Excel возвращает значение из массива по заданным номерам строки и столбца. Функция имеет вот такой синтаксис:

Каждый аргумент имеет очень простое объяснение:

array (массив) – это диапазон ячеек, из которого необходимо извлечь значение.
row_num (номер_строки) – это номер строки в массиве, из которой нужно извлечь значение. Если не указан, то обязательно требуется аргумент column_num (номер_столбца).
column_num (номер_столбца) – это номер столбца в массиве, из которого нужно извлечь значение. Если не указан, то обязательно требуется аргумент row_num (номер_строки)

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

Вот простейший пример функции INDEX (ИНДЕКС):

Формула выполняет поиск в диапазоне A1:C10 и возвращает значение ячейки во 2-й строке и 3-м столбце, то есть из ячейки C2.

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

ПОИСКПОЗ – синтаксис и применение функции

Функция MATCH (ПОИСКПОЗ) в Excel ищет указанное значение в диапазоне ячеек и возвращает относительную позицию этого значения в диапазоне.

Функция MATCH (ПОИСКПОЗ) имеет вот такой синтаксис:

lookup_value (искомое_значение) – это число или текст, который Вы ищите. Аргумент может быть значением, в том числе логическим, или ссылкой на ячейку.
lookup_array (просматриваемый_массив) – диапазон ячеек, в котором происходит поиск.
match_type (тип_сопоставления) – этот аргумент сообщает функции ПОИСКПОЗ, хотите ли Вы найти точное или приблизительное совпадение:
- 1 или не указан – находит максимальное значение, меньшее или равное искомому. Просматриваемый массив должен быть упорядочен по возрастанию, то есть от меньшего к большему.
- 0 – находит первое значение, равное искомому. Для комбинации ИНДЕКС/ПОИСКПОЗ всегда нужно точное совпадение, поэтому третий аргумент функции ПОИСКПОЗ должен быть равен 0.
- -1 – находит наименьшее значение, большее или равное искомому значению. Просматриваемый массив должен быть упорядочен по убыванию, то есть от большего к меньшему.
На первый взгляд, польза от функции ПОИСКПОЗ вызывает сомнение. Кому нужно знать положение элемента в диапазоне? Мы хотим знать значение этого элемента!

Позвольте напомнить, что относительное положение искомого значения (т.е. номер строки и/или столбца) – это как раз то, что мы должны указать для аргументов row_num (номер_строки) и/или column_num (номер_столбца) функции INDEX (ИНДЕКС). Как Вы помните, функция ИНДЕКС может возвратить значение, находящееся на пересечении заданных строки и столбца, но она не может определить, какие именно строка и столбец нас интересуют.

Как использовать ИНДЕКС и ПОИСКПОЗ в Excel

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

Ещё не совсем понятно? Представьте функции ИНДЕКС и ПОИСКПОЗ в таком виде:

=INDEX( столбец из которого извлекаем ,(MATCH ( искомое значение , столбец в котором ищем ,0))
=ИНДЕКС( столбец из которого извлекаем ;(ПОИСКПОЗ( искомое значение ; столбец в котором ищем ;0))

Думаю, ещё проще будет понять на примере. Предположим, у Вас есть вот такой список столиц государств:

Давайте найдём население одной из столиц, например, Японии, используя следующую формулу:

Теперь давайте разберем, что делает каждый элемент этой формулы:

Вот такой результат получится в Excel:

Важно! Количество строк и столбцов в массиве, который использует функция INDEX (ИНДЕКС), должно соответствовать значениям аргументов row_num (номер_строки) и column_num (номер_столбца) функции MATCH (ПОИСКПОЗ). Иначе результат формулы будет ошибочным.

Стоп, стоп… почему мы не можем просто использовать функцию VLOOKUP (ВПР)? Есть ли смысл тратить время, пытаясь разобраться в лабиринтах ПОИСКПОЗ и ИНДЕКС?

В данном случае – смысла нет! Цель этого примера – исключительно демонстрационная, чтобы Вы могли понять, как функции ПОИСКПОЗ и ИНДЕКС работают в паре. Последующие примеры покажут Вам истинную мощь связки ИНДЕКС и ПОИСКПОЗ, которая легко справляется с многими сложными ситуациями, когда ВПР оказывается в тупике.

Почему ИНДЕКС/ПОИСКПОЗ лучше, чем ВПР?

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

Далее я попробую изложить главные преимущества использования ПОИСКПОЗ и ИНДЕКС в Excel, а Вы решите – остаться с ВПР или переключиться на ИНДЕКС/ПОИСКПОЗ.

4 главных преимущества использования ПОИСКПОЗ/ИНДЕКС в Excel:

1. Поиск справа налево. Как известно любому грамотному пользователю Excel, ВПР не может смотреть влево, а это значит, что искомое значение должно обязательно находиться в крайнем левом столбце исследуемого диапазона. В случае с ПОИСКПОЗ/ИНДЕКС, столбец поиска может быть, как в левой, так и в правой части диапазона поиска. Пример: Как находить значения, которые находятся слева покажет эту возможность в действии.

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

Например, если у Вас есть таблица A1:C10, и требуется извлечь данные из столбца B, то нужно задать значение 2 для аргумента col_index_num (номер_столбца) функции ВПР, вот так:

=VLOOKUP("lookup value",A1:C10,2)
=ВПР("lookup value";A1:C10;2)

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

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

Предположим, Вы используете вот такую формулу с ВПР, которая ищет в ячейках от B5 до D10 значение, указанное в ячейке A2:

Формула не будет работать, если значение в ячейке A2 длиннее 255 символов. Вместо неё Вам нужно использовать аналогичную формулу ИНДЕКС/ПОИСКПОЗ:

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

Влияние ВПР на производительность Excel особенно заметно, если рабочая книга содержит сотни сложных формул массива, таких как ВПР+СУММ. Дело в том, что проверка каждого значения в массиве требует отдельного вызова функции ВПР. Поэтому, чем больше значений содержит массив и чем больше формул массива содержит Ваша таблица, тем медленнее работает Excel.

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

ИНДЕКС и ПОИСКПОЗ – примеры формул

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

Как выполнить поиск с левой стороны, используя ПОИСКПОЗ и ИНДЕКС

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

Функции ПОИСКПОЗ и ИНДЕКС в Excel гораздо более гибкие, и им все-равно, где находится столбец со значением, которое нужно извлечь. Для примера, снова вернёмся к таблице со столицами государств и населением. На этот раз запишем формулу ПОИСКПОЗ/ИНДЕКС, которая покажет, какое место по населению занимает столица России (Москва).

Как видно на рисунке ниже, формула отлично справляется с этой задачей:

Теперь у Вас не должно возникать проблем с пониманием, как работает эта формула:

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

Вычисления при помощи ИНДЕКС и ПОИСКПОЗ в Excel (СРЗНАЧ, МАКС, МИН)

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

1. MAX (МАКС). Формула находит максимум в столбце D и возвращает значение из столбца C той же строки:

2. MIN (МИН). Формула находит минимум в столбце D и возвращает значение из столбца C той же строки:

3. AVERAGE (СРЗНАЧ). Формула вычисляет среднее в диапазоне D2:D10, затем находит ближайшее к нему и возвращает значение из столбца C той же строки:

О чём нужно помнить, используя функцию СРЗНАЧ вместе с ИНДЕКС и ПОИСКПОЗ

Используя функцию СРЗНАЧ в комбинации с ИНДЕКС и ПОИСКПОЗ, в качестве третьего аргумента функции ПОИСКПОЗ чаще всего нужно будет указывать 1 или -1 в случае, если Вы не уверены, что просматриваемый диапазон содержит значение, равное среднему. Если же Вы уверены, что такое значение есть, – ставьте 0 для поиска точного совпадения.
- Если указываете 1, значения в столбце поиска должны быть упорядочены по возрастанию, а формула вернёт максимальное значение, меньшее или равное среднему.
- Если указываете -1, значения в столбце поиска должны быть упорядочены по убыванию, а возвращено будет минимальное значение, большее или равное среднему.
Как при помощи ИНДЕКС и ПОИСКПОЗ выполнять поиск по известным строке и столбцу

Эта формула эквивалентна двумерному поиску ВПР и позволяет найти значение на пересечении определённой строки и столбца.

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

Как Вы помните, синтаксис функции INDEX (ИНДЕКС) позволяет использовать три аргумента:

И я поздравляю тех из Вас, кто догадался!

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

=INDEX( Ваша таблица ,(MATCH( значение для вертикального поиска , столбец, в котором искать ,0)),(MATCH( значение для горизонтального поиска , строка в которой искать ,0))
=ИНДЕКС( Ваша таблица ,(MATCH( значение для вертикального поиска , столбец, в котором искать ,0)),(MATCH( значение для горизонтального поиска , строка в которой искать ,0))

Обратите внимание, что для двумерного поиска нужно указать всю таблицу в аргументе array (массив) функции INDEX (ИНДЕКС).

А теперь давайте испытаем этот шаблон на практике. Ниже Вы видите список самых населённых стран мира. Предположим, наша задача узнать население США в 2015 году.

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

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

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

Эта формула возвращает значение на пересечении 4-ой строки и 5-го столбца в диапазоне A1:E11, то есть значение ячейки E4. Просто? Да!

Поиск по нескольким критериям с ИНДЕКС и ПОИСКПОЗ

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

Предположим, у нас есть список заказов, и мы хотим найти сумму по двум критериям – имя покупателя (Customer) и продукт (Product). Дело усложняется тем, что один покупатель может купить сразу несколько разных продуктов, и имена покупателей в таблице на листе Lookup table расположены в произвольном порядке.

Вот такая формула ИНДЕКС/ПОИСКПОЗ решает задачу:

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

MATCH(1,(A2='Lookup table'!$A$2:$A$13),0)*(B2='Lookup table'!$B$2:$B$13)
ПОИСКПОЗ(1;(A2='Lookup table'!$A$2:$A$13);0)*(B2='Lookup table'!$B$2:$B$13)

В формуле, показанной выше, искомое значение – это 1, а массив поиска – это результат умножения. Хорошо, что же мы должны перемножить и почему? Давайте разберем все по порядку:
- Берем первое значение в столбце A (Customer) на листе Main table и сравниваем его со всеми именами покупателей в таблице на листе Lookup table (A2:A13).
- Если совпадение найдено, уравнение возвращает 1 (ИСТИНА), а если нет – 0 (ЛОЖЬ).
- Далее, мы делаем то же самое для значений столбца B (Product).
- Затем перемножаем полученные результаты (1 и 0). Только если совпадения найдены в обоих столбцах (т.е. оба критерия истинны), Вы получите 1. Если оба критерия ложны, или выполняется только один из них – Вы получите 0.
Теперь понимаете, почему мы задали 1, как искомое значение? Правильно, чтобы функция ПОИСКПОЗ возвращала позицию только, когда оба критерия выполняются.

Обратите внимание: В этом случае необходимо использовать третий не обязательный аргумент функции ИНДЕКС. Он необходим, т.к. в первом аргументе мы задаем всю таблицу и должны указать функции, из какого столбца нужно извлечь значение. В нашем случае это столбец C (Sum), и поэтому мы ввели 3.

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

Если всё сделано верно, Вы получите результат как на рисунке ниже:

ИНДЕКС и ПОИСКПОЗ в сочетании с ЕСЛИОШИБКА в Excel

Синтаксис функции ЕСЛИОШИБКА очень прост:

Где аргумент value (значение) – это значение, проверяемое на предмет наличия ошибки (в нашем случае – результат формулы ИНДЕКС/ПОИСКПОЗ); а аргумент value_if_error (значение_если_ошибка) – это значение, которое нужно возвратить, если формула выдаст ошибку.

Например, Вы можете вставить формулу из предыдущего примера в функцию ЕСЛИОШИБКА вот таким образом:

=IFERROR(INDEX($A$1:$E$11,MATCH($G$2,$B$1:$B$11,0),MATCH($G$3,$A$1:$E$1,0)),
"Совпадений не найдено. Попробуйте еще раз!") =ЕСЛИОШИБКА(ИНДЕКС($A$1:$E$11;ПОИСКПОЗ($G$2;$B$1:$B$11;0);ПОИСКПОЗ($G$3;$A$1:$E$1;0));
"Совпадений не найдено. Попробуйте еще раз!")

И теперь, если кто-нибудь введет ошибочное значение, формула выдаст вот такой результат:

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

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

СЦЕПИТЬ

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

СТРОЧН

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

ПРОПИСН

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

ПРОПНАЧ

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

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

ДЛСТР

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

ЛЕВСИМВ и ПРАВСИМВ

Текстовые функции ЛЕВСИМВ и ПРАВСИМВ возвращают заданное количество символов, начиная с начала или с конца строки. Пробел считается за символ.

Текстовая функция ПСТР возвращает заданное количество символов, начиная с указанной позиции. Пробел считается за символ.

СОВПАД

Функция СОВПАД позволяет сравнить две текстовые строки в Excel. Если они в точности совпадают, то возвращается значение ИСТИНА, в противном случае – ЛОЖЬ. Данная текстовая функция учитывает регистр, но игнорирует различие в форматировании.

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

СЖПРОБЕЛЫ

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

В случаях, когда наличие лишнего пробела в конце или начале строки сложно отследить, данная функция становится просто незаменимой. На рисунке ниже видно, что содержимое ячеек А1 и B1 абсолютно одинаково, но это не так. В ячейке А1 мы намеренно поставили лишний пробел в конце слова Excel. В итоге функция СОВПАД возвратила нам значение ЛОЖЬ.

Применив функцию СЖПРОБЕЛЫ к значению ячейки А1, мы удалим из него все лишние пробелы и получим корректный результат:

Функцию СЖПРОБЕЛЫ полезно применять к данным, которые импортируются в рабочие листы Excel из внешних источников. Такие данные очень часто содержат лишние пробелы и различные непечатаемые символы. Чтобы удалить все непечатаемые символы из текста, необходимо воспользоваться функцией ПЕЧСИМВ.

ПОВТОР

Функция ПОВТОР повторяет текстовую строку указанное количество раз. Строка задается как первый аргумент функции, а количество повторов как второй.

НАЙТИ

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

ПОИСК

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

ПОДСТАВИТЬ

Заменяет определенный текст или символ на требуемое значение. В Excel текстовую функцию ПОДСТАВИТЬ применяют, когда заранее известно какой текст необходимо заменить, а не его местоположение.

Удаляет все пробелы из текстовой строки:

ЗАМЕНИТЬ

Заменяет символы, расположенные в заранее известном месте строки, на требуемое значение. В Excel текстовую функцию ЗАМЕНИТЬ применяют, когда известно где располагается текст, при этом сам он не важен.

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

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

Для начала с одного из следующих начните:

Чтобы найти конкретные ячейки на всем ячеек, щелкните любую из них.

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

Совет: Чтобы отменить выделение ячеек, щелкните любую ячейку на листе.

На вкладке Главная нажмите кнопку & выберите > Перейти (в группе Редактирование).

Сочетания клавиш: Нажмите CTRL+G.

В диалоговом окне Перейти к специальным выберите один из следующих параметров:

Чтобы выделить

Ячейки с прикомментами.

Ячейки, содержащие константы.

Ячейки, содержащие формулы.

Примечание: Тип формулы определяется флажками под формулами.

Текущая область

Список текущая область, например весь список.

Текущий массив

Если массив массив содержится активная ячейка массиве, это может быть целая активная ячейка.

Графические объекты, в том числе диаграммы и кнопки, на месте и в текстовых полях.

Различия между строками

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

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

Различия между столбцами

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

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

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

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

Щелкните Все уровни, чтобы найти все ячейки, на которые прямо или косвенно ссылается выделение.

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

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

Щелкните Все уровни, чтобы найти все ячейки, которые прямо или косвенно ссылаются на активную ячейку.

Последняя ячейка

Последняя ячейка на этом сайте с данными или форматированием.

Только видимые ячейки

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

Условные форматы

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

Проверка данных

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

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

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

Описание

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

Создание образца листа

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

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

Определения терминов

В этой статье для описания встроенных функций Excel используются указанные ниже условия.

Определение

Вся таблица подстановки

Значение, которое будет найдено в первом столбце аргумента «инфо_таблица».

Просматриваемый_массив
-или-
Лукуп_вектор

Диапазон ячеек, которые содержат возможные значения подстановки.

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

3 (третий столбец в инфо_таблица)

Ресулт_аррай
-или-
Ресулт_вектор

Диапазон, содержащий только одну строку или один столбец. Он должен быть такого же размера, что и просматриваемый_массив или Лукуп_вектор.

Логическое значение (истина или ложь). Если указано значение истина или опущено, возвращается приближенное соответствие. Если задано значение FALSE, оно будет искать точное совпадение.

Число столбцов, находящегося слева или справа от которых должна указываться верхняя левая ячейка результата. Например, значение "5" в качестве аргумента Оффсет_кол указывает на то, что верхняя левая ячейка ссылки состоит из пяти столбцов справа от ссылки. Оффсет_кол может быть положительным (то есть справа от начальной ссылки) или отрицательным (то есть слева от начальной ссылки).

Функции

LOOKUP ()

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

Ниже приведен пример синтаксиса формулы подСТАНОВКи.

= Просмотр (искомое_значение; Лукуп_вектор; Ресулт_вектор)

Следующая формула находит возраст Марии на листе "образец".

= ПРОСМОТР (E2; A2: A5; C2: C5)

Формула использует значение «Мария» в ячейке E2 и находит слово «Мария» в векторе подстановки (столбец A). Формула затем соответствует значению в той же строке в векторе результатов (столбец C). Так как "Мария" находится в строке 4, функция Просмотр возвращает значение из строки 4 в столбце C (22).

Примечание. Для функции Просмотр необходимо, чтобы таблица была отсортирована.

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

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

Ниже приведен пример синтаксиса формулы ВПР :

= ВПР (искомое_значение; инфо_таблица; номер_столбца; интервальный_просмотр)

Следующая формула находит возраст Марии на листе "образец".

= ВПР (E2; A2: C5; 3; ЛОЖЬ)

Формула использует значение «Мария» в ячейке E2 и находит слово «Мария» в левом столбце (столбец A). Формула затем совпадет со значением в той же строке в Колумн_индекс. В этом примере используется "3" в качестве Колумн_индекс (столбец C). Так как "Мария" находится в строке 4, функция ВПР возвращает значение из строки 4 В столбце C (22).

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

INDEX () и MATCH ()

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

Ниже приведен пример синтаксиса, объединяющего индекс и Match для получения одинаковых результатов поиска и ВПР в предыдущих примерах:

= Индекс (инфо_таблица; MATCH (искомое_значение; просматриваемый_массив; 0); номер_столбца)

Следующая формула находит возраст Марии на листе "образец".

= ИНДЕКС (A2: C5; MATCH (E2; A2: A5; 0); 3)

Формула использует значение «Мария» в ячейке E2 и находит слово «Мария» в столбце A. Затем он будет соответствовать значению в той же строке в столбце C. Так как "Мария" находится в строке 4, формула возвращает значение из строки 4 в столбце C (22).

СМЕЩ () и MATCH ()

Функции СМЕЩ и ПОИСКПОЗ можно использовать вместе, чтобы получить те же результаты, что и функции в предыдущем примере.

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

= СМЕЩЕНИЕ (топ_целл, MATCH (искомое_значение; просматриваемый_массив; 0); Оффсет_кол)

Эта формула находит возраст Марии на листе "образец".

= СМЕЩЕНИЕ (A1; MATCH (E2; A2: A5; 0); 2)

Формула использует значение «Мария» в ячейке E2 и находит слово «Мария» в столбце A. Формула затем соответствует значению в той же строке, но двум столбцам справа (столбец C). Так как "Мария" находится в столбце A, формула возвращает значение в строке 4 в столбце C (22).

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

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