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

lhx120824 · 发表于 2011-1-15 15:29

原帖由 huerhy 于 2011-1-15 11:10 发表
衷心谢谢楼主的指导和帮助！
因为是很菜的菜鸟，只能说一些想要的结果，但对公式一点都不懂。可能是我数学没学好，总是弄不明白公式的结构和含义，十分惭愧！
越来越觉得EXCEl的强大，简直有点儿望而却步了，要从那 ...

慢慢来，不要泄气，我也是从菜鸟过来的，只要有需要，就有动力，总会提高的。

lyq871015 · 发表于 2011-1-15 22:06

您好！楼主！例子里面的效果的确是我想要的。非常感谢！

可是手动选择这个可能对于只有10来个班号的时候还能适用。可我有几十个、100个多的时候查询的速度可能就过慢了。有办法是“手动输入班号－回车－显示结果”这样么？

盼回复！

lhx120824 · 发表于 2011-1-15 23:05

原帖由 lyq871015 于 2011-1-15 22:06 发表
您好！楼主！例子里面的效果的确是我想要的。非常感谢！

可是手动选择这个可能对于只有10来个班号的时候还能适用。可我有几十个、100个多的时候查询的速度可能就过慢了。有办法是“手动输入班号－回车－显示结果” ...

原因在于我的公式中用了ROW(1:10)),"")
把公式=IFERROR(SMALL(IF($B$1=表1[班号],表1[通知日期],""),ROW(1:10)),"")
中的
ROW(1:10)),"")
扩大至1：1000甚至更多1：100000
才能使用更大范围，因为表中数据源已定义为表，公式不用修改。

[ 本帖最后由 lhx120824 于 2011-1-15 23:07 编辑 ]

河东虎 · 发表于 2011-1-15 23:14

楼主，你能否把附件中的教务工作管理各个试室按“座位特征2”安排？这样就把各试室的不规则安排给完成了。

lhx120824 · 发表于 2011-1-16 04:35

原帖由 河东虎 于 2011-1-15 23:14 发表
楼主，你能否把附件中的教务工作管理各个试室按“座位特征2”安排？这样就把各试室的不规则安排给完成了。

看不懂你的 “座位特征2” 是什么？
工作表是否太多?
可参考本贴的451楼。

lhx120824 · 发表于 2011-1-16 11:18

585楼表转换的总结：

规则表转换总结.rar (24.65 KB, 下载次数: 127)
下图中分几种情况：（为了增加扩展性和与单元格的位置无关性，特定义名称
表=七年级任课表!$A$2:$L$18
公式中尽量避开随意选定的数值。

一、单列分别得出公式：
O3=OFFSET($A$2,MOD(ROW(A1)-1,ROWS(表)-1)+1,,) 下拉。
或
O3=INDEX(表,MOD(ROW(A2)-1,ROWS(表)-1)+2,1)  下拉。

P3=OFFSET($A$2,,(ROW(A1)-1)/(ROWS(表)-1)+1)
或
P3=INDEX(表,1,(ROW(A1)-1)/(ROWS(表)-1)+2)

Q3=INDEX(表,MATCH(O3,$A$2:$A$18,0),MATCH(P3,$A$2:$L$2,0))
或
Q3=OFFSET($A$2,MOD(ROW(C1)-1,ROWS(表)-1)+1,(ROW(C1)-1)/(ROWS(表)-1)+1)

二、可右拖下拉的：
=OFFSET($A$2,MOD(COLUMN(),2)*(MOD(ROW(A16),16)+1),(COLUMN()>15)*ROW(A16)/16) 15和16与存放位置和表的行数有关。

=OFFSET($A$2,MOD(COLUMN(A:A),2)*(MOD(ROW(A1)-1,ROWS(表)-1)+1),(COLUMN(A:A)>1)*((ROW(A1)-1)/(ROWS(表)-1)+1))    与存放位置和表的行数无关。


三、改编成多单元格数组公式的：
=INDEX($A$2:$L$18,(MOD(ROW(1:200)-1,16)+2)*(MOD(COLUMN($A:$L),2)),(COLUMN($A:$L)>1)*((ROW(1:200)-1)/16+2))    16和200与表有关。

