职场必备技能:用Excel函数轻松实现数据透视转换,3步将横向数据转为纵向格式

你是不是经常遇到这样的问题？销售数据是横向排列的（每个店铺一列），但你需要的是纵向格式（店铺名称单独成列）。手动复制粘贴？数据少还行，多了简直要命！今天教你两种方法轻松搞定！

一、Index+Match函数组合大法

第一步：填充款号、名称、零售价（固定列）

=INT(ROW(A1)-1)/4)+1

公式往下拉的时候，由于行序号变化，我们会得到如下的结果：

=INDEX($A$2:$C$5,INT(ROW(A1)-1)/4)+1,COLUMN(A1))

第二步：填充 店铺 名称（动态列）已知店铺名称一共是4个，可知店铺列循环返回这4个店铺的名称，即行号循环返回1~4。在上一步中，我们使用INT和ROW函数得到了重复4次依次递增的序号，这一步由于行号确定是在1~4的范围内，所以要用MOD函数：

=MOD(ROW(A1)-1,4)+1

继续使用INDEX函数求完整的店铺名称列的公式：

=INDEX($D$1:$G$1,MOD(ROW(A1)-1,4)+1)

第三步：填充 数量 列（动态匹配）完整公式：

=INDEX($D$2:$G$5, INT((ROW(A1)-1)/4)+1, MOD(ROW(A1)-1,4)+1)

公式解析：

• INT((ROW(A1)-1)/4)+1：确定原数据行号（每4行换一次款号）。
• MOD(ROW(A1)-1,4)+1：确定原数据列号（循环1~4对应4个店铺）。

总结：✅ 自动转换：数据增加时，只需调整公式范围，无需手动调整。✅ 无Power Query/VBA：纯函数实现，适合所有Excel版本。✅ 灵活扩展：如果店铺数量变化（比如6个店），只需修改公式中的4为6即可。

进阶优化：如果不想每次调整公式，可以改用动态范围：款式资料：

=INDEX($A$2:$C$5,INT(ROW(A1)-1)/COUNTA($D$1:$AA$1))+1,COLUMN(A1))

店铺名称：

=INDEX($D$1:$G$1,MOD(ROW(A1)-1,COUNTA($D$1:$AA$1))+1)

数量：

=INDEX($D$2:$G$5, INT((ROW(A1)-1)/COUNTA($D$1:$AA$1))+1, MOD(ROW(A1)-1,COUNTA($D$1:$AA$1))+1)

这样即使店铺数量变化，公式也能自动适应！

二、Power Query 大法（推荐）

1、使用CTRL+T把表格区域A1:G5转换为超级表，载入POWER QUERY；

2、选中所有店铺列，执行逆透视列再上载数据就完成了

Power Query方案的优势

• 一键刷新：原始数据更新后，只需右键刷新即可自动更新结果
• 处理大数据量：比公式更高效，不会拖慢Excel速度
• 可视化操作：无需编写复杂公式，通过界面点击即可完成
• 可重复使用：查询可保存并应用于其他类似数据集

总结：

如果你的EXCEL不支持Power Query，建议使用INDEX+MATCH函数方法，所有版本都可用，反之强烈建议使用Power Query（EXCEL2016开始自带）！！

掌握以上两个技巧后，你可以轻松应对各种需要行列转换的数据处理任务！