Поиск в Excel

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

 

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

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

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

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

 

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

 

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

Поиск по всем листам в Excel

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

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

 

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

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

Комментарии

Настройки просмотра комментариев

Выберите нужный метод показа комментариев и нажмите "Сохранить установки".

Здравствуйте, 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, поскольку у комбобокса на панели инструментов нет таких свойств, как цвет фона)

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

Function Add_Control(ByRef Comm_Bar, ByVal B_Type As Integer, ByVal B_Face As Integer, _
                     ByVal On_Action As String, ByVal B_Caption As String, _
                     Optional ByVal Begin_Group As Boolean = False, Optional Tag As String = "") As CommandBarButton

на
Function Add_Control(ByRef Comm_Bar, ByVal B_Type As Integer, ByVal B_Face As Integer, _
                     ByVal On_Action As String, ByVal B_Caption As String, _
                     Optional ByVal Begin_Group As Boolean = False, Optional Tag As String = "") As Object

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

Private Sub Workbook_Open()    ' при открытии книги
    On Error Resume Next
    ' удаляем поле, если оно уже присутствует на панели
    Application.CommandBars(1).FindControl(, , "SearchCellAddin").Delete
    Application.CommandBars(1).Controls(Caption$).Delete
 
    ' создаём поле заново
    Dim combo As CommandBarComboBox
    Set combo = Add_Control(Application.CommandBars(1), 2, -1, "SearchCell", "Поиск", , "SearchCellAddin")
    With combo
        .Width = 150
        .Style = msoComboLabel ' поле с надписью перед ним
    End With
End Sub

Я так понимаю, что надстройку поиска нельзя редактировать, поскольку она закрыта паролем...
А если мне нужно изменить цвет поля?
В моём случае Я хочу добавить к полю слово поиск и изменить цвет поля, иначе цвет этого самого поля у меня совпадает с цветом моих настроек 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, - если в настройках поиска выбрать опцию "Искать в книге":

Поиск по всем листам в Excel

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

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

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

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

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

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

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

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

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

Отправить комментарий

Содержание этого поля является приватным и не предназначено к показу.
CAPTCHA
Подтвердите, пожалуйста, что вы - человек:
  _____   ____    ____    ___   _   _   ____  
| ___| | __ ) / ___| |_ _| | \ | | |___ \
| |_ | _ \ \___ \ | | | \| | __) |
| _| | |_) | ___) | | | | |\ | / __/
|_| |____/ |____/ |___| |_| \_| |_____|
Введите код, изображенный в стиле ASCII-арт.

Не получается применить макрос? Не удаётся изменить код под свои нужды?

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