Excel在财务分析中的应用

1、 Excel 2007在财务分析中的应用杨晓红 滕小青 朱雄才 编著目 录项目1 Excel 2007高级应用11-1公式1-1-1公式概述1-1-2公式的基本操作与引用1-1-3 单元格引用1-1-3-1 A1引用样式和R1C1引用样式1-1-3-2 相对引用、绝对引用和混合引用1-1-3-3 其他引用工作表区域的方式1-1-4 公式的错误与审核1-2函数1-2-1 函数概述1-2-2 常见的函数1-3数据透视表1-3-1 创建数据透视表1-3-2数据透视表操作1-3-3 数据透视图1-4 Excel 2007宏及应用1-4-1宏安全性1-4-2 录制宏1-4-3 执行宏项目2 Excel

2、2007在创建财务单据和内部报表中的应用232-1创建财务单据2-1-1创建差旅费报销单2-1-2创建材料或产品入库单2-1-3创建材料或产品出库单2-2创建内部报表2-2-1销售收入明细表2-2-2材料发出汇总表2-2-3生产成本明细表项目3 Excel 2007在会计账务处理中的应用313-1记账凭证的填制3-1-1建立会计科目与会计科目代码3-1-2记账凭证单的填制3-1-3记账凭证的填制3-1-4记账凭证的查询3-2明细账的处理3-2-1建立明细账科目表单3-2-2计算本月发生额与月末余额3-3总账的处理3-3-1建立总账科目表单3-3-2建立总账表单3-3-3计算本月总账科目发生额与

3、月末余额3-4编制试算平衡表3-4-1建立试算平衡表3-4-2试算平衡表的应用项目4 Excel 2007工资管理中的应用394-1创建企业工资管理系统4-1-1规划工资明细表构成4-1-2建立工资表基本数据4-1-3从各记录中录入工资数据到工资表4-1-5计算应发工资、各项扣款和实发工资4-2工资数据的统计分析4-2-1利用分类汇总统计数据4-2-2利用数据透视表统计数据4-3利用VLOOKUP函数制作工资条项目5 Excel 2007在财务报表中的应用515-1创建资产负债表5-1-1创建资产负债表基本表格5-1-2添加“总账表”到“财务报表处理”工作簿中。5-1-3计算“总账表”中的“期

4、末余额汇总”。5-1-4添加“明细账表”到“财务报表处理”工作簿中。5-1-5从“总账”“明细账表”工作表中导入数据到“资产负债表”中。5-2创建利润表5-2-1创建利润表基本表格5-2-2设置表中各项目间关系。5-2-3将数据导入利润表。5-3创建现金流量表5-4财务报表分析5-4-1资产负债表结构分析5-4-2利润表结构分析5-5财务指标分析5-2-1偿债能力指标5-2-2营运能力指标5-2-3获利能力指标项目6 Excel 2007在固定资产管理中的应用766-1建立固定资产初始数据库6-1-1固定资产清单的建立6-1-2固定资产的增加与减少5-1-3固定资产的查询6-2固定资产的折旧函

5、数及计提6-2-1固定资产的折旧相关函数6-2-2直线法6-2-3双倍余额递减法6-2-4年数总和法6-2-5计算各项固定资产已提折旧6-3固定资产自动查询与自动计算年折旧额、月折旧额6-3-1固定资产自动查询6-3-2自动计算年折旧额、月折旧额项目7 Excel 2007在货币时间价值计算中的应用927-1货币时间价值的基本问题7-1-1单利的现值和终值7-1-2复利终值和现值7-1-3名义利率和实际利率7-2年金终值和现值7-2-1普通年金7-2-2预付年金7-2-3递延年金7-2-4永续年金7-3不规则现金流的终值和现值计算7-4 Excel 2007在货币时间价值计算中的综合应用7-4

6、-1投资回收期7-4-2净现值7-4-3内部收益率4项目1 Excel 2007高级应用公式与函数1-1 公式1-1-1 公式概述公式：就是由用户自行设计并结合常量数据、单元格引用、运算符等元素进行数据处理和计算的算式。1-1-2 公式的基本操作与引用当用户输入公式时，通常以“=”作为开始，否则excel只能作为文本处理。如果在某个连续的区域使用相同算法的公式，用户可用通过“双击”或“拖动”单元格右下角的填充柄进行公式的复制；如果公式所在单元格区域并不连续，则可以借助“复制”和“粘贴”功能实现公式的复制。例1：如果5月份的销售表结构与4月份完全相同，使用“选择性粘贴”功能实现不连续区域的公式复

7、制，销售表如图1-1所示。方法如下： 图1-1步骤一：选中E4单元格，按“CTRL+C”复制公式；步骤二：选择H4：H9单元格区域，单击鼠标右键，在弹出的菜单中选择；步骤三：在“选择性粘贴”对话框的“粘贴”类型下选择“公式”选项，单击“确定”，即可完成不带格式的公式复制。1-1-3 单元格引用引用单元格是EXCEL作为电子表格软件中的一个重要特征，是EXCEL函数与公式的构成要素之一。1-1-3-1 A1引用样式和R1C1引用样式EXCEL中可设置二种引用样式，A1引用样式和R1C1引用样式。1A1引用样式在默认的情况下，EXCEL使用A1引用样式，该样式用165536表示行号，用AIV表示列

