Excel 2003 高级教程 - PowerPoint PPT Presentation

excel 2003 n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Excel 2003 高级教程 PowerPoint Presentation
Download Presentation
Excel 2003 高级教程

play fullscreen
1 / 88
Excel 2003 高级教程
230 Views
Download Presentation
anja
Download Presentation

Excel 2003 高级教程

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

  1. Excel 2003 高级教程 讲师:高老师 电话:0592-2956189 邮件:kjf@xminfoport.com

  2. 主要内容 • EXCEL的规范化操作 • EXCEL函数与数据分析 • 图表与数据透视表 • 宏与VBA

  3. EXCEL表格的规范化操作 • 构思和设置表结构 • 建立和输入数据 • 编辑和修饰表格 • 简单使用和分析 • 保存表格数据 • 打印输出数据

  4. 制表的六个基本过程

  5. 管理项目与表格结构的关系 • 正确认识管理对象的独立性,准确拆分管理项目至最小管理单元,并建立管理项目名称与表格结构中“列”的对应关系。为数据管理提供合理的数据结构。 • 此项工作的关健在于明确表格列与行的逻辑关系,以便有效组织一个独立的管理项目,形成可供分析的表格。 案例1:规范表格示例.xls,

  6. 表格设计应注意的事项 • 管理项目与表格结构的关系 • 凡须独立管理的项目,必须单独设置项目名称,独占一列,避免混合设置 • 管理项目的独立性必须与扩展性结合考虑,以方便统计分析 • 表格结构设计必须考虑后期统计分析的应用状态。 案例2:成绩表.xls

  7. 表格数据的输入与准确性控制 • 为什么要提出准确性 • 哪些数据容易出错 • 控制原始数据输入准确性的解决方法 • 用调用法控制原始数据的准确性 • 用计算法控制加数据的准确性 • 使用数据有效性 • 使用校对工具

  8. 单元格的基本操作 • 单元格和单元格区域的选定方法 • 使用名称框选定和命名单元格区域 • 单元格的编辑操作 • 选择性粘贴的使用(演示转置和加法)

  9. 原始数据的输入 • 在单元格中输入数值、文本、日期/时间 (重点讲日期) • 序列填充的方法 • 自定义序列 • 同时在多个单元格中输入数据 • 数据有效性的设置 • 使用下拉列表输入数据的方法 • 使用多级下拉列表输入数据

  10. 表格文档的保存技巧 • 正确使用”保存”命令 • 何时使用”另存为”命令 • 使用”保存工作区”命令 • 对文件加密的保存方法

  11. 正确使用”保存”命令 • 新文件的保存规律和方法 “新文件:是通过”新建”命令打开EXCEL窗口,并在其中制作的表格文件.通常窗口”标题栏”中按新建顺序自动编号的文件名,book1.xls,book2.xls,…… 对于新文件,无论使用“保存”还是“另存为”都显示另存为对话框 • 旧文件保存与“保存”命令 • 旧文件(有文件名称),每按一次保存,执行一次覆盖过程,新的替代旧的。 设置备份保存

  12. 何时使用“另存为”命令 • 更名保存 • 将文件存到指定位置 • 改变文件的保存类型 • 使用模板文件

  13. 如何使用“保存工作区” • “工作区”也称“工作面”,是指WINDOWS桌面上正在使用的若干份电子表格文件 • 保存工作区时,原文件按原名进行保存,工作区文件只保存这些文件的位置的存储路径,相当于快捷方式。

  14. 表格数据的打印与输出 • 常规表格的打印输出 • 连续页面打印 • 打印区域设置 • 添加到打印区域 • 超大表格打印

  15. 常规表格页面控制 • 小表格的打印设置 • 面积较小,不足一张纸,可以使用居中 • 大表格的打印设置 • 超出一张纸,但超出有限,可以使用缩放的方式

  16. 有效处理超大表格 • 设置跨页打印表头 • 设置屏幕冻结表头 • 强制分屏显示(拆分窗口) • 强制分页打印(使用分页符) • 设置打印顺序(页面设置,工作表) • 设置页眉和页脚

  17. Excel菜单功能和必会命令

  18. EXCEL函数与数据分析 • 公式和函数入门 • 排序 • 筛选 • 分类汇总 • 实用函数讲解

  19. 公式和函数 公式的使用 公式是用运算符将数据、单元格地址、函数等连接在一起的式子,以等号“=”开头 =5*10-20 常数运算 =A6+B1 对单元格A6和B1中的值相加 =SQRT(10+A2) 使用Excel函数,SQRT表示求开方根 1. 运算符 算术运算符、文本运算符和关系运算符

  20. 公式举例 =8^3*25% 表示8的立方再乘以0.25,结果为128 =5*8/2^3-B2/4 若单元格B2的值是12,则结果为2 =”计算机”& ”电脑” 运算结果是”计算机电脑” = A1 & A2 把单元格A1和A2中的文本进行连接 而生成一个新的文本 = B2<42 比较结果为TRUE (设B2的值是12) = B2>=32 比较结果为FALSE = B2<12 比较结果为FALSE = A3+80 假设A3为日期型数据,则A3+80表 示日期A3加上80天

  21. 比较条件式 用于描述一个条件。例如 >5 <= "ABCD" 或写成 <=ABCD = "计算机" 或写成 "计算机" 或写成 计算机

  22. 2. 公式的输入和复制 当在一个单元格中输入一个公式后,Excel会自动加以运算,并将运算结果存放在该单元中。以后当公式中引用的单元格数据发生变动时,公式所在单元格的值也会随之变动 例1 计算出学生的总评成绩(=机试×40%+笔试×60%) 案例文件:计算机成绩.xls (1) 单击总评成绩第一个单元格E2,使之成为活动单元格 (2) 键入计算公式“=C2*0.4+D2*0.6”后按回车键,此时在单元格E2处显示出计算结果82.2 (3) 再次单击单元格E2使之成为活动单元格;单击“复制”按钮 (4) 选定区域E3:E6;单击“粘贴”按钮

  23. 单元格地址表示方式 四种表示方式 (1) 相对地址:以列标和行号组成,如A1,B2,C3等 公式复制时,若引用公式的单元格地址发生变动,公式中的相对地址会随之变动 (2) 绝对地址:以列标和行号前加上符号“$”构成,如$A$1,$B$2等。公式复制时,公式中的绝对地址保持不变,下面式子表示引用d:\book2.xls的sheet1的B5单元格 ='d:\[book2.xls]Sheet1'!B5 (3) 混合地址:它是上述两种地址的混合使用方式 如$A1(绝对列相对行),A$1(相对列绝对行)等。公式复制时,公式中相对行和相对列部分会随引用公式的单元格地址变动而变动,而绝对行和绝对列部分保持不变 (4)名称地址:事先定义好的单元格区域 (5)三级地址:演示 各部销售统计.xls 出错信息 当公式(或函数)表达不正确时,系统将显示出错信息

  24. 常见错误信息

  25. 函数的使用 格式: =<函数名>(<参数1>, <参数>,…..) 举例: = SUM(C2:C6) = AVERAGE(A1:A3,C2:C6) SUM 求和数 AVERAGE 求平方值 MAX 求最大值 MIN 求最小值 COUNT 求数值数据的个数 COUNTIF 条件求数值数据的个数 IF 条件选择

  26. 函数应用举例 例2 计算基本工资总数,并将结果存放在单元格C7中 (1) 单击单元格C7,使之成为活动单元格 (2) 键入公式“=SUM(C2:C6)”,并按回车键 例3 计算补贴总数和扣除总数,并将结果分别存放在单元格D7和E7中 (1) 选定D2:E7区域 (2) 单击“自动求和”按钮∑,即可对这两列分别求和,和数分别存放在两列的最后一个单元格上 案例文件:工资表.xls

  27. 函数应用举例 例4 计算基本工资的平均值,并将结果存放在C8中 例5 利用函数MAX求出基本工资数的最大值,并将结果存放在单元格C9中 举例 假设单元格A1,A2,A3,A4的内容分别为1,2,空,“ABC”,则COUNT(A1:A4)的值为2 举例 若要求出基本工资数大于等于500的职工人数,可以采用: = COUNTIF(C2:C6, ”>=500”) 举例 在工作表(A1:D9)中,要求出班号为21的班学生人数,可以采用: = COUNTIF(D2:D9, ”21”) 或 = COUNTIF(D2:D9, D3)

  28. 假设函数If() 格式:IF(logical_test,value_if_true,value_if_false) 功能:本函数对比较条件式(logical_test)进行测试,如果条件成立,则取第一个值(value_if_true),否则取第二个值(value_if_false) 举例 已知单元格E3中存放考试分数,现要根据该分数判断学生是否及格  =IF(E3<60,”不及格”,”及格”) 例如 成绩等级与分数的关系:成绩≥80——优良;60≤成绩<80——中;成绩<60——不及格。假设成绩存放在单元格D3中,则可以采用如下函数来实现判断和取得等级信息: =IF(D3>=80,”优良”,IF(D3>=60,”中”,”不及格”)) 练习案例:if函数练习.xls

  29. 记录的筛选 自动筛选 高级筛选 使用到各种运算符 西文符号 比较符: =, >, <, <=, >=, <> 逻辑符: AND(与), OR(或) 通配符: ?(代表单个字符), *(代表多个字符)

  30. 举例:成绩小于60: 成绩<60 • 成绩大于或等于90: 成绩>=90 • 成绩在60~80之间: 成绩>=60 AND 成绩<=80 • ●成绩≥80的班11的学生 • A. 成绩>=80 OR 班号=11 • B. 成绩>=80 AND 班号=11 • ●班11及班21的学生 • A. 班=11 OR 班号=21 • B. 班=11 AND 班号=21 • ●班11所有不及格的学生 • A. 成绩<60 OR 班号=11 • B. 成绩<60 AND 班号=11 思考题

  31. 自动筛选 • 自动筛选采用简单条件来快速筛选记录,将不满足条件的记录暂时隐藏起来,而将满足条件的记录显示在工作表上 • 通过“数据”菜单中“筛选”命令的“自动筛选”来实现的 例18 在上述成绩表中,筛选出成绩大于等于80分的记录 选定数据清单,选择“数据”菜单中的“筛选”命令 从“筛选”级联菜单中选择“自动筛选”选项,此时在标题栏中每个字段名右边出现了一个下拉箭头 单击“成绩”字段名的下拉箭头,从下拉列表单击“自定义…”选项,打开“自定义自动筛选方式”对话框 本题筛选条件:成绩>=80。设置如下

  32. 例19在上述成绩表中,查找成绩在70~90分之间的记录例19在上述成绩表中,查找成绩在70~90分之间的记录 本例题筛选条件是:成绩>=70 AND 成绩<=90,因此在“自定义自动筛选方式”对话框中设置如下

  33. 2. 高级筛选 高级筛选采用复合条件来筛选记录,并允许把满足条件的记录复制到另外的区域,以生成一个新的数据清单。 • 先建立条件区域。条件区域的第一行为条件标记行,第二行开始是条件行 。 • (1) 同一条件行的条件互为“与”(AND)的关系,表示筛选出同时满足这些条件的记录 例20 查找成绩大于等于80分,且班号为“11”的所有记录 筛选条件:成绩>=80 AND 班号=11,条件区域表示:

  34. (2) 不同条件行的条件互为“或”(OR)的关系,表示筛选出满足任何一个条件的记录 例21 查找英语和计算机课程中至少有一科成绩大于90分的记录 英语>90 OR 计算机>90,条件区域表示: (3) 对相同的列(字段)指定一个以上的条件,或条件为一个数据范围,则应重复列标题 例22 查找成绩大于等于60分,并且小于等于90分的姓“林”的记录

  35. 例23在成绩表中,查找11班及21班中成绩不及格的所有记录例23在成绩表中,查找11班及21班中成绩不及格的所有记录 • 筛选条件为: (成绩<60 AND 班号=11)OR(成绩<60 AND 班号=21)

  36. 表格数据排序 • 表格数据的排序原则 • 数据表规则 • 区域命名必须包括”表头”区域 • 排序前要添加连续编号,以保护原始序列 • 多重排序时只有主要关健字重复时才进行次要关健字的排序 • 排序规则 • 升序和降序 • 符号优先,数字其次,日期由远而近为升序,逻辑值false在前,包含公式按结果排序. • 排序报警 • 出现扩展提示要小心操作. • 排序方法 • 单一条件排序 • 多重条件排序 • 序列排序 • 恢复

  37. 简单排序 • 按关键值从小到大(或从小到大)的顺序进行排列 • 排序时,最多可以同时使用三个关键字 • 例24将成绩表按“成绩”从高到低进行排序 (1) 选定数据清单 (2) 选择“数据”菜单中的“排序”命令,打开“排序”对话框 • (3) 设置对话框

  38. 序列排序举例 可以通过序列排序实现一些特殊的查找 在排序对话框中选“选项”就可以进行序列排序,如图

  39. 使用分类汇总处理数据 • 分类汇总操作的基本原则 • 分类原则,要分类的项目必须单独一列 • 凡是需要汇总的列,数据区域中不要有空白单元格,以免分类中遗漏 • 数据区内格式应统一 • 操作原则:先排序,再汇总 • 如何处理结构性分类汇总 • 如何处理筛选式分类汇总(例库统学.xls)

  40. 结构性分类汇总 • 先要对数据清单按分类的关键字进行排序 • 例25在成绩表中,按“班号”字段分类汇总“成绩”的平均分(即统计出各个班的平均分)

  41. 筛选式分类汇总

  42. 常用函数 1. 数学函数 2. 统计函数 3. 文本函数 4. 日期和时间函数 5. 数据库函数 6. 逻辑函数 IF函数举例:在工作表的C8单元格中存放学生身高数(厘米),现要挑选170到175的人选,凡符合条件的显示“符合条件”,不符合条件的不显示,采用的公式为: = IF(AND(C8>=170, C8<=175), ”符合条件”,””) 7. 财务函数 8. 查找与引用函数

  43. 文本函数 • 连接文本& • 文本转换数值Value() • 字符长度Len() • 取字符串Right()、Left()、Mid() 演示从身份证号中取生日

  44. 日期函数运算 • 当前日期(TODAY) • 当前时间(NOW) • 求年、月、日 • 求天数(day360)

  45. 统计函数 • 取整 Int() • 四舍五入 round() • 排名次 Rank() • 求余数 Mod()

  46. 条件函数 • 逻辑运算And()、Or()、Not() • 假设函数(If) • 条件求和(sumif) • 条件计数(countif)

  47. 图表与数据透视表 • 工作表的修饰 • 创建图表 • 用图表进行数据分析 • 数据透视表操作

  48. 修饰表格  • 表格修饰的作用 • 表格数据格式的作用和处理手段 • 对齐方式和表体结构的可视手段 • 边框背景和分割阅读的处理方法 • 表格数据的安全控制和保护手段 • 条件格式和预警信息的控制手段

  49. 为什么要修饰表格 • 缺乏修饰的缺陷 • 无法突出表格数据间的内在关系 • 可读性差 • 保护性差 • 表格修饰的种类

  50. 数字内容更加易读 • 精确值处理 • 负值突出显示(赤字) • 千元格式 • 货币型数字格式 • 日期型数字格式 • 文本型数字格式 数字格式深入