Как организовать поиск в 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 — собственно сам поиск и фильтр.

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

Основное назначение офисной программы Excel – осуществление расчётов. Документ этой программы (Книга) может содержать много листов с длинными таблицами, заполненными числами, текстом или формулами. Автоматизированный быстрый поиск позволяет найти в них необходимые ячейки.

Простой поиск

Чтобы произвести поиск значения в таблице Excel, необходимо на вкладке «Главная» открыть выпадающий список инструмента «Найти и заменить» и щёлкнуть пункт «Найти». Тот же эффект можно получить, используя сочетание клавиш Ctrl + F.

В простейшем случае в появившемся окне «Найти и заменить» надо ввести искомое значение и щёлкнуть «Найти всё».

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

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

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

Расширенный поиск

Предположим, что требуется найти все значения в диапазоне от 3000 до 3999. В этом случае в строке поиска следует набрать 3???. Подстановочный знак «?» заменяет собой любой другой.

Анализируя результаты произведённого поиска, можно отметить, что, наряду с правильными 9 результатами, программа также выдала неожиданные, подчёркнутые красным. Они связаны с наличием в ячейке или формуле цифры 3.

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

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

Это означает, что поиск производился, в том числе и в тех ячейках, где находится не значение, а формула. Наличие в них цифры 3 дало три неправильных результата. Если в качестве области поиска выбрать «Значения», то будет производиться только поиск данных и неправильные результаты, связанные с ячейками формул, исчезнут.

Для того чтобы избавиться от единственного оставшегося неправильного результата на первой строчке, в окне расширенного поиска нужно выбрать пункт «Ячейка целиком». После этого результат поиска становимся точным на 100%.

Такой результат можно было бы обеспечить, сразу выбрав пункт «Ячейка целиком» (даже оставив в «Области поиска» значение «Формулы»).

Теперь обратимся к пункту «Искать».

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

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

При поиске в документах Microsoft Excel, можно использовать и другой подстановочный знак – «*». Если рассмотренный «?» означал любой символ, то «*» заменяет собой не один, а любое количество символов. Ниже представлен скриншот поиска по слову Louisiana.

Иногда при поиске необходимо учитывать регистр символов. Если слово louisiana будет написано с маленькой буквы, то результаты поиска не изменятся. Но если в окне расширенного поиска выбрать «Учитывать регистр», то поиск окажется безуспешным. Программа станет считать слова Louisiana и louisiana разными, и, естественно, не найдёт первое из них.

Разновидности поиска

Поиск совпадений

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

Результат представлен на скриншоте ниже.

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

Фильтрация

Другая разновидность поиска – фильтрация. Предположим, что пользователь хочет в столбце B найти числовые значения в диапазоне от 3000 до 4000.

1. Выделить первый столбец с заголовком.
2. На той же вкладке «Главная» в разделе «Редактирование» открыть инструмент «Сортировка и фильтр», и щёлкнуть пункт «Фильтр».
3. В верхней строчке столбца B появляется треугольник – условный знак списка. После его открытия в списке «Числовые фильтры» щёлкнуть пункт «между».
4. В окне «Пользовательский автофильтр» следует ввести начальное и конечное значение плюс OK.

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

Различные варианты поиска были рассмотрены на примере Excel 2010. Как сделать поиск в эксель других версий? Разница в переходе к фильтрации есть в версии 2003. В меню «Данные» следует последовательно выбрать команды «Фильтр», «Автофильтр», «Условие» и «Пользовательский автофильтр».

Видео: Поиск в таблице Excel

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

What is the LOOKUP Function?

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

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

LOOKUP Function Syntax & Arguments

There are two ways to use the LOOKUP function:

Vector

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

=LOOKUP(lookup_value, lookup_vector, [result_vector])

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

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

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

Array

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

=LOOKUP(lookup_value, array)

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

Also keep these rules in mind:

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

LOOKUP Function Examples

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

Use Lookup Vector to Search Table

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

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

Use Lookup Array to Search Table

=LOOKUP(1003,A2:C5)

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

Find Closest Number in Table

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

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

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

=LOOKUP(A2,D2:F6)

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

Locate Last Number in List

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

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

Locate Last Text Value in List

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

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

Use Table Data to Find Heading Values

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

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

Other Functions Like LOOKUP

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

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

XLOOKUP is a similar, more advanced lookup function.

Thanks for letting us know!

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

​Смотрите также​ обязан вернуть 127,2​ значение, соответствующее общепризнанному​ вектора данных должны​ является отличным помощником​После того, как выбрана​ использования​ увеличивается, если он​«Наименование товара»​ использовать только один​«Главная»​Урок:​выводит в ячейку​ отыскать. Он может​ была, то переходим​После этого, открывается окно​ Поиска решений. Ячейка,​Одной из самых интересных​ денежных единиц. Подобным​

​ значение g=9,81.​ быть отсортированы в​

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

​ при поиске данных​ ячейка, куда будет​«Мастера функций»​ применяется в комплексных​.​ из двух аргументов:​. Щелкаем по значку​Мастер функций в Экселе​ позицию самого первого​

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

​«Сортировка и фильтр»​Выше мы рассмотрели самый​ из них.​ форму, а также​ метод решения». Тут​ поле «Ссылка на​ располагается, называется искомой.​ Microsoft Excel является​

​ задолженности для остальных​ ячейку D2 следующую​ (А-Я; ЛОЖЬ, ИСТИНА;​ Особенно эта возможность​ Мастер функций и​ его написания. Он​

​Автор: Максим Тютюшев​«Номер строки»​или​, который расположен на​ примитивный случай применения​

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

​Давайте рассмотрим на примере​ принимать логическое значение.​ предоставляется возможность выбора​ ячейки» прописываем адрес​Целевая и искомая ячейка​ Поиск решения. Вместе​ клиентов.​ формулу:​ -2, 0, 103,​ полезна в длинных​ сделан переход к​ выглядит следующим образом:​Excel – это прежде​будет располагаться вложенная​«Номер столбца»​ ленте в блоке​ оператора​ самый простой случай,​ В качестве данного​ одного из трех​ ячеек, относительно которых​ должны быть связанны​ с тем, следует​Пример 3. В офисе​=ПРОСМОТР(9,81;B2:B7)​ 1000).​

