教育应用贴（分班、统计、课表等）1142楼添加运动会成绩统计表20121215

lhx120824 · 发表于 2011-1-28 17:00

用offset解决每考场的排的人数累计问题：
用公式：
=SUM(OFFSET($C$2,ROW(1:8),,1,COLUMN(1:6)))
计算表中的每行的向右的列累计，下图的左半部分：
每个考场列累计再与上一考场的
场累计
=SUM(OFFSET($B$2,1,,ROW(1:8),7))
求和，就得到下图的右半部分。
=SUM(OFFSET($B$2,ROW(1:8),,1,COLUMN(1:6)))+SUM(OFFSET($B$2,,,ROW(1:8),7))

场累计2.gif

[ 本帖最后由 lhx120824 于 2011-1-28 17:48 编辑 ]

lhx120824 · 发表于 2011-1-28 17:01

用mmlt解决每考场的排的人数累计问题。
公式为：
=MMULT(C3:H10,--(ROW(1:6)<=COLUMN(A:F)))
下图的左边表。
但第一排还没参于累计，应用公式：
=MMULT(C3:H10,--(ROW(1:6)<COLUMN(A:F)))
（下图中间）
Q列的场累计：（692楼的Q列）
=MMULT(1*(ROW(1:8)>=TRANSPOSE(ROW(1:8))),MMULT(C3:H10,ROW(1:6)^0))
同样需要下移一行
=MMULT(1*(ROW(1:8)>TRANSPOSE(ROW(1:8))),MMULT(C3:H10,ROW(1:6)^0))
求和公式变成：
=MMULT(C3:H10,--(ROW(1:6)<COLUMN(A:F)))+MMULT(--(ROW(1:8)>COLUMN(A:H)),MMULT(C3:H10,ROW(1:6)^0))
（下图右边）

场累计3.gif

[ 本帖最后由 lhx120824 于 2011-1-28 18:09 编辑 ]

shaowu459 · 发表于 2011-1-28 17:07

=SUBTOTAL(9,OFFSET($C$3:$H$3,,,ROW(1:8),))-SUBTOTAL(9,OFFSET(OFFSET($I2,ROW(1:8),),,COLUMN(A:F)-7,,6))+(COLUMN(C3:H10)=8)*(ROW(C3:H10)=10)*H3:H10

生成内存数组结果，不再是虚假的内存数组结果（能在多单元格数组公式中正确运行，但是按F9无法出来结果，也就无法再其他公式中直接引用）

lhx120824 · 发表于 2011-1-28 18:21

原帖由 shaowu459 于 2011-1-28 17:07 发表
=SUBTOTAL(9,OFFSET($C$3:$H$3,,,ROW(1:8),))-SUBTOTAL(9,OFFSET(OFFSET($I2,ROW(1:8),),,COLUMN(A:F)-7,,6))+(COLUMN(C3:H10)=8)*(ROW(C3:H10)=10)*H3:H10

生成内存数组结果，不再是虚假的内存数组结果（能在多单 ...

