北京
乾学院 53 号会员
2 回帖  •  636 浏览  • 
     ...

在包含分类汇总的表格右边拼上对应的子类占比

 
excel(61) 转置(21) 条件分组(6)

例题描述和简单分析

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单元格，即可完成计算。

问答搜集

https://stackoverflow.com/questions/63828183/dynamic-nested-excel-formula


excel(61) 转置(21) 条件分组(6)