[第105期]剩余调休统计[已结]

delete_007

8月的模拟结果显示成“03”也是可以的。不用考虑三位数的情况。
说明：
   1.根据“加班”和“使用调休”信息，统计月底“剩余调休”时间
   2.“使用调休”按先进先出法考虑，即先使用前面剩余的调休
   3.超过3个月仍未使用的调休时间失效，予以清空
   4.调休不会透支，本月不会使用下月的调休
   5.B2:M2>0，B3:M3>=0
   6.B4:M4为模拟结果区，不允许引用；B5:M5为答题区

要求：
   1.纯函数题，不允许VBA、定义名称、辅助单元格
   2.B5公式右拉至M5，或者B5:M5多单元格数组公式
   3.公式需通过2003版本验证
   4.公式需通过随机数据测试

评分：
   1.公式长度（含等号）<=230字符，评1分
   2.公式长度（含等号）<=190字符，评2分

竞赛日期：2014-4-24至2014-5-20






该贴已经同步到 delete_007的微博

Bodhidharma

先送了再說，有空再修，沒空至少不會過期…
B5儲存格數組公式右拉：

1. =RIGHT(SUBSTITUTE(TEXT(SUM(TEXT(--MID(A5&"000000",{1,4,7},2)-TEXT(B3-MMULT(-MID(A5&"000000",{1,4,7},2),-({1,2,3}>ROW(1:3))),"[<0]!0"),"[<0]!0")*10^{6,4,2})+B2-MAX(0,B3-SUM(--MID(A5&"000000",{1,4,7},2))),"00+00+00+00"),"00+",""),8)
   

复制代码

chunlin1wang

A5是0，可以取巧了，B5：

1. =TEXT(SUM(TEXT(MMULT(N(ROW(2:5)>COLUMN($A:$D)),--MID(REPT(0,8-LEN(A5))&A5&B2,{1;4;7;9},2))-B3,"[<]\0")*{-10000;9900;99;1}),"[>1e4]00+00+00;[>100]00+00;0")

复制代码

丢丢表格

来个很笨的

1. =SUBSTITUTE(TEXT(SUM(IF(MMULT(-MID(RIGHT(10^8&A5,8),{1,4,7},2),-({1;2;3}<{2,3,4}))>B3,SUMIF(1:1,B1-{2,1,0},2:2),TEXT(MMULT(-MID(RIGHT(10^8&A5,8)&B2,{1,4,7,9},2),-({1;2;3;4}<{3,4,5}))-B3,"0;!0"))*10^{4,2,0}),"00+00+00"),"00+",)

复制代码

共 226 字

来个最笨的

1. =TEXT(MEDIAN(SUM(--(0&MID(A5,{1,4,7},2)))-B3-N(A2),SUMIF(1:1,B1-2,2:2),),"00+;;")&TEXT(MEDIAN(SUM(--(0&MID(A5,{1,4,7},2)))-B3,N(A2),),"00+;;")&TEXT(MIN(SUM(--(0&MID(A5,{1,4,7},2)))+B2-B3,B2),"0")

复制代码

共195 字

1. =TEXT(MEDIAN(SUM(-(0&MID(A5,{1,4,7},2)),B3,A2),-SUMIF(1:1,B1-2,2:2),),";00+;")&TEXT(MEDIAN(SUM(--(0&MID(A5,{1,4,7},2)))-B3,A2,),"00+;;")&MIN(SUM(--(0&MID(A5,{1,4,7},2)))+B2-B3,B2)

复制代码

共 179 字

笨笨四

154字符：

1. =SUBSTITUTE(TEXT(SUM(LARGE(-TEXT(MID(10^6+(SUBSTITUTE(E5,"+",0)+F2%/10)/999,{2;5;9;12},3)-F3,"0;!0"),{2;3;4}-{0,1})*{-1,1}*10^{4;2;0}),"00+00+00"),"00+",)

复制代码

补充内容 (2014-5-14 12:14):
141（有局限性，四个月累加调休减当月调休不能超过3位数，）
RIGHT(SUBSTITUTE(TEXT(SUM(TEXT(MID(10^8+(SUBSTITUTE(A5,"+",)/1%+B2)/99%,{2;4;6;8},2)-B3,"0;!0")*10^{6;4;2;0})*99%+0.5,"00+00+00"),"00+",),8)

