excel实例教学.docx

excel实例教学.docx

《excel实例教学.docx》由会员分享，可在线阅读，更多相关《excel实例教学.docx（15页珍藏版）》请在冰豆网上搜索。

excel实例教学

评分表是各种竞赛中常用的一种统计和亮分办法。

传统的评分表是用手工记录、手工或计算器计算，然后再人工排序，这样做不仅效率低下，而且容易出错。

利用Excel中排序等相关功能制作的评分表，使总分计算实现自

　　动化，名次排列也变成一个动态的过程，这样就使统计过程有很强的现场感，结果也会更具刺激性。

　　下面以一个8名评委、10名选手参加的卡拉OK大奖赛为例，介绍一下评分自动计算表的制作。

　　1.制作一张表头内容如图1所示的工作表，并将其命名为“评分表1”。

　　图1

　　2.在A2中输入“1”，A3中输入“2”，选中A2和A3两个单元格，并将鼠标置于被选中的两个单元格右下角的“十”字（即填充柄）上，按住鼠标左键向下拖动到A11，这样就快速地输入了参赛选手的序号。

选手越多，这种自动填充方法的优势越明显。

　　3.在J2单元格输入“=（SUM（B2:

I2）-MAX（B2:

I2）-MIN（B2:

I2））/6”。

该公式的作用是：

按照一般的比赛规则，去掉一个最高分、去掉一个最低分，剩余分数的平均分作为选手的最后得分。

为使结果显示格式一致和精确度更高，可使最后得分比每个评委所给的分数多一到两位小数。

例如，可以要求评委的打分统一为一位小数，“最后得分”则保留两位小数。

　　4.在K2单元格中输入“=RANK（J2,$J$2:

$J$11）”。

该公式的作用是算出J2单元格的数值在J2到J11各个单元格中的排序，即1号选手在所有选手中的名次。

注意：

K2中输入的单元格区域必须采用绝对引用（如果不是采用键盘直接输入，而是采用粘贴函数输入，可在要求指定区域参数时，用鼠标拖过从J2到J11的所有单元格后，在显示“J2:

J11”时，将其选中后按下F4键，就会转化成“$J$2:

$J$11”），否则就会出现错误。

　　5.选中J2和K2两个单元格，将鼠标置于被选中的两个单元格的右下角填充柄上，按住鼠标左键向下拖动到第11行，这样就可以算出所有选手的最后得分和名次。

　现在许多学校都建起了校园网，过去很多费时费力的工作，现在放在校园网中都可以轻松地解决。

今天我们来看看学校中的成绩处理工作是如何在校园网中完成的。

　　建立电子计分册

　　要建立各班各科的成绩计分册，首先应该与教务处以及校园网的网管联系，取得学生的学籍数据备用。

如果没有，那就只有“自己动手，丰衣足食”了，呵呵。

　　图1

　　1、建立工作表组

　　打开Excel，新建一“学生成绩处理”工作簿，根据开设的学科数量N插入N+1张工作表，并分别命名为“语文”、“数学”、“英语”等科目表和“总分表”，同时选中所有工作表，形成一个工作表组。

然后打开学生学籍表，从学籍表中分别复制“学号”和“姓名”数据到A、B两列，在名次列（D2）输入计算公式：

“=RANK（H2,（C:

C,H:

H））”，拖动D2单元格的填充柄至最后一行，其他各列按照图1进行设计。

最后，单击“总分”工作表，将表头中的“成绩”改为“总分”，然后在总分栏C2单元格输入“=SUM（语文:

英语!

C2）”，拖动C2单元格的填充柄向下填充至最后一行。

　　2、页面设置

　　选择“文件→页面设置”，根据打印要求，设置好页面大小和文字的格式。

为了使打印时每页都有一个表头，同时下方注明页号，选取“页眉/页脚”标签，首先设置“页眉”如图2所示：

在“左（L）”框中输入两次回车后，输入“科目：

”（注意其后的“&&[标签名]”不能直接由键盘输入，而应该点击上方的最后一个按钮），然后在“中（C）”框中输入表头（显示不下的自动换行）内容。

