Excel在会计中的应用

成都航空职业技术学院Excel 在会计中的应用实 训 指 导 书陈其超 朱劼 编写2012 年 2 月第 1 版前 言前 言Microsoft Excel是一个电子表格处理软件，是 Office办公软件的核心应用程序之一。它功能强大，操作简单，使用范围广，不仅能够制作日常工作中的各种表格，而且还可以普遍运用于报表处理、数学运算、工程计算、财务处理、统计分析、图表制作等各个方面，同是，还提供了丰富的财务函数、数据库管理函数以及数据分析工具。通过运用这些功能，会计人员可以进行会计处理、财务分析、统计分析、线性预测，并制作各种数据分析表、编制各种分析模型。在会计信息化过程中，Excel 有其独特的作用。会计信息化是会计发展史上的里程碑，它不仅推动了会计工作的现代化进程，而且使得会计工作在社会经济发展中的地位和作用得到极大的提高。同时，会计处理手段及技术的现代化，极大地提高了会计信息的及时性、准确性、科学性。Excel 作为专业的数据处理系统，其功能是无可比拟的，它在实现基本的会计数据处理的基础上，进一步对会计信息进行分析，从而为经营管理提供更为有效、准确的决策支持。比之昂贵的大型财务软件，Excel 更适合中小企业处理日常会计业务的需要。本指导书采用项目化教学方法，设计了六个项目。分别介绍了 Excel的基础知识和高级应用，Excel 在会计凭证制作、日记账和分类账创建及会计报表编制这三大会计处理流程中的运用，在会计实务方面主要选取薪酬管理、固定资产管理、往来账款管理及财务分析等范例介绍 Excel的应用。本指导书以会计实务中常见的工作为任务，详细介绍利用 Excel解决会计实务中一些实际问题的方法和步骤，同学们可以按照书中的操作步骤，对照范例文件进行实际操作，迅速掌握利用 Excel解决会计实务问题的方法和诀窍，最终开发、建立自己的会计管理系统。由于本人水平有限，讲义编写时间仓促，疏漏或不足之处在所难免，欢迎广大读者同学批评指正。目录目 录项目一 EXCEL 基础知识 .1工作任务一 EXCEL 基本操作 .1项目活动一 Excel 基本操作界面及对象 .2项目活动二 工作簿和工作表的操作 .6项目活动三 编辑单元格 .11工作任务二 EXCEL 高级应用 .14项目活动一 Excel 公式和函数的使用 .14项目活动二 管理数据清单 .20项目活动三 Excel 宏及其应用 .24项目二 会计凭证的制作 .26工作任务一 页面的设计 .30项目活动一 页面背景的设计 .30项目活动二 页面标题的设计 .31项目活动三 设置页面到各个工作表的链接 .33项目活动四 页面的美化 .36项目活动五 保护工作表 .37工作任务二 会计科目表的制作 .38项目活动一 建立总账科目表 .38项目活动二 修改总账科目表 .40项目活动四 建立明细科目表 .41工作任务三 会计凭证表的制作 .42项目活动一 会计凭证表的结构设计 .42项目活动二 自动形成会计凭证编号的设置 .43项目活动三 范围名称的定义 .44项目活动四 自动显示会计科目的设置 .45项目活动五 单元格输入信息提示的内容 .47项目活动六 会计凭证表实例 .48项目活动七 制作完成的会计凭证表的保护 .48项目活动八 会计凭证表的备份 .49工作任务四 记账凭证的制作 .50项目活动一 记账凭证的结构设计 .50实 操 练 习 .55项目三 日记账管理系统的建立 .57工作任务一 页面的设计 .59工作任务二 现金日记账的创建 .61项目活动一 现金日记账的结构设计 .61前 言项目活动二 设置数据有效性 .63项目活动三 设置条件格式 .64项目活动四 余额自动计算 .65项目活动五 计算本日合计和本月累计 .66工作任务三 现金日报表的编制 .68项目活动一 现金日报表的格式设计 .68项目活动二 现金日报表数据的自动生成 .69工作任务四 银行存款日记账的创建 .71项目活动一 银行存款日记账的结构设计 .71实 操 练 习 .74项目四 薪酬管理实务 .75工作任务一 薪酬管理系统基本表格的建立与数据输入 .78项目活动二 职工基本工资表的建立 .80项目活动三 职工福利表的建立 .81项目活动四 职工社会保险表的建立 .81项目活动五 职工考勤表的建立 .82项目活动六 职工业绩考核表的建立 .83工作任务二 职工工资结算单的创建 .83工作任务三 工资条的生成 .86工作任务四 工资总额汇总表的创建 .88工作任务五 工资费用分配表的建立 .91实 操 练 习 .93项目五 固定资产管理实务 .96工作任务一 固定资产卡片的建立 .98工作任务二 固定资产管理 .100项目活动一 固定资产的增加 .100项目活动二 固定资产的调拨 .101项目活动三 固定资产的减少 .102工作任务三 固定资产折旧计算表 .102项目活动一 平均年限法计提折旧 .102项目活动二 双倍余额递减法计提折旧 .103项目活动三 年数总和法计提折旧 .104工作任务四 固定资产折旧费用分配表的建立 .106项目活动一 按固定资产折旧费用类别分析折旧费用分配情况 .106项目活动二 按固定资产使用部门分析折旧费用分配情况 .109工作任务五 建立独立的固定资产卡片 .109实 操 练 习 .113项目六 往来账款管理实务 .114工作任务一 应收款项表的创建 .115III项目活动一 应收款项表的建立 .115项目活动二 判断应收款项是否到期 .118工作任务二 应收账款账龄分析表 .118项目活动一 应收账款账龄设置 .118项目活动二 应收账款账龄分析表的创建 .119项目活动三 坏账准备计提 .121工作任务三 建立应收票据账期金额分析图 .122工作任务四 应付款项表的创建 .125项目活动一 应付款项表的建立 .125项目活动二 判断应付款项是否到期 .127工作任务五 应付账款账龄分析表 .128项目活动一 应付账款账龄设置 .128项目活动二 应付款账龄分析表的创建 .129工作任务六 建立应付款账期金额分析图 .130实 操 练 习 .133项目七 会计报表的编制 .135工作任务一 科目汇总表的建立 .136项目活动一 生成科目汇总表数据 .136项目活动二 科目汇总表的美化 .139项目活动三 科目汇总表数据的自动更新 .141工作任务二 科目余额表的建立 .141项目活动一 科目余额表的结构设计 .141项目活动二 期初余额的链接调用 .142项目活动三 本期发生额的链接调用 .143项目活动四 期末余额的生成 .145工作任务三 资产负债表的编制 .146项目活动一 资产负债表的格式设计 .146项目活动二 资产负债表的编制步骤 .148工作任务四 利润表的编制 .149项目活动一 利润表的结构设计 .149项目活动二 利润表的编制步骤 .151工作任务五 现金流量表的编制 .151项目活动一 现金流量表的结构设计 .152项目活动二 现金流量表的编制准备 .153项目活动三 现金流量表的编制步骤 .154实 操 练 习 .155Excel 在会计中的应用实训指导书IVExcel 在会计中的应用实训指导书一、实训目的与要求本实训的目的是使学生通过工作任务驱动的方式了解 Excel在会计及财务处理中的应用，能够借此对会计日常工作的 Excel相关应用进行分析和掌握，并通过实训锻炼和提高学生的计算机操作和表格处理能力，为将来的工作奠定良好的基础。在实训过程中，学生每天在教师的指导下按照实训指导书完成实训任务，具体如下：1、每天按上课时间进行实训，不迟到、不早退、不违反各项纪律；2、每天按要求完成老师布置的实训计划，在实训过程中认真参与，积极主动思考，有意识地增强自己的动手能力。3、认真总结实训中的经验教训，由老师点评；4、按要求完成实训总结，并按时提交。二、实训内容Excel的基础知识和高级应用、Excel 在会计凭证制作、日记账和分类账创建及会计报表编制这三大会计处理流程中的运用，以及会计实务方面的薪酬管理、固定资产管理、往来账款管理及财务分析等。三、实训课时60课时（两周）四、实训准备1、分发实训资料，进行相关说明；2、将学生划分为学习小组，每组由小组长负责管理；3、向学生强调实训纪律要求和考核办法。五、考核办法考核成绩分为四个级别：优秀、良好、合格、不合格。优秀：能遵守纪律、在规定时间内完成指定任务并全面参与各个环节工作，总结认真细致；良好：能遵守纪律、在规定时间内完成指定任务并参与各个环节的大部分工作，总结认真；合格：能遵守纪律、在规定时间内完成指定任务并能参与各个环节工作，V有总结；不合格：未能遵守纪律、未在规定时间内完成指定任务，未参与各个环节工作，无总结。六、实训项目1、模块化教学：根据会计学习规律，将 Excel在会计中的应用分成不同的工作任务模块；2、实操练习：结合教师的讲解，进行工作任务实操练习，进一步强化对Excel应用的理解和使用；3、综合任务：通过一项工作任务学以致用，运用 Excel对会计日常工作进行全面的管理。七、实训计划表时间 周一 周二 周三 周四 周五上午熟悉 Excelr基本操作学习会计凭证的制作学习日记账管理系统的建立学习薪酬管理相关表的建立回顾练本周实训内容。下午熟悉 Excel高级应用完成会计凭证制作练习完成日记账相关练习完成薪酬管理表格练习检查本周各项目练习作业时间 周一 周二 周三 周四 周五上午学习固定资产管理的建立学习往来账款管理的建立检查本实训练习作业下午完成固定资产表格练习完成往来账款表格练习结合前面几个项目内容，学习会计三大报表的编制完成会计三大报表练习。完成实训总结注：实训计划根据实际执行情况适度调整。项目一 Excel 基础知识1项目一 Excel 基础知识工作目标随着社会经济的发展，企业的经济环境也在发生着根本性变化，因此，各个企业对会计职能的要求，已从单纯的核算型工具发展成为能够充分利用会计信息并准确地分析现状和预测未来的管理型工具。为了提高企业的会计工作效率，每个财务人员都应该掌握计算机软件工具，以便对会计信息做进一步的分析。Excel 作为一款功能强大的电子表格软件工具，除了具有有强大的表格数据计算和分析功能外，还可以把表格中的数据用各种统计图像表示出来。利用Excel 进行会计信息处理，可使得数据的收集、处理和分析更加科学、便捷，同时更有利于管理理论和管理实践的紧密结合，使企业的管理水平日益提高。通过完成本项目的工作任务，使学习者能够掌握 Excel 的一些基本知识，比如它的工作界面、基本元素、常用工具和常用的公式和函数的使用方法等，帮助学习者建立对 Excel 的感性认识，为以后的学习打下基础。工作思路本项目的工作思路是首先熟悉 Excel 的基本操作；掌握 Excel 公式和函数的使用方法；理解 Excel 中图表的处理和数据管理分析；了解 Excel 宏及其应用方法。工作任务一 Excel 基本操作随着计算机对人类社会的全方痊渗透，面向各行业的计算机应用工具应运而生。电子表格软件为人们提供了一种高效的数据通信、组织、管理和分析工具，备受众人瞩目。作为办公自动化不可缺少的 Excel 正是其中的佼佼者。Excel 是目前市面上最强大的电子表格制作软件，它和Word、 PowerPoint、Access 等软件一起，构成了 Office 办公软件的完整体系。它不仅具有强大的数据组织、计算、分析和统计功能，还可以通过图表、图形等多种形式将处理结果形象地显示出来，并且能够方便地与 Office 其他软件互相调用数据。成都航空职业技术学院 Excel 在会计中的应用2项目一 Excel 基础知识3项目活动一 Excel 基本操作界面及对象一、Excel 操作界面从启动的 Excel 窗口可以看到，该窗口主要由标题栏、工具栏、编辑栏、单元格区域、状态栏和滚动条等部分组成，具体的位置如图 1-1 所示。图 1-1 Excel 初始工作窗口1标题栏标题栏位于工作簿的最上面，显示的是一个 Excel 工作簿文件的名称，如图 1-2。新建一个文件时，系统自动以 Book1、Book2 等名称依此类推为工作簿命名，如需重合名，在保存时进行文件的命名即可。图 1-2 Excel 标题栏在标题栏的最左端是一个控制菜单图标 ，称之为 Office 图标，单击此图标弹出控制菜单，可实现以下命令： 【新建】命令：新建一个工作簿。 【打开】命令：打开一个已经保存的工作簿。快速访问工具栏 标题栏 Excel 程序窗口控制按钮工作薄窗口控制按钮Office 按钮功能区名称框状态栏编辑栏滚动条显示比例显示模式编辑栏滚动条成都航空职业技术学院 Excel 在会计中的应用4 【保存】命令：保存对该工作簿的修改。 【另存为】命令：保存工作簿按指定的格式到文件夹。 【打印】命令：打印预览和打印工作表。 【准备】命令：查看文档属性、检查文档、对文档进行加密、限制权限、添加数字签名、标记为最终状态、运行、兼容检查器。 【发送】命令：将文档副本发送给其他人。 【发布】命令：将文档发布给其他人员。 【关闭】命令：关闭工作簿。在标题的最右端有 3 个按钮 ，分别是【最小化 】按钮、 【还原】按钮（或【最大化】 ）按钮和【关闭】按钮。单【最小化】按钮可以将 Excel 应用程序窗口缩小成一个图标并隐藏在任务栏中，再次单击可以恢复原来的界面；当窗口最大化时，单击【还原】按钮可以还原到以前大小的界面，当窗口没有铺满整个屏幕时，单击此按钮可以实现窗口的最大化；单击【关闭】按钮可以实现窗口的关闭。2菜单栏菜单栏采用下拉、分级的方式分门别类地放置了 Excel 的各种命令和功能（例如【开始】 、 【插入】 、 【页面布局】 、 【公式】 、 【视图】等） ，如单击【开始】工具选项卡，将弹出所有与文件操作相关的菜单列表，每一个菜单项对应一个具体的功能，在工具栏菜单的右下端有一个下拉按钮，单击这些按钮，可以显示工具栏菜单中没有的菜单命令。选择菜单命时，用户除了可以利用鼠标实现外，还可以用键盘操作的方式来实现。用户可以首先按下 Alt 键激活菜单栏，再输入相应菜单名称后面的字母，就可以激活此菜单，输入相应菜单命令后的字母就可以执行该命令。如按下 Alt 键后，按下字母 I 可激活【插入】菜单，然后按下 P 键可执行【插入图片】命令。3工具栏工具栏位于菜单栏下方，由一系列的工具按钮组成，这些工具按钮有的是文字，有的是图标。工具栏的每个按钮都对应菜单栏中相关的命令，单击此按钮就可以执行该命令，因此使用工具栏可使操作更加简单快捷，而且工具栏的图标也便于记忆，节省了许多工作时间。在工具栏中标记为黑色字体的工具按钮处于激活状态可以使用，而灰色字体的工具按钮是暂时不能使用的。如果用户使用的工具在工具栏中没有，则单击工具栏右边的扩展箭头就会弹出剩余的命令菜单。如果用户不再需要某个工具栏，可以将其隐藏起来，只要单击【工具】菜单，把相应的工具栏名称左侧的“”取消就行。项目一 Excel 基础知识54编辑栏编辑栏由名称框和编辑框组成。名称框显示当前活动的单元格的名称，用来定义当前单元格或单元格区域的地址，或者根据地址查找单元格。如图 1-3中，当在单元格 A1 中输入“欢迎！”文本时，则在名称框中显示出活动单元格 A1。编辑框是输入数据和编辑单元格数据的地方。用户在单元格中输入或者编辑数据时，可首先单击该单元格，然后输入或编辑数据，此时在编辑框中也会显示输入和编辑的数据，单击编辑框旁边的【输入】按钮 ，或者按 Enter 键就可以实现对单元格的编辑。图 1-3 编辑栏中同时显示输入的内容5状态栏状态栏位于工作窗口的最下端，用来显示 Excel 当前的工作状态，起着对当前操作状态的一个提示作用。如有关命令、工具栏、按钮、正在进行的操作或光标所在的位置等信息。当用户在单元格内进行编辑时，状态栏就显示“输入”字样，输入完毕后将显示“就绪”字样。在状态栏空白处，右击可以自定义状态栏显示的内容。在状态栏的右下角，可以对工作簿视图进行调整，分别提供了普通视图、页面布局和分页预览 3 种模式供使用者选择。除此之外，使用者还能对单元格的显示比例进行调整。【提示】（1）对话框是计算机与用户进行交流的窗口，在对话框中包括各种各样的选项，如选项卡、复选框、文本框、列表框、命令按钮等，如选择【页面布局】|【页面设置】命令时就会弹出相应的对话框，各种元素的位置及作用如下。 选项卡：代表操作对象的不同属性。 文本框：用于直接输入或编辑文本。 列表框：用于显示数据或其他信息。 单选按钮：单选按钮为圆形，每一组单选按钮中只能选择其中一个。 复选框：复选框为方形，每一组复选框中可以选择其中的多个选项。（2）快捷菜单是用户在使用时，能够快速显示的一种菜单，在 Excel 窗口的不同位置右击，可弹出不同的快捷菜单。在快捷菜单中包含了与用户指定区成都航空职业技术学院 Excel 在会计中的应用6域最相关的一些命令。不同的命令有不同的快捷菜单，用户可以根据需要选择快捷菜单中不同的命令进行操作。二、Excel 操作对象Excel 的操作对象主要是工作簿、工作表和单元格。1工作簿工作簿实质上是 Excel 环境中用来存储和处理数据的文件，也叫 Excel 文档。其文件类型为.xls，一个工作簿可以包含最多 255 张工作表，在黙认情况下，当打开一个工作簿时，每个工作簿就包含三个工作表，分别命名为“Sheet1” 、“Sheet2”、 “Sheet3”，这些工作表标签位于工作簿窗口的底部，用来表示工作簿中不同的工作表，单击不同的工作簿标签，就可以进入相应的工作表；如果用户需要对这些工作表进行重命名，只要双击需要命名的工作表标签，当该名称变成黑色的时候，直接输入新的名称，即可完成对工作表的重命名。2工作表工作表是指由行和列组成的一个表格。Excel 中每张工作表最多可由1000000 行和 16000 列构成，其中行号是由上自下从 1 开始进行编号，列号则由左到右用字母 A 到 XFD 进行编号。每一个新建的 Excel 工作簿，软件会自动建立若干个工作表，用户可以将相关内容的表存放在同一个工作簿中，为相关的工作表之间数据的交换与格式的一致性提供了方便。3单元格工作表中行与列的交叉的小方格就是 Excel 的单元格，每张工作表中包括了 104857616384 个单元格，单元格的列宽可容纳 255 个字符，行高可达到409 磅。每个单元格都有对应的地址，例如 E13 表示第 E 列第 13 行的单元格。用户如果要对某个单元格中的内容进行操作，可以单击工作表中相应的单元格，发现些单元格变成一个黑色的小方框，此时所有的操作就只针对这个单元格了。如图 1-4 所示。图 1-4 活动单元格【提示】由于一个工作簿中有多个工作表，为了区分不同的工作表中的单元格，要在单元格地址前加上相应工作表的名称，并且工作表名称与单元格之间必须用“！”号隔开，如“Sheet3！B4”表示在 Sheet3 工作表中的 B4 单元格。项目一 Excel 基础知识7项目活动二 工作簿和工作表的操作工作簿是 Excel 的门户，要想运用 Excel 进行操作，首先需要创建一个新的工作簿。而一个工作簿中含有多张工作表，这样用户就可以将相关的表存在于同一个工作簿中，以避免多次存取的麻烦，并为相关的工作表之间数据的交换和格式的一致性提供了方便。1新建工作簿如何启动 Excel 应用程序呢？在新建 Excel 工作簿的方法上，大家可能都有自己习惯的方法，下面我们介绍新建 Excel 工作簿的方法。双击桌面上的快捷方式（如果桌面上没有快捷方式，可以使用下面的方法来添加快捷方式：【开始】| 【程序】|Microsoft Office | Microsoft Office Excel（右击）|【发送】| 【桌面快捷方式】 ，如图 1-5 所示。图 1-5 添加 Excel 桌面快捷方式右击桌面空白处，弹出桌面快捷菜单，选择【新建】|【Microsoft Office Excel 工作表】命令，如图 1-6 所示。图 1-6 使用快捷方式新建工作簿在桌面上依次选择【开妈】|【程序】| Microsoft Office | Microsoft Office Excel 命令。成都航空职业技术学院 Excel 在会计中的应用8选择【开始】|【运行】命令，或者使用快捷键（ Win+r） ，弹出如图 1-7 所示的对话框，在【打开】文本框中输入“Excel” ，然后单击【确定】按钮，同样可以打开一个新建的 Excel 工作簿。图 1-7 【运行】对话框如果你已打开一个 Excel 工作表，单击 Office 按钮 ，在弹出的下拉菜单中选择【新建】工具按钮，或者按 Ctrl+N组合键也可以创建一个新的 Excel工作簿。2保存工作簿单击 Office按钮 ，从弹出的下拉菜单中选择【保存】选项，即可保存工作簿；也可以直接使用快捷键 Ctrl+S组合键直接保存正在编辑的工作簿。3保护工作簿如果要对该工作簿赋予一定的权限，即对工作簿进行保护，则单击 Office按钮 ，选择【另存为】命令，在弹出的对话框左下方选择【工具】命令，如图 1-8所示。在下接菜单中选择【常规选项】命令，弹出如图 1-9所示的对话框，对话框中“打开权限密码”是指用户下次打开时需要输入的密码，“修改权限密码”是指打开工作簿后，在对工作簿进行修改时需要输入的密码。用户根据自己的需要设定密码，设置好之后单击【确定】按钮，就完成相应的权限设定了。当下次再打开这个工作簿时，就会提示你输入打开或者权限密码，如果不知道该农密码的人就打不开这个工作簿，或者无法对其中的数据进行修改。图 1-8 【另存为】对话框项目一 Excel 基础知识9图 1-9 【常规选项】对话框4增加工作表在黙认的情况下，一个工作簿有 3个工作表，分别命名为“Sheet1”、“Sheet2”和“Sheet3”。在工作中，有时候需要建立更多的工作表，此时，只要单击工作表标签旁边的【插入】按钮 ，即可插入一张新的工作表；或者右击工作表标签，在弹出的快捷菜单中选择【插入】命令，如图 1-10所示，从弹出的对话框中选择工作表图标，单击【确定】按钮就可以增加工作表了。还有一种方法就是在菜单中选择【开始】|【插入】|【插入工作表】命令。图 1-10 【插入】对话框5移动工作表在工作中，为了满足使用的需要，有时候需要调整工作表的次序，此时只要单击需要调整的工作标签，并按住鼠标左键，使其出现一个向下的箭头，如图 1-11所示，然后将其移动到需要的位置即可。图 1-11 移动工作表6复制工作表工作表的复制与工作表的移动操作相像，单击需要复制的工作簿标签之后，再同时按住鼠标左键和 Ctrl键，在工作表标签的前面即可出一个向下的箭头，拖动鼠标至适当的位置后松开鼠标和 Ctrl键，完成对工作表的复制，新的工作表会被自动命名为以前工作表的名称后面加上字（2），例如“Sheet1”工作表被复制以后会被命名为“Sheet1（2）”。还有一种方法，在工作簿中右击要复制的工作表标签，在弹出的快捷菜单中选择【移动或选择工作表】命令，弹出如图 1-12所示的对话框：选中【建立副本】复选框，再确定需要把工作表复制的位置，比如要把工作表移动到Sheet2和 Sheet3之间，就需要选择把工作表放在 Sheet3之前，然后单击【确成都航空职业技术学院 Excel 在会计中的应用10定】按钮就完成了对工作表的复制。图 1-12 【移动或复制工作表】对话框7隐藏和显示工作表隐藏工作表，打开需要隐藏的工作表，右击工作表标签，在弹出的快捷菜单中选择【隐藏】命令，即可完成对目标工作表的隐藏；或者选择工具栏的【单元格】|【格式】命令，再在弹出的菜单中选择【隐藏和取消隐藏】|【隐藏工作表】命令即可，如图 1-12所示。显示隐藏的工作表。同样是右击工作表标签，在弹出的快捷菜单中选择【取消隐藏】命令，在弹出的【取消隐藏】对话框中选择需要重新显示的工作表；或者选择【格式】|【隐藏和取消隐藏】|【取消隐藏工作表】命令来完成。隐藏/显示工作表的行或列。选定需要隐藏的行或列，即将