@BurdenBear 2019-03-26T13:57:35.000000Z 字数 10827 阅读 327

关于format的讨论

excel

从自然的角度看，我们进行format的目标：

不让一行公式过长
在函数调用中，要容易看出各个参数的位置。
在长连续双目运算的时候，要容易看出计算的前后顺序。
对于一些复杂的结构，我们不希望直观上看上去confusing（只看视觉重点的位置就能看出结构，有些复杂的式子还必须结合一些不同行的其他的地方的一些括号和逗号才能看清楚，但我们希望减少这种情况），也就是说，不属于同一层结构的行之间的视觉重点，我们希望他们尽量分开一点。（关于这块的讨论我还在找具体例子）

一个确定的format style —— style LC 总结:

根据目标2和3，我们现在的format style总的来说有两个大方向：
1. 函数调用换行的结构要清楚看出函数各个参数的位置（除非位置信息不重要）
2. 连续四则运算的换行结构要清楚看出计算顺序，视觉重点要保持向某个方向一致的缩进结构

考虑到这两个目标，这里先给出我总结的对连续双目运算总结的换行对齐style LC的规则：
首先我觉得"最高层视觉重点在左"或"对齐"的风格符合我们的从左到右的视觉习惯。而为了保持一致，我在这种style中不考虑"最高层视觉重点在右"的对齐方式。(left or center align style ---- style LC)
至于是选用"对齐"或"在左"的风格，当运算符级别有区别时，毫无疑问底层运算符要往右缩进；当运算符级别相同时，我们尽量选用"对齐"，来节省缩进———除非选用这种方式会导致和之前的目标4冲突。对于这种情况，我这里找到几种可能的例子:

1.在函数参数开头的地方可能让人confusing

以下两个公式结构不同，但是换行出来的结果和相近。

= IF(FOO = BAR,
     IF(FOO = BARBAR, //这里用对齐，不缩进
        MAX(0,
            IF(FOO = BARBARBAR,
               100,
               200),
            10000000000000000000000000000000000000000),
        200000000000000000000000000000000000000000000000000000 
        * 10000000000000000000000000000000000000000000000000000)
     + 3000 * 1000000000000000000000000000000000000000000000000000000
     + 2000 * 1000000000000000000000000000000000000000000000000000000
     - 1000 * 1222222222222222222222222222222222222222222222222222212
     + 10000000000000000000000000000000000000000000000000000000)

= IF(FOO = BAR,
     IF(FOO = BARBAR, //这里用对齐，不缩进
        MAX(0,
            IF(FOO = BARBARBAR,
               100,
               200),
            10000000000000000000000000000000000000000)),
       200000000000000000000000000000000000000000000000000000 
       * 10000000000000000000000000000000000000000000000000000
     + 3000 * 1000000000000000000000000000000000000000000000000000000
     + 2000 * 1000000000000000000000000000000000000000000000000000000
     - 1000 * 1222222222222222222222222222222222222222222222222222212
     + 10000000000000000000000000000000000000000000000000000000)

但是如果在第一层IF的第二个参数开始处不用"对齐"而是用"最高层在左"的规则就不同了：

= IF(FOO = BAR,
       IF(FOO = BARBAR, //这里不用对齐，缩进
          MAX(0,
              IF(FOO = BARBARBAR,
                 100,
                 200),
              10000000000000000000000000000000000000000),
          200000000000000000000000000000000000000000000000000000 
          * 10000000000000000000000000000000000000000000000000000)
     + 3000 * 1000000000000000000000000000000000000000000000000000000
     + 2000 * 1000000000000000000000000000000000000000000000000000000
     - 1000 * 1222222222222222222222222222222222222222222222222222212
     + 10000000000000000000000000000000000000000000000000000000)

= IF(FOO = BAR,
     IF(FOO = BARBAR, //这里不需要缩进
        MAX(0,
            IF(FOO = BARBARBAR,
               100,
               200),
            10000000000000000000000000000000000000000)),
       200000000000000000000000000000000000000000000000000000 
       * 10000000000000000000000000000000000000000000000000000
     + 3000 * 1000000000000000000000000000000000000000000000000000000
     + 2000 * 1000000000000000000000000000000000000000000000000000000
     - 1000 * 1222222222222222222222222222222222222222222222222222212
     + 10000000000000000000000000000000000000000000000000000000)

