Как искать именованные диапазоны в Excel (3 метода)

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

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

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

• Способ 1: Использование функции «Перейти» для поиска именованных диапазонов
• Способ 2: Поиск именованных диапазонов с помощью VBA (для продвинутых пользователей)
• Способ 3: Мгновенный поиск именованных диапазонов с помощью Kutools (лучший способ)

Метод 1: использование функции «Перейти» для поиска именованных диапазонов

Если вы уже знаете точное имя (или его часть), воспользуйтесь функцией «Перейти» в Excel, чтобы мгновенно перейти к нужному именованному диапазону. Хотя это и не полноценный инструмент поиска, он позволяет находить имена, не открывая Менеджер имён.

Шаги для поиска именованного диапазона с помощью функции «Перейти»

1. Откройте свою рабочую книгу Excel.
2. Нажмите Ctrl+G(или)F5), чтобы открыть диалоговое окно Перейти.
3. В диалоговом окне появится список всех именованных диапазонов, доступных в рабочей книге.
4. Пролистайте список и выберите нужное имя.
5. Нажмите ОК,и Excel немедленно перейдёт к указанному диапазону.

   

Ограничения

• Необходимо вручную просматривать весь список
• Отсутствует поиск по ключевым словам и фильтрация
• Неэффективно при большом количестве именованных диапазонов

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

Метод 2: поиск именованных диапазонов с помощью VBA

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

Шаги для поиска именованных диапазонов с помощью VBA

1. Откройте свою рабочую книгу.
2. Нажмите Alt+F11, чтобы открыть редактор VBA.
3. Выберите Вставка > Модуль, чтобы создать новый модуль.
4. Скопируйте и вставьте следующий код:
   Код VBA: Поиск именованных диапазонов в Excel

   Sub SearchNamedRanges()
   'Updated by Extendoffice 20260422
       Dim keyword As String
       keyword = InputBox("Enter keyword to search in named ranges:", "Kutools for Excel")
       If keyword = "" Then Exit Sub
       Dim nm As Name
       Dim rng As Range
       Dim resultRange As Range
       Dim firstCell As Range
       Dim matchCount As Long
       Dim resultList As String
       Dim firstSheet As Worksheet
       Application.ScreenUpdating = False
       For Each nm In ThisWorkbook.Names
           
           If InStr(1, nm.Name, keyword, vbTextCompare) > 0 Then
               
               On Error Resume Next
               Set rng = nm.RefersToRange
               On Error GoTo 0
               
               If Not rng Is Nothing Then
                   matchCount = matchCount + 1
                   resultList = resultList & nm.Name & vbCrLf
                   If firstCell Is Nothing Then
                       Set firstCell = rng.Cells(1)
                       Set firstSheet = rng.Worksheet
                   End If
                   If Not firstSheet Is Nothing Then
                       If rng.Worksheet.Name = firstSheet.Name Then
                           If resultRange Is Nothing Then
                               Set resultRange = rng
                           Else
                               Set resultRange = Union(resultRange, rng)
                           End If
                       End If
                   End If
               End If
           End If
           Set rng = Nothing
       Next nm
       If matchCount = 0 Then
           MsgBox "No matching named ranges found.", vbInformation
       Else
           Application.Goto firstCell, True
           If Not resultRange Is Nothing Then
               resultRange.Select
           End If
           MsgBox matchCount & " matching named range(s) found:" & vbCrLf & vbCrLf & resultList, vbInformation
       End If
       Application.ScreenUpdating = True
   End Sub

   

5. Нажмите F5, чтобы запустить код.
6. В диалоговом окне запроса введите ключевое слово для поиска именованных диапазонов.
   Примечание:Именованные диапазоны в Excel не могут содержать пробелы, поэтому названия из нескольких слов обычно соединяются с помощью нижнего подчёркивания(например,)Shipping_Cost).
   Если требуется точное совпадение, обязательно введите имя в том же формате, включая нижнее подчёркивание.В противном случае можно использовать частичное совпадение по ключевому слову (например, Shipping или Cost), чтобы найти связанные имена.

   

7. Появится окно сообщения со списком всех подходящих именованных диапазонов. Нажмите ОК.

   

Результат

После выполнения кода:

• Все подходящие именованные диапазоны будут автоматически выделены (включая несколько областей, если это применимо).
• Excel перейдёт к первому диапазону сопоставления, при этом все диапазоны сопоставления останутся выделенными.

Этот метод гибкий, но не слишком удобен для повседневного использования.

Метод 3: мгновенный поиск именованных диапазонов с помощью Kutools

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

Шаги для поиска именованных диапазонов с помощью Kutools

1. Откройте свою рабочую книгу Excel.
2. Перейдите на вкладку Kutools>Навигация, чтобы открыть Навигация.

   

3. На панели Навигация
   1. Перейдите в раздел Менеджер имен.
   2. Установите флажок в поле Поиск в верхней части списка имен.
   3. Введите ключевое слово, связанное с именованным диапазоном.

      

Результат

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

Ключевые преимущества

• Поиск по ключевым словам в режиме реального времени
• Мгновенная фильтрация результатов
• Кликабельные элементы для быстрой навигации
• Чистый и удобный интерфейс

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

Заключение

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

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