用excel制作自动办公系统.docx

用excel制作自动办公系统.docx

《用excel制作自动办公系统.docx》由会员分享，可在线阅读，更多相关《用excel制作自动办公系统.docx（58页珍藏版）》请在冰豆网上搜索。

用excel制作自动办公系统

第一章基础知识

Excel具有非常强大的数据计算功能，是日常工作中经常用到的数据处理软件。

现介绍Excel的常用基本知识。

1.1Excel的基本构成

Excel工作平台分工作薄和工作表。

一个Excel文件就是一个工作薄，在一个工作薄下有多个工作表。

如图1.1所示中的sheet1,sheet2等都是工作薄Book1中的工作表。

在工作表的上方有菜单栏和工具栏，工具栏是操作更快捷的必要选择，读者务必熟悉工具栏的使用。

读者可以自行熟悉菜单栏和工具栏的功能，下方有工作表sheet1、sheet2……。

工作表可以根据自己的需要添加。

方法是用鼠标指向工作表名（如sheet1）点击右键，选择插入，在对话框中选择“工作表”，单击确定，即插入了一个新的工作表。

单元格是Excel工作表非常重要的基本元素。

如图1.1中的黑方框就是一个单元格。

它是运算或引用的重要元素。

单元格的定位，分行和列两个指标。

列标为123……，行标为ABC……，如图1.1中黑方框单元格的位置表述为A1。

1.2单元格的引用

常用的单元格引用分表内引用和表间引用两种。

单元格引用不能在同一单元中引用自己，如在A1单元格中引用A1单元格。

引用分相对引用和绝对引用。

相对引用时Excel会根据自动填充公式时的操作自动改变单元格的行标和列标。

绝对引用时Excel不会根据自动填充公式时的操作自动改变单元格的行标和列标。

1.2.1表内引用

1.表内相对引用单元格

如图1.1所示，在B2单元格中引用单元格A1，方法是：

在B2单元格中输入=A1。

将鼠标指针指向B2右下角方黑点，它将变为+型，按住鼠标左键向右拖动鼠标，Excel将自动向右填充相应单元格B1C1D1……中的数据。

横向拉动鼠标，将改变填充列标，纵向拉动时，将改变填充行标。

2.表内绝对引用单元格

如图1.1所示，在表B2单元格中绝对引用单元格A1，方法是：

在B2单元格中输入=$a$1。

将鼠标指向B2右下角方黑点，它将变为+型，按住鼠标左键向右拖动鼠标，Excel将自动把A1中的数据填充到B3、B4、B5……中。

可见相对引用和绝对引用的区别在于：

相对引用拖动鼠标向右或向下填充公式时，数据会发生改变。

而绝对引用时，所填充的数据不会发生改变。

1.2.2表间引用

表间引用与表内引用所不同的是要在引用前指明引用的SHEET表的名称。

表间引用也分为相对引用和绝对引用两种。

如图1.1所示，在sheet1的表B2中绝对引用sheet2中单元格C2中的数据，方法如下：

在sheet1的单元格B2中输入=sheet2!

$c$2,回车即可。

在sheet1的B2中相对引用sheet2中C2的数据，方法是：

在sheet1的表格B2中输入=sheet2!

C2,回车即可。

单元格中输入的“=”号表示输入确认的意思。

否则不能进行任何运算。

1.3制作宏

1.3.1宏的概念

宏是被存储在Excel中VisualBasic模块中的一系列命令和函数或者说宏是一组动作的组合。

在Excel中，用户经常需要频繁或重复某些固定的内容，如果通过宏把每个步骤和某些特定的操作记录下来，然后将其绑定到某个按钮上，用户只需要通过单击该按钮运行宏，就可以自动完成这些重复性的操作，从而提高了操作人员的工作效率，同时也节省了大量的时间。

宏使用起来比较方便、灵活。

用户不必为某一特殊的任务而去创建模板，只需要在工具栏中单击相应的按钮即可。

同时，宏还可以针对不同的情况将执行的命令任意组合，从而快速、准确地完成所需要的各项工作。

1.3.2宏的制作方法

在Excel中，制作宏主要有两种不同的方法：

录制宏和编写宏。

本书只介绍录制宏。

下面以“单击-按钮”宏为例，来讲解宏的录制宏的操作过程。

（1）打开Excel，单击菜单栏下的工具---宏----录制宏---确定。

录制宏操作开始。

（2）操作要录制的动作过程。

（3）单击菜单栏下的工具---宏----停止录制宏---确定。

宏录制完成。

如清除要清除的数据宏，打开Excel工作表密码的宏，录制结果如下：

例1制作一个Excel用户验证合法性宏。

PrivateSubworkbook_open（）

Dimpassword

password=InputBox（"请输入合法密码:

","提示"）

Ifpassword<>"cjhqxhcrlqsc5794"Then

MsgBox"密码错误，你是非法用户",vbOKOnly,"提示"

Me.Close

（1）

EndIf

EndSub

只要把上述代码复制到Excel菜单栏的工具----宏----VisualBasic下的工程VBAProject窗口下ThisWorkbook的通用窗口中保存。

将Excel菜单工具下的宏---安全设置为低级，注销电脑，打开Excel文件，文件即提示输入用户打开合法密码。

例2制作清除数据按扭宏。

方法如下：

点击Excel菜单工具---宏---录制宏命令。

选中所要删除的数据，单击右键，选中清除内容命令清除数据内容，点击Excel菜单工具---宏---停止录制宏。

完成清除数据宏录制。

单击Excel菜单工具---宏，在弹出对话框的编辑命令中可以查看其代码。

然后删除部分不必要的文字说明，得到更加简洁的如下代码：

Sub按钮_2（）

Range（"a2:

k10001"）=""

EndSub

点击清除数据按钮，Excel中从A2到K10001区域中的数据将被清除。

其它范围内的数据清除可以修改Range（"a2:

k10001"）=""中单元格范围即可完成任意数据的清除。

第二章教育统计量的计算

有人说没有考试就没有统计，没有统计就没有评价，没有评价就没有好教育。

足见教育统计在教育教学中的重要作用。

教育统计必须以教育统计学中的统计与评价量为前提，我们不能随便提出一些个性化的统计标准，如果那样是极不科学的。

教育统计量即是教育统计学中关于学生成绩的统计量。

常用的有总分、参考人数、平均分、标准差、最高分、低分人数、低分率、及格人数、及格率、优生人数、优生率、分数段、有效分、集中度的计算等。

以上统计量都可以通过Excel提供的强大计算功能来实现。

2.1总分参考人数计算

2.1.1总分的计算有两种方法。

一是直接在总分单元格中输入公式；二是先选中要求和的数据，点击工具栏上的求和符号∑自动求和。

如图2.1所示。

①在D1单元格中输入=sum（A1：

C1），

回车求和。

②首先选中A1至C1单元格中的数据，单击工具栏上的求和符号Σ求出总分。

参考人数的求法，分求全校和全校中某班的总人数两种情况。

前者称为绝对参考人数，后者称为条件参考人数。

2.1.2参考人数的求法

如在图2.2中含有“学科达有效分人数”、“上有效分人数分布”、“片区单科分数段统计”、“原始成绩”等工作表，原始成绩工作表祥情参见图2.3。

现在“学科达有效分人数”工作表中计算南中的参考人数。

1.绝对参考人数（无条件参考人数）统计

无条件计算全部非空单元格数量。

在“学科达有效分人数”工作中的B6单元输入=COUNTA（原始成绩!

$b$2:

原始成绩!

$b$10001），回车Excel将自动计算出原始成绩中从B2到B10001单元格中的全部参考人数。

原始成绩在另一工作表“原始成绩”表中。

2.相对参考人数统计

方法一：

相对参考人数即有条件参考人数计算。

如计算原始成绩中学校“南中”的人数。

在B6单元格中输入=COUNTIF（原始成绩!

$b$2:

原始成绩!

$b$10001,"="＆A6）可求全体原始成绩中南中的参考人数。

