[第75期]和我一起值班最多的人是谁？

chenhh803

题目说明及要求


我叫王五，现供职于XX公司。
公司仓库存放有贵重存货，因此要安排专人负责夜间值班巡查，每组三人、不会空岗。
夜间值班的人员要根据每日的上班情况临时拟定，因此基本没有什么规律可循。
左边表格是这10天的值班人员表，请求出和我一起值班最多的人是谁？
比如：5-1、5-3、5-5、5-6、5-7、5-10 我都值班了，这6天里（已用条件格式绿色显示）
张一 4次、张三丰 3次、张三 2次、李四光 2次、李四 1次
所以和我一起值班次数最多的人是 张一，请在C15用公式求出。
另外值班员相对固定，因此和我一起值班的人一定会有重复，如果出现多个最大相同频率的人，则任选一个。


要求：
不能使用 VBA、定义名称等，仅用一个公式求出。不限定公式长度、不限定使用版本
题目截止日：2011-6-13
可能公式较长，为易于审题、理解，请给出主体函数思路说明


得分：
公式正确 技术分2分，精彩思路 另外奖励1分。

附件

请参赛者注意:夜间值班的人员要根据每日的上班情况临时拟定，因此基本没有什么规律可循,例如其它值班人名字也可以为(王二麻子,张七,张八等),而且王五的值班日期也是临时安排的,所以不能以现有的王五的值班日期来取巧.给各位带来的不便,敬请原谅!

如果对题目有疑问、建议，请给楼主或wangg913发短信，以便统一变更，谢谢。--wangg913

[ 本帖最后由 chenhh803 于 2011-6-14 00:19 编辑 ]

悟空师弟

这里可以回复答案吗？

现在可以答题了，--wangg913

176字符：
=INDEX(T(OFFSET(B3,MOD(ROW(1:30)-1,10),ROW(1:30)/10.1)),MODE(IF(MMULT(-(B3:D12="王五"),ROW(1:3))*(B3:D12<>"王五"),MATCH(B3:D12,T(OFFSET(B3,MOD(ROW(1:30)-1,10),ROW(1:30)/10.1)),))))

解题思路：
1、用T(OFFSET(B3,MOD(ROW(1:30)-1,10),ROW(1:30)/10.1))得到一个包含所有姓名的垂直数组；
2、IF + MATCH 得到包含“王五”的行且“王五”不计在内的姓名在这个垂直数组中的相对位置；
3、MODE得到这些相对位置最多的一个数；
4、用INDEX返回最终结果。

[ 本帖最后由 悟空师弟 于 2011-5-16 21:36 编辑 ]

绿玉人

1. =INDEX(INDEX(T(OFFSET(B2,ROW(3:32)/3,MOD(ROW(3:32),3))),N(IF(1,SMALL(IF(MATCH(T(OFFSET(B2,ROW(3:32)/3,MOD(ROW(3:32),3))),T(OFFSET(B2,ROW(3:32)/3,MOD(ROW(3:32),3))),)=ROW(1:30),ROW(1:30)),ROW(INDIRECT("1:"&SUM(1/COUNTIF(B3:D12,B3:D12)))))))),MOD(LARGE(MMULT(COLUMN(A:J)^0,COUNTIF(OFFSET(B2,COUNTIF(OFFSET(B2,ROW(1:10),,,3),"王五")*ROW(1:10),,,3),TRANSPOSE(INDEX(T(OFFSET(B2,ROW(3:32)/3,MOD(ROW(3:32),3))),N(IF(1,SMALL(IF(MATCH(T(OFFSET(B2,ROW(3:32)/3,MOD(ROW(3:32),3))),T(OFFSET(B2,ROW(3:32)/3,MOD(ROW(3:32),3))),)=ROW(1:30),ROW(1:30)),ROW(INDIRECT("1:"&SUM(1/COUNTIF(B3:D12,B3:D12)))))))))))*100+TRANSPOSE(ROW(INDIRECT("1:"&SUM(1/COUNTIF(B3:D12,B3:D12))))),2),100))

复制代码

[ 本帖最后由 绿玉人 于 2011-6-6 08:40 编辑 ]

bluexuemei

1. =INDIRECT(TEXT(SMALL(ROW(3:12)*100+{2,3,4},MODE(MATCH(T(INDIRECT(TEXT(SMALL(IF(MMULT(N(B3:D12="王五"),ROW(1:3)^0)*(B3:D12<>"王五"),ROW(3:12)*100+{2,3,4}),ROW(INDIRECT("1:"&COUNTIF(B3:D12,"王五")*2))),"r0c00"),)),T(INDIRECT(TEXT(SMALL(ROW(3:12)*100+{2,3,4},ROW(1:30)),"r0c00"),)),))),"r0c00"),)

复制代码

2007版本,字符数287.
主体思路,先用T(INDIRECT(TEXT(SMALL(ROW(3:12)*100+{2,3,4},ROW(1:30)),"r0c00"),))求出这十天所有值班人次的一维数组{"王五";"张三丰";"张三";"张一平";"张三丰";"张五";"王五";"李四光";"张一";"张三丰";"李四光";"张三";"李四光";"张三";"王五";"王五";"张三丰";"张一";"张一";"李四";"王五";"张三";"李四光";"张一平";"张七";"张三丰";"张三";"张一";"张三丰";"王五"}

接着用T(INDIRECT(TEXT(SMALL(IF(MMULT(N(B3:D12="王五"),ROW(1:3)^0)*(B3:D12<>"王五"),ROW(3:12)*100+{2,3,4}),ROW(INDIRECT("1:"&COUNTIF(B3:D12,"王五")*2))),"r0c00"),))求得和王五一起值班的人次的一维数组{"张三丰";"张三";"李四光";"张一";"李四光";"张三";"张三丰";"张一";"张一";"李四";"张一";"张三丰"},

接着用MATCH(T(INDIRECT(TEXT(SMALL(IF(MMULT(N(B3:D12="王五"),ROW(1:3)^0)*(B3:D12<>"王五"),ROW(3:12)*100+{2,3,4}),ROW(INDIRECT("1:"&COUNTIF(B3:D12,"王五")*2))),"r0c00"),)),T(INDIRECT(TEXT(SMALL(ROW(3:12)*100+{2,3,4},ROW(1:30)),"r0c00"),)),)求出和王五一起值班的人次在所有值班人次中的位置{2;3;8;9;8;3;2;9;9;20;9;2}

再用Mode求出除了王五以外出现频率最大的人的位置{9}
最后用Indirect取出姓名..献丑了!

[ 本帖最后由 bluexuemei 于 2011-6-10 13:12 编辑 ]

Zaezhong

1. =INDEX(T(OFFSET(B2,ROW(3:32)/3,MOD(ROW(3:32),3))),MODE(IF(COUNTIF(OFFSET(B2:D2,ROW(1:10),),"王五")*(B3:D12<>"王五"),MATCH(B3:D12,T(OFFSET(B2,ROW(3:32)/3,MOD(ROW(3:32),3))),))))

复制代码

172

[ 本帖最后由 Zaezhong 于 2011-6-6 16:39 编辑 ]

wangyuhu

第一次：
2003版（取巧未遂 ）
=INDEX({"李四","李四光","张七","张三","张三丰","张五","张一","张一平"},RIGHT(MAX(MMULT(TRANSPOSE(COUNTIF(OFFSET(B1:D1,LARGE((B3:D12="王五")*{2;3;4;5;6;7;8;9;10;11},{1;2;3;4;5;6;7;8;9;10}),),{"李四","李四光","张七","张三","张三丰","张五","张一","张一平"})),ROW(1:10)^0)*10+ROW(1:8))))

---------------------
第二次：
=INDIRECT(TEXT(MAX((COUNTIF(B3:D12,">="&B3:D12)=MODE(LARGE(COUNTIF(B3:D12,">="&B3:D12)*(B3:D12<>"王五")*MMULT((ROW(1:10)=TRANSPOSE(LARGE((B3:D12="王五")*{1;2;3;4;5;6;7;8;9;10},{1;2;3;4;5;6;7;8;9;10})))*1,ROW(1:10)^0),ROW(INDIRECT("1:"&30-3*COUNTIF(B3:D12,"王五"))))))*(ROW(3:12)*10+{2,3,4})),"r00c0"),)

简化209字符
=INDIRECT(TEXT(MAX((COUNTIF(B3:D12,">"&B3:D12)=MODE(LARGE(COUNTIF(B3:D12,">"&B3:D12)*(B3:D12<>"王五")*MMULT(N(B3:D12="王五"),{1;1;1}),ROW(INDIRECT("1:"&30-3*COUNTIF(B3:D12,"王五"))))))*(ROW(3:12)&{2,3,4})),"r0c0"),)

简化189字符
=INDIRECT(TEXT(MAX((COUNTIF(B3:D12,">"&B3:D12)=MODE(COUNTIF(B3:D12,">"&B3:D12)*IF(B3:D12="王五",ROW(1:10)/9,1)*IF(MMULT(N(B3:D12="王五"),{1;1;1}),1,ROW(1:10)/7)))*(ROW(3:12)&{2,3,4})),"r0c0"),)

[ 本帖最后由 wangyuhu 于 2011-5-25 21:57 编辑 ]

chunlin1wang

=INDEX(T(OFFSET(B3,INT((ROW(1:30)-1)/3),MOD(ROW(1:30)-1,3))),MODE(IF(MMULT(1*(B3:D12=B3),ROW(1:3)^0)*(B3:D12<>B3),MATCH(B3:D12,T(OFFSET(B3,INT((ROW(1:30)-1)/3),MOD(ROW(1:30)-1,3))),))))
不能使用现有王五的单元格？改一下
=INDEX(T(OFFSET(B3,INT((ROW(1:30)-1)/3),MOD(ROW(1:30)-1,3))),MODE(IF(MMULT(1*(B3:D12="王五"),{1;1;1})*(B3:D12<>"王五"),MATCH(B3:D12,T(OFFSET(B3,INT((ROW(1:30)-1)/3),MOD(ROW(1:30)-1,3))),))))

[ 本帖最后由 chunlin1wang 于 2011-5-18 09:47 编辑 ]

fangjianp

1. =INDEX(T(OFFSET(B2,ROW(3:32)/3,MOD(ROW(3:32),3))),MODE(IF(MMULT(-(B3:D12="王五"),{1,2,3}-{1;2;3}),MATCH(B3:D12,T(OFFSET(B2,ROW(3:32)/3,MOD(ROW(3:32),3))),))))

复制代码

公式长度:156
以上公式Excel2003，2007能通过。

1. =INDEX(T(OFFSET(B2,ROW(3:32)/3,MOD(ROW(3:32),3))),MODE(IF(MMULT(-(B3:D12="王五"),{1,2,3}-{1;2;3}),MATCH(B3:D12&0,T(OFFSET(B2,ROW(3:32)/3,MOD(ROW(3:32),3)))&0,))))

复制代码

将上述公式修改一下，少量空岗不会出错，长度160

