excel 去重计数_Excel常用的数据分析技巧（5）

这是Excel系列的第五篇，之后的excel篇章接地气程度会越来越高（都是被工作“熏陶”出来的）。

为啥要写excel系列，百度不香吗？

香，可excel从来不是会不会，而是熟不熟。

以下场景想必分析师都很熟悉：

需求方搬个小板凳坐你旁边，我想这样，这样？你帮我整出来。。。难道你一边百度，一边做吗？ 这不浪费了一个在需求方面前展示666的机会嘛。
领导汇报，需要你在汇报现场快速出图。

于是小编就写了几篇excel，就停不下来了。。。自从接触excel图表之道，才发现“图表”其本质是对数据源的理解，X结构的数据就出X样的图。

Excel篇：

求知鸟：Excel常用的数据分析技巧（1）

求知鸟：数据分析常用的excel函数（2）

求知鸟：Excel常用的数据分析技巧(3)

求知鸟：Excel常用的数据分析技巧(4)

以下是正文：

在之前的文章中，总结过去除重复值的方法：

1、数据—数据工具—删除重复值（以当前选定区域排序：只对选中区域去重；扩展选定区域：会对这个小区域块进行联合去重。
2、数据透视表出来的数据自动去除重复值。
3、开始—条件格式—突出显示单元格规则—重复值
4、SQL： distinct 对所有列去除重复值。

此外，还有 PQ，Python等各种辅助工具。

那么，在excel中如何实现去重计数了？

OX01 去重计数

这里介绍工作中用到的两种方法（留言区可推荐更多方法哈）：

1、sumproduct函数

1）countif函数：对应单元格及向下拉出现重复的次数。

2）然后对1)出现的结果求倒数。

3）内存数组求和。

为帮助理解，介绍个sumproduct的case：

先判断C2:C13的值是否等于”西门庆”，相等则返回TRUE，不等则返回FALSE，由此建立一个有逻辑值构成的内存数组。SUMPRODUCT有一个特性，它会将非数值型的数组元素作为0处理，逻辑值自然是属于非数值型的数组元素，我们使用*1的方式，把逻辑值转化为数值，TRUE转化为1，FALSE转化为0，最后统计求和。

2、excel透视表，需要添加一个复选框。（其本质是进入PQ模式）。

OX02 频数分布直方图与频率分布直方图

频率分布直方图能清楚显示各组频数分布情况又易于显示各组之间频数的差别。它主要是为了将我们获取的数据直观、形象地表示出来，让我们能够更好了解数据的分布情况，因此其中组距、组数起关键作用。分组过少，数据就非常集中；分组过多，数据就非常分散，这就掩盖了分布的特征。当数据在100以内时，一般分5~12组为宜。

从频率分布直方图可以估计出的几个数据：

众数：频率分布直方图中最高矩形的底边中点的横坐标 。若是最高矩形有两个，那么众数也有两个！

href="https://baike.baidu.com/item/%E7%AE%97%E6%9C%AF%E5%B9%B3%E5%9D%87%E6%95%B0/7567019">算术平均数：频率分布直方图每组数值的中间值乘以面积后相加。（面积和为1，频率也为1）

加权平均数：加权平均数就是所有的频率乘以数值后的和相加。

中位数：把频率分布直方图分成两个面积相等部分的平行于Y轴的直线横坐标。 [1]

方差：(各矩形横坐标中点-平均值) ^2 乘以各自矩形的面积 的和

频率分布直方图与概率密度曲线之间关系：

当样本量不断增加而组距不断减小，每一组的平均频率密度就非常接近组中值处的频率密度，此时频率密度直方图的矩形顶边就非常接近一光滑曲线，该曲线就是频率密度函数曲线

绘制频率分布直方图步骤：

1．找出所有数据中的最大值和最小值，并算出它们的差（极差）。

2．决定组距和组数。

3．确定分点。

4．将数据以表格的形式列出来。（列出频率分布）

5．画频数分布直方图（横坐标为样本资料、纵坐标是样本频率除以组距）。

频率分布直方图与频数分布直方图：

频率分布直方图用纵坐标来表示“频率/组距”，即用小长方形的面积来表示频率；

频数分布直方图用纵坐来表示“频数 ”，即用小长方形的高来表示频数。

OX03 字符处理函数

python有更强大的正则表达式可以做数据处理（pandas 中的replace函数和re 模块），但若用excel处理起来很快，就不必用py。工具用啥无所谓，关键是熟练。

1、提取第一个间隔符"/"前的数据，如下图所示的B列计算结果。

=LEFT(A2,FIND("/",A2)-1)

FIND函数发现"/"在A2单元格中首次出现的位置（find是从左向右找），然后使用LEFT函数从左向右提取该长度的字符，即为结果。

=left(text,num_chars)
text代表用来截取的单元格内容。
num_chars代表从左开始截取的字符数。

2、 提取最后一个间隔符"/"后的数据。如下图所示的B列计算结果

此题用find函数就不可解，find是从左向右找。

=TRIM(RIGHT(SUBSTITUTE(A2,"/",REPT(" ",100)),100))

SUBSTITUTE(A2,"/",REPT(" ",100))，这部分公式将A2中的"/"替换为100个空格，然后RIGHT函数从右边提取100个字符，这100个字符必然包括了最后一个"/"后的数据以及大部分的空格，因此最后用TRIM函数清除空格即为结果。

SUBSTITUTE(text，old_text，new_text，[instance_num])
Text是需要替换其中字符的文本，或是含有文本的单元格引用；
Old_text是需要替换的旧文本；
New_text用于替换old_text 的文本；
TRIM(text) ：清除空单元格
REPT（ text， number_times). ：将text内容重复number_times.

听说，双击 文章会出现神奇效果，各位小伙伴不妨试试（zan一个）。

最后，请关注我的数据分析专栏（微信公众号：求知鸟）：

数据分析​zhuanlan.zhihu.com