VBA: автоматизация поиска, фильтра и сортировки в Excel кодом

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

Вспомни: сами операции — сортировка (3.1) и фильтрация (3.2) — уже знакомы; здесь та же логика, только запускается кодом, а не через меню «Данные».

Содержание

Поиск и замена: Find, Replace

Метод .Find(What:="текст") ищет значение в диапазоне и возвращает найденную ячейку (объект Range) или Nothing, если не нашёл, — поэтому результат обязательно проверяют перед использованием. Метод .Replace What:="старое", Replacement:="новое" заменяет значения во всём диапазоне сразу — как обычная замена в Excel (Ctrl+H), но без диалогового окна.

Sub ПримерПоискаИЗамены()
    Dim диапазон As Range
    Set диапазон = Range("A1:C100")

    Dim найдено As Range
    Set найдено = диапазон.Find(What:="Иванов", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)

    If Not найдено Is Nothing Then
        MsgBox "Найдено: " & найдено.Address   ' например, $A$5
    Else
        MsgBox "Не найдено"                     ' Find не нашёл совпадение -> Nothing
    End If

    диапазон.Replace What:="БИ-101", Replacement:="БИ-201", LookAt:=xlWhole, MatchCase:=False
End Sub

Автофильтр из кода

Включение автофильтра и настройка условия — два ОТДЕЛЬНЫХ вызова одного метода: первый вызов (без аргументов) переключает режим фильтра, второй — задаёт условие по конкретному столбцу. Работает так же, как ручной автофильтр (тема 3.2): скрывает строки, не удаляя их.

Sub ПримерФильтрации()
    Dim диапазон As Range
    Set диапазон = Range("A1:C100")

    диапазон.AutoFilter                                   ' шаг 1 — включить режим фильтра
    диапазон.AutoFilter Field:=2, Criteria1:="БИ-101"     ' шаг 2 — задать условие по 2-му столбцу
End Sub

Сортировка из кода

Метод .Sort сортирует диапазон по ключевому столбцу (Key1) и порядку (Order1: xlAscending — по возрастанию, xlDescending — по убыванию). Параметр Header:=xlYes говорит VBA, что первая строка диапазона — заголовки столбцов, и сортировать её не нужно; без этого параметра Excel мог бы попытаться отсортировать и саму строку с названиями.

Sub ПримерСортировки()
    Dim диапазон As Range
    Set диапазон = Range("A1:C100")

    диапазон.Sort Key1:=Range("B1"), Order1:=xlDescending, Header:=xlYes   ' по столбцу B, по убыванию, без строки заголовков
End Sub

Комбинация приёмов в одном макросе

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

Range("A1:C100").Find("Иванов")                 -> находит ячейку с текстом
Range("A1:C100").Replace "БИ-101", "БИ-201"      -> меняет во всём диапазоне
Range("A1:C100").AutoFilter Field:=2, Criteria1:="БИ-201"  -> фильтр по столбцу 2
Range("A1:C100").Sort Key1:=Range("B1"), Order1:=xlDescending  -> сортировка

Проверь себя

Контрольный вопрос. Метод .Find(«текст») в VBA возвращает…

Анайденную ячейку с этим текстом (или Nothing, если не нашёл)

Бколичество найденных совпадений

ВTrue или False

Гвесь диапазон целиком

Подсказка: Find ищет КОНКРЕТНУЮ ячейку, а не просто отвечает «да/нет».

Проверить ответ →

Реальная проверка — на нашей платформе, бесплатно и без регистрации. Открыть задачу и ввести ответ →

Контрольный вопрос. Чем .Replace отличается от .Find?

А.Replace меняет значения во всём диапазоне сразу, а не просто ищет одно

Б.Replace ищет только первое совпадение

В.Replace работает только с числами

Гразницы нет, это одно и то же

Подсказка: Find — только ищет, Replace — ищет И меняет.

Проверить ответ →

Реальная проверка — на нашей платформе, бесплатно и без регистрации. Открыть задачу и ввести ответ →

Контрольный вопрос. Range(«A1:D50″).AutoFilter Field:=3, Criteria1:=»Готово» — что произойдёт со строками, где в 3-м столбце НЕ «Готово»?

Аони скроются, но останутся в таблице

Бони удалятся безвозвратно

Вони переместятся на другой лист

Гничего не изменится

Подсказка: Вспомни тему 3.2: автофильтр прячет строки, не удаляя их — здесь то же самое, просто из кода.

Проверить ответ →

Реальная проверка — на нашей платформе, бесплатно и без регистрации. Открыть задачу и ввести ответ →