8、号。如用“D4”表示第4行与第4列交叉的单元格地址。同时，EXCEL还可对整行或整列进行引用，如6:6，则表示引用第6行整行，即A6：IV6；D:D 表示第D列整列。即D1:D655362R1C1引用样式在EXCEL2007中，单击“OFFICE”按钮“EXCEL选项”“公式”，在“公式”选项卡中，勾选“R1C1引用样式”，即可切换为R1C1引用模式。如图1-2所示。图1-2R1C1引用样式是一种特殊的引用模式，从引用形式的表达看，R1C1引用不仅可以标记单元格的绝对位置，还能标记单元格的相对位置。如第4行第5 列单元格的地址可用：“R4C5”表示。而图1-3所示的销售金额的计算为：R4C5=

9、RC-2* RC-1，与A1引用样式的E4=C4*D4效果相同。图1-31-1-3-2 相对引用、绝对引用和混合引用当公式中使用单元格地址引用时，根据引用方式的不同分为3种引用方式，即相对引用、绝对引用和混合引用。1相对引用和绝对引用相对引用：复制公式时，EXCEL根据目标单元格与源公式所在单元格的相对位置，相应地调整A1样式下公式的引用标识，而R1C1样式下的引用标识则保持不变，但两者所引用的单元格都发生了改变。如在图1-3表中，销售金额=单价*销售数量，利用A1引用样式，则E4=C4*D4，把该公式复制到E5，则公式变为E5=C5*D5；而利用R1C1引用样式，则R4C5= RC-2* R

10、C-1，把该公式复制到R5C5，则公式为R5C5= RC-2* RC-1。绝对引用：复制公式时，不论目标单元格所在位置如何改变，绝对引用所指向的单元格区域都不会改变。例如：在图1-4所示表中，利润=销售金额*利润率。利润率值必须绝对引用。利用A1引用样式，J5=I5*$J$2，把该公式复制到是否J6，则公式变为J6=I6*$J$2，如图1-4所示；而利用R1C1引用样式，则R5C10= RC-1* R2C10，把该公式复制到R6C10，则公式为R6C10= RC-1* R2C10，如图1-5所示.在此例中，$J$2和R2C10均为绝对引用。图1-4图1-52.混合引用EXCEL中的混合引用有行

11、相对、列绝对引用和列相对、行绝对引用二种。行相对、列绝对引用：即仅在行方向上为相对引用，而在列方向上为绝对引用。列相对、行绝对引用：即仅在列方向上为相对引用，而在行方向上为绝对引用。例如：利用混合引用制作“九九乘法表”，见图1-6所示。方法如下（利用A1引用样式）：步骤一：在B2单元格中输入公式：=B$1&"*"&$A2&"="&B$1*$A2；步骤二：将B2公式向下和向右填充到J10即可。J10公式自动修改为：=J$1&"*"&$A10&"="&J$

12、1*$A10。若利用R1C1引用样式，则方法为：步骤一：在R2C2单元格中输入公式：=R1C&"*"&RC1&"="&R1C*RC1；步骤二：将R2C2公式向下和向右填充到R10C10即可。R10C10公式为：=R1C&"*"&RC1&"="&R1C*RC1。图1-6注意：（1）在A1引用样式中，不同的引用方式的区别在于：绝对引用和混合引用使用了美元符号（$）来锁定行号与列号，所有前置“$”的行号或列号在公式的复制过程中都保持不变，如$A$5和$D20

13、。（2）在R1C1引用样式中，相对引用中的行列号使用中括号“”进行包含，如RC-1，R1C2；而绝对引用直接使用数字进行表示，如R1C2和R2C23快速切换4种不同引用方式在相对引用、绝对引用和混合引用中，可手工输入$和，但也可以用功能键<F4>进行不同引用之间快速切换。方法是：先输入单元格引用，再按<F4>键，其切换顺序如下：绝对引用列相对行绝对引用行相对列绝对引用相对引用例：在A1单元格中引用B2为例：在A1引用样式中，输入公式=B2，按<F4>键的切换顺序为：$B$2B$2$B2B2在R1C1引用样式中，输入公式=R1C1，按<F4>键的切

