Microsoft Word - 新1-2.doc

Size: px

Start display at page:

Download "Microsoft Word - 新1-2.doc"

迭栾
5 years ago
Views:

1 第 3 章 Excel 2003 电子表格的高级应用知识点掌握 Excel 2003 公式的构成及使用掌握 Excel 2003 的函数的分类及引用掌握 Excel 2003 的常用函数的格式和功能掌握数据的排序筛选及分类汇总作为当前最流行的办公自动化软件,Excel 2003 具有灵活的数据计算功能, 这使得它在会计工作中大有作为用户可以根据需要编制一些运算公式,Excel 将按公式自动完成这些运算使用 Excel 2003 强大的数值计算和数据分析功能, 既快速, 又方便, 易于会计人员掌握 3.1 Excel 2003 中的公式 Excel 的公式是由数值字符单元格引用函数以及运算符等组成的能够进行计算的表达式这里, 单元格引用是指在公式中输入单元格地址时, 该单元格中的内容也参加运算当引用的单元格中的数据发生变化时, 公式则自动重新进行计算并自动更新计算结果, 用户可以随时观察到数据之间的相互关系 Excel 规定, 公式必须以等号 = 开头, 系统会将 = 号后面的字符串识别为公式 1. 公式中的运算符公式中的运算符主要有算术运算符字符运算符比较运算符和引用运算符四种, 它决定了公式的运算性质 (1) 算术运算符算术运算符用来完成基本的数学运算它连接数值, 产生数值结果主要的算术运算符见表所示表算术运算符运算符功能举例运算结果 +( 加号 ) 加法 = ( 减号 ) 减法 =B3-E7 单元格 B3 的值减去单元格 E7 的值 *( 乘号 ) 乘法 =5*A4 5 乘以单元格 A4 的值 /( 除号 ) 除法 =25/5 5 %( 百分比运算 ) 求百分数 =20% 0.2 ^( 指数运算 ) 乘方 =4^2 16

2 第 3 章 Excel 2003 电子表格的高级应用 39 运算的优先次序为 : 括号指数乘除加减对于同级的运算符按照从左到右的顺序进行 (2) 字符运算符字符运算符是用于将两个字符串或多个字符串连接合并为一个组合字符串, 其运行结果为字符串字符运算符见表所示表字符运算符运算符功能举例运算结果 &( 连接 ) 字符串的连接合并 =" 北京 "&" 中国 " 北京中国要连接或合并字符串, 就需要进行字符运算在字符运算的式子中除运算符和字符串外, 还可以包含单元格引用例如, 假设 A2 单元格中有字符串张山,B2 单元格中有字符串工资清单, 现要在两字符串中间加上字符串这个月的, 合并为一个字符串放在 C2 单元格中我们可以在 C2 单元格中输入公式 "=A2&" 这个月的 "&B2", 然后按回车键即可 (3) 比较运算符比较运算符用于比较两个数值的大小, 其运算结果是逻辑值, 即 True 或 False 两者之一, 其中,True 为逻辑真,False 为逻辑假比较运算符见表所示表比较运算符运算符功能举例运算结果 =( 等于 ) 等于 =50+6=56 True( 真 ) >( 大于 ) 大于 =50+46>100 False( 假 ) <( 小于 ) 小于 =50+77<200 True( 真 ) >=( 大于等于 ) 大于等于 =25+5>=115 False( 假 ) <=( 小于等于 ) 小于等于 =20+77<=97 True( 真 ) <>( 不等于 ) 不等于 =4<>2^2 False( 假 ) (4) 引用运算符引用运算符用于对单元格区域进行合并计算, 其运算结果与被引用单元格性质相同引用运算符见表所示表引用运算符运算符运算功能举例运算说明 : 区域运算 =A1:C4 A1 到 C4 单元区域, 并集运算 =B3,E7 单元格 B3 并 E7 空格交集运算 =A4_B5 单元格 A4 和 B5 的交集 2. 各类运算符的优先级运算符优先级是一套规则该规则在进行表达式运算时用来控制运算符执行的顺序具有较高优先级的运算符先于较低优先级的运算符执行对于同级运算符, 按从左到右的

3 40 Excel 在会计工作中的应用顺序执行温馨提示括号可以改变优先级 Excel 的运算符的优先级由高到低为 : 引用运算符 - 算术运算符字符运算符比较运算符 3. 单元格的引用在对单元格进行操作或运算时, 有时需要指出使用的是哪一个单元格, 这就是引用引用一般用单元格的地址来表示 Excel 提供了三种不同的单元格引用 : 绝对引用相对引用和混合引用 (1) 绝对引用绝对引用是指对单元格内容的完全套用, 不加任何更改无论公式被移动或复制到何处, 所引用的单元格地址始终不变绝对引用的表示形式为在引用单元格列号和行号之前增加符号 $ 例如: 在单元格 J4 中输入公式 =$I$4/$I$l2, 将公式复制到 J5 单元格以后, 被公式引用的单元格仍为 =I4/I12 (2) 相对引用相对引用是指引用的内容是相对而言的, 其引用的是数据的相对位置建立公式的单元格和被公式引用的单元格之间的相对位置关系始终保持不变即在复制或移动公式时, 随着公式所在单元格的位置改变, 被公式引用的单元格的位置也做相应调整以满足相对位置关系不变的要求相对引用的表示形式为列号与行号例如 :E4 单元格中的公式 =C4+D4 复制到 H4 单元格以后为 =F4+G4 (3) 混合引用混合引用是指在一个单元格引用中, 既有绝对引用, 又有相对引用例如 : 公式 =I4/$I12 表示列绝对, 行相对 =I4/I$12 表示列相对, 行绝对 (4) 地址类型的转换在编辑公式时, 当选中某个单元格地址并按下 F4 功能键, 则可以将公式中用到的相对地址转换为绝对地址, 即加上 $ 符号通过以上操作即可将公式中的单元格地址的类型进行了转换例如, 假设单元格 D1 中有公式 =B1/C1, 现想将其改为 =B1/$C$1, 可以这样操作 : 1) 将编辑栏公式中的 C1 部分选中, 或将光标定位在 C 和 1 之间 2) 按下 F4 功能键, 此时编辑栏上 C1 将自动变为 $C$1 3) 按下 Enter 键, 公式修改完毕温馨提示对于单元格地址, 如果依次按下 F4 功能键可以循环改变公式中地址的类型, 例如, 对单元格 $C$1 连续按 F4 功能键, 结果如下 : $C$1 C$1 $C1 C1 $C$1 3.2 Excel 中的函数 Excel 函数是一个预先定义好的特定计算公式, 按照这个特定的计算公式对一个或多

4 第 3 章 Excel 2003 电子表格的高级应用 41 个参数进行计算可得出一个或多个计算结果, 即函数值使用函数不仅可以完成许多复杂的计算, 而且还可以简化公式的繁杂程度 1. 函数的格式 Excel 函数由等号函数名和参数组成其格式为 : = 函数名 ( 参数 1, 参数 2, 参数 3, ) 这里, 函数名指明函数要执行的运算, 比如,SUM 和 MAX 分别表示求和与求最大值参数为指定函数使用的数值单元格引用或表达式参数要用圆括号括起来, 而且括号前后不能有空格 ; 当函数的参数在一个以上时, 必须用逗号将它们分隔开, 例如公式 =PRODUCT(A1,A3,A5,A7,A9) 表示将单元格 A1 A3 A5 A7 A9 中的数据进行乘积运算 ; 另外, 每一个参数必须能产生一个有效值函数的返回值就是计算结果 2. 函数的分类 Excel 为用户提供了十类数百个函数, 它们是常用函数财务函数日期与时间函数数学与三角函数统计函数查找与引用函数数据库函数文本函数逻辑函数以及信息函数等用户可以在公式中使用函数进行运算有关函数的分类及各类函数的函数名如图插入函数对话框所示图插入函数对话框 3. 函数的引用 Excel 函数既可以单独引用又可以在公式中被引用 (1) 单独引用当用户要单独使用函数时, 可以通过单击地址栏的插入函数按钮或选择插入函数命令具体操作如下 : 1) 单击地址栏的插入函数按钮, 打开插入函数对话框, 在对话框的或选择类别下拉列表框中选择类别, 比如常用函数, 然后在选择函数列表框中选择该类的某个函数, 比如 SUM 函数, 此时该函数被选中, 其功能将显示在对话框的下面

42 Excel 在会计工作中的应用 2) 单击确定按钮或 Enter 键, 将打开如图 1-3-2 所示的函数参数对话框利用单元格的引用方式输入参加计算的单元格区域, 比如 B1:H6, 或利用鼠标框出参数所在区域图 1-3-2 函数参数对话框 3) 若函数的参数是可变的, 则对话框将随着可选参数的增多而扩大, 但参数编辑框最多为 5 组

5 42 Excel 在会计工作中的应用 2) 单击确定按钮或 Enter 键, 将打开如图所示的函数参数对话框利用单元格的引用方式输入参加计算的单元格区域, 比如 B1:H6, 或利用鼠标框出参数所在区域图函数参数对话框 3) 若函数的参数是可变的, 则对话框将随着可选参数的增多而扩大, 但参数编辑框最多为 5 组含有插入点的参数编辑框的描述显示在对话框的底部 4) 当编辑框中输入了参数后, 此时该函数的值将显示在对话框左下方的计算结果 = 之后, 单击确定按钮即可完成函数的单独引用 (2) 公式中引用函数除可以单独引用外还可以出现在公式或函数中如果函数与其他信息一起被编写在公式中, 就得到带函数的公式具体编写步骤如下 : 1) 单击要输入公式的单元格, 输入等号 = 2) 依次输入组成公式的单元格引用数值字符运算符等 3) 公式中的函数可以直接输入函数名及参数, 也可以利用插入函数按钮选择函数输入 4) 最后, 按 Enter 键完成公式运算 4. 通配符的使用在 Excel 表格中, 如果要查找某些字符相同但其他字符不一定相同的文本时, 可以使用通配符一个通配符代表一个或多个未确定的字符通配符一般有? 和 * 两个符号, 它们代表不同的含义 (1)?( 问号 ) 表示查找与问号所在位置相同的任意一个字符例如,?en 将查找到 men ben 或 pen fen 等 (2)*( 星号 ) 表示查找与星号所在位置相同的任意个字符例如, lar* 将查找到 lard large 或 larynx 等 3.3 常用函数在日常会计工作中, 我们经常使用 Excel 为我们提供的常用函数财务函数日期与

