《Excel在财务管理中的应用电子教案》.docx

1、Excel在财务管理中的应用电子教案 Excel在财务管理中的应用教案 项目一 Excel基础 【项目要求】 (一) 了解Excel 的基本功能 (二) 掌握各种单元格的编辑操作 (三) 掌握工作表的各种操作 (四) 掌握公式的概念和公式的基本操作 (五) 掌握函数的概念，能够运用函数对工作表进行计算和分析【项目内容】 (一)Excel的优势和功能 (二)使用“自定义”选项对话框 (三)移动快速访问工具栏 (四)添加和删除快速访问工具栏中的命令 (五)设置屏幕显示方式 (六)简单窗口设置 (七)认识工作簿、工作表和单元格 (八)公式【相关知识点析】1 主要概念工作簿Microsoft Offi

2、ce Excel 2007工作簿是包含一个或多个工作表的文件，可以用其中的工作表来组织各种相关信息。一个工作簿至少必须有一个工作表，最多可以有255个工作表。工作簿中的工作表越多，文档容量就越大，运行起来相对较慢。工作表工作表在Excel中用于存储和处理数据的主要文档，也称为电子表格。工作表是由单元格组合而成的一个平面整体，是一个平面二维表格，而且工作表总是存储在工作簿中。Excel 2007的工作表在行和列的数目上都做了很大的扩充，共有1 048 576行、16 384列。默认情况下，工作表是不能完全显示全部行和列的，用户若需要选择，可以拖动Excel窗口右侧和下方的滚动条进行。单元格在工作

3、表中，行与列交叉形成的矩形区域，称为单元格。单元格是工作表输入、编辑、运输的基本单位，单元格中可以存放文字、数字和公式等信息。单元格引用在Excel 2007中，对于工作表的一系列的操作都是以单元格为最小单元进行的。而针对于单元格的相关操作，则常常借助于单元格的地址，对其进行引用，来达到操作的目的。 如，两个单元格内容相加：A1+B3 相对地址以列标和行号组成，如A1，B2，C3等。在进行公式复制等操作时，若引用公式的单元格地址发生变动，公式中的相对地址会随之变动。绝对地址以列标和行号前加上符号“$”构成，如$A$1。在进行公式复制等操作时，当引用公式的单元格地址发生变动时，公式中的绝对地址保

4、持不变。Excel函数Excel函数是Excel内部预先定义的特殊公式，可以执行计算、分析等处理数据任务。函数最终返回结果为值。函数由函数名称和参数构成。函数名称决定了函数的功能和用途。函数参数规定了函数的运算对象、顺序或结构等。参数可以是一个或多个，多个参数之间以逗号分隔。参数可以是常量、逻辑值、数组、单元格引用等，也可以是嵌套使用函数。参数的类型和位置必须满足函数的语法要求，否则将返回错误信息。2 知识拓展拓展1：怎样在保存Excel工作簿时自动删除个人信息有时创建Excel工作簿或Word文档后不希望同时保存作者信息、文档属性等附加信息。在Excel 2007中，依次单击“文件（或Off

5、ice按钮）选项（或Excel选项）信任中心信任中心设置”，在“文档待定设置”下也可看到该选项，但默认该选项是灰色的。这是由于该选项在2007版的Excel、PowerPoint和Word中被禁用。 要激活该选项，让保存工作簿时自动删除个人信息，可以在上图中单击“文档检查器”按钮，弹出“文档检查器”对话框，然后单击“检查文档”按钮，Excel会在对话框中显示检查结果，然后单击“文档属性和个人信息”项中的“全部删除”按钮删除个人信息。 也可通过下面的方法直接运行“文档检查器”： Excel 2007： 依次单击“Office按钮准备检查文档”。 这样操作以后，“保存时从文件属性中删除个人信息”选

