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

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

Как в экселе найти нужное слово по ячейкам

Для отображения адресов тех ячеек, которые содержат то, что вы пытаетесь отыскать, следует придерживаться следующих шагов:

1. Если вы являетесь пользователем программы 2010 года, стоит перейти к меню, после чего кликнуть по «Правке», и затем «Найти».
2. Далее откроется окошко, в котором предстоит пропечатать искомую фразу.
3. Программа предыдущей версии располагает данной кнопкой в меню под названием «Главная», расположенная на панели редактирования.
4. Подобного же результата возможно достигать в любой из версий, одновременно воспользовавшись кнопками Ctrl, а также, F.
5. В поле следует пропечатать фразу, искомые слова либо цифры.
6. Нажав «Найти все», вы запустите поиск по абсолютно всему файлу. Кликнув «Далее», программа по одной клеточке, располагающихся под курсором-ячейкой файла, будет их выделять.
7. Стоит подождать, пока процесс завершится. При этом чем объемнее документ, тем больше времени уйдет на поиск.
8. Возникнет список результатов: имена и адреса клеточек, которые содержат в себе совпадения с указанным значением либо фразой.
9. Кликнув на любую строчку, будет выделена соответствующая ячейка.
10. С целью удобства, можно «растягивать» окно. Таким образом в нем будет виднеться больше строк.
11. Для сортировки данных, необходимо кликать на названиях столбиков над найденными результатами. Нажав на «Лист», строки будут выстроены по алфавиту зависимо от наименования листа, а выбрав «Значения» — расположатся в зависимости от значения. К слову, данные столбики тоже можно «растянуть».

Поисковые параметры

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

1. Следует ввести лишь частичку надписи. Можно даже одну из букв – будут обозначены все участки, где она имеется.
2. Применяйте значки «звездочка», а аткже, знак вопроса. Они способны заместить пропущенные символы.
3. Вопросом обозначается одна недостающая позиция. Если, например, вы пропечатаете «А????», будут отображены ячейки, которые содержат слово из пяти символов, которое начинается с «А».
4. Благодаря звездочке, замещается любое количество знаков. Для поиска всех значений, содержащих корень «раст», следует начать искать согласно ключу «раст*».

Кроме того, вы можете посещать настройки:

• В окошке поиска следует кликнуть «Параметры».
• В разделах просмотра и области поиска, необходимо будет указать, где именно и на основании каких критериев нужно отыскивать совпадения. Можно подобрать формулы, различные примечания либо значения.
• Для различия системой строчных и прописных букв, нужно выставить галочку в поле под названием «Учитывать регистр».
• Отметив пункт «Ячейка полностью», результаты отобразятся в виде клеток, содержащих исключительно заданную поисковую фразу, и ничего кроме нее.

Настройки форматирования ячеек

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

1. В окошке поиска следует кликнуть по параметрам и нажать клавишу «Формат». Будет открыто меню, содержащее несколько вкладок.
2. Можно указывать тот или иной шрифт, тип рамочки, окраску фона, а также, формат вводимых данных. Системой будут просмотрены те участки, которые соответствуют обозначенным критериям.
3. Для взятия информации из текущей клеточки (выделенной на данный момент), следует кликнуть «Использовать формат данной ячейки». В таком случае программой будут найдены все значения, обладающие тем же размером и типом символов, той же окраской, а также, теми же границами и т.п.

Как найти несколько слов в Excel

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

Для посика не одного, а одновременно нескольких слов, необходимо выполнить следующее:

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

Применяем фильтр

Итак, для того, чтобы воспользоваться фильтром, необходимо:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

INDEX(A1:C10,2,3)
=ИНДЕКС(A1:C10;2;3)

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

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

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

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

Например, если в диапазоне B1:B3 содержатся значения New-York, Paris, London, тогда следующая формула возвратит цифру 3 , поскольку “London” – это третий элемент в списке.

MATCH("London",B1:B3,0)
=ПОИСКПОЗ("London";B1:B3;0)

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

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

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

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

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

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

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

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

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

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

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

INDEX($D$2:$D$10,MATCH("Japan",$B$2:$B$10,0))
=ИНДЕКС($D$2:$D$10;ПОИСКПОЗ("Japan";$B$2:$B$10;0))

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