14、换顺序为：R2C2R2C1R1C2R1C1 1-1-3-3 其他引用工作表区域的方式1.合并区域EXCEL除对单个单元格或多个连续单元格进行引用外，还支持对同一个工作表中不连续区域进行引用，通常称为“合并区域”。合并区域的写法是：以半角逗号“,”将各个区域的引用区隔开，并在两端添加半角括号“()”将其包括在内。如公式：=SUM(C5,(D7:E8,F9:H10)2.多工作表区域的引用若要在公式中引用同一个工作薄中其他工作表的单元格区域，EXCEL会自动在引用前添加工作表名，其引用格式为：“工作表名+半角感叹号+引用区域”。如：=SUM（Sheet!C2:E10）注：若工作表名中包含空格、特殊符

15、号（如%、&等）或其首字符为数字，则须用一对单撇号“”对其首尾进行标识，如=SUM（4月!C2:E10）3引用其他工作薄中的工作表若要在公式中引用其他工作薄中的工作表进行引用，其引用格式为：“工作薄名称工作表名+半角感叹号+引用区域”，如=SUM(BOOK1sheet1!A1:B5)1-1-4 公式的错误与审核掌握公式有一定的难度，这不仅因为公式除有自己的书写格式外，还有一些参数，容易出错，因而得不出正确的结果，返回一个错误值。常风的错误如下表：函数公式错误值列表错误类型含义#DIV/0在公式中出现了除以0的错误。#N/A在公式中引用的一些数据不可用#NAME?公式中的文字，系统不能识

16、别#NULL!指定的相交并没有发生#NUM!参数输入值不正确#REF!单元格引用无效#VALUE!提供的参数没有使用系统期望的数值类型#使用了负的日期或负的时间，或单元格中的数据太长或者公式中产生的结果值太大，以至于单元格不能将信息完全显示出来1-2 函数1-2-1 函数概述EXCEL的工作表函数通常被称为EXCEL函数，它由EXCEL内部预先定义并按照特定的顺序、结构来执行计算、分析等数据处理任务的功能模块。因此，EXCEL函数也叫特殊公式。一个函数只有唯一的一个名称，且不区分大小写，它决定了函数的功能和用途。EXCEL函数由函数名称、左括号，参数，半角逗号和右括号构成。如SUM(E1:H1

17、)。另有一些函数比较特殊，它仅由函数名和成对括号构成，无参数内容，如NOW函数。1-2-2 常见的函数EXCEL函数一般分为：l 信息函数l 文本和数据函数l 日期与时间函数l 数学与三角函数l 统计函数l 查找与引用函数l 工程函数l 财务函数1-3 数据透视表数据透视表是Excel最强的功能之一，可将简单的数据灵活应用，完成各种数据分析。1-3-1 创建数据透视表创建步骤：步骤一：打开光盘范例excel1-3-1.xls文件。如图1-7所示。图1-7 基本表步骤二：选定要分析的范围单击“插手”菜单“表”“数据透视表”“数据透视表”命令，打开如图1-8所示。图1-8步骤三：单击“确定”按钮，

18、则出现图1-9所示的“数据透视表字段列表”，以选择要显示的字段。图1-9步骤四：将所需字段分别拖动到“页字段”、“列字段”、“行字段”、“数据项”或“报表筛选”、“列标签”、“行标签”、“数值”等区域。“数值”表示自动求和，指求和项。本例对基本工资求和。其结果如图1-10所示。图1-101-3-2数据透视表操作1-3-2-1 字段筛选每一个放到数据透视表中的字段，都具有自动筛选的功能。如展开“职称”下拉式列表后，可以选择“全部显示”或只显示部分字段。其结果如图1-11所示。图1-111-3-2-2 汇总字段添加完成透视表后，单出“数据透视表”中的数据，则出现“数据透视表字段列表”对话框，拖动“

19、奖金”字段到“数值”区域，则新增一个“奖金”字段求和项。结果如图1-12所示。图1-121-3-2-3 不同统计函数使用在建立数据透视表时，EXCEL会自动使用求和函数将数值相加。实际上可以进行其他函数计算，如计算平均值、求总人数等。修改计算类型的方法如下：步骤一：在数据透视表中选择要修改的值字段名称，如“求和项：基本工资”。单击鼠标右键，然后在弹出菜单中选择“值字段设置”命令，或双击“求和项：基本工资”，则出现“值字段设置”对话框，如图1-13所示。步骤二：在“汇总方式”选项卡中选择要使用的计算类型，如“平均值”。步骤三：如果要修改数据区域中的数据格式，可单击“数据格式”按钮，可出现“设置单

20、元格格式”对话框设置格式。如图1-14所示。步骤四：如果单击图1-3-7中的“值显示方式”选项卡，则可进一步设置数据的显示方式，如“占总和的百分比”。如图1-15所示。图1-3-6图1-13图1-14图1-151-3-2-4计算字段与计算项数据透视表可利用公式，插入计算字段与计算项。计算字段是指在数据透视表的字段列表中增加一个字段，该字段可由其它字段的计算而得。计算字段会一次应用到全部分组，计算项则适用于指定的分组项目。1.插入计算字段操作步骤如下：步骤一：在图1-3-6的数据透视表中，选择数据透视表数据区域内的单元格，然后单击“数据透视表工具”“选项”“工具”“公式”“计算字段”命令，则出现

