漂亮的打印Excel公式？ [英] Pretty Print Excel Formulas?

问题描述

有没有人知道（免费）工具来打印Excel公式？谷歌搜索没有改变。

我有一些半复杂公式的工作表进行点击，所以这会使我的生活变得更容易。

我只是想转一下这样的东西。

  AC6 + AD6 +（IF（H6 =Yes ，1，IF（J6 =是，1,0）））+ IF（X6 =是，1,0）
  

变成更可读的东西，而无需在Vim等手动进行。 Excel确实在括号上进行颜色匹配，但它仍然全部在一行中进行拼接。

解决方案

这个VBA代码将赢没有奖励，但没关系快速查看典型配方。它只是手动执行括号或分隔符。将其粘贴在代码模块中，并从VBA立即窗口命令行调用它。 （编辑：我最近不得不看一些公式，我改进了我原来的答案是什么，所以我回来改变了。）

  public function ppf（f）As String 
 Dim formulaStr As String 
 
如果IsObject（f）然后
 Debug.Assert TypeOf f范围
 
 Dim rng As Range 
 Set rng = f 
 
 formulaStr = rng.Formula 
 Else 
 Debug.Assert VarType（f）= vbString 
 
 formulaStr = f 
 End If 
 
 Dim tabs（0 To 99）As Long 
 
 Dim tabNum As Long 
 tabNum = 1 
 
 Dim tabOffset As Long 
 
 Dim i As Long 
 Dim c As String 
 For i = 1 To Len（formulaStr）
c = Mid $（formulaStr，i，1）
 
如果InStr（（{，c）> 0然后
 ppf = ppf& c 
 
 tabNum = tabNum + 1 
 tabs（tabNum）= tabs（tabNum  -  1）+ tabOffset + 1 
 tabOffset = 0 
 
 ppf = ppf& vbCrLf&空格（tab（tabNum））
 ElseIf InStr（）}，c）> 0然后
 tabNum = tabNum  -  1 
 tabOffset = 0 
 
 ppf = ppf& c& vbCrLf&空格（tab（tabNum））
 ElseIf InStr（+  -  * / ^ ,;，c）> 0然后
 tabOffset = 0 
 
 ppf = ppf& c& vbCrLf&空格（tab（tabNum））
 Else 
 ppf = ppf& c 
 
 tabOffset = tabOffset + 1 
 End If 
 Next i 
结束函数
  

如果你这样打电话：

 ？ppf（[q42]）
  

您不用担心转义双引号等等。你会得到这样的输出：

  AC6 + 
 AD6 + 
（
 IF （
 H6 =是，
 1，
 IF（
 J6 =是，
 1，
 0）
） 
）
 + 
 IF（
 X6 =是，
 1，
 0）
  / pre> 
 
 
您也可以使用纯字符串调用。
 
Does anyone know of a (free) tool to pretty print Excel formulas? A Google search didn't turn anything up. 

I've got a few worksheets of semi-complex formulas to slog through, so this would make my life a bit easier.

I'm just looking to turn something like this
AC6+AD6+(IF(H6="Yes",1,IF(J6="Yes",1,0)))+IF(X6="Yes",1,0)
into something more readable without manually doing it in Vim or the like. Excel does do color-matching on the parentheses, but it's still all mashed together on one line.
解决方案
This VBA code will win no awards, but it's O.K. for quickly looking at typical formulas. It just does what you'd do with parens or separators manually. Stick it in a code module and call it from the VBA immediate window command line. (EDIT: I had to look at some formulas recently, and I improved on what was here from my original answer, so I came back and changed it.)
Public Function ppf(f) As String
    Dim formulaStr As String

    If IsObject(f) Then
        Debug.Assert TypeOf f Is Range

        Dim rng As Range
        Set rng = f

        formulaStr = rng.Formula
    Else
        Debug.Assert VarType(f) = vbString

        formulaStr = f
    End If

    Dim tabs(0 To 99) As Long

    Dim tabNum As Long
    tabNum = 1

    Dim tabOffset As Long

    Dim i As Long
    Dim c As String
    For i = 1 To Len(formulaStr)
        c = Mid$(formulaStr, i, 1)

        If InStr("({", c) > 0 Then
            ppf = ppf & c

            tabNum = tabNum + 1
            tabs(tabNum) = tabs(tabNum - 1) + tabOffset + 1
            tabOffset = 0

            ppf = ppf & vbCrLf & Space(tabs(tabNum))
        ElseIf InStr(")}", c) > 0 Then
            tabNum = tabNum - 1
            tabOffset = 0

            ppf = ppf & c & vbCrLf & Space(tabs(tabNum))
        ElseIf InStr("+-*/^,;", c) > 0 Then
            tabOffset = 0

            ppf = ppf & c & vbCrLf & Space(tabs(tabNum))
        Else
            ppf = ppf & c

            tabOffset = tabOffset + 1
        End If
    Next i
End Function
If you call it like so:
?ppf([q42])
you don't have to worry about escaping your double quotes and so on. You'll get output that looks like this:
AC6+
AD6+
(
 IF(
    H6="Yes",
    1,
    IF(
       J6="Yes",
       1,
       0)
    )
 )
+
IF(
   X6="Yes",
   1,
   0)
You can also call it with a plain string.

                        
这篇关于漂亮的打印Excel公式？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！