根据以上讨论，得到这套style的换行对齐规则：
1. 子表达式里的嵌套调用规则处理对齐。
2. 如果是表达式最高级别一层的运算符，且整个表达式是某个函数调用的参数(多个参数)，则第一个操作数处强制最高级别运算符在左。（保证可以看清函数参数的位置）
3. 如果是表达式内次高级别的运算符，当从左到右，运算符级别相同，可以运用“最高层运算符和上子树视觉重点对齐”，
这种可以适应连乘或连加的情况，节省缩进。
4. 连续双目运算符中，如果子树换行，则其高层运算符也应该换行（或者说层数越低换行惩罚越高）

另外的，也可以把这两点目标设置成惩罚的规则，将对连续双目运算符的换行后的对齐方式的选择当成新的状态转移路径。
转化成惩罚:
目标一：在函数的参数部分，如果某单个参数是多行，其中只有一行能顶着函数参数缩进对应的位置，多出的给与惩罚。
目标二: 缩进不符合双运算符层级顺序的，按逆序对数目给以惩罚。
目标三：相邻行处结构上不属于相邻的层级，但是两者视觉重点隔得太近，一般出现confusing的地方都是这种情况，但不是所有的都会导致confusing

不过我觉得如果是可以确定最优的换行方式之一的话，直接按固定规则就好，否则的话惩罚的计算和新加入的转移路径导致搜索算法增加的耗时感觉会是很可观的一项。
拿例1做例子：

= IF(FOO = BAR,
       100
     + 3000 * 1000000000000000000000000000000000000000000000000000000
     + 2000 * 1000000000000000000000000000000000000000000000000000000
     - 1000 * 1222222222222222222222222222222222222222222222222222212
     + 10000000000000000000000000000000000000000000000000000000,
     0)

考虑加上括号,由于括号的存在，其实也是能看清函数参数的位置的。

= IF(FOO = BAR,
     (100
      + 3000 * 1000000000000000000000000000000000000000000000000000000
      + 2000 * 1000000000000000000000000000000000000000000000000000000
      - 1000 * 1222222222222222222222222222222222222222222222222222212
      + 10000000000000000000000000000000000000000000000000000000),
     0)

当出现更高级别运算符（即更底层运算符子树），则“最高层运算符在左”，这样可以看清楚运算顺序

直观上的感觉，看到作为"视觉重点"的双目运算符，只需要往其右上方看，就可以找到其左操作数节点（或子树）

以下是更多的一些例子：

例16:

By Tie:

= SUMPRODUCT(AG6:AK6; $AG$4:$AK$4) / AL6 
  + (102 / 151,67 / 12)

style LC:

= SUMPRODUCT(AG6:AK6; $AG$4:$AK$4) / AL6 
  + (102 / 151,67 / 12)

例19:

By Tie:

= IF(D6 = 0;
     0;
     VLOOKUP($D6; 
             Données_Chiffrage;
             MATCH(
               BB$5;
               Données_Chiffrage_Entete;
               0);
             FALSE)) 
  * (C6) * (26 + AN6 / 12 + AQ6 / 12)

style LC:

= IF(D6 = 0;
     0;
     VLOOKUP($D6; 
             Données_Chiffrage;
             MATCH(
               BB$5;
               Données_Chiffrage_Entete;
               0);
             FALSE)) 
  * (C6)
  * (26 + AN6 / 12 + AQ6 / 12)

例25：

By Tie:

=     (SUMPRODUCT(BS9:BT9; $BS$4:$BT$4) 
       / (SUM(BN9:BP9) / 12) 
       * 1,04)
    * (1 
       + IF(AN9 = 52; 0,2; 0,2) * BO9 / SUM(BN9:BP9) 
       + 0,5 * BP9 / SUM(BN9:BP9))
  + (102 / 151,67 / 12)

style LC:

=   (SUMPRODUCT(BS9:BT9; $BS$4:$BT$4) 
     / (SUM(BN9:BP9) / 12)
     * 1,04)
    * (1 
       + IF(AN9 = 52; 0,2; 0,2) * BO9 / SUM(BN9:BP9)
       + 0,5 * BP9 / SUM(BN9:BP9)) 
  + (102 / 151,67 / 12)

例子38：

By Tie:

= SUMPRODUCT(
    ((J2 >= YEAR($C$2:$C$4)) * ((J2 <= YEAR($D$2:$D$4)) + ($D$2:$D$4 = ""))),
    SUMIFS($G$2:$G$3, $F$2:$F$3, $B$2:$B$4),
         ((DATEDIF(IF($C$2:$C$4 > DATE(J2, 12, 31), 0, $C$2:$C$4), DATE(J2, 12, 31), "m") 
             + 10000000000000000000000000000000000000000000
             - 10000000000000000000000000000000000000000000 
           > 120000000000000000000000000000000000000000000) 
         * 1200000000000000000000000000000000000000
       - IF($D$2:$D$4 <> "", MONTH(D1:D3) + 2, 0))
     + (DATEDIF(IF($C$2:$C$4 > DATE(J2, 12, 31), 0, $C$2:$C$4), DATE(J2, 12, 31), "m") + 1 <= 12) 
       * ((DATEDIF(IF($C$2:$C$4 > DATE(J2, 12, 31), 0, $C$2:$C$4), DATE(J2, 12, 31), "m") + 1) 
          -- IF($D$2:$D$4 <> "", MONTH(D1:D3) + 2, 0)))

style LC:

= SUMPRODUCT(((J2 >= YEAR($C$2:$C$4)) * ((J2 <= YEAR($D$2:$D$4)) + ($D$2:$D$4 = ""))),
             SUMIFS($G$2:$G$3, $F$2:$F$3, $B$2:$B$4),
                  ((  DATEDIF(IF($C$2:$C$4 > DATE(J2, 12, 31), 0, $C$2:$C$4), DATE(J2, 12, 31), "m") 
                  // 差别就在这里多了两个空格，我觉得可以接受
                      + 10000000000000000000000000000000000000000000
                      - 10000000000000000000000000000000000000000000 
                    > 120000000000000000000000000000000000000000000) 
                  * 1200000000000000000000000000000000000000
               - IF($D$2:$D$4 <> "", MONTH(D1:D3) + 2, 0))
             + (DATEDIF(IF($C$2:$C$4 > DATE(J2, 12, 31), 0, $C$2:$C$4), 
                        DATE(J2, 12, 31), 
                        "m") 
                + 1 <= 12) 
               * ((DATEDIF(IF($C$2:$C$4 > DATE(J2, 12, 31), 0, $C$2:$C$4), 
                                DATE(J2, 12, 31), 
                                "m") 
                   + 1)
                  -- IF($D$2:$D$4 <> "", MONTH(D1:D3) + 2, 0)))

例39：

By Tie:

这里用到了"最高级运算符在右"的风格。按之前几个例子的情况的考虑，某些情况是"最高级运算符在左"或"对齐"比较好，我感觉最好不要再混用"最高级运算符在右"的风格。

= IF(AND(
       C2 >= (LEFT(INDEX($I$1:$M$5, MATCH(B2, $I$1:$I$5, 0), MATCH(D2, $I$1:$M$1, 0)),
                   FIND("-", INDEX($I$1:$M$5, MATCH(B2, $I$1:$I$5, 0), MATCH(D2, $I$1:$M$1, 0))) 
                     - 1) 
                * 1),
       C2 <= MID(INDEX($I$1:$M$5, MATCH(B2, $I$1:$I$5, 0), MATCH(D2, $I$1:$M$1, 0)),
                 FIND("-", INDEX($I$1:$M$5, MATCH(B2, $I$1:$I$5, 0), MATCH(D2, $I$1:$M$1, 0))) 
                   + 1,
                 256) 
               * 1),
    "Yes",
    "No"
  )

style LC:

= IF(AND(
       C2 >= (LEFT(INDEX($I$1:$M$5, MATCH(B2, $I$1:$I$5, 0), MATCH(D2, $I$1:$M$1, 0)),
                     FIND("-", INDEX($I$1:$M$5, MATCH(B2, $I$1:$I$5, 0), MATCH(D2, $I$1:$M$1, 0))) 
                   - 1) //统一往右上角找左操作数，外层函数的参数结构也可以分清楚。
              * 1),
       C2 <= MID(INDEX($I$1:$M$5, MATCH(B2, $I$1:$I$5, 0), MATCH(D2, $I$1:$M$1, 0)),
                    FIND("-", INDEX($I$1:$M$5, MATCH(B2, $I$1:$I$5, 0), MATCH(D2, $I$1:$M$1, 0))) 
                 + 1,
                 256) 
             * 1),
    "Yes",
    "No"
  )

例41：

By tie:

= T(INDEX($B:$B,
          -1 + 6 + ROWS($3:3) 
            - IF(ROUNDUP(ROWS($3:3) / SUM(--($B:$B <> "")), 0) 
                   = 2,
                 SUM(--($B:$B <> "")),
                 0)))

style LC:

= T(INDEX($B:$B,
            - 1 + 6 + ROWS($3:3) 
          - IF(  ROUNDUP(ROWS($3:3) / SUM(--($B:$B <> "")), 0) 
               = 2,
               SUM(--($B:$B <> "")),
               0)))

例43：

By Tie：

= SUM(--(FREQUENCY(IF(('DAY 2'!$H$2:$H$500 = K2) 
                        * NOT('DAY 2'!$H$2:$H$500 = ""),
                      'DAY 2'!$C$2:$C$500),
                      'DAY 2'!$C$2:$C$500) 
         > 0))

style LC:

= SUM(--(FREQUENCY(IF(('DAY 2'!$H$2:$H$500 = K2) 
                       * NOT('DAY 2'!$H$2:$H$500 = ""),
                      'DAY 2'!$C$2:$C$500),
                      'DAY 2'!$C$2:$C$500) 
         > 0))

考虑一个新例子，例43-1：*

这里考察的情况是在复杂的函数的参数中，包含了层级比较多的四则运算表达式，
可以把下面例子中的B2 * SUM(C1:C500) - B1 * SUM(D1:D500)的每一部分都想象成更复杂更长的式子，我们要保证每一部分都换行分开。
而且每个运算符的操作数都比较长例子里面就没举出那么长的例子了。
我们想来看一下最高级操作符"视觉重点"和之前表达式对齐，在左，在右，哪种比较好看。

Raw:

= SUM(--(FREQUENCY(IF(B2 * SUM(C1:C500) - B1 * SUM(D1:D500)> 0,
                      'DAY 2'!$C$2:$C$500),
                      'DAY 2'!$C$2:$C$500) 
         > 0))

在左

B2之前的空格感觉有点奇怪，但从左到右对应语法树从根到叶比较自然。

= SUM(--(FREQUENCY(IF(    B2 
                          * SUM(C1:C500) 
                        - B1 
                          * SUM(D1:D500) 
                      > 0,
                      'DAY 2'!$C$2:$C$500),
                      'DAY 2'!$C$2:$C$500) 
         > 0))

对齐

看不出四则运算的层级，不好

= SUM(--(FREQUENCY(IF(B2 
                      * SUM(C1:C500) 
                      - B1 
                      * SUM(D1:D500) 
                      > 0,
                      'DAY 2'!$C$2:$C$500),
                      'DAY 2'!$C$2:$C$500) 
         > 0))

在右

缩进比较自然，但是看操作符前后的操作数的时候不如在"左"清楚。

= SUM(--(FREQUENCY(IF(B2 
                      * SUM(C1:C500) 
                        - B1 
                      * SUM(D1:D500) 
                          > 0,
                      'DAY 2'!$C$2:$C$500),
                      'DAY 2'!$C$2:$C$500) 
         > 0))

style LC:

= SUM(--(FREQUENCY(IF(    B2 
                          * SUM(C1:C500) 
                        - B1 
                          * SUM(D1:D500) 
                      > 0,
                      'DAY 2'!$C$2:$C$500),
                      'DAY 2'!$C$2:$C$500) 
         > 0))

例46

By Tie:

= ROUNDUP(IF(S5 <> "ü", 0, SUMIF(J3:J5000, R5, F3:F5000)) 
            + IF(S6 <> "ü", 0, SUMIF(J3:J5000, R6, F3:F5000)) 
            + IF(S7 <> "ü", 0, SUMIF(J3:J5000, R7, F3:F5000)) 
            + IF(S8 <> "ü", 0, SUMIF(J3:J5000, R8, F3:F5000)) 
            + IF(S9 <> "ü", 0, SUMIF(J3:J5000, R9, F3:F5000)) / 1440,
          0)

style LC:

= ROUNDUP(IF(  S5 <> "ü", 0, SUMIF(J3:J5000, R5, F3:F5000)) 
             + IF(S6 <> "ü", 0, SUMIF(J3:J5000, R6, F3:F5000)) 
             + IF(S7 <> "ü", 0, SUMIF(J3:J5000, R7, F3:F5000)) 
             + IF(S8 <> "ü", 0, SUMIF(J3:J5000, R8, F3:F5000)) 
             + IF(S9 <> "ü", 0, SUMIF(J3:J5000, R9, F3:F5000)) 
               / 1440,
             0)

例47：

By Tie:

= SUMPRODUCT(((COUNTIF(OFFSET(A1, ROW(A2:A33) - 1, 0), "*apple*") 
               + COUNTIF(OFFSET(A1, ROW(A2:A33) - 1, 0), "*seed*") 
               + COUNTIF(OFFSET(A1, ROW(A2:A33) - 1, 0), "*turf*")) 
              > 0)
             * (B2:B33 = "B"))

style LC:

= SUMPRODUCT(((COUNTIF(OFFSET(A1, ROW(A2:A33) - 1, 0), "*apple*") 
               + COUNTIF(OFFSET(A1, ROW(A2:A33) - 1, 0), "*seed*") 
               + COUNTIF(OFFSET(A1, ROW(A2:A33) - 1, 0), "*turf*")) 
              > 0)
             * (B2:B33 = "B")) // 单个参数可以考虑忽略style B的第2条规则

例48:

By Tie:

= IF(
    OR(A6 = "Brother", A6 = "Sister"),
    0.85 * IF(COUNTIF(B6:F6, "Standard day") = 5,
              100,
              IF(COUNTIF(B6:F6, "Extended day") = 5,
                 120,
                 COUNTIF(B6:F6, "Standard day") * 23 
                   + COUNTIF(B6:F6, "Extended day") * 26)),
    IF(COUNTIF(B6:F6, "Standard day") = 5,
       100,
       IF(COUNTIF(B6:F6, "Extended day") = 5,
          120,
          COUNTIF(B6:F6, "Standard day") * 23 
            + COUNTIF(B6:F6, "Extended day") * 26)))

style LC:

= IF(OR(A6 = "Brother", A6 = "Sister"),
     0.85 * IF(COUNTIF(B6:F6, "Standard day") = 5,
               100,
               IF(COUNTIF(B6:F6, "Extended day") = 5,
                  120,
                    COUNTIF(B6:F6, "Standard day") * 23 
                  + COUNTIF(B6:F6, "Extended day") * 26)),
     IF(COUNTIF(B6:F6, "Standard day") = 5,
       100,
       IF(COUNTIF(B6:F6, "Extended day") = 5,
          120,
            COUNTIF(B6:F6, "Standard day") * 23 
          + COUNTIF(B6:F6, "Extended day") * 26)))

例36：

By Tie:

