老年人壮阳:财务人员实战Excel

第2章　Excel 凭证与凭证汇总表

第1节、凭证汇总表科目代码录入

    有些工作注定是繁琐而没有创造性的重复劳动，比如科目代码的录入工作等。但这些工作就像基本功，只有基本功练好了，才有了学习高深武功的资本。

　　凭证时记录会计信息的重要载体，是生成对外报表的数据基础。汇总凭证是财务工作中的一个重要环节，重大的财务报表也是从这些源头上来。录入、汇总、分析、规划，一步步形成了完善的核算体系。

步骤01 新建工作表

　　启动Excel自动新建一个工作簿，保存并命名。双击工作表标签，将工作表改名为“科目代码”，按回车键确定。单击鼠标右键弹出快捷菜单，为工作表标签设置一个颜色。

步骤02   设置数据有效性

    选中A2单元格，点击“数据”→“数据工具”→“数据有效性”，弹出“数据有效性”对话框，“设置”→“允许”→在下拉列表中选择“自定义”。在“公式”文本框中输入公式：“=COUNTIF(A:A,A2)=1”，点击确定完成设置。

知识点：COUNTIF函数

COUNTIF函数用来返回计算区域中满足给定条件的单元格的个数。
　　函数语法COUNTIF (range,criteria)range：为需要计算其中满足条件的单元格数目的单元格区域。
　　criteria：为确定哪些单元格倍计算在内的条件，其形式可以为数字、表达式和文本。
　　函数说明Excel提供其他的函数，可用来基于条件分析数据。

例如要计算基于一个文本字符或某个范围内的一个数值的总和，可以使用SUMIF函数。若要使公式返回两个基于条的值之一，例如某个指定销售量的销售红利，则可使用IF函数。
　　在本例中，是指在A列中查找和A2单元格中数字相同的单元格个数为1，即在A列没有与A2单元格中的数字相同的单元格。

步骤03   用格式刷复制设置　

    选中A1单元格，单击“开始”→剪贴板上的格式刷按钮，此时，光标起了变化。按住Shift键不放，单击A100单元格，第一列格式复制完成，光标回复常态。

步骤04   输入数据、完善表格
    这里可没有窍门或技巧，老老实实根据公司的科目输入数据吧。数据录入完成，简单设置字体、字号和边框线即可。

