练习12：按行求不重复字母个数

楼主 rongjun	【答题期限】：2010-1-20日0:00截稿（以回帖时间为准），欢迎大家踊跃参与。【题目】：已知15*9的二维字母常量数组Data={"M","C","M","U","K","K","C","C","R";"J","Y","D","J","W","F","B","Z","D";"R","V","M","C","W","O","Z","W","F";"Y","F","V","H","V","P","V","A","S";"Y","Y","T","E","N","W","W","W","T";"Z","X","E","S","T","X","X","Q","S";"D","X","M","L","O","D","B","Z","K";"K","J","L","J","T","S","F","O","X";"Y","W","M","R","F","K","K","C","Y";"I","T","I","Q","G","U","X","O","T";"G","R","T","S","J","C","W","Z","B";"W","Q","C","T","F","A","D","Y","Q";"X","G","O","T","Z","C","L","K","H";"A","A","I","K","Z","B","V","E","D";"L","Z","H","S","N","E","Y","L","W"} 求数组每一行的不重复字母个数。【要求】： 1、用函数公式完成,不用VBA； 2、只允许定义已知常量数组Data一个名称，不允许再定义其他名称； 3、请在K1:K15单元格区域输入公式答题，若公式中引用其他单元格，则视作使用辅助区域（row()、column()引用行列号除外）； 4、使用辅助区域答题者请上传附件。【评分】： 1、使用辅助区域，且得到非内存数组正确答案得1技能分； 2、不使用辅助区域，且得到非内存数组正确答案得2技能分； 3、使用辅助区域，且得到内存数组正确答案得3技能分； 4、不使用辅助区域，且得到内存数组正确答案得5技能分； 5、精彩答案可再获得1—3技能分。
2楼 xyh9999	在K1中输:=SUM(1/COUNTIF(A1:I1,A1:I1)) 再将按Ctrl+Shift+Enter后,K1公式下拖或在K1中输:=SUM(IF(A1:I1<>"",1/COUNTIF(A1:I1,A1:I1))) 再将按Ctrl+Shift+Enter后,K1公式下拖
3楼 xyh9999	有一疑问想请教版主,为什么经常有人出题都禁用VBA功能,应该说用VBA基本可以解决所有公式所能解决的问题(并且很多公式不能解决的问题VBA也能解决),为什么经常在答题时要禁止呀?
4楼皮皮1998	内存数组公式:=COUNT(MATCH(CHAR(COLUMN(A:Z)+64),INDEX(data,ROW(1:15),),)) 普通数组公式:=SUM(IF(MATCH(INDEX(data,ROW(A1),),INDEX(data,ROW(A1),),)=COLUMN(A:I),1,)) 加辅助列的: 不重复值个数.rar
5楼皮皮1998	感觉我那个是假内存,郁闷,我再去找一下真内存!
6楼 syz105729913	参与一下 =SUM(N(MATCH(INDEX(Date,ROW()),INDEX(Date,ROW()),)=COLUMN(A:I)))
7楼 yzy521	看一下，好晕。
8楼 syz105729913	再来一个使用辅助区域的按行求不重复字母个数-syz105729913.rar
9楼 biaotiger1	转过来再转过去，够麻烦的还是辅助列的办法比较好过些（见不重复值1） =SUM(N(FREQUENCY(MMULT(TRANSPOSE(CODE(Data)),--(ROW($1:$15)=ROW())),ROW($65:$91))>0)) =SUM(N(FREQUENCY(CODE(A1:I1),ROW($65:$91))>0)) 循环引用(见不重复值2) =IF(K$1,IF(K3=0,"",K3)&IF(COUNTIF(K3,""&INDIRECT("rc"&K$2,)&""),"",INDIRECT("rc"&K$2,)),"") =IF(K$1,M3+IF(COUNTIF(INDIRECT("RC1:RC"&K$2,),INDIRECT("RC"&K$2+1,)),0,IF(K$2=10,0,1)),0) 不重复值1_2.rar
10楼棉花糖	=MMULT(--(SMALL(CODE(data)+ROW(1:15)10^6,(ROW(1:15)-1)9+COLUMN(A:H))<>SMALL(CODE(data)+ROW(1:15)10^6,(ROW(1:15)-1)9+COLUMN(B:I))),ROW(1:8)^0)+1
11楼 pc520	=SUMPRODUCT(1/COUNTIF(A1:I1,A1:I1)),下拉
12楼 amulee	先来一个，想到了再更新 {=SUM(1/MMULT(N(INDEX(Data,ROW(),0)=TRANSPOSE(INDEX(Data,ROW(),0))),ROW($A$1:$A$9)^0))}
13楼 swallow5121	在$k$1中输入公式“=COUNT(IF(MATCH($A1:$I1,$A1:$I1,0)=COLUMN($A1:$I1),1,""))”后按ctrl+shift+enter键，拖动至$k$15中即可。
14楼 rongjun	以下是我的答案： 1、使用辅助区域，且得到非内存数组： =SUMPRODUCT(1/COUNTIF(A1:I1,A1:I1)) 2、不使用辅助区域，且得到非内存数组: =COUNT(MATCH(CHAR(COLUMN(A:Z)+64),INDEX(Data,ROW()),)) 3、使用辅助区域，且得到内存数组: =MMULT(1/COUNTIF(OFFSET(A1:I1,ROW(1:15)-1,),A1:I15),ROW(1:9)^0) 4、不使用辅助区域，且得到内存数组。因为符合此要求的只有棉花糖 =FREQUENCY(IF(FREQUENCY(ROW(1:15)100+CODE(Data),ROW(1:15)100+CODE(Data)),INT((ROW(1:136)-1)/9)+1),ROW(1:14)) =MMULT(-(INT(TRANSPOSE(ROW(1:136)-1)/9)=ROW(1:15)-1),-(FREQUENCY(ROW(1:15)100+CODE(Data),ROW(1:15)100+CODE(Data))>0)) 【练习12】按行求不重复字母个数_答题.rar
15楼 donghan	学习高手的函数