优化Excel公式：将列基表转为行基表并关联数值

算力阿峰

2026-6-14

解决方案：将宽表转换为长表（逆透视）

你需要将A1:E4的宽格式表格转换为G2:I13的长格式，这是典型的逆透视操作，以下是更简洁且动态的公式实现：

表格示例

	A	B	C	D	E	G	H	I
1	Employee	Q1	Q2	Q3	Q4	Result
2	E_01	500	600	200	800	E_01	Q1	500
3	E_02	70	40	10	90	E_01	Q2	600
4	E_03	300	700	400	100	E_01	Q3	200
5						E_01	Q4	800
6						E_02	Q1	70
7						E_02	Q2	40
8						E_02	Q3	10
9						E_02	Q4	90
10						E_03	Q1	300
11						E_03	Q2	700
12						E_03	Q3	400
13						E_03	Q4	100

最终公式

=LET(
    emp_range, A2:A4,
    qtr_range, B1:E1,
    val_range, B2:E4,
    emp_list, TOCOL(emp_range, 1),
    qtr_list, TOROW(qtr_range),
    emp_repeated, TOCOL(REPT(emp_list, ROWS(qtr_list))),
    qtr_repeated, TOCOL(REPT(qtr_list, ROWS(emp_list))),
    val_flattened, TOCOL(val_range, 1),
    HSTACK(emp_repeated, qtr_repeated, val_flattened)
)

公式解析

提取基础数据：
- emp_list：用TOCOL将员工列（A2:A4）转为一维列表，参数1用于忽略空值，提升扩展性
- qtr_list：用TOROW将季度表头（B1:E1）转为一维列表
- val_flattened：将数值区域（B2:E4）转为一维列，对应每个员工-季度的数值
生成重复序列：
- emp_repeated：每个员工重复ROWS(qtr_list)次（即4次，对应4个季度），再转为列
- qtr_repeated：每个季度重复ROWS(emp_list)次（即3次，对应3个员工），再转为列
组合结果：
- 用HSTACK将员工重复列、季度重复列、数值列横向拼接，直接得到目标长表