6 第 3 章 Excel 2003 电子表格的高级应用 43 时间函数以及统计函数进行计算和财务分析这里仅对日常会计工作中用到的函数的应用加以说明 1. 数学函数 (1)ABS() 函数 [ 格式 ] ABS( 数值表达式 ) [ 功能 ] 取表达式的值的绝对值 [ 举例 ] 公式 =ABS(10-80/2) 的结果为 30 公式 =ABS(10+80/2) 的结果为 50 (2)AVERAGE() 函数 [ 格式 ] AVERAGE( 单元格区域 ) [ 功能 ] 求指定单元格区域内所有数值的平均值 [ 举例 ] 公式 =AVERAGE(B2:E9), 结果为从左上角 B2 到右下角 E9 的矩形区域内所有数值的平均值 (3)COUNT() 函数 [ 格式 ] COUNT( 单元格区域 ) [ 功能 ] 计算指定单元格区域内数值型参数的数目 [ 举例 ] 公式 =COUNT(B3:H3), 结果为 B3 到 H3 区域内数值型参数的数目 (4)MAX() 函数 [ 格式 ] MAX( 单元格区域 ) [ 功能 ] 求指定单元格区域内所有数值的最大值 [ 举例 ] 公式 =MAX(B3:H6), 结果为从左上角 B3 到右下角 H6 的矩形区域内所有数值的最大值公式 =MAX(3,5,12,33), 结果为 33 (5)MIN() 函数 [ 格式 ] MIN( 单元格区域 ) [ 功能 ] 求指定单元格区域内所有数值的最小值 [ 举例 ] 公式 =MIN(B3:H6), 结果为从左上角 B3 到右下角 H6 的矩形区域内所有数值的最小值公式 =MIN(3,5,12,33), 结果为 3 (6)ROUND() 函数 [ 格式 ] ROUND( 数值表达式,n) [ 功能 ] 对数值表达式求值并保留小数点后 n 位为小数, 并对小数点后 n+1 位进行四舍五入 [ 举例 ] 公式 = ROUND( ,2) 的结果为公式 = ROUND( ,-2) 的结果为 1800 (7)SUM() 函数 [ 格式 ] SUM( 单元格区域 ) [ 功能 ] 求指定单元格区域内所有数值的和

7 44 Excel 在会计工作中的应用温馨提示单元格区域是指 1~30 个需要求和的参数若在单元格区域中键入数字逻辑值或由数字组成的文本表达式, 它们将直接参与计算如果单元格区域内为数组或引用, 只有其中的数字将被计算数组或引用中的空白单元格逻辑值文本或错误值将被忽略如果单元格区域内为错误值或是不能转换为数字的文本, 将会导致错误 [ 举例 ] 公式 = SUM(3,5), 结果为 8 公式 = SUM(3,"5",TRUE), 由于文本值被转换为数字, 逻辑值 TRUE 被转换成数字 1, 此时结果为将 3 5 和 1 相加, 即 9 公式 = SUM(A2:A5), 结果为将 A2 A3 A4 A5 四个数相加公式 = SUM(A2:D5), 结果为将由 A2 到 D5 的矩形区域内的所有的数值相加公式 = SUM(E:E), 结果为将 E 列所有的数相加 2. 字符函数 (1)LEFT() 函数 [ 格式 ] LEFT( 字符串,n) [ 功能 ] 从指定字符串左端开始, 提取长度为 n 的子字符串温馨提示如果长度 n 为零或负数, 则结果为一个空串 ; 如果长度 n 大于等于指定字符串的长度, 结果为指定字符串本身 [ 举例 ] 公式 =LEFT("I LOVE BEIJING",6) 的结果为 I LOVE 公式 =LEFT("I LOVE BEIJING",17) 的结果为 I LOVE BEIJING (2)LEN() 函数 [ 格式 ] LEN( 字符串 ) [ 功能 ] 求指定字符串的长度温馨提示字符串的长度指字符串中所含字符的个数 [ 举例 ] 公式 =LEN("I LOVE BEIJING") 的结果为 14 (3)TRIM() 函数 [ 格式 ] TRIM( 字符串 ) [ 功能 ] 删除指定字符串中的前导和尾部的空格 [ 举例 ] 公式 ="##"&TRIM(" I LOVE BEIJING ")&"##" 的结果为 "## I LOVE BEIJING##"