全选此框中所有文字，点击上方的第一个按钮，设置表头的字体、字号等。

这样，所打印出来的计分册已经包含了这些格式信息，无需重复进行设置。

右框中的文字按原样输入即可，对于页脚的设计，只需在每页的下部中央处插入页号。

　　图2

　　最后来设置页边距，这一项很重要，因为在页眉处设计有三行文字，因此需要将上边距加大一些，否则就可能导致页眉与正文打印时互相重合。

我们可以在打印预览窗口中（如图3）拖动相应的虚线至合适位置即可。

同时，为了使打印效果美观，还可以设置打印时水平居中，方法是：

在图3中，点击“页边距”，复选其中的“水平居中”即可。

　　小提示：

不要复选“垂直居中”，因为最后一页可能不能打印一整页。

　　图3

　　共享工作簿

　　计分册的基本制作已经完毕，待考试结束之后，各任课教师只需将本学科的学生成绩如实录入到计分册中，总分、名次等统计工作都会自动完成了。

可这张计分册怎样让各任课教师看到并使用呢？

那就靠我们的校园网了，考虑到数据的安全性（各任课教师不能随意修改其他任课教师的成绩），我们可以给每个工作表设置一个密码，再把工作簿设置为“共享”。

　　1、设置/取消密码

　　单击“语文”工作表标签，选中“语文”工作表，单击“工具”菜单，选择“保护……→保护工作表”，输入密码后确定。

依次选中其他各科的工作表，如法炮制。

　　2、共享工作簿

单击“工具”菜单，选择“共享工作簿”，在出现的“共享工作簿”窗口中，复选“允许多用户同时编辑，同时允许工作簿合并”，[确定]即可。

如果想给共享后的工作簿也设置一个保护密码，可单击“工具”菜单，选择“保护……→保护并共享工作簿”，复选“以追踪修订方式共享”，并设置密码即可。

建立分期付款查询系统

　　如今，分期付款的销售方式越来越普遍，如果有一个能够自动计算首付、月按揭的查询系统就好了。

下面笔者就以一个分期付款购买汽车的自动查询系统（如图1）为例，给大家介绍一下设计过程。

　　搭建查询界面框架

　　新建“购车自动查询系统”工作簿，并将“Sheet1”工作表重命名为“查询系统”。

在B2、B3、B4、B5、B6单元格中分别输入：

汽车品名及总价款（百元）、首期支付金额（百元）、欠款金额（百元）、支付月份、每月须支付金额（百元）。

　　将B2：

D6区域单元格格式中的“垂直对齐”设置为“居中”方式。

设置D6单元格“货币”格式“小数位数”为2，“货币符号”为￥，“负数”为“￥-1234.10”。

将B列和D列中的字符的字号都设置为14，第2，3，4，5，6各行的行高设置为52，A列宽度设为3，B列的宽度设置为30，C列的宽度设置为28，D列的宽度设置为20，E列的宽度设置为3。

　　在H、I列中输入如图2所示的汽车品名和总价款（百元）。

这里假设有100个品牌的汽车，以汽车1、汽车2、汽车3……来代表具体的名称，实际运用时用具有实际意义名称即可。

　　设置控制按钮

　　点击“视图”　“工具栏”　“窗体”，单击“窗体”工具栏中的“列表框”按钮，鼠标光标变为十字状，在图3所示的C2单元格位置画一个矩形框。

　　用鼠标右击刚画出的列表框，在打开的快捷菜单中选择“设置控件格式”命令（如图4），进入“控制”选项卡。

　　在“数据源区域”录入框中输入$H$2:

$H$101；在“单元格链接”录入框中输入$J$2；确认“选定类型”为“单选”，勾选“三维阴影”选项。

　　在“窗体”工具栏中选择“微调项”按钮，当鼠标光标变为十字状时在C3单元格画一个矩形框，用鼠标右键单击它，在打开的快捷菜单中选择“设置控件格式”命令，再在打开的对话框中选择“控制”选项卡，将最小值定义为200（这里假设首期支付金额起点为200百元，即20000元），最大值定义为30000，步长为10，“单元格链接”框中录入$D$3，启用“三维阴影”。

　　用同样方法在C5单元格设置“微调项”按钮，控件格式为：

最小值定义为1，最大值为36（这里假设最长还款期限为三年，即36个月），步长为1，单元格链接栏中录入$D$5。

　　定义公式实现查询功能

　　在D2单元格中输入公式“=INDEX（I2:

I101,J2）”，以实现对I2:

I101中数值的引用。

　　在D4单元格中输入“=D2-D3”。

其意义为欠款金额（百元）=汽车总价款（百元）-首期支付金额（百元）。

　　在D6单元格中输入“=-PMT（0.4%,D5,D4）”，D6单元的结果即每月支付金额。

这里，0.4%表示月利率，D5代表偿还的月份数，D4代表须偿还金额的现值。

PMT是EXCEL中的一个函数，其功能是计算在固定利率下的贷款（或投资或欠款）的等额分期偿还额。

随着公式定义的完成，D列中有关数据会相应出现。

　　修饰查询界面

　　选定H列至J列的内容，右击所选范围，在打开的快捷菜单中选择“隐藏”命令，隐藏所选范围。

　　选定H列至J列的内容，右击所选范围，在打开的快捷菜单中选择“隐藏”命令，隐藏所选范围。

　　使用时，只须选择所要购买的汽车、首付金额和偿还期限，便可立即知道自己每期需要支付的金额数，非常方便。

制作关联课程表

　　插入新的工作表，在表中制作课程表，调整使其正好填满一张A4或B5纸。

然后向下复制，个数与任课教师人数相同，其中第二行第一个单元格为任课教师标识。

　　比如要制作高一年级一、二班数学任课教师的授课表。

首先，在标识单元格内输入“数学1-2”。

然后，选中一班星期一第一节单元格，在编辑栏输入“=IF（高一各班课程表!

B3="数学","1",IF（高一各班课程表!

B14="数学","2",""））”，该授课表星期一第一节就设置完毕。

最后，将该单元格用填充柄复制到星期二至星期六，用相同的方法把第一节复制到最后一节。

则高一年级一、二班数学任课教师的授课表就设置完成了。

如果年级课程表中有数据，则会在授课表中反映出来。

　　在编辑栏输入的公式可以表述为：

如果高一各班课程表的B3为“数学”，则本单元格显示为1，如果不是就进入下层。

如果高一各班课程表的B14为“数学”，则本单元格显示为2，如果不是本单元格为空。

　　为了避免出错，也可以用如下方法来做：

选择“插入函数”对话框，在“选择函数”中选中“IF”。

单击[确定]按钮后，出现“函数参数”对话框，光标指针在logical-test行。

单击高一各班课程表，再单击一班星期一第一节对应的单元格，输入“=数学”。

把指针移到value-if-true行，输入“1”。

再把指针移到value-if-false行，在编辑栏左边选中函数“IF”。

这时再进入“函数参数”对话框。

下面的操作与上面相同，只是将“1”改为“2”，单击[确定]按钮即可。

　　其他任课教师的授课表设置方法也与上面相同，只是改变对应的学科和班级。

如果任课多于两个班，则IF函数的嵌套层数与任课班数相同。

下面是高一年级一至六班历史任课教师授课表的设定公式“=IF（高一各班课程表!

B3="历史","1",IF（高一各班课程表!

B14="历史","2",IF（高一各班课程表!

B25="历史","3",IF（高一各班课程表!

B36="历史","4",IF（高一各班课程表!

B47="历史","5",IF（高一各班课程表!

B58="历史","6",""）））））”。

试中B3、B14、B25、B36、B47、B58分别表示一至六班的星期一第一节对应的单元格。

单科课表轻松做

　　每学期开学，作为学科教师，首先关心的是自己的课程分布情况，可要从总排课表里挑出自己的课程安排，还真得有点“眼力”，那么怎样快速建立一个属于自己的课程表呢？

通常班级或年级的课程表已经由教务处打印完成了，我们就以此为基础，从中提取属于自己的“元素”吧。

