Power BI 处理大中小微企业划型（1）统计局标准规范化

全文简介

大中小微企业划型难点

在监管报告工作流程之中，根据不同的行业组别选取营业收入、人数、总资产三者之中的一个或两个以确定大中小微企业划型是一个比较困难的问题，自动化较为困难，手工划型准确度和一致性较差，所以合规风险较高。
从技术角度看，统计局对大中小微企业的定义，其数据结构没有规范化，并未以数据库行列表的结构化方式呈现。而且计算层次复杂，需要分多维度多档次判断。
首先是大中小微企业划型的行业有16个分组，没有与国家统计标准建立定量对应关系；
其次是不同行业划型判断需要在人员、营业收入、总资产这三个判断维度之中选取一个到两个作为有效维度，排列组合情况较多；
最后是前述有效维度有各自的大中小微区间上下限，以企业各有效维度与相应档次大中小微划型标准下限比较后，取各维度所处最低档次为企业大中小微规模。
从银行内部分工的角度，监管报表大多由财务部门上报，而判断客户类型的一线业务人员对大中小微企业划型标准了解甚少，以上划分标准过于复杂，难以准确理解和运用。如果一线业务人员根据对企业客户的大致印象判断大中小微企业划型，更加难免忙中出错或者反复修改。

Power BI解决方案

使用Power BI，可以建立标准化的大中小微企业划分模型，将判断机制自动化，使得业务人员只需要录入人数、营业收入、总资产这三项基本客户数据，而判断大中小微的工作由Power BI根据预设的逻辑自动完成，所有基础数据与主要过程都可以清晰展现，并且共享至公司内部与外部网络、云端和移动客户端。主要步骤概述如下：
Power Query分拆统计标准、替换、逆透视等方式将统计局标准转换为规范化的一维表，而且只保留排除与企业规模划型无关的维度，减少冗余。
Power Query合并查询，以便在国家标准行业代码与大中小微企业划型行业组二者间建立关系之后，自动将标准行业代码转换为大中小微企业划型行业组；次之，通过合并查询筛选出所需要维度数据，转换为大中小微划型标志，不需要人为判断。
Power BI画布主板之中根据客户信息，灵活切片展示原始数据与最终结果，并且发布至网络云端。
综上所述，Power BI可以实现大中小微企业划型自动化，实现结果与过程可视化，作为进一步处理的基础，或者输出到个人电脑桌面、网络、移动办公设备展现。
图表 1 企业规模大中小微划型输出结果-部分截图

第一篇：统计局标准规范化

转换前统计标准

图表 2 企业规模大中小微划型统计局标准-部分截图

从以上统计局标准部分截图可见，该标准非常适合人工阅读，但是电脑自动化处理就比较困难：
第一栏“行业名称”并非标准的国家标准行业代码，系统输入或者处理汉字容易出错， 类似“工业 ”之中的星型标志“”也容易与系统保留字符混淆。
转换方案：在本划分标准表行业名称与国家标准行业代码二者间建立一对多关系，从系统之中的标准行业代码聚合到统计局企业规模划型行业名称。
第二栏“指标名称”有从业人员与营业收入，营业收入与资产总额两个条件组合，在Excel之中处于两行，而且第二行对应的行业栏为空，不利于自动化处理。
转换方案：涉及两个判断维度的行业，向下填充行业名称栏空格与指标名称一一配比。
第四栏至第七栏，统计标准划分为大型、中型、小型、微型四列，最多需要计算四次，而且表达式之中含有“≥”，“≤”，“＜”等符号，难以直接利用。
转换方案：使用逆透视，将大型、中型、小型、微型四列转为一列，而且提取每一档次标准之中的上限，删除“≥”，“≤”，“＜”等符号，同时为大中小微赋值4,3,2,1，使得在计算过程之中可以使用数值代替汉字比较，提高效率。

转换后统计标准

图表 3 企业规模划型标准整理后-部分截图

从上图可见，转换后的划型标准形成以行业名称和指标名称两栏为左边一组，阈值下限和规模为右边一组的笛卡尔积；而且没有冗余，例如零售业不需要使用资产总额判断，而农、林、牧、渔业只需要使用营业收入判断，其余无关要素不需考虑。

统计标准转换过程

本部分操作全部在Power Query编辑器之中完成，进入方法：
Power BI Desktop 开始=>编辑查询
图表 4 Power Query 入口

Power Query有两个非常强大的特点，一是使用其菜单工具栏可以完成大多数常规工作，即数据清洗与转换，二是使用菜单工具栏的操作可以被记录为代码，稍微复杂的操作所需要代码也可以在简单操作自动录制代码的基础上修改而得。
对初级用户而言，不会写M语言函数代码也可以完成大多数任务，制约Power Query数据处理能力的最大因素是数据空间想象力。
图表 5 Power Query 中 M函数代码与注释

