Есть ли в Excel встроенный метод анализа формул? (т.е.: получить список включенных ссылок RANGE)

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

Например, если у меня есть ячейка с этой формулой:

= A + 25 + B

.... Я хотел бы иметь возможность получить массив диапазонов Excel, содержащихся в формуле, поэтому в этом случае он будет содержать [A] и [B]

"Почему ты вообще хочешь это сделать?", Я слышу, как ты спрашиваешь:
Просто один из примеров того, почему я хочу это сделать, - искать "метки" для диапазонов в формулах..... так что, в отличие от простого нажатия клавиш CTRL+~, чтобы просмотреть формулы на моем листе, я бы хотел вариант программного доступа к диапазонным ссылкам в формуле для поиска метки рядом с целевым диапазоном.

Итак, в моем примере выше, я мог бы написать формулы что-то вроде:

=Offset(CellFormulaRanges('TheAddressMyFormulaIsIn',1),0,-1)
=Offset(CellFormulaRanges('TheAddressMyFormulaIsIn',2),0,-1)

... что даст мне метку слева от 1-го и 2-го диапазонов в формуле.

Выполнение этого потребовало бы использования некоторых функций уже в самом Excel, так как написание парсера формул является сложной задачей:
http://ewbi.blogs.com/develops/2004/12/excel_formula_p.html

Источник

3 ответа

Решение

Спасибо @TimWilliams и @brettdj за то, что они указали мне правильное направление к предыдущим дискуссиям по этой теме, я могу с уверенностью сказать:

НЕТ, EXCEL НЕ ИМЕЕТ МЕТОДА ДЛЯ ПАРСИНГА.

Тем не менее, для моих довольно минимальных целей я придумал что-то, что работает, работает с перекрестными ссылками на листы и может вызываться из UDF.

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

Код является беспорядком и может быть значительно улучшен, но я просто хотел показать его здесь, пока я перехожу к чему-то еще...

РЕДАКТИРОВАТЬ

Также нашел это, что выглядит очень интересно:
http://www.dailydoseofexcel.com/archives/2009/12/05/formula-tokenizer/

Public Function CellPrecedents(cell As Range) As Variant()
    Dim resultRanges As New Collection
    If cell.Cells.count <> 1 Then GoTo exit_CellPrecedents
    If cell.HasFormula = False Then GoTo exit_CellPrecedents

    Dim formula As String
    formula = Mid(cell.formula, 2, Len(cell.formula) - 1)

    If IsRange(formula) Then
        resultRanges.Add Range(formula), 1
    Else
        Dim elements() As String
        'Debug.Print formula & " --> "
        formula = Replace(formula, "(", "")
        formula = Replace(formula, ")", "")
        'Debug.Print formula & " --> "
        elements() = SplitMultiDelims(formula, "+-*/\^")
        Dim n As Long, count As Integer
        For n = LBound(elements) To UBound(elements)
            If IsRange(elements(n)) Then
                'ACTUALLY JUST DO A REDIM PRESERVE HERE!!!!
                count = count + 1
                'resultRanges.Add Range(Trim(elements(n)))  '<---  Do **NOT** store as a range, as that gets automatically Eval()'d
                resultRanges.Add Trim(elements(n))
            End If
        Next
    End If

    Dim resultRangeArray() As Variant
    ReDim resultRangeArray(resultRanges.count)
    Dim i As Integer
    For i = 1 To resultRanges.count
        resultRangeArray(i) = CStr(resultRanges(i))  '// have to store as a string so Eval() doesn't get invoked (I think??)
    Next

    CellPrecedents = resultRangeArray

exit_CellPrecedents:
    Exit Function
End Function

Public Function IsRange(var As Variant) As Boolean
    On Error Resume Next
    Dim rng As Range: Set rng = Range(var)
    If err.Number = 0 Then IsRange = True
End Function

(просто Google SplitMultiDelims для этой функции)

Источник

Tbone, еще один вариант, который не совсем то, что вы просили, но может работать как альтернативное решение.

Вместо того, чтобы использовать формулу, чтобы попытаться найти соответствующий ярлык, попробуйте настроить свои формулы так, чтобы они работали на вас. Вот несколько вариантов в зависимости от формулы, которую вы пытались проанализировать. 1. Если ваша формула поиска, вы можете просто сместить, чтобы посмотреть влево. 2. В качестве альтернативы вы можете использовать функцию "Косвенный" в обеих формулах, чтобы убедиться, что они ссылаются на правильное местоположение.

Источник

Короче говоря, я думаю, вы хотите сделать часть: Use VBA to generate code to reproduce basic calculations on an Excel worksheetи использование функции для возврата n-го адреса или имени элемента коллекции DirectPrecedents.

источник: http://www.vb-helper.com/howto_vba_excel_formulas.html

Однако этот вариант использования устарел. По состоянию на Excel 2007 таблицы позволяют гораздо лучшее решение.

Источник
Другие вопросы по тегам