用数组公式完美解决了问题
非常感谢!
- Sub arrfm1()
- Range("F3").FormulaArray = "=SUM(TEXT({1,-1}%,SUBSTITUTE(TRANSPOSE(0&B3:E3),"":"","";""))*{1,-1})"
- Range("G3").FormulaArray = "=SUM(--TEXT(MMULT(TEXT({1,-1}%,SUBSTITUTE(TRANSPOSE(0&B3:E3),"":"","";""))*{1,-1},{1;1}),""3;!0;1""))-1"
- Range("H3").FormulaArray = "=SUM(N($G$3:$G$6*1000+$F$3:$F$6>Y3*1000+X3))+1"
- Range("F3:H6").FillDown
- End Sub
- Sub arrfm2()
- Range("X3").FormulaArray = "=SUM(IFERROR(--N(OFFSET(L3,,{0,3,6,9;2,5,8,11})),)*{1;-1})" '文本型,N改为T "=SUM(IFERROR(--T(OFFSET(L3,,{0,3,6,9;2,5,8,11})),)*{1;-1})"
- Range("Y3").FormulaArray = "=SUM(--TEXT(MMULT({1,1},IFERROR(--N(OFFSET(L3,,{0,3,6,9;2,5,8,11})),)*{1;-1}),""3;!0;1""))-1" ' "=SUM(--TEXT(MMULT({1,1},IFERROR(--T(OFFSET(L3,,{0,3,6,9;2,5,8,11})),)*{1;-1}),""3;!0;1""))-1"
- Range("Z3").FormulaArray = "=SUM(N($G$3:$G$6*1000+$F$3:$F$6>Y3*1000+X3))+1"
- Range("X3:Z6").FillDown
- End Sub
- Sub arrfm3()
- Range("AA3").FormulaArray = "=SUM(IFERROR(TEXT(L3:U3-N3:W3,""3;;1"")*(L$2:U$2>0),))-1"
- Range("AA3:AA6").FillDown
- End Sub
复制代码
|