公式思路说明：
1、过滤掉不和王五同一天值班的所有人员和王五本人：
   MMULT(--(B3:D12="王五"),{1;1;1})-(B3:D12="王五")
     此公式可通过下列推导简化为：
   MMULT(--(B3:D12="王五"),{1;1;1})-(B3:D12="王五")=MMULT(--(B3:D12="王五"),{1;1;1})-MMULT（--(B3:D12="王五"),{1,0,0;0,1,0;0,0,1})=MMULT(--(B3:D12="王五"),{1;1;1}-{1,0,0;0,1,0;0,0,1})=MMULT(--(B3:D12="王五"),3阶数值方阵且对角线元素为0其余元素非0）=MMULT(--(B3:D12="王五"),{1,2,3}-{1;2;3})
    所以有   MMULT(-(B3:D12="王五"),{1,2,3}-{1;2;3})                       <1>
    在<1>中数组与已知值班名单一一对应,所有不和王五同一天值班的所有人员和王五本人对应位置上的数为0，其余非0
2、T(OFFSET(B2,ROW(3:32)/3,MOD(ROW(3:32),3)))                     <2>
     将值班名单变换为列数组
3、IF((<1>,MATCH(B3:D12,<2>,))：<1>式中非0，取MATCH(B3:D12,<2>,))式中的值
   MATCH(B3:D12,<2>,)为精确匹配
下略

换一种思路,公式长度:154

1. =INDIRECT(TEXT(MAX((MODE(IF(MMULT(-(B3:D12="王五"),{1,2,3}-{1;2;3}),COUNTIF(B3:D12,">"&B3:D12)))=COUNTIF(B3:D12,">"&B3:D12))*ROW(3:12)*10+{2,3,4}),"r0c0"),)

复制代码

设置迭代计算为1,公式可简化为：

1. =INDIRECT(TEXT(MAX((MODE(IF(MMULT(-(B3:D12="王五"),{1,2,3}-{1;2;3}),COUNTIF(A:D,">"&B3:D12)))=COUNTIF(A:D,">"&B3:D12))*(ROW(3:12)&{2,3,4})),"r0c0"),)

复制代码

公式长度为147。迭代的原因是因为答案写在了单元C15,而C15在A：D范围内，并非公式本身需要迭代，若将答案写在e15单元，则无需迭代
计算思路说明:略.
有空岗不会出错.Excel2003,2007能通过.

[ 本帖最后由 fangjianp 于 2011-6-12 22:18 编辑 ]

piny

公式好長 ><
方法一

1. =INDIRECT(TEXT(SMALL(IF(B3:B12="王五",ROW(3:12)*10+{3,4},IF(C3:C12="王五",ROW(3:12)*10+{2,4},IF(D3:D12="王五",ROW(3:12)*10+{2,3}))),MODE(MATCH(T(INDIRECT(TEXT(SMALL(IF(B3:B12="王五",ROW(3:12)*10+{3,4},IF(C3:C12="王五",ROW(3:12)*10+{2,4},IF(D3:D12="王五",ROW(3:12)*10+{2,3},999))),ROW(INDIRECT("1:"&2*COUNTIF(B3:D12,"王五")))),"!R0C0"),)),T(INDIRECT(TEXT(SMALL(IF(B3:B12="王五",ROW(3:12)*10+{3,4},IF(C3:C12="王五",ROW(3:12)*10+{2,4},IF(D3:D12="王五",ROW(3:12)*10+{2,3},999))),ROW(INDIRECT("1:"&2*COUNTIF(B3:D12,"王五")))),"!R0C0"),)),))),"!R0C0"),)

复制代码

方法二

1. =INDIRECT(TEXT(SMALL(MMULT((B3:D12="王五")*ROW(3:12),{1;1;1})*10+{2,3,4},2*COUNTIF(B3:D12,"王五")+MODE(MATCH(SUBSTITUTE(T(INDIRECT(TEXT(SMALL(MMULT((B3:D12="王五")*ROW(3:12),{1;1;1})*10+{2,3,4},ROW(INDIRECT(2*COUNTIF(B3:D12,"王五")+1&":30"))),"!R0C0"),)),"王五",ROW(INDIRECT(2*COUNTIF(B3:D12,"王五")+1&":30"))),SUBSTITUTE(T(INDIRECT(TEXT(SMALL(MMULT((B3:D12="王五")*ROW(3:12),{1;1;1})*10+{2,3,4},ROW(INDIRECT(2*COUNTIF(B3:D12,"王五")+1&":30"))),"!R0C0"),)),"王五",ROW(INDIRECT(2*COUNTIF(B3:D12,"王五")+1&":30"))),))),"!R0C0"),)

复制代码

方法一思路說明
思路一
IF(B3:B12="王五",ROW(3:12)*10+{3,4},IF(C3:C12="王五",ROW(3:12)*10+{2,4},IF(D3:D12="王五",ROW(3:12)*10+{2,3})))

{33,34;FALSE,FALSE;53,54;FALSE,FALSE;72,73;83,84;92,93;FALSE,FALSE;FALSE,FALSE;122,123}

即傳回與五五同天值班的行列號

思路二
T(INDIRECT(TEXT(SMALL(IF(B3:B12="王五",ROW(3:12)*10+{3,4},IF(C3:C12="王五",ROW(3:12)*10+{2,4},IF(D3:D12="王五",ROW(3:12)*10+{2,3},999))),ROW(INDIRECT("1:"&2*COUNTIF(B3:D12,"王五")))),"!R0C0"),))

{"张三丰";"张三";"李四光";"张一";"李四光";"张三";"张三丰";"张一";"张一";"李四";"张一";"张三丰"}

即傳回與王五同天值班的人員

思路三
MODE(MATCH(T(INDIRECT(TEXT(SMALL(IF(B3:B12="王五",ROW(3:12)*10+{3,4},IF(C3:C12="王五",ROW(3:12)*10+{2,4},IF(D3:D12="王五",ROW(3:12)*10+{2,3},999))),ROW(INDIRECT("1:"&2*COUNTIF(B3:D12,"王五")))),"!R0C0"),)),T(INDIRECT(TEXT(SMALL(IF(B3:B12="王五",ROW(3:12)*10+{3,4},IF(C3:C12="王五",ROW(3:12)*10+{2,4},IF(D3:D12="王五",ROW(3:12)*10+{2,3},999))),ROW(INDIRECT("1:"&2*COUNTIF(B3:D12,"王五")))),"!R0C0"),)),))
= MODE(MATCH(思路二,思路二,))

利用MATCH與MODE來找出在思路二出現重複次數最高的位址

思路四
SMALL(IF(B3:B12="王五",ROW(3:12)*10+{3,4},IF(C3:C12="王五",ROW(3:12)*10+{2,4},IF(D3:D12="王五",ROW(3:12)*10+{2,3}))),MODE(MATCH(T(INDIRECT(TEXT(SMALL(IF(B3:B12="王五",ROW(3:12)*10+{3,4},IF(C3:C12="王五",ROW(3:12)*10+{2,4},IF(D3:D12="王五",ROW(3:12)*10+{2,3},999))),ROW(INDIRECT("1:"&2*COUNTIF(B3:D12,"王五")))),"!R0C0"),)),T(INDIRECT(TEXT(SMALL(IF(B3:B12="王五",ROW(3:12)*10+{3,4},IF(C3:C12="王五",ROW(3:12)*10+{2,4},IF(D3:D12="王五",ROW(3:12)*10+{2,3},999))),ROW(INDIRECT("1:"&2*COUNTIF(B3:D12,"王五")))),"!R0C0"),)),)))
= SMALL(思路一,思路三)

[ 本帖最后由 piny 于 2011-6-13 12:43 编辑 ]

chenhh803

占位,我的预设公式是:
=INDIRECT(TEXT(MAX((COUNTIF(B3:D12,">"&B3:D12)=MODE(IF((B3:D12="王五")-MMULT(N(B3:D12="王五"),{1;1;1}),COUNTIF(B3:D12,">"&B3:D12))))*ROW(3:12)*10+COLUMN(B:D)),"R0C0"),)