例题描述和简单分析 Excel中有序存放着多种部件及其金属子部件,当Level=2时,表示该行为部件(汇总行),当Level=3时,表示该行为子部件。Material列和Proportion列分别存放子部件的金属名称和占比。如下所示: | A | B | C | D | 1 | Name | Level | Proportion | Material | 2 | Part 1 | 2 | | | 3 | Part 1 A | 3 | 0.37 | Ally | 4 | Part 1 B | 3 | 0.40 | Ally | 5 | Part 1 C | 3 | 0.04 | Copper | 6 | Part 1 D | 3 | 0.01 | Titainium | 7 | Part 1 E | 3 | 0.04 | Steel | 8 | Part 1 F | 3 | 0.07 | Titainium | 9 | Part 1 G | 3 | 0.07 | Copper | 10 | Part 2 | 2 | | | 11 | Part 2 A | 3 | 0.50 | Steel | 12 | Part 2 B | 3 | 0.50 | Ally |
现在要在部件(汇总行)的右侧拼上每种金属的占比,如下所示: | A | B | C | D | E | F | G | H | 1 | Name | Level | Proportion | Material | Ally | Copper | Steel | Titainium | 2 | Part 1 | 2 | | | 0.77 | 0.11 | 0.04 | 0.08 | 3 | Part 1 A | 3 | 0.37 | Ally | | | | | 4 | Part 1 B | 3 | 0.40 | Ally | | | | | 5 | Part 1 C | 3 | 0.04 | Copper | | | | | 6 | Part 1 D | 3 | 0.01 | Titainium | | | | | 7 | Part 1 E | 3 | 0.04 | Steel | | | | | 8 | Part 1 F | 3 | 0.07 | Titainium | | | | | 9 | Part 1 G | 3 | 0.07 | Copper | | | | | 10 | Part 2 | 2 | | | 0.5 | | 0.5 | | 11 | Part 2 A | 3 | 0.50 | Steel | | | | | 12 | Part 2 B | 3 | 0.50 | Ally | | | | |
上述算法涉及条件分组、转置、补足空行,以及汇总和明细混合数据的处理办法。 解法及简要说明选中Excel片区A1:E12,复制到集算器脚本的A1单元格内,接下来编写如下脚本: | A | B | 1 | …(复制来的数据) | | 2 | =A1.import@t() | | 3 | =A2.group@i(Level==2) | | 4 | =A3.(~.to(2,).groups(Material;sum(Proportion):value,count(1):rowcount)) | | 5 | =A2.id(Material).select(~) | | 6 | =create(${A5.string()}) | | 7 | for A4 | =A7.align(A5,Material) | 8 | | =A6.record(B7.(value)) | 9 | | =A6.insert(0:B7.sum(rowcount)) |
A3:将每个部件及其子部件分到同一组。 A4:对于每组数据,先取子部件,再按金属名称进行二级组,算出每种金属的百分比含量和行数。 A5:对金属名称去除重复,并过滤掉空值 A6:建立空二维表,列名为将来要输出的金属名称。 A7:循环A4中的每一组,先将该组数据按将来要输出的金属名称对齐,再在A6按顺序写入各金属的占比,最后追加与子部件数量相等的空行。 执行上述脚本,连同列名复制A6的计算结果,再粘贴到Excel的E1单元格,即可完成计算。
|