EXCEL常用函数教程.ppt

EXCEL函数教程,主要内容,在实际工作中的应用,基本定义,常用函数,实例应用,单元格的引用在公式的应用中是非常重要的,EXCEL将单元格的引用分为3类:相对引用、绝对引用和混合引用。,基本定义,相对引用,相对引用是指直接输入单元格的位置名称。在默认情况下复制与填充公式时，公式中单元格的引用位置会做相应的变化。,在单元格F4中输入公式：=SUM(C4:E4),然后将公式向下填充到单元格F12,释放鼠标后即可求得相应的结果。然后将公式结果显示为公式，即可看到公式中相对引用的相应变化。,绝对引用,绝对引用是指在引用单元格的同时添加符号“$”，表示引用的位置是绝对的。在复制公式时，公式中单元格的引用始终固定不变。,在单元格F4中输入公式：=SUM($C$4:$E$4),然后将公式向下填充到单元格F12,释放鼠标后即可求得相应的结果。然后将公式结果显示为公式，即可看到公式中的绝对引用保持不变。,将相对引用的符号+F4键,则改为绝对引用,混合引用,混合引用是将两种单元格的引用混合使用。在行号或者列号的前面加符号“$”，该符号后面的位置就是绝对引用。当进行公式复制时，其中绝对引用不发生改变，而相对引用则发生变化。,在单元格F4中输入公式：=D4/$D$7,然后将公式向下填充到单元格F9,释放鼠标后即可求得相应的结果。然后将公式结果显示为公式，即可看到公式中相对引用发生了变化，而绝对引用则保持不变。,数学函数,常用函数,逻辑函数,常规统计函数,查找函数,数学函数,INT函数,INT函数的功能是将数字向下舍入到最接近的整数。,其语法为：INT（number）,其中number为需要进行向下舍入取整的实数。,ROUND函数,ROUND函数的功能是返回某个数字按指定位数取整后的数字。其语法为：,ROUND（number,num_digits),其中number为需要进行四舍五入的数字；num_digits是指定的位数，按此位数进行四舍五入。,使用ROUND函数应注意的事项：1、如果num_digits大于0，则四舍五入到指定的小数位。2、如果num_digits等于0，则四舍五入到最接近的整数。3、如果num_digits小于0，则在小数点的左侧进行四舍五入。,EXCEL函数教程,SUM函数,SUM函数的功能是返回某一个单元格区域中的所有数字之和。其语法为：,SUM（number1,number2,),其中number1,number2,为1到30个需要求和的参数。,注意事项：1、直接键入到参数表中的数字、逻辑值及数字的文本表达式将被计算。2、如果参数为数组或引用，则只有其中的数字将被计算，数组或引用中的空白单元格、逻辑值、文本或错误值等将被忽略。3、如果参数为错误值或者为不能转换为数字的文本，将会导致错误。,EXCEL函数教程,SUMIF函数,SUMIF函数的功能是根据指定的条件对若干的单元格求和。其语法为：,SUMIF（range,criteria,sum_range),其中range为用于条件判断的单元格区域；criteria为确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本；sum_range是需要求和的实际单元格。,注意事项：1、只有在区域中相应的单元格符合条件的情况下，sum_range中的单元格才能求和。2、如果忽略了sum_range，则对区域中的单元格求和。,EXCEL函数教程,SUBTOTAL函数,SUBTOTAL函数的功能是返回列表或者数据库中的分类汇总。通常使用“数据”菜单中的“分类汇总”菜单项即可很容易地创建带有分类汇总的列表。一旦创建了分类汇总，就可以通过编辑SUBTOTAL函数对该列表进行修改。其语法为：,SUBTOTAL（function_num,ref1,ref2,）,其中function_num为1至11(包含隐藏值）或者101到111（忽略隐藏值）之间的数字，指定使用何种函数在列表中进行分类汇总计算；参数ref1,ref2,为需要进行分类汇总计算的1到29个区域或引用。,注意事项：1、如果在ref1,ref2,中有其他的分类汇总（嵌套分类汇总），就将忽略这些嵌套分类汇总，以避免进行重复计算。2、当function_num为从1到11的常数时，SUBTOTAL函数将通过【格式】【行】【隐藏】菜单项隐藏行中的值。当function_num为从101到111的常数时，SUBTOTAL函数将忽略通过【格式】【行】【隐藏】菜单项所隐藏的行中的值。3、SUBTOTAL函数忽略任何不包括在筛选结果中的行，不论使用什么function_num值。4、SUBTOTAL函数适用于数据列或垂直区域，不适用于数据行或水平区域。5、如果所指定的某一个引用为三维引用，函数SUBTOTAL将返回错误值“#VALUE！”。,EXCEL函数教程,EXCEL函数教程,IF函数,IF函数的功能是执行真假判断，根据逻辑计算的真假值返回不同的结果。可以使用IF函数对数值和公式进行条件检测。其语法为：,IF（logical_test，value_if_true，value_if_false),其中logical_test为需要进行判断的任意值或者表达式，value_if_true为当表达式logical_test为TRUE时的返回值，value_if_false为当表达式logical_test为FALSE时的返回值。,注意事项：1、该函数最多可嵌套7层，使用value_if_true及value_if_false可以构造复杂的检测条件。2、在计算参数value_if_true和value_if_false后，该函数会返回相应语句执行后的返回值。3、如果参数表达式中包含数组，那么在执行IF语句时数组中的每一个元素都将被计算。,逻辑函数,常规统计函数,COUNTIF函数,COUNTIF函数的功能是计算区域中满足给定条件的单元格的个数。其语法为：,COUNTIF（range,criteria）,其中range为需要计算其中满足条件的单元格数目的单元格区域；参数criteria为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式、单元格引用或者文本。,注意事项：该函数是计算基于一个文本字符串或某范围内的一个数值的总和。,RANK函数,RANK函数的功能是返回一个数字在数字列表中的排位，数字的排位是其大小与列表中其他值的比值（如果列表已排过序，数字的排位就是它当前的位置）。其语法为：,RANK（number,ref,order）,其中number为需要找到排位的数字；ref为数字列表数组或对数字列表的引用，ref中的非数值型参数将被忽略；order为一个数字，指明排位的方式。如果参数order为0（零）或省略，MicrosoftExcel对数字的排位是基于ref为按照降序排列的列表。如果order不为零，MicrosoftExcel对数字的排位是基于ref为按照升序排列的列表。,注意事项：1、函数RANK对重复数的排位相同。但重复数的存在将影响后续数值的排位。2、由于某些原因，用户可能使用考虑重复数字的排位定义。这可通过将下列修正因素添加到按排位返回的值来实现。该修正因素对于按照升序计算排位（顺序=非零值）或按照降序计算排位（顺序=0或被忽略）的情况都是正确的。,EXCEL函数教程,查找函数,VLOOKUP函数,VLOOKUP函数的功能是在表格或者数值数组的首列查找指定的数值,并由此返回表格或者数组当前行中指定列处的数值。其语法为：,VLOOKUP（lookup_value，table_array，col_index_num，range_lookup）,其中lookup_value为需要在数组第一列中查找的数值；col_index_num为table_array中待返回的匹配值的列序号；range_lookup为一个逻辑值，用以指明函数VLOOKUP返回时是精确匹配还是近似匹配。,注意事项：1、参数lookup_value可以为数值、引用或文本字符串，参数table_array为需要在其中查找数据的数据表。2、col_index_num为1时返回table_array第一列中的数值，col_index_num为2时返回table_array第二列中的数值，依次类推。3、如果为TURE或者省略，则返回近似匹配值，也就是说如果找不到精确匹配值则返回小于lookup_value的最大数值；如果range_value为FALSE，函数VLOOKUP将返回精确匹配值。如果找不到则返回错误值“#N/A”。,实例应用,利用IF和ROUND计算个人所得税,在单元格F3中输入公式：,=SUM(C3:E3),然后将公式向下填充到单元格F12。,计算“个人所得税”。在单元格G3中输入以下公式：,=IF(F322000,ROUND(F3-22000)*0.25+3000+450+150+25,1),IF(F37000,ROUND(F3-7000)*0.2+450+150+25,1),IF(F34000,ROUND(F3-4000)*0.15+150+25,1),IF(F32500,ROUND(F3-2500)*0.1+25,1),IF(F32000,ROUND(F3-2000)*0.05,1),0),EXCEL函数教程,SUMIF的应用,在单元格E15中输入公式：,=SUMIF($H$3:$H$12,3000,$J$3:$J$12),按下【Enter】键即可得相应的结果,把公式向下填充，再更改相应的条件区域，即可得到相应的结果。,EXCEL函数教程,SUBTOTAL的应用,在单元格C13中输入公式：,=SUBTOTAL(9,C3:C12),然后向右填充至H13单元格，即可求出相应的结果。,EXCEL函数教程,在分类筛选中选择要分类汇总的项目，即可得到相应的结果。,EXCEL函数教程,COUNTIF的应用,在单元格B2中输入公式：,=COUNTIF($A$2:$A$14,A2),然后向下填充，即得到相应的结果。,EXCEL函数教程,RANK的应用（确定员工业绩的名次）,在单元格H3中输入以下公式：,=RANK（G3，$G$3：$G$12）,然后向下填充，即得到其它员工的名次。,EXCEL函数教程,在单元格B2中输入以下公式：,VLOOKUP的应用(查找员工考核分),=vlookup(A2，E: