@BurdenBear 2019-03-19T01:57:41.000000Z 字数 5058 阅读 550

Excel Beautifier

未分类

这里列出了一些工具格式化后的excel公式以及我们预期希望得到的格式化结果。

工具

Excel Formula Beautifier ( 以下简称Beautifier)
Expect 利用yapf进行的测试

例子

例1：

RAW:

=IF(SUM( IF(FOO = BAR, 100 + 3000 + 1000000000000000000000000000000000000000000000000000000 + 10000000000000000000000000000000000000000000000000000000 + 100000000000000000000000000000000000000000000, 0) , SUM(1, 10, 0)) = 20 , "FOO", "BAR")

Beautifier:

=IF(
    SUM(
        IF(
            FOO = BAR,
            100 + 3000 + 1000000000000000000000000000000000000000000000000000000 + 10000000000000000000000000000000000000000000000000000000 + 100000000000000000000000000000000000000000000,
            0
        ),
        SUM(
            1,
            10,
            0
        )
    ) = 20,
    "FOO",
    "BAR"
)

Formula Editor:

= IF(
    SUM(
        IF(
            FOO = BAR,
            100 + 3000 + 1000000000000000000000000000000000000000000000000000000 + 10000000000000000000000000000000000000000000000000000000 + 100000000000000000000000000000000000000000000,
            0
        ),
        SUM(
            1,
            10,
            0
        )
    ) = 20,
    "FOO",
    "BAR"
)

Expect:

IF(
    SUM(
        IF(
            FOO = BAR, 100 + 3000 +
            1000000000000000000000000000000000000000000000000000000 +
            10000000000000000000000000000000000000000000000000000000 +
            100000000000000000000000000000000000000000000, 0),
        SUM(1, 10, 0)) = 20, "FOO", "BAR")

对于含有参数但是很短的公式，不一定需要换行；对于不包含函数的公式，如果计算式很长，也应该进行换行。

例2：

RAW:

=SUM(12341234, 1234, 1234, 1234123412341234, 1234, 1234, 1234, 123412341234, 123412341234123412341234, SUM({123124, 123124123124, 123124123124, 123124123124, 123124}))

Beautifier:

=SUM(
    12341234,
    1234,
    1234,
    1234123412341234,
    1234,
    1234,
    1234,
    123412341234,
    123412341234123412341234,
    SUM(
{ 123124,
                123124123124,
                123124123124,
                123124123124,
                123124
            ARRAYROWSTOP)
        ARRAYSTOP)
    )
)

Formula Editor

= SUM(
    12341234,
    1234,
    1234,
    1234123412341234,
    1234,
    1234,
    1234,
    123412341234,
    123412341234123412341234,
    SUM(
        {123124,
        123124123124,
        123124123124,
        123124123124,
        123124}
    )
)

Expect:

=SUM(12341234, 1234, 1234, 1234123412341234, 1234, 1234, 1234, 123412341234,
     123412341234123412341234,
     SUM({123124, 123124123124, 123124123124, 123124123124, 123124}))

Beautifier的结果包含数组的时候有些BUG，期望的结果要尽可能保持紧凑。

例3：

RAW:

IF( FOO = BAR, IF( FOO = BAR, IF( FOO = BAR, IF(FOO = BAR, IF(FOO = BAR, IF(FOO = BAR, IF(FOO = BAR, 1, 2), 2), 2), 2), 2), 2), 2)

Beatifier:

Formula Editor:

EXPECT:

IF(
    FOO = BAR,
    IF(
        FOO = BAR,
        IF(
            FOO = BAR,
            IF(FOO = BAR,
               IF(FOO = BAR, IF(FOO = BAR, IF(FOO = BAR, 1, 2), 2), 2), 2),
            2), 2), 2)

在嵌套比较深的时候确实不好处理，像beautifier其实规则和咱们一开始的版本类似，只是对函数的参数部分进行了换行处理，这样看得比较清楚。yapf的风格是尽量紧凑，可能会牺牲一些代码的可读性。

例4:

RAW:

=IF((B3="女")*(C3>=55)+(B3="男")*(C3>=60),"退休","")

Beautifier:

=IF(
    ( B3 = "女" ) * ( C3 >= 55 ) + ( B3 = "男" ) * ( C3 >= 60 ),
    "退休",
    ""
)

Formula Editor:

= IF(
    (
        B3 = "女"
    ) * (
        C3 >= 55
    ) + (
        B3 = "男"
    ) * (
        C3 >= 60
    ),
    "退休",
    ""
)

Expect:

=IF(
    (B3 = "女") * (C3 >= 55) + (B3 = "男") * (C3 >= 60),
    "退休",
    ""
)

例5：

RAW:

=VLOOKUP(F8,IF({1,0},$B$4:$B$9,$A$4:$A$9),2,0)

Beautifier:

=VLOOKUP(
    F8,
    IF(
        {1,0},
        $B$4:$B$9,
        $A$4:$A$9
    ),
    2,
    0
)

Formula Editor

= VLOOKUP(
    F8,
    IF(
        {1,
        0},
        $B4:$B9,
        $A4:$A9
    ),
    2,
    0
)

expect:

=VLOOKUP(
    F8,
    IF(
        {1, 0},
        $B$4:$B$9,
        $A$4:$A$9
    ), 2, 0)

例6：

Raw:

=SUM(SUMIF(A13:A20,{"北京","上海","广州"},B13:B20))

Beautifier:

=SUM(
    SUMIF(
        A13:A20,
        {"北京","上海","广州"},
        B13:B20
    )
)

