求大佬帮忙看看这个课表

栗子老湿

要求分别填充出，教师名，课头数
     教师名要求填充出不重复的
      课头数要求根据教师名查找这个老师带的有几门课（比如一个老师带多节体育课，则课头数只加一）
           教师用删除重复值解决了，但考虑到以后可能会加新课所以想问问有没有函数能做出个动态的，课头数是真搞不明白

1007890405

建议老师，上PQ区问一下，

良良

A2

1. =IFERROR(INDEX(T(INDIRECT("'练习四素材（"&INT(ROW($300:$1199)/300)&")'!F"&MOD(ROW($300:$1199),300)+1)),SMALL(IF((MMULT(N(T(INDIRECT("'练习四素材（"&INT(ROW($300:$1199)/300)&")'!F"&MOD(ROW($300:$1199), 300)+1))<>{"任课教师","-",""}),{1;1;1})=3)*(MATCH(T(INDIRECT("'练习四素材（"&INT(ROW($300:$1199)/300)&")'!F"&MOD(ROW($300:$1199),300)+1)),T(INDIRECT("'练习四素材（"&INT(ROW($300:$1199)/300)&")'!F"&MOD(ROW($300:$1199),300)+1)),)=ROW($1:$900)),ROW($1:$900)),ROW(A1))),"")

复制代码

B2

1. =COUNT(IF((MATCH(IF(T(INDIRECT("'练习四素材（"&INT(ROW($300:$1199)/300)&")'!F"&MOD(ROW($300:$1199),300)+1))=A2,T(INDIRECT("'练习四素材（"&INT(ROW($300:$1199)/300)&")'!D"&MOD(ROW($300:$1199),300)+1)),""),IF(T(INDIRECT("'练习四素材（"&INT(ROW($300:$1199)/300)&")'!F"&MOD(ROW($300:$1199),300)+1))=A2,T(INDIRECT("'练习四素材（"&INT(ROW($300:$1199)/300)&")'!D"&MOD(ROW($300:$1199),300)+1)),""),)=ROW($1:$900))*(IF(T(INDIRECT("'练习四素材（"&INT(ROW($300:$1199)/300)&")'!F"&MOD(ROW($300:$1199),300)+1))=A2,T(INDIRECT("'练习四素材（"&INT(ROW($300:$1199)/300)&")'!D"&MOD(ROW($300:$1199),300)+1)),"")<>""),ROW($1:$900)))

复制代码

C2

1. =SUM(IF(T(INDIRECT("'练习四素材（"&INT(ROW($300:$1199)/300)&")'!F"&MOD(ROW($300:$1199),300)+1))=A2,N(INDIRECT("'练习四素材（"&INT(ROW($300:$1199)/300)&")'!E"&MOD(ROW($300:$1199),300)+1))))

复制代码

均为数组三键。

zpy2

select f08 姓名,count(distinct(f06)) 课头数,sum(f07) 课时数,group_concat(distinct(f06)) from consolidateSheet where f06 not like '课程名称' group by f08 order by rowid;


https://club.excelhome.net/forum.php?mod=viewthread&tid=1664606&mobile=

ericxzhou