[关闭]
@BurdenBear 2019-03-20T09:55:25.000000Z 字数 49323 阅读 576

Excel Formula Format Example (Part II)

excel

规则说明：

规则1-a： 左括号后换行

规则1-b： 左括号后不换行，之后的参数对齐到左括号后一格。

规则2-a： 右括号换行

规则2-b： 右括号不换行

规则3： 双目运算符后面的操作数是一个函数调用，则提前在运算符前换一行。

默认使用(1-a, 2-a)规则

例1：=IF(B6="red",IF(C6="small","x",""),"")

Raw:

  1. =IF(B6="red",IF(C6="small","x",""),"")

Beautifier:

  1. =IF(
  2.     B6 = "red",
  3.     IF(
  4.         C6 = "small",
  5.         "x",
  6.         ""
  7.     ),
  8.     ""
  9. )

Formula Editor:

  1. = IF(
  2.     B6 = "red",
  3.     IF(
  4.         C6 = "small",
  5.         "x",
  6.         ""
  7.     ),
  8.     ""
  9. )

Expect：

  1. = IF(
  2.     B6 = "red",
  3.     IF(
  4.         C6 = "small",
  5.         "x",
  6.         ""
  7.     ),
  8.     ""
  9. )

例2：=IF(AND(B6="red",C6="small"),"x","")

Raw:

  1. =IF(AND(B6="red",C6="small"),"x","")

Beautifier:

  1. =IF(
  2.     AND(
  3.         B6 = "red",
  4.         C6 = "small"
  5.     ),
  6.     "x",
  7.     ""
  8. )

Formula Editor:

  1. = IF(
  2.     AND(
  3.         B6 = "red",
  4.         C6 = "small"
  5.     ),
  6.     "x",
  7.     ""
  8. )

Expect:

紧凑风格

  1. = IF(
  2.     AND(B6 = "red", C6 = "small"),
  3.     "x",
  4.     ""
  5. )

强制换行

  1. = IF(
  2.     AND(
  3.         B6 = "red",
  4.         C6 = "small"
  5.     ),
  6.     "x",
  7.     ""
  8. )

例3：=IF(B6="red","x",IF(C6="small","x",""))

Raw:

  1. =IF(B6="red","x",IF(C6="small","x",""))

Beautifier:

  1. =IF(
  2.     B6 = "red",
  3.     "x",
  4.     IF(
  5.         C6 = "small",
  6.         "x",
  7.         ""
  8.     )
  9. )

Formula Editor:

  1. = IF(
  2.     B6 = "red",
  3.     "x",
  4.     IF(
  5.         C6 = "small",
  6.         "x",
  7.         ""
  8.     )
  9. )

Expect:

  1. = IF(
  2.     B6 = "red",
  3.     "x",
  4.     IF(
  5.         C6 = "small",
  6.         "x",
  7.         ""
  8.     )
  9. )

例4：=IF(OR(B6="red",C6="small"),"x","")

Raw:

  1. =IF(OR(B6="red",C6="small"),"x","")

Beautifier:

  1. =IF(
  2.     OR(
  3.         B6 = "red",
  4.         C6 = "small"
  5.     ),
  6.     "x",
  7.     ""
  8. )

Formula Editor:

  1. = IF(
  2.     OR(
  3.         B6 = "red",
  4.         C6 = "small"
  5.     ),
  6.     "x",
  7.     ""
  8. )

Expect:

紧凑风格

  1. = IF(
  2.     OR(B6 = "red", C6 = "small"),
  3.     "x",
  4.     ""
  5. )

强制换行

  1. = IF(
  2.     OR(
  3.         B6 = "red",
  4.         C6 = "small"
  5.     ),
  6.     "x",
  7.     ""
  8. )

例5：= INDEX($P$23:$P$30,MATCH($C31,$O$23:$O$30,0))

Raw:

  1. = INDEX($P$23:$P$30,MATCH($C31,$O$23:$O$30,0))

Beautifier:

  1. =INDEX(
  2.     $P$23:$P$30,
  3.     MATCH(
  4.         $C31,
  5.         $O$23:$O$30,
  6.         0
  7.     )
  8. )

Formula Editor:

  1. = INDEX(
  2.     $P$23:$P$30,
  3.     MATCH(
  4.         $C31,
  5.         $O$23:$O$30,
  6.         0
  7.     )
  8. )

Expect:

  1. = INDEX(
  2.     $P$23:$P$30,
  3.     MATCH(
  4.         $C31,
  5.         $O$23:$O$30,
  6.         0
  7.     )
  8. )

例6：=SUMIFS($N$39:$N$47,$L$39:$L$47,$C39,$M$39:$M$47,"HW1")+SUMIFS($N$39:$N$47,$L$39:$L$47,$C39,$M$39:$M$47,"HW2")

Raw:

  1. =SUMIFS($N$39:$N$47,$L$39:$L$47,$C39,$M$39:$M$47,"HW1")+SUMIFS($N$39:$N$47,$L$39:$L$47,$C39,$M$39:$M$47,"HW2")

Beautifier:

  1. =SUMIFS(
  2.     $N$39:$N$47,
  3.     $L$39:$L$47,
  4.     $C39,
  5.     $M$39:$M$47,
  6.     "HW1"
  7. ) + 
  8. SUMIFS(
  9.     $N$39:$N$47,
  10.     $L$39:$L$47,
  11.     $C39,
  12.     $M$39:$M$47,
  13.     "HW2"
  14. )

Formula Editor:

  1. = SUMIFS(
  2.     $N$39:$N$47,
  3.     $L$39:$L$47,
  4.     $C39,
  5.     $M$39:$M$47,
  6.     "HW1"
  7. ) + SUMIFS(
  8.     $N$39:$N$47,
  9.     $L$39:$L$47,
  10.     $C39,
  11.     $M$39:$M$47,
  12.     "HW2"
  13. )

Expect:

紧凑风格

  1. = SUMIFS(
  2.     $N$39:$N$47,
  3.     $L$39:$L$47, $C39,
  4.     $M$39:$M$47, "HW1"
  5. ) + SUMIFS(
  6.     $N$39:$N$47,
  7.     $L$39:$L$47, $C39,
  8.     $M$39:$M$47, "HW2"
  9. )

例7：=SUM(SUMIFS($N$39:$N$47,$L$39:$L$47,$F39,$M$39:$M$47,{"HW2","HW1"}))

Raw:

  1. =SUM(SUMIFS($N$39:$N$47,$L$39:$L$47,$F39,$M$39:$M$47,{"HW2","HW1"}))

Beautifier:

  1. =SUM(
  2.     SUMIFS(
  3.         $N$39:$N$47,
  4.         $L$39:$L$47,
  5.         $F39,
  6.         $M$39:$M$47,
  7.         {"HW2", "HW1"}
  8.     )
  9. )

Formula Editor:

  1. = SUM(
  2.     SUMIFS(
  3.         $N$39:$N$47,
  4.         $L$39:$L$47,
  5.         $F39,
  6.         $M$39:$M$47,
  7.         {"HW2",
  8.         "HW1"}
  9.     )
  10. )

Expect:

紧凑风格

  1. =SUM(
  2.     SUMIFS(
  3.         $N$39:$N$47,
  4.         $L$39:$L$47, $F39,
  5.         $M$39:$M$47, {"HW2", "HW1"}
  6.     )
  7. )

例8：=IFS(B2>10,"L1",B2>20,"L2",B2>30,"L3",B2>40,"L4",TRUE,"L5")

Raw:

  1. =IFS(B2>10,"L1",B2>20,"L2",B2>30,"L3",B2>40,"L4",TRUE,"L5")

Beautifier:

  1. =IFS(
  2.     B2 > 10,
  3.     "L1",
  4.     B2 > 20,
  5.     "L2",
  6.     B2 > 30,
  7.     "L3",
  8.     B2 > 40,
  9.     "L4",
  10.     TRUE,
  11.     "L5"
  12. )

Formula Editor

  1. = IFS(
  2.     B2 > 10,
  3.     "L1",
  4.     B2 > 20,
  5.     "L2",
  6.     B2 > 30,
  7.     "L3",
  8.     B2 > 40,
  9.     "L4",
  10.     TRUE,
  11.     "L5"
  12. )

Expect:

紧凑风格

  1. = IFS(
  2.     B2 > 10, "L1",
  3.     B2 > 20, "L2",
  4.     B2 > 30, "L3",
  5.     B2 > 40, "L4",
  6.     TRUE, "L5"
  7. )

例9：=SUMIF('Base données Market'!$A:$A;$A6;'Base données Market'!$D:$D)

Raw:

  1. =SUMIF('Base données Market'!$A:$A;$A6;'Base données Market'!$D:$D)

Beatifier:

  1. =SUMIF(
  2.     'Base données Market'!$A:$A;
  3.     $A6;
  4.     'Base données Market'!$D:$D
  5. )

Formula Editor:

  1. = SUMIF(
  2.     'Base données Market'!$A:$A;
  3.     $A6;
  4.     'Base données Market'!$D:$D
  5. )

Expect:

  1. = SUMIF(
  2.     'Base données Market'!$A:$A;
  3.     $A6;
  4.     'Base données Market'!$D:$D
  5. )

例10: =IF($C6<='Données Chiffrage'!$A$7;'Données Chiffrage'!$B$7;IF($C6<='Données Chiffrage'!$A$8;'Données Chiffrage'!$B$8;IF($C6<='Données Chiffrage'!$A$9;'Données Chiffrage'!$B$9;IF($C6<='Données Chiffrage'!$A$10;'Données Chiffrage'!$B$10;IF(C6<='Données Chiffrage'!$A$11;'Données Chiffrage'!$B$11;IF($C6<='Données Chiffrage'!$A$12;'Données Chiffrage'!$B$12;IF($C6<='Données Chiffrage'!$A$21;'Données Chiffrage'!$B$16;FALSE)))))))

Raw:

  1. =IF($C6<='Données Chiffrage'!$A$7;'Données Chiffrage'!$B$7;IF($C6<='Données Chiffrage'!$A$8;'Données Chiffrage'!$B$8;IF($C6<='Données Chiffrage'!$A$9;'Données Chiffrage'!$B$9;IF($C6<='Données Chiffrage'!$A$10;'Données Chiffrage'!$B$10;IF(C6<='Données Chiffrage'!$A$11;'Données Chiffrage'!$B$11;IF($C6<='Données Chiffrage'!$A$12;'Données Chiffrage'!$B$12;IF($C6<='Données Chiffrage'!$A$21;'Données Chiffrage'!$B$16;FALSE)))))))

Formula Editor：

  1. = IF(
  2.     $C6 <= 'Données Chiffrage'!$A$7;
  3.     'Données Chiffrage'!$B$7;
  4.     IF(
  5.         $C6 <= 'Données Chiffrage'!$A$8;
  6.         'Données Chiffrage'!$B$8;
  7.         IF(
  8.             $C6 <= 'Données Chiffrage'!$A$9;
  9.             'Données Chiffrage'!$B$9;
  10.             IF(
  11.                 $C6 <= 'Données Chiffrage'!$A$10;
  12.                 'Données Chiffrage'!$B$10;
  13.                 IF(
  14.                     C6 <= 'Données Chiffrage'!$A$11;
  15.                     'Données Chiffrage'!$B$11;
  16.                     IF(
  17.                         $C6 <= 'Données Chiffrage'!$A$12;
  18.                         'Données Chiffrage'!$B$12;
  19.                         IF(
  20.                             $C6 <= 'Données Chiffrage'!$A$21;
  21.                             'Données Chiffrage'!$B$16;
  22.                             FALSE
  23.                         )
  24.                     )
  25.                 )
  26.             )
  27.         )
  28.     )
  29. )

Expect:

  1. = IF(
  2.     $C6 <= 'Données Chiffrage'!$A$7;
  3.     'Données Chiffrage'!$B$7;
  4.     IF(
  5.         $C6 <= 'Données Chiffrage'!$A$8;
  6.         'Données Chiffrage'!$B$8;
  7.         IF(
  8.             $C6 <= 'Données Chiffrage'!$A$9;
  9.             'Données Chiffrage'!$B$9;
  10.             IF(
  11.                 $C6 <= 'Données Chiffrage'!$A$10;
  12.                 'Données Chiffrage'!$B$10;
  13.                 IF(
  14.                     C6 <= 'Données Chiffrage'!$A$11;
  15.                     'Données Chiffrage'!$B$11;
  16.                     IF(
  17.                         $C6 <= 'Données Chiffrage'!$A$12;
  18.                         'Données Chiffrage'!$B$12;
  19.                         IF(
  20.                             $C6 <= 'Données Chiffrage'!$A$21;
  21.                             'Données Chiffrage'!$B$16;
  22.                             FALSE
  23.                         )
  24.                     )
  25.                 )
  26.             )
  27.         )
  28.     )
  29. )

例11: =IF(F6>0;IF(F6<'Données Chiffrage'!$A$30;'Données Chiffrage'!$B$30;IF(F6<'Données Chiffrage'!$A$31;'Données Chiffrage'!$B$31;IF(F6<'Données Chiffrage'!$A$32;'Données Chiffrage'!$B$32;IF(F6<'Données Chiffrage'!$A$33;'Données Chiffrage'!$B$33;IF(F6<'Données Chiffrage'!$A$34;'Données Chiffrage'!$B$34;IF(F6<'Données Chiffrage'!$A$35;'Données Chiffrage'!$B$35;IF(F6<'Données Chiffrage'!$A$36;'Données Chiffrage'!$B$36;IF(F6<'Données Chiffrage'!$A$37;'Données Chiffrage'!$B$37;FALSE))))))));0)

  1. =IF(F6>0;IF(F6<'Données Chiffrage'!$A$30;'Données Chiffrage'!$B$30;IF(F6<'Données Chiffrage'!$A$31;'Données Chiffrage'!$B$31;IF(F6<'Données Chiffrage'!$A$32;'Données Chiffrage'!$B$32;IF(F6<'Données Chiffrage'!$A$33;'Données Chiffrage'!$B$33;IF(F6<'Données Chiffrage'!$A$34;'Données Chiffrage'!$B$34;IF(F6<'Données Chiffrage'!$A$35;'Données Chiffrage'!$B$35;IF(F6<'Données Chiffrage'!$A$36;'Données Chiffrage'!$B$36;IF(F6<'Données Chiffrage'!$A$37;'Données Chiffrage'!$B$37;FALSE))))))));0)
  1. = IF(
  2.     F6 > 0;
  3.     IF(
  4.         F6 < 'Données Chiffrage'!$A$30;
  5.         'Données Chiffrage'!$B$30;
  6.         IF(
  7.             F6 < 'Données Chiffrage'!$A$31;
  8.             'Données Chiffrage'!$B$31;
  9.             IF(
  10.                 F6 < 'Données Chiffrage'!$A$32;
  11.                 'Données Chiffrage'!$B$32;
  12.                 IF(
  13.                     F6 < 'Données Chiffrage'!$A$33;
  14.                     'Données Chiffrage'!$B$33;
  15.                     IF(
  16.                         F6 < 'Données Chiffrage'!$A$34;
  17.                         'Données Chiffrage'!$B$34;
  18.                         IF(
  19.                             F6 < 'Données Chiffrage'!$A$35;
  20.                             'Données Chiffrage'!$B$35;
  21.                             IF(
  22.                                 F6 < 'Données Chiffrage'!$A$36;
  23.                                 'Données Chiffrage'!$B$36;
  24.                                 IF(
  25.                                     F6 < 'Données Chiffrage'!$A$37;
  26.                                     'Données Chiffrage'!$B$37;
  27.                                     FALSE
  28.                                 )
  29.                             )
  30.                         )
  31.                     )
  32.                 )
  33.             )
  34.         )
  35.     );
  36.     0
  37. )