公式中的if为条件语句。

"="＆A6为“南中”识别语句。

方法二：

直接在B6中插入统计函数COUNT或COUNTIF函数，并在相应的对话框中填入相应数字计算参考人数。

方法是：

单击Excel工具栏的插入菜单---函数---选择统计----COUNT，在对话框中输入统计范围，单击确定。

如图2.4所示。

方法三：

学科参考人数的计算

一般来讲每次参加考试各学科的参考人数是不同的。

在计算平均分，及格率等就会用到学科参考人数。

计算学科参考人数的方法有：

1.可以用COUNT函数计算，但计算时要包含零分，不包含空单元格。

2.用数组计算。

如在图2.2的单元格B6中计算语文学科的参考人数。

在B6中输入=sum（（原始成绩!

$e$2:

原始成绩!

$e$10001<150）*（原始成绩!

$e$2:

原始成绩!

$e$10001>0））,同时按住ctrl+shift,回车。

如图2.5，如果需要计算语文学科的零分，则取为=sum（（原始成绩!

$e$2:

原始成绩!

$e$10001<150）*（原始成绩!

$e$2:

原始成绩!

$e$10001>=0））。

2.1.3COUNT、COUNTA、COUNTIF函数的区别。

COUNT只计算数字单元格数，COUNTA计算非空单元格，COUNTIF计算满足条件的单元格数目。

2.2平均分和标准差的计算

平均分和标准差的计算仍然分整体平均分、标准差和局部平均分、标准差两种。

2.2.1平均分的计算

1.条件平均分的求法

如图2.6所示，在D5单元格中输入=if（b5=0,0,sumif（原始成绩!

$b$2:

原始成绩!

$b$9994,"="＆A5,原始成绩!

$e$2:

原始成绩!

$e$9994）/b5。

回车。

意思是如果B5单元格中数值为0，平均分直接填为0，否则，如果原始成绩中B2到b9994单元中为A5（南中），则求E2到E9994单元格数据的和，并除以B5单元格中的数据（参考人数），得南中语文的平均分。

方法二：

插入函数法

如图2.7所示。

求语文学科的全区平均。

单击插入菜单---函数----（函数选择类别）统计---AVERAGE，输入原始成绩语文的单元格范围。

单击确定，得出语文平均分。

2.2.2标准差的计算

标准差是描述成绩离散程度的统计量。

1.条件标准差

如图2.8所示，在J5单元格中输入=STDEV（IF（原始成绩！

$b$2:

原始成绩!

$b$10001=A5,（原始成绩!

$e$2:

原始成绩!

$e$10001）），按住CTRL+SHIFT键，回车。

求得全区中南中学校的学生成绩标准差。

2.求全体成绩的标准差

直接在单元格中插入标准差函数，=STDEV（原始成绩!

$e$2:

原始成绩!

$e$10001），即可求出全区语文成绩的标准差。

2.3最高分和满分人数的计算

最高分有条件最高分和全体最高分。

可用最大值函数和数组两种方法计算。

2.3.1最高分计算

1.条件最高分的计算

如图2.9所示，在K5单元格中输入=MAX（IF（原始成绩!

$b$2:

原始成绩!

$b$9994=a5,原始成绩!

$e$2:

原始成绩!

$e$9994）），按住CTRL+SHIFT键，回车。

求得南中语文最高分。

2.全体最高分的计算

在K5单元格中输入=MAX（原始成绩!

$e$2:

原始成绩!

$e$9994），计算出全区语文成绩的最大值。

2.3.2.用数组计算满分人数

在单元格中输入=sum（（if（原始成绩!

$b$2:

原始成绩!

$b$10001=a5）*（原始成绩!

$e$2:

原始成绩!

$e$10001<=150）*（原始成绩!

$e$2:

原始成绩!

$e$10001>150-0.01）），按住CRTL+SHIFT键，回车。

解释：

150为语文满分值，60为语文及格分，A5为南中所在单元格，if（原始成绩!

$b$2:

原始成绩!

