数组公式兴趣小组练习题－024期

一片绿叶

对不起大家。近期由于工作太忙了，耽误了大家的学习时间，<br/>[em12]<br/>我们前期的要求严格点，不许使用辅助列和定义名称

[此贴子已经被作者于2007-11-1 21:48:11编辑过]

liangwen

<p>EH的竞赛题</p>

Luckyguy2008

看了willin2000的答案才算弄懂了题意，原来是要在黄色区域列出B列中连续出现次数最多的班，次数相同的有几个列几个

Luckyguy2008

太好了，楼主及时赶到[em17]

willin2000

<p>抄袭个现成的上来<img src="http://www.excelpx.com/Skins/Default/emot/em04.gif" align="middle" border="0" alt=""/>:</p><p>C1=INDEX($B:$B,SMALL(IF(MAX(FREQUENCY(ROW(1:20),($B1:$B19&lt;&gt;$B2:$B20)*ROW(1:19)))=FREQUENCY(ROW(1:20),($B1:$B19&lt;&gt;$B2:$B20)*ROW(1:19)),ROW(1:20),99),COLUMN()-2))&amp;""</p><p>右拉公式.</p>

不看不看

<p>我认为本题是这个意思</p><p>aa=SMALL(IF((MATCH(Sheet1!B2:B20,Sheet1!B2:B20,0)=ROW(Sheet1!B1:B19)),ROW(Sheet1!B1:B19),65536),COLUMN(INDIRECT("r1c1:r1c"&amp;SUM(1/COUNTIF(Sheet1!B2:B20,Sheet1!B2:B20)),)))</p><p>&nbsp;公式&nbsp; =MMULT(COLUMN(A1:S1)^0,--(MATCH(B2:B20,B2:B20,0)=aa))</p><p>结果</p><p><table cellspacing="0" cellpadding="0" width="314" border="0" xstr="" style="WIDTH: 236pt; BORDER-COLLAPSE: collapse;"><colgroup><col width="107" style="WIDTH: 80pt; mso-width-source: userset; mso-width-alt: 3424;"></col><col></col><col width="86" style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 2752;"></col><col></col><col width="121" style="WIDTH: 91pt; mso-width-source: userset; mso-width-alt: 3872;"></col><col></col></colgroup><tbody><tr height="20" style="HEIGHT: 15pt; mso-height-source: userset;"><td class="xl22" width="107" height="20" xnum="" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 80pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: yellow;"><font size="2">10</font></td><td class="xl22" width="86" xnum="" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 65pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: yellow;"><font size="2">8</font></td><td class="xl22" width="121" xnum="" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 91pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: yellow;"><font size="2">1</font></td></tr></tbody></table></p>

[此贴子已经被作者于2007-11-2 22:28:57编辑过]

hai

<p>我的想法和不看一样!</p><p>=LARGE(FREQUENCY(COUNTIF($B$2:$B$20,$B$2:$B$20),COUNTIF($B$2:$B$20,$B$2:$B$20)),COLUMN()-2)</p>

szyygb

<p>好好学习</p><p>天天向上</p>