Excel有内置的方法来解析公式吗？ （即：获取包含的RANGE引用的列表） [英] Does Excel have a built in method for parsing formulas? (ie: to obtain a list of included RANGE references)

问题描述

对于单元格中给定的Excel公式，我希望能够解析公式，以获取公式中包含的Excel范围引用列表。

例如，如果我有一个这个公式的单元格：

  = A + 25 + B 
  

....我想要得到一个包含在公式中的excel范围的数组，所以在这种情况下，它会包含[A]和[B]

为什么你甚至想这样做？我可以听到你问：

为什么我想这样做的一个例子是在公式中查找范围的标签.....所以，而不是只是做一个CTRL +〜查看我的表格中的公式，我想要在公式中程序访问范围引用的选项，以便在目标范围旁边查找标签。

所以，在上面的例子中，我可以写公式如下：

  = Offset（CellFormulaRanges（'TheAddressMyF ormulaIsIn'，1），0，-1）
 = Offset（CellFormulaRanges（'TheAddressMyFormulaIsIn'，2），0，-1）
  

...这将给出公式中第一和第二范围左侧的标签。

做这将必须调用Excel本身中的一些功能，因为手写公式解析器是一项复杂的任务：

http://ewbi.blogs.com/develops/2004/12/excel_formula_p.html

解决方案

感谢@TimWilliams和@brettdj指出我正确的方向，以前讨论这个话题，我可以自信地说：

不，EXCEL没有一个分配方法。

然而，由于我的目的很少，我已经提出了一些有用的工作，适用于跨工作表参考，可以从UDF调用。

然而，它是极端的很脆弱，有很多完美合法的公式，我确定它不能正确处理。

代码是一团糟，可以大大改善，但我只是想把它放在这里，因为我现在转向别的东西....

编辑

还发现这个，看起来很有趣：

http://www.dailydoseofexcel.com/archives/2009/12/05/formula-tokenizer/

  Public Function CellPrecedants（cell As Range）As Variant（）
 Dim resultRanges As New Collection 
如果cell.Cells.count<> 1然后GoTo exit_CellPrecedents 
如果cell.HasFormula = False然后GoTo exit_CellPrecedents 
 
 Dim formula As String 
 formula = Mid（cell.formula，2，Len（cell.formula） - 1）
 
如果IsRange（公式）然后
 resultRanges.Add范围（公式），1 
 Else 
 Dim elements（）As String 
'Debug打印公式 - >
 formula = Replace（formula，（，）
 formula = Replace（formula，），）
'Debug.Print formula& ;  - >
 elements（）= SplitMultiDelims（formula，+  -  * / \ ^）
 Dim n As Long，count As Integer 
对于n = ）到UBound（元素）
如果IsRange（elements（n））然后
'实际上只需要一个REDIM保存在这里！ 
 count = count + 1 
'resultRanges.Add范围（Trim（elements（n）））'< --- Do ** NOT **存储为一个范围， ）'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））'//必须存储为字符串，所以Eval（）不被调用（我想？）
下一个
 
 CellPrecedants = resultRangeArray 
 
 exit_CellPrecedants：
退出函数
结束函数
 
公共函数IsRange（var As Variant）As Boolean 
 On Error Resume Next 
 Dim rng As Range：Set rng = Range（var）
如果err.Number = 0 Then IsRange = True 
结束函数
  

google SplitMultiD消除该功能）

For a given Excel formula in a cell, I'd like to be able to parse the formula in order to get a list of Excel Range references contained within the formula.

For example, if I have a cell with this formula:

= A + 25 + B  

....I would like to be able to get an array of excel ranges contained within the formula, so in this case, it would contain [A] and [B]

"Why do you even want to do this"?, I can hear you asking:
Just one example of why I want to do this is to look up "labels" for ranges in formulas.....so, as opposed to just doing a CTRL+~ to view the formulas in my sheet, I'd like the option of programatically accessing the range references within the formula in order to do a lookup of the label beside the target range.

So, in my above example, I could then write formulas something like:

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

...which would give me the the label to the left of the 1st and 2nd ranges within the formula.

Doing this would have to call upon some functionality already within Excel itself, as hand writing a formula parser is a complicated task:
http://ewbi.blogs.com/develops/2004/12/excel_formula_p.html

解决方案

Thanks to @TimWilliams and @brettdj for pointing me in the right direction to previous discussions on this topic, I can confidently say:

NO, EXCEL DOES NOT HAVE A METHOD FOR PARSING.

However, for my fairly minimal purposes, I've come up with something that works, works with cross worksheet references, and can be called from a UDF.

However, it is extremely brittle, and there are multitudes of perfectly legitimate formulas that I'm certain it wouldn't handle properly.

The code is a mess and could be greatly improved but I just wanted to throw it up on here as I'm moving onto to something else for the time being....

EDIT

Also found this, which looks very interesting:
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

(just google SplitMultiDelims for that function)

这篇关于Excel有内置的方法来解析公式吗？ （即：获取包含的RANGE引用的列表）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！