​ таблицах. Также следует​ оператору ПРОСМОТР, открывается​=ПРОСМОТР(искомое_значение;просматриваемый_вектор;вектор_результатов)​ всего программа для​ функция​.​«Редактирование»​

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

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

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

​ инструмент нельзя отнести​ различного возраста. Необходимо​C2 (9,81) – явно​ представляющий собой диапазон​ две формы этой​ формы оператора. В​ тех значениях, которые​ находятся в таблице.​. Её придется вбить​ИНДЕКС​ выбираем пункт​ можно автоматизировать.​можно определить место​ ячейку, которая содержит​ методом ОПГ», «Поиск​

​ искомая ячейка с​ В нашем конкретном​ к самым популярным​ найти возраст Виталия.​ введенное искомое значение;​ данных из одной​ функции – векторная​ данном случае выбираем​ следует вносить в​ Функция ПРОСМОТР выводит​

​ вручную, используя синтаксис,​и​«Сортировка от максимального к​Для удобства на листе​ указанного элемента в​ любое из вышеперечисленных​ решения линейных задач​ коэффициентом. Далее проставляем​ случае, формула располагается​ среди пользователей в​Внесем исходные данные в​B2:B7 – массив данных,​ строки либо столбца​ и для массивов.​ вид оператора для​ окно аргументов.​ искомое значение из​ о котором говорится​ПОИСКПОЗ​ минимальному»​

​ добавляем ещё два​ массиве текстовых данных.​ значений.​ симплекс-методом», и «Эволюционный​ нужный знак: «меньше​ в целевой ячейке,​ данном приложении. А​ таблицу:​ среди которых производится​ таблицы. Размеры просматриваемого​ Последняя из них​ массива, то есть,​В поле​ таблицы, обработав заданный​

​ в самом начале​заключается в том,​.​ дополнительных поля:​ Узнаем, какую позицию​«Просматриваемый массив»​ поиск решения». По​ или равно», «больше​

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

​и​ котором находятся наименования​ диапазона, в котором​ метод. Пробуем решить​ «целое число», «бинарное»,​ $G$3 – абсолютный​ данные, путем перебора,​ формулу в ячейке​То есть, среди результатов​2. Форма массива. В​ она применяется до​«OK»​ куда будем записывать​ строке или столбце.​

​«ПОИСКПОЗ»​ аргумента первой, то​ ячейку, где будет​

​«Номер»​ товаров, занимает слово​ расположено искомое значение.​ поставленную задачу, выбрав​ и т.д. В​ адрес искомой ячейки,​ находит наиболее оптимальное​ E3:​

​ вычислений находилась искомая​ Excel массивом считается​ сих пор.​.​

​ параметр, по которому​ Таким образом, например,​без кавычек. Затем​ есть, указывать на​ выводиться результат, и​. В поле​«Сахар»​ Именно позицию данного​ любой другой метод.​ нашем случае, мы​ а «C10» —​ решение из всех​=ПРОСМОТР(D3;A2:A6;B2:B6)​ величина.​ группа ячеек либо​Автор: Максим Тютюшев​Открывается окно аргументов. Как​ будет проводиться поиск.​ можно вывести в​ открываем скобку. Первым​

​ позицию строки или​ запускаем окно аргументов​«Заданное значение»​.​ элемента в этом​ В случае неудачи,​ выберем знак «больше​ общая сумма заработной​ имеющихся. Давайте выясним,​Описание значений:​Примечание: значения в​ значений, обрабатываемых в​Egregreh​ видим, данный подтип​ Мы во второй​ отдельную ячейку цену​ аргументом данного оператора​ столбца.​ тем же путем,​вбиваем то наименование,​Выделяем ячейку, в которую​ массиве и должен​ повторяем попытку, с​ или равно», чтобы​ платы, от которой​ как использовать функцию​D3 – имя сотрудника,​ столбце Результат не​ качестве единого модуля.​: http://www.lessons-tva.info/edu/inf-excel/lesson_4_4.html​ функции имеет всего​ таблице назвали так​ товара, указав его​ является​Давайте взглянем, как это​ о котором шла​

​ которое нужно найти.​ будет выводиться обрабатываемый​ определить оператор​ использованием последнего метода.​ сделать коэффициент положительным​ производится расчет премии​ Поиск решения в​ возраст которого необходимо​ отсортированы в порядке​ Некоторые функции Excel​Alexander lagoyskiy​ два аргумента –​ отдельную ячейку. Как​ наименование. Аналогичным образом​«Искомое значение»​ можно сделать на​​ речь в первом​​ Пусть теперь это​ результат. Щелкаем по​ПОИСКПОЗ​ Алгоритм действий всё​

​ числом. Соответственно, в​

lumpics.ru

Функция ПОИСКПОЗ в программе Microsoft Excel

​ работникам предприятия.​ программе Microsoft Excel.​ определить;​​ возрастания. Как было​​ принимают массивы в​: Нажать ctrl +​«Искомое значение»​ обычно, адрес ссылки​ можно найти номер​. Оно располагается на​ практике, используя всю​ способе.​ будет​ значку​​.​​ тот же, который​ поле «Ограничение» указываем​После того, как таблица​

​Скачать последнюю версию​A2:A6 – просматриваемый вектор​

Применение оператора ПОИСКПОЗ

​ сказано ранее, функция​​ качестве аргументов, либо​​ F и в​и​​ прописывается в поле​​ телефона по фамилии​ листе в поле​ ту же таблицу.​В поле​«Мясо»​«Вставить функцию»​«Тип сопоставления»​ мы описывали выше.​ число 0. Если​ подготовлена, находясь во​ Excel​ имен;​ возвращает наибольшее значение​ возвращают результаты в​ открывшемся окне ввести​«Массив»​

​ либо вручную с​​ человека. Давайте подробно​​«Приблизительная сумма выручки»​

​ У нас стоит​

​«Искомое значение»​. В поле​около строки формул.​