　　疑虑重重

　　如图1，这是某班的课程总表，我们的要求是：

1、保留表头文字；2、去除非自己所教的课程。

开始我想到了Excel的“高级筛选”功能，后来发现无论怎样设置筛选的条件，都无法完成这一任务（因为“数学”课程的排列事先并不知道，也没有任何规律可言）。

难道重新制作一份或者一个个修改删除多余的文字？

　　柳暗花明

　　一个完全偶然的机会，“条件格式”这四个字闯入了我的视野，经过试验，最终我巧妙地运用“条件格式”解决了这个问题。

方法如下：

　　图1

　　1、在Excel中打开“课程表.xls”（如图1），选中需要的数据区域（除表头和一些标记性文字不被选择）。

　　2、点击菜单“格式→条件格式”，出现如图2所示对话框。

　　3、假设我们要挑出“数学”的内容，我们的思路就是将其他单元格的颜色改为白色，起到暂时隐藏和不能打印出来的效果。

因此，将条件1中设置为“单元格数值”、“不等于”、“=C6”。

其中最后一列的数值是一个地址引用，操作时可以直接点击课表中任一个“数学”单元格。

　　图2

　　4、设置好了条件，单击图2中的[格式（F）……]按钮，设置满足条件的单元格的格式，这里我们只需修改一下它的颜色为“白色”即可。

　　图3

　　5、确定之后，回到课程表。

呵呵，看看图3，是不是已经如愿以偿了呢？

　　总结提高

　　1、上述方法的优点是具有良好的可移植性，对于其他学科教师，如果想得到类似的个人课表，只需将上述步骤中的“数学”相应改为自己的学科名称即可。

　　2、当需要恢复原来的课表时，不要力图通过改变这些单元格的颜色来使它们全部重现出来。

正确的方法应该是：

重复上述步骤的1、2两步，并在图2中点击[删除]按钮来去掉这些过滤条件即可。

　　图4

　　3、如果一个教师同时任两个学科，可以在图2中点击[添加]设置两个以上的条件，并分别指定其格式。

但条件的设置应该适当作一下调整：

首先将图1所选区域的文字全部设置为白色，然后如图4所示设定条件1：

“单元格数值”、“等于”、“=C6”；条件2：

“单元格数值”、“等于”、“=D4”（分别是两门课程所在的单元格），格式都统一设置为“黑色”即可。

　　4、将这个思路推广到其他情况中（比如排课系统中检查课程设置是否合理或是否发生冲突），也可大大提高工作效率。

建立合并计算

　　一个公司内可能有很多的销售地区或者分公司，各个分公司具有各自的销售报表和会计报表，为了对整个公司的所有情况进行全面的了解，就要将这些分散的数据进行合并，从而得到一份完整的销售统计报表或者会计报表。

在Excel中系统提供了合并计算的功能，来完成这些汇总工作。

　　所谓合并计算是指，可以通过合并计算的方法来汇总一个或多个源区中的数据。

MicrosoftExcel提供了两种合并计算数据的方法。

一是通过位置，即当我们的源区域有相同位置的数据汇总。

二是通过分类，当我们的源区域没有相同的布局时，则采用分类方式进行汇总。

　　要想合并计算数据，首先必须为汇总信息定义一个目的区，用来显示摘录的信息。

此目标区域可位于与源数据相同的工作表上，或在另一个工作表上或工作簿内。

其次，需要选择要合并计算的数据源。

此数据源可以来自单个工作表、多个工作表或多重工作簿中。

　　在Excel2000中，可以最多指定255个源区域来进行合并计算。

在合并计算时，不需要打开包含源区域的工作簿。

　　11.1.1通过位置来合并计算数据

　　通过位置来合并计算数据是指：

在所有源区域中的数据被相同地排列，也就是说想从每一个源区域中合并计算的数值必须在被选定源区域的相同的相对位置上。

这种方式非常适用于我们处理日常相同表格的合并工作，例如，总公司将各分公司的合并形成一个整个公司的报表。

