EXCEL知识要点及题解.docx
《EXCEL知识要点及题解.docx》由会员分享，可在线阅读，更多相关《EXCEL知识要点及题解.docx（23页珍藏版）》请在冰豆网上搜索。
EXCEL知识要点及题解
EXCEL知识要点及题解
一、数组公式的使用
1．数组和数组公式
数组就是单元的集合或是一组处理的值的集合.
数组公式：
输入一个单个的公式，它执行多个输入操作并产生多个结果，每个结果显示在一个单元格区域中。
●与单值公式的区别：
它可以产生一个以上的结果，可以看成多重数值的公式
●数组公式的运算对象和结果都是具有相同大小的数组，如50行1列的数组（表中的区域）。
●各数组区域，形成一个整体，数组中的单元格不能单独进行处理。
2．数组公式的用法
①选定需要输入公式的单元格或单元格区域
②在编辑栏输入数组公式
公式的设计必须正确
③按组合键Ctrl+Shift+Enter
3．应用举例：
【样题1】
典型题01-1：
使用数组公式，对Sheet1计算总分和平均分，将其计算结果保存到表中的“总分”列和“平均分”列当中。
操作步骤：
①选择区域：
F2:
F39
②输入数组公式：
=C2:
C39+D2:
D39+E2:
E39
③按组合键Ctrl+Shift+Enter
●公式编辑栏显示{=C2:
C39+D2:
D39+E2:
E39}
④平均分设置同上，公式改为：
=F2:
F39/3
二、统计函数
排位统计函数：
RANK
1．功能：
返回一个数值在一组数值中的排位。
2．语法
RANK（number,ref,order）
Number ：
为需要找到排位的数字。
Ref：
为数字列表数组，其中的非数值型参数将被忽略。
Order：
为一数字，指明排位的方式。
如果order为0（零）或省略，降序排列；如果order不为零，升序排列。
3．应用举例
典型题01-2：
使用RANK函数，对Sheet1中的每个同学排名情况进行统计，并将排名结果保存到表中的“排名”列当中。
操作步骤：
1定位公式的位置：
选中H2
2打开插入函数对话框：
单击菜单“插入函数”
3选择函数：
●在“选择类别”中选择“全部”
●在“选择函数”小窗口中选中“RANK”（如左下图）
●单击“确定”按钮
说明：
选择函数时，可在“选择函数”列表中从键盘中键入“R”，可快速定位至以字母R开头的函数。
如果函数类别明确，应先选择明确的选择类别（如财务函数、文本函数等），然后再选择函数
4在“函数参数”设置对话框输入参数，如右上图
●Number ：
F2
●Ref：
$F$2:
$F$39
●Order：
0
说明：
用鼠标选定区域法输入F2:
F39后按F4键会自动将其转化为绝对引用$F$2:
$F$39，这里必须是绝对引用。
5插入函数：
单击“确定”按钮
6公式填充：
快速拖动H2填充柄到H39，或双击H2填充柄可快速填充到H39。
附注：
向工具栏添加“插入函数”按钮
：
右击工具栏[自定义]“类别”中选“插入”把“命令”栏中的“插入函数”命令拖放到工具栏。
MAX与MIN
1．功能
MAX：
返回一组值中的最大值；MIN：
返回一组值中的最小值。
2．应用举例
实训题6-3：
利用函数，根据Sheet1中的结果，符合以下条件的进行统计。
a．杭州这半个月以来的最高温度和最低温度；
b．上海这半个月以来的最高温度和最低温度；。
操作提示：
在C18单元格中输入：
=MAX（B2:
B16）
在C19单元格中输入：
=MIN（B2:
B16）
在C20单元格中输入：
=MAX（C2:
C16）
在C21单元格中输入：
=MIN（C2:
C16）
典型题2-4-b：
使用统计函数，对Sheel1中的“停车情况记录表”根据下列条件进行统计并填入相应单元格，要求：
b．统计最高的停车费用
操作提示：
在J10单元格中输入：
=MAX（G9:
G39）
COUNTIF
1．功能
有条件计数：
计算区域中满足给定条件的单元格的个数。
有条件求和，见数学函数SUMIF。
2．语法
COUNTIF（range,criteria）
Range：
为需要计算其中满足条件的单元格数目的单元格区域。
Criteria：
为确定哪些单元格将被计算在内的条件
3．应用举例
1．典型题1-4：
4.根据Sheet1中的结果，使用统计函数，统计“数学”考试成绩各个分数段的同学人数，将统计结果保存到Sheet2中的相应位置。
操作提示：
1定位到表Sheet2中单元格B2
2在单元格B2中输入：
●=COUNTIF（Sheet1!
$D$2:
$D$39,"<=20"）
3双击B2填充柄
4修改B3单元格公式为：
●=COUNTIF（Sheet1!
$D$2:
$D$39,"<=40"）-B2
5修改B4单元格公式为：
●=COUNTIF（Sheet1!
$D$2:
$D$39,"<=60"）-B2-B3
6修改B5单元格公式为：
●=COUNTIF（Sheet1!
$D$2:
$D$39,"<=80"）-B2-B3-B4
7修改B6单元格公式为：
●=COUNTIF（Sheet1!
$D$2:
$D$39,">80"）
2．典型题2-4-a：
使用统计函数，对Sheel1中的“停车情况记录表”根据下列条件进行统计并填入相应单元格，要求：
a．统计停车费用大于等于40元的停车记录条数
操作提示：
在J9单元格中输入：
=COUNTIF（G9:
G39,">=40"）
实训题1-4：
对Sheet1中的数据，根据以下条件,利用函数进行统计：
a.统计性别为“男”的用户人数，将结果填入Sheet2的B2单元格中；
b.统计年龄为“>40”岁的用户人数，将结果填入Sheet2的B3单元格中。
操作提示：
定位到表Sheet2中单元格B2
在B2单元格中输入：
=Countif（Sheet1!
B2:
B37,”男”）
在B3单元格中输入：
=Countif（Sheet1!
D2:
D37,”>=40”）
实训题5-3：
使用统计函数，对Sheet1中的数据，根据以下统计条件进行如下统计：
a．统计男性员工的人数，结果填入N3单元格中；
b．统计高级工程师人数，结果填入N4单元格中；
c．统计工龄大于等于10的人数，结果填入N5单元格中。
操作提示：
男性员工的人数：
N3=COUNTIF（D2:
D65,"男"）
高级工程师人数：
N4=COUNTIF（I2:
I65,"高级工程师"）
工龄大于等于10：
N5=COUNTIF（H2:
H65,">=10"）
COUNTBLANK
1．功能
计算指定单元格区域中空白单元格的个数。
。
2．语法
COUNTBLANK（range）
Range：
为需要计算其中空白单元格数目的单元格区域。
3．应用举例
实训题2-3：
某公司对各个部门员工吸烟情况进行统计，作为人力资源搭配的一个数据依据。
对于调查对象，只能回答Y（吸烟）或者N（不吸烟）。
根据调查情况，制做出Sheet3。
请使用函数，统计符合以下条件的数值。
a.统计未登记的部门个数；
b.统计在登记的部门中，吸烟的部门个数。
操作提示：
在单元格B14中输入：
=COUNTBLANK（B2:
E11）
在单元格B15中输入：
=COUNTIF（B2:
E11,"Y"）
三、逻辑函数
IF条件函数
难点：
嵌套
1．功能：
执行真假值判断，根据逻辑计算的真假值，返回不同结果。
2．语法
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可以嵌套七层
3．应用举例：
实训题6-1：
使用IF函数，对Sheet1中的“温度较高的城市”列进行自动填充
操作提示：
在D2单元格中输入：
=IF（B2>C2,"杭州","上海"）
双击D2单元格填充柄
实训题3-1：
使用IF函数，对Sheet1中的“学位”列进行自动填充。
要求：
填充的内容根据“学历”列的内容来确定（假定学生均已获得相应学位）：
-博士研究生－博士
-硕士研究生－硕士
-本科－学士
-其他－无
操作提示：
在H3单元格中输入：
=IF（G3="博士研究生","博士",IF（G3="硕士研究生","硕士",IF（G3="本科","学士","无"）））
双击H3单元格填充柄
注意：
除汉字，其他字符均为半角字符
AND函数
1．作用
当所有条件（参数）都满足时，返回的结果为TRUE；反之返回的结果为FALSE。
2．语法
AND（logical1,logical2,...）
Logical1,logical2,...：
表示待检测的1到30个条件值，各条件值可为TRUE或FALSE。
3．应用举例
典型题01-3：
使用逻辑函数判断Sheet1中每个同学的每门功课是否均高于平均分，如果是，保存结果为TRUE，否则，保存结果为FALSE，将结果保存在表中的“三科成绩是否均超过平均”列当中。
操作提示：
在I2单元格中输入：
=AND（C2>AVERAGE（$C$2:
$C$39）,D2>AVERAGE（$D$2:
$D$39）,E2>AVERAGE（$E$2:
$E$39））
双击I2单元格填充柄
OR函数
1．作用
当所有条件（参数）都不满足时，返回的结果为FALSE；反之返回的结果为TRUE。
2．语法
OR（logical1,logical2,...）
Logical1,logical2,...：
表示待检测的1到30个条件值，各条件值可为TRUE或FALSE。
3．应用举例
使用函数，判断Sheet2中的年份是否为闰年，如果是，结果保存“闰年”，如果不是，则结果保存“平年”，并将结果保存在“是否为闰年”列中。
说明：
闰年定义：
年数能被4整除而不能被100整除，或者能被400整除的年份。
操作提示：
在Sheet2相应单元格输入：
=IF（OR（AND（MOD（A1,4）=0,MOD（A1,100）<>0）,MOD（A1,400）=0）,"闰年","平年"）
说明：
A1是年份所在的单元格
四、查找和引用函数
HLOOKUP
1．功能
从一个数组或表格的第一行中查找含有特定值的字段，再返回同一列中指定行中值。
H：
Horizontal，水平的
2．语法
HLOOKUP（lookup_value,table_array,row_index_num,range_lookup）
Lookup_value：
为要在数组中搜索的数据
Table_array：
为要在其中查找数据的数据表
Row_index_num：
为table_array中待返回的匹配值的行序号。
表格中第一行为1。
●如果row_index_num小于1，函数HLOOKUP返回错误值#VALUE!
；
●如果row_index_num大于table-array的行数，函数HLOOKUP返回错误值#REF!
。
Range_lookup：
TRUE或FALSE
●如果为TRUE或省略，要求Table_array第一行的值必须以递增次序排列。
●如果range_value为FALSE，Table_array第一行的值不需要排列
3．应用举例
典型题02-1：
使用HLOOKUP函数，对Sheel1中的停车单价进行自动填充。
要求：
根据Sheel1中的“停车价目表”价格，利用HLOOKUP函数对“停车情况记录表”中的“单价”列根据不同的车型进行自动填充。
操作提示：
在C9单元格中输入：
=HLOOKUP（B9,$A$2:
$C$3,2,FALSE）
双击C9单元格填充柄
VLOOKUP
1．功能
从一个数组或表格的第一列中查找含有特定值的字段，再返回同一行中指定列中值。
V：
Vertical，垂直的
2．语法
与HLOOKUP类似
3．应用举例
实训题4-1.使用VLOOKUP函数，对Sheet1中的商品单价进行自动填充.
要求：
根据“价格表”中的商品单价，利用VLOOKUP函数，将其单价自动填充到采购表中的“折扣”列中。
操作提示：
在D11单元格中输入：
=VLOOKUP（A11,$F$2:
$G$4,2,FALSE）
双击D11单元格填充柄
五、日期与时间函数
利用日期与时间函数可以计算当前的时间，也可以用来分析或操作公式中与日期和时间有关的值。
HOUR
返回时间值的小时数。
MINUTE
返回时间值中的分钟，为一个介于0到59之间的整数。
YEAR
返回某日期对应的年份。
返回值为1900到9999之间的整数。
TODAY
返回当前系统的日期。
NOW
返回当前日期和时间所对应的序列号。
如果在输入函数前，单元格的格式为“常规”，则结果将设为日期格式。
应用举例
1．典型题02-2：
在Sheel1中，利用时间函数计算汽车在停车库中的停放时间，要求：
a．公式计算方法为“出库时间-入库时间”
b．格式为：
“小时：
分钟：
秒”
（例如：
一小时十五分十二秒在停放时间中的表示为：
“1：
15：
12”）
操作提示：
在F9单元格中输入：
=E9-D9
双击F9单元格填充柄
2．典型题2-3：
使用函数公式，计算停车费用，要求：
根据停放时间的长短计算停车费用，将计算结果填入到“应付金额”列中。
注意：
a．停车按小时收费，对于不满一个小时的按一个小时计费；
b．对于超过整点小时十五分钟的多累积一个小时。
（例如1小时23分，将以2小时计费）
操作提示：
在G9单元格中输入：
IF（HOUR（F9）=0,1,IF（MINUTE（F9）>15,HOUR（F9）+1,HOUR（F9）））*C9
双击G9单元格填充柄
实训题1-1：
使用时间函数，对Sheet1中用户的年龄进行计算。
要求：
计算用户的年龄，并将其计算结果填充到“年龄”列当中。
操作提示：
在D2单元格中输入：
=Year（Today（））-Year（C2）
双击D2单元格填充柄
实训题5-2：
使用时间函数，对Sheet1中员工的“年龄”和“工龄”进行计算，并将结果填入到表中的“年龄”列和“工龄”列中。
操作提示：
在F2单元格中输入：
=YEAR（TODAY（））-YEAR（E2）
双击F2单元格填充柄
在H2单元格中输入：
=YEAR（TODAY（））-YEAR（G2）
双击H2单元格填充柄
六、文本函数
REPLACE
1．功能
将某几位的文字以新的字符串替换。
2．语法
REPLACE（old_text，start_num，num_chars，new_text）
old_text：
原始的文本数据
start_num：
设置要从old_text的第几个字符位置开始替换
num_chars：
设置共有多少字符要被替换
new_text：
要用来替换的新字符串
3．应用实例
实训题1-2：
使用REPLACE函数，对Sheet1中用户的电话号码进行升级。
要求：
对“原电话号码”列中的电话号码进行升级。
升级方法是在区号（0571）后面加上“8”，并将其计算结果保存在“升级电话号码”列的相应单元格中。
操作提示：
在G2单元格中输入：
=REPLACE（F2,4,1,18）
双击G2单元格填充柄
EXACT
1．功能
该函数用来比较两个字符串是否完全相同。
如果它们完全相同，则返回TRUE；否则，返回FALSE。
函数EXACT能区分大小写，但忽略格式上的差。
2．语法
EXACT（text1,text2）
Text1：
待比较的第一个字符串。
Text2：
待比较的第二个字符串。
3．应用实例
实训题13-4：
使用函数，判断Sheet1中L12和M12单元格的文本字符串是否完全相同。
注意：
（a）如果安全相同，结果保存为TRUE，否则保存为FALSE。
（b）将结果保存在Sheet1中的N12单元格中
操作提示：
在N11单元格中输入：
=EXACT（L12,M12）
MID
1．功能
该函数用来返回文本字符串中从指定位置开始的特定数目的字符，该数目由用户指定.
2．语法
MID（text,start_num,num_chars）
Text：
是包含要提取字符的文本字符串。
Start_num：
是文本中要提取的第一个字符的位置。
文本中第一个字符的start_num为1，以此类推。
Num_chars：
指定希望MID从文本中返回字符的个数。
说明：
如果start_num大于文本长度，则MID返回空文本（""）。
如果start_num小于文本长度，但start_num加上num_chars超过了文本的长度，则MID只返回至多直到文本末尾的字符。
如果start_num小于1，则MID返回错误值#VALUE!
。
如果num_chars是负数，则MID返回错误值#VALUE!
。
CONCATENATE
1．功能
将几个文本字符串合并为一个文本字符串。
2．语法
CONCATENATE（text1,text2,...）
Text1,text2,... 为1到30个将要合并成单个文本项的文本项。
这些文本项可以为文本字符串、数字或对单个单元格的引用。
3．MID和CONCATENATE应用实例
实训题10-4：
操作提示：
在G3单元格中输入：
=CONCATENATE（MID（E3,7,4）,"年",MID（E3,11,2）,"月",MID（E3,13,2）,"日"）
双击G3单元格填充柄
LOWER/UPPER
1．功能
字母大小写转换，LOWER是将一个文本字符串中的所有大写字母转换为小写字母，而UPPER是将一个文本字符串中的所有小写字母转换为大写字母。
2．语法
LOWER（text）
UPPER（text）
Text：
是要转换为小写字母的文本。
函数LOWER不改变文本中的非字母的字符。
3．应用实例
实训题13-1：
操作提示：
在B3单元格中输入：
=UPPER（A3）
双击B3单元格填充柄
SEARCH
1．功能
返回指定的字符串在原始字符串中首次出现的位置。
2．语法
SEARCH（find_text,within_text,start_num）
Find_text：
是要查找的文本。
可以在find_text中使用通配符，包括问号（?
）和星号（*）。
问号可匹配任意的单个字符，星号可匹配任意一串字符。
如果要查找真正的问号或星号，请在该字符前键入波形符（~）。
Within_text：
是要在其中查找find_text的文本。
Start_num：
是within_text中开始查找的字符的编号。
3．应用实例
实训题16-4：
操作提示：
在K9单元格中输入：
=SEARCH（J9,I9,1）
七、财务函数
PMT
1．功能
计算贷款每期的偿还金额：
基于固定利率及等额分期付款方式，返回贷款的每期付款额。
PMT：
payment
2．语法
PMT（rate，nper，pv，fv，type）
rate：
贷款利率
nper：
该项贷款的总贷款期限或者总投资期
pv：
从该项贷款（或投资）开始计算时已经入账的款项，或一系列未来付款当前值的累积和
fv：
未来值，或在最后一次付款后希望得到的现金余额，如果忽略该值，将自动默认为0
type：
一个逻辑值，用以指定付款时间是在期初还是在期末，1表示期初，0表示期末，其默认值为0
3．应用实例
实训题08-4-（a）：
操作提示：
在E2单元格中输入：
=PMT（B4,B3,B2,0,0）
IPMT
1．功能
计算贷款每期的应付的利息额：
基于固定利率及等额分期付款方式，返回投资或贷款在某一给定期限内的利息偿还额。
IPMT：
Interestpayment
2．语法
PMT（rate，nper，pv，fv，type）
IPMT（rate，per，nper，pv，fv）
rate：
各期利率
per：
用于计算利息数额的期数，介于1～nper之间
nper：
总投资（或贷款）期，即该项投资（或贷款）的付款期总数
pv：
从该项投资（或贷款）开始计算时已经入账的款项，或一系列未来付款当前值的累积和
fv：
未来值，或在最后一次付款后希望得到的现金余额，如果忽略该值，将自动默认为0
3．应用实例
实训题08-4-（b）：
操作提示：
在E3单元格中输入：
=IPMT（B4/12,9,B3*12,B2,0）
提示：
一定要注意期限单位：
年、月、日，然后进行利率、期限等换算。
FV
1．功能
计算投资未来收益值（终值）：
基于固定利率及等额分期付款方式，返回某项投资的未来值。
FV:
futurevalue
2．语法
FV（rate，nper，pmt，pv，type）
rate：
各期利率
nper：
总投资（或贷款）期，即该项投资（或贷款）的付款期总数
pmt：
各期所应支付的金额
pv：
现值，即从该项投资开始计算时已经入账的款项，或一系列未来付款的当前值的累积和，也称为本金
type：
一个逻辑值，用以指定付款时间是在期初还是在期末，1表示期初，0表示期末，其默认值为0
3．应用实例
实训题14-4-（a）：
操作提示：
在B7单元格中输入：
=FV（B3,B5,B4,B2,0）
PV
1．功能
计算投资现值：
基一系列未来付款的当前值的累积和。
PV:
presentvalue
2．语法
PV（rate，nper，pmt，fv，type）
rate：
贷款利率
nper：
该项贷款的总贷款期限或者总投资期
pmt：
各期所应支付的金额
fv：
未来值，或在最后一次付款后希望得到的现金余额，如果忽略该值，将自动默认为0
type：
一个逻辑值，用以指定付款时间是在期初还是在期末，1表示期初，0表示期末，其默认值为0
实训题14-4-（b）：
操作提示：
在E7单元格中输入：
=PV（E3,E4,E2,0,0）
SLN
1．功能
某项资产在一个期间中的线性折旧值。
2．语法
SLN（cost，salvage，life）
SLN：
straightline
cost：
资产原值
salvage：
资产在折旧期末的价值，即资产残值
life：
折旧期限，即资产的使用寿命
3．应用实例
实训题10-4：
操作提示：
在E4单元格中输入：
=SLN（$B$2,$B$3,$B$4）
在E3单元格中输入：
=SLN（$B$2,$B$3,$B$4*12）
在E2单元格中输入：
=SLN（$B$2,$B$3,$B$4*365）
八、信息函数
ISTEXT
1．功能
判断单元格中的数据是否为文本。
2．语法
ISTEXT（value）
Value：
为需要进行检验的数值或单元格地址。
3．应用实例
实训题2-4：
使用函数，对Sheet3中的B21单元格中的内容进行判断，判断其是否问文本，如果是，结果为“TRUE”；如果不是，结果为“FALSE”，并将结果保存在Sheet3中的B22单元格中。
操作提示：
在B22单元格中输入：
=ISTEXT（B21）
九、数学函数
升级会员 