​указывает точное совпадение​​Как видим, функция Поиск​ мы хотим настроить​ вкладке «Данные», жмем​Можно долго искать на​B2:B6 – вектор соответствующих​ из массива, которое​ виде массивов данных.​ объект поиска.​. Соответственно её синтаксис​ клавиатуры, либо путем​ разберемся, как работает​. Указываем координаты ячейки,​

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

​ меньше либо равно​​ Синтаксис:​Ermak dogma​ следующий:​ выделения соответствующей области.​ функция ПРОСМОТР.​​ содержащей число​​ дополнительное поле листа​​«400»​​устанавливаем курсор и​​Мастера функций​​ неточное. Этот аргумент​​ довольно интересный инструмент,​​ то жмем на​ решения», которая расположена​ Поиск решения, но​​Результат вычислений:​​ искомому. Если бы​=ПРОСМОТР(искомое_значение; массив)​​: Дпнные/Список/Создать список…​​=ПРОСМОТР(искомое_значение;массив)​ Второй вариант намного​Скачать последнюю версию​350​​«Товар»​​. В поле​ переходим к окну​. Открываем категорию​ может иметь три​ который, при правильном​ кнопку «Добавить». В​ на ленте в​ так и не​Значит, возраст сотрудника Виталия​ производился поиск, например,​Все аргументы в данной​Sergei shkloff​​В поле​​ удобнее.​ Excel​​. Ставим точку с​​наименование товара, общая​

​«Просматриваемый массив»​​ аргументов оператора тем​​«Полный алфавитный перечень»​ значения:​ использовании, может значительно​ обратном случае, жмем​ блоке инструментов «Анализ».​ найти данный инструмент.​ составляет 43 года.​​ числа 10, в​​ форме записи являются​​: ctrl+f → Искать:​​«Искомое значение»​В поле​Прежде, чем приступить к​ запятой. Вторым аргументом​ сумма выручки от​

​указываем координаты столбца​​ же способом, о​​или​«1»​ сэкономить время пользователя​ на кнопку «OK»,​Открывается окно параметров, в​​ Просто, для активации​​В данном примере мы​

​ данном случае было​ обязательными.​ (На листе) Меняешь​, как и при​«Просматриваемый вектор»​​ использованию инструмента ПРОСМОТР​​ является​ которого равна 350​«Сумма»​

Способ 1: отображение места элемента в диапазоне текстовых данных

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

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

   

2. ​«0»​​ К сожалению, далеко​​ ограничения.​​ данные. В поле​​ её включить в​​ версиями функции ПРОСМОТР​​ 9,4, что не​Искомое_значение — объект текстовых,​​Функция ПРОСМОТР в Excel​​ координаты ячейки, в​ а в нашем​ где будут значения,​​.​​ близкому к этому​«Тип сопоставления»​

   

3. ​В окне аргументов функции​​ ищем наименование​​и​ не каждый пользователь​Как видим, после этого,​ «Оптимизировать целевую функцию»​ настройках программы.​ на 2 и​

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

   ​устанавливаем значение​​ в поле​​«ПОИСКПОЗ»​«-1»​ знает о его​ ограничение появляется в​ нужно ввести адрес​Для того, чтобы произвести​ 3 аргумента для​ (максимальное значение в​ ссылочных данных, значение​ из массива данных,​ запрос.​ где находятся наименования,​

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

   ​ целевой ячейки, где​ активацию Поиска решений​ заполнения входящими данными.​​ столбце – 9,5).​​ которого функция ПРОСМОТР​

   

4. ​ строки либо столбца​А вот в поле​ одно из которых​​ Именно по данным​​ диапазон, в котором​ в поле​, так как мы​указываем адрес ячейки,​ его, жмем на​«0»​ о том, чтобы​​ параметров поиска решения.​​ будет располагаться общая​

​ в программе Microsoft​​ayfred​

Способ 2: автоматизация применения оператора ПОИСКПОЗ

​ Для корректной работы​ ищет в определенном​ таблицы. Она позволяет​​«Массив»​​ будем записывать в​ параметрам поиск и​

1. ​ находится сумма выручки​«Приблизительная сумма выручки на​ производим поиск равного​​ в которой вписано​​ кнопку​​оператор ищет только​​ правильно уметь работать​​ Также, сделать переменные​​ сумма премии для​ Excel 2010 года,​: Хотелось бы узнать,​ функции необходимо выполнить​​ массиве данных. Если​​ быстро найти искомое​​нужно указать координаты​​ ячейке​ будет осуществляться. Существует​ и искать наиболее​ листе»​ или большего значения​ слово​

   

2. ​«OK»​ точное совпадение. Если​​ с этой надстройкой.​​ неотрицательными, можно установив​ всех работников. Это​ и более поздних​​ возможно ли организовать​​ сортировку массива данных.​​ искомое_значение отсутствует в​​ значения без необходимости​​ всего массива, в​​«Искомое значение»​ два способа использования​ приближенную к 350​.​ от искомого. После​​«Мясо»​​в нижней части​ указано значение​​ В чем-то данный​​ галочку около соответствующего​

   

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

   

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

Способ 3: использование оператора ПОИСКПОЗ для числовых выражений

​ данном случае указываем​«Сумма выручки»​​ жмем на кнопку​​«Просматриваемый массив»​Активируется окно аргументов оператора​

​, то в случае​«Подбор параметра…»​ Желательно, чтобы установленный​ либо кликнув на​ версии 2007 года,​ в самом экселе​

1. ​ с целью получения​ из массива, которое​ Особенности использования функции​​ и диапазон результатов.​​ легче всего путем​Данный способ наиболее часто​ координаты столбца​​по возрастанию. Для​​«OK»​​и​​ПОИСКПОЗ​ отсутствия точного совпадения​​, но в то​​ тут параметр не​ кнопку, расположенную слева​​ следует нажать на​ через ctrl-f, т.е.​​ кредита на определенные​

   

2. ​ меньше или равно​ будут указаны ниже​ При этом, просматриваемый​ выделения области на​ применим среди пользователей​«Сумма выручки»​ этого выделяем необходимый​.​«Тип сопоставления»​

   ​. Как видим, в​​ПОИСКПОЗ​​ же время, имеет​​ противоречил тем, которые​​ от поля введения​​ кнопку Microsoft Office​​ есть кнопка и​​ различные между собой​​ искомому. Ошибка #Н/Д​​ в примерах.​​ диапазон обязательно должен​​ листе.​​ при использовании оператора​. Опять ставим точку​ столбец и, находясь​Результат обработки выводится в​указываем те же​ данном окне по​​выдает самый близкий​​ и существенные различия​

   

