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

Поиск в Excel

Штатными средствами Excel вывести поле для поиска на панель инструментов не удаётся, а вызывать каждый раз диалоговое окно нажатием комбинации клавиш Ctrl + F не всегда удобно.

На помощь придёт эта надстройка — она формирует в строке меню Excel 2003 поле для поиска по всем листам:

Достаточно ввести искомый текст, и нажать клавишу Enter, — и перед вами полный список всех подходящих ячеек со всех листов книги.

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

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

Конечно, функциональность этой надстройки присутствует и в Excel, — если в настройках поиска выбрать опцию «Искать в книге»:

Моя же надстройка чуть упрощает работу — не надо нажимать лишние кнопки для типа Ctrl + F, и не надо выбирать область поиска.

К тому же, при использовании надстройки, вы можете провести мышом (при нажатой левой кнопке) по результатам поиска, — и Excel пролистает (выделит) все найденные ячейки по очереди (во встроенном поиске Excel надо щелкать на каждом результате отдельно)

(добавлено 29.07.2011) Немного подправил код надстройки:

• теперь форма с результатами закрывается по нажатию Esc
• при отсутствии открытой книги не выводится пустая форма
• панель инструментов не сбрасывается к настройкам "по-умолчанию" перед добавлением поля

• 197885 просмотров

Комментарии

Здравствуйте, Lilit
Всё делается очень просто: в отдельном столбце пишете (и протягиваете вниз) простейшую формулу типа =A1&» «&B1&» «&C1
которая склеит значения 3 столбцов через пробел.
И потом сможете выполнять поиск штатными средствами Excel по полному ФИО.

Добрый день! Очень нужна Ваша помощь. Я работая на по программе Excel и очень часто необходимо найти отдельные строки. В основном у меня в файлах каждая строка разделена на несколько столбцов. Например сперва написано ИМЯ, во втором столбце ФАМИЛИЯ, а в третьем ОТЧЕСТВО. Для того, чтобы найти кого-то я использую Ctrl F и ищу ИМЯ (пр: Ольга), а потом читаю все строки, где программа нашла имя и читаю ФАМИЛИИ, что бы найти нужную. Это занимает очень много времени. Есть ли какой то способ найти нужную строку, в которой информация разделена на несколько частей. Пр: искать Ольга, Николаевна, Фомин, чтобы программа показала все строки, где есть данные 3 слова.
Заранее спасибо.

Такой есть вопрос.
Например есть четыре идентичные книги 1а, 2а, 3а и 4а.
В каждой книге по 17 листов с названиями 1в, 2в, 3в и т. д.
Наименования и порядок листов в каждой книге идентичны, различно только содержание.
Возможно ли сделать так, чтобы выбрав, например в одной из этих книг лист 3в,
в других открытых идентичных книгах выбирался этот же лист?
Я всегда работаю с одним набором одинаковых книг и, как правило, приходится во всех
книгах править поочерёдно один и тот же лист и приходится каждый рах при переходе на новую книгу нажимать этот лист.

Даже не знаю, понятно ли я объяснил.

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

Насчёт длины поля: в Excel 2003 можно задать длину текстового поля.
Как это сделать на ленте (Excel 2007/2010) — не знаю (пробовал, не получилось)

Насчёт сортировки, — надо более подробное описание, что и как должно работать.
По цене, — я беру заказы на сумму от 1000 рублей.
Окно поиска какое должно появляться?
Форма с одним длинным текстовым полем?

Назначить макросу комбинацию клавиш Ctrl + F, — без проблем.

Подскажите, где расширить (сделать длиннее) окно поиска в панели «надстройки» для Excel 2007
Пробовал в коде просто поменять 150 (как я понял это искомая длинна блока запроса) на большее число (скажем 666) — ничего не выходит.

А за решение спасибо огромное, особенно понравилось скрол по книге с ЛКМ из результатов поиска.

Да. Окно результатов перенастроил под себя.

Да, можно узансть, скока будет стоить следующая доработка — в окне результатов поиска нужна сортировка по столбцам.
И отдельно — сколько стоит, чтоб окно запроса поиска появлялось в Excel 2007 в стиле родного CTRL-F, то есть чтоб посередине окна рабочей книге и по хоткею.
Исчо раз
Спасибо

