excel - VBA使Excel公式更具可读性

Question

我浏览了该网站，但是有关使Excel公式（公式？）更具可读性的建议倾向于“使用命名范围”或“使用帮助列”。我不喜欢使公式更易于阅读的技术，我想创建一个宏来以更易读的方式显示公式。您可以跳到最后以简化问题描述。我之所以这样说，是因为尽管我通常想解释一下我的方法，但是这次我觉得我的方法可能会干扰问题的实质。另外，我认为在没有上述所有背景知识的情况下，可以很好地解决问题

问题背景

我有很多令人讨厌的巢内复杂公式，这是我今天写的（参见Excel sample document）



或作为代码：

=IFERROR(IF(Latest[Weekday Num]=5,0,M13+MAX(Table3[Lunchtime],Latest[Lunchtime])-INDEX(Table4[Out],Latest[Weekday Num]))+Table3[Time remaining]-SUMIFS(Table4[Work time],Table4[Weekday Num],">"&Latest[Weekday Num],Table4[Weekday Num],"<5")+Latest[Clocked In]-S13,"Refresh csv")

IMO可能看起来很丑陋，但实际上它的结构相当好； IFS和 SUMIFS尽可能避免公式嵌套，所有命名范围存储在表中，适用时使用帮助器列。但是在某些情况下，它会返回错误的结果，并且该公式不便于阅读。 （也不是最长的，我已经用几个a4页的代码编译了公式，尽管那是我无法使用vba构造帮助程序列的时候）

我想要一个采用此公式并将其拆分为分支功能树的宏，其中每个嵌套函数都是另一个树枝-也许在工作簿的各个单元格之间拆分公式的各个组成部分

目前的方法

到目前为止，我已经尝试了以下方法：A和B列用于将公式拆分为较小的部分。我将这些部分定义为Excel公式的函数和参数，因此公式 IF(A1=1,A2,B1)被分为 IF(， A1=1,， A2,和 B1)。我使用B列中的以下公式执行此操作：

=LEFT(A2,IFERROR(MAX(1,MIN(IFERROR(FIND("(",A2),LEN(A2)+1),IFERROR(FIND(")",A2),LEN(A2)+1),FIND(",",A2))),LEN(A2)))

同时，A列查看B列找到的最后一个成分，并将其从长公式中剔除（使用 =SUBSTITUTE(A2,B3,"",1)）。
因此，对于A2中的原始公式（如文本），B2是它的第一个组成部分（例如在我的示例中为 IF(），而A3是A2中的公式减去B2中的第一个组成部分。我向下拖动以进行迭代。

宏

这给了我列B的每个单元格中公式的组成部分的列表。然后，我的宏决定了每个组成部分的级别，并以那么多的单元格缩进了该组成部分。该级别定义为在我的代码中，公式*或“段”的某个组件之前尚未关闭的方括号的数量。宏注释对此进行了解释。

Sub DispFormula()
'takes a split-up formula and indents lines appropriately

Dim CurrLev As Integer, OBrac As Integer, CBrac As Integer
Dim Segment As Range 'each part of the split up formula
LastRow = Sheets("sheet1").Cells(Rows.Count, 2).End(xlUp).Row

Set orange = Range("B2:B" & LastRow) 'all the segments make an orange

CurrLev = 0 'the "level" of the "segment" - it's level is a measure of how many layers deep the formula is nested
            'if(a=1,b,c) is split into 4 components: `if(`, `a=1,`, `b,` & `c)` where `if(` is level 0 and all the other segments are level 1

OBrac = 0 'how many open brackets have happened/ precede a segment of the formula
CBrac = 0 'how many closed brackets have happened
On Error Resume Next
For Each Segment In orange
    If InStr(Segment, "(") <> 0 Then
        OBrac = OBrac + 1
    ElseIf InStr(Segment, ")") <> 0 Then
        CBrac = CBrac + 1
    End If
    Cells(Segment.Row, CurrLev + 3) = Segment 'copies the segment value into a column indented by a number of cells equal to the order of the segment
    CurrLev = OBrac - CBrac 'current level is how many brackets have been opened - how many have been closed,
                            'ie. the number of brackets preceding a segment which are currently open
Next Segment


End Sub

如何改善

那就是我到目前为止所走的路。我真正想要的是将缩进树替换为下拉列表树。对于公式 =IF(MAX(arg1,arg2)=1,arg3,MIN(arg1,arg2))，我想分为以下几个部分： IF(， MAX(， ARG1,， ARG2)， =1,， ARG3,， MIN(， ARG1,和 ARG2)) htn显示它们。不像这样：（我现在是）

IF(     
    MAX(    
        ARG1,
        ARG2)
    =1, 
    ARG3,   
    MIN(    
        ARG1,
        ARG2))

但是像这样：（或类似的）

IF(◀

当您单击 ◀时，将变为：

IF(▼
    MAX(◀   
    =1, 
    ARG3,   
    MIN(◀

然后将 Min扩展为

IF(▼
    MAX(◀   
    =1, 
    ARG3,   
    MIN(▼
        ARG1,
        ARG2))

简短的问题描述



总结一下：


我在Excel中有一个公式； Excel中的公式的一般格式为 FUNCTION(argument1, arg2, arg3...)
每个参数可以是简单的（常量，文本字符串，单元格引用），也可以是复杂的（另一个具有自己的函数和参数的公式）
我想要一个带有输入公式的宏，并创建某种形式的用户界面（例如在我的示例中是特殊放置的单元格，或其他方法）以类似树的方式显示函数
这意味着在用户界面的第一层中，我将显示该函数，第二层具有该函数的参数，如果参数很复杂，它们将具有其他子层
要访问子层，可以使用下拉箭头或其他某种方式来扩展每个函数，以显示其参数（其下方的“层”）。就像Reddit帖子一样，您可以在[+]上单击以更深一层。

Answer 1

我使用此网站来处理长公式，对我很有帮助。

它没有您提到的UI组件，但是在“美化”输出方面做得很好。

Excel Formula Beautifier