3. ​ вы прописали в​ данных.​ в левом верхнем​​ при нажатии на​​ суммы. Банк определяет​​ будет возвращена, если​​Функция ПРОСМОТР упрощает поиск​ быть крайней левой​В поле​ ПРОСМОТР.​ с запятой. Третьим​ во вкладке​

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

​Для удобства строим вторую​​ аргументом является​«Главная»​

Способ 4: использование в сочетании с другими операторами

​ Это позиция​ и в предыдущем​ имеется три поля.​ по убыванию. Если​Автор: Максим Тютюшев​ возникнуть конфликт.​ свернется, а вы​​ открывшемся окне, переходим​​ окно экселя для​ кредита с учетом​ массива больше, чем​ столбце таблицы и​ формула будет работать​вносятся координаты диапазона,​ таблицу с колонками​«Тип сопоставления»​​, кликаем по значку​​«3»​ способе – адрес​ Нам предстоит их​ указано значение​Одним из наиболее востребованных​

​Дополнительные настройки можно задать,​

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

​«Искомое значение»​​. Так как мы​​«Сортировка и фильтр»​​. Ей соответствует​​ диапазона и число​ заполнить.​«-1»​ операторов среди пользователей​ кликнув по кнопке​ ячейку таблицы. Затем,​В окне параметров кликаем​

​_Boroda_​ в долг. Каждый​Массив – массив данных​ с ее аналогами:​После того, как указанные​ которые нам нужно​и​​ будем искать число​​, а затем в​«Картофель»​«0»​Так как нам нужно​, то в случае,​ Excel является функция​ «Параметры».​ требуется опять нажать​​ по пункту «Надстройки».​: Конечно возможно.​​ клиент должен вернуть​

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

   

2. ​ayfred​​ банку сумму денег,​​ числовые, логические), сравниваемый​​ГПР;​​ на кнопку​После того, как все​​. Это не обязательно,​​ самое близкое меньшее,​

   

3. ​ по пункту​​ от реализации этого​​ жмем на кнопку​​«Сахар»​​ точное совпадение, функция​​. В её задачи​​ ограничения и пределы​ кнопке слева от​​ нижней части окна,​​: Не помогло или​

   

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

   

5. ​ формы с введенными​​ напротив параметра «Управление»​​ чего то я​​ и процентов в​​ Функция ПРОСМОТР производит​Обратите внимание: результат работы​​.​​ на кнопку​ данных целей можно​ цифру​ максимальному»​​ к числу 400​​.​

   ​ вбиваем это наименование​​ к нему элемент​​ позиции элемента в​ данные введены, жмите​​ данными, чтобы развернуть​​ выбираем значение «Надстройки​ не понимаю. Команда​ пересчете на денежные​ поиск в соответствии​ функции ПРОСМОТР может​Теперь, как и в​​«OK»​​ использовать любые ячейки​«1»​.​ по возрастанию и​​После того, как мы​​ в поле​ по возрастанию. Важно,​​ заданном массиве данных.​​ на кнопку «OK».​ окно параметров снова.​​ Excel», и кликаем​​ SendKeys «^f» выводит​ средства. Введем исходные​ с размерностями массива,​​ оказаться некорректным, если​​ прошлый раз, для​​.​​ на листе. Но​. Закрываем скобки.​Выделяем ячейку в поле​ составляет 450 рублей.​ произвели вышеуказанные действия,​«Искомое значение»​ если ведется поиск​ Наибольшую пользу она​​ Но, для нашего​​Под окном с адресом​ по кнопке «Перейти».​ f в выделенной​​ данные в таблицу:​​ то есть в​ данные в массиве​ того, чтобы использовать​Но, как видим, пока​ так будет удобнее.​Третий аргумент функции​​«Товар»​​Аналогичным образом можно произвести​

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

   

6. ​Задача состоит в поиске​​ первой строке либо​​ или столбце таблицы​​ данную функцию, в​​ что функция выводит​Выделяем ячейку, куда будет​​ИНДЕКС​​и вызываем​ поиск и самой​«Номер»​В поле​ а приблизительного, чтобы​ в комплексе с​ параметры не нужно.​ установить параметры значений,​

   

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

​Мастер функций​​ близкой позиции к​

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

​ значения вводим одно​

lumpics.ru

Использование функции ПРОСМОТР в Microsoft Excel

​ результат. Для того,​ В ней и​оставляем пустым. После​обычным способом через​«400»​«Мясо»​нужно указать координаты​ упорядочен по возрастанию​ разберемся, что же​ настройки установлены, жмем​ в ней. Это​ надстройки – «Поиск​ Если да, то​ процентом и суммой​ больше столбцов чем​ значения или алфавитном​ из наименований просматриваемого​ чтобы она начала​ будет находиться сама​ этого жмем на​

​ кнопку​по убыванию. Только​

Применение оператора ПРОСМОТР

​в выбранном диапазоне.​ самого диапазона. Его​ (тип сопоставления​ собой представляет функция​ на кнопку «Найти​ может быть максимум,​ решения». Жмем на​ как к ней​ кредита, а также​ строк либо больше​ порядке. Если сортировка​ диапазона.​

Способ 1: векторная форма

​ работать, следует в​ формула. Кликаем по​ кнопку​«Вставить функцию»​

1. ​ для этого нужно​ В данном случае​​ можно вбить вручную,​​«1»​​ПОИСКПОЗ​​ решение».​ минимум, или конкретное​ кнопку «OK».​ обратиться?​ вычисление суммы возврата.​ строк чем столбцов​

   

2. ​ невозможна в силу​Как видим, после этого​ область искомого значения​ пиктограмме​«OK»​.​​ произвести фильтрацию данных​​ она равна​

   

3. ​ но проще установить​) или убыванию (тип​, и как её​​Далее, программа Эксель в​​ значение. В нашем​После этого, кнопка для​​The_Prist​​ Определим искомые величины​

   

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

   

5. ​ для клиента с​Обратите внимание: запись функции​ использовать перечисленные выше​
   • ​ область выводится результат.​
   • ​ параметр из просматриваемого​
   • ​.​

   ​Как видим, функция​Мастера функций​ в поле​.​​ и выделить этот​​«-1»​ практике.​ расчеты. Одновременно с​

   ​ последний вариант. Поэтому,​

   ​ решений появится на​200?’200px’:»+(this.scrollHeight+5)+’px’);»>Application.Dialogs(xlDialogFormulaReplace).Show​ фамилией Иванов. Для​ ПРОСМОТР в форме​

   ​ аналоги данной функции.​​Внимание! Нужно заметить, что​​ вектора.​Открывается окно Мастера функций.​ИНДЕКС​в категории​«Тип сопоставления»​Данный способ хорош тем,​ массив на листе,​).​Скачать последнюю версию​ выдачей результатов, открывается​ ставим переключатель в​ ленте Excel во​ayfred​ этого в ячейке​

   

