Excel在财务中的应用课程资料--第二章 制作会计科目表及会计凭证的

1、第二章 制作会计科目表及会计 凭证的编制 2.1设计思路分析 2.2科目及期初余额设定 l2.2.1会计科目编码规则 l会计科目一般包括一级科目、二级科目 和明细科目 会计科目编号的原则 l系统性原则 l一义性原则 l简洁性原则 l可扩展性原则 l在我国一般采用3位数编号，并且科目 代号从右到左表示科目的性质 如：101现金，102银行存款 2.2.2输入科目名称和编号 l步骤1 新建一个工作簿，在工作表SHEETL中 输入科目表中要包含的项目，并设置科目表标 题行的格式。 l步骤2 在科目表中录入科目代码、总账科目 和明细科目的名称。 l步骤3 选定需要设置数据有效性的单元格区 域（即“余额

2、方向”列标题下方的单元格）， 单击菜单栏中的“数据有效性”命令打开“ 数据有效性”对话框，在“设置”选项卡中的 “允许”下拉列表中选择“序列”，在“来源 ”文本框中输入下拉列表中需包含的项，不同 项之间用英文状态下的逗号分隔。 l步骤4 若要在单击格后显示可选择的输入提 示信息，请在“数据有效性”对话框中单击“ 输入信息”标签，切换到“输入信息”选项卡 ，选择“选定单元格时显示输入信息”复选框 ，然后在“标题”文本框和“输入信息”文本 框中输入具体内容。 l步骤5 单击“出错警告”切换到“出错警告 ”选项卡中，选择“输入无效数据时显示出错 警告”复选框，在“样式”下拉列表中选择“ 停止”，然后

3、可根据需要决定是否键入“标题 ”和“错误信息”。 l步骤6 当在设置了有效性的单元格中键 入其他值时，系统会弹出出错提示话框 ，并且阻止无效数据的输入。 l步骤7 由于科目表中包含总账科目和明 细科目，为了将它们区分开来，为明细 科目单元格区域设置底纹效果。 l步骤8 在表格中“期初余额（借）”列 在侧插入新列，输入列标题为“科目级 次”，然后将在应科目的科目次输入到 该列的单元格中。 2.2.3 设置期初余额及余额平衡 试算 l步骤1 选定好科目表中的项目并完成内 容的输入之后，接下来向科目表中添加 期初余额。 l步骤2 弹出“单元格式”对话框。 l步骤3 接下来根据输入的明细科目余额 计算

4、总账余额，在“应收账款”总账科 目以应余额所在的单元格F18中输入公式 ：=SUM（F19：F21） l步骤4 输入公式,按下Enter键可得到公 式运算结果。 l步骤5 单击需要冻结行的下行中的任意 单元格，单击菜单栏中的“窗口冻结窗 格”命令. l步骤6 此时,拖动右滚动条,会发现工作 中的第1行到第3行被冻结,始终显示在屏 幕上,同时,第3行的下边框会显示一条细 线. l步骤7 在单元格F165中输入公式: l=SUMIF(SES4:SES164,=1”,F4:F164) l步骤8 合并单元格F2和G2，然后将该单元格 填充为黄色，之后输入公式：=IF（F165=G165 ，“经试算借贷

5、方余额已平衡”，“经试算借 贷方余额不平衡”） l步骤9 如果借贷方余额不相等，还应准确显 示余额的差额，将单元格F1和G1填充为绿色， 在单元格F1中输入 “借贷方差额”，在单元 格G1中输入公式：=F1165-G165 2.3 制作记账凭证和填写科目代 码及名称 l3.3.1 记账凭证的基本内容和种类 l记账凭证应具备经下基本内容： l记账凭证的名称及填制单位的名称 l记账凭证的填制日期和编号 l经济业务的内容摘要 l应借应贷账户的名称，包括一级账户、二级账 户和明账户的名称及金额。 l附件张数，即所附原始凭证的张数 l会计主管人员、审核人员、记账和填制凭证人 员的签名或盖章 2.3.2