$b$10001=a5）完成南中判断，（原始成绩!

$e$2:

原始成绩!

$e$10001<=150）*（原始成绩!

$e$2:

原始成绩!

$e$10001>150-0.01）），找出原始成绩E单元格中分数在150-0.01和150分之间的分数个数，则于中学数据没有0.01分值，只有满分在其中之列。

2.4低分人数和分数段人数计算

一般界定为满分的40%以下的分为低分。

低分人数计算分条件低计算和无条件低分人数计算。

2.4.1条件低分人数的计算

1.如计算南中语文成绩低分人数，如图2.10所示，在单元格B5中输入=sum（（if（原始成绩!

$b$2:

原始成绩!

$b$10001=a5）*（原始成绩!

$e$2:

原始成绩!

$e$10001<=60）*（原始成绩!

$e$2:

原始成绩!

$e$10001>0）），按住CRTL+SHIFT键，回车。

2.无条件低分计算

在上述计算中取消条件语句，为=sum（（原始成绩!

$e$2:

原始成绩!

$e$10001<=60）*（原始成绩!

$e$2:

原始成绩!

$e$10001>0）），求得全区的语文低分人数。

也可以先求得每个学校的低分人数，再用求和函数SUM计算全区低分人数。

2.4.2各分数段人数计算

分数段的计算可分为条件和无条件分数段人数计算。

1.条件分数段

如计算南中语文及格人数。

如图2.10所示，在单元格C5中输入=sum（（if（原始成绩!

$b$2:

原始成绩!

$b$10001=a5）*（原始成绩!

$e$2:

原始成绩!

$e$10001<=150）*（原始成绩!

$e$2:

原始成绩!

$e$10001>60）），按住CRTL+SHIFT键，回车。

解释：

A5为南中所在单元格。

2.无条件分数段人数计算

计算全区语文及格人数。

在单元格中输入=sum（（原始成绩!

$e$2:

原始成绩!

$e$10001<=150）*（原始成绩!

$e$2:

原始成绩!

$e$10001>60）），按住CRTL+SHIFT键，回车。

2.5有效分计算

有效分一般在初中或高中成绩统计中出现。

它是评价学科教

学质量高低的重要指标。

计算方法是：

如图2.11所示。

在B7单元格中输入=b5+b6*$c$3,回车，即计算出语文学科一段有效分。

要计算出语文学科准确的一段有效分，要不断调整δ1的值，使模拟上线人数与实际上线人数相等或相近（主要是相同分数引起）。

2.6集中度的计算

集中度表述的是学科在学生群体中的集中程度。

学科集中度是某学科上有效分人数与总分上有效人数的百分比。

可分为条件集中度和无条件集中度。

2.6.1条件集中度的计算

如图2.12所示。

在单元格D6中输入=IF（C6=0,0,SUM（（（原始成绩!

$b$2:

原始成绩!

$b$0001=A6）*（原始成绩!

$e$2:

原始成绩!

$e$0001>=$c$4）*（原始成绩!

$j$2:

原始成绩!

$j$0001>=$d$4/c6），按住CRTL+SHIFT键，回车。

解释：

IF（C6=0,0,SUM（（（原始成绩!

$b$2:

原始成绩!

$b$0001=A6），表示如果C6单元格中数据为0，则C6值0，否则，在原始成绩的B

单元格列中如果单元格中的字符为A6南中，则（原始成绩!

$e$2:

原始成绩!

$e$0001>=$c$4）*（原始成绩!

$j$2:

原始成绩!

$j$0001>=$d$4/c6）计算语文大于C4，113分，总分J大于D4，505分的人数并除以C6，一段人数311人。

得到其百分比，即南中语文学科的集中度。

2.6.2无条件集中度

在上述计算南中语文集中度的过程中，如果只计算全区成绩的集中度，则无需进行条件判断。

即=IF（C6=0,0,SUM（（（原始成绩!

$e$2:

原始成绩!

$e$0001>=$c$4）*（原始成绩!

$j$2:

原始成绩!

