Макрос поиска в excel: Поиск на листе Excel, примеры использования Find на VBA

Поиск на листе Excel, примеры использования Find на VBA

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

Поиск перебором значений

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

Sheets("Данные").Select
For y = 1 To Cells.SpecialCells(xlLastCell).Row
    If Cells(y, 1) = "123" Then
        Exit For
    End If
Next y
MsgBox "Нашел в строке: " + CStr(y)

Минусами этого так сказать «классического» способа являются: медленная работа и громоздкость. А плюсом является его гибкость, т.к. таким способом можно реализовать сколь угодно сложные варианты поиска с различными вычислениями и т. п.

Поиск функцией Find

Гораздо быстрее обычного перебора и при этом довольно гибкий. В простейшем случае, чтобы найти в колонке A ячейку, содержащую «123» достаточно такого кода:

Sheets("Данные").Select
Set fcell = Columns("A:A").Find("123")
If Not fcell Is Nothing Then
    MsgBox "Нашел в строке: " + CStr(fcell.Row)
End If

Вкратце опишу что делают строчки данного кода:

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»

With Worksheets(1).Range("A1:A50")
  Set c = .Find("asd", LookIn:=xlValues)
  Do While Not c Is Nothing
    c.Value = "qwe"
    Set c = .FindNext(c)
  Loop
End With

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

Пример 2: Правильный поиск значения с использованием FindNext, не приводящий к зацикливанию.

With Worksheets(1).Range("A1:A50")
  Set c = .Find("asd", lookin:=xlValues)
  If Not c Is Nothing Then
    firstResult = c.Address
    Do
      c.Font.Bold = True
      Set c = .FindNext(c)
      If c Is Nothing Then Exit Do
    Loop While c.Address <> firstResult
  End If
End With

В ниже следующем примере используется другой вариант продолжения поиска — с помощью той же функции Find с параметром After. Когда найдена очередная ячейка, следующий поиск будет осуществляться уже после нее. Однако, как и с FindNext, когда будет достигнут конец диапазона, Find продолжит поиск с его начала, поэтому, чтобы не произошло зацикливания, необходимо проверять совпадение с первым результатом поиска.

Пример 3: Продолжение поиска с использованием Find с параметром After.

With Worksheets(1).Range("A1:A50")
  Set c = .Find("asd", lookin:=xlValues)
  If Not c Is Nothing Then
    firstResult = c.Address
    Do
      c.Font.Bold = True
      Set c = .Find("asd", After:=c, lookin:=xlValues)
      If c Is Nothing Then Exit Do
    Loop While c.Address <> firstResult
  End If
End With

Следующий пример демонстрирует применение SearchFormat для поиска по формату ячейки. Для указания формата необходимо задать свойство FindFormat.

Пример 4: Найти все ячейки с шрифтом «курсив» и поменять их формат на обычный (не «курсив»)

lLastRow = Cells.SpecialCells(xlLastCell).Row
lLastCol = Cells.SpecialCells(xlLastCell).Column
Application.FindFormat.Font.Italic = True
With Worksheets(1).Range(Cells(1, 1), Cells(lLastRow, lLastCol))
  Set c = .Find("", SearchFormat:=True)
  Do While Not c Is Nothing
    c.Font.Italic = False
    Set c = . Find("", After:=c, SearchFormat:=True)
  Loop
End With

Примечание: В данном примере намеренно не используется FindNext для поиска следующей ячейки, т.к. он не учитывает формат (статья об этом: https://support.microsoft.com/ru-ru/kb/282151)

Коротко опишу алгоритм поиска Примера 4. Первые две строки определяют последнюю строку (lLastRow) на листе и последний столбец (lLastCol). 3-я строка задает формат поиска, в данном случае, будем искать ячейки с шрифтом Italic. 4-я строка определяет область ячеек с которой будет работать программа (с ячейки A1 и до последней строки и последнего столбца). 5-я строка осуществляет поиск с использованием SearchFormat. 6-я строка — цикл пока результат поиска не будет пустым. 7-я строка — меняем шрифт на обычный (не курсив), 8-я строка продолжаем поиск после найденной ячейки.

Хочу обратить внимание на то, что в этом примере я не стал использовать «защиту от зацикливания», как в Примерах 2 и 3, т.к. шрифт меняется и после «прохождения» по всем ячейкам, больше не останется ни одной ячейки с курсивом.

Свойство FindFormat можно задавать разными способами, например, так:

With Application.FindFormat.Font 
  .Name = "Arial" 
  .FontStyle = "Regular" 
  .Size = 10 
End With

Следующий пример — применение функции Find для поиска последней ячейки с заполненными данными. Использованные в Примере 4 SpecialCells находит последнюю ячейку даже если она не содержит ничего, но отформатирована или в ней раньше были данные, но были удалены.

Пример 5: Найти последнюю колонку и столбец, заполненные данными

Set c = Worksheets(1).UsedRange.Find("*", SearchDirection:=xlPrevious)
If Not c Is Nothing Then
  lLastRow = c.Row: lLastCol = c.Column 
Else
  lLastRow = 1: lLastCol = 1
End If
MsgBox "lLastRow=" & lLastRow & " lLastCol=" & lLastCol

В этом примере используется UsedRange, который так же как и SpecialCells возвращает все используемые ячейки, в т. ч. и те, что были использованы ранее, а сейчас пустые. Функция Find ищет ячейку с любым значением с конца диапазона.

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

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

With Worksheets(1).Cells
  Set c = .Find("т??т*", LookIn:=xlValues, LookAt:=xlWhole)
  If Not c Is Nothing Then
    firstResult = c.Address
    Do
      c.Font.Color = RGB(255, 0, 0)
      Set c = .FindNext(c)
      If c Is Nothing Then Exit Do
    Loop While c.Address <> firstResult
  End If
End With

Для поиска функцией Find по маске (шаблону) можно применять символы:

* — для обозначения любого количества любых символов;

? — для обозначения одного любого символа;

~ — для обозначения символов *, ? и ~. (т.е. чтобы искать в тексте вопросительный знак, нужно написать ~?, чтобы искать именно звездочку (*), нужно написать ~* и наконец, чтобы найти в тексте тильду, необходимо написать ~~)

Поиск даты с помощью Find

Если необходимо найти текущую дату или какую-то другую дату на листе Excel или в диапазоне с помощью Find, необходимо учитывать несколько нюансов:

• Тип данных Date в VBA представляется в виде #[месяц]/[день]/[год]#, соответственно, если необходимо найти фиксированную дату, например, 01 марта 2018 года, необходимо искать #3/1/2018#, а не «01.03.2018»
• В зависимости от формата ячеек, дата может выглядеть по-разному, поэтому, чтобы искать дату независимо от формата, поиск нужно делать не в значениях, а в формулах, т.е. использовать LookIn:=xlFormulas

Приведу несколько примеров поиска даты.

Пример 7: Найти текущую дату на листе независимо от формата отображения даты.

d = Date
Set c = Cells. Find(d, LookIn:=xlFormulas, LookAt:=xlWhole)
If Not c Is Nothing Then
  MsgBox "Нашел"
Else
  MsgBox "Не нашел"
End If

Пример 8: Найти 1 марта 2018 г.

d = #3/1/2018#
Set c = Cells.Find(d, LookIn:=xlFormulas, LookAt:=xlWhole)
If Not c Is Nothing Then
  MsgBox "Нашел"
Else
  MsgBox "Не нашел"
End If

Искать часть даты — сложнее. Например, чтобы найти все ячейки, где месяц «март», недостаточно искать «03» или «3». Не работает с датами так же и поиск по шаблону. Единственный вариант, который я нашел — это выбрать формат в котором месяц прописью для ячеек с датами и искать слово «март» в xlValues.

Тем не менее, можно найти, например, 1 марта независимо от года.

Пример 9: Найти 1 марта любого года.

d = #3/1/1900#
Set c = Cells.Find(Format(d, "m\/d\/"), LookIn:=xlFormulas, LookAt:=xlPart)
If Not c Is Nothing Then
  MsgBox "Нашел"
Else
  MsgBox "Не нашел"
End If

Макрос для поиска ячеек в Excel с выпадающим списком и условным форматированием

В данном примере написаны исходные коды VBA-макросов для проверки ячеек на наличие выпадающих списков (или любых средств, созданных инструментом «проверка данных»). А также код макроса для проверки и получения доступа к ячейкам, которые содержат условное форматирование.

Макрос поиска ячейки с выпадающим списком

Допустим у нас имеется таблица Excel сформированная в результате экспорта журнала фактур из истории взаиморасчетов с клиентами фирмы, как показано ниже на рисунке:

Нам необходимо найти все выпадающие списки или определить каким ячейкам присвоена проверка вводимых данных, создана инструментом: «ДАННЫЕ»-«Работа с данными»-«Проверка данных».

В программе Excel по умолчанию есть встроенный инструмент для поиска ячеек с проверкой правил вводимых значений. Чтобы его использовать следует выбрать: ГЛАВНАЯ»-«Редактирование»-«Найти и выделить»-«Выделить группу ячеек». В появившемся диалоговом окне следует отметить опцию «проверка данных» и нажать на кнопку ОК.

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

Откройте редактор макросов Visual Basic (ALT+F11) и создайте новый модуль выбрав в редакторе инструмент: «Insert»-«Module». В созданный модуль введите VBA код макроса:

Sub ProvDan()
  Dim i As Long
  Dim diapaz1 As Range
  Dim diapaz2 As Range
Set diapaz1 = Application.Range(ActiveSheet.Range("A1"), _
ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell))
On Error Resume Next
   For i = 1 To diapaz1.Count
     If IsError(diapaz1(i).Validation.Type) Then
       Else
       If diapaz2 Is Nothing Then
       Set diapaz2 = diapaz1(i)
       Else
       Set diapaz2 = Application.Union(diapaz2, diapaz1(i))
       End If
     End If
   Next
