分类统计

作者：绿色风分类：时间：2022-08-18 浏览：158

楼主悟空师弟	如下图：根据A1:A10统计出如下数据：类别： 1、只有“情怀”两字的单元格数量 2、“情怀”两字在最左边的单元格数量 3、“情怀”两字在最右边的单元格数量 4、“情怀”两字在中间的单元格数量 5、包含“情怀”两字的单元格数量说明：只有“情怀”两字的(比如A1单元格内)，不算在“最左边”，也不算在“最右边”和“中间”。要求：E3单元格内输入公式下拉得出所有结果。详见附件：分类统计.rar 下月初公布答案！
2楼 pzh130	=countif(A1:A10,"情怀") =countif(A1:A10,"情怀?") =countif(A1:A10,"?情怀") =countif(A1:A10,"?情怀?") =countif(A1:A10,"情怀") 看错了！做来做去做不好啊，笨办法，把上面的都联起来 =CHOOSE(ROW(1:1),COUNTIF(A$1:A$10,"情怀"),COUNTIF(A$1:A$10,"情怀?"),COUNTIF(A$1:A$10,"?情怀"),COUNTIF(A$1:A$10,"?情怀?"),COUNTIF(A$1:A$10,"情怀*"))
3楼 raulerini	先写个复杂点的。用通配符穷举 =COUNTIF(A$1:A$10,CHOOSE(ROW(A1),"","","?","?","")&"情怀"&CHOOSE(ROW(A1),"","?","","?",""))
4楼优优	我来领作业
5楼 rongjun	分类统计.rar
6楼 liuguansky	=COUNTIF(A$1:A$10,IF(ROW(A1)>2,"","")&IF(MOD(ROW(A1),5)>2,"?情怀","情怀")&IF(MOD(ROW(A1),2),"","?")&IF(MOD(ROW(A1),3)+ROW(A1)>3,"",""))
7楼 mn860429	=CHOOSE(ROW(1:1),COUNTIF($A$1:A10,"情怀"),COUNTIF($A$1:A10,"情怀")-COUNTIF($A$1:A10,"情怀"),COUNTIF($A$1:A10,"情怀")-COUNTIF($A$1:A10,"情怀"),COUNTIF($A$1:A9,"情怀")-SUM($E$2:E2),COUNTIF($A$1:A10,"情怀"))
8楼 foodorwater	分类统计.zip
9楼禾火亦	1、=COUNTIF(A1:A10,"情怀") 2、=SUMPRODUCT((FIND("情怀",A1:A10,1)=1)(LEN(A1:A10)<>2)) 3、=SUMPRODUCT((RIGHT(A1:A10,2)="情怀")(LEN(A1:A10)>2)) 4、=SUMPRODUCT((FIND("情怀",A1:A10,1)>1)(FIND("情怀",A1:A10,1)<LEN(A1:A10)-1)) 5、=COUNTIF(A1:A10,"情怀*")
10楼 Zaezhong	=COUNTIF(A1:A10,"情怀") =COUNTIF(A1:A10,"情怀?") =COUNTIF(A1:A10,"?情怀") =COUNTIF(A1:A10,"?情怀?") =COUNTIF(A1:A10,"情怀") 分类统计.rar
11楼海洋之星	不知道是否正确 1、 =COUNTIF(A1:A10,"情怀") 2、 =COUNTIF(A1:A10,"情怀?") 3、 =COUNTIF(A1:A10,"?情怀") 4、 =COUNTIF(A1:A10,"?情怀?") 5、 =COUNTIF(A1:A10,"情怀") 分类统计.rar
12楼 piny	E3=INDEX(COUNTIF(A$1:A$10,{"","","?","?",""}&"情怀"&{"","?","","?",""}),ROW(A1)) 下拉
13楼 LOGO	E3输入公式 =COUNTIF($A$1:$A$10,CHOOSE(ROW(A1),"情怀","情怀?","?情怀","?情怀?","情怀")) 下拉
14楼水星钓鱼	=COUNTIF(A1:A10,"情怀") =COUNTIF(A1:A10,"情怀?") =COUNTIF(A1:A10,"?情怀") =COUNTIF(A1:A10,"?情怀?") =COUNTIF(A1:A10,"情怀") 分类统计.rar
15楼悟空师弟	公布答案！

免责声明

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

评论列表

楼主悟空师弟	如下图：根据A1:A10统计出如下数据：类别： 1、只有“情怀”两字的单元格数量 2、“情怀”两字在最左边的单元格数量 3、“情怀”两字在最右边的单元格数量 4、“情怀”两字在中间的单元格数量 5、包含“情怀”两字的单元格数量说明：只有“情怀”两字的(比如A1单元格内)，不算在“最左边”，也不算在“最右边”和“中间”。要求：E3单元格内输入公式下拉得出所有结果。详见附件：分类统计.rar 下月初公布答案！
2楼 pzh130	=countif(A1:A10,"情怀") =countif(A1:A10,"情怀?") =countif(A1:A10,"?情怀") =countif(A1:A10,"?情怀?") =countif(A1:A10,"情怀") 看错了！做来做去做不好啊，笨办法，把上面的都联起来 =CHOOSE(ROW(1:1),COUNTIF(A$1:A$10,"情怀"),COUNTIF(A$1:A$10,"情怀?"),COUNTIF(A$1:A$10,"?情怀"),COUNTIF(A$1:A$10,"?情怀?"),COUNTIF(A$1:A$10,"情怀*"))
3楼 raulerini	先写个复杂点的。用通配符穷举 =COUNTIF(A$1:A$10,CHOOSE(ROW(A1),"","","?","?","")&"情怀"&CHOOSE(ROW(A1),"","?","","?",""))
4楼优优	我来领作业
5楼 rongjun	分类统计.rar
6楼 liuguansky	=COUNTIF(A$1:A$10,IF(ROW(A1)>2,"","")&IF(MOD(ROW(A1),5)>2,"?情怀","情怀")&IF(MOD(ROW(A1),2),"","?")&IF(MOD(ROW(A1),3)+ROW(A1)>3,"",""))
7楼 mn860429	=CHOOSE(ROW(1:1),COUNTIF($A$1:A10,"情怀"),COUNTIF($A$1:A10,"情怀")-COUNTIF($A$1:A10,"情怀"),COUNTIF($A$1:A10,"情怀")-COUNTIF($A$1:A10,"情怀"),COUNTIF($A$1:A9,"情怀")-SUM($E$2:E2),COUNTIF($A$1:A10,"情怀"))
8楼 foodorwater	分类统计.zip
9楼禾火亦	1、=COUNTIF(A1:A10,"情怀") 2、=SUMPRODUCT((FIND("情怀",A1:A10,1)=1)(LEN(A1:A10)<>2)) 3、=SUMPRODUCT((RIGHT(A1:A10,2)="情怀")(LEN(A1:A10)>2)) 4、=SUMPRODUCT((FIND("情怀",A1:A10,1)>1)(FIND("情怀",A1:A10,1)<LEN(A1:A10)-1)) 5、=COUNTIF(A1:A10,"情怀*")
10楼 Zaezhong	=COUNTIF(A1:A10,"情怀") =COUNTIF(A1:A10,"情怀?") =COUNTIF(A1:A10,"?情怀") =COUNTIF(A1:A10,"?情怀?") =COUNTIF(A1:A10,"情怀") 分类统计.rar
11楼海洋之星	不知道是否正确 1、 =COUNTIF(A1:A10,"情怀") 2、 =COUNTIF(A1:A10,"情怀?") 3、 =COUNTIF(A1:A10,"?情怀") 4、 =COUNTIF(A1:A10,"?情怀?") 5、 =COUNTIF(A1:A10,"情怀") 分类统计.rar
12楼 piny	E3=INDEX(COUNTIF(A$1:A$10,{"","","?","?",""}&"情怀"&{"","?","","?",""}),ROW(A1)) 下拉
13楼 LOGO	E3输入公式 =COUNTIF($A$1:$A$10,CHOOSE(ROW(A1),"情怀","情怀?","?情怀","?情怀?","情怀")) 下拉
14楼水星钓鱼	=COUNTIF(A1:A10,"情怀") =COUNTIF(A1:A10,"情怀?") =COUNTIF(A1:A10,"?情怀") =COUNTIF(A1:A10,"?情怀?") =COUNTIF(A1:A10,"情怀") 分类统计.rar
15楼悟空师弟	公布答案！