Excel三种将横向排列的数据转换为纵向排列方法-Excel教程

在使用Excel时，有时候需要将横向排列的数据转化为纵向排列。本经验将介绍通过数组公式、数据透视表、VBA三种方法实现这种功能。

以下图为例：下图A1:G17区域为某网店部分商品的库存数量，“商品尺寸”是横向排列的，现在需要将这些数据按纵向排列，即将“商品尺寸”及其对应的“货号”、“数量”排列到三列中，如图J至L列所示。

前期准备工作（包括相关工具或所使用的原料等）

Excel

详细的操作方法或具体步骤

用数组公式的方式

假如将转换后的数据放在J至L列，在J2单元格输入数组公式：

=OFFSET(A$1,SMALL(IF(B$2:G$17="",4^7,ROW(B$1:G$16)),ROW(A1)),)&""

公式输入完毕按Ctrl+Shift+Enter结束，下同。然后拖动填充柄向下填充公式，直到公式返回空为止。

在K2输入数组公式：

=OFFSET(A$1,,SMALL(IF(OFFSET(B$1,MATCH(J2,A$2:A$17,),,,6)<>"",COLUMN($A:$F)),COUNTIF(J$2:J2,J2)))

在L2输入数组公式：

=OFFSET(A$1,MATCH(J2,A$2:A$17,),SMALL(IF(OFFSET(B$1,MATCH(J2,A$2:A$17,),,,6)<>"",COLUMN($A:$F)),COUNTIF(J$2:J2,J2)))

然后选择K2:L2，双击填充柄将公式填充到这两列的其余单元格。

用数据透视表方式实现

1.利用原始数据建立数据透视表。

按组合键“Alt+D”，再按“P”键，打开“数据透视表和数据透视图向导”对话框，选择“多重合并计算数据区域”，单击“下一步”。

在弹出的对话框中再次单击“下一步”，弹出““数据透视表和数据透视图向导--步骤2b”，选择工作表中的A1:G17区域，单击“添加”按钮。

单击“完成”按钮，Excel会在新工作表中建立数据透视表。

2.通过数据透视表获取明细数据。

右击数据透视表行总计和列总计交叉的单元格，本例为H21，在弹出的快捷菜单中选择“显示详细信息”（也可双击该单元格右下角的填充柄）。

Excel会自动在新工作表中显示该数据透视表数据源的明细数据，如下图所示。

3.筛选C列中的非空数据，将A至C列数据复制到所需位置即可。

经验内容仅供参考，如果您需解决具体问题(尤其法律、医学等领域)，建议您详细咨询相关领域专业人士。作者声明：本文系本人依照真实经历原创，未经许可，谢绝转载。