VBA实现Excel中表格的行列互换

有网友问：EXCEL怎么获取区域内每个单元格对应的行、列信息数据？如下图示，根据左侧表格，将右侧人员的宿舍号，床位号生成出来？

类似的问题事实上可以视作Excel中表格的行、列互换（转置）。那么我们可以使用Excel内置VBA编写一个自定义函数，获取指定姓名的“舍号”与“床号”。程序的基本思路就是给定一个要查找姓名的单元格地址，然后在给定的区域内查找该姓名所在单元格，若找到了，返回该单元格的所指向的“舍号”与“床号”，事实上“舍号”为同号首列单元格的值、“床号”为同列首行单元格的值。假设有如下图所示的Excel工作表：

按ALT+F11组合键进入VBA代码编写窗口，单击【插入\模块】菜单进入通用模块窗口，然后输入如下图所示的VBA模块代码。

这里编写了一个名为getBno的自定义函数，包含2个参数，参数类型均为Range型（单元格/单元格区域地址），实现在yrng中查找xrng的值所在单元格位置；返回值：一个一包含2个元素的一维数组，第1个元素为舍号、第2个元素为床号。

代码比较简单。用一个For Each-Next循环，遍历yrng所表示的单元格区域中的每个单元格，若该单元格的值恰好等于xrng单元格的值，则获得该单元格的row与column属性（即行号r与列号c），然后再取得yrng单元格区域中同行首列单元格的值yrng.cells(r-r0+1,1)、同列首行单元格的值yrng.cells(1,c-c0+1)存入到数组stu中；因为cells为yrng区域的单元格，行号与列号由1开始，这与在工作表中的绝对位置有个偏差值，所以用到了r-r0+1与c-c0+1。最后由函数名返回一维数组值，代码如下图示：

代码编写完成后，返回Excel工作表窗口。在图1所示的工作表中选中L3:M3单元格区域，然后输入公式【=getBno(L3,$A$2:$I$21)】,然后按Ctrl+Shift+Enter组合键确认输入，即可得到“龚燕”的“舍号”与“床号”，如下图示：

因为自定义函数getBno返回的是一个数组，Excel将视作矩阵，所以在公式确认输入时须按【Ctrl+Shift+Enter】组合键，实际公式显示为【{=getBno(K3,$A$2:$I$21)}】，但这个大括号不能手工输入。然后用公式复制填充方式向下填充即可得到其他学生的“舍号”与“床号”。由于查找的区域相同，所以函数中的第2个参数要使用绝对地址引用。

下面给出自定义函数getBno的文本代码（可复制）：

Function getBno(xrng As Range, yrng As Range)
      '在区域yrng中查找单元格xrng的值
      '返回对应的yrng首列与首行值(以数组返回)
      Dim stu(2) As Variant
      Dim r As Long, c As Long
      For Each x In yrng
          If x.Value = xrng.Value Then
              r = x.Row
              c = x.Column
              Exit For
          End If
      Next
      r0 = yrng.Row
      c0 = yrng.Column
      stu(0) = yrng.Cells(r - r0 + 1, 1)
      stu(1) = yrng.Cells(1, c - c0 + 1)
      getBno = stu
End Function

我是鉴水鱼老师，关注我，持续分享更多的Excel知识与操作技巧。