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

kkitt

=INDEX(T(OFFSET(B2,INT(ROW(3:32)/3),MOD(ROW(3:32),3))),MODE(MATCH(IF(ISNUMBER(MATCH(INT(ROW(3:32)/3),IF(MMULT(--(B3:D12="王五"),{1;1;1}),ROW(1:10)),))*(T(OFFSET(B2,INT(ROW(3:32)/3),MOD(ROW(3:32),3)))<>"王五"),T(OFFSET(B2,INT(ROW(3:32)/3),MOD(ROW(3:32),3))),ROW(1:30)),IF(ROW(1:60)<31,T(OFFSET(B2,INT(ROW(3:32)/3),MOD(ROW(3:32),3))),ROW(1:60)-30),)))

鬼狐

=INDEX(T(INDIRECT(TEXT(SMALL(IF(MMULT(--(B3:D12="王五"),ROW(1:3)^0)*(B3:D12<>"王五"),ROW(3:12)*100+COLUMN(B:D),9999),ROW(INDIRECT("1:"&SUM((B3:D12="王五")*2)))),"r0c00"),)),MODE(MATCH(T(INDIRECT(TEXT(SMALL(IF(MMULT(--(B3:D12="王五"),ROW(1:3)^0)*(B3:D12<>"王五"),ROW(3:12)*100+COLUMN(B:D),9999),ROW(INDIRECT("1:"&SUM((B3:D12="王五")*2)))),"r0c00"),)),T(INDIRECT(TEXT(SMALL(IF(MMULT(--(B3:D12="王五"),ROW(1:3)^0)*(B3:D12<>"王五"),ROW(3:12)*100+COLUMN(B:D),9999),ROW(INDIRECT("1:"&SUM((B3:D12="王五")*2)))),"r0c00"),)),)))
501个字符，嵌套9层，03中match貌似会出错，请楼主或者头版帮忙在07或者10版试一下
03中定义名称可行，定义名称为aa=T(INDIRECT(TEXT(SMALL(IF(MMULT(--(B3:D12="王五"),ROW(1:3)^0)*(B3:D12<>"王五"),ROW(3:12)*100+COLUMN(B:D),9999),ROW(INDIRECT("1:"&SUM((B3:D12="王五")*2)))),"r0c00"),))之后公式可缩减为
=index(aa,mode(match(aa,aa,)))，即aa为主体函数
============================================
吴姐跟我说，她只有160+，仰望一下
顺便给个另外思路的公式
=INDIRECT(TEXT(MIN(IF(MODE(IF((B3:D12<>B3)*MMULT(-(B3:D12=B3),{1;1;1}),COUNTIF(B3:D12,">"&IF(B3:D12<>B3,B3:D12))))=IF((B3:D12<>B3)*MMULT(-(B3:D12=B3),{1;1;1}),COUNTIF(B3:D12,">"&IF(B3:D12<>B3,B3:D12))),ROW(3:12)*100+{2,3,4})),"r0c00"),)
这个就不需要07的了，只要03 的就可以了，236字符
============================================
话说貌似王五的位置不固定。。。
那就把B3改成王五吧，多出了10+个字符，公式修改如下248字符
=INDIRECT(TEXT(MIN(IF(MODE(IF((B3:D12<>"王五")*MMULT(-(B3:D12="王五"),{1;1;1}),COUNTIF(B3:D12,">"&IF(B3:D12<>"王五",B3:D12))))=IF((B3:D12<>"王五")*MMULT(-(B3:D12="王五"),{1;1;1}),COUNTIF(B3:D12,">"&IF(B3:D12<>"王五",B3:D12))),ROW(3:12)*100+{2,3,4})),"r0c00"),)

[ 本帖最后由 鬼狐 于 2011-6-4 09:36 编辑 ]

yumoym

2010版附件

wcymiss