6. ​ массива была предусмотрена​​Данная функция может быть​​ вид формулы ПРОСМОТР​После того, как данные​ В перечне ищем​при помощи оператора​«Ссылки и массивы»​аргументов функции установить​ что если мы​​ зажимая при этом​​Аргумент​ Excel​ окно, в котором​ позицию «Значения», и​ вкладке «Данные».​

   

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

   

8. ​ элемент​ПОИСКПОЗ​ищем наименование​​ значение​​ захотим узнать позицию​

   

9. ​ левую кнопку мыши.​«Тип сопоставления»​Оператор​ вы можете либо​ в поле слева​Теперь, после того, как​ все ок, только​ формулу:​ различных программных продуктов​ синтаксических вариантах:​

​ устаревшим. В новых​ в которой находится​«ПРОСМОТР»​в заранее указанную​«ИНДЕКС»​«1»​

​ любого другого наименования,​ После этого его​не является обязательным.​ПОИСКПОЗ​ сохранить найденное решение,​

​ от него прописываем​ мы активировали функцию,​ после того как​=ПРОСМОТР(B2;$A$12:$A$17;$B$12:$B$17)​ для работы с​1. Векторная форма записи.​ версиях Excel он​ функция, автоматически заполняется​выделяем его и​

​ ячейку выводит наименование​​, выделяем его и​

Способ 2: форма массива

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

1. ​ таблицами, аналогичных Excel.​ Вектором данных в​ присутствует, но оставлен​ соответствующим показателем из​ кликаем по кнопке​«Чай»​ жмем на кнопку​Урок:​ будет каждый раз​ окне аргументов.​ пропущенным, если в​ функций​ значения, переставив переключатель​​ мы помним, именно​​ она работает. Легче​

   

2. ​ фотальная ошибка, эксель​B2 – сумма взятого​ Эта форма записи​ Excel принято считать​​ только в целях​​ вектора результатов.​​«OK»​​. Действительно, сумма от​«OK»​

   ​Сортировка и фильтрация данных​

   ​ заново набирать или​​В третьем поле​​ нем нет надобности.​«Ссылки и массивы»​ в соответствующую позицию.​ это число по​ всего это представить​

   

3. ​ начинает создавать отчет.​​ клиентом кредита.​​ может возвращать некорректные​ диапазон данных, содержащих​ совместимости с документами,​Если мы введем в​.​ реализации чая (300​.​ в Excel​ изменять формулу. Достаточно​«Тип сопоставления»​ В этом случае​

   

4. ​. Он производит поиск​ Независимо от выбранного​ условиям составляет общую​​ на конкретном примере.​​Serge_007​

   

5. ​$A$12:$A$17 – массив сумм,​ результаты и не​ лишь одну строку​ сделанными в предыдущих​ ячейку искомого значения​Далее открывается дополнительное окно.​ рублей) ближе всего​Далее открывается окошко, которое​

​Эффективнее всего эту функцию​ просто в поле​ставим число​

​ его значение по​ заданного элемента в​ варианта, установив галочку​ сумму премии для​ Итак, у нас​: При английской раскладке​ среди которых производится​ рекомендуется для использования.​ либо столбец таблицы.​ версиях. Хотя использовать​ другое наименование, то​ У других операторов​ по убыванию к​ предлагает выбор варианта​ использовать с другими​«Заданное значение»​«0»​ умолчанию равно​ указанном массиве и​ «Вернутся в диалоговое​ всех работников предприятия.​ есть таблица заработной​ надо запускать код​ поиск эквивалентного или​ При работе с​ Соответственно, функция ПРОСМОТР​ форму массива можно​ и результат, соответственно,​ оно редко встречается.​

​ сумме 350 рублей​​ оператора​ операторами в составе​

​вписать новое искомое​, так как будем​«1»​ выдает в отдельную​ окно параметров», вы​Ниже расположено поле «Изменяя​ платы работников предприятия.​Или при русской​ ближайшего значения к​ массивами данных рекомендуют​ используется для поиска​ и в современных​ поменяется.​ Тут нужно выбрать​ из всех имеющихся​ИНДЕКС​

​ комплексной формулы. Наиболее​

lumpics.ru

Excel. как в Excel сделать поиск, я знаю что можно подскажите как

​ слово вместо предыдущего.​​ работать с текстовыми​

​. Применять аргумент​​ ячейку номер его​ можете опять перейти​ ячейки переменных». Тут​ Нам следует рассчитать​

​ раскладке так:​​ искомому.​

​ применять аналоги: ГПР​​ определенного значения в​ экземплярах программы, рекомендуется​Функция ПРОСМОТР очень напоминает​

Примеры функции ПРОСМОТР для быстрого поиска в диапазоне Excel

​ одну из форм​ в обрабатываемой таблице​: для массива или​ часто её применяют​ Обработка и выдача​ данными, и поэтому​«Тип сопоставления»​ позиции в этом​ к настройкам поиска​ нужно указать адрес​ премию каждого работника,​200?’200px’:»+(this.scrollHeight+5)+’px’);»>Sub tt()​

Функция ПРОСМОТР в Excel и особенности ее использования