Expect:

  1. = IF(
  2.     F6 > 0;
  3.     IF(
  4.         F6 < 'Données Chiffrage'!$A$30;
  5.         'Données Chiffrage'!$B$30;
  6.         IF(
  7.             F6 < 'Données Chiffrage'!$A$31;
  8.             'Données Chiffrage'!$B$31;
  9.             IF(
  10.                 F6 < 'Données Chiffrage'!$A$32;
  11.                 'Données Chiffrage'!$B$32;
  12.                 IF(
  13.                     F6 < 'Données Chiffrage'!$A$33;
  14.                     'Données Chiffrage'!$B$33;
  15.                     IF(
  16.                         F6 < 'Données Chiffrage'!$A$34;
  17.                         'Données Chiffrage'!$B$34;
  18.                         IF(
  19.                             F6 < 'Données Chiffrage'!$A$35;
  20.                             'Données Chiffrage'!$B$35;
  21.                             IF(
  22.                                 F6 < 'Données Chiffrage'!$A$36;
  23.                                 'Données Chiffrage'!$B$36;
  24.                                 IF(
  25.                                     F6 < 'Données Chiffrage'!$A$37;
  26.                                     'Données Chiffrage'!$B$37;
  27.                                     FALSE
  28.                                 )
  29.                             )
  30.                         )
  31.                     )
  32.                 )
  33.             )
  34.         )
  35.     );
  36.     0
  37. )

例12：=IF(G6=0;0;VLOOKUP(G6;'Données Chiffrage'!$B$30:$L$39;2;0))

Raw:

  1. =IF(G6=0;0;VLOOKUP(G6;'Données Chiffrage'!$B$30:$L$39;2;0))

Formula Editor:

  1. = IF(
  2.     G6 = 0;
  3.     0;
  4.     VLOOKUP(
  5.         G6;
  6.         'Données Chiffrage'!$B$30:$L$39;
  7.         2;
  8.         0
  9.     )
  10. )

Expect:

  1. = IF(
  2.     G6 = 0;
  3.     0;
  4.     VLOOKUP(
  5.         G6;
  6.         'Données Chiffrage'!$B$30:$L$39;
  7.         2;
  8.         0
  9.     )
  10. )

例13：=SUMIFS('Base données Market'!$W:$W;'Base données Market'!$A:$A;$A6;'Base données Market'!$I:$I;M$5;'Base données Market'!$N:$N;M$4)

Raw:

  1. =SUMIFS('Base données Market'!$W:$W;'Base données Market'!$A:$A;$A6;'Base données Market'!$I:$I;M$5;'Base données Market'!$N:$N;M$4)

Formula Editor:

  1. = SUMIFS(
  2.     'Base données Market'!$W:$W;
  3.     'Base données Market'!$A:$A;
  4.     $A6;
  5.     'Base données Market'!$I:$I;
  6.     M$5;
  7.     'Base données Market'!$N:$N;
  8.     M$4
  9. )Sheet1!B2

Expect:

紧凑风格

  1. = SUMIFS(
  2.     'Base données Market'!$W:$W;
  3.     'Base données Market'!$A:$A; $A6;
  4.     'Base données Market'!$I:$I; M$5;
  5.     'Base données Market'!$N:$N; M$4
  6. )Sheet1!B2

强制换行

  1. = SUMIFS(
  2.     'Base données Market'!$W:$W;
  3.     'Base données Market'!$A:$A;
  4.     $A6;
  5.     'Base données Market'!$I:$I;
  6.     M$5;
  7.     'Base données Market'!$N:$N;
  8.     M$4
  9. )Sheet1!B2

例14: =IF(VLOOKUP($D6;Données_Chiffrage;MATCH("CE";Données_Chiffrage_Entete;0);0)="Non";0;VLOOKUP($D6;Données_Chiffrage;MATCH(AG$5;Données_Chiffrage_Entete;0);0))

Raw:

  1. =IF(VLOOKUP($D6;Données_Chiffrage;MATCH("CE";Données_Chiffrage_Entete;0);0)="Non";0;VLOOKUP($D6;Données_Chiffrage;MATCH(AG$5;Données_Chiffrage_Entete;0);0))

Formula Editor:

  1. = IF(
  2.     VLOOKUP(
  3.         $D6;
  4.         Données_Chiffrage;
  5.         MATCH(
  6.             "CE";
  7.             Données_Chiffrage_Entete;
  8.             0
  9.         );
  10.         0
  11.     ) = "Non";
  12.     0;
  13.     VLOOKUP(
  14.     	$D6;
  15.         Données_Chiffrage;
  16.         MATCH(
  17.             AG$5;
  18.             Données_Chiffrage_Entete;
  19.             0
  20.         );
  21.         0
  22.     )
  23. )

Expect:

  1. = IF(
  2.     VLOOKUP(
  3.         $D6;
  4.         Données_Chiffrage;
  5.         MATCH(
  6.             "CE";
  7.             Données_Chiffrage_Entete;
  8.             0
  9.         );
  10.         0
  11.     ) = "Non";
  12.     0;
  13.     VLOOKUP(
  14.     	$D6;
  15.         Données_Chiffrage;
  16.         MATCH(
  17.             AG$5;
  18.             Données_Chiffrage_Entete;
  19.             0
  20.         );
  21.         0
  22.     )
  23. )

例15: =IF(VLOOKUP($D6;Données_Chiffrage;MATCH("CE";Données_Chiffrage_Entete;0);0)="Non";0;AE6-AG6+(AC6/26)/3)

Raw:

  1. =IF(VLOOKUP($D6;Données_Chiffrage;MATCH("CE";Données_Chiffrage_Entete;0);0)="Non";0;AE6-AG6+(AC6/26)/3)

Formula Editor:

  1. = IF(
  2.     VLOOKUP(
  3.         $D6;
  4.         Données_Chiffrage;
  5.         MATCH(
  6.             "CE";
  7.             Données_Chiffrage_Entete;
  8.             0
  9.         );
  10.         0
  11.     ) = "Non";
  12.     0;
  13.     AE6 - AG6 + (
  14.         AC6 / 26
  15.     ) / 3
  16. )

Expect:

  1. = IF(
  2.     VLOOKUP(
  3.         $D6;
  4.         Données_Chiffrage;
  5.         MATCH(
  6.             "CE";
  7.             Données_Chiffrage_Entete;
  8.             0
  9.         );
  10.         0
  11.     ) = "Non";
  12.     0;
  13.     AE6 - AG6 + (AC6 / 26) / 3
  14. )

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

Raw:

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

Formula Editor:

  1. = SUMPRODUCT(
  2.     AG6:AK6;
  3.     $AG$4:$AK$4
  4. ) / AL6 + (
  5.     102 / 151,
  6.     67 / 12
  7. )

Expect:

  1. = SUMPRODUCT(
  2.     AG6:AK6;
  3.     $AG$4:$AK$4
  4. ) / AL6 + (
  5.     102 / 151,
  6.     67 / 12
  7. )

subexpession中有分割符，则强制换行

例17: =SUMIFS('Base données Market'!$AB:$AB;'Base données Market'!$A:$A;$A6)/COUNTIFS('Base données Market'!$AB:$AB;">0";'Base données Market'!$A:$A;$A6)

Raw:

  1. =SUMIFS('Base données Market'!$AB:$AB;'Base données Market'!$A:$A;$A6)/COUNTIFS('Base données Market'!$AB:$AB;">0";'Base données Market'!$A:$A;$A6)

Formula Editor:

  1. = SUMIFS(
  2.     'Base données Market'!$AB:$AB;
  3.     'Base données Market'!$A:$A;
  4.     $A6
  5. ) / COUNTIFS(
  6.     'Base données Market'!$AB:$AB;
  7.     ">0";
  8.     'Base données Market'!$A:$A;
  9.     $A6
  10. )

Expect:

紧凑风格

  1. = SUMIFS(
  2.     'Base données Market'!$AB:$AB;
  3.     'Base données Market'!$A:$A; $A6
  4. ) / COUNTIFS(
  5.     'Base données Market'!$AB:$AB; ">0";
  6.     'Base données Market'!$A:$A; $A6
  7. )

强制换行

  1. = SUMIFS(
  2.     'Base données Market'!$AB:$AB;
  3.     'Base données Market'!$A:$A;
  4.     $A6
  5. ) / COUNTIFS(
  6.     'Base données Market'!$AB:$AB;
  7.     ">0";
  8.     'Base données Market'!$A:$A;
  9.     $A6
  10. )

例18: =IF(G6=0;0;VLOOKUP($G6;Données_Chiffrage;MATCH(AY$5;Données_Chiffrage_Entete;0);FALSE))/2

Raw:

  1. =IF(G6=0;0;VLOOKUP($G6;Données_Chiffrage;MATCH(AY$5;Données_Chiffrage_Entete;0);FALSE))/2

Formula Editor:

  1. = IF(
  2.     G6 = 0;
  3.     0;
  4.     VLOOKUP(
  5.         $G6;
  6.     	Données_Chiffrage;
  7.     	MATCH(
  8.     	    AY$5;
  9.             Données_Chiffrage_Entete;
  10.             0
  11.         );
  12.         FALSE
  13.     )
  14. ) / 2

Expect:

  1. = IF(
  2.     G6 = 0;
  3.     0;
  4.     VLOOKUP(
  5.         $G6;
  6.     	Données_Chiffrage;
  7.     	MATCH(
  8.     	    AY$5;
  9.             Données_Chiffrage_Entete;
  10.             0
  11.         );
  12.         FALSE
  13.     )
  14. ) / 2

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

Raw:

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

Formula Editor:

  1. = IF(
  2.     D6 = 0;
  3.     0;
  4.     VLOOKUP(
  5.         $D6;
  6.         Données_Chiffrage;
  7.         MATCH(
  8.             BB$5;
  9.             Données_Chiffrage_Entete;
  10.             0
  11.         );
  12.         FALSE
  13.     )
  14. ) * (
  15.     C6
  16. ) * (
  17.     26 + AN6 / 12 + AQ6 / 12
  18. )

Expect:

  1. = IF(
  2.     D6 = 0;
  3.     0;
  4.     VLOOKUP(
  5.         $D6;
  6.         Données_Chiffrage;
  7.         MATCH(
  8.             BB$5;
  9.             Données_Chiffrage_Entete;
  10.             0
  11.         );
  12.         FALSE
  13.     )
  14. ) * (C6) * (26 + AN6 / 12 + AQ6 / 12)

例20: =SUMIFS('Base données Market'!$S:$S;'Base données Market'!$A:$A;$A6;'Base données Market'!$H:$H;23)+SUMIFS('Base données Market'!$S:$S;'Base données Market'!$A:$A;$A6;'Base données Market'!$H:$H;25)+SUMIFS('Base données Market'!$S:$S;'Base données Market'!$A:$A;$A6;'Base données Market'!$H:$H;26)+SUMIFS('Base données Market'!$S:$S;'Base données Market'!$A:$A;$A6;'Base données Market'!$H:$H;29)+SUMIFS('Base données Market'!$S:$S;'Base données Market'!$A:$A;$A6;'Base données Market'!$H:$H;35)

Raw:

  1. =SUMIFS('Base données Market'!$S:$S;'Base données Market'!$A:$A;$A6;'Base données Market'!$H:$H;23)+SUMIFS('Base données Market'!$S:$S;'Base données Market'!$A:$A;$A6;'Base données Market'!$H:$H;25)+SUMIFS('Base données Market'!$S:$S;'Base données Market'!$A:$A;$A6;'Base données Market'!$H:$H;26)+SUMIFS('Base données Market'!$S:$S;'Base données Market'!$A:$A;$A6;'Base données Market'!$H:$H;29)+SUMIFS('Base données Market'!$S:$S;'Base données Market'!$A:$A;$A6;'Base données Market'!$H:$H;35)

Formula Editor:

  1. = SUMIFS(
  2.     'Base données Market'!$S:$S;
  3.     'Base données Market'!$A:$A;
  4.     $A6;
  5.     'Base données Market'!$H:$H;
  6.     23
  7. ) + SUMIFS(
  8.     'Base données Market'!$S:$S;
  9.     'Base données Market'!$A:$A;
  10.     $A6;
  11.     'Base données Market'!$H:$H;
  12.     25
  13. ) + SUMIFS(
  14.     'Base données Market'!$S:$S;
  15.     'Base données Market'!$A:$A;
  16.     $A6;
  17.     'Base données Market'!$H:$H;
  18.     26
  19. ) + SUMIFS(
  20.     'Base données Market'!$S:$S;
  21.     'Base données Market'!$A:$A;
  22.     $A6;
  23.     'Base données Market'!$H:$H;
  24.     29
  25. ) + SUMIFS(
  26.     'Base données Market'!$S:$S;
  27.     'Base données Market'!$A:$A;
  28.     $A6;
  29.     'Base données Market'!$H:$H;
  30.     35
  31. )

Expect:

紧凑风格

  1. = SUMIFS(
  2.     'Base données Market'!$S:$S;
  3.     'Base données Market'!$A:$A; $A6;
  4.     'Base données Market'!$H:$H; 23
  5. ) + SUMIFS(
  6.     'Base données Market'!$S:$S;
  7.     'Base données Market'!$A:$A; $A6;
  8.     'Base données Market'!$H:$H; 25
  9. ) + SUMIFS(
  10.     'Base données Market'!$S:$S;
  11.     'Base données Market'!$A:$A; $A6;
  12.     'Base données Market'!$H:$H; 26
  13. ) + SUMIFS(
  14.     'Base données Market'!$S:$S;
  15.     'Base données Market'!$A:$A; $A6;
  16.     'Base données Market'!$H:$H; 29
  17. ) + SUMIFS(
  18.     'Base données Market'!$S:$S;
  19.     'Base données Market'!$A:$A; $A6;
  20.     'Base données Market'!$H:$H; 35
  21. )

例21：=+W4789*IF(AND(N4789="Avant ouverture";I4789="SDV");VLOOKUP($A4789;Market_Etude;MATCH("Coefficient Ajustement";Market_Entete_Etude;FALSE);FALSE);1)

Raw:

  1. =+W4789*IF(AND(N4789="Avant ouverture";I4789="SDV");VLOOKUP($A4789;Market_Etude;MATCH("Coefficient Ajustement";Market_Entete_Etude;FALSE);FALSE);1)

Formula Editor:

  1. =+ W4789 * IF(
  2.     AND(
  3.         N4789 = "Avant ouverture";
  4.         I4789 = "SDV"
  5.     );
  6.     VLOOKUP(
  7.         $A4789;
  8.         Market_Etude;
  9.         MATCH(
  10.             "Coefficient Ajustement";
  11.             Market_Entete_Etude;
  12.             FALSE
  13.         );
  14.         FALSE
  15.     );
  16.     1
  17. )

Expect:

紧凑风格

  1. =+ W4789 * IF(
  2.     AND(N4789 = "Avant ouverture"; I4789 = "SDV");
  3.     VLOOKUP(
  4.         $A4789;
  5.         Market_Etude;
  6.         MATCH(
  7.             "Coefficient Ajustement";
  8.             Market_Entete_Etude;
  9.             FALSE
  10.         );
  11.         FALSE
  12.     );
  13.     1
  14. )

强制换行

  1. =+ W4789 * IF(
  2.     AND(
  3.         N4789 = "Avant ouverture";
  4.         I4789 = "SDV"
  5.     );
  6.     VLOOKUP(
  7.         $A4789;
  8.         Market_Etude;
  9.         MATCH(
  10.             "Coefficient Ajustement";
  11.             Market_Entete_Etude;
  12.             FALSE
  13.         );
  14.         FALSE
  15.     );
  16.     1
  17. )

例22：=IF(SUMIFS('Base données Hyper'!R:R;'Base données Hyper'!A:A;$A9;'Base données Hyper'!H:H;35)>0;"Oui";"Non")

Raw:

  1. =IF(SUMIFS('Base données Hyper'!R:R;'Base données Hyper'!A:A;$A9;'Base données Hyper'!H:H;35)>0;"Oui";"Non")

Formula Editor:

  1. = IF(
  2.     SUMIFS(
  3.         'Base données Hyper'!R:R;
  4.         'Base données Hyper'!A:A;
  5.         $A9;
  6.         'Base données Hyper'!H:H;
  7.         35
  8.     ) > 0;
  9.     "Oui";
  10.     "Non"
  11. )

Expect:

紧凑风格

  1. = IF(
  2.     SUMIFS(
  3.         'Base données Hyper'!R:R;
  4.         'Base données Hyper'!A:A; $A9;
  5.         'Base données Hyper'!H:H; 35
  6.     ) > 0;
  7.     "Oui";
  8.     "Non"
  9. )

强制换行

  1. = IF(
  2.     SUMIFS(
  3.         'Base données Hyper'!R:R;
  4.         'Base données Hyper'!A:A;
  5.         $A9;
  6.         'Base données Hyper'!H:H;
  7.         35
  8.     ) > 0;
  9.     "Oui";
  10.     "Non"
  11. )

