Vba excel поиск во всей книге
Обновлено: 07.07.2024
Я пытаюсь написать VBA процедура, которая будет принимать строку, искать данную книгу Excel и возвращать мне все возможные совпадения.
в настоящее время у меня есть реализация, которая работает, но она очень медленная, поскольку это двойной цикл for. Конечно, встроенный Excel Find функция "оптимизирована", чтобы найти одно совпадение, но я хотел бы вернуть массив начальных совпадений, к которым я могу применить дальнейшие методы.
я опубликую некоторый псевдокод из того, что у меня уже есть
как уже говорилось ранее, этот двойной цикл for делает вещи работать очень медленно, поэтому я ищу, чтобы избавиться от этого, если это возможно. Есть предложения?
обновление
в то время как приведенные ниже Ответы улучшили бы мой метод, я закончил с чем-то немного другим, поскольку мне нужно было делать несколько запросов снова и снова.
вместо этого я решил перебрать все строки в моем документе и создать словарь, содержащий ключ для каждой уникальной строки. Значение, на которое это указывает, будет списком возможных совпадений, поэтому, когда я запрошу позже, я могу просто проверить, существует ли он, и если да, просто получить быстрый список совпадений.
в основном просто делает одну начальную развертку, чтобы сохранить все в управляемой структуре, а затем запросить эту структуру, которая может быть выполнена в O(1) времени
использование диапазона.Метод Find, как указано выше, наряду с циклом для каждого листа в книге, является самым быстрым способом сделать это. Следующее, например, находит строку "вопрос?"в каждом листе, и заменяет его строкой "ответил!".
осторожно при выполнении цикла поиска, что вы не попадаете в бесконечный цикл. Ссылка на первый найденный адрес ячейки и сравнение после каждого оператора" FindNext", чтобы убедиться, что он не вернулся к первой изначально найденной ячейке.
вы можете использовать диапазон.Найти метод:
Это даст вам первую ячейку, которая содержит строку поиска. Повторяя это с установкой аргумента "после" в следующую ячейку, вы получите все остальные вхождения, пока не вернетесь к первому вхождению.
Это, вероятно, будет намного быстрее.
вы можете прочитать данные в массив. Оттуда вы можете сделать матч в памяти, а не читать по одной ячейке за раз.
ниже код позволяет избежать создания бесконечного цикла. Предположим, что XYZ-это строка, которую мы ищем в книге.
основываясь на идее ответа B Hart, вот моя версия функции, которая ищет значение в диапазоне и возвращает все найденные диапазоны (ячейки):
для поиска значения во всей книге:
в своем сценарий, Я должен искать значение в столбце A и нужно выяснить совпадения в столбце B. Поэтому я создал цикл for, внутри него будет смотреть на весь столбец A и получить точное совпадение из столбца B.
При нажатии на кнопку поиск пользователь вводит часть фразы или код,происходит поиск по всем листам и выводит результат.пользователь нажимает то что что он выбирал и автоматически перебрасывает его на ячейку с его результатом. КГ.xlsx
Попыталась так.но не получается.Помогите
Помощь в написании контрольных, курсовых и дипломных работ здесь
Поиск по всем листам
В VB почти нечего не шарю. говорю сразу) Проблема: нужно сделать поиск ячейки в определенном.
БД в Excel: организовать поиск по всем листам и по нескольким критериям
Добрый вечерок! Помогите, пожалуйста. Есть база данных в Excel. Пока использовалась.
Поиск по столбцу A:A по всем листам документа кроме первого
Здравствуйте! имею код поиска заданного значения по столбцу активного листа, Dim.
Произвести поиск по всем листам определённого слова в определенном столбце
Помогите плиз Задачка - в ёкселе провести поиск по всем листам для поиска определённого слова в.
Добавлено через 1 час 33 минуты
Как сделать что бы поиск был только по столбцам С,I,O
Правда, столбцы остаются выделенными на других листах.
всё таки наверное надо форму сделать.с объектами. пользователю не удобно будет искать.так и так будет нажимать ctrl+f
Добавлено через 18 минут
Создала форму.поместила TextBox и ListBox. и кнопку. думаю пользователь вводит часть слова и результат поиска выводиться на ListBox.
Добавлено через 7 минут
а если поместить на кнопку макрос
Добавлено через 26 секунд
что и как лучше сделать!?
Добавлено через 2 часа 6 минут
НАподоби этого необходимо мне.Но эта программа ищет только на одном листе
Поиск какого-либо значения в ячейках Excel довольно часто встречающаяся задача при программировании какого-либо макроса. Решить ее можно разными способами. Однако, в разных ситуациях использование того или иного способа может быть не оправданным. В данной статье я рассмотрю 2 наиболее распространенных способа.
Поиск перебором значений
Довольно простой в реализации способ. Например, найти в колонке "A" ячейку, содержащую "123" можно примерно так:
Минусами этого так сказать "классического" способа являются: медленная работа и громоздкость. А плюсом является его гибкость, т.к. таким способом можно реализовать сколь угодно сложные варианты поиска с различными вычислениями и т.п.
Поиск функцией Find
Гораздо быстрее обычного перебора и при этом довольно гибкий. В простейшем случае, чтобы найти в колонке A ячейку, содержащую "123" достаточно такого кода:
Вкратце опишу что делают строчки данного кода:
1-я строка: Выбираем в книге лист "Данные";
2-я строка: Осуществляем поиск значения "123" в колонке "A", результат поиска будет в fcell;
3-я строка: Если удалось найти значение, то fcell будет содержать Range-объект, в противном случае - будет пустой, т.е. Nothing.
Полностью синтаксис оператора поиска выглядит так:
Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)
What - Строка с текстом, который ищем или любой другой тип данных Excel
After - Ячейка, после которой начать поиск. Обратите внимание, что это должна быть именно единичная ячейка, а не диапазон. Поиск начинается после этой ячейки, а не с нее. Поиск в этой ячейке произойдет только когда весь диапазон будет просмотрен и поиск начнется с начала диапазона и до этой ячейки включительно.
LookIn - Тип искомых данных. Может принимать одно из значений: xlFormulas (формулы), xlValues (значения), или xlNotes (примечания).
LookAt - Одно из значений: xlWhole (полное совпадение) или xlPart (частичное совпадение).
SearchOrder - Одно из значений: xlByRows (просматривать по строкам) или xlByColumns (просматривать по столбцам)
SearchDirection - Одно из значений: xlNext (поиск вперед) или xlPrevious (поиск назад)
MatchCase - Одно из значений: True (поиск чувствительный к регистру) или False (поиск без учета регистра)
MatchByte - Применяется при использовании мультибайтных кодировок: True (найденный мультибайтный символ должен соответствовать только мультибайтному символу) или False (найденный мультибайтный символ может соответствовать однобайтному символу)
SearchFormat - Используется вместе с FindFormat. Сначала задается значение FindFormat (например, для поиска ячеек с курсивным шрифтом так: Application.FindFormat.Font.Italic = True), а потом при использовании метода Find указываем параметр SearchFormat = True. Если при поиске не нужно учитывать формат ячеек, то нужно указать SearchFormat = False.
Чтобы продолжить поиск, можно использовать FindNext (искать "далее") или FindPrevious (искать "назад").
Примеры поиска функцией Find
Пример 1: Найти в диапазоне "A1:A50" все ячейки с текстом "asd" и поменять их все на "qwe"
Обратите внимание : Когда поиск достигнет конца диапазона, функция продолжит искать с начала диапазона. Таким образом, если значение найденной ячейки не менять, то приведенный выше пример зациклится в бесконечном цикле. Поэтому, чтобы этого избежать (зацикливания), можно сделать следующим образом:
Пример 2: Правильный поиск значения с использованием FindNext, не приводящий к зацикливанию.
В ниже следующем примере используется другой вариант продолжения поиска - с помощью той же функции Find с параметром After. Когда найдена очередная ячейка, следующий поиск будет осуществляться уже после нее. Однако, как и с FindNext, когда будет достигнут конец диапазона, Find продолжит поиск с его начала, поэтому, чтобы не произошло зацикливания, необходимо проверять совпадение с первым результатом поиска.
Пример 3: Продолжение поиска с использованием Find с параметром After.
Следующий пример демонстрирует применение SearchFormat для поиска по формату ячейки. Для указания формата необходимо задать свойство FindFormat.
Пример 4: Найти все ячейки с шрифтом "курсив" и поменять их формат на обычный (не "курсив")
Хочу обратить внимание на то, что в этом примере я не стал использовать "защиту от зацикливания", как в Примерах 2 и 3, т.к. шрифт меняется и после "прохождения" по всем ячейкам, больше не останется ни одной ячейки с курсивом.
Свойство FindFormat можно задавать разными способами, например, так:
Поиск последней заполненной ячейки с помощью Find
Следующий пример - применение функции Find для поиска последней ячейки с заполненными данными. Использованные в Примере 4 SpecialCells находит последнюю ячейку даже если она не содержит ничего, но отформатирована или в ней раньше были данные, но были удалены.
Пример 5: Найти последнюю колонку и столбец, заполненные данными
Для поиска функцией Find по маске (шаблону) можно применять символы:
* - для обозначения любого количества любых символов;
? - для обозначения одного любого символа;
- для обозначения символов *, ? и
. (т.е. чтобы искать в тексте вопросительный знак, нужно написать
?, чтобы искать именно звездочку (*), нужно написать
* и наконец, чтобы найти в тексте тильду, необходимо написать
Поиск в скрытых строках и столбцах
Для поиска в скрытых ячейках нужно учитывать лишь один нюанс: поиск нужно осуществлять в формулах, а не в значениях, т.е. нужно использовать LookIn:=xlFormulas
Поиск даты с помощью Find
Если необходимо найти текущую дату или какую-то другую дату на листе Excel или в диапазоне с помощью Find, необходимо учитывать несколько нюансов:
Приведу несколько примеров поиска даты.
Пример 7: Найти текущую дату на листе независимо от формата отображения даты.
Пример 8: Найти 1 марта 2018 г.
Искать часть даты - сложнее. Например, чтобы найти все ячейки, где месяц "март", недостаточно искать "03" или "3". Не работает с датами так же и поиск по шаблону. Единственный вариант, который я нашел - это выбрать формат в котором месяц прописью для ячеек с датами и искать слово "март" в xlValues.
Тем не менее, можно найти, например, 1 марта независимо от года.
Пример 9: Найти 1 марта любого года.
Книги по теме:
Посмотреть все книги по программированию
Комментарии к статье:
| 10.09.17 Дмитрий | Очень толковая и полезная статья. Помогла мне существенно ускорить мой код. Спасибо! |
| 23.11.17 Гость | Спасибо, хорошая статья. |
| 03.12.17 Владимир | Спасибо! Использую в своих проектах. |
| 07.12.17 Эд | Спасибо, очень пригодилась Ваша статья! |
| 19.01.18 Николай | .find не ищет значение в ячейке, если ячейка в скрытой строке. .match позволяет не беспокоится о том, что на листах с источниками данных строка с искомым значением будет скрыта из-за установленного фильтра. |
| 05.02.18 Владимир | Большое спасибо! Очень толково и понятно. |
| 11.03.18 Гость | Здравствуйте, А если мне требуется найти ячейку в определенном столбце с определенным значением, например "строка 1" и если я нахожу такую, то через несколько строк(неизвестно сколько) мне нужно раскрасить следующую ячейку в другом столбце(самую ближайшую) и остановить цикл, и опять по новой искать дальше "строка 1". Можете посоветовать? |
| 26.03.18 Гость | Спасибо! Все бы так описывали! Все доступно и понятно)) |
| 23.05.18 Аркадий | В VBA я новичок. Активно использую интернет для своих вопросов, однако таких информативных, лаконичных и простых в понимании сайтов не много. Огромное спасибо автору! Адрес уже в закладках. |
| 21.07.18 Гость | Спасибо! Уже несколько недель искала подобное! |
| 25.07.18 Joann | Метод .find (в случае далнейших множественных обращений к этому механизму) разумно вынести в отдельную функцию, как из управляющей процедуры передать в эту функцию ее параметры (обязательный текстовый параметр 'what:=' - передается без проблем, а вот значения ('xlValues', 'xlWhole', . ) для 'LookIn:=', 'LookAt:=', . - приводят к ошибке)? |
| 15.08.18 Марат | Спасибо за статью, пополнил свои знания в части метода Find. Простые переборы хороши на небольших диапазонах. А когда нужно обработать сотни тысяч ячеек, Find - хороший инструмент! |
| 28.08.18 Гость | Познавательно и подробно. Спасибо за статью. |
| 16.10.18 Гость | Хорошая статья. Спасибо |
| 29.10.18 Sega | Полезная статейка. Спасибо. |
| 14.11.18 Гость | Статья для начинающих, а тонкости поиска по дате нет ни одного примера |
| 02.02.19 Ибрагим | Чушь полная, плагиат! |
| 01.03.19 inexsu.wordpress.com | Loop While Not c Is Nothing And c.Address <> firstResult Когда c станет Nothing, c.Address даст ошибку Решение https://inexsu.wordpress.com/2018/03/05/range-findnext-method/ |
| 01.03.19 Администратор | Вы правы, такое действительно может произойти, например, если менять значения найденных ячеек. Т.е., например, ищем значения "asd" и меняем их на "qwe". При замене последнего значения, FindNext ничего уже не найдет, вернет Nothing и произойдет ошибка в условии. Внес изменения в статье. Спасибо вам за подсказку. |
| 07.04.19 Гость | Добрый день. есть 2 таблица на разных листах. макрос нашел нужные ячейки с ИНН по 1 условию, затем мне надо чтобы макрос нашел в таблице 2 на листе 2 все названия клиентов с инн по условию 1 из таблицы 1 с листа 1. как тогда использовать find. |
| 07.04.19 Гость | Лучше использовать функцию ВПР |
| 21.05.19 Гость | Очень подробная статья. Отдельное спасибо за множество примеров. |
| 17.10.19 Михаил | Спасибо большое все понятно доступно и очень полезно |
| 31.10.19 Гость | Спасибо. |
| 01.11.19 Гость | ку |
| 01.11.19 Гость | 2 раза |
| 19.11.19 Kamol | Отлично. Поиск с двумя значениями Пример в колонке А="123" и В="456" |
| 25.12.19 Гость | Спасибо! |
| 05.03.20 Гость | Добрый день! Спасибо за код автору Вопрос |
Как сделать поиск слова по, но с любым регистром и любым набором символов
Пример: Я ввожу слово через Textbox
В таблице есть слово компьютер, колодец, ком
Нужно, чтобы программа находила по запросу "омпьюте", "пьюте" слово компьютер а по запросу "ком" слова компьютер и ком"
Вы когда-нибудь задумывались о поиске определенного значения на нескольких листах или книгах в Excel? В этом руководстве представлены различные методы решения проблем, связанных с поиском на нескольких листах или поиском в нескольких книгах.
Быстрый поиск значения в нескольких открытых книгах с помощью Kutools for Excel
Поиск значения на нескольких листах книги с помощью функции поиска и замены
С помощью Excel Найти и заменить функция, вы можете найти определенное значение на нескольких листах.
1. Выберите несколько вкладок листов, на которых вы хотите найти значение, удерживая Ctrl и щелкнув листы в Панель вкладок листа по одному. Смотрите скриншот:
2. Затем нажмите Ctrl + F для Найти и заменить окна и введите значение, которое вы хотите найти, в Найти то, что текстовое поле под Найдите Вкладка, а затем нажмите кнопку Найти все кнопку, чтобы просмотреть все результаты. Смотрите скриншот:
Поиск и замена значений на листах и в книгах
Искать значение во всех книгах папки с VBA
Если вы хотите найти значение во всех закрытых книгах из папки, вы можете применить только VBA для его решения.
1. Включите новую книгу и выберите ячейку, затем нажмите Alt + F11 ключи для открытия Microsoft Visual для базовых приложений окно.
2. Нажмите Вставить > Модуль и вставьте ниже VBA в новое окно модуля.
VBA: поиск значения во всех книгах папки.
3. Затем нажмите F5 ключа или Запустите кнопку для выполнения этого VBA и Выберите папку Появится диалоговое окно, напоминающее вам о выборе папки, в которой вы хотите искать значение. Смотрите скриншот:
4. Затем щелкните OK и другое диалоговое окно появляется, чтобы напомнить вам, сколько ячеек было найдено. Смотрите скриншот:
5. Нажмите OK в Закрыть его, и все найденные ячейки будут перечислены на текущем листе с соответствующей информацией.
Наконечник: В приведенном выше VBA вы ищете значение «КТЕ» , и вы можете изменить «КТЕ» из этого xStrSearch на другое значение по мере необходимости.
Быстрый поиск значения в нескольких открытых книгах с помощью Kutools for Excel
Если вы просто хотите найти значение в нескольких открытых книгах, вы можете использовать Kutools для Excel 'продвинутый Найти и заменить утилита.
После бесплатная установка Kutools for Excel, сделайте следующее:
1. В одной из открытых книг щелкните Kutools > Навигация, а затем нажмите Найти и заменить кнопка идти на Найти и заменить панель. Смотрите скриншот:
2. Затем нажмите Найдите вкладка и введите значение, которое вы хотите найти в Найти то, что текстовое поле, а затем выберите Все книги из В раскрывающийся список и нажмите Найти все чтобы перечислить все найденные ячейки. Смотрите скриншот:
Наконечник:
Работы С Нами Kutools for Excelпродвинутый Найти и заменить Утилита, вы можете искать и заменять значения на выбранных листах в нескольких книгах, во всех книгах, активной книге, активном листе или выборе.
Доброе время всем!
Недавно в бухгалтерии попросили помочь с формированием и обработкой отчетов.
Так вот, есть книга excel, в ней есть n-ное количество листов (более 300) и последний лист "итоги".
В листе "итоги" таблица с инвентаризационными номерами и количеством материалов в каждом. В листах "Лист 1",
"Лист 2", "Лист 3". "Лист n" содержатся точно такиеже таблицы как и в листе "итоги" только количество записей
в каждой по 20 строк.
Количество материалов в листе "итоги" вводится в ручную. А задача состоит в том что после запуска макроса макрос должен перебирать
таблицу итоги по порядку, искать соответствующий инвентаризационный номер в книге
(поиск производется во всей книге, кроме листа "итоги"), и если найдется вводить количество
в найденной таблице.
Прототип книги прилагается.
Вот мои попытки:
но только поиск тут пропочемуто проводится не по книге а по листу.
Также нужен диапозон поиска но как это все реализовать?
Если есть возможность, пожалуйста помогите с советами.
Доброе время всем!
Недавно в бухгалтерии попросили помочь с формированием и обработкой отчетов.
Так вот, есть книга excel, в ней есть n-ное количество листов (более 300) и последний лист "итоги".
В листе "итоги" таблица с инвентаризационными номерами и количеством материалов в каждом. В листах "Лист 1",
"Лист 2", "Лист 3". "Лист n" содержатся точно такиеже таблицы как и в листе "итоги" только количество записей
в каждой по 20 строк.
Количество материалов в листе "итоги" вводится в ручную. А задача состоит в том что после запуска макроса макрос должен перебирать
таблицу итоги по порядку, искать соответствующий инвентаризационный номер в книге
(поиск производется во всей книге, кроме листа "итоги"), и если найдется вводить количество
в найденной таблице.
Прототип книги прилагается.
Вот мои попытки:
но только поиск тут пропочемуто проводится не по книге а по листу.
Также нужен диапозон поиска но как это все реализовать?
Если есть возможность, пожалуйста помогите с советами. Centuriy
Прототип книги прилагается.
Вот мои попытки:
но только поиск тут пропочемуто проводится не по книге а по листу.
Также нужен диапозон поиска но как это все реализовать?
Если есть возможность, пожалуйста помогите с советами. Автор - Centuriy
Дата добавления - 07.10.2013 в 08:59
Читайте также: