求助：基于分隔符的预算拆解自动化分析方案（Excel/Power BI/Python）

一、Power BI（集成Python）方案 #

1. 数据导入与CBS层级拆分 #

• 导入预算数据表（支持Excel/CSV等格式），进入Power Query编辑器
• 选中CBS代码列，点击「拆分列」→「按分隔符」，选择你使用的分隔符（如.、/），选择「拆分为列」，生成Level1、Level2、Level3...等层级列
• 清洗数据：补全缺失的层级值（如子项的上层级继承父项值），确保层级结构完整

2. 成本占比计算（DAX为主，Python辅助复杂场景） #

基础计算用DAX

• 计算总成本：创建度量值 总成本 = SUM('预算表'[成本])
• 计算某层级的父类成本（以Level2为例，对应Level1的父类）：

  父类成本 = CALCULATE(
      SUM('预算表'[成本]),
      ALLEXCEPT('预算表', '预算表'[Level1])
  )
  

• 计算占总成本比例：占总成本比例 = DIVIDE(SUM('预算表'[成本]), [总成本], 0)
• 计算占父类比例：占父类比例 = DIVIDE(SUM('预算表'[成本]), [父类成本], 0)

复杂层级用Python脚本

如果层级不固定（动态深度），可以在Power Query中调用Python处理：

import pandas as pd

# 读取Power BI传入的数据
df = dataset

# 拆分CBS列为层级列表，动态生成层级列
df['层级列表'] = df['CBS代码'].str.split('.')  # 替换为你的分隔符
max_level = df['层级列表'].apply(len).max()
for i in range(max_level):
    df[f'Level{i+1}'] = df['层级列表'].apply(lambda x: x[i] if i < len(x) else None)

# 按层级汇总，计算各层级的总成本
level_cols = [f'Level{i+1}' for i in range(max_level)]
for col in level_cols:
    df[f'{col}_总成本'] = df.groupby(col)['成本'].transform('sum')

# 计算占比
df['占总成本比例'] = df['成本'] / df['成本'].sum()
df['占父类比例'] = df['成本'] / df[f'{level_cols[df["层级列表"].apply(len)-1]}_总成本']

# 返回处理后的数据
df = df.drop('层级列表', axis=1)

运行脚本后将数据加载回Power BI，即可直接使用计算好的字段。

3. 可视化展示 #

• 一级类别占比：用饼图，将Level1作为图例，成本或占总成本比例作为值
• 层级对比：用树状图（或旭日图），展示从Level1到末级的成本占比结构
• 类别间对比：用簇状条形图，对比不同子类别（如ABC/XYZ场地）的成本及占父类比例

二、Excel方案 #

1. 层级拆分与数据清洗 #

• 选中CBS代码列，点击「数据」→「分列」，按分隔符拆分生成层级列（Level1~LevelN）
• 用VLOOKUP或批量填充功能补全缺失的上层级值（如子项的Level1继承父项的Level1）

2. 成本占比计算 #

• 总成本：用SUM(成本列)计算，存为固定单元格（如$Z$1）
• 父类成本：以Level2子项为例，用SUMIFS(成本列, Level1列, 当前行Level1值)计算
• 占比公式：
  • 占总成本：=D2/$Z$1（D2为当前行成本），设置为百分比格式
  • 占父类：=D2/SUMIFS($D:$D,$A:$A,$A2)（A列为Level1，D列为成本）

3. 交互分析 #

• 插入数据透视表，将Level1~LevelN拖入行区域，成本拖入值区域，可展开/折叠层级查看汇总
• 添加切片器，按层级筛选特定类别，快速查看对应成本占比

三、纯Python方案 #

1. 数据读取与层级处理 #

import pandas as pd

# 读取预算数据（支持Excel/CSV）
df = pd.read_excel('预算表.xlsx')

# 拆分CBS代码为层级列
df['层级'] = df['CBS代码'].str.split('.')  # 替换为你的分隔符
max_depth = df['层级'].str.len().max()
for i in range(max_depth):
    df[f'Level_{i+1}'] = df['层级'].str[i]

# 计算总成本
total_cost = df['成本'].sum()
df['占总成本比例'] = df['成本'] / total_cost

# 计算父类成本与占比
for depth in range(1, max_depth):
    level_cols = [f'Level_{j+1}' for j in range(depth)]
    df[f'Level_{depth}_总成本'] = df.groupby(level_cols)['成本'].transform('sum')
    df[f'Level_{depth}_占比'] = df['成本'] / df[f'Level_{depth}_总成本']

2. 可视化输出 #

import plotly.express as px

# 一级类别占比饼图
fig1 = px.pie(df, names='Level_1', values='成本', title='一级预算类别占总成本比例')
fig1.show()

# 层级旭日图
fig2 = px.sunburst(df, path=['Level_1', 'Level_2', 'Level_3'], values='成本', title='预算层级成本结构')
fig2.show()

# 子类别成本对比条形图
fig3 = px.bar(df, x='Level_2', y='成本', color='Level_1', title='二级类别成本对比')
fig3.show()

3. 结果导出 #

# 将计算结果导出到Excel
df.to_excel('预算分析结果.xlsx', index=False)

内容的提问来源于stack exchange，提问作者Michael Yoo