例23：=IF(VLOOKUP($D9;Données_Chiffrage;MATCH("CE";Données_Chiffrage_Entete;0);0)="Non";0;VLOOKUP($D9;Données_Chiffrage;MATCH(AG$5;Données_Chiffrage_Entete;0);0))

Raw:

  1. =IF(VLOOKUP($D9;Données_Chiffrage;MATCH("CE";Données_Chiffrage_Entete;0);0)="Non";0;VLOOKUP($D9;Données_Chiffrage;MATCH(AG$5;Données_Chiffrage_Entete;0);0))

Formula Editor:

  1. = IF(
  2.     VLOOKUP(
  3.         $D9;
  4.         Données_Chiffrage;
  5.         MATCH(
  6.             "CE";
  7.             Données_Chiffrage_Entete;
  8.             0
  9.         );
  10.         0
  11.     ) = "Non";
  12.     0;
  13.     VLOOKUP(
  14.     	$D9;
  15.         Données_Chiffrage;
  16.         MATCH(
  17.             AG$5;
  18.             Données_Chiffrage_Entete;
  19.             0
  20.         );
  21.         0
  22.     )
  23. )

Expect:

  1. = IF(
  2.     VLOOKUP(
  3.         $D9;
  4.         Données_Chiffrage;
  5.         MATCH(
  6.             "CE";
  7.             Données_Chiffrage_Entete;
  8.             0
  9.         );
  10.         0
  11.     ) = "Non";
  12.     0;
  13.     VLOOKUP(
  14.     	$D9;
  15.         Données_Chiffrage;
  16.         MATCH(
  17.             AG$5;
  18.             Données_Chiffrage_Entete;
  19.             0
  20.         );
  21.         0
  22.     )
  23. )

例24：=IFERROR(SUMIFS('Base données Hyper'!$Z:$Z;'Base données Hyper'!$A:$A;$A9)/COUNTIFS('Base données Hyper'!$Z:$Z;">0";'Base données Hyper'!$A:$A;$A9);0)

Raw:

  1. =IFERROR(SUMIFS('Base données Hyper'!$Z:$Z;'Base données Hyper'!$A:$A;$A9)/COUNTIFS('Base données Hyper'!$Z:$Z;">0";'Base données Hyper'!$A:$A;$A9);0)

Formula Editor:

  1. = IFERROR(
  2.     SUMIFS(
  3.         'Base données Hyper'!$Z:$Z;
  4.         'Base données Hyper'!$A:$A;
  5.         $A9
  6.     ) / COUNTIFS(
  7.         'Base données Hyper'!$Z:$Z;
  8.         ">0";
  9.         'Base données Hyper'!$A:$A;
  10.         $A9
  11.     );
  12.     0
  13. )

Expect:

紧凑风格

  1. = IFERROR(
  2.     SUMIFS(
  3.         'Base données Hyper'!$Z:$Z;
  4.         'Base données Hyper'!$A:$A; $A9
  5.     ) / COUNTIFS(
  6.         'Base données Hyper'!$Z:$Z; ">0";
  7.         'Base données Hyper'!$A:$A; $A9
  8.     );
  9.     0
  10. )

强制换行

  1. = IFERROR(
  2.     SUMIFS(
  3.         'Base données Hyper'!$Z:$Z;
  4.         'Base données Hyper'!$A:$A;
  5.         $A9
  6.     ) / COUNTIFS(
  7.         'Base données Hyper'!$Z:$Z;
  8.         ">0";
  9.         'Base données Hyper'!$A:$A;
  10.         $A9
  11.     );
  12.     0
  13. )

例25：=(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)

Raw:

  1. =(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)

Formula Editor:

  1. = (
  2.     SUMPRODUCT(
  3.         BS9:BT9;
  4.         $BS$4:$BT$4
  5.     ) / (
  6.         SUM(
  7.             BN9:BP9
  8.         ) / 12
  9.     ) * 1,
  10.     04
  11. ) * (
  12.     1 + IF(
  13.         AN9 = 52;
  14.         0,
  15.         2;
  16.         0,
  17.         2
  18.     ) * BO9 / SUM(
  19.         BN9:BP9
  20.     )+0,
  21.     5 * BP9 / SUM(
  22.         BN9:BP9
  23.     )
  24. ) + (
  25.     102 / 151,
  26.     67 / 12
  27. )

Expect:

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

SUM只有单个参数的时候就不换行了

例26: =IF(OR(ISNUMBER(SEARCH("dog";E2));ISNUMBER(SEARCH("cat";E2));ISNUMBER(SEARCH("pet";E2)));"8.50";"0")

Raw:

  1. =IF(OR(ISNUMBER(SEARCH("dog";E2));ISNUMBER(SEARCH("cat";E2));ISNUMBER(SEARCH("pet";E2)));"8.50";"0")

Formula Editor:

  1. = IF(
  2.     OR(
  3.         ISNUMBER(
  4.             SEARCH(
  5.                 "dog";
  6.                 E2
  7.             )
  8.         );
  9.         ISNUMBER(
  10.             SEARCH(
  11.                 "cat";
  12.                 E2
  13.             )
  14.         );
  15.         ISNUMBER(
  16.             SEARCH(
  17.                 "pet";
  18.                 E2
  19.             )
  20.         )
  21.     );
  22.     "8.50";
  23.     "0"
  24. )

Expect:

  1. = IF(
  2.     OR(
  3.         ISNUMBER(SEARCH("dog"; E2));
  4.         ISNUMBER(SEARCH("cat"; E2));
  5.         ISNUMBER(SEARCH("pet"; E2))
  6.     );
  7.     "8.50";
  8.     "0"
  9. )

或者

  1. = IF(
  2.     OR(
  3.         ISNUMBER(SEARCH(
  4.             "dog";
  5.             E2
  6.         ));
  7.         ISNUMBER(SEARCH(
  8.             "cat";
  9.             E2
  10.         ));
  11.         ISNUMBER(SEARCH(
  12.             "pet";
  13.             E2
  14.         ))
  15.     );
  16.     "8.50";
  17.     "0"
  18. )

ISNUMBER确定是单目运算符，不再换行。
SEARCH里面如果全部是简单的表达式没有嵌套函数调用，可以考虑不换行

例27: =IF(IFERROR(INDEX(Table1;MATCH($A2;Response_ID;0);MATCH(Q$1;header;0));"")="";"";(INDEX(Table1;MATCH($A2;Response_ID;0);MATCH(Q$1;header;0))))

Raw:

  1. =IF(IFERROR(INDEX(Table1;MATCH($A2;Response_ID;0);MATCH(Q$1;header;0));"")="";"";(INDEX(Table1;MATCH($A2;Response_ID;0);MATCH(Q$1;header;0))))

Formula Editor:

  1. = IF(
  2.     IFERROR(
  3.         INDEX(
  4.             Table1;
  5.             MATCH(
  6.                 $A2;
  7.                 Response_ID;
  8.                 0
  9.             );
  10.             MATCH(
  11.                 Q$1;
  12.                 header;
  13.                 0
  14.             )
  15.         );
  16.         ""
  17.     ) = "";
  18.     "";(
  20.         INDEX(
  21.             Table1;
  22.             MATCH(
  23.                 $A2;
  24.                 Response_ID;
  25.                 0
  26.             );
  27.             MATCH(
  28.             	Q$1;
  29.                 header;
  30.                 0
  31.             )
  32.         )
  33.     )
  34. )

Expect:

  1. = IF(
  2.     IFERROR(
  3.         INDEX(
  4.             Table1;
  5.             MATCH(
  6.                 $A2;
  7.                 Response_ID;
  8.                 0
  9.             );
  10.             MATCH(
  11.                 Q$1;
  12.                 header;
  13.                 0
  14.             )
  15.         );
  16.         ""
  17.     ) = "";
  18.     "";
  19.     (INDEX(
  20.         Table1;
  21.         MATCH(
  22.             $A2;
  23.             Response_ID;
  24.             0
  25.         );
  26.         MATCH(
  27.             Q$1;
  28.             header;
  29.             0
  30.         )
  31.     ))
  32. )

例28: ="Referrals: "&INDEX(INDIRECT("'"&LookupLists!$F$1&"'!"&"AE3:AE184");MATCH(LookupLists!$E$1;INDIRECT("'"&LookupLists!$F$1&"'!"&"A3:A184");0);MATCH("Referrals";INDIRECT("'"&LookupLists!$F$1&"'!"&"AE2:AE184");0))

Raw:

  1. ="Referrals: "&INDEX(INDIRECT("'"&LookupLists!$F$1&"'!"&"AE3:AE184");MATCH(LookupLists!$E$1;INDIRECT("'"&LookupLists!$F$1&"'!"&"A3:A184");0);MATCH("Referrals";INDIRECT("'"&LookupLists!$F$1&"'!"&"AE2:AE184");0))

Formula Editor:

  1. = "Referrals: "&INDEX(
  2.     INDIRECT(
  3.         "'"&LookupLists!$F$1&"'!"&"AE3:AE184"
  4.     );
  5.     MATCH(
  6.         LookupLists!$E$1;
  7.         INDIRECT(
  8.             "'"&LookupLists!$F$1&"'!"&"A3:A184"
  9.         );
  10.         0
  11.     );
  12.     MATCH(
  13.         "Referrals";
  14.         INDIRECT(
  15.             "'"&LookupLists!$F$1&"'!"&"AE2:AE184"
  16.         );
  17.         0
  18.     )
  19. )

Expect:

  1. = "Referrals: "&INDEX(
  2.     INDIRECT("'"&LookupLists!$F$1&"'!"&"AE3:AE184");
  3.     MATCH(
  4.         LookupLists!$E$1;
  5.         INDIRECT("'"&LookupLists!$F$1&"'!"&"A3:A184");
  6.         0
  7.     );
  8.     MATCH(
  9.         "Referrals";
  10.         INDIRECT("'"&LookupLists!$F$1&"'!"&"AE2:AE184");
  11.         0
  12.     )
  13. )

INDIRECT也是一个单目运算符，可以考虑不换行。

例29: =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!")

Raw:

  1. =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!")

Formula Editor:

  1. = IF(
  2.     SEARCH(
  3.         "ABC";
  4.         G45
  5.     ) > 0;
  6.     IF(
  7.         (
  8.             DATE(
  9.                 (
  10.                     (
  11.                         2014 + IF(
  12.                             VALUE(
  13.                                 MID(
  14.                                     G45;(
  16.                                         SEARCH(
  17.                                             "/";
  18.                                             G45
  19.                                         )
  20.                                     )-2;
  21.                                     2
  22.                                 )
  23.                             ) = 1;
  24.                             1;
  25.                             0
  26.                         )
  27.                     )
  28.                 );(
  30.                     MID(
  31.                         G45;(
  33.                             SEARCH(
  34.                                 "/";
  35.                                 G45
  36.                             )
  37.                         )-2;
  38.                         2
  39.                     )
  40.                 );(
  42.                     MID(
  43.                         G45;(
  45.                             SEARCH(
  46.                                 "/";
  47.                                 G45
  48.                             )
  49.                         )+1;
  50.                         2
  51.                     )
  52.                 )
  53.             )
  54.         ) < B45;
  55.         "Send";
  56.         "Don't"
  57.     );
  58.     "#VALUE!"
  59. )

Expect:

(1-a, 2-a)

  1. = IF(
  2.     SEARCH("ABC"; G45) > 0;
  3.     IF(
  4.         (DATE(
  5.             ((2014 + IF(
  6.                 VALUE(MID(
  7.                     G45;
  8.                     (SEARCH("/"; G45)) - 2;
  9.                     2
  10.                 )) = 1;
  11.                 1;
  12.                 0
  13.             )));
  14.             (MID(
  15.                 G45;
  16.                 (SEARCH("/"; G45)) - 2;
  17.                 2
  18.             ));
  19.             (MID(
  20.                 G45; 
  21.                 (SEARCH("/"; G45)) + 1;
  22.                 2
  23.             ))
  24.         )) < B45;
  25.         "Send";
  26.         "Don't"
  27.     );
  28.     "#VALUE!"
  29. )

(1-a, 2-b, 3)

  1. = IF(
  2.     SEARCH("ABC"; G45) > 0;
  3.     IF(
  4.         (DATE(
  5.             ((2014 
  6.             + IF(
  7.                 VALUE(MID(
  8.                     G45;
  9.                     (SEARCH("/"; G45)) - 2;
  10.                     2)) = 1;
  11.                 1;
  12.                 0)));
  13.             (MID(
  14.                 G45;
  15.                 (SEARCH("/"; G45)) - 2;
  16.                 2));
  17.             (MID(
  18.                 G45; 
  19.                 (SEARCH("/"; G45)) + 1;
  20.                 2)))) < B45;
  21.         "Send";
  22.         "Don't");
  23.     "#VALUE!")

(1-b, 2-b, 3)

  1. = IF(SEARCH("ABC"; G45) > 0;
  2.      IF((DATE(((2014
  3.               + IF(VALUE(MID(G45;
  4.                              (SEARCH("/"; G45)) - 2;
  5.                              2)) = 1;
  6.                    1;
  7.                    0)));
  8.               (MID(G45;
  9.                    (SEARCH("/"; G45)) - 2;
  10.                    2));
  11.               (MID(G45; 
  12.                    (SEARCH("/"; G45)) + 1;
  13.                    2)))) < B45;
  14.         "Send";
  15.         "Don't");
  16.     "#VALUE!")

例30: =INDEX($C$58:$C$68,MATCH(AVERAGE(VLOOKUP(F4,$C$58:$D$68,2,0),VLOOKUP(G4,$C$58:$D$68,2,0),VLOOKUP(H4,$C$58:$D$68,2,0),VLOOKUP(I4,$C$58:$D$68,2,0),VLOOKUP(J4,$C$58:$D$68,2,0),VLOOKUP(K4,$C$58:$D$68,2,0),VLOOKUP(L4,$C$58:$D$68,2,0)),$D$58:$D$68,1))

Raw:

  1. =INDEX($C$58:$C$68,MATCH(AVERAGE(VLOOKUP(F4,$C$58:$D$68,2,0),VLOOKUP(G4,$C$58:$D$68,2,0),VLOOKUP(H4,$C$58:$D$68,2,0),VLOOKUP(I4,$C$58:$D$68,2,0),VLOOKUP(J4,$C$58:$D$68,2,0),VLOOKUP(K4,$C$58:$D$68,2,0),VLOOKUP(L4,$C$58:$D$68,2,0)),$D$58:$D$68,1))

Formula Editor:

  1. = INDEX(
  2.     $C$58:$C$68,
  3.     MATCH(
  4.         AVERAGE(
  5.             VLOOKUP(
  6.                 F4,
  7.                 $C$58:$D$68,
  8.                 2,
  9.                 0
  10.             ),
  11.             VLOOKUP(
  12.                 G4,
  13.                 $C$58:$D$68,
  14.                 2,
  15.                 0
  16.             ),
  17.             VLOOKUP(
  18.                 H4,
  19.                 $C$58:$D$68,
  20.                 2,
  21.                 0
  22.             ),
  23.             VLOOKUP(
  24.                 I4,
  25.                 $C$58:$D$68,
  26.                 2,
  27.                 0
  28.             ),
  29.             VLOOKUP(
  30.                 J4,
  31.                 $C$58:$D$68,
  32.                 2,
  33.                 0
  34.             ),
  35.             VLOOKUP(
  36.                 K4,
  37.                 $C$58:$D$68,
  38.                 2,
  39.                 0
  40.             ),
  41.             VLOOKUP(
  42.                 L4,
  43.                 $C$58:$D$68,
  44.                 2,
  45.                 0
  46.             )
  47.         ),
  48.         $D$58:$D$68,
  49.         1
  50.     )
  51. )

Expect:

  1. = INDEX(
  2.     $C$58:$C$68,
  3.     MATCH(
  4.         AVERAGE(
  5.             VLOOKUP(F4, $C$58:$D$68, 2, 0),
  6.             VLOOKUP(G4, $C$58:$D$68, 2, 0),
  7.             VLOOKUP(H4, $C$58:$D$68, 2, 0),
  8.             VLOOKUP(I4, $C$58:$D$68, 2, 0),
  9.             VLOOKUP(J4, $C$58:$D$68, 2, 0),
  10.             VLOOKUP(K4, $C$58:$D$68, 2, 0),
  11.             VLOOKUP(L4, $C$58:$D$68, 2, 0)
  12.         ),
  13.         $D$58:$D$68,
  14.         1
  15.     )
  16. )

