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

Способ 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.

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

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

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

1. Выберите диапазон с данными, которые вам нужны для поиска в поле поиска, затем нажмите Условное форматирование > Новое правило под Главная таб. Смотрите скриншот:

2. в Новое правило форматирования диалоговое окно, вам необходимо:

2.1) Выбрать Используйте формулу, чтобы определить, какие ячейки следует форматировать. вариант в Выберите тип правила коробка;

2.2) Введите формулу = ISNUMBER (ПОИСК ($ B $ 2; A5)) в Формат значений, где эта формула истинна коробка;

2.3) Нажмите Формат кнопка, чтобы указать выделенный цвет для искомого значения;

2.4) Нажмите OK кнопку.

Заметки:

1. В формуле $ B $ 2 — это пустая ячейка, которую необходимо использовать в качестве поля поиска, а A5 — это первая ячейка выбранного диапазона, в котором необходимо искать значения. Пожалуйста, измените их по своему усмотрению.

2. В формуле не учитывается регистр.

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

Создайте собственное окно поиска с формулами для вывода всех результатов поиска

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

1. Выберите пустую ячейку, которая находится рядом с ячейкой E4, здесь я выбираю ячейку D4, затем вводю формулу = ЕСЛИОШИБКА (ПОИСК ($ B $ 2; E4) + СТРОКА () / 100000; «») в строку формул, а затем нажмите Enter ключ. Смотрите скриншот:

Внимание: В формуле $ B $ 2 — это ячейка, которую необходимо использовать в качестве поля поиска, E4 — это первая ячейка списка данных, в котором необходимо выполнить поиск. Вы можете менять их по своему усмотрению.

2. Продолжайте выбирать ячейку E4, затем перетащите маркер заполнения вниз в ячейку D23. Смотрите скриншот:

3. Теперь выберите ячейку C4 и введите формулу. = ЕСЛИОШИБКА (РАНГ (D4; $ D $ 4: $ D $ 23,1); «») в панель формул и нажмите Enter ключ. Выберите ячейку C4, затем перетащите маркер заполнения до C23. Смотрите скриншот:

4. Теперь вам нужно заполнить диапазон A4: A23 порядковым номером, который увеличивается на 1 от 1 до 20, как показано на скриншоте ниже:

5. Выберите пустую ячейку, в которой будет отображаться результат поиска, введите формулу. = ЕСЛИОШИБКА (ВПР (A4; $ C $ 4: $ E $ 23,3; FALSE); «») в панель формул и нажмите Enter ключ. Продолжая выбирать ячейку B4, перетащите маркер заливки вниз до B23, как показано ниже.

С этого момента при вводе данных в поле поиска B2 все совпавшие значения будут перечислены в диапазоне B4: B23, как показано на скриншоте ниже.

Внимание: этот метод не чувствителен к регистру.

Программа 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 найти слово при использовании фильтра, потребуется изучить следующий алгоритм действий:

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

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

Поиск в Excel.

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

excel-office.ru

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

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

Описание

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

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

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

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

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

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

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

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

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

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

Синтаксис

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

​ and Reference)​

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

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

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

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

Замечание

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

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

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

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

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

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

Примеры

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

support.office.com

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

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

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

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

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

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

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

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

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

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

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

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

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

excel2.ru

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

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

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

support.office.com

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

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

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

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

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

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

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

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

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

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

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

​Аргумент​

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

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

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

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

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

​маржа​

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

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

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

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

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

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

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

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

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

planetaexcel.ru

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

Поиск в программе 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?

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

Перейти к содержанию