一个公式搞定空行的动态插入
作者:ITFANS来源:部落窝教育发布时间:2024-12-25 17:45:20点击:307
编按:
前面曾有多篇教程介绍插入空行,包括利用序列排序插入空行,利用定位常量插入空行,利用序列加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:
相关推荐:
版权申明:
本文作者ITFANS;部落窝教育享有稿件专有使用权。若需转载请联系部落窝教育。