6、用EXCEL制作记账凭证 l步骤1 在工作表SHEET2中创建通用记账 凭证表格 l步骤2 在单元格C2中输入公式：=TODAY （） l步骤3 在工作SHEET3中创建收款凭证格 式，并将该工作表改名为“收款凭证” 2.3.2 用EXCEL制作记账凭证 l步骤4 在默认情况下，一个工作簿只有 三个工作表，此时需要插入新的工作表 。 l步骤5 在弹出的“插入”对话框中选择 “工作表”，然后单击“确定”按钮 l步骤6 此时，新插入的工作表默认名称 为SHEET1，拖动工作表标签。将新工作 表移到最右边 l步骤7 在新插入的工作表中创建付款凭证， 并将该工作表名称更改为“付款凭证” l步骤8 用同

7、样的方法，再次新建工作表，创 建“转账凭证”工作表 l步骤9 在“收款凭证”工作表中单击单元格 B3，打开“数据有效性”对话框， 在“允许 ”下拉列表中选择“序列”。在“来源”文本 框中输入“现金，银行存款” l步骤10 接着，该单元格的右侧会显示一个下 拉按钮，单击 l可从下拉列表中进行选择 2.3.3设置科目下拉列表 l步骤1 切换到工作表“科目及期初余额 设定”中单击菜单栏中“插入名称定 义”命令，弹出“定义名称”对话框 l步骤2 接着屏幕上显示，“定义名称 引用位置：”折叠对话框，在当前工作 表中选定需要引用的单元格区域，这里 请选定单元格区域A4：A164 l步骤3 设置好引用位置后

8、，再次单击折 叠按钮，返回“定义名称”对话框中， 单击“添加”按钮将该名称添加到列表 框中，然后单击“确定”按钮关闭该对 话框 l步骤4 切换到工作表“通用记账凭证” 中，选定单元格B4，单击菜单栏中的“ 数据有效性”命令，弹出“数据有效性 ”对话框，对话框，从“允许”下拉列 表中选择“序 列”，在“来源文本框中输入 “=CODE” l步骤5 此时单元格B4的右侧将会显示一 个下拉按钮，单击可展开科目代码下拉 列表 2.3.4自动填入对应科目代码的科 目名称 l步骤1 在“通用记账凭证”工作表中的 单元格C4中输入公式： 2.3.4自动填入对应科目代码的科 目名称 l=IF（LEN（B4）=0

9、，“”，VLOOKUP（B4 ，科目及期初余额设定！SAS4：SCS164 ，2FALSE） l步骤2 在单元格D4中输入公式： l=IF（LEN（B4）=0，“”，VLOOKUP（B4 ，科目及期初余额设定！SAS4：SCS164 ，3，FALSE） l步骤3 拖动序列填充柄，复制公式到以 下行。EXEEL将自动填入对应的总账和明 细账科目名称，如果该科目没有明细科 目，则明细科目列中填入0 l步骤4 如果希望没有明细科目的单元格 为空，可隐藏零值 l步骤5 此时，记账凭证中没有明细科目 的单元格将显示为空 2.4在EXCEL中录入凭证并设置借 贷平衡提示 l3.4.1自动按凭证类别连续编号

10、 l步骤1 在工作簿中插入“凭证录入”工 作表，工作表中的项目 l步骤2 选定单元格D5，单击菜单栏中的 “数据 有效性”命令，弹出“数据有 效性”对话框，切换到“设置”选项卡 中，从“允许”下拉列表中选择“序列 ”，在“来源”文本框中输入凭证类别 ，如“银收”、“银付”、“现收”、 “现付”等，输入的之间用英文状态下 的逗号 l步骤3 设置好有效性后，再单击单元格 D5，此时该单元格的右侧会显示一个下 拉按钮，单击该按钮弹出凭证类别下拉 列表，用户只需从列表中选择当前凭证 所属的类别即可 l步骤4 在单元格E5中输入公式： =COUNTIF（SDS5：D5，D5） l步骤5 用序列填充的方法