• Функция MATCH (ПОИСКПОЗ) ищет значение “Japan” в столбце B , а конкретно – в ячейках B2:B10 , и возвращает число 3 , поскольку “Japan” в списке на третьем месте.
• Функция INDEX (ИНДЕКС) использует 3 для аргумента row_num (номер_строки), который указывает из какой строки нужно возвратить значение. Т.е. получается простая формула:

  INDEX($D$2:$D$10,3)
  =ИНДЕКС($D$2:$D$10;3)

  Формула говорит примерно следующее: ищи в ячейках от D2 до D10 и извлеки значение из третьей строки, то есть из ячейки D4 , так как счёт начинается со второй строки.

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

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

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

VLOOKUP("Japan",$B$2:$D$2,3)
=ВПР("Japan";$B$2:$D$2;3)

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

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

Решая, какую формулу использовать для вертикального поиска, большинство гуру 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 , иначе формула возвратит результат из только что вставленного столбца.

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

3. Нет ограничения на размер искомого значения. Используя ВПР , помните об ограничении на длину искомого значения в 255 символов, иначе рискуете получить ошибку #VALUE! (#ЗНАЧ!). Итак, если таблица содержит длинные строки, единственное действующее решение – это использовать ИНДЕКС /ПОИСКПОЗ .

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

VLOOKUP(A2,B5:D10,3,FALSE)
=ВПР(A2;B5:D10;3;ЛОЖЬ)

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

INDEX(D5:D10,MATCH(TRUE,INDEX(B5:B10=A2,0),0))
=ИНДЕКС(D5:D10;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B5:B10=A2;0);0))

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

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

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

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

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

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

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

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

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

INDEX($A$2:$A$10,MATCH("Russia",$B$2:$B$10,0))

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

• Во-первых, задействуем функцию MATCH (ПОИСКПОЗ), которая находит положение “Russia” в списке:

  MATCH("Russia",$B$2:$B$10,0))
  =ПОИСКПОЗ("Russia";$B$2:$B$10;0))

• Далее, задаём диапазон для функции INDEX (ИНДЕКС), из которого нужно извлечь значение. В нашем случае это A2:A10 .
• Затем соединяем обе части и получаем формулу:

  INDEX($A$2:$A$10;MATCH("Russia";$B$2:$B$10;0))
  =ИНДЕКС($A$2:$A$10;ПОИСКПОЗ("Russia";$B$2:$B$10;0))

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

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

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

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

INDEX($C$2:$C$10,MATCH(MAX($D$2:I$10),$D$2:D$10,0))
=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(МАКС($D$2:I$10);$D$2:D$10;0))

Результат: Beijing

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

INDEX($C$2:$C$10,MATCH(MIN($D$2:I$10),$D$2:D$10,0))
=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(МИН($D$2:I$10);$D$2:D$10;0))

Результат: Lima

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

INDEX($C$2:$C$10,MATCH(AVERAGE($D$2:D$10),$D$2:D$10,1))
=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(СРЗНАЧ($D$2:D$10);$D$2:D$10;1))

Результат: Moscow

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

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

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

В нашем примере значения в столбце D упорядочены по возрастанию, поэтому мы используем тип сопоставления 1 . Формула ИНДЕКС /ПОИСКПО З возвращает “Moscow”, поскольку величина населения города Москва – ближайшее меньшее к среднему значению (12 269 006).

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

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

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

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

INDEX(array,row_num,)
ИНДЕКС(массив;номер_строки;[номер_столбца])

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

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

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

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

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

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

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

• ПОИСКПОЗ для столбца – мы ищем в столбце B , а точнее в диапазоне B2:B11 , значение, которое указано в ячейке H2 (USA). Функция будет выглядеть так:

  MATCH($H$2,$B$1:$B$11,0)
  =ПОИСКПОЗ($H$2;$B$1:$B$11;0)

  4 , поскольку “USA” – это 4-ый элемент списка в столбце B (включая заголовок).

• ПОИСКПОЗ для строки – мы ищем значение ячейки H3 (2015) в строке 1 , то есть в ячейках A1:E1 :

  MATCH($H$3,$A$1:$E$1,0)
  =ПОИСКПОЗ($H$3;$A$1:$E$1;0)

  Результатом этой формулы будет 5 , поскольку “2015” находится в 5-ом столбце.

