关于Excel在财务工作中日常用法的简介

关于Excel在财务工作中日常用法的简介主讲人：李晗，中国注册会计师，曾在会计师事务所执业四年在财务工作中，尤其是财务管理中，经常会进行数据的统计和整理，所以熟练使用Excel对提高财务工作效率是非常有必要的。今天主要向大家介绍Excel在财务工作中的一些运用，如排序功能、分类汇总功能、单元格保护功能、抽表功能以及常用的两个函数用法等。一、排序功能二、分类汇总功能三、单元格保护功能四、抽表功能五、Sumif和Vlookup两个函数用法一、排序功能——以银行对账为例进行演示使用方法：先选中拟排序的表格，再使用数据菜单栏下的排序功能进行排序（快捷键Alt+D+S）如果银行账户比较多或交易量比较大时，传统的人工对账可能要花费大量的人力和时间，而且容易出错。使用Excel数据菜单栏下的排序功能协助对账，可以大幅度提高工作效率和质量。具体方法是将电子版对账单与财务账都按发生额的大小顺序进行排序，然后两者相减，差额为零则一致，不为零则出现调节项。排序前的银行对账单和明细账银行对账单按大小顺序进行排序排序后的银行对账单和明细账将排序后的结果相减查找调节项二、分类汇总功能——用销售清单汇总进行演示分类汇总在财务工作中的运用也非常广泛，如销售清单汇总、应收票据汇总、借款汇总等。分类汇总主要有两种方法：一是利用数据菜单栏下的分类汇总功能；二是利用数据菜单栏下的数据透视表和数据透视图功能。下面以销售清单汇总为例进行演示。（一）数据——分类汇总功能•汇总的数据必须是数据列表，或者外部数据库文件，至少包括数据字段和类别字段。•1、数据字段：包括要汇总的值或数据。在销售清单里，销售量、销售额字段都是数据型字段；•2、类别字段：对数据的描述。销售清单里的月份、经销商、地区等就是类别字段。（二）数据——数据透视表和数据透视图步骤1：指定数据源位置步骤2：指定数据步骤3：完成数据透视表步骤3.1：选择透视表显示位置步骤3.2：对透视表进行布局布局如下：将月份拖到“页”，经销商拖到“行”，销售量和销售额拖到“数据”功能一：按“页”——月进行汇总功能二：通过字段设置修改汇总方式功能三：图表功能三、单元格保护功能——以统计会议预算情况表为例进行演示•可以将不让他人修改的单元格区域锁定，防止误操作。其余没有被锁定的单元格，可以随意编辑；•广泛用于集团公司要求下属子公司以及单位财务部门要求其他部门按照给定格式填报数据，如各类财务报表、统计表等。2010年度会议计划表申报单位：单位：元序号会议名称时间地点人数会议预算会议类别视频会议1、2、3、4、5、合计--•Excel锁定单元格步骤一：选中标题、项目栏以及合计行等需保护的单元格，按ctrl+1，打开“设置单元格格式”对话框，在“保护”项下勾选“锁定”，然后确定；•Excel锁定单元格步骤二：单击工具——保护工作表，可以输入密码，也可以不输入，根据需要而定，确定；•Excel锁定单元格步骤三：“在允许此工作表的所有用户进行”选项中勾选拟允许他人进行的操作。输入密码确认后就完成单元格的保护。若修改已保护的单元格会出现以下提示，此时必须根据提示撤销工作表保护后才能进行修改。四、抽表功能——以抽取资产负债表为例进行演示该功能将某个文件夹中不同WORKBOOK中同名SHEET的内容集中至一个WORKBOOK中，广泛用于抽取既定格式的数据（如各类财务报表、统计表等）。主要具有以下独到之处：•快速。平均而言，抽取30个单位的资产负债表只需要1-2分钟；•方便。由于全部是公式链接，报表数据有改动时，结果会自动更新；•可靠。由于全部由计算机自动完成，人为错误减至最低；•高效。EXCEL不用打开任何一个报表，不过多占用计算机的系统资源。（一）抽表功能概述——安装会计师事务所专门设计的软件（二）以抽取12家单位资产负债表为例进行演示•拟操作的对象是2009年东风有限汽车公司下属12家单位第二季度的财务报告；•通过单元格保护功能统一设置好财务报告中各worksheet的格式；•各单位按照上述格式填报财务报告；•合并前收集齐各单位财务报告，用抽表工具抽取东会01-资产负债表中A1-E108单元格的内容（即资产负债表）。步骤1：选择财务报告集中存放的文档。直接按这个按键，会弹出以下窗口：步骤2：选择编码以“1”字开头的财务报告。图示如下：步骤3：勾选拟抽取的Sheet的名字。按“打开”按钮，会出现Workbook中的所有Sheet列表，从中勾选东会01-资产负债表。图示如下：步骤4：选取拟抽取的范围。从A1到E108单元格。图示如下：步骤5：选择是否需要链接。需要链接则输入“Y”，图示如下：步骤6：存放抽表结果，图示如下：步骤7：调整格式。打开104北京办事处财务报告，按照其资产负债表的格式，利用格式刷统一调整格式，图示如下：步骤8：将同一个Workbook里的不同Sheet的内容集中到一个Sheet里。以将12家单位资产负债表中的审计数按统一格式汇总到一个Sheet里为例进行演示。具体步骤如下：•首先插入一个新Sheet，命名为summary；•利用按钮，抽取每个单位D3至D108单元格的内容；•选择横排或纵排；•选择存放的位置；•选择是否需要链接；•选择是否需要排序。图示如下：演示链接功能•若104北京办事处的资产负债表数据需要修改，可以直接用修改后的Workbook替换原财务报告；•然后同时打开修改后的Workbook和东会01-资产负债表.linked，两个Excel文件会自动链接并进行更新。下图为修改前的104北京办事处和抽表汇总表，其中交易性金融资产为1,354,957.38元1,354,957.38将交易性金融资产金额修改为10万，自动链接后结果如下：五、sumif和vlookup两个函数的用法（一）Sumif（）函数用法简介•SUMIF函数：对符合指定条件的单元格求和；•SUMIF(range,criteria,sum_range)range是指用于筛选的单元格区域；criteria是筛选的条件，可以是数字、表达式或文本值，如≥100、＜100等；sum_range是实际的求和区域。演示1•将条件直接设置在函数里，对符合条件的区域进行汇总求和；•假设A1：A4中的数据是10、20、30、40，B1：B4中的数据是100、200、300、400，那么SUMIF（A1：A4，“15”，B1：B4）的值等于900；•那是因为A2、A3、A4中的数据满足条件，所以相对应的对B2、B3、B4中的数据进行计算。演示2•将条件应用于某个单元格区域，对另一个单元格区域中的对应值求和。•例如，SUMIF(A1:A4,D4,B1:B4)时，该函数仅对单元格区域A1:A4中与单元格区域B1:B4中等于D4单元格（即橘子）对应的单元格中的值求和。（二）Vlookup()函数用法简介•VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)判断引用数据函数；•Lookup_value为需要在数组第一列中查找的数值；•Table_array为需要在其中查找数据的数据表；•Col_index_num为table_array中待返回的匹配值的列序号；•Range_lookup为一逻辑值，指明函数VLOOKUP返回时是精确匹配还是近似匹配。演示：从财务部门获得零件的标准成本，从销售部门获得销售数量，计算已销售商品的成本。谢谢大家！