excel技巧之函数高级应用实战技巧

Chapter6Chapter6图形和图表实战技巧PAGE256PAGE257嵌套函数实战技巧函数的嵌套是指某个函数引用另一个函数的返回值来进行计算。文本函数实战技巧文本函数用于对文本数据进行提取、删除、替换以及组合等运算。统计函数实战技巧财务函数实战技巧查找和引用函数实战技巧统计函数主要有对单元格数量统计、排位、检验与回归分析等功能。财务函数用于对财务数据进行分析和计算，是财务工作中必不可少的工具。查找和引用函数用于在数据表中查找某一特定的内容，或者引用公式中的相关信息内容。日期和时间函数用于对日期和时间进行计算，修改等操作中。日期函数实战技巧函数高级应用实战技巧6函数是E嵌套函数实战技巧函数的嵌套是指某个函数引用另一个函数的返回值来进行计算。文本函数实战技巧文本函数用于对文本数据进行提取、删除、替换以及组合等运算。统计函数实战技巧财务函数实战技巧查找和引用函数实战技巧统计函数主要有对单元格数量统计、排位、检验与回归分析等功能。财务函数用于对财务数据进行分析和计算，是财务工作中必不可少的工具。查找和引用函数用于在数据表中查找某一特定的内容，或者引用公式中的相关信息内容。日期和时间函数用于对日期和时间进行计算，修改等操作中。日期函数实战技巧函数高级应用实战技巧6函数是Excel中重要的内容，本章将在上一章的基础上，从不同的函数类型着手，详细为读者讲述函数高级应用的内容和技巧，以帮助读者完全掌握这部分的知识。本章导读例1快速将日期转换为星期输入输入输入在Excel中使用函数可以快速将日期转换为星期，具体操作方法如下：输入输入输入在工作表中输入几个日期，然后在单元格D3中输入函数“=TEXT(WEEKDAY(D3),”

dddd””，如下图所示。按【Enter】键，将日期转换成星期，在单元格D4中输入“=TEXT(WEEKDAY(C4),”

aaaa””，如下图所示。按【Enter】键，得到中文的星期，结果如下图所示。例2在Excel中使用函数可以快速将日期转换为星期，具体操作方法如下：打开“台历”工作表，复制并修改。在单元格G5中输入公式“=E4-DATE(YEAR(E4),

1,0)”，如下图所示。按【Ctrl+Enter】组合键，得到公式计算结果，如下图所示。例3计算上个月的天数在Excel中计算上个月的天数，可使用如下的两种方法。方法一：使用DAY函数计算输入新建工作表并输入数据内容，在单元格C4中输入公式“=DAY(B4-DAY(B4))”，如下图所示。输入拖动按【Ctrl+Enter】组合键，得到公式计算结果，然后用拖动填充柄填充公式，最终效果如下图所示。拖动输入输入DAY函数用于返回所引用日期中的天数，DAY(B4)计算得出天数5，DAY(B4-DAY(B4))的含义是返回5天前的天数，即上个月最后一天的天数。DAY函数用于返回所引用日期中的天数，DAY(B4)计算得出天数5，DAY(B4-DAY(B4))的含义是返回5天前的天数，即上个月最后一天的天数。方法二：使用DAY函数计算输入B4,-1))”，如下图所示。输入拖动拖动EOMONTH函数用于返回所引用日期之前或之后的月份最后一天的序列号。EOMONTH函数用于返回所引用日期之前或之后的月份最后一天的序列号。EOMONTH(B4,-1)的含义是返回单元格B4中日期前一个月的最后一天的序列。例4每一年的1月1日所在的周是一年中的第一周，使用WEEKNUM函数可求得某个日期所在的周数，具体操作方法如下：打开“台历”工作表，在单元格G6中输入函数“=WEEKNUM(E5)”，如下图所示。单击【Ctrl+Enter】组合键，得到当前日期所在年中的周数，如下图所示。WEEKNUM函数的语法为WEEKNUM函数的语法为WEEKNUM(serial_num,return_type)，Serial_num代表一周中的日期，Return_type为1个数字，确定星期计算从哪一天开始，如果为1，说明一周开始于星期日如果为2，则说明一周开始于星期一，如果省略不写，则默认为1。例5巧妙计算员工年龄在Excel中，如果已知员工的出生年月，就可使用YEAR函数快速的计算出员工的年龄，具体操作方法如下：输入在单元格D3中输入公式“=YEAR(TODAY

()-C3)-1900”，如下图所示。输入按【Ctrl+Enter】组合键，得到结果，如下图所示。输入输入输入输入在“开始”选项卡下“数字”组中的“数字格式”下拉列表框中，将该单元格的数字格式设置为“常规”，该单元格中将自动显示年龄，如下图所示。将鼠标指针移动单元格D3的右下角，拖动填充柄，填充公式，效果如下图所示。因为E因为Excel中使用了1900年时间系统，公式TODAY()-C3进行计算时将自动将相减的结果加上1900，所有要得到正确的年龄，还需减去1900。例6计算两个日期之间工作日的天数的具体操作方法如下：C4,D4)”，如下图所示。按【Ctrl+Enter】组合键，得出计算结果，如下图所示。返回参数start_date和end_date之间完整的工作日数值。其中不包括周日和假期。。返回参数start_date和end_date之间完整的工作日数值。其中不包括周日和假期。。例7计算同年中两个日期之间的月数的具体操作方法如下：在单元格E4中输入公式“=MONTH(D4)-

MONTH(C4)”，按【Ctrl+Enter】键即可计算出结果，如下图所示。输入输入输入输入例8在单元格E5中输入公式“=YEAR(D5)-

YEAR(C5)*12+MONTH(D5)-MONTH(C5)”，按【Ctrl+Enter】键即可计算出结果，如下图所示。例9计算年数的方法很简单，只需用YEAY函数将两个返回值相减即可，如下图所示。输入输入例1使用函数巧查询在Excel中，VLOOKUP函数用于在表格数组的首列查找指定的值，并由此返回表格数组当前行中其他列的值。下面使用该函数来快速查找所需的值，具体操作方法如下：打开“楼盘概览”工作表，在单元格区域E14:F15中输入数据内容和公式，如下图所示。输入输入在单元格E15中输入相应的楼盘名称，将在单元格F15中显示该楼盘的开盘时间，如下图所示。VLOOKUP函数最后一个参数为逻辑值，其中false值代表精确匹配，true值代表模糊匹配。VLOOKUP函数最后一个参数为逻辑值，其中false值代表精确匹配，true值代表模糊匹配。例2用V.LOOKUP函数逆向查询如果已知楼盘的开盘时间，要查询楼盘名称，可输入公式“=VLOOKUP(F16,IF({0,1},D5:

D12,E5:E12),2,FALSE)”，如下图所示。输入5.单击输入5.单击按【Ctrl+Enter】组合键，将得到楼盘的名称，如下图所示。例3CHOOSE函数的用法CHOOSE函数可以根据索引号返回数值参数列表中的数值。下面通过使用该函数得到奖金等级为例，介绍其使用方法。在员工的奖金名单中，选择单元格D3，在其中输入公式“=CHOOSE(IF(C3<=3000,3,IF(

C3>=5000,1,2)),"一等奖","二等奖","三等奖")”，如下图所示。输入输入按【Ctrl+Enter】组合键，得到奖金等级，如下图所示。通过拖动填充柄，复制公式，得到其他员工的销售奖金等级，如下图所示。例4快速查找化工原料的数量继续上一个实例，通过将INDEX函数与MATCH函数配合使用，可以查找某种化学原料的数量，其具体操作方法如下：草酸盐",B39:B45,0))”，如下图所示。输入输入按【Ctrl+Enter】组合键确认，将得到“草酸盐”的数量为460，如下图所示。例5使用函数进行动态求和下面通过实例来介绍使用函数进行动态求和的过程，具体操作方法如下：1.输入2.选择拖动新建工作表，从中输入产品销售额的相关信息，选择单元格D11，然后单击“数据”选项卡下“数据工具”组中的“数据有效性”按钮下方的下拉按钮，在弹出的下拉菜单中选择“数据有效性”选项，如下图所示。1.输入2.选择拖动单击2.设置1.选择青年以3.单击单击2.设置1.选择青年以3.单击4.选择将弹出“数据有效性”对话框，在其中的“允许”下拉列表框中选择“序列”选项，在“来源”文本框中设置序列来源，如下图所示。4.选择单击“确定”按钮，在单元格D11右侧将出现一个下拉按钮，单击它，将弹出下拉列表，可以从中进行选择，效果如下图所示。2.单击1.选择选择单元格区域C4:C8，然后单击“公式”选项卡下“定义的名称”组中的“定义名称”按钮，将弹出“新建名称”对话框，保持默认即可，如下图所示。2.单击1.选择分别选择单元格区域D4:D8、E4:E8和F4:F8，定义名称，然后单击“定义的名称”组中的“名称管理器”按钮，弹出“名称管理器”对话框，从中可查看定义的名称，如下图所示。2.输入1.选择选择单元格D12，从中输入公式“=SUM(

