这篇文章继续介绍一下OFFSET函数在获取动态区域当中的应用。
首先看一下案例:
将一列数据,转化为N行N列。
首先对于确定的需求来说公式较为简单,例如转换为5行3列。
公式如下:=OFFSET($A$1,ROW(A1)*3+COLUMN(A1)-4,)
公式原理:
我们需要在指定位置中使用offset函数做定向的偏移来完成多行多列的操作。
根据行数公式ROW以及列数公式COLUMN,找到向下偏移的规律。
规律如下:
首个单元格即A1单元格的行数(1)*需要划分的列数(3)+A1单元格的列数(1)-需要划分的列数(3)-1,并向整个区域进行拖动即可完成该操作。
下面是使用该公式计算出的偏移量
1*3+1-3-1=0 1*3+2-3-1=1 1*3+3-3-1=2
2*3+1-3-1=3 2*3+2-3-1=4 2*3+3-3-1=5
。。。 。。。 。。。
。。。 。。。 。。。
。。。 。。。 。。。
OFFSET函数根据生成的偏移量就可以将数据进行分行分列的操作了。
那么我们既然了解了这个规律,我们是否可以修改行数以及列数,来动态的进行分行分列的操作呢?
答案是可以的,我们看一下公式:
=IF(OR(COLUMN(D5)-COLUMN($C$5)>$E$3,ROW(D5)-ROW($D$4)>$E$2),"",OFFSET($A$1,ROW(A1)*$E$3+COLUMN(A1)-$E$3-1,))
原理就是根据行列参数,将超过行列的单元格都置为空值,然后使用OFFSET函数,按照之前找到的规律,将行列参数带入就可以达到,随意修改行列参数,进行自动分行分列的操作了。