6、项就会变为可选状态。该选项仅对当前工作簿有效。拓展2：将一列单元格内容合并到一个单元格中 假如A列中有一列名单，现在需要将这些名单合并到一个单元格中，并用某种标点符号将其隔开，在Excel中该怎样操作呢？ 由于A列中的名单较多，不宜用“&”或CONCATENATE函数进行合并，这时可用复制粘贴和替换的方法，步骤如下： 一、复制该列内容到目标单元格。 方法一：用记事本或Word 将这列名单复制到记事本或Word中，然后再复制粘贴回Excel中。这里的技巧是：不能直接选择某个单元格进行粘贴，而是要双击目标单元格或选择目标单元格后单击编辑栏，当光标在其中闪烁时再进行粘贴才能将其粘贴到一个单元格内。

7、方法二：利用剪贴板进行操作 1.打开Excel剪贴板：在Excel 2007中，选择“开始”选项卡，在“剪贴板”组中单击右下角的对话框启动器打开剪贴板。 2.选择这列名单进行复制，复制的内容即可显示在剪贴板中。 3.双击目标单元格或选择目标单元格后单击编辑栏。然后单击剪贴板中所复制的内容，该列名单即可复制到目标单元格中，如下图的C5单元格。 二、替换换行符。 用上述方法将多个单元格内容合并到一个单元格后，原来的各行内容之间还包含了一个换行符，可以用下面的方法将这些换行符替换为分隔符号： 1.选择目标单元格，按Ctrl+H打开“查找和替换”对话框。 2.选择“查找内容”右侧的文本框，按住Alt键

8、，在数字小键盘中输入“0010”，然后再松开Alt键。在“替换为”右侧的文本框中根据需要输入某种分隔符号，如顿号、空格等，如果无需分隔各行内容，则“替换为”保留为空。单击“全部替换”即可。 需要注意的是，进行这种替换后如果紧接着要再次替换换行符，在打开“查找和替换”对话框后，Excel会“记住”要替换的换行符，这时可直接单击“全部替换”按钮。另外，之后要在工作表中再进行其他内容的查找或替换，有时即使所查找的内容明显存在于工作表中，Excel也可能会给出找不到搜索内容的提示。原因是以前在“查找内容”后输入的换行符虽然不可见，但却包含了两行，如果直接在“查找内容”后输入新的查找内容，实际上是输入到

9、了第一行中，Excel自然会找不到搜索内容了。解决的方法是：方法A：在“查找内容”后输入新的查找内容后，将光标定位到所输入的内容后，按下Delete键删除后面的不可见内容，再进行查找或替换。 方法B：关闭并重新启动Excel。项目二 Excel高级应用 【项目要求】 (一)掌握数据管理和分析的方法(二)掌握记录单的使用 (三)掌握数据透视表和透视图的概念和应用【项目内容】 (一)数据管理与分析(二)图表 (三)使用图形和图片【相关知识点解析】1 主要概念排序对一列或多列中的数据按文本（升序或降序）、数字（升序或降序）以及日期和时间（升序或降序）进行排序。还可以按自定义序列（如大、中和小）或格式

10、（包括单元格颜色、字体颜色或图标集）进行排序。大多数排序操作都是针对列进行的，但是，也可以针对行进行。筛选筛选过的数据仅显示那些满足指定条件 （条件：所指定的限制查询或筛选的结果集中包含哪些记录的条件）的行，并隐藏那些不希望显示的行。筛选数据之后，对于筛选过的数据的子集，不需要重新排列或移动就可以复制、查找、编辑、设置格式、制作图表和打印。可以按多个列进行筛选。筛选器是累加的，这意味着每个追加的筛选器都基于当前筛选器，从而进一步减少了数据的子集。数据透视表（图）记录数量众多、以流水账形式记录、结构复杂的工作表，为了将其中的一些内在规律显现出来，可将工作表重新组合并添加算法。即建立数据透视表（图

