如何查找数据中，个数最多，第二多，和第三多的数字？

4fire

QUOTE:

以下是引用gouweicao78在2007-4-22 15:46:57的发言：

=INDEX(A:A,MOD(LARGE(IF(FREQUENCY(A$1:A$21,A$1:A$21),COUNTIF(A$1:A$21,A$1:A$21)*10^5+ROW($1:$21)),ROW(1:1)),10^5))——适用于A1:A21为数字情况

=INDEX(A:A,MOD(LARGE(IF(MATCH(A$1:A$21,A$1:A$21,0)=ROW($1:$21),COUNTIF(A$1:A$21,A$1:A$21)*10^5+ROW($1:$21)),ROW(1:1)),10^5))——适用于A1:A21含文本情况

以上两公式均为数组公式。

感谢感谢！辛苦版主了！

麻烦gouweicao78了！

gouweicao78

注意：有空单元格情况，需要进行容错处理：

比如：

=INDEX(A:A,MOD(LARGE(IF(MATCH(A$1:A$21&"",A$1:A$21&"",0)=ROW($1:$21),COUNTIF(A$1:A$21,A$1:A$21)*10^5+ROW($1:$21)),ROW(1:1)),10^5))——结果会多一个空单元格。

4fire

QUOTE:

以下是引用gouweicao78在2007-4-23 10:27:09的发言：

注意：有空单元格情况，需要进行容错处理：

比如：

=INDEX(A:A,MOD(LARGE(IF(MATCH(A$1:A$21&"",A$1:A$21&"",0)=ROW($1:$21),COUNTIF(A$1:A$21,A$1:A$21)*10^5+ROW($1:$21)),ROW(1:1)),10^5))——结果会多一个空单元格。

再请教下版主gouweicao78，本人太菜，没能弄出来。能帮我解决下吗？

jelpawJS.rar (5.91 KB, 下载次数: 90)

2007-4-23 11:22 上传

点击文件名下载附件
如何查找数据中，个数最多，第二多，和第三多的数字？

gouweicao78

to 4fire:

23楼原公式：

=INDEX(Sheet1!G:G,MOD(LARGE(IF(MATCH(Sheet1!G$4:G$28,Sheet1!G$4:G$28,0)=ROW(Sheet1!$4:$28),COUNTIF(Sheet1!G$4:G$28,Sheet1!G$4:G$28)*10^5+ROW(Sheet1!$4:$28)),ROW(Sheet1!3:3)),10^5))

应改为：

=INDEX(Sheet1!G:G,MOD(LARGE(IF(MATCH(Sheet1!G$4:G$28,Sheet1!G$4:G$28,0)=ROW(Sheet1!$4:$28)-3,COUNTIF(Sheet1!G$4:G$28,Sheet1!G$4:G$28)*10^5+ROW($4:$28)),ROW(1:1)),10^5))

请注意看黄色、红色部分区别。

理解公式，就不会完全“生搬硬套”产生错误了，分层剥开：

IF(MATCH=ROW……求不重复的方法之一

其中：MATCH查找数组在数组本身第一次出现的位置，比如A2:A6为数据A、B、A、C、B，则用Match(A2:A6,A2:A6,0)得到{1;2;1;4;2}，分别表示A在A2:A6第一次出现的位置是1，B第一次出现的位置是2，A第一次出现位置是1，C第一次出现位置是4，B第一次出现位置是2。

此时，因为数据从A2:A6，从第2行开始，因此位置与行号的关系是Row(A$2:A$6)-1（即数据起始行——A2单元格所在行号ROW(A2)减去上一行的行号1），得到以1开始的数组{1;2;3;4;5}

{1;2;1;4;2}={1;2;3;4;5}——〉{TRUE;TRUE;FALSE;TRUE;FALSE}，从而判断A2的"A"是第一次出现的，不重复的。

COUNTIF*10^5+Row(数据所在区域对应行号)——参看[答疑解惑]按多关键字排序

首先，Countif(数据,数据)返回的是数据包含数据本身的个数，得到的是一个数组。将它们放大10^5倍后（戏称财团资助），并不影响个数多少的排序结果，再加上一个不足10^5的行号（零头），仍然不会影响个数多少的排序结果——这里，个数多少相当于多关键字排序的“主关键字”，行号则是“次关键字”。

用LARGE(……,Row(1:1))配合——按个数从多到少排序，于是，这些行号仰仗着个数多少*10^5的资助，自然而然地也有了其排行榜，顺序是遵从个数多少即“主关键字”的排序。

MOD(行号数组,10^5)——对返回的行号数组对10^5求余数，得到的就是真正的行号——受财团资助后的那点小零头。

=INDEX(查找所在列,行号)——取得行列交叉处的值。

[此贴子已经被作者于2007-4-23 13:12:20编辑过]

4fire

感谢gouweicao78！辛苦！

鄙人很菜，初学EXCEL函数。感谢gouweicao78及各位指教！

仔细学习！

谢谢！

SUNDD

24楼版主老师改过的公式复制到表格中,出现循环引用的提示,比对原公式是否是最后部分出了问题?

gouweicao78

QUOTE:

以下是引用SUNDD在2007-6-26 1:33:20的发言：

24楼版主老师改过的公式复制到表格中,出现循环引用的提示,比对原公式是否是最后部分出了问题?

出现循环引用，那估计你公式放的单元格不对，请自己检查一下或传附件看看。

SUNDD

QUOTE:

以下是引用gouweicao78在2007-6-26 9:24:18的发言：

出现循环引用，那估计你公式放的单元格不对，请自己检查一下或传附件看看。

我用的就是23楼中附件的表格,由于对数组公式用的不熟,分析半天也没搞明白

[此贴子已经被作者于2007-6-26 18:18:56编辑过]

lgx55

, 能不能求数组按行或列的升降序排列丫

星语心愿

太厉害了。