Formula Editor:

= SUM(
    SUMIF(
        A13:A20,
        {"北京",
        "上海",
        "广州"},
        B13:B20
    )
)

Expect:

=SUM(SUMIF(
    A13:A20,
    {"北京", "上海", "广州"},
    B13:B20))

例7:

Raw:

=INDEX(B24:F32,MATCH(I24,A24:A32,0),MATCH(I25,B23:F23,0))

Beautifier:

=INDEX(
    B24:F32,
    MATCH(
        I24,
        A24:A32,
        0
    ),
    MATCH(
        I25,
        B23:F23,
        0
    )
)

Formula Editor:

= INDEX(
    B24:F32,
    MATCH(
        I24,
        A24:A32,
        0
    ),
    MATCH(
        I25,
        B23:F23,
        0
    )
)

Expect:

=INDEX(
    B24:F32,
    MATCH(I24, A24:A32, 0),
    MATCH(I25, B23:F23, 0))

例8:

Raw:

=B36&COUNTIF(B$36:B36,B36)

Beautifier:

=B36 &
COUNTIF(
    B$36:B36,
    B36
)

Formual Editor:

= B36&COUNTIF(
    B$36:B36,
    B36
)

Expect:

= B36 & COUNTIF(B$36:B36, B36)

例9

Raw:

=IF(A2>=90,"優等",IF(A2>=80,"甲等",IF(A2>=70,"乙等","丙等")))

Beautifier:

=IF(
    A2 >= 90,
    "優等",
    IF(
        A2 >= 80,
        "甲等",
        IF(
            A2 >= 70,
            "乙等",
            "丙等"
        )
    )
)

Formula Editor:

= IF(
    A2 >= 90,
    "優等",
    IF(
        A2 >= 80,
        "甲等",
        IF(
            A2 >= 70,
            "乙等",
            "丙等"
        )
    )
)

Except:

=IF(A2 >= 90,
    "優等",
    IF(A2 >= 80,
       "甲等",
       IF(A2 >= 70, "乙等", "丙等")))

例10:

Raw:

=SUMIF(A54:A66,">=2018/1/1",C54:C66)-SUMIF(A54:A66,">=2018/6/1",C54:C66)

Beautifier:

=SUMIF(
    A54:A66,
    ">=2018/1/1",
    C54:C66
) -
SUMIF(
    A54:A66,
    ">=2018/6/1",
    C54:C66
)

Formula Editor:

= SUMIF(
    A54:A66,
    ">=2018/1/1",
    C54:C66
) - SUMIF(
    A54:A66,
    ">=2018/6/1",
    C54:C66
)

Expect:

=SUMIF(A54:A66,
       ">=2018/1/1",
       C54:C66) 
- SUMIF(A54:A66,
        ">=2018/6/1",
        C54:C66)

例11：

Raw：

=SUM(SUMIF(A54:A66,{">=2018/1/1",">=2018/6/1"},C54:C66) * {1,-1})

Beautifier：

=SUM(
    SUMIF(
        A54:A66,
        { ">=2018/1/1", ">=2018/6/1" },
        C54:C66
    ) * { 1, - 1}
)

Formula Editor:

= SUM(
    SUMIF(
        A54:A66,
        {">=2018/1/1",
        ">=2018/6/1"},
        C54:C66
    ) * {1,
    -1}
)

Expect：

=SUM(SUMIF(A54:A66,
           {">=2018/1/1", ">=2018/6/1"},
           C54:C66) * {1, -1})

例12：

Raw:

=AVERAGEIFS($D$71:$D$76, $E$71:$E$76, "<23", $E$71:$E$76, ">18.5")

Beautifier:

=AVERAGEIFS(
    $D$71:$D$76,
    $E$71:$E$76,
    "<23",
    $E$71:$E$76,
    ">18.5"
)

Formula Editor:

= AVERAGEIFS(
    $D$71:$D$76,
    $E$71:$E$76,
    "<23",
    $E$71:$E$76,
    ">18.5"
)

Expect:

=AVERAGEIFS(
    $D$71:$D$76,
    $E$71:$E$76,
    "<23",
    $E$71:$E$76,
    ">18.5")

这里还有个想法，如果都是简单的式子，可不可以把判断区域和判断条件放到一行。例如这样:

=AVERAGEIFS(
    $D$71:$D$76,
    $E$71:$E$76, "<23",
    $E$71:$E$76, ">18.5")

例13：

Raw:

=IFERROR(INDEX($B$81:$B$91,SMALL(IF(D$80=$A$81:$A$91,ROW($B$81:$B$91)-2,""), ROW()-2)),"")

Beautifier:

=IFERROR(
    INDEX(
        $B$81:$B$91,
        SMALL(
            IF(
                D$80 = $A$81:$A$91,
                ROW(
                    $B$81:$B$91
                ) - 2,
                ""
            ),
            ROW(
            ) - 2
        )
    ),
    ""
)

Formula Editor:

= IFERROR(
    INDEX(
        $B$81:$B$91,
        SMALL(
            IF(
                D$80 = $A$81:$A$91,
                ROW(
                    $B$81:$B$91
                )-2,
                ""
            ),
            ROW()-2
        )
    ),
    ""
)

Expect:

=IFERROR(
    INDEX(
        $B$81:$B$91,
        SMALL(
            IF(
                D$80 = $A$81:$A$91,
                ROW($B$81:$B$91) - 2,
                ""
            ),
            ROW() - 2)),
    "")