办公小技巧:Excel表格名单行列巧转换

CFan 电脑爱好者 2020-03-16 10:01应用 标签:技巧 办公

表格内同一个部门的人员姓名都挤到一个单元格内,如何才能把这样的数据转换成一人一格的列表呢(图1)?其实,利用拆分列及逆透视列就可以轻松实现。

1901A-HLZH-1

首先,选中数据区域中的任意单元格,选择“数据”选项卡,点击“自表格/区域”按钮,在弹出窗口中会自动选中表中数据的来源区域,即A1:B7,勾选“表包含标题”,确定后,Excel自动打开“查询编辑器”界面。单击“负责人”所在列的列标,选择“转换”选项卡,点击“拆分列→按分割符”,在拆分对话框中,分隔符选择“自定义”,输入“、”(顿号),确定后,“负责人”列就会按人名拆分成若干列(图2)。

1901A-HLZH-2

接下来,按住Ctrl键不放,依次选中“负责人”所在的相应几个列,在“转换”选项卡中点击“逆透视列”。这样,所有“负责人”所对应的若干列就合并到1列了(图3)。

1901A-HLZH-3

最后,右键单击“属性”列的列标,删除,点击“开始→关闭并上载”。这样就会生成一个新的工作表,名单行列转置完成了。

应用扩展:

那么,反过来,如何利用Power Pivot(查询编辑器)进行同类项的合并呢?首先,将鼠标光标置于数据区域任一位置,选择“Power Pivot”选项卡,点击“添加到数据模型”,就会弹出“Power Pivot for Excel”窗口,会看到新建的表名,如“表2_25”。

接着,将“Power Pivot for Excel”窗口最小化,在数据源工作表窗口点击“度量值→新建度量值”,在弹出窗口公式处输入“=CONCATENATEX('表2_25','表2_25'[负责人],"、")”(图4)。

1901A-HLZH-4

打开刚才最小化的表工具窗口,选择“主页”选项卡,新建一个数据透视表,在右侧“数据透视表”窗口将“表2_25”展开,勾选“销售点”和度量值。这样,就实现了同类项的合并(图5)。

1901A-HLZH-5