我在网站上浏览了一下,但是有关如何使Excel公式更易读的建议通常是针对“使用命名范围”或“使用辅助列”的。我不是要让公式更易读,我想创建一个宏来以更易读的方式显示公式。
您可以直接跳到问题的简化描述。我之所以这样说是因为,虽然我通常喜欢解释我的方法,但我感觉这一次我的方法可能会分散注意力,远离问题的本质。此外,我认为这个问题可以很好地概括,不需要所有前面的背景问题背景
我有很多嵌套复杂的公式,以下是我今天编写的一个示例(请参见Excel样本文档):
或者作为代码:
=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")
尽管它看起来不太美观,但在我看来,它实际上构造得相当好;尽可能使用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中的第一个组件。我向下拖动以迭代。
宏
这给了我每个单元格中公式组件的列表。然后我的宏决定每个组件的“级别”,并将该组件缩进相应数量的单元格。'级别'定义为公式中一个组件*或'my code'中的“片段”之前尚未关闭的括号数。该宏的注释解释了这一点。
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))
并显示它们。而不是像现在这样:
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中的公式一般为
函数(参数1,参数2,参数3...)
- 每个参数可能是简单的(常量、文本字符串、单元格引用)或复杂的(另一个具有自己函数和参数的公式)
- 我想要一个宏,它可以接收一个输入的公式,并创建某种形式的用户界面(可以是像我示例中所示的特殊位置的单元格,也可以是其他方式),以树形方式显示该函数
- 这意味着在UI的第一层中,我将显示函数,第二层具有函数的参数,如果参数是复杂的,则会有进一步的子层
- 要访问子层,每个函数都可以展开下拉箭头或其他方法来显示其参数(其下方的“层”)。就像Reddit帖子一样,你可以点击[+]获取更深入的1层。
=(A1+A2)/A3
可以分成在单元格 B1 中写入=(A1+A2)
,在单元格 B2 中写入=B1/A3
。这样可以得到更短、更易读的公式。但是,除非我在公式中重复代码(比如=(A1+A2)/A3+(A1+A2)^A3)
),否则我不赞成将该公式拆分 - 它实际上会阻碍我全面理解该公式的能力。而且,由于没有重复计算,我认为用那种方式拆分它也不会加快计算速度。 - Greedo