XX省计算机二级考试“办公自动化高级应用〞中Excel 考试题常用函数：
[一类] 一般的函数
1．AVERAGE平均值函数: AVERAGE〔被求平均值的区域〕
SUM(range) 求和函数
2．函数 MAX〔range〕功能：求range内的最大值。
函数 MIN〔range〕功能: 求range内的最小值。
3．函数 ABS〔value〕功能：取value的绝对值。
4. 取除法后得余数的函数：MOD〔被除数，除数〕
[二类] IF〔条件〕函数
5．IF函数：
IF〔判别条件，条件成立(真)时取值，条件不成立(假)时取值〕
例：=IF〔C5〔成绩〕<60，"不与格"，"与格"〕
IF函数的嵌套。
Excel中有关逻辑函数
⑴AND〔X条件， Y条件， Z条件，……〕都成立〔TRUE〕，则最终成立。
⑵OR〔X条件， Y条件， Z条件，……〕有一个条件成立，则最终成立。
⑶NOT〔条件〕反之！
[三类] 条件统计函数
6．条件统计函数
① 条件统计个数的函数COUNTIF(被统计个数的区域之绝对引用，条件)
例：数学分数位于0到20分〔含0〕的人数：=COUNTIF(Sheet1!$D$2:$D$39,"<20")
② 条件求和函数：SUMIF(要比较条件的区域，条件，被求和的区域)
例：SUMIF($A$11:$A$43,〞=衣服“,$B$11:$B$43)
[四类] 引用函数
7． 函数RANK的用法：RANK〔排名次的数据依据单元，排名的X围——绝对引用，0(或缺省)/非0〕
说明：0〔或缺省〕以所在单元的名次是降序的位数排名，否则升序。
例：如平均分80.00在G2，全部平均分为$G$2:$G$39，则：
RANK(G2,$G$2:$G$39,0)
8. VLOOKUP叫引用函数；一个数组或表格②table_array的最左列中含有特定值的字段，到另一数据表格、数组或数据库①look_value去查，到同值时，把②的第col_index_num列的对应值填到①的某一指定单元格中。
VLOOKUP〔lookup_value，talbe_array，col_index_num，range_lookup〕
look_value：被查的列的值， 如A11,A12,……会相对下去
table_array：引用的数据表格、数组或数据库，如$F$2:$G$4 绝对引用。纵向对照表
col_index_num：一个数字，代表要返回的值位于table_array中的第几列。
rang_lookup：一个逻辑值，如果其值为TRUE或被省略，则符合多少算多少；如果该值为FALSE时，函数只会查完全符合的数值，如果不到，则返回错误值“＃N/A〞。
例：VLOOKUP(A11,$F$2:$G$5,2,FALSE)
第2列 Look表 | ||||||
A | B | C | D | |||
项目 | …… | …… | 单价 | …… | …… | |
11 | 衣服 | |||||
12 | 裤子 | |||||
鞋子 | ||||||
衣服 | ||||||
裤子 | ||||||
鞋子 | ||||||
衣服 | ||||||
裤子 | ||||||
…… | ||||||
…… | ||||||
table表 | ||
F | G | |
1 | 价格表 | |
2 | 项目 | 单价 |
3 | 衣服 | 120 |
4 | 裤子 | 80 |
5 | 鞋子 | 150 |
table表 | ||||
F | G | H | ||
1 | 项目 | 衣服 | 裤子 | 鞋子 |
2 | 单价 | 120 | 80 | 150 |
9. HLOOKUP横向对照表：
第2行
[五类] 数据库函数
10. 库函数格式为：函数名称〔database，field，criteria〕
database〔数据库〕：包含有每列项目标题的长方形单元格区域，一般即整个工作表。
field〔字段〕：指定数据库函数所作用的数据列名。
如：Sheet1!D1或Sheet1!4
criteria〔条件区域〕：一组包含给定条件的单元格区域。
如在sheet2表中自己先构建条件区间，如；$J$10:$K$11
Database: Criteria:
field1 | field2 | 语文 | excel函数排名数学 | …… |
数据 | 数据 | 数据 | 数据 | …… |
…… | ||||
…… | …… | …… | ||
J | K | |
10 | 数学 | 数学 |
11 | >=80 | <=100 |
①数据库计数函数：DCOUNT〔数据库X围，被计数列，放条件的区域〕
功能：计数数据库中满足指定条件的记录字段〔列〕中包含数值的单元格的个数
J | K | |
10 | 语文 | 数学 |
11 | >=85 | >=85 |
例：DCOUNT(Sheet1!$A$1:$I$39,Sheet1!C1,$J$10:$K$11)
②数据库计数函数：
DCOUNTA〔数据库X围，被计数列，放条件的区域〕
功能：数据库中满足指定条件的记录字段〔列〕中非空单元格的个数
例：DCOUNTA(A2:K24,B2,$J$10:$K$11)
重要：语文和数学，怎么办？用学号，计数满足条件的学号非空数
③数据库中求平均值函数：DAVERAGE(数据库X围，被求平均值的列，放条件的区域)
例：DAVERAGE(A1:G17,E1,J2:L3)
④获取数据库的单元格的值函数：DGET〔数据库X围，单元格所在列，放条件的区域〕
J | K | |
10 | 体育 | 性别 |
11 | >=90 | 女 |
功能：获取数据库的列中提取符合指定条件的单元格的值
例：DGET(A2:K24,C2,$J$10:$K$11)
“体育〞成绩大于或等于90的“女生〞__
⑤数据库的列中满足条件的最大值：DMAX〔数据库X围，列名，放条件的区域〕
K | |
10 | 性别 |
11 | 男 |
例：DMAX(A2:K24,I2,$K$10:$K$11)
“体育〞成绩中男生的最高分：
[六类] 日期时间有关的函数
11. 与日期时间有关的函数
①取年份函数：YEAR〔date〕,取出date的4位年份整数。
② 取当前日期函数：NOW〔〕
YEAR(NOW())-YEAR(C2)
12．INT(number) 取整函数〔不四舍五入〕
INT((F4-E4)/30)
Excle非常聪明，日期相减得出来是天数；394天〔单元格的格式要设成“常规〞〕
[七类] 字符串函数
13．字符串替换函数：REPLACE
功能：将某几位的文字以新的字符串替换。
格式：REPLACE〔old_text，start_num，num_chars，new_text〕
old_text：老的文本数据
start_num：从第几个字符位置开始替换
num_chars：共替换多少字符〔1个汉字算2个〕
new_text：用来替换的新字符串
连字符运算符：&
RIGHT(text,num_chars)根据所指定的字符数num_chars返回文本字符串text中右面最后一个或多个字符。
例：REPLACE(F2,5,8,"8"&RIGHT(F2,7)) 或 =REPLACE(F2,1,4,"05718")
14．计算空白单元格数目函数：COUNTBLANK(range) 功能：计算某个单元格区域中空白单元格的数目。
例：COUNTBLANK(B2:E11)
15．函数ISTEXT〔value〕功能：判定value是否为文本
例：IF(ISTEXT(C21),TRUE,FALSE)
16．文本〔字符串〕函数：MID〔text,start_num，num_chars〕
功能：从文本串中指定位置开始返回特定数目的字符。
例：MID(A3,8,1)
[八类] 财务函数
17. 财务函数：PMT贷了多少款，年利息是多少，贷多少年，等额分期按年偿还贷款金额〔年末〕
功能：基于固定利率与等额分期付款方式，返回贷款的每期付款额
格式：PMT(rate，nper，pv，fv，type)
rate：贷款利率〔年利息〕
nper：该项贷款的总贷款期限或者总投资期〔贷款年限〕
pv：从该项贷款〔或投资〕开始计算时已经入账的款项〔贷款金额〕
fv：未来值，或在最后一次付款后希望得到的现金余额，如果忽略该值，将自动默认为0
type：一个逻辑值，用以指定付款时间是在期初还是在期末，1表示期初，0表示期末
例：按年偿还贷款金额〔年末〕 =PMT(B4,B3,B2,0,0)
A | B | |
1 | 贷款情况 | |
2 | 贷款金额： | 1000000 |
3 | 贷款年限： | 15 |
4 | 年利息： | 4.98% |
18. 财务函数：IPMT贷了多少款，年利息是多少，贷多少年，等额分期按年偿还贷款后，利息逐月减少，求某月所交的利息。
功能：基于固定利率与等额分期付款方式，返回投资或贷款在某一给定期限内的利息偿还额
格式：IPMT(rate，per，nper，pv，fv)
rate：各期利率〔月利息，年利息/12〕
per：用于计算利息数额的期数，介于1～nper之间〔第9月〕
nper：总投资〔或贷款〕期，即该项投资〔或贷款〕的付款期总数〔年数*12月〕
pv：从该项投资〔或贷款〕开始计算时已经入账的款项〔贷款金额〕
fv：未来值，或在最后一次付款后希望得到的现金余额，如果忽略该值，将自动默认为0
例：第9月贷款利息金额 =IPMT(B4/12,9,B3*12,B2,0)
19．财务函数：FV先投一笔钱，每年再投一笔钱，有年利率回报，多少年以后的总金额。
功能：基于固定利率与等额分期付款方式，返回某项投资的未来值
格式：FV (rate，nper，pmt，pv，type)
rate：各期利率〔年利率〕
nper：总投资〔或贷款〕期，即该项投资〔或贷款〕的付款期总数〔再投资年限〕
pmt：各期所应支付的金额〔每年再投资金额〕
pv：现值，即从该项投资开始计算时已经入账的款项，也称为本金〔先投资金额〕
type：一个逻辑值，用以指定付款时间是在期初还是在期末，1表示期初，0表示期末。
例：计算10年以后得到的金额：=FV(B3,B5,B4,B2,0)
A | B | ||
1 | 投资情况表1 | ||
2 | 先投资金额： | -1000000 | |
3 | 年利率： | 5% | |
4 | 每年再投资金额： | -10000 | |
5 | 再投资年限： | 10 | |
6 | |||
7 | 10年以后得到的金额： | ||
20．财务函数：PV每年投一笔钱，有年利率回报，多少年以后预计投资总金额。
功能：一系列未来付款的当前值的累积和，返回的是投资现值
格式：PV(rate，nper，pmt，fv，type)
rate：贷款利率〔年利率〕
nper：该项贷款的总贷款期限或者总投资期〔年限〕
pmt：各期所应支付的金额〔每年投资金额〕
fv：未来值，或在最后一次付款后希望得到的现金余额，如果忽略该值，将自动默认为0
发表评论