=INDEX(T(OFFSET(B2,ROW(3:32)/3,MOD(ROW(3:32),3))),MODE(IF(MMULT(N(B3:D12="王五"),{1;1;1})*(B3:D12<>"王五"),MATCH(B3:D12,T(OFFSET(B2,ROW(3:32)/3,MOD(ROW(3:32),3))),))))
163字符，适合03

=INDIRECT(TEXT(MODE(IF(MMULT(N(B3:D12="王五"),{1;1;1})*(B3:D12<>"王五"),MATCH(B3:D12,T(OFFSET(A3,ROW(1:99)/10,RIGHT(ROW(1:99)))),)))+31,"r0c0"),)
141字符，适合07

[ 本帖最后由 wcymiss 于 2011-6-13 00:50 编辑 ]

alzbz

用2010版勉强求出结果，公式太长，不在此列出，以后再精简。主体思路：先得到行不包含“王五”的数组，再求出出现次数最多的人员。

羽希

没有重复者出现时公式有问题,纠结了老半天,幸好审题发现最后一句"另外值班员相对固定,因此和我一起值班的人一定会有重复,如果出现多个最大相同频率的人,则任选一个".也就是说不会再现我担心的情况,心情好了一点,所以还是拿再来晒晒^_^

[ 本帖最后由 羽希 于 2011-6-10 16:35 编辑 ]

jianbing

解一：
=INDEX(T(OFFSET($B$3,{0;0;0;1;1;1;2;2;2;3;3;3;4;4;4;5;5;5;6;6;6;7;7;7;8;8;8;9;9;9},{0;1;2;0;1;2;0;1;2;0;1;2;0;1;2;0;1;2;0;1;2;0;1;2;0;1;2;0;1;2})),RIGHT(LARGE((FREQUENCY(IF((MMULT(-(B3:D12="王五"),{1;1;1})*MATCH(B3:D12,T(OFFSET($B$3,{0;0;0;1;1;1;2;2;2;3;3;3;4;4;4;5;5;5;6;6;6;7;7;7;8;8;8;9;9;9},{0;1;2;0;1;2;0;1;2;0;1;2;0;1;2;0;1;2;0;1;2;0;1;2;0;1;2;0;1;2})),)=0)+(MMULT(--(B3:D12="王五"),{1;1;1})*MATCH(B3:D12,T(OFFSET($B$3,{0;0;0;1;1;1;2;2;2;3;3;3;4;4;4;5;5;5;6;6;6;7;7;7;8;8;8;9;9;9},{0;1;2;0;1;2;0;1;2;0;1;2;0;1;2;0;1;2;0;1;2;0;1;2;0;1;2;0;1;2})),)=MATCH("王五",T(OFFSET($B$3,{0;0;0;1;1;1;2;2;2;3;3;3;4;4;4;5;5;5;6;6;6;7;7;7;8;8;8;9;9;9},{0;1;2;0;1;2;0;1;2;0;1;2;0;1;2;0;1;2;0;1;2;0;1;2;0;1;2;0;1;2})),)),"q",MMULT(--(B3:D12="王五"),{1;1;1})*MATCH(B3:D12,T(OFFSET($B$3,{0;0;0;1;1;1;2;2;2;3;3;3;4;4;4;5;5;5;6;6;6;7;7;7;8;8;8;9;9;9},{0;1;2;0;1;2;0;1;2;0;1;2;0;1;2;0;1;2;0;1;2;0;1;2;0;1;2;0;1;2})),)),ROW(1:30)))*100+ROW(1:31),1)))

公式有点长，应该做到完全附合新增的规定。

解二：
=INDEX(T(OFFSET($B$3,(ROW(1:30)-1)/3,MOD(ROW(1:30)+2,3))),MODE(IF((MMULT(-(B3:D12="王五"),{1;1;1})*MATCH(B3:D12,T(OFFSET($B$3,(ROW(1:30)-1)/3,MOD(ROW(1:30)+2,3))),)=0)+(MMULT(--(B3:D12="王五"),{1;1;1})*MATCH(B3:D12,T(OFFSET($B$3,(ROW(1:30)-1)/3,MOD(ROW(1:30)+2,3))),)=MATCH("王五",T(OFFSET($B$3,(ROW(1:30)-1)/3,MOD(ROW(1:30)+2,3))),)),"q",MMULT(--(B3:D12="王五"),{1;1;1})*MATCH(B3:D12,T(OFFSET($B$3,(ROW(1:30)-1)/3,MOD(ROW(1:30)+2,3))),))))

