原始数据:
- 部门与职位存在于同一列中
实现功能:
- 根据筛选条件,可对部门或职位进行统计汇总
- 第一列列名根据筛选自动变更,显示当前统计的维度
实现方式:
1. 构建筛选器内容
- 在任意空白单元格内输入需要筛选的内容
2. 插入"组合框"控件,并进行相应设置
- 从开发工具选项卡中插入"组合框"控件
- 右键"组合框控件",选择"设置控件格式"
- 数据源区域 选取 第1步输入的筛选内容(红框区域)
- 单元格链接 选取任意空白单元格,用于返回筛选内容的位置索引(绿色区域)
3. 设置公式,获取筛选器所选择的值
= INDEX(Z1:Z2,W1)
- X1 单元格为设置公式的单元格,结果如图
4. 对筛选索引值(W1),筛选内容(X1)定义名称
- 选中要设置的单元格,选择公式栏下的"定义名称"
- 在名称中输入相应名称即可(此例W1定义为"idx",X1定义为"dept")
5. 导入原始表
6.获取筛选索引值
- 由于powerquery中,数据索引是从0开始,故将原先W1中的值-1
维度索引 = Excel.CurrentWorkbook(){[Name="idx"]}[Content][Column1]{0} - 1
7. 获取筛选内容
dept = Excel.CurrentWorkbook(){[Name="dept"]}[Content][Column1]{0}
8. 分组汇总
8.1 按筛选索引分组(主逻辑)
分组汇总 = Table.Group(更改的类型,
"部门职位", //按部门职位字段分组
{"xx",each _}, //汇总后不做任何操作
1, // 全局模式
(x,y) => Comparer.OrdinalIgnoreCase(Text.Split(x,"_"){维度索引} , Text.Split(y,"_"){维度索引}))
- x代表当前行,y代表当前行后面的行
- 即对部门职位列按"_"进行分组,按维度索引提取对应内容,对比每行内容是否一致,以确定最终分组
- 图中已按职位进行了相应分组
8.2 分组汇总细节构建
分组汇总 = Table.Group(更改的类型,
"部门职位",
{"xx",each
Table.ReorderColumns( //数据位置重排
#table({"部门职位","人数",dept},{{null,null,Text.Split(_[部门职位]{0},"_"){维度索引}}}) //拼接部门行
& Table.AddColumn(_,dept,each null) //增加统计维度列
& #table({"部门职位","人数",dept},{{"小计",List.Sum(_[人数]),null}}), //拼接小计行
{dept,"部门职位","人数"}
)
},
1,
(x,y) => Comparer.OrdinalIgnoreCase(Text.Split(x,"_"){维度索引} , Text.Split(y,"_"){维度索引}))[xx] //深化处理后的列
- 通过字段添加,表合并,数据位置重排等方式构建细节汇总项目
9. 合并表数据
合并表 = Table.Combine(分组汇总)
10.发布上载至页面