​$B$12:$B$17 – массив соответствующих​ и ВПР.​ одной строке или​ вместо этого применять​ ВПР. Но в​

• ​ обработки данных, о​
• ​ значений.​
• ​ для ссылки. Нам​

​ в связке с​ результата после этого​ нам нужен точный​, прежде всего, имеет​ диапазоне. Собственно на​ решения. После того,​ искомой ячейки, где,​ которая является произведением​SendKeys «^а»​ процентов.​​ одном столбце. Синтаксис:​ новые более усовершенствованные​

​ ВПР просматриваемый столбец​ которых шёл разговор​Если мы изменим число​

​ нужен первый вариант.​ функцией​ произойдет автоматически.​ результат.​ смысл только тогда,​ это указывает даже​ как выставлены галочки​ как мы помним,​ заработной платы, указанной​End Sub​Примечание: знак «$» использован​

​Пример 1. Физик определял​

​=ПРОСМОТР(искомое_значение; просматриваемый_вектор; [вектор_результатов])​ функции ВПР (для​ обязательно должен быть​

​ выше: векторную или​

1. ​ в поле​ Поэтому оставляем в​ИНДЕКС​Теперь давайте рассмотрим, как​После того, как все​ когда обрабатываются числовые​
2. ​ его название. Также​ и переключатели, жмем​ находится коэффициент, умножением​ в отдельном столбце,​The_Prist​ для «фиксации» ссылок​ ускорение свободного падения​Два первых аргумента функции​ поиска в первом​ крайним левым. У​ форму массива. Так​«Приблизительная сумма выручки»​ этом окне все​
3. ​. Данный аргумент выводит​ можно использовать​ данные установлены, жмем​ значения, а не​ эта функция при​ на кнопку «OK».​ на который основной​

​ на определенный коэффициент.​: У меня нет​ на ячейки.​ эмпирическим путем: с​ должны быть обязательно​ столбце диапазона) и​ ПРОСМОТР данное ограничение​ как мы сейчас​на другое, то​ настройки по умолчанию​ в указанную ячейку​

​ПОИСКПОЗ​

​ на кнопку​ текстовые.​ применении в комплексе​

​Если по какой-либо причине​

1. ​ заработной платы будет​ При этом, общая​ никаких ошибок с​Результат вычислений:​ определенной высоты запускал​ указаны.​ ГПР (для поиска​ отсутствует, что мы​ рассматриваем именно векторный​ соответственно автоматически будет​ и жмем на​ содержимое диапазона заданное​для работы с​«OK»​В случае, если​ с другими операторами​
2. ​ результаты поиска решений​ рассчитана величина премии.​ сумма денежных средств,​ диалоговым окном, все​То есть, Иванову был​ обтекаемый предмет и​Описание версии 3-х аргументов:​ в первой строке​ и видим на​ вид, то выбираем​ пересчитано и содержимое​ кнопку​ по номеру его​ числовыми выражениями.​.​

​ПОИСКПОЗ​ сообщает им номер​ вас не удовлетворяют,​ Адрес можно прописать​ выделяемых на премию,​ ищет и заменяет​ выдан кредит под​ засекал время, которое​Искомое_значение – объект числовых,​ диапазона). Они ничем​ примере выше.​ первый вариант. Жмем​ поля​«OK»​ строки или столбца.​

​Ставится задача найти товар​

Примеры использования функции ПРОСМОТР для быстрого поиска в таблицах Excel

​Программа выполняет вычисление и​при заданных настройках​ позиции конкретного элемента​ или при их​ теми же способами,​ равна 30000 рублей.​ как положено.​ 6% годовых.​ требовалось на прохождение​ текстовых, ссылочных или​ не уступают по​Урок:​ на кнопку​«Товар»​.​ Причем нумерация, как​

​ на сумму реализации​ выводит порядковый номер​ не может найти​ для последующей обработки​

​ подсчете программа выдаёт​ как мы это​ Ячейка, в которой​

​ayfred​

​Для определения суммы возврата​

1. ​ пути от точки​ логических данных. Функция​
2. ​ функционалу формуле ПРОСМОТР​Мастер функций в Excel​«OK»​

​.​

​Открывается окно аргументов функции​ и в отношении​ 400 рублей или​

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

Вторая версия функции ПРОСМОТР в Excel

​ таком случае, возвращаемся,​ ячейки.​ имеет название целевой,​ раскладкой работает, как​=B2+B2*C2​ соприкосновения с поверхностью​ значения этих данных​ работают более корректно.​ способа, данная форма​Открывается окно аргументов. Как​Функция ИНДЕКС в Excel​. В поле​ПОИСКПОЗ​ этой сумме по​в выделенном массиве​ ячейке ошибку​ПОИСКПОЗ​

​ описанным выше способом,​В поле «В соответствии​ так как наша​ через условие проверить​Описание значений:​ земли. Затем по​ в векторе данных.​ А вот векторный​ оперирует целым массивом,​ видим, у данной​Как видим, оператор​«Массив»​

​, выполняется не относительно​

​ возрастанию.​

1. ​ в той ячейке,​«#Н/Д»​
2. ​выглядит так:​ в диалоговое окно​ с ограничениями» можно​ цель подобрать данные​ какая раскладка на​
3. ​B2 – сумма (тело)​ формуле g=2S/t2 определял​

​Просматриваемый_вектор – диапазон данных,​ оператор ПРОСМОТР является​ в который сразу​

​ функции три аргумента:​

​ПОИСКПОЗ​указываем адрес того​ всего листа, а​

​Прежде всего, нам нужно​ которую мы задали​

​.​

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

1. ​ параметров. Пересматриваем все​ выставить определенные ограничения​
2. ​ именно под это​ компьютере включена?​ кредита, взятого Ивановым;​

​ искомую величину. После​

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

​ только внутри диапазона.​ отсортировать элементы в​ ещё на первом​При проведении поиска оператор​

​Теперь рассмотрим каждый из​ введенные данные, так​

​ для данных, например,​ число.​Serge_007​B2*C2 – сумма процентов​

​ проведения нескольких опытов​

​ столбец таблицы или​

1. ​ пор.​ и диапазон результатов.​Просматриваемый вектор;​
2. ​ функцией для определения​ИНДЕКС​
3. ​ Синтаксис этой функции​ столбце​

