您的位置: 首页 > Office技巧 > excel技巧

数组公式提取区域中的重复值

时间:2013-12-14 整理:docExcel.net

[Excel如何查看公式明细]Excel如何查看公式明细?excel表格中的数据是公式算出来的,想要显示算出数据的公式,该怎么显示呢?下面我们就来看看Excel表格显示公式的技巧...+阅读

有时需要要提取多行多列区域中的重复数据到某列,如下图所示,A2:C11包含一些重复的水果名称(已用深红色进行了标记),要将重复的名称提取到E列。

提取区域中的重复值示例

在E2中输入数组公式:

=INDIRECT(TEXT(MIN(IF((COUNTIF($A$2:$C$11,$A$2:$C$11)>1)*(COUNTIF($E$1:E1,$A$2:$C$11)=0),ROW($2:$11)*100+COLUMN($A:$C),7^8)),"R0C00"),)&""

公式输入完毕按Ctrl+Shift+Enter结束,然后拖动填充柄向下填充,直到公式结果返回空为止。

数组公式返回区域中的重复值

说明:

①INDIRECT函数的第二个参数为“0”(未输入即为0),表示返回R1C1样式的引用。

②TEXT函数的第二个参数为“R0C00”,将第一个参数的数值返回为R1C1样式的文本。受该参数的限制,包含重复值的区域需在CV列(第100列)的左侧,否则公式可能不会返回正确的结果。

③TEXT函数的第一个参数,以E3单元格中的公式为例说明如下。IF函数返回一个数组,本例为2行10列。当区域中的重复数据未出现在E3单元格上方的区域中时,数组元素为区域中相应单元格行号和列数所构成数值,即“行号×100+列数”;对于不重复的数据,或者虽重复但已出现在E3单元格的上方,数组元素为“7^8”(5764801),受“R0C00”格式的规定,其前5位为行数,后两位为列数。表示如果出现这种情况,将返回R57648C01(即A57648)单元格的值,通常为空。MIN函数返回上述数组的最小值,如E3单元格为“203”,此时TEXT函数返回“R2C03”,INDIRECT函数返回“橘子”。

④公式中的“$A$2:$C$11”、“$2:$11”、“$A:$C”和“$E$1:E1”都需根据实际进行修改。

另外,当区域中只包含数值时,还可用下面的数组公式按从大到小的顺序提取重复数值:

区域中只包含数值时返回重复数

首先在E1单元格中输入某个文本,如本例中的“重复值”字样。然后在E2单元格中输入数组公式:

=LARGE(IF(COUNTIF($A$2:$C$11,$A$2:$C$11)>1,$A$2:$C$11),SUM(COUNTIF($A$2:$C$11,$E$1:E1))+1)

公式输入完毕按Ctrl+Shift+Enter结束,然后拖动填充柄向下填充,直到公式结果返“#NUM!”为止。

本文地址:https://www.docexcel.net/show/3_3835.html

以下为关联文档:

excel如何快速提取手机号后4位数字有时在表格数据中需要提取手机号后4位数字另作他用,如何快速提取呢?一起来了解一下吧...

excel公式不会自动计算怎么办有时会发现明明公式相关的数值已经变化,按理说公式的结果也应该自动计算变化,可实际上没变化,这是怎么回事呢?一起来了解一下吧...

excel怎么找出被公式引用的单元格excel表格不知道数据是否被公式引用了,要想知道数据是否被公式引用了怎么办?一起来了解一下吧...

Word怎么设置段落公式和文字水平对齐不少用户在使用Word文档编排文字的时候,经常会在自己的Word文档中插入一些公式,那么Word怎么让段落的公式和文字水平对齐呢?一起来了解一下吧...

Excel怎么提取单元格中的数字怎么一键提取excel单元格内的数字呢?单元格内有数字还有文字,一个个编辑太影响工作效率,那么Excel怎么提取单元格中的数字?这篇文章主要介绍了Excel提取单元格中部分数字的方法,...

怎么提取Excel单元格中的部分内容文字放在一个单元格了,怎么批量提取Excel单元格中的部分内容呢?这篇文章主要介绍了Excel批量提取单元格中部分内容的方法,需要的朋友可以参考下...

excel函数公式大全 excel最常用的八个函数的使用方法这篇文章主要介绍了excel函数公式大全 excel最常用的八个函数的使用方法的相关资料,需要的朋友可以参考下本文详细内容介绍...

excel锁定公式怎么输入 excel绝对值符号输入教程在excel表格编辑中,“$”符号是经常用到的一个,可以锁定公式不被修改,可能还是有很多用户不清楚excel锁定公式$怎么输入,下面小编带来excel绝对值符号输入教程,希望对大家有所帮...

excel如何设置单元格显示公式不显示结果公式是工作表中非常常用的内容了。有时候,如果需要在工作表里面显示公式,而非公式计算的值,也是可以进行操作的...

相关推荐: