Excel 在账表管理中的应用.ppt

1、第八章 Excel 在账表管理中的应用,第一节 科目余额表 第二节 科目汇总表 第三节 分类账,第一节科目余额表,以会计凭证表为依据，可填列完成科目余额表，并根据凭证表、余额表生成各类账页，以利于财务管理。在案例 73 所建立的科目余额表中，已输入期初余额并试算平衡，现在进一步引用本期发生额并计算期末余额。,返回,下一页,第一节科目余额表,一、引用本期发生额 【技能储备】 1.会计明细账户发生额与其总账发生额的钩稽关系。 明细分类账是总分类账的明细记录，一个总分类账户的本期借方（贷方）发生额必然等于所属明细分类账户本期借方（贷方）发生额之和。 2. Excel 中数学与三角函数 SUMIF 函

2、数运用。 3.单元公式中单元格引用技巧。Excel 单元公式中，单元格的引用有“相对引用”和“绝对引用”两种，在案例 81 中，需掌握“相对引用”的应用。,返回,上一页,下一页,第一节科目余额表,【案例 81】将“会计凭证表”中本期发生额引入“科目余额表”“本期发生额”栏。 【操作提示】 （1）打开德龙公司“科目余额表”。 （2）选取 G6 单元格，单击“插入函数”按钮，选择“SUMIF”函数，将光标移至 Range自变量处，然后单击工作簿窗口左下角“会计凭证表”表标签，切换至“会计凭证表”工作表，然后输入 G:G，如图 81 所示。 （3）将光标移至 Criteria 自变量处，返回到“科目

3、余额表”页面，选择 A6 单元格。,返回,上一页,下一页,第一节科目余额表,（4）将光标移至 Sum_range 自变量处，单击“会计凭证表”表标签，接着输入 K:K，如图 82 所示。 （5）单击“确定”按钮，公式完成，此时，G6 单元格显示“库存现金”科目本期借方发生额 47 300 元。以上步骤（2）至（5）亦可在“科目余额表”G6 单元格直接输入公式“=SUMIF(会计凭证表!G:G,A6,会计凭证表!K:K)”。公式含义：将“会计凭证表”G 列数据中符合“科目余额表”A6 单元格数据条件（1001，“库存现金”的科目编码），相对应行的“会计凭证表”K 列数据之和置于 G6 单元格。即

4、：将凭证表“库存现金”借方发生额之和置于“科目余额表”G6 单元格内。,返回,上一页,下一页,第一节科目余额表,（6）将公式复制到 G6:G77 各单元格。 （7）上述公式只适用于末级科目借方发生额汇总，而非末级科目，则采用科目发生额向上汇总的方式。本练习中，借方发生额栏需编制如下汇总公式：,返回,上一页,下一页,第一节科目余额表,G9=G10 “应收票据”=所属明细账借方发生额之和 G22=G23+G24 G34=G35 G36=G37 G38=SUM（G39;G42） G44=SUM（G45:G47）,返回,上一页,下一页,第一节科目余额表,“库存商品”=A、B 产品明细账借方发生额之和

5、“应付账款”=所属明细账借方发生额之和 “预收账款”=所属明细账借方发生额之和 “应付职工薪酬”=所属明细账借方发生额之和 “应交税费应交增值税”=所属明细账借方发生额之和 G43=G44+G48+G49+G50+G51“应交税费”=所属明细账借方发生额之和G59=SUM（G60;G62） “利润分配”=所属明细账借方发生额之和,返回,上一页,下一页,第一节科目余额表,以上汇总公式输入G列相应单元格后，这些单元格内经步骤（6）复制的公式将被输入的新公式覆盖。 （8）选取“科目余额表”H6 单元格，单击“插入函数”按钮，选择“SUMIF”函数，将光标移至 Range 自变量处，然后单击工作簿窗口

6、左下角“会计凭证表”表标签，切换至“会计凭证表”工作表，然后输入 G:G，如图 83 所示。 （9）将光标移至 Criteria 自变量处，回到“科目余额表”页面，选择 A6 单元格。,返回,上一页,下一页,第一节科目余额表,（10）将光标移至 Sum_range 自变量处，单击“会计凭证表”标签，接着输入 L:L。 （11）单击“确定”按钮，公式完成。此时，H6单元格显示“库存现金”科目本期贷方发生额 47 860 元。以上步骤（8）至（11）亦可直接在 H6单元格输入公式“=SUMIF(会计凭证表!G:G,A6,会计凭证表!L:L)”。函数公式含义：将凭证表中“库存现金”贷方发生额之和置于

