列联表分析（Excel PQ 功能用得好）

成套配电箱组价一般为：元器件价格（断路器、漏电断路器等、电度表等）、辅材配件（铜排、电线）、箱（柜）壳体、人工、综合取费、税金。日常工作中，配电箱内元器件品种繁多、规格各异，配电箱组价构成分析耗时费力，我们可以尝试使用 EXCEL 一键 PQ 方案，快速完成配电箱组价分析。

Power Query 是在 Excel 平台，进行数据清理、转换数据的最佳工具，我们只需五分钟完成 PQ 方案搭建，一“源”变更，一键刷新，自动生成分析报表。

常规操作与目标需求

用 Excel 常规做法进行数据分析

用 PQ 方案进行数据分析

PQ 方案的使用方法

1

获取数据

数据获取路径：数据→获取数据→自文件→从工作簿→浏览→选择“源文件”→确定→转换数据。如图所示：

注意：

① 获取数据后，不要轻易移动 Excel 文件存储路径及名称，改动后需在【查询设置】应用的步骤“源”设置同步修改；

② 可以从不同来源、不同结构、不同形式获取数据。

2

数据整理

将第一行用作标题→转换→填充（向下）→合并列→查询列表中复制→修改新查询名称（表格重复利用过程，需求不同，避免重复操作）→筛选行→删除其他列（保留配电箱名称和数量）→复制“源”→重命名“整合数据表”。

注意：按统一格式进行横向合并，纵向（追加）合并，条件合并等。

Ⅱ

合并查询→弹出对话框中选择合并来源“配电箱数量表”→选中两表匹配列（配电箱名称列）→确定→展开Newcolumn勾选需要合并到表中的内容→确定→筛选行（根据数据计算逻辑关系，保留单个配电箱组价项，取消小计及合计行） →删除列（序号列无操作需求） → 添加自定义列→ M函数条件语句“if …then…else”→数据类型选择小数（新添加的自定义列格式设置） →上载仅限连接→查询列表中复制刚完成的“整合数据表”。

注意：合并查询只要先选中匹配要用的列，然后选择要合并哪些列进来就可以了，合并查询与VLOOKUP相似，但比VLOOKUP更有担当、更快，尤其数据列多的时候匹配取数相对来说更简单了。

Ⅲ

获取数据后切换到“转换”选项卡，选中楼号箱名列点击分组依据→弹出对话框选择楼号箱名→新列名分别填写元件合价、求和、元件合价（求和列）→合并查询匹配配电箱数量列（同刚才分享操作，不再重复介绍） →自定义列（配电箱单价=合价/数量） →拖拉列调整列顺序→按报表成果文件命名列→上载创建新表。

Ⅳ

查询列表中复制“整合数量表”→修改新查询名称“构成分析表”→选中元器件名称列点击分组依据→弹出对话框选择高级→添加分组为“元器件名称” →求和元件数量和元件合价→自定义列“元件单价=元件合价/元件数量” →调整列顺序→选中元件合价列点击排序“升序或降序” →自定义列“成本占比” → 公式为“[元件合价]/List.Sum(更改的类型5[元件合价])” → 上载创建新表“成本构成成果文件”。

注意：分组依据是分类汇总的利器，而分组依据高级选项中“添加聚合”按钮，可对多列进行分类统计，与EXCEL中只有“行”项目的数据透视功能类似（聚合可以进行求和、最大值、最小值、计数等值统计）；成本占比公式为“[每类元件合价]/List.Sum(更改的类型5[所有元件合价])” ，求和公式写入要正确，注意大小写，另外求和列最后一次操作名称的写入。

Ⅴ

查询列表中复制“构成分析表”→修改新查询名称“构成分析表（按厂家）”→合并查询“源”匹配品牌列→上载创建透视表→生成成果文件“按甲指厂家分类统计”。

3

成果文件

编制一个“源”文件，搭建 PQ 方案，自动生成三个成果文件（汇总表、构成分析表、按甲指分类统计……可以更多）；点击菜单栏“数据——查询和连接”，回看操作过程或修改操作过程。一劳永逸，一键刷新。

PQ 方案操作总览

*本文系乔平原创，独家来稿

转载务必申请授权，并注明作者与出处，违者必究

本文仅代表作者观点，仅供参考，如有异议，烦请留言