admin管理员组文章数量:1530034
最近遇到一个问题,如图所示,每个表的字段分别一列展示,不同表有公共字段,也有独有的字段,现想统计这些表一共涉及到哪些字段。基本思路就是将这些表的字段合并为一列再去重。因为涉及到70多列,复制粘贴比较耗时,于是想到用Excel中的OFFSET函数解决。
OFFSET函数的功能是以指定的引用为参考系,通过给定偏移量得到新的引用。返回的引用可以是任何一个单元格,也可以是单元格区域,还可以指定返回的行数或列数。其格式为:
OFFSET(reference,rows,cols,height,width)
- 参数reference是一个引用区域,作为偏移量的参照系,它必须是对单元格或相连单元格区域的引用,否则函数将返回错误值“#VALUE!”
- 参数rows表示相对于reference参照系偏移的行数,若为正数表示在起始引用的下方,若为负数表示在起始引用的上方;
- 参数cols表示相对于reference参照系偏移的列数,若为正数表示在起始引用的右方,若为负数表示在起始引用的左方;
- 参数height表示高度,即要返回的目标引用区域的行数,它必须是正数;
- 参数width表示宽度,即要返回的目标引用区域的列数,它必须是正数。
注意如果省略了height或width,则认为其高度或宽度与reference相同;此函数实际上并不移动任何单元格或更改选定区域,它只是返回一个引用,它可用于任何需要将引用作为参数的函数。
例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列数据。
- Step2:因为每一列的长度不同,所以对于没有数据根据公式自动取零了。复制D列的数据粘贴数值到B列,在此利用查找/替换功能把零去掉,即将零替换为空值。
- Step3:利用数据–删除重复项功能进行去重,得到最终效果。
ps:初衷是通过撰写博文记录自己所学所用,实现知识的梳理与积累;将其分享,希望能够帮到面临同样困惑的小伙伴儿。如发现博文中存在问题,欢迎随时交流~~
版权声明:本文标题:Excel函数——OFFSET函数将多列合并成一列 内容由热心网友自发贡献,该文观点仅代表作者本人, 转载请联系作者并注明出处:https://m.elefans.com/xitong/1726453169a1071530.html, 本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容,一经查实,本站将立刻删除。
发表评论