Сделать можно,- только это заметно усложнит код (встроенный в Excel поиск тут не подойдёт)
Если готовы оплатить доработку, — сделаю.

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

Здравствуйте, Павел.
Можете сами доработать — код открыт, делайте с ним что угодно.
Если сами не разберётесь, — всегда есть возможность заказать доработку (не бесплатно)

Или воспользуйтесь этой надстройкой: http://excelvba.ru/programmes/SearchText
(там есть вывод на лист результата всех столбцов)

Добрый день.
Великолепная надстройка, работает в 2007м экселе. Очень помогла ваша работа в работе со списочными файлами 🙂 Единственно подскажите, а возможно ли как то сделать что бы в окошке итога поиска был ещё 1 столбец справа от «Содержимого ячейки» который бы отображал данные из ячейке справа от найденной.

Т.е. у меня список большой в 2 столбца, условно в первом название товара, во втором регион из которого мы его поставляем.

Хотелось бы, что бы в итоге в результате поиска мне как раз показывал инф. о регионе происхождения этого товара 🙂 надеюсь я понятно изложил свои мысли. И могу ли я сам как то доработать до моего видения?

Заранее благодарю за ответ!

Да, можно. Привязывайте к чему угодно, я же не против)
Код открыт — изменяйте его как хотите.

Можно как-нибудь этот код привязать к кнопке на листе?

Всё, что вы перечислили, сделать можно (сложнее всего — прокрутку списка мышью, остальное проще)

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

Или это уже требования не из разряда свободно-распространяемой надстройки?

Да, я сам не там смотрел. Всё там редактируется. Изменил, что требовалось и надпись появилась. Но были обнаружены некоторые неудобства в работе.
Первое — есть ли возможность прокрутки списка найденного колесом мыши?
Не всегда удобно левой кнопкой водить и не всегда точно.
Второе — есть ли возможность при повторном клике на форму сделать так, что бы предыдущая запись удалялась сразу? Или вообще, что бы удалялась сразу после вывода результатов поиска (это даже лучше). Иначе последняя поисковая фраза во-первых болтается там без дела, во-вторых всем видна кто рядом (это не айс знать всем, что я там искал). В-третьих двойным щелчком вся надпись не выделяется и приходится её выделять вручную и удалять. В-четвёртых не оперативно в работе. Хочется нажать на форму, внести данные и найти, а не сначала удалять старую запись, а потом вводить новую.

Код, вообще-то, не закрыт.

Добавить слово «поиск» перед полем — несложно, а вот сделать поле цветным — проблематично (надо использовать WinAPI, поскольку у комбобокса на панели инструментов нет таких свойств, как цвет фона)

Чтобы добавить слово «поиск»,
замените

И измените процедуру Workbook_Open следующим кодом:

Я так понимаю, что надстройку поиска нельзя редактировать, поскольку она закрыта паролем.
А если мне нужно изменить цвет поля?
В моём случае Я хочу добавить к полю слово поиск и изменить цвет поля, иначе цвет этого самого поля у меня совпадает с цветом моих настроек Excel и оно просто теряется и фактически невидимо.
Как быть?

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

Здравствуйте. Огромное спасибо за полезную и просто суперскую надстройку. Скажите, а такого типа надстройку нельзя ли сделать с поиском по excel-файлам, например, размещенных в одной директории в нужном столбце? мне кажется, она была бы не менее популярна и востребована. Если вы сможете помочь, не сочтите за труд, отпишитесь на мыло. заранее благодарен

а через CTRL+F слабо ??

Уже нашла, прошу прощения)

Диана, ну а как вы хотели?
Надстройка — это обычный файл Excel, если вы его запускаете — он открывается, не запускаете — не открывается.
Представляете, все бы открываемые вами файлы автоматически добавлялись в автозагрузку. запустили Excel, а там автоматом открылось 200 старых файлов.

Я специально расписывал все способы, как можно подставить надстройку в автозапуск:
http://excelvba.ru/code/autorun
Смотрите варианты, которые без макросов: добавление файла в папку автозагрузки, или подключение через меню «Надстройки»
(выберите любой из этих двух способов — и панель поиска никуда у вас не исчезнет)

Надстройка чудесная! Но когда закрываю и открываю Excel заново, её нужно снова запускать, т.к. поле поиска не появляется. Может, проблема с моими настройками?

Спасибо за надстройку. Если текст находится в объединенной ячейке и только в одном экземпляре, то выскакивет пустое окно «Результат поиска текста».

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

За «пожалуйста» не переделываю )

Если заплатите — запросто переделаю под ваши нужды.
Либо сами переделывайте — вам же надо, да и код не закрыт паролем.
(там переделывать придётся около 90% кода — практически делать всё «с нуля»)

Переделайте, пожалуйста, так:

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

Это я уже заметил, Дима. Спасибо.
Для надёжности оставил и удаление по тегу — теперь точно переизбытка полей быть не должно )

Прикрепил к статье исправленный файл.

Забыл в коде при закрытии поменять:
Application.CommandBars(1).Controls(Caption$).Delete

такой код должен работать(удалять старое поле перед закрытием) во всех версиях:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Caption$ = «Поиск значения во всех листах книги» & vbLf & » » & vbLf & _
«Введите текст для поиска, и нажмите «Enter»»
‘ удаляем поле, если оно уже присутствует на панели
Application.CommandBars(1).Controls(«SearchCellAddin»).Delete
End Sub

Private Sub Workbook_Open() ‘ при открытии книги
On Error Resume Next
Caption$ = «Поиск значения во всех листах книги» & vbLf & » » & vbLf & _
«Введите текст для поиска, и нажмите «Enter»»
‘ удаляем поле, если оно уже присутствует на панели
Application.CommandBars(1).Controls(Caption$).Delete
‘ создаём поле заново
Add_Control(Application.CommandBars(1), 2, -1, «SearchCell», _
Caption$, , «SearchCellAddin»).Width = 150
End Sub

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

Прикрепил исправленную версию надстройки — которая при закрытии Excel удаляет созданное поле.
Скачайте новую версию, откройте её, и закройте.
Если поможет, но частично (уберёт только одно поле) — повторите процедуру несколько раз.

PS: Странно, надстройка должна формировать только одно поле.
Что за версия Excel у вас установлена?

PS: Если не поможет — запустите Excel (откроется пустая книга), откройте редактор VBA нажатием Alt + F11, потом нажмите Ctrl + G для отображения окна Immediate.

В этом окне введите текст CommandBars(1).Reset и нажмите Enter:

После этих действий гланая панель инструментов примет первоначальный вид:

Окошко куда вводиться слово для поиска появилось несколько раз и остались, как их все убрать??

Артемий, попробуйте обновить эту страницу (нажав Ctrl + R), или же открыть её в другом браузере.

Дело в том, что последние 2 дня сайт временами работает нестабильно (кто-то нашел в нем уязвимость, и эксплуатирует её, нарушая работу сайта),

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

Перезагрузка страницы (сброс кэша браузера) должна помочь.

На всякий случай выслал копию прикреплённого файла вам на почту.

Добрый день! Не получается скачать Ваш файл — ошибка.

Хотел скачать с Вашего сайта файл «SearchWorkbook.xla» (на стр. http://excelvba.ru/code/SearchCells), но по ссылки открывает HTML с кодом.
Скачиваю как объект — опять же скачивает страничку.

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

Заранее благодарю!
А

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

Конечно, функциональность этой надстройки присутствует и в Excel, — если в настройках поиска выбрать опцию "Искать в книге":

Моя же надстройка чуть упрощает работу — не надо нажимать лишние кнопки для типа Ctrl + F, и не надо выбирать область поиска.

К тому же, при использовании надстройки, вы можете провести мышом (при нажатой левой кнопке) по результатам поиска, — и Excel пролистает (выделит) все найденные ячейки по очереди (во встроенном поиске Excel надо щелкать на каждом результате отдельно)

> Можно выделить одновременно все листы в книге и воспользоваться обычным поиском в Excel
у меня в Excel 2003 такая фишка не прошла, ищет только на последнем и 1ом листах

to EducatedFool
функция там просто блеск, спасибо большое за код)))

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

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

Здравствуйте, существует очень простая альтернатива данной надстройке. Можно выделить одновременно все листы в книге и воспользоваться обычным поиском в Excel, ввести искомое выражение в строку поиска и нажать кнопку "найти все". В итоге получим тот же самый результат, что и при использовании надстройки. Но все, что делается никогда не бывает напрасным. Из кода данной надстройки можно почерпнуть много полезного. ОГРОМНАЯ ВАМ ЗА ЭТО БЛАГОДАРНОСТЬ.

В Excel 2007 поле поиска отображается на ленте на вкладке «Надстройки»

На нашем Excel2007 надстройка не запускается. Может быть что-то есть для Excel2007?

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

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

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

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

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

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

1. Находясь во вкладке «Главная», кликаем по кнопке «Найти и выделить», которая расположена на ленте в блоке инструментов «Редактирование». В появившемся меню выбираем пункт «Найти…». Вместо этих действий можно просто набрать на клавиатуре сочетание клавиш Ctrl+F.
2. После того, как вы перешли по соответствующим пунктам на ленте, или нажали комбинацию «горячих клавиш», откроется окно «Найти и заменить» во вкладке «Найти». Она нам и нужна. В поле «Найти» вводим слово, символы, или выражения, по которым собираемся производить поиск. Жмем на кнопку «Найти далее», или на кнопку «Найти всё».
3. При нажатии на кнопку «Найти далее» мы перемещаемся к первой же ячейке, где содержатся введенные группы символов. Сама ячейка становится активной.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Мы рады, что смогли помочь Вам в решении проблемы.

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

Помогла ли вам эта статья?

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

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

=ВПР (C3 ;ЕСЛИ (ЕНД (ВПР (C3 ;Таблица2!C3:D7 ;2; 0)); Таблица3! C3:D7 ;Таблица2! C3:D7 );2; 0).

Но такой вариант работает только с 2 таблицами, а в случае, когда листов больше, нужно увеличивать количество вложений для функции ЕСЛИ. Но при этом:

• во-первых, если много листов, то есть огромный шанс что длина формулы будет больше допустимого размера и перестанет работать;
• во-вторых, это просто непрактично, так как при работе такой мега-формулы возникает значительно риск ошибок и при изменениях придётся переделывать формулу.

Но, как всегда, выход есть. Рассмотрим небольшую хитрость с помощью, которой и будем искать в нужных листах. Начнем работу с создания перечня листов нашей книги, где будем производить поиск значений. В нашем случае это диапазон $E$3:$E$7. Теперь для получения значения в столбик «Найденная стоимость» согласно условию в столбике «Номенклатуру которую ищем» нам нужна формула:

Как видите, формула выделена фигурными скобками, это означает, что её необходимо вводить как формулу массива с помощью горячего сочетания клавиш Ctrl+Shift+Enter. Это самое главное условие правильной работы этой формулы в других случаях она не будет работать. Формула объемная и требует объяснения принципа её работы. Функция ДВССЫЛ необходима, что бы конвертировать текстовые отображения ссылок на листы нашей книги в действительные. Сам принцип работы функции ДВССЫЛ, я описывать не буду, рассмотрим только необходимую формулу для этапа нашего вычисления: СЧЁТЕСЛИ (ДВССЫЛ («’»&$E$3:$E$7 &»‘! C1:C50″); A3).

Как следствие, при вычислении этого блока у нас формируется массив из некоторого количества значений, которые мы ищем, и которые повторяются на листах нашего списка, и имеет вид: СЧЁТЕСЛИ(<2;0;0;0>;A3). О работе функции СЧЁТЕСЛИ я писал отдельно и более подробно.

Следующим рассматриваемым блоком нашей композиции будет формула: ПОИСКПОЗ (ИСТИНА; СЧЁТЕСЛИ (ДВССЫЛ («’»&$E$3:$E$7 &»‘! C1:C50″); A3)>0;0), которая и работает с указанным выше блоком такого вида: ПОИСКПОЗ (ИСТИНА; СЧЁТЕСЛИ (<2;0;0;0>; A3)>0;0). Вследствие чего мы узнаем, какую позицию занимает имя листа в нашем массиве списке листов $E$3:$E$7. Теперь же при помощи функции ИНДЕКС мы получаем название листа, и можем применить его имя в структуре функции ДВССЫЛ, а она передаст полученное значение уже далее функции ВПР. Пошагово это будет выглядеть так:

