如何多条件去除重复项计个数.xls

月份船名编号1广州10011广州10021深圳10031深圳10041深圳10042深圳10052深圳10062深圳10072陆丰10082陆丰1008多条件去除重复项计个数如何求出1月份深圳编号的个数。要将重复的个数去除不计在内。月份船名编号个数(0)(1)(2)(3)(4)(5)(6)1广州100121广州100111TRUE1广州FALSEFALSE1广州10021广州100222TRUE1广州FALSEFALSE1深圳10031深圳100333TRUE1深圳TRUETRUE1深圳10041深圳100444TRUE1深圳TRUETRUE1深圳10041深圳100445 FALSE 1深圳TRUEFALSE2深圳10052深圳100566TRUE2深圳FALSEFALSE2深圳10062深圳100677TRUE2深圳FALSEFALSE2深圳10072深圳100788TRUE2深圳FALSEFALSE2陆丰10082陆丰100899TRUE2陆丰FALSEFALSE2陆丰10082陆丰1008910 FALSE 2陆丰FALSEFALSE(0)=$A$2:$A$11&$B$2:$B$11&$C$2:$C$11(1)=MATCH($A$2:$A$11&$B$2:$B$11&$C$2:$C$11,$A$2:$A$11&$B$2:$B$11&$C$2:$C$11,0)(2)=ROW($A$2:$A$11)-ROW($A$2)+1(3)=(MATCH($A$2:$A$11&$B$2:$B$11&$C$2:$C$11,$A$2:$A$11&$B$2:$B$11&$C$2:$C$11,0)=ROW($A$2:$A$11)-ROW($A$2)+1)(4)=$A$2:$A$11&$B$2:$B$11(5)=($A$2:$A$11&$B$2:$B$11)=1深圳(6)=(MATCH($A$2:$A$11&$B$2:$B$11&$C$2:$C$11,$A$2:$A$11&$B$2:$B$11&$C$2:$C$11,0)=ROW($A$2:$A$11)-ROW($A$2)+1)*$A$2:$A$11&$B$2:$B$11=1深圳(7)=ROW($A$2:$A$11)(8)=IF(MATCH($A$2:$A$11&$B$2:$B$11&$C$2:$C$11,$A$2:$A$11&$B$2:$B$11&$C$2:$C$11,0)=ROW($A$2:$A$11)-ROW($A$2)+1)*$A$2:$A$11&$B$2:$B$11=1深圳,ROW($A$2:$A$11)(7)(8)2 FALSE3 FALSE44556 FALSE7 FALSE8 FALSE9 FALSE10 FALSE11 FALSE=MATCH($A$2:$A$11&$B$2:$B$11&$C$2:$C$11,$A$2:$A$11&$B$2:$B$11&$C$2:$C$11,0)=(MATCH($A$2:$A$11&$B$2:$B$11&$C$2:$C$11,$A$2:$A$11&$B$2:$B$11&$C$2:$C$11,0)=ROW($A$2:$A$11)-ROW($A$2)+1)=(MATCH($A$2:$A$11&$B$2:$B$11&$C$2:$C$11,$A$2:$A$11&$B$2:$B$11&$C$2:$C$11,0)=ROW($A$2:$A$11)-ROW($A$2)+1)*$A$2:$A$11&$B$2:$B$11=1深圳=IF(MATCH($A$2:$A$11&$B$2:$B$11&$C$2:$C$11,$A$2:$A$11&$B$2:$B$11&$C$2:$C$11,0)=ROW($A$2:$A$11)-ROW($A$2)+1)*$A$2:$A$11&$B$2:$B$11=1深圳,ROW($A$2:$A$11)=IF(MATCH($A$2:$A$11&$B$2:$B$11&$C$2:$C$11,$A$2:$A$11&$B$2:$B$11&$C$2:$C$11,0)=ROW($A$2:$A$11)-ROW($A$2)+1)*$A$2:$A$11&$B$2:$B$11=1深圳,ROW($A$2:$A$11)=*贴子主题：如何多条件去除重复项计个数?大眼鱼 等级：新手上路 如何多条件去除重复项计个数?点击浏览该文件 2003-1-19 22:42:00 劍魔 头衔：ExcelHome顾问 =COUNT(IF(MATCH($A$1:$A$11&$B$1:$B$11&$C$1:$C$11,$A$1:$A$11&$B$1:$B$11&$C$1:$C$11,0)=ROW($A$1:$A$11)*$A$1:$A$11&$B$1:$B$11=1深圳,ROW($A$1:$A$11)按ctrl+shift+enter -人生到處知何似？應似飛鴻踏雪泥，泥上偶然留指爪，鴻飛那復計東西。2003-1-19 23:05:00 大眼鱼 等级：新手上路 老大你的公式有点长,还有我有点看不懂.=SUM(IF(B2:B11=深圳,IF(COUNTIF(C2:C11,C2:C11)=0,1/COUNTIF(C2:C11,C2:C11)上面那个公式只有一个条件,如果要加上月份这个条件上去应怎做?帮我修改下好不?那是数组公式来的 2003-1-19 23:26:00 风之助 等级：初级三 （1）公式：=SUM(IF(B2:B11=深圳,IF(COUNTIF(C2:C11,C2:C11)=0,1/COUNTIF(C2:C11,C2:C11)是不正确的，得不出正确结果（除非凑巧）（2）以下公式是去除重复项计数的两种方法（例如去除重复项计数B1:B10）：第一种方法用数组公式：=SUM(IF(COUNTIF($B$1:$B$10,$B$1:$B$10)=0,0,1/COUNTIF($B$1:$B$10,$B$1:$B$10)第二种方法是我从上边剑魔版主的公式里提出来的数组公式：=COUNT(IF(MATCH($B$1:$B$10,$B$1:$B$10,0)=ROW($B$1:$B$10),ROW($B$1:$B$10)以下我将上边剑魔版主的公式的每一步过程列出来，我也是这么一步一步理解的：附件 -好风凭借力，送我上青云。2003-1-21 14:18:00 劍魔 头衔：ExcelHome顾问 TO:风之助 舉一反三，分析詳實，給你個精華，但希望大眼魚也能看得懂。-人生到處知何似？應似飛鴻踏雪泥，泥上偶然留指爪，鴻飛那復計東西。2003-1-21 18:57:00 =COUNT(IF(MATCH($A$1:$A$11&$B$1:$B$11&$C$1:$C$11,$A$1:$A$11&$B$1:$B$11&$C$1:$C$11,0)=ROW($A$1:$A$11)*$A$1:$A$11&$B$1:$B$11=1深圳,ROW($A$1:$A$11)人生到處知何似？應似飛鴻踏雪泥，泥上偶然留指爪，鴻飛那復計東西。=SUM(IF(B2:B11=深圳,IF(COUNTIF(C2:C11,C2:C11)=0,1/COUNTIF(C2:C11,C2:C11)上面那个公式只有一个条件,如果要加上月份这个条件上去应怎做?=SUM(IF(B2:B11=深圳,IF(COUNTIF(C2:C11,C2:C11)=0,1/COUNTIF(C2:C11,C2:C11)（2）以下公式是去除重复项计数的两种方法（例如去除重复项计数B1:B10）：=SUM(IF(COUNTIF($B$1:$B$10,$B$1:$B$10)=0,0,1/COUNTIF($B$1:$B$10,$B$1:$B$10)=COUNT(IF(MATCH($B$1:$B$10,$B$1:$B$10,0)=ROW($B$1:$B$10),ROW($B$1:$B$10)以下我将上边剑魔版主的公式的每一步过程列出来，我也是这么一步一步理解的：人生到處知何似？應似飛鴻踏雪泥，泥上偶然留指爪，鴻飛那復計東西。=COUNT(IF(MATCH($A$1:$A$11&$B$1:$B$11&$C$1:$C$11,$A$1:$A$11&$B$1:$B$11&$C$1:$C$11,0)=ROW($A$1:$A$11)*$A$1:$A$11&$B$1:$B$11=1深圳,ROW($A$1:$A$11)