7、“科目余额表”H6 单元格。,返回,上一页,下一页,第一节科目余额表,（12）将公式复制到 H6:H77 各单元格内。 （13）同理，此公式也只适用于末级科目发生额汇总，对于非末级科目，则与汇总借方发生额时一样，编制上级科目汇总公式。本案例中，贷方发生额汇总需编制如下公式：,返回,上一页,下一页,第一节科目余额表,H9=H10 “应收票据”=所属明细账贷方发生额之和 H11=H12+H13 H18=H19+H20+H21 H22=H23+H24 H34=H35 H36=H37 H38=SUM（H39;H42） H44=SUM（H45:H47）,返回,上一页,下一页,第一节科目余额表,“应收账款

8、”=所属明细账贷方发生额之和 “原材料”=甲、乙、丙明细账贷方发生额之和 “库存商品”=A、B 产品明细账贷方发生额之和 “应付账款”=所属明细账贷方发生额之和 “预收账款”=所属明细账贷方发生额之和 “应付职工薪酬”=所属明细账贷方发生额之和 “应交税费应交增值税”=所属明细账贷方发生额之和,返回,上一页,下一页,第一节科目余额表,H43=H44+H48+H49+H50+H51“应交税费”=所属明细账贷方发生额之和H59=SUM（H60;H62） “利润分配”=所属明细账贷方发生额之和以上汇总公式输入 H 列相应单元格后，这些单元格内经步骤（12）复制的公式将被新公式覆盖。,返回,上一页,下

9、一页,第一节科目余额表,二、本期发生额试算平衡 经过本章 8.1.1 的操作，本期发生额已引用完毕，遵循“有借必有贷，借贷必相等”的记账规则，须对本期借贷方发生额进行试算平衡，务必使“本期借方总账发生额”之和等于“本期贷方总账发生额”之和。,返回,上一页,下一页,第一节科目余额表,【技能储备】 1.会计科目当期借贷方发生额的钩稽关系。根据记账规则： 全部账户本期借方发生额合计=全部账户本期贷方发生额合计2. Excel 中数学与三角函数 SUMIF 函数的运用。 3. Excel 公式中通配符的含义及运用。在 Excel 中，通配符“?”表示任何单个字符；通配符“*”表示任何字符数。,返回,上

10、一页,下一页,第一节科目余额表,【案例 82】对德龙公司“科目余额表”“本期发生额”试算平衡。 【操作提示】 （1）选取 G78 单元格，单击“ ”按钮，执行“插入函数”命令。 （2）选择 SUMIF 函数，在弹出的函数对话框中输入参数，如图 84 所示。 （3）单击“确定”按钮，此时，G78 单元格显示本期借方生发额之和 1 148 894。,返回,上一页,下一页,第一节科目余额表,以上 步骤（1）至（3）亦可在G78单元格直 接输入公式“=SUMIF(A6:A77,?, G6:G77)”。公式含义：在 A6:A77 单元区域查找一级科目的单元格，将与其相对应的 G6:G77 区域中符合条件

11、的单元格数据相加，并将计算结果置于 G78 单元格中。 （4）选取 H78 单元格，单击“ ”按钮，执行“插入函数”命令。 （5）选择 SUMIF 函数，在弹出的函数对话框中输入参数，如图 85 所示。,返回,上一页,下一页,第一节科目余额表,（6）单击“确定”按钮，此时，G78 单元格显示本期贷方生发额之和 1 148 894。 以上步骤（4）至（6）亦可在 H78 单元格直接输入公式“=SUMIF(A6:A77,?,H6:H77)”。 公式含义：在 A6:A77 单元区域查找一级科目的单元格，将与其相对应的 H6:H77 区域中符合条件的单元格数据相加，并将计算结果置于 H78 单元格中。

12、 （7）以上公式设置完成后，自动生成的数据应符合 G78=H78 的平衡关系，并与“会计凭证表”中借贷方发生额试算平衡数额相等，否则，可能公式设置错误；如若发生借贷发生额合计数不等的情况，应检查修改公式至平衡为止。,返回,上一页,下一页,第一节科目余额表,三、计算期末余额 【技能储备】 1.各类型会计科目余额的计算公式。期末余额的计算基本公式为： 资产、成本类账户期末余额=期初余额+本期借方发生额本期贷方发生额负债、所有者权益类账户期末余额=期初余额+本期贷方发生额本期借方发生额 2. Excel公式中单元格数据引用。在编制公式时需引用本表其他单元格数据，引用方式为相对引用。,返回,上一页,下