1. =ВПР (A3; ДВССЫЛ («’»&ИНДЕКС (<«Таблица1″; « Таблица2»; « Таблица3»; «Таблица4»; «Таблица5»>;1) &»‘! C:D»); 2;0);
2. =ВПР(A2;ДВССЫЛ(«’Таблица1′! C:D»);2;0);
3. =ВПР(A2;’Таблица1′!C:D;2;0).

Ну, вот мы и получили универсальную формулу, которая производит поиск значений в Excel и является очень гибкой и удобной. В случаях, когда возникнет необходимость добавить в рабочую книгу еще листы с таблицами, то необходимо всего на всего прописать их в списке рабочих листов $E$3:$E$7, изменив предварительно ее размер или попросту изначально сделать ее динамическим диапазоном, и править формулу будет не нужно.

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

Поиск по нескольким листам с помощью макроса VBA

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

Функция будет иметь следующий вид:

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

Set rFndRng = .Range(rTable.Address).Resize(, 1).Find(vCriteria, , xlValues, iPart)

If Not rFndRng Is Nothing Then

VLookUpAllSheets = rFndRng.Offset(, lColNum — 1).Value

Расшифруются аргументы написанной функции так:

• rTable – прописывается таблица, как в обыкновенной функции ВПР, для поиска значений;
• vCriteria – аргумент, который указывает любое текстовое значение или ссылка на ячейку, которая содержит значение для поиска;
• lColNum – прописывается тот номер столбика из аргумента rTable, значение в котором нам необходимо изъять, возможно, использовать ссылку на столбик с помощью функции СТОЛБЕЦ;
• iPart – аргумент, в котором прописываем необходимый метод просмотра. Когда аргумент не указан или указан аргумент равно 1, в таком случае будет проводиться поиск с полным совпадением значений в ячейках. В таких случаях есть возможность применить символы подстановки: «*» и «?». Если же, в аргументе указано другое значение кроме 1, функция будет искать, и отбирать значения при частичном вхождении.

Я надеюсь, что поиск значений в 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. В меню «Данные» следует последовательно выбрать команды «Фильтр», «Автофильтр», «Условие» и «Пользовательский автофильтр».

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

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

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

Если необходимо найти какое-либо значение в большой таблице очень часто применяется функция ВПР. Но ВПР работает только с одной таблицей и нет никакой возможности средствами самой функции просмотреть искомое значение на нескольких листах. Если поиск необходимо осуществить только по двум листам, то можно схитрить:
=ВПР( 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, 22 013 скачиваний)

ДВССЫЛ нам нужна для преобразования текстового представления ссылок на листы в действительные. Подробно не буду останавливаться на принципе работы ДВССЫЛ, просто приведу этапы вычислений:
СЧЁТЕСЛИ(ДВССЫЛ(«‘»& $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, 22 013 скачиваний)

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

ВАЖНО! если в результате записи формулы получаете ошибку #ССЫЛКА! (#REF!) , то скорее всего файл, из которого получаете данные, сохранен в формате xlsx(xlsm и т.п.), который содержит более 1млн. строк. А файл с формулой в раннем формате xls. Чтобы ошибки не было сохраните файл с формулой тоже в новом формате(Сохранить как — Книга Excel (.xlsx)), закройте и откройте заново. Формула должна заработать, если записана правильно.
Либо укажите фиксированный диапазон для ВПР, с количеством строк не более 65536. Вместо «‘!A:B» должно получиться так: «‘!A1:B60000»

Решил добавить простенькую функцию пользователя(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 указать «при», то совпадением будет считаться и слово «прибыль»(первый буквы совпадают) и «неприятный»(в середине встречается «при»). Но в этом случае знаки * и ? будут восприниматься «как есть». Может пригодиться, если в искомом тексте присутствуют символы звездочки и вопросительного знака и надо найти совпадения, учитывая эти символы.