← Сборка отчёта из нескольких…Пользовательские функции (U… →

Поиск, фильтрация и обработка данных

Четыре операции, из которых состоит 90% офисной рутины: найти значение, отфильтровать по условию, вычистить дубли и подсветить проблемные строки.

Range.Find — программный «Ctrl+F»: ищет значение в диапазоне и возвращает ссылку на ячейку, где нашёл, или Nothing, если не нашёл ничего.

Зачем это в коде, если всё есть в интерфейсе

Найти, отфильтровать, удалить дубли — всё это Excel умеет и без единой строчки VBA. Но руками это делается каждый раз заново. А в макросе — один раз, кнопкой, всегда в одном и том же порядке, без «ой, я забыл снять фильтр». Именно поэтому четыре приёма из этого урока встречаются почти в любом рабочем макросе, и именно на них новички ломают себе файлы. Разберём каждый на боевом примере.

Дальше везде работаем с листом «Данные»: шапка в строке 1, колонки A–F — Дата, Менеджер, Товар, Количество, Сумма, Источник.

Поиск: Find и FindNext

Простейший сценарий — спросить у пользователя товар и прыгнуть на первое совпадение.

Option Explicit

Sub FindProduct()

    Dim ws As Worksheet
    Dim hit As Range
    Dim query As String

    Set ws = ThisWorkbook.Worksheets("Данные")

    query = Trim$(InputBox("Какой товар ищем?", "Поиск"))
    If Len(query) = 0 Then Exit Sub          ' нажали «Отмена» или ничего не ввели

    Set hit = ws.Columns("C").Find( _
        What:=query, _
        LookIn:=xlValues, _
        LookAt:=xlWhole, _
        MatchCase:=False)

    If hit Is Nothing Then
        MsgBox "«" & query & "» в колонке C не найден.", vbExclamation
    Else
        ws.Activate
        hit.Select
        MsgBox "Нашёл в ячейке " & hit.Address(False, False) & _
               ", строка " & hit.Row, vbInformation
    End If

End Sub

Ключевое здесь — Set hit = ... и проверка If hit Is Nothing. Find возвращает объект (ячейку), поэтому присваивание идёт через Set. А если совпадения нет, вернётся Nothing — и любая попытка спросить у него .Row уронит макрос с «Object variable not set». Проверка на Nothing — не перестраховка, а обязательная часть любого Find.

Ловушка, на которой обжигаются все

Параметры LookIn, LookAt и MatchCase — «липкие». Excel запоминает их последнее значение — причём общее для окна поиска Ctrl+F и для VBA. Если ты не указал LookAt явно, макрос возьмёт то, что пользователь выбрал в диалоге час назад. Вчера искал целиком — сегодня по части. Отсюда мистические баги «на моём компьютере работает». Лечение простое: указывай LookIn и LookAt явно всегда, при каждом вызове Find. xlWhole — совпадение целиком, xlPart — вхождение подстроки.

Найти все совпадения

Find находит первое, FindNext — следующее, по кругу. Значит, нужен якорь: запоминаем адрес первой находки и крутим цикл, пока не вернулись к нему.

Sub HighlightAllMatches()

    Dim ws As Worksheet
    Dim searchArea As Range
    Dim hit As Range
    Dim firstAddress As String
    Dim found As Long

    Set ws = ThisWorkbook.Worksheets("Данные")
    Set searchArea = ws.Range("C2:C" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row)

    Set hit = searchArea.Find(What:="Клавиатура", LookIn:=xlValues, LookAt:=xlWhole)

    If hit Is Nothing Then
        MsgBox "Совпадений нет.", vbInformation
        Exit Sub
    End If

    firstAddress = hit.Address        ' запомнили, откуда начали круг

    Do
        found = found + 1
        hit.Interior.Color = RGB(255, 235, 156)   ' жёлтая заливка

        Set hit = searchArea.FindNext(hit)
        If hit Is Nothing Then Exit Do            ' страховка
    Loop While hit.Address <> firstAddress

    MsgBox "Подсвечено совпадений: " & found, vbInformation

End Sub

Почему проверка If hit Is Nothing Then Exit Do вынесена отдельной строкой, а не приписана в условие Loop While? Потому что VBA не умеет короткого замыкания. В выражении Not hit Is Nothing And hit.Address <> firstAddress он вычислит обе части, даже если первая уже дала «ложь», — и на hit.Address упадёт. В большинстве языков так нельзя, в VBA — можно и больно. Разделяй проверки на отдельные строки.

Без якоря firstAddress цикл будет крутиться вечно: дойдя до низа, FindNext начинает круг заново.

Фильтр по условию и выгрузка видимых строк

Классическая задача: «выгрузи мне сделки от 100 000 на отдельный лист».

Sub ExportBigDeals()

    Dim ws As Worksheet
    Dim wsOut As Worksheet
    Dim lastRow As Long
    Dim visibleRows As Range

    Set ws = ThisWorkbook.Worksheets("Данные")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    If ws.AutoFilterMode Then ws.AutoFilterMode = False   ' снимаем старый фильтр

    ' Field:=5 — пятая колонка ДИАПАЗОНА (это E, «Сумма»), а не пятая колонка листа
    ws.Range("A1:F" & lastRow).AutoFilter Field:=5, Criteria1:=">=100000"

    On Error Resume Next
    Set visibleRows = ws.Range("A2:F" & lastRow).SpecialCells(xlCellTypeVisible)
    On Error GoTo 0

    If visibleRows Is Nothing Then
        MsgBox "Под условие не попала ни одна строка.", vbInformation
    Else
        Set wsOut = ThisWorkbook.Worksheets.Add
        wsOut.Name = "Крупные сделки " & Format(Now, "hh-mm")

        ws.Range("A1:F1").Copy wsOut.Range("A1")      ' шапка
        visibleRows.Copy wsOut.Range("A2")            ' только видимые строки
        wsOut.Columns("A:F").AutoFit
    End If

    ws.AutoFilterMode = False
    Application.CutCopyMode = False

End Sub

Три тонкости, которые надо запомнить:

  • Field:=5 считается от левого края диапазона фильтра, а не от колонки A листа. Если бы фильтр стоял на C1:H100, пятой колонкой была бы G. Самая частая ошибка новичка — отфильтровать не то, что хотел.
  • Критерий передаётся строкой: ">=100000", "<>" (непустые), "Иванов". Для дат надёжнее собирать строку через Format — иначе месяц и день могут поменяться местами из-за локали.
  • SpecialCells(xlCellTypeVisible) выбрасывает ошибку 1004, если видимых ячеек нет вообще. Не «возвращает пусто», а именно падает. Поэтому вокруг него — On Error Resume Next и проверка на Nothing.

Плюс: visibleRows.Copy копирует только видимые строки, скрытые фильтром не поедут. Именно это нам и нужно.

Удаление дублей

У Excel есть встроенный метод, и он мгновенный. Но у него есть свойство, которое стоит людям нервов: после макроса не работает Ctrl+Z. Запуск любого макроса очищает стек отмены Excel. Удалил лишнее — вернуть штатно уже нечем. Поэтому сначала копия, потом удаление.

Sub DropDuplicates()

    Dim ws As Worksheet
    Dim backup As Worksheet
    Dim lastRow As Long
    Dim rowsBefore As Long
    Dim rowsAfter As Long

    Set ws = ThisWorkbook.Worksheets("Данные")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    rowsBefore = lastRow - 1

    ' СНАЧАЛА резервная копия: отменить удаление через Ctrl+Z будет нельзя
    ws.Copy After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
    Set backup = ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
    backup.Name = "Копия " & Format(Now, "dd-mm hh-mm")

    ' дубль = совпали Дата (1), Менеджер (2) и Товар (3)
    ws.Range("A1:F" & lastRow).RemoveDuplicates _
        Columns:=Array(1, 2, 3), Header:=xlYes

    rowsAfter = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row - 1

    MsgBox "Было строк: " & rowsBefore & vbCrLf & _
           "Стало строк: " & rowsAfter & vbCrLf & _
           "Удалено дублей: " & (rowsBefore - rowsAfter), vbInformation

End Sub

Номера в Columns:=Array(1, 2, 3) — снова относительные, от левого края диапазона. Header:=xlYes говорит, что первая строка — шапка, и трогать её не надо. Метод оставляет первое вхождение и убивает все последующие; если тебе нужна, наоборот, самая свежая запись — сначала отсортируй по дате по убыванию.

Подсветка по правилу: два разных способа

Тут важно понимать разницу. Условное форматирование (FormatConditions) — это живое правило: поправил сумму в ячейке — цвет пересчитался сам. Прямая заливка (Interior.Color) — это разовый мазок краской: данные поменяются, а цвет останется врать.

Sub MarkLowSales()

    Dim ws As Worksheet
    Dim rng As Range
    Dim lastRow As Long

    Set ws = ThisWorkbook.Worksheets("Данные")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    Set rng = ws.Range("A2:F" & lastRow)

    rng.FormatConditions.Delete        ' иначе правила копятся при каждом запуске

    ' правило по формуле: смотрим на колонку E текущей строки
    With rng.FormatConditions.Add(Type:=xlExpression, Formula1:="=$E2<10000")
        .Interior.Color = RGB(255, 199, 206)   ' бледно-красная заливка
        .Font.Color = RGB(156, 0, 6)
        .StopIfTrue = False
    End With

End Sub

Знак доллара в $E2 — не украшение. Колонка закреплена ($E), строка свободна (2), и формула пишется относительно левой верхней ячейки диапазона, то есть A2. Для строки 3 Excel сам подставит $E3 — так подсвечивается вся строка целиком, а не одна ячейка.

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

Sub HighlightBigRowsFast()

    Dim ws As Worksheet
    Dim values As Variant
    Dim i As Long
    Dim lastRow As Long

    Set ws = ThisWorkbook.Worksheets("Данные")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' одно обращение к листу вместо тысяч: вся колонка E — в массив
    values = ws.Range("E2:E" & lastRow).Value

    Application.ScreenUpdating = False

    For i = 1 To UBound(values, 1)
        If IsNumeric(values(i, 1)) Then
            If values(i, 1) >= 100000 Then
                ws.Range("A" & (i + 1) & ":F" & (i + 1)).Interior.Color = RGB(226, 239, 218)
            End If
        End If
    Next i

    Application.ScreenUpdating = True

End Sub

Массив values — двумерный, даже если колонка одна: обращение всегда values(i, 1). Индексация начинается с 1, а не с 0. И заметь смещение i + 1: первая строка массива соответствует строке 2 листа, потому что строку 1 занимает шапка. Это классический источник ошибки «подсветилось на строку выше».

Как это работает

Все четыре операции объединяет одна идея: каждое обращение к ячейке — это разговор VBA с движком Excel через границу процессов, и он стоит дорого. Тысяча обращений в цикле — тысяча пересечений границы. Один Range.Value на весь диапазон — одно. Отсюда все приёмы этого урока: массивы вместо поячеечных циклов, AutoFilter вместо ручного перебора с проверкой условия, RemoveDuplicates вместо самописного словаря. Пусть тяжёлую работу делает движок Excel — он написан на C++ и быстрее любого твоего цикла.

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

Частые ошибки

СимптомПричина и лечение
Ошибка «Object variable or With block variable not set» после FindНе проверили результат на Nothing. Find без совпадений возвращает Nothing, а не пустую строку.
Find сегодня находит не то, что вчераНе задали LookIn и LookAt явно — Excel подставил значения из последнего диалога Ctrl+F. Указывай их всегда.
Цикл FindNext крутится вечноЗабыли запомнить firstAddress: поиск идёт по кругу и никогда не заканчивается сам.
Ошибка 1004 на SpecialCellsПод фильтр не попало ни одной строки. Оберни в On Error Resume Next и проверь на Nothing.
Отфильтровалась не та колонкаField считается от левого края диапазона фильтра, а не от колонки A листа.
Удалились нужные строки, Ctrl+Z не помогаетМакрос очищает стек отмены Excel. Делай резервную копию листа до RemoveDuplicates.
Цвета в файле «врут» после правки данныхИспользовали Interior.Color там, где нужно было живое правило FormatConditions.
Файл раздулся, Excel тормозитПравила условного форматирования накопились: FormatConditions.Add без предварительного .Delete при каждом запуске плодит копии.

Итоги

  • Find возвращает объект Range или Nothing — присваивай через Set и всегда проверяй на Nothing.
  • Параметры LookIn и LookAt «липкие» — задавай их явно при каждом вызове.
  • Обход всех совпадений — FindNext плюс якорь firstAddress, иначе вечный цикл. VBA не делает короткого замыкания: проверки на Nothing выноси отдельной строкой.
  • AutoFilter + SpecialCells(xlCellTypeVisible) — штатный способ выгрузить строки по условию; Field считается от края диапазона.
  • RemoveDuplicates необратим: Ctrl+Z после макроса не работает. Сначала копия листа.
  • FormatConditions — живое правило, пересчитывается само; Interior.Color — статичная заливка, устаревает вместе с данными.
  • Читай данные в массив (Variant) одним обращением — это главный ускоритель любого макроса.
Проверьте себя
1. Макрос ищет значение в диапазоне: Set hit = rng.Find(What:="Мышь"). Значения в диапазоне нет. Что окажется в hit?
AПустая строка ""
BЗначение Nothing — обращение к hit.Row уронит макрос
CНоль
DПервая ячейка диапазона
2. На листе стоит фильтр по диапазону A1:F100. Какую колонку отфильтрует строка AutoFilter Field:=5?
AКолонку E — пятую от левого края диапазона фильтра
BКолонку F — счёт идёт с нуля
CПятую колонку листа независимо от диапазона
DВсе колонки сразу