21、1-16所示图。图1-16步骤二：在弹出的“插入计算字段”对话框中，在“名称”文本框中输入计算字段的名称，如奖金增加20%。在“公式”文本框中输入要使用的公式，方法为：首先在“字段”列表中选中在插入的字段，如“奖金”，再单击“插入字段”按钮，将字段复制到公式中，再输入公式的其余部分。如图1-17所示。图1-17步骤三：单击“确定”按钮，即可添加一个计算字段，如图1-18所示。图1-182.添加计算项计算项是对数据透视表的数据进行进一步的运算，但不会增加字段。如对“无线上网人数统计”表（见图1-19）求数据透视表，结果如图1-20所示。如果要增加一条“杭州店+南京店”的数据，则必须使用添加计算项

22、功能，方法如下：步骤一：打开excel1-3-2.xls工作表，选择行区域的任意标题，单击“数据透视表工具”“选项”“工具”“公式”“计算项”命令，则出现1-21所示图。图1-20图1-19图1-21步骤二：在“添加计算项”对话框的“名称”文本框中输入此添加项的名称，如“杭州店+南京店”。步骤三：在“添加计算项”对话框的“公式”文本框中输入要使用的公式。如图1-22所示。图1-22步骤四：单击“确定”，则完成插入项的操作，结果如图1-23所示。图1-23图1-3-191-3-2-5自定义组数据透视表除了依照指定的字段分组之外，也可以使用字段中的数据自定义组。如对图1-24进行2个月分成一组，则

23、操作过程如下：步骤一：在图1-23的数据透视表中，选择一月和二月二个单元格，然后单击鼠标右键，再单击“组合”命令。此时添加了一个“月2”字段，其标题为“数据组1”，如图1-25所示。图1-24步骤二：在编辑栏中把“数据组1”改成“第1个双月”，再将“三月”和“四月”组合成“第2个双月”，其结果如图1-25所示。图1-25步骤三：单击“第1个双月”标题左边的号，使它变成号，则可将月份隐藏，只显示“第1个双月”和“第2个双月”的统计数据。结果如图1-26所示。图1-261-3-3 数据透视图数据透视图与数据透视表相连，展示数据透视表的数据。1-3-3-1 创建数据透视图创建数据透视图的方法如下：步

24、骤一：以图1-27所示的数据透视表为例。打开打开excel1-3-2.xls工作表，选定要分析的范围单击“插手”菜单“表”“数据透视表”“数据透视图”命令，打开如图1-28所示对话框。同时确认在对话框中“表/区域”的范围的正确性，否则进行重新设置，并且选择放置数据透视表及数据透视图的位置。图1-27图1-28步骤二：单击“确定”按钮，此时出现“数据透视表字段列表”，如图1-29所示。用鼠标拖动字段到相应的区域，即可完成创建数据透视表与透视图。如图1-30所示。图1-29图1-3-22步骤三：在“数据透视图筛选窗格”对话框中，可利用“轴字段”和“图例字段”的下拉式列表中选择选择要显示的内容。如只

25、显示“杭州店”及“南京店”的“一月”和“三月”的数据。图1-301-4 Excel宏及应用Excel的强大优势还在于它提供的宏语言Visual Basic for Application(VBA)。Visual Basic是windows环境下开发应用软件的一种通用程序设计语言，功能强大，简便易用。VBA是它的一个子集，可以广泛地应用于Microsoft公司开发的各种软件中，例如Word、Excel、Access等。宏是微软公司为其OFFICE软件包设计的一个特殊功能，是一系列的命令与函数的集合，可以用来执行某个特定任务，目的是让用户文档中的一些任务自动化。OFFICE中的WORD和EXEAL

26、都有宏。1-4-1宏安全性宏相当于在EXCEL中另外运行一个小程序，但它存在一定的安全隐患，因为有些人会在宏中加入木马程序。因此，EXCEL的默认值中是停止宏的使用。如果此时选用“视图”“宏”“查看宏”，则出图1-31所示。图1-31 宏已禁用如果要使用宏，所以必须更改宏安全性设置。操作过程如下：步骤一：单击“Office按钮”“EXCEL选项”命令，在“常用”选项卡中勾选“在功能区显示开发工具选项卡”复选框，如图1-32所示。步骤二：完成后，则出现图1-33所示的“开发工具”选项卡。图1-32图1-33 在功能区显示“开发工具”选项卡步骤三：单击“开发工具”“代码”“宏安全性”命令，即出图1

27、-34所示的“宏设置”对话框。图中显示宏安全性有4种选择，前两种是完全禁用，第3经数字签名的宏可以使用，第4 种是全部可以使用。由于启用所有宏有一定的风险，所以选用第3种，但由于不是每一位用户都有数字签名，所以必须再用其他方式设置。图1-34宏设置步骤四：在图1-34的宏设置中，单击“受信任位置”选项卡，则出现图1-35所示的对话框，单击“添加新位置”，把要进行宏运用的文件夹放入“受信认的位置”，同时勾选“同时信任此位置的子文件夹”复选框，如图1-36所示，则这个文件夹之下的所有文件都可以运行宏。经过以上设置，不明来源的宏会被禁用，而经过数字签名的宏和放在指定位置文件中的宏才会被执行。图1-3

