![374c43e69ec5b506aa9afb65222bb538.png](https://i-blog.csdnimg.cn/blog_migrate/cb32669e618b8cf7abfb139eaf5e57ae.jpeg)
写在前面:
之前分享过EXCEL中简单的单列数据转单行,或单行数据转单列(EXCEL行列互转三种方法 ),其中有一个方法用的就是OFFSET函数与ROW、COLUMN函数的嵌套。
今天运用OFFSET+ROW+COLUMN函数组合处理更多稍微复杂一点的数据。
1.一列转多列多行(或多行多列)
①先列后行,比如一列数据转为四列多行。
在目标区域第一个单元格B19输入以下公式,然后向右拉到第4列,再下拉直到出现空白。
=OFFSET($A$1,COLUMN(A1)-1+(ROW(A1)-1)*4,)&""
![4ca9874e4d470584c8573d8a43ddabdf.png](https://i-blog.csdnimg.cn/blog_migrate/e9bfb787028acbe50d4e8afea2b2339a.jpeg)
②先行后列,比如一列数据转为四行多列。
在单元格H19输入以下公式,然后向下拉到第4行,再右拉直到出现空白。
=OFFSET($A$1,ROW(A1)-1+COLUMN(A1)*4-4,)&""
2.一行转多列多行(或多行多列)
①先列后行,比如一行数据转为三列多行。
同样,此例在单元格B6输入以下公式,然后向右拉到第3列,再下拉直到出现空白。
=OFFSET($A$1,,(COLUMN(A1)-1)+(ROW(A1)-1)*3,)&""
![c20b426e31ed4b6be9fb2d55db450807.png](https://i-blog.csdnimg.cn/blog_migrate/ac2230f0e0df2512a9b44853cf32022f.jpeg)
②先行后列,比如一列数据转为三行多列。
在单元格H6输入以下公式,然后向下拉到第3行,再右拉直到出现空白。
=OFFSET($A$1,,(ROW(A1)-1)+(COLUMN(A1)-1)*3,)&""
3.多行多列转一列
比如源数据六行五列:
![ad227394ba4b7badf72d2e98ea1c8cc3.png](https://i-blog.csdnimg.cn/blog_migrate/31e6e127b923484900d7f0b0861daf8d.jpeg)
①先向下引用源数据,再向右引用。
单元格B9输入以下公式,下拉直到出现空白。
=OFFSET($A$1,MOD(ROW(A6),6),ROW(A6)/6-1,)&""
②先向右引用源数据,再向下引用。
单元格D9输入以下公式,下拉直到出现空白。
=OFFSET($A$1,ROW(A5)/5-1,MOD(ROW(A5),5))&""
4.多行多列转一行
多行多列数据转成单行的情况实际应用中应该不算常见,不过这里也一并整理了,若需要可直接套用公式。
比如源数据五行六列:
![187947984e15ea034c8e3ad23e9b1e06.png](https://i-blog.csdnimg.cn/blog_migrate/99527710bbc2bc2847c0cbe9b669e012.jpeg)
①先向右引用源数据,再向下引用。
单元格B8输入以下公式,右拉直到出现空白。
=OFFSET($A$1,INT(COLUMN(F1)/6)-1,MOD(COLUMN(F1),6))&""
②先向下引用源数据,再向右引用。
单元格B9输入以下公式,右拉直到出现空白。
=OFFSET($A$1,MOD(COLUMN(E1),5),INT(COLUMN(E1)/5)-1)&""
公式说明:
①ROW()返回行号,比如ROW(A2)=2;
②COLUMN()返回列号,比如COLUMN(F1)=6;
③MOD()求余数,比如MOD(1,5)=1,即1÷5余数是1,再比如MOD(5,5)=0,因为5被5整除,没有余数;
④上面每个公式最后都加了&"",作用是把引用空单元格得到的0去掉,显示为空白;
⑤上面公式中用到不少逗号,(英文半角状态下),不能随意省略或移动位置,每一个小豆芽都有它存在的意义,有兴趣研究的可以看看下面链接OFFSET的基础用法。
![9705ec962959b74c69ce5d028e2621d1.gif](https://i-blog.csdnimg.cn/blog_migrate/b6cc6fbdf9bd3553ae4b68373a9c2133.gif)
【原文链接:EXCEL中一列(行)转多行多列或多行多列转一列(行)】
![d407b7d8d7a261128dc1ba11a08286b3.png](https://i-blog.csdnimg.cn/blog_migrate/6a8f20907a0c19ccec2b54008e1f5a80.jpeg)