Как найти текст только в формулах в Excel

Иногда нужно не просто найти значение в Excel, а определить, содержится ли оно внутри формулы. Это особенно важно при проверке таблиц — например, при поиске жёстко заданных налоговых ставок, таких как 0,08, проверке использования устаревших функций, таких как ВПР, обнаружении формул, связанных со старым листом или книгой, или поиске формул, содержащих определённые условия, например «Закрыто» или «Утверждено».

Проблема в том, что один и тот же текст или число могут встречаться и в обычных ячейках. Например, 0,08 может присутствовать в столбце с налоговыми ставками, в примечаниях и даже внутри формул, таких как =B2*(1+0,08). При использовании стандартной команды «Найти» в Excel результаты включают все эти случаи, из-за чего проверка формул становится медленной и запутанной.

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

• Метод 1: Поиск Ячейки с формулами с помощью команд «Перейти к» и «Найти»
• Метод 2: Поиск Выделить формулы в диапазонах, на листах или в рабочих книгах с помощью Kutools
• Метод 3: Выделение ячеек с формулами, содержащими определённый текст, с помощью VBA
• Какой метод подходит вам лучше всего?

Поиск Ячейки с формулами с помощью «Перейти к специальному» и «Найти»

Этот метод — самый простой вариант, когда поиск нужно выполнить только в пределах текущего листа или выбранного диапазона. Идея проста: сначала выделите все ячейки с формулами, а затем ищите именно в этом выделении. Такой подход помогает избежать совпадений с обычными значениями или примечаниями, содержащими тот же текст.

1. На вкладке Главнаянажмите Найти и выделить>Формулы.

   

2. Теперь выделены только ячейки с формулами. Нажмите Ctrl + F, чтобы открыть диалоговое окно Поиск и замена.
3. В поле Найти введите текст, который нужно искать только в формулах.
4. Нажмите Параметры >>, если раздел «Дополнительные параметры» скрыт.
5. Установите значение параметра В пределахна Листи установите значение параметра Искать вна Формулы.

   

6. Нажмите Найти все или Найти далее.

Результат

На этом листе поиск 0,08 после выделения ячеек с формулами возвращает только те формулы, которые содержат 0,08, например жёстко заданные налоговые формулы в столбце D, исключая значения налоговых ставок в столбце C и текстовые совпадения в столбце E.

Поиск по диапазонам, листам или книгам с помощью Kutools

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

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

1. Нажмите Kutools > Найти > Расширенный поиск, чтобы открыть панель Расширенный поиск.
2. В раскрывающемся списке В пределах выберите нужный вариант. Вы можете выполнять поиск в Выделенном фрагменте, Текущем листе, Выбранных листах, Текущей книге или во всех книгах.
3. Включите только кнопку Формула и отключите все остальные выбранные кнопки в разделе «Тип поиска», чтобы Kutools выполнял поиск только внутри формул.
4. В раскрывающемся списке Тип выберите условие, например Текст содержит.
5. В поле Значение введите текст, который необходимо найти, например 0,08, ВПР и т.д.
6. Нажмите Найти. Все ячейки с формулами появятся в списке результатов.
7. Щёлкните любой результат, чтобы сразу перейти к соответствующей ячейке, или нажмите Выделить, чтобы выбрать все совпадающие ячейки одновременно.

   

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

Выделение ячеек с формулами, содержащими определённый текст, с помощью VBA

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

1. Выделите диапазон, в котором требуется выполнить поиск. Чтобы искать по всему листу, сначала щёлкните любую ячейку.
2. Нажмите Alt + F11, чтобы открыть редактор VBA.
3. Нажмите Вставка > Модуль.
4. Вставьте следующий код в модуль:

   Sub FindTextInFormulasOnly()
       Dim c As Range
       Dim searchText As String
       Dim searchRange As Range
       Dim foundCount As Long
   
       searchText = InputBox("Enter the text to find in formulas:", "Kutools for Excel")
   
       If Len(searchText) = 0 Then Exit Sub
   
       On Error Resume Next
       Set searchRange = Selection.SpecialCells(xlCellTypeFormulas)
       On Error GoTo 0
   
       If searchRange Is Nothing Then
           MsgBox "No formula cells found in the selected range.", vbInformation, "Kutools for Excel"
           Exit Sub
       End If
   
       For Each c In searchRange
           If InStr(1, c.Formula, searchText, vbTextCompare) > 0 Then
               c.Interior.Color = vbYellow
               foundCount = foundCount + 1
           End If
       Next c
   
       If foundCount = 0 Then
           MsgBox "No matches found.", vbInformation, "Kutools for Excel"
       Else
           MsgBox foundCount & " matching formula cell(s) found and highlighted.", vbInformation, "Kutools for Excel"
       End If
   End Sub

5. Нажмите F5, чтобы запустить макрос.
6. Введите текст, который необходимо найти. Ячейки с формулами будут выделены жёлтым цветом.

   

Какой метод подходит вам лучше всего?

Заключение

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

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

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

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