excel列统计相同元素(excel统计相同内容数量)

Excel表格存在重复数据时，使用SUMPRODUCT函数实现连续的排名

SUMPRODUCT函数的基本功能是返回多个数组元素对应乘积的和，但是SUMPRODUCT还有很多高阶的用法。这篇文章为朋友们分享使用SUMPRODUCT函数实现连续的排名、也叫中式排名。

一.SUMPRODUCT函数简介：

1语法：SUMPRODUCT(array1, [array2], [array3], ...)

2参数：

（1）Array1 必需。其相应元素需要进行相乘并求和的第一个数组参数。

（2）Array2, array3,... 可选。2 到 255 个数组参数，其相应元素需要进行相乘并求

3说明：

（1）数组参数必须具有相同的维数，否则，函数 SUMPRODUCT 将返回错误值 #VALUE!。

（2）函数 SUMPRODUCT 将非数值型的数组元素作为 0 处理。

4基本用法演示：

二.RANK和SUMPRODUCT函数排名的区别：

当存在重复的数据时；RANK函数排名会出现间断，SUMPRODUCT 函数则可以实现连续的排名。如下图所示因为存在两个总分为278的同学，SUMPRODUCT 函数将总分276的同学排名为4，而RANK函数将其排名为5。

三.SUMPRODUCT函数实现中式排名的操作方法及公式解析：

1.在F2单元格输入公式：

=SUMPRODUCT(($E$2:$E$13>=E2)/COUNTIF($E$2:$E$13,$E$2:$E$13))

2.公式解析：

（1）COUNTIF($E$2:$E$13,$E$2:$E$13)的功能是统计E2:E13单元格区域每个单元格内容在这个单元格区域出现的次数；这部分函数的倒数1/COUNTIF($E$2:$E$13,$E$2:$E$13)的功能是保证在求和时对于重复出现的元素的和为1（这里是实现中式排名的关键）。例如总分278出现两次则返回的值为0.5。

（2）($E$2:$E$13>=E2)返回的是由逻辑值构成的数组。当E2:E13的单元格内容大于等于E2单元格内容时返回TRUE,否则返回FALSE。在与（1）构造的数组相乘时，true代表1、false代表0。

（3）当公式填充到F6单元格时；E4和E5单元格利用上述构造的数组求和为1（出现两次，每次的值都为0.5），所以能够实现连续的排名。

四.注意事项：

1.SUMPRODUCT函数为数组函数，所以在输入公式时要Ctrl+Shift+Enter填充。

2.将公式修改为=SUMPRODUCT(1/COUNTIF($E$2:$E$13,$E$2:$E$13))可以实现不重复个数的计数。

Excel按指定数字重复数据

小伙伴们，大家好！今天和大家分享按指定数字重复数据技巧。

先来看一下效果图，A列文本，B列数量，在D列根据B列的数字重复A列的文本。

方法1 函数公式

公式：=LOOKUP(ROW(1:1)-1,SUBTOTAL(9,OFFSET(B$1,,,ROW($1:1))),A$2:A2)&""

方法2 Excel Word双剑合璧

在C2输入公式往下复制

=REPT(A2&CHAR(10),B2)

按Ctrl+C复制C2:C5到Word中，按Ctrl+V粘贴，然后Ctrl+A全选，按Ctrl+C复制

回到Excel中。按Ctrl+V粘贴就OK了。

方法3 Power Query

Power Query在office2016及以上版本都有自带这个功能了

鼠标指针点击数据源任意一单元格，点击数据选项卡，点击来自表格/区域

勾选表包含标题，确定。

进入Power Query编辑器，点击添加列，自定义列，输入公式：={1..[数量]}，确定。其中的“数量”是字段标题。

扩展新行。

在“产品”字段上右键，删除其他列。

然后点击主页→关闭并上载，会生成一个新的工作表，将转换好的数据上载到工作表中。

如果后期对数据源增加或删除，只需在转换后的数据表格上右键刷新就可以。

好了，今天的分享就是这些内容，祝您学习愉快！

图文作者：心电感应