同样的问题： 在vlookup嵌套级别比较深且vlookup的公式比较简单时，是否可以不换行。

例31: =IF(A1="Facility Variance Report",IF(MID(A2,FIND(":",A2)+2,20)="Baptist Memorial Hos",MID(A2,FIND("_",A2)+1,2)&"-"&TRIM((MID(A2,FIND(":",A2)+2,17)))&IF(IFERROR(FIND("West",A2),0)>0," W","")&" "&MID(A2,FIND(CHAR(1),SUBSTITUTE(A2,"-",CHAR(1),2))+1,3)&" -FVar",MID(A2,FIND("_",A2)+1,2)&"-"&MID(A2,FIND("_",A2)+1,2)&"-"&TRIM((MID(A2,FIND(":",A2)+2,20)))&IF(IFERROR(FIND("West",A2),0)>0," W","")&"-FVar"),IF(MID(A2,FIND(":",A2)+2,20)="Baptist Memorial Hos",MID(A2,FIND("_",A2)+1,2)&"-"&TRIM((MID(A2,FIND(":",A2)+2,17)))&IF(IFERROR(FIND("West",A2),0)>0," W","")&" "&MID(A2,FIND("-",A2,FIND("-",A2)+1)+1,3)&"-LTM",MID(A2,FIND("_",A2)+1,2)&"-"&MID(A2,FIND("_",A2)+1,2)&"-"&TRIM((MID(A2,FIND(":",A2)+2,20)))&IF(IFERROR(FIND("West",A2),0)>0," W","")&"-LTM"))

Raw:

  1. =IF(A1="Facility Variance Report",IF(MID(A2,FIND(":",A2)+2,20)="Baptist Memorial Hos",MID(A2,FIND("_",A2)+1,2)&"-"&TRIM((MID(A2,FIND(":",A2)+2,17)))&IF(IFERROR(FIND("West",A2),0)>0," W","")&" "&MID(A2,FIND(CHAR(1),SUBSTITUTE(A2,"-",CHAR(1),2))+1,3)&" -FVar",MID(A2,FIND("_",A2)+1,2)&"-"&MID(A2,FIND("_",A2)+1,2)&"-"&TRIM((MID(A2,FIND(":",A2)+2,20)))&IF(IFERROR(FIND("West",A2),0)>0," W","")&"-FVar"),IF(MID(A2,FIND(":",A2)+2,20)="Baptist Memorial Hos",MID(A2,FIND("_",A2)+1,2)&"-"&TRIM((MID(A2,FIND(":",A2)+2,17)))&IF(IFERROR(FIND("West",A2),0)>0," W","")&" "&MID(A2,FIND("-",A2,FIND("-",A2)+1)+1,3)&"-LTM",MID(A2,FIND("_",A2)+1,2)&"-"&MID(A2,FIND("_",A2)+1,2)&"-"&TRIM((MID(A2,FIND(":",A2)+2,20)))&IF(IFERROR(FIND("West",A2),0)>0," W","")&"-LTM"))

Formula Editor:

  1. = IF(
  2.     A1 = "Facility Variance Report",
  3.     IF(
  4.         MID(
  5.             A2,
  6.             FIND(
  7.                 ":",
  8.                 A2
  9.             )+2,
  10.             20
  11.         ) = "Baptist Memorial Hos",
  12.         MID(
  13.             A2,
  14.             FIND(
  15.                 "_",
  16.                 A2
  17.             )+1,
  18.             2
  19.         )&"-"&TRIM(
  20.             (
  21.                 MID(
  22.                     A2,
  23.                     FIND(
  24.                         ":",
  25.                         A2
  26.                     )+2,
  27.                     17
  28.                 )
  29.             )
  30.         )&IF(
  31.             IFERROR(
  32.                 FIND(
  33.                     "West",
  34.                     A2
  35.                 ),
  36.                 0
  37.             ) > 0,
  38.             " W",
  39.             ""
  40.         )&" "&MID(
  41.             A2,
  42.             FIND(
  43.                 CHAR(
  44.                     1
  45.                 ),
  46.                 SUBSTITUTE(
  47.                     A2,
  48.                     "-",
  49.                     CHAR(
  50.                         1
  51.                     ),
  52.                     2
  53.                 )
  54.             )+1,
  55.             3
  56.         )&" -FVar",
  57.         MID(
  58.             A2,
  59.             FIND(
  60.                 "_",
  61.                 A2
  62.             )+1,
  63.             2
  64.         )&"-"&MID(
  65.             A2,
  66.             FIND(
  67.                 "_",
  68.                 A2
  69.             )+1,
  70.             2
  71.         )&"-"&TRIM(
  72.             (
  73.                 MID(
  74.                     A2,
  75.                     FIND(
  76.                         ":",
  77.                         A2
  78.                     )+2,
  79.                     20
  80.                 )
  81.             )
  82.         )&IF(
  83.             IFERROR(
  84.                 FIND(
  85.                     "West",
  86.                     A2
  87.                 ),
  88.                 0
  89.             ) > 0,
  90.             " W",
  91.             ""
  92.         )&"-FVar"
  93.     ),
  94.     IF(
  95.         MID(
  96.             A2,
  97.             FIND(
  98.                 ":",
  99.                 A2
  100.             )+2,
  101.             20
  102.         ) = "Baptist Memorial Hos",
  103.         MID(
  104.             A2,
  105.             FIND(
  106.                 "_",
  107.                 A2
  108.             )+1,
  109.             2
  110.         )&"-"&TRIM(
  111.             (
  112.                 MID(
  113.                     A2,
  114.                     FIND(
  115.                         ":",
  116.                         A2
  117.                     )+2,
  118.                     17
  119.                 )
  120.             )
  121.         )&IF(
  122.             IFERROR(
  123.                 FIND(
  124.                     "West",
  125.                     A2
  126.                 ),
  127.                 0
  128.             ) > 0,
  129.             " W",
  130.             ""
  131.         )&" "&MID(
  132.             A2,
  133.             FIND(
  134.                 "-",
  135.                 A2,
  136.                 FIND(
  137.                     "-",
  138.                     A2
  139.                 )+1
  140.             )+1,
  141.             3
  142.         )&"-LTM",
  143.         MID(
  144.             A2,
  145.             FIND(
  146.                 "_",
  147.                 A2
  148.             )+1,
  149.             2
  150.         )&"-"&MID(
  151.             A2,
  152.             FIND(
  153.                 "_",
  154.                 A2
  155.             )+1,
  156.             2
  157.         )&"-"&TRIM(
  158.             (
  159.                 MID(
  160.                     A2,
  161.                     FIND(
  162.                         ":",
  163.                         A2
  164.                     )+2,
  165.                     20
  166.                 )
  167.             )
  168.         )&IF(
  169.             IFERROR(
  170.                 FIND(
  171.                     "West",
  172.                     A2
  173.                 ),
  174.                 0
  175.             ) > 0,
  176.             " W",
  177.             ""
  178.         )&"-LTM"
  179.     )
  180. )

Expect:

(1-a, 2-a)

  1. = IF(
  2.     A1 = "Facility Variance Report",
  3.     IF(
  4.         MID(
  5.             A2,
  6.             FIND(":", A2) + 2,
  7.             20
  8.         ) = "Baptist Memorial Hos",
  9.         MID(
  10.             A2,
  11.             FIND("_", A2) + 1,
  12.             2
  13.         ) & "-" & TRIM((MID(
  14.             A2,
  15.             FIND(":", A2) + 2,
  16.             17
  17.         ))) & IF(
  18.             IFERROR(
  19.                 FIND("West", A2),
  20.                 0
  21.             ) > 0,
  22.             " W",
  23.             ""
  24.         ) & " " & MID(
  25.             A2,
  26.             FIND(
  27.                 CHAR(1),
  28.                 SUBSTITUTE(
  29.                     A2,
  30.                     "-",
  31.                     CHAR(1),
  32.                     2
  33.                 )
  34.             ) + 1,
  35.             3
  36.         ) & " -FVar",
  37.         MID(
  38.             A2,
  39.             FIND("_", A2) + 1,
  40.             2
  41.         ) & "-" & MID(
  42.             A2,
  43.             FIND("_", A2) + 1,
  44.             2
  45.         ) & "-" & TRIM((MID(
  46.             A2,
  47.             FIND(":", A2) + 2,
  48.             20
  49.         ))) & IF(
  50.             IFERROR(
  51.                 FIND("West", A2),
  52.                 0
  53.             ) > 0,
  54.             " W",
  55.             ""
  56.         ) & "-FVar"
  57.     ),
  58.     IF(
  59.         MID(
  60.             A2,
  61.             FIND(":", A2) + 2,
  62.             20
  63.         ) = "Baptist Memorial Hos",
  64.         MID(
  65.             A2,
  66.             FIND("_", A2) + 1,
  67.             2
  68.         ) & "-" & TRIM((MID(
  69.             A2,
  70.             FIND(":", A2) + 2,
  71.             17
  72.         ))) & IF(
  73.             IFERROR(
  74.                 FIND("West", A2),
  75.                 0
  76.             ) > 0,
  77.             " W",
  78.             ""
  79.         ) & " " & MID(
  80.             A2,
  81.             FIND(
  82.                 "-", 
  83.                 A2,
  84.                 FIND("-", A2) + 1
  85.             ) + 1,
  86.             3
  87.         ) & "-LTM",
  88.         MID(
  89.             A2,
  90.             FIND("_", A2) + 1,
  91.             2
  92.         ) & "-" & MID(
  93.             A2,
  94.             FIND("_", A2) + 1,
  95.             2
  96.         ) & "-" & TRIM((MID(
  97.             A2,
  98.             FIND(":", A2) + 2,
  99.             20
  100.         ))) & IF(
  101.             IFERROR(
  102.                 FIND("West", A2),
  103.                 0
  104.             ) > 0,
  105.             " W",
  106.             ""
  107.         ) & "-LTM"
  108.     )
  109. )

Char、Trim都是单目运算符，无需换行。
无嵌套的简单短函数可以放在一行，无需强制换行。

(1-a, 2-a, 3)

  1. = IF(
  2.     A1 = "Facility Variance Report",
  3.     IF(
  4.         MID(
  5.             A2,
  6.             FIND(":", A2) + 2,
  7.             20
  8.         ) = "Baptist Memorial Hos",
  9.         MID(
  10.             A2,
  11.             FIND("_", A2) + 1,
  12.             2
  13.         ) & "-" 
  14.         & TRIM((MID(
  15.             A2,
  16.             FIND(":", A2) + 2,
  17.             17
  18.         ))) 
  19.         & IF(
  20.             IFERROR(
  21.                 FIND("West", A2),
  22.                 0
  23.             ) > 0,
  24.             " W",
  25.             ""
  26.         ) & " " 
  27.         & MID(
  28.             A2,
  29.             FIND(
  30.                 CHAR(1),
  31.                 SUBSTITUTE(
  32.                     A2,
  33.                     "-",
  34.                     CHAR(1),
  35.                     2
  36.                 )
  37.             ) + 1,
  38.             3
  39.         ) & " -FVar",
  40.         MID(
  41.             A2,
  42.             FIND("_", A2) + 1,
  43.             2
  44.         ) & "-" 
  45.         & MID(
  46.             A2,
  47.             FIND("_", A2) + 1,
  48.             2
  49.         ) & "-" 
  50.         & TRIM((MID(
  51.             A2,
  52.             FIND(":", A2) + 2,
  53.             20
  54.         ))) 
  55.         & IF(
  56.             IFERROR(
  57.                 FIND("West", A2),
  58.                 0
  59.             ) > 0,
  60.             " W",
  61.             ""
  62.         ) & "-FVar"
  63.     ),
  64.     IF(
  65.         MID(
  66.             A2,
  67.             FIND(":", A2) + 2,
  68.             20
  69.         ) = "Baptist Memorial Hos",
  70.         MID(
  71.             A2,
  72.             FIND("_", A2) + 1,
  73.             2
  74.         ) & "-" 
  75.         & TRIM((MID(
  76.             A2,
  77.             FIND(":", A2) + 2,
  78.             17
  79.         ))) 
  80.         & IF(
  81.             IFERROR(
  82.                 FIND("West", A2),
  83.                 0
  84.             ) > 0,
  85.             " W",
  86.             ""
  87.         ) & " " 
  88.         & MID(
  89.             A2,
  90.             FIND(
  91.                 "-", 
  92.                 A2,
  93.                 FIND("-", A2) + 1
  94.             ) + 1,
  95.             3
  96.         ) & "-LTM",
  97.         MID(
  98.             A2,
  99.             FIND("_", A2) + 1,
  100.             2
  101.         ) & "-" 
  102.         & MID(
  103.             A2,
  104.             FIND("_", A2) + 1,
  105.             2
  106.         ) & "-" 
  107.         & TRIM((MID(
  108.             A2,
  109.             FIND(":", A2) + 2,
  110.             20
  111.         ))) & IF(
  112.             IFERROR(
  113.                 FIND("West", A2),
  114.                 0
  115.             ) > 0,
  116.             " W",
  117.             ""
  118.         ) & "-LTM"
  119.     )
  120. )

(1-a, 2-b, 3)

  1. = IF(
  2.     A1 = "Facility Variance Report",
  3.     IF(
  4.         MID(
  5.             A2,
  6.             FIND(":", A2) + 2,
  7.             20) = "Baptist Memorial Hos",
  8.         MID(
  9.             A2,
  10.             FIND("_", A2) + 1,
  11.             2) & "-" 
  12.         & TRIM((MID(
  13.             A2,
  14.             FIND(":", A2) + 2,
  15.             17))) 
  16.         & IF(
  17.             IFERROR(
  18.                 FIND("West", A2),
  19.                 0) > 0,
  20.             " W",
  21.             "") & " " 
  22.             & MID(
  23.                 A2,
  24.                 FIND(
  25.                     CHAR(1),
  26.                     SUBSTITUTE(
  27.                         A2,
  28.                         "-",
  29.                         CHAR(1),
  30.                         2)) + 1,
  31.                 3) & " -FVar",
  32.         MID(
  33.             A2,
  34.             FIND("_", A2) + 1,
  35.             2) & "-" 
  36.         & MID(
  37.             A2,
  38.             FIND("_", A2) + 1,
  39.             2) & "-" 
  40.             & TRIM((MID(
  41.                 A2,
  42.                 FIND(":", A2) + 2,
  43.                 20))) 
  44.             & IF(
  45.                 IFERROR(
  46.                     FIND("West", A2),
  47.                     0
  48.                 ) > 0,
  49.                 " W",
  50.                 "") & "-FVar"),
  51.     IF(
  52.         MID(
  53.             A2,
  54.             FIND(":", A2) + 2,
  55.             20) = "Baptist Memorial Hos",
  56.         MID(
  57.             A2,
  58.             FIND("_", A2) + 1,
  59.             2) & "-" 
  60.         & TRIM((MID(
  61.             A2,
  62.             FIND(":", A2) + 2,
  63.             17)))
  64.         & IF(
  65.             IFERROR(
  66.                 FIND("West", A2),
  67.                 0) > 0,
  68.             " W",
  69.             "") & " " 
  70.         & MID(
  71.             A2,
  72.             FIND(
  73.                 "-", 
  74.                 A2,
  75.                 FIND("-", A2) + 1) + 1,
  76.             3) & "-LTM",
  77.         MID(
  78.             A2,
  79.             FIND("_", A2) + 1,
  80.             2) & "-" 
  81.         & MID(
  82.             A2,
  83.             FIND("_", A2) + 1,
  84.             2) & "-" 
  85.         & TRIM((MID(
  86.             A2,
  87.             FIND(":", A2) + 2,
  88.             20))) 
  89.         & IF(
  90.             IFERROR(
  91.                 FIND("West", A2),
  92.                 0) > 0,
  93.             " W",
  94.             "") & "-LTM"))

