巧用EXCEL“函数+透视”实现多属性数据行列转换

在日常工作中，经常会碰到被审计单位提供的数据与要求上报的数据格式不一致的情况，这就需要进行行列转换。但对于具有姓名、身份证号等多个属性的数据行列转换，无论用哪种常规方法来实现，均难度较大且费时费力。笔者在实践中运用EXCEL2016的“函数+透视”功能快速完成数据标准化整理，本文以某次审计中遇到的村干部及家属信息数据转换为例，分享操作经验。

一、数据整理需求分析

在该次审计中，虽然被审计单位提供的数据（见图1）满足标准表（见图2）要求，但仍然存在着字段值不规范和表格格式不一致的问题。

图1

图2

1.标准表中要求填报的内容为父亲、母亲、配偶、子女1、子女2……而现有数据中为长子、次子、小女、女婿、儿媳等，需要剔除无用内容，并规范保留字段内容。

2.两表都以村干部信息为关键字，但标准表中村干部家属信息为横向排列，而现有数据中是纵向排列，需要将行数据转为列显示。

二、相应解决办法

（一）表格内容整理

1.清洗并规范数据。利用筛选功能，将表中与村干部关系中的数据按照父亲、母亲、配偶、子女四类进行统一，剔除无用信息，如图3所示。

图3

2.对子女进行编号。按照标准表要求，用COUNTIFS函数将同一位村干部的多名子女编号，分为子女1、子女2……

（1）如图4所示，在J2单元格输入公式:=IF(I2="子女",COUNTIFS($E$2:E2,E2,$I$2:I2,"子女"),"")，意思是当前单元格为“子女”时，统计从E2单元格到当前单元格为同一村干部身份证号出现的次数。

图4

（2）利用函数将子女与顺序号合并得到子女序号，如图5所示。

图5

（二）与村干部关系整理

1.如图6所示，选择“数据”-“获取和转换”-“从表格”，进入POWER QUERY编辑器。

图6

2.如图7所示，选择“家属姓名”、“家属身份证号”两列进行逆透视。

图7

3.如图8所示，选择“属性”列，利用“转换”-“替换值”，将“家属姓名”和“家属身份证号”中的家属两字替换掉。

图8

4.如图9所示，选中“与村干部关系”和“属性”列，利用“转换”-“合并列”构造出标准表所需字段。

图9

5.如图10所示，选中“已合并”和“值”列，执行“转换”-“透视列”，在弹出窗口中，选择“值列”为“值”，将使用列“已合并”中的名称创建新列，“聚合值函数”选择“不要聚合”。

图10

6.点击“确定”并按标准表调整字段顺序后就得到所需的标准表，如图11所示。选择“关闭并上载”，将结果保存到EXCEL中。

图11