Excel财务应用2015 讲义.pptVIP

袁志刚 2015-9 湖北武汉 7.2 贷款还款计算 等额分期还款方式 每期偿还金额：=pmt(利率，期数，贷款金额） 每期偿还的利息：=ipmt(利率，第几期，期数，贷款金额） 每期偿还的本金：=ppmt(利率，第几期，期数，贷款金额） 计算利率：=rate(期数，每期还款额，贷款额） 名义利率与实际利率 将名义利率转化为实际利率：=effect(名义利率，每年复利期数） 将实际利率转化为名义利率：=nominal(实际利率，一年内计息次数） 8.1 银行存款余额调节表 思路 利用vlookup函数在银行对账单和银行存款日记账之间双向查询，出现#N/A即未达账项，然后利用sumproduct函数将未达账项分类求合计。 公式 银行已收/企业未收款：=SUMPRODUCT((ISERROR($D$3:$D$19)=TRUE)*($A$3:$A$190)*($A$3:$A$19)) 其中，iserror()=true意味着企业未达，a3:a190意味着是企业未收款 对账单上的其他项目调节公式以此类推。 8.2 应收账款管理 统计收款金额合计 =sumifs(合同金额列，合同编号列，要统计的合同编号） 分客户的账龄分析 创建透视表，账龄字段放进行区域，金额放进数值区域 光标在行标签区域，右键-创建组，将参数设为1,90,30 将账龄字段拖进列标签 将客户字段拖进行标签 此时可以看到每家客户的应收账款分布汇总情况 还可双击希望查看明细的行，对明细数据进行查看 8.3 应收款账龄分析 1【插入】选项卡-选择【数据透视表】 2 在“字段列表”工作区中，将过期天数字段拖入行标签，将金额两次拖入数值区域 3 在透视表中的行标签区域内点击鼠标右键，在右键菜单中选择“组合”，将起始于，终止于，步长分别改为：1,120,30 4 光标放在“金额2”的列上右键，选择【值字段设置】，切换为“值显示方式”，并在列表中选择“占同列数据总和的百分比” 8.4 应付账款提醒 思路 利用条件格式进行应付款提醒 步骤 计算到期天数：=付款日期-today() 选中到期天数列, 开始-条件格式-新建规则-只为包含以下内容的单元格设置格式 设置单元格值0, 为单元格设置红色填充色 设置单元格值10, 为单元格设置蓝色填充色 9.1 报销单制作 为分公司，部门，币种，支付类型等信息设置有效性 在参数表上创建部门列表 数据-数据有效性 设置有效性条件为“序列”，然后在下方的来源框选择参数表上的部门列表 提示：excel2010之前的版本不能跨表选择有效性序列，需要为首先为序列定义名称 设置填写提示信息 数据-数据有效性，切换到“输入信息”标签，填写标题和输入信息 保护 选中需要填写的单元格，右键-设置单元格格式-保护，取消勾选“锁定” 审阅-保护工作表-输入密码 9.2 名称定义 名称可以代表一个单元格或者一个单元格区域，或者是常量，公式。 名称的定义：选中需要命名的单元格或区域，在界面左上角名称框中输入名称后回车； 需要注意的是：名称框只能用于定义单元格和单元格区域的名称，公式的名称需要在【公式】【名称管理器】中进行定义。 名称的引用：需要引用某单元格时输入为该单元格定义的名称：＝名称 步骤1：选中需要命名的某个单元格或单元格区域。 步骤2：在左上角名称框输入命名后回车。 删除名称：选择【公式】菜单【名称管理器】；选中需要删除的名称，点击“删除”按钮。 9.3 报表汇总 快速汇总表 =sum(起始表：结束表！需汇总的单元格） 要求：各个表结构必须一致 利用透视表多重合并计算数据区域汇总 调出多重合并计算数据区域功能快捷键：文件-选项-快速访问工具栏，选择所有命令，在列表中找到“数据透视表和数据透视图向导”，选到右侧 点击快速访问工具栏上的透视表向导按钮，选择“多重合并计算数据区域”选项，选择创建单页字段，逐个选择需合并的表，完成即可。 优点：数据可多可少，可在一个视图上选择查看各个表的数据 9.4 数字大写转化 转化效果 公式 中文小写公式：=numberstring(b3,1) 中文大写公式：=numberstring(b3,2) 逐字转化： =numberstring(b3,3) 另外的写法 中文小写公式：=text(b3,”[dbnum1]”) 中文大写公式：=text(b3,”[dbnum2]”) 逐字转化： =text(b3,”[dbnum1]0”) 9.5 应用技巧 格式编号 要求：在编号前加N0.字符，并使得编号为8位，不足前面补零 =NO.TEXT(A2 读取多个其他工作表上的数据 公式：=INDIRECT(D4!b2) 其中D4是工作表名称所在单元格，b2是要读取的数据所在