EXCEL基础操作.docx
《EXCEL基础操作.docx》由会员分享，可在线阅读，更多相关《EXCEL基础操作.docx（60页珍藏版）》请在冰豆网上搜索。
EXCEL基础操作
EXCEL实例教程
一．制作竞赛评分自动计算表2
二．用Excel巧做电子计分册4
三．建立分期付款查询系统7
四．制作关联课程表11
五．建立合并计算12
六．如何不让数字自动转换为时间19
七．用FreQuency函数统计分数段优于IF函数20
八．妙用Shift键将Excel表格转为图片21
九．轻松删除Excel表格中的空行22
十．如何正确打印不连续区域的数据24
十一.让数据分列排好序25
十二.美观样式锦上添花26
十三.数组函数运用范例27
十四.在单元格中要打钩怎么办？
30
十五.不相邻的区域打印在同一页31
十六.显示部分公式运行结果的技巧32
十七.更改单元格格式后不刷新问题的解决方法33
十八.原来打印考场清单可以这样34
十九.在大量数据中提取特定数据36
二十.让数据分列排好序40
二十一.美观样式锦上添花41
二十二.姓名筛选42
二十三.谈Excel高速输入的技巧43
二十四.巧妙移走Excel中的超链接三招45
二十五.巧录录入Excel各种非常规数据46
二十六.数据集导出excel时进行重组48
二十七.如何用EXCEL表格作画50
二十八.运用Excel三大条件函数解决实际问题53
二十九.保护表格的隐藏单元格数据不被删除54
三十.妙用Excel五个函数统计学生期末考试分数段58
三十一.如何仅在部分单元格中设置图标61
三十二.不同sheet同时输入一样内容66
三十三.自动醒目的小计67
三十四.永恒的间隔底纹69
三十五.在工作日历中突显周休日72
一．制作竞赛评分自动计算表
评分表是各种竞赛中常用的一种统计和亮分办法。
传统的评分表是用手工记录、手工或计算器计算，然后再人工排序，这样做不仅效率低下，而且容易出错。
利用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行，这样就可以算出所有选手的最后得分和名次。
二．用Excel巧做电子计分册
现在许多学校都建起了校园网，过去很多费时费力的工作，现在放在校园网中都可以轻松地解决。
今天我们来看看学校中的成绩处理工作是如何在校园网中完成的。
建立电子计分册
要建立各班各科的成绩计分册，首先应该与教务处以及校园网的网管联系，取得学生的学籍数据备用。
如果没有，那就只有“自己动手，丰衣足食”了，呵呵。
图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分别表示一至六班的星期一第一节对应的单元格。
五．建立合并计算
一个公司内可能有很多的销售地区或者分公司，各个分公司具有各自的销售报表和会计报表，为了对整个公司的所有情况进行全面的了解，就要将这些分散的数据进行合并，从而得到一份完整的销售统计报表或者会计报表。
在Excel中系统提供了合并计算的功能，来完成这些汇总工作。
所谓合并计算是指，可以通过合并计算的方法来汇总一个或多个源区中的数据。
MicrosoftExcel提供了两种合并计算数据的方法。
一是通过位置，即当我们的源区域有相同位置的数据汇总。
二是通过分类，当我们的源区域没有相同的布局时，则采用分类方式进行汇总。
要想合并计算数据，首先必须为汇总信息定义一个目的区，用来显示摘录的信息。
此目标区域可位于与源数据相同的工作表上，或在另一个工作表上或工作簿内。
其次，需要选择要合并计算的数据源。
此数据源可以来自单个工作表、多个工作表或多重工作簿中。
在Excel2000中，可以最多指定255个源区域来进行合并计算。
在合并计算时，不需要打开包含源区域的工作簿。
11.1.1通过位置来合并计算数据
通过位置来合并计算数据是指：
在所有源区域中的数据被相同地排列，也就是说想从每一个源区域中合并计算的数值必须在被选定源区域的相同的相对位置上。
这种方式非常适用于我们处理日常相同表格的合并工作，例如，总公司将各分公司的合并形成一个整个公司的报表。
再如，税务部门可以将不同地区的税务报表合并形成一个市的总税务报表等等。
下面我们以一个实例来说明这一操作过程，建立如图11-1所示的工作簿文件。
在本例中我们将对工作表济南、青岛进行合并操作，其结果保存在工作表天津中，执行步骤如下：
（1）为合并计算的数据选定目的区，如图11-2所示。
（2）执行“数据”菜单中的“合并计算”命令，出现一个如图11-3的对话框。
（3）在“函数”框中，选定我们希望MicrosoftExcel用来合并计算数据的汇总函数，求和（SUM）函数是默认的函数。
（4）在“引用位置”框中，输入希望进行合并计算的源区的定义。
如果想不击键就输入一个源区域定义，先选定“引用位置”框，然后在工作表选项卡上单击“济南”，在工作表中选定源区域。
该区域的单元格引用将出现在“引用位置”框中，如图11-4所示。
（5）按下“添加”按钮。
对要进行合并计算的所有源区域重复上述步骤。
可以看到合并计算对话框如图11-5所示。
最后按下“确定”按钮。
我们就可以看到合并计算的结果，如图11-6所示。
（6）对于合并计算，我们还可以将保存在不同工作簿中的工作表进行合并操作，其操作步骤是在我们执行上述步骤2时，如果需要的工作表没有被打开，选择“浏览”按钮，之后出现一个如图11-7的浏览对话框，我们可以从中选定包含源区域的工作簿。
当我们选择了“确定”按钮后，MicrosoftExcel将在（引用位置）框中插入文件名和一个感叹号（!
）。
下一步我们可以键入单元格引用或源区域的名字，也可以为源区域键入全路径，工作簿名字和工作表名字。
然后在工作表名字后面键入一个感叹号（!
），并键入单元格引用或源区域的名字。
如果不能确定单元格引用区域，也可以先将该工作簿文件打开，然后利用“窗口”菜单，选择该工作簿文件，按上例中的步骤3完成选定工作。
重复上例中的步骤2到5，最后按下“确定”按钮完成合并计算的工作。
图11-8显示了一个引用不同工作簿的实例。
11.1.2通过分类来合并计算数据
通过分类来合并计算数据是指：
当多重来源区域包含相似的数据却以不同方式排列时，此命令可使用标记，依不同分类进行数据的合并计算，也就是说，当选定的格式的表格具有不同的内容时，我们可以根据这些表格的分类来分别进行合并工作。
举例来说，假设某公司共有两个分公司，它们分别销售不同的产品，如图11-9的显示，总公司要得到完整的销售报表时，就必须使用“分类”来合并计算数据。
下面我们就以汇总这两个分公司的销售报表事例来说明这一操作过程。
在本例中我们将对工作簿XS1、XS2进行合并操作，其结果保存在工作簿XS3中，执行步骤如下：
（1）为合并计算的数据选定目的区。
执行“数据”菜单中的“合并计算”命令，出现一个合并计算对话框。
在“函数”框中，选定用来合并计算数据的汇总函数。
求和（SUM）函数是默认的函数。
（2）在“引用位置”框中，输入希望进行合并计算的源区的定义。
如果想不击键就输入一个源区域定义，先选定“引用位置”框，然后在“窗口”菜单下，选择该工作簿文件，在工作表中选定源区域。
该区域的单元格引用将出现在“引用位置”框中。
对要进行合并计算的所有源区域重复上述步骤。
如果源区域顶行有分类标记，则选定在“标题位置”下的“首行”复选框。
如果源区域左列有分类标记，则选定“标题位置”下的“最左列”复选框。
在一次合并计算中，可以选定两个选择框。
在本例中我们选择“最左列”选项，如图11-10所示。
按下“确定”按钮。
我们就可以看到合并计算的结果，如图11-11所示。
11.1.3合并计算的自动更新
此外，我们还可以利用链接功能来实现表格的自动更新。
也就是说，如果我们希望当源数据改变时，MicrosoftExcel会自动更新合并计算表。
要实现该功能的操作是，在“合并计算”对话框中选定“链接到源”复选框，选定后在其前面的方框中会出现一个“√”符号。
这样，当每次更新源数据时，我们就不必都要再执行一次“合并计算”命令。
还应注意的是：
当源和目标区域在同一张工作表时，是不能够建立链接的。
六．如何不让数字自动转换为时间
Excel软件相信大家都经常会使用到，但是Excel本身的许多默认设置可能在使用中给新手造成许多的困难，比如默认的数字转换成时间的设置就非常的让许多初学者头疼，往往找不到解决办法。
今天我们就来说一下如何不让Excel自动将输入的数字转换成日期。
方法一：
选定单元格（可以是多个单元格）后选择菜单“格式→单元格”（或直接在单元格上单击鼠标右键后选择“设置单元格格式”）在打开的“单元格格式”窗口中单击“数字”选项卡，在“分类”中选定“文本”并“确定”即可。
方法二：
在输入内容的前面加上一个英文半角的单引号，就是告诉Excel将单引号后的内容作为文本处理（单引号本身并不会被打印出来）。
七．用FreQuency函数统计分数段优于IF函数
用Excel怎样统计出学生成绩各分数段内的人数分布呢？
很多文章都推荐使用CountIF函数，可是每统计一个分数段都要写一条函数，十分麻烦。
例如，要在C58:
C62内统计显示C2:
C56内小于60分、60至70之间、70至80之间、80至90之间、90至100之间的分数段内人数分布情况，要输入以下5条公式：
1.在C58内输入公式统计少于60分的人数：
=CountIF（C2:
C56,"<60"）
2.在C59内输入公式统计90分至100之间的人数：
=CountIF（C2:
C56,">=90"）
3.在C60内输入公式统计80至90之间的人数：
=CountIF（C2:
C56,">=80"）-CountIF（C2:
C56,">=90"），
4.在C61内输入公式统计70到80之间的人数：
=CountIF（C2:
C56,">=70"）-CountIF（C2:
C56,">=80"），
5.在C62内输入公式统计60到70之间的人数：
=CountIF（C2:
C56,">=60"）-CountIF（C2:
C56,">=70"）。
如果要把0至10之间、10至20之间、20至30……90至100之间这么多个分数段都统计出来，就要写上十条公式了。
其实，Excel已经为我们提供了一个进行频度分析的FreQuency数组函数，它能让我们用一条数组公式就轻松地统计出各分数段的人数分布。
例如，我们要统计出C2:
C56区域内0至100每个分数段内的人数分布：
1.在B58:
B68内输入：
0、9.9、19.9、……9.9、99.9、100。
2.用鼠标选择区域C58至C69，在编辑栏内输入“=FreQuency（C2:
C56,B58:
B69）”。
3.按“Crtl+Shift+Enter”组合键产生数组公式“={FreQuency（C2:
C56,B58:
B69）}”，这里要注意“{}”不能手工键入，必须按下“Crtl+Shift+Enter”组合键由系统自动产生。
完成后C58:
C69将显示分数分布情况。
八．妙用Shift键将Excel表格转为图片
Shift键在Word下有这样的妙处，如果我们在按下Shift键，同时点击“编辑”菜单，原来的复制和粘贴就会变成“复制图片”和“粘贴图片”。
利用这一功能，我们也可以将这方法移接到Excel中运用，把Excel一个数据表以图片的形式进行复制，从而将其转换为图片。
方法如下：
首先选中需要复制成图片的单元格区域，然后按住Shift键依次选择“编辑→复制图片”命令，接着弹出“复制图片”窗口，选择“图片”单选项后点击“确定”按钮，这时就将选定的表格区域复制成图片了。
最后复制到目标只需直接选择“粘贴”命令即可（或者按Shift键再选择“编辑→粘贴图片”命令）。
我们还可以将其在Word中进行粘贴。
另外，在复制图片时如果选择了“如打印效果”单选项，在粘贴的时候如果表格没有边框，复制后的图片也不会出现边框。
九．轻松删除Excel表格中的空行
在数据统计与分析过程中，由于记录的添加、删除以及数据表的合并等原因，经常会在表格中出现一些空行。
这些空行的存在既不美观，同时也影响了数据分析的结果。
如果一行一行地删除，费时费力，笔者这里总结了以下三种方法，让你轻松删除Excel表格中的空行。
一、定位删除法
利用Excel中提供的“定位”功能，一次性定位表格中的所有空行，然后将其“一网打尽”。
依次选择“编辑→定位”菜单命令，在出现的“定位”对话框中单击“定位条件”按钮，在接着弹出的“定位条件”对话框中点选“空值”单选项，最后单击“确定”按钮，这样就可以将表格中所有的空行全部选中了。
然后鼠标右击选中区域，在弹出的快捷菜单中依次选择“删除→整行”命令即可。
Cico提示：
使用该方法删除时要确保其他非空行中的所有单元格内均填有数值，否则会出现误删除记录的现象。
二、筛选删除法
利用Excel中提供的筛选功能将表格所有的空行筛选出来，然后将其删除。
由于Excel在筛选时只能识别连续的行，遇到空行时则不会继续向下进行筛选，所以在进行筛选操作之前，我们可以在表格中的任意位置插入一列，在该列中填充一个简单的序列让表格连续起来。
依次选择“数据→筛选→自动筛选”菜单命令，然后任选一个字段（如“性别”），选择筛选条件为“空白”，这样就可以将空行筛选出来。
接下来选中这些
升级会员 