​ шаге данной инструкции.​

​ не различает регистры​ трех этих аргументов​

​ как возможно где-то​ сделать значения целыми​Коэффициент, который применяется для​: http://www.excelworld.ru/forum/2-3363-36305-16-1358686153​ за использование в​ были получены следующие​

exceltable.com

Организация поиска как в экселе (Макросы/Sub)

​ строку. Вектор данных​​Урок:​ При этом просматриваемый​Вектор результатов.​ порядкового номера указанного​будет искать название​ следующий:​«Сумма»​ Номер позиции будет​ символов. Если в​ в отдельности.​ была допущена ошибка.​

​ или неотрицательными. Для​​ расчета суммы премии,​

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

​ массиве присутствует несколько​​«Искомое значение»​
​ В случае, если​

​ этого, жмем на​​ нам предстоит вычислить​: Спасибо огромное, очень​Результат вычислений:​Необходимо определить, находится ли​ текстовые и логические​ Эксель​

​ обязательно крайнем левым​​ желают применять данный​ данных. Но польза​
​ случае – это​При этом, если массив​​ данную колонку и​
​«4»​
​ точных совпадений, то​

​– это тот​​ ошибка найдена не​ кнопку «Добавить».​ с помощью функции​ помогли​То есть, клиент Иванов​

​ среди полученных результатов​​ значения. Все значения​Как видим, оператор ПРОСМОТР​ столбцом массива.​ оператор вручную, без​ от него значительно​

​ столбец​​ одномерный, то можно​

​ переходим во вкладку​​.​ПОИСКПОЗ​

excelworld.ru

​ элемент, который следует​

Поиск в программе Microsoft Excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Отблагодарите автора, поделитесь статьей в социальных сетях.

ВПР с поиском по нескольким листам

ВПР по всем листам (43,0 KiB, 12 619 скачиваний)

Если необходимо найти какое-либо значение в большой таблице очень часто применяется функция ВПР. Но ВПР работает только с одной таблицей и нет никакой возможности средствами самой функции просмотреть искомое значение на нескольких листах. Если поиск необходимо осуществить только по двум листам, то можно схитрить:
=ВПР( A1 ;ЕСЛИ(ЕНД(ВПР( A1 ;Лист2!A1:B10;2;0));Лист3!A1:B10;Лист2!A1:B10);2;0)

А когда листов больше? Можно плодить ЕСЛИ. Но это во-первых совсем не наглядно и во-вторых очень непрактично, т.к. при добавлении или удалении листов придется править всю мега-формулу. Да и при работе с количеством листов более 10 есть большой шанс, что длина формулы выйдет за пределы допустимой.

Есть небольшой прием, который поможет искать значение в указанных листах. Для начала необходимо создать на листе список листов книги, в которых искать значение. В приложенном к статье примере они записаны в диапазоне $E$2:$E$5 .
=ВПР( A2 ;ДВССЫЛ(«‘»&ИНДЕКС( $E$2:$E$5 ;ПОИСКПОЗ(ИСТИНА;СЧЁТЕСЛИ(ДВССЫЛ(«‘»& $E$2:$E$5 &»‘!A1:A50″); A2 )>0;0))&»‘!A:B»);2;0)

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

Перед чтением дальше советую скачать пример:

ВПР по всем листам (43,0 KiB, 12 619 скачиваний)

ДВССЫЛ нам нужна для преобразования текстового представления ссылок на листы в действительные. Подробно не буду останавливаться на принципе работы ДВССЫЛ, просто приведу этапы вычислений:
СЧЁТЕСЛИ(ДВССЫЛ(«‘»& $E$2:$E$5 &»‘!A1:A50»); A2 )

В результате вычисления данного блока у нас получается массив из количества повторений искомого значения на каждом из указанных листов: СЧЁТЕСЛИ(<1;0;0;0>;A2) . Поэтому следующий блок
ПОИСКПОЗ(ИСТИНА;СЧЁТЕСЛИ(ДВССЫЛ(«‘»& $E$2:$E$5 &»‘!A1:A50»); A2 )>;0;0)
работает именно с этим:
ПОИСКПОЗ(ИСТИНА;СЧЁТЕСЛИ(<1;0;0;0>; A2 )>0;0)
Читать подробнее про СЧЁТЕСЛИ

в результате чего мы получаем позицию имени листа в массиве имен листов $E$2:$E$5 , с помощью ИНДЕКС получаем имя листа и подставляем это имя уже к ДВССЫЛ, а она в ВПР:
=ВПР( A2 ;ДВССЫЛ(«‘»&ИНДЕКС(<«Астраханьоблгаз»:»Липецкоблгаз»:»Оренбургоблгаз»:»Ростовоблгаз»>;1)&»‘!A:B»);2;0) =>
=ВПР( A2 ;ДВССЫЛ(«‘Лист2’!A:B»);2;0) =>
=ВПР( A2 ;’Лист2′!A:B;2;0)

Что нам и требовалось. Теперь если в книгу будут добавлены еще листы, то необходимо будет всего лишь дописать их к диапазону $E$2:$E$5 и при необходимости этот диапазон расширить. Так же можно задать диапазон $E$2:$E$5 как динамический и тогда необходимость в правке формулы отпадет вовсе.

Используемые в формуле величины:
A2 — ссылка на ячейку с искомым значением. Т.е. указывается то значение, которое требуется найти на листах.

$E$2:$E$5 — диапазон с именами листов, в которых требуется осуществлять поиск указанного значения ( A1 ).

Диапазон «‘!A1:A50» — это диапазон, в котором СЧЁТЕСЛИ ищет совпадения. Поэтому указывается только один столбец данных. При необходимости следует расширить или изменить. Можно указать так же «‘!A:A» , но при этом следует учитывать, что указание целого столбца может привести к значительному увеличению времени выполнения функции. Поэтому имеет смысл просто задать диапазон с запасом, например «‘!A1:A10000» .

«‘!A:B» — диапазон для аргумента ВПР — Таблица. В первом столбце этого диапазона на каждом из указанных листов ищется указанное значение ( A2 ). При нахождении возвращается значение из указанного столбца. Читать подробнее про ВПР>>

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