8 第 3 章 Excel 2003 电子表格的高级应用日期函数 (1)TODAY() 函数 [ 格式 ] TODAY() [ 功能 ] 按指定格式返回系统当前日期 [ 举例 ] 若系统当前日期为 2008 年 5 月 1 日, 则公式 = TODAY() 的结果为 (2)DAY() 函数 [ 格式 ] DAY( 日期表达式 ) [ 功能 ] 对日期表达式求值, 并从其中取出有关日的序号 [ 举例 ] 若系统当前日期为 2008 年 5 月 1 日, 则公式 = DAY(TODAY()) 的结果为 1 (3)MONTH() 函数 [ 格式 ] MONTH( 日期表达式 ) [ 功能 ] 对日期表达式求值, 并从其中取出有关月的序号 [ 举例 ] 若系统当前日期为 2008 年 5 月 1 日, 则公式 = MONTH(TODAY()) 的结果为 5 (4)YEAR() 函数 [ 格式 ] YEAR( 日期表达式 ) [ 功能 ] 对日期表达式求值, 并从其中取出有关年的序号 [ 举例 ] 若系统当前日期为 2008 年 5 月 1 日, 则公式 = YEAR(TODAY()) 的结果为 2008 温馨提示日月年的序号是以数字型的形式表示 DAY() MONTH() 和 YEAR() 函数分别从给定的日期中提取日月年的不同部分 4. 逻辑函数 (1)AND 函数 [ 格式 ] AND( 条件 1, 条件 2,, 条件 n) [ 功能 ] 如果所有条件均为 TRUE( 即成立 ), 则结果为 TRUE, 否则结果为 FALSE [ 举例 ] 若 B3 单元格的值为 100,D3 单元格的值为 90, 则公式 =AND(B3>90,D3<60) 的结果为 FALSE (2)OR 函数 [ 格式 ] OR( 条件 1, 条件 2,, 条件 n) [ 功能 ] 如果所有条件均为 FALSE( 即不成立 ), 则结果为 FALSE, 否则, 只要有一个条件为 TRUE, 其结果为 TRUE [ 举例 ] 若 B3 单元格的值为 100,D3 单元格的值为 90, 则公式 =OR(B3>90,D3<60) 的结果为 TRUE (3)NOT 函数 [ 格式 ] NOT( 条件 ) [ 功能 ] 如果条件为 TRUE, 则结果为 FALSE, 否则, 条件为 FALSE, 则结果为 TRUE [ 举例 ] 若 B3 单元格的值为 100, 则公式 =NOT(B3>90) 的结果为 FALSE

9 46 Excel 在会计工作中的应用温馨提示一般情况下, 以上 3 个逻辑函数是与 IF 函数结合使用的 (4)IF() 函数 [ 格式 ] IF( 条件表达式, 表达式 1, 表达式 2) [ 功能 ] 首先计算条件表达式的值, 如果为 TRUE, 则函数的结果为表达式 1 的值, 否则, 函数的结果为表达式 2 的值 [ 举例 ] 若 B3 单元格的值为 100, 则公式 =IF(B3>=90," 优秀 "," 优良 ") 的结果为优秀公式 =IF(AND(B3>=90,B3<=95)," 优良 "," 不确定 ") 的结果为不确定温馨提示 IF 函数只包含 3 个参数, 它们是需要判断的条件当条件成立时的返回值和当条件不成立时的返回值当需要判断的条件多于 1 个时, 可以进行 IF 函数的嵌套, 但最多只能嵌套 7 层利用 value_if_true( 条件为 true 时的返回值 ) 和 value_if_false( 条件为 false 时的返回值 ) 参数可以构造复杂的检测条件例如, 公式 =IF(B3:B9<60," 差 ",IF (B3:B9<75," 中 ",IF(B3:B9<85," 良 "," 好 "))) IF 函数在会计数据处理中具有广泛的应用 (5)SUMIF() 函数 [ 格式 ] SUMIF( 单元格区域 1, 条件, 单元格区域 2) [ 功能 ] 对于单元格区域 1 范围内的单元格进行条件判断, 将满足条件的对应的单元格区域 2 中的单元格求和 [ 举例 ] 公式 =SUMIF(C3:C9,211,F3:F9) 的结果是将 C3:C9 区域中值为 211 的对应在 F3:F9 区域中的同行单元格的值相加温馨提示 SUMIF() 函数常用于进行分类汇总 (6)COUNTIF() 函数 [ 格式 ] COUNTIF( 单元格区域, 条件 ) [ 功能 ] 计算给定单元格区域内满足给定条件的单元格的数目 [ 举例 ] 公式 =COUNTIF(C3:C9,211) 的结果是单元格区域 C3:C9 中值为 211 的单元格的个数温馨提示在数据汇总统计分析中,COUNT() 函数和 COUNTIF() 函数是非常有用的函数 5. 财务函数 (1)DDB() 函数

第 3 章 Excel 2003 电子表格的高级应用 47 [ 格式 ] DDB(cost,salvage,life,period,factor) [ 功能 ] 根据双倍余额递减法或其他指定的方法, 返回某项固定资产在指定期间内的折旧额温馨提示 DDB 函数中, 参数 cost 表示固定资产原值,salvage 表示净残值,life 表示固定资产使用年限,period 表示

10 第 3 章 Excel 2003 电子表格的高级应用 47 [ 格式 ] DDB(cost,salvage,life,period,factor) [ 功能 ] 根据双倍余额递减法或其他指定的方法, 返回某项固定资产在指定期间内的折旧额温馨提示 DDB 函数中, 参数 cost 表示固定资产原值,salvage 表示净残值,life 表示固定资产使用年限,period 表示进行折旧计算的期次, 它的单位必须与 life 一致参数 factor 表示折旧加速因子, 它是可选项, 缺省值为 2, 表示双倍余额递减法, 若为 3, 则表示三倍余额递减法 [ 举例 ] 假定打开固定资产折旧.xls 工作表若利用双倍余额递减法计算折旧额, 其值放在 B10 单元格内操作如下 : 1) 单击单元格 B10, 输入 =, 然后单击地址栏的插入函数按钮, 打开插入函数对话框 2) 在插入函数对话框的或选择类别下拉列表框中选择财务, 在选择函数列表框中选择 DDB, 如图所示图插入函数对话框 3) 单击确定按钮, 打开 DDB 函数的函数参数对话框, 在其 5 个参数文本框中分别输入单元格引用参数, 如图所示 4) 单击确定按钮, 计算出来的结果将会出现在 B10 单元格内 (2)SLN() 函数 [ 格式 ] SLN(cost,salvage,life) [ 功能 ] 计算某项资产某一期的直线折旧额温馨提示该函数中参数 cost salvage life 分别表示固定资产原始价值折旧期末时的净残值和固定资产折旧周期

48 Excel 在会计工作中的应用图 1-3-4 函数参数对话框 [ 举例 ] 假定打开固定资产折旧.

对话框的或选择类别下拉列表框中选择财务, 在选择函数列表框中选择 SLN, 如图 1-3-5 所示图 1-3-5 插入函数对话框 3) 单击确定按钮, 打开 SLN 函数的函数参数

11 48 Excel 在会计工作中的应用图函数参数对话框 [ 举例 ] 假定打开固定资产折旧.xls 工作表若利用直线法计算当月折旧额, 其值放在 B12 单元格内操作如下 : 1) 单击单元格 B12, 输入 =, 然后单击地址栏的插入函数按钮, 打开插入函数对话框 2) 在插入函数对话框的或选择类别下拉列表框中选择财务, 在选择函数列表框中选择 SLN, 如图所示图插入函数对话框 3) 单击确定按钮, 打开 SLN 函数的函数参数对话框, 在其 3 个参数文本框中分别输入单元格引用参数, 如图所示 4) 单击确定按钮, 计算出来的结果将会出现在 B12 单元格内 (3)SYD() 函数 [ 格式 ] SYD(cost,salvage,life,period) [ 功能 ] 返回某项固定资产按年数总和折旧法计算的每期折旧金额

第 3 章 Excel 2003 电子表格的高级应用 49 图 1-3-6 函数参数对话框温馨提示该函数中, 参数 cost salvage life period 的含义与 DDB 函数一致 [ 举例 ] 假定打开固定资产折旧.

12 第 3 章 Excel 2003 电子表格的高级应用 49 图函数参数对话框温馨提示该函数中, 参数 cost salvage life period 的含义与 DDB 函数一致 [ 举例 ] 假定打开固定资产折旧.xls 工作表若利用年数总和折旧法计算折旧额, 其值放在 B12 单元格内操作如下 : 1) 单击单元格 B12, 输入 =, 然后单击地址栏的插入函数按钮, 打开插入函数对话框 2) 在插入函数对话框的或选择类别下拉列表框中选择财务, 在选择函数列表框中选择 SYD, 如图所示图插入函数对话框 3) 单击确定按钮, 打开 SYD 函数的函数参数对话框, 在其 4 个参数文本框中分别输入单元格引用参数, 如图所示 4) 单击确定按钮, 计算出来的结果将会出现在 B12 单元格内

50 Excel 在会计工作中的应用图 1-3-8 函数参数对话框 3.4 数据排序排序是将数据列表中的记录按照某个字段名的数据值或条件从小到大或从大到小地进行排列用来排序的字段名或条件称为排序关键字 1.

13 50 Excel 在会计工作中的应用图函数参数对话框 3.4 数据排序排序是将数据列表中的记录按照某个字段名的数据值或条件从小到大或从大到小地进行排列用来排序的字段名或条件称为排序关键字 1. 数据排序应遵循的原则 (1) 如果用某一列来做排序关键字, 则在该列上有完全相同项的行将保持它们的原始次序 (2) 在排序中有空白单元格的行会被放置在排序的数据列表的最后 (3) 被隐藏的行不会被移动, 除非它们是分级显示的一部分 (4) 排序选项在最后一次排序后会被保存下来, 直到修改它们或修改选定区域或列标记为止 (5) 如果按多列的排序关键字进行排序, 则主要的列中有完全相同项的行会根据指定的第二列作排序第二列中有完全相同项的行会根据指定的第三列作排序 2. 单个关键字排序当数据列表中的数据需要按照某一个关键字进行升序或降序排列, 只需首先单击该关键字所在列的任意一个单元格, 然后单击常用工具栏中的升序按钮或降序按钮即可完成排序 3. 多个关键字排序当数据列表中的数据需要按照一个以上的关键字进行升序或降序排列, 可以通过排序命令进行 (1) 选定需要排序的单元格区域, 单击数据排序菜单命令, 打开排序对话框, 如图所示 (2) 在主要关键字下拉列表框选择第一关键字及排序方式, 然后在次要关键字下拉列表框选择第二关键字及排序方式, 最后在我的数据区域中选中有标题行,

表示第一行作为标题行不参与排序第 3 章 Excel 2003 电子表格的高级应用 51 图 1-3-9 排序对话框 (3) 单击确定按钮结束排序温馨提示由于数据之间的相关性, 有关系的数据都应被选定在排序区域内, 否则,

对话框中的选项按钮, 打开排序选项对话框, 如图 1-3-10 所示, 在此可自定义排序次序可以选择按英文字母排序时是否区分大小写, 在排序方向上, 也可以根据需要按列排序或按行排序, 在排序方法上, 可选择按

14 表示第一行作为标题行不参与排序第 3 章 Excel 2003 电子表格的高级应用 51 图排序对话框 (3) 单击确定按钮结束排序温馨提示由于数据之间的相关性, 有关系的数据都应被选定在排序区域内, 否则, 就不能进行排序操作例如, 如果用户在数据列表中有 6 列, 但在对数据进行排序之前只选定了它们中的 3 列, 则剩下的列将不会被排序, 从而使排序结果张冠李戴如果已经产生了这种错误, 单击工具栏上的撤销按钮即可还原单击排序对话框中的选项按钮, 打开排序选项对话框, 如图所示, 在此可自定义排序次序可以选择按英文字母排序时是否区分大小写, 在排序方向上, 也可以根据需要按列排序或按行排序, 在排序方法上, 可选择按字母排序或按笔划排序图排序选项对话框 3.5 数据筛选使用数据列表常常需要能很快找到信息例如, 要查找某部门中年龄在 45 岁以上的女职工的名单及年龄等数据筛选是查找和处理单元格区域中数据子集的快捷方法筛选

15 52 Excel 在会计工作中的应用与排序不同, 它并不重排区域, 只会显示出包含某一值或符合一组条件的行而隐藏其他的行 Excel 提供的自动筛选自定义自动筛选和高级筛选可以满足大部分需要 1. 自动筛选自动筛选是指一次只能对工作表中的一个单元格区域进行筛选, 包括按选定内容筛选, 它适用于简单条件下的自动筛选当使用自动筛选命令时, 自动筛选箭头将显示在筛选区域中列标签的右侧筛选时, 首先选择要进行筛选的数据区域, 单击数据筛选自动筛选菜单命令, 此时列标题 ( 字段名 ) 的右侧即出现, 然后根据筛选条件单击其列标题右侧的进行选择, 所需的记录将被筛选出来, 其余记录被隐藏 2. 自定义筛选在进行数据筛选时, 往往会用到一些特殊的条件, 用户可以通过自定义筛选器进行筛选自定义筛选既可以显示含有一个值或另一个值的行, 也可以显示某个列满足多个条件的行单击列标题右侧的, 在弹出的下拉列表中选择 ( 自定义 ) 选项, 打开自定义自动筛选方式对话框, 如图所示图自定义自动筛选方式对话框在列表框中对该字段进行条件设定, 然后单击确定按钮即可得到筛选出的记录温馨提示单击数据筛选自动筛选命令, 取消自动筛选前的复选标记, 列标题右侧的将同时消失, 取消自动筛选, 数据将全部还原 3. 高级筛选与以上两种筛选方法相比, 高级筛选可以选用更多的筛选条件, 并且可以不使用逻辑运算符而将多个筛选条件加以逻辑运算高级筛选还可以将筛选结果从数据列表中抽取出来并复制到当前工作表的指定位置 (1) 条件区域的构成使用高级筛选时, 需要建立一个条件区域条件区域是用来指定筛选的数据所必须满足的条件条件区域的构成如下 : 1) 条件区域的首行输入数据列表的被查询的字段名, 如基本工资适当补贴

第 3 章 Excel 2003 电子表格的高级应用 53 等, 字段名的拼写必须正确并且要与数据列表中的字段名完全一致 2) 条件区域内不一定包含数据列表中的全部字段名, 可以使用复制粘贴的方法输入需要的字段名, 并且不一定按字段名在数据列表中的顺序排列 3) 在条件区域的第二行及其以下各行开始输入筛选的具体条件, 可以在条件区域的同一行输入多重条件

16 第 3 章 Excel 2003 电子表格的高级应用 53 等, 字段名的拼写必须正确并且要与数据列表中的字段名完全一致 2) 条件区域内不一定包含数据列表中的全部字段名, 可以使用复制粘贴的方法输入需要的字段名, 并且不一定按字段名在数据列表中的顺序排列 3) 在条件区域的第二行及其以下各行开始输入筛选的具体条件, 可以在条件区域的同一行输入多重条件在同一行输入的多重条件其间的逻辑关系是与 ; 在不同行输入的多重条件其间的逻辑关系是或 (2) 高级筛选的操作首先在数据列表的空白区域建立条件区域比如在工资表中筛选出实发工资在 4000 元 ( 含 4000 元 ) 以上的部门经理和实发工资在 2500 元以下的管理人员, 则建立的条件区域如图所示图建立的条件区域然后单击数据筛选高级筛选命令, 打开高级筛选对话框, 如图所示图高级筛选对话框选择筛选结果放置的位置, 分别单击列表区域和条件区域右侧的按钮, 折叠对话框, 选择数据区域和条件区域, 单击确定按钮, 将得到筛选结果温馨提示单击数据筛选全部显示命令, 可取消筛选, 恢复全部数据 3.6 分类汇总 1. 常用的统计函数在 Excel 中, 为了便于进行分类汇总操作, 以汇总统计的方式为用户提供了常用的统计函数它们中有计数求和求平均值求最大值和最小值求乘积计数值标准偏差总体标准偏差方差和总体方差等函数

54 Excel 在会计工作中的应用各个汇总统计函数的格式和功能如表 1-3-5 所示表 1-3-5 分类汇总统计函数函数格式功能计数 Count() =COUNT( 指定区域 ) 计算指定区域内数值型参数的数目求和 Sum() =SUM( 指定区域 ) 求指定区域内所有数值的和求平均值 Average() =AVERAGE( 指定区域 ) 求指定区域内所有数值的平均值求最大值

17 54 Excel 在会计工作中的应用各个汇总统计函数的格式和功能如表所示表分类汇总统计函数函数格式功能计数 Count() =COUNT( 指定区域 ) 计算指定区域内数值型参数的数目求和 Sum() =SUM( 指定区域 ) 求指定区域内所有数值的和求平均值 Average() =AVERAGE( 指定区域 ) 求指定区域内所有数值的平均值求最大值 Max() =MAX( 指定区域 ) 求指定区域内所有数值的最大值求最小值 Min() =MIN( 指定区域 ) 求指定区域内所有数值的最小值求乘积 Product() =PRODUCT( 指定区域 ) 求指定区域内所有数值的乘积计数值 Count Nums() =COUNT NUMS( 指定区域 ) 计算指定区域内数字数据的记录个数标准偏差 Stdev() =STDEV( 指定区域 ) 估算给定样本的标准偏差总体标准偏差 Stdevp() =STDEVP( 指定区域 ) 计算给定的样本总体的标准偏差方差 Var() =VAR( 指定区域 ) 估算给定样本的方差总体方差 Varp() =VARP( 指定区域 ) 计算给定的样本总体的方差 2. 分类汇总命令分类汇总除使用 Excel 提供的统计函数外还可以根据某一字段的字段值, 对记录进行分类和对各类记录的数值字段进行统计, 如求和求均值计数求最大值求最小值等温馨提示在进行分类汇总前应先对数据列表按汇总的字段进行排序操作步骤如下 : 首先, 单击数据分类汇总命令, 打开分类汇总对话框, 如图所示图分类汇总对话框然后在分类字段下拉列表中选择分类字段, 在汇总方式下拉列表中选择汇总方式, 拖动选定汇总项的滚动条选择需汇总的字段, 最后单击确定按钮即可

18 第 3 章 Excel 2003 电子表格的高级应用 55 温馨提示在分类汇总对话框中, 单击全部删除按钮即可取消分类汇总操作 3.7 合并计算合并计算用于对多张工作表中相同字段不同记录的数据进行统计计算 (1) 在进行合并计算之前先建立一张同结构的工作表用来存放统计结果 (2) 单击数据合并计算命令, 打开合并计算对话框, 如图所示图合并计算对话框 (3) 在函数下拉列表中选择要计算的函数, 单击引用位置的折叠按钮, 折叠对话框, 选择合并的第一张工作表标签并选中待合并数据的源单元格区域 (4) 单击合并计算 - 引用位置对话框的折叠按钮, 展开该对话框 (5) 单击添加按钮, 第一个数据源区域即出现在所有引用位置中, 重复以上步骤将其他工作表的单元格区域依次添加到所有引用位置列表框中 (6) 选中复选框, 当工作表中的源数据发生变化时, 合并计算的结果数据也随之变化, 然后单击确定按钮完成合并计算温馨提示单击左侧的和钮, 可以分级查看合并项的源数据 3.8 数据透视表数据透视表是一种交互式工作表, 用于对现有数据列表进行汇总和分析创建数据透视表后, 可以按不同的需要, 依不同的关系来提取和组织数据 (1) 用鼠标单击数据列表中的任一单元格 (2) 选择数据数据透视表和数据透视图菜单命令, 打开数据透视表和

56 Excel 在会计工作中的应用数据透视图向导 3 步骤之 1 对话框, 如图 1-3-16 所示默认待分析的数据源类型为 Microsoft Office Excel 数据列表或数据库, 所需创建的报表类型为数据透视表

3 步骤之 2 对话框在该对话框中的选定区域文本框中输入要建立数据透视表的数据源区域一般情况下会选定整个列表, 用户也可键入修改或用鼠标在工作表中重新选定区域如果数据源来自外部文件, 可单击浏览按钮, 打开浏览

1-3-18 数据透视表和数据透视图向导 3 步骤之 3 对话框 (5) 选中新建工作表单选按钮, 单击完成按钮后, 屏幕上将出现数据透视表布局窗口, 并在该窗口中出现了数据透视表字段列表和一个数据透视表工具栏,

19 56 Excel 在会计工作中的应用数据透视图向导 3 步骤之 1 对话框, 如图所示默认待分析的数据源类型为 Microsoft Office Excel 数据列表或数据库, 所需创建的报表类型为数据透视表图数据透视表和数据透视图向导 3 步骤之 1 对话框 (3) 单击下一步按钮, 打开数据透视表和数据透视图向导 3 步骤之 2 对话框, 如图所示图数据透视表和数据透视图向导 3 步骤之 2 对话框在该对话框中的选定区域文本框中输入要建立数据透视表的数据源区域一般情况下会选定整个列表, 用户也可键入修改或用鼠标在工作表中重新选定区域如果数据源来自外部文件, 可单击浏览按钮, 打开浏览对话框进行查找 (4) 单击下一步按钮, 打开数据透视表和数据透视图向导 3 步骤之 3 对话框, 如图所示该对话框中有两个单选按钮新建工作表和现有工作表, 用来决定数据透视表的放置位置图数据透视表和数据透视图向导 3 步骤之 3 对话框 (5) 选中新建工作表单选按钮, 单击完成按钮后, 屏幕上将出现数据透视表布局窗口, 并在该窗口中出现了数据透视表字段列表和一个数据透视表工具栏, 如图所示用鼠标拖动部门字段到请将页字段拖到此处的位置, 将姓名字段拖到请将行字段拖到此处的位置, 将人员类别字段拖到将列字段拖到此处的位置, 将基本工资字段拖到请将数据项拖至此处的位置, 完成数据

