手工公式到Manual公式Google Sheet

Question

我一直在把这些公式拖到Google页面的末尾，这使得页面变得更重，并且在加载/执行操作时会造成很大的干扰。

我正在尝试将这些公式转换为Array。

任何帮助都将不胜感激。

板条连接https://docs.google.com/spreadsheets/d/1pgVGJxkw39uelgPU9ceZLXflnP8KaFGou4rJfSsfVXc/edit#gid=0

=IF(AND(E3<>"",B3<>""),EDATE(E3,6),"")

=IF(F3="Confirmed","13",IF(F3="Probation","0",IF(F3="Terminate","",IF(F3="",""))))

=IF(F2="Confirmed",IFERROR(IF(MONTH(DATEVALUE(TEXT(G2,"mmm")&" 1"))=1,12,IF(MONTH(DATEVALUE(TEXT(G2,"mmm")&" 1"))=2,11,IF(MONTH(DATEVALUE(TEXT(G2,"mmm")&" 1"))=3,10,IF(MONTH(DATEVALUE(TEXT(G2,"mmm")&" 1"))=4,9,IF(MONTH(DATEVALUE(TEXT(G2,"mmm")&" 1"))=5,8,IF(MONTH(DATEVALUE(TEXT(G2,"mmm")&" 1"))=6,7,IF(MONTH(DATEVALUE(TEXT(G2,"mmm")&" 1"))=7,6,IF(MONTH(DATEVALUE(TEXT(G2,"mmm")&" 1"))=8,5,IF(MONTH(DATEVALUE(TEXT(G2,"mmm")&" 1"))=9,4,IF(MONTH(DATEVALUE(TEXT(G2,"mmm")&" 1"))=10,3,IF(MONTH(DATEVALUE(TEXT(G2,"mmm")&" 1"))=11,2,IF(MONTH(DATEVALUE(TEXT(G2,"mmm")&" 1"))=12,1))))))))))))*13/12),IF(F2="Probation","0",""))

=IF(F2<>"",K2+IF(I2=0,H2-L2,I2-L2),"")

Answer 1

以下四个公式应该适用于您。

公式#1

=ArrayFormula(IF((E3:E<>"")*(B3:B<>""),EDATE(E3:E,6),""))

公式#2

=ArrayFormula(IFS(F3:F="Confirmed","13",F3:F="Probation",0,F3:F="Terminate","",F3:F="",""))

公式#3

=ArrayFormula(IF(F3:F="Confirmed",
  IFERROR(IF(MONTH(DATEVALUE(TEXT(G3:G,"mmm")&" 1"))=1,12,
          IF(MONTH(DATEVALUE(TEXT(G3:G,"mmm")&" 1"))=2,11,
          IF(MONTH(DATEVALUE(TEXT(G3:G,"mmm")&" 1"))=3,10,
          IF(MONTH(DATEVALUE(TEXT(G3:G,"mmm")&" 1"))=4,9,
          IF(MONTH(DATEVALUE(TEXT(G3:G,"mmm")&" 1"))=5,8,
          IF(MONTH(DATEVALUE(TEXT(G3:G,"mmm")&" 1"))=6,7,
          IF(MONTH(DATEVALUE(TEXT(G3:G,"mmm")&" 1"))=7,6,
          IF(MONTH(DATEVALUE(TEXT(G3:G,"mmm")&" 1"))=8,5,
          IF(MONTH(DATEVALUE(TEXT(G3:G,"mmm")&" 1"))=9,4,
          IF(MONTH(DATEVALUE(TEXT(G3:G,"mmm")&" 1"))=10,3,
          IF(MONTH(DATEVALUE(TEXT(G3:G,"mmm")&" 1"))=11,2,
          IF(MONTH(DATEVALUE(TEXT(G3:G,"mmm")&" 1"))=12,1))))))))))))*13/12),
  IF(F3:F="Probation",0,"")))

公式#4

