送人玫瑰,手有余香,请将文章分享给更多朋友
动手操作是熟练掌握EXCEL的最快捷途径!
【置顶公众号】或者【设为星标】及时接收更新不迷路
小伙伴们好,今天要和大家介绍一个关于自定义序列方面的小技巧。通过本篇的学习,我们可以通过几个简单的小函数就构造出我们需要的序列来。
题目是这样的:
根据左侧的源数据,用公式构造出右侧的数据来。题目看起来很简单,但如果没有掌握构建序列的技巧,这个题目还是有一定难度的。
先贴一位大神写的公式吧。
首先选中单元格区域E1:H1,输入公式“=TRANSPOSE($A$1:$A$4)&INDEX(TRANSPOSE($B$1:$B$3&TRANSPOSE($C$1:$C$5)),MOD(ROW(A5),5)+1,INT(ROW(A5)/5))”,三键回车并向下拖曳即可。
由于这个方法不是我们今天的重点,因此公式就不过多介绍了。有兴趣的朋友们可以自己试着拆解这个公式。如有任何问题可以私信我哦!
如果大家还记得前两天我发的另外一个帖子这个技巧非常深奥,据说只有不到3.5%的人能够熟练应用。一旦掌握则终身受用!,那么就一定会对下面我要着重介绍的这个技巧还有一定的印象。如果已经忘记了,就赶紧去看看吧!
先来看A列。A列中有4个数字,在最终结果中每个数字各占一列,个数是B、C两列的数量乘积。
这个很简单,不需要过多介绍。
下面来看看B列。现在需要把B列中每个字母重复5次。
在单元格E1中输入公式“=INDEX($A$1:$A$4,COLUMN()-4)&INDEX($B$1:$B$3,(ROW(A1)-1)/5+1)”,并向右向下拖曳即可。
这里使用了(ROW(A1)-1)/5+1这个技巧,配合INT函数INT((ROW(A1)-1)/5)+1就可以创建{1;1;1;1;1;2;2;2;2;2;3;3;3;3;3}这样一个序列。但由于本例中是和INDEX函数配合使用,由于INDEX函数忽略小数的特性,这里只要写成(ROW(A1)-1)/5+1这种形式就可以了,不必在外侧套用INT函数。
来解释一下这个公式。
当前行为第一行时ROW(A1)的值是1,ROW(A1)-1的值是0,结果除以5后仍然是0,INT函数取整后为0,再加上1后得到1
随着公式向下拖曵,到第二行,ROW(A2)-1的值就是1了,除以5后的值是0.2,INT函数取整后仍然为0,加上1后的结果仍然是1
来到第六行时,ROW(A6)-1的结果是5,除以5后得1,INT函数取整后得1,公式的结果就是2了
第十一行时,公式的结果就是3了
依此类推
这里第二个INDEX函数可以写成“=INDEX($B$1:$B$3,ROW(A5)/5)”。大家动手试一下看看这两个写法有什么异同点。
最后我们来看看C列。简单说,就是要把C列按照1-5的序列重复3次。
在单元格E1中输入公式“=INDEX($A$1:$A$4,COLUMN()-4)&INDEX($B$1:$B$3,(ROW(A1)-1)/5+1)&INDEX($C$1:$C$5,MOD(ROW(A1)-1,5)+1)”,并向右向下拖曳即可。
这里使用了MOD(ROW(A1)-1,5)+1这样的技巧创建了{1;2;3;4;5;1;2;3;4;5;1;2;3;4;5}这样的序列。
这个公式的思路和上面介绍的是类似的。以5为一个循环,分别对0-4,5-9,10-14按5来求余数,结果是0、1、2、3、4这5个数。加上1以后就是1-5这个自然数序列了。
从上面的例子中可以看到,利用MOD、ROW、INT等几个简单的小函数,就可以创建出我们需要的序列来,从而帮助我们进一步完成最终目标。这些使用的技巧,朋友们都记下了吗?
-END-
长按下方二维码关注EXCEL应用之家
面对EXCEL操作问题时不再迷茫无助
我就知道你“在看”
联系客服