On Error GoTo 0
If diapaz2 Is Nothing Then
MsgBox "Ненайдено ниодной ячейки!"
Else
  diapaz2.Select
  MsgBox "Найдено: " & diapaz2.Count & " ячеек!"
End If
End Sub


Если нужно выделить все ячейки в таблице, которые содержат проверку вводимых значений включенной инструментом «Проверка данных», тогда выберите инструмент: «РАЗРАБОТЧИК»-«Код»-«Макросы»-«ProvDan»-«Выполнить».

В результате выделились 14 ячеек в столбце G, для которых включена проверка данных в стиле выпадающего списка:

В данном коде мы сначала выделяем все ячейки на текущем листе с помощью инструкции:

Cells.Select

После, определяем диапазон ячеек на листе, который использует исходная таблица и с которыми будет работать наш макрос. Чтобы определить диапазон таблицы на рабочем листе Excel, мы могли бы использовать свойство UsedRange при создании экземпляра объекта Range в переменной diapaz1. Данное свойство охватывает только непустые ячейки, а это может быть даже несмежный диапазон. Но таблица может содержать пустые ячейки для, которых присвоена проверка ввода значений. Чтобы наш макрос не игнорировал пустые ячейки внутри таблицы мы определяем смежный (неразрывный) диапазон, который начинается с ячейки A1 и заканчивается последней используемой ячейкой на рабочем листе Excel.

Set diapaz1 = Application.Range(ActiveSheet.Range(«A1»), ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell))

Последняя ячейка находиться наиболее отдаленно от ячейки A1 (в данном примере – это G15) и была использована на листе (это обязательное условие). При чем использована в прямом смысле, она может даже не содержать значения, но иметь измененный числовой формат, другой цвет фона, другие границы, объединение и т.п. Чтобы найти последнюю используемую ячейку на листе стандартными средствами Excel, выберите инструмент: «ГЛАВНАЯ»-«Редактирование»-«Найти и выделить»-«Выделить группу ячеек».

В появившемся окне следует выбрать опцию «последнюю ячейку». А после просто нажать ОК. Курсор клавиатуры сразу переместиться на последнюю используемую ячейку на рабочем листе Excel.

Можно даже при создании экземпляра объекта Range в переменной diapaz1 использовать диапазон целого листа. Для этого просто замените выше описанную инструкцию на:

Set diapaz1 = Selection

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

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

On Error Resume Next

Но после конца цикла обработка ошибок снова включается.

On Error GoTo 0

Внутри цикла проверяться по отдельности все ячейки на наличие включенной проверки вводимых значений инструментом «Проверка данных». Если ячейка содержит проверку вводимых значений?

If IsError(diapaz1(i).Validation.Type) Then

Тогда она присоединяется к диапазону ячеек, находящихся в переменной diapaz2.

Set diapaz2 = Application.Union(diapaz2, diapaz1(i))

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

MsgBox «Найдено: » & diapaz2.Count & » ячеек!»



Макрос поиска ячейки с условным форматированием

Некоторые ячейки в исходной таблице содержат условное форматирование, а пользователю Excel необходимо их все найти и выделить. Очень часто нельзя визуально определить присвоено ли ячейке условное форматирование или нет. Чтобы найти и выделить ячейки с условным форматированием в Excel можно воспользоваться встроенным инструментом. Просто необходимо выбрать опцию в меню: «ГЛАВНАЯ»-«Редактирование»-«Найти и выделить»-«Выделить группу ячеек».

В появившемся окне отмечаем опцию «условные форматы» и нажимаем кнопку ОК.

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

Снова откройте редактор Visual Basic (ALT+F11) и в уже созданный модуль добавьте новый код для нового макроса:

Sub ProvFormat()
  Dim i As Long
  Dim diapaz1 As Range
  Dim diapaz2 As Range
Set diapaz1 = Application.Range(ActiveSheet.Range("A1"), _
ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell))
   For i = 1 To diapaz1.Count
     If diapaz1(i).FormatConditions.Count > 0 Then
       If diapaz2 Is Nothing Then
       Set diapaz2 = diapaz1(i)
       Else
       Set diapaz2 = Application.Union(diapaz2, diapaz1(i))
       End If
     End If
   Next
If diapaz2 Is Nothing Then
MsgBox "Ненайдено ниодной ячейки!"
Else
  diapaz2.Select
  MsgBox "Найдено: " & diapaz2.Count & " ячеек!"
End If
End Sub


Теперь чтобы проверить, посчитать и выделить все ячейки для которых было применено любое условное форматирование запустите макрос: «РАЗРАБОТЧИК»-«Код»-«Макросы»-«ProvFormat»-«Выполнить».

В результате будут выделены все соответствующие ячейки и отобразиться сообщение с информацией о их количестве:

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

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

If diapaz1(i).FormatConditions.Count > 0 Then

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

Программы и макросы Excel в категории Поиск в Excel

Как найти текст, слова и символы в Excel? Удаление строк по шаблону

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

Как найти заданные слова в строках Excel? Поиск слов, букв, цифр и символов в Excel

Простейшим способом поиска заданного слова, буквы, цифры, символа либо сочетания из них является стандартный поиск. Все параметры для поиска вводятся в диалоговом окне «Найти и заменить», которое можно вызвать из главного меню, либо при помощи сочетания горячих клавиш «Ctrl+f» (где f-первая буква английского слова find – найти). Кроме обычного поиска можно выполнять также поиск с заменой.

Использование фильтров для поиска заданного слова, буквы, цифры или символа в Excel

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

Как программно найти и удалить определенные строки в Excel, используя VBA-оператор Like?

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

Sub Udalenie_Strok_Po_Shablonu()
    Dim r As Long, FirstRow As Long, LastRow As Long
    Dim Region As Range, iRow As Range, Cell As Range
    Dim Shablon As String
 
    Shablon = "здесь вводится искомый текст"
 
    Set Region = ActiveSheet.UsedRange
    FirstRow = Region.Row
    LastRow = Region.Row - 1 + Region.Rows.Count 
        For r = LastRow To FirstRow Step -1
        Set iRow = Region.Rows(r - FirstRow + 1)
            For Each Cell In iRow.Cells
                If Cell Like Shablon Then
                    Rows(r).Delete
                End If
            Next Cell
        Next r
End Sub

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

В этом макросе для поиска необходимых фраз используется нечеткий поиск и VBA-оператор сравнения Like, позволяющий сравнивать строки с образцом. При сравнении строк этот оператор различает буквы верхнего и нижнего регистра и результат сравнения зависит от инструкции Option Compare.

Для решения подобных задач могут использоваться также VBA-функции Instr и Find.

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

надстройка для выборочного удаления ячеек, строк и столбцов по условию

Другие материалы по теме:

Как выполнить поиск значений в программе «Excel»

Как выполнить поиск значений в программе «Excel» .

Поиск в Эксель

Далее описаны несколько вариантов поиска и фильтрации данных в таблице «Эксель».

1. Классический поиск «MS Office».

2. Условное форматирование (выделение нужных ячеек цветом)

3. Настройка фильтров по одному или нескольким значениям.

4. Фрагмент макроса для перебора ячеек в диапазоне и поиска нужного значения.

1) Классический поиск (обыкновенный).

Вызвать панель (меню) поиска можно сочетанием горячих клавиш ctrl+F. (Легко запомнить: F- Found).

Окно поиска состоит из поля, в которое вводится искомый фрагмент текста или искомое число, вкладки с дополнительными настройками («Параметры») и кнопки «Найти».

Классический поиск в Excel

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

Условное форматирование для искомых ячеек.

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

Такой поиск реализуется через опцию условное форматирование.

Для форматирования ячеек следует выделить диапазон ячеек, которых нужно найти слово. Далее на вкладке «Главная» нажать кнопку  «Условное форматирование».

Настройка форматирования для выделения искомых слов

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

В открывшемся окне указать искомое слово и цвет заливки ячейки. Нажать «ОК». Все ячейки, содержащие нужное слово будут окрашены.

3) Третий способ поиска слов в таблице «Excel» — это использование фильтров.

Фильтр устанавливается во вкладке «Данные» или сочетанием клавиш  ctrl+shift+L.
Настройка фильтра для поиска слов

Кликнув по треугольнику фильтра можно в контекстном меню выбрать пункт «Текстовые фильтры»,  далее «содержит…» и указать искомое слово.

После нажатия кнопки «Ок» на Экране останутся только ячейки столбца, содержащие искомое слово.

4) Способ поиска номер четыре — это макрос VBA для поиска (перебора значений).

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

Sub Poisk()

‘ ruexcel.ru макрос проверки значений (поиска)

Dim keyword As String

keyword = «Искомое слово» ‘присвоить переменной искомое слово

On Error Resume Next ‘при ошибке пропустить

For Each cell In Selection ‘для всх ячеек в выделении (выделенном диапазоне)

If cell.Value = «» Then GoTo Line1 ‘если ячейка пустая перейти на «Line1″

If InStr(StrConv(cell.Value, vbLowerCase), keyword) > 0 Then cell.Interior.Color = vbRed ‘если в ячейке содержится слово окрасить ее в красный цвет (поиск)

Line1:

Next cell

End Sub

Метод Range.Find (Excel) | Microsoft Docs

• 
  08/14/2019
• Чтение занимает 3 мин

В этой статье

Находит определенные сведения в диапазоне.Finds specific information in a range.

Примечание

Хотите создавать решения, которые расширяют возможности Office на разнообразных платформах?Interested in developing solutions that extend the Office experience across multiple platforms? Ознакомьтесь с новой моделью надстроек Office.Check out the new Office Add-ins model. У надстроек Office мало места по сравнению с надстройками и решениями VSTO, которые можно создавать с помощью практически любой технологии веб-программирования, например HTML5, JavaScript, CSS3 и XML.Office Add-ins have a small footprint compared to VSTO Add-ins and solutions, and you can build them by using almost any web programming technology, such as HTML5, JavaScript, CSS3, and XML.

СинтаксисSyntax

выражение.Find (What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)expression.Find (What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)

выражение: переменная, представляющая объект Range.expression A variable that represents a Range object.

ПараметрыParameters

Возвращаемое значениеReturn value

Объект Range, представляющий первую ячейку, в которой обнаружены требуемые сведения.A Range object that represents the first cell where that information is found.

ПримечанияRemarks

Этот метод возвращает значение Nothing, если совпадения не найдены.This method returns Nothing if no match is found. Метод Find не влияет на выделенный фрагмент или активную ячейку.The Find method does not affect the selection or the active cell.

Параметры для аргументов LookIn, LookAt, SearchOrder и MatchByte сохраняются при каждом использовании этого метода.The settings for LookIn, LookAt, SearchOrder, and MatchByte are saved each time you use this method. Если вы не укажете значения этих аргументов при следующем вызове метода, будут использоваться сохраненные значения.If you do not specify values for these arguments the next time you call the method, the saved values are used. Установка этих аргументов изменяет параметры в диалоговом окне Найти, а изменение параметров в диалоговом окне Найти приводит к изменению сохраненных значений, которые используются, если опустить аргументы.Setting these arguments changes the settings in the Find dialog box, and changing the settings in the Find dialog box changes the saved values that are used if you omit the arguments. Чтобы избежать проблем, устанавливайте эти аргументы явным образом при каждом использовании этого метода.To avoid problems, set these arguments explicitly each time you use this method.

Для повторения поиска можно использовать методы FindNext и FindPrevious.You can use the FindNext and FindPrevious methods to repeat the search.

Когда поиск достигает конца указанного диапазона поиска, он возвращается в начало диапазона. When the search reaches the end of the specified search range, it wraps around to the beginning of the range. Чтобы остановить поиск при этом возврате, сохраните адрес первой найденной ячейки, а затем проверьте адрес каждой последующей найденной ячейки, сравнив его с этим сохраненным адресом.To stop a search when this wraparound occurs, save the address of the first found cell, and then test each successive found-cell address against this saved address.

Чтобы найти ячейки, отвечающие более сложным шаблонам, используйте инструкцию For Each…Next с оператором Like.To find cells that match more complicated patterns, use a For Each…Next statement with the Like operator. Например, следующий код выполняет поиск всех ячеек в диапазоне A1:C5, где используется шрифт, имя которого начинается с букв Cour.For example, the following code searches for all cells in the range A1:C5 that use a font whose name starts with the letters Cour. Когда Microsoft Excel находит соответствующее значение, шрифт изменяется на Times New Roman.When Microsoft Excel finds a match, it changes the font to Times New Roman.

