excel函数获取单元格区域中移除空单元格后的值
时间:2022-07-15 18:28:39
excel函数获取单元格区域中移除空单元格后的值,如下图1所示,列C中有很多空单元格,使用公式将其整理,移除空单元格并将值放置在列D中。
图1
自已动手试一试。
公式
在单元格D2中,输入数组公式:
=IFERROR(OFFSET($C$2,SMALL(IF((List)>1,ROW(List),””),ROW(E1))-2,0),””)
下拉至单元格出现空为止。
公式中的List为名称,引用列C中的单元格区域C2:C10000。
公式解析
主要看公式中的:
OFFSET($C$2,SMALL(IF((List)>1,ROW(List),””),ROW(E1))-2,0)
OFFSET函数的语法为:
OFFSET(reference, rows, cols, [height],[width])
与公式相对应:
参数reference的值为:$C$2
参数rows的值为:SMALL(IF((List)>1,ROW(List),””),ROW(E1))-2
参数cols的值为:0
也就是说,OFFSET函数返回以单元格C2为起点向下SMALL(IF((List)>1,ROW(List),””),ROW(E1))-2行的单元格区域。
重点看看:
SMALL(IF((List)>1,ROW(List),””),ROW(E1))-2
我们要从IF((List)>1,ROW(List),””)中获取第ROW(E1)小的值,而ROW(E1)=1,即最小的值。
对于IF((List)>1,ROW(List),””)来说,因为List是一个9999行1列的区域,所以
(List)>1
解析为由TRUE/FALSE值组成的9999行1列的数组。列C中单元格的值大于1,返回TRUE,否则返回FALSE,在工作表中演示如下图2所示。
图2
而ROW(List)则是由第2行至第10000行的行号组成的数组。
那么,IF语句生成的数组由列C中含有值对应的行号和空组成,即:
{2;””;””;””;6;7;8;””;””;””;””;13;14;15;…}
相应地在工作表中演示如下图3所示。
图3
将上述生成的数组代入SMALL函数中:
SMALL(IF((List)>1,ROW(List),””),ROW(E1))
即:
SMALL({2;””;””;””;6;7;8;””;””;””;””;13;14;15;…},1)
得到:
2
将公式向下拉时,ROW(E1)将自动更新为ROW(E2)、ROW(E3)、…等,即返回值2、3、…,获取数组中第2小的值、第3小的值、…
相应地在工作表中演示如下图4所示。
图4
这样,在单元格D2中的公式最终等价为:
OFFSET($C$2, 2-2,0)
得到单元格C2中的值。
在单元格D3中的公式最终等价为:
OFFSET($C$2, 6-2,0)
得到单元格C6中的值。
在单元格D4中的公式最终等价为:
OFFSET($C$2, 7-2,0)
得到单元格C7中的值。
依此类推。
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
如何解决MathType兼容Office 2016
![](https://img.aspxhome.com/file/2023/6/16896_0s.jpg)
怎样在word中输入三 级钢筋符号
![](https://img.aspxhome.com/file/2023/2/20652_0s.png)
如何在word文档中插入用户保存的图片
![](https://img.aspxhome.com/file/2023/6/32996_0s.jpg)
如何在word中给文件添加密码
![](https://img.aspxhome.com/file/2023/3/33103_0s.jpg)
如何用WPS表格作柏拉图(主次因素排列图)?
![](https://img.aspxhome.com/file/2023/0/a163500_0s.png)
如何冻结excel表格行和列?(图文)
![](https://img.aspxhome.com/file/2023/9/38189_0s.jpg)
Win10明明是管理员却没有权限怎么办?Win10是管理员还要权限
![](https://img.aspxhome.com/file/2023/7/51517_0s.png)
Word文档批注的插入、修改与删除方法
Win10开机提示Invalid Partition Table怎么办?
![](https://img.aspxhome.com/file/2023/3/50593_0s.png)
快速为Excel工作簿创建工作表目录的方法
Word2007如何并排比较文档
![](https://img.aspxhome.com/file/2023/8/18108_0s.jpg)
win10显示我的电脑1001无标题解决方法?win10我的电脑1001无标题怎么办?
![](https://img.aspxhome.com/file/2023/2/47282_0s.jpg)
Excel中怎样自动计算多个单元格的行数
![](https://img.aspxhome.com/file/2023/1/a140771_0s.jpg)
怎样在Word2013中删除自动更正内容
![](https://img.aspxhome.com/file/2023/6/20966_0s.jpg)
在Word中表头要重复该怎么办?
![](https://img.aspxhome.com/file/2023/4/24964_0s.png)
win10桌面图标消失桌面变黑了怎么办?win10桌面图标消失桌面变黑解决方法
![](https://img.aspxhome.com/file/2023/6/47806_0s.png)
Word2003中隐含模块编译错误
![](https://img.aspxhome.com/file/2023/6/21166_0s.jpg)
Excel结合Word实现表格格式转换
![](https://img.aspxhome.com/file/2023/5/20135_0s.jpg)
如何复制Word2007表格中的内容,粘贴时去掉表格?
Excel表格怎么实现文本之间用逗号隔开?
![](https://img.aspxhome.com/file/2023/3/a141483_0s.jpg)