excle做法.docx
《excle做法.docx》由会员分享，可在线阅读，更多相关《excle做法.docx（29页珍藏版）》请在冰点文库上搜索。
excle做法
2.1数组的使用
2.2.1数组的意义
数组是单元的集合或是一组处理的值的集合。
可以写一个数组公式，即输入一个单个的公式，它执行多个输入操作并产生多个结果，每个结果显示在一个单元格区域中。
数组公式可以看成有多重数值的公式，它与单值公式的不同之处在于它可以产生一个以上的结果。
一个数组公式可以占用一个或多个单元区域，数组元素的个数最多为6500个。
数组应用示例：
将下图表中的“笔试成绩比例分”列按计算方法：
（笔试成绩/3）*60%填写。
1.1
图21
说明：
此问题可以用单个公式编辑再拖动引用公式的方法完成，但不是数组方式。
2.1.2数组的编辑
1．操作步骤：
（1）先选中要定义数组的全部数据区域J3:
J18；
（2）在编辑栏中按题目要求编写公式“=I3：
I18/3*0.6”；
（3）按ctrl+shift+enter键，所编辑的公式出现数组标志符号“{}”，同时J3：
J18列各个单元中生成相应结果如下图。
图22
2.其它练习
练习示例：
对上一问题中“面试成绩比例分”和“总成绩”列分别按公式“面试成绩*0.4”和“笔试成绩比例分+面试成绩比列分”进行填写。
操作方法：
参照上例进行。
2.1.3数组的修改
数组修改示例：
将上一题目中“笔试成绩比例分”按公式：
（笔试成绩/2）*60%计算。
1．操作步骤：
（1）选中数组的全部数据区域J3:
J18；
（2）在编辑栏中修改公式“=I3：
I18/2*0.6”；
（3）按ctrl+shift+enter键，所编辑的公式出现数组标志符号“{}”，同时J3：
J18列各个单元中生成相应结果。
2．说明：
（1）数组公式不能单个进行修改，否则系统提示错误。
（2）修改数组过程中数组标记“{}”会消失，需重新按ctrl+shift+enter键。
2.2高级筛选
2.2.1高级筛选
对于筛选条件比较复杂的情况，必须使用高级筛选功能来处理。
使用高级筛选功能必须先建立一个条件区域，用来指定筛选条件。
条件区域的第一行是所有作为筛选条件的字段名，这些字段名与数据列表中的字段名必须一致，条件区域的其它行则输入筛选条件。
需要注意的是条件区域与数据列表不能重叠，必须用空行或空列隔开。
条件区域的运算关系是：
同一行的条件是“与”，同一列的条件是“或”。
高级筛选应用示例：
将下图表中的数据进行筛选，条件为：
性别---女，所在区域---西湖区
图23
2.2.2高级筛选的编辑
1．操作步骤：
（1）在J3单元格输入“性别”，在K3单元格输入“所在区域”，在J4单元格输入“女”，在K4单元格输入“西湖区”；
（2）将光标点中数据区的任意位置；
图24
（3）点击菜单“数据””筛选””高级筛选”,出现数据区和条件区定义对话框;
（4）在对话框中定义对应的数据区$A$1:
$H$37和条件区$J$3:
$K$4，见图2-4，点击“确定”，生成筛选结果，如图2-5。
图25
2．说明：
（1）点击筛选菜单前必须把光标点进数据区，否则不能正确筛选。
（2）如果列名称中有空格，如“性别”，在筛选区中也使用相同的样式输入，否则不能正确筛选。
（3）条件的具体值如果是文本型如“女”、“西湖区”，直接书写；如果是数值型应写成相应的数学表达式，如条件“年龄大于40”，应在年龄字段名下写“>40”。
2.2.3撤销高级筛选
操作方法：
点击菜单“数据””筛选””全部显示”,恢复筛选前数据状态。
2.3透视表与透视图的使用
2.3.1使用透视表
一、透视表概述
数据透视表是一种对大量数据快速汇总和建立交叉列表的交互式表格，不仅能够改变行和列以查看源数据的不同汇总结果，也可以显示不同页面以筛选数据，还可以根据需要显示区域中的明细数据。
二、透视表的创建方法
透视表应用示例：
对图2-1表中的数据建立一个数据透视表，具体要求（a）显示每个报考单位的人的不同学历的总人数；（b）行区域设置为“报考单位”；（c）列区域设置为“学历”；（d）数据区域设置为“学历”;（e）统计为“学历”。
操作步骤：
（1）点击图表中的任意位置，选择“数据”“数据透视表和数据透视图向导”如图2-6,点击“下一步”;
图26
（2）出现图2-7，定义建立透视表的数据区，点击“下一步”；
图27
（3）定义透视表建立的位置，如图2-8，点击“完成”；
图28
（4）在如图2-9的环境中定义透视表的具体内容，根据题目要求将数据表字段列表窗口的“报考单位”字段拖动到透视表区域中的“将行字段拖至此处”位置，将“学历”字段拖动到透视表区域中的“将列字段拖至此处”位置，最后将将“学历”字段拖动到透视表区域中的“请将数据项拖至此处”位置。
图29
（5）生成透视表结果如图2-10
图220
说明：
透视表的内容也可以在步骤（3）中点击“布局”来定义。
2.3.2使用透视图
一、概述
数据透视图是将数据透视表结果赋以更加生动、形象的表示方式。
因为数据透视图需利用数据透视表的结果，因此其操作是与透视表相关联的。
二、创建方法
1．直接创建透视图
仍以图2-1为例，创建反映各报考单位学历状况的统计透视图。
操作步骤：
（1）见透视表操作步骤1，在图2-6“所需创建的报表类型”中选择“数据透视图”；
（2）见透视表操作步骤2；
（3）见透视表操作步骤3；
（4）在图2-11中的字段列表框中将“报考单位”字段拖至图下方的“在此处放置分类字段”区域，将“学历”字段拖放至右侧的“在此处放置系列字段”处，将“学历”字段拖放至中央数据区，生成如图2-12所示的结果。
图231
2．通过透视表创建透视图
在建立透视表后可直接建立相应的透视图。
操作步骤：
在已建立的透视表窗口，见图2-10，点击“图表向导”图标，也可以生成图2-12的透视图结果。
图242
说明：
创建透视图chart1的同时还会自动生成一张透视表。
2.4常用函数与应用示例
2.4.1日期时间函数
1.year函数
功能：
返回某日期对应的年份，返回值为1900到9999之间的整数。
格式：
YEAR（serial_number）。
说明：
serial_number是一个日期值，也可以是格式为日期格式的单元格名称。
2.today函数
功能：
返回当前日期。
格式：
TODAY（）
日期函数应用示例：
使用日期函数自动填写图2-3中的“年龄”字段结果。
操作步骤
（1）点击D2单元格，在编辑栏中编辑公式“=YEAR（TODAY（））-YEAR（C2）”；
（2）D2单元格产生结果42，用拖动引用公式的方法自动填充整个列年龄数据。
3.minute函数
功能：
返回时间值中的分钟，即一个介于0到59之间的整数。
格式：
MINUTE（serial_number）
说明：
serial_number是一个时间值，也可以是格式为时间格式的单元格名称。
4.hour函数
功能：
返回时间值的小时数。
即一个介于0到23之间的整数。
格式：
HOUR（serial_number）
说明：
serial_number是一个时间值，也可以是格式为时间格式的单元格名称。
时间函数应用示例：
见下一节中实战示例2问题2计算停车时间问题。
2.4.2逻辑函数
1.AND（与）函数
功能：
在其参数组中，所有参数逻辑值为TRUE，即返回TRUE。
格式：
AND（logical1,logical2,...）。
说明：
logical1,logical2,... 为需要进行检验的1到30个条件，分别为TRUE或FALSE。
2.OR（或）函数
功能：
在其参数组中，任何一个参数逻辑值为TRUE，即返回TRUE。
格式：
OR（logical1,logical2,...）。
说明：
logical1,logical2,... 为需要进行检验的1到30个条件，分别为TRUE或FALSE。
AND和OR函数应用示例：
见下一节中实战示例1问题4闰年问题。
3.IF函数
功能：
执行真假值判断，根据逻辑计算的真假值，返回不同结果。
格式：
IF（logical_test,value_if_true,value_if_false）。
说明：
Logical_test 表示计算结果为TRUE或FALSE的任意值或表达式，Value_if_true 是 logical_test为TRUE时返回的值，Value_if_false 是 logical_test为FALSE时返回的值。
If函数应用示例：
见图2-1，使用IF函数，对Sheet1中的“学位”列进行自动填充。
要求：
填充的内容根据“学历”列的内容来确定（假定学生均已获得相应学位）：
-博士研究生－博士
-硕士研究生－硕士
-本科－学士
-其他－无
操作步骤
（1）点击H3单元格，在编辑栏中编辑公式“=IF（G3="博士研究生","博士",IF（G3="硕士研究生","硕士",IF（G3="本科","学士","无"）））”；
（2）H3单元格产生结果"博士"，用拖动引用公式的方法生成整个列学位数据。
操作说明
（1）编辑公式中的各种符号应使用英文半角字符。
（其它公式编辑也同样，不再赘述。
）
（2）公式中的字符信息前后必须使用定界符““””。
（3）IF嵌套的层数不超过7个。
2.4.3算术与统计函数
1.RANK函数
功能：
为指定单元的数据在其所在行或列数据区所处的位置排序。
格式：
RANK（number,reference,order）。
说明：
number是被排序的值，reference是排序的数据区域，order是升序、降序选择，其中order取0值按降序排列，order取1值按升序排列。
RANK函数应用示例：
对图2-1中的数据，根据“笔试成绩”自动生成“排名”列的相应值。
操作步骤
（1）点击N3单元格，在编辑栏中编辑公式“=RANK（I3,$I$3:
I18,0）”
（2）N3单元格产生结果"1"，用拖动引用公式的方法生成整个列学位数据。
操作说明
（1）范围起始参数必须用”$I$3”即绝对引用方式,如使用“I3”相对引用方式则排序错误。
2.MOD函数
功能：
返回两数相除的余数。
格式：
MOD（number,divisor）。
说明：
Number 为被除数，Divisor 为除数。
MOD函数应用示例：
见下一节中实战示例1问题4闰年问题。
3.MAX函数
功能：
返回一组值中的最大值。
格式：
MAX（number1,number2,...）。
说明：
Number1,number2,... 是要从中找出最大值的1到30个数字参数。
MAX函数应用示例：
见下一节中实战示例2问题4统计最高停车费用问题。
4.COUNTIF函数
功能：
计算区域中满足给定条件的单元格的个数。
格式：
COUNTIF（range,criteria）
说明：
Range 为需要计算其中满足条件的单元格数目的单元格区域。
Criteria 为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式、单元格引用或文本。
COUNTIF函数应用示例：
见下一节中实战示例1问题2统计图书种类问题。
5.SUMIF函数
功能：
根据指定条件对若干单元格求和。
格式：
SUMIF（range,criteria,sum_range）。
说明：
Range 为用于条件判断的单元格区域，Criteria 为确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本。
SUMIF函数应用示例：
见下一节中实战示例1问题3统计各用户总金额问题。
2.4.4查找函数
1.HLOOKUP函数
功能：
在表格或数值数组 的首行查找指定的数值，并由此返回表格或数组当前列中指定行处的数值。
格式：
HLOOKUP（lookup_value,table_array,row_index_num,range_lookup）。
说明：
Lookup_value 为需要在数据表第一行中进行查找的数值。
Lookup_value可以为数值、引用或文本字符串。
Table_array 为需要在其中查找数据的数据表。
可以使用对区域或区域名称的引用。
Row_index_num 为table_array中待返回的匹配值的行序号。
Range_lookup 为一逻辑值，指明函数HLOOKUP查找时是精确匹配，还是近似匹配。
如果为TRUE或省略，则返回近似匹配值。
也就是说，如果找不到精确匹配值，则返回小于lookup_value的最大数值。
说明：
Lookup_value的值必须与table_array第一行的内容相对应。
HLOOKUP函数应用示例：
见下一节中实战示例2问题1统计自动填充停车费单价问题。
2.VLOOKUP函数
说明：
VLOOKUP函数的用法与HLOOKUP基本一致，不同在于table_array数据表的数据信息是以行的形式出现，见图2-18中A2:
C3区域，而VLOOKUP的table_array数据表是以列的形式出现。
2.4.5文本函数
1.REPLACE函数
功能：
使用其它文本字符串并根据所指定的字符数替换某文本字符串中的部分文本。
格式：
REPLACE（old_text,start_num,num_chars,new_text）。
说明：
Old_text 是要替换其部分字符的文本；Start_num 是要用new_text替换的old_text中字符的位置；Num_chars 是希望REPLACE使用new_text替换old_text中字符的个数，如果Num_chars 为“0”，是在指定位置插入新字符；New_text 是要用于替换old_text中字符的文本。
REPLACE函数应用示例：
在图2-3所示的表中，对“原电话号码”列中的电话号码进行升级。
升级方法是在区号（0571）后面加上“8”，并将其计算结果保存在“升级电话号码”列的相应单元格中。
操作步骤
（1）点击G2单元格，在编辑栏中编辑公式“=replace（f2,5,0,8）”;
（2）G2单元格产生修改后的电话号码，用拖动引用公式的方法生成整个列数据。
2.MID函数
功能：
返回文本字符串中从指定位置开始的特定数目的字符。
格式：
MID（text,start_num,num_chars）。
说明：
text 是包含要提取字符的文本字符串，Start_num 是文本中要提取的第一个字符的位置，Num_chars 指定希望MID从文本中返回字符的个数。
3.CONCATENATE函数
功能：
将几个文本字符串合并为一个文本字符串。
格式：
CONCATENATE（text1,text2,...）。
说明：
Text1,text2,... 为1到30个将要合并成单个文本项的文本项。
MID函数与CONCATENATE函数应用示例：
图2-13
见图2-13，仅使用文本函数MID函数和CONCATENATE函数，对Sheet1中的“出生日期”列进行自动填充。
要求：
a.填充的内容根据“身份证号码”列的内容来确定：
-身份证号码中的第7位~第10位：
表示出生年份;
-身份证号码中的第11位~第12位：
表示出生月份;
-身份证号码中的第13位~第14位：
表示出生日;
b.填充结果的格式为：
xxxx年xx月xx日（注意：
不使用单元格格式进行设置）。
操作步骤
（1）点击G3单元格，在编辑栏中编辑公式“=CONCATENATE（MID（E3,7,4）,"年",MID（E3,11,2）,"月",MID（E3,13,2）,"日"）”
（2）G3单元格产生指定格式的数据，用拖动引用公式的方法生成整个列数据。
2.4.6其它函数
二级等级考试的EXCEL部分对一级考试涉及的常用函数如SUM、AVERAGE等仍然需要熟练掌握。
另外还涉及一些函数需要掌握，限于篇幅大小不能详细介绍，此处仅仅做简单说明。
1.SLN函数
功能：
返回某项资产在一个期间中的线性折旧值。
格式：
SLN（cost,salvage,life）
说明：
Cost 为资产原值,Salvage 为资产在折旧期末的价值（也称为资产残值）,Life 为折旧期限（有时也称作资产的使用寿命）。
2.TYPE函数
功能：
返回数值的类型。
格式：
TYPE（value）.
说明：
Value 是数字、文本以及逻辑值等类型的表格数据，如果是数字，type的返回值为1；如果是字符，type的返回值为2；如果是逻辑值，type的返回值为4。
3.PMT函数
功能：
基于固定利率及等额分期付款方式，返回贷款的每期付款额。
格式：
PMT（rate,nper,pv,fv,type）。
说明：
Rate 贷款利率。
Nper 该项贷款的付款总数。
Pv 现值，或一系列未来付款的当前值的累积和，也称为本金。
Fv 为未来值，或在最后一次付款后希望得到的现金余额，如果省略fv，则假设其值为零，也就是一笔贷款的未来值为零。
Type 数字0或1，用以指定各期的付款时间是在期初还是期末,0或省略为期末，1为期初。
4.IPMT函数
功能：
基于固定利率及等额分期付款方式，返回给定期数内对投资的利息偿还额。
格式：
IPMT（rate,per,nper,pv,fv,type）。
说明：
Per 用于计算其利息数额的期数，必须在1到nper之间。
其它与上以函数相同。
5.数据库函数
数据库函数是一类函数，其基本格式为函数名（database,field,criteria）,其中database是构成数据清单或数据库的单元格区域，field是函数所使用的数据列，criteria是一组包含给定条件的单元格区域。
需要掌握的数据库函数有DCOUNT,DGET,DAVERAGE,DSUM等。
2.5二级考试实战示例与练习
实战示例1
如图2-14
图2-14
题目：
在考生文件夹的Paper子文件夹中，已有DExcel.xls文件。
按下列要求操作，并将结果存盘。
1.使用数组公式，计算Sheet1中的订购金额，将结果保存到表中的
“金额”列当中。
2.使用统计函数，对Sheet1中结果按以下条件进行统计，并将结果保存在
Sheet1中的相应位置，要求：
a.统计出版社名称为“高等教育出版社”的书的种类数；
b.统计订购数量大于110且小于850的书的种类数。
3.使用函数计算，每个用户所订购图书所需支付的金额总数，将结果
保存在Sheet1中的相应位置。
4.使用函数，判断Sheet2中的年份是否为闰年，如果是，结果保存“闰年”，
如果不是，则结果保存“平年”，并将结果保存在“是否为闰年”列中。
说明：
闰年定义：
年数能被4整除而不能被100整除，或者能被400整除的年份。
5.将Sheet1复制到Sheet3中，对Sheet3进行高级筛选，要求：
a.筛选条件为“订数>=500，且金额总数<=30000”；
b.将结果保存在Sheet2中。
（注：
无需考虑是否删除筛选条件）
6.根据Sheet1中的结果，在Sheet4中新建一张数据透视表，要求：
a.显示每个客户在每个出版社所订的教材数目；
b.行区域设置为：
“出版社”；
c.列区域设置为：
“客户”；
d.计数项为订数。
问题1：
操作步骤
（1）在Sheet1中选中填写金额的单元格i2：
i51；
（2）在编辑栏中书写公式“=G2:
G51*H2:
H51”；
（3）按ctrl+shift+enter键，所编辑的公式出现数组标志符号“{}”，同时i2：
i51列各个单元中生成相应结果。
问题2：
操作步骤
（1）在Sheet1L2单元格中编辑公式“=COUNTIF（D2:
D51,"高等教育出版社"）”；
（2）确定公式，生成统计结果;
（3）在Sheet1L3单元格中编辑公式“=COUNTIF（G2:
G51,">110"）-COUNTIF（G2:
G51,">850"）”；
（4）确定公式，生成统计结果。
说明：
（1）COUNTIF函数中如果使用条件表达式需要用引号引起。
（2）一个COUNTIF函数不能同时使用两个表达式，如Ｌ3中的两个条件关系需转化为两个COUNTIF函数计算。
问题3：
操作步骤
（1）在Sheet1L8单元格中编辑公式“=SUMIF（A2:
A51,"C1",I2:
I51）”，确定公式
（2）在Sheet1L9单元格中编辑公式“=SUMIF（A2:
A51,"C2",I2:
I51）”，确定公式
（3）在Sheet1L10单元格中编辑公式“=SUMIF（A2:
A51,"C3",I2:
I51）”，确定公式
（4）在Sheet1L11单元格中编辑公式“=SUMIF（A2:
A51,"C4",I2:
I51）”，确定公式
问题4：
操作步骤
（1）选中Sheet2表格，出现图2-15；
（2）在B2单元格中编辑公式“=if（or（and（mod（A2，4）=0，mod（A2，100<>0））,mod（A2,400）=0）,”闰年”,”平年”）”,确定公式；
图2–15
（3）拖动引用B2公式至B21，生成全部结果。
问题5：
操作步骤
（1）选择Sheet1中表格内容，复制到Sheet3中粘贴（A1：
I51部分）；
（2）在Sheet3K4：
L5单元格编辑高级筛选条件，其中K4中为“订数”，K5中为“金额总数”，L4中为“>=500”，L5中为“<=30000”；
（3）点击菜单“数据””筛选””高级筛选”,出现数据区和条件区定义对话框，按Sheet3中的实际位置定义；
（4）
生成结果如图2-16，复制数据内容，至Sheet2中粘贴。
图2-16
问题6：
操作步骤
（1）点击数据区中的任意位置，选择“数据”“数据透视表和数据透视图向导”，按数据透视表的一般定义过程定义相应内容；
（2）在如图2-9所对应的环境中，将“出版社”字段拖动至行字段区，将“客户”字段拖动至列字段区，将“订数”字段拖动至数据区，生成结果如图2-17；
图2-17
（3）根据题目要求，将工作簿中的Sheet4删除，将当前透视表所在工作表Sheet5更名为Sheet4。
实战示例2
如图2-18和图2-19
图2-18
图2-19
题目：
在考生文件夹的Paper子文件夹中，已有DExcel.xls文件。
按下列要求操作，并将结果存盘。
1.使用HLOOKUP函数，对Sheet1中的停车单价进行自动填充。
要求：
根据Sheet1中的“停车价目表”价格，利用HLOOKUP函数
对“停车情况记录表”中的“单价”列根据不同的车型进行自动填充。
2.在Sheet1中，利用时间函数计算汽车在停车库中的停放时间，要求：
a.公式计算方法为“出库时间-入库时间”
b.格式为：
“小时：
分钟：
秒”
（例如：
一小时十五分十二秒在停放时间中的表示为：
“1：
15：
12”）
3.使用函数公式，计算停车费用，要求：
根据停放时间的长短计算停车费用，将计算结果填入到“应付金额”列中。
注意：
a.停车按小时收费，对于不满一个小时的
升级会员 