Excle常用函数及技巧(08).ppt

常用实用函数及操作技巧（EXCLE）,2,第一部分：常用实用函数第二部分：操作技巧,报告内容,3,第一部分：常用实用函数,一、逻辑函数二、数学函数三、统计函数四、查询和引用函数五、文本函数六、日期、时间函数七、财务函数（现值、终值）,4,一、逻辑函数,（一）AND函数：进行交集运算1、说明所有参数的逻辑值为真时，返回TRUE；只要一个参数的逻辑值为假，即返回FALSE。即当所有条件都成立时，返回true，否则，返回false。2、语法AND(logical1,logical2,.)Logical1,logical2,.表示待检测的1到30个条件值，各条件值可为TRUE或FALSE。,5,实例：,1、返回同时满足以下条件的销售员：a、保费超过6万元；b、性别为“男”；c、年龄为不超过40岁。,2、返回“可递减暂时性差异”。,6,（二）OR函数：进行并集运算1、说明在其参数组中，任何一个参数逻辑值为TRUE，即返回TRUE；所有参数的逻辑值为FALSE，即返回FALSE。即只要有一个逻辑值为true，就返回true，所有逻辑值均为false，才返回false。2、语法OR(logical1,logical2,.)Logical1,logical2,.为需要进行检验的1到30个条件，分别为TRUE或FALSE。,一、逻辑函数,7,实例：,1、返回满足以下条件之一的销售员：a、保费超过6.5万元的男性；b、保费超过3.5万元的男性。,8,（三）IF函数：条件函数1、说明执行真假值判断，根据逻辑计算的真假值，返回不同结果。可以使用函数IF对数值和公式进行条件检测。2、语法IF(logical_test,value_if_true,value_if_false)Logical_test表示计算结果为TRUE或FALSE的任意值或表达式。Value_if_truelogical_test为TRUE时返回的值。Value_if_falselogical_test为FALSE时返回的值。最多可嵌套七层；,一、逻辑函数,9,1、返回满足以下条件的销售员：a、X8万元为“优秀”。,实例：,10,第一部分：常用实用函数,一、逻辑函数二、数学函数三、统计函数四、查询和引用函数五、文本函数六、日期、时间函数七、财务函数（现值、终值）,11,（一）ABS函数：1、说明：计算绝对值返回数字的绝对值。绝对值没有符号。2、语法Abs(number)（二）INT函数：1、说明：向下取整将数字向下舍入到最接近的整数。2、语法Int(number)Number需要进行向下舍入取整的实数。,二、数学函数,实例：=INT(8.9)返回8；,12,（三）MOD函数：1、说明：返回两数相除的余数结果的正负号与除数相同。2、语法Mod(number,divisor)Number为被除数。Divisor为除数。,二、数学函数,实例：,13,（四）Product函数：计算乘积1、说明：将所有以参数形式给出的数字相乘，并返回乘积值。2、语法Product(number1,number2,.)Number1,number2,.为1到30个需要相乘的数字参数。3、注意当参数为数字、逻辑值或数字的文字型表达式时可以被计算；当参数为错误值或是不能转换成数字的文字时，将导致错误。4、比较Sum函数是计算数据之和，而product是计算数据之乘积,二、数学函数,实例：,14,（五）ROUND函数：按位数舍入1、说明返回某个数字按指定位数取整后的数字。2、语法Round(number,num_digits)Number需要进行四舍五入的数字。Num_digits指定的位数，按此位数进行四舍五入。3、注意如果num_digits大于0，则四舍五入到指定的小数位。如果num_digits等于0，则四舍五入到最接近的整数。如果num_digits小于0，则在小数点左侧进行四舍五入。,二、数学函数,实例：,15,（六）SUM函数：计算数字之和1、说明返回某一单元格区域中所有数字之和。也可返回符合条件的数据之和（即条件求和）2、语法Sum(number1,number2,.)Number1,number2,.为1到30个需要求和的参数。3、注意事项在使用条件求和时，在所有内容输入完毕之后，同时按“ctrl+shift+enter”,二、数学函数,16,实例：,17,（七）SUMIF函数：条件求和1、说明：根据指定条件对若干单元格求和。2、语法Sumif(range,criteria,sum_range)Range为用于条件判断的单元格区域。Criteria为确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本。例如，条件可以表示为32、32、32或apples。Sum_range是需要求和的实际单元格。3、注意事项（1）只有在区域中相应的单元格符合条件的情况下，sum_range中的单元格才求和。如果忽略了sum_range，则对区域中的单元格求和。（2）MicrosoftExcel还提供了其他一些函数，它们可根据条件来分析数据。例如，如果要计算单元格区域内某个文本字符串或数字出现的次数，则可使用COUNTIF函数。如果要让公式根据某一条件返回两个数值中的某一值（例如，根据指定销售额返回销售红利），则可使用IF函数。,二、数学函数,18,第一部分：常用实用函数,一、逻辑函数二、数学函数三、统计函数四、查询和引用函数五、文本函数六、日期、时间函数七、财务函数（现值、终值）,19,（一）COUNT和COUNTA函数：统计单元格个数1、说明：Count：返回包含数字以及包含参数列表中的数字的单元格的个数。Counta：返回参数列表中非空值的单元格个数。2、语法Count(value1,value2,.)Value1,value2,.为包含或引用各种类型数据的参数（1到30个）。3、注意事项（1）函数COUNT在计数时，将把数字、日期、或以文本代表的数字计算在内；但是错误值或其他无法转换成数字的文字将被忽略。（2）如果参数是一个数组或引用，那么只统计数组或引用中的数字；数组或引用中的空白单元格、逻辑值、文字或错误值都将被忽略。如果要统计逻辑值、文字或错误值，请使用函数COUNTA。,三、统计函数,20,实例：,A、通过“车牌号码”统计车辆台数，使用count函数，返回结果为“0”；B、通过“车牌号码”统计车辆台数，使用counta函数，返回结果为“8”；C、通过“序号”统计车辆台数，使用count函数，返回结果为“8”；,21,（二）COUNTIF函数：按条件统计1、说明计算区域中满足给定条件的单元格的个数。2、语法Countif(range,criteria)Range为需要计算其中满足条件的单元格数目的单元格区域。Criteria为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。例如，条件可以表示为32、32、32或apples。3、注意事项MicrosoftExcel提供其他函数，可用来基于条件分析数据。例如，若要计算基于一个文本字符串或某范围内的一个数值的总和，可使用SUMIF工作表函数。若要使公式返回两个基于条件的值之一，例如某指定销售量的销售红利，可使用IF工作表函数。,三、统计函数,22,D、统计“车牌号码”中以“苏”开头的车辆台数，使用countif函数，返回“8”；E、统计“序号”中数字大于“4”的个数，通过countif函数，返回“4”；,实例：,23,（三）MAX和MAXA函数：返回最大值1、说明返回一组值中的最大值。2、语法MAX(number1,number2,.)Number1,number2,.是要从中找出最大值的1到30个数字参数。3、注意事项（1）可以将参数指定为数字、空白单元格、逻辑值或数字的文本表达式。如果参数为错误值或不能转换成数字的文本，将产生错误。（2）如果参数为数组或引用，则只有数组或引用中的数字将被计算。数组或引用中的空白单元格、逻辑值或文本将被忽略。（3）如果逻辑值和文本不能忽略，请使用函数MAXA来代替。（4）如果参数不包含数字，函数MAX返回0（零）。,三、统计函数,24,实例：,25,（四）MIN和MINA函数：返回最小值1、说明返回一组值中的最小值。2、语法MIN(number1,number2,.)Number1,number2,.是要从中找出最小值的1到30个数字参数。3、注意事项（1）可以将参数指定为数字、空白单元格、逻辑值或数字的文本表达式。如果参数为错误值或不能转换成数字的文本，将产生错误。（2）如果参数是数组或引用，则函数MIN仅使用其中的数字，空白单元格，逻辑值、文本或错误值将被忽略。（3）如果逻辑值和文本字符串不能忽略，请使用MINA函数。（4）如果参数中不含数字，则函数MIN返回0。,三、统计函数,26,实例：,27,一、逻辑函数二、数学函数三、统计函数四、查询和引用函数五、文本函数六、日期、时间函数,第一部分：常用实用函数,28,（一）HLOOKUP函数：实现水平查找1、说明在表格的首行查找指定的数值，并由此返回表格当前列中指定行处的数值。当比较值位于数据表的首行，并且要查找下面给定行中的数据时，请使用函数HLOOKUP。HLOOKUP中的H代表“行”。2、语法（1）HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)Lookup_value为需要在数据表第一行中进行查找的数值。Lookup_value可以为数值、引用或文本字符串。Table_array为需要在其中查找数据的数据表。可以使用对区域或区域名称的引用。Table_array的第一行的数值可以为文本、数字或逻辑值。如果range_lookup为TRUE，则table_array的第一行的数值必须按升序排列：.-2、-1、0、1、2、A-Z、FALSE、TRUE；否则，函数HLOOKUP将不能给出正确的数值。如果range_lookup为FALSE，则table_array不必进行排序。（2）文本不区分大小写。,四、查询函数,29,（一）HLOOKUP函数：实现水平查找3、注意事项（1）可以用下面的方法实现数值从左到右的升序排列：选定数值，在“数据”菜单中单击“排序”，再单击“选项”，然后单击“按行排序”选项，最后单击“确定”。在“排序依据”下拉列表框中，选择相应的行选项，然后单击“升序”选项。（2）Range_lookup为一逻辑值，指明函数HLOOKUP查找时是精确匹配，还是近似匹配。如果为TRUE或省略，则返回近似匹配值。也就是说，如果找不到精确匹配值，则返回小于lookup_value的最大数值。如果range_value为FALSE，函数HLOOKUP将查找精确匹配值，如果找不到，则返回错误值#N/A!。（3）如果函数HLOOKUP找不到lookup_value，且range_lookup为TRUE，则使用小于lookup_value的最大值。（4）如果函数HLOOKUP小于table_array第一行中的最小数值，函数HLOOKUP返回错误值#N/A!。,30,实例：,31,（二）VLOOKUP函数：实现垂直查找1、说明在表格的首列查找指定的数值，并由此返回表格当前行中指定列处的数值。在VLOOKUP中的V代表垂直。2、语法（1）VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)Lookup_value为需要在表格第一列中查找的数值。Lookup_value可以为数值、引用或文本字符串。Table_array为需要在其中查找数据的数据表。可以使用对区域或区域名称的引用，例如数据库或列表。如果range_lookup为TRUE，则table_array的第一列中的数值必须按升序排列：、-2、-1、0、1、2、-Z、FALSE、TRUE；否则，函数VLOOKUP不能返回正确的数值。如果range_lookup为FALSE，table_array不必进行排序。（2）Table_array的第一列中的数值可以为文本、数字或逻辑值。（3）文本不区分大小写。,四、查询函数,32,（二）VLOOKUP函数：实现垂直查找（4）Col_index_num为table_array中待返回的匹配值的列序号。Col_index_num为1时，返回table_array第一列中的数值；col_index_num为2，返回table_array第二列中的数值，以此类推。如果col_index_num小于1，函数VLOOKUP返回错误值值#VALUE!；如果col_index_num大于table_array的列数，函数VLOOKUP返回错误值#REF!。（5）Range_lookup为一逻辑值，指明函数VLOOKUP返回时是精确匹配还是近似匹配。如果为TRUE或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于lookup_value的最大数值；如果range_value为FALSE，函数VLOOKUP将返回精确匹配值。如果找不到，则返回错误值#N/A。（6）如果函数VLOOKUP找不到lookup_value，且range_lookup为TRUE，则使用小于等于lookup_value的最大值。（7）如果lookup_value小于table_array第一列中的最小数值，函数VLOOKUP返回错误值#N/A。（8）如果函数VLOOKUP找不到lookup_value且range_lookup为FALSE，函数VLOOKUP返回错误值#N/A。,四、查询函数,33,实例：,34,第一部分：常用实用函数,一、逻辑函数二、数学函数三、统计函数四、查询和引用函数五、文本函数六、日期、时间函数七、财务函数（现值、终值）,35,（一）CONCATENATE函数：合并字符串1、说明将几个文本字符串合并为一个文本字符串。2、语法CONCATENATE(text1,text2,.)Text1,text2,.为1到30个将要合并成单个文本项的文本项。这些文本项可以为文本字符串、数字或对单个单元格的引用。3、注意事项也可以用&（和号）运算符代替函数CONCATENATE实现文本项的合并。,五、文本函数,36,（二）EXACT函数：比较字符串是否完全相同1、说明该函数测试两个字符串是否完全相同。如果它们完全相同，则返回TRUE；否则，返回FALSE。函数EXACT能区分大小写，但忽略格式上的差异。利用函数EXACT可以测试输入文档内的文本。2、语法EXACT(text1,text2)Text1待比较的第一个字符串。Text2待比较的第二个字符串。,五、文本函数,37,（三）LEFT函数：返回左边字符1、说明LEFT基于所指定的字符数返回文本字符串中的第一个或前几个字符。LEFTB基于所指定的字节数返回文本字符串中的第一个或前几个字符。此函数用于双字节字符。2、语法LEFT(text,num_chars)LEFTB(text,num_bytes)Text是包含要提取字符的文本字符串。Num_chars指定要由LEFT所提取的字符数。Num_chars必须大于或等于0。如果num_chars大于文本长度，则LEFT返回所有文本。如果省略num_chars，则假定其为1。Num_bytes按字节指定要由LEFTB所提取的字符数。,五、文本函数,38,实例：,39,（四）RIGHT函数：返回右边字符 1、说明 RIGHT 根据所指定的字符数返回文本字符串中最后一个或多个字符。RIGHTB 根据所指定的字符数返回文本字符串中最后一个或多个字符。此函数用于双字节字符。2、语法 RIGHT(text,num_chars)RIGHTB(text,num_bytes)Text 是包含要提取字符的文本字符串。Num_chars 指定希望 RIGHT 提取的字符数。Num_bytes 指定希望 RIGHTB 根据字节所提取的字符数。3、注意事项 Num_chars 必须大于或等于 0。如果 num_chars 大于文本长度，则 RIGHT 返回所有文本。如果忽略 num_chars，则假定其为 1。,五、文本函数,40,实例：,41,（五）MID函数：返回特定字符 1、说明 MID 返回文本字符串中从指定位置开始的特定数目的字符，该数目由用户指定。MIDB 返回文本字符串中从指定位置开始的特定数目的字符，该数目由用户指定。此函数用于双字节字符。2、语法 MID(text,start_num,num_chars)MIDB(text,start_num,num_bytes)Text 是包含要提取字符的文本字符串。Start_num 是文本中要提取的第一个字符的位置。文本中第一个字符的 start_num 为 1，以此类推。Num_chars 指定希望 MID 从文本中返回字符的个数。Num_bytes 指定希望 MIDB 从文本中返回字符的个数（按字节）。3、注意事项 如果 start_num 大于文本长度，则 MID 返回空文本()。如果 start_num 小于文本长度，但 start_num 加上 num_chars 超过了文本的长度，则 MID 只返回至多直到文本末尾的字符。,五、文本函数,42,实例：,43,第一部分：常用实用函数,一、逻辑函数二、数学函数三、统计函数四、查询和引用函数五、文本函数六、日期、时间函数七、财务函数（现值、终值）,44,（一）DADEDIF函数：计算两个日期之间的天数、月数、年数 1、说明 计算两个日期之间的天数、月数、年数 2、语法 DATEDIF（start-date,end-date,unit）start-date表示时间段内的第一个日期或起始日期；end-date表示时间段内的最后一个日期或结束日期；unit表示所需信息的返回类型，具体是“y”表示“年”、“ym”表示“月”、“md”表示“天”；,六、日期、时间函数,45,（二）YEAR函数：返回年份 1、说明 返回某日期对应的年份。返回值为 1900 到 9999 之间的整数。2、语法 YEAR(serial_number)Serial_number 为一个日期值，其中包含要查找年份的日期。应使用 DATE 函数来输入日期，或者将日期作为其他公式或函数的结果输入。例如，使用 DATE(2008,5,23)输入 2008 年 5 月 23 日。3、注意事项 Microsoft Excel 可将日期存储为可用于计算的序列号。默认情况下，1900 年 1 月 1 日的序列号是 1 而 2008 年 1 月 1 日的序列号是 39448，这是因为它距 1900 年 1 月 1 日有 39448 天。,六、日期、时间函数,46,（三）MONTH函数：返回月份 1、说明 返回以序列号表示的日期中的月份。月份是介于 1（一月）到 12（十二月）之间的整数。2、语法 MONTH(serial_number)Serial_number 表示一个日期值，其中包含要查找的月份。应使用 DATE 函数来输入日期，或者将日期作为其他公式或函数的结果输入。,六、日期、时间函数,47,第一部分：常用实用函数,一、逻辑函数二、数学函数三、统计函数四、查询和引用函数五、文本函数六、日期、时间函数七、财务函数（现值、终值）,48,（一）FV函数：返回终值 1、说明 计算在固定利率条件下等额分期付款方式，返回某项投资的未来值。2、语法 FV(Rate,Nper,Pmt,Pv,Type)Rate为各期利率,各期利率应固定不变；Nper为总投资期，即该项投资的付款期总数；Pmt：为各期所应支付的金额，其数值在整个年金期间保持不变。如果忽略 pmt，则pv 参数必须有数据。Pv为现值，即从该项投资开始计算时已经入帐的款项，或一系列未来付款的当前值的累积和，也称为本金。如果省略 PV，则假设其值为零，但 pmt 参数必须有数据。Type数字 0 或 1，用以指定各期的付款时间是在期初还是期末。如果省略 type，则假设其值为零。0为期末，1为期初。,七、财务函数,49,（一）FV函数：返回终值 3、注意事项（1）应确认所指定的 rate 和 nper 单位的一致性。例如，同样是四年期年利率为 12%的贷款，如果按月支付，rate 应为 12%/12，nper 应为 4*12；如果按年支付，rate 应为 12%，nper 为 4。（2）在所有参数中，支出的款项，用负数表示；收入的款项，用正数表示。4、实例，如下。可点击进入。,50,（二）PV函数：返回现值 1、说明返回投资的现值。现值为一系列未来付款的当前值的累积和。2、语法 PV(Rate,Nper,Pmt,Fv,Type)Rate为各期利率,各期利率应固定不变；Nper为总投资期，即该项投资的付款期总数；Pmt：为各期所应支付的金额，其数值在整个年金期间保持不变。如果忽略 pmt，则Fv 参数必须有数据。Fv为终值，即最终清理时所得的现金，如果省略 fv，则假设其值为零（一笔贷款的未来值即为零）。如果省略 Fv，则假设其值为零，但 pmt 参数必须有数据。Type数字 0 或 1，用以指定各期的付款时间是在期初还是期末。如果省略 type，则假设其值为零。0为期末，1为期初。,七、财务函数,51,（二）PV函数：返回现值 3、注意事项（1）应确认所指定的 rate 和 nper 单位的一致性。例如，同样是四年期年利率为 12%的贷款，如果按月支付，rate 应为 12%/12，nper 应为 4*12；如果按年支付，rate 应为 12%，nper 为 4。（2）在所有参数中，支出的款项，用负数表示；收入的款项，用正数表示。4、实例，如下。可点击进入。,52,（三）RATE函数：返回年金的各期利率。1、说明 返回年金的各期利率。2、语法 RATE(Nper,Pmt,Pv，Fv,Type，Guess)Nper为总投资期，即该项投资的付款期总数；Pmt为各期所应支付的金额，其数值在整个年金期间保持不变。如果忽略 pmt，则Fv 参数必须有数据。Pv为现值，为现值，即从该项投资开始计算时已经入帐的款项，或一系列未来付款当前值的累积和，也称为本金。Fv为终值，即最终清理时所得的现金，如果省略 fv，则假设其值为零（一笔贷款的未来值即为零）。如果省略 Fv，则假设其值为零，但 pmt 参数必须有数据。Type数字 0 或 1，用以指定各期的付款时间是在期初还是期末。如果省略 type，则假设其值为零。0为期末，1为期初。Guess 为预期利率。如果省略，则假设该值为 10%。,七、财务函数,53,（三）RATE函数：返回年金的各期利率。3、注意事项（1）函数 RATE 通过迭代法计算得出，并且可能无解或有多个解。如果在进行 20 次迭代计算后，函数 RATE 的相邻两次结果没有收敛于 0.0000001，函数 RATE 将返回错误值#NUM!。如果函数 RATE 不收敛，请改变 guess 的值。通常当 guess 位于 0 到 1 之间时，函数 RATE 是收敛的。（2）在所有参数中，支出的款项，用负数表示；收入的款项，用正数表示。（3）应确认所指定的 guess 和 nper 单位的一致性，对于年利率为 12%的 4 年期贷款，如果按月支付，guess 为 12%/12，nper 为 4*12；如果按年支付，guess 为 12%，nper 为 4。,54,（三）RATE函数：返回年金的各期利率。4、实例：（1）某人建议你贷给他30 000 元,并同意每年年末付给你9 000 元,共付五年。你是否应接受该建议?在Excel 工作表的单元格中录入:=RATE(5,9 000,-30 000),回车确认,结果自动显示为15.24%。结果表明,如果15.24%高于其他投资项目的报酬率,则可以接受该建议。（2）计算分期收款的折现率。某公司出售一套设备,协议约定采用分期收款方式,从销售当年年末开始分5 年收款,每年收200 万元,合计1 000 万元(不考虑增值税)。假定购货方在销售成立日支付货款,付800 万元即可。购货方在销售成立日支付的800 万元可以看做是应收金额的公允价值。该笔业务的账务处理,涉及折现率的计算问题,即要计算每年年末的“未实现融资收益”和“财务费用”数据。首先要计算年金为200 万元、期数为5 年、现值为800 万元的折现率。在Excel 工作表的单元格中录入:=RATE(5,200,-800),回车确认,结果显示为7.93%。,55,（四）NPER函数：返回某项投资的总期数。1、说明 返回基于固定利率及等额分期付款方式，返回某项投资的总期数。2、语法 NPER(Rate,Pmt,Pv,Fv,Type)Rate为各期利率,各期利率应固定不变；Pmt：为各期所应支付的金额，其数值在整个年金期间保持不变。如果忽略 pmt，则Fv 参数必须有数据。Pv为现值，即从该项投资开始计算时已经入帐的款项，或一系列未来付款的当前值的累积和，也称为本金。Fv为终值，或在最后一次付款后希望得到的现金余额。如果省略 fv，则假设其值为零。如果省略 Fv，则假设其值为零，但 pmt 参数必须有数据。Type数字 0 或 1，用以指定各期的付款时间是在期初还是期末。如果省略 type，则假设其值为零。0为期末，1为期初。,七、财务函数,56,（四）NPER函数：返回某项投资的总期数3、注意事项 在所有参数中，支出的款项，用负数表示；收入的款项，用正数表示。4、实例，如下。可点击进入。（1）某一投资，要求的最低回报率为6%，现在一次投入500万元，每期支付200万元，投资清理时可收回3000万元，请计算投资期？（2）某一投资，要求的最低回报率为5%，现在一次投入300万元，每期支付100万元，投资清理时可收回1000万元，请计算投资期？,57,（五）PMT函数：返回计算等额还款额。1、说明 基于固定利率及等额分期付款方式，返回贷款的每期付款额。2、语法 PMT(Rate,Nper,Pv,Fv,Type)Rate为各期利率,各期利率应固定不变；Nper该项贷款的付款总数；Pv为现值，即从该项投资开始计算时已经入帐的款项，或一系列未来付款的当前值的累积和，也称为本金。Fv为终值，或在最后一次付款后希望得到的现金余额。如果省略 fv，则假设其值为零。如果省略 Fv，则假设其值为零，但 pmt 参数必须有数据。Type数字 0 或 1，用以指定各期的付款时间是在期初还是期末。如果省略 type，则假设其值为零。0为期末，1为期初。,七、财务函数,58,（五）PMT函数：返回计算等额还款额。3、注意事项（1）在所有参数中，支出的款项，用负数表示；收入的款项，用正数表示。（2）PMT 返回的支付款项包括本金和利息，（3）应确认所指定的 rate 和 nper 单位的一致性。4、实例，如下。可点击进入。（1）某贷款60万元，银行贷款年利率6%，贷款期为25年，请计算各月支付房贷的金额？（2）某人储蓄，银行存款利率为4%，要求10年之后达到100万元请计算各月需储蓄的金额？,59,（六）PPMT函数：返回计算等额还款方式下各期本金。1、说明 基于固定利率及等额分期付款方式，返回投资在某一给定期间内的本金偿还额。2、语法 PPMT(Rate,Per，Nper，Pv，Fv，Type)Rate为各期利率,各期利率应固定不变；Per用于计算其本金数额的期数，必须介于 1 到 nper 之间。Nper该项贷款的付款总数；Pv为现值，即从该项投资开始计算时已经入帐的款项，或一系列未来付款的当前值的累积和，也称为本金。Fv为终值，或在最后一次付款后希望得到的现金余额。如果省略 fv，则假设其值为零。如果省略 Fv，则假设其值为零，但 pmt 参数必须有数据。Type数字 0 或 1，用以指定各期的付款时间是在期初还是期末。如果省略 type，则假设其值为零。0为期末，1为期初。,七、财务函数,60,（六）PPMT函数：返回计算等额还款方式下各期本金。3、注意事项（1）在所有参数中，支出的款项，用负数表示；收入的款项，用正数表示。（2）应确认所指定的 rate 和 nper 单位的一致性。4、实例，如下。可点击进入。（1）某贷款60万元，银行贷款年利率6%，贷款期为25年，请计算第15个月、第150个月的月支付房贷款中本金的金额？,61,（七）IPMT函数：返回计算等额还款方式下各期利息。1、说明 基于固定利率及等额分期付款方式，返回给定期数内对投资的利息偿还额。2、语法 IPMT(Rate,Per，Nper，Pv，Fv，Type)Rate为各期利率,各期利率应固定不变；Per用于计算其本金数额的期数，必须介于 1 到 nper 之间。Nper该项贷款的付款总数；Pv为现值，即从该项投资开始计算时已经入帐的款项，或一系列未来付款的当前值的累积和，也称为本金。Fv为终值，或在最后一次付款后希望得到的现金余额。如果省略 fv，则假设其值为零。如果省略 Fv，则假设其值为零，但 pmt 参数必须有数据。Type数字 0 或 1，用以指定各期的付款时间是在期初还是期末。如果省略 type，则假设其值为零。0为期末，1为期初。,七、财务函数,62,（七）IPMT函数：返回计算等额还款方式下各期利息。3、注意事项（1）在所有参数中，支出的款项，用负数表示；收入的款项，用正数表示。（2）应确认所指定的 rate 和 nper 单位的一致性。4、实例，如下。可点击进入。（1）某贷款60万元，银行贷款年利率6%，贷款期为25年，请计算第15个月、第150个月的月支付房贷款中利息的金额？,63,（八）NPV函数：返回计算净现值。1、说明通过使用贴现率以及一系列未来支出（负值）和收入（正值），返回一项投资的净现值。2、语法 NPV(Rate,value1，value2，value3，)Rate 为某一期间的贴现率，是一固定值。Value1,value2,.为 1 到 29 个参数，代表支出及收入。3、注意事项（1）Value1,value2,.在时间上必须具有相等间隔，并且都发生在期末。（2）NPV 使用 Value1,Value2,的顺序来解释现金流的顺序。所以务必保证支出和收入的数额按正确的顺序输入。（3）函数 NPV 假定投资开始于 value1 现金流所在日期的前一期，并结束于最后一笔现金流的当期。函数 NPV 依据未来的现金流来进行计算。如果第一笔现金流发生在第一个周期的期初，则第一笔现金必须添加到 函数 NPV 的结果中，而不应包含在 values 参数中。,七、财务函数,64,（八）NPV函数：返回计算净现值。3、注意事项（续）（4）在所有参数中，支出的款项，用负数表示；收入的款项，用正数表示。（5）应确认所指定的 rate 和 nper 单位的一致性。4、实例，如下。可点击进入。,（1）某投资年初投入100万元，第一年末收益30万元，第二年收益40万元，第三年收益为0元，第四年收益为60万元，第五年处置收益30万元，假设收益率要求为10%，请问此投资是否合算？净现值为正数18.13万元，适合投资。,65,（九）IRR函数：内涵报酬率函数。1、说明 返回由数值代表的一组现金流的内部收益率。这些现金流不必为均衡的，但作为年金，它们必须按固定的间隔产生，如按月或按年。内部收益率为投资的回收利率，其中包含定期支付（负值）和定期收入（正值）。2、语法 IRR(Values，Guess）Values为数组或单元格的引用，包含用来计算返回的内部收益率的数字。Guess为对函数 IRR 计算结果的估计值。如果省略 guess，假设它为10%。3、注意事项（1）Values 必须包含至少一个正值和一个负值，以计算返回的内部收益率。（2）函数 IRR 根据数值的顺序来解释现金流的顺序。故应确定按需要的顺序输入了支付和收入的数值。,七、财务函数,66,（九）IRR函数：返回计算内涵报酬率。3、注意事项（续）（3）在所有参数中，支出的款项，用负数表示；收入的款项，用正数表示。（4）如果函数 IRR 返回错误值#NUM!，或结果没有靠近期望值，可用另一个 guess 值再试一次。4、实例，如下。可点击进入。,（1）某投资年初投入100万元，第一年末收益30万元，第二年收益40万元，第三年收益为0元，第四年收益为60万元，第五年处置收益30万元，请计算其内涵报酬率？,67,（十）财务函数的小结。1、财务函数的基本语法顺序是（Rate,Nper,Pmt,Pv,Fv,Type)，当计算Pmt时，其函数语法为Pmt（Rate,Nper,Pv,Fv,Type)，当计算Nper时，其函数是Nper（Rate,Pmt,Pv,Fv,Type),其他的一次类推。2、Pmt=Ppmt+Ipmt 3、函数 IRR 与函数 NPV（净现值函数）的关系十分密切。函数 IRR 计算出的收益率即净现值为 0 时的利率。见NPV实例S2。4、NPV与PV的功能都是计算现值，不同的是PV要求各期的金额一致，而NPV没有此要求。,七、财务函数,68,第一部分：常用实用函数第二部分：操作技巧,报告内容,69,第二部分：操作技巧,一、数据透视表二、分列三、打印批注四、快捷健五、其他,70,（一）数据透视表介绍 在Excle中，数据透视表是最具有创造性的分析工具，同时具有很强的技术性和强大的分析功能。熟悉掌握数据透视表，用户可以有效分析、组织复杂数据，并根据不同的标准对数据进行分类汇总。尤其对于数量众多、结构复杂的工作表，数据透视表可以十分便利的将其中的数据分布规律显现出来，并将工作表重新组合、添加算法等。（二）创建数据透视表1、启动数据透视表和数据透视图向导。选择数据源区域的任一单元格，然后选择【数据】【数据透视表和数据透视图】命令，启动向导，如图所示。,一、数据透视表,71,2、设置数据源类型 在向导的第一个对话框中，用户需要选择数据源类型，在本例中实用的是excle工作表，因此选中【Microsoft Excel数据清单或数据库】单选按钮，同时选中【数据透视表】单选按钮。（在默认情况下，这些按钮都是自动选中的，我们不作修改即可）如图。3、选择数据源区域 单击向导对话框的【下一步】按钮后，在下一对话框中选择数据源区域。如果在开始操作之前我们选中了数据源中的单元格，在此时就会默认选中整个数据源区域；如果修改、重选等，实用【选定区域】后代按钮（红色），自行选择区域。如图。,72,4、选择数据透视表的插入位置 单击对话框【下一步】按钮，在下一个对话框中选择数据透视表的插入位置（是使用“新建工作表”还是“现有工作表”），如图。5、打开【布局】对话框 单击对话框中的【布局】按钮，打开布局对话框，在该对话框中能定义数据透视表的布局和集结数据的方式，如图。布局对话框的界面主要有3部分：顶部的指导文本、中部的工作区和右侧的“可得字段”列表。其中工作区由“列”、“行”、“数据”3部分组成，“可得字段”列表中列出了可以向工作区的3个部分添加的数据字段。,73,6、设置数据透视表的布局 将“可得字段”直接拖动至指定位置。我们将“预付赔款”进行分险种分机构列示，可将“公司”拖动至行，“险种”拖动至列，“本币金额”拖动至数据。如图。7、查看数据透视表的选项 设置数据透视表的布局后，单击【布局】对话框中【确定】按钮，返回到向导对话框中，单击【选项】按钮。打开【数据透视表选项】对话框，查看数据透视表的选项。如图。,74,8、查看完成的数据透视表 单击【数据透视表选项】对话框中的【确定】按钮，返回到向导中，然后单击【完成】按钮，查看完成的数据透视表。如图。,75,9、数据透视表数据源修改的刷新 数据透视表的数据源修改后，数据透视表并不能自动刷新，必须使用【刷新】按钮方可完成。如在2102机构下增加0330险种的预付赔款1000元。,76,第二部分：操作技巧,一、数据透视表二、分列三、打印批注四、