13、一页,第一节科目余额表,【案例 83】计算德龙公司 2008 年 12 月期末余额。 【操作提示】 （1）单击 I6 单元格，输入公式“=E6+G6H6”，将公式复制到 I 列中资产、成本类账户单元格内。 （2）单击 J32 单元格，输入公式“=F32+H32G32”，将公式复制至 J 列中负债、所有者权益账户单元格内（“应交税费应交增值税”账户各明细账除外）。,返回,上一页,下一页,第一节科目余额表,四、期末余额试算平衡 在 8.1.3 中，通过公式设置而计算得到的期末余额也应进行试算平衡。 【技能储备】 1.会计科目借贷方期末余额的钩稽关系。根据“资产=负债+所有者权益”的会计等式，本期所

14、有总账科目借方期末余额合计数应等于总账科目贷方期末余额合计数。平衡公式： 全部账户的借方期末余额合计=全部账户的贷方期末余额合计。 2. Excel 中数学与三角函数 SUMIF 函数运用技巧。,返回,上一页,下一页,第一节科目余额表,【案例 84】对德龙公司“科目余额表”设置期末余额试算平衡公式。 【操作提示】 （1）选取 I78 单元格，单击编辑栏 按钮。 （2）在函数选择对话框中选择 SUMIF 函数，在弹出的界面中输入参数，如图 86 所示。,返回,上一页,下一页,第一节科目余额表,（3）单击“确定”按钮。 以上步骤（1）（3）亦可在 I78 单元格内输入公式“=SUMIF(A6:A7

15、7,?,I6:I77)”。 （4）单击 J78 单元格，单击编辑栏 按钮。 （5）在函数选择对话框中选择 SUMIF 函数，在弹出的界面中输入参数，如图 87 所示。 （6）单击“确定”按钮。 以上步骤（4）（6）亦可在 J78 单元格内输入公式“=SUMIF(A6:A77,?,J6:J77)”。 公式输入完成后，生成的数据应符合 I78=J78 的平衡关系。如图 88 所示。,返回,上一页,第二节科目汇总表,【技能储备】 1.科目汇总表的作用及格式。科目汇总表在会计核算中起着承上启下的作用：一方面，将一定会计期间发生的经济业务进行汇总；另一方面，为编制会计报表提供数据。 2. Excel中数

16、据透视表的建立及编辑操作技巧。数据透视表是交互式报表，可快速合并和比较大量数据，如果要合并较大的列表并对每个数据进行多种比较时，可以使用数据透视表。,返回,下一页,第二节科目汇总表,创建数据透视表可通过“数据”菜单下的“数据透视表和数据透视图”命令，采用“向导式”操作方法进行。其创建向导分为三个步骤：第一步，选择待分析数据源的数据类型并指定创建的报表类型（是创建数据透视表还是数据透视图）；第二步，输入或选定要建立数据透视表的数据源区域（即选择需分析的数据范围及位置）；第三步，选择数据透视表的布局并指定数据透视表的显示位置。 通过以上三步建立数据透视表后，在数据透视表页面，Excel 自动给出数

17、据透视表工具栏，利用此工具栏，可对数据透视表中的数据进行增加、显示、隐藏、删除等编辑修改操作。在编制科目汇总表时，可应用 Excel“数据透视表”功能。,返回,上一页,下一页,第二节科目汇总表,【案例 85】编制德龙公司 2008 年 12 月科目汇总表。科目汇总表基本格式如表 81 所示。 【操作提示】 （1）打开德龙公司“会计凭证表”。 （2）选择“数据/数据透视表和数据透视图”命令（如图 810 所示），在弹出的“数据透视表和数据透视图向导一”对话框中，“请指定待分析数据的数据源类型”选项选择“MicrosoftOffice Excel 数据列表或数据库”；在“所需创建的报表类型”选项选

18、择“数据透视表”。如图811 所示。 （4）在“选定区域”项选定数据区域“会计凭证表!$A$2:会计凭证表!$L$85”，如图 812所示。,返回,上一页,下一页,第二节科目汇总表,（5）单击“下一步”，进入向导三，如图 813 所示。 （6）在“数据透视表显示位置”项选择“新建工作表”。 （7）单击“布局”按钮，进入“数据透视表和数据透视图向导布局”，如图 814。 （8）在“数据透视表和数据透视图向导布局”对话框中，将“年”、“月”拖至“页”区域。如图 815 所示。,返回,上一页,下一页,第二节科目汇总表,（9）在“数据透视表和数据透视图向导布局”对话框中，将“科目编码”、“一级科目名称

