How to rewrite excel formulas in a readable manner

问题:

I have an Excel file with formulas in this manner:

=IF(OR(ISERROR(G16),ISERROR(G17)),X16,IF(OR(G16="xxx",G16="yyy",G16="zzz"),Y16,IF(G16="333","N\A",IF(G17="333",Z16,IF(D17="",IF((HEX2DEC(W$10)-HEX2DEC(W16))/VLOOKUP(F16,$M$36:$N$41,2,FALSE)<0,0,(HEX2DEC(W$10)-HEX2DEC(W16))/VLOOKUP(F16,$M$36:$N$41,2,FALSE)), IF((HEX2DEC(W17)-HEX2DEC(W16))/VLOOKUP(F16,$M$36:$N$41,2,FALSE)<0,0,(HEX2DEC(W17)-HEX2DEC(W16))/VLOOKUP(F16,$M$36:$N$41,2,FALSE)))))))

I would like to simplify them so it will be written in a more readable manner.

• Can I edit/write Excel formulas in indented way?
• What kind of simplifications can I do?
• Should I use an VBA script instead of Excel's formulas?

回答1:

As an example using helper columns, you could shorten the formula with the following

[A1] =VLOOKUP(F16,$M$36:$N$41,2,FALSE)

[B1] =HEX2DEC(W$10)

[C1] =HEX2DEC(W16)

[D1] =HEX2DEC(W17)

then the large formula is shortened to

=IF(OR(ISERROR(G16),ISERROR(G17)),X16,IF(OR(G16="xxx",G16="yyy",G16="zzz"),Y16,IF(G16="333","N\A",IF(G17="333",Z16,IF(D17="",IF((B1-C1)/A1<0,0,(B1-C1)/A1), IF((D1-C1)/A1<0,0,(D1-C1)/A1))))))

This is particularly effective when using volatile functions such as DATE or NOW which you don't want to recalc for every cell when it's the same result.

Whether it's more readable, perhaps not but you can label column headings with appropriate comments

回答2:

You can use Alt+Enter in the formula bar to make your formula multiline. Sadly, no tabs only spaces so it becomes tedious to create and edit. See also

http://www.dailydoseofexcel.com/archives/2005/04/01/excel-formula-formatter/

回答3:

Naming some of the cells you refer to might make the whole thing more readable

回答4:

FormulaDesk is a free Excel add-in that makes complex formulas more readable and easier to understand without having to rewrite them. It makes creating, editing, debugging and understanding formulas much easier. It has two modes: 'Edit View' and 'Explore View', which can be toggled.

• The ‘Edit’ view is an enhanced formula editor, with Intellisense etc. It formats as you type, vertically offsetting nested elements for clarity and understandability.

• The ‘Explore’ view presents the formula in a simple nested/rolled-up way, with the simplest top-level view of your formula first, but allowing you to drill-down to nested expressions. This enables you to quickly understand how/why it is returning the current result. Click the green bars (rolled-up results) to drill-down. Alternatively, click the ‘Expand all’, ‘Collapse all’ buttons.

• In both views you can hover over various elements (eg: functions, parameters etc) to see a popup with more information, such as definition/description, current value etc. Hovering over a range will display its current value for example.

• There are quite a few other capabilities.

[Disclosure: I am the author of FormulaDesk]

回答5:

A combination of helper columns and named ranges would make that formula quite simple.

In the following image you can see how named ranges can unclutter a formula:

Notice that "prices" is name for range A2:A7 and "inflated_prices" is name for B2:B7.

Notice also that names are intelligent: sum(prices) will sum the whole range, whereas =+prices*2 in B2 resolves to =+A2*2, =+prices*2 in B3 resolves to =+A3*2 and so on.

回答6:

You can simplify your formula substantially while still keeping a single formula. You are repeating almost the same expression 4 times with the HEX2DEC/VLOOKUP part, that can be reduced to a single instance if you recognise that this

=IF(formula<0,0,formula)

.....is equivalent to

=MAX(0,formula)

[for numeric results of formula]

and if you nest your IF(D17="".....expression within the main formula, i.e. this version

=IF(ISERROR(G16&G17),X16,IF(OR(G16={"xxx","yyy","zzz"}), Y16,IF(G16="333","N\A",IF(G17="333",Z16,MAX(0,(HEX2DEC(IF(D17="",W$10,W17))-HEX2DEC(W16))/VLOOKUP(F16,$M$36:$N$41,2,0))))))

回答7:

Since you asked about VBA code, I thought I'd give it a try. It's certainly more understandable and therefore maintainable, however the function has 11 arguments so it's a little unwieldy.

Function Magic(d17 As Range _
                , f16 As Range _
                , g16 As Range _
                , g17 As Range _
                , w10 As Range _
                , w16 As Range _
                , w17 As Range _
                , x16 As Range _
                , y16 As Range _
                , z16 As Range _
                , m36 As Range) As Variant


    Dim a As Variant
    Dim b As Variant

    If IsError(g16.Value) Or IsError(g17.Value) Then
        Magic = x16.Value
        Exit Function
    End If

    If g16.Value = "xxx" Or g16.Value = "yyy" Or g16.Value = "zzz" Then
        Magic = y16.Value
        Exit Function
    End If

    If g16.Value = "333" Then
        Magic = "N\A"
        Exit Function
    End If

    If g17.Value = "333" Then
        Magic = z16.Value
        Exit Function
    End If

    If d17.Value = "" Then
        a = Application.WorksheetFunction.Hex2Dec(w10.Value) _
                - Application.WorksheetFunction.Hex2Dec(w16.Value)
        a = a / Application.WorksheetFunction.VLookup(f16.Value, m36, 2, False)
        If a < 0 Then
            Magic = 0
            Exit Function
        Else
            Magic = a
            Exit Function
        End If
    Else
        b = Application.WorksheetFunction.Hex2Dec(w17.Value) _
                - Application.WorksheetFunction.Hex2Dec(w16.Value)
        b = b / Application.WorksheetFunction.VLookup(f16.Value, m36, 2, False)
        If b < 0 Then
            Magic = 0
            Exit Function
        Else
            Magic = b
            Exit Function
        End If
    End If
End Function

To make it easier to follow your formula logic (and I didn't know what the cells represent), I named the variables for the cell references. You'll want to rename them to something meaningful. The code belongs in a module.

回答8:

If you have a formula that looks like that, then to get a meaningful response you will have to post a sample workbook on a forum with clear instructions on what you are trying to achieve.

Yes, some of the answers above point out how you can view the formula better, or get rid of some superfluous stuff, or hide the complexity within some VBA (which in my opinion only addresses cosmetics, probably with significant expense in terms of greatly increased recalculation time).

But without knowing the intent of the formula - and of the workbook in which it sits - one can only offer so much advice.

If you have tens of thousands of formulas like this in your workbook, then you have a data structure problem, and not a formula problem. The most efficient formula is the one that is avoided. If you were to redesign this workbook from scratch so that it leveraged off of Excel Tables, PivotTables, and perhaps the Advanced Filter, then you would avoid tens of thousands of formulas like this one. Maybe hundreds of thousands of formulas.

回答9:

FormulaDesk only works on windows.

In addition to FormulaDesk above:

Here's a link to FastExcel's current Beta 3. But they seem to have been around for over a decade. $29 bucks. I think windows only. The site is unclear.

http://fastexcel.wordpress.com/2014/04/28/making-sense-of-complex-formulas-an-indenting-viewer-editer/

Similar functionality from Precision Calc. $12 Has an Nagware free version if you only need this now and then. Windows only.

http://precisioncalc.com/tf/what_is_the_formulator.html