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

1、用excel制作自动办公系统第一章 基础知识Excel具有非常强大的数据计算功能，是日常工作中经常用到的数据处理软件。现介绍Excel的常用基本知识。1.1 Excel的基本构成Excel工作平台分工作薄和工作表。一个Excel文件就是一个工作薄，在一个工作薄下有多个工作表。如图1.1所示中的sheet1,sheet2等都是工作薄Book1中的工作表。在工作表的上方有菜单栏和工具栏，工具栏是操作更快捷的必要选择，读者务必熟悉工具栏的使用。读者可以自行熟悉菜单栏和工具栏的功能，下方有工作表sheet1、sheet2。工作表可以根据自己的需要添加。方法是用鼠标指向工作表名（如sheet1）点击右键

2、，选择插入，在对话框中选择“工作表”，单击确定，即插入了一个新的工作表。单元格是Excel工作表非常重要的基本元素。如图1.1中的黑方框就是一个单元格。它是运算或引用的重要元素。单元格的定位，分行和列两个指标。列标为1 2 3，行标为A B C ，如图1.1中黑方框单元格的位置表述为A1。1.2 单元格的引用 常用的单元格引用分表内引用和表间引用两种。单元格引用不能在同一单元中引用自己，如在A1单元格中引用A1单元格。引用分相对引用和绝对引用。相对引用时Excel会根据自动填充公式时的操作自动改变单元格的行标和列标。绝对引用时Excel不会根据自动填充公式时的操作自动改变单元格的行标和列标。1

3、.2.1 表内引用1.表内相对引用单元格如图1.1所示，在B2单元格中引用单元格A1，方法是：在B2单元格中输入=A1。将鼠标指针指向B2右下角方黑点，它将变为+型，按住鼠标左键向右拖动鼠标，Excel将自动向右填充相应单元格B1 C1 D1中的数据。横向拉动鼠标，将改变填充列标，纵向拉动时，将改变填充行标。2.表内绝对引用单元格如图1.1所示，在表B2单元格中绝对引用单元格A1，方法是：在B2单元格中输入=$a$1。将鼠标指向B2右下角方黑点，它将变为+型，按住鼠标左键向右拖动鼠标，Excel将自动把A1中的数据填充到B3、 B4、B5中。可见相对引用和绝对引用的区别在于：相对引用拖动鼠标向

4、右或向下填充公式时，数据会发生改变。而绝对引用时，所填充的数据不会发生改变。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 宏的概念宏是被存储在Ex

5、cel中Visual Basic模块中的一系列命令和函数或者说宏是一组动作的组合。在Excel中，用户经常需要频繁或重复某些固定的内容，如果通过宏把每个步骤和某些特定的操作记录下来，然后将其绑定到某个按钮上，用户只需要通过单击该按钮运行宏，就可以自动完成这些重复性的操作，从而提高了操作人员的工作效率，同时也节省了大量的时间。宏使用起来比较方便、灵活。用户不必为某一特殊的任务而去创建模板，只需要在工具栏中单击相应的按钮即可。同时，宏还可以针对不同的情况将执行的命令任意组合，从而快速、准确地完成所需要的各项工作。1.3.2 宏的制作方法在Excel中，制作宏主要有两种不同的方法：录制宏和编写宏。本

6、书只介绍录制宏。下面以“单击-按钮”宏为例，来讲解宏的录制宏的操作过程。（1）打开Excel，单击菜单栏下的工具-宏-录制宏-确定。录制宏操作开始 。（2）操作要录制的动作过程。（3）单击菜单栏下的工具-宏-停止录制宏-确定。宏录制完成。如清除要清除的数据宏，打开Excel工作表密码的宏，录制结果如下：例1制作一个Excel用户验证合法性宏。Private Sub workbook_open()Dim passwordpassword = InputBox(请输入合法密码:, 提示)If password cjhqxhcrlqsc5794 ThenMsgBox 密码错误，你是非法用户, vbO

7、KOnly, 提示Me.Close (1)End IfEnd Sub只要把上述代码复制到Excel菜单栏的工具-宏-Visual Basic下的工程VBA Project窗口下ThisWorkbook的通用窗口中保存。将Excel菜单工具下的宏-安全设置为低级，注销电脑，打开Excel文件，文件即提示输入用户打开合法密码。例2制作清除数据按扭宏。方法如下：点击Excel菜单工具-宏-录制宏命令。选中所要删除的数据，单击右键，选中清除内容命令清除数据内容，点击Excel菜单工具-宏-停止录制宏。完成清除数据宏录制。单击Excel菜单工具-宏，在弹出对话框的编辑命令中可以查看其代码。然后删除部分不

