Команда найти в microsoft excel

В документах 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 ориентирована на ускоренные расчеты. Зачастую документы здесь состоят из большого количества листов, на которых представлены длинные таблицы с числами, формулами или текстом. Для удобного нахождения нужных ячеек существует специальный автоматизированный поиск. Ознакомившись с особенностями его использования, можно сократить время работы в документах. О том, как искать в Экселе слова, фразы или ячейки, подробно написано ниже.

Поиск слов

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

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

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

Существует также способ нестрогого поиска, который подходит для ситуаций, когда искомое слово помнится частично. Он предусматривает использование символов-заменителей (джокерные символы). В Excel их всего два:

•  «?» – подразумевает любой отдельно взятый символ;
•  «*» – обозначает любое количество символов.

 Примечательно, при поиске вопросительного знака или знака умножения дополнительно впереди ставится тильда («~»). При поиске тильды, соответственно – две тильды.

 Алгоритм неточного поиска слова:

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

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

Поиск нескольких слов

Не зная, как найти слово в таблице в Еxcel, следует также воспользоваться функцией раздела «Редактирование» – «Найти и выделить». Далее нужно отталкиваться от искомой фразы:

•  если фраза точная, введите ее и нажмите клавишу «Найти все»;
•  если фраза разбита другими ключами, нужно при написании ее в строке поиска дополнительно проставить между всеми словами «*».

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

Поиск ячеек

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

Для поиска ячеек с формулами выполняются следующие действия.

1. В открытом документе выделить ячейку или диапазон ячеек (в первом случае поиск идет по всему листу, во втором – в выделенных ячейках).
2. Во вкладке «Главная» выбрать функцию «Найти и выделить».
3. Обозначить команду «Перейти».
4. Выделить клавишу «Выделить».
5. Выбрать «Формулы».
6. Обратить внимание на список пунктов под «Формулами» (возможно, понадобится снятие флажков с некоторых параметров).
7. Нажать клавишу «Ок».

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

1. Перейти во вкладку «Главная».
2. Выбрать функцию «Найти и выделить».
3. Нажать на команду «Найти».
4. Перейти в «Параметры» и выбрать «Формат».
5. Здесь выделить функцию «Выравнивание», поставить отметку «Объединить ячейки».
6. Нажать на «Ок».
7. Нажать на кнопку «Найти все» и проанализировать список ячеек, которые объединены на соответствующем листе.

При нажимании кнопкой мыши на элемент в списке происходит выделение объединенной ячейки на листе. Дополнительно доступна функция «Отменить объединение ячеек».

 Выполнение представленных выше действий приводит к нахождению всех объединенных ячеек на листе и при необходимости отмене данного свойства. Для поиска скрытых ячеек проводятся следующие действия.

1. Выбрать лист, требующий анализа на присутствие скрытых ячеек и их нахождения.
2. Нажать клавиши «F5_гт_
   Special».
3. Нажать сочетание клавиш «CTRL + G_гт_ Special».

Можно воспользоваться еще одним способом для поиска скрытых ячеек:

1. Открыть функцию «Редактирование» во вкладке «Главная».
2. Нажать на «Найти».
3. Выбрать команду «Перейти к разделу». Выделить «Специальные».
4. Попав в группу «Выбор», поставить галочку на «Только видимые ячейки».
5. Нажать кнопку «Ок».

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

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

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

• нажать на ячейку, не предусматривающую условное форматирование;
• выбрать функцию «Редактирование» во вкладке «Главная»;
• нажать на кнопку «Найти и выделить»;
• выделить категорию «Условное форматирование».

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

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

Поиск через фильтр

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

• выделить заполненную ячейку;
• во вкладке «Главная» выбрать функцию «Сортировка»;
• нажать на кнопку «Фильтр»;
• открыть выпадающее меню;
• ввести искомый запрос;
• нажать кнопку «Ок».

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

Способ 1

Самый простой способ — выполнить поиск. Для этого можно нажать клавиатурную комбинацию CTRL + F (от англ. Find), откроется окно поиска слов.

Для нажатия клавиатурной комбинации, нажмите клавишу клавиатуры CTRL и, удерживая ее, нажмите клавишу F (на английский язык переходить не нужно).

Вместо клавиатурной комбинации можно использовать кнопку поиска на панели Главная — Найти и выделить — Найти.

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

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

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

Также можно сделать шире столбцы: Книга, Лист, Имя и т.д., потянув за маркеры между названиями столбцов.

В столбце Значение можно видеть полный текст ячейки, в котором есть искомые символы (в нашем примере — excel). Чтобы перейти к этому месту в таблице просто нажмите левой кнопкой мыши на нужную строку, и курсор автоматически переместится в выбранную ячейку таблицы.

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

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

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

Здесь можно указать дополнительные параметры поиска.

Искать:

• на листе — только на текущем листе;
• в книге — искать во всем документе Excel, если он состоит из нескольких листов.

Просматривать:

• по строкам — искомая фраза будет искаться слева направо от одной строки к другой;
• по столбцам — искомая фраза будет искаться сверху вниз от одного столбца к другому.

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

Область поиска — определяет, где именно нужно искать совпадения:

• в формулах;
• в значениях ячеек (уже вычисленные по формулам значения);
• в примечаниях, оставленных пользователями к ячейкам.

А также дополнительные параметры:

• Учитывать регистр — означает, что заглавные и маленькие буквы будут считаться как разные.

Например, если не учитывать регистр, то по запросу «excel» будет найдены все вариации этого слова, например, Excel, EXCEL, ExCeL и т.д.

Если поставить галочку учитывать регистр, то по запросу «excel» будет найдено только такое написание слова и не будет найдено слово «Excel».

• Ячейка целиком — галочку нужно ставить в том случае, если нужно найти те ячейки, в которых искомая фраза находится целиком и нет других символов. Например, есть таблица со множеством ячеек, содержащих различные числа. Поисковый запрос: «200». Если не ставить галочку ячейка целиком, то будут найдены все числа, содержащие 200, например: 2000, 1200, 11200 и т.д. Чтобы найти ячейки только с «200», нужно поставить галочку ячейка целиком. Тогда будут показаны только те, где точное совпадение с «200».
• Формат… — если задать формат, то будут найдены только те ячейки, в которых есть искомый набор символов и ячейки имеют заданный формат (границы ячейки, выравнивание в ячейке и т.д.). Например, можно найти все желтые ячейки, содержащие искомые символы.

Формат для поиска можно задать самому, а можно выбрать из ячейки-образца — Выбрать формат из ячейки…

Чтобы сбросить настройки формата для поиска нужно нажать Очистить формат поиска.

Это меню вызывается, если нажать на стрелочку в правой части кнопки Формат.

Способ 2

Второй способ, который позволяет найти искомое слово в таблице Excel, не является именно поиском, но также может быть удобен для работы. Это фильтр по фразе (символам), который позволяет вывести на экране только те строки, которые содержат искомые символы.

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

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

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

Результат будет таков.

Таблица до применения фильтра и таблица после применения фильтра.

Фильтрация не изменяет таблицу и не удаляет строки, она просто показывает искомые строки, скрывая не нужны. Чтобы удалить фильтр, нужно нажать на стрелочку в заголовке — Удалить фильтр с слова…

Также можно нажать на стрелочку и выбрать Текстовые фильтры — Содержит и указать искомые символы.

И далее ввести искомую фразу, например «Мюнхен».

Результат будет таков — только строки, содержащие слово «Мюнхен».

Этот фильтр сбрасывается также, как и предыдущий.

Таким образом, у пользователя есть варианты поиска слова в Excel — собственно сам поиск и фильтр.

Видеоурок по теме

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

​Смотрите также​ аргумент не указан,​ метод решения». Тут​​ кнопку, расположенную слева​​(нач_позиция) в «главной»​​ не зависимо от​​ с​

Описание

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

​ регистра. Если Вам​

​ИНДЕКС​​продукт​​INDEX​INDEX​/​

​намного лучше, чем​ заданных строки и​column_num​

​Загрузка надстройки мастера подстановок​

​ «Капуста» в массиве​​ функцию​​ синтаксис формулы и​ умолчанию считается равным​ одного из трех​ данных.​SEARCH​​ нужен поиск с​​и​​(Product). Дело усложняется​​(ИНДЕКС) позволяет использовать​(ИНДЕКС), из которого​ПОИСКПОЗ​ВПР​ столбца, но она​(номер_столбца).​​Нажмите кнопку​​ B2:B10))​​ПОИСК​​ использование функций​ 1. При указании​​ способов расчета: «Поиск​​После этого, окно параметров​​(ПОИСК). В результате​​ учётом регистра, Вы​ПОИСКПОЗ​ тем, что один​

​ три аргумента:​​ нужно извлечь значение.​

• ​.​. Однако, многие пользователи​ не может определить,​

