Excel在会计与财务中的应用课件：Excel在会计账簿中的应用

4.1建立科目汇总表4.2建立科目余额表4.3建立会计账簿4.4专家设置

Excel在会计账簿中的应用4.1建立科目汇总表4.1.1设计科目汇总表4.1.2生成科目汇总表2024/4/182步骤1：在“会计凭证表”后新建工作表，重命名为“科目汇总表”。步骤2：从“会计科目表”中复制“科目代码”与“总账科目”两列，粘贴到“科目汇总表”的A、B两列。选中A、B列，在“开始”功能区“编辑”组中打开“清除”下拉框，选择“清除格式”命令，如图所示。4.1.1设计科目汇总表步骤3：保留一级科目代码，删除6~8位代码所在行。方法一：选中A2单元格，点击“数据”功能区升序排列快捷键，将科目信息按照“科目代码”进行升序排序，如图所示。排序后删除后几行（6~8位代码所在行），只保留4位代码（一级代码），如图所示。4.1.1设计科目汇总表方法二：选中A、B两列，在“数据”功能区“数据工具”组中选择“删除重复项”命令，如图所示。弹出“删除重复项”对话框，在“列”复选框中只选择“总账科目”，单击“确定”，如图所示。在弹出的提示框中单击确定，如图所示，这样就只保留了一级科目代码和唯一的总账科目。4.1.1设计科目汇总表步骤4：将A1单元格改为“总账代码”，C1单元格输入“借方金额”，D1单元格输入“贷方金额”，如图所示。4.1.1设计科目汇总表根据“会计凭证表”生成“科目汇总表”，即根据凭证表中的“总账代码”汇总成科目汇总表，利用SUMIF函数即可解决。步骤1：选中C2单元格，在编辑栏输入公式：=SUMIF(会计凭证表!G:G,科目汇总表!A2,会计凭证表!I:I)，如图所示。将鼠标放在C2单元格右下角，双击“填充柄”填充C列。公式解析：此处SUMIF函数的含义为：在“会计凭证表”的G列（“总账代码”列）中匹配“科目汇总表”中A2单元格的数据（1001），匹配成功的行对“会计凭证表”的I列（“借方金额”列）求和。4.1.2生成科目汇总表步骤2：在D2单元格编辑栏输入公式：=SUMIF(会计凭证表!G:G,科目汇总表!A2,会计凭证表!J:J)，并利用填充柄填充D列。步骤3：美化科目汇总表。选择“套用表格格式”下拉框，选择“浅绿”样式，在弹出的“套用表格式”对话框中单击确定，如图所示。制作好的科目汇总表如图所示。4.1.2生成科目汇总表4.2建立科目余额表4.2.1设计科目余额表4.2.2生成科目余额表2024/4/189步骤1：在“科目汇总表”标签后点击新建工作表，重命名为“科目余额表”。步骤2：复制“科目汇总表”A、B两列，粘贴在“科目余额表”A:B列，如图所示。4.2.1设计科目余额表步骤3：在C1单元格输入“余额方向”。选中C2单元格在编辑栏输入公式=VLOOKUP(A2,会计科目表!A:E,4,FALSE)，如图所示，并利用填充柄填充C列。公式解析：此处VLOOKUP函数的含义为：以A2单元格的数据“1001”为线索，在“会计科目表”A:E列的首列中精确匹配“1001”所在行，取该行第4列（即D列“余额方向”）的数据。4.2.1设计科目余额表步骤4：选中第2行，插入整行，如左图所示。选中D1-E1，在“开始”功能区“对齐方式”组中选择“合并后居中”命令，输入“期初余额”；并合并A1:A2、B1:B2、C1:C2、F1:G1、H1:I1；在D2单元格输入“借方”，E2单元格输入“贷方”；在F1输入“本期发生额”，F2输入“借方”，G2输入“贷方”；在H1输入“期末余额”，H2输入“借方”，I2输入“贷方”，如右图所示。4.2.1设计科目余额表步骤1：填写期初余额，根据案例，将7月份期末余额即8月份期初余额填入D、E两列，如图所示。4.2.2生成科目余额表步骤2：填写本期发生额公式。F3单元格编辑栏输入公式：=VLOOKUP(A3,科目汇总表!A:D,3,FALSE),如图所示，双击填充柄填充F列。同理，G3单元格编辑栏输入公式：=VLOOKUP(A3,科目汇总表!A:D,4,FALSE)，双击填充柄填充G列。公式解析：此处VLOOKUP函数的含义为：以A3单元格的数据“1001”为线索，在科目汇总表A:D列的首列中精确匹配“1001”所在行，取该行第3列（即C列“借方金额”）的数据。4.2.2生成科目余额表步骤3：H3单元格编辑栏输入公式：=IF(C3="借",D3+F3-G3,0)，如图所示，双击填充柄填充H列。同理，I3单元格编辑栏输入公式：=IF(C3="贷",E3+G3-F3,0)，双击填充柄填充I列。公式解析：此处IF函数的含义为：对C3单元格的余额方向进行判断，如果C3单元格是“借”字，那么“期末借方余额”为“期初借方余额”+“本期借方发生额”-“本期贷方发生额”，否则（即C3是“贷”字）期末借方余额为0。4.2.2生成科目余额表步骤4：美化科目余额表。由于科目余额表标题分为2行，不能直接使用“套用表格格式”。简单美化一下：先选中标题行，字体设置为加粗，格式设置为居中；再选中整个表格区域，添加边框。制作完成的“科目余额表”如图所示。4.2.2生成科目余额表4.3建立会计账簿4.3.1日记账4.3.2明细账4.3.3总账2024/4/1817日记账是由出纳人员根据审核后的现金（银行）收、付款记账凭证和银行付款凭证，逐日逐笔按顺序登记，反映现金、银行存款增减变动情况的一种特种日记账。B传媒公司凭证类别为记账凭证，没有现金收讫，只需要建立银行存款日记账，应根据包含“银行存款”科目的凭证登记。现金日记账（若有）做法相同。步骤1：选中“会计凭证表”，在“插入”功能区选择“数据透视表”。如图所示。需要注意的是，在进行此操作前，需要将“会计凭证表”撤销工作表保护。2024/4/18184.3.1日记账步骤2：在打开的“创建数据透视表”对话框中，“表/区域”文本框选择会计凭证表区域，并在“选择放置数据透视表的位置”处选择“新工作表”，单击“确定”。如图所示。2024/4/18194.3.1日记账步骤3：设置数据透视表字段。将“总账科目”字段放置“筛选”区域，并在B1单元格中选择“银行存款”项目。将“日期”、“凭证号”、“摘要”字段放入“行”区域，将“借方金额”、“贷方金额”放入“值”区域。如图所示。2024/4/18204.3.1日记账步骤4：值字段设置。双击B3单元格，在弹出的值字段设置窗口中，将字段数据的计算类型修改为“求和”，如图所示。同理修改C3单元格，修改后如图所示。2024/4/18214.3.1日记账步骤5：在“数据透视表工具”的“设计”功能区选择“分类汇总”下拉菜单，选择“不显示分类汇总”命令。如图所示。2024/4/18224.3.1日记账步骤6：在“数据透视表工具”的“设计”功能区选择“报表布局”下拉菜单，选择“以表格形式显示”命令，如图所示。2024/4/18234.3.1日记账明细分类账是分类账簿的一种，主要反映各个明细科目的具体发生额。明细分类账利用数据透视表的分类汇总功能将日记账的数据进行统计汇总，通过选择明细科目的方法，查看明细科目表的所有业务数据。其做法与日记账做法相似。步骤1：方法一：按照“银行存款日记账”制作方法，制作明细账的“数据透视表”。方法二：在“银行存款日记账”标签右击，选择“移动或复制”，在“移动或复制工作表”对话框中选择“科目汇总表”之前，并选择“建立副本”，单击确定。如图所示。2024/4/18244.3.2明细账步骤2：将工作表标签更名为“明细账”。点击数据透视表中任意一个单元格，单击右键选择“显示字段列表”，如下图所示。在字段列表中删除原有筛选字段“总账科目”，将“账户名称”放入筛选器区域。如右图所示。2024/4/18254.3.2明细账当需要查询时，点击B1单元格，选择明细科目即可，如图所示。2024/4/18264.3.2明细账总账的制作也可以根据“会计凭证表”利用“数据透视表”制作。步骤1：按明细账的方法建立总账副本。将“总账”工作表放置在“明细账”之后。步骤2：更改“数据透视表字段”区域，筛选区域放置“日期”，行区域放置“总账代码”和“总账科目”，值区域放置“借方金额”和“贷方金额”，如图所示。2024/4/18274.3.3总账步骤3：在“数据透视表工具”的“分析”功能区中打开“字段、项目和集”下拉菜单，选择“计算字段”命令。如图所示。2024/4/18284.3.3总账步骤4：在“插入计算字段”对话框的“名称”文本框输入“借方余额”，“公式”文本框中输入“=IF((借方金额-贷方金额)>0,借方金额-贷方金额,0)”，如图所示。同样的，再插入一个“贷方余额”计算字段，公式为“=IF((贷方金额-借方金额)>0,贷方金额-借方金额,0)”。设置完毕后结果如图所示。2024/4/18294.3.3总账4.4专家设置4.4.1每一页都打印标题行设置4.4.2设置背景2024/4/1830在打印行数较多的表格时，往往后几页由于缺少表头而无法区分各表格的内容。如图所示，很难判断实收资本的100000元是在哪一个会计期间。因此在打印时，需要让每一页顶端都显示标题行才好。2024/4/18314.4.1每一页都打印标题行设置4.4.1每一页都打印标题行设置步骤1：在“页面布局”功能区“页面设置”组中选择“打印标题”命令。步骤2：在弹出的“页面设置”|“工作表”选项卡下，“打印标题”区域中“顶端标题行”选择第1~2行，然后单击“打印”按钮，如图所示。4.4.1每一页都打印标题行设置步骤1：在“页面布局”功能区“页面设置”组中选择“打印标题”命令。步骤2：在弹出的“页面设置”|“工作表”选项卡下，“打印标题”区域中“顶端标题行”选择第1~2行，然后单击“打印”按钮，如图所示。4.4.1每一页都打印标题行设置此时，打印的第2页就会出现标题行，如图所示。4.4.2设置背景有些公司在编辑Excel表格中要加入公司的背景或者公司的LOGO，具体操作步骤如下：步骤1：打开需要添加背景的工作表，然后在“页面布局”选项卡的“页面设置”组中单击“背景”按钮，如图所示。4.4.2设置背景步骤2：弹出“插入图片”对话框，选择图片来源，如图所示。4.4.2设置背景步骤3：弹出“工作表背景”对话框，选择要插入的图片，然后单击“插入”按钮，如图所示，这样就添加好了背景。思考练习

C有限责任公司2020年8月份发生如下业务：(1)2日，购买甲商品25000元，购买乙商品28000元，增值税税率为9%，商品已入库，以银行存款支付货款。(2)5日，收到X公司归还的货款100000元，存入银行账户。(3)8日，用银行存款支付欠Y公司的货款287000元。(4)15日，向X公司销售甲产品，售价590000元，乙产品36000元，增值税率9%，款项存入银行账户。(5)18日，使用现金支付招待费3000元。(6)20日，计提当月短期借款利息2500元。(7)24日，从Y公司购进A材料17000元，购进B材料11000元，增值税税率为9%