Excel中OFFSET函数(一) |
您所在的位置:网站首页 › excel中如何进行转置 › Excel中OFFSET函数(一) |
1.OFFSET函数
Offset是Excel中的函数,在Excel中,OFFSET函数的功能为以指定的引用为参照系,通过给定偏移量得到新的引用。返回的引用可以为一个单元格或单元格区域。并可以指定返回的行数或列数。Reference 作为偏移量参照系的引用区域。Reference 必须为对单元格或相连单元格区域的引用;否则,函数 OFFSET 返回错误值#VALUE!。 函数语法 OFFSET(reference,rows,cols,height,width) Reference 作为偏移量参照系的引用区域。Reference 必须为对单元格或相连单元格区域的引用;否则,函数 OFFSET 返回错误值#VALUE!。 Rows相对于偏移量参照系的左上角单元格,上(下)偏移的行数。行数可为正数(代表在起始引用的下方)或负数(代表在起始引用的上方)。 Cols 相对于偏移量参照系的左上角单元格,左(右)偏移的列数列数可为正数(代表在起始引用的右边)或负数(代表在起始引用的左边)。 Height高度,即所要返回的引用区域的行数。Height 可以为负,-x表示当前行向上的x行。 Width宽度,即所要返回的引用区域的列数。Width 可以为负,-x表示当前行向左的x行。 以上摘自百度百科,下面才是重点简单来说,Offset功能可以理解为:“寻找指定位置”并返回该位置的值。(可以看作在玩飞行棋) 其中的参数reference —— 该次“寻找”的“起点” rows —— 向下寻找的格数(值为负表示向上) cols —— 向右寻找的格数(值为负表示向左) height与width表示寻找的终点的大小(是一块区域(height行 1. 寻找单元格 目的:让E8单元格显示北京市在19年5月的CPI,即B4中的值 函数:E8中使用函数——Offset(A1,3,1); 过程:以A1为起点,先向下走3格到达A4,再向右走1格到达目的地B4。返回B4的值,E8被赋值为101.8. 拓展:在下拉,右拉之后,我们发现——在这种情况下,使用“E8=offset(A1,3,1)”与使用"E8=B4"的效果是一样的(实现了矩阵的复制),那么我们使用这个函数的必要性在哪呢? 所以,我如果再提出一个问题:让这个矩阵实现如下图所示的转置(此时"E8=B4"就不管用了)对于这个问题,我们分2步来进行分析求解 在进行下拉操作时,如何将起点右边的数据放在终点的下方?如图中如何将北京,天津,河北竖着放?在说明这个操作之前,我们先了解——当我们进行所谓的“下拉”,“右拉”操作时,对于新增单元格来说,它们的函数是怎样变化的。 从上图中可以发现——“下拉”时,offset函数中的参数中,对于单个的数值(如3,1)是不变的,而对于坐标(字母与数字的组合,如A1)—— 下拉时,坐标的行标(A1中“1”→“2”)增加,列标不变;右拉时,坐标的列数(A1中"A"→"B")增加,行标不变。
2. 在了解了“下拉”的变化后,我们再来看所要达到的目的——A1右方数据放于A1下方位置。对此,我们可以让下拉时使得“行的变化”转化成“列的变化”,即使得“E8的‘下’一个新增单元格” 对应 “A1的‘右’一个单元格”。 要完成上述操作:首先我们需要“固定起点”。因此我们使用“$”符号,其表示绝对引用,当进行上下拉或左右拉时,$后的字母或数字保持不变。因此offset的第一个参数(起点坐标)固定为A1————Offset($A$1,...); 那再来看看我们的目的:“行转竖” 相当于——当下拉使得函数中的行标增加(1→2→...)时,函数中的offset中的单次右移量cols随之增加。 因此,我们使用ROW()函数来反应列标的变化,并将它放在offset的cols位置来让cols随之增加。 即Offset($A$1,0,row(A1)-1)。 【还不懂的看这里: 其次,我们需要:每次E8向下拉一下,E9数值来源(E9中offset的终点)相对于E8数值来源(A1)来说,向下移动0格,向左移动一格。所以需要在E8的函数中便表明,一旦下拉(某一坐标的行标变化)就要同时使得offset中右移的格数加一(cols+1)。因此对于E8的Offset的第二,三个参数(下移,右移格数)————Offset($A$1,0,row(A1)-1); row()是一个函数,其功能为返回括号内左边的行数,例如row(A4) = 4; row(A2) = 2; row(B5) = 5;使用该函数的作用就是 当我们下拉时A1中的1会递增,我们借用这个递增的数,实现cols+1的目的。】 最后,对于Offset后两个参数,由于我们移动的是单元格,所以不需要给值,其默认为1,1。效果如下: 有了上方“横转竖”的基础,我们再来看“竖转行”的函数。首先我们尝试就Offset($A$1,0,row(A1)-1)函数向右拉,看看会出现什么情况。
那再来看看我们的目的:“竖转行” 相当于——当右拉使得函数中的列标增加(A→B→...)时,函数中的offset中的单次下移量rows随之增加。 因此,我们使用COLUMN()函数来反应列标的变化,并将它放在offset的rows位置来让rows随之增加。 即Offset($A$1,COLUMN(A1)-1,row(A1)-1)。 实现转置:Offset($A$1,COLUMN(A1)-1,row(A1)-1) |
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |