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

草民1982111 · 发表于 2011-6-10 22:47

07版本，解释在附件内。

[ 本帖最后由草民1982111 于 2011-6-12 11:26 编辑 ]

aa7701914 · 发表于 2011-6-13 11:43

=INDEX(
INDEX(INDEX($B$3:$D$13,N(IF(1,ROUNDUP(ROW($1:$31)/3,))),N(IF(1,MOD(ROW($1:$31)-1,3)+1))),N(IF(1,SMALL(IF(MATCH(INDEX($B$3:$D$12,N(IF(1,ROUNDUP(ROW($1:$30)/3,))),N(IF(1,MOD(ROW($1:$30)-1,3)+1))),INDEX($B$3:$D$12,N(IF(1,ROUNDUP(ROW($1:$30)/3,))),N(IF(1,MOD(ROW($1:$30)-1,3)+1))),)=ROW(1:30),ROW(1:30),31),ROW(1:30))))),
RIGHT(LARGE(MMULT(TRANSPOSE(ROW(1:30)^0),N(INDEX(IF(IF(MMULT(N(B3:D12=B15),{1;1;1}),B3:D12,ROW(1:10)*40+COLUMN(A:C))=B15,4^8,IF(MMULT(N(B3:D12=B15),{1;1;1}),B3:D12,ROW(1:10)*40+COLUMN(A:C))),N(IF(1,ROUNDUP(ROW($1:$30)/3,))),N(IF(1,MOD(ROW($1:$30)-1,3)+1)))=TRANSPOSE(INDEX(INDEX($B$3:$D$13,N(IF(1,ROUNDUP(ROW($1:$31)/3,))),N(IF(1,MOD(ROW($1:$31)-1,3)+1))),N(IF(1,SMALL(IF(MATCH(INDEX($B$3:$D$12,N(IF(1,ROUNDUP(ROW($1:$30)/3,))),N(IF(1,MOD(ROW($1:$30)-1,3)+1))),INDEX($B$3:$D$12,N(IF(1,ROUNDUP(ROW($1:$30)/3,))),N(IF(1,MOD(ROW($1:$30)-1,3)+1))),)=ROW(1:30),ROW(1:30),31),ROW(1:30))))))))+COLUMN(A:AD)%,1),2)*1)&""
王五为名称引用。超长公式，嘿。10版本。

思路：
1.将区域中符合条件值找出，同时构建一个虚拟的数据填充，方便下面引用，如：=IF(MMULT(N(B3:D12="王五"),{1;1;1}),B3:D12,ROW(1:10)*3-3+COLUMN(A:C))
2.去除区域中B3:D13的重复值，并以1维的形式存储。
3.步骤1中的区域与步骤2中的非重复值构建查找对应重复出现最多的值
4.以步骤2中的区域进行查找匹配步骤3所返回的第二大值。

利用INDEX+N+IF返回内存数组区域，解题中的思路较为原始，没有技巧性。

[ 本帖最后由 aa7701914 于 2011-6-13 22:07 编辑 ]

fjrlgj · 发表于 2011-6-13 12:06

还真想不出来,勉强凑个数:
=INDIRECT(TEXT(MAX((MODE(IF(MMULT(-(B3=B3:D12),{1;1;1})*(B3:D12<>B3),-(CODE(LEFT(B3:D12))&CODE(MID(B3:D12,2,1))&CODE(MID(B3:D12&0,3,1)))))=-(CODE(LEFT(B3:D12))&CODE(MID(B3:D12,2,1))&CODE(MID(B3:D12&0,3,1))))*(ROW(3:12)*10+{2,3,4})),"r0c0"),)

wangg913 · 发表于 2011-6-13 16:07

=INDEX(T(OFFSET(B2,ROW(3:32)/3,MOD(ROW(3:32),3))),RIGHT(MAX(FREQUENCY(IF((B3:D12="王五")+MMULT(-(B3:D12="王五"),{1;1;1}),COUNTIF(B3:D12,"<"&B3:D12)),COUNTIF(B3:D12,"<"&B3:D12))/1%+ROW(1:31)),2))
哈哈，太长了。

chenhh803 · 发表于 2011-6-14 00:17

结贴，请勿修正答案。

草民1982111 · 发表于 2011-6-14 03:46

夜深人静，有E相陪，点上一根，细细品味。

jianbing · 发表于 2011-6-14 05:20

看得我胃疼（真疼！物理性）
脑子不好使了，转不过弯。

lhx120824 · 发表于 2011-6-14 11:25

惭愧，用了好长时间也没有找到满意的方法，向大家多多学习。

chenhh803 · 发表于 2011-6-14 15:06

EH是一个藏龙卧虎的论坛，里面高手如云。所以小子在评题的时候就有一种架在火炉上烤的感觉，生怕出现一点失误，浪费了各位老师的一番心血。
因这是小子的第一次出题，水平有限，难免考虑不周，如各位老师对评分结果有所疑问，请及时提出，可发消息结我或者wangg913版主，谢谢大家的精彩解答！谢谢大家的支持！您的支持是我前进的动力。
对于自己认为优秀的解答，请能够献花的朋友不要吝啬自己的鲜花。
感谢EH提供一个这么好的平台！感谢wangg913版主在这一次出题中的大力支持！
有老师曾发短消息说：“此题即然充许2007版本的公式，为什么不允许03版本定义名称，这对于03版本的用户有点不太公平。”
我在这里解释一下，因为考虑到评分和审题的问题所以才做此决定的，在这里要向03版本用户说声对不起，因为这样对你们确实不公平，还请大家多多谅解！

评分标准：
公式长度小于（含）180字符技术分3分,财富50
公式长度小于（含）200字符技术分2分,财富40
公式长度小于（含）300字符技术分2分,财富20
公式长度大于300字符技术分2分
不符合财富10做为激励

chenhh803 · 发表于 2011-6-14 15:08

最优解法: fangjianp 老师字符数:154
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"),)
说明:
fangjianp老师的后一条公式达到字符数147,wcymiss老师的后一条公式达到141。但这两条公式在实际应用中还要考虑不在列表中的因素。如有不对之处还请大家指正。
此条公式通用性强，字符短，矩阵的构造达到登峰造极
感谢fangjianp老师的精彩解答，

题目总结：
因文本在函数中处理确实是一个难点，在论坛中出现最多的疑难杂症都与文本有关。因为如果要求出众数的话，就必须把文本变为数值。此为本题难点。这次各位老师的解题总体思路大致为两种
1：先把多维转为一维，再利用查找函数求出他的位置，利用位置来取得众数，然后再取得姓名；
2：利用COUNTIF函数可以比较文本的大小，来取得每个文本的唯一数字标记；
3：有的老师想到用CODE函数来区分文本，但此法局限性太大，如果文本比较长就难区分了，因草民老师的公式具有扩展性，所以得分，其它老师用穷尽办法的都没有给分。
因为取三个字符的公式，如出现少数民族姓名那公式就会无限长了。此题的考点就在于如何区分文本，如何把文本的多维转一维。

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

		自动登录	找回密码
密码			免费注册

[Excel 函数与公式] [第75期]和我一起值班最多的人是谁？

本帖子中包含更多资源

评分

本帖子中包含更多资源

评分

本帖子中包含更多资源

评分

评分

评分

本帖子中包含更多资源

评分