28、5宏设置图1-361-4-2 创建宏创建宏最简单的方法是使用录制的方式。在录制时必须将所有要用到的工作表预备好。整个过程可分为3部分：首先，启动录制工具，并定义宏名称；第二步，开始逐一执行欲录制的各项工作；最后，停止录制宏。 方法如下：步骤一：打开一范例文件，单击“开发工具”选项卡上的“代码”组中，单击“录制宏”。 则可显示“录制新宏”对话框，如图1-37。图1-37录制宏步骤二：在“宏名”文本框中，输入宏的名称（默认名称为Macro1）。 注： 宏名的第一个字符必须是字母。后面的字符可以是字母、数字或下划线字符。宏名中不能有空格，下划线字符可用作单词的分隔符。如果使用的宏名还是单元

29、格引用，则可能会出现错误消息，指示宏名无效。步骤三：在“快捷键”文本框中按shift+A组合键，则代表同时按Ctrl+shift+A组合键即可执行这个宏程序。步聚四：可以指定宏存放的位置，在范例中，将其放在“当前工作簿”。步聚五：单击“确定”按钮之后，开始录制。1-4-3 执行宏方法一：使用宏命令执行宏步骤一：选定欲执行宏的单元格。步骤二：单击“开发工具”选项卡上的“代码”组中，单击“宏”命令，弹出如图1-38所示的“宏”对话框。图1-38执行宏步骤三：在“宏名称”列表中，选择欲执行宏的名称，如Macro1。步骤四：单击“执行”按钮，即可执行此宏。方法二：使用快捷键执行宏步骤一：单击“开发工具

30、”选项卡上的“代码”组中，单击“宏”命令，弹出 “宏”对话框。步骤二：在“宏名称”列表中，选择欲执行宏的名称，如Macro4，单击“选项”按钮，如图1-39所示。图1-39“宏选项”对话框步骤三：在“宏”选项框的“快捷键”中输入要使用的快捷键。如Ctrl+Shift+S。步骤四：单击“确定”。之后，即可用所设的“快捷键”如Ctrl+Shift+S，就可执行Macro4。项目2创建财务单据2-1创建财务单据2-1-1创建差旅费报销单企业建账需要众多的单据为依据，如员工出差，在报销差旅费时一方面要附带报销原件（如车票、住宿发票、餐饮发票等），另一方面还要填写差旅费报销单，将报销的原始票据和差旅费报

31、销单粘贴在一起才可以构成财务部门建账的依据。另外，企业财务单据借款单、领款单、付款单等等，本章仅介绍差旅费报销单的制作。下面了解差旅费报销单的构成项目，如图2-1所示。图2-2图2-1差旅费报销单制作流程如下：1.新建一个工作簿，并命名为“财务单据”，将“sheet1”工作表命名为“差旅费报销单”，如图2-2所示。2.在工作表各行中依次输入如图2-3所示内容。图2-33. 输入表尾与附加信息，如图2-4所示。选中区域R2：R15合并后居中，输入“附单据 张”并竖排文字，选中区域A16：Q16合并后左对齐，输入“主管： 审核： 证明： 经领人：”有关文字。图2-44. 设置文字格式和美化报销单表

32、格。5.最后对合计金额求和如图2-5所示。选中求和的单元格，输入“=SUM(N9:N12,Q9:Q11)” “N9:N12,Q9:Q11” 表示需要求和的区域。图2-6图2-56、设置退补金额如图2-6所示。选中求差的单元格，输入“=P14-Q13+K24”。2-1-2创建材料或产品入库单新建一张工作表，命名为“材料入库单”按照如图2-7设置相关信息。图2-8图2-72-1-3创建材料或产品出库单新建一张工作表，命名为“领料单”按照如图2-8设置相关信息。2-2创建内部报表企业在日常运作中必然会产生一些费用或收入，而财务部门根据这些费用收入单据建立日常费用收入统计工作，以确保账目清晰。在Exc

33、el中建立费用收入统计表后，可以利用数据透视表、透视图、图表等工作进行分析，从而为后期财务预算提供准确的依据。2-2-1销售收入明细表1新建一个工作簿，并命名为“2010年1月销售收入统计”，将“sheet1”工作表命名为“1月销售总表”，如图2-9所示。图2-92.根据实际需要设置各销售收入明细所需项目内容，如销售日期、产品名称、客户、销售数量、销售单价、销售金额等。3.设置金额栏。选中H3输入“=F3*G3”，可得“金额=数量×单价”。如图2-10所示。图2-11图2-104输入全月业务明细。同理输入1月全月的销售收入明细可得到1月份全月销售收入统计表，如图2-11所示。5对销售