=INDEX(表,(MOD(ROW(1:200)-1,ROWS(表)-1)+2)*(MOD(COLUMN(表),2)),(COLUMN(表)>1)*((ROW(1:200)-1)/(ROWS(表)-1)+2))    只考虑选定区域的行数（200与表单元格个数有关）

（参考 piny和  shaowu459 提供的公式改编

不规则表的转换待续。

数组16.gif

[ 本帖最后由 lhx120824 于 2011-1-16 13:04 编辑 ]

shaowu459 · 发表于 2011-1-16 15:44

供参考，一种按照课程排列，一种按照班号排列。比较马虎，现在这个应该是可以用的……原来发的都有若干瑕疵……

[ 本帖最后由 shaowu459 于 2011-1-16 16:51 编辑 ]

lhx120824 · 发表于 2011-1-16 18:49

原帖由 shaowu459 于 2011-1-16 15:44 发表
供参考，一种按照课程排列，一种按照班号排列。比较马虎，现在这个应该是可以用的……原来发的都有若干瑕疵……

不规则表转换：
一、单列分别用公式：
O3=INDEX(A:A,MOD(SMALL(IF(B3:L18<>"",ROW(A3:A18)+COLUMN($B:$L)*1000),ROW(1:1000)),1000))  多单元格数组公式
或：
O3=INDEX(A:A,RIGHT(SMALL(IF(B3:L18<>"",ROW(A3:A18)+COLUMN($B:$L)*1000),ROW(1:1000)),3))  多单元格数组公式

或
O3=INDIRECT(TEXT(MOD(SMALL(IF($B$3:$L$18<>"",ROW($3:$18)+COLUMN($B:$L)*1000),ROW(A1)),1000),"r0c!1"),)  下拉

P3=INDEX($2:$2,INT(SMALL(IF(B3:L18<>"",ROW(A3:A18)+COLUMN($B:$L)*1000),ROW(1:1000))/1000)) 多单元格数组公式

或
P3=INDIRECT(TEXT(INT(SMALL(IF($B$3:$L$18<>"",ROW($3:$18)+COLUMN($B:$L)*1000),ROW(A1))/1000),"r!2c000"),)  下拉。

Q3=INDEX($B$3:$L$18,MATCH($W3,A:A,0)-2,MATCH($X3,$A$2:$K$2,0)-1)  下拉。
或
Q3=INDIRECT(TEXT(MMULT(--MID(TEXT(SMALL(IF($B$3:$L$18<>"",ROW($3:$18)+COLUMN($B:$L)*1000),ROW(A1)),"00000"),{1,3},{2,3}),{1;1000}),"r0c000"),) 下拉。

二、一个公式右拖下拉：(shaowu459 在610楼提供）
O3=INDIRECT(INDEX(TEXT(MID(RIGHT(SMALL(IF($B$3:$L$18<>"",COLUMN($B:$L)+ROW($3:$18)*1000)+IF($B$3:$L$18<>"",(ROW($1:$16)+(COLUMN($A:$K)-1)*16)*10^8*COLUMN($A:$K),10^30),ROW(A1)),5),{1,3,1},{2,3,5}),{"R0C1","R2C0","R0C000"}),COLUMN(A1)),)
或
O3=INDIRECT(INDEX(TEXT(MID(TEXT(SMALL(IF($B$3:$L$18<>"",COLUMN($B:$L)+ROW($3:$18)*1000),ROW(A1)),"00000"),{1,3,1},{2,3,5}),{"R0C1","R2C0","R0C000"}),COLUMN(A1)),)

不规则表转换总结.rar (36.42 KB, 下载次数: 122)

[ 本帖最后由 lhx120824 于 2011-1-16 19:01 编辑 ]

河东虎 · 发表于 2011-1-16 21:02

楼主能否对451楼的考场设置写个使用说明，即先输入哪个单元格内容，再输入哪个单元格内容。

angellbxsc · 发表于 2011-1-16 21:03

楼主的发起组织很好，这种方法也可以推广到其他领域，大家集思广益。充分利用好excel这个比较好用的工具。
赞一个。

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

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

回复 480楼 lhx120824 的帖子