Excel函数 | 您所在的位置:网站首页 › excel多行数据合并成一行数据透视表怎么弄 › Excel函数 |
最近遇到一个问题,如图所示,每个表的字段分别一列展示,不同表有公共字段,也有独有的字段,现想统计这些表一共涉及到哪些字段。基本思路就是将这些表的字段合并为一列再去重。因为涉及到70多列,复制粘贴比较耗时,于是想到用Excel中的OFFSET函数解决。 例1:获取单元格,如图在F2单元格输入公式OFFSET(A1,2,1,1,1),表示以A1单元格为参考向下移动2行、向右移动1列,获得单个单元格。输入公式按“Enter”即可得到图中所示结果。 例2:获取单元格区域,如图选中G2:H4单元格区域输入公式OFFSET(A1,1,1,3,2),表示以A1单元格为参考向下移动1行、向右移动1列,获得3行2列的单元格区域。因为获得的是区域,需要输入完公式后按“Ctrl+Shift+Enter”,即得到图中所示结果。 例3:将表1的数据转成表2形式,即数值列为各指标数值的依次追加。为了实现拖动鼠标复制公式且保证得到正确的结果,结合了绝对引用$E$2、ROW()、MOD()、INT()等,其中 $E$2表示在E列公式中都是以E2为参考系的ROW()是获取当前单元格所在行,MOD()是取余,INT()是取整,三个函数结合实现动态计算偏移量。因为表1中每列数值为9个,因此MOD()、INT()中均除以9。首先,E3单元格的公式 OFFSET($E$2,MOD(ROW(E3)-3,9)+1,INT((ROW(E3)-3)/9)+3) =OFFSET($E$2,MOD(3-3,9)+1,INT((3-3)/9)+3) =OFFSET($E$2,0+1,0+3) =OFFSET($E$2,1,3) 表示以E2单元格为参考向下移动1行、向右移动3列,获得单个单元格,得到如图结果。 E16单元格的公式OFFSET($E$2,MOD(ROW(E16)-3,9)+1,INT((ROW(E16)-3)/9)+3) =OFFSET($E$2,MOD(16-3,9)+1,INT((16-3)/9)+3) =OFFSET($E$2,4+1,1+3) =OFFSET($E$2,5,4) 表示以E2单元格为参考向下移动5行、向右移动4列,获得单个单元格,得到如图结果。 通过示例基本掌握了OFFSET()的使用方法,下面利用OFFSET()解决开篇的问题。 Step1:在单元格D3输入公式OFFSET($D$3,MOD(ROW(D3)-3,21),INT((ROW(D3)-3)/21)+1,1,1),然后向下拖动鼠标即得到D列数据。![]() ![]() ![]() ![]() |
CopyRight 2018-2019 实验室设备网 版权所有 |