34、收入统计表进行汇总分析。选中表中“产品名称”任意单元格，点击“数据”菜单下的“排序（升序）”，再点击“分类汇总”，分类字段选择“产品名称”，汇总方式选择“求和”，汇总项选择“数量”和“金额”，如图2-12所示。再按“确定”。图2-13图2-12可得结果如图2-13所示。还可以按其他方式进行分类汇总，如客户等等。2-2-2材料发出汇总表1新建一个工作簿，并命名为“材料发出明细表”，将“sheet1”工作表命名为“1月”，如图2-14所示。图2-142根据实际需要设置相关项目内容。如按产品种类进行设置列标识信息，按材料种类设置行标识信息，每种产品下又设“数量”、“单价”、“金额”三列列标识，最后设

35、置数量和金额汇总列标识。3设置“金额”列公式。选中D4单元格输入“=B4*C4”，同理设置其他“金额”列公式。（公式表示“金额=数量×单价”）4设置“汇总数量”和“汇总金额”公式。选中Q4单元格输入“=B4+E4+H4+K4+N4”（公式表示将各产品的数量进行求和）如图2-15所示。图2-15同理，选中R4单元格，输入“=D4+G4+J4+M4+P4”可求得汇总金额。将公式复制到其他单元格。5根据实际材料领用业务输入相关数据，可得当月材料发出汇总表。2-2-3生产成本明细表1新建一张工作表，命名为“生产成本明细表”，并设置相应的标识信息，如图2-16所示。图2-162设置“本月生产费

36、用合计”公式。选中G3输入“=SUM(C3:F3)”可得本月生产费用合计数。3设置“本月完工产品费用”公式。选中I3输入“=G3-H3”可得本月完工产品费用数。4设置“单位成本”公式。选择中K3输入“=I3/J3”可得单位成本数。5根据实际业务输入相应的数据，并复制公式到其他单元格，结果如图2-17所示。图2-17项目3 Excel2007在会计账务处理中的应用企业日常账务处理是企业财务工作的基础，也是至关重要的的一部分，宏观世它奖影响企业后期的财务分析、预测和决策。传统的日常贴身长务处理是手工记账的方式来完成，这种方式工作量较大，而且稍不注意容易出现错误，为节省人力和提高工作效率，Excel

37、2007完成账务处理，减轻财务人员的工作量。3-1记账凭证的填制记账凭证又称记账凭单，或分录凭单，是会计人员根据审核无误的原始凭证按照经济业务事项的内容加以归类，并据以确定会计分录后所填制的会计凭证。在实际工作中，为了便于登记账簿，需要将来自不同的单位、种类繁多、数量庞大、格式大小不一的原始凭证加以归类、整理，填制具有统一格式的记账凭证，确定会计分录并将相关的原始凭证附在记账凭证后面。它是登记账簿的直接依据。3-1-1建立会计科目与会计科目代码会计科目分会计科目名称和会计科目代码，会计科目名称按照会计制度规定设置，不应随意简化，会计科目代码是按照行业会计制度统一规定的代码确定的，应与财政部规定

38、的代码相吻合。会计科目一般设到三级明细科目。企业可以根据本单位实际需要建立会计科目和会计代码。二级以下的科目及代码结合单位实际需要进行设定。有关科目名称和科目代码设置如下表所示。科目代码科目名称科目代码科目名称1001库存现金2221001应交税费-进项税额1002银行存款2221002应交税费-销项税额1012其他货币资金2221003应交税费-已交税金1122应收账款2221101应交税费-应交所得税1221其他应收款2241其他应付款1232坏账准备2271其他应交款1221其他应收款2501长期借款1402在途物资4001实收资本1403原材料4002资本公积1405库存商品4101盈

39、余公积1405001库存商品-PP水4103本年利润1405002库存商品-银粉漆4104利润分配1405003库存商品-黑无光5001生产成本1405004库存商品-尼龙灰底5101制造费用1405005库存商品-光油6001主营业务收入1408周转材料6051其他业务收入1408001周转材料-包装物6111投资收益1501待摊费用6301营业外收入1601固定资产6401主营业务成本1602累计折旧6402其他业务成本1604在建工程6403营业税金用附加1606固定资产清理6601销售费用1801长期待摊费用6602管理费用2001短期借款6603财务费用2202应付账款6711营业外

40、支出2211应付职工薪酬6801所得税费用2221应交税费6901以前年度损益调整新建设工作簿，命名为“会计财务处理系统”，将“sheet1”命名为“会计科目表”，根据本单位实际需要建立会计科目代码表，如图3-1所示。图3-13-1-2记账凭证表单的填制企业会计科目设置完成后，需建立记账凭证表单。记账凭证基本内容有：记账凭证的名称；填制记账凭证的日期记账凭证的编号经济业务事项的内容摘要经济业务事项所涉及的会计科目及其记账方向经济业务事项的金额记账标记等。1将“sheet2”命名为“记账凭证”工作表，合并A1：J1单元格区域，输入“记账凭证清单”，在A2：J2单元格区域输入记账凭证列标识。如图3

41、-2所示。图3-22设置“记账编号”列的单元格格式为“文本”格式，设置“日期”格式为月日格式。3设置“科目代码”填充序列。选择“数据”主菜单下的“数据有效性”命令，打开 “数据有效性”对话框，如图3-3所示设置“允许”下拉式菜单中选择“序列”，在“来源”中输入“科目代码表!A2:A55”，然后点击确定。该列其他单元格通过复制法设置填充序列。4根据输入的科目代码自动返回科目名称。如图3-4所示。图3-3图3-4注意“科目名称”下返回值“#N/A”，是因为E3单元格未输入数据，当E3单元格输入数据后，公式会按照E3单元格的值将正确的结果赋值给F3。同理用复制法将“科目名称”列其他单元格进行赋值。3

42、-1-3记账凭证的填制记账凭证表单建立完成，就可以输入当月发生的业务了。结果如图3-5所示。图3-6图3-53-1-4记账凭证的查询建立记账凭证后，可利用Excel2007的筛选功能按需要进行了查询，如按科目代码查询等。1.添加自动筛选。选中数据编辑区域任一单元格，单击“排序和筛选”工具栏中的“筛选”即可添加自动筛选。如图3-6所示。2.按科目代码、摘要内容或方向等列标识进行查询。3-2明细账的处理明细账是按照分类账户开设的，连续、分类登记某一类经济业务，提供详细核算资料的账簿。宏观世界能够具体、详细地反映经济活动情况，对总分类账起辅助和补充作用，同时也为会计报表的编制提供必要的资料。3-2-

43、1建立明细账科目表单明细账有三栏式、数量金额式、多栏式等，但基本是“借方”、“贷方”和“余额”三栏的三栏式。1.建立明细账工作表，并建立表单基本信息，选中A4单元格输入“=科目代码表!A2”可得到A4单元格的“科目代码”，再向下复制此公式即可得到所有“科目代码”。同理选中B4单元格输入“=科目代码表!B2”即可得到B4单元格的“科目名称”，再向下复制公式，可得到所有“科目名称”，如图3-7所示。图3-72.复制期初数据到本月明细表单中。如图3-8所示。图3-83-2-2计算本月发生额与月末余额1.使用SUMIF函数从“记账凭证”工作表中计算本期借方发生额，并快速复制其他科目借方发生额，如图3-

44、9所示。图3-10图3-92.使用SUMIF函数从“记账凭证”工作表中计算本期贷方发生额，并快速复制其他科目贷方发生额，如图3-10所示。3.计算期末余额。期末余额等于期初余额加上本期发生额，选中G4单元格输入“=IF(C4-D4)+(E4-F4)>=0,(C4-D4)+(E4-F4),0)”得到期末借方余额，再向下复制公式可得到所有科目期末借方余额，同理选中H4单元格输入“=IF(C4-D4)+(E4-F4)<0,ABS(C4-D4)+(E4-F4),0)”，得到期末贷方余额，再向下复制公式可得到所有科目期末贷方余额，如图3-11所示。3-3总账的处理图3-113-3-1建立总账

45、科目表单新建“总账”工作表，建立列标识，复制总账科目代码和科目名称到“总账”工作表中，同时也复制期初余额到“总账”工作表中，如图3-12所示。图3-123-3-2计算本月总账科目发生额与月末余额图3-131.在“明细账”表单分列出总账科目。在“明细账”工作表的“科目代码”前插入新列，如图3-13所示，选中A4单元格输入“=LEFT（B4，4）”按ENTER健，即得到“库存现金”的总账代码，向下复制可得到所有明细科目的总账代码。2.计算总账发生额。选中E4单元格输入“=SUMIF(明细账!$A$4:$A$61,A4,明细账!$F$4:$F$61)”按ENTER健，即可得“库存现金”本期借方发生额

46、，复制可得所有总账科目借方发生额，同理选中F4单元格输入“=SUMIF(明细账!$A$4:$A$61,A4,明细账!$G$4:$G$61)”可得“库存现金”本期贷方发生额，向下复制可得所有总账科目本期贷方发和额，如图3-14所示。图3-143同明细账期限末余额计算方法一样计算总账各科目期末余额。3-4编制试算平衡表3-4-1建立试算平衡表试算平衡表包含元素较少，建立方式也比较简单，新建一新工作表命名为“试算平衡表”，建立列标识。如图3-15所示。图3-153-4-2试算平衡表的应用1.计算借方金额。选中A3单元格输入“=sum(总账!E4：E47)”按ENTER健即可。2计算贷方金额。选中B3

47、单元格输入“=sum(总账!F4：F47)”按ENTER健即可。3选中C3单元格输入“=A3-B3”按ENTER可计算借方与贷方金额的差额。4选中D4单元格输入“=if(C3=0,”平衡”,”不平衡”)”按ENTER即可判断是否平衡。项目4 Excel2007在工资管理中的应用任何企事业单位的运作都离不开账目的管理，而工资管理是单位账目中一个重要的部分，同时也是财务人员必不可少的一项工作。制作工资明细表是一项非常繁重的工作，大量的数据、复杂的计算，使得财务人员每到月底忙得不可开交而且时常发生错误。利用Excel2007强大的处理功能，可以轻松地对工资数据进行管理，同时可以建立工资管理系统。4-

48、1创建企业工资管理系统4-1-1规划工资明细表构成要建立工资管理系统首先要构建工资明细表的基本框架即工资表中应包含哪些要素，不同的单位构成要素会有所不同，具体操作如下：1.新建工作簿命名为“工资管理系统”，将“sheet1”命名为“工资发放明细表”。2.选中A1：M1单元格，单击“合并及居中”，输入工作表标题，如图4-1所示。图4-13.在A2：M2区域依次输入表格构成要素，建立列标识。如图4-2所示。图4-24.在单元格A1输入第一个员工号“jh001”用自动填充法输入所有员工号，如图4-3所示。图4-35.在工资明细表中输入基本数据，如“姓名”、“所属部门”、“职称”。如图4-4所示。图4

49、-44-1-2建立工资表基本数据1.建立基本工资表。将“sheet2” 命名为“基本工资表”，建立列标识。如图4-5所示。,2.录入“基本工资”，选中E3单元格，在公式栏中输入公式：=IF(D3=”教授”,2000,IF(D3=”副教授”,1500,IF=(D3=”讲师”,1200,IF(D3=”助教”,900),按Enter健，即可按照D3单元格中的职称得出E3单元格的基本工资，然后选中E3向下拉动复制公式，可快速计算出所有员工的基本工资。如图4-6所示。图4-5图4-63.以相同方法录入“职务津贴表”、“奖金课酬表”、“各类补贴”、“考勤记录表”等工资构成要素内容。如图4-7、4-8、4-

50、9、4-10所示，其中“考勤扣款表”是根据基本工资除以30天取整乘请假天数计算而得的。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时返回的值。图4-7图4-8图4-9图4-104-1-3从各记录中录入工资数据到工资表建立好各类基本数据表后，从各记录表中导入数据到工资发放明细表。1选中“工资发放明细表”中的单元格F3，在公式编辑栏

51、中输入公式：=VLOOKUP(A3,基本工资表!A3:F16,6),按ENTER健，即可从基本工资表中得到第一位员工的工资。再次选中“工资发放明细表”中的单元格F3，向下复制公式可以得到其他员工的基本工资。如图4-11所示。图4-112同理，将“职务津贴”、“奖金课酬”、“各类补贴”、“考勤扣款”导入工资发放明细表中。如图4-12所示。图4-12VLOOKUP函数是Excel等电子表格中的横向查找函数，它与LOOKUP函数和HLOOKUP函数属于一类函数，VLOOKUP是按行查找的，HLOOKUP是按列查找的。VLOOKUP(lookup_value,table_array,row_index

52、_num,range_lookup)Lookup_value为需要在数据表第一列中进行查找的数值。Lookup_value可以为数值、引用或文本字符串。Table_array为需要在其中查找数据的数据表。使用对区域或区域名称的引用。Row_index_num为table_array中待返回的匹配值的行序号。Row_index_num为1 时，返回table_array 第一列的数值，row_index_num 为2时，返回table_array第二列的数值，以此类推。如果row_index_num小于1，函数VLOOKUP返回错误值 #VALUE!；如果row_index_num 大于tabl

53、e_array的列数，函数VLOOKUP返回错误值#REF!。Range_lookup为一逻辑值，指明函数VLOOKUP 查找时是精确匹配，还是近似匹配。如果为 TRUE 或省略，则返回近似匹配值。也就是说，如果找不到精确匹配值，则返回小于lookup_value的最大数值。如果lookup_value为FALSE，函数VLOOKUP将查找精确匹配值，如果找不到，则返回错误值#N/A。4-1-4计算应发工资、各项扣款和实发工资1计算各类扣款、应发工资。公积金扣款是根据基本工资的5%扣款，选中L3单元格，在公式编辑栏中输入“=F3*5%”按ENTER健即可得到第一位员工的公积金扣款，依次向下复制

54、公式即可得到所有员工的公积金扣款。2计算个人所得税，个人所得税计算公式：应纳税=(本月收入总额-个人所得税起征额)×税率-速算扣除数个人所得税率表级数全月应纳税所得额税率（%）速算扣除数不超过500元的50超过500元至2，000元的部分1025超过2，000元至5，000元的部分15125超过5，000元至20，000元的部分20375超过20，000元至40，000元的部分251375超过40，000元至60，000元的部分303375超过60，000元至80，000元的部分356375超过80，000元至100，000元的部分4010375超过100，000元的部分4515375选择中N3单元格，在公式编辑栏中输入公式：“=IF(J3>102000,(J3-2000)*0.45-15375,IF(J3>82000,(J3-2000)*0.4-10375,IF(J3>62000,(J3-2000)*0.35-6375,IF(J3>42000,(J3-2000)*0.3-3375,IF(J3>22000,(J3-2000)*0.25-1