IT168文库
2Excel 2007Excel 2007 在审计分析中的应用审计是指审计机关依法独立检查国家行政、事业单位和企业单位及其他经济组织的会计凭证、会计账簿、会计报表,以及其他与财政收支、财务收支有关的资料和资产,监督财政收支和财务收支真实、合法和效益的行为。在审计工作中,需要应用 Excel 进行数据信息的处理,从而进行数据统计分析工作。因此,在进行审计工作之前,需要掌握一些 Excel 操作的基础知识。1.1 熟悉 Excel 2007 的公式众所周知,Excel 是以数据处理、公式计算见长,从简单的四则运算到复杂的财务计算、统计分析等,都可以使用相应的公式实现求解。因此,掌握 Excel 2007 常用操作的基础就是要认识其公式。1.1.1 Excel 的公式组成Excel 中的公式是指在单元格中执行计算功能的各式各样的方程式。其中,公式中元素的结构或次序决定了最终的计算结果。在 Excel 中,运用的公式一般都遵循特定的语法或次序,在输入的过程中,最先输入的是等号(=) ,再输入参与计算的元素(运算数) ,每个运算数可以是数值、单元格或引用单元格区域、标志、名称或工作函数等,不过这些参与计算的运算数都是通过运算符隔 开的。在 Excel 中,公式一般涉及运算符、运算符的优先顺序、数值转换等概念。1运算符运算符用于指明对公式中元素所做的计算的类型。在 Excel 中,运算符可以分为算术运算符、比较运算符、文本运算符和引用运算符等 4 种,具体的功能与组成如下。(1)算术运算符算术运算符用于完成基本的数学运算,即加、减、乘、除、求幂、百分号,各算术运算符与用途如表 1-1 所示。表 1-1 算术运算符算术运算符 名 称 用 途 示 例+ 加号 加 5+6- 减号 减,也可以表示负数 9-1, -9* 星号 乘 6*6/ 斜杠 除 9/2% 百分号 百分比 90% 乘幂 求幂 42(相当于 4*4)3审计工作中常用的 Excel 知识 第 1 章(2)比较运算符比较运算符用于比较两个值,结果为一个逻辑值,即 TRUE(真)或 FALSE(假) 。这类运算符常用于判断,根据判断结果决定下一步进行何种操作,具体的比较运算符的名称与用途如表 1-2 所示。表 1-2 比较运算符比较运算符 名 称 用 途 示 例= 等号 等于 A5=B5 大于号 大于 A5B5= 大于等于号 大于等于 A5=B5不等于 不等于 A5B5(3)文本运算符Excel 中的文本运算符只有一个文本串联符 “&”,用于将两个或更多个字符串连接在一起。如 A5 单元格包含“名 ”,B5 单元格包含“姓” ,若要以格式“名 姓”显示全名可输入公式“=A5& &B5 ”;若要以格式“姓,名”显示全名则输入“=B5&,&A5” 。(4)引用运算符引用运算符用于合并单元格区域,各引用运算符的名称与用途如表 1-3 所示。表 1-3 引用运算符引用运算符 名 称 用 途 示 例: 冒号 引用单元格区域 A5:A15, 逗号 合并多个单元格引用 SUM(A5:A15,D5:D15)空格 将两个单元格区域进行相交 A1:C1B1:B5 的结果为 B12运算符的优先顺序在 Excel 中,一个公式中同时可以包含多个运算符,这时就需要按照一定的优先顺序进行计算,对于相同优先级的运算符,将从左到右进行计算。另外,将需要先计算的部分用括号括起来,可提高优先顺序。运算符的优先顺序具体如表 1-4 所示。表 1-4 运算符的优先顺序运 算 符 名 称 优 先 级: 冒号 空格 , 逗号 % 百分比 4Excel 2007Excel 2007 在审计分析中的应用续表运 算 符 名 称 优 先 级 乘幂 *和/ 乘和除 +和- 加和减 & 连接两串文本 3公式中的数值转换尽管每个运算符都需要特定类型的参数与之对应,但如果输入参数的类型与所需要的不同,Excel 将尝试一切可能的转换,并完成计算。常见的数据类型转换如表 1-5 所示。表 1-5 常见的数据类型转换表公 式 产 生 结 果 说 明=1+2 3在 Excel 中,文本与字符串都是由一对英文双引号来使用的,但当公式中需要数字时,Excel 会将其中的文本转换成数字=6/1/2001-5/1/2001 31 Excel 将具有 mm/dd/yy 格式的文本看作日期,将日期转换成序列号之后,再进行计算=SQRT(6+1) #VALUE!(#值!)Excel 不能将文本“6+1”转换成数字,如果使用“7”或“6”+“1”代替 “6+1”,Excel 将把文本转换成数字=A&TRUE ATRUE 当需要文本时,Excel 将数字和逻辑型数值转换成文本1.1.2 公式的输入在了解 Excel 公式的组成情况后,可以通过实现公式的输入,来完成数据的计算分析操作。1输入公式在 Excel 中输入公式,首先选定要输入公式的单元格,接着输入等号(=) ,再输入相应的公式内容,最后按 Enter 键,结束公式的输入。2编辑公式在公式的运用过程中,有时需要对输入的公式进行修改编辑,这需要选中要编辑的单元格,在编辑栏内对需要修改的公式进行修改,再按 Enter 键,完成编辑操作。如果公式是数组公式,按【Ctrl+Shift+Enter】组合键即可。3移动公式在移动公式的过程中,公式中的单元格引用并不改变。5审计工作中常用的 Excel 知识 第 1 章【例 1-1】C1 单元格中有如图 1-1 所示的公式“=A1+B1” ,需要将该公式移动到 D2单元格,使 D2 单元格中的公式依然是 “=A1+B1”。其方法有两种。首先选中含有公式的 C1 单元格,使鼠标的指针移到单元格四周黑色的边框上,但不能放到单元格的右下角,按住鼠标左键拖动到 D2 单元格的左上角,松开鼠标可实现移动操作,如图 1-2 所示。右击含有公式的 C1 单元格,从弹出的快捷菜单中选择【剪切】命令后,再右击 D2 单元格,从弹出的快捷菜单中选择 【粘贴】命令,完成公式的移动操作。图 1-1 输入公式 图 1-2 移动公式4复制公式在复制公式时,包括公式中单元格的绝对引用和相对引用两种方式。单元格绝对引用的方式是在字母和数字前加符号“$” 。【例 1-2】在 C5 单元格中有如图 1-3 所示的公式“=$A$5+$B$5” ,将该公式复制到 D8 单元格,使其公式也为“ =$A$5+$B$5”。只用右击 C5 单元格,从弹出的快捷菜单中选择【复制】命令。再右击 C8 单元格,从弹出的快捷菜单中选择【粘贴】命令,完成公式的复制操作,如图 1-4 所示。图 1-3 源公式 图 1-4 复制公式【 例 1-3】 C10 单 元 格 中 有 如 图 1-5 所 示 的 公 式 “=A10+B10”, 将 该 公 式 复 制 到 D12 单元 格 , 使 其 公 式 也 为 “=A12+B12”。 只 用 按 照 绝 对 引 用 方 法 可 完 成 复 制 操 作 , 如 图 1-6 所 示 。6Excel 2007Excel 2007 在审计分析中的应用图 1-5 输入数据 图 1-6 复制“相对引用”公式5用计算结果替换公式在运用 Excel 公式时,有时需要用计算结果替换公式完成计算操作,只用右击需要复制的结果单元格,从弹出的快捷菜单中选择【复制】命令,再右击需要替换的公式单元格,从弹出的快捷菜单中选择【选择性粘贴】命令,打开【选择性粘贴】对话框,如图 1-7 所示。在其中选中“数值”单选按钮并单击【确定】按钮,完成替换操作。1.1.3 单元格的引用引用是指运用公式把其他单元格中的内容引入正在操作的单元格的过程,这个其他单元格可在同一工作表上,也可在同一工作簿的另一工作表上,或不同工作簿的工作表上。引用的种类很多,但总结起来大致有 3 种:引用单元格、引用单元格区域和引用名称。单元格的引用又分为相对引用、绝对引用、混合引用和三维引用等 4 种方式。1相对引用相对引用是指在把 Excel 工作表中含有公式的单元格复制到其他单元格中时,由于目标单元格的地址发生了变化,公式中所含单元格的地址也相对变化。【例 1-4】C97 单元格内的公式为“=A97+B97” ,如图 1-8 所示。把 C97 单元格复制到 C98 单元格,该公式自动地变为 “=A98+B98”,地址自动地进行了相对的位移,如图1-9 所示。2绝对引用绝对引用是指在公式中单元格的行列坐标前添加“$”符号,这样,这个公式复制到任何地方,这个单元格的值都绝对不变。【例 1-5】C97 单元格内的公式为“=$A$97+B97” ,如图 1-10 所示。把 C97 单元格复制到 C98 单元格,C98 单元格的公式变为“=$A$97+B98” ,如图 1-11 所示,实现了绝对图 1-7 【选择性粘贴 】对话框 7审计工作中常用的 Excel 知识 第 1 章的引用。8Excel 2007Excel 2007 在审计分析中的应用图 1-8 相对引用条件 图 1-9 相对引用的结果图 1-10 绝对引用条件 图 1-11 绝对引用的结果3混合引用混合引用是指在一个单元格地址引用中,既有绝对地址引用,同时也包含相应地址 引用。【例 1-6】C97 单元格内的公式为“=$A97+B97” ,如图 1-12 所示。把 C97 单元格的公式复制到 D97 单元格中, D97 单元格内的公式变为“$A97+C97” ,如图 1-13 所示。图 1-12 混合引用条件 图 1-13 混合引用结果4三维引用三维引用是指在一个工作簿中从不同的工作表引用单元格。其格式一般为:工作表名!单元格地址。【例 1-7】为了完成计算,需要在 Sheet2 工作表的 A1 单元格中引用 Sheet1 工作表的A97 单元格的数据。如图 1-14 所示为 Sheet1 工作表的 A97 单元格的数据,在 Sheet2 工作9审计工作中常用的 Excel 知识 第 1 章表的 A1 单元格中输入“=Sheet1!A97” ,完成三维的引用操作,如图 1-15 所示。图 1-14 三维引用条件图 1-15 三维引用结果在引用单元格的过程中,如果要将相对引用更改为绝对引用,先选中要更改公式的单元格后,在编辑栏中选择要更改的引用并按 F4 键,进行相应组合间的切换:绝对列与绝对行,绝对列与相对行,相对列与绝对行以及相对列与相对行。用户只要从中选择所需要的引用,即可完成引用的更改操作。1.2 熟悉 Excel 2007 函数Excel 2007 中所提的函数其实是一些预定义的公式,它们使用一些成为参数的特定数值按特定顺序或结构进行计算。用户可以直接用它们对某个区域内的数值进行一系列运算,如计算平均值、确定贷款的支付额、排序显示和运算文本数据等。1.2.1 函数的基本结构一般情况下,Excel 函数是由函数名称、参数和括号组成的。函数的基本结构为:函数名称(参数 1,参数 2,参数 3,参数 n)。函 数 名 称 指 出 函 数 的 含 义 , 通 常 情 况 下 是 由 一 个 字 符 串 来 表 示 的 , 函 数 的10Excel 2007Excel 2007 在审计分析中的应用名 称 是 唯 一 的 。参数位于函数名称的后面,并且需要用圆括号括起来,当存在多个参数时,参数之间需要用半角的逗号分隔开,参数是一个可以变化的量,参数的多少是随函数定义来确定的。例如,求一列数 A1 到 A10 之间所有数据的总和,其函数表达式为 “=SUM(A1:A10)”。1.2.2 参数和函数值的类型参数可以是数字、文本、逻辑值、数组、名称、单元格引用和错误值,也可以是常量、公式或其他函数,但无论是什么样的参数都必须产生有效的值。1数字数字可以是正数也可以是负数,可以是整数也可以是小数,数字可以进行算术运算,也可以进行比较运算。如果要计算 20、40、60 和 80 的平均值,可以用“=AVERAGE(20,40,60,80)”函数来表示,这样参数和函数都是数字。如果要计算单元格区域 A1:I10 中有数字的单元格的个数,就可以用“=COUNT (A1:I10)”函数来表示,其函数的返回值是一个整数。2文本文本是一个字符串,需要用引号引起来。3逻辑值逻辑值只有两个选择,即 TRUE 和 FALSE,分别代表真和假。逻辑值可以由逻辑运算或逻辑函数获得,函数 TRUE 返回逻辑值为“真” ,函数FALSE 返回逻辑值为“假” ;使用 IF 函数可以确定条件为真还是假,并由此返回不同的数值。4单元格引用单元格引用是将单元格或单元格区域作为函数的参数来进行引用。5名称名称是为了方便用户使用或引用而创建的代表单元格、单元格区域、公式或常量的字符串,如果要使用名称首先就要定义这个名称。【例 1-8】A1:A10 单元格区域内显示的是公司的供应商名称,因此需要将该单元格区域定义成供应商名称。具体的操作步骤如下:选中 A1:A10 单元格区域,选择【公式】【定义名称】命令,打开一个下拉菜 1 11审计工作中常用的 Excel 知识 第 1 章单,如图 1-16 所示。选择“定义名称”选项,打开【新建名称】对话框,如图 1-17 所示。2图 1-16 执行命令 图 1-17 【新建名称】对话框在“名称”文本框中输入相应的名称,单击【确定】按钮完成设置操作。这样,3用户在以后的计算中,直接引用这个名称可以完成计算操作。6错误值错误值是指公式不能正确计算出结果或公式引用的单元格还有错误而导致的,由于出现的错误原因不同,也会出现不同的错误值,如表 1-6 所示。表 1-6 错误值显现表错 误 值 原 因 说 明#VALUE! 函数中使用的变量或参数类型错误#NUM! 使用了无效的数字值#REF! 公式引用了一个无效的单元格#NULL! 公式中使用了一种不允许出现相交,但却交叉了的两个区域#NAME? 公式中使用了 Excel 不能辨认的文本或名称#N/A 公式中引用的数据对函数或公式不可用#DIV/0! 公式使用了 0 作为除数,或者公式中使用了一个空单元格# 列宽不够,或者包含一个无效的时间或日期在 Excel 数据输入过程中,如果出现输入错误,会出现一个【选项】按钮错误的标志。【例 1-9】在 A104 单元格中出现一个数据输入错误的标志,如图 1-18 所示。单击单元格右侧的【选项】按钮,出现一个下拉菜单,如图 1-19 所示。在该下拉菜单中选择相应的选项,进行错误的修改操作。12Excel 2007Excel 2007 在审计分析中的应用图 1-18 错误显示13审计工作中常用的 Excel 知识 第 1 章图 1-19 错误修改提示在公式与函数的运用过程中,出现错误是不可避免的,但 Excel 能够通过一定的规则检查出错误。这样,用户可以根据这些错误提示进行相应的更改。具体的操作步骤如下:在 【Excel 选项】对话框中选择 “公式”选项,进入【错误检查】窗口,如图 1- 1 20 所示。图 1-20 【错误检查】窗口在“错误检查”列表框中决定是否选中“允许后台错误检查”复选框后,再在2“错误检查规则”列表框中选中相应的复选框,最后单击【确定】按钮,完成错误检测的设置。7数组数组用于建立可产生多个结果或可对存放在行和列中的一组参数进行运算的单个公式。在 Excel 中有两类数组:区域数组和常量数组。区域数组是一个矩形的单元格区域,该区域中的单元格共用一个公式;常量数组将一组给定的常量用作某个公式的参14Excel 2007Excel 2007 在审计分析中的应用数。15审计工作中常用的 Excel 知识 第 1 章8常量常量是直接输入单元格或公式中的数字或文本,或由名称所代表的数字或文本值