ExcelTip.Net留存知识帖 ---【注:附件之前被网盘供应商清空后,现已修复-现已修复-现已修复为本地下载!】
现在位置:首页 > 我的测验 > 函数与公式 > 练习12:按行求不重复字母个数

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

作者:绿色风 分类: 时间:2022-08-18 浏览:151
楼主
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)
  1. =SUM(N(FREQUENCY(MMULT(TRANSPOSE(CODE(Data)),--(ROW($1:$15)=ROW())),ROW($65:$91))>0))
  2. =SUM(N(FREQUENCY(CODE(A1:I1),ROW($65:$91))>0))
循环引用(见 不重复值2)
  1. =IF(K$1,IF(K3=0,"",K3)&IF(COUNTIF(K3,"*"&INDIRECT("rc"&K$2,)&"*"),"",INDIRECT("rc"&K$2,)),"")
  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、使用辅助区域,且得到非内存数组:

  1. =SUMPRODUCT(1/COUNTIF(A1:I1,A1:I1))

2、不使用辅助区域,且得到非内存数组:

  1. =COUNT(MATCH(CHAR(COLUMN(A:Z)+64),INDEX(Data,ROW()),))

3、使用辅助区域,且得到内存数组:

  1. =MMULT(1/COUNTIF(OFFSET(A1:I1,ROW(1:15)-1,),A1:I15),ROW(1:9)^0)

4、不使用辅助区域,且得到内存数组。因为符合此要求的只有棉花糖

  1. =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))

  1. =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
学习高手的函数

免责声明

有感于原ExcelTip.Net留存知识的价值及部分知识具有的时间限定性因素, 经与ExcelTip.Net站长Apolloh商议并征得其同意, 现将原属ExcelTip.Net的知识帖采集资料于本站点进行展示, 供有需要的人士查询使用,也慰缅曾经的论坛时代。 所示各个帖子的原作者如对版权有异议, 可与本人沟通提出,或于本站点留言,我们会尽快处理。 在此,感谢ExcelTip.Net站长Apolloh的支持,感谢本站点所有人**绿色风(QQ:79664738)**的支持与奉献,特此鸣谢!
------本人网名**KevinChengCW(QQ:1210618015)**原ExcelTip.Net总版主之一

评论列表
sitemap