EXCEL实例教程

EXCEL实例教程一．制作竞赛评分自动计算表 (3)二．用Excel巧做电子计分册 (5)三．建立分期付款查询系统 (8)四．制作关联课程表 (12)五．建立合并计算 (13)六．如何不让数字自动转换为时间 (20)七．用FreQuency函数统计分数段优于IF函数 (21)八．妙用Shift键将Excel表格转为图片 (22)九．轻松删除Excel表格中的空行 (23)十．如何正确打印不连续区域的数据 (25)十一. 让数据分列排好序 (26)十二. 美观样式锦上添花 (27)十三. 数组函数运用范例 (28)十四. 在单元格中要打钩怎么办？ (31)十五. 不相邻的区域打印在同一页 (32)十六. 显示部分公式运行结果的技巧 (33)十七. 更改单元格格式后不刷新问题的解决方法 (34)十八. 原来打印考场清单可以这样 (35)十九. 在大量数据中提取特定数据 (37)二十. 让数据分列排好序 (41)二十一. 美观样式锦上添花 (42)二十二. 姓名筛选 (43)二十三. 谈Excel高速输入的技巧 (44)二十四. 巧妙移走Excel中的超链接三招 (46)二十五. 巧录录入Excel各种非常规数据 (47)二十六. 数据集导出excel时进行重组 (49)二十七. 如何用EXCEL表格作画 (51)二十八. 运用Excel三大条件函数解决实际问题 (54)二十九. 保护表格的隐藏单元格数据不被删除 (55)三十. 妙用Excel五个函数统计学生期末考试分数段 (59)三十一. 如何仅在部分单元格中设置图标 (62)三十二. 不同sheet同时输入一样内容 (67)三十三. 自动醒目的小计 (68)三十四. 永恒的间隔底纹 (70)三十五. 在工作日历中突显周休日 (73)一．制作竞赛评分自动计算表评分表是各种竞赛中常用的一种统计和亮分办法。

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

利用Excel中排序等相关功能制作的评分表，使总分计算实现自动化，名次排列也变成一个动态的过程，这样就使统计过程有很强的现场感，结果也会更具刺激性。

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

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

图12. 在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行，这样就可以算出所有选手的最后得分和名次。

二．用Excel巧做电子计分册现在许多学校都建起了校园网，过去很多费时费力的工作，现在放在校园网中都可以轻松地解决。

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

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

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

图11、建立工作表组打开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",""))”，该授课表星期一第一节就设置完毕。