8、必要的文字说明，得到更加简洁的如下代码：Sub 按钮_2()Range(a2:k10001) = End Sub点击清除数据按钮，Excel中从A2到K10001区域中的数据将被清除。其它范围内的数据清除可以修改Range(a2:k10001) = 中单元格范围即可完成任意数据的清除。第二章 教育统计量的计算有人说没有考试就没有统计，没有统计就没有评价，没有评价就没有好教育。足见教育统计在教育教学中的重要作用。教育统计必须以教育统计学中的统计与评价量为前提，我们不能随便提出一些个性化的统计标准，如果那样是极不科学的。教育统计量即是教育统计学中关于学生成绩的统计量。常用的有总分、参考人数、平均分

9、、标准差、最高分、低分人数、低分率、及格人数、及格率、优生人数、优生率、分数段、有效分、集中度的计算等。以上统计量都可以通过Excel提供的强大计算功能来实现。2.1 总分 参考人数计算2.1.1总分的计算有两种方法。一是直接在总分单元格中输入公式；二是先选中要求和的数据，点击工具栏上的求和符号自动求和。如图2.1所示。在D1单元格中输入=sum（A1：C1），回车求和。首先选中A1至C1单元格中的数据，单击工具栏上的求和符号求出总分。参考人数的求法，分求全校和全校中某班的总人数两种情况。前者称为绝对参考人数，后者称为条件参考人数。2.1.2参考人数的求法如在图2.2中含有“学科达有效分人数”

10、、“上有效分人数分布”、“片区单科分数段统计”、“原始成绩”等工作表，原始成绩工作表祥情参见图2.3。现在“学科达有效分人数”工作表中计算南中的参考人数。1.绝对参考人数（无条件参考人数）统计无条件计算全部非空单元格数量。在“学科达有效分人数”工作中的B6单元输入=COUNTA(原始成绩!$b$2:原始成绩!$b$10001），回车Excel将自动计算出原始成绩中从B2到B10001单元格中的全部参考人数。原始成绩在另一工作表“原始成绩”表中。 2.相对参考人数统计方法一：相对参考人数即有条件参考人数计算。如计算原始成绩中学校“南中”的人数。在B6单元格中输入=COUNTIF（原始成绩!$b$

11、2: 原始成绩!$b$10001, =A6）可求全体原始成绩中南中的参考人数。公式中的if为条件语句。=A6为“南中”识别语句。方法二：直接在B6中插入统计函数COUNT或COUNTIF函数，并在相应的对话框中填入相应数字计算参考人数。方法是：单击Excel工具栏的插入菜单-函数-选择统计-COUNT，在对话框中输入统计范围，单击确定。如图2.4所示。方法三：学科参考人数的计算一般来讲每次参加考试各学科的参考人数是不同的。在计算平均分，及格率等就会用到学科参考人数。计算学科参考人数的方法有：1.可以用COUNT函数计算，但计算时要包含零分，不包含空单元格。2.用数组计算。如在图2.2的单元格B

12、6中计算语文学科的参考人数。在B6中输入=sum(原始成绩!$e$2:原始成绩!$e$100010),同时按住ctrl+shift,回车。如图2.5，如果需要计算语文学科的零分，则取为=sum(原始成绩!$e$2:原始成绩!$e$10001=0)。2.1.3 COUNT、 COUNTA、 COUNTIF函数的区别。COUNT只计算数字单元格数， COUNTA计算非空单元格， COUNTIF计算满足条件的单元格数目。2.2 平均分和标准差的计算平均分和标准差的计算仍然分整体平均分、标准差和局部平均分、标准差两种。2.2.1平均分的计算1.条件平均分的求法如图2.6所示，在D5单元格中输入=if(

13、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所

14、示，在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$9

15、994=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$10001150-0.01)，按住CRTL+SHIFT键，回车。解释：150为语文满分值，60为语文及格分，A5为南中所在单元格，if(原始成绩!$b$2:原始成绩!$b$10001=a5)完成南

16、中判断，(原始成绩!$e$2:原始成绩!$e$10001150-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$100010)，按住CRTL+SHIFT键，回车。2.无条件低分计算在上述计

17、算中取消条件语句，为=sum(原始成绩!$e$2:原始成绩!$e$100010)，求得全区的语文低分人数。也可以先求得每个学校的低分人数，再用求和函数SUM计算全区低分人数。 2.4.2 各分数段人数计算分数段的计算可分为条件和无条件分数段人数计算。1.条件分数段如计算南中语文及格人数。如图2.10所示，在单元格C5中输入=sum(if(原始成绩!$b$2:原始成绩!$b$10001=a5) *(原始成绩!$e$2:原始成绩!$e$1000160)，按住CRTL+SHIFT键，回车。解释：A5为南中所在单元格。2.无条件分数段人数计算计算全区语文及格人数。在单元格中输入=sum(原始成绩!$e

18、$2:原始成绩!$e$1000160)，按住CRTL+SHIFT键，回车。 2.5 有效分计算 有效分一般在初中或高中成绩统计中出现。它是评价学科教学质量高低的重要指标。 计算方法是：如图2.11所示。在B7单元格中输入=b5+b6*$c$3,回车，即计算出语文学科一段有效分。要计算出语文学科准确的一段有效分，要不断调整1的值，使模拟上线人数与实际上线人数相等或相近（主要是相同分数引起）。2.6 集中度的计算集中度表述的是学科在学生群体中的集中程度。学科集中度是某学科上有效分人数与总分上有效人数的百分比。可分为条件集中度和无条件集中度。2.6.1 条件集中度的计算如图2.12所示。在单元格D6

19、中输入=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)计算语文大于

20、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 教学

21、质量评价按平均分、低分率、及格人数加权0.6，及格率，优生人数加权0.6，优生率、标准差、生源因子按全区排名得分的总分即为该学科的教学质量得分，各学科得分的总和即为学校教学质量得分。用得分多少来评价学校各个学科或学校的教学质量第三章 应用举例应用举例是运用前边学过的相关计算方法开发一此小的常用自动办公系统。3.1 学生成绩1分表制作1分表包含整十分数人数，如100分，110分，和非整分数人数计算，如121分，其整表如图3.1所示。1.用数组计算整分数人数用数组计算考分表中语文分数为280的人数。在B6中输入=SUM（理科原始成绩!$j$2: 理科原始成绩!$j$10001）=A6)，按住CTR

