Поиск в файлах excel строки vba
Метод Find объекта Range предназначен для поиска ячейки и сведений о ней в заданном диапазоне по ее значению, формуле и примечанию. Чаще всего этот метод используется для поиска в таблице ячейки по слову, части слова или фразе, входящей в ее значение.
Синтаксис метода Range.Find
Expression . Find ( What , After , LookIn , LookAt , SearchOrder , SearchDirection , MatchCase , MatchByte , SearchFormat )Expression – это переменная или выражение, возвращающее объект Range, в котором будет осуществляться поиск.
В скобках перечислены параметры метода, среди них только What является обязательным.
Метод Range.Find возвращает объект Range, представляющий из себя первую ячейку, в которой найдена поисковая фраза (параметр What). Если совпадение не найдено, возвращается значение Nothing.
Если необходимо найти следующие ячейки, содержащие поисковую фразу, используется метод Range.FindNext.
Параметры метода Range.Find
- xlValues (-4163) – значения;
- xlComments (-4144) – примечания*;
- xlNotes (-4144) – примечания*;
- [xlFormulas (-4123) – формулы]**.
- xlWhole (1) – полное совпадение;
- xlPart (2) – частичное совпадение.
- xlByRows (1) – поиск по строкам;
- xlByColumns (2) – поиск по столбцам.
- xlNext (1) – поиск вперед;
- xlPrevious (2) – поиск назад.
- False (0) – поиск без учета регистра (по умолчанию);
- True (1) – поиск с учетом регистра.
- False (0) – двухбайтовый символ может соответствовать однобайтовому символу;
- True (1) – двухбайтовый символ должен соответствовать только двухбайтовому символу.
* Примечания имеют две константы с одним значением. Проверяется очень просто: MsgBox xlComments и MsgBox xlNotes .
** Тесты показали неработоспособность метода Range.Find с константой xlFormulas в моей версии VBA Excel.
В справке Microsoft тип данных всех параметров, кроме SearchDirection, указан как Variant.
Я пытаюсь написать VBA процедура, которая будет принимать строку, искать данную книгу Excel и возвращать мне все возможные совпадения.
в настоящее время у меня есть реализация, которая работает, но она очень медленная, поскольку это двойной цикл for. Конечно, встроенный Excel Find функция "оптимизирована", чтобы найти одно совпадение, но я хотел бы вернуть массив начальных совпадений, к которым я могу применить дальнейшие методы.
я опубликую некоторый псевдокод из того, что у меня уже есть
как уже говорилось ранее, этот двойной цикл for делает вещи работать очень медленно, поэтому я ищу, чтобы избавиться от этого, если это возможно. Есть предложения?
обновление
в то время как приведенные ниже Ответы улучшили бы мой метод, я закончил с чем-то немного другим, поскольку мне нужно было делать несколько запросов снова и снова.
вместо этого я решил перебрать все строки в моем документе и создать словарь, содержащий ключ для каждой уникальной строки. Значение, на которое это указывает, будет списком возможных совпадений, поэтому, когда я запрошу позже, я могу просто проверить, существует ли он, и если да, просто получить быстрый список совпадений.
в основном просто делает одну начальную развертку, чтобы сохранить все в управляемой структуре, а затем запросить эту структуру, которая может быть выполнена в O(1) времени
использование диапазона.Метод Find, как указано выше, наряду с циклом для каждого листа в книге, является самым быстрым способом сделать это. Следующее, например, находит строку "вопрос?"в каждом листе, и заменяет его строкой "ответил!".
осторожно при выполнении цикла поиска, что вы не попадаете в бесконечный цикл. Ссылка на первый найденный адрес ячейки и сравнение после каждого оператора" FindNext", чтобы убедиться, что он не вернулся к первой изначально найденной ячейке.
вы можете использовать диапазон.Найти метод:
Это даст вам первую ячейку, которая содержит строку поиска. Повторяя это с установкой аргумента "после" в следующую ячейку, вы получите все остальные вхождения, пока не вернетесь к первому вхождению.
Это, вероятно, будет намного быстрее.
вы можете прочитать данные в массив. Оттуда вы можете сделать матч в памяти, а не читать по одной ячейке за раз.
ниже код позволяет избежать создания бесконечного цикла. Предположим, что XYZ-это строка, которую мы ищем в книге.
основываясь на идее ответа B Hart, вот моя версия функции, которая ищет значение в диапазоне и возвращает все найденные диапазоны (ячейки):
для поиска значения во всей книге:
в своем сценарий, Я должен искать значение в столбце A и нужно выяснить совпадения в столбце B. Поэтому я создал цикл for, внутри него будет смотреть на весь столбец A и получить точное совпадение из столбца B.
Зачастую, при работе с данными в Excel приходится выборочно удалять или скрывать часть информации, чаще всего это строки либо столбцы, содержащие, либо не содержащие определенные слова, буквы, цифры, символы, либо их сочетания. Помогают в этом такие стандартные средства Excel, как поиск, фильтр и расширенный фильтр. Если этих инструментов для решения задачи недостаточно – на помощь приходит VBA.
Как найти заданные слова в строках Excel? Поиск слов, букв, цифр и символов в Excel
Простейшим способом поиска заданного слова, буквы, цифры, символа либо сочетания из них является стандартный поиск. Все параметры для поиска вводятся в диалоговом окне «Найти и заменить», которое можно вызвать из главного меню, либо при помощи сочетания горячих клавиш «Ctrl+f» (где f-первая буква английского слова find – найти). Кроме обычного поиска можно выполнять также поиск с заменой.
Использование фильтров для поиска заданного слова, буквы, цифры или символа в Excel
Для выбора строк, содержащих определенную пользователем информацию можно использовать фильтрацию. В Excel 2007 и выше, например, кроме обычного текстового фильтра, предусмотрен фильтр по цвету заливки ячеек и по цвету шрифта. Текстовый фильтр позволяет использовать такие условия как «равно…», «не равно…», «начинается с…», «заканчивается на…», «содержит…», «не содержит…». После того как все необходимые строки отфильтрованы, можно производить с ними любые действия, в том числе и удаление строк.
Как программно найти и удалить определенные строки в Excel, используя VBA-оператор Like?
По разным причинам стандартные средства Excel не всегда подходят для решения тех или иных задач. Ниже приведен программный код макроса, позволяющего находить в ячейках используемого диапазона определенный шаблоном текст и удалять всю строку активного рабочего листа, содержащую ячейку с заданным текстом. Искомый текст присваивается переменной "Shablon" при помощи специальных символов совпадения с образцом.
Для того, чтобы перенести этот программный код на свой компьютер, наведите курсор мыши на поле с программным кодом, нажмите на одну из двух кнопкок в правом верхнем углу этого поля, скопируйте программный код и вставьте его в модуль проекта на своем компьютере (подробнее о том, как сохранить программный код макроса).
В этом макросе для поиска необходимых фраз используется нечеткий поиск и VBA-оператор сравнения Like, позволяющий сравнивать строки с образцом. При сравнении строк этот оператор различает буквы верхнего и нижнего регистра и результат сравнения зависит от инструкции Option Compare.
Для решения подобных задач могут использоваться также VBA-функции Instr и Find.
Аналогичные действия выполняет надстройка для Excel, использование которой позволяет вводить искомый текст без специальных символов в диалоговом окне и задавать различные области поиска.
В Excel вы можете использовать функцию «Найти и заменить», чтобы найти определенное значение, но знаете ли вы, как выделить результаты поиска после поиска? В этой статье я расскажу о двух различных способах облегчения поиска и выделения результатов поиска в Excel.
Искать и выделять результаты поиска по коду VBA
В Excel есть код VBA, который может помочь вам найти значение и затем выделить результаты поиска.
1. Включите лист, который вы хотите найти, и выделите результаты, нажмите Alt + F11 ключи для открытия Microsoft Visual Basic для приложений окно.
2. Нажмите Вставить > Модульи вставьте приведенный ниже код в новый модуль.
VBA: выделение результатов поиска
3. Нажмите F5 нажмите клавишу, затем появится окно поиска, в котором вы можете ввести указанное вами значение.
4. Нажмите OK, совпадающие результаты были выделены цветом фона. Кроме того, появляется диалоговое окно с вопросом, отменить ли выделение. Нажмите OK чтобы отменить выделение и закрыть диалоговое окно, нажмите Отмена чтобы сохранить выделение и закрыть диалог.
Внимание:
1. Если подходящего значения не найдено, появится диалоговое окно с напоминанием.
2. Этот VBA работает для всего активного листа и нечувствителен к регистру.
Вы хотите повысить зарплату и много времени проводить с семьей?
Вкладка Office повышает эффективность работы Microsoft Office на 50% прямо сейчас
Невероятно, но работать с двумя или более документами проще и быстрее, чем над одним.
По сравнению с известными браузерами инструмент с вкладками в Office Tab более мощный и эффективный.
Сократите для вас сотни щелчков мыши и набора текста с клавиатуры каждый день, попрощайтесь с рукой мыши.
Если вы обычно работаете с несколькими документами, вкладка Office поможет вам сэкономить время.
30-дневная бесплатная пробная версия , кредитная карта не требуется.
Поиск и выделение результатов поиска с помощью условного форматирования
В Excel функция условного форматирования также может автоматически выделять результаты поиска.
Предположим, что данные и окно поиска отображаются, как показано на скриншоте ниже, теперь выполните следующие действия:
1. Выберите диапазон, в котором нужно выполнить поиск, и нажмите Главная > Условное форматирование > Новое правило.
2. в Новое правило форматирования диалоговое окно, выберите Используйте формулу, чтобы определить, какие ячейки следует форматировать. в Выберите тип правила раздел, затем перейдите в текстовое поле под Формат значений, где эта формула истинна, напишите = И ($ E $ 2 <> "", $ E $ 2 = A4) .
E2 - это ячейка, в которую вы поместите поисковое значение, а A4 - это первая ячейка в диапазоне, в котором вы выполняете поиск.
3. Нажмите Формат кнопку, перейдите к Формат ячеек диалога под Заполнять на вкладке выберите нужный цвет. Нажмите OK > OK чтобы закрыть диалоги.
С этого момента, когда вы вводите ключевое слово в ячейку E2, результаты поиска будут автоматически выделяться в указанном диапазоне.
Файл примера
Другие операции (статьи), связанные с форматированием Conditioanl
Подсчет / суммирование ячеек по цветам с условным форматированием в Excel
Теперь это руководство расскажет вам о некоторых удобных и простых методах быстрого подсчета или суммирования ячеек по цвету с условным форматированием в Excel.
Создать диаграмму с условным форматированием в Excel
Например, у вас есть таблица оценок для класса, и вы хотите создать диаграмму для цветных оценок в разных диапазонах. В этом руководстве представлен метод решения этой задачи.
Гистограмма с накоплением условного форматирования в Excel
В этом руководстве показано, как создать столбчатую диаграмму с условным форматированием, как показано на скриншоте ниже, шаг за шагом в Excel.
Условное форматирование строк или ячеек, если два столбца равны в Excel
В этой статье я представляю метод условного форматирования строк или ячеек, если два столбца равны в Excel.
Применить условное форматирование для каждой строки в Excel
Иногда может потребоваться применить условное форматирование для каждой строки. Кроме многократной установки одних и тех же правил для каждой строки, есть несколько уловок для решения этой задачи.
Читайте также: