《Word财务人员进阶教程完整版.docx》由会员分享，可在线阅读，更多相关《Word财务人员进阶教程完整版.docx（49页珍藏版）》请在三一办公上搜索。
1、只记得函数名称，记不清函数的参数时：可以在编辑栏中输入一个等号其后接函数名，再按Ctrl+A键，则自动进入“函数参数”。返回到函数对话框：光标定位于编辑栏，再点击钮。多个工作表的单元格合并计算：=Sheet1!D4+Sheet2!D4+Sheet3!D4 或=SUM(Sheet1：Sheet3!D4)Excel中自动筛选后计算个数时：=SUBTOTAL(3,C3:C11)Excel中开方运算：如8开3次方，输入“8 (1/3)”Excel中查找工作表中的链接：按Ctrl+或“编辑菜单链接”Excel中让空单元格自动填为0：选中需更改的区域后点查找命令，直接输替换为0即可。Excel中设置加权平
2、均：设置公式解决（其实就是一个除法算式），分母是各个量值之和，分子是相应的各个数量之和，它的结果就是这些量值的加权平均值。为多个工作表设置相同的页眉和页脚或一次打印：在某工作表名称处右击点“选择全部工作表”，这时你的所有操作都是针对全部工作表了。Excel中无法输入小数点，显示的是逗号：“控制面板区域和语言选项中文（中国）自定义”中将小数点改为“.”快速选取特定区域：例如选取A2:A1000，按F5键出现“定位”窗口中，在“引用”栏内输入要选取的区域A2:A1000。快速返回选中的区域：Ctrl+BacksPae（退格）Excel中隐藏列：Ctrl+，若取消隐藏可选中该隐藏列的前后两列Ctrl
3、+ Shift+，右击选取消或双击选中的任一列宽或改变任一列宽；或当鼠标变为双竖线时拖动。把Word里的数字转换到Excel中：选中，复制，设置输入单元格为文本，选择性粘贴，值选中，表格转换为文本，粘贴，分列，对分列选项设置文本；另存为文本文件，在Excel中打开文本文件直接打开一个电子表格文件时打不开：“文件夹选项文件类型”中找到.xls文件，并在“高级”中确认是否有参数1%，如果没有，就手工加上。Excel中快速复制上一单元格的内容：选中下面的单元格，按Ctrl+西文单引号Excel中给单元格数据的尾部快速添加信息：选中该单元格按2键输入数据即可。将某个长行转成段落并在指定区域内换行：例如
4、A10内容很长，欲将其显示在A列至C列之内：选定A10:C12点击“编辑菜单填充内容重排”，此法很适合用于表格内的注释。Excel中快速定位到活动单元格所在列或行中的最后一个非空单元格，或者下一个单元格为空，则扩展到下一个非空单元格：Ctrl+Shift+箭头让Excel自动填充固定倍数的小数点或固定个数的零：“工具菜单选项编辑自动设置小数点”，若需要自动填充小数点，应在“位数”框中输入小数点右面的位数，如。信封尺寸：DL220110Excel中将一列组合型字符分开到若干列保存：可用“数据分列”命令Excel中合并各列(A列、B列)中的数据，中间加减号：=A3&-&B3用汉字名称代替单元格地址
5、：选定单元格区域在“名称框”中直接输入名字；选定要命名的区域，选“插入菜单名称定义”键入名字即可。在公式中快速输入不连续的单元格地址：按Ctrl键选取这些区域后“定义”名称，将此区域命名，如Group1，然后在公式中使用这个区域名，如“=SUM(Group1)”命名常数：在某个工作表中经常用到利率3.5%来计算利息，可以在“插入菜单名称定义当前工作薄的名字”框内输入“利率”，在“引用”框中输入“=0.035”确定。制作财务报表时，自动转换大写金额：在要转换大写金额的区域上右击，“自定义”输入“DBNum2 0万0千0百0拾0元0角整Excel中把一个编辑了宏或自定义函数的工作簿移植到其它电脑上
6、使用：最科学的方法是保存为加载宏。保存好的宏可以通过“工具”菜单加载宏命令，载入进来使用。将一些常见文件夹快捷方式添加到Excel“打开”和“另存为”对话框左侧区域中：点击“打开”或“另存为”对话框，选定自己喜欢的文件夹，再点击“工具(L)添加到我的位置”命令。 将一个表格中的某些列分别组合打印出来：选将不需要打印的行事列隐藏起来，再点击“视图”菜单视图管理器，如上图所示。输入一些唯一数据，如身份证号码时，可用数据有效性功能，防止重复输入：选定要输入数据的区域，如列，再点击“数据有效性允许(A):自定义公式(F)“=countif ( B:B,B1)=1”，如上图所示。接着点击“出错警告”选项
7、卡，标题:“数据重复” 错误信息:“请核查后，重新输入！”如果希望一次打开多个文档，可以将它们保存为一个工作区文件：打开多个文档后点击“文件”菜单下的“保存工作区”命令利用Excel锁定、隐藏和保护工作表的功能，把公式隐藏和锁定起来不让使用者查看和修改：先点击“编辑菜单定位定位条件”按钮，选中“公式”项，会看到单元格中含公式的区域。若想隐藏可右击该区域后点“设置单元格格式保护”，选中“锁定”与“隐藏”功能，点击“工具菜单保护保护工作表”，输入密码。为数字自动添加单位：选中单元格区域后右击点“设置单元格格式”对话框中的数字选项卡中的“自定义”命令，输入#.00元利用F4键快速切换“相对引用”和“
8、绝对引用”格式：在编辑栏中选中公式后按下F4键对于一些复杂公式，可以利用“公式求值”分段查检公式的返回结果，以查出错误所在：定位在含公式的单元格上点击“工具”菜单公式审核公式求值。第1章 办公室管理工作表办公室的工作并不难，只是多而杂。处理得井井有条是完成了工作，处理得一塌糊涂，也是工作了，但却做了一堆糊涂账。下面这些表格是办公室工作中常遇到的，跟财务管理，或多或少，有一些直接或间接的关系，谨作为一些例子，希望能给你的日常工作一点启示。办公室用品分为消耗性物品和非消耗性物品，领用需登记在册。一来可以掌控耗材的使用情况，控制成本，二来对于物品的领用做到心中有数，特别是非消耗性办公室用品，原则不能
9、重复申领，登记可做到有账可查。计算乘积的函数=PRODUCT(D3:E3) 其中D3数量；E3是单价。知识点PRODUCT函数：函数将所有以参数形式给出的数字相乘，并返回乘积值。函数语法PRODUCT (number l，number2，)函数说明1当参数为数字、逻辑值或数字的文字型表达式时可以被计算；当参数为错误值或是不能转换为数字的文字时，将导致错误。2如果参数为数组或引用，只有其中的数字将被计算。数组或引用中的空白单元格、逻辑值、文本或错误值将被忽略。报销费公式的编制当车辆使用时为了办公事，车辆消耗费可以报销，如果车辆使用为私事，那么车辆产生的消耗费则不予报销。本着这个原则，来编制报销费
10、的公式。选中I3单元格，在编辑栏中输入公式：“=IF(D3=公事,H3,0)”，按回车键确定。编制驾驶员补助费选中J3单元格，输入公式：“=IF(G3-F3)*248,INT(G3-F3)*24-8)*30,0)”，按回车键确定。其中“(G3-F3)*24”是将时间格式转换为小时制，“8”为法定工作时间八小时，“30”为超出法定八小时工作制时，每小时补助金额。插入部门合计行并编制计数、汇总公式为了方便观察和统计各部门用车情况，需要按部门进行分类统计。在不同的部门后插入两个空行。然后在C列按部门的不同，分别输入“业务部 计数”和“业务务部门 汇总”，下同。同时，调整列宽保证单元格中内容完整显示。
11、选中H6单元格，输入公式：“=SUBTOTAL(3,H3:H5)”，在右击鼠标设置为“数值”格式。其中“3”对应COUNTA函数，表示返回H3:H5区域中非空值的单元格个数。选中H7单元格，输入公式：“=SUBTOTAL(9,H3:H5)”，其中“9”对应SUM函数，表示对H3:H5区域求和并返回值。 最后跨行复制以上两个公式Ctrl+C，Ctrl+V。知识点SUBTOTAL函数：返回列表或数据库中的分类汇总。函数语法SUBTOTAL(Function_num，refl，ref2，) Function_num：为l到11(包含隐藏值)或l01到111(忽略隐藏值)之间的数字，指定使用何种函数在
12、列表中进行分类汇总计算。ref1、ref2：为要进行分类汇总计算的1到254个区域或引用。函数说明如果在ref1、ref2中有其他的分类汇总(嵌套分类汇总)，将忽略这些嵌套分类汇总，以避免重复计算。当Function_num为从l到11的常数时，该函数将包括通过“隐藏行”命令所隐藏的行中的值，当你要对列表中的隐藏和非隐藏数字进行分类汇总时，请使用这些常数。当Function_num为从101到111的常数时，SUBTOTAL函数将忽略通过“隐藏行”命令所隐藏的行中的值。只对列表中的非隐藏数字进行分类汇总时，就使用这些常数。总计数与总计公式的编制对本月车辆使用情况进行汇总统计，选中C23单元格输
13、入“总计数”，在C24单元格输入“总计”。选中H23单元格，输入公式：“=SUBTOTAL(3,H3:H20)”。选中H24单元格输入公式：“=SUBTOTAL(9,H3:H20)”。 第2章 固定资产的核算财务统计表中以固定资产核算、收费统计、账龄统计和损益表尤为重要。通过这些表,企业决策者才可以了解销售业绩、营业规模，便于了解企业的销售情况及市场前景。还可以了解企业的费用和支出情况，便于控制和降低成本费用开支水平。固定资产的核算对于财务人员来说，一直是个比较头疼的工作。如果用手工制作，不仅工作量非常大，技术含量非常低，而且非常耗时耗力。本章以固定资产核算为例，根据录入的设备原值、使用年限、
14、残值率等信息，自动生成年折旧额、月折旧额等数据，形成手工输入和电脑自动计算相结合的固定资产台账表。把我们的财务人员从繁重和枯燥的手工计算中解放出来。固定资产核算在应用Excel之前有两套账，一套是固定资产卡片或台账，作用是登记购入固定资产的名称、原值、折旧年限、年折旧额、净残值等，同时还要记录因报废或售出后固定资产的减少。另一套账负责记录每个会计期间各项资产应计提的折旧额、累计折旧额和净值。使用Excel创建固定资产台账和折旧提算表则可大大减轻工作量，并增加核算的准确度。下面是依据国家规定制定的分类折旧表，可用来查询设备的折旧年限。 固定资产台账中编制“编号”公式依次录入“使用部门资产类别名称
15、计量单位始用日期原值耐用年限残值率”等数据。再选中B3单元格，在编辑栏中输入公式：“=SUMPRODUCT($C$3：C3= C3)*1)”，回车确认。该公式是指计算C3单元格到C3单元格中内容相同的单元格个数，返回值为“1”。我们来看B10单元格的公式：“=SUMPRODUCT($C$3：C10=C10)*1)”计算C3单元格到C10单元格中内容相同的单元格个数，返回值为“6”，从C3单元格数到C10单元格，“管理部分”出现正好是6次。知识点SUMPRODUCT函数的功能是在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。函数语法SUMPRODUCT(arrayl，array2，a
16、rray3，)arrayl，array2，array3：为2到255个数组，函数将对相应元素进行相乘并求和。函数说明数组参数必须具有相同的维数，否则SUMPRODUCT函数将返回错误值#VALUE!。函数SUMPRODUCT将非数值型的数组元素作为0处理。固定资产台账中编制“残值”公式选中K3单元格输入公式：“=ROUND(H3*J3，2)”，回车键确认。固定资产台账中编制“年折旧额”公式选中L3单元格输入公式：“= ROUND(SLN(H3,K3,I3),2)”，按回车键确认。其中用SLN函数对资产进行线性折旧，折旧额用四合五入法保留两位小数。知识点SLN函数的功能是返回某项资产在一个期间中
17、的线性折旧值。函数语法SLN(cost，salvage，life)cost：为资产原值。salvage：为资产在折旧期末的价值(有时也称为资产残值)。life：为折旧期限(有时也称作资产的使用寿命)。编制“月折旧额”公式选中M3单元格输入公式：“=ROUND(L312，2)”，按回车键确认。在公室管理工作中，要制作固定资产的月折旧表，这里以2009年1月的折旧表为例。当制作下一个月的折旧表时，上期原值、上期折旧、上期累积折旧等数据就可以直接从上月的折旧表中复制了。 本月折旧=上期折旧+折旧变化 （=G3+J3） 本月原值=上期原值+原值变化 （=F3+I3）本月累计折旧=上期折旧+上期累计折旧
18、+折旧变化-累折变化 （=G3+H3+J3-K3）本月净值=本月原值-本月累计折旧 （=M3-N3）到期提示=本月原值-设备残值-本月累计折旧 （=M3-固定资产台账!K3-N3）选中P3：P15区域，点击“格式/条件格式”设置“小于”、“L3”再点击“格式”按钮，选红色填充，表示满足该条件时，单元格用红色填充。第3章 两个重要的统计（收费统计表、账龄统计表）新建“收费登记表”工作表，在A1:E41区域录入标题及日常收费记录。新建“收费统计表”工作表，制作标题和月份数。编制“单位1 的2008年”收费公式选中B3格，输入公式：“=SUMPRODUCT(收费登记表!$C$2:$C$100=$A3
19、)*(收费登记表!$D$2:$D$100=LOOKUP(2,1/($B$1:B$1),$B$1:B$1)*(收费登记表!$B$2:$B$100=B$2)*收费登记表!$E$2:$E$100)”，按回车键确认。使用拖拽的方法完成该列公式的复制。在横向复制（智能填充）2009年收费公式，再编制“单位2”和“单位3”的收费公式或者做（智能填充）即可。选中D3格，输入公式：“=SUMPRODUCT(收费登记表!$C$2:$C$100=$A3)*(收费登记表!$D$2:$D$100=LOOKUP(2,1/($B$1:B$1 ),$B$1:D$1)*(收费登记表!$B$2:$B$100=D$2)*收费登记
20、表!$E$2:$E$100)”，按回车键确认。用拖拽法完成该列公式的复制。选中F3格，输入公式：“=SUMPRODUCT(收费登记表!$C$2:$C$100=$A3)*(收费登记表!$D$2:$D$100=LOOKUP(2,1/($B$1:F$1 ),$B$1:F$1)*(收费登记表!$B$2:$B$100=F$2)*收费登记表!$E$2:$E$100)”，按回车键确认。使用拖拽的方法完成该列公式的复制。账龄统计表无论是对内还是对外，企业都需要进行账龄分析。特别是法律健全的今天，各个企业对应收账款的账龄数则更为关心。因为账龄一旦超过诉讼时效，就不再受法律保护，财务人员必须及时创建账龄分析表，提
21、醒相关决策者。财务人员常用的账龄统计表，经过Excel处理后，能直观地反映出每个往来单位的账龄。这对于及时观察往来账的诉讼时效，避免给企业造成损失由很大的作用。下面，我们就来对这两种统计表的制作进行讲解。本节将示范如何通过Excel直观反映每个账户的账龄，并计算出每个账龄区间总额。新建“往来账龄分析”工作表，在A2格中输入“截止时间：”，在B2中输入“2009-1-1”，其它格制作标题，设要输的“上限值天数”和“下限值天数”的区域为：“自定义/ 0天 ”这样输数字时会自动带“天”，输其它字符时不会自动带“天”并且在编制“金额”公式时才能计算正确。编制“金额”公式选中D4格，输入公式：“=IF(
22、AND($B$2-$C4=D$2,$B$2-$C4D$1),$B4,0)”，回车。知识点AND函数是Excel逻辑函数中较为常用的函数之一，当所有参数的逻辑值为真时它返回TRUE，只要有一个参数的逻辑值为假即返回FALSE。AND函数一般不单独使用，而是作为嵌套函数与IF函数一起使用。由AND函数返回的值作为IF函数的条件判断依据，最后返回不同的结果。函数语法AND（logica11，logical2，）logica11，logical2，表示待检测的1到30个条件值，各个条件值可以为TRUE或FALSE。函数说明参数必须是逻辑值TRUE或FALSE，或者包含逻辑值的数组或引用。如果数组或引用
23、参数中包含文本或空白单元格，这些值将被忽略。如果指定的单元格区域内包括非逻辑值，AND将返回错误值#VALUE！。横向、纵向复制公式，选中D4格，智能填充至D4：H18区域。第4章 损益表损益表是反映企业在一定期间内收入、费用和经营成果情况的会计报表，揭示了企业盈利(或亏损)的实际形成情况。通过损益表，企业决策者可以了解销售业绩、营业规模，便于了解企业的销售情况及市场前景。还可以了解企业的费用和支出情况，便于控制和降低成本费用开支水平。损益表还是进行部门考核的依据，还能帮助投资人和债权人了解和预测企业的盈利能力和偿债能力，并据以作出进一步投资或收回投资的决策。财务除了会制作表格外，还要学会分析
24、数据。通过编制一些简单的公式可以自动实现数据的计算和对比等。大家可以使用这些简单的方法，结合实际的财务数据分析的需要进行灵活运用。通过设定的销量、变动成本、固定成本和售价可以推算出盈亏平衡销量及收入；通过成本分析、销售分析能够清楚地预测企业的盈亏走向；分析项目获利能力的年金终值、年金现值和投资回收期望等指标。用饼图进行费用结构分析：企业费用汇总时，通常要使用到管理费用、财务费用和制造费用等总账科目。对其所属的二级科目的结构分析是财务管理工作的重要组成部分，通过分析可以了解总账科目的主要构成，从而为制定降低费用的方案提供依据。以饼图的方式反映费用结构可以清楚地看出各个二极科目在总费用中所占的比例
25、。 柱形图主要用于一个公司中的多个部门，或者一个集团下的多个公司的费用横向对比。作为上级财务管理人员，在进行公司的财务分析时对于下属部门或公司的费用进行比较，从而为监督、榆查下属部门或公司的费用提供依据。通过柱形图可以很直观地看出各项费用在各个下属部门或公司的消耗。量、价差分析表材料成本的变化由两个方面构成，一是单位消耗量，二是购进价格。通过创建“量、价差分析”表可以将材料成本的绝对变化额分解成：单位消耗量变化对材料成本的影响和价格变化对成本的影响。通过制作此表可以为企业制定降低成本方案和建立成本考核制度提供依据。创建“量、价差分析”工作表，输入标题、材料名称、单位、本期单价、上年同期单价、本
26、期累计耗量、同期累计耗量等数据。单位价差=本期单价-上年同期单价 F4=D4-E4 价格影响=（本期单价-上年同期单价）同期累计耗量 I4=F4*H4 数量影响=（本期累计耗量-同期累计耗量）本期单价 J4=（G4-H4）*D4 第5章 量本利分析量本利在财务分析中占有重要作用，通过设定的销量、变动成本、固定成本和售价可以推算出盈亏平衡销量及收入。本章以数据表和分析图的形式展示某一数据发生变化时盈亏线的变化情况：1滚动条的使用2散点图的使用量本利就是对成本、销量和利润的分析。成本、销量和利润三者之间的变化关系是决定企业是否盈利的关键，量本利分析是定量分析出企业成本、销量和利润三者之间的变化关系
27、。盈亏平衡点指标是企业盈亏分界线，它也是由量本利分析引出。这里我们需要用到一些公式：成本=单位成本产量+固定费用 收入=售价产量在过到盈利平衡点时，成本=收入。此时的盈亏平衡量=固定费用(售价-单位成本)。固定费用保持不变，售价和单位成本可以变动，因此盈亏平衡量是变动的。根据盈亏平衡量，可以求得，盈亏平衡收入=盈亏平衡量售价。新建“量本利分析”工作表，输入“产量(KG)”数据，并新建“固定费用、售价、单位成本”等数据区域以备后用，录入“固定费用”数值时设“自定义/ #,#0万元 ”， 录入“售价、单位成本” 数值时设“自定义/ 0万元 ”。否则公式计算时有误。成本公式C3 =($K$26*B3
28、+$C$28)/10000 收入公式D3 =($H$26*B3)/10000 利润公式E3 =D3-C3设置盈亏平衡线横坐标数据：在C20格输入“盈亏平衡线辅助数据”后在C22格输公式：“=ROUND(C28/(H26-K26),2)”。并在C23:C25输入公式：=C22设置盈亏平衡线纵坐标数据：在D22格输“1800”；在D23格输入公式：“=(C23*H26)/10000”；在D24格输“0”；在D25格输“-100”设置盈亏平衡量：在B30格输“盈亏平衡量”； 在C30格输入公式：=C23；在B31格输“盈亏平衡收入”；在其后单元格中输公式：=D23。为方便查看，可设置相关格的单位和格
29、式，同上。使用“窗体”工具栏，添加“滚动条”控件，在G28格拖出一个滚动条，右击选“设置控件格式”命令，设当前值(C): 0 最小值(M):60 最大值(X):100 步长(I):1 步长(P):10 单元格链接(L):$H$26 三维阴影在J28格拖出一个滚动条，右击选“设置控件格式”命令，设当前值(C): 35 最小值(M):35 最大值(X):100 步长(I):1 步长(P):10 单元格链接(L):$K$26 三维阴影生成散点图“插入/XY散点图/平滑线散点图/数据区域(D): =量本利分析(2)!$B$2:$E$18 列(L) /系列/点击添加(A)钮（把盈亏平衡线辅助数据添加到图
30、表中），名称(N): 盈亏平衡线 X值(X):选C22:C25 =量本利分析 (2)!$C$22:$C$25 Y值(Y): 选D22:D25 =量本利分析 (2)!$D$22:$D$25 ”设置刻度：设Y轴刻度为固定值去掉勾，最小值(N):-100 最大值(X):2000 主刻单位:100 次刻单位:100完成散点图。盈亏平衡量的变动分析通过上一节的工作表建立、数据输入，以及一系列的设置，我们已经有了一张量本利分析表，此时就可以通过这张表来分析售价变动和单位成本变动对盈亏平衡量、盈亏平衡收入以及散点图上盈亏平衡线的影响。售价降低对盈亏平衡量的影响：拖动第一个滚动条，降低产品售价。此时散点图中的
31、盈亏平衡线向右侧移动，表明盈亏平衡量增大，相应的单元格数据也会增大。售价提高对盈亏平衡量的影响：拖动第一个滚动条，提高产品售价。此时散点图中的盈亏平衡线向左侧移动，表明盈亏平衡量减小，相应的单元格数据也会减小。单位成本降低对盈亏平衡量的影响：拖动第二个滚动条，降低单位成本。此时散点图中的盈亏平衡线向左侧移动，表明盈亏平衡量减小，相应的单元格数据也会减小。单位成本提高对盈亏平衡量的影响：拖动第二个滚动条，提高单位成本。此时散点图中的盈亏平衡线向右侧移动，表明盈亏平衡量增大，相应的单元格数据也会增大。第6章 成本分析在量本利分析中要求将企业成本划分为变动和固定成本，在成本项目中很多项目既有固定成分
32、，又有变动成分，所以需要将其分解。1.趋势线的使用 2.RAND函数、MlN函数、MATCH函数成分分解表如何准确地将成本中的固定成分和变动成分分离是做好量本利分析的关键，一此成本项目的性质比较叫确，可以直接划分为固定成本或变动成本，但是有的则比较模糊。比如电费，虽然电费与产量有关，但是产量为零时电费却并不为零，这说叫电费中既有变动成分，又有固定成分。新建“”工作表，输入数据，并选中C2:D14区域（产量、电费）生成没有线的默认散点图，可进行一些小的调整，以便于查看。首先可删除图例，因为现在只有一个系列。给散点添加趋势线：右击某个散点，选“添加趋势线”选“线性/勾选显示公式”，此时，生成最终的
33、散点图。在散点图上出现了线性趋势线和一个二元一次方程。方程中的截距代表固定成本，即505.4；斜率代表单位变动成本，即1.9942。知识点：趋势线Excel图表中，散点图、折线图、面积图、条形图、柱形图、股份图和气泡图中都可以添加趋势线，通常适合使用趋势线的图表有两类，一是成对的数字数据，即典型的散点图中使用的数据；二是基于时间的数据，比如折线图、散点图、面积图等使用的数据。另外，除了线性趋势线，Excel还提供了非线性趋势线：对数：当数据增加或减少的变化速率非常大，然后很快变得平缓时使用。多项式：数据规则波动时使用，可以根据数据的波动规律制定多项式的阶数。乘幂：数据按照固定的速率增加时使用，
34、此时数据不能为零值或负数。指数：数据以递增或者递减的趋势变化时使用，数据同样不能为零或负数。移动平均：不是真正的趋势线，它是原数据按照指定的项数不高平均值。使用移动平均时要设定移动平均的项数。在勾选“显示公式”后，图形上会显示线性或非线性的趋势线对应的公式。在勾选“显示R平方值”后，图形上会显示模型的拟合系数。一般情况下，拟合系数越大，趋势线和原数据的拟合程度越好；反之则越差。采购成本分析表材料的成本是生产成本的重要组成部分，而材料的成本除了价格因素外，还有一项很重要的因素，就是采购成本。采购成本通常由两项组成，一是采购环节发生的费用；二是材料存储时的发生的费用。这是两个互相制约的因素，每批采
35、购量大，采购次数少，可以减少年采购成本，但是存储费用会增加；反之亦然。因此确定采购量和存储量之例的关系也是一项很重要的工作，通过“采购成本分析”可以帮助企业设置科学合理的采购量和采购次数，从而为降低企业采购成本提供可靠依据。我们先创建采购成本和存储成本在不同批次下的数据表，再利用公式计算最小成本、采购批次和采购量。然后添加年采购量、年采购成本和单位存储成本滚动条，最后制作存储成本和采购成本的散点图。首先，我们还是要来看一下本节要使用的公式：采购数量=年采购量/年采购批次 B2 =$B$19/A2 平均存量=采购数量/2 C2 =B2/2 存储成本=平均存量单位存储成本 D2 =C2*$I$19
36、 采购成本=年采购批次采购成本 E2 =A2*$E$19 总成本=存储成本+采购成本 F2=D2+E2 新建“采购成本分析”工作表，录入标题、年采购批次（A2:A13）等数据。用上边的公式计算后，再智能填充B2:F2区域的公式到B3:F13区域。在A16格输【最低采购成本】，B16格输公式：=MIN(F2:F13)；D16格输【采购批次】，E16格输公式：=INDEX(A2:A13,MATCH(B16,F2:F13,0)；H16格输【采购量】，I16格输公式：=INDEX(B2:B13,MATCH(B16,F2:F13,0)知识点MATCH函数是返回在指定方式下与指定数值匹配的数组中元素的相应
37、位置。如果需要找出匹配元素的位置而不是匹配元素本身，则应该使用MATCH函数而不是LOOKUP函数。函数语法MATCH(lookup_value，lookup_array，match_type)Lookup_value为需要在Lookup _array中查找的数值。例如，如果要在电话簿中查找某人的电话号码，则应该将姓名作为查找值，但实际上需要的是电话号码。Lookup_value可以为数值(数字、文本或逻辑值)或对数字、文本或逻辑值的单元格引用。Lookup_array可能包含所要查找的数值的连续单元格区域。Lookup_array应为数组或数组引用。Match_type为数字-1、0或1。如
38、果为1，则查找小于或等于Lookup_value的最大数值，Lookup_array必须按升序排列：、-2、-l、0、l、2、AZ、FALSE、TRUE；如果为0，则查找等于Lookup_value的第一个数值，Lookup_array可以按任何顺序排列；如果为-l，将查找大于或等于Lookup_value的最小数值，Lookup_array必须按降序排列：TRUE、FALSE、ZA、2、1、0、-1、-2，等等。如果省略Match_type，则假设为l。函数说明如果Match_type为0且Lookup_value为文本，可以在Lookup_value中使用通配符、问号(？)和星号(*)。问
39、号匹配任意单个字符；星号匹配任意一串字符。如果要查找实际的问号或星号，请在该字符前键入波形符()。添加年采购量滚动条：在A21、D21、H21格拖出一个滚动条，右击选“设置控件格式”命令设A21格当前值(C): 0 最小值(M):1000 最大值(X):3000 步长(I):200 步长(P):10 单元格链接(L):$B$19 三维阴影设D21格当前值(C): 0 最小值(M):200 最大值(X):600 步长(I):100 步长(P):10 单元格链接(L):$E$19 三维阴影设H21格当前值(C): 0 最小值(M):4 最大值(X):12 步长(I):1 步长(P):10 单元格链
40、接(L):$I$19 三维阴影添加拆线图，选中D1:E13，插入数据点拆线图，在设置X轴，显然我们希望它表示年采购批次，“图表菜单/源数据/系列，分类(X)轴标志(T): =采购成本分析!$A$2:$A$13 ”（选A2:A13区域作为X轴的数据源），正是我们所需要的，在改变图例位置到上边。采购量的变动影响分析拖动滚动条，增大年采购量，存储成本增大而采购成本未变，因此折线图上的存储成本线发生变动而采购成本线保持不变。存储成本增大，造成总成本增大，所以最低采购成本、采购批次、采购量也相应增大。采购成本的变动影响分析拖动滚动条，增大采购成本，采购成本增大而存储成本未变，因此折线图上的采购成本线发生
41、变动而存储成本线保持不变。采购成本增大，造成总成本增大，所以最低采购成本、采购量也相应增大，采购批次则减小。单位存储成本的变动影响分析拖动滚动条，增大单位存储成本，采购成本未变，因此折线图上的存储成本线发生变动而采购成本线保持不变。采购成本增大，造成总成本增大，所以最低采购成本、采购批次也相应增大，采购量则减小。第7章 销售分析作为企业经营的重要环节，销售的重要性不言而喻，销售利润是企业追求的目标。对销售利润及其影响因素的分析有着非常重要的作用。同样，对于销售情况的预测也是一项十分重要的工作。销售利润分析销售利润受到许多因素的影响，比如产品的售价变动、成本的增减、销售数量的变化等。本节将展示如
42、何将利润的变化额以定量的形式分解到各个因素中。新建一个“销售数据表”工作表，输入产品名称销售数量销售收入销售税金销售成本等内容。销售利润=销售收入-销售成本-销售税金 F3=C3-D3-E3，并复制公式到F4:F9区域。K3=H3-I3-J3，并复制公式到K4:K9区域。上年同期单位成本售价 L3=IF($B3=0,0,ROUND(C3/$B3,4) 复制公式到L3:O3区域。上年总利润 P3=ROUND(O3*B3,2) 复制公式到P4:P9区域。本年同期单位成本售价 Q3=IF(G3=0,0,ROUND(H3/$G3,4) 复制公式到Q3:T9区域。本年总利润U3=ROUND(T3*G3,
43、2) 复制公式到U4:U9区域。创建增减变化数据表接下来我们要制作单位成本增减变化数据表。这个部分将要使用到的公式：利润变化 =本年实际总利润上年同期总利润 V3=U3-P3 复制公式到V4:V9销售影响 =（本年实际销量上年同期销量）上年同期单位利润 W3 =IF(OR(B3=0,G3=0),0,ROUND(G3-B3)*O3,2) 复制公式到W4:W9售价影响 =（本年实际售价上年同期售价）本年实际销量 X3 =IF(OR(B3=0,G3=0),0,ROUND(Q3-L3)*G3,2) 复制公式到X4:X9税金影响 =（上年同期单位税金本年实际单位税金）本年实际销量Y3 =IF(OR(B3
44、=0,G3=0),0,ROUND(M3-R3)*G3,2) 复制公式到Y4:Y9成本影响 =（上年同期单位成本本年实际单位成本）本年实际销量Z3 =IF(OR(B3=0,G3=0),0,ROUND(N3-S3)*G3,2) 复制公式到Z4:Z9品种影响，若上年同期销售数量为0，等于本年实际利润；若本年实际销售量为0，等于上年同期利润的负值。AA3 =IF(B3=0,U3,IF(G3=0,-P3,0) 复制公式到AA4:AA9添加销售利润分析图，选择W2:AA2和W13:AA13两个区域，插入簇状柱形图，删除图例，在图中把正负按不同颜色区分开较容易分辨，双击负值数据点设颜色。销售预测分析预测工作
45、在财务管理中也是很重要的，除了销售预测，对于费用、成本、利润等都要进行科学地预测，才能为管理者提供决策的依据。新建“销售预测分析”工作表，输入月份、销量两列数据，选中B2:C13单元格，插入数据点折线图，删除图表中的“月份”系列，先择“B3:B13”为X轴数据源。并给X轴和Y轴加上标题。调整Y轴设置刻度：设Y轴刻度为固定值去掉勾，最小值(N):5200 最大值(X):82000 主刻单位:500 次刻单位:100完成折线图。添加趋势线：右击折线，选“添加趋势线”，选择“线性/显示公式”。 使用TREND函数预测销售量在B17格输入“函数法”，C17格输入“预测方法一：使用TREND函数预测12
46、月份销售量”C18格输入公式：=TREND(C3:C13,B3:B13,12)使用FORECAST函数预测销售量在C20格输入“预测方法二：使用FORECAST函数预测12月份销售量”。在C21格输入公式：=FORECAST(12,C3:C13,B3:B13)使用线性趋势方程预测12月份销量在B23格输入“线性法”；在C23格输入“使用线性趋势方程预测12月份销量”；根据趋势线公式，在C24格输入公式：=182*B14+5771.6经过以上步骤，我们就得到了一个预测12月份销售情况的数据表。 知识点TREND函数是指返回一条线线性回归拟合线的值。即找到适合已知数组known_ys和known_xs的直线(用最小二乘法)，并返回指定数组new_xs在直线上对应的Y值。函数语法TREND(known_ys，known_xs，new_xs，const) known_ys是关系表达式y=mx+b中已知的y值集合。如果数组known_ys在单独一列中，则该项的每一列被视为一个独立的变量。如果数组known_ys在单独一行中，则该项的每一行被视为一个独立的变量。known_xs是关系表达式y=mx+b中已知的可选x值集合。数组known_xs可以包含一组或多组变量。如果仅使用一个变量，那么只要known_xs和known_ys具有