Задание. Напиши строку кода сам. Нужно отсортировать диапазон A1:C100 по столбцу B по УБЫВАНИЮ, считая первую строку заголовками. Впиши строку целиком: сам диапазон, метод сортировки и три именованных параметра — ключевой столбец, порядок и признак заголовков.

Подсказка: Ключевой столбец задают как Key1:=Range(«B1»), направление — Order1:= со словом xlDescending для убывания, заголовки — Header:=xlYes.

✅ Готово, если: ты ввёл(а) верный ответ, и онлайн-проверка его приняла.

Проверить ответ →

Реальная проверка — на нашей платформе, бесплатно и без регистрации. Открыть задачу и ввести ответ →

Контрольный вопрос. Параметр Header:=xlYes в методе .Sort означает…

Апервая строка диапазона — заголовки, и сортировать её не нужно

Бвыводить заголовок результата

Всортировать только заголовки

Гтребовать пароль для сортировки

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

Проверить ответ →

Реальная проверка — на нашей платформе, бесплатно и без регистрации. Открыть задачу и ввести ответ →

Задание. Диапазон A1:A5 содержит числа 30, 10, 50, 20, 40. После Range(«A1:A5»).Sort Key1:=Range(«A1»), Order1:=xlAscending — на какой позиции (1-5 считая от A1) окажется число 30? Впиши число.

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

✅ Готово, если: ты ввёл(а) верный ответ, и онлайн-проверка его приняла.

Проверить ответ →

Реальная проверка — на нашей платформе, бесплатно и без регистрации. Открыть задачу и ввести ответ →

Задание. Напиши строку кода сам. Нужно включить автофильтр на диапазоне A1:C100 и оставить видимыми только строки, где во 2-м столбце стоит значение БИ-101. Впиши строку целиком (сам диапазон, метод автофильтра и два именованных параметра — номер столбца и условие).

Подсказка: Номер столбца задают параметром Field:=, а само значение для отбора — параметром Criteria1:= с текстом в кавычках.

✅ Готово, если: ты ввёл(а) верный ответ, и онлайн-проверка его приняла.

Проверить ответ →

Реальная проверка — на нашей платформе, бесплатно и без регистрации. Открыть задачу и ввести ответ →

Задание. Дан макрос:
For Each c In Range("A1:A10")<br> If c.Value = 2 Then c.Value = 3<br>Next c

Столбец A1:A10 содержит оценки: 5, 4, 2, 3, 5, 2, 4, 3, 5, 2. Сколько ячеек изменит этот код? Впиши число.

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

✅ Готово, если: ты ввёл(а) верный ответ, и онлайн-проверка его приняла.

Проверить ответ →

Реальная проверка — на нашей платформе, бесплатно и без регистрации. Открыть задачу и ввести ответ →

Контрольный вопрос. Отметь все верные утверждения про автоматизацию через VBA (не менее двух вариантов).

Выберите все верные варианты.

АAutoFilter из кода работает так же, как ручной автофильтр — прячет, не удаляет

Б.Sort требует указать столбец-ключ и порядок сортировки

В.Find может не найти совпадение и тогда возвращает Nothing

Гприёмы поиска, фильтра и сортировки нельзя использовать в одном макросе вместе

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

Подсказка: Два варианта либо запрещают то, что на самом деле разрешено, либо описывают не ту функцию.

Проверить ответ →

Реальная проверка — на нашей платформе, бесплатно и без регистрации. Открыть задачу и ввести ответ →

Задание. В коде ниже пропущено имя параметра, который задаёт САМО значение для фильтрации (не номер столбца, а именно текст, по которому фильтруем):
Range("A1:D50").AutoFilter Field:=4, ___:="Оплачено"

Допиши только имя параметра, без точки и скобок.

Подсказка: Field указывает НОМЕР столбца; у значения, по которому фильтруем, свой отдельный именованный параметр.

✅ Готово, если: ты ввёл(а) верный ответ, и онлайн-проверка его приняла.

Проверить ответ →

Реальная проверка — на нашей платформе, бесплатно и без регистрации. Открыть задачу и ввести ответ →

Итог

Итог. .Find ищет ячейку по значению, .Replace меняет во всём диапазоне сразу. AutoFilter из кода прячет строки по условию, как и ручной автофильтр (тема 3.2). .Sort сортирует по указанному ключевому столбцу и направлению, Header:=xlYes бережёт заголовки. Комбинация поиска, фильтра и сортировки в одном макросе — типичный сценарий автоматизации рутины.

← Назад к главе 5 · ↑ В начало урока · ⌂ В начало курса · Вперёд: Диаграммы, таблицы, сводные через VBA →