作者：庄君,牛改芳等
出版社：电子工业出版社
格式: AZW3, DOCX, EPUB, MOBI, PDF, TXT
Excel财务管理与应用50例(含光盘1张)试读：
前言
Excel作为使用最为广泛的电子表格软件，它以操作简便、功能丰富和效率高而著称，非常适合对计算机要求不高的财务管理人员使用。本书以Excel2007为基础，通过实例，详细介绍了利用Excel在会计和财务管理中应用的一般方法。本书一共分为13章，按照会计流程和财务管理的知识要点，结合使用者可能会遇到的实际问题，根据具体实例讲述Excel在财务管理中的使用。第1章简单介绍Excel的基础知识，包括数据输入、数据分析、绘制图表和假设分析等；第2章介绍公式和函数的应用，包括引用方式、函数的使用方法和数组公式等；第3章介绍了现金日记账的编制方法，包括建立简易的现金日记账和对简易现金日记账进行数据分析，以及利用函数和公式来建立现金日记账的方法等；第4章讲述凭证和账簿的有关知识，包括凭证汇总表的设计、查询界面和辅助区域的设计、凭证查询，以及生成科目汇总表和账簿等；第5章介绍了应收款管理系统的设计，包括应收款信息及处理情况、应收款信息查询、图表分析和数据透视图分析等；第6章介绍工资管理，包括了和工资管理相关的基础表格的设计、利用基础表建立员工工资表，以及最终生成员工的工资条等；第7章介绍了进销存管理，包括建立进销存管理的基础表结构，并为这些基础表添加公式，查询数据和建立简易报表，还介绍了通过Microsoft Query来收集局域网内其他计算机的数据等；第8章介绍了固定资产管理系统，包括简历基础表结构以及为这些基础表添加公式并最终完成固定资产的查询等；第9章介绍了企业财务报告的生成，包括报表生成前的准备工作、利用函数生成资产负债表和利用Microsoft Query查询获得数据，并最终通过查询数据生成资产负债表等；第10章介绍了财务预算，包括销售预测、销售预算和生产预算、成本预算，以及现金预算等；第11章介绍了货币时间价值，包括终值计算、现值计算、普通和预付年金、其他年金，以及等额归还贷款和利率问题等；第12章介绍了流动资金管理和销售预测，包括现金管理、存货管理和销售预测方法等；第13章介绍了投资决策，包括静态评价指标、动态评价指标、不规则现金流量和修正内部收益率、评价互斥方案和项目现金流量计算等。本书实例典型，内容丰富，有很强的针对性。书中各章详细介绍了实例的具体操作步骤，读者只需按照书中介绍的步骤一步步地实际操作，就能完全掌握本书的内容。为了帮助读者更加直观地学习本书，我们将书中实例所涉及到的全部操作文件都收录到本书的配套光盘中，包括两大部分，即“data”文件夹和“video”文件夹。前者包含所有本书实例所使用的数据，其内容是按照书中的章节来组织的。后者收录了书中所有实例的操作录像文件，读者可以对应起来进行学习。录像文件是“*exe”格式的，双击文件名即可打开观看。本书由庄君、牛改芳等编著，参与本书编写和修改的还有张凌云、陈秀兰、杨光明、王梅、周俊、于海涛、王立华、李勇、徐浩、李建国、马建军、申士爱、许小荣等同志。在此，编者对以上人员致以诚挚的谢意！作者力图使本书的知识性和实用性相得益彰，但由于水平有限，书中错误、纰漏之处在所难免，欢迎广大读者、同仁批评斧正。我们的E-mail地址：qiyuqin@phei.com.cn编 者 2009年5月第1章 Excel概述Excel是财务工作人员使用最多的办公类软件之一，它是一个功能强大的电子表格应用软件，主要是以表格的方式来完成数据的输入、计算和分析，并能生成各种图表，连接数据库，进行数据处理和制作报表。案例1-1 数 据 输 入目的与要求（一）案例目的熟悉在Excel中输入数据，设置数据类型，设置数据有效性和条件格式的方法。（二）案例要求熟悉Excel2007的界面，了解Excel2007的各个组成部分。内容及数据来源在单元格中输入如下的内容，并为B列数据设置指定的样式。（1） 从A1单元格到A5单元格依次输入销售截止时间、销售数量、产品单价、总成本和利润。（2） 从B1单元格到B5单元格依次输入2006-1-2、10000、5.5、60000，利润所在的B5单元格为计算的结果。（3） 对于B1单元格最终要显示为“二〇〇六年一月二日”。（4） 销售数量为整数且输入的数据范围是最小值为10000，最大值为1000000。（5） 产品的单价5.5要求为会计专用格式，并保留为两位小数，显示人民币符号。（6） B4单元格总成本60000要求显示60000.00。（7） 在B5单元格内输入“=B2*B3-B4”，计算出利润的值，如果该值小于0就显示为蓝底红字。操作指导1. 输入数据（1） 打开Excel2007，选中“Sheet1”工作表。（2） 选中A1单元格，在A1单元格中输入内容“销售截止时间”，输入完成后用鼠标单击B1单元格，在单元格内输入内容“2006-1-2”，按Enter键确认，完成输入。（3） 选中B1单元格，右击鼠标，在弹出的快捷菜单中选择“设置单元格格式”命令，打开“设置单元格格式”对话框，如图1-1所示。在分类中选择“日期”，在类型中选择“二〇〇一年三月十四日”这种显示类型，单击“确定”按钮，完成日期单元格格式的设置。图1-1 单元格格式对话框（4） 选中A2单元格，在A2单元格中输入内容“销售数量”，输入完成后按键盘上向右方向键，将活动单元格移动至B2单元格，在B2单元格内输入内容“10000”，按Enter键确认，完成输入。（5） 选中B2单元格，打开“数据”选项卡，执行“数据工具”组内的“数据有效性”命令，打开数据有效性对话框，如图1-2所示。单击“设置”标签，在“允许”下拉列表框中选择“整数”，在“数据”下拉列表框中选择“介于”，最小值输入10000，最大值输入1000000，单击“确定”按钮，完成数据有效性的设定。图1-2 数据有效性对话框说明：作为一个真实的数据，销售数量只能是一个正数，不能是小数，并且销售数量总是在一定的合理范围内的，因此对于这样的数据可以设定数据有效性，确保用户输入的数据准确性。例如此处当输入数据9000时，会打开如图1-3所示的消息框。图1-3 错误消息如果要对某个单元格区域设置数据有效性，就必须事先将该区域选中。数据有效性对公式计算的结果没有作用，对于设置数据有效性之前就已经存在的数据也不会产生约束力。（6） 选中A3单元格，在A3单元格中输入内容“产品单价”，输入完成后按键盘上向右方向键，将活动单元格移动至B3单元格，在B3单元格内输入内容“5.5”，按Enter键确认，完成输入。（7） 选中B3单元格，右击鼠标，在弹出的快捷菜单中选择“设置单元格格式”命令，打开“设置单元格格式”对话框，如图1-4所示。在“分类”中选择“货币”，小数位数选择“2”，在货币符号下拉列表框中选择“￥”，单击“确定”按钮，完成对单价数字格式的设置。图1-4 设置单元格格式对话框（8） 选中A4单元格，在A4单元格中输入内容“总成本”，输入完成后按键盘上向右方向键，将活动单元格移动至B4单元格，在B4单元格内输入内容“60000”，按Enter键确认，完成输入。（9） 选中B4单元格，打开“开始”选项卡，单击“数字”组上的“增加小数位数”按钮，一直到出现“60000.00”为止。（10） 选中A5单元格，在A5单元格中输入内容“利润”，输入完成后按键盘上向右方向键，将活动单元格移动至B5单元格，按键盘上的等于号“=”，然后用鼠标单击B2单元格，再按键盘上的乘号“*”，再用鼠标单击B3单元格，再按键盘上的减号“-”，最后用鼠标选择B4单元格，完成单元格公式“=B2*B3-B4”的设置，按Enter键确认，完成公式输入。说明：在Excel2007中公式必须用“=”引出。（11） 选中B5单元格，打开“开始”选项卡，执行“样式”组内的“条件格式”下的“突出显示单元格规则”下的“小于”命令，打开如图1-5所示的对话框。在“为小于以下值的单元格设置格式”下的文本框内输入数值0，将格式设置为下拉列表框中选择“自定义”，打开“设置单元格格式”对话框，如图1-6所示。在字体中设置字颜色为红色，单击“填充”标签，将“背景色”设置为浅蓝色，单击“确定”按钮，返回设置小于条件格式对话框，再次单击“确定”按钮，完成条件格式的设置。图1-5 条件格式对话框图1-6 单元格格式对话框2. 复制数据如果要输入的数据已经存在，则可以不直接输入，而是通过复制粘贴的方法将已经存在的数据放置到单元格中。复制数据通常用下述的方法：● 组合键的方式：按Ctrl+C组合键表示复制，按Ctrl+V组合键表示粘贴。● 右键的方式：选中对象右击执行复制命令，然后选中要粘贴数据的起始位置，右击鼠标，在弹出的快捷菜单中选择粘贴命令。● 选项卡方式：在“开始”选项卡的“剪贴板”中选择复制粘贴命令。上述的方法可以混合使用。即用任意一种方法复制数据，用任意一种方法粘贴数据。（1） 选中A1单元格，在单元格内输入“复制数据”，按Enter键确认。选择A1单元格，按键盘上的Ctrl+C组合键，完成数据的复制。（2） 选中A2单元格，按键盘上的Ctrl+V组合键，完成数据的粘贴。（3） 选中A3单元格，按Enter键，完成向A3单元格内粘贴数据。说明：通过上述方法复制和粘贴数据，当数据处于可复制状态的时候，该单元格会显示一个虚线的边框，如图1-7所示。图1-7 可粘贴数据状态按Ctrl+V组合键进行粘贴，粘贴完成后，A1单元格仍旧处于虚线框的状态，表示此时仍旧可以粘贴数据。当在A3单元格中按Enter键粘贴后，A1单元格的虚线框消失，表示此时已经无法继续进行粘贴。3. 填充数据使用快捷键进行复制粘贴的方法还是太慢，有的时候需要快速的填充大量的数据，此时可以使用填充数据的方法。具体操作步骤如下所示。（1） 选中A1单元格，在单元格内输入“填充数据”，按Enter键确认，选择A1单元格，将鼠标移动到A1单元格的右下角，当鼠标的形状变成如图1-8所示的细黑十字的时候，按住鼠标左键向下拖动到A4单元格，完成单元格内容的填充。图1-8 鼠标位于单元格右下角时的形状（2） 选中B1单元格，在单元格内输入“填充数据”，按Enter键确认，选择B1单元格，将鼠标移动到B1单元格的右下角，当鼠标的形状成如图1-9所示的细黑十字的时候，双击鼠标，即可完成数据的填充。说明：用上述方法填充数据是有限制的，也就是说填充的行数最终和左侧的行数相同。这种方法特别有利于连续多行数据的快速填充。图1-9 双击复制数据（3） 选中C1单元格，在C1单元格内输入数值1，选中C1单元格，将鼠标移动到C1单元格的右下角，当鼠标的形状成为细黑十字的时候，按住鼠标右键向下拖动到C4单元格，放开鼠标打开如图1-10所示的快捷菜单，执行“填充序列”命令，完成C1:C4单元格内容按照序列的填充。图1-10 快捷菜单说明：此处的填充序列的方式和一般的按住鼠标左键不同，在这里起作用的是鼠标右键。（4） 选中D1单元格输入数据1，选中D2单元格输入数据2，选中D1:D2单元格区域，将鼠标移动到D2单元格的右下角，当鼠标的形状成为细黑十字的时候，按住鼠标左键不放向下拖动，完成数据的填充。 说明：输入两个数据的目的是明确两个数据之间的间隔大小，例如在D1中输入1，在D2中输入4，那么用上述方法填充，D3单元格的数据就是7，D4单元格的数据就是10。（5） 选中E1单元格输入数据1，选中E2单元格输入数据2，选中E1:E2单元格区域，将鼠标移动到E2单元格的右下角，当鼠标的形状成为细黑十字的时候，双击鼠标左键，完成数据的填充。说明：上述两种填充的效果是一致的。使用双击可以填充的行数和左侧的一列的行数有关。案例1-2 数 据 分 析目的与要求（一）案例目的掌握在Excel中进行排序、筛选、分类汇总和使用数据透视表的方法。（二）案例要求掌握数据清单的含义，能够按照数据清单的要求组织数据。内容及数据来源某股票从2003年1月1日到2003年1月31日所有具有成交数量的股票交易信息，如图1-11所示。图1-11 基础数据要求根据上表所提供的信息进行如下的操作：（1） 首先将数据按照日期的降序进行排序。（2） 按照主关键字星期降序，次要关键字日期升序和第三关键字成交量升序的方式进行排序。（3） 通过自动筛选查找所有星期四的记录，筛选完成后删除筛选结果。（4） 利用高级筛选功能，筛选出1月中星期四成交量大于100万的，或者是星期五收盘价大于7元的记录。（5） 通过分类汇总的方式计算一个星期中的每一天的平均成交量。（6） 通过数据透视表计算一个星期中的每一天的平均成交量。操作指导1. 数据排序用户对数据排序的对象通常是数据清单。所谓数据清单就是工作表中数据的排列方式类似数据库中表，第一行是字段名，从第二行开始是记录。数据清单中不能存在空行。（1） 打开Excel2007，在工作表中输入上图所示的数据。（2） 在工作表中选中A列任意一个有数据的单元格，打开“数据”选项卡，执行“排序和筛选”组中的降序排序命令，对上表所示信息按照日期从大到小的顺序进行排列。说明：排序的对象通常是数据清单，但并不意味着不是数据清单就无法排序。通常用户的记录是比较规则的，但是用户的表头未必是严格的字段名的样式，可能会有多行，如果出现这样的情况，可以选择表头的最末行，再打开“数据”选项卡，执行“排序和筛选”组中的降序排序命令。（3） 选中数据清单中任意一个单元格，打开“数据”选项卡，执行“排序和筛选”组中的“排序”命令，打开“排序”对话框，如图1-12所示。在“主要关键字”下拉列表框中选择“星期”按降序排列，单击“排序”对话框上的“添加条件”按钮，在“次要关键字”下拉列表框中选择“日期”按升序排列，在下一个“次要关键字”下拉列表框中选择“成交量”按升序排列，单击“确定”按钮，完成对排序条件的设置。图1-12 排序对话框说明：多个关键字排序的时候，首先按照主要关键字排序，在首要关键字相同的情况下按照次要关键字排序，在首要关键字和次要关键字都相同的情况下按照第三关键字排序。排序的时候Excel默认数据清单是有标题行的，标题行不会参与排序。排序后的结果部分内容，如图1-13所示。图1-13 排序的结果部分2. 自动筛选（1） 选中数据清单中任意一个包含数据的单元格，打开“数据”选项卡，执行“排序和筛选”组中的“筛选”命令，完成自动筛选的设置，如图1-14所示。图1-14 设置自动筛选（2） 单击H1的下三角按钮，选择4，则显示所有星期四的交易记录。（3） 打开“数据”选项卡，再次执行“排序和筛选”组中的“筛选”命令，完成取消自动筛选状态的设置。3. 高级筛选假设要筛选1月中星期四成交量大于100万的，或者是星期五收盘价大于7元的记录，操作步骤如下。（1） 在A23:C25单元格区域中输入如图1-15所示的条件，完成高级筛选条件的设置。 图1-15 设置高级筛选条件说明：高级筛选条件中的字段名要和数据清单中的字段名完全一致，最好是从数据清单中将字段名复制过来。在同一行中各个条件之间的关系是并且的关系，例如在第24行，其条件就是表示要筛选出星期四并且交易量大于100万的记录，行与行之间的关系是或者的关系，整个条件区域的条件就是星期四成交量大于100万的，或者是星期五收盘价大于7元。（2） 选中数据清单中任意一个包含数据的单元格，打开“数据”选项卡，执行“排序和筛选”组中的“高级”命令，打开“高级筛选”对话框，如图1-16所示。在列表区域显示的是“A1:H21”单元额区域，单击条件区域后的折叠按钮，选择“A23:C25”单元格区域，完成高级筛选的设置，单击“确定”按钮，将在原来的数据清单区域显示筛选结果。图1-16 高级筛选对话框说明：在数据清单中任意选择一个单元格，那么打开高级筛选对话框的时候，Excel会自动选择将选中单元格所在的数据清单作为列表区域。经过高级筛选后的结果如图1-17所示。图1-17 高级筛选结果（3） 打开“数据”选项卡，再次执行“排序和筛选”中的“清除”命令，完成取消筛选的结果。4. 分类汇总一个字段的值通常有重复的，这些重复的值就构成了很多类，例如对于星期这个字段，就会有几个重复的值。分类汇总能够针对其中的每一天进行汇总。分类汇总在操作之前要进行排序操作。（1） 选中H列中任意一个有数据的单元格，打开“数据”选项卡，执行“排序和筛选”组内的升序命令，完成将数据清单按照星期的顺序从小到大的进行排列。说明：分类汇总之前要对分类的字段进行排序。（2） 打开“数据”选项卡，执行“分级显示”中的“分类汇总”命令，打开“分类汇总”对话框，如图1-18所示。在“分类字段”下拉列表框中选择“星期”字段，在“汇总方式”下拉列表框中选择“平均值”的汇总方式，在“选定汇总项”中勾选“成交量”复选框，单击“确定”按钮，完成分类汇总。图1-18 分类汇总对话框说明：分类汇总之后的结果如图1-19和图1-20所示。图1-19 分类汇总结果图1-20 分类汇总结果（3） 打开“数据”选项卡，执行“分级显示”中的“分类汇总”命令，打开“分类汇总”对话框，单击“全部删除”按钮，完成取消“分类汇总”。5. 数据透视表数据透视表是一种快速高效的分析方法，它是一个非常复杂的工具，本案例仅仅介绍了最简单的使用方法。通过数据透视表能够快速的计算一个星期中的每一天的平均成交量。使用数据透视表之前不需要对数据进行排序操作，用户只需要保证所分析的数据表是一个数据清单即可。具体的操作步骤如下所示：（1） 选中数据清单中任意一个单元格，打开“插入”选项卡，执行“表”组中的“数据透视表”命令，打开“创建数据透视表”对话框，选择A1:H21单元格区域，如图1-21所示。单击“确定”按钮，完成数据源区域设置。图1-21 数据区域选择（2） 在如图1-22所示的“数据透视表字段列表”对话框中，将“星期”字段拖动到行标签内，将“成交量”字段放置到数值区域，完成对数据透视表布局设计。说明：在上图中行标签的含义就是将星期这个字段中不同的值放在行上，实际上是不同的星期数据形成了一列。同理列标签的含义就是该字段中所有不同的值放在列上，不同的数据形成了一行，最终的结果如图1-23所示。要注意的是用户生成的数据透视表不一定和下图所示的完全一致，但是其操作方法是一样的。图1-22 数据透视表字段列表图1-22 数据透视表结果（3） 双击“求和项:成交量”所在的单元格，打开如图1-24所示的“值字段设置”对话框，将计算类型更改为“平均值”，单击“确定”按钮，完成对计算类型的更改。图1-24 数据透视表字段对话框使用数据透视表的最终结果如图1-25所示，该结果与分类汇总的计算结果是一致的。图1-25 数据透视表计算结果案例1-3 图表的绘制目的与要求（一）案例目的掌握在Excel中利用图表向导绘制图表的方法。掌握在Excel中选择图表对象的方法，编辑图表对象格式。掌握次坐标轴的设置方法。（二）案例要求了解数据清单的含义以及图表各部分的名称。内容及数据来源假设在某个月中星期一到星期五的平均成交量情况如表1-1所示。表1-1 某个月中星期一到星期五的平均成交量 要求根据上表进行以下的操作（1） 绘制平均成交量图表。（2） 为图表添加平均成交价并最终删除新添加的数据系列。（3） 为平均成交价设置一个次坐标轴。（4） 将平均成交量的图表类型由XY散点图更改为柱形图。操作指导1. 绘制平均成交量图表（1） 打开Excel2007，从A1单元格开始在工作表中输入案例内容中所示的表格数据，完成图表对象的数据输入。（2） 选中数据清单外的任意一个单元格，打开“插入”选项卡，单击 “图表”组内右下角的启动对话框按钮，打开如图1-26所示的“插入图表”对话框，选择左侧“XY（散点图）”，选择“带平滑线和数据标记的散点图”，单击“确定”按钮，完成图表类型的选择。图1-26 选择图表类型说明：如果在绘制图表之前选中数据清单中任意一个单元格，那么程序会自动为图表设置一个区域；如果选中数据清单外任意一个单元格，那么就需要用户自行指定图表所代表的数据区域。（3） 打开“设计”选项卡，执行“数据”组中的“选择数据”命令，打开如图1-27所示的“选择数据源”对话框，单击“图表数据区域”后的折叠按钮，选择A1:B6单元格区域，单击“确定”按钮，完成数据源区域的设置。图1-27 选择数据源最后完成的图表，如图1-28所示。图1-28 图表2. 添加和删除数据系列图表是用图形的方式表示数据，这样每个字段的数据就构成了一个数据系列，数据系列实质上是数据源在图表中的链接。图表中数据系列是可以变动的，变动的方法通常有以下两种：● 将数据源中的某一列数据选中，直接将该列数据拖到图表中。● 将某一列数据复制，然后选中图表，将该列数据粘贴到图表中，完成向图表中添加数据系列。数据系列也可以删除，选中图表中多余的数据系列，按键盘上的Delete键直接删除。由于图表和数据源只是链接关系，从图表中删除数据系列不过是删除了数据的链接关系，使得该列数据不在图表中显示而已，对于数据本身是没有影响的。（1） 选中C1:C6单元格区域，按键盘上的Ctrl+C组合键，然后单击图表，按键盘上的Ctrl+V组合键，向图表中粘贴数据，完成新系列的导入。说明：由于图表经过上述设置，平均成交价相对于平均成交量来说数值太小，添加之后几乎看不到平均成交价数据系列的位置，但是可以从图表工具栏或者图例中看出已经成功添加数据系列。（2） 打开“布局”选项卡，在“当前所选内容”下拉列表框中选择“系列平均成交价”选项，按键盘上的Delete键，完成从图表中删除数据系列。3. 设置次坐标轴用户在上述案例中拖入的“系列平均成交价”因为数值太小，几乎无法显示，因此就要考虑用两个坐标轴。可以在图表中设置两条垂直的Y轴，第二条Y轴就称之为次坐标轴，次坐标轴可以有和主坐标轴不同的刻度和含义。（1） 选中C1:C6单元格区域，按键盘上的Ctrl+C组合键，然后单击图表，按键盘上的Ctrl+V组合键，向图表中粘贴数据，完成新系列的导入。（2） 打开“布局”选项卡，从“当前所选内容”组中的下拉列表框中选择“系列平均成交价”选项，右击图表中选中的系列，在弹出的快捷菜单中选择“设置数据系列格式”命令，打开“设置数据系列格式”对话框，如图1-29所示。单击“系列选项”标签，选择“系列绘制在”下的“次坐标轴”单选按钮，单击“关闭”按钮，完成次坐标轴的设置。试读结束[说明：试读内容隐藏了图片]