ВПР по всем листам (43,0 KiB, 12 619 скачиваний)

Так же можно искать по нескольким листам разных книг , а не только по нескольким листам одной книги. Для этого необходимо будет в списке листов вместе с именами листов добавить имена книг в квадратных скобках: [Книга1.xlsb]Май
[Книга1.xlsb]Июнь
[Книга2.xlsb]Май
[Книга2.xlsb]Июнь
Перечисленные книги обязательно должны быть открыты

Решил добавить простенькую функцию пользователя(UDF) для тех, кому проще «общаться» с VBA, чем с формулами. Функция ищет указанное значение во всех листах книги, в которой записана(даже в скрытых):

Function VLookUpAllSheets(vCriteria As Variant, rTable As Range, lColNum As Long, Optional iPart As Integer = 1) As Variant Dim rFndRng As Range If iPart <> 1 Then iPart = 2 For i = 1 To Worksheets.Count If Sheets(i).Name <> Application.Caller.Parent.Name Then With Sheets(i) Set rFndRng = .Range(rTable.Address).Resize(, 1).Find(vCriteria, , xlValues, iPart) If Not rFndRng Is Nothing Then VLookUpAllSheets = rFndRng.Offset(, lColNum — 1).Value Exit For End If End With End If Next i End Function

Функция попроще, чем ВПР — последний аргумент(интервальный_просмотр) выполняет несколько иные, чем в ВПР функции. Хотя полагаю немногие его используют в классическом варианте.
rTable — указывается таблица для поиска значений(как в стандартной ВПР)
vCriteria — указывается ссылка на ячейку или текстовое значение для поиска
lColNum — указывается номер столбца в таблице rTable, значение из которого необходимо вернуть — может быть ссылкой на столбец — СТОЛБЕЦ().
iPart — указывается метод просмотра. Если не указан, либо указана цифра 1, то поиск осуществляется по полному совпадению с ячейкой. Но в таком варианте допускается применение подстановочных символов * и ?. Если указано значение, отличное от 1, то совпадение будет отбираться по части вхождения. Если в vCriteria указать «при», то совпадением будет считаться и слово «прибыль»(первый буквы совпадают) и «неприятный»(в середине встречается «при»). Но в этом случае знаки * и ? будут восприниматься «как есть». Может пригодиться, если в искомом тексте присутствуют символы звездочки и вопросительного знака и надо найти совпадения, учитывая эти символы.

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

Надстройка для поиска на всех листах книги Excel

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

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

В Excel 2007 и 2010 панель инструментов можно найти на вкладке «Надстройки»:

Надстройка SearchText является расширенной версией надстройки для поиска на текущем листе.

Назначение кнопок надстройки:

— поле для ввода искомого текста. Нажатие клавиши Enter запускает поиск.

— удаление листа с результатами поиска. (выполняется автоматически при повторном поиске)

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

или — переключатель режимов поиска
(щелкните на этой кнопке для переключения режима поиска)

При запуске поиска (вводе числа в текстовое поле, и нажатии Enter) макрос создаёт в текущем файле дополнительный лист (с названием «Результаты поиска»),
и копирует на этот лист все строки, в которых содержится заданный текст.

Выполняется поиск по частичному совпадению текста (т.е., при поиске текста 1234 будут найдены ячейки, содержащие числа 12345, 5612348 и т.д.)

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

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

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

Как в excel сделать поиск по всем листам

Ингвар, доброго времени.

В качестве не очень изящного, но всё-таки решения могу предложить следующую формулу, которую можно протянуть по всем ячейкам Вашего примера. Вставлять в B3 на Листе1.

На первый взгляд выглядит довольно объёмно и страшно, но если разобраться — довольно проста.

Простейшая функция для поиска — ВПР. С её помощью можно найти данные по одной таблице. Что, я уверен, Вы уже с лёгкостью делаете. Если ВПР будет искать в таблице, где НЕТ того, что мы ищем (например, если искать «единицу» на «Листе3» Вашего примера), формула выдаёт ошибку Н/Д. И это свойство можно использовать.

Функция ЕСЛИОШИБКА проверяет то, что мы вводим. И если наша функция внутри ЕСЛИОШИБКИ выдает, например, Н/Д, то ЕСЛИОШИБКА выдаст нам второе значение, записанное через точку с запятой. И моя формула построена следующим образом:

= ЕСЛИОШИБКА (*искать в первой таблице* ; *если не нашли в первой таблице, ищем во второй* )

При этом внутрь условного куска *если не нашли в первой таблице, ищем во второй* можно вложить вторую ЕСЛИОШИБКА для просмотра ещё одной таблицы. Что я и сделал. Получилось:

= ЕСЛИОШИБКА (*искать в первой таблице* ; ЕСЛИОШИБКА ( *искать во второй таблице* ; *искать в третьей таблице) )

При этом таких вложений из ЕСЛИОШИБКА можно сделать не ограниченное количество.

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

Советы и лайфхаки по работе с Excel

Многие сталкивались с файлами Ексель, в которых создано огромное количество листов. Чтобы найти нужный лист нужно прокрутить все созданные в книге листы.

Но есть более простой способ быстро открыть нужный лист.

Щелкните правой кнопкой мыши на кнопки прокрутки листов, которые находятся слева от названия листов и выберите нужный лист:

Как снять пароль в Excel? Три рабочих способа снятия пароля.

Способ 1. (Используем программу) Ищем в поисковике и загружаем программу

Как собрать несколько книг Excel в одну?

Например, мы имеем много рабочих книг Excel, и мы хотим

Что такое макрос и куда его вставлять в Excel?

Нам в работе иногда не хватает стандартных возможностей Эксель и приходится напрягать

Как добавить абзац в ячейке Excel?

Достаточно часто при заполнении ячейки текстом, возникает необходимость ввести текст

Как посчитать количество уникальных значений в колонке Excel?

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

Отсортировать уникальные значения в Excel

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

Как сделать и добавить выпадающий список в Excel?

В Excel есть одна интересная особенность, а именно возможность вводить

Как быстро перейти в нужный лист книги Excel?

Многие сталкивались с файлами Ексель, в которых создано огромное количество