• ​column_num​Microsoft Office​Формула ищет в C2:C10​нужно использовать для​ПОИСК​ начальной позиции положение​ решения нелинейных задач​ свернется, а вы​ названия городов будут​ можете использовать функцию​в функцию​ покупатель может купить​INDEX(array,row_num,[column_num])​

​ В нашем случае​Предположим, Вы используете вот​ Excel по-прежнему прибегают​ какие именно строка​(номер_столбца) – это​

Синтаксис

​, а затем —​

​ первое значение, соответствующее​

​ работы с текстовой​и​

• ​ искомого фрагмента все​​ методом ОПГ», «Поиск​ сможете выделить нужную​

• ​ проигнорированы поиском.​​FIND​ЕСЛИОШИБКА​ сразу несколько разных​​ИНДЕКС(массив;номер_строки;[номер_столбца])​​ это​

• ​ такую формулу с​​ к использованию​ и столбец нас​​ номер столбца в​​ кнопку​ значению​

Замечание

• ​ строкой «МДС0093.МужскаяОдежда». Чтобы​​ПОИСКБ​​ равно будет считаться​​ решения линейных задач​​ ячейку таблицы. Затем,​Теперь проверенная и исправленная​(НАЙТИ), с которой​​.​​ продуктов, и имена​​И я поздравляю тех​​A2:A10​

• ​ВПР​​ВПР​​ интересуют.​ массиве, из которого​Параметры Excel​​Капуста​​ найти первое вхождение​​в Microsoft Excel.​​ с первого символа,​ симплекс-методом», и «Эволюционный​ требуется опять нажать​ формула будет считать​ мы познакомимся далее​Синтаксис функции​ покупателей в таблице​​ из Вас, кто​​.​

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

• ​и выберите категорию​​(B7), и возвращает​​ «М» в описательной​Функции​

• ​ даже если начальные​​ поиск решения». По​​ по той же​ только те строки,​ в рамках марафона​​ЕСЛИОШИБКА​​ на листе​ догадался!​

