人民邮电出版社—《execl在财务中的应用》02.ppt

Excel在财务中的应用,项目二 Excel在账务处理中的应用,项目二 Excel在账务处理中的应用,学习目标,【知识目标】 了解使用Excel实现账务电算化的步骤和方法。 掌握SUMIF函数和IF函数的格式。 【能力目标】 学会使用Excel实现账务处理的操作。 学会使用Excel生成总账、明细账等操作。,李娜在熟练掌握Excel基础操作之后，开始尝试用Excel建账。 丰源公司的账户期初数据如表2-1所示。 该公司2010年9月份的具体业务如下：公司为增值税一般纳税人，增值税税率为17%，所得税税率为25%。材料核算采用先进先出法。原材料月初库存量为500吨。 工作情境分析 若要完成丰源公司2010年9月份的账务处理流程，需分成以下几项工作任务：建账设置账户输入期初余额输入记账凭证生成总账生成明细账。,工作情境,一、任务分析 所谓建账即用Excel建立一个工作簿，并建立若干张工作表，用以分别存放会计科目及其期初余额、记账凭证，以及根据记账凭证自动生成的总账和明细账等。 在该任务中需要明确工作簿与工作表的关系，用到的操作技能是新建工作簿与工作表、对工作表进行重命名。,任务一 建账,二、任务实施 操作步骤如下。 （1）在工作盘E盘建立“丰源公司”文件夹，在该文件夹下建立“总账”文件夹。 （2）启动Excel，在总账文件夹下建立总账工作簿“1009总账.xls”。 （3）双击工作表Sheet1标签，输入一个新工作表名字“封面” （4）在“封面”工作表中输入以下信息，如图2-1所示。,任务一 建账,（5）将Sheet2命名为“1009会计科目及余额表”。 （6）将Sheet3命名为“凭证模板”。 （7）增加工作表Sheet4、Sheet5、Sheet6，分别命名为“1009凭证”、“1009总账及试算平衡表”和“1009明细账”。 以上操作的结果如图2-2所示。见数据模型。,任务一 建账,任务二 设置账户,一、任务分析 设置账户即建立一个“会计科目及余额表”，会计科目是会计记账的核心，它主要有三个方面的功能：一是会计分录的对象；二是记账的标准；三是制表的纲目。在日常的会计核算中，会计科目一般分为一级科目、二级科目及明细科目，其中一级科目是国家财政部统一规定的。,任务二 设置账户,二、任务实施 操作步骤如下。 （1）在工作表“1009会计科目及余额表”的A1单元格中输入“科目编码”，B1单元格中输入“科目名称”，选择A列，打开格式菜单，选择单元格命令，打开“单元格格式”对话框，设置数字类型为文本。 （2）在工作表“1009会计科目及余额表”的A2至A75单元格中分别输入新会计准则体系的科目编码及相应的子科目编码，在B2至B75单元格中分别输入新会计准则体系的科目名称及相应的子科目名称。,任务二 设置账户,（3）选择A1和B1单元格，单击工具栏上的“填充颜色”按钮，填上青绿色。 （4）选择A1：B75单元格区域，单击工具栏上的“边框”按钮右侧的下拉按钮，从中选择“所有框线”，加上“田”字型边框。 （5）选择C2单元格，选择“窗口”菜单中“冻结窗格”命令，将A1和B1单元格及其内容固定在现有位置，不随行列的翻动而隐藏。以上操作的结果如图2-3所示。,任务三 输入期初余额,一、任务分析 输入期初余额即在“1009会计科目及余额表”中输入2010年9月各账户期初数据，并实现试算平衡。在输入期初数据时，需注意总账科目余额与下级科目余额之间的关系：总账科目余额=下级科目余额之和。在进行试算平衡时，要设置试算平衡公式：借方总账科目余额之和=贷方总账科目余额之和。,任务三 输入期初余额,二、任务实施 操作步骤如下。 （1）选择C1单元格，输入“期初借方余额”；选择D1单元格，输入“期初贷方余额”。 （2）定义有明细科目的汇总科目的计算公式。在输入时因为只要求输入最低级科目的余额，上级科目的余额要根据设置的公式自动进行计算，所以汇总科目的单元格数据是通过其他单元格数据加总得出的。,任务三 输入期初余额,（3）将丰源公司2010年9月会计科目（非汇总科目）的期初余额输入该工作表中，输入结果如图2-4所示。 （4）用格式刷把C1：D1刷成和A1相同格式，选择C1：D75，加上边框。 （5）选择A76：B76单元格，单击工具栏上的“合并及居中”按钮，在合并后的单元格中输入“合计”二字。 （6）在C76中输入“=”，打开名称框旁边的下拉按钮，选择SUMIF函数，打开编辑函数参数的窗口。 （7）选择C76，在编辑栏单击A:A，按F4键，把相对地址切换为绝对地址，回车确认。 （8）用填充柄把C76的公式复制到D76，得到贷方余额之和也为10 711 050。,任务三 输入期初余额, SUMIF函数是条件求和函数。 【类型】逻辑函数 【格式】SUMIF（搜索范围，搜索条件，求和范围） 【功能】可以根据其他区域中的值，通过使用SUMIF工作表函数对区域求和。 例如：C76= SUMIF（A:A，“?“，C:C）,一、任务分析 输入记账凭证即建立一个“1009凭证”工作表，在此表中输入所有业务凭证。记账凭证清单应具有记账凭证的所有信息，应设置类别编号、凭证日期、附件、摘要、科目编码、总账科目、明细科目、借方金额、贷方金额、制单人、审核人、记账人等字段。此外，在输入过程中要设置一定的数据校验功能，如日期格式、金额格式、科目编码和科目名称的有效性等。,任务四 输入、审核记账凭证及记账,二、任务实施 1制作凭证模板 操作步骤如下。 （1）打开工作表“凭证模板”，设计凭证输入的表头。 （2）设置A1:L3单元格边框为“田”字型，中线为蓝虚线，边线为蓝实线。 （3）设置“类别编号”、“凭证日期”、“附件”、“摘要”、“科目编码”等列的数据有效性。 （4）设置“总账科目”列和“明细科目”列的取值公式。 （5）设置“借方金额”、“贷方金额”的格式。 （6）定义平衡检查公式。,任务四 输入、审核记账凭证及记账, VLOOKUP函数用于表格数组的首列查找值，并由此返回表格数组当前行的对应列的值。 【类型】查找与引用函数 【格式】VLOOKUP（lookup_value，table_array，col_index_num，range_lookup） 【功能】在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。,任务四 输入、审核记账凭证及记账, LEFT函数用于基于所指定的子字符数返回其母字符串中从左边数起的第1个或前几个字符。 【类型】文本函数 【格式】LEFT（text，num_chars） 【功能】基于所指定的字符数返回文本字符串中的第1个或前几个字符。,任务四 输入、审核记账凭证及记账,2输入记账凭证 操作步骤如下。 （1）选择“凭证模板”表，将凭证模板复制到“1009凭证”中。 （2）根据公司发生的业务，输入会计分录。,任务四 输入、审核记账凭证及记账,3审核凭证与过账 由于Excel本身是一个电子表格软件，凭证都放在数据清单里面，为了清晰的表明凭证是否审核或是否记账，还可以灵活地使用为单元格填充颜色的方法来表示是否审核或是记账。例如，无填充颜色表示未审核，蓝色填充表示已经审核，黄色填充表示已经记账。,任务四 输入、审核记账凭证及记账,一、任务分析 总分类账，简称总账，是根据总分类科目（一级科目）开设账户，用来登记全部经济业务，进行总分类核算，提供总括核算资料的分类账簿。总分类账所提供的核算资料，是编制会计报表的主要依据，任何单位都必须设置总分类账。 试算平衡表是列有总分类账中所有账户及余额的简单表格。这份表格有助于检查记录的准确性和编制财务报表。,任务五 生成总账、试算平衡表,二、任务实施 1制作总账及试算平衡表 操作步骤如下。 （1）复制“1009会计科目及余额表”的A1:D76区域。 （2）选中“1009总账及试算平衡表”A1单元格，右击打开快捷菜单，选择“选择性粘贴”，打开“选择性粘贴”对话框，选择单选项“值和数字格式”后单击“确定”按钮。 （3）删除二级以下科目所在的行，因为此表的科目均为总账科目。,任务五 生成总账、试算平衡表,（4）选择E1单元格，输入“本期借方发生额合计”，选择F1单元格，输入“本期贷方发生额合计”；选择G1单元格，输入“期末借方余额”，选择H1单元格，输入“期末贷方余额”。 （5）选择A1:H1区域，将其填充为青绿色。 （6）选择A1:H48区域，设置边框类型为“所有框线”。 （7）设置E2单元格的公式 。 （8）设置F2单元格的公式 。 （9）设置G2单元格的公式 。,任务五 生成总账、试算平衡表, IF函数用于进行真假值判断，根据逻辑计算的真假值，返回不同结果。 【类型】逻辑函数 【格式】IF（Logical_test，Value_if_true，Value_if_false） 【功能】执行真假值判断，根据逻辑计算的真假值，返回不同的结果。 Logical_test表示计算值为“真”或“假”的任意值或表达式； Value_if_true表示当Logical_test为“真”时返回的值； Value_if_false表示当Logical_test为“假”时返回的值。,任务五 生成总账、试算平衡表,（10）设置H2单元格的公式 。 （11）将E2单元格的公式纵向填充至E47单元格。 （12）将F2单元格的公式纵向填充至F47单元格。 （13）将G2单元格的公式纵向填充至G47单元格。 （14）将H2单元格的公式纵向填充至H47单元格。 （15）选择E48，输入“=SUM（E2:E47）”，并将公式横向向右填充至H48单元格。 （16）选择I2单元格，选择“窗口”菜单中“冻结窗格”命令 。,任务五 生成总账、试算平衡表,2重算总账及试算平衡表 操作步骤如下： 选择“工具”菜单中“选项”命令，选择“重新计算”选项卡，在“计算”选项组中选择“手动重算”单选按钮和“保存前自动重算”复选框，如图2-15所示。 设置结束后，可直接按功能键F9重算所有工作表或重算活动工作表。从图2-14中可以看出，试算平衡是成功的。,任务五 生成总账、试算平衡表,一、任务分析 明细分类账简称明细账，是指根据总分类科目设置的，由其所属的明细分类科目开设的明细分类账户组成，用以记录某一类经济业务明细核算资料的分类账簿，能提供有关经济业务的详细资料。明细分类账簿应根据经济业务的种类和经营管理的要求分别设置。按其外表形式，明细账采用活页式账簿和卡片式账簿；按其账页格式，明细账一般分为三栏式明细分类账簿、数量金额式明细分类账簿和多栏式明细分类账簿3种。,任务六 生成各类明细账,二、任务实施 操作步骤如下。 （1）选择“1009明细账”工作表的A3单元格。 （2）选择“数据”菜单中“数据透视表和数据透视图”命令，打开“数据透视表和数据透视图向导3步骤之1”对话框。 （3）在“选定区域”文本框中输入要汇总的数据区域“1009凭证！$A$1:$L$200”。 （4）单击“下一步”按钮，在“数据透视表显示位置”选项中选择“现有工作表”单选按钮，具体单元格地址是“=$A$3” 。 （5）单击“完成”按钮，进行数据透视表的布局设置。,任务六 生成各类明细账,二、任务实施 操作步骤如下： （6）选择“数据透视表字段列表”的“凭证日期”字段，再选择左下角“添加到”按钮右侧下拉列表框中的“行区域”选项，然后单击“添加到”按钮，将“凭证日期”字段添加到“行区域”中。 （7）隐藏“凭证日期”、“类别编号”、“摘要”、“科目编码”、“总账科目”字段的统计单元格，然后用鼠标左键按住F3单元格的“数据”不放，拖动到G3单元格的“汇总”中。 （8）选择F4单元格，右击打开快捷菜单，选择“字段设置”命令，在打开的“数据透视表字段”对话框中，将“汇总方式”列表框设为“求和” 。 （9）适当调整各行高和列宽。,任务六 生成各类明细账,一、实训目的 掌握Excel的账务处理流程。 二、实训资料 青岛宏发公司为增值税一般纳税人，增值税率为17%，所得税率为25%。材料核算采用先进先出法，原材料月初库存量为500吨。公司2010年12月份的具体业务如下。 三、实训要求 1建账；2设置账户 ；3输入期初余额 ；4输入记账凭证 ；5生成总账；6生成明细账 。,