精华内容
下载资源
问答
  • excel去重函数
    千次阅读
    2020-12-23 10:08:32

    在日常工作问题中,经常会遇到一个问题,当你呼哧呼哧处理了大半天的数据后才发现,原始数据好多重复的,导致你得到的结果全部错误……

    顿时,想砍人的心都有了,心理阴影面积无穷大……

    当然,胖斯基也遇到过,不过秉着吃一堑长一智的精神,后来凡是遇到数据问题处理,先检查是否有重复项,待处理完后再继续往下。

    So,我们今天的主题是面对重复值,你如何处理?当然处理重复值的方法有很多,今天重点从countif函数入手,着手界面针对不同重复情况下的问题处理。

    为了便于问题的逐步深入,我们先从最基本,最简单的入手。

    用Excel自带功能,进行重复值的判断,如下:

    上面的办法的确标记出了重复值,但是比如要判断重复几次?第几次重复?只保留唯一呢?又该如何处理?

    函数COUNTIF,不陌生,其含义是:COUNTIF(rang,criteria),翻译成人话就是说:这个函数是用来统计某一个区间,满足某个条件的个数,其规范:COUNTIF(区域,条件),看着比较简单,看看它的应用。

    1. 判断用户姓名是否重复

    公式:=IF(COUNTIF($A$3:$A$14,A3)>1,"重复","")

    说明:

    COUNTIF($A$3:$A$14,A3)很好理解,在A3:A14区间范围内查找A3,看其出现几次;利用IF增加判断,如果大于1,显然是重复。

    2. 判断用户是否重复,若是重复,且非首次出现则标记为重复,否则空白

    公式:=IF(COUNTIF($A$3:A3,A3)>1,"重复","")

    说明:COUNTIF($A$3:A3,A3)相对于问题1,有点变化,就是区间的范围发生了变化,变成了$A$3:A3,这个需要看【$】的理解了,在胖斯基看来,【$】像大头钉,钉住某个单元格,让其不动。所以$A$3:A3可以理解为,起始范围A3是固定不变的,结束范围A3是变化的,可以为A4,A5,A6…… 具体看公式的拖拽范围。

    所以上述公式可以理解为,从A3开始,逐步A3:A3,A3:A4,A3:A5……等形成一个变化的增大区间,在借助其条件,来统计个数。由于是逐渐变大范围,所以一个数据如果重复,那其第1次出现,其结果为1,第2次,第3次,则逐渐为2,3,……n,所以利用IF来判断就可以解决问题。

    3. 判断用户是否重复,若是重复,且非最后出现则标记为重复,否则空白

    公式:=IF(COUNTIF(A3:$A$14,A3)>1,"重复","")

    说明:COUNTIF(A3:$A$14,A3)可以理解为固定住范围区间的结尾,而范围区间的开始则是个变化值。

    为了便于理解,我们将范例1-3的核心部分做一下对比,便于理解。

    关注公式部分

    如果你看懂了,相信9*9乘法表,你也会了,如下:

    公式略,如果想了解,请回复胖斯基

    4. 名单中不重复的人数一共有几个?

    公式=SUM(1/COUNTIF(A3:A14,A3:A14)),其为数组公式,三键一起按

    说明:COUNTIF(rang,criteria)第2个参数是一个条件值,但是此时COUNTIF(A3:A14,A3:A14),第2个参数是一个区间,故此时是一个数组公式。

    即在A3:A14区域中,其区域下的每一个值都要去判断出现了几次,得到结果如下:{3;3;3;1;2;2;3;3;3;1;2;2},再用1来除,得到其倒数,={0.33;0.33;0.33;1;0.5;0.5;0.33;0.33;0.33;1;0.5;0.5},再SUM求和,即可得到结果。

    5. 提取名单中不重复人的姓名

    INDEX(A:A,MIN(IF(COUNTIF($B$2:B2,$A$3:$A$14)=0,ROW($A$3:$A$14),4^8)))&""

    说明:

    COUNTIF($B$2:B2,$A$3:$A$14)重点在这里,在B2开始的区域里面,逐渐扩大区域,在该区域中去判断A列的数据出现几次,如果为0次,则记录其行号ROW($A$3:$A$14),反之记录4^8(65536,足够大的一个数据);

    再用Min函数,随着单元格的拖动来形成一个新的数组,最后借助Index来取出A列数据。

    例5 是在Excel的文本去重中是比较常用的,这里简要描述,待后续详细讲解此类方法。

    胖斯基说:

    想要学好Excel,就从这些简单的函数开始吧!也许万事开头难,没准坚持坚持就变成大神了!

    更多相关内容
  • 起因:因领导层需要将电商平台利润核算自动化,原始数据...先进行计算当列有多少数值,再使用INDIRECT函数引用此数值,进行套用未进化的方法1 / 未进化的方法2 因为此方法太麻烦,且不常用,故而就只是说一个概念留存~

    起因:因领导层需要将电商平台利润核算自动化,原始数据不可以动,所以需要用到公式

    (PS:其实所谓自动化不是Python / VBA办公自动化,就是嵌套一堆公式,然后每月更新电商后台基础数据,就可以出来利润结果~)

    正文:

    普通方法1:

    直接使用=SUM(1/COUNTIF(A1:A1000,A1:A1000))

    普通方法2:

    =SUMPRODUCT(1/COUNTIF(A1:A500,A1:A500))

    但是:

    上面的两个方法都有一个BUG,如果选中的区域有空,那么就会报错 #DIV/0!,因为是除法,有空,就意味着有0,除以0就会报错。

    进化方法1:

    =1/COUNTIFS(A:A,A2)

    再对此列进行求和

    进化方法2:

    1、=COUNTIF($A$1:A3,A3)

    1.1、需要锁定第一个单元格,下拉公式,这样区域就会不断增加,

    2、=COUNTIF(B:B,1)

    2.1、再计算结果为1的数量

    进化方法3:

    先进行计算当列有多少数值,再使用INDIRECT函数引用此数值,进行套用未进化的方法1 / 未进化的方法2

    因为此方法太麻烦,且不常用,故而就只是说一个概念留存~

    展开全文
  • 我想要达到的结果 几个不同的普通公式都行,不要vba宏、数组公式三键回车等,谢谢
  • Excel函数学习:悟空百问の009:去除重复项的深入思考我们经常整理一些数据,数据不可以动原数据,又需要进行数据分析处理,整理,数据汇总,如何快速的找到这些重复的值,这成了我们很头疼的东西,今天就来和大家...

    Excel函数学习:悟空百问の009:去除重复项的深入思考

    我们经常整理一些数据,数据不可以动原数据,又需要进行数据分析处理,整理,数据汇总,如何快速的找到这些重复的值,这成了我们很头疼的东西,今天就来和大家探讨一下这个重复项的问题。

    今天的数据信息来自“古天乐慈善基金会”,来自一个“建校狂魔”古天乐的英勇的数据。

    本期重点

    方案一:删除重复值

    操作流程:菜单栏-数据-删除重复值

    方法剖析:删除重复值有几个注意事项

    1.直接动原数据,无法进行数据分析,直接见结果,无过程,没法确认重复项。

    2.速度太快,都没反应过来,脑瓜子跟不上(这应该是优点吧)

    3.无论什么数据直接可以进行行值的删除重复项,没有顾虑。

    去除重复项

    方案二:条件格式

    操作流程:菜单栏-开始-条件格式-突出单元格规则-重复值

    方法剖析:条件格式的几个相关事宜

    1.条件格式本身可以做很多操作,不仅仅是对重复值进行显示,也可以进行判断,进行公式填充,进行错误值显示,重复值显示仅仅是其中一个很小的一方面运用。

    2.条件格式和去除重复项唯一的区别:可能就是去重项直接出结果,条件格式给你一个过程,然后不出结果。给你显示重复的项目,标注出颜色,让你自己去处理,这个是增加数量还是删重。

    3.条件格式的运用可以令我们工作有很多信息借用这个信息判断,让我们操作的数据先行一步进行处理优化,然后进行人为操作。类似于粮草先行,先用条件格式进行数据分析,然后在处理,可以提高效率。

    条件格式

    方案三:COUNTIF函数的运用

    操作流程:函数运用,进行函数的数据判断后,用条件格式显示结果。

    COUNTIF函数

    函数解析:=COUNTIF(C$2:C$15,C2)

    查询C2的值在C2:C15中出现的次数

    通过条件格式,显示大于1.5的为红色出来的结果就是重复项目

    重点注意:

    1.COUNTIF函数是数据计数函数,计数某值出现的次数,这个可以计算概率,计算值综合运用效果显著。

    2.这里用了条件格式的另一种用法,就是大于某值时的显示,和重复值的显示是一个道理。

    3.这里问什么要讲这个COUNTIF函数,就是通过逻辑关系告诉你,一个EXCEL按钮,也可以通过其他方式显示,这个方式就是以后VBA的一个基础,通过逻辑判断,来显示我们需要的结果。

    方案三丶1/2:COUNTIF函数的运用

    嘿嘿,这已经结束了为什么还要讲一个3.1/3.2呢?你想函数时有没有想过一个很偏僻的想法,比如说,能不能显示第一次出现的重复项的值,能不能显示第二次重复项的值,那么第一次或者第二次的值就可以不用动了,我只动一个数值?想过这个点吗?这里其实值要对COUNTIF函数的 取值范围进行更换,就可以达到很多效果。

    比如说:

    第一次重复时显示重复数,第二次不显示

    =COUNTIF(C2:C$15,C2)

    向下拉取时区域,每一行少一行上面区域,所以选值范围缩小,第二次出现重复值不显示。

    显示第一次

    第二次重复时显示重复数,第一次不显示

    =COUNTIF(C$2:C2,C2)

    向下拉取时,增加选值区域,所以第二次出现重复会显示,第一次就不会显示。

    显示第二次

    总结

    本身的删除重复项要熟练运用,然后慢慢学习条件格式,进行进一步学习,最后学学函数,进行大脑开发,深入学习函数,真的可以提高逻辑判断能力,加油!

    展开全文
  • Excel去除重复值方法汇总

    千次阅读 2020-12-23 10:08:38
    经常有人问到excel中去除重复值的问题,这个问题本来也一直有很多人讨论,现将做法汇总出来共大家参考。什么是重复值顾名思义,重复值是指一组数据中有重复记录,去除这些重复的记录就叫去除重复值,具体有两种情况...

    经常有人问到excel中去除重复值的问题,这个问题本来也一直有很多人讨论,现将做法汇总出来共大家参考。

    什么是重复值 顾名思义,重复值是指一组数据中有重复记录,去除这些重复的记录就叫去除重复值,具体有两种情况:源数据如图

    去重后

    结果一:所有在源数据中出现过二次及以上的记录均已经去除,结果二指,结果数据表示保留的数据均在源数据中有,但在结果中只出现一次

    结果二就是我们常说的去重

    去重方法常见的去重方法很多,本文列举自带工具去重、高级筛选去重、函数去重、VBA去重、透视表去重和SQL去重共六种方法

    1.利用excel自带去重工具去重

    自excel2007以后,excel就增加了去重功能,具体看下图

    操作步骤:选中需要去重的原始数据/点击数据选项卡/点击删除重复项/确定

    2.所有excel版本均可实现的办法:高级筛选

    点击数据选项卡/点击排序和筛选组的高级按钮,弹出对话框

    按上图设置后,确定即可。A1:A9系源数据,条件区域E1是一个空白单元格,关键点在于要勾选“选择不重复记录”

    注意Excel97、XP及2003的高级筛选在数据菜单

    以上两种办法是最常见的办法,高级筛选还可以跨表操作,但实际工作中,去重不但要跨表,还要能更新列表,比如源数据是不断更新的,去重结果也是需要不断更新的,所以前文的方法并不能带来多少实际的用途,自然,更先进的办法也就来了

    3.函数去重

    ​ 太多的人喜欢用函数处理数据,所以去重也少不了他的身影,请看截图

    数据源在H1:H11区域,函数运算结果在I列,在I1单元格输入的函数公式:=INDEX(H:H,SMALL(IF(MATCH($H$1:$H$11,$H$1:$H$11,)=ROW($1:$11),ROW($1:$11),4^8),ROW(A1)))&""这是流传最广的函数写法之一

    提示:数组公式要三键结束(ctrl+shift+enter),然后下拉填充至出现空格

    公式翻译:I1单元格结果为index引用H列中的数据,数据排序号是数组按小排序的最小值1,该数组构成为

    如果在H1:H11区域中来精确查找H1:H11值的序号等于行号时取行号,否则取当前行号和65536(4^8或者2^16,EXCEL最大行号值2003及以前版本,2007以后版本为1048576即2的10次方2^20)

    上面太拗口,看公式解释:

    a.MATCH($H$1:$H$11,$H$1:$H$11,)是match的简写,标准写法为MATCH($H$1:$H$11,$H$1:$H$11,0),意思是在H1:H11区域中查找H1:H11的值,并且精确匹配,其结果是{1;2;3;4;2;6;3;8;8;2;2},可以解释为:H1:H11的出现顺序,从中可以看到第五个数据等于2,重复了,其余类似,

    b.MATCH($H$1:$H$11,$H$1:$H$11,)=ROW($1:$11)表示将{1;2;3;4;2;6;3;8;8;2;2}与H1:H11的行号比较(ROW是计算行号函数),得到的结果为{TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE}

    c.加上IF函数后,取值为{1;2;3;4;65536;6;65536;8;65536;65536;65536},

    d.再加上small函数,数组变成{1;2;3;4;6;8;65536;

    65536;65536;65536;65536}

    e.最好,index按顺序取值并连上””[&""]|排除错误或零值,得到结果

    f.当源数据区域发生变化,对应修改函数的取值区域后,三键结尾,下拉公式

    4.VBA代码去重

    ​ 用excel的VBA编程也可解决去重问题,最常使用的方法有以下

    a.ActiveSheet.Range("$H$1:$H$11").RemoveDuplicatesColumns:=1,

    Header:=xlNo这是VBA的RemoveDuplicates删除重复记录方法

    b.Range("H1:H11").AdvancedFilterAction:=xlFilterCopy,

    CopyToRange:=Range("X1"),

    Unique:=True 这是利用的VBA筛选去重

    c.用VBA字典看示例 以下三个来源于论坛,非原创

    示例1(可以添加三列记录到字典的)

    Sub Test()

    Dim i&, DicA As Object, DicB As Object, DicC As

    Object

    Dim Arr1, Arr2, Arr3

    Set DicA = CreateObject("scripting.dictionary")

    Set DicB = CreateObject("scripting.dictionary")

    Set DicC = CreateObject("scripting.dictionary")

    Arr1 = Range("A1:A60000")

    Arr2 = Range("B1:B60000")

    Arr3 = Range("C1:C60000")

    For i = LBound(Arr1) To UBound(Arr1)

    '循环数组Arr1

    DicA(Arr1(i, 1)) =""

    '添加不重复值到字典DicA

    Next i

    For i = LBound(Arr2) To UBound(Arr2)

    '循环数组Arr2

    If DicA.exists(Arr2(i, 1))Then

    '如果Arr2中的值存在于字典DicA中,则往字典DicB添加重复的值

    DicB(Arr2(i, 1)) =""

    End If

    Next i

    For i = LBound(Arr3) To UBound(Arr3)

    '循环数组Arr3

    If DicB.exists(Arr3(i, 1))Then

    '如果Arr3中的值存在于字典DicB中,则往字典DicC中添加重复的值

    DicC(Arr3(i, 1)) =""

    End If

    Next i

    Range("D1").Resize(DicC.Count, 1)

    =Application.Transpose(DicC.keys)

    '把DicC的keys值赋予给D列

    Set DicA = Nothing

    Set DicB = Nothing

    Set DicC = Nothing

    End Sub

    类似的,一个入库单汇总的小程序

    示例2 SubDataWrtin()

    Dim Arr,

    k%, str$

    Dim Ary,

    i%, icl%

    Dim Dic

    As Object

    Dim Sh As

    Worksheet

    Set Sh =

    Sheets("入库单数据库")

    Set Dic

    =CreateObject("Scripting.Dictionary")

    Arr

    =Sh.Range("E5", Sh.[E65536].End(3)(1, 3))”

    Ary =

    Arr

    i = 0

    For k = 1

    To UBound(Arr)

    str =Join(Application.Index(Arr, k), " ")

    If Not Dic.exists(str)Then

    Dic(str) =""

    i = i + 1

    For icl = 1 To 3

    Ary(i, icl) =Arr(k, icl)

    Next

    End If

    Next

    Dic.RemoveAll

    Sheets("目录").[A5].Resize(i,3) = Ary

    End Sub

    E5请做相应修改

    简单适用的B列元数据,E列去重结果

    示例3 Sub 筛选不重复数据()

    Set dic = CreateObject("Scripting.Dictionary")

    '字典

    For Each r In Sheets("Sheet1").Range("b2:b"&

    Sheets("Sheet1").[b65536].End(xlUp).Row)

    '数组

    On Error

    Resume

    Next

    '忽略错误继续执行VBA代码,避免出现错误消息

    If Not

    r.Value ="" Then dic.Add r.Value,

    ""

    '如果不是空,给字典添加内容

    Next

    Sheets("Sheet2").Range("e2").Resize(dic.Count,

    1)

    =Application.WorksheetFunction.Transpose(dic.keys)

    '从e2单元开始向下放

    On Error GoTo

    0

    '恢复正常的错误提示

    End Sub

    用VBA也比较麻烦,而且要求有一定的编程基础,所以接下来来跟容易的

    5.数据透视

    点插入选项卡/数据透视表,按图设置​

    把姓名拖入行标签中,右键透视表/透视表选项,取消列总计复选框,确定

    得到结果

    当据源发生变化时,右键透视表,刷新即可更新列表

    这是最简单的能更新的办法

    6.SQL联合查询去重

    ​利用SQL语句的去重功能实现,也能更新数据

    点数据选项卡/现有连接,出现如图

    点击浏览更多按钮,按资源管理器方式找到本excel文件并点击打开

    选中sheet1,确定

    点击属性按钮,打开属性对话框

    点击定义选项卡

    删除命令文本里面的内容,写入select distinct 姓名from

    [sheet2$]语句

    注意:除中文外,空格及标点均为英文小写状态输入,]后没有内容,点击确定

    选择放置结果的起始单元格,确定

    完成

    需要更新时,右击结果,刷新即可

    前述六种办法,一般情况下推荐使用第一二办法,需要经常更新时,建议采用第五第六种办法,使用数组函数在数据太多时电脑运行缓慢,不推荐,至于VBA,呵呵,能用VBA编程的高手不在乎这种小问题的

    展开全文
  • 于是去百度,但是发现好多东西百度上说的千花乱坠,但是真正可以实现的寥寥无几,于是自己又稍稍结合网上的东西以及自己的模糊记忆做了一些东西,现在将excel中的去重以及数据填充方法加以分享,愿对各位有所帮助。...
  • 去重条件计数(excel去重统计个数)

    千次阅读 2020-12-28 23:34:26
    去重条件计数(excel去重统计个数)2020-05-08 11:12:07共10个回答为达成效果,花时间完全照你的重录一遍,因为你没有列标行号,故以我下图为例,在充值人数去重合计栏的G2单元格输入公式=TEXT(SUMPRODUCT(VALUE(H2:J2)),...
  • Q:我也想像那些大神一样用Excel函数来删除重复数据,你可以给我写一个公式吗?A:怎样的数据源,先截图看看。Q:收到多个销售员发过来的客户跟进名单,由于要汇总,所以发现不同的销售员的名单有重复的客户姓名,...
  • excel去重计数_如何在Excel中计数

    千次阅读 2020-08-10 15:39:19
    excel去重计数 如何在Excel中计数 (How to Count in Excel)There are lots of different ways to count things in Excel – maybe you need to count the numbers in a column, or all the data, or just the ...
  • https://pypi.org/project/xlrd/ xlrd是1.2的版本 https://pypi.org/project/xlwt/ 分别下载tar.gz 文件 解压后的文件夹,cmd python setup.py install
  • excel多表格整合去重与空格去除

    千次阅读 2019-03-28 13:47:34
    1,检查下excel表格中需要整合去重的数据是否带有空格,如果带有空格必须先去除空格 首先选中需要去除空格的数据,之后单击【数据】按钮,选择【分列】,点击下一步, 点选【空格】 继续点选下一步,最后点选...
  • 写在前面的Tips: 使用函数drop_duplicates 其中参数subset可以指定按照某字段进行去重。效果如下图 原创文章 12获赞 11访问量 587 关注 私信 展开阅读全文 作者:Haaaley
  • 在工作中使用Excel 2010时,经常会有需要在对原始记录清单进行整理时,剔除其中一些重复项。接下来本文就来讲解下Excel 2010中去除重复项的几种常用技巧。所谓的重复项,通常是指在Excel 2010中某些记录在各个字段...
  • EXCEL中如何用一个公式去统计某单一品牌的总销售额准备工具/材装有windows 10的电脑一台Microsoft Office 家庭和学生版 2016 excel软件。excel表格把十几个小表的数据统计成一个总表的办法如下:1、打开excel表单...
  • EXCEL函数公式集

    热门讨论 2010-03-16 03:26:38
    excel常用函数公式及技巧搜集 从身份证号码中提取出生年月日 从身份证号码中提取出性别 从身份证号码中进行年龄判断 按身份证号号码计算至今天年龄 以2006年10月31日为基准日,按按身份证计算年龄(周岁)的公式 按...
  • Excel去重并进行统计(对列)

    千次阅读 2021-09-16 09:50:17
    1. 先做去重操作 首先,选中A列数据,然后单击功能区的数据选项卡——高级 注意【列表区域】和【复制到】两个选项框中的公式。 如果你统计的数据不在A列,或者数据不在2-38,要随机应变。 2. 去重后的结果 3. 紧...
  • 三种EXCEL去重统计方法

    万次阅读 2019-05-31 14:41:52
    数据去重统计是常用的一个功能,如果你还没有做过数据去重统计,就说明你离数据分析还很远。 好了,开始上干货。 方法一:先去重,再统计 先使用“删除重复项”功能按要统计的字段去重,如下图所示 然后再用...
  • excel实现筛选去重操作

    千次阅读 2019-05-27 15:01:00
    前情提要: 做图表时,希望更新数据后能自动化更新图表,需要各种公式之间相互配合。此时的需求是,将A表中的不同用户登录的地点做一个图表统计。 1、创建透视表 以用户id和地点当做行标签制作透视表,透视表选择...
  • 选择数据 - 高级 将A列的重复数据,去重,复制到J列,(这里的Data 5 是Sheet页名称)
  • 本文提供两种从数组中删除重复项的方法(自定义函数)供同学参考!
  • excel对比两边数据去重

    千次阅读 2020-09-29 15:52:12
    需求:筛选重复数据,A列是1000条数据,C列是100条数据,删除重复的数据,只剩900条。 首先,A列数据要分列,因为数据格式不一样,会导致后面的问题,一直下一步到完成 第二步,在B和D列加数字1,相当于索引,...
  • C81=IF(A81<>A80,MAX(C$80:C80)+1,C80)向下bai复制 G81=IFERROR(VLOOKUP(ROW(A1),IF({1,0},$C$81:$C$90,$A$81:$A$90),2,0),"")向下复制 H81=IF(G83="","",MAX(IF($C$81:$C$90=ROW(A1),$B$81:$B$90)))
  • excel中实现多条件去重计数

    千次阅读 2021-12-23 17:57:38
    这次我们来介绍怎么在excel中实现多条件去重计数。...如图,先用FILTER查询在A2:A11中符合在B2:B11中等于B2的单元格,C2:C11中等于C3的单元格,接着用UNIQUE函数去重,最后用COUNTA函数计算符合条件的单元格。 ...
  • 数据出现重复值是我们日常工作中经常会遇到的问题,例如下面这个EXCEL表格里,订单编号这一列里出现了多个重复值,这个时候我们就要对数据进行去重了。去重的方法有很多,这里介绍两种方法:1、通过EXCEL去重;2、...
  • 原标题:【Excel VBA】使用字典快速对数据去重数据去重复一直是数据整理过程中常见的问题之一,Excel解决方法有【删除重复项】、【高级筛选】、【数据透视表】、SQL语句、VBA的字典和集合等等……各有所长也各有所短...
  • 介绍一个用Python对Excel表格数据进行去重、分类、异常处理及分析的实例。

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 4,388
精华内容 1,755
关键字:

excel函数去重

友情链接: okxbg8.zip