huahua2005

B5单元格公式如下，右拉至M5，212字符~

1. =TEXT(IF(B1<2,B2-B3,SUM(MMULT({-1,1,0,0;0,-1,1,0;0,0,-1,1},--TEXT(SUM(--TEXT(MID(A5,{1;4;7},2),"0;;0;!0"))+B2-B3-SUBTOTAL(9,OFFSET(B2,,{-2;-1;0;99},,{3;2;1;1})),"0;!0"))*10^{4;2;0})),"[>1e4]00+00+00;[<1e2];00+00")
   

复制代码

周小事儿

唉，我又来回复了
156字符

1. =RIGHT(SUBSTITUTE(TEXT(SUM(TEXT(MID(FIXED((SUBSTITUTE(A4,"+",0)*1000+B2)/999,3)*10^-9&0,3*$B1:$E1,3)-B3,"0;!0")*10^{6,4,2,0}*99%,0.5),"00+00+00"),"00+",),8)

复制代码

若考虑B3:M3多个连续为0或者皆为0
公式为

1. =SUBSTITUTE(TEXT(SUM(MMULT({1,-1,0,0;0,1,-1,0;0,0,1,-1},-TEXT(MID(FIXED((SUBSTITUTE(A5,"+",0)*1000+B2)/999,3)*10^-9&0&0,{3;6;9;12},3)-B3,"0;!0"))*10^{4;2;0}),"00+00+00"),"00+",)

复制代码

周小事儿

修正为
147字符

1. =RIGHT(SUBSTITUTE(TEXT(SUM(TEXT(MID(10^12+(SUBSTITUTE(A5,"+",0)*1000+B2)/0.999,3*$B1:$E1,3)-B3,"0;!0")*10^{6,4,2,0}*99%,0.5),"00+00+00"),"00+",),8)

复制代码

支持全为0的
166字符

1. =SUBSTITUTE(TEXT(SUM(MMULT({1,-1,0,0;0,1,-1,0;0,0,1,-1},-TEXT(MID(10^12+(SUBSTITUTE(A5,"+",0)*1000+B2)/0.999,{2;5;8;11},3)-B3,"0;!0"))*10^{4;2;0}),"00+00+00"),"00+",)

复制代码

周小事儿

上贴手误
148字符

1. =RIGHT(SUBSTITUTE(TEXT(SUM(TEXT(MID(10^12+(SUBSTITUTE(A5,"+",0)*1000+B2)/0.999,{2,5,8,11},3)-B3,"0;!0")*10^{6,4,2,0}*99%,0.5),"00+00+00"),"00+",),8)

复制代码

166字符，适用使用调休为0的情况：

1. =SUBSTITUTE(TEXT(SUM(MMULT({1,-1,0,0;0,1,-1,0;0,0,1,-1},-TEXT(MID(10^12+(SUBSTITUTE(A5,"+",0)*1000+B2)/0.999,{2;5;8;11},3)-B3,"0;!0"))*10^{4;2;0}),"00+00+00"),"00+",)

复制代码

wangg913

原以为是求结果。

1. =A5+B2-B3+MIN(,B3-A5+SUM(A2:INDEX(2:2,B1-1)))

复制代码

现改为：

1. =SUBSTITUTE(TEXT(SUM(MMULT({1,-1,0,0;0,1,-1,0;0,0,1,-1},-TEXT(MMULT(-({1;2;3;4}>={1,2,3,4}),-LEFT(RIGHT("00+"&A5&100+B2,{11;8;5;2}),2))-B3,"0;\0;0"))*10^{4;2;0}),"00+00+00"),"00+",)

复制代码

1. =SUBSTITUTE(TEXT(SUM(MMULT({1,-1,0,0;0,1,-1,0;0,0,1,-1},-TEXT(MMULT(-(ROW(2:5)>$B1:$E1),-LEFT(RIGHT(0&0&A5&100+B2,{11;8;5;2}),2))-B3,"0;\0"))*10^{4;2;0}),"00+00+00"),"00+",)

复制代码