从今天开始我们学习函数,为了不让大家感觉到枯燥难懂,所以我们每次都以实例来讲解,实例中需要用到什么函数以及理论就学习什么,函数讲完如果有技巧性的东西也会补充在后面。

本节也是解决一个朋友的问题,护士在统计病床时需要把一列病床号转为一个规定几行几列的矩阵,由于不会函数,所以每次只能手工复制粘贴。

为了解决上面的问题,我们先来学习四个函数:

excel中如何把数据转换成矩阵(EXCEL函数列与矩阵互转)(1)

01

ROW([reference]),返回引用单元格所在的行,

例如:在A1单元格中输入=row(B1),则返回是1,意思就是B1是B列第一行,返回行号,所以是1。若=row(B1:B3),ctrl shift ctrl结束(以后章节里统一叫三键结束),则返回数组{1;2;3},因为引用区域为数组区域,所以返回的也是对应的数组区域。对数组不懂的同学不要纠结,我们后面会详细讲,本节先理解单个单元格的引用。

excel中如何把数据转换成矩阵(EXCEL函数列与矩阵互转)(2)

02

COLUMN([reference]),返回单元格所在的列,用法与row类似,不多解释了,大家自己琢磨。

excel中如何把数据转换成矩阵(EXCEL函数列与矩阵互转)(3)

03

OFFSET(reference, rows, cols, [height], [width]),返回对单元格或单元格区域中指定行数和列数的区域的引用。例如:在A1中输入=offset(A1,2,3,1,1),结果会显示D3单元格的内容,意思是以A1为参照,向下偏移两个单元格,向右偏移三个单元格,返回高度、宽度都为1的单元格(即1个单元格)。

excel中如何把数据转换成矩阵(EXCEL函数列与矩阵互转)(4)

04

Int( number ),将数字向下舍入到最接近的整数,例如:=int(1.7)则返回1。表是1.7向下取舍,取整数;=int(-1.7),则返回-2。

以上这四个函数其实也很简单对吧,就是返回单元格所在的行数、列数以及引用以一定条件进行偏移后的单元格,对数字向下取整。有了以上这四个武器,下面的问题就迎刃而解了。

1、列转矩阵

把下面示例中A列数字1-12转化成一个4×3(4行,3列)的矩阵。

excel中如何把数据转换成矩阵(EXCEL函数列与矩阵互转)(5)

我们首先想这就是个引用位置的问题呀,矩阵中的每个数字对应A列中的数字就可以,比如C2中引用的是A1,D2引用的是A2……即下面这样

excel中如何把数据转换成矩阵(EXCEL函数列与矩阵互转)(6)

引用的单元格相对A1都是向下偏移0,1,2,3……一直到11,向右偏移永远都是0,因为只有一列,所以offset函数第三个参数是0,最后两个不用输入,默认按照参照(A1)的高度、宽度返回,即一个单元格。所以我们把offset的第一个参数定为$A$1(绝对引用),只要能搞定第二个参数就可以了。

第二个参数我们希望向右拉的时候能够按照每拉一个单元格就增加1,向下拉的时候每拉一个单元格就增加3,这样的话只要在C2中输入=offset($A$1,第二个参数,0),向右向下拉满区域就OK了。

向右拉一个单元格增加1,列数也正好增加1列,所以正好用COLUMN来实现这个效果,比如在C2中输入=column(A1)-1,就会返回0,即1-1=0,向右拉,D3中=column(A2)-1,就会返回1,即2-1=1,以此类推……

但是向下拉呢,C3中公式变为了=column(b1)-1,返回还是0,我们希望返回的是3,即矩阵的列数,C4中我们希望返回的是6,即每下拉一行,我们希望返回的数字能在上一行的基础上增加3,换个思路就是表示在第一行的基础上增加3的倍数,所以我们用返回行的函数,在C2中输入=(row(A1)-1)*3,返回0,向下拉一格C3=(row(A2)-1)*3,返回3,……

利用column和row组合,即在C2中输入

=offset($A$1,column(A1)-1 row(A1)-1)*3,0)

向右向下拉满规定好的4×3的区域,看看效果!

当向右拉时,公式第一部分返回列数会对应增加1,而第二部分是返回行数,向右拉行数没变化,所以第二部分返回的数字是不变的,向下拉时红色第一部分列数没变,所以返回的数字是不变的,第二部分行数有变化,相应增加了3的倍数,两个结合,正好实现了右拉增加1,下拉增加3。

通过上面的分析,大家对列转矩阵能做到了吧,当然还有别公式可以实现,我们这节先讲这个三个公式。

2、矩阵转列

把下面黄色区域的矩阵转化为G列一列数字

excel中如何把数据转换成矩阵(EXCEL函数列与矩阵互转)(7)

思路:以A1为参照,转化成1列,offset第一个参数每向下拉8个单元格,需要增加1,第二个参数向右拉的变化是0到5。

G1中公式 =OFFSET($A$1,INT((ROW(A1)-1)/6),MOD((ROW(A1)-1),6))

思维方式和列转矩阵类似的,我这里只奉上公式,大家自己思考,分解公式,一步一步去理解并领会其中的意思,只有多思考,多琢磨,我们才能让函数成为我们的工具。不懂的同学可以在群里请教!记住学习函数一定要领会才能做到灵活,而不是简单的模仿。

转自:米宏Office

,