=ArrayFormula(IF(F3:F<>"",O3:O+IF(K3:K=0,I3:I-P3:P,K3:K-P3:P),""))

更新：尝试以下公式来计算分配的休假月，替换上面的第三个公式：

=ArrayFormula(IF(LEN(G3:G),
                (13 - MONTH(DATEVALUE(TEXT(G3:G,"mmm")&" 1"))) * 13/12,
                ""))

UPDATE#2修改了上面的公式，添加了两个条件检查，我忽略了这一点。

=ArrayFormula(IF(LEN(G4:G),
   IF(F4:F="Confirmed",
     (13 - MONTH(DATEVALUE(TEXT(G4:G,"mmm")&" 1"))) * 13/12,
     IF(F4:F="Probation",0,0)),""))

请注意，可以删除最后一个IF测试，并将其替换为"0"，但您可能希望将其作为将来的参考。在所有公式中，我还将" 0“更改为仅为数字0。

Answer 2

试用期休假不包括"C“值：

=ArrayFormula(IFERROR(1/(1/(IF(B3:B="",,LEN(SUBSTITUTE(TRIM(TRANSPOSE(QUERY(TRANSPOSE(IF(
 IFERROR(VLOOKUP(ROW(Jan!A3:A), {ROW(Jan!A3:A), Jan!C3:AG, Feb!C3:AE, Mar!C3:AG, Apr!C3:AF, May!C3:AG, Jun!C3:AF, Jul!C3:AG, Aug!C3:AG, Sep!C3:AF, Oct!C3:AG, Nov!C3:AF, Dec!C3:AG}, 
 IF(1=SPLIT(REPT("01", DATE(YEAR(E3:E), MONTH(E3:E)+6, DAY(E3:E))-("01/01/2020"-1)), 0), COLUMN(B:AAA), )*1, 0))<>"C", 
 IFERROR(VLOOKUP(ROW(Jan!A3:A), {ROW(Jan!A3:A), Jan!C3:AG, Feb!C3:AE, Mar!C3:AG, Apr!C3:AF, May!C3:AG, Jun!C3:AF, Jul!C3:AG, Aug!C3:AG, Sep!C3:AF, Oct!C3:AG, Nov!C3:AF, Dec!C3:AG}, 
 IF(1=SPLIT(REPT("01", DATE(YEAR(E3:E), MONTH(E3:E)+6, DAY(E3:E))-("01/01/2020"-1)), 0), COLUMN(B:AAA), )*1, 0)), )),,9^9))), " ", )))))))

补偿叶：

=ArrayFormula(IFERROR(1/(1/(IF(B3:B="",,LEN(SUBSTITUTE(TRIM(TRANSPOSE(QUERY(TRANSPOSE(IF(
 {Jan!C3:AG, Feb!C3:AG, Mar!C3:AG, Apr!C3:AG, May!C3:AG, Jun!C3:AG, Jul!C3:AG, Aug!C3:AG, Sep!C3:AG, Oct!C3:AG, Nov!C3:AG, Dec!C3:AG}="C", 
 {Jan!C3:AG, Feb!C3:AG, Mar!C3:AG, Apr!C3:AG, May!C3:AG, Jun!C3:AG, Jul!C3:AG, Aug!C3:AG, Sep!C3:AG, Oct!C3:AG, Nov!C3:AG, Dec!C3:AG}, )),,9^9))), " ", )))))))

不管试用期如何，今年的假期总数

=ArrayFormula(IFERROR(1/(1/(IF(B3:B="",,LEN(SUBSTITUTE(TRIM(TRANSPOSE(QUERY(TRANSPOSE( 
 {Jan!C3:AG, Feb!C3:AG, Mar!C3:AG, Apr!C3:AG, May!C3:AG, Jun!C3:AG, Jul!C3:AG, Aug!C3:AG, Sep!C3:AG, Oct!C3:AG, Nov!C3:AG, Dec!C3:AG}),,9^9))), " ", )))))))