|
本帖最后由 delete_007 于 2014-10-21 14:28 编辑
不知道有没有理解错题目,每分都做了!应该能拿一分吧!1分答卷:在附件中,E14:E31为辅助列
2分答卷:数组下拉
- {=SUM(LOOKUP(MMULT(N(B$14:B$31&D$14:D$31<=TRANSPOSE((MATCH(B$14:B$31&D$14:D$31,B$14:B$31&D$14:D$31,)=A$14:A$31)*B$14:B$31&D$14:D$31)),A$14:A$31^0)-CHOOSE(B$14:B$31,9,5,0),B$9:G$10)*(C$14:C$31=G24))}
复制代码 3分答卷:H24:H29多单元格数组
- {=MMULT(N(G24:G29=TRANSPOSE(C14:C31)),LOOKUP(MMULT(N(B14:B31&D14:D31<=TRANSPOSE((MATCH(B14:B31&D14:D31,B14:B31&D14:D31,)=A14:A31)*B14:B31&D14:D31)),D14:D31^0)-CHOOSE(B14:B31,9,5,0),B9:G10))}
复制代码
内存数组公式可改为:=MMULT(N(G24:G29=TRANSPOSE(C14:C31)),LOOKUP(MMULT(N(B14:B31+D14:D31%%<=TRANSPOSE((MATCH(B14:B31&D14:D31,B14:B31&D14:D31,)=A14:A31)*D14:D31%%+B14:B31)),D14:D31^0)-CHOOSE(B14:B31,12,6,0),B9:G10))
——delete_007
|
本帖子中包含更多资源
您需要 登录 才可以下载或查看,没有账号?免费注册
x
评分
-
1
查看全部评分
-
|