22、L+SHIFT键，回车。解释：B3中的数据0，实际输入的是0.01，实际上计算的是语文分数介于280280.01的分数。由于分数不存在0.01分数，所以计算的是280的分数。2.计算非整数分数人数用数组计算考分表中语文分数为281的人数。在C6中输入=SUM（理科原始成绩!$j$2: 理科原始成绩!$j$10001）A6+$B$3)，按住CTRL+SHIFT键，回车。其它用类似的方法可计算出来。3.2 学生自动分班系统学生自动分班系统能够将学生按男女比例、入学考试分数按班分配均匀。现将本系统的制作方法写作如下：如图3.2所示，本系统含有2个工作表，自动分班参数，和自动分班结果。其中自动分班参数

23、，有男分、男余、男次、男；女分、女余、女次、女、班次、姓名、总分性别、班数设定、设定参加分班的人数。图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

24、,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.信息工作表的制作在随机函

25、数F4中输入=IF(D4=,RAND()，在考号G4中输入=IF(F4=,$I$3+H3)，在H4中填充000000001（与考号相同位数），在H列中自动填充连续数据号。在生成座位按钮号中输入宏代码：Sub Macro1() Range(A4:E20006).Select Application.CutCopyMode = False Selection.ClearContentsEnd Sub2.考生座位编排系统制作如图3.5所示，在片区A3中输入：=IF(考生基础信息!A4=,考生基础信息!A4)；在学校B3中输入：=IF(考生基础信息!B4=,考生基础信息!B4)；在班次C3中输入：=I

26、F(考生基础信息!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=

27、,RAND()，在监考教室G4中输入=IF(F4=,$I$3+H3)。在生成监考教师教室按钮的代码中输入：Sub Macro1()Range(A4:E20006).Select Application.CutCopyMode = False Selection.ClearContentsEnd Sub保存即可。2.监考教师调换编排表制作如图3.7所示，在A3中输入：=IF(监考教师基础信息!A4=,监考教师基础信息!A4)；在B3中输入：=IF(监考教师基础信息!B4=,监考教师基础信息!B4)；在C3中输入：=IF(监考教师基础信息!C4=,监考教师基础信息!C4)；在D3中输入：=IF(监

28、考教师基础信息!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(学校参

29、考人数!B50-D6*$C$2)/$E$2,0)。其它方法类似。3.7 自动计算标准用标准分来衡量学生的成绩，是现行考试中一种常见的方法。现在把原始分转化为标准分，多数是用专用的软件完成计算过程的。其实用我们熟悉的Excel 2002，同样可以方便地把原始分转化为标准分。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