2023年3月计算机二级Excel函数公式详解.doc

惯用函数
Excel 一共提供了数百个内部函数，限于篇幅，此处仅对某些最惯用函数作一简朴简介。如有需要，可查阅Excel联机协助或其他参照资料，以理解更多函数和更具体阐明。
1、数学函数
(1) 绝对值函数ABS
格式：ABS(number)
功能：返回参数number绝对值。
例如：ABS(-7)返回值为7；ABS(7)返回值为7。

(2) 取整函数INT
格式：INT(number)
功能：取一种不不不大于参数number最大整数。
例如：INT(8.9)，INT(-8.9) 其成果分别是8,-9。

(3) 圆周率函数PI
格式：PI()
功能：返回圆周率π值。
阐明：此函数无需参数，但函数名后括号不能少。

(4) 四舍五入函数ROUND
格式：ROUND(number，n)
功能：依照指定位数，将数字四舍五入。
阐明：其中n为整数，函数按指定n位数，将number进行四舍五入。当n>0，数字将被四舍五入到所指定小数位数；当n＝0，数字将被四舍五入成整数；当n<0，数字将被四舍五入到小数点左边指定位数。
例如：Round(21.45,1)，Round(21.45,0),Round(21.45,-1)其成果分别是21.5，21，20。

(5) 求余函数MOD
格式：MOD(number,divisor)
功能：返回两数相除余数。成果正负号与除数相似。
阐明：Number为被除数，Divisor为除数。
例如：MOD(3,2)等于1，MOD(-3,2)等于1，MOD(3,-2)等于-1，MOD(-3,-2)等于-1。

(6) 随机函数RAND
格式：RAND()
功能：返回一种位于[0,1)区间内随机数。
阐明：此函数无需参数，但函数名后括号不能少。
产生[a,b]区间内随机整数公式：int(rand()*(b-a+1))+a

(7) 平方根函数SQRT
格式：SQRT(number)
功能：返回给定正数平方根。
例如：SQRT(9)等于3。

(8) 求和函数SUM
格式：SUM(number1，number2，…)
功能：返回参数表中所有参数之和。
阐明：number1,number2,……是1-30个需规定和参数。若在参数中直接输入数值、逻辑值或文本型数字，则逻辑真和假值将转换为数值1和0，文本型数字将转换成相应数值型数字参与运算。若引用单元格中浮现空白单元格、逻辑值、文本型数字，则该参数将被忽视。

(9) 条件求和函数SUMIF
格式：SUMIF(range，criteria，sum_range)
功能：依照指定条件对若干单元格求和。
阐明：
range：用于条件判断单元格区域。
criteria：进行累加单元格应满足条件，其形式可觉得数字，表达式或文本。如：条件可以表达为5、"6"、"<60"、"专家"。
sum_range:求和实际单元格。假如省略sum_range，则直接对range中单元格求和。
例如：A1:A4单元区域中分别存储四个职工月收入，分别为：，2500，3000和5000；B1:B4单元区域中内容为：专家、讲师、专家、副专家。则SUMIF(B1:B4，"专家",A1:A4)值为5000，表达求B1:B4单元格中职称为专家人员相应于A1:A4单元格中月收入和。

2、记录函数
(1) 求平均值函数AVERAGE
格式：AVERAGE(number1，number2，…)
功能：求参数平均值。
阐明：最多可有30个参数，参数可以是数值、区域或区域名。若引用参数中涉及文字、逻辑值或空单元格，则将忽视这些参数。
例如：a1:a5区域中数值分别为1，2，3，4，5，则average(a1:a5)为3。

(2) COUNT函数
格式：COUNT(value1，value2，…)
功能：计算所列参数（最多30个）中数值型数据个数。
阐明：函数计数时，会把直接作为参数输入数字、文本型数字、空值、逻辑值、日期计算进去；但对于错误值或无法转化成数据内容则被忽视。假如参数是数组或引用，那么只记录数组或引用中数字，数组或引用中空白单元格、逻辑值、文本型数字也将被忽视。这里“空值”是指函数参数中有一种“空参数”，和工作表单元格“空白单元格”是不同。
例如：COUNT(0.1,FALSE，"5","three",4,6.66,70,,8,#div/0!)中就有一种空值，计数时也计算在内，该函数值为8；而COUNT(A1:D4)是计算区域A1:D4中非空白数字单元格个数，注意，空白单元格不计算在内。

(3) COUNTA函数
格式：COUNTA(value1，value2，…)
功能：计算所列参数（最多30个）中数据项个数。
阐明：这里，“数据”是一种广义概念，可以涉及任何类型数据。但假如参数是单元格引用，则引用中空白单元格将被忽视。
例如：COUNTA(5，TRUE，，""，"ABC")计算成果为5。

(4) 条件计数函数COUNTIF
格式：COUNTIF(range,criteria)
功能：计算给定区域内满足特定条件单元格数目。
阐明：
range：但愿计算满足特定条件非空单元格目区域。
criteria：需计数单元格应满足条件，其形式可觉得数字、表达式或文本。
例如：设A1:A4中内容分别是“red”，“green”，“red”和“black”，则COUNTIF(A1:A4，"red")为2；若B1:B4中内容分别为25，35，40和60，则COUNTIF(B1:B4，">=40")为2。

例 在工资表中记录职称是副专家人数和40岁以上职工人数。
=countif(c3:c22,”副专家”)
=countif(e3:e22,”>=40”)

(5) 最大值函数MAX
格式：MAX(number1，number2，…)
功能：求参数表（最多30个）中最大值。
阐明：参数可以是数值、空白单元格、逻辑值或数字文本表达式等。错误值或不能转化为数值文字作为参数时，会引起错误。若参数中不含数字，则返回0。
例如：MAX(78，"98",TRUE,,66)计算成果为98。

(6) 最小值函数MIN
格式：MIN(number1，number2，…)
功能：求参数表（最多30个）中最小值。
阐明：参数阐明与MAX相似。

3、文本函数
(1) LOWER函数
格式：LOWER(text)
功能：将一种字符串中所有大写字母转换为小写字母。
阐明：text是要转换为小写形式字符串。函数LOWER不变化字符串中非字母字符。
例如：LOWER("Apt. 2B") 等于 "apt. 2b"。

(2) UPPER函数
格式： UPPER(text)
功能：将一种字符串中所有小写字母转换为大写字母。
阐明：text是要转换为大写形式字符串。函数UPPER不变化字符串中非字母字符。
例如：UPPER("total") 等于 "TOTAL"。

(3) LEFT函数
格式：LEFT(text,num_chars)
功能：在字符串text中从左边第一种字符开始截取num_chars个字符。
阐明：参数num_chars为截取字符串长度，必要不不大于等于零。假如num_chars不不大于text总长度，则返回text所有内容。假如省略num_chars，则视为1。
例如：LEFT("计算机应用基本",5)为 “计算机应用”，LEFT("abcd")为 “a”。

(4) RIGHT函数
格式：RIGHT(text,num_chars)
功能：在字符串text中从右边第一种字符开始截取num_chars个字符。
阐明：参数阐明同LEFT函数。
例如：RIGHT("Merry，Chrismas"，8)为 “Chrismas”，RIGHT("abcd")为 “d”。

(5) MID函数
格式：MID(text,start_num,num_chars)
功能：从字符串text第start_num个字符开始截取num_chars个字符。
阐明：start_num是截取字符串起始位置。假如start_num不不大于字符串长度，则函数mid返回“”（空字符串）；假如start_num不大于字符串长度，但start_num与num_chars和超过字符串长度，则函数mid返回从start_num到字符串结束所有字符；假如start_num不大于1，则函数Mid将返回错误值#VALUE!
例如：MID("peking university",1,6)为“peking”

(6) LEN函数
格式：LEN(text)
功能：返回字符串text中字符个数。
例如：len("university")为10。

4、日期与时间函数
(1) DATE函数
格式：DATE(year,month,day)
功能：返回指定日期序列数，所谓序列数是从191月1日到所输入日期之间总天数。
阐明：year代表年份，是介于1900到9999之间一种数字。month代表月份，假如输入月份不不大于12，将从指定年份一月份开始往上加算。day代表该月份中第几天，假如day不不大于该月份最大天数，将从指定月份第一天开始往上加算。
例如：DATE(,5,1)为39569，返回代表5月1日序列数。

(2) YEAR函数
格式：YEAR(serial_number)
功能：返回于序列数serial_number相相应年份数。
例如：YEAR(39569)为。

(3) MONTH函数
格式：MONTH(serial_number)
功能：返回序列数serial_number相相应月份数。
例如：MONTH(39569)为5。

(4) DAY函数
格式：DAY(serial_number)
功能：返回序列数serial_number相相应天数。
例如：DAY(39569)为1。

(5) TODAY函数
格式：TODAY()
功能：返回计算机系统内部时钟当前日期序列数。
例如：TODAY()为39570，表达计算机系统当前日期是5月2日。

(6) TIME函数
格式：TIME（hour，minute，second）
功能：返回指定期间序列数。
阐明：该序列数是一种介于0到0.之间十进制小数，相应着自0:00:00（12:00:00 AM）到23:59:59（11:59:59 PM）时间。其中hour介于0到23，代表小时；minute介于0到59，代表分钟；second介于0到59，代表秒。
例如：TIME(12,0,0)为0.5，相应12:00:00 PM；TIME(17,58,10)为0.，相应5:58PM。

(7) NOW函数
格式：NOW()
功能：返回计算机系统内部时钟当前日期和时间序列数。
阐明：该序列数是一种不不大于1带小数正数，其中整数某些代表当前日期，小数某些代表当前时间。
例如：NOW()为39523.，表达3月16日11:52AM。

5、逻辑函数
(1) AND函数
格式：AND(logical1，logical2，…，logical30)
功能：当所有参数逻辑值为真（TRUE）时返回TRUE；只要一种参数逻辑值为假（FALSE）即返回FALSE。
阐明：logical1，logical2，...为待检测若干个条件值（最多30个），各条件值必要是逻辑值（TRUE或FALSE）、计算成果为逻辑值表达式、或者是涉及逻辑值单元格引用。假如引用参数涉及文字或空单元格，则忽视其值；假如指定单元格区域内涉及非逻辑值，则返回错误值#VALUE。
例如：AND(TRUE,7>2) 为TRUE；AND(9-5=4,5>8)为FALSE。

(2) OR函数
格式：OR(logical1,logical2，…，logical30)
功能：在参数中，任何一种参数逻辑值为真，即返回逻辑值TRUE；只有所有参数为假，才返回FALSE。
阐明：与AND函数相似。
例如：OR(TRUE,3+5=8)为TRUE；OR(1+1=3,3+2=6)为FALSE。

(3) NOT函数
格式：NOT(logical)
功能：对逻辑参数logical求相反值。
例如：NOT(FALSE)为TRUE；NOT(1+4=5)为FALSE。

(4) IF函数
格式：IF(logical_test,value_if_true,value_if_false)
功能：对条件式logical_test进行测试，假如条件为逻辑值TRUE，则取value_if_true值，否则取value_if_false值。
阐明：
l 函数IF最多可以嵌套七层，用value_if_false及value_if_true参数可以构造复杂检测条件。
l 在计算参数value_if_true和value_if_false后，函数IF返回相应语句执行后返回值。
图 4-51 用IF函数进行级别分类

例如：在如图4-51所示商品销售业绩表中，规定用IF函数完毕对每位人员业绩评价。评价原则是：全年销售额>=000为“先进”，1500000<=全年销售额<000为“良好”，1000000<=全年销售额<1500000为“普通”，全年销售额<1000000为“较差”。其操作环节如下：
① 在J3单元格输入函数：
＝IF(J3>=000,"先进"，IF(J3>=1500000,"良好",IF(J3>=1000000，"普通","较差"))）
② 将J3中公式复制到J4:J17。

6、数据库记录函数
数据库记录函数格式为：函数名(database，field，criteria)。其中database是涉及字段数据库区域；field指定函数所要记录数据列，可以是带引号字段名，如“级别”，也可以是是字段名所在单元格地址，还可以是代表数据库中数据列位置序号，1表达第一列，2表达第二列等；criteria为一组涉及给定条件单元格区域，即条件区域。条件区域写法同高档筛选。
惯用数据库记录函数有：
DAVERAGE(database,field,criteria)：对数据库中满足条件记录指定字段求平均值。
DSUM(database,field,criteria)：对数据库中满足条件记录指定字段求和。
DMAX(database,field,criteria)：对数据库中满足条件记录指定字段求最大值。
DMIN(database,field,criteria)：对数据库中满足条件记录指定字段求最小值。
DCOUNT(database,field,criteria)：计算指定数据库中符合条件且包具有数字单元格数。
DCOUNTA(database,field,criteria)：返回数据库中满足给定条件非空单元格数目。

例如，在商品销售业绩表（见图4-54）中，计算男性中级销售人员人数以及她们业绩奖金平均值。操作环节如下：
(1) 在空白区域E19:F20建立条件，办法同“高档筛选”条件区域建立。
(2) 在E21、E22单元分别输入各记录函数：
E21：＝DCOUNT(A2:J17，，E19:F20)
E22：＝DAVERAGE(A2:J17,8，E19:F20)
记录成果如图4-65所示。

图4-65 数据库记录函数成果

8、其她函数
(1) 频率分析函数FREQUENCY
格式：FREQUENCY(range1,range2)
功能：将区域range1中数据按垂直区域range2(分段点)进行频率分布记录，记录成果放在range2右边列相应位置。
阐明：输入公式前要选定显示成果区域，返回数组中元素个数比range2中元素数目多一种，输入公式完毕要按“Ctrl+Shift+Enter”组合键，不能按Enter键。
例如：若工作表中已输入某班学生成绩，如图4-52所示。记录成绩在0~59、60~69、70~79、80~89和90~100各区间中人数。
图4-53使用频度函数
图4-52 学生成绩

操作环节如下：
① 在区域C2:C5中输入分段点分数59、69、79、89。
② 选定显示成果区域D2:D6。
③ 用鼠标单击编辑栏，在编辑栏中输入公式“=FREQUENCY(A1:A10,C2:C5)”。
④ 按“Ctrl+Shift+Enter”组合键，成果如图4-53所示。

(2) 排名函数RANK
格式：RANK(number,range,rank-way)
功能：返回单元格number在一种垂直区域range中排位名次，rank-way是排位方式。rank-way为0或省略，则按降序排列(值最大为第1名)。Rank-way不为0则按升序排列(值最小为第1名)。
阐明：RANK函数对相似数排位相似。但相似数存在将影响后续数值排位。
例如：A1:A5区域中具有数字5、7、3、9、3，则RANK(A2,A1:A5)=2；RANK(A2,A1:A5,1)=4。