Теперь вставляем эти формулы в функцию ИНДЕКС и вуаля:

INDEX($A$1:$E$11,MATCH($H$2,$B$1:$B$11,0),MATCH($H$3,$A$1:$E$1,0))
=ИНДЕКС($A$1:$E$11;ПОИСКПОЗ($H$2;$B$1:$B$11;0);ПОИСКПОЗ($H$3;$A$1:$E$1;0))

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

INDEX($A$1:$E$11,4,5))
=ИНДЕКС($A$1:$E$11;4;5))

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

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

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

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

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

{=INDEX("Lookup table"!$A$2:$C$13,MATCH(1,(A2="Lookup table"!$A$2:$A$13)*
(B2="Lookup table"!$B$2:$B$13),0),3)}
{=ИНДЕКС("Lookup table"!$A$2:$C$13;ПОИСКПОЗ(1;(A2="Lookup table"!$A$2:$A$13)*
(B2="Lookup table"!$B$2:$B$13);0);3)}

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

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

Как Вы, вероятно, уже заметили (и не раз), если вводить некорректное значение, например, которого нет в просматриваемом массиве, формула ИНДЕКС /ПОИСКПОЗ сообщает об ошибке #N/A (#Н/Д) или #VALUE! (#ЗНАЧ!). Если Вы хотите заменить такое сообщение на что-то более понятное, то можете вставить формулу с ИНДЕКС и ПОИСКПОЗ в функцию ЕСЛИОШИБКА .

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

IFERROR(value,value_if_error)
ЕСЛИОШИБКА(значение;значение_если_ошибка)

Где аргумент 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));
"Совпадений не найдено. Попробуйте еще раз!")

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

Если Вы предпочитаете в случае ошибки оставить ячейку пустой, то можете использовать кавычки (“”), как значение второго аргумента функции ЕСЛИОШИБКА . Вот так:

IFERROR(INDEX(массив,MATCH(искомое_значение,просматриваемый_массив,0),"")
ЕСЛИОШИБКА(ИНДЕКС(массив;ПОИСКПОЗ(искомое_значение;просматриваемый_массив;0);"")

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

Работа с VB проектом (12)
Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (63)
Разное (39)
Баги и глюки Excel (3)

Найти в ячейке любое слово из списка

Предположим, вы получаете от поставщика/заказчика/клиента заполненную таблицу с перечнем продукции:

И эту таблицу необходимо сравнить с артикулами/кодами товара в имеющемся у вас каталоге продукции:

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


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

(49,5 KiB, 13 249 скачиваний)

На листе "Заказ" в этом файле таблица, полученная от заказчика, а на листе "Каталог" наши артикулы.
Сама формула на примере файла будет выглядеть так:

ПРОСМОТР(2;1/ПОИСК(Каталог!$A$2:$A$11; A2);Каталог!$A$2:$A$11)
=LOOKUP(2,1/SEARCH(Каталог!$A$2:$A$11,A2),Каталог!$A$2:$A$11)
эта формула вернет название артикула, если в тексте есть хоть один артикул из каталога и #Н/Д (#N/A) если артикул не найден в каталоге.
Прежде чем облагородить эту формулу всякими дополнениями(вроде виде убирания ненужных #Н/Д) давайте разберемся как она работает.
Функция ПРОСМОТР (LOOKUP) ищет заданное значение(2) в указанном диапазоне(массиве - второй аргумент). В качестве диапазона обычно приводится массив ячеек, но функция ПРОСМОТР имеет первую нужную нам особенность - она старается преобразовать непосредственно в массив любое выражение, записанное вторым аргументом. Иными словами она вычисляет выражение в этом аргументе, чем мы и пользуемся, подставив в качестве второго аргумента выражение: 1/ПОИСК(Каталог!$A$2:$A$11;A2) . Часть ПОИСК(Каталог!$A$2:$A$11;A2) ищет поочередно каждое значение из списка Каталога в ячейке A2 (наименование из таблицы Заказчика). Если значение найдено, то возвращается номер позиции первого символа найденного значения. Если значение не найдено - возвращается значение ошибки #ЗНАЧ!(#VALUE!). Теперь вторая особенность: функция требует расположения данных в массиве в порядке возрастания. Если данные расположены иначе - функция будет просматривать массив до тех пор, пока не найдет значение больше искомого, но максимально к нему приближенное(хотя если данные позволяют - для более точного поиска все же лучше отсортировать список по возрастанию). Поэтому сначала мы 1 делим на выражение ПОИСК(Каталог!$A$2:$A$11;A2) , чтобы получить массив вида: {0,0181818181818182:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!}
Ну а в качестве искомого значения мы подсовываем функции число 2 - заведомо большее число, чем может вообще встретиться в массиве(т.к. единица, поделенная на любое число будет меньше двух). И как результат мы получим позицию в массиве, в которой встречается последнее совпадение из каталога. После чего функция ПРОСМОТР запомнит эту позицию и вернет значение из массива Каталог!$A$2:$A$11 (третий аргумент), записанное в этом массиве для этой позиции.
Вы можете просмотреть этапы вычисления функции самостоятельно для каждой ячейки, я здесь просто приведу этапы чуть в расширенном для понимания виде:

1. =ПРОСМОТР(2;1/ПОИСК(Каталог!$A$2:$A$11; A2);Каталог!$A$2:$A$11)
2. =ПРОСМОТР(2;
   1/{55:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!};
   Каталог!$A$2:$A$11)
3. =ПРОСМОТР(2;{0,0181818181818182:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!};Каталог!$A$2:$A$11)
4. =ПРОСМОТР(2;
   1;
   {"FM2-3320":"CV455689":"Q5949X":"CE321A":"CE322A":"CE323A":"00064073":"CX292708":"CX292709":"CX292710"})
5. ="FM2-3320"

Теперь немного облагородим функцию и сделаем еще пару реализаций
Реализация 1:
Вместо артикулов и #Н/Д выведем для найденных позиций "Есть" , а для отсутствующих "Не найден в каталоге" :
=ЕСЛИ(ЕНД(ПРОСМОТР(2;1/ПОИСК(Каталог!$A$2:$A$11;A2)));"Не найден в каталоге";"Есть")
=IF(ISNA(LOOKUP(2,1/SEARCH(Каталог!$A$2:$A$11,A2))),"Не найден в каталоге","Есть")
работа функции проста - с ПРОСМОТР(LOOKUP) разобрались, поэтому остались только ЕНД и ЕСЛИ.
ЕНД (ISNA) возвращает ИСТИНА (TRUE) если выражение внутри неё возвращает значение ошибки #Н/Д (#N/A) и ЛОЖЬ (FALSE) если выражение внутри не возвращает значение этой ошибки.
ЕСЛИ (IF) возвращает то, что указано вторым аргументом если выражение в первом равно ИСТИНА (TRUE) и то, что указано третьим аргументом, если выражение первого аргумента ЛОЖЬ (FALSE) .

Реализация 2:
Вместо #Н/Д выведем "Не найден в каталоге" , но при этом если артикулы найдены - выведем названия этих артикулов:
=ЕСЛИОШИБКА(ПРОСМОТР(2;1/ПОИСК(Каталог!$A$2:$A$11;A2);Каталог!$A$2:$A$11);"Нет в каталоге")
=IFERROR(LOOKUP(2,1/SEARCH(Каталог!$A$2:$A$11,A2),Каталог!$A$2:$A$11),"Нет в каталоге")
Про функция ЕСЛИОШИБКА (IFERROR) я подробно рассказывал в этой статье: Как в ячейке с формулой вместо ошибки показать 0 .
Если вкратце, то если выражение, заданное первым аргументом функции, возвращает значение любой ошибки, то функция вернет то, что записано вторым аргументом(в нашем случае это текст "Не найден в каталоге"). Если же выражение не возвращает ошибку, то функция ЕСЛИОШИБКА запишет то значение, которое было получено выражением в первом аргументе(в нашем случае это будет наименование артикула).

Реализация 3
Надо не просто определить какому артикулу соответствует, но и вывести цену для наименования по этому артикулу(сами цены должны быть расположены в столбце B листа Каталог):
=ЕСЛИОШИБКА(ПРОСМОТР(2;1/ПОИСК(Каталог!$A$2:$A$11;A2);Каталог!$B$2:$B$11);"")
=IFERROR(LOOKUP(2,1/SEARCH(Каталог!$A$2:$A$11,A2),Каталог!$B$2:$B$11),"")

Пара важных замечаний:

• данные на листе с артикулами не должны содержать пустых ячеек. Иначе с большой долей вероятности формула будет возвращать значение именно пустой ячейки, а не то, которое подходит под условия поиска
• формула осуществляет поиск таким образом, что ищется любое совпадение. Например, в качестве артикула записана цифра 1 , а в строке наименований может встречаться помимо целой 1 еще и 123 , 651123 , FG1412NM и т.п. Для всех этих наименований может быть подобран артикул 1, т.к. он содержится в каждом наименовании. Как правило это может произойти, если артикул 1 расположен в конце списка

Поэтому желательно перед использованием формулы отсортировать список по возрастанию(от меньшего к большему, от А до Я).

В приложенном в начале статьи примере вы найдете все разобранные варианты.

Если же вам понадобится выводить все наименования, то можно воспользоваться функцией СОДЕРЖИТ_ОДНО_ИЗ из моей надстройки MulTEx .

Статья помогла? Поделись ссылкой с друзьями! Видеоуроки

{"Bottom bar":{"textstyle":"static","textpositionstatic":"bottom","textautohide":true,"textpositionmarginstatic":0,"textpositiondynamic":"bottomleft","textpositionmarginleft":24,"textpositionmarginright":24,"textpositionmargintop":24,"textpositionmarginbottom":24,"texteffect":"slide","texteffecteasing":"easeOutCubic","texteffectduration":600,"texteffectslidedirection":"left","texteffectslidedistance":30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"slide","texteffectslidedirection1":"right","texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic","texteffectduration1":600,"texteffectdelay1":1000,"texteffect2":"slide","texteffectslidedirection2":"right","texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic","texteffectduration2":600,"texteffectdelay2":1500,"textcss":"display:block; padding:12px; text-align:left;","textbgcss":"display:block; position:absolute; top:0px; left:0px; width:100%; height:100%; background-color:#333333; opacity:0.6; filter:alpha(opacity=60);","titlecss":"display:block; position:relative; font:bold 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff;","descriptioncss":"display:block; position:relative; font:12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff; margin-top:8px;","buttoncss":"display:block; position:relative; margin-top:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"font-size:12px;","descriptioncssresponsive":"display:none !important;","buttoncssresponsive":"","addgooglefonts":false,"googlefonts":"","textleftrightpercentforstatic":40}}

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

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

Что такое Поиск решений

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

Где в Excel поиск решений

Надстройка Поиск решений поставляется вместе с Excel, но по умолчанию отключена. Чтобы включить его, перейдите по вкладке Файл в группу Параметры. В появившемся диалоговом окне Параметры, выберите Надстройки -> Управление: Надстройки Excel -> Перейти. В окне Надстройки устанавливаем галочку напротив поля Поиск решения, жмем ОК.

Теперь во вкладке Данные появилась новая группа Анализ с кнопкой Поиск решения.

Пример использования Поиска решения

Данный пост основан на примере использования . Файл совместим со всеми версиями Excel.

Определение проблемы

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

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

Для начала требуется определить каждый пункт к какой-нибудь группе.

Чтобы указать привязанность пункта к группе, будем помечать их единицей (1), в противном случае нулем (0).

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

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

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

Наша задача минимизировать разницу между суммами групп.

Теперь мы можем присвоить каждой группе пункты, для этого вручную проставляем единицы в столбцах С и D. Excel отобразит разницу сумм групп в ячейке G11.

Проблема в том, что количество возможных комбинаций 2 8 , т.е. 256 вероятных ответов на вопрос. Если на каждый из них тратить по 5 секунд, это займет у нас 21,3 минуты, предполагая, что мы сможем выдержать темп и запомнить лучшую комбинацию.

Вот где Поиск решения находит применение.

Поиск оптимального решения в Excel

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

Наши правила

Наше основное требование – это минимизировать разницу между двумя группами. В нашем примере она находится в ячейке G11 – Группа B минус Группа A. Нам нужно, чтобы значение в ячейке G11 было настолько малым насколько это возможно, но больше или равно 0.

Мы также знаем, что пункт может находиться либо в Группе A, либо в Группе B, к тому он не может быть дробным. Таким образом у нас два ограничения для каждого элемента:

Во-первых: Значение элемента в колонке Итог должна равняться единице.

Во-вторых: Значения элементов в группах должны быть целыми.

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

Диалоговое окно Поиска решения

В этом разделе описано окно надстройки Поиск решения и его использования для определения проблемы.

Пустое окно Поиска решения

Заполненное окно Поиска решения

Оптимизировать целевую функцию

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

До

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

Мы хотим, чтобы суммы обоих групп совпадали, т.е. чтобы разница сумм была равна 0. Это может показаться странным, но нам не требуется минимизировать разницу, потому что при этом все элементы будут помещены в Группу A, что приведет к значению ячейки G11 меньше нуля.

Другой способ наложения ограничения – изменить G11 на =ABS(G10-F10). При этом мы сможем установить маркер на Минимум, как результат достижения целевой функции.

Но пока мы остановимся на формуле =G10-F10 и установим маркер в значение равным 0.

Изменяя ячейки переменных

Изменяемые ячейки – ячейки, которые надстройка попытается изменить, чтобы решить задачу. В нашем случае это привязка элемента к конкретной группе: $ C$2:$ D$9.

В соответствии с ограничениями

Ограничения – это правила, которые лимитируют возможные решения проблемы.

Нам необходимо добавить несколько ограничений в наш список:

1. В колонке Итого каждый элемент должен равняться 1
2. Элементы групп должны быть целым числом
3. Сумма значений столбца Итого должна равняться 8

Чтобы наложить ограничения, жмем кнопку Добавить

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

Загрузить/сохранить параметры поиска решений

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

Запуск поиска оптимального решения в Excel

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

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

Два параметра, которые необходимо будет менять время от времени:

Точность ограничения: значение от 0 до 1, где, чем больше цифра, тем больше ограничение

Целочисленная оптимальность: показывает насколько далеко от целого числа ограничение имеет право быть.

Запуск модели

Чтобы запустить надстройку нажмите кнопку Найти решение в основном окне.

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

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

Теперь у вас есть 4 варианта на выбор:

— Восстановить исходные значения

Запустить отчет

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

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

Сохранить сценарий

Где необходимо ввести название вашего сценария модели и нажать кнопку ОК.

Все сценарии доступны в Диспетчере сценариев , который находится во вкладке Данные в группе Работа с данными –> Анализ что-если -> Диспетчер сценариев.

Дата: 3 января 2016 Категория:

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

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

Для поиска текста в Microsoft Excel есть две функции:

1. НАЙТИ() . Эта функция имеет 2 обязательных аргумента и один необязательный:
   • Текст для поиска – та последовательность символов, которую нужно найти. Это обязательный аргумент
   • Исходный текст – строка, в которой осуществляется поиск. Обязательный аргумент
   • Начальная позиция – порядковый номер символа в исходном тексте, с которого начинается поиск. Необязательный аргумент . Если он не задан, поиск начинается с первого символа

Функция НАЙТИ учитывает и не поддерживает использование подстановочных символов (о них читайте дальше).

Функция НАЙТИ в Эксель

1. ПОИСК(Текст для поиска; Исходный текст; [Начальная позиция] ) имеет аналогичный синтаксис и аргументы. Отличается тем, что не учитывает регистр и поддерживает использование постановочных символов .

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

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

Вы спросите, что такое подстановочные символы? Это специальные символы, позволяющие пропустить неизвестную часть текста для поиска:

• ? – один любой символ
• * – любая последовательность символов

Если знак вопроса или звёздочка – часть строки для поиска, а не подстановочный символ, поставьте перед ними знак тильда (~).

Давайте запишем такую формулу: =ПОИСК(«??чн»;A1;1). Функция будет искать строку из двух любых символов, после которых идут буквы «чн». Поиску соответствует часть слова «Табличный» в тексте примера, т.е. комбинация символов «личн». Результатом функции будет значение «4».

Подстановочные символы в Excel

Теперь запишем формулу =ПОИСК(«п*с»;A2;1) . Функция ищет комбинацию «п» + любое количество любых символов + «с». В примере – это часть слова «процессор». Результат выполнения функции – 11.

Подстановочный символ-звездочка в Эксель

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

А следующая статья будет посвящена . Важная тема, которую обязательно нужно изучить!

Задавайте вопросы в комментариях и подписывайтесь на обновления , чтобы ничего не пропустить!