含多值单元格的表格计数与数据透视后续问题咨询
多值拆分讲师的课程授课数交叉透视实现方案
核心处理逻辑分四步:
- 前置去重:先按「课程名+课程日期」对原始记录去重,避免同一门课因多个学生报名被重复计数,保证同一日期的同一门课只计1次授课记录
- 多值拆分:把去重后的Instructor列里以斜杠分隔的多讲师内容拆为独立行,每一行保留原记录的课程、日期绑定关系,避免拆分后数据错位
- 交叉计数:基于拆分后的数据,统计每门课程对应不同讲师的授课次数
- 格式对齐:补充讲师维度的汇总行,匹配要求的统计表结构
Excel/Power Query 操作路径
- 选中原始数据区域,依次点击「数据」选项卡 >「从表格/区域」,将数据加载到Power Query编辑器
- 同时选中
Class和Course Date两列,依次点击「主页」选项卡 >「删除行」>「删除重复项」,完成同课程同日期的记录去重 - 选中Instructor列,依次点击「转换」选项卡 >「拆分列」>「按分隔符」,自定义分隔符输入
/,拆分规则选择「每次出现分隔符时拆分」,高级选项设置拆分为「行」,确认后即可完成多讲师的行拆分 - 拆分完成后,同时选中Class和Instructor两列,点击「转换」选项卡 >「分组依据」,按两列分组,新建「授课次数」列,操作选择「计数行」
- 将分组结果加载回Excel后插入数据透视表,将Class字段拖入行区域、Instructor字段拖入列区域、授课次数字段拖入值区域,开启透视表「列总计」功能,调整列顺序即可得到目标格式
Python Pandas 代码实现
import pandas as pd # 加载原始数据,可根据实际数据源替换读取方式 df = pd.read_csv("原始数据.csv") # 先按课程+日期去重,避免同一门课因多学生报名被重复计数 df_dedup = df.drop_duplicates(subset=['Class', 'Course Date']) # 将斜杠分隔的讲师列拆分为独立行 df_exploded = df_dedup.assign(Instructor=df_dedup['Instructor'].str.split('/')).explode('Instructor') # 生成交叉透视表,自动填充0值、追加总计行 pivot_result = pd.crosstab( index=df_exploded['Class'], columns=df_exploded['Instructor'], margins=True, margins_name='Total' ) # 按目标列顺序调整排列 pivot_result = pivot_result[['Jake', 'Ashley', 'Total']] print(pivot_result)
运行代码后可直接输出匹配要求的统计结果,无需额外格式调整。
内容的提问来源于stack exchange,提问作者John
相关产品推荐
相关产品推荐