再如，税务部门可以将不同地区的税务报表合并形成一个市的总税务报表等等。

　　下面我们以一个实例来说明这一操作过程，建立如图11-1所示的工作簿文件。

巧用Excel进行学生成绩统计

　　在老师的日常工作中，对学生的成绩进行统计分析管理是一项非常重要也是十分麻烦的工作，如果能够利用EXCEL强大的数据处理功能，就可以让各位老师迅速完成对学生的成绩的各项分析统计工作。

下面就向各位朋友介绍一些利用EXCEL进行学生成绩管理的小技巧。

　　一、快速转换学生考试成绩等级　　

　　有的时候，会遇到要将学生的考试成绩按实际考试分数转换成相应成绩等级的情况，如将考试成绩在90分以上的成绩转换成“A+”形式，85-89分的成绩转换成“A”形式...。

一般情况，在EXCEL表格中大家会采用IF（）函数来设计公式进行转换，这样所设计的公式会变得很复杂，如果进行转换的成绩等级类型超过IF（）函数的最大嵌套（7层）时，IF（）函数就无能为力了。

这时我们可用如下的方法来简化操作。

　　1、打开学生成绩工作表（图1）。

　　2、在G2到I12单元格录入考试成绩分数段与考试成绩等级对照表。

　　3、在D3单元格录入公式“=INDEX（I$3:

I$12,MATCH（1,（C3>=G$3:

G$12）*（C3<=H$3:

H$12）,0））”，由于该公式为数组公式，在录入完上述内容后，必须同时按下“Ctrl+Shift+Enter”键，为上述公式内容加上数组公式标志即大括号“{}”。

该公式的作用就是，根据C3单元格中的学生成绩，在D3单元格自动将该成绩转换成相应的成绩等级。

　　4、将光标移到D3单元格，向下拖动填充柄至D12单元格，将公式进行快速复制，这样就可以迅速完成转换学生成绩等级的工作（图2）。

　　5、还可以按照自己的喜好，将G2至I12的单元格区域设置为“隐藏”，以使表格更加美观。

　　二、快速统计学生考试成绩分布情况　　

　　在利用EXCEL管理学生考试成绩时，常常要统计各分数段学生考试成绩的分布情况，如果采用下面介绍的这种方法，就能使这项工作变得非常方便。

　　1、打开学生成绩工作表（本例仍使用上例的工作表）。

　　2、在G3至G6单元格录入学生考试成绩的统计分段点。

如在本例中采用的统计分段点为：

60、69、79、89，即统计60分以下、61-69、70-79、80-89、90分以上五个学生考试成绩区段的人数分布情况，当然你也可以根据自己的实际需要在此进行不同的设置。

　　3、选中要进行公式设计的单元格区域B14至B18，按下F2键，录入公式“=FREQUENCY（C3:

C12,G3:

G6）”，由于该公式为数组公式，在录入完上述内容后，必须同时按下“Ctrl+Shift+Enter”键，为上述公式内容加上数组公式标志即大括号“{}”。

　　4、当上述操作完成后，在B14至B18单元格就迅速得到了正确的学生考试成绩分布情况（图3）。

　　5、值得注意的是，在我们设计统计区段时，这个统计区段必须比统计分段点的数据个数多一个。

这个多出来的统计区段表示超出最高间隔的数值个数。

例如，在本例中，我们设计的统计分段点为60、69、79、89四个数值，这时要想取得正确的统计区段分布数据，就必须在B14至B18五个单元格中输入FREQUENCY（）函数计算的结果，多出来的这一个单元格将返回学生成绩表中大于90分的成绩的人数。

　　通过上面两个实例，相信大家对Excel应用到学生成绩统计中的效果已经有所了解。

其实，作业一款面向大众的电子表格软件，它应用的场合非常多，只要我们进行思考，你会发现一切都是变的简单起来。

利用Dictionary对象处理EXCEL重复值

　　对于EXCEL的重复值这个问题，比较普通，OFFICEBA曾经发过一文章：

让Excel2007来筛选唯一值或删除重复值讨论过，今天再来说说利用Dictionary对象处理EXCEL重复值