For Each c In [A1:C5] If c.Font.Name Like "Cour*" Then c.Font.Name = "Times New Roman" End If Next`

ПримерыExamples

В этом примере показано, как найти все ячейки в диапазоне A1:A500 на листе 1, содержащие значение 2, и изменить значение ячейки на 5.This example finds all cells in the range A1:A500 in worksheet one that contain the value 2, and changes the entire cell value to 5. Таким образом, значения 1234 и 99299 содержали 2 и оба значения ячеек станут 5.That is, the values 1234 and 99299 both contain 2 and both cell values will become 5.

Sub FindValue()
    
    Dim c As Range
    Dim firstAddress As String

    With Worksheets(1).Range("A1:A500") 
        Set c = .Find(2, lookin:=xlValues) 
        If Not c Is Nothing Then 
            firstAddress = c.Address 
            Do 
                c.Value = 5 
                Set c = .FindNext(c) 
            Loop While Not c Is Nothing
        End If 
    End With
    
End Sub

В этом примере показано, как найти все ячейки в диапазоне A1:A500 на листе 1, содержащие подстроку «abc», и изменить ее на «xyz».This example finds all cells in the range A1:A500 on worksheet one that contain the substring «abc» and then replaces «abc» with «xyz».

Sub FindString()

    Dim c As Range
    Dim firstAddress As String

    With Worksheets(1).Range("A1:A500")
        Set c = .Find("abc", LookIn:=xlValues)
        If Not c Is Nothing Then
            firstAddress = c.Address
            Do
                c.Value = Replace(c.Value, "abc", "xyz")
                Set c = .FindNext(c)
            Loop While Not c Is Nothing
        End If
    End With

End Sub

Поддержка и обратная связьSupport and feedback

Есть вопросы или отзывы, касающиеся Office VBA или этой статьи?Have questions or feedback about Office VBA or this documentation? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.

Инструмент поиска Excel (макрос) — CodeProject

Введение

Этот макрос-скрипт используется для поиска определенных ячеек в документах Excel и копирования ячеек на отдельный лист.

Фон

Это мой первый проект на CodeProject и мой первый сценарий VBA, поэтому я открыт для предложений по улучшению моего кода! Я написал этот сценарий вместе с моими коллегами для быстрого поиска в наших документах и ​​получения данных.

Использование кода

Код предназначен для импорта в документ Excel.Файл имеет расширение .bas .

Обнаружение ошибок

 При ошибке Перейти к ErrorCatch 

Переменные

 Тусклый рабочий лист_Счет как целое число
Тусклый индекс как целое число
Dim columnCount As Integer
Dim inputRange As Range
Уменьшить размер ячейки как строка 

Начальные заголовки для определения столбцов

Таблицы ("Результаты поиска"). Диапазон ("A1: E1"). Значение = _
    Массив («Имя клиента», «Пример столбца»,
    «Пример столбца», «Пример столбца», «Пример столбца») 

Основной корпус

DW1962 ) для предотвращения мерцания экрана при обработке данных
Заявка.ScreenUpdating = False


WorkSheet_Count = ActiveWorkbook.Worksheets.Count


         Для index = 2 To WorkSheet_Count
           
            
            Установите inputRange = ActiveWorkbook.Worksheets (index) .Cells.Find ("Имя клиента:")
            
               
               Если не inputRange - это ничего, то
                   cellContent = ActiveSheet.Cells (inputRange.Row, _
                   (inputRange.Column + 1)). Адрес (False, False)
                   Таблицы (ActiveWorkbook.Worksheets (индекс).Имя) .Range (cellContent) .Copy Worksheets_
                   ("SearchResults"). Диапазон ("A" и индекс)
            
               
               Еще
                   Таблицы ("Результаты поиска"). Диапазон_
                   ("A" & индекс) .Value = "Нет информации"
            
               Конец, если
               Установите inputRange = Nothing

         
          Следующий индекс


    
                           DW1962 ), чтобы "исправить ширину столбцов"
    
    Для columnCount = 1 в ActiveSheet.UsedRange.Columns.Count
 
    Столбцы (columnCount) .EntireColumn.AutoFit
 
    Следующий столбецCount

    Выход из подводной лодки


ErrorCatch:
    MsgBox "При поиске ячейки произошла ошибка"

Концевой переводник 

Чтобы найти ячейку, вам нужно заменить строку в inputRange на все, что вы хотите найти:

 Установить inputRange = ActiveWorkbook.Worksheets (index) .Cells.Find_
(«Все, что вы хотите найти здесь») 

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

 листов (ActiveWorkbook.Рабочие листы (индекс). Имя). Диапазон (cellContent) .Copy Worksheets_
(«Какое угодно имя листа»). Диапазон («A» и индекс) 

Достопримечательности

Я много узнал о том, как программировать макросы, создавая этот проект, и надеюсь, что это поможет и некоторым из вас!

Также я получил петлитель рабочих листов из этой статьи Microsoft.

Как импортировать макрос

1. Откройте Excel и щелкните Developer > Visual Basic .
2. Чтобы импортировать макрос, щелкните Файл > Импорт файла , перейдите в папку, в которую вы экспортировали макрос, выберите файл и затем щелкните Открыть .
3. Сохраните модуль.
4. Создайте новый лист с именем « SearchResults » (любое имя листа, указанное в этой строке):

   Sheets (ActiveWorkbook.Worksheets (index) .Name) .Range (cellContent) .Copy Worksheets («Любое имя листа, которое вы хотите»). Range («A» и индекс)

5. Запустить макрос и собрать результаты.

История

• V1.0 выпущена 13.12.2016
• V1.1 выпущена 15.12.2016
  • Измененный код, реализация предложений
  • Обновленный скрипт в zip-файле

Как создать поле поиска с фильтрацией для данных Excel — Guru электронных таблиц

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

1. Перейдите на вкладку разработчика на ленте
2. Нажмите кнопку раскрывающегося списка Insert в группе Controls
3. Выберите Option Button Form Control (первая строка, последний значок)
4. Теперь ваша мышь должна выглядеть как перекрестие, и вам просто нужно щелкнуть где-нибудь в таблице, чтобы нарисовать кнопку выбора

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

One Pitfall

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

1. Вместо использования текстового поля управления формой вы можете использовать либо Cell , либо текстовое поле ActiveX для хранения текста поиска (у меня есть строки кода в следующих макросах VBA прокомментировал, что может справиться с этими ситуациями)
2. Назначьте сочетание клавиш для выполнения макроса, что избавляет от необходимости нажимать кнопку поиска

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

Именование ваших объектов

Ключ к правильной работе этого кода — правильная настройка ваших объектов (также называемых элементами управления формой). Сначала вам нужно будет определить имя текстового поля, содержащего ваш поисковый запрос. Для этого вам нужно выделить текстовое поле, а затем посмотреть на поле имени (которое находится слева от строки формул ).

Обычно вы увидите имя по умолчанию «Текстовое поле 1», однако вы можете изменить это имя на что-нибудь более значимое, например «UserSearch». Убедитесь, что вы нажали клавишу Enter сразу же после ввода нового имени, чтобы применить изменение имени! Если вы щелкните за пределами поля имени перед нажатием клавиши ВВОД, ваше текстовое поле вернется к своему прежнему имени.

Excel VBA Find — Полное руководство

«Я хорошо знаю, от чего бегу, но не знаю, что ищу» — Мишель де Монтень

Введение

Этот пост охватывает все, что вам нужно знать о функции VBA Find .В нем простым языком объясняется, как использовать Find. Он также содержит тонн примеров кода Find, которые вы можете использовать прямо сейчас.

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

Если вы хотите найти текст в строке , тогда вам нужны функции InStr и InStrRev.

Если вы хотите найти последнюю строку или столбец с данными, перейдите к Поиску последней ячейки, содержащей данные

Скачать исходный код

Что такое функция поиска VBA?

Функция поиска очень часто используется в VBA.Три наиболее важные вещи, которые нужно знать о Find:

1. Функция Find является членом Range.
2. Выполняет поиск в диапазоне ячеек с, содержащих заданное значение или формат.
3. По сути, это то же самое, что использовать Find Dialog на листе Excel.

Введение

Диалог поиска в Excel

Чтобы просмотреть диалоговое окно «Найти» в Excel, перейдите на ленту Home и щелкните Find & Select в разделе Editing .В появившемся меню выберите Найти (сочетание клавиш Ctrl + F)

Когда вы это сделаете, появится следующий диалог:

Функция поиска VBA использует большинство параметров, которые вы можете увидеть в этом диалоговом окне.

Как использовать параметры с функцией поиска

Чтобы использовать параметры, передайте их в качестве параметров функции поиска. Это похоже на то, как вы используете функции рабочего листа.Например, функция Sum имеет диапазон в качестве параметра. Это означает, что вы даете ему диапазон, когда используете его.

Поиск VBA использует параметры таким же образом. Вы должны отдать ему предмет, который ищете. Это первый параметр и он обязателен.

Остальные параметры необязательны. Если вы их не используете, Find будет использовать существующие настройки. Мы скоро узнаем об этом подробнее.

Эти параметры показаны в таблице в следующем разделе. В следующих разделах приведены примеры и подробное описание того, как использовать эти параметры.

Параметры поиска VBA

В следующих таблицах показаны все параметры поиска.

Важное примечание о параметрах поиска

Имейте в виду следующее, так как это может вызвать много неудобств при использовании Find.

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

Например, если вы установите для параметра LookIn значение xlComments , он будет искать значение только в комментариях.В следующий раз, когда вы запустите Find (из диалогового окна или из VBA), существующий параметр LookIn будет Комментарии.

Следующий код показывает пример этого

'Искать только в комментариях
Диапазон ("A1: A5"). Найдите "John", LookIn: = xlComments
'Будет искать комментарии, поскольку это существующая настройка
Диапазон ("A1: A5"). Найдите "Джона".

'Искать только по формулам
Диапазон ("A1: A5"). Найдите "John", LookIn: = xlFormulas
'Будет искать формулы, так как это существующая настройка
Диапазон ("A1: A5").Найдите "Джона"
 

Это применимо к параметрам LookIn , LookAt , SearchOrder и MatchByte .

Найти возвращаемое значение

Если элемент поиска найден, функция Find возвращает ячейку со значением. То есть он возвращает тип Range из одной ячейки.

Если элемент поиска не найден, функция Find возвращает объект, для которого установлено значение Nothing .

В следующих примерах вы увидите, как работать с возвращаемым значением.

Как сделать простой поиск

Начнем с простого примера поиска VBA. При использовании функции поиска

вам потребуются три вещи

1. Диапазон для поиска
2. Значение , которое вы ищете
3. Диапазон для хранения возвращенной ячейки

Возьмем следующие образцы данных

Мы будем искать текст «Jena» в ячейках с A1 по A5.

Следующий код выполняет поиск «Jena». Когда он находит «Jena», он помещает ячейку в переменную rgFound.

'Найдите имя Йена в диапазоне A1: A5
Dim rg найден как диапазон
Установите rgFound = Range ("A1: A5"). Найти ("Jena")

'Печатать адрес ячейки в немедленном окне (Ctrl + G)
Debug.Print rgFound.Address
 

Приведенный выше код показывает самый простой поиск, который вы можете выполнить. Если вы впервые используете функцию поиска VBA, я рекомендую вам попрактиковаться на таком простом примере.

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

Когда значение не найдено

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

Следующий код выдаст эту ошибку, если текст «John» не найден в диапазоне от A1 до A5

Установите rgFound = Range ("A1: A5").Найти ("Джон")

'Показывает ошибку, если Джон не был найден
Debug.Print rgFound.Address
 

Что нам нужно сделать, так это проверить возвращаемое значение, как показано в следующем коде

Установите rgFound = Range ("A1: A5"). Найти ("John")

Если rgFound - ничего, тогда
    Debug.Print «Имя не найдено».
Еще
    Debug.Print "Имя найдено в:" & rgFound.Address
Конец, если
 

Использование After с Find

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

Пример 1 Без После

Давайте посмотрим на следующий код.

Установить cell = Range ("A1: A6"). Найти ("Rachal")
 

Функция Find вернет ячейку A2, поскольку именно здесь находится первый «Rachal».

Пример 2 Использование после

В следующем примере мы используем after.Мы говорим VBA начать поиск «Rachal» после ячейки A2

.

Установить ячейку = Диапазон ("A1: A6"). Найти ("Rachal", После: = Диапазон ("A2"))
 

Это вернет ячейку A6

Пример 3 Обертывание вокруг

Если совпадение не найдено, поиск будет продолжаться. Это означает, что он вернется к началу диапазона.

В следующем примере мы ищем Drucilla. Начнем наш поиск после ячейки A2.Поиск будет искать от A3 до A6, а затем перейдет к A1.

Таким образом, следующий код вернет A1, поскольку нет текста «Drucilla» от A3 до A6:

Установить ячейку = Диапазон ("A1: A6"). Найти ("Drucilla", После: = Диапазон ("A2"))
 

Порядок поиска для этого примера был A4, A5, A6, A1.

Вы можете попробовать эти примеры на себе, загрузив рабочую книгу сверху сообщения.

Использование LookIn с Find

Использование LookIn позволяет выполнять поиск в значениях, формулах или комментариях.

Важное примечание: Если в ячейке есть только текст, этот текст считается формулой И значением. См. Подробную информацию в таблице ниже

Мы собираемся использовать следующие образцы данных.

A2 Содержит только «Apple» как значение
A3 Содержит только «Apple» как формулу
A4 Содержит только «Apple» в комментарии

Приведенный ниже код выполняет поиск «Apple» в различных типах: значение, формула, цепочный комментарий и примечание.

Чтобы увидеть рабочий пример этого кода, вы можете скачать исходный код сверху этого сообщения.

'Поиск по значению, формуле, цепочному комментарию и примечанию.
https: // excelmacromastery.com / excel-vba-find /
Sub UseLookIn ()

    'Находит A2
    Dim rg найден как диапазон
    Установите rgFound = shLookin.Range ("A1: A5"). Найти ("Apple", LookIn: = xlValues)
    Debug.Print "Найдено" Apple "как значение в:" & rgFound.Address

    Находит A3
    Установите rgFound = shLookin.Range ("A1: A5"). Найти ("Apple", LookIn: = xlFormulas)
    Debug.Print "Найдено" Apple "в виде формулы в:" & rgFound.Address

    Находки A4
    Установите rgFound = shLookin.Range ("A1: A5").Найти («Яблоко», LookIn: = xlCommentsThreaded)
    Debug.Print "Найдено" Apple "в виде комментария, вложенного в:" & rgFound.Address
    
    Находит A5
    Установите rgFound = shLookin.Range ("A1: A5"). Найти ("Apple", LookIn: = xlNotes)
    Debug.Print "Найдено" Apple "как примечание в:" & rgFound.Address

Конец подписки
 

Важное примечание , что я использовал xlCommentsThreaded для третьего, поскольку цепочки комментариев используются в Office 365. Если вы используете старую версию, в которой нет цепочек комментариев, используйте xlComments .

Использование LookAt с функцией поиска

Использование функции LookAt довольно просто.

1. xlWhole означает, что значение поиска должно соответствовать всему содержимому ячейки.
2. xlPart означает, что значение поиска должно соответствовать только части ячейки.

В следующем примере «Apple» является частью содержимого ячейки в ячейке A2, а это полное содержимое ячейки A3.

Первая находка в следующем коде находит «Apple» в A2.Вторая находка ищет полное совпадение, поэтому находит A3.

https://excelmacromastery.com/
Sub UseLookAt ()

    Затемнить ячейку как диапазон

    'Находит A2
    Установите cell = Range ("A1: A3"). Найти ("Apple", Lookat: = xlPart)
    Отладка. Печать ячейки. Адрес

    Находит A3
    Установить ячейку = Диапазон ("A1: A3"). Найти ("Яблоко", Lookat: = xlWhole)
    Отладка. Печать ячейки. Адрес

Конец подписки
 

Вы можете попробовать эти примеры на себе, загрузив рабочую книгу сверху сообщения.

Использование SearchOrder с функцией поиска

Параметр SearchOrder позволяет нам искать по строке или столбцу. В следующих примерах данных у нас есть два вхождения текста «Elli».

Если мы будем искать по строке, то сначала найдем «Elli» в B2. Это потому, что мы ищем в первой строке порядка, затем в строке 2 и т. Д.

Если мы будем искать по столбцу, мы сначала найдем «Elli» в A5. Это потому, что мы ищем в столбце заказа A, столбце B и т. Д.

В следующем коде показан пример использования SearchOrder с этими образцами данных

https://excelmacromastery.com/
Sub UseSearchOrder ()

    Затемнить ячейку как диапазон

    'Находит B2
    Установить ячейку = Диапазон ("A1: B6"). Найти ("Elli", SearchOrder: = xlRows)
    Отладка. Печать ячейки. Адрес

    Находит A5
    Установить ячейку = Диапазон ("A1: B6"). Найти ("Elli", SearchOrder: = xlColumns)
    Отладка. Печать ячейки. Адрес

Конец подписки
 

Использование SearchDirection с функцией поиска

SearchDirection позволяет искать вперед или назад.Итак, представьте, что у вас есть диапазон A1: A7. Поиск с использованием xlNext будет идти в порядке

A1, A2, A3, A4, A5, A6, A7

Поиск с использованием xl Предыдущий будет идти в порядке

A7, A6, A5, A4, A3, A2, A1

Использование xlNext с образцами данных вернет A2, так как здесь будет найдено первое совпадение. Использование xlPrevious вернет A6.

ПРИМЕЧАНИЕ: подчеркивание позволяет разбить строку
https: // excelmacromastery.com /
Sub UseSearchDirection ()

    Затемнить ячейку как диапазон

    'Находит A2
    Установить cell = shData.Range ("A1: A7") _
        .Find ("Элли", SearchDirection: = xlNext)
    Отладка. Печать ячейки. Адрес

    Находит A6
    Установить cell = shData.Range ("A1: A7") _
        .Find ("Элли", SearchDirection: = xlPrevious)
    Отладка. Печать ячейки. Адрес

Конец подписки
 

Использование xlPrevious с After

Если вы используете параметр «После» с xlPrevious, то он будет запускаться до из ячейки «После».Итак, если мы установим ячейку после как A6, то порядок поиска будет

А5, А4, А3, А2, А1, А7, А6.

Следующий код показывает пример этого

https://excelmacromastery.com/
Sub UseSearchDirectionAfter ()

    Затемнить ячейку как диапазон

    'Находит A2
    Установить cell = shData.Range ("A1: A7"). Найти ("Elli" _
            , После: = Диапазон ("A6"), SearchDirection: = xlPrevious)
    Отладка. Печать ячейки. Адрес

    Находит A6
    Установите cell = shData.Диапазон ("A1: A7"). Найти ("Elli" _
            , После: = Диапазон ("A7"), SearchDirection: = xlPrevious)
    Отладка. Печать ячейки. Адрес

Конец подписки

 

Использование MatchCase с Find

Параметр MatchCase используется для определения, имеет ли значение регистр букв при поиске. Может быть установлено значение True или False.

• Верно — регистр букв должен соответствовать
• False — регистр букв не имеет значения

В следующем примере списка есть две записи для «Elli».У второго строчная буква e

В следующих примерах кода показан результат установки MatchCase значений True и False

https://excelmacromastery.com/
Sub UseMatchCase ()

    Затемнить ячейку как диапазон

    'Находит A2
    Установить ячейку = Диапазон ("A1: B6"). Найти ("elli", MatchCase: = False)
    Отладка. Печать ячейки. Адрес

    Находит A6
    Установить ячейку = Диапазон ("A1: B6"). Найти ("elli", MatchCase: = True)
    Отлаживать.Распечатать ячейку. Адрес

Конец подписки
 

Использование MatchByte с Find

Параметр MatchByte используется для языков с двухбайтовым набором символов. Это такие языки, как китайский / японский / корейский.

Если вы их не используете, этот параметр не имеет значения. Используются они следующим образом

• Истина означает соответствие только двухбайтовых символов двухбайтовым символам.
• False означает, что двухбайтовые символы могут соответствовать одно- или двухбайтовым символам.

Использование WildCard

Мы можем использовать символ звездочки (*) как подстановочный знак при поиске текста. Звездочка представляет один или несколько символов.
Например,
«T *» найдет любое слово, начинающееся с T.
«Кому *» найдет любое слово, которое начинается с To.
«* y» найдет любое слово, которое заканчивается на y.
«* ey» найдет любое слово, оканчивающееся на ey.

В приведенном ниже коде показаны примеры использования подстановочного знака на основе этих данных:

'Примеры использования подстановочного знака
https: // excelmacromastery.com / excel-vba-find /
Подстановочный знак ()

    Dim rg найден как диапазон
    
    'Находит Тома в A2
    Установите rgFound = shWildCard.Range ("A1: A6"). Найти ("T *")
    Debug.Print rgFound.Value & "был найден в ячейке" & rgFound.Address

    Находит Тима в A5
    Установите rgFound = shWildCard.Range ("A1: A6"). Найти ("Ti *")
    Debug.Print rgFound.Value & "был найден в ячейке" & rgFound.Address
    
    Находит Томми в A4
    Установите rgFound = shWildCard.Диапазон ("A1: A6"). Найти ("* my")
    Debug.Print rgFound.Value & "был найден в ячейке" & rgFound.Address
    
    'Находит Эллен в A3
    Установите rgFound = shWildCard.Range ("A1: A6"). Найти ("* len *")
    Debug.Print rgFound.Value & "был найден в ячейке" & rgFound.Address
    
    'Находит Хелен в A6
    Установите rgFound = shWildCard.Range ("A1: A6"). Найти ("* elen *")
    Debug.Print rgFound.Value & "был найден в ячейке" & rgFound.Address
    
Конец подписки
 

Использование SearchFormat с функцией поиска

Формат поиска немного отличается от других параметров.Это позволяет вам искать формат ячейки, такой как тип шрифта или цвет ячейки.

Сначала необходимо установить формат с помощью свойства Application.FindFormat . Затем вы устанавливаете SearchFormat на True для поиска этого формата.

В следующем примере данных у нас отформатированы две ячейки. В ячейке A5 установлен полужирный шрифт, а в ячейке A6 — красный цвет заливки.

Следующий код выполняет поиск ячейки, выделенной жирным шрифтом:

'Найдите ячейку, выделенную жирным шрифтом
https: // excelmacromastery.com / excel-vba-find /
Sub UseSearchFormat ()

    Dim findText As String
    findText = "Элли"

    'Очистить предыдущие форматы и установить новый формат
    Application.FindFormat.Clear
    Application.FindFormat.Font.Bold = True

    'Находит A2
    Dim rg найден как диапазон
    Установите rgFound = Range ("A1: A6"). Найти (findText, SearchFormat: = False)
    Debug.Print "Found" & findText & "'в ячейке:" & rgFound.Address

    Находит A5
    Установите rgFound = Range ("A1: A6").Найти (findText, SearchFormat: = True)
    Debug.Print "Found" & findText & "'в ячейке:" & rgFound.Address
    
    Application.FindFormat.Clear

Конец подписки
 

Использование подстановочных знаков с форматом

Вы можете искать ячейку только на основе формата. Другими словами, значение в ячейке игнорируется при поиске. Вы делаете это, помещая «*» в строку поиска.

Следующий код выполняет поиск отформатированной ячейки — цвет ячейки в этом примере установлен на красный.Не имеет значения содержимое ячейки:

'Найдите отформатированную ячейку - содержимое не имеет значения
Excel VBA Find – A Complete Guide

Sub UseSearchFormatWild ()
    
    'Очистить предыдущие форматы и установить новый формат
    Application.FindFormat.Clear
    Application.FindFormat.Interior.Color = rgbRed

    'Находит A2, поскольку он игнорирует формат и находит первую ячейку с любым содержимым
    Dim rg найден как диапазон
    Установите rgFound = shSearchFormat.Диапазон ("A1: B6"). Найти ("*", SearchFormat: = False)
    Debug.Print «Найден формат в ячейке:» & rgFound.Address

    'Находит A5, поскольку это первая ячейка с форматом, установленным на красный цвет внутренней части
    Установите rgFound = shSearchFormat.Range ("A1: B6"). Найти ("*", SearchFormat: = True)
    Debug.Print "Найден формат в ячейке:" & rgFound.Address
    
    Application.FindFormat.Clear

Конец подписки
 

Важно — Формат очистки

Когда вы устанавливаете атрибуты FindFormat, они остаются на месте до тех пор, пока вы не установите их снова.Это то, чего стоит остерегаться.

Например, представьте, что вы установили полужирный шрифт и затем использовали команду «Найти». Затем вы устанавливаете размер шрифта 12 и снова используете команду «Найти». Поиск будет искать ячейки, в которых шрифт выделен жирным шрифтом И размером 12.

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

Application.FindFormat.Clear
 

Как видите, мы использовали это во втором примере SearchFormat выше.

Множественный поиск

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

.FindNext выполняет поиск на основе настроек, которые мы использовали в Find. В следующем коде показан простой пример поиска первого и второго вхождений текста «Elli».

https://excelmacromastery.com/
Sub SearchNext ()

    Затемнить ячейку как диапазон
    'Сначала найти - A2
    Установить ячейку = Диапазон ("A1: A9"). Найти ("Elli")
    Debug.Print «Найдено:» & cell.Address

    'Найди вторую - А5
    Установить ячейку = диапазон ("A1: A9"). FindNext (ячейка)
    Debug.Print «Найдено:» & cell.Address

Конец подписки
 

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

Используем Find , чтобы получить первый предмет. Если мы находим элемент, мы используем цикл Do Loop с .FindNext , чтобы найти остальные вхождения.

FindNext завершится. То есть после того, как он найдет A9, он продолжит поиск с A1. Поэтому мы сохраняем адрес первой найденной ячейки. Когда FindNext снова возвращает эту ячейку, мы знаем, что нашли все элементы.

Следующий код найдет все вхождения Elli

https: // excelmacromastery.com /
Sub MultipleSearch ()

    'Получить имя для поиска
    Тусклое имя как строка: name = "Elli"

    'Получить диапазон поиска
    Dim rgSearch As Range
    Установите rgSearch = Range ("A1: A9")

    Затемнить ячейку как диапазон
    Установите cell = rgSearch.Find (имя)

    'Если не найден, то выйти
    Если ячейка ничто, тогда
        Debug.Print «Не найдено»
        Выход из подводной лодки
    Конец, если

    'Сохранить адрес первой ячейки
    Dim firstCellAddress как строка
    firstCellAddress = ячейка.Адрес

    'Найти все ячейки, содержащие Элли
    Делать
        Debug.Print «Найдено:» & cell.Address
        Установите cell = rgSearch.FindNext (cell)
    Цикл пока firstCellAddress cell.Address

Конец подписки
 

Результатом этого кода будет
Найдено: $ A $ 2
Найдено: $ A $ 5
Найдено: $ A $ 8

Поиск последней ячейки, содержащей данные

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

'Найдите последнюю строку с данными в столбце A
LastRow = Ячейки (Rows.Count, 1) .End (xlUp) .Row

'Найдите последнюю строку с данными в столбце C
LastRow = Ячейки (Rows.Count, 3) .End (xlUp) .Row
 

Чтобы найти последний столбец с данными, мы используем аналогичный код

'Найти последний столбец с данными в строке 1
lLastCol = Cells (1, Columns.Count) .End (xlToLeft) .Column

'Найдите последний столбец с данными в строке 3
lLastCol = Cells (3, Columns.Count) .End (xlToLeft) .Column
 

Поиск клеток с образцами

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

Например, чтобы найти все имена, начинающиеся с E , вы можете использовать следующий код

'Выведите все имена, начинающиеся с буквы E
https: // excelmacromastery.com /
Sub PatternMatch ()

    Затемнить ячейку как диапазон
    'Пройдите каждую ячейку в диапазоне
    Для каждой ячейки в диапазоне ("A1: A20")
        'Проверить узор
        Если ячейка Like "[E] *" Тогда
            Отладка. Печать ячейки
        Конец, если
    следующий

Конец подписки
 

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

Чтобы увидеть реальный пример использования сопоставления с образцом, ознакомьтесь с Примером 3: Проверка допустимости имени файла.

Альтернатива использованию VBA Find

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

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

https://excelmacromastery.com/
Sub UseArrayToCount ()

    Dim arr как вариант
    'читать диапазон ячеек в массив
    arr = Sheet2.Диапазон ("A1: B25"). Значение

    Dim name As Variant, cnt As Long
    'Пройдите через массив
    Для каждого имени в обр.
        Обнаружен счет в имени "Луч"
        Если name = "Ray" Тогда
            cnt = cnt + 1
        Конец, если
    Следующее имя

    Debug.Print "Число вхождений:" & cnt

Конец подписки
 

Если вы хотите узнать больше о массивах, ознакомьтесь с публикацией Полное руководство по использованию массивов в Excel VBA.

Найти и заменить

Для поиска и замены вы можете использовать функцию Replace . Это очень похоже на использование функции поиска.

Функция замены выходит за рамки этого поста, хотя многое из того, что вы здесь читаете, можно использовать с ней. Вы можете увидеть подробности в Microsoft — VBA Replace Function

Что дальше?

Бесплатное руководство по VBA Если вы новичок в VBA или хотите отточить существующие навыки VBA, почему бы не попробовать The Ultimate VBA Tutorial.

Связанное обучение: Получите полный доступ к обучающим вебинарам по Excel VBA и всем учебным материалам.

( ПРИМЕЧАНИЕ: Планируете создание приложения VBA или управление им? Узнайте, как создать 10 приложений Excel VBA с нуля.)

5 ресурсов для макросов Excel для автоматизации электронных таблиц

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

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

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

Для всего есть субреддит, включая Microsoft Excel.Пользователи Reddit обсуждают все аспекты программного обеспечения, а макросы — популярная тема для разговоров. Если вы ищете макрос определенного типа, лучше всего использовать инструменты поиска на сайте. Однако, если вы просто хотите увидеть лучших из лучших, ознакомьтесь с самым крутым макро-соревнованием октября 2016 года.

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

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

Многие пользователи Excel полагаются на Excel Forum за помощью в решении различных проектов и проблем.На сайте есть форум, посвященный программированию, VBA и макросам, и это отличное место для поиска новых макросов, созданных другими пользователями.

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

Если вы не ищете что-то конкретное, лучший способ перемещаться по сайту — это сортировать темы по количеству просмотров.Таким образом, вы увидите, какие темы оказались наиболее популярными. Поскольку форум Excel существует уже более десяти лет, есть много контента, который можно просмотреть.

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

Единственный недостаток Mr.Excel в том, что он не так хорошо классифицируется, как Excel Forum. В результате нет разделения между сообщениями о макросах и более общими запросами. Однако вы можете легко обойти эту проблему, выполнив поиск по запросу «макрос», «VBA» или другой подобный термин.

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

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

Excel Easy — еще одно популярное место для людей, желающих улучшить свое понимание Excel.В нем есть множество руководств для новичков от экспертов, но если вы сосредотачиваетесь на макросах, вы захотите проверить его раздел Примеры.

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

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

Дальнейшие действия с макросами Excel

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

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

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

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

Как организовать файлы Windows с помощью SSD и HDD

Вот как можно использовать SSD и HDD вместе для достижения наилучших результатов на ПК с Windows, особенно если на вашем SSD мало места.

Об авторе

Брэд Джонс
(Опубликовано 110 статей)

Английский писатель, в настоящее время проживающий в США. Найдите меня в Твиттере через @radjonze.

Ещё от Brad Jones

Подпишитесь на нашу рассылку новостей

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

Еще один шаг…!

Пожалуйста, подтвердите свой адрес электронной почты в письме, которое мы вам только что отправили.

Динамическое окно поиска с фильтром Excel (извлечение данных по мере ввода)

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

Что-то вроде того, что показано ниже:

У этого есть двойная функция — вы можете выбрать название страны из раскрывающегося списка или вы можете вручную ввести данные в поле поиска, и он покажет вам все соответствующие записи.Например, когда вы вводите «I», вы получаете все названия стран с алфавитом I.

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

Посмотреть видео — Создание окна поиска с динамическим фильтром Excel

Создание окна поиска с динамическим фильтром Excel

Этот динамический фильтр Excel можно создать в 3 этапа:

1. Получение уникального списка товаров (в данном случае стран). Это будет использоваться при создании раскрывающегося списка.
2. Создание поля поиска. Здесь я использовал поле со списком (элемент управления ActiveX).
3. Установка данных. Здесь я бы использовал три вспомогательных столбца с формулами для извлечения совпадающих данных.

Вот как выглядят необработанные данные:

ПОЛЕЗНЫЙ СОВЕТ. Почти всегда полезно преобразовать данные в таблицу Excel. Вы можете сделать это, выбрав любую ячейку в наборе данных и используя сочетание клавиш Control + T.

Шаг 1. Получение уникального списка элементов

1. Выберите все страны и вставьте его в новый рабочий лист.
2. Выберите список стран -> Перейти к данным -> Удалить дубликаты.
3. В диалоговом окне «Удалить дубликаты» выберите столбец, в котором находится список, и нажмите «ОК». Это удалит дубликаты и даст вам уникальный список, как показано ниже:
4. Еще один шаг — создать именованный диапазон для этого уникального списка. Для этого:
   • Перейдите на вкладку «Формула» -> Определить имя
   • В диалоговом окне «Определить имя»:
     • Имя: CountryList
     • Область: Рабочая книга
     • Относится к: = UniqueList! $ A $ 2: $ A $ 9 (у меня есть список на отдельной вкладке с именем UniqueList в A2: A9.Вы можете ссылаться на него, где бы ни находился ваш уникальный список)

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

  См. Также: Как использовать формулу для получения списка уникальных элементов.  

Шаг 2. Создание окна поиска с динамическим фильтром Excel

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

Мы можем использовать поле со списком в Excel, чтобы создать этот фильтр поля поиска. Таким образом, всякий раз, когда вы вводите что-либо в поле со списком, это также будет отражаться в ячейке в реальном времени (как показано ниже).

Вот шаги, чтобы сделать это:

1. Перейдите на вкладку разработчика -> Элементы управления -> Вставить -> Элементы управления ActiveX -> Поле со списком (элементы управления ActiveX).
2. Щелкните в любом месте листа. Он вставит поле со списком.
3. Щелкните правой кнопкой мыши поле со списком и выберите «Свойства».
4. В окне «Свойства» внесите следующие изменения:
   • Связанная ячейка: K2 (вы можете выбрать любую ячейку, в которой вы хотите, чтобы отображались входные значения. Мы будем использовать эту ячейку при настройке данных).
   • ListFillRange: CountryList (это именованный диапазон, который мы создали на шаге 1. Это покажет все страны в раскрывающемся списке).
   • MatchEntry: 2-fmMatchEntryNone (это гарантирует, что слово не будет автоматически заполнено при вводе)
5. При выбранном поле со списком перейдите на вкладку разработчика -> Элементы управления -> щелкните режим разработки (это выведет вас из режим дизайна, и теперь вы можете вводить что угодно в поле со списком.Теперь все, что вы набираете, будет отражено в ячейке K2 в реальном времени).

Шаг 3 — Установка данных

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

Вспомогательный столбец 1: Введите серийный номер для всех записей (в данном случае 20). Для этого вы можете использовать формулу ROWS ().

Вспомогательный столбец 2: В вспомогательном столбце 2 мы проверяем, соответствует ли текст, введенный в поле поиска, тексту в ячейках столбца страны.

Это можно сделать с помощью комбинации функций ЕСЛИ, ЕЧИСЛО и ПОИСК.

Вот формула:

 = ЕСЛИ (ЕЧИСЛО (ПОИСК ($ K $ 2, D4)), E4, "") 

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

Если есть совпадение, эта формула возвращает номер строки, в противном случае возвращается пустое значение. Например, если поле со списком имеет значение «США», все записи с указанием страны как «США» будут иметь номер строки, а все остальные будут пустыми («»).

Вспомогательный столбец 3: В вспомогательный столбец 3, нам нужно собрать все номера строк из вспомогательного столбца 2 вместе.Для этого мы можем использовать комбинацию формул IFERROR и SMALL. Вот формула:

 = ЕСЛИОШИБКА (МАЛЕНЬКАЯ ($ F $ 4: $ F $ 23, E4), "") 

Эта формула складывает вместе все совпадающие номера строк. Например, если поле со списком имеет значение US, все номера строк, в которых есть «US», складываются вместе.

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

 = ЕСЛИОШИБКА (ИНДЕКС ($ B $ 4: $ D $ 23, $ G4, COLUMNS ($ I $ 3: I3)), "") 

Эта формула состоит из 2 частей:
ИНДЕКС — извлекаются данные исходя из номера строки.
ЕСЛИ ОШИБКА — возвращает пустое значение, если данные отсутствуют.

Вот снимок того, что вы наконец получили:

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

Загрузите файл примера динамического фильтра Excel

Проявите творческий подход! Попробуйте некоторые варианты

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

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

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

Примечание. Если вы используете Office 365, вы можете использовать функцию ФИЛЬТР, чтобы быстро фильтровать данные по мере ввода. Это проще, чем метод, показанный в этом руководстве.

Вам также могут понравиться следующие руководства по Excel:

Как работать с макросами в Excel без программирования кода

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

Ни один человек, записывающий макросы, не должен быть специалистом — программистом. Он может не знать язык программирования VBA, когда создает свои собственные макропрограммы с помощью Macro Recorder.

Взаимодействие с другими людьми

Пожалуйста, включите панель «РАЗРАБОТЧИК». Просто откройте группу «Параметры» в меню «Файл». В открывшемся окне «Параметры Excel» найдите группу «Настроить ленту». Обратите внимание на правый столбец настроек под тем же названием, что и «Ribbon Setup».Отметьте вкладку «РАЗРАБОТЧИК» — это хорошо видно на фото.

Теперь у вас есть новая вкладка «РАЗРАБОТЧИК», доступная со всеми нашими инструментами для автоматизации работы в Excel и создания макросов.

Использование VBA и макросов в Microsoft Excel

Макросы

— это внутренние приложения, которые всегда выполняют всю рутинную работу, облегчая жизнь пользователю ПК. Каждый пользователь может создать макрос даже без знания языков программирования. Для этого используйте специальную командную кнопку, запускающую его.В этом режиме все действия Macro Recorder в Excel записываются путем перевода кода VBA на язык программирования в автоматическом режиме. После окончания записи мы получаем готовую программу, выполняющую действия, которые совершил пользователь при ее написании.

Запись макросов в процессе Excel

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

1. Нажмите кнопку Macro Recorder на вкладке «DEVELOPER».
2. Когда вы получите диалоговое окно, заполните его параметрами макроса и нажмите кнопку ОК.
3. Когда закончите процесс, нажмите кнопку «Остановить запись». После этого макрос будет автоматически сохранен.
4. Чтобы выполнить или отредактировать записанный макрос, нажмите кнопку «Макросы» или выберите комбинацию клавиш ALT + F8. После него появится окно со списком записанных макросов и кнопками для управления ими.

Используя макропрограммы, вы можете увеличить продуктивность работы пользователя (до десятков раз!).Однако для 100% корректной записи пользовательских макросов необходимо соблюдать несложные правила, которые существенно влияют на их качество в момент их записи и производительность.

Взаимодействие с другими людьми

Работа с макросами в Excel

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

1. Используйте правильные имена в макросах

Дайте макросам короткие, но значимые имена.Когда вы сочувствуете всей процедуре, вы начнете создавать множество макросов. Вам будет легче найти короткие и значимые имена. Система VBA позволяет указать описание имени. Обязательно всегда используйте его. Имя макроса всегда (!) Должно начинаться с букв и не может содержать пробелов, различных символов или различных знаков препинания. После первого символа вы можете использовать числа или символы подчеркивания, но максимальная длина имени составляет всего восемьдесят символов.

2.Использовать относительные и абсолютные ссылки на ячейки

Абсолютная ссылка на ячейку — это точное местоположение курсора, когда информация о его местоположении записывается в макроадреса с жесткой привязкой к определенной ячейке во время записи. Абсолютные ссылки ограничивают возможности макросов, если вы добавляете или даже удаляете данные в документе Excel — список данных станет больше. Относительные инструменты (ссылки) не привязывают курсор к конкретному адресу ячейки. Excel использует по умолчанию «Абсолютный» режим; однако вы всегда можете изменить его, включив кнопку «Использовать относительные ссылки», расположенную под кнопкой Macro Recorder на панели инструментов вкладки «DEVELOPER».

3. Всегда начинайте запись макроса с позиции ячейки A1

Абсолютный подсчет ячеек всегда осуществляется от начальной (домашней) позиции A1 до адреса курсора с вашими данными. Если вы сохранили свой макрос в личной книге макросов (мы рекомендуем всегда выполнять его), вы можете использовать свою программу на других листах с аналогичными данными. Никогда не имеет значения, когда вы наводите курсор, когда начинаете запись макроса. Даже если он уже находится в ячейке A1, ваш первый макрос лучше всего писать после нажатия клавиш Ctrl + Home.Приведем пример. Представьте, что каждый месяц вы получаете несколько десятков таблиц из всех веток. Вам необходимо систематизировать данные и произвести необходимые расчеты для ежемесячного отчета. Выполняя все эти функции, запишите свой макрос.

4. Используйте специальные клавиши направления при записи макроса

Используйте специальные клавиши со стрелками для управления курсором (Ctrl + Up и т. Д.). Используя эти клавиши направления, вы можете просто добавлять, легко изменять или даже полностью удалять данные в таблице по мере необходимости.Использовать компьютерную мышь для записи действий не так удобно и так надежно. Забудьте об использовании мыши для работы с макросами в Excel.

5. Создание макросов для конкретных небольших задач

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

.