Execl高级技术应用与实践.docx
《Execl高级技术应用与实践.docx》由会员分享，可在线阅读，更多相关《Execl高级技术应用与实践.docx（55页珍藏版）》请在冰豆网上搜索。
Execl高级技术应用与实践
第3章 Execl高级技术应用与实践
本章概述
MicrosoftExcel是美国微软公司开发的Windows环境下的电子表格系统，它是目前应用最为广泛的办公室表格处理软件之一。
自Excel诞生以来Excel历经了Excel5.0、Excel95、Excel97、Excel2000和Excel2003等不同版本。
随着版本的不断提高，Excel软件的强大的数据处理功能和操作的简易性逐渐走入了一个新的境界，整个系统的智能化程度也不断提高，它甚至可以在某些方面判断用户的下一步操作，使用户操作大为简化。
这些特性，已使Excel成为现代办公软件重要的组成部分。
Excel具有强有力的数据库管理功能、丰富的宏命令和函数、强有力的决策支持工具，它具有以下主要特点：
（一）分析能力
Excel除了可以做一些一般的计算工作外，还有400多个函数，用来做统计、财务、数学、字符串等操作以及各种工程上的分析与计算。
（二）操作简便
当需要将工作表上某个范围内的数据移到工作表上的另一个位置时，只需用按鼠标键，选定要移动的资料，将该范围资料拖动至所需的位置，松开鼠标即可。
如果要将公式或数据复制到临近的单元格内，可以拖动“填充柄”’，公式或数据就会被复制到目标单元格中。
此外，在使用Excel时，可以单击鼠标右键，屏幕上将出现相应的“快捷菜单”它将帮助用户尽快地寻找到所需要的常用命令。
Excel内有许多工具按钮，每一个按钮代表一个命令。
例如，要建立一个新的工作簿文件，就可直接按下工具栏第一个按钮，而不必先选择“文件”菜单，然后再选择其中的“新建”命令。
（三）图表能力
在Excel中，系统大约有100多种不同格式的图表可供选用，用户只要做几个简单的按键动作，就可以制作精美的图表。
通过图表指南一步步的引导，可使用不同的选项，得到所需的结果，满意的话就继续，不满意则后退一步，重新修改选项，直到最后出现完美的图表。
（四）数据库管理能力
对于一个公司，每天都会产生许多新的业务数据，例如，销售数据、库存的变化、人事变动的数据资料等。
这些数据必须加以处理，才能知道每段时间的销售金额、某个时候的存货量、要发多少薪水给每个员工等。
要对这些数据进行有效的处理，就离不开数据库系统。
所谓数据库系统，就是一组有组织的信息。
例如，将每位职员的简历写在一张卡片上，卡片放在盒子内，盒子内的数据通常组成列和行。
再如，每种产品的产地、规格、单位、单价、数量组成一列，每一行都包含同一属性的数据，每列都包含同一品种的数据，即每列都有产地、规格、单位、单价、数量。
管理数据库可用专门的数据库管理软件，如FoxPro，Access，Oracle，Sqlserver等。
在Excel中提供了类似的数据库管理功能，保存在工作表内的数据都是按照相应的行和列存储的，这种数据结构再加上Excel提供的有关处理数据库的命令和函数，使得Excel具备了能组织和管理大量数据的能力。
（五）宏语言功能
利用Excel中的宏语言功能，用户可以将经常要执行的操作的全过程记录下来，并将此过程用一简单的组合按键或工具按扭保存起来。
这样，在下一次操作中，只需按下所定义的宏功能的相应按键或工具按钮即可，而不必重复整个过程。
例如，可以定义一个打开最后编辑文件且可以自动执行的宏，以后当用户打开Excel后，将自动打开上一次编辑的工作簿。
在Excel中，高级用户可使用VisualBasic语言，进行宏命令的开发。
利用宏命令，用户可以将Excel的下拉菜单和对话框更改或将图形按钮的说明更换，使它们更适合于用户的工作习惯和特殊要求。
（六）样式功能
在Excel中，用户可以利用各种文字格式化的工具和制图工具，制作出美观的报表。
Excel工作表里的资料，在打印以前可将其放大或缩小进行观察，用户可以对要打印的文件作微调。
用户可将要打印出的格式制作好，并存储成样本，以后可以读取此样本文件，就可依据样本文件的格式打印出美观的报表。
Excel的专业文书处理程序具有样式工具。
所谓样式，就是将一些格式化的组合用一个名称来表示，以后要使用这些格式化的组合时，只要使用此名称即可，因此可大幅度地节省报表格式化的时间。
（七）对象连接和嵌入功能
利用对象连接和嵌入功能，用户可将其他软件（例如，画笔）制作的图形插入到Excel的工作表中。
当需要更改图案时，只要在图案上双击鼠标键，制作该图案的程序就会自动打开，图案将出现在该图形编辑软件内，修改、编辑后的图形也会在Excel内显示出来。
也可以将一个声音文件或动画文件嵌入到Excel工作表中，使工作表变成一幅声形并貌的报表。
（八）连接和合并功能
通常，每个工作在一张工作表上执行即可，早期的工作表软件都只能在一张工作表上执行。
但有时需要同时用到多张工作表，例如，公司内每个分公司每月都会有会计报表，要将各分公司区的资料汇总起来，就需要用到连接和合并功能。
Excel很容易将工作表连接起来，并进行汇总工作。
任务一学生成绩表的制作
一、任务的提出
我们经常要制作成绩表并且对学生成绩要进行分析。
输入成绩单时，要进行数据有效性检验。
其中包括：
学号不能重复、成绩必须在0-100之间，备注只能输入是空、免修、缓考、缺考。
根据平时成绩、期末成绩、实验成绩所占的比例计算总评成绩。
在成绩单的下方，按总评成绩分段统计人数及所占百分比，并且统计缓考、缺考、免修、实考的人数。
进行成绩分析时，除了统计人数和百分比之外，还要计算平均值、标准差，
并制作成绩分布的图表。
二、任务的结果
做成的Excel文件包含两个工作表：
成绩分析和成绩单，如图3-1、3-2所示。
图3-1学生成绩分析
图3-2学生成绩单
三、知识准备
制作如图3-1、3-2所示的成绩单和成绩分析表，需要用到Excel的一部分函数，还要设置单元格的数据有效性。
1.Excel相关函数
（1）COUNTIF函数
功能：
对指定区域中满足单个指定条件的单元格进行计数。
例如：
COUNTIF（B2:
B25,”张三”），统计B2到B5的单元格中值为“张三”的单元格的个数。
语法格式：
COUNTIF（range,criteria）
参数说明：
Range：
要对其进行计数的一个或多个单元格。
Criteria：
用于定义将对那些单元格进行计数的数字、表达式、单元格引用或文本字符串。
例如，条件可以表示为32、”>32”、B4、”苹果”或“32”。
假设工作表中A1-A5单元格的值如图3-3所示。
图3-3COUNTIF函数用到的测试数据
表3-1给出了各个COUNTIF函数的值。
表3-1COUNTIF函数实例
公式
说明
结果
=COUNTIF（A1:
A4,”苹果”）
单元格区域A1到A4中包含”苹果”的单元格的个数
2
=COUNTIF（A1:
A4,A3）
单元格区域A1到A4中包含”桃子”（A3单元格的值）的单元格的个数
1
=COUNTIF（A1:
A4,A3）+COUNTIF（A1:
A4,A1）
单元格区域A1到A4中包含”桃子”（A3单元格的值）和”苹果”（A1单元格的值）的单元格的个数
3
=COUNTIF（B1:
B4,”>55”）
单元格区域B1到B4中值大于55的单元格的个数
2
=COUNTIF（B1:
B4,”<>“&B3）
单元格区域B2到B5中值不等于75（B4单元格的值）的单元格的个数
3
=COUNTIF（B1:
B4,”>=32”）-COUNTIF（B1:
B4,”>85”）
单元格区域B2到B5中值大于或等于32且小于或等于85的单元格的个数
3
=COUNTIF（A:
A，A1）
在A列中值等于A1单元格的值的单元格个数
2
（2）AND函数
函数功能：
返回逻辑值。
如果所有参数均为逻辑真（TRUE），则返回逻辑真（TRUE），反之返回逻辑假（FALSE）。
语法格式：
AND（logical1,logical2,...）
参数说明：
Logical1,Logical2,Logical3……：
表示待测试的条件值或表达式，最多这30个。
应用举例：
在C5单元格输入公式：
=AND（A5>=60,B5>=60）。
如果A5和B5中的数值均大于等于60，则C5的值为TRUE。
如果A5和B5中的数值至少有一个小于60，则C5的值为FALSE。
（3）OR函数
函数功能：
返回逻辑值。
只要有一个参数为逻辑真（TRUE），则返回逻辑真（TRUE），反之返回逻辑假（FALSE）。
语法格式：
OR（logical1,logical2,...）
参数说明：
Logical1,Logical2,Logical3……：
表示待测试的条件值或表达式，最多这30个。
应用举例：
在C5单元格输入公式：
=OR（A5>=60,B5>=60）。
如果A5和B5中有一个单元格的数值大于等于60，则C5的值为TRUE。
如果A5和B5中的数值都小于60，则C5的值为FALSE。
（4）LEN函数
函数功能：
返回文本字符串中的字符个数
语法格式：
LEN（text）
参数说明：
text是要计算长度的文本字符串，包括空格。
应用举例：
函数LEN（“abcd”）的值等于5。
LEN（A1）的值等于A1单元格中文本字符串的字符个数。
函数OR（LEN（A1）=15,LEN（A1）=18）的值取决于A1单元格中字符的个数。
如果A1中的字符个数等于15或18，函数的值等于逻辑真（TRUE）。
A1中的字符个数既不等于15也不等于18时，函数的值等于逻辑假（FALSE）。
函数AND（COUNTIF（A:
A,A1）<2,OR（LEN（A1）=15,LEN（A1）=18））的值取决于A1单元格中字符的个数和A1单元格的值在A列是否重复。
如果单元格A1的值同时满足两个条件：
A1的值在A列不重复，A1中的文本的长度等于15或18，则该函数的值为逻辑真（TRUE），否则为逻辑假（FALSE）。
（5）SUM函数
函数功能：
返回某一单元格区域中所有数字之和。
语法格式：
SUM（number1,number2,...）
参数说明：
Number1,number2,...是要对其求和的1到30个参数。
应用举例：
SUM（A1：
A10）对A1~A10单元格求和。
（6）AVERAGE函数
函数功能：
返回一单元格区域中所有数字的平均值（算术平均值）。
语法格式：
AVERAGE（number1,number2,...）
参数说明：
Number1,number2,...是要计算其平均值的1到30个数字参数。
（7）STDEVP函数
函数功能：
返回基于样本估算标准偏差。
语法格式：
STDEVP（number1,number2,...）
参数说明：
Number1,number2,...是对应于总体中的样本的1到30个数字参数。
（8）INDIRECT函数
函数功能：
返回由文本字符串指定的引用。
语法格式：
INDIRECT（ref_text,[a1]）
参数说明：
Ref_text：
必需，对单元格的引用，此单元格包含A1样式的引用、R1C1样式的引用、定义为引用的名称或对作为文本字符串的单元格的引用。
如果ref_text不是合法的单元格的引用，函数INDIRECT返回错误值#REF!
。
如果ref_text是对另一个工作簿的引用（外部引用），则那个工作簿必须被打开。
如果源工作簿没有打开，函数INDIRECT返回错误值#REF!
。
A1：
可选，一个逻辑值，用于指定包含在单元格ref_text中的引用的类型。
如果a1为TRUE或省略，ref_text被解释为A1-样式的引用。
如果a1为FALSE，则将ref_text解释为R1C1样式的引用。
例如：
INDIRECT（“sheet2!
A1:
A6”），表示引用sheet2工作表中的A1~A6单元格。
如果要引用其它工作表中的单元格，要使用INDIRECT函数。
2.数据有效性设置
Excel强大的制表功能，给我们的工作带来了方便，但是在表格数据录入过程中难免会出错，比如重复的身份证号码，超出范围的无效数据等。
Excel中的数据有效性功能可以对一个单元格输入的数据作出限制，也可以自行设定数据有效性。
比如，可以限制输入的数值要大于1000。
利用有效性功能还可以实现下拉列表。
（1）设置数据范围
在菜单栏选择“数据”→“有效性”，弹出如图3-4所示的对话框。
图3-4数据有效性对话框
在这个对话框可以设置有效性条件、输入信息、出错警告和输入法模式。
STEP1设置数据的范围
在设置选项卡中的允许下拉框中，可以选择整数、小数、日期、时间、文本长度，如图3-5所示。
然后再数据的下拉框选择条件，在最大值、最小值下面输入框输入相应的最大值和最小值，如图3-6所示。
STEP2设置提示信息
如果对某个单元格设置了提示信息，那么当用键盘或鼠标选中该单元格时，在该单元格的右下方会显示这些提示信息。
这里的提示信息和批注有一定的区
图3-5有效性条件类型
图3-6数据范围设置
别，在单元格插入批注后，只有用鼠标选中单元格才会显示批注。
用键盘选中单元格，不显示批注。
设置提示信息选项卡如图3-7所示。
例如，选中excel表中的B2单元格，选择菜单栏“数据”→“有效性”→“输入信息”，在输入信息选项卡的标题的文本框输入“性别”，在“输入信息”文本框输入“请输入男或女”。
单击“确定”。
用鼠标或键盘选中B2单元格后，就会出现如图3-8所示的提示信息。
图3-7输入信息选项卡
图3-8带有提示信息的工作表
STEP3设置出错警告
在“出错信息”选项卡中可以设置出错警告对话框的样式、标题和错误信息，例如，选中A1单元格，在有效性条件的“设置”选项卡中，在“允许”栏选择“整数”，在“数据”栏选择“介于”，在“最小值”栏输入1，在“最大值”栏输入120，如图3-9所示。
在“出错警告”选项卡中，在“样式”栏选择“停止”，在“标题栏”输入“年龄输入错误”，在“错误信息栏”输入“年龄应在1到120岁之间。
”，如图3-10所示。
这时如果A1单元格的数据不在1到120之间，则弹出如图3-11所示的对话框。
图3-9设置范围选项卡
图3-10设置错误信息选项卡
图3-11出错信息对话框
（2）设置自定义公式
打开“数据有效性”对话框，在“允许”下拉列表框，选择“自定义”时，通过设置公式可以自定义有效性条件。
例如，为了避免在A列有重复输入，先选中A列的单元格，在自定义的公式栏输入“=COUNTIF（A:
A,A1）<2”，如图3-12所示。
图3-12自定义公式
对于A1单元格，自定义的有效性公式为“=COUNTIF（A:
A,A1）<2”，即A1单元格的值在A列不重复。
对于A2单元格，自定义的有效性公式自动变成“=COUNTIF（A:
A,A2）<2”，以此类推。
（3）设置序列
有时候我们在各列或各行中都输入同样的几个值，例如：
输入学生的等级时只输入四个值：
优秀、良好、合格、不合格。
这是希望Excel单元格能象下拉列表框一样，直接选择就可以实现输入。
Excel可以通过设置单元格的数据有效性中的“序列”来实现。
选中单元格或单元格区域，选择菜单“数据”→“有效性”，在“允许”条件栏选择“序列”，如图3-13所示。
这时还需要输入序列的来源，序列来源有三种：
直接输入、来源于本工作表的单元格、来源于其它工作表的单元格。
如果序列的选项比较少，可以直接输入来源，各选项之间用英文逗号分隔。
序列设置完后，在录入性别时就可以象下拉列表框一样，选择输入，如图3-14所示。
图3-13直接输入序列来源
图3-14用下拉列表框输入数据
如果序列的选项比较多，来源可以选择本工作表的单元格区域，如图3-15所示。
在来源栏可以直接输入单元格区域，也可以单击
进行选择。
序列的来源也可以选择其他的工作表，这时需要使用INDIRECT函数。
例如，要设置sheet1工作表中单元格的数据有效性，而序列来源于sheet2工作表的单元格区域B2~B5。
那么在来源栏要输入“=INDIRECT（“sheets!
B2:
B5”）”。
图3-15序列来源于本工作表的单元格区域
四、任务实施过程
学生成绩表的制作分三步进行，先设计成绩登记表和成绩分析表的空表，然后设置单元格的数据有效性和计算公式，再输入数据。
STEP1设计成绩登记表和成绩分析表的空表如图3-16、3-17所示。
STEP2设置单元格的数据有效性
1.选中要输入学号的单元格区域，选择菜单“数据”→“有效性”，在“设置”选项卡的“允许”栏，选择“自定义”，在“公式”栏输入“=AND（LEN（B4）=13,COUNTIF（B:
B,B4）<2）”（不要输入引号），表示学号的长度是13，并且学号不重复。
2.选中要输入成绩的区域，在数据有效性的“设置”选项卡中，在“允许”栏选择“整数”，在“数据”栏选择“介入”，在“最小值”输入1，在“最小值”栏输入100。
表示成绩是整数，并且在1到100之间。
3.选中要输入的备注单元格区域，在数据有效性的“设置”选项卡中，在“允许”栏选择“序列”，在“来源”输入“免修,缓考,缺考”。
STEP3设置单元格的计算公式
图3-16成绩登记表空表
图3-17成绩分析表空表
1.设置总评成绩的计算公式
总评成绩的计算公式为：
总评成绩=平时成绩*10%+期末成绩*60%+实验成绩*30%。
选中I4单元格，在单元格中输入“=0.1*F4+0.6*G4+0.3*H4”，然后将单元格I4的计算公式复制到下面的单元格。
2.设置“成绩单”工作表中各成绩段的人数和百分比计算公式。
在“90分以上”这一行，在D20单元格输入公式“=COUNTIF（$I$4:
$I$18,">=90"）&"人"”，在F20单元格输入公式“=COUNTIF（$I$4:
$I$18,">=90"）*100/COUNT（$I$4:
$I$18）”。
在“80-89分”这一行，在D21单元格输入公式“=COUNTIF（$I$4:
$I$18,">=80"）-COUNTIF（$I$4:
$I$18,">=90"）&"人"”，在F21单元格输入公式“=（COUNTIF（$I$4:
$I$18,">=80"）-COUNTIF（$I$4:
$I$18,">=90"））*100/COUNT（$I$4:
$I$18）”。
在“70-79分”这一行，在D22单元格输入公式“=COUNTIF（$I$4:
$I$18,">=70"）-COUNTIF（$I$4:
$I$18,">=80"）&"人"”，在F22单元格输入公式“=（COUNTIF（$I$4:
$I$18,">=70"）-COUNTIF（$I$4:
$I$18,">=80"））*100/COUNT（$I$4:
$I$18）”。
在“60-69分”这一行，在D23单元格输入公式“=COUNTIF（$I$4:
$I$18,">=60"）-COUNTIF（$I$4:
$I$18,">=70"）&"人"”，在F23单元格输入公式“=（COUNTIF（$I$4:
$I$18,">=60"）-COUNTIF（$I$4:
$I$18,">=70"））*100/COUNT（$I$4:
$I$18）”。
在“不及格”这一行，在D24单元格输入公式“=COUNTIF（$I$4:
$I$18,"<60"）&"人"”，在F24单元格输入公式“=COUNTIF（$I$4:
$I$18,"<60"）*100/COUNT（$I$4:
$I$18）”。
在“合计”这一行，在D25单元格输入公式“=COUNTA（B4:
B18）&"人"”，在F25单元格输入公式“=COUNTIF（$I$4:
$I$18,"<60"）*100/COUNT（$I$4:
$I$18）”。
在“缓考、缺考”这一行，在C26单元格输入公式“=COUNTIF（$J$6:
$J$16,"缓考"）+COUNTIF（$J$6:
$J$16,"缺考"）&"人"”，在E26单元格输入公式“=COUNTIF（$J$6:
$J$16,"免修"）&"人"”。
在实考人数栏的G26单元格输入公式“=COUNTA（$B$4:
$B$18）-COUNTA（$J$4:
$J$18）&"人"”
说明：
在公式中用到了“&”运算符，是连接前后的文本（或数字）。
如：
2&”人”，运算结果为2人。
3.设置“成绩分析”工作表中各成绩段的人数和百分比计算公式。
在“人数”这一行的C4单元格中，输入公式“=COUNTIF（INDIRECT（"成绩单!
$I$4:
IJ$18"）,">=90"）”，然后将该单元格的公式复制到右边的单元格。
在“所占百分比”这一行的C5单元格输入公式“=C4*100/SUM（$C$4:
$G$4）”，然后将该单元格的公式复制到右边的单元格。
在“平均值”右边的C6单元格输入公式“=AVERAGE（INDIRECT（"成绩单!
$I$4:
$I$18"））”，在“标准差”右边的单元格输入公式“”=STDEVP（INDIRECT（"成绩单!
$I$4:
$I$18"））。
STEP4设计成绩分布饼图
选择菜单“插入”→“图表”，在图表类型中选择“饼图”→“下一步”，在数据区域中选择区域“=成绩分析!
$C$3:
$G$4”→“下一步”，在“数据标志”选项卡中，在“数据标签包括”栏勾选“值”→“完成”。
五、常用技巧
1.在设置数据有效性时，一次可以选中多个单元格同时设置。
也可以先设置一个单元格，然后通过自动填充，将设置好的数据有效性复制到其它单元格。
自动填充操作如下:
（1）选中欲复制数据的初始单元格（或单元格范围）。
（2）将鼠标指针指向填充柄，待鼠标指针变成黑色十字。
（3）按住鼠标左键拖动填充柄经过所有欲填充的单元格，然后释放鼠标键。
2.在输入公式时，如果公式中含有引号、冒号、逗号、括号等符号，一定要在英文的状态下输入，否则要输入一个复杂的公式，很难成功。
3.用COUNTIF函数统计I4~I8单元格区域成绩在70~79之间的人数时，可以公式“=COUNTIF（$I$4:
$I$18,">=60"）-COUNTIF（$I$4:
$I$18,">=70"”。
即用60分以上的人数减去70分以上的人数。
六、任务拓展
1.在本例中，统计各分数段的人数和百分比时，把缺考、缓考、免修的人数、成绩全部计算在内，统计结果不能准确地反应实际情况。
要求对本例进行修改，缺考、缓考、免修的学生不在统计范围之内。
2.针对本例，在学号的左边增加一列“所属院系”。
所属院系通过选择下拉列表框来实现输入。
学校所有的院系存放在另外的工作表中。
任务二职工工资表的制作
一、任务的提出
在人事部门每月都要制作职工工资表，要求根据每月基本工资、补贴补助和奖金，计算出应发工资。
从应发工资中扣除代缴保险和纳税起点2000元，计算出应纳税所得额。
根据应纳税所得额，计算出速算扣除数和个税税率。
根据应纳税所得金额、个税税率和速算扣除数，计算出应纳税额。
最后根据应发工资、代缴保险和应纳税额计算出实发工资。
完成“工资表”后还要制作“工资条”，在“工资条”工作表，每个职工占两行，第1行是标题
升级会员 