19、”、“二级科目名称”拖动到“行”区域。 （10）在“数据透视表和数据透视图向导布局”对话框中，将“借方金额”、“贷方金额”拖动到“数据”区域。 （11）双击刚才拖至“数据”区域的“借方金额”，弹出“数据透视表字段”对话框。如图 816 所示。 （12）在对话框“汇总方式”栏选择“求和”，如图 817 所示。,返回,上一页,下一页,第二节科目汇总表,（13）单击“数字”按钮，弹出“单元格格式”对话框；在对话框“分类”中选择“会计 专用”，“小数位数”选择“2”，在“货币符号”栏选择“无”。如图 818 所示。 （14）单击“确定”按钮，返回到“数据透视表字段”对话框，再单击。 （15）双击“数据

20、”区域“贷方金额”，在弹出的“数据透视表字段”对话框中，重复（12）至（14）步骤，将贷方金额“汇总方式”更改为求和，数字格式设置为“会计专用”。操作完成后，“数据透视表”布局对话框如图 819 所示。此时可看到：“数据”栏中“借方金额”、“贷方金额”已由“计数项”改为“求和项”（与图 815 相比较）。,返回,上一页,下一页,第二节科目汇总表,（16）单击“确定”按钮，返回“数据透视表和数据透视图向导三”对话框。单击“完成”按钮。 （17）在新建工作表中显示出数据透视表，如图 820 所示。 （18）将这张新工作表移至“工资表”后，更名为“科目汇总表”。 （19）选择第 1 行，执行“插入/

21、行”命令；选择刚插入的 A1:E1，单击“合并及居中” 按钮，在合并单元格中输入“德龙公司科目汇总表”，调整好行高列宽。如图 821 所示。,返回,上一页,下一页,第二节科目汇总表,（20）在 B2 单元“年”选项处选择“08”，B3“月”选项处选择“12”。如图 822 所示。 （21）将鼠标移至“数据”标题字段处，按下左键，拖动“数据”至“汇总”字段处。 （22）松开鼠标键。 （23）选择“科目编码”中任意单元格并单击鼠标右键，在弹出的菜单中选择“字段设置”命令。如图 823 所示。,返回,上一页,下一页,第二节科目汇总表,（24）在弹出的“数据透视表字段”对话框的“分类汇总”中选择“无”

22、选项，表示对科 目编码不进行汇总。如图 824 所示。 （25）单击“高级”按钮，弹出“数据透视表字段高级选项”对话框，在“自动排序选项” 栏选择“升序”，表示按科目编码数字由小到大排列。如图 825 所示。 （26）单击“确定”按钮，返回“数据透视表字段”对话框，单击“完成”按钮。此时， 科目汇总表变为如图 826 所示。,返回,上一页,下一页,第二节科目汇总表,（27）在“一级科目名称”栏单击任意单元格，点击鼠标右键，在弹出菜单中选择“字段设置”命令。 （28）在弹出的“数据透视表字段”对话框“分类汇总”栏选择“无”，单击“确定”按 钮，此时，汇总表如图 827 所示。 （29）单击科目汇

23、总表任意单元格并右击，在弹出快捷菜单中选择“数据透视表向导”命令，进入“数据透视表和数据透视图向导三”对话框，如图 828 所示。,返回,上一页,下一页,第二节科目汇总表,（30）单击“选项”按钮，进入“数据透视表选项”对话框，如图 829 所示。 （31）在对话框“格式选项”栏“合并标志”前打上选项。 （32）单击“确定”按钮，返回“数据透视表和数据透视图向导三”对话框，单击“完成” 按钮。此时，汇总表格式如图 830 所示。通过以上操作步骤，完成德龙公司科目汇总表编制。若初次运用“数据透视表”，可能认为较复杂，但熟悉其操作步骤后，就会觉得非常简单。,返回,上一页,第三节分类账,分类账包括总