• ​Затем соединяем обе части​​ ячейках от​​ гораздо проще. Так​ базовая информация об​ Если не указан,​Надстройки​​ значение в ячейке​​ части текстовой строки,​ПОИСК​ 8 были пропущены​ умолчанию, используется первый​ кнопке слева от​ которые содержат слово​30 функций Excel за​​очень прост:​​Lookup table​Начнём с того, что​ и получаем формулу:​B5​ происходит, потому что​ этих двух функциях,​ то обязательно требуется​​.​​ C7 (​ задайте для аргумента​И​ в анализе. То​​ метод. Пробуем решить​​ формы с введенными​ «bank» в названии​ 30 дней​​IFERROR(value,value_if_error)​​расположены в произвольном​ запишем шаблон формулы.​=INDEX($A$2:$A$10;MATCH(«Russia»;$B$2:$B$10;0))​​до​​ очень немногие люди​ полагаю, что уже​ аргумент​​В поле​​100​

Примеры

​начальная_позиция​ПОИСКБ​ есть в рассматриваемом​ поставленную задачу, выбрав​ данными, чтобы развернуть​ профессии:​.​ЕСЛИОШИБКА(значение;значение_если_ошибка)​ порядке.​ Для этого возьмём​=ИНДЕКС($A$2:$A$10;ПОИСКПОЗ(«Russia»;$B$2:$B$10;0))​D10​ до конца понимают​

support.office.com

Поиск значений в списке данных

​A2​ВПР​и​ то функция​и нажмите кнопку​ ИНДЕКС и ПОИСКПОЗ.​ текста, которая является​ позицию первой текстовой​ значение 14.​ использованием последнего метода.​ установить параметры значений,​ командой сайта office-guru.ru​ какой-либо текст внутри​ значение, проверяемое на​ПОИСКПОЗ​/​ ссылки для​:​на связку​ИНДЕКС​

В этой статье

​ИНДЕКС​Перейти​К началу страницы​

​ серийным номером (в​ строки (считая от​При работе с аргументом​

​ Алгоритм действий всё​ которые будут находиться​Источник: http://blog.contextures.com/archives/2011/01/19/30-excel-functions-in-30-days-18-search/​ текстовой строки. В​

​ предмет наличия ошибки​решает задачу:​ПОИСКПОЗ​

​ИНДЕКС​=VLOOKUP(A2,B5:D10,3,FALSE)​ИНДЕКС​

​могут работать вместе.​возвращает значение из​.​

Поиск значений в списке по вертикали по точному совпадению

​Для выполнения этой задачи​ данном случае —​ первого символа второй​ «искомый_текст» можно использовать​ тот же, который​

Примеры функции ВПР

​ в ней. Это​Перевел: Антон Андронов​ этом примере мы​

Примеры функций ИНДЕКС и ПОИСКПОЗ

​ (в нашем случае​

​{=INDEX(‘Lookup table’!$A$2:$C$13,MATCH(1,(A2=’Lookup table’!$A$2:$A$13)*​и добавим в​и​=ВПР(A2;B5:D10;3;ЛОЖЬ)​и​

​ПОИСКПОЗ​ ячейки, находящейся на​В области​​ используется функция ВПР.​​ «МДС0093»). Функция​ текстовой строки). Например,​ следующие подстановочные знаки.​​ мы описывали выше.​​ может быть максимум,​

​Автор: Антон Андронов​ будем искать одиночный​ – результат формулы​

​(B2=’Lookup table’!$B$2:$B$13),0),3)}​

Поиск значений в списке по вертикали по приблизительному совпадению

​ неё ещё одну​ПОИСКПОЗ​

​Формула не будет работать,​​ПОИСКПОЗ​определяет относительную позицию​ пересечении указанных строки​

​Доступные надстройки​Важно:​ПОИСК​ чтобы найти позицию​Вопросительный знак (?). Он​Как видим, функция Поиск​​ минимум, или конкретное​​Одной из самых интересных​ символ (введённый в​ИНДЕКС​{=ИНДЕКС(‘Lookup table’!$A$2:$C$13;ПОИСКПОЗ(1;(A2=’Lookup table’!$A$2:$A$13)*​ функцию​, чтобы диапазоны поиска​ если значение в​, а тратить время​ искомого значения в​​ и столбца.​​установите флажок рядом​

​  Значения в первой​начинает поиск с​ буквы «n» в​

​ будет соответствовать любому​

Поиск значений по вертикали в списке неизвестного размера по точному совпадению

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

​ ячейке B5) внутри​​/​(B2=’Lookup table’!$B$2:$B$13);0);3)}​ПОИСКПОЗ​ не сбились при​ ячейке​ на изучение более​ заданном диапазоне ячеек,​Вот простейший пример функции​ с пунктом​ строке должны быть​ восьмого символа, находит​

​ слове «printer», можно​​ знаку.​ довольно интересный инструмент,​ случае, это будет​

​ Microsoft Excel является​​ текстовой строки, находящейся​​ПОИСКПОЗ​Эта формула сложнее других,​, которая будет возвращать​ копировании формулы в​A2​

​ сложной формулы никто​​ а​INDEX​Мастер подстановок​ отсортированы по возрастанию.​ знак, указанный в​ использовать следующую функцию:​Звездочка (*). Этот символ​ который, при правильном​ последний вариант. Поэтому,​​ Поиск решения. Вместе​​ в ячейке B2.​

​); а аргумент​

Поиск значений в списке по горизонтали по точному совпадению

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

​длиннее 255 символов.​ не хочет.​​ИНДЕКС​​(ИНДЕКС):​и нажмите кнопку​В приведенном выше примере​

​ аргументе​=ПОИСК(«н»;»принтер»)​ будет соответствовать любой​

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

Поиск значений в списке по горизонтали по приблизительному совпадению

​ ставим переключатель в​ с тем, следует​

​=SEARCH(B5,B2)​​value_if_error​ ранее, но вооруженные​=INDEX(Ваша таблица,(MATCH(значение для вертикального​

​Вы можете вкладывать другие​ Вместо неё Вам​Далее я попробую изложить​использует это число​=INDEX(A1:C10,2,3)​ОК​ функция ВПР ищет​искомый_текст​Эта функция возвращает​

​ комбинации знаков.​ сэкономить время пользователя​ позицию «Значения», и​

​ отметить, что данный​

Создание формулы подстановки с помощью мастера подстановок (только Excel 2007)

​=ПОИСК(B5;B2)​​(значение_если_ошибка) – это​ знанием функций​ поиска,столбец, в котором​ функции Excel в​ нужно использовать аналогичную​ главные преимущества использования​ (или числа) и​

​=ИНДЕКС(A1:C10;2;3)​.​ имя первого учащегося​, в следующей позиции,​4​Если же требуется найти​ на различных подсчетах.​ в поле слева​ инструмент нельзя отнести​Если текст найден, функция​ значение, которое нужно​ИНДЕКС​ искать,0)),(MATCH(значение для горизонтального​ИНДЕКС​ формулу​

1. ​ПОИСКПОЗ​

2. ​ возвращает результат из​​Формула выполняет поиск в​​Следуйте инструкциям мастера.​​ с 6 пропусками в​​ и возвращает число​​, так как «н»​​ подобные символы в​

3. ​ К сожалению, далеко​​ от него прописываем​​ к самым популярным​SEARCH​

   ​ возвратить, если формула​

4. ​и​​ поиска,строка в которой​ ​и​ИНДЕКС​​и​​ соответствующей ячейки.​​ диапазоне​​К началу страницы​

5. ​ диапазоне A2:B7. Учащихся​​ 9. Функция​​ является четвертым символом​​ строке, то в​​ не каждый пользователь​​ число 30000. Как​​ среди пользователей в​

6. ​(ПОИСК) возвратит номер​​ выдаст ошибку.​​ПОИСКПОЗ​ искать,0))​​ПОИСКПОЗ​​/​​ИНДЕКС​​Ещё не совсем понятно?​

7. ​A1:C10​

​Этот учебник рассказывает о​

support.office.com

Функции ИНДЕКС и ПОИСКПОЗ в Excel – лучшая альтернатива для ВПР

​ с​ПОИСК​​ в слове «принтер».​​ аргументе «искомый_текст» перед​​ знает о его​​ мы помним, именно​ данном приложении. А​ позиции его первого​Например, Вы можете вставить​​Вы одолеете ее.​​=ИНДЕКС(Ваша таблица,(MATCH(значение для вертикального​, например, чтобы найти​ПОИСКПОЗ​в Excel, а​ Представьте функции​и возвращает значение​​ главных преимуществах функций​​6​

​всегда возвращает номер​Можно также находить слова​ ними нужно поставить​ существовании, не говоря​ это число по​​ зря. Ведь эта​​ символа в текстовой​ формулу из предыдущего​ Самая сложная часть​ поиска,столбец, в котором​ минимальное, максимальное или​:​​ Вы решите –​​ИНДЕКС​ ячейки во​ИНДЕКС​ пропусками в таблице нет,​

​ знака, считая от​ в других словах.​ тильду (~).​​ о том, чтобы​​ условиям составляет общую​ функция, используя исходные​ строке. Если не​ примера в функцию​ – это функция​ искать,0)),(MATCH(значение для горизонтального​ ближайшее к среднему​=INDEX(D5:D10,MATCH(TRUE,INDEX(B5:B10=A2,0),0))​ остаться с​​и​​2-й​​и​​ поэтому функция ВПР​ начала​ Например, функция​Если искомый текст не​ правильно уметь работать​​ сумму премии для​​ данные, путем перебора,​

• ​ найден, результатом будет​ЕСЛИОШИБКА​
• ​ПОИСКПОЗ​ поиска,строка в которой​
• ​ значение. Вот несколько​=ИНДЕКС(D5:D10;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B5:B10=A2;0);0))​
• ​ВПР​ПОИСКПОЗ​
  • ​строке и​ПОИСКПОЗ​
  • ​ ищет первую запись​просматриваемого текста​
  • ​=ПОИСК(«base»;»database»)​ был найден приложением​
  • ​ с этой надстройкой.​
  • ​ всех работников предприятия.​ находит наиболее оптимальное​

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

​ сообщение об ошибке​вот таким образом:​, думаю, её нужно​​ искать,0))​​ вариантов формул, применительно​​4. Более высокая скорость​​или переключиться на​в таком виде:​3-м​в Excel, которые​ со следующим максимальным​

​, включая символы, которые​возвращает​ или начальная позиция​ В чем-то данный​Ниже расположено поле «Изменяя​ решение из всех​​#VALUE!​​=IFERROR(INDEX($A$1:$E$11,MATCH($G$2,$B$1:$B$11,0),MATCH($G$3,$A$1:$E$1,0)),​​ объяснить первой.​​Обратите внимание, что для​​ к таблице из​​ работы.​

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

​ИНДЕКС​​=INDEX(столбец из которого извлекаем,(MATCH​​столбце, то есть​ делают их более​ значением, не превышающим​ пропускаются, если значение​5​ установлена меньше 0,​

​ инструмент напоминает функцию​
​ ячейки переменных». Тут​

​ имеющихся. Давайте выясним,​(#ЗНАЧ).​

• ​»Совпадений не найдено.​​MATCH(1,(A2=’Lookup table’!$A$2:$A$13),0)*(B2=’Lookup table’!$B$2:$B$13)​ двумерного поиска нужно​ предыдущего примера:​Если Вы работаете​
• ​/​​ (искомое значение,столбец в​ из ячейки​ привлекательными по сравнению​ 6. Она находит​ аргумента​, так как слово​ больше общего количества​​«Подбор параметра…»​​ нужно указать адрес​
• ​ как использовать функцию​​В случае, когда результатом​ Попробуйте еще раз!»)​ПОИСКПОЗ(1;(A2=’Lookup table’!$A$2:$A$13);0)*(B2=’Lookup table’!$B$2:$B$13)​ указать всю таблицу​1.​ с небольшими таблицами,​ПОИСКПОЗ​​ котором ищем,0))​​C2​

​ с​ значение 5 и возвращает​​начальная_позиция​​ «base» начинается с​ присутствующих символов, в​, но в то​ искомой ячейки, где,​

​ Поиск решения в​​ является ошибка, Вы​​=ЕСЛИОШИБКА(ИНДЕКС($A$1:$E$11;ПОИСКПОЗ($G$2;$B$1:$B$11;0);ПОИСКПОЗ($G$3;$A$1:$E$1;0));​

​В формуле, показанной выше,​
​ в аргументе​

​MAX​ то разница в​​.​​=ИНДЕКС(столбец из которого извлекаем;(ПОИСКПОЗ(искомое​.​​ВПР​​ связанное с ним​​больше 1.​​ пятого символа слова​ ячейке отобразиться ошибка​​ же время, имеет​​ как мы помним,​

​ программе Microsoft Excel.​ можете использовать функцию​»Совпадений не найдено.​ искомое значение –​array​(МАКС). Формула находит​ быстродействии Excel будет,​​1. Поиск справа налево.​​ значение;столбец в котором​

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

​Очень просто, правда? Однако,​​. Вы увидите несколько​​ имя​Скопируйте образец данных из​ «database». Можно использовать​ #ЗНАЧ.​ и существенные различия​ находится коэффициент, умножением​

​Скачать последнюю версию​​IFERROR​​ Попробуйте еще раз!»)​ это​(массив) функции​ максимум в столбце​​ скорее всего, не​​Как известно любому​ ищем;0))​ на практике Вы​

​ примеров формул, которые​
​Алексей​

​ следующей таблицы и​​ функции​​Если «искомый_текст» не найден,​ с ним.​

​ на который основной​
​ Excel​

• ​(ЕСЛИОШИБКА), чтобы вместо​​И теперь, если кто-нибудь​1​INDEX​D​ заметная, особенно в​ грамотному пользователю Excel,​Думаю, ещё проще будет​
• ​ далеко не всегда​​ помогут Вам легко​.​ вставьте их в​
• ​ПОИСК​​ возвращается значение ошибки​Автор: Максим Тютюшев​​ заработной платы будет​​Можно долго искать на​ выполнения функции​ введет ошибочное значение,​
  • ​, а массив поиска​​(ИНДЕКС).​​и возвращает значение​​ последних версиях. Если​ВПР​ понять на примере.​ знаете, какие строка​ справиться со многими​Дополнительные сведения см. в​ ячейку A1 нового​
  • ​и​​ #ЗНАЧ.​В приложении Excel предусмотрено​ рассчитана величина премии.​​ ленте, где находится​​SEARCH​​ формула выдаст вот​​ – это результат​А теперь давайте испытаем​ из столбца​​ же Вы работаете​​не может смотреть​​ Предположим, у Вас​​ и столбец Вам​
  • ​ сложными задачами, перед​​ разделе, посвященном функции​ листа Excel. Чтобы​ПОИСКБ​​ большое разнообразие инструментов​ Адрес можно прописать​ Поиск решения, но​(ПОИСК) вывести соответствующее​

​ такой результат:​ умножения. Хорошо, что​​ этот шаблон на​​C​ с большими таблицами,​ влево, а это​ есть вот такой​ нужны, и поэтому​

​ которыми функция​ ВПР.​ отобразить результаты формул,​для определения положения​Пример 1. Есть набор​ для обработки текстовых​ теми же способами,​ так и не​​ сообщение. Функция​​Если Вы предпочитаете в​​ же мы должны​​ практике. Ниже Вы​​той же строки:​​ которые содержат тысячи​ значит, что искомое​​ список столиц государств:​​ требуется помощь функции​ВПР​К началу страницы​ выделите их и​ символа или текстовой​ текстовой информации с​ и числовых данных.​ как мы это​

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

​ найти данный инструмент.​IFERROR​ случае ошибки оставить​ перемножить и почему?​ видите список самых​=INDEX($C$2:$C$10,MATCH(MAX($D$2:I$10),$D$2:D$10,0))​​ строк и сотни​​ значение должно обязательно​​Давайте найдём население одной​​ПОИСКПОЗ​​бессильна.​​Для выполнения этой задачи​ нажмите клавишу F2,​ строки в другой​ контактными данными клиентов​​ Одним из наиболее​​ делали для целевой​ Просто, для активации​(ЕСЛИОШИБКА) появилась в​ ячейку пустой, то​

​ Давайте разберем все​ населённых стран мира.​​=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(МАКС($D$2:I$10);$D$2:D$10;0))​​ формул поиска, Excel​​ находиться в крайнем​​ из столиц, например,​

​.​В нескольких недавних статьях​ используются функции СМЕЩ​
​ а затем — клавишу​ текстовой строке, а​ и их именами.​

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

​ по порядку:​ Предположим, наша задача​Результат: Beijing​ будет работать значительно​

​ левом столбце исследуемого​
​ Японии, используя следующую​

​Функция​ мы приложили все​ и ПОИСКПОЗ.​

• ​ ВВОД. При необходимости​​ затем вернуть текст​​ Информация записана в​ ПОИСК. Она позволяет​​В поле «В соответствии​​ её включить в​ версии 2007. В​​ («»), как значение​​Берем первое значение в​​ узнать население США​​2.​ быстрее, при использовании​ диапазона. В случае​
• ​ формулу:​​MATCH​​ усилия, чтобы разъяснить​​Примечание:​​ измените ширину столбцов,​​ с помощью функций​​ разных форматах. Необходимо​ определять в строке,​ с ограничениями» можно​ настройках программы.​ более ранних версиях​

  ​ второго аргумента функции​
​ столбце​

  ​ в 2015 году.​MIN​ПОИСКПОЗ​​ с​​=INDEX($D$2:$D$10,MATCH(«Japan»,$B$2:$B$10,0))​​(ПОИСКПОЗ) в Excel​​ начинающим пользователям основы​ Данный метод целесообразно использовать​ чтобы видеть все​ПСТР​​ найти, с какого​​ ячейке с текстовой​ выставить определенные ограничения​Для того, чтобы произвести​

​ тот же результат​ЕСЛИОШИБКА​

​A​Хорошо, давайте запишем формулу.​(МИН). Формула находит​​и​​ПОИСКПОЗ​=ИНДЕКС($D$2:$D$10;ПОИСКПОЗ(«Japan»;$B$2:$B$10;0))​​ ищет указанное значение​​ функции​​ при поиске данных​​ данные.​​и​​ символа начинается номер​ информацией позицию искомой​

​ для данных, например,​ активацию Поиска решений​ можно было получить,​​. Вот так:​​(Customer) на листе​ Когда мне нужно​ минимум в столбце​ИНДЕКС​​/​​Теперь давайте разберем, что​​ в диапазоне ячеек​​ВПР​

​ в ежедневно обновляемом​
​Данные​

​ПСТРБ​ телефона.​ буквенной или числовой​ сделать значения целыми​ в программе Microsoft​ используя​​IFERROR(INDEX(массив,MATCH(искомое_значение,просматриваемый_массив,0),»»)​​Main table​​ создать сложную формулу​​D​вместо​ИНДЕКС​ делает каждый элемент​​ и возвращает относительную​​и показать примеры​​ внешнем диапазоне данных.​​Выписки​или заменить его​Введем исходные данные в​​ комбинации и записывать​​ или неотрицательными. Для​

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

​ Excel 2010 года,​IF​ЕСЛИОШИБКА(ИНДЕКС(массив;ПОИСКПОЗ(искомое_значение;просматриваемый_массив;0);»»)​и сравниваем его​​ в Excel с​​и возвращает значение​​ВПР​​, столбец поиска может​​ этой формулы:​​ позицию этого значения​ более сложных формул​ Известна цена в​​Доход: маржа​​ с помощью функций​ таблицу:​ ее с помощью​ этого, жмем на​ и более поздних​(ЕСЛИ) вместе с​Надеюсь, что хотя бы​​ со всеми именами​​ вложенными функциями, то​​ из столбца​​. В целом, такая​​ быть, как в​​Функция​ в диапазоне.​ для продвинутых пользователей.​ столбце B, но​

​маржа​ЗАМЕНИТЬ​​В ячейке, которая будет​​ чисел.​​ кнопку «Добавить».​​ версий, переходим во​ISERROR​ одна формула, описанная​​ покупателей в таблице​​ я сначала каждую​​C​​ замена увеличивает скорость​​ левой, так и​​MATCH​

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

​Например, если в диапазоне​​ Теперь мы попытаемся,​ неизвестно, сколько строк​​Здесь «босс».​​и​ учитывать данные клиентов​Для нахождения позиции текстовой​После этого, открывается окно​ вкладку «Файл». Для​(ЕОШИБКА).​ в этом учебнике,​ на листе​​ вложенную записываю отдельно.​​той же строки:​​ работы Excel на​​ в правой части​(ПОИСКПОЗ) ищет значение​B1:B3​ если не отговорить​ данных возвратит сервер,​Формула​ЗАМЕНИТЬБ​ без телефона, введем​ строки в другой​

​ добавления ограничения. В​ версии 2007 года,​​=IFERROR(SEARCH(B5,B2),»Not Found»)​​ показалась Вам полезной.​​Lookup table​Итак, начнём с двух​=INDEX($C$2:$C$10,MATCH(MIN($D$2:I$10),$D$2:D$10,0))​13%​ диапазона поиска. Пример:​ «Japan» в столбце​​содержатся значения New-York,​​ Вас от использования​ а первый столбец​Описание​. Эти функции показаны​​ следующую формулу:​​ аналогичной применяют ПОИСК​ поле «Ссылка на​ следует нажать на​=ЕСЛИОШИБКА(ПОИСК(B5;B2);»Not Found»)​ Если Вы сталкивались​

​(A2:A13).​ функций​​=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(МИН($D$2:I$10);$D$2:D$10;0))​​.​ Как находить значения,​​B​​ Paris, London, тогда​ВПР​​ не отсортирован в​​Результат​​ в примере 1​​=ПОИСК(“, тел.”;адрес_анализируемой_ячейки).​​ и ПОИСКБ. Расчет​​ ячейки» прописываем адрес​

​ кнопку Microsoft Office​
​Еще один способ проверить​

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

​ ячеек, относительно которых​​ в левом верхнем​​ результат, возвращаемый​​ поиска, для которых​​ возвращает​, которые будут возвращать​3.​ВПР​ покажет эту возможность​ в ячейках​ цифру​ показать альтернативные способы​C1​Позиция первого знака «и»​Важно:​ искомой информации:​ символа анализируемой ячейки.​ вводится ограничение. В​ углу окна. В​SEARCH​ не смогли найти​​1​​ номера строки и​

​AVERAGE​на производительность Excel​​ в действии.​​B2:B10​​3​ реализации вертикального поиска​ — это левая верхняя​ в строке ячейки​ ​​Далее мы можем использовать​​ Так, если задать​ нашем случае, это​ открывшемся окне, переходим​(ПОИСК), на наличие​ подходящее решение среди​​(ИСТИНА), а если​​ столбца для функции​​(СРЗНАЧ). Формула вычисляет​​ особенно заметно, если​

​2. Безопасное добавление или​, и возвращает число​​, поскольку «London» –​​ в Excel.​ ячейка диапазона (также​​ A2, начиная с​​Эти функции могут быть​​ любые другие функции​​ функцию ПОИСК “л”​ искомая ячейка с​​ в раздел «Параметры».​​ ошибки – воспользоваться​

​ информации в этом​
​ нет –​

​ИНДЕКС​ среднее в диапазоне​ рабочая книга содержит​​ удаление столбцов.​​3​ это третий элемент​Зачем нам это? –​ называемая начальной ячейкой).​​ шестого знака.​​ доступны не на​​ для отображения представленной​​ для слова «апельсин»​

​ коэффициентом. Далее проставляем​
​В окне параметров кликаем​

​ функцией​ уроке, смело опишите​​0​:​D2:D10​ сотни сложных формул​Формулы с функцией​, поскольку «Japan» в​ в списке.​ спросите Вы. Да,​Формула​7​ всех языках.​ информации в удобном​ мы получим значение​ нужный знак: «меньше​​ по пункту «Надстройки».​​ISNUMBER​​ свою проблему в​​(ЛОЖЬ).​​ПОИСКПОЗ для столбца​​, затем находит ближайшее​ массива, таких как​ВПР​​ списке на третьем​​=MATCH(«London»,B1:B3,0)​

​ потому что​​ПОИСКПОЗ(«Апельсины»;C2:C7;0)​​=ПОИСК(A4;A3)​Функция ПОИСКБ отсчитывает по​ формате:​ 4, так как​ или равно», «больше​​ После перехода, в​​(ЕЧИСЛО). Если строка​ комментариях, и мы​Далее, мы делаем то​– мы ищем​ к нему и​​ВПР+СУММ​​перестают работать или​ месте.​=ПОИСКПОЗ(«London»;B1:B3;0)​ВПР​ищет значение «Апельсины»​Начальная позиция строки «маржа»​

​ два байта на​На рисунке видно, как​​ именно такой по​​ или равно», «равно»,​​ нижней части окна,​​ найдена, результатом​ все вместе постараемся​ же самое для​ в столбце​

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

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

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

​ «целое число», «бинарное»,​​ напротив параметра «Управление»​​SEARCH​ решить её.​ значений столбца​B​ столбца​ что проверка каждого​ если удалить или​INDEX​​MATCH​​ единственная функция поиска​

​ Начальную ячейку не​​ ячейке A4) в​​ если языком по​​ из двух функций​​ буква в текстовом​ и т.д. В​ выбираем значение «Надстройки​(ПОИСК) будет число,​Урок подготовлен для Вас​B​, а точнее в​C​ значения в массиве​ добавить столбец в​(ИНДЕКС) использует​​(ПОИСКПОЗ) имеет вот​​ в Excel, и​​ следует включать в​​ строке «Доход: маржа»​ умолчанию является язык​ ПСТР и ПОИСК​ выражении.​

​ нашем случае, мы​ Excel», и кликаем​ а значит функция​ командой сайта office-guru.ru​

​(Product).​
​ диапазоне​

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

• ​ такой синтаксис:​​ её многочисленные ограничения​​ этот диапазон.​ (ячейка, в которой​ с поддержкой БДЦС.​

  ​ мы вырезаем фрагмент​
​Функция ПОИСК работает не​

• ​ выберем знак «больше​ по кнопке «Перейти».​​ISNUMBER​​Источник: https://www.ablebits.com/office-addins-blog/2014/08/13/excel-index-match-function-vlookup/​Затем перемножаем полученные результаты​B2:B11​=INDEX($C$2:$C$10,MATCH(AVERAGE($D$2:D$10),$D$2:D$10,1))​​ функции​​ функции​
• ​для аргумента​MATCH(lookup_value,lookup_array,[match_type])​

  ​ могут помешать Вам​
​1​

​ выполняется поиск — A3).​​ В противном случае​ текста из строк​ только для поиска​​ или равно», чтобы​​Открывается окно с надстройками.​​(ЕЧИСЛО) вернет TRUE​​Перевел: Антон Андронов​ (1 и 0).​, значение, которое указано​=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(СРЗНАЧ($D$2:D$10);$D$2:D$10;1))​

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

​ВПР​ВПР​​row_num​​ПОИСКПОЗ(искомое_значение;просматриваемый_массив;[тип_сопоставления])​​ получить желаемый результат​​ — это количество столбцов,​8​ функция ПОИСКБ работает​ разной длины. Притом​ позиции отдельных букв​ сделать коэффициент положительным​ Ставим галочку напротив​

​ (ИСТИНА). Если же​​Автор: Антон Андронов​​ Только если совпадения​ в ячейке​​Результат: Moscow​​. Поэтому, чем больше​любой вставленный или​​(номер_строки), который указывает​​lookup_value​

​ во многих ситуациях.​
​ которое нужно отсчитать​

​=ЗАМЕНИТЬ(A3;ПОИСК(A4;A3);6;»объем»)​

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

​Используя функцию​
​ значений содержит массив​

​ удалённый столбец изменит​

​ из какой строки​​(искомое_значение) – это​​ С другой стороны,​ справа от начальной​​Заменяет слово «маржа» словом​​ функция ПОИСК, и​ в нужном месте​ и для целой​ поле «Ограничение» указываем​​ надстройки – «Поиск​​ то​

​30 функций Excel за​
​ столбцах (т.е. оба​

​(USA). Функция будет​

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

​СРЗНАЧ​​ и чем больше​​ результат формулы, поскольку​​ нужно возвратить значение.​​ число или текст,​​ функции​​ ячейки, чтобы получить​ «объем», определяя позицию​​ отсчитывает по одному​​ так, чтобы отделить​ комбинации. Например, задав​​ число 0. Если​​ решения». Жмем на​​SEARCH​​ 30 дней​ критерия истинны), Вы​ выглядеть так:​в комбинации с​ формул массива содержит​ синтаксис​ Т.е. получается простая​ который Вы ищите.​​ИНДЕКС​​ столбец, из которого​ слова «маржа» в​

• ​ байту на каждый​​ ее от номера​​ данную команду для​ мы хотим настроить​ кнопку «OK».​(ПОИСК) сообщит об​мы распознавали типы​ получите​
• ​=MATCH($H$2,$B$1:$B$11,0)​​ИНДЕКС​​ Ваша таблица, тем​ВПР​ формула:​ Аргумент может быть​и​ возвращается значение. В​

​ ячейке A3 и​ символ.​​ телефона.​​ слов «book», «notebook»,​ ещё одно ограничение,​После этого, кнопка для​​ ошибке, а​​ ошибок с помощью​​1​​=ПОИСКПОЗ($H$2;$B$1:$B$11;0)​​и​​ медленнее работает Excel.​​требует указывать весь​=INDEX($D$2:$D$10,3)​ значением, в том​ПОИСКПОЗ​ этом примере значение​ заменяя этот знак​

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

​К языкам, поддерживающим БДЦС,​Пример 2. Есть таблица​​ мы получим значение​​ то жмем на​ запуска функции Поиска​ISNUMBER​ функции​

​. Если оба критерия​​Результатом этой формулы будет​​ПОИСКПОЗ​​С другой стороны, формула​​ диапазон и конкретный​=ИНДЕКС($D$2:$D$10;3)​ числе логическим, или​– более гибкие​ возвращается из столбца​ и последующие пять​

​ относятся японский, китайский​ с текстовой информацией,​​ 5, так как​​ кнопку «Добавить». В​ решений появится на​

​(ЕЧИСЛО) возвратит FALSE​
​ERROR.TYPE​

​ ложны, или выполняется​4​, в качестве третьего​

​ с функциями​ номер столбца, из​Формула говорит примерно следующее:​ ссылкой на ячейку.​ и имеют ряд​​ D​​ знаков текстовой строкой​​ (упрощенное письмо), китайский​​ в которой слово​ именно с этого​ обратном случае, жмем​​ ленте Excel во​​ (ЛОЖЬ).​(ТИП.ОШИБКИ) и убедились,​

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

​ (традиционное письмо) и​ «маржа» нужно заменить​ по счету символа​ на кнопку «OK»,​​ вкладке «Данные».​​В значении аргумента​​ что она может​​ них – Вы​

​ это 4-ый элемент​ПОИСКПОЗ​и​ данные.​ от​(просматриваемый_массив) – диапазон​ их более привлекательными,​.​

​Доход: объем​ корейский.​ на «объем».​ начинается искомое слово​ чтобы сохранить введенные​Теперь, после того, как​find_text​

​ быть очень полезна​ получите​​ списка в столбце​​чаще всего нужно​ИНДЕКС​Например, если у Вас​​D2​​ ячеек, в котором​

• ​ по сравнению с​​К началу страницы​=ПСТР(A3;ПОИСК(» «;A3)+1,4)​​ПОИСК(искомый_текст;просматриваемый_текст;[начальная_позиция])​​Откроем книгу Excel с​ «book».​​ ограничения.​​ мы активировали функцию,​(искомый_текст) можно использовать​​ для исправления ошибок​​0​B​

  ​ будет указывать​
​просто совершает поиск​

  ​ есть таблица​​до​​ происходит поиск.​ВПР​Для выполнения этой задачи​​Возвращает первые четыре знака,​​ПОИСКБ(искомый_текст;просматриваемый_текст;[начальная_позиция])​

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

  ​A1:C10​
​D10​

  ​match_type​​.​​ используется функция ГПР.​ которые следуют за​

​Аргументы функций ПОИСК и​ формулу для поиска​​ с такими, как:​​ ограничение появляется в​

​ она работает. Легче​
​*​

​18-й день марафона мы​​Теперь понимаете, почему мы​​ПОИСКПОЗ для строки​или​ выполняя аналогичную работу​, и требуется извлечь​

​и извлеки значение​
​(тип_сопоставления) – этот​

​Базовая информация об ИНДЕКС​ См. пример ниже.​​ первым пробелом в​​ ПОИСКБ описаны ниже.​​ нужного слова «маржа»:​​НАЙТИ (осуществляет поиск с​​ соответствующем поле окна​​ всего это представить​(звёздочка) заменяет любое​​ посвятим изучению функции​​ задали​

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

​– мы ищем​​-1​​ заметно быстрее.​ данные из столбца​​ из третьей строки,​​ аргумент сообщает функции​ и ПОИСКПОЗ​Функция ГПР выполняет поиск​ строке «Доход: маржа»​Искомый_текст​Теперь дополним формулу функцией​​ учетом регистра);​​ параметров поиска решения.​​ на конкретном примере.​​ количество символов или​SEARCH​1​ значение ячейки​

​в случае, если​Теперь, когда Вы понимаете​B​ то есть из​ПОИСКПОЗ​​Используем функции ИНДЕКС и​​ по столбцу​​ (ячейка A3).​​    Обязательный. Текст, который требуется​ ЗАМЕНИТЬ:​ПСТР (возвращает текст);​ Также, сделать переменные​ Итак, у нас​ их отсутствие, а​(ПОИСК). Она ищет​​, как искомое значение?​​H3​ Вы не уверены,​

​ причины, из-за которых​​, то нужно задать​​ ячейки​​, хотите ли Вы​​ ПОИСКПОЗ в Excel​

​Продажи​
​марж​
​ найти.​
​Функция ПОИСК очень схожа​

​ЗАМЕНИТЬ (заменяет символы).​ неотрицательными, можно установив​ есть таблица заработной​?​​ символ (или символы)​​ Правильно, чтобы функция​​(2015) в строке​​ что просматриваемый диапазон​ стоит изучать функции​ значение​​D4​​ найти точное или​Преимущества ИНДЕКС и ПОИСКПОЗ​

​и возвращает значение​
​=ПОИСК("""";A5)​

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

• ​2​, так как счёт​​ приблизительное совпадение:​​ перед ВПР​​ из строки 5 в​​Позиция первой двойной кавычки​    Обязательный. Текст, в котором​ по принципу действия.​ команда ПОИСК не​​ параметра чуть ниже.​​ Нам следует рассчитать​
• ​ любой одиночный символ.​ и сообщает, где​​возвращала позицию только,​​, то есть в​ среднему. Если же​​и​​для аргумента​
• ​ начинается со второй​1​ИНДЕКС и ПОИСКПОЗ –​​ указанном диапазоне.​​ («) в ячейке​
• ​ нужно найти значение​ Более того у​ учитывает регистра. Если​ Желательно, чтобы установленный​ премию каждого работника,​В нашем примере использован​ он был найден.​​ когда оба критерия​​ ячейках​ Вы уверены, что​ИНДЕКС​col_index_num​ строки.​​или​​ примеры формул​

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

​ выполняются.​​A1:E1​ такое значение есть,​, давайте перейдём к​(номер_столбца) функции​​Вот такой результат получится​​не указан​Как находить значения, которые​ разделе, посвященном функции​5​искомый_текст​ аргументы. Только лишь​ нее станем искать​ противоречил тем, которые​ заработной платы, указанной​​*​​ как справиться с​Обратите внимание:​​:​​ – ставьте​

​ самому интересному и​ВПР​ в Excel:​– находит максимальное​ находятся слева​ ГПР.​=ПСТР(A5;ПОИСК(«»»»;A5)+1;ПОИСК(«»»»;A5;ПОИСК(«»»»;A5)+1)-ПОИСК(«»»»;A5)-1)​.​ названия аргументов отличаются,​ положение буквы «а»​ вы прописали в​ в отдельном столбце,​​, поэтому в названиях​​ теми ситуациями, когда​

​В этом случае​=MATCH($H$3,$A$1:$E$1,0)​0​ увидим, как можно​

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

​, вот так:​Важно! Количество строк и​ значение, меньшее или​Вычисления при помощи ИНДЕКС​К началу страницы​Возвращает из ячейки A5​​Начальная_позиция​​ а по сути​​ в слове «Александр»,​​ ограничениях, иначе, может​​ на определенный коэффициент.​​ улиц будут найдены​​ эта функция выдаёт​​ необходимо использовать третий​=ПОИСКПОЗ($H$3;$A$1:$E$1;0)​для поиска точного​ применить теоретические знания​=VLOOKUP(«lookup value»,A1:C10,2)​ столбцов в массиве,​​ равное искомому. Просматриваемый​​ и ПОИСКПОЗ​​Для выполнения этой задачи​​ только текст, заключенный​​    Необязательный. Номер знака в​​ и типам значений​

​ в ячейке появится​​ возникнуть конфликт.​​ При этом, общая​

​ фразы CENTRAL, CENTER​
​ ошибку.​

​ не обязательный аргумент​​Результатом этой формулы будет​​ совпадения.​ на практике.​=ВПР(«lookup value»;A1:C10;2)​ который использует функция​ массив должен быть​​Поиск по известным строке​​ используется функция ГПР.​​ в двойные кавычки.​​ аргументе​​ – одинаковые:​​ выражение 1, так​Дополнительные настройки можно задать,​ сумма денежных средств,​ и CENTRE.​

​Итак, давайте рассмотрим внимательно​ функции​5​​Если указываете​​Любой учебник по​

​Если позднее Вы вставите​
​INDEX​ упорядочен по возрастанию,​ ​ и столбцу​
​Важно:​босс​

​просматриваемый_текст​Но опытный пользователь Excel​ как это первый​ кликнув по кнопке​

​ выделяемых на премию,​=ISNUMBER(SEARCH($E$2,B3))​ теорию и практические​ИНДЕКС​, поскольку «2015» находится​1​​ВПР​​ новый столбец между​

​(ИНДЕКС), должно соответствовать​
​ то есть от​

​Поиск по нескольким критериям​  Значения в первой​Предположим, что требуется найти​, с которого следует​ знает, что отличие​ символ в анализируемой​ «Параметры».​ равна 30000 рублей.​=ЕЧИСЛО(ПОИСК($E$2;B3))​ примеры по функции​. Он необходим, т.к.​ в 5-ом столбце.​, значения в столбце​твердит, что эта​ столбцами​

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

​ внутренний телефонный номер​

office-guru.ru

30 функций Excel за 30 дней: ПОИСК (SEARCH)

​ начать поиск.​​ у этих двух​ информации. При задании​​Здесь можно установить точность​ Ячейка, в которой​Если мы запишем два​​SEARCH​​ в первом аргументе​Теперь вставляем эти формулы​ поиска должны быть​ функция не может​A​

​row_num​0​​ сочетании с ЕСЛИОШИБКА​​ отсортированы по возрастанию.​ сотрудника по его​Функции​ функций очень существенные.​ команды НАЙТИ «а»​ ограничения и пределы​ находится данная сумма,​ знака минус (двойное​(ПОИСК). Если у​ мы задаем всю​

​ в функцию​ упорядочены по возрастанию,​ смотреть влево. Т.е.​​и​​(номер_строки) и​– находит первое​Так как задача этого​В приведенном выше примере​ идентификационному номеру или​ПОИСК​

Функция 18: SEARCH (ПОИСК)

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

Как можно использовать функцию SEARCH (ПОИСК)?

​ИНДЕКС​​ а формула вернёт​​ если просматриваемый столбец​B​column_num​ значение, равное искомому.​

• ​ учебника – показать​ функция ГПР ищет​ узнать ставку комиссионного​
• ​и​ верхнему и нижнему​
• ​ отрезке текста, мы​ данные введены, жмите​

Синтаксис SEARCH (ПОИСК)

​ так как наша​​ISNUMBER​​ хитрости или примеры​ указать функции, из​

​и вуаля:​
​ максимальное значение, меньшее​

• ​ не является крайним​​, то значение аргумента​(номер_столбца) функции​
• ​ Для комбинации​​ возможности функций​ значение 11 000 в строке 3​ вознаграждения, предусмотренную за​
• ​ПОИСКБ​​ регистру (большие и​ получим значение 6,​ на кнопку «OK».​ цель подобрать данные​

Ловушки SEARCH (ПОИСК)

​(ЕЧИСЛО), то она​​ работы с этой​​ какого столбца нужно​=INDEX($A$1:$E$11,MATCH($H$2,$B$1:$B$11,0),MATCH($H$3,$A$1:$E$1,0))​ или равное среднему.​ левым в диапазоне​ придется изменить с​MATCH​ИНДЕКС​​ИНДЕКС​​ в указанном диапазоне.​ определенный объем продаж.​не учитывают регистр.​​ маленькие буквы). Функция​ так как именно​​ Но, для нашего​

Пример 1: Находим текст в строке

​ именно под это​​ возвратит значения​​ функцией, пожалуйста, делитесь​ извлечь значение. В​=ИНДЕКС($A$1:$E$11;ПОИСКПОЗ($H$2;$B$1:$B$11;0);ПОИСКПОЗ($H$3;$A$1:$E$1;0))​Если указываете​ поиска, то нет​2​(ПОИСКПОЗ). Иначе результат​/​и​

​ Значение 11 000 отсутствует, поэтому​
​ Необходимые данные можно​

​ Если требуется учитывать​​ НАЙТИ чувствительна к​​ 6 позицию занимает​ случая, изменять эти​ число.​1​ ими в комментариях.​ нашем случае это​​Если заменить функции​​-1​

​ шансов получить от​на​ формулы будет ошибочным.​​ПОИСКПОЗ​​ПОИСКПОЗ​ она ищет следующее​​ быстро и эффективно​​ регистр, используйте функции​ регистру символов. Например,​​ строчная «а» в​​ параметры не нужно.​Коэффициент, который применяется для​/​Функция​ столбец​ПОИСКПОЗ​, значения в столбце​​ВПР​​3​​Стоп, стоп… почему мы​​всегда нужно точное​

​для реализации вертикального​
​ максимальное значение, не​

Пример 2: Используем символы подстановки с SEARCH (ПОИСК)

​ находить в списке​НАЙТИ​​ есть список номенклатурных​​ слове «Александр».​После того, как все​ расчета суммы премии,​​0​​SEARCH​C​​на значения, которые​​ поиска должны быть​желаемый результат.​​, иначе формула возвратит​​ не можем просто​ совпадение, поэтому третий​ поиска в Excel,​ превышающее 11 000, и возвращает​​ и автоматически проверять​​и​ единиц с артикулом.​​Кроме того, функция ПОИСК​​ настройки установлены, жмем​ нам предстоит вычислить​

​вместо TRUE/FALSE (ИСТИНА/ЛОЖЬ).​​(ПОИСК) ищет текстовую​​(Sum), и поэтому​ они возвращают, формула​​ упорядочены по убыванию,​​Функции​ результат из только​ использовать функцию​​ аргумент функции​​ мы не будем​ 10 543.​

​ их правильность. Значения,​НАЙТИБ​​ Необходимо найти позицию​​ работает не для​ на кнопку «Найти​ с помощью функции​ Далее, функция​

​ строку внутри другой​
​ мы ввели​

Пример 3: Определяем стартовую позицию для SEARCH (ПОИСК)

​ станет легкой и​ а возвращено будет​ПОИСКПОЗ​​ что вставленного столбца.​​VLOOKUP​ПОИСКПОЗ​​ задерживаться на их​​Дополнительные сведения см. в​​ возвращенные поиском, можно​​.​ маленькой буквы «о».​​ всех языков. От​​ решение».​ Поиска решений. Ячейка,​SUM​ текстовой строки, и,​3​

​ понятной:​ минимальное значение, большее​

​и​

​Используя​(ВПР)? Есть ли​должен быть равен​ синтаксисе и применении.​ разделе, посвященном функции​ затем использовать в​

​В аргументе​
​Теперь смотрите как ведут​

​ команды ПОИСКБ она​Далее, программа Эксель в​ в которой он​(СУММ) в ячейке​ если находит, то​.​=INDEX($A$1:$E$11,4,5))​ или равное среднему.​ИНДЕКС​

​ПОИСКПОЗ​ смысл тратить время,​​0​​Приведём здесь необходимый минимум​ ГПР.​​ вычислениях или отображать​​искомый_текст​ себя по-разному эти​ отличается тем, что​ ячейках выполняет необходимые​ располагается, называется искомой.​​ E2 подсчитает суммарное​​ сообщает её позицию.​И, наконец, т.к. нам​​=ИНДЕКС($A$1:$E$11;4;5))​​В нашем примере значения​в Excel гораздо​/​

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

​ две функции при​
​ на каждый символ​

​ расчеты. Одновременно с​Целевая и искомая ячейка​
​ количество записей, где​
​Функция​

​ нужно проверить каждую​

office-guru.ru

Функция Microsoft Excel: поиск решения

​Эта формула возвращает значение​ в столбце​ более гибкие, и​ИНДЕКС​ лабиринтах​-1​ а затем разберём​Примечание:​ несколько способов поиска​ знаки: вопросительный знак​ поиске большой буквы​ отсчитывает по 1​ выдачей результатов, открывается​ должны быть связанны​ искомый текст был​SEARCH​ ячейку в массиве,​ на пересечении​D​

​ им все-равно, где​, Вы можете удалять​

Включение функции

​ПОИСКПОЗ​– находит наименьшее​ подробно примеры формул,​ Поддержка надстройки «Мастер подстановок»​ значений в списке​ (​ «О» в критериях​ байту, в то​ окно, в котором​

​ друг с другом​ найден.​(ПОИСК) ищет текстовую​ эта формула должна​4-ой​упорядочены по возрастанию,​ находится столбец со​ или добавлять столбцы​и​ значение, большее или​ которые показывают преимущества​ в Excel 2010​ данных и отображения​?​

​ поиска:​ время как ПОИСКБ​ вы можете либо​ с помощью формулы.​В следующем примере в​ строку внутри другой​ быть формулой массива.​строки и​

​ поэтому мы используем​ значением, которое нужно​ к исследуемому диапазону,​ИНДЕКС​ равное искомому значению.​ использования​

​ прекращена. Эта надстройка​ результатов.​) и звездочку (​Отличие №2. В первом​ — по два.​

Подготовка таблицы

​ сохранить найденное решение,​ В нашем конкретном​ столбце B показаны:​ текстовой строки. Она​ Вы можете видеть​5-го​ тип сопоставления​ извлечь. Для примера,​ не искажая результат,​?​ Просматриваемый массив должен​ИНДЕКС​ была заменена мастером​Поиск значений в списке​*​ аргументе «Искомый_текст» для​Чтобы воспользоваться функцией, необходимо​ либо восстановить исходные​ случае, формула располагается​Название города | Профессия​ может:​ это по фигурным​столбца в диапазоне​1​ снова вернёмся к​ так как определен​

​=VLOOKUP(«Japan»,$B$2:$D$2,3)​ быть упорядочен по​и​ функций и функциями​ по вертикали по​). Вопросительный знак соответствует​ функции ПОИСК мы​

​ ввести следующую формулу:​ значения, переставив переключатель​ в целевой ячейке,​Наша задача найти профессии,​Найти строку текста внутри​ скобкам, в которые​A1:E11​. Формула​ таблице со столицами​ непосредственно столбец, содержащий​=ВПР(«Japan»;$B$2:$D$2;3)​ убыванию, то есть​ПОИСКПОЗ​ для работы со​ точному совпадению​ любому знаку, звездочка —​

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

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

​, то есть значение​ИНДЕКС​ государств и населением.​ нужное значение. Действительно,​В данном случае –​ от большего к​вместо​ ссылками и массивами.​Поиск значений в списке​ любой последовательности знаков.​ подстановки для указания​В этой формуле задаваемые​ Независимо от выбранного​ вид: «=C10*$G$3», где​ введённую в ячейке​

​ (без учёта регистра).​ когда закончите вводить​ ячейки​/​ На этот раз​ это большое преимущество,​ смысла нет! Цель​ меньшему.​ВПР​В Excel 2007 мастер​

​ по вертикали по​ Если требуется найти​ не точного, а​ значения определяются следующим​ варианта, установив галочку​ $G$3 – абсолютный​ E1. Формула в​Использовать в поиске символы​ формулу, не забудьте​E4​ПОИСКПО​ запишем формулу​ особенно когда работать​ этого примера –​На первый взгляд, польза​.​ подстановок создает формулу​ приблизительному совпадению​ вопросительный знак или​ приблизительного значения, которое​

​ образом.​ «Вернутся в диалоговое​ адрес искомой ячейки,​ ячейке C2 будет​ подстановки.​ нажать​. Просто? Да!​З​ПОИСКПОЗ​ приходится с большими​ исключительно демонстрационная, чтобы​ от функции​Функция​ подстановки, основанную на​

​Поиск значений по вертикали​ звездочку, введите перед​ должно содержаться в​Искомый текст. Это числовая​ окно параметров», вы​ а «C10» —​ следующая:​Определить стартовую позицию в​

​Ctrl+Shift+Enter​В учебнике по​возвращает «Moscow», поскольку​/​ объёмами данных. Вы​ Вы могли понять,​ПОИСКПОЗ​INDEX​ данных листа, содержащих​ в списке неизвестного​ ним тильду (​ исходной текстовой строке.​ и буквенная комбинация,​ можете опять перейти​ общая сумма заработной​=—ISNUMBER(SEARCH($E$1,B2))​ просматриваемом тексте.​.​ВПР​ величина населения города​ИНДЕКС​ можете добавлять и​ как функции​вызывает сомнение. Кому​(ИНДЕКС) в Excel​ названия строк и​ размера по точному​~​ Вторая функция НАЙТИ​

​ позицию которой требуется​ к настройкам поиска​ платы, от которой​=—ЕЧИСЛО(ПОИСК($E$1;B2))​Функция​Если всё сделано верно,​мы показывали пример​ Москва – ближайшее​, которая покажет, какое​ удалять столбцы, не​ПОИСКПОЗ​ нужно знать положение​ возвращает значение из​ столбцов. С помощью​

​ совпадению​).​ не умеет использовать​

​ найти.​ решения. После того,​ производится расчет премии​Данная формула нашла строки,​SEARCH​ Вы получите результат​ формулы с функцией​ меньшее к среднему​

​ место по населению​ беспокоясь о том,​и​ элемента в диапазоне?​

​ массива по заданным​ мастера подстановок можно​Поиск значений в списке​Если​ в работе символы​Анализируемый текст. Это тот​ как выставлены галочки​ работникам предприятия.​ которые содержат слово​(ПОИСК) имеет вот​ как на рисунке​ВПР​ значению (12 269​ занимает столица России​ что нужно будет​ИНДЕКС​ Мы хотим знать​ номерам строки и​ найти остальные значения​ по горизонтали по​

​искомый_текст​ подстановки масок текста:​ фрагмент текстовой информации,​ и переключатели, жмем​После того, как таблица​ «bank», но в​ такой синтаксис:​ ниже:​для поиска по​ 006).​ (Москва).​ исправлять каждую используемую​работают в паре.​ значение этого элемента!​ столбца. Функция имеет​ в строке, если​ точному совпадению​не найден, возвращается​ «*»; «?»; «~».​ из которого требуется​ на кнопку «OK».​ подготовлена, находясь во​ одной из них​SEARCH(find_text,within_text,[start_num])​Как Вы, вероятно, уже​ нескольким критериям. Однако,​Эта формула эквивалентна двумерному​Как видно на рисунке​ функцию​ Последующие примеры покажут​Позвольте напомнить, что относительное​ вот такой синтаксис:​ известно значение в​Поиск значений в списке​ значение ошибки #ЗНАЧ!.​Для примера попробуем в​

​ вычленить искомую букву​Если по какой-либо причине​ вкладке «Данные», жмем​ это слово встречается​ПОИСК(искомый_текст;текст_для_поиска;[нач_позиция])​ заметили (и не​ существенным ограничением такого​ поиску​ ниже, формула отлично​ВПР​ Вам истинную мощь​ положение искомого значения​INDEX(array,row_num,[column_num])​ одном столбце, и​ по горизонтали по​Если аргумент​​ этих же исходных​​ или сочетание и​ результаты поиска решений​ на кнопку «Поиск​ не в названии​

​find_text​

lumpics.ru

Пример преимущества функции ПОИСК в Excel перед функцией НАЙТИ

​ раз), если вводить​ решения была необходимость​ВПР​ справляется с этой​.​ связки​ (т.е. номер строки​ИНДЕКС(массив;номер_строки;[номер_столбца])​ наоборот. В формулах,​ приблизительному совпадению​начальная_позиция​ строках столбца «наименования»​ вернуть позицию.​ вас не удовлетворяют,​

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

​ решения», которая расположена​ профессии, а в​(искомый_текст) – текст,​ некорректное значение, например,​ добавлять вспомогательный столбец.​и позволяет найти​ задачей:​3. Нет ограничения на​ИНДЕКС​ и/или столбца) –​Каждый аргумент имеет очень​ которые создает мастер​Создание формулы подстановки с​опущен, то он​ найти приблизительный текст.​

​Начальная позиция. Данный фрагмент​ или при их​ на ленте в​ названии города. Это​ который Вы ищете.​ которого нет в​ Хорошая новость: формула​ значение на пересечении​=INDEX($A$2:$A$10,MATCH(«Russia»,$B$2:$B$10,0))​ размер искомого значения.​и​ это как раз​ простое объяснение:​ подстановок, используются функции​

​ помощью мастера подстановок​ полагается равным 1.​

• ​ Для этого укажем​ необязателен для ввода.​
• ​ подсчете программа выдаёт​
• ​ блоке инструментов «Анализ».​

​ нас не устраивает!​within_text​ просматриваемом массиве, формула​ИНДЕКС​ определённой строки и​=ИНДЕКС($A$2:$A$10;ПОИСКПОЗ(«Russia»;$B$2:$B$10;0))​Используя​ПОИСКПОЗ​ то, что мы​array​ ИНДЕКС и ПОИСКПОЗ.​ (только Excel 2007)​Если аргумент​ следующий вид критерия​ Но, если вы​ ошибку, то, в​Открывается окно параметров, в​После каждого названия города​(текст_для_поиска) – текстовая​ИНДЕКС​

​/​ столбца.​Теперь у Вас не​ВПР​, которая легко справляется​ должны указать для​(массив) – это​Щелкните ячейку в диапазоне.​Для решения этой задачи​начальная_позиция​

​ поиска используя символы​ желаете найти, к​

​ таком случае, возвращаемся,​

​ которое нужно внести​ стоит символ​ строка, внутри которой​

1. ​/​ПОИСКПОЗ​В этом примере формула​ должно возникать проблем​
2. ​, помните об ограничении​ с многими сложными​ аргументов​ диапазон ячеек, из​На вкладке​ можно использовать функцию​
3. ​не больше 0​ подстановки: «н*ая».​ примеру, букву «а»​ описанным выше способом,​ данные. В поле​|​ происходит поиск.​ПОИСКПОЗ​может искать по​ИНДЕКС​ с пониманием, как​ на длину искомого​ ситуациями, когда​row_num​ которого необходимо извлечь​Формулы​ ВПР или сочетание​ или больше, чем​Как видим во втором​ в строке со​ в диалоговое окно​ «Оптимизировать целевую функцию»​(вертикальная черта), поэтому​start_num​сообщает об ошибке​ значениям в двух​/​ работает эта формула:​ значения в 255​ВПР​

​(номер_строки) и/или​ значение.​в группе​

1. ​ функций ИНДЕКС и​ длина​ отличии функция НАЙТИ​
2. ​ значением «А015487.Мужская одежда»,​ параметров. Пересматриваем все​ нужно ввести адрес​

​ мы, используя функцию​(нач_позиция) – если​#N/A​ столбцах, без необходимости​ПОИСКПОЗ​Во-первых, задействуем функцию​

​ символов, иначе рискуете​оказывается в тупике.​column_num​row_num​Решения​ ПОИСКПОЗ.​просматриваемого текста​ совершенно не умеет​

​ то необходимо указать​ введенные данные, так​ целевой ячейки, где​

​SEARCH​

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

​ не указан, то​(#Н/Д) или​ создания вспомогательного столбца!​будет очень похожа​MATCH​ получить ошибку​Решая, какую формулу использовать​(номер_столбца) функции​(номер_строки) – это​

​выберите команду​Дополнительные сведения см. в​

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

​ будет располагаться общая​

​(ПОИСК), можем найти​ поиск начнётся с​

​#VALUE!​Предположим, у нас есть​ на формулы, которые​(ПОИСКПОЗ), которая находит​#VALUE!​

​ для вертикального поиска,​INDEX​ номер строки в​Подстановка​ разделе, посвященном функции​ #ЗНАЧ!.​ спецсимволы для подстановки​ 8, чтобы анализ​ была допущена ошибка.​ сумма премии для​ позицию этого символа.​ первого символа.​

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

​(#ЗНАЧ!). Если Вы​ список заказов, и​ мы уже обсуждали​ положение «Russia» в​(#ЗНАЧ!). Итак, если​

​ большинство гуру Excel​(ИНДЕКС). Как Вы​ массиве, из которой​.​

​ ВПР.​Аргумент​

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

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

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

​Если команда​Что означает:​начальная_позиция​ поиска при неточном​ с восьмой позиции,​ ошибка найдена не​ можно сделать либо​ быть указана, как​SEARCH​ сообщение на что-то​

​ сумму по двум​ с одним лишь​=MATCH(«Russia»,$B$2:$B$10,0))​ строки, единственное действующее​ИНДЕКС​ИНДЕКС​

​ Если не указан,​Подстановка​=ИНДЕКС(нужно вернуть значение из​можно использовать, чтобы​ совпадении в исходной​ то есть после​ была, то переходим​ пропечатав координаты вручную,​ значение аргумента​(ПОИСК) возвратит позицию​ более понятное, то​ критериям –​ отличием. Угадайте каким?​=ПОИСКПОЗ(«Russia»;$B$2:$B$10;0))​

​ решение – это​/​может возвратить значение,​ то обязательно требуется​недоступна, необходимо загрузить​ C2:C10, которое будет​ пропустить определенное количество​ строке.​

​ артикула. Если этот​ к параметру «Выберите​ либо кликнув на​start_num​ первой совпадающей строки,​ можете вставить формулу​имя покупателя​Как Вы помните, синтаксис​Далее, задаём диапазон для​

exceltable.com

​ использовать​