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

lhx120824

初中两校成绩统计模板（多表合并表定义）简化数据20101210
初中两校成绩统计模板（多表合并表定义）简化数据20101210.rar (773.23 KB, 下载次数: 1424)

2010-12-10 09:15 上传

点击文件名下载附件

为减小文件的大小，只选取了少量的数据，成绩也是模拟的，由于功能太多，除了四个原始数据的成绩表外，其余的全是公式得出的，因此打开和保存比较慢。
工作簿包含有：
6、7、8、9四个年级的原始成绩表（其中有总分、各种排名、等级转换（公式得出）等，）
合并表（用公式把4个年级的4各表和在一起，不用复制粘贴）
分率表（各年级、各校、各班的平均分、及格率、优秀率、标准分）
分段表（各年级、学校、班级的分数段，科目可以选择，分段标准可以调整）
教师表（中学任课教师表转科目班级表、各种分数分数段）
查询表（可查询学校、年级、班级个前任意名次人数的学生记录，并由各年级、班级的人数分布，各班的总分前x名的各科的平均分对照表）
设置表（各科的总分表、差距分表、标准分赋值表、单位信息表）
赋分表（把教师的平均分、标准分、优秀率、及格率按不同的标准进行赋分）

只可自己改造使用，勿外传，切记。
适用版本2010。

如有需要，在适当时机可详细解释使用方法和公式。

[ 本帖最后由 lhx120824 于 2010-12-10 09:18 编辑 ]

lhx120824

原帖由 tomxie 于 2010-12-10 09:23 发表
8 sheet 校次 你看一下前三个  好象有点小问题
看起来很漂亮 还是非常不错的

看清楚了，是不同学校的，bh的前两位81、82代表8年级1中、8年级2中的不同学校，因此校名次有两个第一。

lhx120824

体会列表的功能，欢迎大家参与讨论
首先声明版本用的是2010。
选定需要列表的区域后，执行【插入】【表格】【表包含标题】，就在名称定义中定义了一个表1，定义范围是刚才选定的区域。相应的标题行自行当名称使用。
先体会附件中的公式部分，然后我再继续解释。

列表.rar (29.82 KB, 下载次数: 239)

2010-12-10 14:55 上传

点击文件名下载附件

从i列到n列的公式，只需在每一列输入一个公式，回车后整列自动填充完成，相当于多单元格数组公式的功能，但不用选定所有的单元格。
其中总分的公式（在i2）
=SUM(表1[@[语文]:[物理]])
操作的方法是单击 i2单元格，然后执行【公式】【自动求和】回车后即可完成整列的公式自动填充。
也可以输入
=sum（
后鼠标选定d2至h2,即可自动变成
表1[@[语文]:[物理]])
然后加上右括号回车即可。
其余的公式在j2、k2、l2、m2、n2中分别输入回车即可完成整个表的填充操作。


帮助中提到的列表功能：

创建或删除工作表中的 Excel 表格
全部显示全部隐藏
在 Microsoft Excel 工作表 （工作表:在 Excel 中用于存储和处理数据的主要文档。也称为电子表格。工作表由排列成行或列的单元格组成。工作表总是存储在工作簿中。）中创建表格（以前称为列表）后，即可独立于该表格外部的数据对该表格中的数据进行管理和分析。例如，可以筛选表格列、添加汇总行、应用表格格式以及将表格发布到正在运行 Windows SharePoint Services 3.0 或 SharePoint Foundation 2010 的服务器上。



如果不再想处理表格中的数据，则可以将表格转换为常规数据区域 （区域:工作表上的两个或多个单元格。区域中的单元格可以相邻或不相邻。），同时保留所应用的任何表格样式。当不再需要表格及其包含的数据时，可以删除该表格。

注释   不要将 Excel 表格与模拟运算表 （模拟运算表:模拟运算表是一个单元格区域，用于显示在一个或多个公式中替换不同值所得到的结果。有两种类型的数据表:单输入表和双输入表。）混淆，后者是一套模拟分析命令。

您要做什么？

--------------------------------------------------------------------------------

创建表格
删除表格而不丢失数据或表格格式
删除表格及其数据

--------------------------------------------------------------------------------
创建表格
您可以使用以下两种方式中的一种创建表格：以默认表格样式插入表格，或者以所选的样式将数据的格式设置为表格。

使用默认表格样式插入表格
1.在工作表上，选择要包括在表格中的单元格区域。这些单元格可以为空，也可以包含数据。
2.在“插入”选项卡上的“表格”组中，单击“表格”。


键盘快捷方式  还可以按 Ctrl+L 或 Ctrl+T。

3.如果选择的区域包含要显示为表格标题的数据，请选中“表包含标题”复选框。
如果未选中“表包含标题”复选框，则表格标题将显示默认名称。可以通过以下方法来更改默认名称：选择要替换的默认标题，然后键入所需文本。

