2022年2022年计算机Excel函数整理 .pdf
relationship, establishe d equivalent relationship 14, and subject: appli cation problem (4)-scores and percentage application problem review content overview answers scores, and percentage application problem of key is: according to meaning, (1) determine standard volume (units 1) (2) find associate volume rate corresponds to relationship, Then in-line sol ution. Category fraction multiplication word problem score Division applications engineeri ng problem problem XV, a subject: review of the measurement of the amount of capacity, measurement and units of measurement of common units of measurement and their significa nce i n rate 1, currency, length, area, volume, unit size, volume, weight and rate. (Omitted) 2, commonly use d time units and their relationships. (Slightly) with a measurement units Zhijian of of poly 1, and of method 2, and poly method 3, and of method and poly method of relationship measurement distance of method 1, and tool measurement 2, and estimates 16, and subject: geometry preliminary knowledge (1)-line and angle review content line, and segment, and Ray, and vertical, and parallel, and angle angl e of classification (slightly) 17, and subject: geometry preliminary knowledge (2)-plane graphi cs review conte nt triangle, and edges shaped, a nd round, and fan axisymmetric graphics perimeter and area combination graphics of area subject : Preliminary knowledge (3)-review of solid content category 1-d shapes are divided into: cylinder and cone 2, column is divided into: cuboi d, square 3, cone cone of the features of cuboids and cubes relationship between characteristics of circular cone is slightly solid surfa ce area and volume 1, size 2, table .和4.8.3 常用函数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 进行四舍五入。当n0，数字将被四舍五入到所指定的小数位数；当n0，数字将被四舍五入成整数；当n0，数字将被四舍五入到小数点左边的指定位数。例如： 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) 功能：返回给定正数的平方根。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 1 页，共 8 页 - - - - - - - - - relationship, establishe d equivalent relationship 14, and subject: appli cation problem (4)-scores and percentage application problem review content overview answers scores, and percentage application problem of key is: according to meaning, (1) determine standard volume (units 1) (2) find associate volume rate corresponds to relationship, Then in-line sol ution. Category fraction multiplication word problem score Division applications engineeri ng problem problem XV, a subject: review of the measurement of the amount of capacity, measurement and units of measurement of common units of measurement and their significa nce i n rate 1, currency, length, area, volume, unit size, volume, weight and rate. (Omitted) 2, commonly use d time units and their relationships. (Slightly) with a measurement units Zhijian of of poly 1, and of method 2, and poly method 3, and of method and poly method of relationship measurement distance of method 1, and tool measurement 2, and estimates 16, and subject: geometry preliminary knowledge (1)-line and angle review content line, and segment, and Ray, and vertical, and parallel, and angle angl e of classification (slightly) 17, and subject: geometry preliminary knowledge (2)-plane graphi cs review conte nt triangle, and edges shaped, a nd round, and fan axisymmetric graphics perimeter and area combination graphics of area subject : Preliminary knowledge (3)-review of solid content category 1-d shapes are divided into: cylinder and cone 2, column is divided into: cuboi d, square 3, cone cone of the features of cuboids and cubes relationship between characteristics of circular cone is slightly solid surfa ce area and volume 1, size 2, table .和2例如： 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 、=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 页，共 8 页 - - - - - - - - - relationship, establishe d equivalent relationship 14, and subject: appli cation problem (4)-scores and percentage application problem review content overview answers scores, and percentage application problem of key is: according to meaning, (1) determine standard volume (units 1) (2) find associate volume rate corresponds to relationship, Then in-line sol ution. Category fraction multiplication word problem score Division applications engineeri ng problem problem XV, a subject: review of the measurement of the amount of capacity, measurement and units of measurement of common units of measurement and their significa nce i n rate 1, currency, length, area, volume, unit size, volume, weight and rate. (Omitted) 2, commonly use d time units and their relationships. (Slightly) with a measurement units Zhijian of of poly 1, and of method 2, and poly method 3, and of method and poly method of relationship measurement distance of method 1, and tool measurement 2, and estimates 16, and subject: geometry preliminary knowledge (1)-line and angle review content line, and segment, and Ray, and vertical, and parallel, and angle angl e of classification (slightly) 17, and subject: geometry preliminary knowledge (2)-plane graphi cs review conte nt triangle, and edges shaped, a nd round, and fan axisymmetric graphics perimeter and area combination graphics of area subject : Preliminary knowledge (3)-review of solid content category 1-d shapes are divided into: cylinder and cone 2, column is divided into: cuboi d, square 3, cone cone of the features of cuboids and cubes relationship between characteristics of circular cone is slightly solid surfa ce area and volume 1, size 2, table .和4(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) 功能：返回指定日期的序列数，所谓序列数是从1900 年 1 月 1 日到所输入日期之间的总天数。说明： year 代表年份，是介于1900 到 9999 之间的一个数字。month 代表月份，如果输入的月份大于12，将从指定年份的一月份开始往上加算。day 代表该月份中第几天，如果day 大于该月份的最大天数，将从指定月份的第一天开始往上加算。例如： DATE(2008,5,1) 为 39569，返回代表2008 年 5 月 1 日的序列数。(2) YEAR 函数格式： YEAR(serial_number) 功能：返回于序列数serial_number相对应的年份数。例如： YEAR(39569)为 2008。(3) MONTH函数名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 4 页，共 8 页 - - - - - - - - - relationship, establishe d equivalent relationship 14, and subject: appli cation problem (4)-scores and percentage application problem review content overview answers scores, and percentage application problem of key is: according to meaning, (1) determine standard volume (units 1) (2) find associate volume rate corresponds to relationship, Then in-line sol ution. Category fraction multiplication word problem score Division applications engineeri ng problem problem XV, a subject: review of the measurement of the amount of capacity, measurement and units of measurement of common units of measurement and their significa nce i n rate 1, currency, length, area, volume, unit size, volume, weight and rate. (Omitted) 2, commonly use d time units and their relationships. (Slightly) with a measurement units Zhijian of of poly 1, and of method 2, and poly method 3, and of method and poly method of relationship measurement distance of method 1, an d tool measurement 2, and estimates 16, and subject: geometry preliminary knowledge (1)-line and angle review content line, and segment, and Ray, and vertical, and parallel, and angle angl e of classification (slightly) 17, and subject: geometry preliminary knowledge (2)-plane graphi cs review conte nt triangle, and edges shaped, a nd round, and fan axisymmetric graphics perimeter and area combination graphics of area subject : Preliminary knowledge (3)-review of solid content category 1-d shapes are divided into: cylinder and cone 2, column is divided into: cuboi d, square 3, cone cone of the features of cuboids and cubes relationship between characteristics of circular cone is slightly solid surfa ce area and volume 1, size 2, table .和5格式： MONTH(serial_number) 功能：返回序列数serial_number相对应的月份数。例如： MONTH(39569) 为 5。(4) DAY 函数格式： DAY(serial_number) 功能：返回序列数serial_number相对应的天数。例如： DAY(39569)为 1。(5) TODAY 函数格式： TODAY() 功能：返回计算机系统内部时钟现在日期的序列数。例如： TODAY()为 39570，表示计算机系统当前日期是2008 年 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.，表示 2008 年 3 月 16 日 11:52AM。5、逻辑函数(1) AND 函数格式： AND(logical1，logical2， logical30)功能： 当所有参数的逻辑值为真（TRUE ）时返回 TRUE ；只要一个参数的逻辑值为假（FALSE ）即返回 FALSE 。说明： logical1, logical2, .为待检测的若干个条件值（最多30 个） ，各条件值必须是逻辑值（ TRUE或 FALSE ） 、计算结果为逻辑值的表达式、或者是包含逻辑值的单元格引用。如果引用的参数包含文字或空单元格，则忽略其值；如果指定的单元格区域内包含非逻辑值，则返回错误值#VALUE 。例如： AND(TRUE,72) 为 TRUE ； AND(9-5=4,58) 为 FALSE 。(2) OR 函数格式： OR(logical1,logical2, ， logical30) 功能：在参数中，任何一个参数逻辑值为真，即返回逻辑值TRUE ；只有全部参数为假，才返回 FALSE 。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 5 页，共 8 页 - - - - - - - - - relationship, establishe d equivalent relationship 14, and subject: appli cation problem (4)-scores and percentage application problem review content overview answers scores, and percentage application problem of key is: according to meaning, (1) determine standard volume (units 1) (2) find associate volume rate corresponds to relationship, Then in-line sol ution. Category fraction multiplication word problem score Division applications engineeri ng problem problem XV, a subject: review of the measurement of the amount of capacity, measurement and units of measurement of common units of measurement and their significa nce i n rate 1, currency, length, area, volume, unit size, volume, weight and rate. (Omitted) 2, commonly use d time units and their relationships. (Slightly) with a measurement units Zhijian of of poly 1, and of method 2, and poly method 3, and of method and poly method of relationship measurement distance of method 1, and tool measurement 2, and estimates 16, and subject: geometry preliminary knowledge (1)-line and angle review content line, and segment, and Ray, and vertical, and parallel, and angle angl e of classification (slightly) 17, and subject: geometry preliminary knowledge (2)-plane graphi cs review conte nt triangle, and edges shaped, a nd round, and fan axisymmetric graphics perimeter and area combination graphics of area subject : Preliminary knowledge (3)-review of solid content category 1-d shapes are divided into: cylinder and cone 2, column is divided into: cuboi d, square 3, cone cone of the features of cuboids and cubes relationship between characteristics of circular cone is slightly solid surfa ce area and volume 1, size 2, table .和6说明：与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的值。说明：函数 IF 最多可以嵌套七层，用 value_if_false及 value_if_true参数可以构造复杂的检测条件。在计算参数value_if_true和 value_if_false后，函数 IF 返回相应语句执行后的返回值。例如：在如图4-51 所示的商品销售业绩表中，要求用IF 函数完成对每位人员的业绩评价。评价的标准是：全年销售额=为“优秀”，=全年销售额 为“良好”，=全年销售额 为“一般”，全年销售额 =,优秀 ，IF(J3=,良好 ,IF(J3=, 一般 , 较差 ) ） 将 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-51 用 IF 函数进行等级分类名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 6 页，共 8 页 - - - - - - - - - relationship, establishe d equivalent relationship 14, and subject: appli cation problem (4)-scores and percentage application problem review content overview answers scores, and percentage application problem of key is: according to meaning, (1) determine standard volume (units 1) (2) find associate volume rate corresponds to relationship, Then in-line sol ution. Category fraction multiplication word problem score Division applications engineeri ng problem problem XV, a subject: review of the measurement of the amount of capacity, measurement and units of measurement of common units of measurement and their significa nce i n rate 1, currency, length, area, volume, unit size, volume, weight and rate. (Omitted) 2, commonly use d time units and their relationships. (Slightly) with a measurement units Zhijian of of poly 1, and of method 2
