Excel竖向结构转横向结构，OFFSET函数妙用

用微信扫码二维码

分享至好友和朋友圈

fduihhhjkl之前给大家分享了竖向结构转横向结构的5种方法，就是把表一样式转换为表二样式，具体的文章可参阅如下：

3秒钟搞定Excel数据竖向结构转横向结构，这5种方法你要掌握

那如果我们想把表二结构数据转换为表一结构样式的要怎么做，就比如如下这样的？

今天就给大家分享下这种数据表转换怎么实现。

1、首选我们要先确定表二的姓名列，在表一要实现多少行。

我们从表二可以看出，每个姓名有3个参数，即可以确定表一的行数为：姓名数*参数数。

2、确认下B4:B11的姓名内容在H列什么时候实现第二次重复，比如第一次到"木木"了，那我下一个人名应该返回B4的内容"赵云"，这个要怎么做？

这是可以考虑用MOD函数，比如MOD(ROW(1:1),9)，即可以实现每9个一循环了。

循环重复知道了，那要怎么把表二B列对应的人名写到H列呢，OFFSET函数即可以实现？

OFFSET(原始点,偏移的行数,偏移的列数,引用的行数,引用的列数)

我们就可以以B4为原始点，偏移的行数使用MOD函数实现，偏移列数为空。

3、下面只要在H3输入如下公式：

=IF(ROW(1:1)>(COUNTA($B$4:$B$21))*(COUNTA($C$3:$E$3)),"",OFFSET(B$4,MOD(ROW(1:1)-1,COUNTA($B$4:$B$21)),))

这里用到了IF函数进判定，当行数大于数据区域行数的时候，显示为空值，否则以OFFSET函数的值显示

计数数据区域取的是B4:B21,代表我们可以在B11之后再增加姓名后，数据依然可以更新。

4、内容参数列可参考姓名列的方法，使用OFFSET函数，不过此时是列偏移，而不是行偏移了，偏移的条件是每经过8行（事例的姓名行数），向右偏移一列。

这时再使用MOD函数就不行了，我们可以直接使用除法。

在I3输入公式：

=IF($H3="","",OFFSET($B$3,,(INT(ROW(1:1)-1)/(COUNTA($B$4:$B$21)))+1))

同样增加IF函数判断，判断的条件是姓名列是否存在，存在即进行偏移，否则为空。

5、输出结果查询，就相比较简单了，我们已经知道了姓名、参数，就可以使用交叉查询进行实现了，可以在J2输入公式：

=IF(H3="","",SUMPRODUCT((H3=$B$4:$B$11)*(V3=$C$3:$E$3)*$C$4:$E$11))

特别声明：以上内容(如有图片或视频亦包括在内)为自媒体平台“网易号”用户上传并发布，本平台仅提供信息存储服务。

Notice: The content above (including the pictures and videos if any) is uploaded and posted by a user of NetEase Hao, which is a social media platform and only provides information storage services.