二维码 购物车
部落窝在线教育欢迎您!

一个公式搞定空行的动态插入

 

作者:ITFANS来源:部落窝教育发布时间:2024-12-25 17:45:20点击:307

分享到:
0
收藏    收藏人气:0人
版权说明: 原创作品,禁止转载。

编按:

前面曾有多篇教程介绍插入空行,包括利用序列排序插入空行,利用定位常量插入空行,利用序列加VLOOKUP函数插入空行等。本次介绍一种只需一个公式,不做任何辅助列或者多余操作即可插入空行的方法。该法适应性强,支持动态插入空行。

 

很多人都采用创建序列并对序列进行排序的方法插入空行,如下图。此法最大不足就是适应性差,数据增减后就需要重来;其次就是无法动态插入空行。

 

 

今天笔者分享用一个公式搞定空行插入的方法,不但可以适应数据的增减,还可以根据空行数、间隔数变化动态插入。

 

 

 

空行插入常见有两种情况:固定间隔插入固定空行、非固定间隔插入固定空行,下面分别介绍。

1.固定间隔插入固定空行

按固定间隔插入固定空行,可以理解为交替地引用原数据和原数据之外的空值。

“间隔3行插入两个空行”为例,只要生成类似“1;2;3;999;999;4;5;6;999;999……”的序列,然后使用INDEX或者CHOOSEROWS函数按序列引用原数据即可。

 

 

因此,用公式插入空行的关键就是生成引用的行号序列。

序列中的“1;2;3……”等用于引用原数据,可以由ROW或SEQUENCE函数生成。

序列中的“999”是常量,用于引用空值。注意,并非必须“999”,它可以是任何一个大于原数据个数的整数。需要插入几个空行,就需要几个999。

 

引用原数据的行号数:

=ROW(A1:A99) 或 = SEQUENCE(99)

引用空值的“999”:

空值只在能被3整除的行数之后,所以可以用余数等于0来确定“999”。

=IF(MOD(SEQUENCE(99),3)=0,999,NA())

需要的是两个999,所以重复一列。

 

 

上述三列数据可以用一个IF函数公式一并得到:

 

 

说明:这里运用了IF函数的{1,0}结构,不了解的伙伴可以戳此

 

接下来使用TOCOL函数将3列数据转换为1列:

 

 

如果要实现动态插入,可以将常量数组{1,0,0}用含空行数的数组公式EXPAND(1,,B2+1,0)取代,将求余数中的3用间隔数B3取代:

=TOCOL(IF(EXPAND(1,,B2+1,0), SEQUENCE(99),IF(MOD(SEQUENCE(99),B3)=0,999,NA())),3)

最后用INDEX或者CHOOSEROWS函数引用:

 

 

2.非固定间隔插入固定空行

间隔数不固定,则不能再用余数是否等于0来判断“999”的位置。

譬如下方按运动大类插入空行。

除第一个运动类外,应在每个含“运动”字符的单元格前插入空行。

 

 

“999”的确定:

=IF(RIGHT(A6:A104,2)="运动",999,NA())

最终:

 

 

再譬如在不同字段之间插入空行。

可以利用错开一位的单元格进行不等于判断,确定“999”的位置。

 



 

本文配套的练习课件请添加客服微信buluowojiaoyu索取。

Excel高手,快速提升工作效率,部落窝教育Excel精品好课任你选择

扫下方二维码关注公众号,可随时随地学习Excel:

相关推荐:

序列排序法插入空行

VLOOKUP函数插入空行

IF函数{1,0}结构原理

新函数TOCOL和TOROW

版权申明:

本文作者ITFANS;部落窝教育享有稿件专有使用权。若需转载请联系部落窝教育。