= SUMPRODUCT(
      OFFSET($J$2, 0, 0, SUM(ROW()) - 1),
      OFFSET($O$2, 11 - SUM(ROW()), 0, SUM(ROW()) - 1)) 
  + SUMPRODUCT(
        OFFSET($J$2, 0, 0, SUM(ROW()) - 1),
        OFFSET($O$2, 11 - SUM(ROW()), 0, SUM(ROW()) - 1)

style LC:

= SUMPRODUCT(OFFSET($J$2, 0, 0, SUM(ROW()) - 1),
             OFFSET($O$2, 11 - SUM(ROW()), 0, SUM(ROW()) - 1)) 
  + SUMPRODUCT(OFFSET($J$2, 0, 0, SUM(ROW()) - 1),
               OFFSET($O$2, 11 - SUM(ROW()), 0, SUM(ROW()) - 1)

基本一样

例29：

Raw:

= IF(SEARCH("ABC"; G45) > 0;
     IF((DATE(((2014 + IF(VALUE(MID(G45; (SEARCH("/"; G45)) - 2; 2)) = 1; 
                          1; 
                          0)));
              (MID(G45; (SEARCH("/"; G45)) - 2; 2)); //这个冒号有点confusing
              (MID(G45; (SEARCH("/"; G45)) + 1; 2)))) 
          < B45;
        "Send";
        "Don't");
     "#VALUE!")

咋一看有点分不清";"号分割的是内层的还是外层函数的参数。

style LC:

= IF(SEARCH("ABC"; G45) > 0;
     IF(  (DATE(((2014 + IF(VALUE(MID(G45; (SEARCH("/"; G45)) - 2; 2)) = 1; 
                            1; 
                            0)));
                (MID(G45; (SEARCH("/"; G45)) - 2; 2));
                (MID(G45; (SEARCH("/"; G45)) + 1; 2)))) 
        < B45;
        "Send";
        "Don't");
     "#VALUE!")

新例1:

RAW:

=INDEX(INDIRECT("'"&CELL("contents",LookupLists!$F$1)&"'!"&"$B$3:$G$18"),MATCH(LookupLists!$E$1,INDIRECT("'"&CELL("contents",LookupLists!F1)&"'!"&"$A$3:$A$18"),0),MATCH("Sales",INDIRECT("'"&CELL("contents",LookupLists!$F$1)&"'!"&"$B$2:$G$2"),0))

style LC:

=INDEX(INDIRECT(  "'" 
                & CELL("contents",LookupLists!$F$1)
                & "'!" 
                & "$B$3:$G$18"),
       MATCH(LookupLists!$E$1,
             INDIRECT(  "'" 
                      & CELL("contents",LookupLists!F1)
                      & "'!"
                      & "$A$3:$A$18"),
             0),
       MATCH("Sales",
             INDIRECT(  "'"
                      & CELL("contents",LookupLists!$F$1)
                      & "'!"
                      & "$B$2:$G$2"),
             0))

另外就是我手动进行format的时候，一般是默认是连续的同级别的运算符要么都换要么都不换的，可不可以允许同级别的运算符有些换行有些不换行
比如这种格式：

=INDEX(INDIRECT(  "'" 
                & CELL("contents",LookupLists!$F$1)
                & "'!" & "$B$3:$G$18"),
       MATCH(LookupLists!$E$1,
             INDIRECT(  "'" 
                      & CELL("contents",LookupLists!F1)
                      & "'!" & "$A$3:$A$18"),
             0),
       MATCH("Sales",
             INDIRECT(  "'"
                      & CELL("contents",LookupLists!$F$1)
                      & "'!" & "$B$2:$G$2"),
             0))

我觉得从惩罚的角度来看这种情况应该要加一些惩罚的，但是这样处理可能也有一些好处，能节省行数

关于format的讨论

从自然的角度看，我们进行format的目标：

一个确定的format style —— style LC 总结:

1.在函数参数开头的地方可能让人confusing

例16:

By Tie:

style LC:

例19:

By Tie:

style LC:

例25：

By Tie:

style LC:

例子38：

By Tie:

style LC:

例39：

By Tie:

style LC:

例41：

By tie:

style LC:

例43：

By Tie：

style LC:

考虑一个新例子，例43-1：*

Raw:

在左

对齐

在右

style LC:

例46

By Tie:

style LC:

例47：

By Tie:

style LC:

例48:

By Tie:

style LC:

例36：

By Tie:

style LC:

例29：

Raw:

style LC:

新例1:

RAW:

style LC:

内容目录