INDIRECT($D$11))”，按【Ctrl+Enter】组合键确认，将得到“销售点1”的所有产品的销售额，如下图所示。2.输入1.选择例6判断员工姓名是否重复录入在员工信息表中，要判断是否重复录入了员工的信息，可通过查找员工姓名是否重复，来进行判断（假如员工中没有重名者），其具体操作方法如下：新建员工信息表，选择单元格H4，从中输入“=IF((ROW()=MATCH(C4,$C$1:$C$16,0)),

"","重复")”，如下图所示。输入输入拖动按【Ctrl+Enter】组合键确认输入，然后拖动填充柄进行填充，将查找出重复的姓名，如下图所示。拖动例7快速生成数字序列制作工作表时，经常要输入数字序列，输入数字序列的方法有很多，这里我们使用函数来快速输入数字序列，其具体操作方法如下：新建工作表，选择单元格区域A1:A10，然后按【F2】键，从中输入“=ROW(1:10)”，如下图所示。2.输入1.选择2.输入1.选择按【Ctrl+Shift+Enter】组合键，确认数组输入，即可快速填充好数字序列，如下图所示。输入要在一行中填充数字序列，可以先选择要输入数字序列的单元格区域，然后按【F2】键输入函数“=COLUMN(A:F)”，如下图所示。输入按【Ctrl+Shift+Enter】组合键，确认数组输入，也可快速填充字序列，如下图所示。例8在制作工作表时，经常用在不同的工作表中引用数据信息，下面介绍一种使用函数引用其他工作表中的数据的方法。在Sheet3中创建一张员工档案表，存放员工的基本信息，如下图所示。3.选择2.单击1.选择切换到Sheet1中，输入有关员工工资的信息，然后选择单元格B4，单击“公式”选项卡下“函数库”组中的“查找与引用”下拉按钮，在弹出的下拉菜单中选择VLOOKUP函数，如下图所示。3.选择2.单击1.选择弹出“函数参数”对话框，从中设置相应的参数，如下图所示。设置设置拖动单击【确定】按钮，关闭对话框，得到公式结果，然后拖动填充柄填充公式，如下图所示。拖动例9返回最后一个非空单元格在包含空的单元格中，要查找最后一个非空单元格中的值，可按如下方法操作：建工作表，从中制作有关产品的信息数据表，新然后在单元格E13中输入公式“LOOKUP(CHAR(255),B:B)”，然后按【Ctrl+Enter】组合键确认，得到公式查找的结果，如下图所示。输入输入输入输入在单元格E14中输入公式“＝LOOKUP(9E+307,C:D)，然后按【Ctrl+Enter】组合键，得到最后结果，如下图所示。CHAR函数用于返回对应于数字代码的字符，它可将其他类型计算机文件中的代码转换为字符；9E+307为Excel中可以输入的最大数值。CHAR函数用于返回对应于数字代码的字符，它可将其他类型计算机文件中的代码转换为字符；9E+307为Excel中可以输入的最大数值。10快速查找最后一个非空单元格的位置的具体操作步骤如下：输入在单元格F3中输入公式“=MATCH(CHAR(

1),B:B,-1)”，如下图所示。输入按【Ctrl+Enter】组合键，查找到的最后一个非空单元格的位置为11，如下图所示。11快速定位到空单元格通过创建单元格名称和使用链接函数可以快速定位到空单元格中。下面以具体的实例介绍快速定位到空单元格的方法。2.单击1.输入新建工作表，从中输入产品信息，单击“公式”选项卡下“定义的名称”组中的“定义名称”按钮，如下图所示。2.单击1.输入弹出“新建名称”对话框，在“名称”文本框中输入文字“空单元格”，在“引用位置”填充2.输入单击2.输入2.输入1.输入文本框中输入公式“=OFFSET($B$1,COUNTA(

$B:$B)+1,)”如下图所示。填充2.输入单击2.输入2.输入1.输入单击“确定”按钮，创建动态名称，然后选择单元格F3，从中输入“=HYPERLINK("#空单元格","快速定位")”，如下图所示。公式确认后，将在单元格F3中显示链接文字“快速定位”，单击它，将定位到“产品”列下方的空单元格中，如下图所示。12快速返回唯一值列表有的工作表中有不少重复的数据信息，要过滤这些重复的信息，让表中显示唯一的数据值，可以采用如下的方法。打开一张产品信息表，在该表中“产品”列中有重复的产品信息，如下图所示。在单元格B14中输入公式“=OFFSET($B

$2,SMALL(IF(MATCH($B$3:$B$11,$B$3:$B$11,0)=ROW($B$3:$B$11)-2,ROW($B$3:$B$11)-2),ROW(A1)),)”，如下图所示。按【ctrl+shift+enter】组合键，返回产品值“AC001”，使用填充柄进行填充，将得到不重复的值，结果如下图所示。例1输入选择PMT函数返回在固定利率下，投资或贷款的等额分期偿还额。使用该函数计算贷款的还款额的具体操作方法如下：输入选择打开“分期付款计算表”工作表，选择单元格E4，如下图所示。选择选择单击“公式”选项卡下“函数库”组中的“插入函数”按钮，弹出“插入函数”对话框，在“选择函数”列表框中选择PMT函数，如下图所示。单击“确定”按钮，弹出“函数参数”对话框，从中输入参数，如下图所示。单击“确定”按钮，得到计算结果，用前面所讲的复制公式的方法，复制公式，计算出其他的月偿还金额，如下图所示。1335613356PMT函数的语法结构为PMT（rate,nper,pv,fv,type），其中，rate为贷款利率。nPMT函数的语法结构为PMT（rate,nper,pv,fv,type），其中，rate为贷款利率。nper为该项贷款的付款期总数。pv为现值，或一系列未来付款的当前值的累积和，也称为本金。Fv为未来值，或在最后一次付款后希望得到的现金余额，如果省略fv，则假设其值为0，也就是一笔贷款的未来值为0。Type为数字0或1，用以指定各期的付款时间是在期初还是期末。如果省略，则默认为0，代表期末付款。例2假设在银行存款，要使10年后得到7万元，则计算每月的存款金额，可使用如下的方法得出：新建工作表，从中输入数据，并在单元格C5中输入公式“=PMT(C4/12,C3*12,0,70000)”如下图所示。输入输入按【Ctrl+Enter】组合键，计算出每个月应存款的金额，如下图所示。例3PPMT函数基于固定利率及等额分期付款方式，用于返回投资在某一给定期间内的本金偿还额。使用该函数计算本金偿还金额的具体操作方法如下：新建工作表，从中输入数据，选择单元格C5，输入“=PPMT(”，如下图所示。输入输入设置按【Shift+F3】组合键，弹出“函数参数”对话框，从中设置参数，如下图所示。设置拖动单击“确定”按钮，求得返还的第一年本金额，然后拖动填充柄，填充公式，最终效果如下图所示。拖动例4IPMT函数基于固定利率及等额分期付款方式，用于返回给定期数内对的利息偿还额。使用该函数计算利息偿还金额的具体操作方法如下：以上个技巧中的表格为基础，添加“返还利息”列，在单元格D5中输入函数“=IPMT($D$3,B5,$C$3,$B$3)”，如下图所示。填充输入填充输入按【Ctrl+Enter】组合键，得到第一年的返还利息额，通过拖动填充柄，填充公式，得到每年的返还利息，最终效果如下图所示。例5使用FV函数计算投资价值使用FV函数可以返回投资的现值。现值为一系列未来付款的当前值的累积和。该函数的使用方法如下：在“投资计算表”中单击单元格F5，然后在其中输入函数“=FV(C5/12,D5,E5)”，如下图所示。输入输入按【Enter】键，因为是表格所以自动填充公式计算出结果，如下图所示。该函数的语法结构为FV(rate,nper,该函数的语法结构为FV(rate,nper,

pmt,fv,type)，其中，pmt为各期所应支付的金额，其数值在整个年金期间保持不变。通常，pmt包括本金和利息，但不包括其他费用或税款。如果忽略pmt，则必须包含fv参数。Fv为未来值，或在最后一次支付后希望得到的现金余额，如果省略fv，则假设其值为零。可以根据保守估计的利率来决定每月的存款额。如果忽略fv，则必须包含pmt参数。例6假设要购买一种养老保险，购买成本为10万元，该保险可以在20年内于每月末回报800元、投资报酬率为10％，要计算该保险的年金现值，其具体的计算方法如下：新建工作表，从中输入数据，然后单击单元格C7，从中输入函数“=PV(C4/12,C3*12,

C2,0)”，如下图所示。输入输入该函数的语法结构为PV(rate,nper,pmt,fv,type)该函数的语法结构为PV(rate,nper,pmt,fv,type)。按【Ctrl+Enter】组合键，计算出年金的现值，如下图所示。计算得出的年金现值为82.899.69元，小于10万元，所以购买该保险不划算。计算得出的年金现值为82.899.69元，小于10万元，所以购买该保险不划算。例7假设要用5年时间分期付款购车，每年预付6000元，银行利率为6.5％，要计算如果要一次性支付完，需支付的金额，其具体的计算方法如下：新建工作表，从中输入数据，然后单击单元格C7，从中输入函数“=PV(C4,C3,C2,1)”，如下图所示。输入输入按【Ctrl+Enter】组合键，计算出的一次性支付金额，如下图所示。例8SLN函数用于计算某项资产在一个期间中的线性折旧值。使用该函数计算线性折旧值的具体操作方法如下：新建工作表，从中输入数据，选择单元格E3，如下图所示。1.输入选择1.输入选择单击“公式”选项卡下“函数库”组中的“插入函数”按钮，弹出“插入函数”对话框，在“选择函数”列表框中选择SLN函数，如下图所示。选择单击“确定”按钮，弹出“函数参数”对话框，从中输入参数，然后单击“确定”按钮，得到每年的线性折旧值，如下图所示。选择选择输入选择输入SLN函数的语法结构为SLN(SLN函数的语法结构为SLN(cost,

salvage,life)，其中，cost为固定资产原值，salvage为指定固定资产残值，life为折旧周期。例9DB函数用于计算在固定利率下给定日期内的资产折旧值，其具体操作方法如下：选择新建工作表，从中输入数据，选择单元格C6，如下图所示。选择单击编辑栏左侧的“插入函数”按钮，弹出“插入函数”对话框，在“选择函数”列表框中选择DB函数，如下图所示。DB函数的语法结构为DB(cost,DB函数的语法结构为DB(cost,

salvage,life,period,month)，其中Period为需要计算折旧值的期间，Month为第一年使用的月数，如省略，则代表12个月。设置单击“确定”按钮，弹出“函数参数”对话框，从中输入参数，然后单击“确定”按钮，得到每年的线性折旧值，如下图所示。设置设置2.输入1.填充通过向下拖动该单元格右下角的填充柄，在单元格区域C7:C15填充公式。然后在单元格C16中输入公式“=B3-C3-SUM(C6:C15)”，计算出固定资产在使用周期内最后6个月的折旧值，如下图所示。设置2.输入1.填充使用固定余额递减法计算折旧值，最后一期的折旧值应该通过公式“使用固定余额递减法计算折旧值，最后一期的折旧值应该通过公式“固定资产原值-剩余残值-每年中折旧值之和”来计算。10使用DDB函数可以计算固定资产折旧，其具体操作方法如下：3.选择2.单击1.输入新建工作表，从中输入数据，然后单击“公式”选项卡下“函数库”组中的“财务”下拉按钮，在弹出的下拉面板中选择DDB函数，如下图所示。3.选择2.单击1.输入DDB函数的语法结构为DDDB函数的语法结构为DDB(

cost,salvage,life,period,factor)，其中Factor为余额递减速率。如果factor被省略，默认为2（即双倍余额递减法）。将弹出“函数参数”对话框，从中设置相应的函数，如下图所示。单击“确定”按钮，得到使用双倍余额递减法计算出的折旧值，然后在单元格区域C7:C11中填充公式，得到的结果如下图所示。选择选择还可以使用1.5倍或其他余额递减法计算固定资产折旧值。还可以使用1.5倍或其他余额递减法计算固定资产折旧值。11SYD函数用于返回固定资产按年限总和折旧法计算出的指定期内的折旧值。使用该函数计算资产折旧的具体操作方法如下：设置2.输入1.输入设置3.选择2.单击1.输入新建工作表，从中输入数据，然后单击“公式”选项卡下“函数库”组中的“财务”下拉按钮，在弹出的下拉面板中选择SYD函数，如下图所示。设置2.输入1.输入设置3.选择2.单击1.输入将弹出“函数参数”对话框，从中设置相应的函数，如下图所示。填充单击“确定”按钮，得到使用年限综合折旧法计算出的折旧值，然后在单元格区域C7:C12中填充公式，得到的结果如下图所示。填充使用SUM函数可以计算出累计的折旧值，如下图所示。。。12用可变递减折旧法求折旧值VDB函数使用双倍余额递减法或其他指定的方法，返回指定的任何期间内（包括部分期间）的资产折旧值。使用该函数计算资产折旧的具体操作方法如下：新建工作表，从中输入数据，然后单击在单元格D4中输入“=VDB(”，如下图所示。按【Shift+F3】组合键，将弹出“函数参数”对话框，在其中可以设置其具体的函数参数，如下图所示。单击“确定”按钮，得出第一个月的资产折旧值，如下图所示。输入输入输入输入输入输入输入输入输入输入计算得出的各资产折旧值，如下图所示。VDB函数的语法结构为VDB(VDB函数的语法结构为VDB(

cost,salvage,life,start_period,end_period,factor,no_switch)，其中，Start_period为进行折旧计算的起始期间；End_period为进行折旧计算的截止期间；No_switch为某个逻辑值，当该参数为TRUE时，表示当折旧值大于余额递减计算值时，不会转用直线折旧法，当为FALSE时，即会专用直线折旧法。13使用VDB函数计算累计的资产折旧值，具体操作方法如下：新建工作表，从中输入数据，然后单击单元格D6，从中输入函数“=VDB(B3,C3,D3,0,2+

1/2,TRUE)”如下图所示。按【Enter】键，将得到第3年第2个季度末的累计资本折旧。然后在单元格D10中输入函数“=VDB(B3,C3,D3,0,2+1/2,1.5,TRUE)”，如下图所示。按【Ctrl+Enter】组合键，得到使用1.5倍余额递减法求得的第3年第2个季度末的累计资产折旧，如下图所示。14假设现有银行存款2万元，以后每月月初往银行存款1500元，银行的存款利率是3.3%，如果要计算存多久后，银行存款变为10万元，可按如下的方法计算：输入设置输入输入设置输入按【shift+F3】组合键，弹出“函数参数”对话框，从中设置相应的参数，如下图所示。修改修改NPER函数基于固定利率及等额分期付款方式，返回某项投资的总期数。NPER函数基于固定利率及等额分期付款方式，返回某项投资的总期数。15使用RATE函数计算假设5年前向某公司投入资金10万元作为入股资金，投入后每年年终可获分红6000元，现在公司一次性支付40万元，购买这些股份，要计算这笔资金的年利率，可使用RATE函数来计算，其具体的计算方法如下：按【shift+F3】组合键，弹出“函数参数”对话框，从中设置相应的参数，如下图所示。2.选择1.选择设置输入2.选择1.选择设置输入单击“确定”按钮，得到年利率，如下图所示。RATE函数用于返回年金的各期利率。RATE函数的语法结构为RATE(nper,pmt,pv,fv,type,guess)RATE函数用于返回年金的各期利率。RATE函数的语法结构为RATE(nper,pmt,pv,fv,type,guess)，其中,Guess为预期利率，如果省略，则默认为10%。16用IRR函数计算内部收益率IRR函数用于返回由数值代表的一组定期现金流的内部收益率。这些现金流必须按固定的间隔产生，如按月或按年。内部收益率为投资的回收利率，其中包含定期支付（负值）和定期收入（正值）。使用该函数计算内部收益率的具体方法如下：新建工作表，从中输入数据，然后选择单元格C14，按【Shift+F3】组合键，弹出“插入函数”对话框，从中选择IRR函数，如下图所示。设置单击“确定”按钮，弹出“函数参数”对话框，从中设置函数的参数，如下图所示。设置输入单击“确定”按钮，得到投资三年后的内部收益率，然后在单元格C15中输入函数“=IRR(C5:C10)”，如下图所示。输入按【Ctrl+Enter】组合键，计算出投资五年后的内部收益率，如下图所示。17XIRR函数返回一组不定期发生的现金流的内部收益率。使用该函数计算内部收益率的具体操作方法如下：选择2.选择1.输入设置输入选择2.选择1.输入设置输入按【shift+F3】组合键，弹出“函数参数”对话框，从中设置相应的参数，如下图所示。单击“确定”按钮，得到计算结果，然后在“开始”选项卡下“数字”组中设置该单元格的数字格式为“百分比”，如下图所示。18假设投资某个项目，所需资金30万元，从银行的贷款利率是4.5%，投资的收益是8.9%，其具体的计算方法如下：新建工作表，从中相关的输入数据，然后选择单元格E6，如下图所示。按【shift+F3】组合键，弹出“插入函数”对话框，从中选择MIRR函数，如下图所示。MIRR函数返回某一连续期间内现金流的修正内部收益率。该函数计算充分考虑了投资的成本和现金再投资的收益率。该函数的语法结构为MIRR(values,finance_rate,reinvest_rate)MIRR函数返回某一连续期间内现金流的修正内部收益率。该函数计算充分考虑了投资的成本和现金再投资的收益率。该函数的语法结构为MIRR(values,finance_rate,reinvest_rate)，其中，Values为一个数组或对包含数字的单元格的引用。这些数值中包括支出（负值）及收入（正值）；Finance_rate为现金流中使用的资金支付的利率；Reinvest_rate为将现金流再投资的收益率。设置设置单击“确定”按钮，弹出“函数参数”对话框，从中输入各相关参数，如下图所示。设置设置单击“确定”按钮，得到投资一年的修正内部收益率，然后向下拖动填充柄，填充公式，结果如下图所示。在7年内，投资项目的内部收益率都为负，所以该投资不可行。在7年内，投资项目的内部收益率都为负，所以该投资不可行。19用NPV函数计算净现值假设一年前投资5000元，在本年的年末净收入1200元，在第二年的年末净收入达到5000元，第三年的年末净收入达到15000元，折现率8%，要计算其净现值，可使用NPV函数计算，其具体操作方法如下：新建工作表，从中输入数据，然后单击单元格C11，从中输入函数“=NPV(”，如下图所示。输入输入按【shift+F3】组合键，弹出“函数参数”对话框，从中设置相应的参数，如下图所示。单击“确定”按钮，输入公式，然后双击该单元格，将公式修改为“=NPV(C9,C6,C7,

C8)+C5”得到净现值，如下图所示。NPV函数通过使用贴现率以及一系列未来支出（投资额）和收入额，返回一项投资的净现值。NPV函数通过使用贴现率以及一系列未来支出（投资额）和收入额，返回一项投资的净现值。20计算期初投资的净现值假设第一年初投资5000元，第一年年末净收入1000元，第二年年末净收入6000元，第三年年末的净收入为12000元，折现率10%，要计算其净现值，可按如下方法操作：新建工作表，从中输入数据，然后单击单元格C11，从中输入函数“=NPV(”，如下图所示。输入修改输入设置按【shift+F3】组合键，弹出“函数参数”对话框，从中设置相应的参数，如下图所示。输入修改输入设置Value1,value2,...在时间上必须具有相等间隔，并且都发生在期末。Value1,value2,...在时间上必须具有相等间隔，并且都发生在期末。单击“确定”按钮，输入公式，然后双击该单元格，将公式修改为“=NPV(C9,C6,C7,

C8)+C5”得到净现值，如下图所示。Rate为贴现率，通常为必要收益率或加权资本成本；NPVRate为贴现率，通常为必要收益率或加权资本成本；NPV函数的参数为每年末净现金流量，所以在该实例中不能直接将“年初的投资额”作为NPV函数的参数。21用XNPV函数计算净现值XNPV函数用于返回一组不定期发生的现金流的净现值，使用该函数计算净现值的具体操作方法如下：新建工作表，从中输入数据，然后单击单元格C12，从中输入函数“=XNPV(”，如下图所示。设置按【shift+F3】组合键，弹出“函数参数”对话框，从中设置相应的参数，如下图所示。设置单击“确定”按钮，得到不定期现金流的净现值，结果如下图所示。例1从身份证中提取出生日期拖动2.输入设备输入1.输入设备使用MID函数能够从身份证中提取出生日期，具体操作方法如下：拖动2.输入设备输入1.输入设备新建工作表，在单元格B2中输入身份证号，在单元格C2中输入函数，如下图所示。MID函数用于返回文本字符串中从指定位置开始的特定数目的字符，语法为：MID(text,start_num,num_chars)MID函数用于返回文本字符串中从指定位置开始的特定数目的字符，语法为：MID(text,start_num,num_chars)。例如，MID(B2,7,4)是指从单元格B2中第7个字符开始提取，提取4个字符。按【Enter】键，得到的结果如下图所示。例2用特殊符号补充空位在财务工作中，通常要将输入金额中的空位补齐，在Excel中使用函数可以轻松的办到，具体操作方法如下。在单元格中输入数据和函数，如下图所示。按【Ctrl+Enter】组合键，得到公式的计算结果；然后拖动填充并进行公式填充，得到的结果如下图所示。REPT函数用来不断地重复显示某一文本字符串，对单元格进行填充。REPT函数用来不断地重复显示某一文本字符串，对单元格进行填充。例3创建文本直方图使用TEPT函数还可以创建文本直方图，具体操作方法如下。输入打开“楼盘概览”工作表，在单元格I5中输入函数“=REPT(“★”,INT(H5/100))，如下图所示。输入按【Ctrl+Enter】组合键，得到公式的计算结果；然后将公式复制到单元格区域I6:I12中，得到的结果如下图所示。例4快速统计单词个数如果单元格中的字符是英文单词，因为英文中的每个单词由不同的字母组成，因此不能简单地用LEN函数来统计，这时可用如下的方法来统计单词的个数。2.输入1.输入2.输入1.输入新建一张工作表，在单元格B16中输入单词，在单元格B18中输入公式“=LEN(B16)-

LEN(SUBSTITUTE(B16,””,””))+1”，其中SUBSTITUTE函数用于将单元格B6中的空格替换为空,也就是删除空格，LEN函数用于计算字符数，两个LEN函数求得的字符数相减，即为空格数，空格数再加1，即为空格隔开的单词数，如下图所示。2.输入1.输入2.输入1.输入输入输入按【Enter】键，得出计算结果，如下图所示。LEN函数用于返回文本字符串中的字符数，是ELEN函数用于返回文本字符串中的字符数，是Excel中较常用的函数。例5快速统计字符出现的次数在工作中，有时需要统计单元格中出现某以相同的字符的次数，这时，也可应用LEN函数。其具体操作方法如下：新建工作表，在其中输入文本内容，然后在单元格B18中输入公式“=（LEN(B16)-LEN(

SUBSTITUTE(B16),”风尚”，””)）/2”，该公式是用于计算单元格B16中“风尚”的个数，如下图所示。按【Ctrl+Enter】组合键，得到计算的结果，如下图所示。例6用嵌套函数限定产品的价格使用SUM、N和FREQUENCY嵌套函数也可以限定产品的价格，下面仍以上个技巧中的实例为例进行介绍。12.1,D4))”，如下图所示，拖动输入输入拖动输入输入按【Ctrl+Enter】组合键，得到公式计算结果，如下图所示。使用MIN使用MIN和MAX函数的嵌套，通过将价格与最大值、最小值的比较来实现限价。例7巧妙生成工资条打印工资条是现代办公中经常遇到的问题，在Excel中使用函数也可以自动创建工资条，然后打印出来即可。其操作方法如下：在Excel中，创建“公司员工工资表”，如下图所示。选择单元格B19，并在其中输入公式“=IF(MOD(ROW(C3)-2,3),OFFSET($B$3,(MOD(ROW(C3)-3,3)>0)*ROUND((ROW(C3)-2)/3,),COLUMN(C3)-2),"")”如下图所示。按【Ctrl+Enter】组合键，得到公式的计算结果，然后向右拖动填充柄，再向下拖动填充柄，得到完整的工资条，如下图所示。MOD(ROW(C3)-3,3)>0)*ROUND((ROW(C3)-2)/3,)的含义是将MOD函数求得的余数和0进行比较，如果逻辑值为MOD(ROW(C3)-3,3)>0)*ROUND((ROW(C3)-2)/3,)的含义是将MOD函数求得的余数和0进行比较，如果逻辑值为FALSE，不管ROUND函数求得的数值为多少，它们相乘的结果始终为零，这样就保证了OFFSET函数所返回的行数不变，即显示列标题。如果MOD函数求得余数不为零，则和0进行比较后，得到逻辑值TRUE,与ROUND函数相乘后，得到下移行数，从而通过OFFSET函数显示从单元格B3开始向下移一定行数后的单元格数据。例8巧妙使用循环引用记录时间显示3.输入1.选择填充输入设置如果能够在工作表中记录数据输入的时准确时间，将给用户的工作带来很大的好处。在Excel中使用函数能够轻松实现用户的要求，其具体操作步骤如下：显示3.输入1.选择填充输入设置新建工作表，从中输入数据，将“时间”列的数字公式设置为“时间”，然后在可修改“时间”的数字格式为“常规”，如下图所示。在单元格C3中输入公式“=IF(ISBLANK(

B3),"",IF(C3="",NOW(),C3))”，如下图所示。要正确得出输入的时间，应该先输入并复制公式，然后在“收入要正确得出输入的时间，应该先输入并复制公式，然后在“收入”列中输入数据。2.选中打开“Excel选项”对话框，在左侧的列表中选择“公式”选项，在右侧的“计算选项”选项区中选中“启用迭代计算”复选框，并将“最多迭代次数”设置为1，如下图所示。2.选中例9巧用公式屏蔽出错信息在填充公式时，有时候可能会因为公式中引用的单元格信息改变，导致其不满足公式的参数定义等原因，导致公式计算结果错误，例如，在“合格产品统计”工作表中，由于要统计合格产品的个数，所以使用了公式“=C4*E4”，在产品合格率未检测出来时，使用该公式计算，就会显示错误信息，如下图所示。输入输入要避免这种情况的发生，可以通过对公式进行完善，巧妙地解决单元格中显示错误的公式计算结果，其具体操作方法如下：输入输入在单元格F4中输入公式“=IF(ISNUMBER(

C4*E4),C4*E4,"无法计算")”，如下图所示。显示输入显示输入得到计算结果后，拖动填充柄复制公式，单元格F7中将不会得到错误信息，效果如下图所示。在单元格C9中输入公式“=COS(B9*PI()/

180”或者“COS(RADIANS(B9))”，都将求得角度为30°的余弦，如下图所示。COS函数的语法结构为：COS(number)COS函数的语法结构为：COS(number)。Number为需要求余弦的角度，以弧度表示。如果角度是以度表示的，则可将其乘以PI()/180或使用RADIANS函数将其转换成弧度。例1用COUNTIF函数可以计算单元格区域中满足给定条件的单元格的个数，下面以求“GHU系列产品种类”为例，介绍该函数的使用方法：打开“商品价格表”，从中输入数据，然后在单元格F21中输入公式“=COUNTIF(F13:

F19,"GHU")”，如下图所示。输入输入按【Ctrl+Enter】组合键，得到公式计算结果，如下图所示。例2显示求满足多个条件的单元格个数，最常用的方法是使用COUNT和IF函数嵌套来实现。下面以计算“员工考核成绩表”中各科成绩都在75分以上的员工人数为例，介绍其具体方法。显示新建工作表，从中输入数据，然后在单元格E12中输入公式“=COUNT(IF((C4:C10>75)*(

输入输入按【Ctrl+shift+Enter】组合键，得到公式计算结果，计算出满足条件的单元格有3个，如下图所示。该实例中的IF条件句用于返回各科成绩均大于75分的单元格区域。再使用COUNT函数计算数值结果的个数。该实例中的IF条件句用于返回各科成绩均大于75分的单元格区域。再使用COUNT函数计算数值结果的个数。例3COUNTIFS函数是Excel2007中新增的函数，该函数也可用于计算某个单元格区域中满足多重条件的单元格数目。其具体操作方法如下：打开“员工考核成绩表”，在单元格D5中输入公式“=COUNTIFS(C4:C10,">75",D4:

D10,">75",E4:E10,">75")”，如下图所示。按【Ctrl+Enter】组合键，将得到计算的结果，统计出满足条件的人数有1人，如下图所示。使用COUNTIFS函数时，只有当单元格区域中的数据满足为其指定的所有相应条件时才对其进行计算。使用COUNTIFS函数时，只有当单元格区域中的数据满足为其指定的所有相应条件时才对其进行计算。例4在用Excel统计成绩时，根据成绩高低进行排序，按序列自动填充出名次。当成绩相同时，填充出名次还是不同的，如下图所示。此时，可以利用使用RANK排位函数实现同分同名次的操作，具体操作方法如下：打开“员工考核成绩表”，添加“总分”和“排名”列，在单元格G4中输入函数“=RANK(F4,F$4:F$10,0)”，如下图所示。填充输入输入填充输入输入按【Ctrl+Enter】组合键后，会得到该行总成绩的分数排名。使用拖动填充柄的方法，向下填充公式即可排名，效果如下图所示。例5在G4单元格中输入函数“=COUNTIF(F$4:

F$10,">"&F4)+1”，如下图所示。输入输入按【Ctrl+Enter】组合键，显示名次。然后通过拖动该单元格的填充柄向下填充即可实现排名，如下图所示。例6采用IF函数和COUNT函数的嵌套照样能够实现同分同名次排列的嵌套，具体操作方法如下：在单元格G4中输入“1”，然后在G5单元格中输入函数“=IF(F5=F4,G4,COUNT($F$4:

F5))”，如下图所示。输入输入输入输入输入输入按【Ctrl+Enter】组合键，该单元格中显示排名。拖动该单元格中的填充柄向下填充公式，得到排名结果，如下图所示。对于前两种方法，在没有将总分进行排序的情况下，也可以得出正确的排名；而最后一种方法只有在对总分进行降序排序时才能得出正确的排名结果。对于前两种方法，在没有将总分进行排序的情况下，也可以得出正确的排名；而最后一种方法只有在对总分进行降序排序时才能得出正确的排名结果。例7填充FREQUENCY频率函数用于计算数值在某个单元格区域内的出现频率，然后返回一个垂直数组。例如，计算不同的分段中考核成绩的个数，其具体使用方法如下：填充输入新建工作表，从中输入姓名和成绩两列数据，如下图所示。输入输入输入在工作表中添加“成绩分段”和“各分段中成绩的个数”列，并在“分段”列中输入几个分段，表示考核成绩划分为<=60、61~70、71~80、81~90、90分以上五个区域，如下图所示。选择单元格区域D3:D7，然后按【F2】键输入公式“=FREQUENCY(B2:B9,C3:C6)”，如下图所示。按【Ctrl+Shift+Enter】组合键，返回公式的计算结果，如下图所示。例8通过函数也可以在单元格区域中填充数字序列，其具体操作方法如下：在新建的工作表中输入相关的数据信息，然后在单元格D3中输入公式“=MOD(

COLUMN(A1)-1,$C3)+$B$3)”,如下图所示。输入填充输入输入填充输入计算出公式结果，分别向右、向下拖动填充柄，填充公式，结果如下图所示。2.单击1.选择修改2.单击1.选择修改例9要使列表中的编号在进行数据筛选后，自动重新按顺序排列，可按如下方法操作：在Excel2007中，创建一张“产品订购单”，在这里“订单编号”列不使用输入数字序列的方法创建。用户可在单元格B4中输入函数“=SUBTOTAL(103,$C3:C4)-1”，如下图所示。求得计算结果后，用填充柄自动填充公式，效果如下图所示。填充填充选择数据表中任意单元格，如单元格B3，单击“数据”选项卡下“排序和筛选”组中的“筛选”按钮，效果如下图所示。单击“产品代码”所在单元格右侧的下拉按钮，在弹出的下拉面板的列表中选中“AM001”复选框，如下图所示。输入选中输入选中单击“确定”按钮，得到筛选结果，“订单编号”自动重新按顺序排列，如下图所示。SUBTOTAL函数用于返回列表或数据库中的分类汇总，隐藏在创建了分类汇总或数据筛选后，可以使用该函数对列表中的数据进行修改。SUBTOTAL函数用于返回列表或数据库中的分类汇总，隐藏在创建了分类汇总或数据筛选后，可以使用该函数对列表中的数据进行修改。10使用SUBTOTAL函数还可以方便地统计总数，具体操作方法如下：在“产品订购单”的单元格D16中输入函数“=SUBTOTAL(109,D4:D15)”,如下图所示。输入输入输入输入通过公式计算，统计出所有订单的数量为62100，如下图所示。对数据列表进行筛选后，筛选出产品AM001的订单数量，如下图所示。SUBTOTAL函数的第1个参数为1~11（包含隐藏值）或101~111（忽略隐藏值）中的某个数字，代表已使用相关函数对数据列表中的数据进行计算。其中103代表使用COUNTA函数进行计算；109代表使用SUM函数进行计算。SUBTOTAL函数的第1个参数为1~11（包含隐藏值）或101~111（忽略隐藏值）中的某个数字，代表已使用相关函数对数据列表中的数据进行计算。其中103代表使用COUNTA函数进行计算；109代表使用SUM函数进行计算。11用DSUM函数进行多条件求和的具体操作方法如下：创建“员工考核成绩表”后，在单元格区域C12:D13中输入求和的条件，然后在单元格E13中输入函数“=DSUM(B3:G10,"工作能力",C12:D13)”，如下图所示。结果结果按【Ctrl+Enter】组合键，得到求和的结果，即所有满足“所在部门”为“市场部”，“总分”大于200的员工的“工作能力”总得分情况，如下图所示。12用POISSON函数计算概率假设厂家每生产一批产品，出现次品的个数是7，要求产品不出现次品的概率，可按如下方法操作：输入输入新建工作表，因为求产品不出现次品的概率，所以将期望出现的次品的个数就是0。在工作表的单元格C4中输入函数“=POISSON(C2,C3,FALSE)”，如下图所示。输入输入按【Ctrl+Enter】组合键，计算出不出现次品的概率，结果如下图所示。POISSON函数返回泊松分布，通常用于预测一段时间内事件发生的次数。POISSON函数返回泊松分布，通常用于预测一段时间内事件发生的次数。POISSON函数函数中有三个参数，其中第1个参数为随机发生的次数，第2个参数为平均次数，第3个参数为一个逻辑值，如果为false，表示返回泊松概率密度函数，即随机事件发生的次数恰好为某个确定的数值。如果为true，表示返回泊松累积分布概率，即随机事件发生的次数在0到某个确定数值之间（包含0和1）。POISSON函数函数中有三个参数，其中第1个参数为随机发生的次数，第2个参数为平均次数，第3个参数为一个逻辑值，如果为false，表示返回泊松概率密度函数，即随机事件发生的次数恰好为某个确定的数值。如果为true，表示返回泊松累积分布概率，即随机事件发生的次数在0到某个确定数值之间（包含0和1）。13求累计分布概率假设公司所有员工每个月请假的平均次数为6次，要求员工请假次数小于5次出现的概率，可按如下方法操作：新建工作表，从中输入数据，并在单元格C4中输入函数“=POISSON(5,C3,TRUE)”，如下图所示。按【Ctrl+Enter】组合键，计算出员工请假次数小于5次出现的概率，如下图所示。14计算每月期望出单的概率假如公司每月收到订单的平均份数为96.8，则计算公司在一个月内收到的订单数大于105份时的概率，可按如下方法操作：输入新建工作表，从中输入数据，并在单元格C4中输入函数“=1-POISSON(105,C3,TRUE)”，如下图所示。输入按【Ctrl+Enter】组合键，计算出单在大于105份的概率，如下图所示。15计算线性回归方程斜率输入SLOPE函数返回根据known_y's和known_x's中的数据点拟合的线性回归直线的斜率。使用该函数计算线性回归方程的斜率的具体操作方法如下：输入假设已知线性回归方程的部分X、Y值，如下图所示。在单元格C14中输入函数“=SLOPE(C3:

C12,B3:B12)”，如下图所示。按【Ctrl+Enter】组合键，得到公式的计算结果，如下图所示。SLOPE函数的参数可以是数字，或者是包含数字的名称、数组或引用SLOPE函数的参数可以是数字，或者是包含数字的名称、数组或引用16LINEST函数使用最小二乘法对已知数据进行最佳直线拟合，然后返回描述此直线的数组。利用该函数也可以计算线性方程的斜率和Y轴的截距，其操作方法如下：新建工作表，从中输入相关的数据，并选择单元格区域E4:F4然后按【F2】键，将光标定位到所选单元格区域中，输入函数“=LINEST(C3:C12,B3:B12,,FALSE)”如下图所示。输入输入按【Ctrl+Shift+Enter】组合键，得到数组公式的计算结果，如下图所示。17PEARSON函数用于返回Pearson（皮尔生）乘积矩相关系数r，其范围在-1.0~1.0之间（包括-1.0和1.0）的无量纲指数，反映了两个数据集合之间的线性相关程度。该函数的使用方法如下：2.输入1.输入输入2.输入1.输入输入按【Ctrl+Enter】组合键，即可得到得出的Pearson乘积矩相关系数，如下图所示。PEARSON函数的语法结构为PEARSON函数的语法结构为PEARSON(array1,array2)，其中array1为自变量，array2为因变量。18用RSQ函数求R的平方RSQ函数用于返回根据known_y's和known_x's中数据点计算得出的Pearson乘积矩相关系数的平方，其值也可以理解为y方差与x方差的比例。该函数的使用方法如下：新建工作表，从中输入数据，并在单元格C15中输入函数“=RSQ(C3:C13,B3:B13)”，如下图所示。选中并拖动设置选中并拖动设置按【Ctrl+Enter】组合键，即可得到得出的Pearson乘积矩相关系数的平方值，如下图所示。19快速用函数预测未来销售额如果销售额和销售年份之间存在线性关系，使用LINEST统计函数可以预测未来年份的销售额，其具体操作方法如下：新建工作表，将线性回归分析数据输入到其中，如下图所示。输入输入假设趋势预测方程为y=mx+b，利用LINEST函数，可方便的求出m和b的值，如下图所示。输入相关的数据内容，然后在单元格D13中输入函数“=SUM(C3:C11,A3:A11)*{10,1}）”，通过该函数计算出预测的值，如下图所示。输入输入因为销售额和销售年份之间满足预测方程y=mx+b，所以利用该方程也可求得预测出的销售额情况，如下图所示。20用TREND函数预测未来值TREND函数用于返回一条线性回归拟合线的值。即找到适合已知数组known_y's和known_x's的直线（用最小二乘法），并返回指定数组new_x's在直线上对应的y值。下面介绍使用该函数求得预测值的方法：假设要预测未来五年内的销售额，给出的相关数据如下图所示。选择单元格区域D15:D19，按【F2】键，从中输入函数“=TREND(D4:D12,B4:B12,B15:

B19)”,如下图所示。单击输入单击输入按【Ctrl+Shift+Enter】组合键，得到数组公式的计算结果，如下图所示。TREND函数的语法结构为TREND函数的语法结构为TREND(known_y's,known_x's,new_x's,const)，如果以数组形式输入，则将得到相应的多个数值。21快速为员工年终评估打分在大多数企业中，年终要对员工进行考核评估，评估的最后得分可按如下的方法计算得出。在“年终评估分值计算”工作表中，单击单元格G3，如下图所示。设置选择设置选择单击编辑栏左侧的“插入函数”按钮，弹出“插入函数”对话框，从中选择TRIMMEAN函数，如下图所示。单击“确定”按钮，弹出“函数参数”对话框，从中设置参数，如下图所示。TRIMMEAN函数用于返回去掉数据集中一定百分比的的最大值和最小值后，求的剩余数据的平均值。结果单击“确定”按钮，计算得出最终得分，然后拖动填充柄填充公式，如下图所示。TRIMMEAN函数用于返回去掉数据集中一定百分比的的最大值和最小值后，求的剩余数据的平均值。结果例1巧用函数求EAN_13校验码在Excel中，使用函数就可以轻松的求得EAN-13校验码。其具体操作方法如下：新建工作表，从中输入数据，然后在单元格C3中输入公式“=10-RIGHT(SUM(MID($

B3,{1;2;3;4;5;6;7;8;9;10;11;12},1)*{1;3;1;3;1;3;1;3;1;3;1;3}))”，如下图所示。输入输入按【Ctrl+Enter】组合键，得到公式计算结果，计算出单元格B3中条码的校验位数字为8，如下图所示。拖动填充柄，向下填充其他公式，计算出其他条码的校验位数字，如下图所示。填充填充EAN_13条码校验码的求法为：从条码第2位数字（从左往右）开始，将所有偶数位的数字求和后再乘以3。然后从条码的第1位数字开始，所有奇数位的数字求和。将得到的两个结果相加，最后将所得结果用大于或等于该结果且为10最小整数倍的数减去，其差即为所求校验码的值。EAN_13条码校验码的求法为：从条码第2位数字（从左往右）开始，将所有偶数位的数字求和后再乘以3。然后从条码的第1位数字开始，所有奇数位的数字求和。将得到的两个结果相加，最后将所得结果用大于或等于该结果且为10最小整数倍的数减去，其差即为所求校验码的值。输入例2巧用公式制作九九乘法表输入在Excel中通过公式可以快速制作出九九乘法表，其具体操作方法如下：新建工作表，在第2行和A列中输入数字1~9，然后设置乘法表的样式，并在单元格B3中输入公式“=B$2&"*"&$A3&"="&B$2*$A3”，如下图所示。按【Ctrl+Enter】组合键，将得到计算的结果,然后单击“开始”选项板下“剪贴板”组中的“复制”按钮，复制公式，并按住【Ctrl】键的同时选择单元格C4、D5、E6、F7、G8、H9、I10和J11，再单击“剪贴板”组中“粘贴”按钮下方的下拉按钮，在弹出的快捷菜单的选择“公式”选项，粘贴公式，如下图所示。输入复制输入复制分别拖动相应单元格右下角的填充柄，填充其他公式，得到九九乘法表，效果如下图所示。例3快速产生随机的整数前面已经介绍过使用RAND函数可以得到一系列的随机的数字，利用IN和RAND函数的嵌套，则可以产生任意范围内的整数，其具体操作方法如下：在单元格C2中输入嵌套函数“=INT(

RAND()*10)”，确认后，将在该单元格显示0~10之间的随机整数，在其他的单元格中复制公式，得到的结果如下图所示。输入在单元格C3中输入嵌套函数“=INT(

RAND()*100)”,并通过复制的方法复制公式，在单元格区域C3:G6中得到0~100之间的随机数字，如下图所示。输入例4巧妙实现隔列求和要快速的进行隔行求和，可通过嵌套函数来实现。下面以“公司支出统计表”工作表中偶数列（第4列和第6列）的值为例，介绍隔行求和的具体操作方法。输入拖动输入输入打开“公司支出统计表”工作表，在单元格G6中输入公式“=SUM((C6:F6)*(MOD(

COLUMN(C6:F6),2)=0))”如下图所示。输入拖动输入输入按【Ctrl+Shift+Enter】组合键，得到公式的计算结果，如下图所示。例5要快速的进行隔行求和，可通过嵌套函数来实现。下面以“公司支出统计表”工作表中偶数列（第4列和第6列）的值为例，介绍隔行求和的具体操作方法。打开“公司支出统计表”工作表，从中输入数据，然后在单元格的D6中输入公式“=SUM((MOD(ROW($A$1:$A$8),ROW(A2))=1)*C$6:C$13)”如下图所示。按【Ctrl+Shift+Enter】组合键，得到公式的计算结果，如下图所示。将鼠标指针移动到该单元格右下角，拖动填充柄，向下填充公式，然后在向右拖动填充柄，结果如下图所示。例6让求和结果中不显示0在进行求和计算时，可能会求得的计算结果为0，如果打印出来，会影响打印效果。这时，可以通过利用公式将为0的结果不显示。其具体操作方法如下：在“公司支出统计表”工作表中添加“9月份”一行，然后在单元格G6中输入求和公式，求得结果，并将其公式复制到单元格G14中，结果会得到0值，如下图所示。输入输入如果在单元格G6中输入“=IF(SUM(C6:F6

),””,SUM(C6:F6)”，然后在将其复制到单元格G14中，结果不显示0值，如下图所示。输入输入例7快速求相交区域的数据和在Excel中，如果按平时书写分数的方法输入分数，输入的数据会自动转换成日期，要想输入分数，可按如下方法操作。公式求得的结果就是单元格区域B2:D4与单元格区域C4:E6相交部分的数据和，如下图所示。例8用AVERAGE函数和其他函数嵌套能够求得满足不同条件的数据的平均值，其具体操作方法如下：在“员工考核成绩表”中，单击单元格C12，从中输入“=AVERAGE(IF((C4:E10>=60)*(C4:

E10<=75),C4:E10))”，如下图所示。按【Ctrl+Shift+Enter】组合键，得到数组公式的计算结果，如下图所示。通过IF函数用于判断单元格区域C4:E10中满足大于等于60，小于等于75的数据。通过IF函数用于判断单元格区域C4:E10中满足大于等于60，小于等于75的数据。例9要求单元格区域中最大几个数的平均值，可按如下的方法操作：输入输入在单元格D12中输入公式“=AVERAGE(

LARGE(C4:E10,ROW(INDIRECT("1：3"))))”，如下图所示。输入输入按【Ctrl+Shift+Enter】组合键，得到数组公式的计算结果，如下图所示。ROW(INDIRECT("1:7")的含义是得到数组{1，2，3}，再使用LARGE函数求得单元格区域C4:E10中的前7个最大值。ROW(INDIRECT("1:7")的含义是得到数组{1，2，3}，再使用LARGE函数求得单元格区域C4:E10中的前7个最大值。10巧妙用空格对齐姓名在Excel中制作电子表格时，输入姓名是经常遇到的，而姓名有的是三个字，有的是两个字，让人看起来有点乱，这时我们可在两个字的姓名中间添加一个空格，来使输入的姓名更加地整齐，下面介绍一种批量添加空格的方法。打开“工资变动表”工作表，在单元格H6中输入公式“=IF(LEN(C6)=2,MID(C6,1,1)&""&MID(C6,2,1),C6)”，如下图所示。得到提取的结果后，再使用填充柄复制公式，最终结果如下图所示。删除2.单击1.选中删除2.单击1.选中11巧从身份证中判断性别从身份证中可以判断公民的性别，15位的身份证中第15位数代表性别，18位身份证的第17位数代表性别，其中奇数为“男”，偶数为“女”，下面以18为身份证为例，介绍使用公式判断性别的方法。新建工作表，从中输入身份证号等相关内容，在单元格C3中输入“=IF(MOD(MID(B3,

17,1),2),"男","女")”如下图所示。输入输入输入得到公式计算结果后，通过填充柄填充公式，结果如下图所示。输入输入输入12快速按性别统计员工人数利用Excel函数分别统计男性员工和女性员工的人数，操作方法如下：打开“员工档案资料表”工作表，在单元格D18中统计计男性员工人数，应在其中输入公式“="男"&COUNTIF(A1:A15,"男")&"人"”，再选择空白单元格，在其中输入公式“="女"&COUNTIF(D2:D227,"女")&"人"”，按【Enter】键即可，如下图所示。填充输入按【Enter】键，得到计算结果，然后在单元格D19中统计女性员工的人数，从中输入公式“="女"&COUNTIF(D2:D227,"女")&"人"”，按【Enter】键即可。如下图所示。填充输入13用IF函数限定产品的价格如果只允许产品的价格在一定的价格范围内变化，可以使用函数来实现，其常用的方法有两种。下面以控制不同等级的产品GHU001的价格最高不超过19.8，最低不低于12.1为例，来介绍其具体的使用方法。新建工作表，从中输入数据，然后在单元格E4中输入公式“=IF(D4>19.8,19.8,IF(D4<

12.1,12.1,D4))”，如下图所示。该实例中公式的含义是当价格值大于19.8时，价格值仍然显示19.8，当价格值小于12.1时，仍然显示12.1，如果价格值在12.1~19.8的范围内，则价格不会变动，从而实现最高和最低的限价。该实例中公式的含义是当价格值大于19.8时，价格值仍然显示19.8，当价格值小于12.1时，仍然显示12.1，如果价格值在12.1~19.8的范围内，则价格不会变动，从而实现最高和最低的限价。按【Ctrl+Enter】组合键，得到公式计算结果，然后通过拖动填充柄，填充公式，结果如下图所示。通过将单元格D4和D5中的价格进行更改，来验证公式的正确性，如下图所示。填充更改填充更改14根据农历年份判断生肖每一年对应一个生肖，在Excel中根据这一规律，使用相应的函数可以根据农历年份判断出该年的生肖。其具体操作步骤如下：新建工作表，从中输入数据，并在单元格C3中输入公式“=MID("鼠牛虎兔龙蛇马羊猴鸡狗猪",MOD(B3-4,12)+1,1)”，如下图所示。输入输入按【Ctrl+Enter】组合键，得到公式的计算结果，然后向下拖动填充柄，填充公式，结果如下图所示。填充填充15用函数判断数字的奇偶性使用函数判断数字奇偶的方法非常简单，具体操作方法如下：新建工作表，从中输入数据，并在单元格C4中输入公式“IF(ISODD(B4),"奇数","偶数")”,如下图所示。输入输入ISODD为奇数判断函数，语法结构为ISODD(number)，如果其参数为奇数，则返回逻辑值TRUE,否则返回FALSE。ISODD为奇数判断函数，语法结构为ISODD(number)，如果其参数为奇数，则返回逻辑值TRUE,否则返回FALSE。按【Ctrl+Enter】组合键，得到公式的计算结果，然后将鼠标指针移动到该单元格的右下角，拖动填充柄，填充公式，结果如下图所示。利用其他的嵌套函数也可以对数字的奇偶性进行判断，如下图所示。ISEVEN函数用于判断数