11、向下复制D5单 元格有效性设置和D5单元格中的公式， 此时，E列中凭证号会随着D列中选定的 凭证类别连续编号 2.4.2设置账户名称查询列表 l步骤1 切换到工作表“科目及期初余额 设定”，在工作表中的H的新增“账户查 询”栏，在H3单元格中输入“账户查询 ”，H4单元格中输入公式： =IF(C4=”,B4,CONCATENATE(B4,”_” ,C4) l步骤2 指向单元格H4的右下角，拖动序 列填充柄复制公式，计算出所有的账户 查询 l步骤3 选定单元格区域H4：H164， 单击菜单栏中的“插入名称定义”命 令，弹出“定义名称”对话框，在“当 前工作簿中的名称”文本框中输入“账 户名称”，

12、 单击“添加”按钮，然后再 “确定”按钮 l步骤4 切换到工作表“凭证录入”中， 选定借方栏中的账户名称单元格区域， 单击“数据 有效性”命令，在“数据 有效性”对话框中的“设置”选项卡中 的“允许”下拉列表中选择“序列”， 在“来源”文本框中输入“=账户名称” l步骤5 此后，当需要输入账户名称时， 就可以直接从下拉表中选择了 l步骤6 用同样的方法为贷方栏中的账户 名称设置下拉列表 2.4.3设置借贷平衡提示 l步骤1 单击单元格K5，输入公式：=IF （H5=J5，“”，“借贷不平”） l步骤2 拖动单元格K5右下角的序列填充 柄，复制公式到该列以下的单元格 l步骤3 当输入借方金额，未

13、输入贷方金 额，或者输入的贷方金额不等于借方金 额时，在该行对应的K列单元格中将显示 “借贷不平！”提示信息 2.5 生成科目汇总表 l汇总记帐凭证是为了简化登记总帐的手 法，根据一次性记帐凭证按照一定的方 法进行归类汇总编制而成的。科目汇总 表是一种典型的汇总记帐凭证，是根据 一次性记帐凭证定期整理、汇总各类帐 户的借、贷方发生额，并据以登记总帐 的一种汇总性记帐凭证 2.5 生成科目汇总表 l一般企业如果企业业务量较多，可以三 、五天汇总一次，业务量少的也要十天 汇总一次。科目汇总表通常包括会计科 目、记帐、借（贷）方的本期发生额等 内容。 2.5.1利用筛选功能汇总发生额 l步骤1 插入

14、一个新的工作表，命令为“ 科目汇总表” 2.5.1利用筛选功能汇总发生额 l步骤2 切换到“凭证录入”工作表中， 选定表格中的标题行，单击菜单栏中“ 数据筛选自动筛选”命令 l步骤3 此时，在标题行的单元格中会出 现一个下拉按钮，单击“账户名称”单 元格的下拉按钮，从弹出的下拉列表中 选择“科目汇总表”工作表中需要统计 的会计科目名称，如“银行存款” l步骤4 此时，表格中将只显示借方账户 名称为“银行存款”的记录 l步骤5 将该结果填入“科目汇总表”中 “银行存款”科目的本期借方发生额栏 2.5.2利用公式汇总发生额 l步骤1 在“科目汇总表”工作表中，选 定单元格C5，然后输入公式：=SUMIF（ 凭证录入！SG5：SGS32，SA5，凭证录入 ！SH5：SHS32） 2.5.2利用公式汇总发生额 l步骤2 按下来计算贷方本期发生额，在 单元格D5中输入公式：=SUMIF（凭证录 入！SIS5：SIS32，SA5，凭证录入！ SJS5：SJS32） l步骤3 拖动单元格D5右下角的序列填充 柄，向下