还剩12页未读,继续阅读
本资源只提供10页预览,全部文档请下载后查看!喜欢就下载吧,查找使用更方便
文本内容:
1.2条件格式选定需要的全部数据单元格格式-条件格式(例使表格中大于某个值的数据变为红色、加粗)
1.2查找替换选中需要替换(例M-男、F-女)全部单元格编辑-查找(替换)-替换
1.2条件函数例题根据评价标准,显示出各部门业绩及评价结果点击函数按钮,找到IF函数,选定C3,(从最大的数值开始!!!)在函数对话框第一条里输入B3=400000,第二条里输入”很好”英文状态双引号光标放在第三条输入框内,单击左上角IF,输入第二层函数参数在新的函数对话框第一条里输入B3=300000,第二条里输入”好”光标放在第三条输入框内,单击左上角IF,输入第三层函数参数在新的函数对话框第一条里输入B3=200000,第二条里输入”一般”光标放在第三条输入框内,单击左上角IF,输入第四层函数参数在新的函数对话框第一条里输入B3200000,第二条里输入”差”光标放在第三条输入框内,最后单击确定,其他部门的拖动复制即可
2.1时间价值函数
(1)分别计算每年偿还的本金和利息(PPMT定期偿还、固定利率下,给定期次内某项投资回报或偿还贷款的本金部分)(IPMT定期偿还、固定利率下,给定期次内某项投资回报或偿还贷款的利息部分)例某人购买商品房,现贷款180000元,复利年利率6%如果要在未来5年内等额偿还贷款本金及利息(每年年末支付),每年需要偿还多少钱?每年的还款中本金和利息各是多少?单击目标单元格-单击函数按钮-财务函数(PPMT)本金(PPMT)=PPMT(各期利率,期次,总贷款期,贷款额)单击目标单元格-单击财务按钮-财务函数(IPMT)利息(IPMT)=IPMT(各期利率,期次,总贷款期,贷款额)
(2)年金函数(PMT)贷款金额200000,贷款利率5%,年限5年利用年金函数计算每年应偿还多少钱本息偿还额(PMT)=PMT(各期利率,总贷款期,贷款额)
2.2投资决策函数例投资评价期间A项目B项目C项目0-10000-20000-300001100001500020000280001000015000350001000010000净现值 内含报酬率 要求计算各项目的净现值(假定利率为10%)和内含报酬率(请将公式定义在上表中保留2位小数)单击目标单元格-单击函数按钮-财务函数(净现值NPV)净现值(NPV)=NPV(贴现率,各期收入)+前期支出单击目标单元格-单击财务按钮-财务函数(内含报酬率IRR)内含报酬率(IRR)=IRR(各期支出和收入)
2.3折旧函数
(1)固定余额递减法(DB)计算各年折旧、累计折旧单击目标单元格-单击函数按钮-财务函数DB各年折旧(DB)=DB(原值,预计残值,预计使用年限,期次,第一年月份数默认为12)当期累计折旧=本期折旧+上期累计折旧
(2)年数总和法(SYD)计算各年折旧、累计折旧单击目标单元格-单击函数按钮-财务函数(SYD)各年折旧(SYD)=SYD(原值,估计残值,预计使用年限,期次)当期累计折旧=本期折旧+上期累计折旧
(3)直线法(SLN)计算各年折旧、累计折旧单击目标单元格-单击函数按钮-财务函数(SLN),每年折旧金额相等各年折旧(SLN)=SLN(原值,估计残值,预计使用年限)当期累计折旧=本期折旧+上期累计折旧
(4)双倍余额递减法(DDB)各年折旧(DDB)=DDB(原值,估计残值,预计使用年限,余额递减速率默认为2)
(5)特定或部分时期的折旧额(VDB)折旧(VDB)=VDB(原值,残值,使用期限,折算开始期次,折算结束期次,余额抵减速率,逻辑值)逻辑值是指折旧额超过用余额递减法计算的水品是,是否转换为直线折旧法
3.1排序选中目标单元格区域-数据-排序
3.2高级筛选在列表下方建立筛选条件区域,列出条件(注意)数据-筛选-高级筛选,方式-将筛选结果复制到其他位置列表区域-选择给出的表格内容的区域条件区域-选择自己建立的区域复制到-选择筛选结果要复制到的区域
3.3分类汇总选中目标单元格区域-数据-分类汇总
3.4数据透视表选定全部数据(包括表头文字)数据-数据透视表和数据透视图-向导1待分析数据源类型(MSExcel),创建的报表类型(数据透视表),下一步向导2键入或选定数据源区域,下一步向导3透视表显示位置新建工作表or现有工作表(在当前表选定输出位置)完成将字段拖动到透视表上,注意行字段位置竖着的,列字段位置横着的
4.1单变量求解(利润)例题利用单变量求解计算,当销量为多少时,利润能达到15000先在目标单元格(B6)定义公式=B3*B5-B4*B5工具-单变量求解目标单元格B6目标值15000可变单元格B5(所要求的销量)
4.1单变量求解(时间)例题计算当购买一辆汽车时,如果月还款
1539.29元,则贷款额度为多少?(贷款额度用负整数表示)先在目标单元格(C8)定义公式年金公式PMT各期利率C7/12(月利率)总贷款期C6贷款额C5工具-单变量求解目标单元格C8目标值
1539.29可变单元格C5(所要求的贷款额度)
4.2模拟运算表(单变量)例题先在指定单元格(D4)定义计算公式(年金函数PMT)选定包括定义单元格在内的数据列区域(D4E14)数据-模拟运算表引用行单元格选定行所要引用内容的单元格(B4年限)引用列单元格选定列所要引用内容的单元格(B5利率)以上在期限不变(10年),利率变化条件下,求月还款金额,称为单变量模拟运算表
4.2模拟运算表(双变量)例题先在指定单元格(A4)定义计算公式(年金函数PMT)选定包括定义单元格在内的表格区域(A4F9)数据-模拟运算表引用行单元格选定行所要引用内容的单元格(B2利率)引用列单元格选定列所要引用内容的单元格(B3年限)以上在年限和利率都在变化条件下,求年还款金额,称为双变量模拟运算表
4.3规划求解例已知某公司甲、乙、丙三种产品的单位利润和三个车间的工时限额,求生产甲、乙、丙各多少可使企业利润最大?
1.输入计算公式1)计算利润小计,B8=B6*B7,C
8、D8同理2)计算实际工时,E2=$B$7*B2+$C$7*C2+$D$7*D2,E
3、E4同理3)计算总利润,B9=B8+C8+D
82.进行规划求解1)工具-规划求解,目标单元格B9(总利润最大值),选中最大值按钮可变单元格B7:D7甲、乙、丙各自最佳产量2)添加,约束条件“实际工时”小于等于“最高工时”3)求解,选中“保存规划求解结果”选项,确定
4.4方案管理器量本利分析项目数值单价100变动成本30固定成本3000保本点销量 盈亏平衡点 工具-方案-添加选择可变单元格,确定,输入变量值全部完成后点击摘要,选择输出地址-确定指定名称插入-名称-指定-选最左列-确定斜线表头格式-单元格-边框数字-文本先把文字全输入,然后Alt+Enter强制换行,一行变两行对齐-水平居中、垂直居中再用空格调整两行互换选中一行鼠标放在该行上沿,光标呈方向十字按住Shift拖动鼠标,当左边数字呈X:X时放手输出结果用负数表示右键设置单元格-数值型-选带负号的示例设置(没有的)自动填充项工具-选项-自定义序列-新序列等差数列在两个单元格输入两个数,同时选中两个单元格,填充柄下拉等比数列编辑-填充-序列,选等比数列,输入步长数据求和SUMIF函数。