步骤05   冻结窗口

    根据每个公司情况的不同，科目的数量也不同。在进行数据录入的时候，财务人员经常会遇到这样的情况：不管多大的显示器，都无法完整显示表格，给录入工作带来些许不便利。录入时间一长，还可能出错。这个时候可以用到Excel[Http://www.officewhy.com/excel/]冻结窗格的功能。在本例中就遇到这种情况，这时，选中A2单元格，点击“视图”→“窗口”→“冻结窗格”

第2节、凭证汇总表的制作

     -

前面一系列工作，其目的就是为了编制凭证汇总表，只有经过数据汇总，才能给出一个明确的数据，成为规划决策的依据。这里我们选择一些重要的项目为大家讲解汇总表的制作，当然，在实际凭证汇总中，每个项目都是不能省略的。

步骤01   新建工作表
    在“凭证明细表“工作簿中，将Sheet3改名为“凭证汇总”，在A1单元格输入“凭证汇总表”，在A3：C3中分别输入标题。选中B2单元格，在右键菜单中选择“设置单元格格式”→“日期”→“2001年3月”。然后输入表格的时间“2009-5-7”。在C2单元格输入“编号：第 号至第 号”。

步骤02   输入科目名称

在A4:A19单元格区域中输入科目名称，在A20单元格输入“合计”。

步骤03   设置货币格式
    选中B4:C19单元格区域，点击鼠标右键弹出快捷菜单，选择“设置单元格格式”→“数字” →“分类”→“货币”，将“小数位数”的值设置为“2”，“货币符号”选择“无”，“负数”选择“-1,234.10”，然后点击确定按钮完成单元格区域设置。

步骤04   编制借方金额汇总公式
    选中B4单元格，在编辑栏中输入公式：“=SUMIF(凭证明细!$F:$F,$A4,凭证明细!D:D)”，按回车键确认。将光标移动到B4单元格右下角，当光标变为黑十字形状时，按住鼠标左键不放，向下拖动鼠标到B19松开，即可完成公式的复制。适当调整B列列宽，使B列内容能够完全显示出来。

步骤05   编制贷方金额汇总公式
选中C4单元格，在编辑栏中输入公式：“= SUMIF(凭证明细!$F:$F,$A4,凭证明细!E:E)”，用上面的方法，拖动完成C5:C19单元格区域公式的复制。

步骤06   编制金额合计
    选中B20单元格，点击“编辑”中的Σ符号，自动生成求和公式，用同样的方法完成C20的金额合计。

步骤07 完善表格

    表格中的0很影响表格的美观，可以采用前面说过的方法取消零值的显示。点击Office按钮→Excel选项→高级→此工作表的显示选项→去掉复选项“在具有零值的单元格中显示零”→确定。

　　设置工作表的字体、字号和边框线，根据个人爱好对表格进行美化。还可以在“页面布局”→工作表选项→网格线→去掉“查看”复选框中的勾→确定。这样就取消显示网格线了。

步骤08 工作表的保护

    每个人都明白财务报表对于财务工作的重要性，每个数据都是严谨不可随意更改的。很多人拥有查阅报表的权利，但却没有修改数据的权利。做财务工作的人都知道，哪怕是一个数字，都足以改变全盘。那么，怎样保证工作表不被随意改动呢？除了对自己电脑的加密保护外，工作表同样可以设置密码保护。

　　在“审阅”→更改→保护工作表→在“取消工作表保护时使用的密码”，现在，想要修改工作表，系统都会弹出一个警告窗口。如果需要修改数据，就要先撤销工作表保护，需要输入事先设定的那个密码。当然，一定要记住这个密码，否则自己也无法修改工作表中的数据了。我们还可以设置保护工作簿，对于一个系统的表格，这样的保护措施是必要的。

第3节、凭证明细表中的自动筛选

步骤01 设置自动筛选

　　选择A1:G1单元格区域，点击“数据”→“筛选”，此时，在A1:G1单元格区域的每个单元格的右侧会出现一个小三角的下拉按钮。

步骤02 进行筛选查询

　　单击A1单元格右侧的下拉按钮，然后选择“6”，“凭证明细”工作表中就会筛选出“序号”为6的记录。搭配其他几项进行筛选查询，

　　可以更加精确地查找到符合条件的记录

步骤03 美化表格

　　对表格的字体、字号、边框线等进行简单设置，为方便显示A1:G1单元格区域适当加宽。为方便浏览，可使用冻结窗格的功能。

第4节、编制一、二级科目代码

步骤01 输入一级科目代码

　　选中F2单元格，在编辑框输入公式：“=VLOOKUP(C2,科目代码!$A$2:$C$171,2,FALSE)”，按回车键确认。

　　选中F2单元格，将光标放在单元格的右下角，当光标变成黑十字形状时按住左键不放，向下拖拽到相应位置后松开左键，即可完成该列的公式复制。

步骤02 输入二级科目代码

    选中G2单元格，输入公式：“=VLOOKUP(C2,科目代码!$A$2:$C$171,3,FALSE)”，按回车键确认。选中G2单元格，将光标放到右下角，当光标变成黑十字形状，按住鼠标左键不放，向下拉动鼠标到相应位置，松开左键即可完成该列公式的复制

　　知识点：

本例公式说明

一级科目代码公式：是指在“科目代码”工作表的A2:C171单元格区域的A列中查找与“凭证明细”工作表中C2单元格中内容相同的单元格，然后返回对应B列中的内容到当前单元格。

二级科目代码公式：是指在“科目代码”工作表的A2:C171单元格区域的A列中查找与“凭证明细”工作表中C2单元格中相同的单元格，然后返回对应C列中的内容到当前单元格。

第5节、凭证明细表样板设置科目代码的录入方式

步骤01   新建工作表
    单击工作表标签Sheet2，改名为“凭证明细”，然后在A1:G1单元格区域输入标题名称。

步骤02   定义科目代码名称
    单击“科目代码”工作表标签进入“科目代码”工作表，选中A2:A100单元格区域，然后单击菜单“公式”→“定义名称”→弹出“新建名称”对话框，在“名称”中输入“data”，然后单击确定按钮。这样就将“科目代木”工作表中的A2:A100单元格区
域的名称定义为“data”。

步骤03   设置数据有效性
    单击“凭证明细”工作表标签，选中C2:C204单元格区域，点击“数据”→“数据工具”→“数据有效性”，在弹出的“数据有效性”对话框，在“设置”→“允许”→选择“序列”。

    在“来源”中输入“=data”，单击确定按钮。

步骤04   单元格边的下拉列表
    此时，单击C2单元格，在单元格的右侧会出现一个下拉按钮，鼠标单击下拉按钮会弹出一个下拉列表，列表中的内容是“科目代码”工作表中的科目代码，用户可以用鼠标选择输入内容。

步骤05   输入数据
    在A2:E204单元格区域输入数据，遇到数字过长，可适当调整列宽，让单元格中的数据完整显示。

第3章　Excel 应收应付款表格

第1节、到期示意表的提醒功能

尽管非常用心，非常在意，你也不可能清楚记得每一件事。但电脑还可以自动对重要的事情进行提示。比如，到期应付款项，可以自动填充颜色来提醒财务人员，更方便查看。

就上面例子，按照给定日期，自动对将要到期的汇票项填充颜色以提醒使用者，更醒目更直接。

步骤01   调用上例工作表

打开上例工作表，将文件另存为新文件，删除不用的F列，同时删除G5、G6单元格内容，并去掉G6的填充色。

步骤02   使用条件格式
　　选中A2:E2单元格区域，点击“开始”→“样式”→“条件格式”→新建规则，填出“新建格式规则”对话框。在“选择规则类型”中选择“使用公式确定要设置格式的单元格”，然后在“编辑规则说明”的编辑框中输入公式：“=($D2-$G$2<=30)*($D2-$G$2>0)”。

步骤03   设置单元格格式
　　公式输入完成后，点击下方的“格式”按钮，弹出“设置单元格格式”对话框，在“填充”选项卡中，选择一个填充颜色，然后按“确定”按钮返回“条件格式”对话框。再次按确定按钮，完成条件格式的设置。

步骤04   复制条件格式
　　选中A2:E2单元格区域，点击工具栏中的“格式刷”按钮，当光标改变形状，变成空心十字加格式刷的形状时，按住Shift键不放单击E20单元格，现在，格式全部复制到A1:E20单元格区域了。

步骤05   高亮区分的到期提示项

现在，条件格式设置完成了，表中30天内到期的记录项就会自动填充橘黄色显示出来，非常醒目。

第2节、承兑汇票到期示意图

在公司财务工作中，长期会使用承兑汇票，承兑汇票到期后需要用现金偿还。这张到期示意图，可以清楚显示到期偿还的金额，还有到规定日期还剩余多少天数。相关人员从这张表可以随时掌握承兑汇票的现状。

步骤01   新建表格并录入数据

启动Excel2007新建一个工作簿，将Sheet1改名为“到期示意表”。在第一行单元格输入标题，并完成A2:E20单元格区域的数据录入。

步骤02   输入给定日期

选中H2单元格，输入给定日期“2009-2-28”，作为到期示意的日期判断标准。适当调整列宽，保证所有数据完整显示。

步骤03   编制到期示意公式

选中F2单元格，在编辑栏中输入公式：“=IF(AND(D2-$H$2<=30,D2-$H$2>0),D2-$H$2,0)”，按回车键确认。将光标放在F2单元格的右下角，当光标变成黑十字形状时，按住鼠标左键不放，向下拖动鼠标到F20单元格松开，就完成了改列公式的复制。

步骤04   到期示意
　　以给定日期为标准，未来30天内到期的汇票记录显示剩余天数，不满足条件的汇票记录，如已经过期的则显示为“0”。

步骤05   插入SUMIF函数
　　现在，我们来编制汇总金额的公式。选中H6单元格，点击“开始”→编辑→Σ符号边的下拉列表按钮→其他函数→弹出“插入函数”对话框。在“选择类别”中选择“数学与三角函数”，在“选择函数”列表中选择“SUMIF”函数，点击确定。

步骤06   输入函数各项参数
　　弹出“函数参数”对话框，将光标放到“Range”文本框中，用鼠标在工作表中选中2:F20单元格区域，这是条件判断的区域。在“Criteria”文本框中输入“">0"”，这是条件。将光标定位到“Sum_range”文本框，然后用鼠标在工作表中选定E2:E20单元格区域，这是求和区域。点击确认按钮。

步骤07   生成汇总金额
　　这时，H6单元格自动生成了公式：“=SUMIF(F2:F20,">0",E2:E20)”，并计算出汇总金额。

知识点：SUMIF函数SUMIF函数将根据指定的条件对若干个单元格求和。
　　函数语法SUMIF(range,criteria,sum_range)range：为用于条件判断的单元格区域。

criteria：为确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本。

sum_range：是需要求和的实际单元格。
　　函数说明只有在区域中相应的单元格符合条件的情况下sum_range中的单元格才可求和。如果忽略了sum_range，则对区域中的单元格求和。
    本例公式说明其各个参数值指定SUMIF函数从F2:F20单元格区域查找大于零的记录，并对E列中同一行的相应单元格的数值进行汇总。F列是已经计算得到的到期剩余天数，E列是该汇票的金额。通过这样的条件求和，即可得到30天内即将到期的汇票总金额。

步骤08   完善表格
    表格到此已经实现了功能，现在需要进一步美化。设置表格标题的字体、字号和填充颜色。同时为了突出显示给定日期和将到期金额，选中H2和H6单元格，在“设置单元格格式”中为这两个单元格选择一个醒目的填充色。表格中的0很影响表格的美观，可以通过设置取消零值的显示。点击Office按钮→
Excel选项→高级→此工作表的显示选项→去掉复选项“在具有零值的单元格中显示零”→确定。

步骤09   到期提示的另一种提示方法
　　在上面得到期提示表中，到期提示是到给定日期剩余的天数。如果你不想看到还有多少天，而只是需要一个更明显的字样，提示到期了。那么，你可以通过下面的设置实现。选择F2:F20单元格区域，点击鼠标右键弹出快捷菜单，选择“设置单元格格式”→数字→自定义，然后在右边的“类型”列表中选择“[>0]"到""期";G/通用格式”，点击确定。

步骤10   到期提示
　　现在，可以看到到期提示发生了变化。喜欢哪一种方法，全凭个人喜欢。

第3节、应付款图表制作

　　对于财务人员来说，数字是有魔力的，搭建出一座财务的高塔，非常神奇。但对于非财务人员来说，数字却是枯燥的、乏味的、死板的。表哥、表姐辛苦汇总而来的表格对于某些人来说，有种遥远而陌生的感觉，但图表却不同，形象且直观地反应出一些客观事实，对于那些对数字不感冒的人，图表是拉近距离非常好的表现形式。Excel就有非常丰富的图表绘制工具，通过各种几何图形和色彩传达着信息。下面，我们就以应付款明细表为例，为大家讲解图表的绘制。

步骤01   指定数据源
　　选中A3单元格，按住Shift键盘不放，再点击B12单元格，就选中了用于绘图的数据。

步骤02   柱形图
　　选择“插入”→“图表”→“柱形图”→“簇状柱形图”，也就是柱形图的第一个。

步骤03   标准的柱形图
　　这时，Excel自动生成了一个标准的簇状柱形图，样式也非常简单。仔细观察，会发现Excel上方快捷按钮变成了图表工具。

步骤04   修改图表样式
　　在图表工具的“图表样式”中，我们可以对柱状图的颜色和形状进行选择，这里我们选择了深紫色且柱子呈立体效果的“样式30”。

步骤05   调整柱体尺寸
　　在图表工具→格式→大小→将柱体的尺寸修改为22厘米。

步骤06   设置坐标轴格式
　　在图表工具→布局→坐标轴，对横竖坐标轴的线条颜色、线型、对齐方式等进行简单设置。

步骤07   添加数据标签
　　用鼠标点击图标中的柱子，这个时候，所有柱子的四角就多了一些小圆圈。将光标放到小圆圈上，单击鼠标右键，弹出快捷菜单。在快捷菜单中点击“添加数据标签”，现在，每个柱子的头顶就多了一行数据。这正是我们想要的直观感觉。
 步骤08   修改数据列名称
    在步骤七的右键菜单中，点击“选择数据源”，点击“图例项（系列）”中的“编辑”按钮，弹出“编辑数据系列”对话框，在系列名称中，将“系列1”修改为“应付金额”，点击确定按钮。

步骤09   删除图表标题
　　现在，图表的正上方多了一个标题“应付金额”，这个标题是我们所不需要的，双击标题选中该标题，直接将文字删除即可。

步骤10   调整图表位置
　　在图标上点击鼠标右键，选择“设置绘图区格式”，在弹出的对话框中，可以为绘图区设置填充色等。现在，图表已经完成了，用鼠标拖动到合适的位置，还可以对图表的宽度和高度进行调整。

第4节、公司应付款表制作

    作为一个有信誉的公司，付款及时，不拖不赖是很重要的。但付款的同时要考虑公司现金流的问题，什么时候付，付多少都需要财务人员为公司提供数据支持。现在，有一家公司有10个供货商应付款，金额不等。公司计划近期付款，并有两种不同的付款方案。方案一：小于或等于1000元的账户一次性付清，大于1000元的账户偿付应付金额的50%。方案二：小于或等于3000元的账户一次性付清，大于3000元的账户偿还应付金额的40%。现在公司需要财务人员制作一份表格，按照两种方案给出支付明细，并计算总还款金额。

步骤01   新建工作表
　　启动Excel2007创建新的工作簿，将Sheet1工作表改名为“应付款明细”并保存。在A1单元格输入“应付款明细表”，在A2:D2单元格区域输入标题名称。

步骤02   录入数据并求和
　　将供货单位、应付金额按照实际情况一一录入。在A14输入“合计”，在B14编制求和公式。选中B14单元格，在编辑栏中输入求和公式：“=SUM(B3:B12)，按回车键确认。

步骤03   编制方案一的公式
     现在需要编制公式实现方案一得功能：小于或等于1000元的账户一次性付清，大于1000元的账户首次支付应付金额的50%。选中C3单元格，在编辑栏中输入公式：“=IF(B3<=1000,B3,ROUND(B3*50%,2))”，按回车键确认。
　　选中C3单元格，将光标放在单元格右下角，当光标变成黑十字形状时，按住鼠标左键不放，向下拖动鼠标到C12松开，完成C4:C12单元格区域的公式复制。

步骤04   方案一求和
　　选中C13单元格，在编辑栏中输入公式：“=SUM(C3:C12)，按回车键确认。现在，方案一的应付款明细，和公司首付款的总金额就出来了。

步骤05   编辑方案二的公式
　　选中D3单元格，在编辑栏中输入公式：“=IF(B3<=3000,B3,ROUND(B3*40%,2))”，按回车键确认。选中D3单元格，将光标放在单元格右下角，当光标变成黑十字形状时，按住鼠标左键不放，向下拖动鼠标到D12松开，完成D4:D12单元格区域的公式复制。

步骤06   编制求和公式
　　选中D13单元格，在编辑栏中输入公式：“=SUM(D3:D12)，按回车键确认。现在，方案二的应付款明细，和公司首付款的总金额也计算好了。

步骤07   完善表格
　　设置表格边框线，适当调整行高列宽，调整标题的字体、字号和文字居中显示等。现在，两种方案的应付款表就完成了。

第5节、公司应收款表制作

　　某公司年终盘点，需要了解当年应收款的拖欠情况，以此作为第二年销售计划调整的依据。现在有8家销售商有拖欠款项，公司需要财务部出具一份详细的应收款清单。

步骤01   新建工作表
　　启动Excel2007创建新的工作簿，将Sheet1工作表标签改名为“应收账款”。在第一行输入工作表名称，选中I4单元格，在右键快捷菜单中选择“设置单元格格式”→数字→日期→2001-3-14→确定，然后在编辑栏输入日期“2008-12-30”。在A2:N2单元格区域输入标题名称，并适当调整列宽，方便完全显示单元格内容。

步骤02   录入数据
　　将8家未付款公司的数据一一录入，包括开票日期、发票号码、公司名称、应收金额、已收款金额、收款期等数据。

步骤03   未收款金额计算
　　选中F3单元格，在编辑栏中输入公式：“=D3-E3”，按回车键确认。将光标放在F3的右下角，当光标变成黑十字形状时，按住鼠标左键不放，向下拖动鼠标到F10单元格松开，完成该列公式的复制。

步骤04   到期日期计算
　　选中H3单元格，在编辑栏中输入公式：“=A3+G3”，按回车键确认。将光标放在H3的右下角，当光标变成黑十字形状时，按住鼠标左键不放，向下拖动鼠标到H10单元格松开，完成该列公式的复制。

步骤05   判断是否到期
　　选中I3单元格，在编辑栏中输入公式：“=IF(H3>$I$1,"否","是"”，按回车键确认。将光标放在I3的右下角，当光标变成黑十字形状时，按住鼠标左键不放，向下拖动鼠标到I10单元格松开，完成该列公式的复制。

知识点：IF函数IF函数用来执行真假值判断，根据逻辑计算的真假值返回不同的结果。　　函数语法IF(logical_test,value_if_true,value_if_false)logical_test：表示计算结果为TRUE或FALSE的任意值或表达式。

value_if_true：表示logical_test为TRUE时返回的值。
　　value_if_false：表示logical_test为FALSE时返回的值。
　　函数说明IF函数最多可以嵌套7层，使用value_if_true及value_if_false参数可以构造复杂的检测条件。在计算参数value_if_false及value_if_true后，IF函数返回相应语句执行后的返回值。如果IF函数的参数包含数组，在执行KF语句时数组中的每一个元素都将被计算。

步骤06   未到期金额计算
　　选中J3单元格，在编辑栏中输入公式：“=IF($I$1-$H3<0,$D3-$E3,0)”，按回车键确认。将光标放在J3的右下角，当光标变成黑十字形状时，按住鼠标左键不放，向下拖动鼠标到J10单元格松开，完成该列公式的复制。

步骤07   编制逾期天数计算公式
     对于到期未收到得款项，需要对时间段有具体的了解，就可以划分为几个时间段来分析，本例中分为四个时间段:0~30天、30~60天、60~90天和90天以上。现在针对每个时间段编制公式如下：
　　K3单元格中输入公式：“=IF(AND($I$1-$H3>0,$I$1-$H3<=30),$D3-$E3,0)”；
    L3单元格中输入公式：“=IF(AND($I$1-$H3>30,$I$1-$H3<=60),$D3-$E3,0)”；
    M3单元格中输入公式：“=IF(AND($I$1-$H3>60,$I$1-$H3<=90),$D3-$E3,0)”；
    N3单元格中输入公式：“=IF($I$1-$H3>90,$D3-$E3,0)”。
　　公式输入完成后，按回车键确认，同时使用上述公式复制的方法完成该列单元格区域的公式。

步骤08   合计
针对四个时间段的应收款进行合计，选中J11单元格，点击“开始”→“编辑”→Σ→在J11自动生成求和公式：“=SUM(J3:J10)”。使用同样的方法，完成K11、L11、M11、N11的合计运算。

步骤09   完善表格
对表格的字体、字号、边框线进行设置，取消网格线显示。

第4章　Excel 进销存管理表

第1节、财会教程-制作入库表

　　货品收到，检查无误后，就可以入库了。这时，先填写入库表，登记在案才有了之后的出库和库存分析，也让盘存等操作有了依据。

入库表效果图

步骤01   新建工作表
　　将上述工作簿中的Sheet3工作表改名为“入库表”，并保存。在B2:M2单元格区域输入表格的标题，并适当调整单元格列宽，保证单元格中的内容完整显示。

步骤02   录入数据
　　在B3:B12中输入“入库单号码”，在C3:C12单元格区域输入“供货商代码”。选中C3单元格，在右键菜单中选择“设置单元格格式”→”数字”→”
分类”→”自定义”→在“类型”文本框中输入“"GHS-"0”→确定。

步骤03   编制“供货商名称”公式
　　选中D3单元格，在编辑栏中输入公式：“=IF(ISNA(VLOOKUP(C3,供货商代码!$A$2:$B$11,2,0)),"",VLOOKUP(C3,供货商代码!$A$2:$B$11,2,0))”，按回车键确认。

　　知识点：ISNA函数ISNA函数用来检验值为错误值#N/A（值不存在）时，根据参数值返回TRUE或FALSE。
　　函数语法ISNA（value）value：为需要进行检验的数值。
　　函数说明函数的参数value是不可转换的。该函数在用公式检验计算结果时十分有用。
　　本例公式说明查看C3的内容对应于“供货商代码”工作表中有没有完全匹配的内容，如果没有返回空白内容，如果有完全匹配的内容则返回“供货商代码”工作表中B列对应的内容。

步骤04   复制公式
　　选中D3单元格，将光标移到单元格右下角，当光标变成黑十字形状时，按住鼠标左键不放，向下拉动光标到D12单元格松开，就可以完成D4:D12单元格区域的公式复制。

步骤05   录入“入库日期”和“商品代码”
　　将“入库日期”列录入入库的时间，选中G3单元格，按照前面的方法，自定义设置单元格区域的格式，并录入货品代码。

步骤06   编制“商品名称”公式

　　选中H3单元格，在编辑栏中输入公式：“=IF(ISNA(VLOOKUP(G3,货品代码!A:D,2,0)),"",VLOOKUP(G3,货品代码!A:D,2,0))”，按回车键确认。使用上述公式复制的方法，将H3单元格中的公式复制到H4:H12单元格区域。

步骤07   编制“规格”公式
　　选中I3单元格，在编辑栏中输入公式：“=IF(ISNA(VLOOKUP(G3,货品代码!A:D,3,0)),"",VLOOKUP(G3,货品代码!A:D,3,0))”，按回车键确认。使用公式复制方法，完成I列单元格的公式复制。
　　在公式复制的时候，可以适当将公式多复制一段，因为在实际应用过程中，是要不断添加记录的。

步骤08   编制“计量单位”公式
　　选中J3单元格，在编辑栏输入公式：“=IF(ISNA(VLOOKUP(G3,货品代码!A:D,4,0)),"",VLOOKUP(G3,货品代码!A:D,4,0))”，按回车键确认。使用上述公式复制法完成J列单元格公式的复制。

步骤09   设置“有无发票”的数据有效性
　　选中F3：F12单元格区域，点击菜单“数据”→选择数据工具栏中的“数据有效性”→弹出“数据有效性”对话框→在“允许”下拉菜单中选择“序列”→在“来源”文本框中输入“有,无”，点击确定按钮完成设置。

步骤10   选择有或无
　　选中F3单元格，在单元格右侧出现一个下拉按钮，单击按钮弹出下拉列表，可以直接选择“有”或“无”，而不用反复打字了。

步骤11   编制“金额”公式
　　在K3:K12和L3:L12单元格区域分别录入数量和单价。选中M3单元格，在编辑栏中输入公式：“=K3*L3”，按回车键确认。使用公式复制的方法完成K列单元格区域公式。

步骤12   完善表格
　　设置边框线，调整字体、字号和单元格文本居中显示等，取消网格线显示。考虑实际应用中，数据是不断增加的，可以预留几行。

第2节、制作出库表

　　下面我们来学习如何对出库的每一项进行记录，并制作出库表。

出库表效果图

步骤01   新建工作表
　　插入一个新的工作表，改名为“出库表”，并保存。在B2:L2单元格区域输入表格的标题，并适当调整单元格列宽，保证单元格中的内容完整显示。选中C3单元格，用前面的方法设置单元格格式，在自定义中输入“"LYR-"0”，并使用“格式刷”将格式复制到C列其他单元格中。录入“出库单号码”和“领用人代码”。

 步骤02   编制“领用人姓名”公式
　　选中D3单元格，在编辑栏中输入公式：“=IF(ISNA(VLOOKUP(C3,领用人代码!A:B,2,0)),"",VLOOKUP(C3,领用人代码!A:B,2,0))”，按回车键确定。使用公式复制的方法完成D列公式的复制。

步骤03   编制“货品代码”公式

　　根据实际情况，录入“领用时间”和“货品代码”，并设置单元格格式。

　　选中G3单元格，在编辑栏中输入公式：“=IF(ISNA(VLOOKUP(F3,货品代码!A:　　D,2,0)),"",VLOOKUP(F3,货品代码!A:D,2,0))”，按回车键确认。使用公式复制的方法，完成G列单元格的公式复制。

步骤04   编制“规格”、“计量单位”公式
　　“出库表”的公式与“入库表”工作表大致相同，所以不用重复讲解，具体公式如下：H3=IF(ISNA(VLOOKUP(F3,货品代码!A:D,3,0)),"",VLOOKUP(F3,货品代码!A:D,3,0))I3=IF(ISNA(VLOOKUP(F3,货品代码!A:D,4,0)),"",VLOOKUP(F3,货品代码!A:D,4,0))选中H3:I3单元格区域，使用公式复制的方法，完成H4：I11单元格区域的公式复制。

步骤05   编制“金额”公式
　　在J列录入领用数量，在K列输入单价。选中L3单元格，在编辑栏中输入公式：“=J3*K3”，按回车键确认。使用公式复制的方法，完成L列单元格区域的公式复制。

步骤06   完善表格
　　设置字体、字号、文本居中显示和边框线，取消网格线显示即可。

第3节、货品总账表的制作

    过上期库存量、本期出入库记录计算出本期期末货品库存情况；通过货品库存量，可以看到积压的资金量；通过分析，还可以看到货品销售的走势、进货的缺陷，在下一次进货的时候就可以根据分析结果调整购买货品的种类。对于服装销售店来说，还可以从中看出季节变化带来的货品积压，并搞一些打折促销活动，将过季货品低价处理掉。这正是制作出入库表和货品总账表的目的所在。.

货品总账表效果图

步骤01   新建工作表
　　插入新的工作表，改名为“货品总账”，并保存。在B2:L2单元格区域设计表格标题，然后进行合并居中设置，并适当调整单元格列宽，保证单元格中内容完整显示。

步骤02   录入数据
　　选中B4:B13单元格区域，设置单元格格式，通过自定义让货品代码前自动生成“NK-”，然后输入货品代码。使用前面的方法对数据有效性进行设置。

步骤03   编制“货品名称”、“计量单位”公式 

　　与前两例中的“货品名称”、“计量单位”数据调用方法相同，公式分别如下：　　C4=IF(ISNA(VLOOKUP(B4,货品代码!A:D,2,0)),"",VLOOKUP(B4,货品代码!A:D,2,0))D4=IF(ISNA(VLOOKUP(B4,货品代码!A:D,4,0)),"",VLOOKUP(B4,货品代码!A:D,4,0))选中C4:D4单元格区域，使用公式复制的方法完成C5:D13单元格区域的公式复制。

步骤04   录入“期初库存”数据
     在E4:F13中单元格区域录入“期初库存”的“数量”和“金额”，在上一期报表中可以查到这些数据。同样可以采用调用的方法读取这些数据，在本例中直接输入数据。

 步骤05   编制“本期入库数量”公式
　　选中G4单元格，在编辑栏中输入公式：“=SUMIF(入库表!$G:$G,$B4,入库表!K:K)”，按回车键确认。通过这个公式，在“入库表”工作表的G列中查找货品代码为“NK-101”，并将K列中对应的数量相加返回一个值。

步骤06   编制“本期入库金额”公式
　　选中H4单元格，在编辑栏中输入公式：“=SUMIF(入库表!$G:$G,$B4,入库表!M:M)”，按回车键确认。这个公式的意思是，在“入库表”工作表的G列中查找与B4单元格中想匹配的货品代码，然后将M列中的对应金额相加返回一个值。

步骤07   编制“本期出库”
　　本期出库中的数量和金额的公式编制方法相同，如下所示：
　　本期出库数量：I4=SUMIF(出库表!$F:$F,$B4,出库表!J:J)
　　本期出库金额：J4=SUMIF(出库表!$F:$F,$B4,出库表!L:L)

步骤08   公式复制
　　选中G4:J4单元格区域，使用公式复制的方法完成这四列单元格的公式复制。

步骤09   编制期末余额公式
　　选中K4单元格，在编辑栏中输入公式：“=E4+G4-I4”，按回车键确认。
　　选中L4单元格，在编辑栏中输入公式：“=F4+H4-J4”，按回车键确认。
　　选中K4:L4单元格，使用公式复制的方法完成这两列单元格的公式复制。

步骤10   完善表格
　　设置表格边框线，取消网格线和零值的显示，设置字号、单元格文本居中显示，完成表格的美化。

第4节、进销存管理表

　　对于一个企业来说，生产中遇到的材料可谓琳琅满目。每天都有大量的材料入库或出库，通过系统化的管理，对于材料的出库、入库和库存做到有账可查。大的企业是这样，一个小的百货店、销售公司也会面临同样的问题。通过Excel表格，了解商品的周期、库存积压情况，可以帮助店主或公司销售人员更好地决策。看似简单的一个出库表、入库表和总账，所反应出来的问题却值得分析，继而做出正确的判断。事业上的成功绝非偶然，往往出自对细节的关注。这里我们以服装销售的进销存工作表为例讲解出库表、入库表和汇总表的制作，举一反三，这套工作表也可以用于企业的材料入库、出库和汇总核算。

　　材料核算在工业企业中占据至关重要的地位，它是成本核算的基础。材料成本是产品成本的主体，正确核算成本才能保障企业最终收益的准确计算，而及时地核算成本则是按时创建报表的前提。对于大型企业来说，经过多年的总结、开发，有自己一套完整的材料核算系统，财务人员只需要熟悉它、使用它、完善它。
　　随着创业呼声越来越高，加入到创业队伍的人越发多起来，胆子大、步子快是这群新兴势力的共同特点。但光有雄心并不能实现创业梦，还需要具有财务管理能力，对于货品的管理，可以借鉴企业对于材料收、发、余核算的管理模式。这里以一个服装店为例，讲解货品管理的一些方法。

步骤01   新建工作表
　　启动Excel2007创建新的工作簿，将Sheet1改名为“货品代码”。在A1:D1单元格区域输入标题。

步骤02   设置数据有效性
　　选中A2:A11单元格区域，点击菜单“数据”→“数据有效性”，弹出“数据有效性”对话框。选择“设置”→在“允许”下拉列表中选择“文本长度”，在“数据”下拉菜单中选择“等于”，在“长度”文本框中输入“3”，点击“确定”按钮完成有效性的设置。
　　现在，你会发现在货品代码栏中，小于或大于3位的代码都无法输入了。

步骤03   录入数据
　　在工作表中输入数据，并根据内容适当调整单元格的列宽，保证单元格中内容完整显示。

步骤04   设置货品代码显示
　　选中A1单元格，点击鼠标右键，在快捷菜单中选择“设置单元格格式”，在“数字”→“分类”→“自定义”→在“类型”文本框中输入“"NK-"0”,单击”确定“按钮完成设置。

步骤05   复制单元格设置
　　选中A1单元格，单击“格式刷”按钮，当光标发生变化后，按住Shift键不放单击A11单元格，完成A列单元格格式设置的复制。现在，货品代码前都多了“NK-”。

步骤06   完善表格
　　对字体、字号、居中、边框线等进行设置，并取消网格线的显示。

　　使用同样的方法，制作“供货商代码”表格和“领用人代码”表格，这里就不重复步骤了，表格实际显示效果如下。

第5章　Excel 办公室管理工作表

第1节、办公用品领用记录表

　　办公室用品分为消耗性物品和非消耗性物品，领用需登记在册。一来可以掌控耗材的使用情况，控制成本，二来对于物品的领用做到心中有数，特别是非消耗性办公室用品原则不能重复申领，登记可做到有账可查。

办公室用品领用表效果图

步骤01   新建工作表
　　启动Excel2007，新建工作簿，将Sheet1改名为“办公用品领用记录表”。选中A1单元格，在编辑栏中输入“办公用品领用记录表”，在A2:I2单元格区域输入标题，在对齐方式中点击“居中”按钮。选中A1:I1单元格区域，点击上方“合并居中”按钮。

步骤02   设置格式
　　选中A3单元格，点击鼠标右键，在弹出的快捷菜单中选择“设置单元格格式”→“数字”→“日期”，在右边的“类型”列表中选择“01-3-14”，点击确定。选中A3单元格，点击“格式刷”按钮，当鼠标变化后按着Shift键不放点击A17，就完成了A列单元格区域的格式复制。

步骤03   录入数据
　　按照当月办公用品的领用情况，逐一将数据录入表格。

步骤04   计算价值
　　选中F3单元格，在编辑栏中输入公式：“=PRODUCT(D3:E3)”，按回车键确定。

选中F3单元格，将光标放在单元格右下角，当光标变成黑十字形状时，按住鼠标左键不放，向下拖动鼠标到F17松开，就完成了F4:F17单元格区域的公式复制。

　　知识点：PRODUCT函数PRODUCT函数将所有以参数形式给出的数字相乘，并返回乘积值。
　　函数语法PRODUCT（number1,number2,…）
　　函数说明①当参数为数字、逻辑值或数字的文字型表达式时可以被计算；当参数为错误值或是不能转换为数字的文字时，将导致错误。
　　②如果参数为数组或引用，只有其中的数字将被计算。数组或引用中的空白单元格、逻辑值、文本或错误值将被忽略。

步骤05   完善表格
　　表格中数据已经完成，现在对表格的样式做进一步美化。设置字体、字号、边框线和填充色，并适当调整列宽，保证表中内容完整显示。

第2节、财务报表中车辆使用管理表

　　很多办公用品在使用中会产生费用，而使用又存在公事或私事两种情况，费用上就会有所区别。该怎么处理这类账务呢？这里以车辆使用为例，做简单讲解，财务人员可在实际应用中举一反三。

车辆使用管理表效果图

步骤01   新建工作表
　　启动Excel2007，创建新的工作簿，将Sheet1改名为“车辆使用管理表”。选中A1单元格，在编辑栏输入“公司车辆使用管理表”。在A2:J2单元格区域输入标题，并适当调整列宽，保证单元格中内容完整显示。选中A1:J1单元格区域，点击“合并居中”按钮。

步骤02   数据录入
　　将当月用车记录逐一录入，在录入时，按部门顺序录入。选中H3单元格，点击鼠标右键弹出快捷菜单，选择“设置单元格格式”→“数字”→“货币”→设置“小数位数”的值为“0”→“货币符号”选择人民币符号，点击确定完成设置。选中H3单元格，点击“格式刷”按钮，当光标变化后按着Shift键不放，点击J12单元格，完成H3:J12单元格区域的格式复制。现在，这个单元格区域的数字前自动生成了一个人民币符号。

步骤03   报销费公式的编制
　　当车辆使用时为了办公事，车辆消耗费可以报销，如果车辆使用为私事，那么车辆产生的消耗费则不予报销。本着这个原则，来编制报销费的公式。选中I3单元格，在编辑栏中输入公式：“=IF(D3="公事",H3,0)”，按回车键确定。

步骤04   报销费公式的复制
　　选中I3单元格，将光标放在单元格的右下角，当光标变成黑十字形状时，按着鼠标左键不放，向下拖动鼠标到I12单元格松开，就完成了I列单元格区域公式的复制。

步骤05   编制驾驶员补助费
　　选中J3单元格，在编辑栏中输入公式：“=IF((G3-F3)*24>8,INT((G3-F3)*24-8) *30,0)”，按回车键确定。选中J3单元格，将光标放在单元格右下角，当光标变成黑十字形状时，按住鼠标左键不放，向下拖动光标到J12单元格松开，完成J列公式的复制。

步骤06   插入部门合计行
　　为了方便观察和统计各部门用车情况，需要按部门进行分类统计。在不同的部门后插入两个空行，然后在C列按部门的不同，分别输入“业务部 计数”和“业务部门 汇总”，同时调整列宽保证单元格中内容完整显示。

步骤07   编制各部门计数、汇总公式

　　选中H6单元格，在编辑栏中输入公式：“=SUBTOTAL(3,H3:H5)”，按回车键确认。点击鼠标右键，在弹出的快捷菜单中选择“设置单元格格式”→数字→数值→点击确定按钮。选中H7单元格，在编辑栏中输入公式：“=SUBTOTAL(9,H3:H5)”，按回车键确认。

　　知识点：SUBTOTAL函数返回列表或数据库中的分类汇总。
　　函数语法SUBTOTAL(function_num, ref1, ref2, ...)function_num：为1到11（包含隐藏值）或101到111（忽略隐藏值）之间的数字，指定使用何种函数在列表中进行分类汇总计算。
　　ref1、ref2：为要进行分类汇总计算的1到254个区域或引用。
　　函数说明如果在ref1、ref2……中有其他的分类汇总（嵌套分类汇总），将忽略这些嵌套分类汇总，以避免重复计算。
　　当function_num为从1到11的常数时，SUBTOTAL 函数将包括通过“隐藏行”命令所隐藏的行中的值，当你要对列表中的隐藏和非隐藏数字进行分类汇总时，请使用这些常数。当function_num为从101到111的常数时，SUBTOTAL 函数将忽略通过“隐藏行”命令所隐藏的行中的值。当你只对列表中的非隐藏数字进行分类汇总时，就使用这些常数。
　　function_num对应的函数如下：

本例公式说明=SUBTOTAL(3,H3:H5)，“3”对应COUNTA函数，表示返回H3:H5单元格区域中非空值的单元格个数。
=SUBTOTAL(9,H3:H5)，“9”对应SUM函数，表示对H3:H5单元格区域求和并返回值。

步骤08   跨行公式复制
　　在前面的例子中，我们已经掌握了连续单元格公式的复制，但是当单元格不间断不连续时，如果复制公式呢？方法很简单，就是我们熟悉的CTRL+C和CTRL+V命令。选中H6单元格，同时按下CTRL+C键，然后用鼠标选中H10、H14、H17、H21单元格，并同时按下CTRL+V键，公式和格式就同时复制完成了。比如，选中H21单元格，编辑栏中显示的公式就是：“=SUBTOTAL(3,H18:H20)”，Excel的职能化就此体现出来。使用同样的方法，对汇总公式进行复制。

步骤09   总计数与总计公式的编制

　　对本月车辆使用情况进行汇总统计，选中C23单元格，输入“总计数”，在C24单元格输入“总计”。选中H23单元格，在编辑栏中输入公式：“=SUBTOTAL(3,H3:H20)”，按回车键确认。选中H24单元格，在编辑栏中输入公式：“=SUBTOTAL(9,H3:　　H20)”，按回车键确认。
步骤10   完善表格
　　到此为止，工作表中的内容已经完成，现在来进行工作表最后一步的美化工作。首先，取消零值的显示。点击Office按钮→Excel选项→高级→此工作表的显示选项→去掉复选项“在具有零值的单元格中显示零”→确定。然后对字体、字号、边框线和填充色进行设置。

第3节、考勤管理表的制作

　　传统的考勤表是用手工记录的，根据员工的出勤情况，划上叉叉、勾勾或圈圈。到了月末，挨个数清楚那些标记，统计出员工当月出勤情况。这种方法很原始，还容易出错。现代公司当然要实行现代化管理，于是考勤机便应运而生了。但很快弊端就出现了。产品再高科技，也不如人脑来得灵活，因为人懂得如何去作弊。总不能再回归到原始的考勤办法吧，利用Excel可以讲手工和电脑劳动相结合，起码现在你不用去数勾勾叉叉了。
考勤管理表效果图

步骤01   新建表格
　　启动Excel2007创建新的工作簿，将Sheet1改名为“考勤管理表”，保存。在A1单元格中输入“2009年3月考勤表”，在A2单元格输入“员工编号”，在A3单元格输入“A101”。选中A3单元格，将光标放在单元格右下角，当光标变成黑十字形状时，按住鼠标左键不放，向下拉动光标到A22单元格松开，在A列自动生成了员工编号。在B2输入“1号”，同样使用刚才的方法自动生成日期编号，不过这次是向右拉动鼠标。

步骤02   设置多列列宽
　　现在，你也能发现这个表格的问题，太宽了，以至于无法完整浏览。选中B列，按住Shift键不放选中AF列，点击鼠标右键，在弹出的右键菜单中选择“列宽”，在弹出的“列宽”对话框中输入“2.5”，点击确定按钮。用同样的方法将AG到AL列的列宽设置为“3.5”。适当调整第二行的行高，保证单元格中文字的完整显示。

步骤03   录入数据
　　为了省事，我们用“a”表示正常出勤，“b”表示迟到，“c”表示早退，“d”表示矿工，“e”表示事假，“f”表示病假。每天根据员工的出勤情况，进行记录。

步骤04   公式的编制
　　月末，记录完成，需要对出勤表进行统计。有Excel的帮助，我们并不需要去数有几个a、b、c、d、e、f，通过公式可以非常容易实现统计功能。选中AG3单元格，在编辑栏中输入公式：“=COUNTIF(B3:AF3,"a")”，按回车键确定。使用同样的方法完成其余单元格的公式，如下：
　　   AH3=COUNTIF(B3:AF3,"b")

       AI3=COUNTIF(B3:AF3,"c")

        AJ3=COUNTIF(B3:AF3,"d")

        AK3=COUNTIF(B3:AF3,"e")

        AL3=COUNTIF(B3:AF3,"f")

步骤05   多项公式的复制
　　以前的例子中，我们都是对单列的公式进行复制，当连续几列都需要复制公式时，该怎么办呢？其实，方法是相同的。选中AG3:AL3单元格区域，将光标放在AL3右下角，当光标变成黑十字形状时，按住鼠标左键不放，向下拉动光标至第22行，松开鼠标左键，就完成了AG4:AL22单元格区域的公式复制。

步骤06   完善表格
　　表格中内容已经完善，接下来就是表格外观的美化了。对于这种看上有些单调的表格，除了用边框线区别外，最好还要使用不同的填充色，将数据记录区域和数据统计区域区分，这样更便于浏览。

第6章　Excel 固定资产的核算

第1节、固定资产月折旧表的制作

　　在财务工作中，要制作固定资产的月折旧表，这里以2009年1月的折旧表为例简单讲解。当制作下一个月的折旧表时，上期原值、上期折旧、上期累积折旧等数据就可以直接从上月的折旧表中复制了。

月折旧表效果图

步骤01   新建工作表并设置自动换行
    将上例工作簿中的Sheet3改名为“月折旧表”，并保存。在B2:P2单元格区域的单元格中输入表格标题，并适当调整单元格的列宽，保证单元格内容完整显示。选中F2:N2单元格区域，点击弹出右键菜单→“设置单元格格式”→“对齐”→在“水平对齐”的下拉列表中选择“居中”→在“垂直对齐”的下拉菜单中选择“居中”→在“文本控制”的“自动换行”前打勾→点击“确定”完成设置。

步骤02   设置数据有效性
　　使用部门、资产类别的输入方法，跟上例中使用的数据有效性方法是相同的。你可以采用刚才的方法再做一次设置。不过这里，可以偷懒一下。将上个表格中这部分内容用CTRL+C和CTRL+V复制过来，复制会将格式和数据有效性设置一并复制过来的。

步骤03   编制“编号”公式
　　选中B3单元格，在编辑栏中输入公式：“=SUMPRODUCT(($C$3:C3=C3)*1)”，按回车键确认。使用拖拽的方法完成公式的复制。

步骤04   录入数据
　　在E3:E15单元格区域录入名称，在F3:H15录入上期数据。

步骤05   编制“本月折旧” 公式
　　选中L3单元格，在编辑栏中输入公式：“=G3+J3”，按回车键确认。使用拖拽的方法完成该列公式的复制。

步骤06   编制“本月原值”公式
　　选中M3单元格，在编辑栏中输入公式：“=F3+I3”，按回车键确认。用拖拽的方法完成该列的公式复制。

步骤07   编制“本月累计折旧”公式
　　选中N3单元格，在编辑栏中输入公式：“=G3+H3+J3-K3”，按回车键确认。使用拖拽的方法完成该列公式的复制。

步骤08   编制“本月净值”公式
　　选中O3单元格，在编辑栏中输入公式：“=M3-N3”，按回车键确认。使用拖拽的方法完成该列公式的复制。

步骤09   编制“到期提示”公式
　　选中P3单元格，在编辑栏中输入公式：“=M3-固定资产台账!K3-N3”，按回车键确认。使用拖拽的方法完成该列公式的复制。

步骤10   设置条件格式
　　选中P3:P15单元格区域，点击“开始”选项卡→“样式”→“条件格式”→“新建格式规则”→弹出“新建格式规则”对话框→在“选择规则类型”中选择“只为包含以下内容的单元格设置格式”→在下方的设置格式中，选择“单元格值”、“小于”，在文本框中输入“=L3”。
　　点击“格式”按钮，弹出“设置单元格格式”对话框，在“填充“中选择红色，表示满足该条件时候，单元格用红色填充。点击确认返回“新建格式规则”，点击“确定”按钮完成设置。

步骤11   编制“合计”公式
　　合并B16:E16单元格区域，输入“合计”。在F16:P16单元格区域的各个单元格中输入求和公式。

步骤12   完善表格
　　设置表格边框线，设置标题字体、字号、字形加粗等，为标题栏添加一个填充色，取消网格线的显示。

第2节、固定资产台账表的制作

　　固定资产的台账通常详细记录了物资的购入时间、价格、经办人或部门，有的还记录了每种物资流转的每一个细节，是财务人员或统计人员记录、核算与管理固定资产的主要手段。台账可以根据实际需要设计，并没有固定的格式。
　　下面就以某公司的几种设备为例，简单讲解固定资产台账表的制作。

固定资产台账表效果图

步骤01   新建工作表
　　在上例工作簿中，将Sheet2改名为“固定资产台账”，并保存。在B2:M2单元格区域输入表格标题，并适当调整列宽，保证单元格中内容完成显示。

步骤02   设置数据有效性
　　选中C3：C15单元格区域，在菜单中点击“数据”→“数据工具”→“数据有效性”→弹出“数据有效性”对话框。在“设置”选项卡中→在“允许”下拉列表中选择“序列”→在“来源”文本框中输入“管理部门,生产车间”，然后点击“确定”按钮完成数据有效性的设置。

步骤03   输入“使用部门”
　　选中C3单元格，在单元格的右侧就会出现一个下拉按钮，点击弹出列表，现在只需要在下拉列表中选择使用部门即可输入C列单元格的内容了。对于大量需要重复输入的文字，使用数据有效性设置，可大大提高工作效率。

步骤04   设置数据有效性并输入“资产类别”
　　选中D3：D15单元格区域，使用上述的方法打开“数据有效性”对话框，在“允许”中选择“序列”，在“来源”文本框中输入“运输设备,电子设备,房屋建筑物,机械设备”，点击“确定”按钮完成数据有效性设置。现在，我们就可以通过下拉列表中的选项快速输入D列的资产类别了。

步骤05   录入“名称”和“计量单位”
　　在E3:F15单元格区域中分别录入资产的“名称”和“计量单位”。当然，在计量单位列中，同样可以使用数据有效性设置，来完成该列的数据录入。适当调整列宽，保证单元格中内容的完整显示。

步骤06   编制“编号”公式
　　选中B3单元格，在编辑栏中输入公式：“=SUMPRODUCT(($C$3:C3=C3)*1)”，按回车键确认。使用拖拽的方法完成B4:B15单元格区域的公式复制。

　　知识点：SUMPRODUCT函数SUMPRODUCT函数的功能是在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。
　　函数语法SUMPRODUCT(array1,array2,array3, ...)array1, array2, array3, ...:：为2到255个数组，函数将对相应元素进行相乘并求和。
　　函数说明数组参数必须具有相同的维数，否则SUMPRODUCT函数将返回错误值#VALUE!。
　　函数SUMPRODUCT将非数值型的数组元素作为0处理。
　　本例公式说明=SUMPRODUCT(($C$3:C3=C3)*1):是指计算C3单元格到C3单元格中内容相同的单元格个数，返回值为“1”。
　　这听起来不太好懂，那么我们来看一下B10单元格的公式：　　=SUMPRODUCT(($C$3:C10=C10)*1)：计算C3单元格到C10单元格中内容相同的单元格个数，返回值为“6”，从C3单元格数到C10单元格，“管理部分”出现正好是6次。

步骤07   录入数据
　　在G3:J15单元格区域，分别录入“始用年限”、“原值”、“残值率”和“耐用年限”。

步骤08   编制“残值”公式
　　选中K3单元格，在编辑栏中输入公式：“=ROUND(H3*J3,2)”，按回车键确认。使用拖拽的方法完成该列公式的复制。

步骤09   编制“年折旧额”公式
　　选中L3单元格，在编辑栏中输入公式：“=ROUND(SLN(H3,K3,I3),2)”，按回车键确认。使用拖拽的方法完成该列公式的复制。

知识点：SLN函数SLN函数的功能是返回某项资产在一个期间中的线性折旧值。
　　函数语法SLN(cost,salvage,life)cost：为资产原值。
　　salvage：为资产在折旧期末的价值（有时也称为资产残值）。
　　life：为折旧期限（有时也称作资产的使用寿命）。
　　本例公式说明用SLN函数对资产进行线性折旧，折旧额用四舍五入法保留两位小数。

步骤10   编制“月折旧额”公式
　　选中M3单元格，在编辑栏中输入公式：“=ROUND(L3/12,2)”，按回车键确认。使用拖拽的方法完成该列公式的复制。

步骤11   完善表格
　　设置表格中的字体、字号、文本居中显示，并为标题栏设置填充色，设置边框线然后取消网格线显示。

第3节、固定资产分类折旧表

　　固定资产核算在应用Excel之前有两套账，一套是固定资产卡片或台账，作用是登记购入固定资产的名称、原值、折旧年限、年折旧额、净残值等，同时还要记录因报废或售出后固定资产的减少。另一套账负责记录每个会计期间各项资产应计提的折旧额、累计折旧额和净值。使用Excel创建固定资产台账和折旧提算表则可大大减轻工作量，并增加核算的准确度。
　　下面是依据国家规定制定的分类折旧表，可用来查询设备的折旧年限。

步骤01   新建工作表
　　启动Excel2007新建一个工作簿，将Sheet1改名为“分类折旧”，并保存。选中A1单元格，输入“工业企业固定资产分类折旧年限”，选中A1:B1单元格区域，点击“合并居中”按钮。

步骤02   输入通用设备折旧年限
　　在A2:B15单元格区域的单元格中分别输入通用设备及其折旧年限。适当调整列宽，保证单元格中内容完成显示。

步骤03   依次录入其他折旧年限
　　这个工作表没有复杂的公式需要设置，只有繁琐的录入工作，重复劳动而已。这里就不一步步都写出来了，分别将折旧年限录入即可。在输入右侧的折旧年限表时，中间空一列，也就是空出C列，这样更方便浏览，页面也更整洁。

步骤04   调整列宽
　　因为文字量较大，列宽要重新调整才能完整显示。但如果简单拉动调整列宽，两侧宽度难免不同，这样做出的表格缺乏对称性，影响整体布局。最好的方法是设置列宽，保证列宽完全相同。选定A列，点击鼠标右键弹出快捷菜单，选择“列宽”，在弹出的“列宽”对话框中输入数字“35”，将D列宽度也设置为35。用同样的方法，将B列和E列的列宽调整为“22”。

步骤05   完善表格
　　设置标题的字体、字号，字形加粗，并为左右两边的大标题设置填充色，让它更醒目。设置表格边框线，然后取消网格线显示，现在表格一下清爽整齐起来。

第4节、固定资产的核算表

　　固定资产的核算对于财务人员来说，一直是个比较头疼的工作。如果用手工制作，工作量非常大，技术含量非常低，但却非常耗时耗力。本章以固定资产核算为例，根据使用者录入的设备原值、使用年限、残值率等信息，自动生成年折旧额、月折旧额等数据，形成手工输入和电脑自动计算相结合的固定资产台账表。把财务人员从繁重和枯燥的手工计算中解放出来。

第7章　Excel 两个重要的统计表

第1节、账龄统计表

　　无论是对内还是对外，企业都需要进行账龄分析。特别是法律健全的今天，各企业对应收账款的账龄数应更为关心。因为账龄一旦超过诉讼时效，就不再受法律保护，财务人员必须及时创建账龄分析表，提醒相关决策者。
　　本节将示范如何通过Excel直观反映每个账户的账龄，并计算出每个账龄区间总额。

账龄统计表效果图

步骤01   新建工作表
　　启动Excel2007新建一个工作簿，将Sheet1改名为“往来账龄分析”，并保存。选中A2单元格，输入“截止时间：”，在B2输入“2009-1-1”，A3:I3输入标题，设置文字居中显示，然后适当调整单元格列宽，保证单元格内容完整显示。在A4:A18输入单位名称，选中A19单元格，输入“合计”。

步骤02   录入“期末金额”并求和
　　在B4:B18单元格区域输入“期末金额”，选中B19单元格，点击“编辑”中的Σ符号，自动生成求和公式：“=SUM(B4:B18)”，按回车键确认。

步骤03   设置日期格式并录入数据
　　选中C4:C18单元格区域，点击鼠标右键弹出快捷菜单，在“设置单元格格式”中，在“数字“中选择”日期，在“类型”中选择“2001-03-14”，点击确定完成设置。然后录入最后一笔交易的日期。

步骤04   设置自定义格式
　　选中D1:H2单元格区域，在右键菜单中选择“设置单元格格式”，然后选择“自定义”，在“类型”文本框中输入“0"天"”，点击确定按钮完成设置。

步骤05   输入数据
　　在C1单元格输入“上限值天数”，在C2单元格输入“下限值天数”，然后在D1:H2单元格区域录入天数数字，然后表格中就自动在数字后加个“天”，如图所示。

第2节、企业收费统计表的制作

　　对于收费，相信财务都会有详细的记录，包含了缴费时间、缴费单位、金额等信息。但这一条条记录对于分析判断起不了多大作用。但如果将这些信息进行归类整理，制作有针对性的收费统计表就不一样了。有了Excel，我们不用手工整理计算，自动生成统计表即可。

收费统计表效果图

步骤01   制作收费记录表
　　启动Excel2007创建一个新的工作簿，将Sheet1改名为“收费登记表”，并保存。在A1:E1单元格区域输入标题。然后录入日常收费记录，适当调整列宽保证单元格中内容完整显示。设置边框线、字体、字号和文本居中显示等，并取消网格线的显示。

步骤02   创建收费统计表
　　将Sheet2工作表改名为“收费统计表”，并保存。输入标题，对B1:C1、D1:E1和F1:G1单元格区域进行合并居中操作，并适当调整列宽。

步骤03   填充序列
　　选中A3单元格，输入“1”，将光标放在A3单元格的右下角，当光标变成黑十字形状时，按住鼠标左键不放，向下拖拽光标到相应位置松开。单击右下角自动填充的下拉小三角，在弹出菜单中选择“填充序列”。这时，A列单元格区域就按序列自动生成了一排数字。同样的操作，却得到不同的结果，个中细节需要表哥、表姐自己体会。熟练使用可以让Excel带来更多的方便。

步骤04   设置单元格格式
　　选中B3:G14单元格区域，单击鼠标右键弹出快捷菜单，在“设置单元格格式”中，选择“货币”，而在“货币符号”下拉列表中选择“无”，点击“确定”完成设置。

步骤05   编制单位1的2008年收费公式
　　选中B 3单元格，在编辑栏中输入公式：“= S UMP R O D U C T ( (收费登记表!$C$2:$C$100=$A3)*(收费登记表!$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)”，按回车键确认。使用拖拽的方法完成该列公式的复制。

步骤06   横向复制2009年收费公式
　　选中B3单元格，将光标放在单元格的右下角，当光标变成黑十字形状时，按住鼠标左键不放，朝右拖动光标到C3单元格松开，就能完成C3单元格的公式复制。使用拖拽的方法，完成C列公式的复制。
　　我们前面的公式复制都是在同列中进行的，对于不同列横向的公式复制，方法不过时大同小异。

步骤07   编制单位2的收费公式
　　选中D 3单元格，在编辑栏中输入公式：“=sumpoduct((收费登记表!$C$2:$C$100=$A3)*(收费登记表!$D$2:$D$100=LOOKUP(2,1/($B$1:D$1<>""),$B$1:D$1))*(收费登记表!$B$2:$B$100=D$2)*收费登记表!$E$2:$E$100)”，按回车键确定。将该公式复制到E3单元格，选中D:E3单元格区域，使用公式复制的方法完成D4:E14单元格区域公式的复制。

步骤08   编制单位3的收费公式
　　选中F 3单元格，在编辑栏中输入公式：“=sumpoduct((收费登记表!$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)，按回车键确认。使用上步骤的方法，完成F3:G14单元格区域的公式。

步骤09   合计
选中A15单元格，输入“合计“。在B15:G15单元格区域的各个单元格中制作各列求和公式。

步骤10   完善表格
设置表头填充色，设置字体、字号和边框线，取消网格线和零值的显示。

第8章　Excel 损益表

第1节、如何自动生成特殊日期

　　在工作中需要计算一些特殊的日期，例如给定一个日期，如何得到这个日期所在月份最后一天的日期；计算给定日期上一个月最后一天的日期等等。本节简单讲解如何用Excel自动生成这些日期。
步骤01   新建工作表
　　打开Excel2007新建一个工作表，在工作表中制作如图的工作表。

步骤02   编制计算给定日期最后一天的公式
　　选中B4单元格，输入公式：“=DATE(YEAR(B1),MONTH(B1)+1,0)”，按回车键确认。

　　知识点：DATE函数DATE函数全部隐藏返回代表特定日期的序列号。如果在输入函数前，单元格的格式为“常规”，则结果将设为日期格式。
　　函数语法DATE(year,month,day)year?：默认情况下，Excel将使用1900日期系统。

　　month：代表一年中从1月到12月（一月到十二月）各月的正整数或负整数。
　　day：代表一月中从1日到31日各天的正整数或负整数。

步骤03   编制给定日期该月所属季度公式
　　选中B5单元格，输入公式：“=ROUNDUP(MONTH(B1)/3,0)”，按回车键确认。

步骤04   编制给定日期上月末最后一天的公式
　　选中B6单元格，输入公式：“=B1-DAY(B1)”，按回车键确认。

步骤05   给定日期并查看变化
　　选中B1单元格，输入时间“2009-5-1”，按回车键，现在可以看到各单元格日期的变化了。

第2节、序号的两种编制方法

　　通常，我们习惯逐行编制序号，但在实际工作中，会隔行编制，甚至根据是否有内容编制序号。本节的例子就是针对有内容的行次编制序号。序号编制效果图

步骤01   新建工作表
　　打开Excel创建一个新的工作簿，制作一个工作表。

步骤02   编制第一种序号公式
　　选中B3单元格，在编辑栏中输入公式：“=IF(D3<>"",N(B2)+1,N(B2))”，按回车键确认。使用拖拽的方法完成该列公式的复制。

步骤03   编制第二种序号公式
　　选中C3单元格，在编辑栏中输入公式：“=IF(D3="","",MAX($C$2:C2)+1)”，按回车键确认。使用拖拽的方法完成该列公式的复制。通过效果图，很容易就能看出两种序号编制的区别在哪里。

　　知识点：MAX函数MAX函数的功能是返回一组值中的最大值。

　　函数语法MAX(number1,number2,...)number1,number2, ...是要从中找出最大值的1到255个数字参数。
　　函数说明参数可以是数字或者是包含数字的名称、数组或引用。逻辑值和直接键入到参数列表中代表数字的文本被计算在内。如果参数为数组或引用，则只使用该数组或引用中的数字。数组或引用中的空白单元格、逻辑值或文本将被忽略。如果参数不包含数字，函数MAX返回0零）。如果参数为错误值或为不能转换为数字的文本，将会导致错误。

第3节、损益表制作方法

　　这里，我们以一个简单的损益表为例，简单讲解制作方法。

损益表效果图

步骤01   新建工作表
　　启动Excel2007新建一个工作簿，将Sheet1改名为“损益表”，然后保存。在A1：A3单元格区域、A4:D4单元格区域和D3单元格输入工作表的标题。

步骤02   录入数据
　　在A5:C20单元格区域录入相关内容，并适当调整单元格列宽，保证单元格中内容完整显示。

步骤03   复制“上期累计数”
　　选中G4单元格，输入“上期累计数”。打开上一期的损益表，选择上期损益表中的“本年累计数”，复制该数据。选中本表格的G5单元格，右键单击弹出快捷菜单，选择“选择性黏贴”。

步骤04   选择性黏贴

　　在弹出的“选择性黏贴”对话框，选择“数值”，点“确定”完成设置。Excel的复制、黏贴式包含了公式、格式、边框和有效性等所有内容，用户在使用复制黏贴的时候，可以使用选择性黏贴，只选择自己需要的项目。

步骤05   编制公式
　　选中D5单元格，在编辑栏中输入公式：“=C5+G5”，按回车键确认。用拖拽的方法完成该列公式的复制。适当调整列宽，保证单元格中内容完整显示。

步骤06   完善表格
　　设置字体、字号、边框线，取消网格线和零值的显示即可。

第9章　Excel 简单数据分析方法

第1节、材料量、价差分析表

　　材料成本的变化由两个方面构成，一是单位消耗量，二是购进价格。通过创建“量、价差分析”表可以将材料成本的绝对变化额分解成：单位消耗量变化对材料成本的影响和价格变化对成本的影响。通过制作此表可以为企业制定降低成本方案和建立成本考核制度提供依据。

量、价差分析表效果图

步骤01   新建工作表
新建一个工作簿，并在工作簿中重命名其中一个工作表，删除其他的。输入表格的标题、数据，并设置格式等。

步骤02   设置“单位价差”公式
单击F4单元格，在编辑栏中输入公式“=D4-E4”，按回车确认。 

步骤03   复制公式
将光标移动到F4单元格的右下角，当光标变为十字形状时双击就可以在F5:F10单元格区域快速地复制公式。

步骤04   设置“价格影响”公式
“价格影响”公式：（本期单价-上年同期单价）×同期累计耗量。在I4单元格输入公式“=F4*H4”，这就是“价格影响”公式了。然后使用与上一步相同的方法复制公式。

步骤05   设置“数量影响”公式
“数量影响”公式：（本期累计耗量-同期累计耗量）×本期单价。在J4单元格输入公式“=(G4-H4)*D4”，这就是“数量影响”公式。然后复制公式。

第2节、用饼图进行企业费用结构分析

企业费用汇总时，通常要使用到管理费用、财务费用和制造费用等总账科目。对其所属的二级科目的结构分析是财务管理工作的重要组成部分，通过分析可以了解总账科目的主要构成，从而为制定降低费用的方案提供依据。以饼图的方式反映费用结构可以清楚地看出各个二极科目在总费用中所占的比例。

费用结构分析表效果图

步骤01   新建工作表
　　新建一个工作簿，并在工作簿中重命名其中一个工作表，删除其他的。输入表格的标题、数据，并设置格式等。最重要的是，在“合计”一项的后面使用求合函数——SUM函数，统计C4至C14的总和。 

步骤02   选择图表类型
选中B2:C14单元格区域单击“插入”选项卡的“饼图”按钮弹出一个图形菜单，选择一个二维饼图。 

步骤03   生成饼图
到这里我们就完成了一个简单的饼图。 

步骤04   准备对饼图进行布局设置
选中饼图，在“布局”选项卡中选择“数据标签”的“其他数据标签选项”，准备进行数据标签设置。 

步骤05   数据标签设置
进入“设置数据标签格式”对话框中，在“标签选项”，选中如图中的三项，这就是将会显示在饼图中的项目。也可以根据需要选择其他要显示的项目。

步骤06   完成设置
点击“关闭”，完成对饼图格式的设置，得到最终的显示结果。

第3节、用柱形图进行费用对比分析

本节的内容主要用于一个公司中的多个部门，或者一个集团下的多个公司的费用横向对比。作为上级财务管理人员，在进行公司的财务分析时对于下属部门或公司的费用进行比较，从而为监督、检查下属部门或公司的费用提供依据。通过柱形图可以很直观地看出各项费用在各个下属部门或公司的消耗。

用对比分析表效果图

步骤01   新建工作表
新建一个工作簿，并在工作簿中重命名其中一个工作表，删除其他的。输入表格的标题、数据，并设置格式等。在合计项目中我仍然要使用SUM函数进行列求和。
步骤02   选择图表类型
选中B2:F7单元格区域,单击“插入”选项卡的“柱形图”按钮弹出一个图形菜单，选择一个二维柱形图。 

步骤03   生成柱形图
现在我们已经生成了一个简单的柱形图，效果如下。

步骤04   数据标签设置
选中柱形图，在“布局”选项卡中选择“数据标签”的“其他数据标签选项”。 进入“设置数据标签格式”对话框中，在“标签选项”，选中如图中的项目，这就是将会显示在饼图中的项目。注意，要对每一个系列都进行一次设置，方法是在柱形图中点击某个系列的一个标签，然后在对话框中进行设置。 

步骤05   完成设置
点击“关闭”，完成对饼图格式的设置，得到最终的显示结果。

第4节、简单的财务数据分析图

本章介绍一些利用Excel进行简单数据分析的方法，通过编制一些简单的公式可以自动实现数据的计算和对比。大家可以使用这些简单的方法，结合实际的财务数据分析的需要进行灵活运用

费用结构分析表效果图

量、价差分析表效果图

第10章　Excel 本量利分析

第1节、盈亏平衡量的变动分析

　　通过上一节的工作表建立、数据输入，以及一系列的设置，我们已经有了一张本量利分析表，现在我们就可以通过这个表来分析售价变动和单位成本变动对盈亏平衡量、盈亏平衡收入以及散点图上盈亏平衡线的影响了。

步骤01   售价降低对盈亏平衡量的影响
　　拖动第一个滚动条，降低产品售价。此时散点图中的盈亏平衡线向右侧移动，表明盈亏平衡量增大，相应的单元格数据也会增大。 

步骤02   售价提高对盈亏平衡量的影响
　　拖动第一个滚动条，提高产品售价。此时散点图中的盈亏平衡线向左侧移动，表明盈亏平衡量减小，相应的单元格数据也会减小。 

步骤03   单位成本降低对盈亏平衡量的影响
　　拖动第二个滚动条，降低单位成本。此时散点图中的盈亏平衡线向左侧移动，表明盈亏平衡量减小，相应的单元格数据也会减小。 

步骤04   单位成本提高对盈亏平衡量的影响
　　拖动第二个滚动条，提高单位成本。此时散点图中的盈亏平衡线向右侧移动，表明盈亏平衡量增大，相应的单元格数据也会增大。

第2节、本量利分析表,本量利分析样图

本量利在财务分析中占有重要作用，通过设定的销量、变动成本、固定成本和售价可以推算出盈亏平衡销量及收入。本章以数据表和分析图的形式展示某一数据发生变化时盈亏线的变化情况。

　　本量利就是对成本、销量和利润的分析。成本、销量和利润三者之间的变化关系是决定企业是否盈利的关键，本量利分析是定量分析出企业成本、销量和利润三者之间的变化关系。盈亏平衡点指标是企业盈亏分界线，它也是由本量利分析引出。

 本量利分析效果图

　　这里我们需要用到一些公式：
　　成本＝单位成本×产量＋固定费用收入＝售价×产量在过到盈利平衡点时，成本＝收入。此时的盈亏平衡量＝固定费用/（售价－单位成本）。固定费用保持不变，售价和单位成本可以变动，因此盈亏平衡量是变动的。根据盈亏平衡量，可以求得，盈亏平衡收入＝盈亏平衡量×售价。 

步骤01   新建工作表并输入字段名
　　新建一个工作簿，并在工作簿中重命名其中一个工作表，删除其他的。输入表格的标题、数据，并设置格式等。

步骤02   输入数据并设置格式
　　在B3:B18区域输入产量数据，并新建立“固定费用”、“售价”、“单位成本”等新的数据区域以备后用。 

步骤03   创建成本公式
　　在C3单元格输入公式：=($K$26*B3+$C$28)/10000，并将公式复制到C4:C18区域。 

步骤04   创建收入公式
　　在D3单元格输入公式：=($H$26*B3)/10000，并将公式复制到D4:D18区域。

步骤05   创建利润公式
　　在E3单元格输入公式：=D3-C3，并将公式复制到E4:E18区域。 

步骤06   设置盈亏平衡线横坐标数据
　　在C2 0单元格输入标题“盈亏平衡线辅助数据”。在C2 2单元格输入公式：=ROUND(C28/(H26-K26),2)。并在C23:C25输入公式：=C22。 

步骤07   设置盈亏平衡线纵坐标数据
　　在D22单元格输入“1800”；在D23单元格输入公式：=(C23*H26)/10000；在D24单元格输入“0”；在D25单元格输入“-100”。 

步骤08   设置盈利平衡量和盈亏平衡收入
　　在B30单元格输入“盈亏平衡量”，在C30单元格输入公式：=C23。在B31单元格输入“盈亏平衡收入”，在单元格输入公式：=D23。同时，为了方便查看，可以设置一下相关单元格的单位和格式。 

步骤09   添加新命令
　　在“自定义快速访问工具栏”中选择“其他命令

步骤10   添加“滚动条”
　　弹出一个对话框，在下拉列表中选择“不在功能区中的命令”，添加“滚动条（窗体控件）”。 

步骤11   插入滚动条
　　通过上面的设置，在自定义快速访问工具栏中有了“滚动条”按钮。单击该按钮，在G28单元格拖出一个滚动条。使用同样的方法，在J28也拖出一个滚动条。 

步骤12   准备设置滚动条格式
　　在刚刚插入的滚动条上点击鼠标右键，在弹出的菜单中选择“设置控件格式”。

步骤13   设置滚动条格式
　　如图所示，在弹出的对话框中设置滚动条相关的值。包括最小和最大值，更重要的是与之相关的单元格链接设置。

步骤14   设置另一个滚动条格式
　　设置方法与上一步相同，不同的是设置的值。

步骤15   生成散点图
　　在“插入”选项卡中选择“散点图”，在弹出的菜单中选择“带平滑线和数据标记的散点图”。 

步骤16   添加数据系列
　　双击散点图，在工具栏上选择“选择数据”，在弹出的对话框中单击“添加”，这一步的目的是向散点图中添加新的数据系列。

步骤17   设置“盈亏平衡线”
　　在弹出的对话框中，系列名称设置为“盈亏平衡线”。X轴的数据通过点击文本框后面的编辑按钮进行选择。本例中应该选择C22:C25。Y轴使用相同的方法，选择D22:D25。最后，点击 “确定”完成设置。

步骤18   调整坐标刻度
　　我们看到，得到的散点图Y轴的刻度不够细，数据读取不便，我们可以对它进行一些设置。先选中散点图，在“布局”选项卡中选择如图的项目。

第11章　Excel 成本分析

第1节、采购成本分析表

　　 材料的成本是生产成本的重要组成部分，而材料的成本除了价格因素外，还有一项很重要的因素，就是采购成本。采购成本通常由两项组成，一是采购环节发生的费用；二是材料存储时的发生的费用。这是两个互相制约的因素，每批采购量大，采购次数少，可以减少年采购成本，但是存储费用的增加；反之亦然。
　　因此确定采购量和存储量之间的关系也是一项很重要的工作，通过“采购成本分析”可以帮助企业设置科学合理的采购量和采购次数，从而为降低企业采购成本提供可靠依据。

　　采购成本分析表效果图

我们先创建采购成本和存储成本在不同批次下的数据表，再利用公式计算最小成本、采购批次和采购量。然后添加年采购量、年采购成本和单位存储成本滚动条，最后制作存储成本和采购成本的散点图。首先，我们还是要来看一下本节要使用的公式：
　　采购当量＝年采购量/年采购批次平均存量＝采购数量/2存储成本＝平均存量×单位存储成本采购成本＝年采购批次×采购成本总成本＝存储成本＋采购成本

步骤01 新建工作表
新建一个工作簿，并在工作簿中重命名其中一个工作表，删除其他的。输入表格的字段标题、数据，并设置格式等。

步骤02 输入公式
 在B2到F2依次输入下列公式：
　　=$B$19/A2           =B2/2                =C2*$I$19
    =A2*$E$19           =D2+E2 

步骤03 复制公式
　　选中B2:F2，移动光标到F2右下角，双击即可复制上述公式至B3:F13区域。

步骤04 最低采购成本公式
　　在B16单元格输入公式：=MIN(F2:F13)。

步骤05 采购批次公式
　　在E16单元格输入公式：=INDEX(A2:A13,MATCH(B16,F2:F13,0))。 

步骤06 采购量公式
　　 在I16单元格输入公式：=INDEX(B2:B13,MATCH(B16,F2:F13,0))。关于INDEX函数
的使用我们在前面的相关章节已经介绍过。 

　　知识点：
　　MIN函数返回一组值中的最小值。
　　函数语法MIN(number1,number2,...)Number1,number2,...是要从中查找最小值的1到255个数字。
　　函数说明参数可以是数字或者是包含数字的名称、数组或引用。逻辑值和直接键入到参数列表中代表数字的文本被计算在内。如果参数为数组或引用，则只使用该数组或引用中的数字。数组或引用中的空白单元格、逻辑值或文本将被忽略。如果参数中不含数字，则函数MIN返回0。如果参数为错误值或为不能转换为数字的文本，将会导致错误。
　　MATCH函数返回在指定方式下与指定数值匹配的数组中元素的相应位置。如果需要找出匹配元素的位置而不是匹配元素本身，则应该使用MATCH函数而不是LOOKUP函数。
　　函数语法MATCH(lookup_value,lookup_array,match_type)Lookup_value为需要在Lookup_array中查找的数值。例如，如果要在电话簿中查找某人的电话号码，则应该将姓名作为查找值，但实际上需要的是电话号码。Lookup_value可以为数值（数字、文本或逻辑值）或对数字、文本或逻辑值的单元格引用。
　　Lookup_array可能包含所要查找的数值的连续单元格区域。Lookup_array应为数组或数组引用。　　Match_type为数字-1、0或1。Match_type指明Excel如何在lookup_array中查找lookup_value。如果match_type为1，函数MATCH查找小于或等于lookup_value的最大数值，Lookup_array必须按升序排列：...、-2、-1、0、1、2、...、A-Z、FALSE、TRUE；如果match_type为0，函数MATCH查找等于lookup_value的第一个数值，Lookup_array可以按任何顺序排列；如果match_type为-1，函数MATCH查找大于或等于lookup_value的最小数值，Lookup_array必须按降序排列：TRUE、FALSE、Z-A、...、2、1、0、-1、-2、...，等等。如果省略match_type，则假设为1。
　　函数说明函数MATCH返回lookup_array中目标值的位置，而不是数值本身。例如，MATCH("b",{"a","b","c"},0)返回2，即“b”在数组{"a","b","c"}中的相应位置。查找文本值时，函数MATCH不区分大小写字母。如果函数MATCH查找不成功，则返回错误值#N/A。如果match_type为0且lookup_value为文本，可以在lookup_value中使用通配符、问号(?)和星号(*)。问号匹配任意单个字符；星号匹配任意一串字符。如果要查找实际的问号或星号，请在该字符前键入波形符(~)。
　　步骤07 添加年采购量滚动条

　　我们前面已经在自定义快速访问工具栏上添加了滚动条按钮，现在我们又要用到它。点击这个按钮，然后在A21单元格拖出一个滚动条。
　　步骤08 设置年采购量滚动条
    在刚刚添加的滚动条上单击鼠标右键，在弹出的对话框中对滚动条进行如图中所示的设置。与这个滚动条相关联的单元格是B19，设置方法是单击文本框后面的编辑按钮，再用鼠标选中B19单元格。单击“确定”完成设置。　

      步骤09 添加并设置采购成本滚动条
    使用与上两步相机的方法在D21单元格添加滚动条，并对其进行设置，设置内容如图所示。

步骤10 添加并设置单位存储成本滚动条
　　同理，在H21单元格添加滚动条，并对其进行设置，设置内容如图所示。 

步骤11 完成设置
　　 通过以上几个步骤的设置，我们已经得到一个具有数据调节功能的采购成本分析表。 

第2节、采购成本变动分析

步骤01 采购量的变动影响分析
    拖动滚动条，增大年采购量，存储成本增大而采购成本未变，因此折线图上的存储成本线发生变动而采购成本线保持不变。
    存储成本增大，造成总成本增大，所以最低采购成本、采购批次、采购量也相应增大。

步骤02 采购成本的变动影响分析
    拖动滚动条，增大采购成本，采购成本增大而存储成本未变，因此折线图上的采购成本线发生变动而存储成本线保持不变。采购成本增大，造成总成本增大，所以最低采购成本、采购量也相应增大，采购批次则减少。

步骤03 单位存储成本的变动影响分析

    拖动滚动条，增大单位存储成本，采购成本未变，因此折线图上的存储成本线发生变动而采购成本线保持不变。采购成本增大，造成总成本增大，所以最低采购成本、采购批次也相应增大，采购量则减少。

第3节、材料成本汇总表

知识点：

数据透视表的数据源使用数据透视表可以汇总、分析、浏览和提供摘要数］使用数据透视图可以在数据透视表中可视化此摘要数据，并且可以方便地查看比较、模式和趋势。数据透视表和数据透视图都能使您做出有关企业中关键数据的决策。在创建数据透视表时，可使用多种源数据类型。
　　使用工作表数据可以将Excel工作表中的数据作为报表的数据来源。数据应为列表格式，第一行包含列标签，其余行包含相同列中的类似项，并且数据区域中没有空白的行或列。Excel将列标签作为报表的字段名称。
　　使用外部数据源要汇总和分析Excel的外部数据，则可从包括数据库、OLAP多维数据集和文本文件的外部数据源上检索数据。
　　使用其他数据透视表每次在新建数据透视表或数据透视图时，Excel均将报表数据的副本存储在内存中，并将其保存为工作簿文件的一部分。这样每张新的报表均需要额外的内存和磁盘空间。
　　但是，如果将现有数据透视表作为同一个工作簿中的新报表的源数据，则两张报表就可以共享同一个数据副本。因为可以重新使用存储区，所以就会缩小工作簿文件，减少内存中的数据。
　　如果要将某个数据透视表用作其他报表的源数据，则两个报表必须位于同一工作簿中。如果源数据透视表位于另一工作簿中，则需要将源报表复制到要新建报表的工作簿位置。不同工作簿中的数据透视表和数据透视图是独立的，它们在内存和工作簿文件中都有各自的数据副本。
　　在刷新新报表中的数据时，Excel也会更新源报表中的数据，反之亦然。如果对某个报表中的项进行分组或取消分组，那么也将同时影响两个报表。如果在某个报表中创建了计算字段或计算项，则也将同时影响两个报表。
　　更改现有报表的源数据更改源数据将导致用于分析的数据也发生变化。例如，您可能希望方便地从测试数据库切换到生产数据库。可以通过刷新报表，使用与原始数据连接信息类似的新数据来更新数据透视表。

　　在材料消耗环节既要统计同一产品不同材料的消耗量，又要计算不同产品对同一材料的消耗量。如何利用材料消耗明细账快速准确地制作出两个统计口径下的材料消耗汇总表是很重要的工作。

材料成本汇总表效果图

步骤01 新建工作表
　　新建一个工作簿，并在工作簿中重命名其中一个工作表，删除其他的。输入表格的字段标题、数据，并设置格式等。 

步骤02 生成材料金额
　　在D3输入公式：=INT(RAND()*1000)，并复制公式到D4:D32区域。 

知识点：RAND函数
　　D函数返回大于等于0及小于1的均匀分布随机实数，每次计算工作表时都将返回一个新的随机实数。
函数语法
RAND()
函数说明
　　如果要使用函数RAND生成一随机数，并且使之不随单元格计算而改变，可以在编辑栏中输入“=RAND()”，保持编辑状态，然后按F9，将公式永久性地改为随机数。

　　在本例中，使用公式：=INT(RAND()*1000)，即是生成一个大于0及小于1000的整数。
　　如果要生成一个规定范围内的随机数，可以使用公式：=RAND()*(上限-下限)+下限

 步骤03 开始生成数据透视表
     选择B2:D32中的任意单元格，然后在“插入”选项卡中选择“数据透视表”里的“数据透视表”，开始生成数据透视表。

步骤04 使用向导进行设置
    这时会弹出一个对话框，本例中不必改动任何选项。在本例中数据来源就是我们的工作表，而数据透视表生成于新的工作表中。 

步骤05 设置布局
　　在这一步我们要在新生成的工作表中设置数据透视表的布局形式，比较简单的方式就是直接在窗口中拖动字段名称到相应的区域。 

步骤06 生成数据透视表
　　通过上面的一系列设置，我们得到一张数据透视表，表中显示了材料和产品的汇总数据。 

步骤07 增加数据透视图
　　有了数据透视表，但我们希望更加直观地查看数据，所以我们可以增加一张数据透视表，方法跟之前生成柱形图的方法是一样的。 

步骤08 生成数据透视图
　　通过上面一个简单的步骤，我们就得到了一张数据透视图，不过我们还需要对图形做一些细节的调整。 

步骤09 设置数据标签
　　现在我们要对柱形图的数据标签进行设置，以方便查看。方法前面已经讲过，在“布局”选项卡中选择“数据标签”下的“其他数据标签选项”。 

步骤10 显示系列名称
　　在弹出的对话框中，我们选择显示系列名称，这样柱形图上就会直观地显示材料的类型。

步骤11 完成设置
　　做完以上的步骤，我们就得到了最终的结果。实际上，本节的例子除了使用数据透视表，我们也可以使用公式来得到相同的结果，这里我们就不再赘述了，有兴趣的朋友可以自己研究一下。 

第4节、企业成本|分析表|分解表

　　成本分析中有很多常用的表，本章将会看到其中一些。上一章我们讲了本量利分析，本量利分析要求将企业成本划分为变动和固定成本，在成本项目中很多项目既有固定成分，又有变动成分，所以需要将其分解。

材料成本汇总表效果图

 
采购成本分析表效果图

如何准确将成本中的固定成分和翻去成分分离是做好本量利分析的关键，一些成本项目的性质比较明确，可以直接划分为固定成本或变动成本，但是有的则比较模糊。比如电费，虽然电费与产量有关，但是产量为零时电费却并不为零，这说明电费中既有变动成分，又有固定成分。

成本分解表效果图

步骤01   新建工作表
新建一个工作簿，并在工作簿中重命名其中一个工作表，删除其他的。输入表格的标题、数据，并设置格式等。这个工作表中包括了产量和电费的数据。

步骤02   生成散点图
　　选中C2:D14区域，使用上一章介绍的方法插入一个散点图，这次我们要选择的是一个没有线的默认散点图。 

步骤03   对散点图进行调整
　　对于生成的散点图我们进行一些小的调整，以便最终效果更加便于查看。首先我们可以删除图例，因为现在只有一个系列。另外，我们还可以修改一下散点图的标题

步骤04   设置轴标题
　　如图所示，从“布局”选项卡中选择“坐标轴标题”下的横坐标及纵坐标项目可以添加轴标题，并设置相应的格式等。添加后可以直接对轴标题进行编辑。 

步骤05   给散点添加趋势线
　　右键单击散点图中的任意一个散点，从弹出的快捷菜单中选择“添加趋势线” 。

步骤06   趋势线的设置
　　在弹出的对话框中，选择“线性”，并勾选“显示公式”。

步骤07   分解成本
　　确定后生成最终的散点图。可以看到，在散点图上出现了线性趋势线和一个二元一次方程。方程中的截距代表固定成本，即505.4；斜率代表单位变动成本，即1.9942。 

知识点：趋势线
　　Excel的图表中，散点图、折线图、面积图、条形图、柱形图、股份图和气泡图中都可以添加趋势线，通常适合使用趋势线的的图表有两类，一是成对的数字数据，即典型的散点图中使用的数据；二是基于时间的数据，比如折线图、散点图、面积图等使用的数据。另外，除了线性趋势线，Excel还提供了非线性趋势线：
　　* 对数：当数据增加或减少的变化速率非常大，然后很快变得平缓时使用。
　　* 多项式：数据规则波动时使用，可以根据数据的波动规律制定多项式的阶数。
　　* 乘幂：数据按照固定的速率增加时使用，此时数据不能为零值或负数。
　　* 指数：数据以递增或者递减的趋势变化时使用，数据同样不能为零或负数。
　　* 移动平均：不是真正的趋势线，它是原数据按照指定的项数不高平均值。使用移动平均时要设定移动平均的项数。
　　在勾选“显示公式”后，图形上会显示线性或非线性的趋势线对应的公式。
　　在勾选“显示R平方值”后，图形上会显示模型的拟合系数。一般情况下，拟合系数越大，趋势线和原数据的拟合程度越好；反之则越差。

第12章　Excel 销售分析

第1节、销售预测相关函数

    TREND函数
　　返回一条线性回归拟合线的值。即找到适合已知数组known_y's和known_x's的直线（用最小二乘法），并返回指定数组new_x's在直线上对应的y值。
　　函数语法TREND(known_y's,known_x's,new_x's,const)Known_y's是关系表达式y=mx+b中已知的y值集合。
　　*如果数组known_y's在单独一列中，则known_x's的每一列被视为一个独立的变量。
　　*如果数组known_y's在单独一行中，则known_x's的每一行被视为一个独立的变量。Known_x's是关系表达式y=mx+b中已知的可选x值集合。
　　*数组known_x's可以包含一组或多组变量。如果仅使用一个变量，那么只要known_x's和known_y's具有相同的维数，则它们可以是任何形状的区域。如果用到多个变量，则known_y's必须为向量（即必须为一行或一列）。
　　*如果省略known_x's，则假设该数组为{1,2,3,...}，其大小与known_y's相同。
　　New_x's为需要函数TREND返回对应y值的新x值。
　　*New_x's与known_x's一样，对每个自变量必须包括单独的一列（或一行）。因此，如果known_y's是单列的，known_x's和new_x's应该有同样的列数。如果known_y's是单行的，known_x's和new_x's应该有同样的行数。
　　*如果省略new_x's，将假设它和known_x's一样。
　　*如果known_x's和new_x's都省略，将假设它们为数组{1,2,3,...}，大小与known_y's相同。
　　Const为一逻辑值，用于指定是否将常量b强制设为0。
　　*如果const为TRUE或省略，b将按正常计算
　　*如果const为FALSE，b将被设为0（零），m将被调整以使y=mx。
　　函数说明*可以使用TREND函数计算同一变量的不同乘方的回归值来拟合多项式曲线。例如，假设A列包含y值，B列含有x值。可以在C列中输入x^2，在D列中输入x^3，等等，然后根据A列，对B列到D列进行回归计算。
　　*对于返回结果为数组的公式，必须以数组公式的形式输入。
　　*当为参数（如known_x's）输入数组常量时，应当使用逗号分隔同一行中的数据，用分号分隔不同行中的数据。
　　FORECAST函数
根据已有的数值计算或预测未来值。此预测值为基于给定的x值推导出的y值。已知的数值为已有的x值和y值，再利用线性回归对新值进行预测。可以使用该函数对未来销售额、库存需求或消费趋势进行预测。
　　函数语法FORECAST(x,known_y's,known_x's)X为需要进行预测的数据点。
    Known_y's为因变量数组或数据区域。
　　Known_x's为自变量数组或数据区域。
　　函数说明*如果x为非数值型，函数FORECAST返回错误值#VALUE!。
　　*如果known_y's和known_x's为空或含有不同个数的数据点，函数FORECAST返回错误值#N/A。
　　*如果known_x's的方差为零，函数FORECAST返回错误值#DIV/0!。

第2节、销售预测数据表制作步骤

步骤01创建历史数据表
　　新建一个工作簿，并在工作簿中重命名其中一个工作表，删除其他的。输入表格的标题、数据，并设置格式等。 

步骤02添加折线图
　　选中B2:C13单元格，在“插入”选项卡中选择“折线图”里的“带数据标记的折线图”。

步骤03调整X轴
　　删除图形中的月份系列，通过“数据选择”中的水平轴数据源编辑，选择B3:B13为X轴数据源。还可以顺便给X和Y轴加上标题。 

步骤04调整坐标轴的刻度

　　选中折线图，在“布局”选项卡中选择“坐标轴”下关于Y轴的相关项目，对Y轴的刻度进行设置。

　　步骤05调整坐标
　　上下限在弹出的对话框中，进行如图中的相关设置，主要是调整Y轴的上下限值。 

步骤06设置数据系列格式
　　选中任意数据点，单击鼠标右键，选择“设置数据系列格式”。 

步骤07设置点和线的颜色
　　在弹出的对话框中，对数据系列的点和折线的颜色进行设置。还可以对线型以及线宽等进行设置。 

步骤08完成折线设置
　　单击“关闭”，完成对折线的设置，现在我们已经得到了已经设置好的折线图。 

步骤08完成折线设置
　　单击“关闭”，完成对折线的设置，现在我们已经得到了已经设置好的折线图。 

步骤09添加趋势线
　　鼠标右键单击折线，在弹出的菜单中选择“添加趋势线”。 

步骤10趋势线设置
　　在弹出的对话框中，选择“线性”，并勾选“显示公式”。

步骤11完成趋势线设置
　　单击“关闭”完成对趋势线的设置，得到新的图形。 

步骤12使用TREND函数预测销售量
　　在B17单元格输入“函数法”。
　　在C17单元格输入“预测方法一：使用TREND函数预测12月份销售量”。
　　在C18单元格输入公式：=TREND(C3:C13,B3:B13,12)

第3节、销售利润数据表中添加销售利润变化图

步骤01选择数据源
　　选中W2:AA2和W13:AA13两个区域，在“插入”选项卡中选择“柱形图”里的“簇状柱形图”。

步骤02生成柱形图
　　经过上一步，就可以得到一个柱形图。 

步骤03进行一些微调
　　我们可以对柱形图进行一些微调，比如删除图例、添加标题等。 

步骤04按正负设置颜色
　　在柱形图中把正负按不同的颜色区分开比较容易分辨，我们可以双击负值的数据点，单击鼠标右键，选择“设置数据点格式”，在弹出的对话框中进行设置。

 步骤05最终结果
　　经过复制的步骤，我们终于得到了最终的结果。

第4节、创建增减变化数据表

接下来我们要制作单位成本增减变化数据表。这个部分将要使用到的公式：

利润变化＝本年实际总利润－上年同期总利润
销售影响＝（本年实际销量－上年同期销量）×上年同期单位利润
售价影响＝（本年实际售价－上年同期售价）×本年实际销量
税金影响＝（上年同期单位税金－本年实际单位税金）×本年实际销量
成本影响＝（上年同期单位成本－本年实际单位成本）×本年实际销量
　　品种影响，若上年同期销售数量为0，等于本年实际利润；若本年实际销售量为0，等于上年同期利润的负值。

增减变化数据表效果图

步骤01 设置增减变化数据表
　　我们紧接单位成本表输入增减变化数据表的标题，并设置格式等。 

步骤02 计算利润变化
　　在V3单元格输入公式：=U3-P3，并复制公式到V4:V9。

步骤03 计算销量影响
　　在W3单元格输入公式：=IF(OR(B3=0,G3=0),0,ROUND((G3-B3)*O3,2))，并复制公式到W4:W9。 

步骤04 计算售价影响
　　在X3单元格输入公式：=IF(OR(B3=0,G3=0),0,ROUND((Q3-L3)*G3,2))，并复制公式到X4:X9。 

步骤05 计算税金影响
　　在Y3单元格输入公式：=IF(OR(B3=0,G3=0),0,ROUND((M3-R3)*G3,2))，并复制公式到Y4:Y9。

步骤06 计算成本影响
　　在Z3单元格输入公式：=IF(OR(B3=0,G3=0),0,ROUND((N3-S3)*G3,2))，并复制公式到Z4:Z9。

步骤07 计算品种影响
　　在AA3单元格输入公式：=IF(B3=0,U3,IF(G3=0,-P3,0))，并复制公式到AA4:AA9。 

步骤08 计算总量变化
　　在V13单元格输入公式：=SUM(V3:V9)，并复制公式到W13:AA13。

步骤09 设置零值不显示
　　选中整个数据表区域，打开Excel选项窗口，选择“高级”选项卡，将“在具有零值的单元格中显示零”的勾去掉。 

步骤10 完成设置
　　通过上面的设置，数据表中的零值就不会显示了。 

知识点：

OR函数
　　在其参数组中，任何一个参数逻辑值为TRUE，即返回TRUE；任何一个参数的逻辑值为FALSE，即返回FALSE。

函数语法
　　OR(logical1,logical2,...)
　　Logical1,logical2,...是1到255个需要进行测试的条件，测试结果可以为TRUE或FALSE。

函数说明
　　参数必须能计算为逻辑值，如TRUE或FALSE，或者为包含逻辑值的数组或引用。

　　如果数组或引用参数中包含文本或空白单元格，则这些值将被忽略。如果指定的区域中不包含逻辑值，函数OR返回错误值#VALUE!。可以使用OR数组公式来检验数组中是否包含特定的数值。若要输入数组公式，请按Ctrl+Shift+Enter。

第5节、创建单位成本表

　　有了销售数据表，接下来可以制作各种产品上年同期和本年实际的单位成本表。

单位成本表效果图

步骤01 设置单位成本表
　　我们紧接销售数据表输入单位成本表的标题，并设置格式等。 

步骤02 计算上年同期单位成本各项指标
　　在L3单元格输入公式：=IF($B3=0,0,ROUND(C3/$B3,4))，复制公式到L3:O3区域。 

步骤03 计算上年总利润
　　在P3单元格输入公式：=ROUND(O3*B3,2)，复制公式到P4:P9区域。 

步骤04 计算本年同期单位成本各项指标
　　在Q3单元格输入公式：=IF(G3=0,0,ROUND(H3/$G3,4))，复制公式到Q3:T9区域。

步骤05 计算本年总利润
　　在U3单元格输入公式：=ROUND(T3*G3,2)，复制公式到U4:U9区域。 

步骤06 计算上年单位成本各项累计数
　　在L13单元格输入公式：=SUM(L3:L9)，复制公式到M13:P13区域。 

步骤07 计算本年单位成本各项累计数
　　在Q13单元格输入公式：=SUM(Q3:Q9)，复制公式到R13:U13区域。

第6节、销售分析表-创建销售数据表

　　作为企业经营的重要环节，销售的重要性不言而喻，销售利润是企业追求的目标。对销售利润及其影响因素的分析有着非常重要的作用.同样，对于销售情况的预测也是一项十分重要的工作，本章也会在这方面进行讲解。

销售数据表效果图

　　要创建销售数据表，计算销售利润时要使用公式：销售利润＝销售收入－销售成本－销售税金

步骤01 新建工作表
　　新建一个工作簿，并在工作簿中重命名其中一个工作表，删除其他的。输入表格的标题，并设置格式等。

步骤02 输入数据
　　在数据区域输入每个产品的相关数据。 

步骤03 计算上年产品销售利润
　　在F3单元格输入公式：=C3-D3-E3，并复制公式至F4:F9区域。 

步骤04 计算本年产品销售利润
　　在K3单元格输入公式：=H3-I3-J3，并复制公式至K4:K9区域。 

步骤05 计算上年累计数
　　在A13单元格输入“合计”；在B13单元格输入公式：=SUM(B3:B9)，并复制公式至C13:F13区域。 

步骤06 计算本年累计数
　　在G13单元格输入公式：=SUM(G3:G9)，并复制公式至H13:K13区域。

第13章　Excel 投资决策

第1节、投资决策表

　　进行任何项目的投资都需要一个回收期，即都有一个投人产出的过程，过程的长短就是投资回收期。项目的投资回收期是每个项目出资人最关心的指标，回收期的长短直接影响着投资人做出决策。

  投资决策表效果图

步骤01 新建工作表

　　新建一个工作簿，并在工作簿中重命名其中一个工作表，删除其他的。输人表格的标题，并设置格式等。

步骤02 输入数据
　　在数据区域输入相关数据。

步骤03累计净现金流量

　　在C4单元格输入公式：=SUM($C$3:C3)复制公式到D4:J4区域。

步骤04投资回收期的整数年份
　　在C6单元格输入公式：=MATCH(0,C4:J4,1)。

步骤05投资回收期的小数年份
　　在C7单元格输入公式：

 =INDEX(C4:J4,MATCH(0,C4:J4,1))*-1/INDEX(C3:J3,MATCH(0,C4:J4,1)+1)。

步骤06总的投资回收期
在C8单元格输入公式：=C6+C7

第2节、等额还款函数的应用

步骤01新建工作表
　　在上面的工作簿中再新建一个工作表，输入表格的标题，并设置格式等。

步骤02输入数据
　　在数据区域输入相关数据。
步骤03等额还款公式
　　在C5单元格输入公式：=PPMT(C4/12,1,C3*12,C2)。

　　知识点：

　　PPMT函数基于固定利率及等额分期付款方式，返回投资在某一给定期间内的本金偿还额。
　　函数语法PPMT(rate,per,nper,pv,fv,type)Rate为各期利率。
　　Per用于计算其本金数额的期数，必须介于1到nper之间。
　　Nper为总投资期，即该项投资的付款期总数.
　　Pv为现值，即从该项投资开始计算时已经入帐的款项，或一系列未来付款当前值的累积和，也称为本金。
　　Fv为未来值，或在最后一次付款后希望得到的现金余额，如果省略fv，则假设其值为零，也就是一笔贷款的未来值为零。
　　Type数字0或1，用以指定各期的付款时间是在期初还是期末。　　

　　*0或省略期末*1期初函数说明应确认所指定的rate和nper单位的一致性。例如，同样是四年期年利率为12%的贷款，如果按月支付，rate应为12%/12，nper应为4*12；如果按年支付，rate应为12%，nper为4。

第3节、年金终值函数应用

步骤01新建工作表
　　在上面的工作簿中再新建一个工作表，输入表格的标题，并设置格式等。

步骤02输入数据
　　在数据区域输入相关数据。

步骤03年金终值公式
　　在C5单元格输入公式：=FV(C3/12,C4,C2,,0)。

　　知识点：

FV函数基于固定利率及等额分期付款方式，返回某项投资的未来值。
　　函数语法FV(rate,nper,pmt,pv,type)Rate为各期利率。
　　Nper为总投资期，即该项投资的付款期总数。
　　Pmt为各期所应支付的金额，其数值在整个年金期间保持不变。通常，pmt包括本金和利息，但不包括其他费用或税款.如果省略pmt，则必须包括pv参数。
　　Pv为现值，或一系列未来付款的当前值的累积和。如果省略PV，则假设其值为零，并且必须包括pmt参数。
　　Type数字0或1，用以指定各期的付款时间是在期初还是期末。如果省略type，则假设其值为零。
　　*0期末*1期初函数说明应确认所指定的rate和nper单位的一致性。例如，同样是四年期年利率为12%的贷款，如果按月支付，rate应为12%/12，nper应为4*12；如果按年支付，rate应为12%，nper为4。
　　对于所有参数，支出的款项，如银行存款，表示为负数；收入的款项，如股息收

第4节、年金现值函数的应用

    步骤01新建工作表
新建一个工作簿，并在工作簿中重命名其中一个工作表，删除其他的。输入表格的标题，并设置格式等。
　　步骤02输入数据
在数据区域输入相关数据。步骤03年金现值公式
在C5单元格输入公式：=PV(C3/12,12*C4,C2,,0)。 

　　知识点：

　　PV函数返回投资的现值。现值为一系列未来付款的当前值的累积和。例如，借入方的借入款即为贷出方贷款的现值。

　　函数语法PV(rate,nper,pmt,fv,type)Rate为各期利率。例如，如果按10%的年利率借入一笔贷款来购买汽车，并按月偿还贷款，则月利率为10%/12（即0.83%）。可以在公式中输入10%/12、0.83%或0.008作为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，用以指定各期的付款时间是在期初还是期末。
　　*0或省略期末*1期初