(1-b, 2-b)

  1. = IF(A1 = "Facility Variance Report",
  2.      IF(MID(A2,
  3.             FIND(":", A2) + 2,
  4.             20) = "Baptist Memorial Hos",
  5.         MID(A2,
  6.             FIND("_", A2) + 1,
  7.             2) & "-" & TRIM((MID(A2,
  8.                                  FIND(":", A2) + 2,
  9.                                  17))) & IF(IFERROR(FIND("West", A2),
  10.                                                     0) > 0,
  11.                                             " W",
  12.                                             "") & " " & MID(A2,
  13.                                                             FIND(CHAR(1),
  14.                                                                  SUBSTITUTE(A2,
  15.                                                                             "-",
  16.                                                                             CHAR(1),
  17.                                                                             2)) + 1,
  18.                                                             3) & " -FVar",
  19.         MID(A2,
  20.             FIND("_", A2) + 1,
  21.             2) & "-" & MID(A2,
  22.                            FIND("_", A2) + 1,
  23.                            2) & "-" & TRIM((MID(A2,
  24.                                                 FIND(":", A2) + 2,
  25.                                                 20))) & IF(IFERROR(FIND("West", A2),
  26.                                                                    0) > 0,
  27.                                                            " W",
  28.                                                            "") & "-FVar"),
  29.      IF(MID(A2,
  30.             FIND(":", A2) + 2,
  31.             20) = "Baptist Memorial Hos",
  32.         MID(A2,
  33.             FIND("_", A2) + 1,
  34.             2) & "-" & TRIM((MID(A2,
  35.                                  FIND(":", A2) + 2,
  36.                                  17))) & IF(IFERROR(FIND("West", A2),
  37.                                                     0) > 0,
  38.                                             " W",
  39.                                             "") & " " & MID(A2,
  40.                                                             FIND("-", 
  41.                                                                  A2,
  42.                                                                  FIND("-", A2) + 1) + 1,
  43.                                                             3) & "-LTM",
  44.         MID(A2,
  45.             FIND("_", A2) + 1,
  46.             2) & "-" & MID(A2,
  47.                            FIND("_", A2) + 1,
  48.                            2) & "-" & TRIM((MID(A2,
  49.                                                 FIND(":", A2) + 2,
  50.                                                 20))) & IF(IFERROR(FIND("West", A2),
  51.                                                                    0) > 0,
  52.                                                            " W",
  53.                                                            "") & "-LTM"))

(1-b, 2-b, 3)

  1. = IF(A1 = "Facility Variance Report",
  2.      IF(MID(A2,
  3.             FIND(":", A2) + 2,
  4.             20) = "Baptist Memorial Hos",
  5.         MID(A2,
  6.             FIND("_", A2) + 1,
  7.             2) & "-" 
  8.         & TRIM((MID(A2,
  9.                 FIND(":", A2) + 2,
  10.                 17))) 
  11.         & IF(IFERROR(FIND("West", A2),
  12.                      0) > 0,
  13.              " W",
  14.              "") & " " 
  15.         & MID(A2,
  16.               FIND(CHAR(1),
  17.                    SUBSTITUTE(A2,
  18.                               "-",
  19.                               CHAR(1),
  20.                               2)) + 1,
  21.               3) & " -FVar",
  22.         MID(A2,
  23.             FIND("_", A2) + 1,
  24.             2) & "-" 
  25.         & MID(A2,
  26.               FIND("_", A2) + 1,
  27.               2) & "-"
  28.         & TRIM((MID(A2,
  29.                     FIND(":", A2) + 2,
  30.                     20)))
  31.         & IF(IFERROR(FIND("West", A2),
  32.                           0) > 0,
  33.              " W",
  34.              "") & "-FVar"),
  35.      IF(MID(A2,
  36.             FIND(":", A2) + 2,
  37.             20) = "Baptist Memorial Hos",
  38.         MID(A2,
  39.             FIND("_", A2) + 1,
  40.             2) & "-" 
  41.         & TRIM((MID(A2,
  42.                     FIND(":", A2) + 2,
  43.                     17))) 
  44.         & IF(IFERROR(FIND("West", A2),
  45.                           0) > 0,
  46.              " W",
  47.              "") & " " 
  48.         & MID(A2,
  49.               FIND("-", 
  50.                    A2,
  51.                    FIND("-", A2) + 1) + 1,
  52.               3) & "-LTM",
  53.         MID(A2,
  54.             FIND("_", A2) + 1,
  55.             2) & "-" 
  56.         & MID(A2,
  57.               FIND("_", A2) + 1,
  58.               2) & "-" 
  59.         & TRIM((MID(A2,
  60.                     FIND(":", A2) + 2,
  61.                     20))) 
  62.         & IF(IFERROR(FIND("West", A2),
  63.                           0) > 0,
  64.              " W",
  65.              "") & "-LTM"))

例32: =CONCATENATE(IFERROR(IF(ISNUMBER(SEARCH('Tagging Inputs'!$C$4,'Template Ideas'!D2))=TRUE,VLOOKUP('Tagging Inputs'!$C$4,'Tagging Inputs'!$B$12:$C$38,2,FALSE),""),""),IF(AND(ISNUMBER(SEARCH('Tagging Inputs'!$C$4,'Template Ideas'!D2)),OR(ISNUMBER(SEARCH('Tagging Inputs'!$C$5,'Template Ideas'!D2)),ISNUMBER(SEARCH('Tagging Inputs'!$C$6,'Template Ideas'!D2)),ISNUMBER(SEARCH('Tagging Inputs'!$C$7,'Template Ideas'!D2)),ISNUMBER(SEARCH('Tagging Inputs'!$C$8,'Template Ideas'!D2))))=TRUE,"; ",""),IFERROR(IF(ISNUMBER(SEARCH('Tagging Inputs'!$C$5,'Template Ideas'!D2))=TRUE,VLOOKUP('Tagging Inputs'!$C$5,'Tagging Inputs'!$B$12:$C$38,2,FALSE),""),""),IF(AND(ISNUMBER(SEARCH('Tagging Inputs'!$C$5,'Template Ideas'!D2)),OR(ISNUMBER(SEARCH('Tagging Inputs'!$C$6,'Template Ideas'!D2)),ISNUMBER(SEARCH('Tagging Inputs'!$C$7,'Template Ideas'!D2)),ISNUMBER(SEARCH('Tagging Inputs'!$C$8,'Template Ideas'!D2))))=TRUE,"; ",""),IFERROR(IF(ISNUMBER(SEARCH('Tagging Inputs'!$C$6,'Template Ideas'!D2))=TRUE,VLOOKUP('Tagging Inputs'!$C$6,'Tagging Inputs'!$B$12:$C$38,2,FALSE),""),""),IF(AND(ISNUMBER(SEARCH('Tagging Inputs'!$C$6,'Template Ideas'!D2)),OR(ISNUMBER(SEARCH('Tagging Inputs'!$C$7,'Template Ideas'!D2)),ISNUMBER(SEARCH('Tagging Inputs'!$C$8,'Template Ideas'!D2))))=TRUE,"; ",""),IFERROR(IF(ISNUMBER(SEARCH('Tagging Inputs'!$C$7,'Template Ideas'!D2))=TRUE,VLOOKUP('Tagging Inputs'!$C$7,'Tagging Inputs'!$B$12:$C$38,2,FALSE),""),""),IF(AND(ISNUMBER(SEARCH('Tagging Inputs'!$C$7,'Template Ideas'!D2)),ISNUMBER(SEARCH('Tagging Inputs'!$C$8,'Template Ideas'!D2)))=TRUE,"; ",""),IFERROR(IF(ISNUMBER(SEARCH('Tagging Inputs'!$C$8,'Template Ideas'!D2))=TRUE,VLOOKUP('Tagging Inputs'!$C$8,'Tagging Inputs'!$B$12:$C$38,2,FALSE),""),""))

Raw:

  1. =CONCATENATE(IFERROR(IF(ISNUMBER(SEARCH('Tagging Inputs'!$C$4,'Template Ideas'!D2))=TRUE,VLOOKUP('Tagging Inputs'!$C$4,'Tagging Inputs'!$B$12:$C$38,2,FALSE),""),""),IF(AND(ISNUMBER(SEARCH('Tagging Inputs'!$C$4,'Template Ideas'!D2)),OR(ISNUMBER(SEARCH('Tagging Inputs'!$C$5,'Template Ideas'!D2)),ISNUMBER(SEARCH('Tagging Inputs'!$C$6,'Template Ideas'!D2)),ISNUMBER(SEARCH('Tagging Inputs'!$C$7,'Template Ideas'!D2)),ISNUMBER(SEARCH('Tagging Inputs'!$C$8,'Template Ideas'!D2))))=TRUE,"; ",""),IFERROR(IF(ISNUMBER(SEARCH('Tagging Inputs'!$C$5,'Template Ideas'!D2))=TRUE,VLOOKUP('Tagging Inputs'!$C$5,'Tagging Inputs'!$B$12:$C$38,2,FALSE),""),""),IF(AND(ISNUMBER(SEARCH('Tagging Inputs'!$C$5,'Template Ideas'!D2)),OR(ISNUMBER(SEARCH('Tagging Inputs'!$C$6,'Template Ideas'!D2)),ISNUMBER(SEARCH('Tagging Inputs'!$C$7,'Template Ideas'!D2)),ISNUMBER(SEARCH('Tagging Inputs'!$C$8,'Template Ideas'!D2))))=TRUE,"; ",""),IFERROR(IF(ISNUMBER(SEARCH('Tagging Inputs'!$C$6,'Template Ideas'!D2))=TRUE,VLOOKUP('Tagging Inputs'!$C$6,'Tagging Inputs'!$B$12:$C$38,2,FALSE),""),""),IF(AND(ISNUMBER(SEARCH('Tagging Inputs'!$C$6,'Template Ideas'!D2)),OR(ISNUMBER(SEARCH('Tagging Inputs'!$C$7,'Template Ideas'!D2)),ISNUMBER(SEARCH('Tagging Inputs'!$C$8,'Template Ideas'!D2))))=TRUE,"; ",""),IFERROR(IF(ISNUMBER(SEARCH('Tagging Inputs'!$C$7,'Template Ideas'!D2))=TRUE,VLOOKUP('Tagging Inputs'!$C$7,'Tagging Inputs'!$B$12:$C$38,2,FALSE),""),""),IF(AND(ISNUMBER(SEARCH('Tagging Inputs'!$C$7,'Template Ideas'!D2)),ISNUMBER(SEARCH('Tagging Inputs'!$C$8,'Template Ideas'!D2)))=TRUE,"; ",""),IFERROR(IF(ISNUMBER(SEARCH('Tagging Inputs'!$C$8,'Template Ideas'!D2))=TRUE,VLOOKUP('Tagging Inputs'!$C$8,'Tagging Inputs'!$B$12:$C$38,2,FALSE),""),""))

Formula Editor:

  1. = CONCATENATE(
  2.     IFERROR(
  3.         IF(
  4.             ISNUMBER(
  5.                 SEARCH(
  6.                     'Tagging Inputs'!$C$4,
  7.                     'Template Ideas'!D2
  8.                 )
  9.             ) = TRUE,
  10.             VLOOKUP(
  11.                 'Tagging Inputs'!$C$4,
  12.                 'Tagging Inputs'!$B$12:$C$38,
  13.                 2,
  14.                 FALSE
  15.             ),
  16.             ""
  17.         ),
  18.         ""
  19.     ),
  20.     IF(
  21.         AND(
  22.             ISNUMBER(
  23.                 SEARCH(
  24.                     'Tagging Inputs'!$C$4,
  25.                     'Template Ideas'!D2
  26.                 )
  27.             ),
  28.             OR(
  29.                 ISNUMBER(
  30.                     SEARCH(
  31.                         'Tagging Inputs'!$C$5,
  32.                         'Template Ideas'!D2
  33.                     )
  34.                 ),
  35.                 ISNUMBER(
  36.                     SEARCH(
  37.                         'Tagging Inputs'!$C$6,
  38.                         'Template Ideas'!D2
  39.                     )
  40.                 ),
  41.                 ISNUMBER(
  42.                     SEARCH(
  43.                         'Tagging Inputs'!$C$7,
  44.                         'Template Ideas'!D2
  45.                     )
  46.                 ),
  47.                 ISNUMBER(
  48.                     SEARCH(
  49.                         'Tagging Inputs'!$C$8,
  50.                         'Template Ideas'!D2
  51.                     )
  52.                 )
  53.             )
  54.         ) = TRUE,
  55.         "; ",
  56.         ""
  57.     ),
  58.     IFERROR(
  59.         IF(
  60.             ISNUMBER(
  61.                 SEARCH(
  62.                     'Tagging Inputs'!$C$5,
  63.                     'Template Ideas'!D2
  64.                 )
  65.             ) = TRUE,
  66.             VLOOKUP(
  67.                 'Tagging Inputs'!$C$5,
  68.                 'Tagging Inputs'!$B$12:$C$38,
  69.                 2,
  70.                 FALSE
  71.             ),
  72.             ""
  73.         ),
  74.         ""
  75.     ),
  76.     IF(
  77.         AND(
  78.             ISNUMBER(
  79.                 SEARCH(
  80.                     'Tagging Inputs'!$C$5,
  81.                     'Template Ideas'!D2
  82.                 )
  83.             ),
  84.             OR(
  85.                 ISNUMBER(
  86.                     SEARCH(
  87.                         'Tagging Inputs'!$C$6,
  88.                         'Template Ideas'!D2
  89.                     )
  90.                 ),
  91.                 ISNUMBER(
  92.                     SEARCH(
  93.                         'Tagging Inputs'!$C$7,
  94.                         'Template Ideas'!D2
  95.                     )
  96.                 ),
  97.                 ISNUMBER(
  98.                     SEARCH(
  99.                         'Tagging Inputs'!$C$8,
  100.                         'Template Ideas'!D2
  101.                     )
  102.                 )
  103.             )
  104.         ) = TRUE,
  105.         "; ",
  106.         ""
  107.     ),
  108.     IFERROR(
  109.         IF(
  110.             ISNUMBER(
  111.                 SEARCH(
  112.                     'Tagging Inputs'!$C$6,
  113.                     'Template Ideas'!D2
  114.                 )
  115.             ) = TRUE,
  116.             VLOOKUP(
  117.                 'Tagging Inputs'!$C$6,
  118.                 'Tagging Inputs'!$B$12:$C$38,
  119.                 2,
  120.                 FALSE
  121.             ),
  122.             ""
  123.         ),
  124.         ""
  125.     ),
  126.     IF(
  127.         AND(
  128.             ISNUMBER(
  129.                 SEARCH(
  130.                     'Tagging Inputs'!$C$6,
  131.                     'Template Ideas'!D2
  132.                 )
  133.             ),
  134.             OR(
  135.                 ISNUMBER(
  136.                     SEARCH(
  137.                         'Tagging Inputs'!$C$7,
  138.                         'Template Ideas'!D2
  139.                     )
  140.                 ),
  141.                 ISNUMBER(
  142.                     SEARCH(
  143.                         'Tagging Inputs'!$C$8,
  144.                         'Template Ideas'!D2
  145.                     )
  146.                 )
  147.             )
  148.         ) = TRUE,
  149.         "; ",
  150.         ""
  151.     ),
  152.     IFERROR(
  153.         IF(
  154.             ISNUMBER(
  155.                 SEARCH(
  156.                     'Tagging Inputs'!$C$7,
  157.                     'Template Ideas'!D2
  158.                 )
  159.             ) = TRUE,
  160.             VLOOKUP(
  161.                 'Tagging Inputs'!$C$7,
  162.                 'Tagging Inputs'!$B$12:$C$38,
  163.                 2,
  164.                 FALSE
  165.             ),
  166.             ""
  167.         ),
  168.         ""
  169.     ),
  170.     IF(
  171.         AND(
  172.             ISNUMBER(
  173.                 SEARCH(
  174.                     'Tagging Inputs'!$C$7,
  175.                     'Template Ideas'!D2
  176.                 )
  177.             ),
  178.             ISNUMBER(
  179.                 SEARCH(
  180.                     'Tagging Inputs'!$C$8,
  181.                     'Template Ideas'!D2
  182.                 )
  183.             )
  184.         ) = TRUE,
  185.         "; ",
  186.         ""
  187.     ),
  188.     IFERROR(
  189.         IF(
  190.             ISNUMBER(
  191.                 SEARCH(
  192.                     'Tagging Inputs'!$C$8,
  193.                     'Template Ideas'!D2
  194.                 )
  195.             ) = TRUE,
  196.             VLOOKUP(
  197.                 'Tagging Inputs'!$C$8,
  198.                 'Tagging Inputs'!$B$12:$C$38,
  199.                 2,
  200.                 FALSE
  201.             ),
  202.             ""
  203.         ),
  204.         ""
  205.     )
  206. )

