Excel 如何批量将矩阵(多行多列)数据转为单行或单列数据

该问题源于这样一个实践场景,试想有一个花名册,如下这样:


现在需要根据这个花名册批量将其转换为考试时贴在桌上的小标签,如下这样:

那么这个需求本质上就是将多行多列数据(考生姓名、考生编号、证件号码三列)转为单列数据(上图需求结果的第二列)。第一列是静态数据,第三列是递增数列,相对都比较容易实现。

现在就教大家如何实现。

需要用到的 Excel 函数

OFFSET

实现该需求的最主要的函数,能实现通过计算坐标取值。这句话有点绕,大多数时候我们写函数的时候是将对应单元格的坐标写死的,也就是将一列数据经过计算后输出到另一列,输入的单元格和输出的单元格的坐标是彼此对应的。而这个函数能够帮我们按照自己的需求“计算”出所需坐标并取值。

具体用法如下:

OFFSET(reference, rows, cols, [height], [width])

参数解释:

  • reference: 偏移坐标。如我们默认的偏移坐标是(0,0),即左上第一个点。如果设置了偏移坐标,后面的 rows 和 cols 都是相对 reference 来讲的。
  • rows: 第几行。相对reference 来讲。
  • cols: 第几列。reference 来讲的。
    剩余的两个参数本文当中用不到,在此不作解释。

实际坐标为(reference + rows, reference + cols)。如想获取坐标为(10, 10)的单元格,则OFFSET((5, 5), 5, 5),OFFSET((3, 7), 7, 3)都是获取到这个单元格的函数。

QUOTIENT

整除,无余数。

QUOTIENT(10, 5) = 2

QUOTIENT(10, 3) = 3

MOD

取余。

MOD(10, 5) = 0

MOD(10, 3) = 1

ROW

获取横坐标。

ROW((3, 5)) = 3

ROW((5, 3)) = 5

其他用到的 Excel 小技巧

相对位置和绝对位置

在函数中要直接获取一个单元格,可以A1,也可以$A$1。前者是相对位置,也即坐标值会随着单元格的拉大而变化。后者是绝对位置,反之,坐标值不会随着单元格的拉大而变化。

具体实现


=OFFSET(Sheet2!$B$2,QUOTIENT(ROW(A3), 3)-1,MOD(ROW(A3), 3))

=OFFSET(Sheet2!$B$2,QUOTIENT(ROW(A3), 3)-1,3)

参考

热门相关:极品医圣   贩罪   我的抖音太无敌   伏天剑尊   强宠头号鲜妻:陆少,滚!