将横向排列的数据转换为纵向排列的几种方法.docVIP

将横向排列的数据转换为纵向排列的几种方法 作者：Lijia 文章来源： 点击数：3164 更新时间：2012-12-10 9:22:38 在Excel中将横向排列的数据转换为纵向排列，通常用的方法是复制数据后进行选择性粘贴，同时选择“转置”。但有时无法使用“转置”的功能，例如下图A1:G17区域为某网店部分商品的库存数量，“商品尺寸”是横向排列的，现在需要将这些数据按纵向排列，即将“商品尺寸”及其对应的“货号”、“数量”排列到三列中，如图J至L列所示。 本文介绍用数据透视、数组公式和VBA等三种方法来实现这种转换，以Excel 2010为例。 方法一、用数据透视表 1.利用原始数据建立数据透视表。 按组合键“Alt+D”，再按“P”键，打开“数据透视表和数据透视图向导”对话框，选择“多重合并计算数据区域”，单击“下一步”。 在弹出的对话框中再次单击“下一步”，弹出“数据透视表和数据透视图向导-步骤2b”，选择工作表中的A1:G17区域，单击“添加”按钮。 单击“完成”按钮，Excel会在新工作表中建立数据透视表。 2.通过数据透视表获取明细数据。 右击数据透视表行总计和列总计交叉的单元格，本例为H21，在弹出的快捷菜单中选择“显示详细信息”（也可双击该单元格右下角的填充柄）。 Excel会自动在新工作表中显示该数据透视表数据源的明细数据，如图所示。 3.筛选C列中的非空数据，将A至C列数据复制到所需位置即可。 方法二、用数组公式 假如将转换后的数据放在J至L列，在J2单元格输入数组公式： =OFFSET(A$1,SMALL(IF(B$2:G$17=,47,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，双击填充柄将公式填充到这两列的其余单元格。 方法三、用VBA 用下面的VBA代码也可实现上述转换，方法是按Alt+F11，打开VBA编辑器，在代码窗口中粘贴下列代码并运行。Sub 转换()Dim Arr1, Arr2()Dim Rnum As Integer, Cnum As Integer, Tnum As IntegerDim i As Integer, j As Integer, k As IntegerApplication.ScreenUpdating = FalseRnum = A65536.End(xlUp).RowCnum = 7Tnum = Rnum * CnumRange(J2:L & Tnum).ClearContentsArr1 = Range(A1:G & Rnum)ReDim Arr2(1 To Tnum, 1 To 3)For i = 2 To Rnum For j = 2 To Cnum If Arr1(i, j) Then k = k + 1: Arr2(k, 1) = Arr1(i, 1) Arr2(k, 2) = Arr1(1, j) Arr2(k, 3) = Arr1(i, j) End If NextNextRange(J2).Resize(k, UBound(Arr2, 2) = Arr2Application.ScreenUpdating = TrueEnd Sub摘自：/Article/exceljichu/201212/961.html有时需要在Excel中将区域中数据的排列方式进行转换，例如将横向排列的数据转换为纵向排列。对于数据区域转换后只保留第一列为纵向排列，而其他各列则由横向转为纵向排列的情况，可用数据透视表的“多重合并计算数据区域”功能来实现，参考本站将横向排列的数据转换为纵向排列的几种方法一文。如果要将数据区域中的前两列或多列保持为纵向排列，用数据透视表的“多重合并计算数据区域”功能无法实现，例如将下图左侧区域“货号”列为文本格式，“日期”列为日期格式，其他各列为数值，要将左侧区域的数据转换为右侧区域中的排列方式。其中“货号”列和“日期”列保留纵向排列，其他的各种“尺寸”由横向排列转换为纵向排列。 本文介绍在Excel 2010/2013中用数据透视表来实现这种转换的方法，以Excel 2013和上图为例，步骤如下： 1.建立数据透视表。选择数据区域中的某个单元格，选择“插入数据透视表”，在弹出的对话框中单击“确定”，在新工作表中创建数据透视表。 2.在“数据透视表字段列表”中依次勾选全部字段名称，“货号”和“日期”字段会自动放入到“行”区域中，其他字段会自动放入到“值”区域。同时“列”区域中会出现一个“数值”字段，将该字段拖入到“行”区域中。 这时的数据透视表如图： 3.设置数据透视表布局。 选择数据透视表，在“数据透视表工具-设计选项卡中的“布局”组中进行如下设置：文章来源透视表筛选排序工作簿工作表分类汇总求和 选择“分类汇总不显示分类汇总”； 选择“报表布局以表格形式显示”； 选择“报表布局重复所有项目标签”； 选择“总计对行和列禁用”