提示
创建表格后，“表工具”将变得可用，同时会显示“设计”选项卡。您可以使用“设计”选项卡上的工具自定义或编辑该表格。请注意，仅当选定表格中的某个单元格时才显示“设计”选项卡。
与 Office Excel 2003 中的列表不同，表格不包含用于快速添加新行的特殊行（用 * 标记）。
使用所选样式插入表格
1.在工作表中，选择空单元格区域或包含要快速将其格式设置为表格的数据的单元格区域。
2.在“开始”选项卡上的“样式”组中，单击“套用表格式”。


注释   当使用“套用表格格式”时，Excel 会自动插入一个表。

3.在“浅色”、“中等深浅”或“深色”下，单击要使用的表样式。如果选择的区域包含要显示为表格标题的数据，请单击包含标题行的表格样式。
注释   创建一个或多个自定义表格样式后，这些自定义表格样式将显示在“自定义”下。

提示
创建表格后，如果选定表格中的某个单元格，则“表工具”将变得可用，同时会显示“设计”选项卡。您可以使用“设计”选项卡上的工具自定义或编辑该表格。
与 Excel 2003 中的列表不同，表格不包含用于快速添加新行的特殊行（用 * 标记）。
返回页首

删除表格而不丢失数据或表格格式
创建表格之后，您可能不想继续使用该软件所带的表格功能。或者，您可能需要一种表格样式，但无需表格功能。若要停止处理表格数据而又不丢失所应用的任何表格样式格式，可以将表格转换为工作表上的常规数据区域。

1.单击表格中的任意位置。
提示   将显示“表工具”，其上增加了“设计”选项卡。必须选定表格中的某个单元格才能显示“设计”选项卡。

2.在“设计”选项卡上的“工具”组中，单击“转换为区域”。


注释   将表格转换回区域后，表格功能将不再可用。例如，行标题不再包括排序和筛选箭头，而在公式中使用的结构化引用（使用表格名称的引用）将变成常规单元格引用。

提示
还可以右键单击表格，指向“表格”，然后单击“转换为区域”。
还可以在创建表格后立即单击“快速访问工具栏”上的“撤消” ，将该表格转换回区域。
返回页首

删除表格及其数据
如果不再需要使用表格及其包含的数据，则可以删除该表格。

1.在工作表上，选择相应的表格。
提示   按 Ctrl+A 两次会选中整个表，包括表标题。

2.按 Delete。
提示   还可以单击“快速访问工具栏”上的“撤消” 来删除您刚创建的表格。

lhx120824

o列的多单元格数组公式MMULT(）  不能插入列表中，如果是单单元格公式放入o2中回车，则此列自动扩展到表中。
=MMULT(N(表1[总分]-(ROW()-1)/1000<=TRANSPOSE(表1[总分]-(ROW()-1)/1000)),ROW(表1[总分])^0)

如果在A到H列中追加学生成绩的话，G到N列的公式则自动填充。
如果删除部分行，则表的范围自动缩小。注意表的右下角的标记。
即2010中行列自动扩充。
多单元格数组公式则不允许删除行。






以上的操作是输入公式后回车，系统自动填充。
其中的
表1[[语文]:[总分]]
是托选单元格的效果。

只定义一个表，表的标题行字段就可以用作名称，不用再次定义，比如引用整个表的语文成绩列，就可写成
表1[语文]
可在整个工作簿中使用，如果用多表定义，（不同的工作表中），引用时只需变成
表x[[语文]
不用加代表工作表的“！”
具体应用参见帖子的373楼的附件
教育专业贴：（有成绩统计、分班、考场混排、课表编排等研究过程和结论）20101210更新

[ 本帖最后由 lhx120824 于 2010-12-10 14:58 编辑 ]

shoulu2010

路过收藏，楼主很用心

回头客哟

不好意思，刚挤进来，原来里面全是高手！

xiangjm123

认真学习楼主的教程，感谢楼主的分享，我是一位小学的教导主任。看到这么多的好东西，我流泪了。太感谢大家的分享！这样，使我们的工作效率将提高不知道多少倍！再次表示感谢！

借我一世红尘

感谢楼主无私分享

红尘居士

楼主太强了, ,向楼主致敬,好实用

lhx120824

从“成绩总表”通过函数统计出各科满分的学生的两种方法

其中查找科目的公式是：
=IF(ROW(1:676)>COUNTIF(成绩总表!C2:E677,"=100"),"",CHOOSE(RIGHT(SMALL((IF((成绩总表!C2:E677=100)*ROW(1:676),ROW(1:676),4^8)+1)+COLUMN(A:C)/10,ROW(1:676))),"语文","数学","英语"))
或
=INDEX(成绩总表!$C$1:$E$1,,SMALL(IF((成绩总表!$B$2:$B$342=B2)*(成绩总表!$C$2:$E$342=100),{1,2,3}),COUNTIF($B$2:B2,B2)))




参考http://club.excelhome.net/thread-660336-2-1.html

[ 本帖最后由 lhx120824 于 2010-12-12 00:20 编辑 ]