精华内容
下载资源
问答
  • 去重复计算数量,函数公式实现难度大;❸ 提问的是个男生。我尝试着把问题精简了一下,是这样的,你就凑活着看吧。一列「用户 ID」,一列「活动日期」,现在想统计,每个用户参加活动的天数。因为用户可能在 1 天...

    私信回复关键词【工具】,获取Excel高效小工具合集!

    让你的Excel效率开挂~(◦˙▽˙◦)

    前段时间遇到这样一个问题,让我很头疼。

    2d9b8be7f233b8de3823636ea5749fd8.png

    头疼的原因有 3 点:

    ❶ 问题描述不清晰,理解起来困难;

    ❷ 去重复计算数量,函数公式实现难度大;

    ❸ 提问的是个男生。

    570711720f92f55b83e697a621f2d3e7.png

    我尝试着把问题精简了一下,是这样的,你就凑活着看吧。

    985257b92035a716eb5b731d2b1c64fa.png

    一列「用户 ID」,一列「活动日期」,现在想统计,每个用户参加活动的天数。

    因为用户可能在 1 天中参加多次活动,所以要根据「用户 ID」对「活动日期」去除重复,然后再计数。

    明白了吗?

    明白了,咱们就开始干!

    add48db65f6019f3833500ddf36b2252.png

    01

    方法一

    关于去除重复计数,也就是统计唯一值,Excel 中有一个经典的用法。

    使用 SUMRODUCT 和 COUNTIF/COUNTIFS 函数完成。

    = SUMPRODUCT(1/COUNTIF(统计区域,统计区域))

    现在看不懂没关系,我们通过这个案例,一起走一遍这个过程。

    ❶ COUNTIFS 统计数量。

    首先是统计数量,因为这里有「用户 ID」「活动日期」两列数据,所以我们用 COUNTIFS 函数。

    202e9cae952c77913476bf31bbea8c38.png

    完成公式如下:

    =COUNTIFS(C2:C16,C2:C16,B2:B16,H2)

    计算结果:

    = {0;0;0;0;0;0;0;0;0;0;0;1;1;1;1}

    注意:这里有一个数组的用法,在判断条件的参数中使用数组,那么计算的结果,也是对应数量的数组。

    d804667ba7ecf7df17256a4771805aa2.png

    ❷ 数量求倒数。

    接下来,用 1 除以计数结果,获取对应的倒数。

    2048a20c14ad83a7f8176d40c73dede1.png

    完成公式如下:

    =1/COUNTIFS(C2:C16,C2:C16,B2:B16,H2)

    计算结果:

    ={#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;1;1;1;1}

    ▲左右滑动查看

    因为「1/0」会出现「#DIV/0!」的错误,所以公式外面,再加一个 IFERROR 容错:

    =IFERROR(1/COUNTIFS(C2:C16,C2:C16,B2:B16,H2),0)

    计算结果:

    ={0;0;0;0;0;0;0;0;0;0;0;1;1;1;1}

    这一步是非重复计数的关键操作,结合下一步倒数求和,会更容易理解。

    ❸ SUMPRODUCT 倒数求和。

    因为 SUM 函数不支持数组操作,所以这里使用 SUMPROUDCT 进行求和。

    580c8c9d4a17d30aca806a923543cf1e.png

    完成公式如下:

    =SUMPRODUCT(IFERROR(1/COUNTIFS(C2:C16,C2:C16,B2:B16,H2),0))

    计算结果:

    = 4

    到这一步,你可能就明白求倒数的意义了。

    如果相同数据出现了 2 次,那么计数过程就是「1/2 + 1/2 =2」;

    如果出现了 3 次,就是 3 个「1/3」相加「=3」;

    其他次数以此类推,即实现了非重复计数。

    ❹ 增加「用户 ID」判断。

    但是上一步计算结果,显然是错的,QY1 的去重计数,应该是 1 才对啊。

    这是因为计数的过程,没有对用户进行限制。

    因为 QY1 有「10/4」的记录,所有的「10/4」都被统计到 QY1 用户上了。

    c4b5cd117f0aa9c2ae16cbbbbd7c733a.png

    所以需要再增加一个用户条件的判断,这里使用 EXACT 函数实现。

    c261d9d47f205783e6c91d4f99b7d22f.png

    完整公式如下:

    =EXACT(B2:B16,H2)

    计算结果:

    ={FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE}

    ▲左右滑动查看

    EXACT 的作用,是判断两个数值是否相等。

    因为 EXACT 中也是引用了区域,所以计算结果是一个 TRUE 和 FALSE 的数组。

    接下来,是把 EXACT 的计算结果,作为条件添加到前面的公式中。

    方法很简单,和第 1 步的计数过程相乘就可以了。

    9cfd0abb9d5d8c4cafabfdec734b9251.png

    完成公式如下:

    =SUMPRODUCT(IFERROR(1/COUNTIFS(C2:C16,C2:C16,B2:B16,H2)*EXACT(B2:B16,H2),0))

    ▲左右滑动查看

    计算结果:

    =1

    这样就把非当前用户的统计给去掉了,也就得到了最终的结果。

    02

    方法二

    方法 1 是传统的方法,经过一番折腾,最终算是圆满完成了需求。

    但是正如你所感受到的,传统函数公式的思路太古怪,一般人很难想到用 1/次数的方法,来做去重计数。

    d23d0d68653613e8d7842a4b97b9f89a.png

    这也是函数公式难学的主要原因。

    正常用户的思路,不应该是这样的嘛?

    ❶ 筛选用户 ID

    ❷ 去除重复值

    ❸ 统计数量

    这个正常的思路,用传统公式是很难实现的。

    但是 Office 365 中新增的 FILTER 和 UNIQUE 函数,让这个过程变的简单,变的正常了。

    ❶ 筛选用户 ID。

    使用新增的 FILTER 函数,可以轻松的根据「用户 ID」筛选对应的记录。

    74085cbe92f834207416ebc89e8c7432.png

    完整公式如下:

    =FILTER($C$2:$C$16,$B$2:$B$16=H4)

    计算结果:

    ={43739;43739;43739;43740;43741;43742}

    FILTER 的作用就是筛选符合条件的记录。

    (日期返回的是数字格式,所以变成了 43739 的样子。)

    ❷ 去除重复值。

    Office 365 中新增的 UNIQUE 函数,就是用来去除重复值的。

    ecd62314dc70c46d9cb1622a9c21970d.png

    完整公式如下:

    =UNIQUE(FILTER($C$2:$C$16,$B$2:$B$16=H4))

    计算结果:

    ={43739;43740;43741;43742}

    注意到了吗?FILTER 筛选出来的重复值,被 UNIQUE 函数一下子去除掉了。

    ❸ 统计数量。

    有了去重后的筛选结果,统计数量太简单,就是普通的 COUNTA 函数嘛。

    aa3cfe8ed2f5027f926615dc928d342b.png

    完整公式如下:

    =COUNTA(UNIQUE(FILTER($C$2:$C$16,$B$2:$B$16=H4)))

    计算结果:

    = 4

    简单的 3 个步骤,符合常规思路,你肯定一下子就学会了,不是吗?

    03

    总结

    温馨提示:

    FILTER 和 UNIQUE 函数,目前只有 Office 365 的版本才有,而且需要参加「预览版体验计划」。

    今天的非重复计数学会了吧,别忘了点个赞!

    私信回复关键词【工具】,获取Excel高效小工具合集!

    让你的Excel效率开挂~(◦˙▽˙◦)

    6ae9eb7a38cfe1f001a52ce5cbbe7490.png
    展开全文
  • 整行重复的,用高级筛选很容易去除,对于关键字重复,只需保留一行的情况 我这里有一个比较简便易学的办法,就是用辅助列公式处理, 网上有些用countif的不少做法 ,那样选出来的是所有重复数据, 而在有多个数据...

    平时除了vlookup(点击查看详解)外,恐怕被问起最多的就属去除重复数据了,重复数据产生的原因很多种,录入重复 、数据合并后重复等等。最后的数据形式就是有一个关键字重复或者整行数据都有重复。整行重复的,用高级筛选很容易去除,对于关键字重复,只需保留一行的情况 我这里有一个比较简便易学的办法,就是用辅助列公式处理, 网上有些用countif的不少做法 ,那样选出来的是所有重复数据, 而在有多个数据的关键字行留下一行, 需要手动去留下多个重复数据的一行, 我这里介绍的方法, 可以一次性筛选出所有非重复数据 ,而且留下重复数据中的一行。原理(能看懂就看,看不懂可忽略):首先对关键字进行排序,然后用match函数对当前行关键字对整列数据进行匹配,返回当前数据匹配到的行标。

    当数据重复的时候 每个关键字就会匹配到第一个,所以当前辅助列公式返回的值就会不等于本行位置,这样就可以标识出非重复的关键字行,示例如下。

    在去重列, ture即表示非重复数据, false即多余的重复数据, 然后利用自动筛选, 筛选出false行 删除即可得到非重复数据(或者筛选出ture复制到新表也可达到非重复数据)

    当然,现在excel2007版本以上可以用系统提供的去重功能,但是看不到中间去重过程,我这个方法一是可以用在2003版本上,二就是理解透彻的话,结合其他公式可以更精准的控制去重。

    2017-08-17 by excel880

    展开全文
  • 假设A列中数据如下,去除重复项后的结果数据如B列, 在B2单元格输入公式:=IFERROR(INDEX(A:A,MATCH(,COUNTIF(B$1:B1,IF(A$2:A$99="",$A$1,A$2:A$99)),)+1),"") 然后按下Ctrl+Shift+Enter组合键激活数组公式,下拉...

    假设A列中数据如下,去除重复项后的结果数据如B列,

    在B2单元格输入公式:=IFERROR(INDEX(A:A,MATCH(,COUNTIF(B$1:B1,IF(A$2:A$99="",$A$1,A$2:A$99)),)+1),"")

    然后按下Ctrl+Shift+Enter组合键激活数组公式,下拉数组公式即可。

     

    转载于:https://www.cnblogs.com/xmqlv20081008/p/8078760.html

    展开全文
  • 去除重复内容,就用这个EXCEL公式,1分钟搞定! 一份高中录取名册中,上千条学生信息,生源校有七八十所,如何从表中轻松提取出不重复的生源校名单呢? 有人或许会这样做:复制录取名册中所有生源校到一个空白...

    去除重复内容,就用这个EXCEL公式,1分钟搞定!

    一份高中录取名册中,上千条学生信息,生源校有七八十所,如何从表中轻松提取出不重复的生源校名单呢?

    有人或许会这样做:复制录取名册中所有生源校到一个空白EXCEL表中,再按学校名排序,最后分段再删除重复的部分。这样做,不仅费时、费力,最重要的,还有可能误删了数据。

    其实,我们可以运用一个数组公式,就可以轻松提取出不重复内容。

    在上图,F2单元格中,写下数组公式:=INDEX(D:D,1+MATCH(,COUNTIF(F$1:F1,D$2:D$21),))&"",编辑完成后,按Ctrl+Shift+回车(一定要按这三个键噢!),然后往下复制,直至没有新学校名称出现为止。

    现在,为大家讲解一下这个数组公式:

    1)COUNTIF(F$1:F1,D$2:D$21):用COUNTIF函数,从F1单元格开始的扩展区域,查找D$2:D$21区域(生源校)每个数据的个数,返回一个由0和1构成的数组,如果D$2:D$21区域的数值在公式扩展区域中出现过,结果就是1;如果没出现,结果就是0。这个公式中,COUNTIF函数的第一参数,采用了行绝对引用(F$1)和相对引用(F1),向下复制,依次会产生:F$1:F2、F$1:F3、F$1:F4、。。。公式提取结果就会被重复利用。

    2)利用MATCH函数,在COUNTIF函数返回的数组中查找第一个0的位置,也就是查找首次出现的数据所在的位置。由于生源校的标题行占了1行,所以,将这个值+1,就得到需要提取的不重复值在查找区域中行的位置。

    3)最后,利用INDEX函数,以MATCH函数的计算结果作为索引值,提取D列对应位置上的不重复值。用&" ",避免显示错误值。

    这个公式,可能有一点点理解难度,不过,不影响你使用,到时,你只要会复制公式,改一下参数就行了。今后,对于类似提取不重复内容的工作,就会既轻松,又准确、高效。

    每天学习一点点,每天进步一点点,积硅步,必能致千里,得大成。

    展开全文
  • 去重复计算数量,函数公式实现难度大;❸ 提问的是个男生。我尝试着把问题精简了一下,是这样的,你就凑活着看吧。一列「用户 ID」,一列「活动日期」,现在想统计,每个用户参加活动的天数。因为用户可能在 1 天...
  • 点击上方蓝字关注星标★不...❷ 去重复计算数量,函数公式实现难度大;❸ 提问的是个男生。我尝试着把问题精简了一下,是这样的,你就凑活着看吧。一列「用户 ID」,一列「活动日期」,现在想统计,每个用户参加活动...
  • 在InfoPath的重复表中使用公式去筛选最小值数据 by S.Y.M. Wong-A-Ton 学习如何在INFOPATH中使用公式(而不使用代码)找到重复表中某个栏的最小值并且高亮显示。 需求: 当你创建一个重复表,里面...
  • 起因:因领导层需要将电商平台利润核算自动化,原始数据不可以动,所以需要用到公式 (PS:其实所谓自动化不是Python / VBA办公自动化,就是嵌套一堆公式,然后每月更新电商后台基础数据,就可以出来利润结果~) ...
  • 比如下面的表格就一个月累计数据,那么,如果想知道整个表格中去除重复的人名后,一共有多少个销售人员,我们可以通过Sum结合Countif的数组方法求得该结果。函数公式在E2单元格输入公式【{=SUM(1/COUNTIF($B$2:$B$13...
  • execl去除重复数据

    2018-07-26 15:59:44
    比较两个execl表是否有重复完整的公式=IF(A2=Sheet2!A2:A2864,"是","否")两个表的内容如上,需要对比出其中的重复数据1、首先在B2位置输入=if(A2= #A2表示需要对比的数据开始位置2、切换到另一个sheet表,从上到下...
  • 当我们在测试阶段时难免会出现一些程序写错,导致数据重复,本人因为这个问题,倒腾了几天,刚开始的思路是写脚本,因为自己语言学的太菜,导致连一个定义的公式都没想出来,后来是利用MongoDB里面的聚合解决的。...
  • 起因:因领导层需要将电商平台利润核算自动化,原始数据不可以动,所以需要用到公式(PS:其实所谓自动化不是Python / VBA办公自动化,就是嵌套一堆公式,然后每月更新电商后台基础数据,就可以出来利润结果~)正文:...
  • 今日目标:学会使用UNIQUE函数今天看到一个金句:每天在几十个群里解答Excel的问题,答疑经验告诉我:这句话说的TMD对啦!很多人连「我还没准备好」这一步都还...百度到的公式,大部分情况都让人看不懂。=INDEX($B$...
  • 单列,多列和区域去除重复公式的详细讲解
  • 真实数据可能很多个我们可能会有以下的几个需求1、找出重复的数据2、计算重复的数据重复的次数是多少3、找出不重复的数据4、去除重复值保留一个今天我们通过Excel技巧或函数公式依次来进行解决1、找出重复我们可以...
  • 真实数据可能很多个我们可能会有以下的几个需求1、找出重复的数据2、计算重复的数据重复的次数是多少3、找出不重复的数据4、去除重复值保留一个今天我们通过Excel技巧或函数公式依次来进行解决1、找出重复我们可以...
  • list集合对象去除重复,值相加求和功能快捷键合理的创建标题,有助于目录的生成如何改变文本的样式插入链接与图片如何插入一段漂亮的代码片生成一个适合你的列表创建一个表格设定内容居中、居左、居右SmartyPants创建...
  • 微视短视频去水印工具 趣头条视频如何去重复 直接使用公式的配置,那么,公式来自何处?爆炸冠军助手简单易用,可收集成千上万的爆炸标题公式,现在我们可以看到生活提示和复制数据等视频。,在用户点击购买后,作者...
  • 直接在excel里面去除重复

    千次阅读 2014-04-18 22:04:38
    2003版用高级筛选, 数据-筛选-高级筛选 ...当然,还可以用函数公式的方法来取得不重复值 具体步骤,现演示如下: 设数据如图: 现要删除“姓名”和“电话”都重复的行。 点“数据”
  • 去重复计算数量,函数公式实现难度大;❸ 提问的是个男生。我尝试着把问题精简了一下,是这样的,你就凑活着看吧。一列「用户 ID」,一列「活动日期」,现在想统计,每个用户参加活动的天数。因为用户可能在 1 天...
  • 而在一份汇总数据中,会经常出现的一个情况就是,数据中包含许多的重复内容。这里我们就需要通过各种方法,将表格中唯一的值提取出来。如上图订单编号可以看的,我们编号数据中有许多红色标记的编号是出现重复的。...
  •   公式:全排列数f(n)=n!(定义0!=1),如1,2,3三个元素的全排列为: ​ 1,2,3 ​ 1,3,2 ​ 2,1,3 ​ 2,3,1 ​ 3,1,2 ​ 3,2,1 ​ 共3 * 2 * 1 = 6 种。 分析   由图可以看出可以利用分治法,先...
  • 今天和分享2种不借助公式就能在excel中删除重复值的方法,10秒搞定1000条数据!!在记录了很多数据的表格中,难免会出现一些重复的记录,如果某字段具有唯一性,不允许与其他内容重复,就需要对重复值进行处理,常用...
  • 有助于目录的生成如何改变文本的样式插入链接与图片如何插入一段漂亮的代码片生成一个适合你的列表创建一个表格设定内容居中、居左、居右...列表如何创建一个注脚注释也是必不可少的KaTeX数学公式新的甘特图功能...
  • 通过公式处理的数据跟其它数据有关联 需要对这些数据进行去除它们的关联只需要它们的值,复制之后,不直接粘贴,点击右键,有粘贴的 选项,一般我选择值粘贴,快捷键是Ctrl+Alt+v 筛选出重复数据 筛选下面的条件...
  • 当遇到要计算一个巨大表格中某列实际个数有多少时(实际存在重复项)要计算该列项的个数,该如何是好呢??如下表格的简单示范形式。...2、答:在表格中任一空白处,填入公式:=SUM(IF(COUNTIF(A2:A8,A2:A8)
  • 大家好,我是你们的拉登老师。...比如 Excel 最常见的,提取非重复值的问题。上面这个表格中,要从「部门」和「姓名」中提取非重复值,怎么做?百度到的公式,大部分情况都让人看不懂。公式如下:=...
  • 在新表格填充数据的位置输入公式:SUMIF(条件区域,条件,求和数据区域); 条件区域:原表格所有数据;条件:删除重复项时所参考的条件,此为PN编码;求和数据区域:原表格中需求和的那一列数据 例: 此表格中填写...

空空如也

空空如也

1 2 3 4 5 ... 16
收藏数 305
精华内容 122
关键字:

去重复公式