excel2007技巧之6函数高级应用实战技巧

6.1 日期和时间函数实战技巧 例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函数计算 在单元格D4中输入公式“=DAY(EOMONTH( B4,-1))”，如下图所示。 按【Ctrl+Enter】组合键，得到公式计算结果，然后拖动填充柄填充公式，结果如下图所示。 例4 轻松求得当前日期所在的周数 每一年的1月1日所在的周是一年中的第一周，使用WEEKNUM函数可求得某个日期所在的周数，具体操作方法如下： 打开“台历”工作表，在单元格G6中输入函数“=WEEKNUM(E5)”，如下图所示。 单击【Ctrl+Enter】组合键，得到当前日期所在年中的周数，如下图所示。 例5 巧妙计算员工年龄 在Excel中，如果已知员工的出生年月，就可使用YEAR函数快速的计算出员工的年龄，具体操作方法如下： 在单元格D3中输入公式“=YEAR(TODAY ()-C3)-1900”，如下图所示。 按【Ctrl+Enter】组合键，得到结果，如下图所示。 在“开始”选项卡下“数字”组中的“数字格式”下拉列表框中，将该单元格的数字格式设置为“常规”，该单元格中将自动显示年龄，如下图所示。 将鼠标指针移动单元格D3的右下角，拖动填充柄，填充公式，效果如下图所示。 例6 计算两个日期之间工作日的天数 计算两个日期之间工作日的天数的具体操作方法如下： 在单元格C6中输入公式“=NETWORKDAYS( C4,D4)”，如下图所示。 按【Ctrl+Enter】组合键，得出计算结果，如下图所示。 例7 计算同年中两个日期之间的月数 计算同年中两个日期之间的月数的具体操作方法如下： 在单元格E4中输入公式“=MONTH(D4)- MONTH(C4)”，按【Ctrl+Enter】键即可计算出结果，如下图所示。 例8 计算跨年份两个日期之间的月数 在单元格E5中输入公式“=YEAR(D5)- YEAR(C5)*12+MONTH(D5)-MONTH(C5)”，按【Ctrl+Enter】键即可计算出结果，如下图所示。 例9 计算两个日期之间的年数 计算年数的方法很简单，只需用YEAY函数将两个返回值相减即可，如下图所示。 6.2 查找和引用函数实战技巧 例1 使用函数巧查询 在Excel中，VLOOKUP函数用于在表格数组的首列查找指定的值，并由此返回表格数组当前行中其他列的值。下面使用该函数来快速查找所需的值，具体操作方法如下： 打开“楼盘概览”工作表，在单元格区域E14:F15中输入数据内容和公式，如下图所示。 在单元格E15中输入相应的楼盘名称，将在单元格F15中显示该楼盘的开盘时间，如下图所示。 例2 用V.LOOKUP函数逆向查询 从上一个技巧中，已知VLOOKUP函数只能根据首列中指定的值查询其他列与之相对应的内容。其实，使用该函数和其他函数的嵌套也可以实现通过其他列的数据来查询首列的内容，即逆向查询，其具体操作方法如下： 如果已知楼盘的开盘时间，要查询楼盘名称，可输入公式“=VLOOKUP(F16,IF({0,1},D5: D12,E5:E12),2,FALSE)”，如下图所示。 按【Ctrl+Enter】组合键，将得到楼盘的名称，如下图所示。 例3 CHOOSE函数的用法 CHOOSE函数可以根据索引号返回数值参数列表中的数值。下面通过使用该函数得到奖金等级为例，介绍其使用方法。 在员工的奖金名单中，选择单元格D3，在其中输入公式“=CHOOSE(IF(C3<=3000,3,IF( C3>=5000,1,2)),"一等奖","二等奖","三等奖")”，如下图所示。 按【Ctrl+Enter】组合键，得到奖金等级，如下图所示。 通过拖动填充柄，复制公式，得到其他员工的销售奖金等级，如下图所示。 例4 快速查找化工原料的数量 继续上一个实例，通过将INDEX函数与MATCH函数配合使用，可以查找某种化学原料的数量，其具体操作方法如下： 在化工原料信息工作表中，单击单元格D48，从中输入公式“=INDEX(C39:C45, MATCH(" 草酸盐",B39:B45,0))”，如下图所示。 按【Ctrl+Enter】组合键确认，将得到“草酸盐”的数量为460，如下图所示。 例5 使用函数进行动态求和 下面通过实例来介绍使用函数进行动态求和的过程，具体操作方法如下： 新建工作表，从中输入产品销售额的相关信息，选择单元格D11，然后单击“数据”选项卡下“数据工具”组中的“数据有效性”按钮下方的下拉按钮，在弹出的下拉菜单中选择“数据有效性”选项，如下图所示。 将弹出“数据有效性”对话框，在其中的“允许”下拉列表框中选择“序列”选项，在“来源”文本框中设置序列来源，如下图所示。 单击“确定”按钮，在单元格D11右侧将出现一个下拉按钮，单击它，将弹出下拉列表，可以从中进行选择，效果如下图所示。 选择单元格区域C4:C8，然后单击“公式”选项卡下“定义的名称”组中的“定义名称”按钮，将弹出“新建名称”对话框，保持默认即可，如下图所示。 分别选择单元格区域D4:D8、E4:E8和F4:F8，定义名称，然后单击“定义的名称”组中的“名称管理器”按钮，弹出“名称管理器”对话框，从中可查看定义的名称，如下图所示。 选择单元格D12，从中输入公式“=SUM( INDIRECT($D$11))”，按【Ctrl+Enter】组合键确认，将得到“销售点1”的所有产品的销售额，如下图所示。 例6 判断员工姓名是否重复录入 在员工信息表中，要判断是否重复录入了员工的信息，可通过查找员工姓名是否重复，来进行判断（假如员工中没有重名者），其具体操作方法如下： 新建员工信息表，选择单元格H4，从中输入“=IF((ROW()=MATCH(C4,$C$1:$C$16,0)), "","重复")”，如下图所示。 按【Ctrl+Enter】组合键确认输入，然后拖动填充柄进行填充，将查找出重复的姓名，如下图所示。 例7 快速生成数字序列 制作工作表时，经常要输入数字序列，输入数字序列的方法有很多，这里我们使用函数来快速输入数字序列，其具体操作方法如下： 新建工作表，选择单元格区域A1:A10，然后按【F2】键，从中输入“=ROW(1:10)”，如下图所示。 按【Ctrl+Shift+Enter】组合键，确认数组输入，即可快速填充好数字序列，如下图所示。 要在一行中填充数字序列，可以先选择要输入数字序列的单元格区域，然后按【F2】键输入函数“=COLUMN(A:F)”，如下图所示。 按【Ctrl+Shift+Enter】组合键，确认数组输入，也可快速填充字序列，如下图所示。 例8 快速引用不同工作表中的数据 在制作工作表时，经常用在不同的工作表中引用数据信息，下面介绍一种使用函数引用其他工作表中的数据的方法。 在Sheet3中创建一张员工档案表，存放员工的基本信息，如下图所示。 切换到Sheet1中，输入有关员工工资的信息，然后选择单元格B4，单击“公式”选项卡下“函数库”组中的“查找与引用”下拉按钮，在弹出的下拉菜单中选择VLOOKUP函数，如下图所示。 弹出“函数参数”对话框，从中设置相应的参数，如下图所示。 单击【确定】按钮，关闭对话框，得到公式结果，然后拖动填充柄填充公式，如下图所示。 例9 返回最后一个非空单元格 在包含空的单元格中，要查找最后一个非空单元格中的值，可按如下方法操作： 建工作表，从中制作有关产品的信息数据表，新然后在单元格E13中输入公式“LOOKUP(CHAR(255),B:B)”，然后按【Ctrl+Enter】组合键确认，得到公式查找的结果，如下图所示。 在单元格E14中输入公式“＝LOOKUP(9E+307,C:D)，然后按【Ctrl+Enter】组合键，得到最后结果，如下图所示。 例10 快速查找最后非空单元格的位置 快速查找最后一个非空单元格的位置的具体操作步骤如下： 在单元格F3中输入公式“=MATCH(CHAR( 1),B:B,-1)”，如下图所示。 按【Ctrl+Enter】组合键，查找到的最后一个非空单元格的位置为11，如下图所示。 例11 快速定位到空单元格 通过创建单元格名称和使用链接函数可以快速定位到空单元格中。下面以具体的实例介绍快速定位到空单元格的方法。 新建工作表，从中输入产品信息，单击“公式”选项卡下“定义的名称”组中的“定义名称”按钮，如下图所示。 弹出“新建名称”对话框，在“名称”文本框中输入文字“空单元格”，在“引用位置” 文本框中输入公式“=OFFSET($B$1,COUNTA( $B:$B)+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”，使用填充柄进行填充，将得到不重复的值，结果如下图所示。 6.3 财务函数实战技巧 例1 使用PMT函数计算还款金额 PMT函数返回在固定利率下，投资或贷款的等额分期偿还额。使用该函数计算贷款的还款额的具体操作方法如下： 打开“分期付款计算表”工作表，选择单元格E4，如下图所示。 单击“公式”选项卡下“函数库”组中的“插入函数”按钮，弹出“插入函数”对话框，在“选择函数”列表框中选择PMT函数，如下图所示。 单击“确定”按钮，弹出“函数参数”对话框，从中输入参数，如下图所示。 单击“确定”按钮，得到计算结果，用前面所讲的复制公式的方法，复制公式，计算出其他的月偿还金额，如下图所示。 例2 使用PMT函数计算存款金额 假设在银行存款，要使10年后得到7万元，则计算每月的存款金额，可使用如下的方法得出： 新建工作表，从中输入数据，并在单元格C5中输入公式“=PMT(C4/12,C3*12,0,70000)”如下图所示。 按【Ctrl+Enter】组合键，计算出每个月应存款的金额，如下图所示。 例3 使用PPMT函数求本金偿还额 PPMT函数基于固定利率及等额分期付款方式，用于返回投资在某一给定期间内的本金偿还额。使用该函数计算本金偿还金额的具体操作方法如下： 新建工作表，从中输入数据，选择单元格C5，输入“=PPMT(”，如下图所示。 按【Shift+F3】组合键，弹出“函数参数”对话框，从中设置参数，如下图所示。 单击“确定”按钮，求得返还的第一年本金额，然后拖动填充柄，填充公式，最终效果如下图所示。 例4 使用IPMT函数求本金偿还额 IPMT函数基于固定利率及等额分期付款方式，用于返回给定期数内对的利息偿还额。使用该函数计算利息偿还金额的具体操作方法如下： 以上个技巧中的表格为基础，添加“返还利息”列，在单元格D5中输入函数“=IPMT($D$3,B5,$C$3,$B$3)”，如下图所示。 按【Ctrl+Enter】组合键，得到第一年的返还利息额，通过拖动填充柄，填充公式，得到每年的返还利息，最终效果如下图所示。 例5 使用FV函数计算投资价值 使用FV函数可以返回投资的现值。现值为一系列未来付款的当前值的累积和。该函数的使用方法如下： 在“投资计算表”中单击单元格F5，然后在其中输入函数“=FV(C5/12,D5,E5)”，如下图所示。 按【Enter】键，因为是表格所以自动填充公式计算出结果，如下图所示。 例6 使用PV函数计算年金的现值 假设要购买一种养老保险，购买成本为10万元，该保险可以在20年内于每月末回报800元、投资报酬率为10％，要计算该保险的年金现值，其具体的计算方法如下： 新建工作表，从中输入数据，然后单击单元格C7，从中输入函数“=PV(C4/12,C3*12, C2,0)”，如下图所示。 按【Ctrl+Enter】组合键，计算出年金的现值，如下图所示。 例7 用PV函数求一次性支付金额 假设要用5年时间分期付款购车，每年预付6000元，银行利率为6.5％，要计算如果要一次性支付完，需支付的金额，其具体的计算方法如下： 新建工作表，从中输入数据，然后单击单元格C7，从中输入函数“=PV(C4,C3,C2,1)”，如下图所示。 按【Ctrl+Enter】组合键，计算出的一次性支付金额，如下图所示。 例8 使用SLN函数计算资产折旧 SLN函数用于计算某项资产在一个期间中的线性折旧值。使用该函数计算线性折旧值的具体操作方法如下： 新建工作表，从中输入数据，选择单元格E3，如下图所示。 单击“公式”选项卡下“函数库”组中的“插入函数”按钮，弹出“插入函数”对话框，在“选择函数”列表框中选择SLN函数，如下图所示。 单击“确定”按钮，弹出“函数参数”对话框，从中输入参数，然后单击“确定”按钮，得到每年的线性折旧值，如下图所示。 例9 用固定余额递减法求资产折旧 DB函数用于计算在固定利率下给定日期内的资产折旧值，其具体操作方法如下： 新建工作表，从中输入数据，选择单元格C6，如下图所示。 单击编辑栏左侧的“插入函数”按钮，弹出“插入函数”对话框，在“选择函数”列表框中选择DB函数，如下图所示。 单击“确定”按钮，弹出“函数参数”对话框，从中输入参数，然后单击“确定”按钮，得到每年的线性折旧值，如下图所示。 通过向下拖动该单元格右下角的填充柄，在单元格区域C7:C15填充公式。然后在单元格C16中输入公式“=B3-C3-SUM(C6:C15)”，计算出固定资产在使用周期内最后6个月的折旧值，如下图所示。 例10 用双倍余额递减法求资产折旧 使用DDB函数可以计算固定资产折旧，其具体操作方法如下： 新建工作表，从中输入数据，然后单击“公式”选项卡下“函数库”组中的“财务”下拉按钮，在弹出的下拉面板中选择DDB函数，如下图所示。 将弹出“函数参数”对话框，从中设置相应的函数，如下图所示。 单击“确定”按钮，得到使用双倍余额递减法计算出的折旧值，然后在单元格区域C7:C11中填充公式，得到的结果如下图所示。 例11 用年限总和折旧法求资产折旧 SYD函数用于返回固定资产按年限总和折旧法计算出的指定期内的折旧值。使用该函数计算资产折旧的具体操作方法如下： 新建工作表，从中输入数据，然后单击“公式”选项卡下“函数库”组中的“财务”下拉按钮，在弹出的下拉面板中选择SYD函数，如下图所示。 将弹出“函数参数”对话框，从中设置相应的函数，如下图所示。 单击“确定”按钮，得到使用年限综合折旧法计算出的折旧值，然后在单元格区域C7:C12中填充公式，得到的结果如下图所示。 使用SUM函数可以计算出累计的折旧值，如下图所示。 例12 用可变递减折旧法求折旧值 VDB函数使用双倍余额递减法或其他指定的方法，返回指定的任何期间内（包括部分期间）的资产折旧值。使用该函数计算资产折旧的具体操作方法如下： 新建工作表，从中输入数据，然后单击在单元格D4中输入“=VDB(”，如下图所示。 按【Shift+F3】组合键，将弹出“函数参数”对话框，在其中可以设置其具体的函数参数，如下图所示。 单击“确定”按钮，得出第一个月的资产折旧值，如下图所示。 在相应的单元格分别输入函数，如下图所示。 计算得出的各资产折旧值，如下图所示。 例13 使用VDB函数计算累计折旧 使用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 使用NPER函数计算投资期数 假设现有银行存款2万元，以后每月月初往银行存款1500元，银行的存款利率是3.3%，如果要计算存多久后，银行存款变为10万元，可按如下的方法计算： 新建工作表，从中输入数据，然后单击单元格C7，从中输入函数“=NPER(”，如下图所示。 按【shift+F3】组合键，弹出“函数参数”对话框，从中设置相应的参数，如下图所示。 单击“确定”按钮，得到计算结果，然后双击该单元格，显示公式，并将其修改为“=NPER(C3/12,C4,C2,C5,1)/12”，按【Ctrl+Enter】组合键，计算的结果如下图所示。 例15 使用RATE函数计算 假设5年前向某公司投入资金10万元作为入股资金，投入后每年年终可获分红6000元，现在公司一次性支付40万元，购买这些股份，要计算这笔资金的年利率，可使用RATE函数来计算，其具体的计算方法如下： 新建工作表，从中输入数据，然后单击单元格C7，从中输入函数“=RATE(”，如下图所示。 按【shift+F3】组合键，弹出“函数参数”对话框，从中设置相应的参数，如下图所示。 单击“确定”按钮，得到年利率，如下图所示。 例16 用IRR函数计算内部收益率 IRR函数用于返回由数值代表的一组定期现金流的内部收益率。这些现金流必须按固定的间隔产生，如按月或按年。内部收益率为投资的回收利率，其中包含定期支付（负值）和定期收入（正值）。使用该函数计算内部收益率的具体方法如下： 新建工作表，从中输入数据，然后选择单元格C14，按【Shift+F3】组合键，弹出“插入函数”对话框，从中选择IRR函数，如下图所示。 单击“确定”按钮，弹出“函数参数”对话框，从中设置函数的参数，如下图所示。 单击“确定”按钮，得到投资三年后的内部收益率，然后在单元格C15中输入函数“=IRR(C5:C10)”，如下图所示。 按【Ctrl+Enter】组合键，计算出投资五年后的内部收益率，如下图所示。 例17 用XIRR函数计算内部收益率 XIRR函数返回一组不定期发生的现金流的内部收益率。使用该函数计算内部收益率的具体操作方法如下： 新建工作表，从中输入数据，然后单击单元格C12，从中输入函数“=XIRR(”，如下图所示。 按【shift+F3】组合键，弹出“函数参数”对话框，从中设置相应的参数，如下图所示。 单击“确定”按钮，得到计算结果，然后在“开始”选项卡下“数字”组中设置该单元格的数字格式为“百分比”，如下图所示。 例18 用MIRR函数计算内部收益率 假设投资某个项目，所需资金30万元，从银行的贷款利率是4.5%，投资的收益是8.9%，其具体的计算方法如下： 新建工作表，从中相关的输入数据，然后选择单元格E6，如下图所示。 按【shift+F3】组合键，弹出“插入函数”对话框，从中选择MIRR函数，如下图所示。 单击“确定”按钮，弹出“函数参数”对话框，从中输入各相关参数，如下图所示。 单击“确定”按钮，得到投资一年的修正内部收益率，然后向下拖动填充柄，填充公式，结果如下图所示。 例19 用NPV函数计算净现值 假设一年前投资5000元，在本年的年末净收入1200元，在第二年的年末净收入达到5000元，第三年的年末净收入达到15000元，折现率8%，要计算其净现值，可使用NPV函数计算，其具体操作方法如下： 新建工作表，从中输入数据，然后单击单元格C11，从中输入函数“=NPV(”，如下图所示。 按【shift+F3】组合键，弹出“函数参数”对话框，从中设置相应的参数，如下图所示。 单击“确定”按钮，输入公式，然后双击该单元格，将公式修改为“=NPV(C9,C6,C7, C8)+C5”得到净现值，如下图所示。 例20 计算期初投资的净现值 假设第一年初投资5000元，第一年年末净收入1000元，第二年年末净收入6000元，第三年年末的净收入为120 00元，折现率10%，要计算其净现值，可按如下方法操作： 新建工作表，从中输入数据，然后单击单元格C11，从中输入函数“=NPV(”，如下图所示。 按【shift+F3】组合键，弹出“函数参数”对话框，从中设置相应的参数，如下图所示。 单击“确定”按钮，输入公式，然后双击该单元格，将公式修改为“=NPV(C9,C6,C7, C8)+C5”得到净现值，如下图所示。 例21 用XNPV函数计算净现值 XNPV函数用于返回一组不定期发生的现金流的净现值，使用该函数计算净现值的具体操作方法如下： 新建工作表，从中输入数据，然后单击单元格C12，从中输入函数“=XNPV(”，如下图所示。 按【shift+F3】组合键，弹出“函数参数”对话框，从中设置相应的参数，如下图所示。 单击“确定”按钮，得到不定期现金流的净现值，结果如下图所示。 6.4 文本函数实战技巧 例1 从身份证中提取出生日期 使用MID函数能够从身份证中提取出生日期，具体操作方法如下： 新建工作表，在单元格B2中输入身份证号，在单元格C2中输入函数，如下图所示。 按【Enter】键，得到的结果如下图所示。 例2 用特殊符号补充空位 在财务工作中，通常要将输入金额中的空位补齐，在Excel中使用函数可以轻松的办到，具体操作方法如下。 在单元格中输入数据和函数，如下图所示。 按【Ctrl+Enter】组合键，得到公式的计算结果；然后拖动填充并进行公式填充，得到的结果如下图所示。 例3 创建文本直方图 使用TEPT函数还可以创建文本直方图，具体操作方法如下。 打开“楼盘概览”工作表，在单元格I5中输入函数“=REPT(“★”,INT(H5/100))，如下图所示。 按【Ctrl+Enter】组合键，得到公式的计算结果；然后将公式复制到单元格区域I6:I12中，得到的结果如下图所示。 例4 快速统计单词个数 如果单元格中的字符是英文单词，因为英文中的每个单词由不同的字母组成，因此不能简单地用LEN函数来统计，这时可用如下的方法来统计单词的个数。 新建一张工作表，在单元格B16中输入单词，在单元格B18中输入公式“=LEN(B16)- LEN(SUBSTITUTE(B16,” ”,””))+1”，其中SUBSTITUTE函数用于将单元格B6中的空格替换为空,也就是删除空格，LEN函数用于计算字符数，两个LEN函数求得的字符数相减，即为空格数，空格数再加1，即为空格隔开的单词数，如下图所示。 按【Enter】键，得出计算结果，如下图所示。 例5 快速统计字符出现的次数 在工作中，有时需要统计单元格中出现某以相同的字符的次数，这时，也可应用LEN函数。其具体操作方法如下： 新建工作表，在其中输入文本内容，然后在单元格B18中输入公式“=（LEN(B16)-LEN( SUBSTITUTE(B16),”风尚”，””)）/2”，该公式是用于计算单元格B16中“风尚”的个数，如下图所示。 按【Ctrl+Enter】组合键，得到计算的结果，如下图所示。 例6 用嵌套函数限定产品的价格 使用SUM、N和FREQUENCY嵌套函数也可以限定产品的价格，下面仍以上个技巧中的实例为例进行介绍。 在单元格C3中输入公式“=MIN(19.8,MAX( 12.1,D4))”，如下图所示， 按【Ctrl+Enter】组合键，得到公式计算结果，如下图所示。 例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】组合键，得到公式的计算结果，然后向右拖动填充柄，再向下拖动填充柄，得到完整的工资条，如下图所示。 例8 巧妙使用循环引用记录时间 如果能够在工作表中记录数据输入的时准确时间，将给用户的工作带来很大的好处。在Excel中使用函数能够轻松实现用户的要求，其具体操作步骤如下： 新建工作表，从中输入数据，将“时间”列的数字公式设置为“时间”，然后在可修改“时间”的数字格式为“常规”，如下图所示。 在单元格C3中输入公式“=IF(ISBLANK( B3),"",IF(C3="",NOW(),C3))”，如下图所示。 确认公式后，使用填充柄填充公式，如下图所示。 打开“Excel选项”对话框，在左侧的列表中选择“公式”选项，在右侧的“计算选项”选项区中选中“启用迭代计算”复选框，并将“最多迭代次数”设置为1，如下图所示。 在“收入”列中输入收入额，在“时间”列的相应单元格中将显示输入时间，如下图所示。 例9 巧用公式屏蔽出错信息 在填充公式时，有时候可能会因为公式中引用的单元格信息改变，导致其不满足公式的参数定义等原因，导致公式计算结果错误，例如，在“合格产品统计”工作表中，由于要统计合格产品的个数，所以使用了公式“=C4*E4”，在产品合格率未 检测 工程第三方检测合同工程防雷检测合同植筋拉拔检测方案传感器技术课后答案检测机构通用要求培训 出来时，使用该公式计算，就会显示错误信息，如下图所示。 要避免这种情况的发生，可以通过对公式进行完善，巧妙地解决单元格中显示错误的公式计算结果，其具体操作方法如下： 在单元格F4中输入公式“=IF(ISNUMBER( C4*E4),C4*E4,"无法计算")”，如下图所示。 得到计算结果后，拖动填充柄复制公式，单元格F7中将不会得到错误信息，效果如下图所示。 在单元格C9中输入公式“=COS(B9*PI()/ 180”或者“COS(RADIANS(B9))”，都将求得角度为30°的余弦，如下图所示。 6.5 统计函数实战技巧 例1 计算某个单元格值出现的次数 用COUNTIF函数可以计算单元格区域中满足给定条件的单元格的个数，下面以求“GHU系列产品种类”为例，介绍该函数的使用方法： 打开“商品价格表”，从中输入数据，然后在单元格F21中输入公式“=COUNTIF(F13: F19,"GHU")”，如下图所示。 按【Ctrl+Enter】组合键，得到公式计算结果，如下图所示。 例2 求满足多个条件的单元格个数 求满足多个条件的单元格个数，最常用的方法是使用COUNT和IF函数嵌套来实现。下面以计算“员工考核成绩表”中各科成绩都在75分以上的员工人数为例，介绍其具体方法。 新建工作表，从中输入数据，然后在单元格E12中输入公式“=COUNT(IF((C4:C10>75)*( D4:D10>75)*(E4:E10>75),B4:E10))”，如下图所示。 按【Ctrl+shift+Enter】组合键，得到公式计算结果，计算出满足条件的单元格有3个，如下图所示。 例3 巧用COUNTIFS函数求个数 COUNTIFS函数是Excel 2007中新增的函数，该函数也可用于计算某个单元格区域中满足多重条件的单元格数目。其具体操作方法如下： 打开“员工考核成绩表”，在单元格D5中输入公式“=COUNTIFS(C4:C10,">75",D4: D10,">75",E4:E10,">75")”，如下图所示。 按【Ctrl+Enter】组合键，将得到计算的结果，统计出满足条件的人数有1人，如下图所示。 例4 对考核成绩实现同分同名次排列 在用Excel统计成绩时，根据成绩高低进行排序，按序列自动填充出名次。当成绩相同时，填充出名次还是不同的，如下图所示。 此时，可以利用使用RANK排位函数实现同分同名次的操作，具体操作方法如下： 打开“员工考核成绩表”，添加“总分”和“排名”列，在单元格G4中输入函数“=RANK(F4,F$4: F$10,0)”，如下图所示。 按【Ctrl+Enter】组合键后，会得到该行总成绩的分数排名。使用拖动填充柄的方法，向下填充公式即可排名，效果如下图所示。 例5 用COUNTIF 函数也求名次排列 使用COUNTIF计数函数也可以对考核成绩实现同分同名次排列，具体操作方法如下： 在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函数求频率 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)”,如下图所示。 计算出公式结果，分别向右、向下拖动填充柄，填充公式，结果如下图所示。 将首数字修改为25，则数字序列将相应的自动修改为以25开头的数字序列，如下图所示。 例9 用SUBTOTAL函数统计编号 要使列表中的编号在进行数据筛选后，自动重新按顺序排列，可按如下方法操作： 在Excel 2007中，创建一张“产品订购单”，在这里“订单编号”列不使用输入数字序列的方法创建。用户可在单元格B4中输入函数“=SUBTOTAL(103,$C3:C4)-1”，如下图所示。 求得计算结果后，用填充柄自动填充公式，效果如下图所示。 选择数据表中任意单元格，如单元格B3，单击“数据”选项卡下“排序和筛选”组中的“筛选”按钮，效果如下图所示。 单击“产品代码”所在单元格右侧的下拉按钮，在弹出的下拉面板的列表中选中“AM001”复选框，如下图所示。 单击“确定”按钮，得到筛选结果，“订单编号”自动重新按顺序排列，如下图所示。 例10 用SUBTOTAL函数统计总数 使用SUBTOTAL函数还可以方便地统计总数，具体操作方法如下： 在“产品订购单”的单元格D16中输入函数“=SUBTOTAL(109,D4:D15)”,如下图所示。 通过公式计算，统计出所有订单的数量为62100，如下图所示。 对数据列表进行筛选后，筛选出产品AM001的订单数量，如下图所示。 例11 用DSUM函数进行多条件求和 用DSUM函数进行多条件求和的具体操作方法如下： 创建“员工考核成绩表”后，在单元格区域C12:D13中输入求和的条件，然后在单元格E13中输入函数“=DSUM(B3:G10,"工作能力",C12:D13)”，如下图所示。 按【Ctrl+Enter】组合键，得到求和的结果，即所有满足“所在部门”为“市场部”，“总分”大于200的员工的“工作能力”总得分情况，如下图所示。 例12 用POISSON函数计算概率 假设厂家每生产一批产品，出现次品的个数是7，要求产品不出现次品的概率，可按如下方法操作： 新建工作表，因为求产品不出现次品的概率，所以将期望出现的次品的个数就是0。在工作表的单元格C4中输入函数“=POISSON(C2,C3,FALSE)”，如下图所示。 按【Ctrl+Enter】组合键，计算出不出现次品的概率，结果如下图所示。 例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】组合键，得到公式的计算结果，如下图所示。 例16 用LINEST函数求斜率和截距 LINEST函数使用最小二乘法对已知数据进行最佳直线拟合，然后返回描述此直线的数组。利用该函数也可以计算线性方程的斜率和Y轴的截距，其操作方法如下： 新建工作表，从中输入相关的数据，并选择单元格区域E4:F4然后按【F2】键，将光标定位到所选单元格区域中，输入函数“=LINEST(C3:C12,B3:B12,,FALSE)”如下图所示。 按【Ctrl+Shift+Enter】组合键，得到数组公式的计算结果，如下图所示。 例17 用PEARSON函数求线性相关度 PEARSON函数用于返回 Pearson（皮尔生）乘积矩相关系数r，其范围在-1.0~1.0之间（包括-1.0和1.0）的无量纲指数，反映了两个数据集合之间的线性相关程度。该函数的使用方法如下： 新建工作表，从中输入数据，并在单元格C15中输入函数“=PEARSON(B3:B13,C3:C13)”，如下图所示。 按【Ctrl+Enter】组合键，即可得到得出的 Pearson乘积矩相关系数，如下图所示。 例18 用RSQ函数求R的平方 RSQ函数用于返回根据 known_y's 和 known_x's中数据点计算得出的Pearson乘积矩相关系数的平方，其值也可以理解为y 方差与x 方差的比例。该函数的使用方法如下： 新建工作表，从中输入数据，并在单元格C15中输入函数“=RSQ(C3:C13,B3:B13)”，如下图所示。 按【Ctrl+Enter】组合键，即可得到得出的 Pearson 乘积矩相关系数的平方值，如下图所示。 例19 快速用函数预测未来销售额 如果销售额和销售年份之间存在线性关系，使用LINEST统计函数可以预测未来年份的销售额，其具体操作方法如下： 新建工作表，将线性回归 分析 定性数据统计分析pdf销售业绩分析模板建筑结构震害分析销售进度分析表京东商城竞争战略分析 数据输入到其中，如下图所示。 假设趋势预测方程为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】组合键，得到数组公式的计算结果，如下图所示。 例21 快速为员工年终评估打分 在大多数企业中，年终要对员工进行考核评估，评估的最后得分可按如下的方法计算得出。 在“年终评估分值计算”工作表中，单击单元格G3，如下图所示。 单击编辑栏左侧的“插入函数”按钮，弹出“插入函数”对话框，从中选择TRIMMEAN函数，如下图所示。 单击“确定”按钮，弹出“函数参数”对话框，从中设置参数，如下图所示。 单击“确定”按钮，计算得出最终得分，然后拖动填充柄填充公式，如下图所示。 6.6 嵌套函数实战技巧 例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，如下图所示。 拖动填充柄，向下填充其他公式，计算出其他条码的校验位数字，如下图所示。 例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】组合键，得到数组公式的计算结果，如下图所示。 例9 求区域中最大几个数的平均值 要求单元格区域中最大几个数的平均值，可按如下的方法操作： 在单元格D12中输入公式“=AVERAGE( LARGE(C4:E10,ROW(INDIRECT("1：3"))))”，如下图所示。 按【Ctrl+Shift+Enter】组合键，得到数组公式的计算结果，如下图所示。 例10 巧妙用空格对齐姓名 在Excel中制作电子表格时，输入姓名是经常遇到的，而姓名有的是三个字，有的是两个字，让人看起来有点乱，这时我们可在两个字的姓名中间添加一个空格，来使输入的姓名更加地整齐，下面介绍一种批量添加空格的方法。 打开“工资变动表”工作表，在单元格H6中输入公式“=IF(LEN(C6)=2,MID(C6,1,1)&" "&MID(C6,2,1),C6)”，如下图所示。 得到提取的结果后，再使用填充柄复制公式，最终结果如下图所示。 例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))”，如下图所示。 按【Ctrl+Enter】组合键，得到公式计算结果，然后通过拖动填充柄，填充公式，结果如下图所示。 通过将单元格D4和D5中的价格进行更改，来验证公式的正确性，如下图所示。 例14 根据农历年份判断生肖 每一年对应一个生肖，在Excel中根据这一规律，使用相应的函数可以根据农历年份判断出该年的生肖。其具体操作步骤如下： 新建工作表，从中输入数据，并在单元格C3中输入公式“=MID("鼠牛虎兔龙蛇马羊猴鸡狗猪",MOD(B3-4,12)+1,1)”，如下图所示。 按【Ctrl+Enter】组合键，得到公式的计算结果，然后向下拖动填充柄，填充公式，结果如下图所示。 例15 用函数判断数字的奇偶性 使用函数判断数字奇偶的方法非常简单，具体操作方法如下： 新建工作表，从中输入数据，并在单元格C4中输入公式“IF(ISODD(B4),"奇数","偶数")”,如下图所示。 按【Ctrl+Enter】组合键，得到公式的计算结果，然后将鼠标指针移动到该单元格的右下角，拖动填充柄，填充公式，结果如下图所示。 利用其他的嵌套函数也可以对数字的奇偶性进行判断，如下图所示。 例16 快速计算文本格式数据 有时侯，用户可能把要使用公式或函数进行计算的数据错误的设置为“文本”格式，这样就不能计算出结果。如果用户不修改数据的格式，可以通过以下的方法来计算。 打开“产品销售统计表”工作表，在单元格G4中输入嵌套函数“=SUM(VALUE(C4: F4))”，如下图所示。 按【Ctrl+Shift+Enter】组合键，即可得到计算结果，如下图所示。 例17 数字和文本的分离 有的单元格中的数据是有数字和文本共同组成的，要分离数字和文本，可按如下的方法操作： 打开“商品价格表”工作表，在单元格E13中将商品的数字编号通过公式从单元格D13中提取出来，输入公式“=RIGHT(D13,SUM(LEN( D13)-LEN(SUBSTITUTE(D13，{0,1,2,3,4,5,6, 7,8,9},))))”，如下图所示。 按【Ctrl+Enter】组合键，得到计算结果，通过拖曳控制柄的方法，复制公式，效果如下图所示。 在单元格F13中输入公式“=SUBTITUTE( D13,E13,)”，该公式的含义是显示将单元格D13中的数字内容替换为空后的字符，如下图所示。 得到计算结果后，通过拖动控制柄复制公式，效果如下图所示。 例18 使用MID函数分填金额 在财务工作中，通常要把一定金额分填到不同的列中，下面介绍使用MID函数分填金额的具体操作方法。 在不同的单元格中输入不同的文本，然后在单元格C3中输入公式“=MID(PEPT(“ ”,11- LEN(ROUND($B3,2)*