Expect:

(1-a, 2-a)

  1. = CONCATENATE(
  2.     IFERROR(
  3.         IF(
  4.             ISNUMBER(SEARCH(
  5.                 'Tagging Inputs'!$C$4,
  6.                 'Template Ideas'!D2
  7.             )) = TRUE,
  8.             VLOOKUP(
  9.                 'Tagging Inputs'!$C$4,
  10.                 'Tagging Inputs'!$B$12:$C$38,
  11.                 2,
  12.                 FALSE
  13.             ),
  14.             ""
  15.         ),
  16.         ""
  17.     ),
  18.     IF(
  19.         AND(
  20.             ISNUMBER(SEARCH(
  21.                 'Tagging Inputs'!$C$4,
  22.                 'Template Ideas'!D2
  23.             )),
  24.             OR(
  25.                 ISNUMBER(SEARCH(
  26.                     'Tagging Inputs'!$C$5,
  27.                     'Template Ideas'!D2
  28.                 )),
  29.                 ISNUMBER(SEARCH(
  30.                     'Tagging Inputs'!$C$6,
  31.                     'Template Ideas'!D2
  32.                 )),
  33.                 ISNUMBER(SEARCH(
  34.                     'Tagging Inputs'!$C$7,
  35.                     'Template Ideas'!D2
  36.                 )),
  37.                 ISNUMBER(SEARCH(
  38.                     'Tagging Inputs'!$C$8,
  39.                     'Template Ideas'!D2
  40.                 ))
  41.             )
  42.         ) = TRUE,
  43.         "; ",
  44.         ""
  45.     ),
  46.     IFERROR(
  47.         IF(
  48.             ISNUMBER(SEARCH(
  49.                 'Tagging Inputs'!$C$5,
  50.                 'Template Ideas'!D2
  51.             )) = TRUE,
  52.             VLOOKUP(
  53.                 'Tagging Inputs'!$C$5,
  54.                 'Tagging Inputs'!$B$12:$C$38,
  55.                 2,
  56.                 FALSE
  57.             ),
  58.             ""
  59.         ),
  60.         ""
  61.     ),
  62.     IF(
  63.         AND(
  64.             ISNUMBER(SEARCH(
  65.                 'Tagging Inputs'!$C$5,
  66.                 'Template Ideas'!D2
  67.             )),
  68.             OR(
  69.                 ISNUMBER(SEARCH(
  70.                     'Tagging Inputs'!$C$6,
  71.                     'Template Ideas'!D2
  72.                 )),
  73.                 ISNUMBER(SEARCH(
  74.                     'Tagging Inputs'!$C$7,
  75.                     'Template Ideas'!D2
  76.                 )),
  77.                 ISNUMBER(SEARCH(
  78.                     'Tagging Inputs'!$C$8,
  79.                     'Template Ideas'!D2
  80.                 ))
  81.             )
  82.         ) = TRUE,
  83.         "; ",
  84.         ""
  85.     ),
  86.     IFERROR(
  87.         IF(
  88.             ISNUMBER(SEARCH(
  89.                 'Tagging Inputs'!$C$6,
  90.                 'Template Ideas'!D2
  91.             )) = TRUE,
  92.             VLOOKUP(
  93.                 'Tagging Inputs'!$C$6,
  94.                 'Tagging Inputs'!$B$12:$C$38,
  95.                 2,
  96.                 FALSE
  97.             ),
  98.             ""
  99.         ),
  100.         ""
  101.     ),
  102.     IF(
  103.         AND(
  104.             ISNUMBER(SEARCH(
  105.                 'Tagging Inputs'!$C$6,
  106.                 'Template Ideas'!D2
  107.             )),
  108.             OR(
  109.                 ISNUMBER(SEARCH(
  110.                     'Tagging Inputs'!$C$7,
  111.                     'Template Ideas'!D2
  112.                 )),
  113.                 ISNUMBER(SEARCH(
  114.                     'Tagging Inputs'!$C$8,
  115.                     'Template Ideas'!D2
  116.                 ))
  117.             )
  118.         ) = TRUE,
  119.         "; ",
  120.         ""
  121.     ),
  122.     IFERROR(
  123.         IF(
  124.             ISNUMBER(SEARCH(
  125.                 'Tagging Inputs'!$C$7,
  126.                 'Template Ideas'!D2
  127.             )) = TRUE,
  128.             VLOOKUP(
  129.                 'Tagging Inputs'!$C$7,
  130.                 'Tagging Inputs'!$B$12:$C$38,
  131.                 2,
  132.                 FALSE
  133.             ),
  134.             ""
  135.         ),
  136.         ""
  137.     ),
  138.     IF(
  139.         AND(
  140.             ISNUMBER(SEARCH(
  141.                 'Tagging Inputs'!$C$7,
  142.                 'Template Ideas'!D2
  143.             )),
  144.             ISNUMBER(SEARCH(
  145.                 'Tagging Inputs'!$C$8,
  146.                 'Template Ideas'!D2
  147.             ))
  148.         ) = TRUE,
  149.         "; ",
  150.         ""
  151.     ),
  152.     IFERROR(
  153.         IF(
  154.             ISNUMBER(SEARCH(
  155.                 'Tagging Inputs'!$C$8,
  156.                 'Template Ideas'!D2
  157.             )) = TRUE,
  158.             VLOOKUP(
  159.                 'Tagging Inputs'!$C$8,
  160.                 'Tagging Inputs'!$B$12:$C$38,
  161.                 2,
  162.                 FALSE
  163.             ),
  164.             ""
  165.         ),
  166.         ""
  167.     )
  168. )

ISNUMBER是单目运算符，可以不换行

(1-a, 2-b)

  1. = CONCATENATE(
  2.     IFERROR(
  3.         IF(
  4.             ISNUMBER(SEARCH(
  5.                 'Tagging Inputs'!$C$4,
  6.                 'Template Ideas'!D2)) = TRUE,
  7.             VLOOKUP(
  8.                 'Tagging Inputs'!$C$4,
  9.                 'Tagging Inputs'!$B$12:$C$38,
  10.                 2,
  11.                 FALSE),
  12.             ""),
  13.         ""),
  14.     IF(
  15.         AND(
  16.             ISNUMBER(SEARCH(
  17.                 'Tagging Inputs'!$C$4,
  18.                 'Template Ideas'!D2)),
  19.             OR(
  20.                 ISNUMBER(SEARCH(
  21.                     'Tagging Inputs'!$C$5,
  22.                     'Template Ideas'!D2)),
  23.                 ISNUMBER(SEARCH(
  24.                     'Tagging Inputs'!$C$6,
  25.                     'Template Ideas'!D2)),
  26.                 ISNUMBER(SEARCH(
  27.                     'Tagging Inputs'!$C$7,
  28.                     'Template Ideas'!D2)),
  29.                 ISNUMBER(SEARCH(
  30.                     'Tagging Inputs'!$C$8,
  31.                     'Template Ideas'!D2)))) = TRUE,
  32.         "; ",
  33.         ""),
  34.     IFERROR(
  35.         IF(
  36.             ISNUMBER(SEARCH(
  37.                 'Tagging Inputs'!$C$5,
  38.                 'Template Ideas'!D2)) = TRUE,
  39.             VLOOKUP(
  40.                 'Tagging Inputs'!$C$5,
  41.                 'Tagging Inputs'!$B$12:$C$38,
  42.                 2,
  43.                 FALSE),
  44.             ""),
  45.         ""),
  46.     IF(
  47.         AND(
  48.             ISNUMBER(SEARCH(
  49.                 'Tagging Inputs'!$C$5,
  50.                 'Template Ideas'!D2)),
  51.             OR(
  52.                 ISNUMBER(SEARCH(
  53.                     'Tagging Inputs'!$C$6,
  54.                     'Template Ideas'!D2)),
  55.                 ISNUMBER(SEARCH(
  56.                     'Tagging Inputs'!$C$7,
  57.                     'Template Ideas'!D2)),
  58.                 ISNUMBER(SEARCH(
  59.                     'Tagging Inputs'!$C$8,
  60.                     'Template Ideas'!D2)))) = TRUE,
  61.         "; ",
  62.         ""
  63.     ),
  64.     IFERROR(
  65.         IF(
  66.             ISNUMBER(SEARCH(
  67.                 'Tagging Inputs'!$C$6,
  68.                 'Template Ideas'!D2)) = TRUE,
  69.             VLOOKUP(
  70.                 'Tagging Inputs'!$C$6,
  71.                 'Tagging Inputs'!$B$12:$C$38,
  72.                 2,
  73.                 FALSE),
  74.             ""),
  75.         ""),
  76.     IF(
  77.         AND(
  78.             ISNUMBER(SEARCH(
  79.                 'Tagging Inputs'!$C$6,
  80.                 'Template Ideas'!D2
  81.             )),
  82.             OR(
  83.                 ISNUMBER(SEARCH(
  84.                     'Tagging Inputs'!$C$7,
  85.                     'Template Ideas'!D2
  86.                 )),
  87.                 ISNUMBER(SEARCH(
  88.                     'Tagging Inputs'!$C$8,
  89.                     'Template Ideas'!D2
  90.                 )))) = TRUE,
  91.         "; ",
  92.         ""
  93.     ),
  94.     IFERROR(
  95.         IF(
  96.             ISNUMBER(SEARCH(
  97.                 'Tagging Inputs'!$C$7,
  98.                 'Template Ideas'!D2)) = TRUE,
  99.             VLOOKUP(
  100.                 'Tagging Inputs'!$C$7,
  101.                 'Tagging Inputs'!$B$12:$C$38,
  102.                 2,
  103.                 FALSE),
  104.             ""),
  105.         ""),
  106.     IF(
  107.         AND(
  108.             ISNUMBER(SEARCH(
  109.                 'Tagging Inputs'!$C$7,
  110.                 'Template Ideas'!D2)),
  111.             ISNUMBER(SEARCH(
  112.                 'Tagging Inputs'!$C$8,
  113.                 'Template Ideas'!D2))) = TRUE,
  114.         "; ",
  115.         ""),
  116.     IFERROR(
  117.         IF(
  118.             ISNUMBER(SEARCH(
  119.                 'Tagging Inputs'!$C$8,
  120.                 'Template Ideas'!D2
  121.             )) = TRUE,
  122.             VLOOKUP(
  123.                 'Tagging Inputs'!$C$8,
  124.                 'Tagging Inputs'!$B$12:$C$38,
  125.                 2,
  126.                 FALSE),
  127.             ""),
  128.         ""))

(1-b, 2-b)

  1. = CONCATENATE(IFERROR(IF(ISNUMBER(SEARCH('Tagging Inputs'!$C$4,
  2.                                          'Template Ideas'!D2)) = TRUE,
  3.                          VLOOKUP('Tagging Inputs'!$C$4,
  4.                                  'Tagging Inputs'!$B$12:$C$38,
  5.                                  2,
  6.                                  FALSE),
  7.                          ""),
  8.                       ""),
  9.               IF(AND(ISNUMBER(SEARCH('Tagging Inputs'!$C$4,
  10.                                      'Template Ideas'!D2)),
  11.                      OR(ISNUMBER(SEARCH('Tagging Inputs'!$C$5,
  12.                                         'Template Ideas'!D2)),
  13.                         ISNUMBER(SEARCH('Tagging Inputs'!$C$6,
  14.                                         'Template Ideas'!D2)),
  15.                         ISNUMBER(SEARCH('Tagging Inputs'!$C$7,
  16.                                         'Template Ideas'!D2)),
  17.                         ISNUMBER(SEARCH('Tagging Inputs'!$C$8,
  18.                                         'Template Ideas'!D2)))) = TRUE,
  19.                  "; ",
  20.                  ""),
  21.               IFERROR(IF(ISNUMBER(SEARCH('Tagging Inputs'!$C$5,
  22.                                          'Template Ideas'!D2)) = TRUE,
  23.                          VLOOKUP('Tagging Inputs'!$C$5,
  24.                                  'Tagging Inputs'!$B$12:$C$38,
  25.                                  2,
  26.                                  FALSE),
  27.                          ""),
  28.                       ""),
  29.               IF(AND(ISNUMBER(SEARCH('Tagging Inputs'!$C$5,
  30.                                      'Template Ideas'!D2)),
  31.                      OR(ISNUMBER(SEARCH('Tagging Inputs'!$C$6,
  32.                                         'Template Ideas'!D2)),
  33.                         ISNUMBER(SEARCH('Tagging Inputs'!$C$7,
  34.                                         'Template Ideas'!D2)),
  35.                         ISNUMBER(SEARCH('Tagging Inputs'!$C$8,
  36.                                         'Template Ideas'!D2)))) = TRUE,
  37.                  "; ",
  38.                  ""),
  39.               IFERROR(IF(ISNUMBER(SEARCH('Tagging Inputs'!$C$6,
  40.                                          'Template Ideas'!D2)) = TRUE,
  41.                          VLOOKUP('Tagging Inputs'!$C$6,
  42.                                  'Tagging Inputs'!$B$12:$C$38,
  43.                                  2,
  44.                                  FALSE),
  45.                          ""),
  46.                       ""),
  47.               IF(AND(ISNUMBER(SEARCH('Tagging Inputs'!$C$6,
  48.                                      'Template Ideas'!D2)),
  49.                      OR(ISNUMBER(SEARCH('Tagging Inputs'!$C$7,
  50.                                         'Template Ideas'!D2)),
  51.                         ISNUMBER(SEARCH('Tagging Inputs'!$C$8,
  52.                                         'Template Ideas'!D2)))) = TRUE,
  53.                  "; ",
  54.                  ""),
  55.               IFERROR(IF(ISNUMBER(SEARCH('Tagging Inputs'!$C$7,
  56.                                          'Template Ideas'!D2)) = TRUE,
  57.                          VLOOKUP('Tagging Inputs'!$C$7,
  58.                                  'Tagging Inputs'!$B$12:$C$38,
  59.                                  2,
  60.                                  FALSE),
  61.                          ""),
  62.                       ""),
  63.               IF(AND(ISNUMBER(SEARCH('Tagging Inputs'!$C$7,
  64.                                      'Template Ideas'!D2)),
  65.                      ISNUMBER(SEARCH('Tagging Inputs'!$C$8,
  66.                                      'Template Ideas'!D2))) = TRUE,
  67.                  "; ",
  68.                  ""),
  69.               IFERROR(IF(ISNUMBER(SEARCH('Tagging Inputs'!$C$8,
  70.                                          'Template Ideas'!D2)) = TRUE,
  71.                          VLOOKUP('Tagging Inputs'!$C$8,
  72.                                 'Tagging Inputs'!$B$12:$C$38,
  73.                                 2,
  74.                                  FALSE),
  75.                          ""),
  76.                       ""))

例33: =IF(AND(ISNUMBER(SEARCH("Company",A2)),ISNUMBER(SEARCH("ABC",A2))),"Company ABC","")

Raw:

  1. =IF(AND(ISNUMBER(SEARCH("Company",A2)),ISNUMBER(SEARCH("ABC",A2))),"Company ABC","")

Formula Editor:

  1. = IF(
  2.     AND(
  3.         ISNUMBER(
  4.             SEARCH(
  5.                 "Company",
  6.                 A2
  7.             )
  8.         ),
  9.         ISNUMBER(
  10.             SEARCH(
  11.                 "ABC",
  12.                 A2
  13.             )
  14.         )
  15.     ),
  16.     "Company ABC",
  17.     ""
  18. )

Expect:

  1. = IF(
  2.     AND(
  3.         ISNUMBER(SEARCH(
  4.             "Company",
  5.             A2
  6.         )),
  7.         ISNUMBER(SEARCH(
  8.             "ABC",
  9.             A2
  10.         ))
  11.     ),
  12.     "Company ABC",
  13.     ""
  14. )

例34: =SUM(IF($G$14:$G$5002="Liz S Salary",IF($Q$14:$Q$5000>=Summary!$F$6,IF($Q$14:$Q$5000<=Summary!$F$7,$R$14:$R$5000,0),0),0))

Raw:

  1. =SUM(IF($G$14:$G$5002="Liz S Salary",IF($Q$14:$Q$5000>=Summary!$F$6,IF($Q$14:$Q$5000<=Summary!$F$7,$R$14:$R$5000,0),0),0))

Formula Editor:

  1. = SUM(
  2.     IF(
  3.         $G$14:$G$5002 = "Liz S Salary",
  4.         IF(
  5.             $Q$14:$Q$5000 >= Summary!$F$6,
  6.             IF(
  7.                 $Q$14:$Q$5000 <= Summary!$F$7,
  8.                 $R$14:$R$5000,
  9.                 0
  10.             ),
  11.             0
  12.         ),
  13.         0
  14.     )
  15. )

Expect:

  1. = SUM(
  2.     IF(
  3.         $G$14:$G$5002 = "Liz S Salary",
  4.         IF(
  5.             $Q$14:$Q$5000 >= Summary!$F$6,
  6.             IF(
  7.                 $Q$14:$Q$5000 <= Summary!$F$7,
  8.                 $R$14:$R$5000,
  9.                 0
  10.             ),
  11.             0
  12.         ),
  13.         0
  14.     )
  15. )

例35: =IFERROR(VLOOKUP(AI57,SALESCODES!$B:$G,3,FALSE),IFERROR((VLOOKUP(AI57,SALESCODES!$C:$G,2,FALSE)),"Value if error"))

Raw:

  1. =IFERROR(VLOOKUP(AI57,SALESCODES!$B:$G,3,FALSE),IFERROR((VLOOKUP(AI57,SALESCODES!$C:$G,2,FALSE)),"Value if error"))

Formula Editor:

  1. = IFERROR(
  2.     VLOOKUP(
  3.         AI57,
  4.         SALESCODES!$B:$G,
  5.         3,
  6.         FALSE
  7.     ),
  8.     IFERROR(
  9.         (
  10.             VLOOKUP(
  11.                 AI57,
  12.                 SALESCODES!$C:$G,
  13.                 2,
  14.                 FALSE
  15.             )
  16.         ),
  17.         "Value if error"
  18.     )
  19. )

Expect:

  1. = IFERROR(
  2.     VLOOKUP(
  3.         AI57,
  4.         SALESCODES!$B:$G,
  5.         3,
  6.         FALSE
  7.     ),
  8.     IFERROR(
  9.         (VLOOKUP(
  10.             AI57,
  11.             SALESCODES!$C:$G,
  12.             2,
  13.             FALSE
  14.         )),
  15.         "Value if error"
  16.     )
  17. )

例36: =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)

Raw:

  1. =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)

Formula Editor:

  1. = SUMPRODUCT(
  2.     OFFSET(
  3.         $J$2,
  4.         0,
  5.         0,
  6.         SUM(
  7.             ROW()
  8.         )-1
  9.     ),
  10.     OFFSET(
  11.         $O$2,
  12.         11 - SUM(
  13.             ROW()
  14.         ),
  15.         0,
  16.         SUM(
  17.             ROW()
  18.         )-1
  19.     )
  20. ) + SUMPRODUCT(
  21.     OFFSET(
  22.         $J$2,
  23.         0,
  24.         0,
  25.         SUM(
  26.             ROW()
  27.         )-1
  28.     ),
  29.     OFFSET(
  30.         $O$2,
  31.         11 - SUM(
  32.             ROW()
  33.         ),
  34.         0,
  35.         SUM(
  36.             ROW()
  37.         )-1
  38.     )

Expect:

  1. = SUMPRODUCT(
  2.     OFFSET(
  3.         $J$2,
  4.         0,
  5.         0,
  6.         SUM(ROW()) - 1
  7.     ),
  8.     OFFSET(
  9.         $O$2,
  10.         11 - SUM(ROW()),
  11.         0,
  12.         SUM(ROW())-1
  13.     )
  14. ) + SUMPRODUCT(
  15.     OFFSET(
  16.         $J$2,
  17.         0,
  18.         0,
  19.         SUM(ROW())-1
  20.     ),
  21.     OFFSET(
  22.         $O$2,
  23.         11 - SUM(ROW()),
  24.         0,
  25.         SUM(ROW())-1
  26.     )

SUM 为单参数，不换行。里面嵌套的Row也是单参数（无参数）的情况，继续不换行。

例37: =SUM(ROUNDUP(COUNTIF(A1:AH1,">="&ROW(INDIRECT("A1:A"&MAX(A1:AH1),TRUE)))/12,0))

Raw:

  1. =SUM(ROUNDUP(COUNTIF(A1:AH1,">="&ROW(INDIRECT("A1:A"&MAX(A1:AH1),TRUE)))/12,0))

Formula Editor:

  1. = SUM(
  2.     ROUNDUP(
  3.         COUNTIF(
  4.             A1:AH1,
  5.             ">="&ROW(
  6.                 INDIRECT(
  7.                     "A1:A"&MAX(
  8.                         A1:AH1
  9.                     ),
  10.                     TRUE
  11.                 )
  12.             )
  13.         ) / 12,
  14.         0
  15.     )
  16. )

Expect:

  1. = SUM(
  2.     ROUNDUP(
  3.         COUNTIF(
  4.             A1:AH1,
  5.             ">=" & ROW(INDIRECT(
  6.                 "A1:A" & MAX(A1:AH1),
  7.                 TRUE
  8.             ))
  9.         ) / 12,
  10.         0
  11.     )
  12. )

Row 为单参数的情况

例38: =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")+1>12)*12-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)))

Raw:

  1. =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")+1>12)*12-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)))

Formula Editor:

  1. = SUMPRODUCT(
  2.     (
  3.         (
  4.             J2 >= YEAR(
  5.                 $C$2:$C$4
  6.             )
  7.         ) * (
  8.             (
  9.                 J2 <= YEAR(
  10.                     $D$2:$D$4
  11.                 )
  12.             ) + (
  13.                 $D$2:$D$4 = ""
  14.             )
  15.         )
  16.     ),
  17.     SUMIFS(
  18.         $G$2:$G$3,
  19.         $F$2:$F$3,
  20.         $B$2:$B$4
  21.     ),(
  22.         (
  24.             DATEDIF(
  25.                 IF(
  26.                     $C$2:$C$4 > DATE(
  27.                         J2,
  28.                         12,
  29.                         31
  30.                     ),
  31.                     0,
  32.                     $C$2:$C$4
  33.                 ),
  34.                 DATE(
  35.                     J2,
  36.                     12,
  37.                     31
  38.                 ),
  39.                 "m"
  40.             )+1 > 12
  41.         ) * 12 - IF(
  42.             $D$2:$D$4 <> "",
  43.             MONTH(
  44.                 D1:D3
  45.             )+2,
  46.             0
  47.         )
  48.     ) + (
  49.         DATEDIF(
  50.             IF(
  51.                 $C$2:$C$4 > DATE(
  52.                     J2,
  53.                     12,
  54.                     31
  55.                 ),
  56.                 0,
  57.                 $C$2:$C$4
  58.             ),
  59.             DATE(
  60.                 J2,
  61.                 12,
  62.                 31
  63.             ),
  64.             "m"
  65.         )+1 <= 12
  66.     ) * (
  67.         (
  68.             DATEDIF(
  69.                 IF(
  70.                     $C$2:$C$4 > DATE(
  71.                         J2,
  72.                         12,
  73.                         31
  74.                     ),
  75.                     0,
  76.                     $C$2:$C$4
  77.                 ),
  78.                 DATE(
  79.                     J2,
  80.                     12,
  81.                     31
  82.                 ),
  83.                 "m"
  84.             )+1
  85.         ) -- IF(
  86.             $D$2:$D$4 <> "",
  87.             MONTH(
  88.                 D1:D3
  89.             )+2,
  90.             0
  91.         )
  92.     )
  93. )

Expect:

  1. = SUMPRODUCT(
  2.     ((J2 >= YEAR($C$2:$C$4)) * ((J2 <= YEAR($D$2:$D$4)) + ($D$2:$D$4 = ""))),
  3.     SUMIFS(
  4.         $G$2:$G$3,
  5.         $F$2:$F$3, $B$2:$B$4
  6.     ),
  7.     ((DATEDIF(
  8.         IF(
  9.             $C$2:$C$4 > DATE(J2, 12, 31),
  10.             0,
  11.             $C$2:$C$4
  12.         ),
  13.         DATE(J2, 12, 31),
  14.         "m"
  15.     ) + 1 > 12) * 12 - IF(
  16.         $D$2:$D$4 <> "",
  17.         MONTH(D1:D3) + 2,
  18.         0
  19.     )) + (DATEDIF(
  20.         IF(
  21.             $C$2:$C$4 > DATE(J2, 12, 31),
  22.             0,
  23.             $C$2:$C$4
  24.         ),
  25.         DATE(J2, 12, 31),
  26.         "m"
  27.     ) + 1 <= 12) * ((DATEDIF(
  28.         IF(
  29.             $C$2:$C$4 > DATE(J2, 12, 31),
  30.             0,
  31.             $C$2:$C$4
  32.         ),
  33.         DATE(J2, 12, 31),
  34.         "m"
  35.     ) + 1) -- IF(
  36.         $D$2:$D$4 <> "",
  37.         MONTH(D1:D3) + 2,
  38.         0
  39.     ))
  40. )

将无嵌套的短函数放在一行，如DATE、MONTH
子表达式不引起换行

例39: =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")

Raw:

  1. =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")

Formula Editor:

  1. = IF(
  2.     AND(
  3.         C2 >= (
  4.             LEFT(
  5.                 INDEX(
  6.                     $I$1:$M$5,
  7.                     MATCH(
  8.                         B2,
  9.                         $I$1:$I$5,
  10.                         0
  11.                     ),
  12.                     MATCH(
  13.                         D2,
  14.                         $I$1:$M$1,
  15.                         0
  16.                     )
  17.                 ),
  18.                 FIND(
  19.                     "-",
  20.                     INDEX(
  21.                         $I$1:$M$5,
  22.                         MATCH(
  23.                             B2,
  24.                             $I$1:$I$5,
  25.                             0
  26.                         ),
  27.                         MATCH(
  28.                             D2,
  29.                             $I$1:$M$1,
  30.                             0
  31.                         )
  32.                     )
  33.                 )-1
  34.             ) * 1
  35.         ),
  36.         C2 <= MID(
  37.             INDEX(
  38.                 $I$1:$M$5,
  39.                 MATCH(
  40.                     B2,
  41.                     $I$1:$I$5,
  42.                     0
  43.                 ),
  44.                 MATCH(
  45.                     D2,
  46.                     $I$1:$M$1,
  47.                     0
  48.                 )
  49.             ),
  50.             FIND(
  51.                 "-",
  52.                 INDEX(
  53.                     $I$1:$M$5,
  54.                     MATCH(
  55.                         B2,
  56.                         $I$1:$I$5,
  57.                         0
  58.                     ),
  59.                     MATCH(
  60.                         D2,
  61.                         $I$1:$M$1,
  62.                         0
  63.                     )
  64.                 )
  65.             )+1,
  66.             256
  67.         ) * 1
  68.     ),
  69.     "Yes",
  70.     "No"
  71. )

Expect:

  1. = IF(
  2.     AND(
  3.         C2 >= (
  4.             LEFT(
  5.                 INDEX(
  6.                     $I$1:$M$5,
  7.                     MATCH(
  8.                         B2,
  9.                         $I$1:$I$5,
  10.                         0
  11.                     ),
  12.                     MATCH(
  13.                         D2,
  14.                         $I$1:$M$1,
  15.                         0
  16.                     )
  17.                 ),
  18.                 FIND(
  19.                     "-",
  20.                     INDEX(
  21.                         $I$1:$M$5,
  22.                         MATCH(
  23.                             B2,
  24.                             $I$1:$I$5,
  25.                             0
  26.                         ),
  27.                         MATCH(
  28.                             D2,
  29.                             $I$1:$M$1,
  30.                             0
  31.                         )
  32.                     )
  33.                 ) - 1
  34.             ) * 1
  35.         ),
  36.         C2 <= MID(
  37.             INDEX(
  38.                 $I$1:$M$5,
  39.                 MATCH(
  40.                     B2,
  41.                     $I$1:$I$5,
  42.                     0
  43.                 ),
  44.                 MATCH(
  45.                     D2,
  46.                     $I$1:$M$1,
  47.                     0
  48.                 )
  49.             ),
  50.             FIND(
  51.                 "-",
  52.                 INDEX(
  53.                     $I$1:$M$5,
  54.                     MATCH(
  55.                         B2,
  56.                         $I$1:$I$5,
  57.                         0
  58.                     ),
  59.                     MATCH(
  60.                         D2,
  61.                         $I$1:$M$1,
  62.                         0
  63.                     )
  64.                 )
  65.             ) + 1,
  66.             256
  67.         ) * 1
  68.     ),
  69.     "Yes",
  70.     "No"
  71. )

例40: =T(INDEX($B:$B,-1+6+ROWS($3:3)-IF(ROUNDUP(ROWS($3:3)/SUMPRODUCT(--($B:$B<>"")*COLUMN($A:$A)),0)=2,SUMPRODUCT(--($B:$B<>"")*COLUMN($A:$A)),0)))

Raw:

  1. =T(INDEX($B:$B,-1+6+ROWS($3:3)-IF(ROUNDUP(ROWS($3:3)/SUMPRODUCT(--($B:$B<>"")*COLUMN($A:$A)),0)=2,SUMPRODUCT(--($B:$B<>"")*COLUMN($A:$A)),0)))

Formula Editor:

  1. = T(
  2.     INDEX(
  3.         $B:$B,
  4.         -1+6 + ROWS(
  5.             $3:3
  6.         ) - IF(
  7.             ROUNDUP(
  8.             	ROWS(
  9.                     $3:3
  10.                 ) / SUMPRODUCT(
  11.                      -- (
  12.                         $B:$B <> ""
  13.                     ) * COLUMN(
  14.                         $A:$A
  15.                     )
  16.                 ),
  17.                 0
  18.             ) = 2,
  19.             SUMPRODUCT(
  20.                  -- (
  21.                     $B:$B <> ""
  22.                 ) * COLUMN(
  23.                     $A:$A
  24.                 )
  25.             ),
  26.             0
  27.         )
  28.     )
  29. )

Expect:

  1. = T(
  2.     INDEX(
  3.         $B:$B,
  4.         -1 + 6 + ROWS($3:3) - IF(
  5.             ROUNDUP(
  6.             	ROWS($3:3) / SUMPRODUCT(--($B:$B <> "") * COLUMN($A:$A)),
  7.                 0
  8.             ) = 2,
  9.             SUMPRODUCT(--($B:$B <> "") * COLUMN($A:$A)),
  10.             0
  11.         )
  12.     )
  13. )

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

Raw:

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

Formula Editor:

  1. = T(
  2.     INDEX(
  3.         $B:$B,
  4.         -1+6 + ROWS(
  5.             $3:3
  6.         ) - IF(
  7.             ROUNDUP(
  8.             	ROWS(
  9.                     $3:3
  10.                 ) / SUM(
  11.                      -- (
  12.                         $B:$B <> ""
  13.                     )
  14.                 ),
  15.                 0
  16.             ) = 2,
  17.             SUM(
  18.                  -- (
  19.                     $B:$B <> ""
  20.                 )
  21.             ),
  22.             0
  23.         )
  24.     )
  25. )

Expect:

  1. = T(
  2.     INDEX(
  3.         $B:$B,
  4.         -1 + 6 + ROWS($3:3) - IF(
  5.             ROUNDUP(
  6.             	ROWS($3:3) / SUM(--($B:$B <> "")),
  7.                 0
  8.             ) = 2,
  9.             SUM(--($B:$B <> "")),
  10.             0
  11.         )
  12.     )
  13. )

例42: = IF( OR(D2 <1000, D2 = 1000),"5", IF( AND(OR(D2 <10000, D2 = 10000 ), D2 >1000),"4", IF(AND(OR(D2 > 10000, D2 = 100000), D2 < 100000), "3", IF(AND(OR(D2 > 100000,D2 = 100000), D2 < 1000000),"2", IF(OR(D2 > 1000000,D4 = 1000000),"1","")))))

Raw:

  1. = IF( OR(D2 <1000, D2 = 1000),"5", IF( AND(OR(D2 <10000, D2 = 10000 ), D2 >1000),"4", IF(AND(OR(D2 > 10000, D2 = 100000), D2 < 100000), "3", IF(AND(OR(D2 > 100000,D2 = 100000), D2 < 1000000),"2", IF(OR(D2 > 1000000,D4 = 1000000),"1","")))))

Formula Editor:

  1. = IF(
  2.     OR(
  3.         D2 < 1000,
  4.         D2 = 1000
  5.     ),
  6.     "5",
  7.     IF(
  8.         AND(
  9.             OR(
  10.                 D2 < 10000,
  11.                 D2 = 10000
  12.             ),
  13.             D2 > 1000
  14.         ),
  15.         "4",
  16.         IF(
  17.             AND(
  18.                 OR(
  19.                     D2 > 10000,
  20.                     D2 = 100000
  21.                 ),
  22.                 D2 < 100000
  23.             ),
  24.             "3",
  25.             IF(
  26.                 AND(
  27.                     OR(
  28.                         D2 > 100000,
  29.                         D2 = 100000
  30.                     ),
  31.                     D2 < 1000000
  32.                 ),
  33.                 "2",
  34.                 IF(
  35.                     OR(
  36.                         D2 > 1000000,
  37.                         D4 = 1000000
  38.                     ),
  39.                     "1",
  40.                     ""
  41.                 )
  42.             )
  43.         )
  44.     )
  45. )

Expect:

  1. = IF(
  2.     OR(D2 < 1000, D2 = 1000),
  3.     "5",
  4.     IF(
  5.         AND(
  6.             OR(D2 < 10000, D2 = 10000),
  7.             D2 > 1000
  8.         ),
  9.         "4",
  10.         IF(
  11.             AND(
  12.                 OR(D2 > 10000, D2 = 100000),
  13.                 D2 < 100000
  14.             ),
  15.             "3",
  16.             IF(
  17.                 AND(
  18.                     OR(D2 > 100000, D2 = 100000),
  19.                     D2 < 1000000
  20.                 ),
  21.                 "2",
  22.                 IF(
  23.                     OR(D2 > 1000000, D4 = 1000000),
  24.                     "1",
  25.                     ""
  26.                 )
  27.             )
  28.         )
  29.     )
  30. )

AND、OR 无嵌套的话，可以考虑不换行，或者说不必要每个参数都换行

例43: =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))

Raw:

  1. =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))

Formula Editor:

  1. = SUM(
  2.      -- (
  3.         FREQUENCY(
  4.             IF(
  5.                 (
  6.                     'DAY 2'!$H$2:$H$500 = K2
  7.                 ) * NOT(
  8.                     'DAY 2'!$H$2:$H$500 = ""
  9.                 ),
  10.                 'DAY 2'!$C$2:$C$500
  11.             ),
  12.             'DAY 2'!$C$2:$C$500
  13.         ) > 0
  14.     )
  15. )

Expect:

  1. = SUM(--(FREQUENCY(
  2.     IF(
  3.         ('DAY 2'!$H$2:$H$500 = K2) * NOT('DAY 2'!$H$2:$H$500 = ""),
  4.         'DAY 2'!$C$2:$C$500
  5.     ),
  6.     'DAY 2'!$C$2:$C$500
  7. ) > 0))

例44: =TRIM(CONCATENATE(IF(COUNTIF(Alice!A:A,A1)>0,"Alice ",""),IF(COUNTIF(Bob!A:A,A1)>0,"Bob ",""),IF(COUNTIF(Chris!A:A,A1)>0,"Chris ","")))

Raw:

  1. =TRIM(CONCATENATE(IF(COUNTIF(Alice!A:A,A1)>0,"Alice ",""),IF(COUNTIF(Bob!A:A,A1)>0,"Bob ",""),IF(COUNTIF(Chris!A:A,A1)>0,"Chris ","")))

Formula Editor:

  1. = TRIM(
  2.     CONCATENATE(
  3.         IF(
  4.             COUNTIF(
  5.                 Alice!A:A,
  6.                 A1
  7.             ) > 0,
  8.             "Alice ",
  9.             ""
  10.         ),
  11.         IF(
  12.             COUNTIF(
  13.                 Bob!A:A,
  14.                 A1
  15.             ) > 0,
  16.             "Bob ",
  17.             ""
  18.         ),
  19.         IF(
  20.             COUNTIF(
  21.                 Chris!A:A,
  22.                 A1
  23.             ) > 0,
  24.             "Chris ",
  25.             ""
  26.         )
  27.     )
  28. )

Expect:

  1. = TRIM(CONCATENATE(
  2.     IF(
  3.         COUNTIF(Alice!A:A, A1) > 0,
  4.         "Alice ",
  5.         ""
  6.     ),
  7.     IF(
  8.         COUNTIF(Bob!A:A, A1) > 0,
  9.         "Bob ",
  10.         ""
  11.     ),
  12.     IF(
  13.         COUNTIF(Chris!A:A, A1) > 0,
  14.         "Chris ",
  15.         ""
  16.     )
  17. ))

例45: =IF(E1=" ",VLOOKUP(A3,'Study Time'!$A$13:$I$37,9,FALSE),IF(ISNUMBER(MATCH(A3,INDIRECT(E1&"!"&"A3:A11"),0)),VLOOKUP(A3,INDIRECT(E1&"!"&"A3:C11"),3,FALSE)+INDIRECT(E1&"!"&"H72"),VLOOKUP(A3,'Study Time'!$A$13:$I$37,9,FALSE)+INDIRECT(E1&"!"&"H72")))

Raw:

  1. =IF(E1=" ",VLOOKUP(A3,'Study Time'!$A$13:$I$37,9,FALSE),IF(ISNUMBER(MATCH(A3,INDIRECT(E1&"!"&"A3:A11"),0)),VLOOKUP(A3,INDIRECT(E1&"!"&"A3:C11"),3,FALSE)+INDIRECT(E1&"!"&"H72"),VLOOKUP(A3,'Study Time'!$A$13:$I$37,9,FALSE)+INDIRECT(E1&"!"&"H72")))

Formula Editor:

  1. = IF(
  2.     E1 = " ",
  3.     VLOOKUP(
  4.         A3,
  5.         'Study Time'!$A$13:$I$37,
  6.         9,
  7.         FALSE
  8.     ),
  9.     IF(
  10.         ISNUMBER(
  11.             MATCH(
  12.                 A3,
  13.                 INDIRECT(
  14.                     E1&"!"&"A3:A11"
  15.                 ),
  16.                 0
  17.             )
  18.         ),
  19.         VLOOKUP(
  20.             A3,
  21.             INDIRECT(
  22.                 E1&"!"&"A3:C11"
  23.             ),
  24.             3,
  25.             FALSE
  26.         ) + INDIRECT(
  27.             E1&"!"&"H72"
  28.         ),
  29.         VLOOKUP(
  30.             A3,
  31.             'Study Time'!$A$13:$I$37,
  32.             9,
  33.             FALSE
  34.         ) + INDIRECT(
  35.             E1&"!"&"H72"
  36.         )
  37.     )
  38. )

Expect:

  1. = IF(
  2.     E1 = " ",
  3.     VLOOKUP(
  4.         A3,
  5.         'Study Time'!$A$13:$I$37,
  6.         9,
  7.         FALSE
  8.     ),
  9.     IF(
  10.         ISNUMBER(MATCH(
  11.             A3,
  12.             INDIRECT(E1 & "!" & "A3:A11"),
  13.             0
  14.         )),
  15.         VLOOKUP(
  16.             A3,
  17.             INDIRECT(E1 & "!" & "A3:C11"),
  18.             3,
  19.             FALSE
  20.         ) + INDIRECT(E1 & "!" & "H72"),
  21.         VLOOKUP(
  22.             A3,
  23.             'Study Time'!$A$13:$I$37,
  24.             9,
  25.             FALSE
  26.         ) + INDIRECT(E1 & "!" & "H72")
  27.     )
  28. )

例46: =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)

Raw:

  1. =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)

Formula Editor:

  1. = ROUNDUP(
  2.     IF(
  3.         S5 <> "ü",
  4.         0,
  5.         SUMIF(
  6.             J3:J5000,
  7.             R5,
  8.             F3:F5000
  9.         )
  10.     ) + IF(
  11.         S6 <> "ü",
  12.         0,
  13.         SUMIF(
  14.             J3:J5000,
  15.             R6,
  16.             F3:F5000
  17.         )
  18.     ) + IF(
  19.         S7 <> "ü",
  20.         0,
  21.         SUMIF(
  22.             J3:J5000,
  23.             R7,
  24.             F3:F5000
  25.         )
  26.     ) + IF(
  27.         S8 <> "ü",
  28.         0,
  29.         SUMIF(
  30.             J3:J5000,
  31.             R8,
  32.             F3:F5000
  33.         )
  34.     ) + IF(
  35.         S9 <> "ü",
  36.         0,
  37.         SUMIF(
  38.             J3:J5000,
  39.             R9,
  40.             F3:F5000
  41.         )
  42.     ) / 1440,
  43.     0
  44. )

Expect:

  1. = ROUNDUP(
  2.     IF(
  3.         S5 <> "ü",
  4.         0,
  5.         SUMIF(J3:J5000, R5, F3:F5000)
  6.     ) + IF(
  7.         S6 <> "ü",
  8.         0,
  9.         SUMIF(J3:J5000, R6, F3:F5000)
  10.     ) + IF(
  11.         S7 <> "ü",
  12.         0,
  13.         SUMIF(J3:J5000, R7, F3:F5000)
  14.     ) + IF(
  15.         S8 <> "ü",
  16.         0,
  17.         SUMIF(J3:J5000, R8, F3:F5000)
  18.     ) + IF(
  19.         S9 <> "ü",
  20.         0,
  21.         SUMIF(J3:J5000, R9, F3:F5000)
  22.     ) / 1440,
  23.     0
  24. )

合并无嵌套的短函数到一行

例47: =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"))

Raw:

  1. =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"))

Formula Editor:

  1. = SUMPRODUCT(
  2.     (
  3.         (
  4.             COUNTIF(
  5.                 OFFSET(
  6.                     A1,
  7.                     ROW(
  8.                         A2:A33
  9.                     )-1,
  10.                     0
  11.                 ),
  12.                 "*apple*"
  13.             ) + COUNTIF(
  14.                 OFFSET(
  15.                     A1,
  16.                     ROW(
  17.                         A2:A33
  18.                     )-1,
  19.                     0
  20.                 ),
  21.                 "*seed*"
  22.             ) + COUNTIF(
  23.                 OFFSET(
  24.                     A1,
  25.                     ROW(
  26.                         A2:A33
  27.                     )-1,
  28.                     0
  29.                 ),
  30.                 "*turf*"
  31.             )
  32.         ) > 0
  33.     ) * (
  34.         B2:B33 = "B"
  35.     )
  36. )

Expect:

  1. = SUMPRODUCT(((COUNTIF(
  2.     OFFSET(
  3.         A1,
  4.         ROW(A2:A33) - 1,
  5.         0
  6.     ),
  7.     "*apple*"
  8. ) + COUNTIF(
  9.     OFFSET(
  10.         A1,
  11.         ROW(A2:A33) - 1,
  12.         0
  13.     ),
  14.     "*seed*"
  15. ) + COUNTIF(
  16.     OFFSET(
  17.         A1,
  18.         ROW(A2:A33) - 1,
  19.         0
  20.     ),
  21.     "*turf*"
  22. )) > 0) * (B2:B33 = "B"))

例48: =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)))

Raw:

  1. =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)))

Formula Editor:

  1. = IF(
  2.     OR(
  3.         A6 = "Brother",
  4.         A6 = "Sister"
  5.     ),
  6.     0.85 * IF(
  7.         COUNTIF(
  8.             B6:F6,
  9.             "Standard day"
  10.         ) = 5,
  11.         100,
  12.         IF(
  13.             COUNTIF(
  14.                 B6:F6,
  15.                 "Extended day"
  16.             ) = 5,
  17.             120,
  18.             COUNTIF(
  19.                 B6:F6,
  20.                 "Standard day"
  21.             ) * 23 + COUNTIF(
  22.                 B6:F6,
  23.                 "Extended day"
  24.             ) * 26
  25.         )
  26.     ),
  27.     IF(
  28.         COUNTIF(
  29.             B6:F6,
  30.             "Standard day"
  31.         ) = 5,
  32.         100,
  33.         IF(
  34.             COUNTIF(
  35.                 B6:F6,
  36.                 "Extended day"
  37.             ) = 5,
  38.             120,
  39.             COUNTIF(
  40.                 B6:F6,
  41.                 "Standard day"
  42.             ) * 23 + COUNTIF(
  43.                 B6:F6,
  44.                 "Extended day"
  45.             ) * 26
  46.         )
  47.     )
  48. )

Expect:

  1. = IF(
  2.     OR(A6 = "Brother", A6 = "Sister"),
  3.     0.85 * IF(
  4.         COUNTIF(B6:F6, "Standard day") = 5,
  5.         100,
  6.         IF(
  7.             COUNTIF(B6:F6, "Extended day") = 5,
  8.             120,
  9.             COUNTIF(B6:F6, "Standard day") * 23 + COUNTIF(B6:F6, "Extended day") * 26
  10.         )
  11.     ),
  12.     IF(
  13.         COUNTIF(B6:F6, "Standard day") = 5,
  14.         100,
  15.         IF(
  16.             COUNTIF(B6:F6, "Extended day") = 5,
  17.             120,
  18.             COUNTIF(B6:F6, "Standard day") * 23 + COUNTIF(B6:F6, "Extended day") * 26
  19.         )
  20.     )
  21. )

[例49:] =IF(ISNA(VLOOKUP($A3;#REF!;'8 History Fcst'!AH$1;FALSE));0;VLOOKUP($A3;#REF!;'8 History Fcst'!AH$1;FALSE))

Raw:

  1. =IF(ISNA(VLOOKUP($A3;#REF!;'8 History Fcst'!AH$1;FALSE));0;VLOOKUP($A3;#REF!;'8 History Fcst'!AH$1;FALSE))

Formula Editor:

  1. = IF(
  2.     ISNA(
  3.         VLOOKUP(
  4.             $A3;#REF!;'8 History Fcst'!AH$1;
  5.             FALSE
  6.         )
  7.     );
  8.     0;
  9.     VLOOKUP(
  10.         $A3;#REF!;'8 History Fcst'!AH$1;
  11.         FALSE
  12.     )
  13. )

Expect:

  1. = IF(
  2.     ISNA(VLOOKUP(
  3.         $A3; 
  4.         #REF!;
  5.         '8 History Fcst'!AH$1;
  6.         FALSE
  7.     ));
  8.     0;
  9.     VLOOKUP(
  10.         $A3; 
  11.         #REF!;
  12.         '8 History Fcst'!AH$1;
  13.         FALSE
  14.     )
  15. )

Formula Editor 的解析好像有点问题。一般Vlookup他的处理都是强制换行的，可能是因为 #REF!

[例50:] =IF($X12=$AJ$8;$DU12/$DU$8*AR$8;IF($X12=$AJ$7;AR$7/$DU$7*$DU12;AR$9/$DU$9*$DU12))

Raw:

  1. =IF($X12=$AJ$8;$DU12/$DU$8*AR$8;IF($X12=$AJ$7;AR$7/$DU$7*$DU12;AR$9/$DU$9*$DU12))

Formula Editor:

  1. = IF(
  2.     $X12 = $AJ$8;
  3.     $DU12 / $DU$8 * AR$8;
  4.     IF(
  5.         $X12 = $AJ$7;
  6.         AR$7 / $DU$7 * $DU12;
  7.         AR$9 / $DU$9 * $DU12
  8.     )
  9. )

Expect:

  1. = IF(
  2.     $X12 = $AJ$8;
  3.     $DU12 / $DU$8 * AR$8;
  4.     IF(
  5.         $X12 = $AJ$7;
  6.         AR$7 / $DU$7 * $DU12;
  7.         AR$9 / $DU$9 * $DU12
  8.     )
  9. )
添加新批注
在作者公开此批注前，只有你和作者可见。
回复批注