ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

搜索
EH技术汇-专业的职场技能充电站 妙哉!函数段子手趣味讲函数 Excel服务器-会Excel,做管理系统 Excel Home精品图文教程库
HR薪酬管理数字化实战 Excel 2021函数公式学习大典 Excel数据透视表实战秘技 打造核心竞争力的职场宝典
300集Office 2010微视频教程 数据工作者的案头书 免费直播课集锦 ExcelHome出品 - VBA代码宝免费下载
用ChatGPT与VBA一键搞定Excel WPS表格从入门到精通 Excel VBA经典代码实践指南
查看: 62999|回复: 47

[讨论] excel提取不重复值(重复的保留一个)

  [复制链接]

TA的精华主题

TA的得分主题

发表于 2013-10-18 18:10 | 显示全部楼层 |阅读模式
本帖最后由 dengjq2000 于 2013-10-18 18:48 编辑

       一、INDEX—MATCH=ROW 法=INDEX(A:A,SMALL(IF(MATCH($A$2:$A$11,$A$2:$A$11,0)=ROW($A$2:$A$11)-1,ROW ($2:$11),4^8),ROW(1:1)))&""
分析:
1、对 A 列中的每一项利用 MATCH 函数找出其在 A 列中首次出现的位置,返回相应的行 号。超出列表范围时将返回一个较大的行号,如 65536。
2、利用 SMALL 函数对这些行号数据由小到大排序。
3、利用 INDEX 函数返回与查找到的行号相应的 A 列的值。
4、用 A 列的值与””连接作为最终的值,当行号足够大时,对应的 A 列单元格为空单元格, 空单元格与""连接,依然是空单元格。
最终结果,从 A 列筛选出的没有重复数据的单元格排在表格的上半部分,下半部分为空单 元格,正是我们想要的效果。

       二、COUNTIF 法=INDEX(A:A,MIN(IF(COUNTIF($C$1:C1,$A$2:$A$160)=0,ROW($A$2:$A$160),4^8)))&""(C2开始输入公式)这种解法看着有点怪异,一般人是想不到的,除非你是二般人。说它怪,怪在哪儿呢?怪就 怪在它的着眼点让人匪夷所思:
1、要查找 A 列中不重复的数据,一般人都会首先考虑怎样处理 A 列中的数据,其它无关的 或空白区域是不会上眼的。这种解法的独到之处就是首先在一空白列处查找 A 列中的数据, 如果找不到就返回该数据所在的行号,否则返回一个较大的值 65536。
2、用 MIN 函数据找到 A 列中的数据首次出现的行号。
3、用 INDEX 函数在空白列处返回对应的 A 列中的值。
      
       三、FREQUENCY 法(仅适用于数值)=INDEX(A:A,SMALL(IF(FREQUENCY($A$2:$A$11,$A$2:$A$11),ROW($A$2:$A$11),4^8), ROW(1:1)))&""
1、用 FREQUENCY 函数判断 A 列中的每一个数据是否在 A 列中出现一次,如果仅出现一 次就返回当前行号,否则返回一个较大的数 65536。
2、用 SMALL 函数对返回的行号从由小到大排序。
3、用 INDEX 函数返回对应的 A 列中的值。

       四、COUNTIF 法=IF(COUNTIF($A$2:A2,A2)>1,"",A2)
1、利用函数 COUNTIF 统计 A 列中的数据是否大于 1,如果大于 1,返回空值,否则返回 当前值。
2、对生成的数据排序以清除空白单元格。   
   
       五、其它方法。
公式一:=SUM(1/countif(A1:A10,A1:A10)) 按 Ctrl+Shift+回车键结束
公式二:=COUNT(0/(MATCH(A1:A10,A1:A10)=ROW(1:10))) 按 Ctrl+Shift+回车键结束
公式三:=SUMPRODUCT(1/COUNTIF(A1:A10,A1:A10))
公式四:=COUNT(0/(COUNTIF(A1:A10,A1:A10)=1)) 按 Ctrl+Shift+回车键结束
公式五:=SUM(N(COUNTIF(A1:A10,A1:A10)=1)) 按 Ctrl+Shift+回车键结束
公式六:=SUM(INT(1/COUNTIF(A1:A10,A1:A10)))按 Ctrl+Shift+回车键结束

评分

2

查看全部评分

TA的精华主题

TA的得分主题

发表于 2013-10-18 18:33 | 显示全部楼层
建议楼主上个附件,以便测试交流。

TA的精华主题

TA的得分主题

发表于 2013-10-18 18:36 | 显示全部楼层
本帖最后由 笑着...两年过去 于 2013-10-18 18:50 编辑

有没有试过offset咧?
见二楼附件说明,http://club.excelhome.net/thread-1030093-1-1.html
33楼有经典用法

TA的精华主题

TA的得分主题

发表于 2015-6-8 00:10 | 显示全部楼层
笑着...两年过去 发表于 2013-10-18 18:36
有没有试过offset咧?
见二楼附件说明,http://club.excelhome.net/thread-1030093-1-1.html
33楼有经典 ...

是不是说错楼层了,33楼没有公式用法

TA的精华主题

TA的得分主题

发表于 2015-10-30 23:23 | 显示全部楼层
我想问下要是数据分散在不同的sheet里面用公式怎么写?

TA的精华主题

TA的得分主题

发表于 2016-3-3 20:12 | 显示全部楼层
笑着...两年过去 发表于 2013-10-18 18:36
有没有试过offset咧?
见二楼附件说明,http://club.excelhome.net/thread-1030093-1-1.html
33楼有经典 ...

好像是40列的说,

TA的精华主题

TA的得分主题

发表于 2016-3-4 09:06 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2017-3-26 23:08 | 显示全部楼层
大神,我怎么按照原样抄下来在excel测试,往下拖拽,结果出了第一个是正确显示,其他都是错误址啊

TA的精华主题

TA的得分主题

发表于 2017-5-20 15:00 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
=INDEX(A:A,MIN(IF(COUNTIF($C$1:C1,$A$2:$A$160)=0,ROW($A$2:$A$160),4^8)))&""
这个公式用了5个函数,
=lookup(1,0/(COUNTIF($C$1:C1,$A$2:$A$160)=0),$A$2:$A$160)
它用了2个函数,公式最短,
=INDEX($A$2:$A$160,MATCH(0,COUNTIF($C$1:C1,$A$2:$A$160),0))&""
它用了3个函数。
LOOKUP不愧为为我们EXCEL HOME所称的查询之王!道经:顺为凡,逆则仙,只在中间巅倒巅。LOOKUP的查询结果顺序正好与源数据是巅倒的!外星人马云也是用巅倒的方法洞察这个世界的!

评分

1

查看全部评分

TA的精华主题

TA的得分主题

发表于 2017-7-14 08:30 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
先收藏了,到时一个一个测试看哪个更优
您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

手机版|关于我们|联系我们|ExcelHome

GMT+8, 2024-3-29 17:45 , Processed in 0.059942 second(s), 10 queries , Gzip On, Redis On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

沪公网安备 31011702000001号 沪ICP备11019229号-2

本论坛言论纯属发表者个人意见,任何违反国家相关法律的言论,本站将协助国家相关部门追究发言者责任!     本站特聘法律顾问:李志群律师

快速回复 返回顶部 返回列表