11、）。数据透视表（图）能帮助用户分析、组织数据。利用它可以很快地从不同角度对数据进行分类汇兑。2 知识拓展拓展1：在Excel2007中快速删除重复数据项在Excel以前的版本中，可以通过“高级筛选”来删除重复记录，在Excel2007中不但保留了“高级筛选”这个功能，而更为值得注意的是Excel 2007增加了一个“删除重复项”按钮，从而这项操作变得更加方便、快捷。首先选择需要删除重复项的所有单元格。如果是要对所有数据进行这一操作，可以任意选择一个单元格。在工具栏中选择“数据”选项卡，再单击“排序与筛选”区中的“删除重复项”按钮。在弹出的“删除重复项”对话框选择要检查的字段，最终将只对选中的字

12、段进行比较。这里选择后三个字段。最后单击“确定”按钮，多余的重复项就被删除了。拓展2：怎样在Excel中筛选加粗的文字？有时要在Excel中筛选加粗的文字，虽然Excel没有提供直接的方法，但可以先通过辅助列来确定单元格内容是否加粗，然后再对辅助列进行筛选，从而间接地筛选出加粗的文字。判断单元格文字是否加粗可以用GET.CELL函数或自定义函数来进行。下面以筛选图中A1:A16区域的加粗文字为例，介绍在Excel 2007中的操作方法： 方法一、辅助列+ 定义名称 用GET.CELL函数可以判断单元格格式，如加粗、倾斜、下划线等，但该函数只能用于定义的名称中。 1.选择B2单元格，按快捷键Ct

13、rl+F3，在Excel 2007中打开 “名称管理器”，单击“名称管理器”中的“新建”按钮，打开“新建名称”对话框。 在“名称”后输入一个名称，如“cuti”，在“引用位置”后的文本框中输入公式： =GET.CELL(20,A2)&T(NOW() GET.CELL函数的第一个参数为“20”，用于判断指定的单元格是否为粗体。后面加上“T(NOW()”，是为了当工作表中的任意单元格进行计算时（或按F9键），该函数也能重新计算。单击“确定”，然后关闭“名称管理器”。 2.在B2单元格中输入公式： =cuti 然后拖动填充柄将公式填充到B3:B16区域，如果A列文字为粗体，则公式返回TRUE。 3.

14、筛选B列为TRUE的单元格，即可筛选出A列中的粗体文字。 方法二、辅助列+自定义函数 1.按Alt +F11，打开VBA编辑器，单击菜单“插入模块”，在代码窗口中输入下面的代码： Function Bold(rCell As Range) Application.Volatile Bold = rCell.Font.Bold End Function Application.Volatile方法将该自定义函数标记为易失性函数，这样当工作表中的任意单元格进行计算（或按F9键）时，该函数也将重新计算。 2.关闭VBA编辑器，返回到Excel界面。在B2单元格中输入公式： =bold(A2) 然后拖

15、动填充柄，将公式填充到B3:B16区域。如果A列文字为粗体，则公式返回TRUE。 3.筛选B列。 注意：如果公式输入完毕后，对A列中的某些单元格进行了加粗或取消加粗操作，上述两种方法中的公式不会立即自动刷新，这时按F9键刷新即可。案例分析案例1：怎样筛选第8位为“8”和“6”的手机号？在Excel工作表中有一列手机号，怎样筛选出从左往右数第8位为“8”和“6”的手机号？ 要在一列数值中筛选出某位置为指定数字的数据，有很多方法都可以实现。例如用高级筛选、用辅助列+公式等方法，本文介绍用自动筛选的方法。假如手机号码在A2:A100区域中，要筛选出其中第8位为“6”和“8”的手机号，方法如下。 1.

16、先将A列中的数值转换为文本。由于在自定义自动筛选时要用到通配符（？、*），而通配符无法应用于数值，故先将A列中的手机号转换为文本。如果手机号原本就是以文本形式存储的数字，则跳过此步。 选择A列中的手机号码，在Excel 2007功能区中选择“数据”选项卡，在“数据工具”组中单击“分列”。 在弹出的对话框中单击两次“下一步”，在“第3步”中选择“文本”，单击“完成”。这样就将该列数值转换成文本。 2.对A列进行自定义自动筛选。如果A1单元格没有出现筛选箭头，单击“数据筛选自动筛选”。单击A1单元格中的下拉箭头，选择“自定义”，弹出“自定义自动筛选方式”对话框。将第一个筛选条件设置为“等于”、“*

17、6?”，第二个筛选条件设置为“*8?”，两个条件之间的关系设置为“或”，如图。单击“确定”。由于手机号都是11位，通配符“*”代表任意多个字符，“?”代表单个字符，因而“*6?”会筛选出第8位为“6”的号码。 Excel会将第8位为“6”或“8”的手机号筛选出来。案例2：Excel2007数据透视表应用实例当大量的数据罗列在 Excel2007 表格中需要你分类汇总的时候，你想过如何处理么，你可能会想到“ 排序 ”，但是相同的数据如何只选一呢？如图1。 图1该表为某公司3个月的办公用品采购数据在Excel2007中的罗列，因为月月之间都有重复采购，所以存在大量的 重复数据 ，我们现在需要将重复

18、数据只保留一个，从形成一个办公用品价目表，以下我们通过Excel2007中的“ 数据透视表 ”来完成。在菜单栏中进入“插入”选择“数据透视表”项，如图2图3。 图2 图3 在“选择一个表或区域”中我们选择自己所需要的列，因为我们要做的是一个办公用品价目表，所以只需要“类别”“名称”“单位”“单价”四项，并在“选择放置数据透视表的位置”选择“新工作表”，所以如图4。 图4 点击“确定”如图5： 图5在右侧“选择要添加到报表的字段”框中选择“类别”，在“选项”菜单中选择“字段设置”如图6。 图6字段设置有两个选项卡，在“分类汇总和筛选”卡中选择“无”，在“布局和打印”选择“以表格形式显示项目标签”

19、，然后点击“确定”。在右侧“选择要添加到报表的字段”框中选择“名称”如图7。 图7选择“名称”列从“办公桌”以下的数据，继续进行“字段设置”方法如前，当“单位”的“字段设置”结束后，在右侧选择“单价”项，即可形成如下表，如图8： 图8项目三 Excel在账务处理中的应用【项目要求】(一) 了解凭证及各种账簿的样式 (二) 理解excel中会计凭证表在账簿制作中的基础作用 (三) 掌握用excel进行会计凭证表制作的方法 (四) 掌握excel中IF、VLOOKUP、SUMIF等函数的使用 (五) 掌握运用excel的数据透视表功能建立各种账表的方法【项目内容】(一) Excel在会计凭证中的应

20、用 (二) Excel在会计账簿中的应用 (三) Excel在账务处理中的用用实例 (四) 掌握excel中IF、VLOOKUP、SUMIF等函数的使用 (五) 掌握运用excel的数据透视表功能建立各种账表的方法【相关知识点析】1 主要概念会计凭证记录经济业务、明确经济责任的书面证明，也是登记账簿的依据。填制和审核会计凭证是会计核算的基本方法之一，是会计核算工作的起始环节。原始凭证在经济业务发生或完成时由相关人员取得或填制的，用以记录或证明经济业务发生或完成情况并具有法律效力的证明文件。记账凭证会计人员根据审核后的原始凭证进行归类、整理，并确定会计分录而编制的会计凭证，是登记账簿的依据。会计

21、账簿以会计凭证为依据，全面地、连续地、系统地、综合地记录和反映企业、机关和事业等单位经济活动全部过程的簿籍。它是一种具有专门格式的簿籍，是由具有专门格 式的账页装订成册，或用其他方式联结而成的，用以根据会计凭证来登录经济业务，以全面、连续、序时、分类地反映经济业务的一种簿籍。日记账也称序时账簿，是按经济业务发生时间的先后顺序，逐日逐笔记录经济业务的账簿。分类账是按照账户分类记录各项经济业务的账簿。按照分类详细程度的不同，该账簿分为总分类账簿和明细分类账簿。备查账又称辅助账，是对日记账和分类账簿中不能记载或记载不全的经济业务进行补充登记的账簿。分类账是按照账户分类记录各项经济业务的账簿。任何企业

22、的一切经济活动都应分类整理计入分类账的有关账户中，这样企业的经济活动和财务状况就可以通过分类账分门别类地反映出来。科目汇总表是根据一定期间内的所有经济业务，对相同的会计科目进行归类，定期汇总出每一个会计科目的借方本期发生额合计数和贷方本期发生额合计数的一种表格。科目余额表是用来记录本期所有会计科目的发生额和余额的表格，它是科目汇总表的进一步延伸，能够反映出某一会计期间相关会计科目的期初余额、本期发生额、期末余额，为编制会计报表提供更完善的数据。2 知识拓展：Excel账务处理系统的分析与设计（一）主要功能模块组成Excel账务处理系统按照一般会计信息化软件的设计思路，划分为四大模块，即系统初始

23、化模块（会计科目表、初始余额表）、凭证处理模块（记账凭证清单、凭证查询）、账簿处理模块（试算平衡表、科目汇总表、账簿查询）、报表处理模块（资产负债表、利润表、现金流量表）（见图1）。此图也可以作为系统主界面。（二）各主要模块的设计思路首先按系统主要模块结构图建立Excel账务处理系统封面，然后再按顺序建立各模块工作表页面。1.系统初始化模块（1）会计科目表。建立一个会计科目工作表，输入企业所有需要的总账科目，这个工作表是凭证、账簿、报表录入、汇总、查询的基础，因此必须首先创建（见图2）。另外，可以按总账科目名称分别建立明细科目工作表，逐个工作表完成明细科目的输入，并且每个明细科目工作表加入借、

24、贷方初始余额项目。以原材料明细账为例，其明细科目工作表如图3所示。（2）初始余额表。这个工作表的功能是反映各总账账户的初始余额，主要包括各科目名称、编号、借贷方期初余额（由明细科目工作表中的期初余额汇总生成）等栏目。2.凭证处理模块（1）记账凭证清单。记账凭证清单工作表是日常账务处理的核心内容，用于录入会计业务，即填制凭证，但这里不是按传统凭证的格式填制，而是采用类似普通日记账的形式录入，方便凭证查询。它的栏目包括记账凭证的主要项目，包括凭证字号、附件张数、填制日期、会计科目、摘要、借贷方金额、制单人、审核人、现金流量分类（用于确认现金流量项目）等（见图4）。（2）凭证查询。凭证查询工作表采用

25、传统收款凭证、付款凭证、转账凭证的格式界面，可以随时打印输出。它的重要特点是只要在凭证号单元格中输入凭证号，凭证查询工作表中的其他项目会根据输入的凭证号，通过IF函数自动从记账凭证清单工作表中获得相关凭证各项目信息并填充到各单元格中（见图5），并通过NUMBERSTRING函数把金额转化为中文大写金额。3.账簿处理模块（1）试算平衡表工作表包括期初余额、本期发生额、期末余额等栏目，其中期初余额是从初始余额表中提取；本期发生额通过SUMIF函数从记账凭证清单工作表中分类汇总得到；期末余额根据前两项计算得到。科目汇总表工作表中，也是从记账凭证清单中分类汇总提取数据，考虑到一个月里面可能分时段产生多

26、张科目汇总表，SUMIF无法进行这样的多重判断，故须使用SUMPRODUCT函数来实现多条件求和。（2）账簿查询。分别建立总账查询工作表、明细账查询工作表、特种日记账查询工作表，通过SUMIF、VLOOKUP、OFFSET、IF等函数从记账凭证清单、试算平衡表中提取数据。4.报表处理模块（1）资产负债表工作表和利润表工作表的数据主要是根据试算平衡表分析引用填列。（2）现金流量表工作表的计算填列，主要是根据前面录入的记账凭证清单工作表的现金流量分类栏，通过SUMIF函数分类汇总填列。3 案例分析案例1： VLOOKUP函数在会计工作中的应用实例自动显示会计科目在用Excel进行账务处理时，必须填

27、写“科目编码”及“科目名称”，而每一个“科目名称”都有一个相对应的“科目编码”，下面使用Vlookup函数直接输入简单方便的“科目编码”来自动生成“科目名称”。1.先打开（或输入）工作表“会计科目”，该工作表中已将所有科目编码与科目名称对应输入，如图1所示。2.单击“插入/名称/定义”命令，在弹出的“定义名称”对话框中，将此工作表的范围名称定义为“编KM”，并将引用位置设为“科目编码表!$A$3：$C$500”。值得注意的是，通常会将“范围名称”的引用位置定义得比实际范围要大，目的是预防未来会有增加或删减等变动情况而影响到整体。3.切换到工作表“会计分录表”，如图1所示。选中E4单元格，单击“

28、公式编辑”按钮，在随后的“编辑框”中，输入如下公式：“=IF（ISERROR（VLOOKUP（$D4，编KM，2，FALSE），VLOOKUP（$D4，编KM，2，FALSE）”。此公式表示：当D4单元格为（即空格的意思），则E4单元格也显示为空格；如果D4单元格为非空格时，则以E4单元格的数据去引用“编KM”这个范围名称，找出完全符合E4单元格的数据，并回传第二列的值到E4单元格。这里的“FALSE”代表着精确匹配。4.在D4单元格中输入“100201”，则E4单元格中自动生成“现金”字段，如在G3单元格中输入“1009”，此科目编码为无效科目编码，因此E4单元格也显示为空格，提示此处科目编

29、码填写错误。案例2： VLOOKUP函数在会计工作中的应用实例在库龄分析上的妙用在Excel中可以建立如图2所示的库龄分析表，在C4单元格中输入公式：“=$F$1-B4”，在D4单元格中输入公式：“=VLOOKUP（C4，$F$2：$G$9，2，1）”，然后利用Excel提供的拖曳复制功能，把公式复制到下面对应的单元格中。这样Excel就可以根据每个存货的入库时间和截止时间自动求出库龄，然后根据库龄得到相应的区间，利用数据透视表工具就可以对存货进行库龄分析。案例3： VLOOKUP函数在会计工作中的应用实例进行工资数据的查询如图3所示，建立工资基本信息表，选择“工资基本信息！A2：J10”区域

30、，命名该区域为“JBXX”，在“VLOOKUP查询”工作表中输入各工资项目，并输入相应的公式。这样在“VLOOKUP查询”工作表B1单元格输入要查询的“职工代码”，即可查询出此职工的工资情况。案例4： VLOOKUP函数在会计工作中的应用实例计算个人所得税如图4所示，在Excel中新建一个工作簿，选择sheet1，输入相关文字和数据，在工作簿相应的位置，建立“个人所得税税率表”。在C2单元格中输入公式：“=VLOOKUP（B2，$H$2：$K$10，3）”，其含义是在$H$2:$K$10数据库区域中查找比B2小的数值，找到后返回查找表第三列的数值为0.15。同理，可设置公式查找出B2对应的速算扣除数，这样就可以算出张雨的应纳个人所得税额。运用Excel的拖曳复制功能，可以将C2、E2、D2单元格中的公式复制到下面的单元格，这样就可以求出其他人的应纳个人所得税额。在实际应用过程中，个人所得税税率表也可以放到另一工作表中。项目四 Excel在工资核算中的应用 【项目要求】 (一) 了解工资的构成及工资各项之间的关系； (二)理解并掌握个人所得税的计算方法； （三)熟悉工资表主要项目及其数据的填制方法； （四）能够运用Excel设计、制作工资表； （五）能够运用Excel对工资数据