用mode()来代替Frequency() 以减少嵌套。当同时于我值班最多的人数是1次的时候会出错。

[ 本帖最后由 jianbing 于 2011-6-11 15:43 编辑 ]

wddn

=INDIRECT(TEXT(MAX((MMULT(--(B3:D12="王五"),{1;1;1})*(B3:D12<>"王五")*COUNTIF(B3:D12,">"&B3:D12)=MOD(MAX(FREQUENCY(IF(MMULT(--(B3:D12="王五"),{1;1;1})*(B3:D12<>"王五"),COUNTIF(B3:D12,">"&B3:D12)),ROW(1:30)-1)*100+ROW(1:31)-1),100))*(ROW(B3:B12)*100+{2,3,4})),"R0C00"),)
其中：
IF(MMULT(--(B3:D12="王五"),{1;1;1})*(B3:D12<>"王五"),COUNTIF(B3:D12,">"&B3:D12))
是与王五一起值班者姓名的文本排位。
今日简化一下（163字符）：
=INDIRECT(TEXT(MAX(IF(COUNTIF(B3:D12,">"&B3:D12)=MODE(IF(MMULT(-(B3:D12="王五"),{1;1;1})*(B3:D12<>"王五"),COUNTIF(B3:D12,">"&B3:D12))),ROW(3:12)*10+{2,3,4})),"R0C0"),)

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

tslx8129

=INDEX(B3:D12,CEILING(MAX((MODE(ABS(NOT((B3:B12<>"王五")*(C3:C12<>"王五")*(D3:D12<>"王五"))*{1,1,1}*(B3:D12<>"王五")*--(CODE(MID((B3:D12)&0,1,1))&CODE(MID((B3:D12)&0,2,1))&CODE(MID((B3:D12)&0,3,1)))-ROW(1:10)*3-{1,2,3}+3)+ROW(1:10)*3+{1,2,3}-3)=--(CODE(MID((B3:D12)&0,1,1))&CODE(MID((B3:D12)&0,2,1))&CODE(MID((B3:D12)&0,3,1))))*(ROW(1:10)*3+{1,2,3}-3))/3,1),MOD(MAX((MODE(ABS(SIGN(ROW(1:10)*{1,1,1})*((B3:B12="王五")+(C3:C12="王五")+(D3:D12="王五"))*NOT(SIGN(LEN(B3:D12)*(B3:D12="王五")))*--(CODE(MID((B3:D12)&0,1,1))&CODE(MID((B3:D12)&0,2,1))&CODE(MID((B3:D12)&0,3,1)))-ROW(1:10)*3-{1,2,3}+3)+ROW(1:10)*3+{1,2,3}-3)=--(CODE(MID((B3:D12)&0,1,1))&CODE(MID((B3:D12)&0,2,1))&CODE(MID((B3:D12)&0,3,1))))*(ROW(1:10)*3+{1,2,3}-3))-1,3)+1)


原理是INDEX(B3:D12,CEILING(众数所在最后一个单元格编号/3,1),MOD(众数所在最后一个单元格编号-1,3)+1)
众数所在最后一个单元格编号即
1，2，3
4，5，6
7，8，9
10，11，12
这种排列方式下最多名字的最后一个对应单元格，对于表中应为28，即张一

缺点是这公式不能解决全部是四字名字且四字前三字都一样这种情况，不过现实中这种情况基本为0。

[ 本帖最后由 tslx8129 于 2011-6-13 16:54 编辑 ]

死亡メロディ

我也冒个泡 混两分