Как извлечь ссылки на ячейки из формул в Excel?

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

Извлечение ссылок на ячейки из формул с помощью пользовательской функции

Извлечение ссылок на ячейки из формул с помощью пользовательской функции

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

1. Нажмите и удерживайте клавиши ALT + F11, чтобы открыть окно Microsoft Visual Basic for Applications.

2. Щелкните Вставить > Модуль и вставьте следующий код в окно Модуля.

Код VBA: Извлечение ссылок на ячейки из формул:

Function ExtractCellRefs(Rg As Range) As String
'Updateby Extendoffice
    Dim xRetList As Object
    Dim xRegEx As Object
    Dim I As Long
    Dim xRet As String
    Application.Volatile
    Set xRegEx = CreateObject("VBSCRIPT.REGEXP")
    With xRegEx
        .Pattern = "('?[a-zA-Z0-9\s\[\]\.]{1,99})?'?!?\$?[A-Z]{1,3}\$?[0-9]{1,7}(:\$?[A-Z]{1,3}\$?[0-9]{1,7})?"
        .Global = True
        .MultiLine = True
        .IgnoreCase = False
    End With
    Set xRetList = xRegEx.Execute(Rg.Formula)
    If xRetList.Count > 0 Then
        For I = 0 To xRetList.Count - 1
            xRet = xRet & xRetList.Item(I) & ", "
        Next
        ExtractCellRefs = Left(xRet, Len(xRet) - 2)
    Else
        ExtractCellRefs = "No Matches"
    End If
End Function

3. После вставки кода сохраните его и вернитесь на лист, затем введите эту формулу: =extractcellrefs(G2) (G2 — это ячейка, содержащая формулу, из которой нужно извлечь ссылки на ячейки) в ячейку, где вы хотите получить результат, и нажмите клавишу Enter. Все ссылки на ячейки будут извлечены из формулы, см. скриншот: