第3章 Excel在会计账簿中的应用（课件）《Excel在财会管理中的应用（第二版）》（水利电力出版社）

第3章 Excel在会计账簿中的应用 本章学习目标 本章主要讲解会计账簿的相关知识以及如何利用Excel设置和登记会计账簿。通过本章的学习，掌握以下内容： 会计账簿的概念、意义、种类和格式 明细账的格式设置和登记方法 总账的格式设置和登记方法 3.1 会计账簿概述 3.1.1 会计账簿的意义 3.1.2 会计账簿的种类 3.1.1 会计账簿的意义 会计账簿是全面记录和反映一个单位经济业务，把大量分散的数据或资料进行归类整理，逐步加工成有用会计信息的簿籍，它是编制会计报表的重要依据。它是由具有一定格式、相互连结的账页所组成，账页一旦标上会计科目，它就成为用来记录和反映该科目所规定核算内容的账户。 3.1.1 会计账簿的意义 通过设置和登记会计账簿： （1）能够把大量的、分散的会计核算资料系统化，为加强经济核算提供资料。 （2）可以为正确计算成本、经营成果和收益分配等提供必要的会计核算资料。 （3）利用会计账簿所提供的核算资料，既可以为编制会计报表提供主要的依据，也可以为会计分析和会计检查提供必要的依据。 （4）既有利于会计核算资料的保存和利用，也有利于会计核算工作的分工。 3.1.2 会计账簿的种类 1．会计账簿按用途的分类 会计账簿按其用途，可以分为序时账簿、分类账簿和备查（辅助）账簿。 2．会计账簿按外表形式的分类 会计账簿按其外表形式，可以分为订本式账簿、活页式账簿和卡片式账簿。 3．会计账簿按格式的分类 会计账簿按其格式可分为三栏式账簿、多栏式账簿和数量金额式账簿。 3.2 会计账簿的登记 3.2.1 现金日记账的设置与登记 3.2.2 银行存款日记账的设置与登记 3.2.3 明细账的设置与登记 3.2.4 总账的格式和登记 3.2.1 现金日记账的设置与登记 现金日记账是用来逐日反映库存现金的收入、付出及结余情况的特种日记账。它是由单位出纳人员根据审核无误的现金收、付款凭证和从银行提现的银付凭证逐笔进行登记的。为了确保账簿的安全、完整，现金日记账必须采用订本式账簿。现金日记账的格式一般有“三栏式”、“多栏式”和“收付分页式”三种。在实际工作中大多采用的是“三栏式”账页格式。 3.2.1.1 建立“登记现金日记账”工作表 1．在Excel中新建一工作簿，将其命名为“现金日记账的登记”进行保存，双击Sheet1标签，将其命名为“登记现金日记账”，对其进行内容与格式的设计。 2．对工作表进行格式设计。 3．设计会计科目表，在“现金日记账的登记”工作簿中，双击Sheet3工作表标签，将其命名为“会计科目表”。按新会计准则设置会计科目，也可将在第2章中使用的“会计科目表”复制过来。 3.2.1.1 建立“登记现金日记账”工作表 4．对“登记现金日记账”工作表进行公式和数据有效性设计 （1）单击A3单元格，输入公式“=YEAR(TODAY())&"年"”。 （2）设置输入月份的数据有效性规则。 （3）设置输入日期的数据有效性规则。 （4）设置输入对方科目的数据有效性规则。 （5）设置借、贷方金额的数据有效性规则。 （6）设置余额公式 5．对表进行保护 3.2.1.2 建立“输出现金日记账”工作表 1．在“现金日记账的登记”工作簿中，双击Sheet2标签，将其命名为“输出现金日记账”，对其进行内容与格式的设计。 2．公式设置 这样，“输出现金日记账”工作表的公式便设置完成了，用户接下来可以对其进行保护设置。 3.2.2 银行存款日记账的设置与登记 银行存款日记账是专门用来记录银行存款收支业务的一种特种日记账。银行存款日记账必须采用订本式账簿，其账页格式一般采用“收入”（借方）、“支出”（贷方）和“余额”三栏式。银行存款收入数额应根据有关的现金付款凭证登记。 3.2.2 银行存款日记账的设置与登记 1．建立银行存款日记账，将工作表命名为“中国工商银行”。（假设该单位的开户行除中国工商银行以外，还有中国银行和中国农业银行，中国银行和中国农业银行的日记账分别存放在“中国银行”和“中国农业银行”工作表中，在此省略。） 2．建立银行存款日记账汇总表。 3．输入期初数据及设计公式 SUMIF函数可以对区域中符合指定条件的值求和。其语法为：SUMIF(range,criteria,[sum_range])。其中：range表示用于条件计算的单元格区域。每个区域中的单元格都必须是数字或名称、数组或包含数字的引用。空值和文本值将被忽略。Criteria表示用于确定对哪些单元格求和的条件，其形式可以为数字、表达式、单元格引用、文本或函数。任何文本条件或任何含有逻辑或数学符号的条件都必须使用双引号括起来。如果条件为数字，则无需使用双引号。sum_range表示要求和的实际单元格。如果省略sum_range参数，Excel会对在范围参数中指定的单元格求和。使用SUMIF函数匹配超过255个字符的字符串时，将返回不正确的结果#VALUE!。sum_range参数与range参数的大小和形状可以不同。求和的实际单元格通过以下方法确定：使用sum_range参数中左上角的单元格作为起始单元格，然后包括与range参数大小和形状相对应的单元格。 3.2.3 明细账的设置与登记 3.2.3.1明细账的格式 明细账是按照明细分类账户开设的，连续、分类登记某一类经济业务，提供详细核算资料的账簿。它能够具体、详细地反映经济活动情况，对总分类账起辅助和补充作用，同时也为会计报表的编制提供必要的明细资料。 明细账的格式有三栏式、多栏式、数量金额式等，但最基本的格式是有“借方”、“贷方”、“余额”三栏的三栏式 。 3.2.3 明细账的设置与登记 3.2.3.2 数据库 （1）数据清单的字段名占一行，且必须是第一行（不一定是工作表中的第一行）； （2）每个字段的名称必须惟一； （3）每一列必须有相同的数据类型和格式，且每一列必须相邻； （4）每条记录占一行，且必须连续，中间不能有空行； （5）每个单元格输入的数据前面不能有空格； （6）如果工作表内除数据清单外还有其他数据，则数据清单和其他数据之间必须有至少一行或一列的空白单元格； （7）避免在一个工作表上建立多个数据清单。 3.2.3 明细账的设置与登记 3.2.3.3 数据库的生成 利用数据库生成明细账的基本思路是：首先设置数据库的结构，其次将所录入的记账凭证内容通过一定的方法输入到数据库中，最后对数据库中的数据进行筛选，生成明细账。 3.2.3 明细账的设置与登记 （1）设置条件区域。 （2）选择数据区域。 （3）执行“高级筛选”命令。在“数据”选项卡中单击中“排序和筛选”数据组中的“高级”命令，打开“高级筛选”对话框。 在对话框中，提供了两种筛选方式：“在原有区域显示筛选结果”和“将筛选结果复制到其他位置”。如果希望通过在原有数据区域隐藏不符合条件的数据行来筛选数据清单，则选中“在原有区域显示筛选结果”；如果希望通过将符合条件的数据行复制到工作表的其他位置来筛选数据清单，则选中“将筛选结果复制到其他位置”。 （5）复制筛选结果。 （6）粘贴筛选结果。 （7）设置余额计算公式。 （8）设置余额方向。 （9）计算本期发生额和期末余额。 3.2.3 明细账的设置与登记 3.2.3.4 动态明细账的制作方法 1．设计明细账 2．制作“期初余额表” 3．为明细账设置公式 （1）单击A1单元格，输入公式“C3&"--"&C4&"明细账"”。 （2）输入会计科目编码 INDEX函数：返回表格或区域中的值或值的引用。它有两种形式：数组形式和引用形式。这里重点介绍引用形式。其语法为：INDEX(reference,row_num,[column_num],[area_num])，其中：reference表示对一个或多个单元格区域的引用。如果为引用输入一个不连续的区域，必须将其用括号括起来。如果引用中的每个区域只包含一行或一列，则相应的参数row_num或column_num分别为可选项。row_num表示引用中某行的行号，函数从该行返回一个引用。column_num表示引用中某列的列标，函数从该列返回一个引用。area_num表示选择引用中的一个区域，以从中返回row_num和column_num的交叉区域。如果将row_num或column_num设置为0，函数INDEX分别返回对整列或整行的引用。Row_num、column_num和area_num必须指向reference中的单元格；否则，函数INDEX返回错误值#REF!。如果省略row_num和column_num，函数INDEX返回由area_num所指定的引用中的区域。 3.2.3 明细账的设置与登记 MATCH函数：可在单元格区域中搜索指定项，然后返回该项在单元格区域中的相对位置。例如，如果单元格区域A1:A3包含值5、25和38，输入公式“=MATCH(25,A1:A3,0)”确认后会返回数字2，因为值25是单元格区域中的第二项。其语法为： MATCH(lookup_value,lookup_array,[match_type]) 其中：lookup_value表示需要在lookup_array中查找的值。lookup_value参数可以为值（数字、文本或逻辑值）或对数字、文本或逻辑值的单元格引用。lookup_array表示要搜索的单元格区域。match_type为数字-1、0或1，指定Excel如何在lookup_array 中查找lookup_value的值。此参数的默认值为1。lookup_array 参数中的值必须按降序排列。注意：MATCH函数会返回lookup_array中匹配值的位置而不是匹配值本身。在查找文本值时，MATCH函数不区分大小写字母。如果MATCH函数找不到指定的数据，它会返回错误值#N/A。 （3）输入日期 3.2.3 明细账的设置与登记 （4）输入期初余额 使用VLOOKUP函数可以搜索某个单元格区域的第一列，然后返回该区域相同行上任何单元格中的值。其语法为：VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])，其中：lookup_value表示要在表格或区域的第一列中搜索的值，它可以是值或引用。如果为lookup_value参数提供的值小于table_array参数第一列中的最小值，则VLOOKUP将返回错误值#N/A。table_array表示含数据的单元格区域。可以使用对区域或区域名称的引用。table_array第一列中的值是由lookup_value搜索的值。这些值可以是文本、数字或逻辑值。文本不区分大小写。col_index_num表示table_array参数中必须返回的匹配值的列号。col_index_num参数为1时，返回table_array第一列中的值；col_index_num为2时，返回table_array第二列中的值，依此类推。如果col_index_num参数小于1，则VLOOKUP返回错误值#VALUE!。如果大于table_array的列数，则VLOOKUP返回错误值#REF!。range_lookup是一个逻辑值，指定希望VLOOKUP查找精确匹配值还是近似匹配值。如果range_lookup为TRUE或被省略，则返回精确匹配值或近似匹配值。如果找不到精确匹配值，则返回小于lookup_value的最大值。如果range_lookup参数为FALSE，VLOOKUP将只查找精确匹配值。如果table_array的第一列中有两个或更多值与lookup_value匹配，则使用第一个找到的值。如果找不到精确匹配值，则返回错误值#N/A。 3.2.3 明细账的设置与登记 （5）在“记账凭证数据库”中查找符合条件的记录 OFFSET函数可以以指定的引用为参照系，通过给定偏移量得到新的引用。返回的引用可以为一个单元格或单元格区域。并可以指定返回的行数或列数。其语法为：OFFSET(reference,rows,cols,[height],[width])。其中：Reference作为偏移量参照系的引用区域。Reference必须为对单元格或相连单元格区域的引用，否则，OFFSET返回错误值#VALUE!。Rows表示相对于偏移量参照系的左上角单元格，上（下）偏移的行数。如果使用2作为参数Rows，则说明目标引用区域的左上角单元格比reference低2行。行数可为正数（代表在起始引用的下方）或负数（代表在起始引用的上方）。Cols表示相对于偏移量参照系的左上角单元格，左（右）偏移的列数。如果使用2作为参数Cols，则说明目标引用区域的左上角的单元格比reference 靠右2列。列数可为正数（代表在起始引用的右边）或负数（代表在起始引用的左边）。Height表示高度，即所要返回的引用区域的行数。其值必须为正数。Width表示宽度，即所要返回的引用区域的列数。其值必须为正数。如果省略height或width，则假设其高度或宽度与reference相同。 3.2.4 总账的格式和登记 总账是指总分类账簿，也称总分类账，是根据总分类科目开设账户，用来登记全部经济业务，进行总分类核算，提供总括核算资料的分类账簿。总分类账所提供的核算资料，是编制会计报表的主要依据，任何单位都必须设置总分类账。总分类账一般采用订本式账簿。总分类账的账页格式，一般采用“借方”、“贷方”、“余额”在栏式，根据实际需要，也可以在“借方”、“贷方”两栏内增设“对方科目”栏。总分类账的登记依据和方法，主要取决于所采用的会计核算形式。它可以直接根据各种记账凭证逐笔登记，也可以先把记账凭证按照一定方式进行汇总，编制成科目汇总表或汇总记账凭证等，然后，据以登记。 3.2.4 总账的格式和登记 3.2.4.1 编制试算平衡表 1．设计试算平衡表结构。 2．为方便操作，将前述已建立的会计期初余额表和记账凭证数据库两张工作表复制到“登记总账”工作簿中。 3．引用日期、期初余额的借、贷方余额。 4．计算会计科目的借、贷方发生额及期末余额。 ABS函数可以返回数字的绝对值。绝对值没有符号。其语法为：ABS(number)，其中：Number为需要计算其绝对值的实数。 通过以上操作，试算平衡表便制作完成了。 3.2.4 总账的格式和登记 3.2.4.2 登记总账 （1）建立“应付账款”总账。 （2）打开“记账凭证数据库”，输入筛选条件，形成筛选条件区域。 （3）单击数据清单中的任何一个单元格。 （4）执行高级筛选命令。单击“数据”选项卡中的“排序和筛选”命令组的“高级”按钮，弹出“高级筛选”对话框，在其中选择输出方式，输入数据区域、条件区域、存放位置 （5）单击“确定”按钮。 （6）复制和粘贴筛选结果。 （7）设置余额计算公式和方向。 （8）设置本期发生额和期末余额。 $$