24、分类账和明细分类账。 一、总分类账 总分类账也称总账，是根据会计制度规定的一级会计科目开设的，用以全面、总括地反映、记录全部经济业务的账簿。总分类账最常用的格式为三栏式，设置借方、贷方和余额三个基本金额栏目。,返回,下一页,第三节分类账,【技能储备】 1.总在 Excel 中，总账的生成方法有两种：一种是用科目余额表经筛选形成；另一种是三栏式账页，每个一级科目一个账页。此处介绍科目余额表式总账的生成。 2. Excel 中“自动筛选”功能的运用技巧。Excel 的数据筛选有自动筛选和高级筛选两种方式，生成总分类账时需用到“自动筛选”功能。,返回,上一页,下一页,第三节分类账,【案例 86】建立

25、德龙公司 2008 年 12 月总账工作表。 【操作提示】 （1）打开德龙公司“科目余额表。 （2）选取 A4:J78 区域，单击“数据/筛选/自动筛选”命令。 （3）打开“科目编码”下拉清单，选择“自定义自动筛选方式”，如图 831 所示。 （4）在弹出的“自定义自动筛选方式”对话框中，逻辑关系选择“等于”。如图 832 所示。,返回,上一页,下一页,第三节分类账,（5）科目编码条件定义为一级科目，即科目编码为 4 位，输入“?”，单击“确定”按钮。 （6）选择 C、D 两列，单击右键，在弹出快捷菜单中选择“隐藏”命令（将明细科目列隐藏），完成后得到德龙公司总账。如图 833 所示。,返回,

26、上一页,下一页,第三节分类账,二、明细分类账 明细分类账也称明细账，是根据总账科目所属的明细科目而设置，用于分类、连续地登记经济业务以提供明细核算资料的账簿。明细账分为三栏式明细账和多栏式明细账。 【技能储备】 1. Excel 中“高级筛选”功能的运用技巧。与“自动筛选”不同的是，“高级筛选”需构造条件区域，用以满足数据查找的需要。,返回,上一页,下一页,第三节分类账,2. Excel 中逻辑函数 IF、文本函数 TRIM 等函数及嵌套函数的运用。TRIM 函数用于除了单词之间的单个空格外，清除文本中所有的空格。函数公式如下： = TRIM(Text)式中，参数 Text 为需要清除其中空格

27、的文本。 【案例 87】编制德龙公司“库存商品A 产品”明细账。此处以德龙公司为案例，讲述三栏式明细账的生成（操作方法也适用于日记账的生成）。,返回,上一页,下一页,第三节分类账,【操作提示】 （1）打开德龙公司总账工作簿，新建一工作表并更名为“明细账”，将新工作表移至“总账”工作表之后。 （2）选取 A1:N1，单击“合并及居中”按钮，在合并单元格内输入“库存商品明细账”，调整好行高、列宽、字体、字号。 （3）单击 A2 单元格，输入“科目编码”；单击 A3 单元格，输入 A 产品科目编码“140501”（最好是将“会计科目表”中的 A 产品编码复制过来，保证条件区域与源数据区域的一致性；此

28、处即为条件区域）。如图 834 所示。,返回,上一页,下一页,第三节分类账,（4）单击“数据/筛选/高级筛选”命令，出现“高级筛选”窗口，如图 835 所示。 （5）在“方式”栏选择“将筛选结果复制到其他位置” 选项；在“列表区域”处输入（也 可用鼠标点击引用）“会计凭证表!$A$2:会计凭证表!$L$85”（此处为设置源数据区域。 （6）在“条件区域”栏输入“明细账！$A$2:明细账!$A$3”（即在第（3）步骤中已定义好的条件区域）。如图 836 所示。,返回,上一页,下一页,第三节分类账,（7）在“复制到”栏输入要将数据显示的位置：“明细账!$A$4”。 （8）单击“确定”按钮，得到符合

29、条件的明细账项。如图 837 所示。 （9）此时，“库存商品A 产品”的本期明细数据已显示在明细账页上；在第 4、5 行之间插入一空白行，在“摘要”栏内输入“期初余额”；单击 M4 单元格，输入“方向”；单击 N4，输入“余额”。,返回,上一页,下一页,第三节分类账,（10）单击 M5 单元格，输入期初余额方向“借”；单击 N5 单元格，输入公式“= 科目余额表!E23”（亦可采用科目余额表单元格引用的方法），表示引用“科目余额表”中 E23 单元格数据（即 A 产品期初余额）。 （11）单击M6单元格，输入公式“ =IF(IF(TRIM(M5)=借 ,N5,N5)+K6L6)0,借,IF(IF(TRIM(M5)=借,N5,N5)+K6L6)0,贷,平)”，此公式用于自动判断余额借贷方向。将公式复制到 M 列各行单元格内。如图 838 所示。,返回,上一页,下一页,第三节分类