为什么说INDEX+ROW+COLUMN是必学的函数组合?行变列 列变行 行列转换,横竖转换 图文

2020年5月12日09:26:45 评论 244 views
表格 定制  数据 合并 处理 分析 VBA 编程 开发 网页
表格 定制  数据 合并 处理 分析 VBA 编程 开发 网页

将一列转多行,或者是多行转一列,几乎都会用到INDEX+COLUMN+ROW函数组合,这3个函数组合使用,可能是我见过的最好的行列转换方法!

下图中,需要将A列的姓名和B列的个人信息转成D1:J10单元格区域的表格。

为什么说INDEX+ROW+COLUMN是必学的函数组合?这篇文章告诉你答案

完成的效果图

解决该问题的思路如下:

1、首先,需要手动输入D1:J1单元格区域的标题名称。

为什么说INDEX+ROW+COLUMN是必学的函数组合?这篇文章告诉你答案

2、在D2:D10单元格区域中引入A列的姓名。这里可以使用INDEX函数。D2单元格的公式等于“=INDEX($A:$A,1)”,D3单元格的公式等于“=INDEX($A:$A,7)”,D4单元格的公式等于“=INDEX($A:$A,13)”,... 。

为什么说INDEX+ROW+COLUMN是必学的函数组合?这篇文章告诉你答案

这里我们可以发现一个规律:INDEX函数的第二个参数表示行号,而且上下两个单元格之间行数相差了6行,所以我们要将这个规律用函数表示出来。行号的规律就是:6*ROW($A1)-5,公式下拉的时候,$A1会依次变成$A2,$A3,... ,对应的行号也就是1,2,3,... ,把公式嵌入到INDEX函数的第二个参数中即可。

所以D2单元格的公式等于=INDEX($A:$A,6*ROW($A1)-5),选中D2单元格,按快捷键Ctrl+E即可得到D3:D10单元格的姓名。

为什么说INDEX+ROW+COLUMN是必学的函数组合?这篇文章告诉你答案

3、将B列的内容显示到E1:J10区域对应的单元格中。

E2单元格的公式等于=INDEX($B:$B,1),F2单元格的公式等于=INDEX($B:$B,2),G2单元格的公式等于=INDEX($B:$B,3),... ,E3单元格的公式等于=INDEX($B:$B,7),F3单元格的公式等于=INDEX($B:$B,8),G3单元格的公式等于=INDEX($B:$B,9),... ,INDEX函数的第二个参数1,2,3,7,8,9,... ,表示行号。

为什么说INDEX+ROW+COLUMN是必学的函数组合?这篇文章告诉你答案

如果我们想要让行号自动变化,可以将E2单元格公式=INDEX($B:$B,1)替换成=INDEX($B:$B,COLUMN(A$1)+6*(ROW($B1)-1)),将公示右拉填充至J2单元格,下拉填充至J10单元格即可。

为什么说INDEX+ROW+COLUMN是必学的函数组合?这篇文章告诉你答案

细心的小伙伴会发现,这里转换后的日期公式不是我们想要的,这里我们再设置一下单元格的格式即可。

选中J2:J10单元格区域,点击“鼠标右键”,在右键菜单中选择“设置单元格格式”。

为什么说INDEX+ROW+COLUMN是必学的函数组合?这篇文章告诉你答案

弹出“设置单元格格式”对话框,点击“自定义”选项卡,找到日期类型“yyyy/m/d”,点击“确定”按钮即可。

为什么说INDEX+ROW+COLUMN是必学的函数组合?这篇文章告诉你答案

4、完成后的效果图如下。

为什么说INDEX+ROW+COLUMN是必学的函数组合?这篇文章告诉你答案

效果图

5、动图演示如下。

为什么说INDEX+ROW+COLUMN是必学的函数组合?这篇文章告诉你答案

本期教程跟大家讲解到这里,如您练习的过程中有遇到问题,可以在评论区留言哦~

权 禁止转载 原文链接为https://www.toutiao.com/item/6733915992084185608/

  • 郑广学老师微信号
  • EXCEL880B
  • weinxin
  • 我的微信公众号
  • EXCEL880
  • weinxin
表格 定制  数据 合并 处理 分析 VBA 编程 开发 网页
表格 定制  数据 合并 处理 分析 VBA 编程 开发 网页

发表评论

您必须才能发表评论!