本帖最后由 丢丢表格 于 2021-5-31 17:32 编辑
静听溪流 20楼 =SUM(MMULT(TEXT(SUBTOTAL(9,OFFSET(B4,,,,COLUMN(A:L)))-518,"0;!0;0")/1,MUNIT(12)-(ROW($1:$12)+1=COLUMN(A:L)))*B$2:M$2)
梅州 37 楼
=SUM({1;-1}*ABS(B4:M4-{-1;1}*TEXT(SUMIF($1:$1,"<"&B$1:M$1+1,4:4)-R$2,"0;!0"))/2*B$2:M$2)
这两个公式特别推荐,很好的解题思路,赞一个!下面这个公式就是以这两位老师的思路进行归纳的。
归纳公式 =SUM(TEXT(SUMIF($1:$1,"<"&B$1:M$1+{0;1},4:4)-R$2,"0;!0")*{-1;1}*B$2:M$2)
swyrlbq 18楼
=SUM((B4:M4-TRANSPOSE(FREQUENCY(ROW(INDIRECT("1:"&R$2)),SUBTOTAL(9,OFFSET(B4,,,,B$1:L$1)))))*B$2:M$2)
这个公式的思路很新颖
solextrade 2楼
=SUM(MOD(SMALL(IF({1;0},TEXT(SUBTOTAL(9,OFFSET(B4,,,,B$1:M$1))-R$2,"0;!0"),B4:M4)+10^(B$1:M$1+2),B$1:M$1*2-1),1000)*B$2:M$2)
魚。。 3楼
=SUM(IF(SUBTOTAL(9,OFFSET(B4,,,,COLUMN(A:L)))-R$2<B4:M4,TEXT(SUBTOTAL(9,OFFSET(B4,,,,COLUMN(A:L)))-R$2,"0;!0"),B4:M4)*B$2:M$2)
魚。。 4楼
=SUM(SUBSTITUTE(TEXT(SUMIF(OFFSET(B4,,,,COLUMN(A:L)),">0")-R$2,"[<0]!0;[<"&B4:M4&"]0;!"&SUBSTITUTE(B4:M4,0,"魚")),"魚",0)*B$2:M$2)
2datou 5楼
=IF(B4-518>0,SUMPRODUCT(B4:M4,$B$2:$M$2)-518*$B$2,IF(AND(B4-518<0,SUM(B4:C4)-518>0),SUMPRODUCT(B4:M4,$B$2:$M$2)-B4*$B$2-(518-B4)*$C$2,IF(SUM(B4:C4)-518<0,SUMPRODUCT(B4:M4,$B$2:$M$2)-B4*$B$2-C4*$C$2-(518-SUM(B4:C4))*$D$2)))
sucx007 6楼
=SUM(TEXT(SUMIF(OFFSET(B4,,,,B$1:M$1),">0")-R$2,"[>"&B4:M4&"]"""&B4:M4&""";\0;0")*B$2:M$2)
cinlo 13楼
=SUM(TEXT(TEXT(SUMIF(OFFSET(B4,,,,B$1:M$1),">0")-R$2-B4:M4,"!0;-0")+B4:M4,"0;!0")*B$2:M$2)
Meteor-渣渣 17楼
=SUM(IF(SUBTOTAL(9,OFFSET($A4,,,,COLUMN(A:L)))>$R$2,B4:M4,IF(SUBTOTAL(9,OFFSET($B4,,,,COLUMN(A:L)))>$R$2,SUBTOTAL(9,OFFSET($B4,,,,COLUMN(A:L)))-$R$2,))*B$2:M$2)
jisijie630 19楼 =SUM(TEXT(IF(B$1:M$1>MIN(IF(SUMIF(OFFSET(B4,,,,COLUMN(A:L)),">0")>R$2,B$1:M$1)),B4:M4,SUMIF(OFFSET(B4,,,,COLUMN(A:L)),">0")-R$2),"0;!0;!0")*B$2:M$2)
预留公式 41楼
=SUM(TEXT(B4:M4-TEXT(SUMIF($1:$1,"<"&B$1:M$1,4:4)-R$2,"!0;0"),"0;!0")*B$2:M$2)
|