EXCEL在管理中的高级应用VIP

1,斯顿系列教程,SBCShanghai,EXCEL在管理上的应用,2,课程结构,管理报告,EXCEL,POWERPOINT,文本,概念性图表,量化性图表,3,基础技巧内容把查找、统计、分析、预测的工作留给计算机的方法分析图形应用,技巧内容,4,工资及个人所得税计算核算报表财务分析应用,财务模型内容,5,7.数据透视表8.宏与自定义函数,6.图表,5.打印,4.公式及函数,3.编辑及格式化,1.菜单及工具按钮,2.使用工作簿/工作表,基本技巧内容,6,7,1.菜单及工具按钮,II.自定义工具栏和菜单在现有的工具栏内添加或删除按钮或命令创建自己的工具栏和菜单(打开自定义开关:Customize),8,7.数据透视表8.宏与自定义函数,6.图表,5.打印,4.公式及函数,3.编辑及格式化,1.菜单及工具按钮,2.使用工作簿/工作表,基本技巧内容,9,I.工作簿1.工作簿窗口:多个工作簿窗口和一个工作簿多个窗口多个工作簿窗口的排列:平铺,水平排列,垂直排列和层叠一个工作簿多个窗口:新建窗口,保存工作区2.隐藏工作簿(为什么打开了一个工作簿却什么也没看到?)3.保护工作簿(工具保护保护工作簿),2.使用工作簿/工作表,10,II.工作表(1/2)1.开始工作之前的一些基本考虑:尽量采用长的而不是宽的工作表以方便用PageUp,PageDown快速流览.不必把所有的信息信息放在一张工作表内.为工作表添加必要的抬头和注释为工作表命名简短而有意义的名称删除多余的空白工作表定义好所有的打印设置以方便使用者需要时直接打印为新数据预留一些空白行或列.这样添加数据时不用再修改公式.,2.使用工作簿/工作表,11,II.工作表(2/2)2.插入和删除工作表3.命名和重命名工作表4.移动和复制工作表5.工作表的显示:拆分和冻结冻结工作表缩放工作表(显示比例,对工作簿的全屏显示)显示方式(普通,分页预览)6.保护工作表(锁定单元格,隐藏单元格公式)7.隐藏工作表(格式工作表隐藏.注意和隐藏工作簿的区别:窗口隐藏),2.使用工作簿/工作表,12,7.数据透视表8.宏与自定义函数,6.图表,5.打印,4.公式及函数,3.编辑及格式化,1.菜单及工具按钮,2.使用工作簿/工作表,基本技巧内容,13,I.编辑复制,剪切和粘贴(灵活运用右键和自动填充功能)插入和删除撤消及重复以前的操作查找,替换和定位,3.编辑及格式化,14,II.格式化格式化单元格(字体,对齐,边框,颜色,图案,大小,保护和隐藏)条件格式分级显示注释审核6.有效性,3.编辑及格式化,15,7.数据透视表8.宏与自定义函数,6.图表,5.打印,4.公式及函数,3.编辑及格式化,1.菜单及工具按钮,2.使用工作簿/工作表,基本技巧内容,16,I.公式所有的公式都是以=开头公式包含的运算符:+-*/()相对引用,绝对引用和混合引用(巧用F4键)如何不用公式而迅速获得常用计算结果(状态栏)什么是三维公式(Sheet2:Sheet16!A3)名称的定义(Ctrl+F3),粘贴(F3),以及常量和公式名称的使用公式的6种错误值:#DIV/0,#NAME?,#VALUE,#REF!,#N/A,#NUM!,4.公式及函数,17,II.函数一个函数就是一个预定义的公式所有的函数都包括一个函数名和一系列的参数财务工作中常用的函数:数学函数:SUM,SUMIF,SUMPRODUCT,COMBIN,ROUND,ROUNDDOWN,ROUNDUP,FLOOR,CEILING,INT,AVERAGE文本函数:MID,RIGHT,LEFT逻辑函数:IF,AND,OR,NOT查找函数:VLOOKUP,HLOOKUP财务分析函数:PV,NPV,FV,PMT,IRR,SLN,DDB,4.公式及函数,18,COMBIN函数计算从给定数目的对象集合中提取若干对象的组合数。利用函数COMBIN可以确定一组对象所有可能的组合数。语法COMBIN(number,number_chosen)Number为对象的总数量。Numberchosen为每一组合中对象的数量。公式说明（结果）=COMBIN(8,2)从八个候选人中提取两个候选人的组合数(28),4.公式及函数,19,ROUND函数语法ROUND(number,num_digits)Number需要进行四舍五入的数字。Num_digits指定的位数，按此位数进行四舍五入。说明如果num_digits大于0，则四舍五入到指定的小数位。如果num_digits等于0，则四舍五入到最接近的整数。如果num_digits小于0，则在小数点左侧进行四舍五入。=ROUND(2.15,1)将2.15四舍五入到一个小数位(2.2)=ROUND(2.149,1)将2.149四舍五入到一个小数位(2.1)=ROUND(-1.475,2)将-1.475四舍五入到两小数位(-1.48)=ROUND(21.5,-1)将21.5四舍五入到小数点左侧一位(20),4.公式及函数,20,ROUNDDOWN函数ROUNDDOWN(number,num_digits)Number为需要向下舍入的任意实数。Num_digits四舍五入后的数字的位数。说明函数ROUNDDOWN和函数ROUND功能相似，不同之处在于函数ROUNDDOWN总是向下舍入数字。如果num_digits大于0，则向下舍入到指定的小数位。如果num_digits等于0，则向下舍入到最接近的整数。如果num_digits小于0，则在小数点左侧向下进行舍入。=ROUNDDOWN(3.2,0)将3.2向下舍入，小数位为0(3)=ROUNDDOWN(76.9,0)将76.9向下舍入，小数位为0(76)=ROUNDDOWN(3.14159,3)将3.14159向下舍入，保留三位小数(3.141)=ROUNDDOWN(-3.14159,1)将-3.14159向下舍入，保留一位小数(-3.1)=ROUNDDOWN(31415.92654,-2)将31415.92654向下舍入到小数点左侧两位(31400),4.公式及函数,21,ROUNDUP函数ROUNDUP(number,num_digits)Number为需要向上舍入的任意实数。Num_digits四舍五入后的数字的位数。说明函数ROUNDUP和函数ROUND功能相似，不同之处在于函数ROUNDUP总是向上舍入数字。如果num_digits大于0，则向上舍入到指定的小数位。如果num_digits等于0，则向上舍入到最接近的整数。如果num_digits小于0，则在小数点左侧向上进行舍入。=ROUNDUP(3.2,0)将3.2向上舍入，小数位为0(4)=ROUNDUP(76.9,0)将76.9向上舍入，小数位为0(77)=ROUNDUP(3.14159,3)将3.14159向上舍入，保留三位小数(3.142)=ROUNDUP(-3.14159,1)将-3.14159向上舍入，保留一位小数(-3.2)=ROUNDUP(31415.92654,-2)将31415.92654向上舍入到小数点左侧两位(31500),4.公式及函数,22,FLOOR函数FLOOR(number,significance)Number所要四舍五入的数值。Significance基数。说明如果任一参数为非数值参数，则FLOOR将返回错误值#VALUE!。如果number和significance符号相反，则函数FLOOR将返回错误值#NUM!。不论number的正负号如何，舍入时参数的绝对值都将减小。如果number恰好是significance的倍数，则无需进行任何舍入处理。示例=FLOOR(2.5,1)将2.5沿绝对值减小的方向向下舍入，使其等于最接近的1的倍数(2)=FLOOR(-2.5,-2)将-2.5沿绝对值减小的方向向下舍入，使其等于最接近的-2的倍数(-2)=FLOOR(-2.5,2)返回错误值，因为-2.5和2的符号不同(#NUM!)=FLOOR(1.5,0.1)将1.5沿绝对值减小的方向向下舍入，使其等于最接近的0.1的倍数(1.5)=FLOOR(0.234,0.01)将0.234沿绝对值减小的方向向下舍入，使其等于最接近的0.01的倍数(0.23),4.公式及函数,23,CEILING函数CEILING(number,significance)Number要四舍五入的数值。Significance是需要四舍五入的乘数。说明如果参数为非数值型，CEILING返回错误值#VALUE!。无论数字符号如何，都按远离0的方向向上舍入。如果数字已经为Significance的倍数，则不进行舍入。如果Number和Significance符号不同，CEILING返回错误值#NUM!。示例=CEILING(2.5,1)将2.5向上舍入到最接近的1的倍数(3)=CEILING(-2.5,-2)将-2.5向上舍入到最接近的-2的倍数(-4)=CEILING(-2.5,2)返回错误值，因为-2.5和2的符号不同(#NUM!)=CEILING(1.5,0.1)将1.5向上舍入到最接近的0.1的倍数(1.5)=CEILING(0.234,0.01)将0.234向上舍入到最接近的0.01的倍数(0.24),4.公式及函数,24,PV函数返回投资的现值。现值为一系列未来付款的当前值的累积和。例如，借入方的借入款即为贷出方贷款的现值。语法PV(rate,nper,pmt,fv,type)Rate为各期利率。例如，如果按10%的年利率借入一笔贷款来购买汽车，并按月偿还贷款，则月利率为10%/12（即0.83%）。可以在公式中输入10%/12、0.83%或0.0083作为rate的值。Nper为总投资（或贷款）期，即该项投资（或贷款）的付款期总数。例如，对于一笔4年期按月偿还的汽车贷款，共有4*12（即48）个偿款期数。可以在公式中输入48作为nper的值。Pmt为各期所应支付的金额，其数值在整个年金期间保持不变。通常pmt包括本金和利息，但不包括其他费用及税款。例如，$10,000的年利率为12%的四年期汽车贷款的月偿还额为$263.33。可以在公式中输入-263.33作为pmt的值。如果忽略pmt，则必须包含fv参数。Fv为未来值，或在最后一次支付后希望得到的现金余额，如果省略fv，则假设其值为零（一笔贷款的未来值即为零）。例如，如果需要在18年后支付$50,000，则$50,000就是未来值。可以根据保守估计的利率来决定每月的存款额。如果忽略fv，则必须包含pmt参数。Type数字0或1，用以指定各期的付款时间是在期初还是期末。Type值支付时间0或省略期末1期初,4.公式及函数,25,NPV函数通过使用贴现率以及一系列未来支出（负值）和收入（正值），返回一项投资的净现值。语法NPV(rate,value1,value2,.)Rate为某一期间的贴现率，是一固定值。Value1,value2,.为1到29个参数，代表支出及收入。Value1,value2,.在时间上必须具有相等间隔，并且都发生在期末。NPV使用Value1,Value2,的顺序来解释现金流的顺序。所以务必保证支出和收入的数额按正确的顺序输入。如果参数为数值、空白单元格、逻辑值或数字的文本表达式，则都会计算在内；如果参数是错误值或不能转化为数值的文本，则被忽略。如果参数是一个数组或引用，则只计算其中的数字。数组或引用中的空白单元格、逻辑值、文字及错误值将被忽略。,4.公式及函数,26,FV函数基于固定利率及等额分期付款方式，返回某项投资的未来值。语法：FV(rate,nper,pmt,pv,type)Rate为各期利率。Nper为总投资期，即该项投资的付款期总数。Pmt为各期所应支付的金额，其数值在整个年金期间保持不变。通常pmt包括本金和利息，但不包括其他费用及税款。如果忽略pmt，则必须包括pv参数。Pv为现值，即从该项投资开始计算时已经入帐的款项，或一系列未来付款的当前值的累积和，也称为本金。如果省略PV，则假设其值为零，并且必须包括pmt参数。Type数字0或1，用以指定各期的付款时间是在期初还是期末。如果省略type，则假设其值为零。Type值支付时间：0期末1期初说明应确认所指定的rate和nper单位的一致性。例如，同样是四年期年利率为12%的贷款，如果按月支付，rate应为12%/12，nper应为4*12；如果按年支付，rate应为12%，nper为4。在所有参数中，支出的款项，如银行存款，表示为负数；收入的款项，如股息收入，表示为正数。,4.公式及函数,27,PMT函数基于固定利率及等额分期付款方式，返回贷款的每期付款额。语法：PMT(rate,nper,pv,fv,type)Rate贷款利率。Nper该项贷款的付款总数。Pv现值，或一系列未来付款的当前值的累积和，也称为本金。Fv为未来值，或在最后一次付款后希望得到的现金余额，如果省略fv，则假设其值为零，也就是一笔贷款的未来值为零。Type数字0或1，用以指定各期的付款时间是在期初还是期末。Type值支付时间：0或省略期末，1期初说明PMT返回的支付款项包括本金和利息，但不包括税款、保留支付或某些与贷款有关的费用。应确认所指定的rate和nper单位的一致性。例如，同样是四年期年利率为12%的贷款，如果按月支付，rate应为12%/12，nper应为4*12；如果按年支付，rate应为12%，nper为4。,4.公式及函数,28,IRR函数返回由数值代表的一组现金流的内部收益率。这些现金流不必为均衡的，但作为年金，它们必须按固定的间隔产生，如按月或按年。内部收益率为投资的回收利率，其中包含定期支付（负值）和定期收入（正值）。语法：IRR(values,guess)Values为数组或单元格的引用，包含用来计算返回的内部收益率的数字。Values必须包含至少一个正值和一个负值，以计算返回的内部收益率。函数IRR根据数值的顺序来解释现金流的顺序。故应确定按需要的顺序输入了支付和收入的数值。如果数组或引用包含文本、逻辑值或空白单元格，这些数值将被忽略。Guess为对函数IRR计算结果的估计值。在大多数情况下，并不需要为函数IRR的计算提供guess值。如果省略guess，假设它为0.1(10%)。如果函数IRR返回错误值#NUM!，或结果没有靠近期望值，可用另一个guess值再试一次。说明函数IRR与函数NPV（净现值函数）的关系十分密切。函数IRR计算出的收益率即净现值为0时的利率。下面的公式显示了函数NPV和函数IRR的相互关系：NPV(IRR(B1:B6),B1:B6)等于3.60E-08（在函数IRR计算的精度要求之中，数值3.60E-08可以当作0的有效值）。,4.公式及函数,29,SLN函数返回某项资产在一个期间中的线性折旧值。语法SLN(cost,salvage,life)Cost为资产原值。Salvage为资产在折旧期末的价值（也称为资产残值）。Life为折旧期限（有时也称作资产的使用寿命）。,4.公式及函数,30,DDB函数使用双倍余额递减法或其他指定方法，计算一笔资产在给定期间内的折旧值。语法：DDB(cost,salvage,life,period,factor)Cost为资产原值。Salvage为资产在折旧期末的价值（也称为资产残值）。Life为折旧期限（有时也称作资产的使用寿命）。Period为需要计算折旧值的期间。Period必须使用与life相同的单位。Factor为余额递减速率。如果factor被省略，则假设为2（双倍余额递减法）。要点这五个参数都必须为正数。说明双倍余额递减法以加速的比率计算折旧。折旧在第一阶段是最高的，在后继阶段中会减少。DDB使用下面的公式计算一个阶段的折旧值：(资产原值-资产残值)-前面阶段的折旧总值)*(余额递减速率/生命周期)如果不想使用双倍余额递减法，更改余额递减速率。当折旧大于余额递减计算值时，如果希望转换到直线余额递减法，请使用VDB函数。,4.公式及函数,31,7.数据透视表8.宏与自定义函数,6.图表,5.打印,4.公式及函数,3.编辑及格式化,1.菜单及工具按钮,2.使用工作簿/工作表,基本技巧内容,32,设置页面外观调整分页符,5.打印,使用打印预览指定打印内容,33,7.数据透视表8.宏与自定义函数,6.图表,5.打印,4.公式及函数,3.编辑及格式化,1.菜单及工具按钮,2.使用工作簿/工作表,基本技巧内容,34,6.图表,创建一个新图表修改及美化图表的外观使用图表的一些原则,35,创建一个新图表创建图表快捷键:F11设计图表三部曲：制作图表标准四步曲:,6.图表,3.设置图表选项,2.指定数据源,1.选择图表类型,4.指定图表放置位置,确定你所要表达的主题（将数据转化为主题）,确定对比关系（从主题到对比关系）,选择图表形式（从对比关系到图表）,36,修改及美化图表的外观图表标题:命名,设置图案,字体和对齐方式坐标轴:显示或隐藏,设置图案,刻度,字体,数字格式和对齐方式网格线:显示或隐藏,设置图案,刻度图例:显示或隐藏,设置图案,字体和位置数字标志:显示或隐藏,设置图案,字体,数字格式和对齐方式数据表:显示或隐藏,设置图案,字体背景区域:设置图案数据系列:设置图案,误差线Y,次序,重叠比例,分类间距和系列线,6.图表,37,使用图表的一些原则选用适当的图表类型不要企图在同一张图表内同时表示超过两组的数据不必在图表内显示太多的数字选择恰当的坐标刻度充分利用图表的彩色视觉功能在图表内添加适当的对象以突出重点,6.图表,38,图表类型汇总表,对比关系类型,基本图表样式,选择图表形式（从对比关系到图表）,39,7.数据透视表,6.图表,5.打印,4.公式及函数,3.编辑及格式化,1.菜单及工具按钮,2.使用工作簿/工作表,基本技巧内容,8.宏与自定义函数,40,7.数据透视表,41,7.数据透视表,42,7.数据透视表,43,7.数据透视表,44,7.数据透视表,45,7.数据透视表,6.图表,5.打印,4.公式及函数,3.编辑及格式化,1.菜单及工具按钮,2.使用工作簿/工作表,基本技巧内容,8.宏与自定义函数,46,8.宏与自定义