excel函数的运用

excel函数的运用 IF 用途:执行逻辑判断，它可以根据逻辑表达式的真假，返回不同的结果，从而执行数值或公式的条件检测任务。 语法:IF(logical_test，value_if_true，value_if_false)。 参数:Logical_test计算结果为TRUE或FALSE的任何数值或表达式;value_if_true是Logical_test为TRUE时函数的返回值，如果logical_test为TRUE并且省略了value_if_true，则返回TRUE。而且value_if_true可以是一个表达式;value_if_false是Logical_test为FALSE时函数的返回值。如果logical_test为FALSE并且省略value_if_false，则返回FALSE。value_if_false也可以是一个表达式。 实例:公式“=IF(C2>=85，"A"，IF(C2>=70，"B"，IF(C2>=60，"C"，IF(C2<60，"D"))))”，其中第二个IF语句同时也是第一个IF语句的参数。同样，第三个IF语句是第二个IF语句的参数，以此类推。例如，若第一个逻辑判断表达式C2>=85成立，则D2单元格被赋值“A”;如果第一个逻辑判断表达式C2>=85不成立，则计算第二个IF语句“IF(C2>=70”;以此类推直至计算结束，该函数广泛用于需要进行逻辑判断的场合。 VLOOKUP 用途:在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。当比较值位于数据表首列时，可以使用函数VLOOKUP代替函数HLOOKUP。 语法:VLOOKUP(lookup_value，table_array，col_index_num，range_lookup) 参数:Lookup_value为需要在数据表第一列中查找的数值，它可以是数值、引用或文字串。Table_array为需要在其中查找数据的数据表，可以使用对区域或区域名称的引用。Col_index_num为table_array中待返回的匹配值的列序号。Col_index_num为1时，返回table_array第一列中的数值;col_index_num为2，返回table_array第二列中的数值，以此类推。Range_lookup为一逻辑值，指明函数VLOOKUP返回时是精确匹配还是近似匹配。如果为TRUE或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于lookup_value的最大数值;如果range_value为FALSE，函数VLOOKUP将返回精确匹配值。如果找不到，则返回错误值#N/A。 实例:如果A1=23、A2=45、A3=50、A4=65，则公式“=VLOOKUP(50，A1:A4，1，TRUE)”返回50。 COUNTIF 用途:统计某一区域中符合条件的单元格数目。 语法:COUNTIF(range，criteria) 参数:range为需要统计的符合条件的单元格数目的区域;Criteria为参与计算的单元格条件，其形式可以为数字、表达式或文本(如36、">160"和"男"等)。其中数字可以直接写入，表达式和文本必须加引号。 实例:假设A1:A5区域内存放的文本分别为女、男、女、男、女，则公式“=COUNTIF(A1:A5，"女")”返回3。 ROUND 用途:按指定位数四舍五入某个数字。 语法:ROUND(number，num_digits) 参数:Number是需要四舍五入的数字;Num_digits为指定的位数，Number按此位数进行处理。 注意:如果num_digits大于0，则四舍五入到指定的小数位;如果num_digits等于0，则四舍五入到最接近的整数;如果num_digits小于0，则在小数点左侧按指定位数四舍五入。 实例:如果A1=65.25，则公式“=ROUND(A1，1)”返回65.3;=ROUND(82.149，2)返回82.15;=ROUND(21.5，-1)返回20。 ROUNDUP 用途:按绝对值增大的方向舍入一个数字。 语法:ROUNDUP(number，num_digits) 参数:Number为需要舍入的任意实数，Num_digits指定舍入的数字位数。 注意:如果num_digits为0或省略，则将数字向上舍入到最接近的整数。如果num_digits小于0，则将数字向上舍入到小数点左边的相应位数。 实例:如果A1=65.251，则公式“=ROUNDUP(A1，0)”返回66;=ROUNDUP(A1，1)返回66;=ROUNDUP(A1，2)返回65.26;=ROUNDUP(-3.14159，1)返回-3.2;=ROUNDUP(31415.92654，-2)返回31500。 INT 用途:将任意实数向下取整为最接近的整数。 语法:INT(number) 参数:Number为需要处理的任意一个实数。 实例:如果A1=16.24、A2=-28.389，则公式“=INT(A1)”返回16，=INT(A2)返回-29。 SUM 用途:返回某一单元格区域中所有数字之和。 语法:SUM(number1，number2，...)。 参数:Number1，number2，...为1到30个需要求和的数值(包括逻辑值及文本表达式)、区域或引用。 注意:参数表中的数字、逻辑值及数字的文本表达式可以参与计算，其中逻辑值被转换为1、文本被转换为数字。如果参数为数组或引用，只有其中的数字将被计算，数组或引用中的空白单元格、逻辑值、文本或错误值将被忽略。 实例:如果A1=1、A2=2、A3=3，则公式“=SUM(A1:A3)”返回6;=SUM("3"，2，TRUE)返回6，因为"3"被转换成数字3，而逻辑值TRUE被转换成数字1。 SUMIF 用途:根据指定条件对若干单元格、区域或引用求和。 语法:SUMIF(range，criteria，sum_range) 参数:Range为用于条件判断的单元格区域，Criteria是由数字、逻辑表达式等组成的判定条件，Sum_range为需要求和的单元格、区域或引用。 实例:某单位统计工资报表中职称为“中级”的员工工资总额。假设工资总额存放在工作表的F列，员工职称存放在工作表B列。则公式为“=SUMIF(B1:B1000， "中级"，F1:F1000)”，其中“B1:B1000”为提供逻辑判断依据的单元格区域，"中级"为判断条件，就是仅仅统计B1:B1000区域中职称为“中级”的单元格，F1:F1000为实际求和的单元格区域。 AVERAGE 用途:计算所有参数的算术平均值。 语法:AVERAGE(number1，number2，...)。 参数:Number1、number2、...是要计算平均值的1,30个参数。 实例:如果A1:A5区域命名为分数，其中的数值分别为100、70、92、47和82，则公式“=AVERAGE(分数)”返回78.2。 MAX 用途:返回数据集中的最大数值。 number2，...) 语法:MAX(number1， 参数:Number1，number2，...是需要找出最大数值的1至30个数值。 实例:如果A1=71、A2=83、A3=76、A4=49、A5=92、A6=88、A7=96，则公式“=MAX(A1:A7)”返回96。 MIN 用途:返回给定参数表中的最小值。 语法:MIN(number1，number2，...)。 参数:Number1，number2，...是要从中找出最小值的1到30个数字参数。 实例:如果A1=71、A2=83、A3=76、A4=49、A5=92、A6=88、A7=96，则公式“=MIN(A1:A7)”返回49;而=MIN(A1:A5，0，-8)返回-8。 COUNT 用途:返回数字参数的个数。它可以统计数组或单元格区域中含有数字的单元格个数。 语法:COUNT(value1，value2，...)。 参数:value1，value2，...是包含或引用各种类型数据的参数(1,30个)，其中只有数字类型的数据才能被统计。 实例:如果A1=90、A2=人数、A3=〞〞、A4=54、A5=36，则公式“=COUNT(A1:A5)”返回3。 RANK 用途:返回一个数值在一组数值中的排位(如果数据清单已经排过序了，则数值的排位就是它当前的位置)。 语法:RANK(number，ref，order) 参数:Number是需要计算其排位的一个数字;Ref是包含一组数字的数组或引用(其中的非数值型参数将被忽略);Order为一数字，指明排位的方式。如果order为0或省略，则按降序排列的数据清单进行排位。如果order不为零，ref当作按升序排列的数据清单进行排位。 注意:函数RANK对重复数值的排位相同。但重复数的存在将影响后续数值的排位。如在一列整数中，若整数60出现两次，其排位为5，则61的排位为7(没有排位为6的数值)。 实例:如果A1=78、A2=45、A3=90、A4=12、A5=85，则公式“=RANK(A1，$A$1:$A$5)” 返回5、8、2、10、4。 COUNTIF 用途:计算区域中满足给定条件的单元格的个数。 语法:COUNTIF(range，criteria) 参数:Range为需要计算其中满足条件的单元格数目的单元格区域。Criteria为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。 VALUE 用途:将表示数字的文字串转换成数字。 语法:value(text)。 参数:Text为带引号的文本，或对需要进行文本转换的单元格的引用。它可以是Excel可以识别的任意常数、日期或时间格式。如果Text不属于上述格式，则value函数返回错误值#value!。 注意:通常不需要在公式中使用value函数，Excel可以在需要时自动进行转换。value函数主要用于与其他电子表格程序兼容。 实例:公式“=value(",1，000")”返回1000;=value("16:48:00")-value("12:00:00")返回0.2，该序列数等于4小时48分钟。 现在用EXCEL的实例进一步说明一下所用到的函数.. 一、用COUNTIF函数统计各分数段学生数 语文”学科各分数段学这是最常用、最容易理解的一种方法，我们用它来统计“ 生数。 ?分别选中C63、C67单元格，输入公式:=COUNTIF(C3:C62,"<60")和=COUNTIF(C3:C62,">=90")，即可统计出“语文”成绩“低于60分”和“大于等于90”的学生人数。 ?分别选中C64、C65和C66单元格，输入公式:=COUNTIF(C3:C62,">=60")-COUNTIF(C3:C62,">=70")、=COUNTIF(C3:C62,">=70")-COUNTIF(C3:C62,">=80")和=COUNTIF(C3:C62,">=80")-COUNTIF(C3:C62,">=90")，即可统计出成绩在60-69分、70-79分、80-89分区间段的学生人数。 注意:同时选中C63至C67单元格，将鼠标移至C67单元格右下角，成细十字线状时，按住左键向右拖拉至I列，就可以统计出其它学科各分数段的学生数。 如果某些学科(如体育)，其成绩是不具体数值，而是字符等级(如“优秀、良好”等)，我们可以用COUNTIF函数来统计各等级的学生人数。 如果某些学科(如体育)，其成绩是不具体数值，而是字符等级(如“优秀、良好”等)， 我们可以用COUNTIF函数来统计各等级的学生人数。 ?在K64至K67单元格中，分别输入成绩等级字符。 ?选中L64单元格，输入公式:=COUNTIF($L$3:$L$62,K64)，统计出“优秀”的学生人数。 ?再次选中L64单元格，用“填充柄”将上述公式复制到L65至L67单元格中，统计出其它等级的学生人数。 二、按性别统计职工数 (1)函数分解 COUNTIF函数计算区域中满足给定条件的单元格的个数。 语法:COUNTIF(range,criteria) Range为需要计算其中满足条件的单元格数目的单元格区域;Criteria为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。 (2)实例分析 假设上面使用的人事管理工作表中有599条 记录 混凝土 养护记录下载土方回填监理旁站记录免费下载集备记录下载集备记录下载集备记录下载 ，统计职工中男性和女性人数的方法是:选中单元格D601(或其他用不上的空白单元格)，统计男性职工人数可以在其中输入公式“="男"&COUNTIF(D2:D600,"男")&"人"”;接着选中单元格D602，在其中输入公式“="女"&COUNTIF(D2:D227,"女")&"人"”。回车后即可得到“男399人”、“女200人”。 上式中D2:D600是对“性别”列数据区域的引用，实际使用时必须根据数据个数进行修改。“男”或“女”则是条件判断语句，用来判断区域中符合条件的数据然后进行统计。“&”则是字符连接符，可以在统计结果的前后加上“男”、“人”字样，使其更具有可读性。 三、年龄统计 在人事管理工作中，统计分布在各个年龄段中的职工人数也是一项经常性工作。假设前例《按性别统计职工数》介绍的工作表的E2:E600单元格存放职工的工龄，我们要以5年为一段分别统计年龄小于20岁、20至25岁之间，一直到55至60岁之间的年龄段人数，可以采用下面的操作方法。 (1)函数分解 FREQUENCY函数以一列垂直数组返回某个区域中数据的频率分布。 语法:FREQUENCY(data_array,bins_array) Data_array为一数组或对一组数值的引用，用来计算频率。如果data_array中不包含任何数值，函数FREQUENCY返回零数组;Bins_array为间隔的数组或对间隔的引用，该间隔用于对data_array中的数值进行分组。如果bins_array中不包含任何数值，函数FREQUENCY返回data_array中元素的个数。 (2)实例分析 首先在工作表中找到空白的I列(或其他列)，自I2单元格开始依次输入20、25、30、35、40...60，分别表示统计年龄小于20、20至25之间、25至30之间等的人数。然后在该列旁边选中相同个数的单元格，例如J2:J10准备存放各年龄段的统计结果。然后在编辑栏输入公式 “=FREQUENCY(YEAR(TODAY())-YEAR(E2:E600),I2:I10)”，按下Ctrl+Shift+Enter组合键即可在选中单元格中看到计算结果。其中位于J2单元格中的结果表示年龄小于20岁的职工人数，J3单元格中的数值表示年龄在20至25之间的职工人数等。 内部资料 仅供参考