Excel学习笔记：行列转换
offset函数
功能：
以指定的（单元格或相连单元格区域的引用）为参照系，通过给定偏移量得到新的引用。返回的引用可以是一个单元格也可以是一个区域（可以指定行列数，即数组）。
语法：
=offset( reference, rows, cols, [height], [width])
=offset(参照单元格， 行偏移量， 列偏移量， 返回几行， 返回几列)
参数解释：
- 参数Reference —— 参照单元格（单元格或相连单元格区域的引用）。否则，offset函数返回错误值#VALUE!
- 参数Rows——行偏移量。正数（代表在参照单元格的下方），负数（代表在参照单元格的上方）。
- 参数Cols——列偏移量。正数（代表在参照单元格的右边）或负数（代表在参照单元格的左边）。
- 参数Height——返回几行（必须为正数），即所要返回的引用区域的行数。
- 参数Width——返回几列(必须为正数），即所要返回的引用区域的列数。
最后2个参数不指定时，默认返回1行1列。
实例：
输入公式：=offset(A1,1,1,1,1) -- 返回A1的右下角单元格
输入公式：=sum(offset(A1,1,1,2,2)) -- 返回A1的右下角2*2单元格，并进行求和
可通过
F9按钮查看数据。
另外，也可以通过选定2*2单元格，再进行输入公式，按住三键CTRL+SHIFT+ENTER进行数组计算。
三键（CTRL+SHIFT+ENTER） -- 数组计算
行列转换的三种方式
1.右键转置
复制源数据--右键--选择性粘贴--勾“转置”，确定。
2.转置公式TRANSPOSE
- 列转行 → 先选中横向连续的N个空白单元格，在编辑栏输入公式
=TRANSPOSE($A$1:$A$10)，然后按三键CTRL+SHIFT+ENTER即可； - 反之，行转列则先选中竖向的单元格区域，后续操作类似。
$表示绝对引用，可按F4直接切换。
3.引用函数OFFSET+ROWS/COLUMN（支持随时更新数据）
方法一、方法二只能一次性转置，后期无法随时更新数据。
- 列转行 → 输入公式
=OFFSET($A$1,COLUMN(A1)-1,0,1,1)向右拉 - 行转列 → 输入公式
=OFFSET($A$1,0,ROW(A1)-1,1,1)向下拉
一行（列）转多行多列
结合OFFSET函数与ROW/COLUMN函数进行嵌套使用，即可实现。
1.一列转多列多行（或多行多列）
①先列后行，比如一列数据转为四列多行。
在目标区域第一个单元格输入以下公式，然后向右拉到第4列，再下拉直到出现空白。
=OFFSET($A$1,COLUMN(A1)-1+(ROW(A1)-1)*4,)&""
②先行后列，比如一列数据转为四行多列。
在单元格输入以下公式，然后向下拉到第4行，再右拉直到出现空白。
=OFFSET($A$1,ROW(A1)-1+COLUMN(A1)*4-4,)&""
2.一行转多列多行（或多行多列）
①先列后行，比如一行数据转为三列多行。
同样，此例在单元格输入以下公式，然后向右拉到第3列，再下拉直到出现空白。
=OFFSET($A$1,,(COLUMN(A1)-1)+(ROW(A1)-1)*3,)&""
②先行后列，比如一列数据转为三行多列。
在单元格输入以下公式，然后向下拉到第3行，再右拉直到出现空白。
=OFFSET($A$1,,(ROW(A1)-1)+(COLUMN(A1)-1)*3,)&""
3.多行多列转一列
①先向下引用源数据，再向右引用。
单元格输入以下公式，下拉直到出现空白。
=OFFSET($A$1,MOD(ROW(A6),6),ROW(A6)/6-1,)&""
②先向右引用源数据，再向下引用。
单元格输入以下公式，下拉直到出现空白。
=OFFSET($A$1,ROW(A5)/5-1,MOD(ROW(A5),5))&""
4.多行多列转一行
多行多列数据转成单行的情况实际应用中应该不算常见，不过这里也一并整理了，若需要可直接套用公式。
①先向右引用源数据，再向下引用。
单元格输入以下公式，右拉直到出现空白。
=OFFSET($A$1,INT(COLUMN(F1)/6)-1,MOD(COLUMN(F1),6))&""
②先向下引用源数据，再向右引用。
单元格输入以下公式，右拉直到出现空白。
=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去掉，显示为空白；
⑤上面公式中用到不少逗号,（英文半角状态下），不能随意省略或移动位置，每一个小豆芽都有它存在的意义。
参考链接1：Excel中offset函数的使用方法
参考链接2：Excel行列互转的三种方法