以上过程只有两个语句“提取阈值档次数值”与“取模”是从自动代码手工修改得出的；其余源、统计局标准表_Table（导航）、删除顶端行、向下填充、逆透视数量标准、更改阈值档次类型、删除列、添加索引等都可以直接通过菜单操作完成，菜单操作代码由机器录制。
主要步骤叙述如下：

获取转换前的统计局大中小微企业划型标准

手工操作命令：

源：Power Query编辑器=>开始=>新建源=>浏览（C:\数据源\）=>点击（大中小微企业划型.xlsx）

导航：双击红圈内的Table

图表 6 Power Query 获取数据

上图中M函数代码第一句“源”的意思是从指定位置（数据源参数 ）的Excel工作簿文件之中读取统计局标准等多个智能表格（Table ）或者工作表（sheet）子数据源。在Data一栏表格之中选中右边区域，可见下方显示出Table的具体内容，如蓝色箭头起始位置所示。
第二句导航即“源{[Item="统计局标准表",Kind="Table"]}[Data]”是从多个子数据源之中提取出第6行统计局标准这一张智能表格，但是由于该智能表格从word之中贴出，而word表格中一个单元格内有换行符会被Excel识别为合并单元格，并且Power Query会按照换行符分行，导致在数据表中有多处显示null（空值）。

处理null值

图表 7 处理多余 null 值

删除第一行：开始=>删除行=>删除最前面几行=>对话框之中输入1

向下填充：转换=>向下

经过“删除顶端行”与“向下填充”两步之后，得到规范化的数据表，只有一行列标题，数据表之中每一行与每一列结构相同，内容相似。

逆透视将大中小微标准转换到一列

图表 8 逆透视

逆透视数量标准：转换=>按Ctrl键后选中四列（大型、中型、小型、微型）=>逆透视列，或者仅逆透视选定列，二者效果相同。

上一步“向下填充”完成后，大中小微企业划型数量标准被分在四列，这与统计局文件标准结构一致，也是大多数读者日常工作之中所习惯的方式。但是这样判断企业划型时，需要分别到四列去计算四遍，所以机器运行效率并不高；如果将四列并做一列，那么只需要运算一次，效率将大大提升。

提取阈值档次数值：这一步较为复杂，不可以直接使用菜单命令完成，“//”左边是代码，右边是注释，详情参见下方截图。

图表 9 分拆提取区间阈值下限

由于这一步之中新增列标签前数据类型符号是ABC¦123，代表阈值下限数据类型是任意，而后续处理要求阈值下限数据类型是整数，所以下一步还需要转换数据类型。
更改阈值档次数据类型：开始=>选中 阈值下限=>数据类型=>下拉菜单中选择 整数。

添加大中小微档次辅助列

大中小微下限转换为一维表之后，如果增加一列以4,3,2,1对应大中小微汉字代表的企业规模，那么后续将更加方便

添加索引：添加列=>索引列=>从 0，生成从0到119的120个数字，再双击列标题，修改列名为“档次”。

取模：转换=>标准=>在弹出的对话框之中输入 4，这样将上一步插入的索引档次列数值除以4后得余数，转换为0,1,2,3，在120行重复出现30次。

以上手工操作之后，为了将大中小微与4,3,2,1对应，需要手工修改代码，修改后代码如下：
取模 = Table.TransformColumns(添加索引, {{"档次", each 4-Number.Mod(_, 4), type number}})
4减去上一步“添加索引表”之中“档次”栏每一个值除以4之后的余数，将原0,1,2,3转换为4,3,2,1
删除不需要的内容
为了数据表更加简洁，需要在两个步骤之中分别删除不需要的两列计量与区间，以及删除在规模一列之中多余的型字。
删除列：按Ctrl键=>选中计量与区间两列=>右击鼠标=>在快捷菜单之中点击删除。
删除型字：转换=>替换值=>在上方对话框之中输入汉字 型，下方对话框不输入任何内容

本篇小结

Power Query处理不规范数据的能力非常强大，为了将统计局规模划分标准转化为便于自动化处理的一维表，本篇之中主要使用了从Excel导入数据，删除指定行，向下填充，逆透视，添加条件列按不同情境返回不同数值，将汉字对应转换为整数，添加索引列，取模转换，转换数据类型等操作步骤。
以上这些步骤大多数可以通过菜单完成，而且菜单输入指令也可以被自动记录为代码。
加入QQ讨论群 PowerBI与1104 后，可获取源文件。

银行业Excel商务智能实践者