查看: 7173|回复: 21
如何统计满足条件的区域内不重复数值个数
阅读权限20
在线时间 小时
序号数值1数值2数值3数值42dfghg eryjl3dfghg 2dfghg 3eryjl5642wetrjkl3eryuhjk
(1.64 KB, 下载次数: 81)
14:54 上传
点击文件名下载附件
请问：序号为2时，数值1到数值4各单元格中不重复个数
阅读权限100
在线时间 小时
Sub lqxs()
Dim Arr, i&, j&, aa, c%
Dim d, k, t
Set d = CreateObject(&Scripting.Dictionary&)
Sheet1.Activate
Arr = [a1].CurrentRegion
For i = 2 To UBound(Arr)
& & d(Arr(i, 1)) = d(Arr(i, 1)) & i & &,&
Next
k = d.keys
t = d(2): d.RemoveAll
t = Left(t, Len(t) - 1)
If InStr(t, &,&) Then
& & aa = Split(t, &,&)
& & For j = 0 To UBound(aa)
& && &&&For c = 2 To UBound(Arr, 2)
& && && && &If Arr(aa(j), c) && && Then
& && && && && & d(Arr(aa(j), c)) = d(Arr(aa(j), c)) + 1
& && && && &End If
& && &&&Next
& & Next
Else
& & For c = 2 To UBound(Arr, 2)
& && &&&If Arr(t, c) && && Then
& && && && &d(Arr(t, c)) = d(Arr(t, c)) + 1
& && &&&End If
& & Next
End If
If d.Count & 0 Then MsgBox d.Count
End Sub
复制代码
阅读权限100
在线时间 小时
& & & & & & & &
请见附件。点击按钮即可。
15:18 上传
点击文件名下载附件
9.89 KB, 下载次数: 99
阅读权限100
在线时间 小时
=COUNT(0/(MATCH(B2:E10,T(OFFSET(B1,MOD(SMALL(ROW(1:9)/1%+IF(A2:A10=2,ROW(1:9)*{1,1,1,1},99),ROW(1:36)),100),MOD(ROW(4:39),4))),)=ROW(1:9)*4-{3,2,1,0}))复制代码数组公式。
阅读权限20
在线时间 小时
感谢两位！由于我的数据量比较大，同时计算结果需要在固定的表格中显示，有以下问题需要两位再帮助完善一下：
1.对于VBA能否让它自动显示在某一单元格中。
2.对于数组公式能否再简单一些或不用数组公式，以免计算缓慢。
3.我水平有限，又想学到知识，学以致用，两位老师能否将各自方法的意思做一下讲解。
非常感谢两位老师的赐教，谢谢！
阅读权限95
在线时间 小时
=SUM((COUNTIF(OFFSET(B2,,,ROW(1:9),4),B2:E10)=1)*(IF(A2:A10=2,B2:E10,)&&0))
阅读权限95
在线时间 小时
本帖最后由 ykqrs 于
17:32 编辑
wlc5201130 发表于
=SUM((COUNTIF(OFFSET(B2,,,ROW(1:9),4),B2:E10)=1)*(IF(A2:A10=2,B2:E10,)0))
我琢磨了很长时间，你那个也不对COUNTIF(OFFSET(B2,{0;1;2;3;4;5;6;7;8},,,{1,2,3,4}),B2:E10)
我这个能和你合在一起就好了
阅读权限95
在线时间 小时
& & & & & & & &
wlc5201130 发表于
=SUM((COUNTIF(OFFSET(B2,,,ROW(1:9),4),B2:E10)=1)*(IF(A2:A10=2,B2:E10,)0))
我找到了和在一起的方法，这样就不会有错误了，=SUM((COUNTIF(OFFSET(B2,{0;1;2;3;4;5;6;7;8},,-{1;2;3;4;5;6;7;8;9},{1,2,3,4}),B2:E10)=1)*(IF(A2:A10=2,B2:E10,)&&0))复制代码
(2.65 KB, 下载次数: 72)
17:56 上传
点击文件名下载附件
阅读权限95
在线时间 小时
& & & & & & & &
这样看起来能短一些=SUM((COUNTIF(OFFSET(B2,ROW(1:9)-1,,-ROW(1:9),{1,2,3,4}),B2:E10)=1)*(IF(A2:A10=2,B2:E10,)&&0))复制代码
这个公式是有问题的。&
阅读权限100
在线时间 小时
學習各位的解法
玩命加载中，请稍候
玩命加载中，请稍候
Powered by
本论坛言论纯属发表者个人意见，任何违反国家相关法律的言论，本站将协助国家相关部门追究发言者责任！ & & 本站特聘法律顾问：徐怀玉律师 李志群律师|关于我们|微信编辑器|加入我们|意见反馈|在线交谈|网站地图Excel常见问题:统计不重复数据的个数_Excel技巧网_传送门
Excel常见问题:统计不重复数据的个数
1、本站畅销20万册的Excel\Word图书，请点击“阅读原文”。2、本微信首页底部莱单，可查看所有历史消息。3、微信回复：“教程”二字，可获取海量Excel视频学习教程下载地址。（二）疑难96 在Excel中，如何统计不重复数据个数在处理各种业务时，经常需要统计某个数据区域内的不重复数据个数。如图 96-1所示A列是一组随机产生的数据，现需要统计其中不重复数据个数，即重复出现的数据按1个计算，该如何操作呢？图96-1统计不重复数据个数→ 解决方案1：使用1/COUNTIF与SUM函数组合统计不重复数据个数。→ 操作方法在C2单元格输入以下2个公式之一：公式1
{=SUM(1/COUNTIF(A2:A16,A2:A16))}公式2
=SUMPRODUCT(1/COUNTIF(A2:A16,A2:A16))→ 原理分析1．使用COUNTIF函数进行条件统计，返回A2:A16单元格区域内每个数据出现次数的数组：{2;2;1;3;2;1;3;2;1;1;2;1;1;2;3}被1除后，生成数组：{1/2;1/2;1;1/3;1/2;1;1/3;1/2;1;1;1/2;1;1;1/2;1/3}即出现N次重复的，就变成N个1/N，求和就是1，达到重复值只算1次的目的。2．最后使用SUM或SUMPRODUCT函数求和即可得到区域内不重复值的个数。→ 知识扩展1/COUNTIF函数统计不重复值的优缺点1．统计区域内不得有空单元格，否则返回#DIV/0!错误。可以使用嵌套IF函数的数组公式解决这个问题，公式如下：{=SUM(IF(A2:A16"",1/COUNTIF(A2:A16,A2:A16)))}2．因为EXCEL浮点运算可能产生误差而造成答案不正确，即公式返回值比正确值小。对于这种情况，可以用嵌套ROUND函数修正。3．COUNTIF函数对数据类型没有要求，文本、数值、逻辑值、错误值均可，每一种错误值算作一个不重复数据。4．统计区域不限于单行或单列（即一维引用），可以是多行多列的矩形区域，但必须是对单元格区域的引用，而不能是非引用类型的数组。→ 解决方案2：使用MATCH=ROW比较判断统计不重复数据个数。操作方法在C2单元格输入下列2个公式之一：公式1
{=SUM(--(MATCH(A2:A16,A2:A16,)=ROW(2:16)-1))}公式2
=SUMPRODUCT(--(MATCH(A2:A16,A2:A16,)=ROW(2:16)-1))→ 原理分析1．使用MATCH函数返回区域内每个数据第一次出现的位置数组：{1;2;3;4;1;6;4;8;9;10;8;12;13;2;4}2．然后与其对应的行号位置比较，因为只有第一次出现的位置才会一致，所以统计的是不重复个数。3．使用减负运算将判断结果返回的逻辑值转换为1、0，最后使用SUM或SUMPRODUCT求和即可得出不重复数据的个数。关于MATCH函数语法，请参阅：第4章第1节疑难120→ 知识扩展MATCH=ROW法统计不重复值的优缺点1．统计区域内不得有空单元格，否则MATCH函数返回#N/A错误。对于包含空单元格的区域，可以用文本合并进行相应的处理，公式如下：=SUMPRODUCT((A2:A16"")*(MATCH(A2:A16&"",A2:A16&"",0)=ROW(2:16)-1))其中，(A2:A16"")用于防止将空单元格&""后算作一个空文本数据。2．不会有浮点运算误差。3．数据类型可以是文本、数值、逻辑值，但不得包含错误值。4．数据可以是内存数组，也可以是单元格区域的引用，但必须是单行或单列。→ 解决方案3：使用FREQUENCY函数统计不重复数字个数。→ 操作方法在C2单元格输入下列2个公式之一：公式1
=COUNT(1/FREQUENCY(A2:A16,A2:A16))公式2
=SUM(--(FREQUENCY(A2:A16,A2:A16)>0))→ 原理分析1．使用FREQUENCY函数返回统计区域内数字的分布频率数组：{2;2;1;3;0;1;0;2;1;1;0;1;1;0;0;0}由此可见，第一次出现的数字位置返回数字出现个数，而第2次出现就返回0，因此只要统计非0的个数即可。2．公式1使用1/FREQUENCY将0转换为#DIV/0!错误值，再利用COUNT函数忽略错误值的特性统计数组中非0数字的个数，公式2使用--(FREQUENCY>0)将数组大于0判断得到的逻辑值转换为数值1、0，再使用SUM函数求和，由此实现统计不重复数据个数。3．由于FREQUENCY函数默认返回数组，COUNT、SUM函数均将其视为常量数组代入计算，因而无需按组合键形成数组公式。关于FREQUENCY函数语法，请参阅：第3章疑难108→ 知识扩展FREQUENCY函数统计不重复值的优缺点1．统计区域可以有空单元格。因为FREQUENCY函数将忽略空白单元格和文本。2．使用公式2没有浮点运算误差。3．数据必须为数值，如果是一维的文本数据，可以借助MATCH函数转换为序列号数值再进行统计。4．参数不受引用或数组的尺寸范围限制，可以支持多行多列的数值数据的不重复统计。版权所有，侵权必究。1、本站畅销20万册的Excel\Word图书，请点击“阅读原文”。2、本微信首页底部莱单，可查看所有历史消息。3、微信回复：“教程”二字，可获取海量Excel视频学习教程下载地址。
觉得不错，分享给更多人看到
Excel技巧网 微信二维码
分享这篇文章
Excel技巧网 最新文章如何用SUMPRODUCT函数和COUNTIF函数计算不重复人数 - 简书
如何用SUMPRODUCT函数和COUNTIF函数计算不重复人数
文 / 康康哥哥假如我们有一张表，其中有很多人名，但是我们想知道这份表里总共有多少个不重复的人名，用公式该怎么实现呢？
看过多少人主演的片子
计算不重复人数可以用SUMPRODUCT函数和COUNTIF函数组合来实现。我们先来看看这两个函数怎么用。SUMPRODUCT顾名思义，一个是“SUM”，意思是求和；另一个是“PRODUCT”，意思是乘积。所以这个函数的意思就是先乘积，再把乘积的结果求和。这么说有点抽象哈，下面我们举个栗子就好懂啦。=SUMPRODUCT(A1:A8,B1:B8)：把A1:A8和B1:B8两个单元格区域中行对应的单元格的数值相乘，然后再把所有得到的乘积累加。通俗点讲，计算过程分两步：（1）A1*B1，A2*B2，……，A8*B8；（2）把第一步中8个值相加。这个栗子中参数的个数是2个，实际上SUMPRODUCT函数的参数可以有很多个，也可以是一个，但是有一点要注意的是，这些参数必须是数组，并且各个数组的维数必须一致！又有点抽象对不对？稍微解释一下：（1）所谓“数组”通俗点讲就是M行N列的一个数据区域，比如A3:C10就是一个8行3列的数组。
（2）所谓维数一致是指SUMPRODUCT函数的各个参数必须有相同的行数和列数。比如E5:G12也是一个8行3列的数组，它和A3:C10同时作为SUMPRODUCT的函数是不会出错的。但是如果不是一个8行3列的区域（比如8行2列，或者3行8列），同时作为SUMPRODUCT函数的参数是会报错“＃VALUE!”滴！（3）常用的数组是一维数组，即一行值或一列值，比如A1:D1，A1:A5等。（4）如果SUMPRODUCT的参数只是一个单元格的数值的话，也是不算错的，因为单个单元格不就是一个1*1的数组嘛！（5）假如SUMPRODUCT函数只有一个参数的话，那么就用不着跟谁乘了，就把数组里的各个值相加就完了。比如=SUMPRODUCT(A1:A5)，其结果就=A1+A2+A3+A4+A5。（6）如果SUMPRODUCT函数有多个参数的话，就像2个参数那样把各自对应的数组元素相乘后，再把结果相加就好了。（不难理解吧，认真脸。）OK，废了好大劲终于把SUMPRODUCT函数的基本内容介绍完了，下面我们再来认识一下COUNTIF函数。讲真，COUNTIF函数是一个超好用的函数！它的主要作用是统计满足条件的单元格有多少个。语法规则是这样的：=COUNTIF(数据区域，计数的条件)第二个参数是计数条件，根据其要求的内容来统计第一个参数数据区域中满足条件的单元格有多少个。还是举个栗子比较容易懂。
各种数据类型
如图中所示，假如我们在某空白单元格中输入：（1）=COUNTIF(H2:H9,890)：表示在H2:H9数据区域中统计值为890的单元格的个数。在图中很容易看出来，只有一个单元格满足条件，那么这个公式的结果就是1；（2）=COUNTIF(H2:H9,"&&0")：表示在H2:H9数据区域中统计不等于0的单元格的个数。很显然，除了H4是0之外，其他单元格都不等于0，因此这个公式的结果是7；（3）=COUNTIF(H2:H9,H4)：表示在H2:H9数据区域中统计值等于H4中单元格值的单元格的个数。也很容易看出来只有H4本身和H4相等，因此这个公式的结果是1；（4）=COUNTIF(H2:H9,"&"&H2)：表示在H2:H9数据区域中统计数值大于H2值的单元格个数。这里注意两点：一是“&”要有引号，二是“&”和“H2”之间要用“&”连接。这个公式的结果是2，这貌似和我们在图中看到的不一样，不是只有890比32大吗，为啥结果是2？这个并不难解释哦，因为H9中的日期本质上也是数字，只是显示为日期格式而已。如果我们选中H9单元格，将其数字格式改为“常规”，或直接用快捷键“Ctrl+Shift+~”，就可以看到日期格式变成了常规格式，原来就是个数字嘛！这样是不是比32大的数字有2个了？
常规格式的日期
（5）=COUNTIF(H2:H9,"&&")：表示在H2:H9数据区域中统计不等于真空的单元格个数。我们看到H7中是没有任何内容的，是一个真空单元格，那么这个公式的值显而易见就是7了！现在我们将两个函数都学会了，那么下面就让我们回到开头的问题，怎样用SUMPRODUCT函数和COUNTIF函数计算不重复的人数。
输入统计函数
在D1单元格中输入公式：=SUMPRODUCT(1/COUNTIF(A2:A15,A2:A15))结果我们已经看到了，是8。数一数A列是不是正好有8个名字呢？下面我们来分析一下这个公式的原理。首先看最内层COUNTIF(A2:A15,A2:A15)：表示在A2:A15这个数据区域中分别找值等于A2，A3，……A15的值有多少个，我们可以自己数一下，其结果会返回一个内存数组：{2,2,3,3,2,2,2,1,2,1,3,1,2,2}其次再看1/COUNTIF(A2:A15,A2:A15)：表示将上述内存数组取倒数：{1/2,1/2,1/3,1/3,1/2,1/2,1/2,1,1/2,1,1/3,1,1/2,1/2}最后再看最外层SUMPRODUCT(1/COUNTIF(A2:A15,A2:A15))：表示将上述取倒数后的内存数组求和，很显然，如果某个名字在表中出现N次，那么取倒数后该数就会变成1/N，恰好在内存数组中又会有N个1/N，这N个1/N用SUMPRODUCT求和后变成了1。也就是说，如果有M个名字，那么整个数组求和后最后的值恰好就是M！！！是不炒鸡赞~两个看似很简单的函数合体之后会发挥难以置信的作用，这就是Excel的奥妙所在，也是我们学习Excel的动力所在。好了，今天就到这里了，祝所有热爱学习的孩子都能天天进步~
不装逼的新时代青年，不矫情的反鸡汤作者，不妥协的高格调斗士。
做为互联网的一个工作人员，尤其是运营岗位，一天工作时间最多触碰的工具就是excel了，比如数据整理、分析、设计报表、做后台数据字典、做运营计划等等。Excel绝对是运营人首要掌握的工具，所以本文汇总两类技能：Excel常用快捷键大全和35个Excel常用函数。 Excel常...
上次给大家分享了《2017年最全的excel函数大全（2）——web函数》，这次分享给大家查找和引用函数（上）。 ADDRESS 函数 含义 你可以使用 ADDRESS 函数，根据指定行号和列号获得工作表中的某个单元格的地址。例如，ADDRESS(2,3) 返回 $C$2。...
之前罂粟姐姐推过一篇文章Excel中SUMIF函数的10个高级用法！超级经典~，有小伙伴回复说：“还是SUMPRODUCT函数好用”。 的确，刚入门学习EXCEL的时候，最先接触到的条件统计函数就是SUMIF、SUMIFS、COUNTIF、COUNTIFS等，非常简单，容易...
我们首先看到的是1级竞技场，也就是最初级的水平。在这个层级当中的需求呢，通常是一些最基本的统计需求，例如求和、统计个数、求平均值、最大值、最小值等等；有时候还需要在统计的基础上对结果的精度做一些调整，例如四舍五入并保留几位小数，或者直接保留整数结果等等。 那么在这里需要用...
按照用途分类出以下统计函数： AVEDEV 用途：返回一组数据与其平均值的绝对偏差的平均值，该函数可以评测数据(例如学生的某科考试成绩)的离散度。语法：AVEDEV(number1,number2,...)
参数：Number1、number2、...是用来计算绝对偏差平...
愁眉不展，孤立无援，隐忍难过 Step 2 【连接】你目前的情绪是由什么事件引起的？ 婆婆带娃总是不让宝宝睡觉，宝宝已经困的眼睛睁不开了还不哄睡。 Step 3 【意义】这件事，为何会激起我的这些情绪？ 主要是医生说宝宝体重不达标，原因是睡眠不好会影响身体发育 Step 4...
我从未圣洁过的心 又被赠与了疮痍 躺在死寂的深渊里 恶毒的花在我的头发里开满地 向我瞳仁里泼下的火油 曾以为是蜜饯而欣喜不已 玻璃珠在日光下温柔而明媚 向我抛来的微笑样的弧线 将它抓在手里 却变成了沉重的子弹 把手心熔出一个凝神的黑洞 灼烧起我的灵魂 面包刀的尖端对着我的胸...
文 | 萧寒晨夕 01
多少人想结束这百无聊赖的前半生 有一天中午去外面餐馆吃饭，常去的那家，人满为患，左手的邻桌有两个女人，看起来应该都三十五不到。两个人面对面坐着，其中一个噼里啪啦说话声音很大，另外一个则几乎全程没有啃声，安静异常。 “我就是胆小就是懦弱就是不自信，怎...
日，2017“我好像在哪儿见过你”巡回演唱会深圳站结束后，薛之谦更新了一条微博： 愿上半年没有让大家失望... 过完长假... 这四只MV一起上.... 晚安... 深圳....
日，高尚MV发布。 薛之谦有时候不守信用的很，明...
[Unity]技术学习路线图(长期更新) 效果实现如图 对象层级关系图 PacketCell - Right对象作为单元格背景 PacketContainer对象作为单元格容器 PacketLabel对象作为单元格物体 PacketCell - Left对象作为单元格背景 ...EXCEL如何得出数组序列中不重复单元格且满足某一条件的个数?_百度知道
EXCEL如何得出数组序列中不重复单元格且满足某一条件的个数?
如表：人员
30000现在想要一个公式计算出某个部门如“财务部”的不重复的人员的个数。
补充：不要透视，不建辅助列，不进行其他手动操作，求一数组相关公式。
答题抽奖
首次认真答题后
即可获得3次抽奖机会，100%中奖。
=SUMPRODUCT((B2:B1000=&财务部&)*(1/COUNTIF(A2:A00)) 注意B2：B1000， A2:A1000
调整为实际范围 ， 否则 会返回
我自己凑合出了个公式：=SUMPRODUCT(1/COUNTIF($A$2:$A$9,$A$2:$A$9)*($B$2:$B$9=&财务部&))与你的最近，选你了。多谢
=roundup(SUMPRODUCT((B2:B1000=&财务部&)*(1/COUNTIF(A2:A00)),0)
采纳率：56%
来自团队：
在除A,B列外的任一单元格输入公式:=COUNT(IF(B1:B100=&财务部&,IF(MATCH(A1:A100,A1:A100,)=ROW(1:100),)))这是数组公式,要按CTRL+SHIFT+ENTER三键结束
本回答被网友采纳
=SUMPRODUCT((B1:B10=&财务部&)*(MATCH(A1:A10&B1:B10,A1:A10&B1:B10,)=ROW(A1:A10)))
用数据透视表吧，很简单，行标签选人员，筛选（页面）选部门，金额放到数据区域，汇总选计数，直接就出来
数据透视筛选部门
其他3条回答
为您推荐：
其他类似问题
excel的相关知识
换一换
回答问题，赢新手礼包
个人、企业类
违法有害信息,请在下方选择后提交
色情、暴力
我们会通过消息、邮箱等方式尽快将举报结果通知您。