透视表的创建, 得到如图 1-3-20 所示的数据透视表第 3 章 Excel 2003 电子表格的高级应用 57 图 1-3-19 数据透视表工具栏图 1-3-20 建立的数据透视表温馨提示拖入数据区的字段如果是非数字型则默认为对其进行计数 ; 如果是数字型则默认为进行求和如果源数据列表中的数据发生了变化, 而数据透视表并不随之变化, 此时用户不需要重新生成透视表, 只需单击

20 透视表的创建, 得到如图所示的数据透视表第 3 章 Excel 2003 电子表格的高级应用 57 图数据透视表工具栏图建立的数据透视表温馨提示拖入数据区的字段如果是非数字型则默认为对其进行计数 ; 如果是数字型则默认为进行求和如果源数据列表中的数据发生了变化, 而数据透视表并不随之变化, 此时用户不需要重新生成透视表, 只需单击数据更新按钮即可更新透视表中的数据 3.9 图表的制作图表在数据统计中用途很大, 形象直观的图表与文字数据相比更容易让人理解图表广泛地应用于数据显示和分析, 它可以通过图形的方式直观地表示出数值大小及变化趋势等 1. 图表 Excel 工作表中的数据可以用图形的方式来表示图表具有较好的视觉效果, 可方便用户查看数据的差异图案和预测趋势例如, 用户不必分析工作表中的多个数据列就可

21 58 Excel 在会计工作中的应用以立即看到数据的升降, 或者方便地对不同数据项进行比较 2. 图表的种类 Excel 中可以建立两种图表 : 嵌入式图表和独立式图表嵌入式图表与建立工作表的数据共存于同一工作表中, 独立式图表则单独存在于另一个工作表中 3. 图表的类型 Excel 2003 提供了 14 种类型的图表, 分别如下 (1) 柱形图 : 柱形图用于显示一段时间内的数据变化或说明项目之间的比较结果 (2) 条形图 : 条形图显示了各个项目之间的比较情况纵轴表示分类, 横轴表示值 (3) 折线图 : 折线图显示了相同间隔内数据的预测趋势 (4) 饼图 : 饼图显示了构成数据系列的项目相对于项目总和的比例大小饼图中只显示一个数据系列 ; 当希望强调某个重要元素时, 饼图就很有用 (5)XY 散点图 :XY 散点图既可以显示多个数据系列的数值间的关系, 也可以将两组数字绘制成一系列的 XY 坐标 (6) 面积图 : 面积图强调了随时间的变化幅度由于也显示了绘制值的总和, 因此面积图也可显示部分相对于整体的关系 (7) 圆环图 : 像饼图一样, 圆环图也显示了部分与整体的关系, 但圆环图可以包含多个数据系列圆环图的每一个环都代表一个数据系列 (8) 雷达图 : 在雷达图中, 每个分类都有它自己的数值轴, 每个数值轴都从中心向外辐射而线条则以相同的顺序连接所有的值 (9) 曲面图 : 当希望在两组数据间查找最优组合时, 曲面图将会很有用 (10) 气泡图 : 气泡图是一种 XY( 散点 ) 图数据标记的大小反映了第三个变量的大小 (11) 股价图 : 盘高盘低收盘图常用来说明股票价格 (12) 圆锥图圆柱图和棱锥图 : 圆锥图圆柱图和棱锥图数据标记能使三维柱形图和条形图具有生动的效果每种类型的图表还有若干子类型, 如柱形图中有簇状柱形图堆积柱形图百分比柱形图三维簇状柱形图三维堆积柱形图三维百分比柱形图和三维柱形图共 7 个子图表类型另外还可按照自己的需要自定义图表的类型 4. 使用一步创建法创建图表在工作表上选定要创建图表的数据区域, 按下 F11 键, 可插入一张新的独立式图表, 该方法只能建立独立式图表 5. 使用图表向导创建图表在工作表上选定要创建图表的数据区域, 单击常用工具栏中图表向导按钮或单击插入图表菜单命令, 打开图表向导对话框, 选择图表类型, 按照向导提示依次设定图表源数据的数据区域, 图表选项中的图表标题图表位置等内容很容易就创建所需的图表

60 新编计算机应用基础实训指导 (2) 单元格显示格式设置选中 (A1:I1) 单元格区域, 在格式工具栏中选择加粗按钮, 将字体设置为加粗显示 ; 选择居中显示按钮, 将字体设置为居中显示 ; 选择底纹设置按钮, 将单元格底纹设置为黄色, 显示结果如图 3 3 所示图 3

60 新编计算机应用基础实训指导 (2) 单元格显示格式设置选中 (A1:I1) 单元格区域, 在格式工具栏中选择加粗按钮, 将字体设置为加粗显示 ; 选择居中显示按钮, 将字体设置为居中显示 ; 选择底纹设置按钮, 将单元格底纹设置为黄色, 显示结果如图 3 3 所示图 3 模块三电子表格软件 Excel 训练项目 1 教师人事档案管理表的录入与编辑训练目标掌握 Excel 2003 工作簿的建立保存与打开方法掌握工作表中各种不同类型数据的输入方法掌握单元格格式的设置方法掌握表格边框和底纹的设置方法掌握特殊符号的输入方法训练内容制作教师人事档案管理表, 最终显示效果如图 3 1 所示图 3 1 教师人事档案管理表样本实训步骤 (1) 启动 Excel