$j$0001>=$d$4/c6），按住CRTL+SHIFT键，得到的是语文学科的集中度。

2.6.3生源因子的计算

生源因子各校进入全区成绩前30%的人数，30%～70%的人数，70%～100%的人数分别排名得分的总分排名即为生源因子得分。

2.7教学质量评价

按平均分、低分率、及格人数加权0.6，及格率，优生人数加权0.6，优生率、标准差、生源因子按全区排名得分的总分即为该学科的教学质量得分，各学科得分的总和即为学校教学质量得分。

用得分多少来评价学校各个学科或学校的教学质量

第三章应用举例

应用举例是运用前边学过的相关计算方法开发一此小的常用自动办公系统。

3.1学生成绩1分表制作

1分表包含整十分数人数，如100分，110分，和非整分数人数计算，如121分，其整表如图3.1所示。

1.用数组计算整分数人数

用数组计算考分表中语文分数为280的人数。

在B6中输入=SUM（（理科原始成绩!

$j$2:

理科原始成绩!

$j$10001）

$j$2:

理科原始成绩!

$j$10001）>=A6）），按住CTRL+SHIFT键，回车。

解释：

B3中的数据0，实际输入的是0.01，实际上计算的是语文分数介于280～280.01的分数。

由于分数不存在0.01分数，所以计算的是280的分数。

2.计算非整数分数人数

用数组计算考分表中语文分数为281的人数。

在C6中输入=SUM（（理科原始成绩!

$j$2:

理科原始成绩!

$j$10001）<=A6+$C$3）*（理科原始成绩!

$j$2:

理科原始成绩!

$j$10001）>A6+$B$3）），按住CTRL+SHIFT键，回车。

其它用类似的方法可计算出来。

3.2学生自动分班系统

学生自动分班系统能够将学生按男女比例、入学考试分数按班分配均匀。

现将本系统的制作方法写作如下：

如图3.2所示，本系统含有2个工作表，自动分班参数，和

自动分班结果。

其中自动分班参数，有男分、男余、男次、男；女分、女余、女次、女、班次、姓名、总分性别、班数设定、设定参加分班的人数。

图3.2

其各部分设置代码如下：

1.男分，在A3中输入：

=IF（D3=0,0,IF（B3=0,M$3,IF（B3<=M$3,M$3+1-B3,B3-M$3）））

2.男余：

在B3中输入=IF（D3=0,"",MOD（C3,2*M$3））

3.男次：

在C3中输入=RANK（H3,H$3:

H$5002）

4.男：

在D3中输入=IF（L3="男",K3,0）

5.女分：

在E3中输入：

=IF（H3=0,0,IF（F3=0,1,IF（F3<=M$3,F3,2*M$3+1-F3）））

6.女余：

在F3中输入=IF（H3=0,"",MOD（G3,2*M$3））

7.女次：

在G3中输入=RANK（H3,H$3:

H$5002）

8.女：

在H3中输入=IF（L3="女",K3,0）

9.在班次：

在I3中输入=A3+E3

自动分班系统结果工作的制作。

如图3.3所示。

在A3班次中输入：

=自动分班参数!

I3，在姓名B3中输入：

=自动分班参数!

J3

在总分D3中输入=自动分班参数!

K3，在性别D3中输入：

=自动分班参数!

L3。

保护工作表即完成制作。

操作说明：

在录入学生原始数据，姓名、总分、性别时应当完整。

否则，易出错。

3.3考生考试座位编排系统

如图3.4所示，本系统包含2个工作表。

考生基础信

息工作表和考生座位编排系统工作表。

1.信息工作表的制作

在随机函数F4中输入=IF（D4="","",RAND（）），在考号G4中输入=IF（F4="","",$I$3+H3），在H4中填充000000001（与考号相同位数），在H列中自动填充连续数据号。

在生成座位按钮号中输入宏代码：

SubMacro1（）

Range（"A4:

E20006"）.Select

Application.CutCopyMode=False

Selection.ClearContents

EndSub

2.考生座位编排系统制作

如图3.5所示，在片区A3中输入：

=IF（考生基础信息!

A4="","",考生基础信息!

A4）；在学校B3中输入：

=IF（考生基础信

息!

B4="","",考生基础信息!

B4）；在班次C3中输入：

=IF（考生基础信息!

C4="","",考生基础信息!

C4）；在姓名

D3中输入：

=IF（考生基础信息!

D4="","",考生基础信息!

D4）；在E3中输入：

=IF（考生基础信息!

E4="","",考生基础信息!

E4）；在F3中输入：

=IF（考生基础信息!

G4="","",考生基础信息!

G4）

保护工作表，即完成考生座位自动编排系统的制作。

操作注意：

在录入考生信息时应当完整，否则易出错。

3.4监考教师自动调换系统

如图3.6所示，本系统仍有两个工作表，分别是监考教师基础信息和监考教师调换编排表。

1.监考教师基础信息工作表

在监考教师基础信息工作表中的A2、B2、C2、D2、E2、G2、H2均为基本信息。

F2为随机函数。

在F3中输入=IF（D4="","",RAND（）），在监考教室G4中输入=IF（F4="","",$I$3+H3）。

在生成监考教师教室按钮的代码中输入：

SubMacro1（）

Range（"A4:

E20006"）.Select

Application.CutCopyMode=False

Selection.ClearContents

EndSub

保存即可。

2.监考教师调换编排表制作

如图3.7所示，在A3中输入：

=IF（监考教师基础信息!

A4="","",

监考教师基础信息!

A4）；在B3中输入：

=IF（监考教师基础信息!

B4="","",监考教师基础信息!

B4）；在C3中输入：

=IF（监考教师基础信息!

C4="","",监考教师基础信息!

C4）；在D3中输入：

=IF（监考教师基础信息!

D4="","",监考教师基础信息!

D4）；在E3中输入：

=IF（监考教师基础信息!

E4="","",监考教师基础信息!

E4）；在F3中输入：

=IF（监考教师基础信息!

G4="","",监考教师基础信息!

G4）。

3.5阅卷教师人数套算表

如图3.8所示为学校参考人数信息表。

如图3.9为阅卷教师套算比例工作表

如图3.10为学校阅卷教师人数工作表。

制作方法如下：

在C5中输入：

=IF（学校参考人数!

D6="","",ROUND（学校参考人数!

D6/套算比例表!

$B$4,0））。

其它类似。

3.6试卷大袋、小袋套算表

如图3.11所示，在D6中输入：

=INT（学校参考人

数!

B50/$C$2），在E6中输入：

=ROUNDUP（（学校参考人数!

B50-D6*$C$2）/$E$2,0）。

其它方法类似。

3.7自动计算标准

用标准分来衡量学生的成绩，是现行考试中一种常见的方法。

现在把原始分转化为标准分，多数是用专用的软件完成计算过程的。

其实用我们熟悉的Excel2002，同样可以方便地把原始分转化为标准分。

1.标准分的计算原理

标准分的计算原理是对于每一个分数XI，先算出这个分数以下的考生数占考生总数的百分比PI，再查这个PI对应于正态分布表中最接近的数值ZI，最后通过公式：

标准分=ZI*100+500算得的。

标准分总分的计算方法是：

把每位考生各科的标准分相加求和，再算出这个和以下学生成绩的学生数，除以总人数得到PI值，最后按以上算各科标准分的方法，完成标准总分的转换过程。

2.应用举例　　

假设该年级学生共有500人，现计算语文成绩的标准分。

如图3.12所示。

在第5行至501行中，在H5单元格单击，输入公式：

“=100*NORMSINV（（（RANK（D2,D$2:

D$501,1）-1）/（COUNTA（D$2:

D$501））））+500”后回车，如果输入无误，可看到该单元格按照语文原始分转化出来的语文标准分。

由于函数NORMSINV（0）返回的值是“#NUM!

”（在以上公式中，就是最低分的考生），而标准分

图3.12

最低分是100分，因此，进行标准分转换时，在显示“#NUM!

”的单元格上，必须手工把它改为100。

3