用函数分配书籍

FindingB

工作簿1.rar (8.54 KB, 下载次数: 16)

2020-7-6 10:17 上传

点击文件名下载附件

三秋之叶

F2
=SUM((MID(E2,COLUMN($A:$C),1)=$A$2:$A$6)*$C$2:$C$6/$B$2:$B$6)

FindingB

三秋之叶 发表于 2020-7-6 10:30
F2
=SUM((MID(E2,COLUMN($A:$C),1)=$A$2:$A$6)*$C$2:$C$6/$B$2:$B$6)

如何提取分配方案。

cemmm

E2=IFERROR(CONCAT(T(OFFSET($A$1,MID(IFERROR(SMALL(IF((MID(ROW($123:$456),1,1)<MID(ROW($123:$456),2,1))*(MID(ROW($123:$456),2,1)<MID(ROW($123:$456),3,1))*(--MID(ROW($123:$456),3,1)<6),ROW($123:$456),""),ROW(A1)),""),ROW($A$1:$A$3),1),))),"")
F2=IFERROR(SUM(N(OFFSET($C$1,MID(IFERROR(SMALL(IF((MID(ROW($123:$456),1,1)<MID(ROW($123:$456),2,1))*(MID(ROW($123:$456),2,1)<MID(ROW($123:$456),3,1))*(--MID(ROW($123:$456),3,1)<6),ROW($123:$456),""),ROW(A1)),""),ROW($A$1:$A$3),1),))/N(OFFSET($B$1,MID(IFERROR(SMALL(IF((MID(ROW($123:$456),1,1)<MID(ROW($123:$456),2,1))*(MID(ROW($123:$456),2,1)<MID(ROW($123:$456),3,1))*(--MID(ROW($123:$456),3,1)<6),ROW($123:$456),""),ROW(A1)),""),ROW($A$1:$A$3),1),))),"")
F2就是两个相除，重复的太多了，不想弄了。F2用楼上的吧。

FindingB

cemmm 发表于 2020-7-6 11:07
E2=IFERROR(CONCAT(T(OFFSET($A$1,MID(IFERROR(SMALL(IF((MID(ROW($123:$456),1,1)

谢谢。提取的每组里必须包含“丙”，该如何同步解决呢？

cemmm

=IFERROR(CONCAT(T(OFFSET($A$1,MID(IFERROR(SMALL(IF((MID(ROW($123:$456),1,1)<MID(ROW($123:$456),2,1))*(MID(ROW($123:$456),2,1)<MID(ROW($123:$456),3,1))*(--MID(ROW($123:$456),3,1)<6)*((--MID(ROW($123:$456),1,1)=3)+(--MID(ROW($123:$456),2,1)=3)+(--MID(ROW($123:$456),3,1)=3)=1),ROW($123:$456),""),ROW(A1)),""),ROW($A$1:$A$3),1),))),"")