是的，经测试，
691、692楼的 sum  和 696楼的  =SUBTOTAL(9,OFFSET(
在参与其他公式的计算时容易出现错误。
只有695楼的函数
MMULT
才能再参与到其他公式中进行运算。
为了能使公式进行扩展，把引用区域进行定义：

考场=Sheet1!$C$3:$H$10
相应的公式
=MMULT(C3:H10,--(ROW(1:6)<COLUMN(A:F)))+MMULT(--(ROW(1:8)>COLUMN(A:H)),MMULT(C3:H10,ROW(1:6)^0))
进行修改为：
=MMULT(考场,--(ROW(INDIRECT("1:"&COLUMNS(考场)))<TRANSPOSE(ROW(INDIRECT("1:"&COLUMNS(考场))))))+MMULT(--(ROW(INDIRECT("1:"&ROWS(考场)))>TRANSPOSE(ROW(INDIRECT("1:"&ROWS(考场))))),MMULT(考场,ROW(INDIRECT("1:"&COLUMNS(考场)))^0))

公式中的：
ROW(1:6）改为    ROW(INDIRECT("1:"&COLUMNS(考场))
COLUMN(A:F)  改为  TRANSPOSE(ROW(INDIRECT("1:"&COLUMNS(考场))))
ROW(1:8) 改为 ROW(INDIRECT("1:"&ROWS(考场))
COLUMN(A:H) 改为  TRANSPOSE(ROW(INDIRECT("1:"&ROWS(考场))))

也就是说，由名称考场来确定公式中引用的行数和列数。

体会sum+offset及mmlt函数在排考场中的作用 .rar (30.75 KB, 下载次数: 276)

[ 本帖最后由 lhx120824 于 2011-1-29 09:47 编辑 ]

lhx120824 · 发表于 2011-1-28 20:22

原帖由 shaowu459 于 2011-1-28 17:07 发表
=SUBTOTAL(9,OFFSET($C$3:$H$3,,,ROW(1:8),))-SUBTOTAL(9,OFFSET(OFFSET($I2,ROW(1:8),),,COLUMN(A:F)-7,,6))+(COLUMN(C3:H10)=8)*(ROW(C3:H10)=10)*H3:H10

生成内存数组结果，不再是虚假的内存数组结果（能在多单 ...

谢谢shaowu459的参与，你给我提供了很好的思路，然后我才敢把大家的智慧进行消化总结，分享给大家。

shaowu459 · 发表于 2011-1-29 00:15

696楼的公式可以直接生成内存数组，可以直接参与其他函数的运算，当做中间参数的。见下图：

lhx120824 · 发表于 2011-1-29 09:23

原帖由 shaowu459 于 2011-1-29 00:15 发表
696楼的公式可以直接生成内存数组，可以直接参与其他函数的运算，当做中间参数的。见下图：

看来要好好研究研究，多加体会，不能轻易下结论。

lhx120824 · 发表于 2011-1-29 11:04

现征集多表平均分、优秀率、分数段的统计方案。
征求多表平均分、优秀率的统计方案20110119（方法不限）
http://club.excelhome.net/thread-678405-1-1.html

征求多表得分分数段（等级）的统计方案20110119（方法不限）
http://club.excelhome.net/viewth ... &extra=page%3D1

稍后进行总结分享。

[ 本帖最后由 lhx120824 于 2011-1-29 11:06 编辑 ]

lhx120824 · 发表于 2011-1-30 18:10

关于分数段的方法之一，山菊花版主给了一种方法，现提供如下：

lhx120824_征求多表分数段的统计方法20110119.zip (99.46 KB, 下载次数: 193)


1、自定义名称
      根据A列前两位数，引用对应工作表数据，为简化公式，固定引用每列的2:1000行数据。
            编号       =INDIRECT(LEFT(统计!$A5,2)&"!a2:a1000")
            考号       =INDIRECT(LEFT(统计!$A9,2)&"!b2:b1000")
            成绩       =OFFSET(INDIRECT(LEFT(统计!$A6,2)&"!b2:b1000"),,MATCH(统计!$C$1,INDIRECT(LEFT(统计!$A6,2)&"!c1:k1"),))
      按年级、科目引用满分值：
            满分       =VLOOKUP(LEFT(统计!$A11,2),预设!$A$4:$K$7,MATCH(统计!$C$1,预设!$A$3:$K$3,),)


2、计算考试人数
      B3=SUMPRODUCT((LEFT(编号,LEN(A3))=A3)*ISNUMBER(考号))


3、计算各分数段人数
      A、计算95%以上人数
            C3=SUMPRODUCT((LEFT(编号,LEN($A3))=$A3)*ISNUMBER(考号)*(成绩/满分>=0.95))
      B、计算D:J人数
            D3=SUMPRODUCT((LEFT(编号,LEN($A3))=$A3)*ISNUMBER(考号)*(成绩/满分>=N(D$2)))-SUM($C3:C3)

版主的B、公式也可以改为：
   D3=SUMPRODUCT((LEFT(编号,LEN($A3))=$A3)*ISNUMBER(考号)*(成绩/满分>=N(D$2))*(成绩/满分<N(C$2)))

[ 本帖最后由 lhx120824 于 2011-1-31 22:20 编辑 ]

lhx120824 · 发表于 2011-1-30 18:20

准备改进成按年级进行分数段的统计表。

用数据有效性进行年级和科目选择的方法进行查询统计，统计公式用FREQUENCY。

多表分数段的统计选择年级科目（用FREQUENCY）.rar (78.28 KB, 下载次数: 289)

把4个年级的成绩表分别用插入表表包含标题行进行名称定义。然后再定义2个名称：
得分：
=OFFSET(INDIRECT("表"&LEFT(年级统计!$A3,2)&"[考号]"),,MATCH(年级统计!$C$1,INDIRECT("表"&LEFT(年级统计!$A3,2)&"[#标题]"),)-2)
满分：
=VLOOKUP(LEFT(!$A3,2),预设!$A$4:$K$7,MATCH(!$C$1,预设!$A$3:$K$3,),)

则公式为
c3:k3=IFERROR(TRANSPOSE(FREQUENCY(IF((LEFT(INDIRECT("表"&LEFT($A3,2)&"[编号]"),LEN($A3))=$A3)*ISNUMBER(INDIRECT("表"&LEFT($A3,2)&"[考号]")),得分/满分,""),TRANSPOSE($C$2:$J$2))),"")

[ 本帖最后由 lhx120824 于 2011-1-30 21:53 编辑 ]

		自动登录	找回密码
密码			免费注册

[分享] 教育应用贴（分班、统计、课表等）1142楼添加运动会成绩统计表20121215