D2=INDEX(A:A,MOD(SMALL($B$2:$B$11+ROW($B$2:$B$11)/1000,ROW(A1)),1)*1000)
数组公式向下复制【数组公式,公式输完后,光标放在公式编辑栏同时按下CTRL+SHIFT+回车键,使数组公式生效】
你想要前几名随意设置,不想要数组公式就需要加辅助列实现。
如果考虑名次有重复,则可用公式:
=INDEX($A$2:$A$11,MOD(SMALL(IF($B$2:$B$11<SMALL($B$2:$B$11,6),$B$2:$B$11*100+ROW($B$2:$B$11),4^8),ROW(1:1)),100)-1)
同时按Ctrl+Shift+Enter三键输入数组公式,下拉。
其实公式可进一步简化:
=INDEX($A$2:$A$11,MOD(SMALL($B$2:$B$11*100+ROW($B$2:$B$11),ROW(1:1)),100)-1)
我其实把表格在提问中简单化了,但是套不上你的公式。这个是我表格中的数据(类比名次),有正负,小数点后两位,原始数据更复杂一点,但是还是想找最大的前五个数对应的A列的数据。求大神给个公式能套上,因为A列有1万多个数据,第一行也有1百多个数据,没公式搞不定
看你的追问与图无法联系起来,描述中两次说到A列,但图中看不到A列。单就截图来说,提问时的截图比追问的截图好,有完整的行号和列标,这样别人才看得懂,也容易写公式。
请你明确说明,要取的最大的前5个数在哪一列,1万多行,具体到最后的行号是多少,前5个最大数的相同的情况按哪列来确定先后顺序,得到的结果是取哪列数据,放在哪?这些从你的追问中没有一个能找到答案。怎么给设计公式?
我想找B列、C列、D列...等等每一列的最大的五个数(包含重复数)所对应的A列的名称,放到sheet1的表格中。前5个最大数的相同的情况下可以随机选取,没有优先级。
先不考虑正数不足5个的吧,公式要简单些,B2中输入公式:
=INDEX(nn!$A$2:$A$17420,RIGHT(LARGE(IF(nn!B$2:B$17420>0,nn!B$2:B$17420*10^6,0)+ROW($2:$17420),ROW(1:1)),5)-1)
同时按Ctrl+Shift+Enter三键输入数组公式,下拉到B6,再一起右拉。
如果正数不足5个,公式要复杂很多,真有需要,再说吧。
还是不行呀,大神。现在的情况是
1、最大的前五个数,肯定有很多列是有重复的。比如第一列我降序之后最大值7.2 相同的就有12个。
2、正数肯定超过5个的,不用考虑负数。
你把文件发到[email protected]吧,因为我用少量数据测试公式是没问题的。
大数据量的工作表用小范围的数据来模拟的确容易出错。B2中用公式:
=INDEX(nn!$A$2:$A$17420,RIGHT(LARGE(nn!B$2:B$17420*10^9+ROW($2:$17420),ROW(1:1)),5)-1)
如果前5个数中有相同的,行号大的先取出。
下面的公式,则是行号小的先取出:
=INDEX(nn!$A$2:$A$17420,100000-RIGHT(LARGE(nn!B$2:B$17420*10^9+100000-ROW($2:$17420),ROW(1:1)),5)-1)
已按上述公式对将你的文件弄好。