　　先编写一个函数来达到处理EXCEL重复值的功能，然后就应用到实例当中。

FunctionMergerRepeat（IndexAsInteger,ParamArrayarglist（）AsVariant）

'*******************************************

'功能：

获得指定单元格区域或数组中的不重复集合或值

'参数说明：

'Index：

整型，当值小于1时，函数返回一个集合；

'　　　大于1且小于不重复项的时，返回一个不重复的值

'　　　大于不重复项时，返回空。

'arglist（）：

可为单元格区域或数组常量。

'发布：

'*******************************************

DimNotRepeatAsObject,tStrAsString

SetNotRepeat=CreateObject（"Scripting.Dictionary"）

ForEachargInarglist

　　ForEachrRanInarg

　　　　IfTypeName（rRan）="Range"Then

　　　　　　IfrRan.Value<>""ThenNotRepeat（rRan.Value）=0

　　　　Else

　　　　　　NotRepeat（rRan）=0

　　　　EndIf

　　Next

Next

IfIndex<1Then

　　MergerRepeat=NotRepeat.keys

ElseIfIndex<=NotRepeat.CountThen

　　arr=NotRepeat.keys

　　MergerRepeat=arr（Index-1）

Else

　　MergerRepeat=""

EndIf

EndFunction

　　下面用几个应用的实例，来说明该函数的应用。

　　1、利用此函数能返回某区域中不重复值的数量，以B1：

B10为例

　　在单元格中输入公式：

=COUNTA（MergerRepeat（0,B1:

B10））

　　回车即有

　　2、利用此函数返回不同单元格不重复值比较

　　例如在C列从C1格开始列出B1:

B10的不重复数值。

　　在C1格中输入公式：

=MergerRepeat（ROW（）,$A$1:

$A$10）

　　并向下填充。

　　3、求多个区域（可以不连续）加数组的不重复个数。

　　=COUNTA（MergerRepeat（0,A1:

A6,{"abc","Excel吧",1,""},C2:

C6））

　　虽然此函数对于处理大量数据来说效率不高，但还算很不错了。

大家可以在此基础上编写更加完善的函数进行完善别忘了和大家分享哦

专业图表轻松做

　　用户在使用Excel图表时，首要的问题是利用现成的模板来创建图表。

一般，用户不是专业的图形设计师，他们只想得到一个能展示其数据效果的专业图表。

在Exce2007中，有一个全新的预览效果向导的用户界面。

总之，这项目设计是为用户提供一系列的选择来完成专业设计的工作，然后通过组合和调整创建专业图表。

我们将详细介绍这些功能来创建精美的图表，使用户能通过几次点击便能完成图表。

在Exce2007中通过简单的四个步骤创建图表，从而引导新用户入门。

这四步是：

选择图表类型，选择图表版式，设置图表样式，设置文档主题。

在本贴我将介绍前面二步，其余二步在下一贴介绍。

　　图表类型

　　在当前版本中创建一个图表，第一步就是要选择图表类型：

柱形图、折线图、散点图、饼图、曲面图等等。

在Excel2007中，有更多的图表类型以供选择。

以下演示如何选择图表类型：

在插入图表栏中插入一个图表，Excel2007的插入菜单中有个插入图表栏（共七种图表类型）。

利用大图标和细分图表类型的工具提示，可以非常容易地选择所需的图表类型。

　　Excel2007除了提供最常见的图表种类：

柱形图、折线图、饼图、条形图、面积图、散点图等，其余图表种类全部放在其它图表栏中。

如果要浏览全部图表类型，或改变已有图表的种类，打开创建图表对话框可以容易地浏览和选择图表类型。

　　快捷键：

Alt+F1在当前工作表中创建默认图表类型的图表对象；F11新建默认图表类型的图表对象

　　曾经有用户提出增加图表类型的请求，非常遗憾在Office2007中我们未能提供更多的图表类型。

我们计划在下个版本中深入研究和重新考虑，当然，也请你提供更多的反馈信息。

　　图表版式

　　图表类型一旦选择后，