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)