Excel2003教程(完整版).ppt

电子报表处理软件Excel 学习目标 掌握在excel中输入数据 处理工作表 设置工作表格式的方法 工作表的打印掌握常用函数的使用方法掌握图表的制作 掌握数据的排序 筛选 分类汇总方法掌握用excel进行数据的分析的方法 第一部分 Excel基础知识 内容提要 Excel工作界面 列号 名字框 行号 当前单元格 工作表标签 建立基本概念 1 标题栏 当前编辑的工作簿名 2 工作簿 一般由3张表格组成一个工作簿 最多255张 3 工作表 工作的主要对象 由行 列组成的表格 列A B IV 256 行1 2 65536 4 单元格 行和列的交叉 表格的最小单位 5 活动单元格 当前正在操作的单元格 被黑线框住 6 工作表标签 Sheet1 Sheet3 当前活动的白色 其余灰色 7 标签滚动按钮 利用标签按钮 切换显示工作表 8 滚动栏 纵 横 工作簿窗口 工作表图表 工作簿与工作表 工作簿 Workbook 工作簿是计算和存储数据的文件 也就是通常意义上的Excel文件 每一个工作簿可由一个或多个工作表组成 在默认的情况下是由3个工作表组成的 工作表 Worksheet 它是工作簿的一部分 也称作电子报表 工作簿 255 建立基本概念 认识电子表格 建立基本概念 创建工作表 输入数据 输入 原始数据 中英文字符 数字 和公式 步骤1 明确内容输入的单元格 当前活动单元格 步骤2 内容输入 注意观察公式栏 确认操作 Enter Tab 取消操作 Esc 数据类型 文本 数字 日期和时间 逻辑值和出错值等 工作表的操作与编辑 创建工作表 创建公式 B4 25 SUM D5 F5 工作表的操作与编辑 使用公式公式形式 运算数据和运算符运算数据 单元格 数字 字符 区域名 区域运算符 算术 百分号 乘方 字符 文本连接 关系 引用运算符 1 冒号 区域运算符 产生对包括在两个引用之间的所有单元格的引用 B5 B15 2 逗号 联合运算符 将多个引用合并为一个引用 SUM B5 B15 D5 D15 3 空格 交叉运算符产生对两个引用共有的单元格的引用 B7 D7C6 C8 有关工作表的操作 工作表的操作方法工作表的编辑 编辑单元格数据 双击 移动或拷贝数据 工具栏按钮或鼠标拖动 有规律数据的快速填充插入与删除行 列 区域 选择工作表中的数据工作表的更名插入与删除工作表复制工作表 工作表的操作与编辑 一 单元格选取单个单元格 单击所需的单元格 连续多个单元格 单击首单元格 Shift 单击末单元格 多个不连续单元格 Ctrl 单击单元格 整行或整列 单击行头或列头 全部单元格 单击行头 列头相交的全选按钮 清除选定的单元格 选定单元格 按键盘上的Delete键 工作表的建立 二 数据的输入 1 单元格数据的输入 单击单元格 直接在单元格输入数据 双击单元格 在编辑框内输入数据 注意 数值数字右对齐 字符左对齐 数字字符输入 数字 数值输入超长 以科学记数法显示 字符输入超长 扩展到右边列 若右边有内容 截断显示 按Tab Shift Tab 进行个单元格之间的移动 2 数据自动输入 1 自动填充 指一批有规律的数据 例等差 等比数列 输入前2个数 选中该2个单元格 指向第2个单元格右下角的自动填充柄 拖曳填充柄到结束 工具 选项 自定义序列 在输入序列框依次输入序列 之间用逗号分隔 2 系统提供的序列数据例 甲 乙 丙 3 6 9 星期一 星期二 方法 输入一个数据 利用自动填充柄拖曳到结束填充 3 用户自定义序列数据 输入第一个数 选中包含该数的区域 设置填充序列 编辑 填充 序列 等差 比差 浏览工作表的方法工作表的修饰 有关工作表的操作 用编辑键进行浏览 PageUp PageDown 用名字框快速定位到某个单元格分割屏幕法 冻结拆分窗口 用工具栏中的 格式刷 按钮进行格式统一工作表标题的处理用 格式 单元格 命令修饰工作表条件格式 工作表的操作与编辑 函数是Excel自带的一些已经定义好的公式 如何在工作表中应用函数 不同类型的函数要求给定不同类型的参数 函数名 参数1 参数2 代表函数功能 B2 B3 B4 B5 B6 B7 B8 B9 B10 9 Average B2 B10 公式与函数的应用 函数应用举例 函数按功能分为以下几种类型 数学与三角函数 比如 SUM SUMIF 统计函数 比如 AVERAGE COUNTIF 逻辑函数 比如 IF 财务函数 比如 PMT 日期和时间函数 比如 TODAY 数据库函数 比如 DCOUNT 查找与引用函数 比如 VLOOKUP 文本函数 比如 CONCATENATE 信息函数 比如 ISBLANK 公式与函数的应用 Excel的函数 Excel的函数 补充 Excel的错误信息 单元格地址在公式中的作用 相对地址 A1绝对地址 A 1混合地址 A1A 1 E4 B1 A2 C2 公式的自动重算功能基于参加计算的数据均引用其存放位置 单元格地址 公式与函数的应用 创建三维公式 不同工作表中的数据所在单元格地址的表示 工作表名称 单元格引用地址不同工作簿中的数据所在单元格地址的表示 工作簿名称 工作表名称 单元格引用地址 Sale1 xls 销售统计 E 4 Sale2 xls 销售统计 E 4 Sale3 xls 销售统计 E 4 Sale4 xls 销售统计 E 4 公式与函数的应用 打印工作表 工作表的打印设置打印预览 设置页面设置页边距设置页眉 页脚设置工作表的选项 打印工作表 打印设置 在 文件 菜单的 页面设置 中设置 打印设置固定标题行 设置 打印设置固定标题行 第一页 标题 第二页 标题 更直观地显示数据 图表 图表 数值数据的图形化 创建步骤 选取数据插入图表独立图表嵌入图表编辑图表修饰图表 数据表格 图表相关名词术语 图表相关名词术语 图例 数据标记 类型轴 X 刻度线 值轴 Y 图表相关名词术语 编辑图表 选择图表类型增加或删除图表元素图表标题X轴标题Y轴标题图表标记数据标签图例刻度线 创建和修饰图表 修饰图表样例 创建和修饰图表 数据的组织与管理 数据清单概念 数据清单概念 数据清单 正确创建规则 列名唯一纯数据 杜绝空行 非法数据 同列数据的数据类型和格式应完全相同避免在一张工作表上放置多份数据清单 数据清单概念 使用 数据 记录单 菜单命令 打开某数据清单的记录单 利用记录单可以进行下列操作 输入数据修改和删除数据查找数据清单中的记录 使用记录单编辑数据清单 数据清单概念 排序条件 单一条件的排序 简单排序 多重条件的排序 轻重缓急 数据类型 升序 降序 文本A B ZZ X A数字1 2 10 9 日期Jan1 Dec31 数据排序 数据排序 1 简单排序 所谓简单排序就是根据数据清单中的某一列数据进行排序 只要单击用来排序的字段列中的任一单元格 然后再单击常用工具栏中的 降序 或 升序 按钮 数据清单中的记录就会根据该列数据的大小按降序或升序重新排列 2 多重条件的排序 如果排序的要求比较复杂 比如要先按总分进行排序 总分相同再按中医基础得分进行排序等 这时排序不再局限于单列 须使用 数据 菜单中的 排序 命令 进行复杂排序的方法是 单击数据清单的任一单元格 选择 数据 菜单的 排序 命令 在 排序 对话框中设置排序的关键字以及排序的顺序 若关键字选择为 无 则表示不参与排序操作 为保证数据库的字段名不参加排序 必须选中 当前数据清单 中的 有标题行 单选按钮 然后再单击 确定 按钮 如果想按自定义次序排序数据 或排列字母数据时想区分大小写 可在 排序 对话框中单击 选项 按钮 如图所示 自定义排序序列 升序光驱CPU硬盘主板降序光驱CPU硬盘主板自定义光驱CPU硬盘主板 数据排序 筛选数据 大表 明晰的小表 有条件的筛选 自动筛选高级筛选 数据筛选 1 自动筛选 单击数据清单中任一单元格 选择 数据 菜单中的 筛选 命令的 自动筛选 子命令 每个字段右侧均出现下拉箭头 单击某列的下拉箭头 选取筛选条件 如 单击 销售部门 列的筛选箭头 在选取筛选条件时 若选择 照相机部 如图所示 筛选结果只显示男生记录 2 取消自动筛选 选择 数据 菜单中 筛选 命令的 全部显示 子命令 或再次选择 数据 菜单中的 筛选 命令的 自动筛选 命令以去掉其前面的对号 即可关闭自动筛选 复原数据 3 高级筛选 补充 如果需要输入复杂的筛选条件 或者将符合条件的数据输出到工作表的其它单元格中 可以使用高级筛选功能 使用 高级筛选 功能 高级筛选 功能完成筛选那些需要匹配计算条件或筛选条件中含复杂的 与 和 或 关系的数据 或 与 数据筛选 高级筛选的操作方法 将表格的字段名行复制到一个单独的区域 一般为表格的下方 与表格隔开数行 称为条件设置区域 在条件设置区域中 在字段名行的下面一行开始设置筛选的条件 若多个字段的筛选条件处于同一行则表示 并且and 不在同一行时表示 或者or 单击数据清单中的任一单元格 从 数据 菜单中选择 筛选 中的 高级筛选 命令 屏幕显示 高级筛选 对话框 如图所示 在给出的对话框中的 数据区域 选定原数据清单的数据区域 系统会自动选择 包括字段名行 在 条件区域 中选择刚才设定的条件区域 包括字段名行 选定筛选出的数据显示的区域左上角单元格 否则将在原数据清单中显示筛选结果 原数据清单将不能再恢复 然后单击 确定 按钮 其处理结果 见如下图示 分类汇总 先分类 排序 后按类汇总分析 分级显示符号 分类汇总 对分类汇总的字段进行简单排序 使相同的记录集中在一起 单击数据清单的任一单元格 选择 数据 菜单的 分类汇总 命令 出现 分类汇总 对话框 P46如图所示 在 分类汇总 对话框中 选择分类字段 汇总计算的方式和要进行汇总的字段 可选择多个 分类汇总的操作步骤 单击 确定 按钮 就显示分类汇总表 如图所示 分类汇总后 数据将分级显示 单击汇总表左上角的数字1 2 3或汇总表左侧的加 减号可选择汇总的明细级别 选择 编辑 菜单中的 撤消 命令或再次选择 数据 菜单中的 分类汇总 命令 在 分类汇总 对话框中单击 全部删除 按钮 即可恢复原有数据格式 数据透视表 分类汇总适合根据一个字段进行分类 对一个或多个字段进行汇总 如果要求按多个字段进行分类汇总 用分类汇总就比较麻烦了 为此 Excel中提供了数据透视表功能 1 建立数据透视表 2 修改数据透视表 3 数据透视表的格式化 4 删除数据透视表 1 建立数据透视表 例如要统计各产品的销售数量 首先单击数据清单的任一单元格 选择 数据 菜单中的 数据透视表 命令 出现 数据透视表向导 窗口 如图所示 数据透视表向导 步骤1 对话框 根据系统提示 选择数据来源 默认为 MicrosoftExcel数据清单或数据库 一般选择该项 单击 下一步 按钮 出现如图所示 数据透视向导 步骤2 对话框 步骤2用于选定数据区域 一般情况下它会选定整个列表 用户可在工作表中重新选定区域 单击 下一步 按钮 出现如图所示 数据透视向导 步骤3 对话框 单击 版式 按钮出现如图所示的 数据透视向导 版式 对话框 将右边的字段拖到左边的图上 构造用户需要的数据透视表 单击 完成 按钮 完成数据透视表的创建 如图所示 在根据向导进行操作时 需注意以下几点 在选择数据来源时 若数据来源就是当前工作表或其它Excel工作表 可选择 MicrosoftExcel数据清单或数据库 否则应选择 外部数据源 通过ODBC打开其它格式的数据文件 若在选择 数据 菜单的 数据透视表 前已经选择了数据清单中的某一单元格为当前单元格 系统会自动选定数据区域 否则需要用户自己选择数据区域 在设置透视表布局时 若把某字段拖放到 页 中 则表示按该字段分页显示 若把某字段拖放到 行 中 则表示要按该字段进行分类汇总 若把某字段拖放到 列 中 则表示按该字段的值分列显示汇总结果 若把某字段拖放到 数据 中 则表示对该字段进行计算 如求和 求平均值等 单击 数据 中的字段名 可以选择计算方式 2 修改数据透视表用 数据透视表 工具栏 通过 视图 菜单中的 工具栏 命令可调出 数据透视表 工具栏 可以方便地修改透视表 3 数据透视表的格式化设计完数据透视表时 可以对透视表进行格式化 选取表中一个单元 再选择 格式 菜单中的 自动套用格式 命令 选取一个格式即可 也可以自定义格式化 4 删除数据透视表数据透视表一般存放在一个单独的工作表中 删除该工作表即可将数据透视表删除 数据交换 与word进行数据交换打开word文稿打开excel文件 选中要插入到word中的图表或数据 点击 编辑 复制 转入word 点击 编辑 选择性粘贴 在对话框中选择 粘贴链接 在 作为 下 选择 作为excel工作表对象 点击 确定 即可 用excel进行抽样 补充 简单随机抽样单击 工具 数据分析 在弹出的窗口中选择 随机数发生器 在参数对话框中输入总体编号的范围 随机数个数 随机数基数栏分别输入样本的个数和起始编号 分布栏选择 均匀 后按 确定 按纽 分层抽样对数据分组 确定每组抽取的样本数利用简单随机抽样抽取样本 Excel的使用技巧 补充 如何在单元格内分段 alt 回车 如何在多个单元格内输入相同内容 选定这些单元格 在其中一个单元格中输入内容后按ctrl enter键即可 如何建立超级链接如何只复制工作表中的数据如何快速设置格式如何插入批注如何从列表中选择数据输入如何显示单元格中的平方 立方及删除线等如何两次选定单元格 单击选定 按Ctrl再次单击鼠标选定此单元格 如何超越工作表保护 复制到新的工作簿 如何让不同类型数据用不同颜色显示如何建立分类下拉列表填充项 成批修改数据 比如我们做好一个EXCEL表格 填好了数据 现在想修改其中的一列 行 例如 想在A列原来的数据的基础上加8 有没有这样的公式 是不是非得手工的一个一个数据地住上加 对于这个问题我们自然想到了利用公式 当你利用工式输入A1 A1 8时 你会得到EXCEL的一个警告 MICROSOFTEXCEL不能计算该公式 只有我们自己想办法了 这里介绍一种简单的方法 第一步 在想要修改的列 假设为A列 的旁边 插入一个临时的新列 为B列 并在B列的第一个单元格 B1 里输入8 第二步 把鼠标放在B1的或下角 待其变成十字形后住下拉直到所需的数据长度 此时B列所有的数据都为8 第三步 在B列上单击鼠标右键 复制 B列 第四步 在A列单击鼠标的右键 在弹出的对话框中单击 选择性粘贴 在弹出的对话框中选择 运算 中的你所需要的运算符 在此我们选择 加 这是本方法的关键所在 第五步 将B列删除 输入有效数据 1 设置有效数据指数据类型 范围 设置数据输入提示和错误输入提示 选取要定义有效数据的区域 数据 有效数据 设置标签 数据类型 范围 输入信息标签 输入时提示 错误警告标签 出错时提示 2 有效数据的检查设置好有效数据后 当输入的数据有错 系统根据错误警告标签设置 自动显示出错提示 小结 Excel的基本概念 工作薄 工作表 活动单元格 相对引用 绝对引用 函数工作表的编辑 复制 移动 改名 设置格式图表的创建与修饰数据的排序 筛选 汇总 分析 第二部分 Excel函数 1 IF函数 主要功能 根据对指定条件的逻辑判断的真假结果 返回相对应的内容 使用格式 IF Logical Value if true Value if false 参数说明 Logical代表逻辑判断表达式 Value if true表示当判断条件为逻辑 真 TRUE 时的显示内容 如果忽略返回 TRUE Value if false表示当判断条件为逻辑 假 FALSE 时的显示内容 如果忽略返回 FALSE 应用举例 在A2单元格中输入公式 IF A1 18 符合要求 不符合要求 确信以后 如果A1单元格中的数值大于或等于18 则A2单元格显示 符合要求 字样 反之显示 不符合要求 字样 2 AND函数 主要功能 返回逻辑值 如果所有参数值均为逻辑 真 TRUE 则返回逻辑 真 TRUE 反之返回逻辑 假 FALSE 使用格式 AND logical logica2 参数说明 Logica1 Logica2 Logica3 表示待测试的条件值或表达式 最多这30个 应用举例 在C5单元格输入公式 AND A5 60 B5 60 确认 如果C5中返回TRUE 说明A5和B5中的数值均大于等于60 如果返回FALSE 说明A5和B5中的数值至少有一个小于60 3 OR函数 主要功能 返回逻辑值 仅当所有参数值均为逻辑 假 FALSE 时返回函数结果逻辑 假 FALSE 否则都返回逻辑 真 TRUE 使用格式 OR logical logica2 参数说明 Logica1 Logica2 Logica3 表示待测试的条件值或表达式 最多这30个 应用举例 在C62单元格输入公式 OR A62 60 B62 60 确认 如果C62中返回TRUE 说明A62和B62中的数值至少有一个大于或等于60 如果返回FALSE 说明A62和B62中的数值都小于60 4 INT函数 主要功能 将数值向下取整为最接近的整数 使用格式 INT number 参数说明 number表示需要取整的数值或包含数值的引用单元格 应用举例 输入公式 INT 18 89 确认后显示出18 INT类似功能函数 1 ROUND number num digits 实例 ROUND 65 25 1 返回65 3 ROUND 82 149 2 返回82 15 ROUND 21 5 1 返回202 ROUNDDOWN number num digits 实例 ROUNDDOWN 65 251 0 返回65 ROUNDDOWN 65 251 2 返回65 25 ROUNDDOWN 31415 92654 2 返回314003 ROUNDUP number num digits 实例 ROUNDUP 65 251 0 返回66 ROUNDUP 65 251 2 返回65 26 ROUNDUP 31415 92654 2 返回31500 5 ISERROR函数 主要功能 用于测试函数式返回的数值是否有错 如果有错 该函数返回TRUE 反之返回FALSE 使用格式 ISERROR value 参数说明 Value表示需要测试的值或表达式 应用举例 输入公式 ISERROR A35 B35 确认以后 如果B35单元格为空或 0 则A35 B35出现错误 此时前述函数返回TRUE结果 反之返回FALSE 6 ISBLANK函数 主要功能 用于测试函数式返回的数值是否为空格 如果是空格 该函数返回TRUE 反之返回FALSE 使用格式 ISBLANK value 参数说明 Value是需要进行检验的参数单元格 应用举例 公式 ISBLANK 返回FALSE 7 LEFT函数 主要功能 从一个文本字符串的第一个字符开始 截取指定数目的字符 使用格式 LEFT text num chars 参数说明 text代表要截字符的字符串 num chars代表给定的截取数目 应用举例 假定A38单元格中保存了 我喜欢天极网 的字符串 我们在C38单元格中输入公式 LEFT A38 3 确认后即显示出 我喜欢 的字符 8 LEN函数 主要功能 统计文本字符串中字符数目 使用格式 LEN text 参数说明 text表示要统计的文本字符串 应用举例 假定A41单元格中保存了 我今年28岁 的字符串 我们在C40单元格中输入公式 LEN A40 确认后即显示出统计结果 6 9 FIND函数 主要功能 FIND用于查找其他文本串 within text 内的文本串 find text 并从within text的首字符开始返回find text的起始位置编号使用格式 FIND find text within text start num 参数说明 ind text是待查找的目标文本 Within text是包含待查找文本的源文本 Start num指定从其开始进行查找的字符 即within text中编号为1的字符 如果忽略start num 则假设其为1 实例 如果A1 假捻部 则公式 FIND 假捻 A1 1 返回1 10 MAX函数 主要功能 求出一组数中的最大值 使用格式 MAX number1 number2 参数说明 number1 number2 代表需要求最大值的数值或引用单元格 区域 参数不超过30个应用举例 输入公式 MAX E44 J44 7 8 9 10 确认后即可显示出E44至J44单元和区域和数值7 8 9 10中的最大值 11 MID函数 主要功能 从一个文本字符串的指定位置开始 截取指定数目的字符 使用格式 MID text start num num chars 参数说明 text代表一个文本字符串 start num表示指定的起始位置 num chars表示要截取的数目 应用举例 假定A47单元格中保存了 我喜欢天极网 的字符串 我们在C47单元格中输入公式 MID A47 4 3 确认后即显示出 天极网 的字符 12 VLOOKUP函数 主要功能 在数据表的首列查找指定的数值 并由此返回数据表当前行中指定列处的数值 使用格式 VLOOKUP lookup value table array col index num range lookup 参数说明 Lookup value代表需要查找的数值 Table array代表需要在其中查找数据的单元格区域 Col index num为在table array区域中待返回的匹配值的列序号 当Col index num为2时 返回table array第2列中的数值 为3时 返回第3列的值 Range lookup为一逻辑值 如果为TRUE或省略或1 则返回近似匹配值 也就是说 如果找不到精确匹配值 则返回小于lookup value的最大数值 如果为FALSE或0 则返回精确匹配值 如果找不到 则返回错误值 N A 13 函数名称 SUM 主要功能 计算所有参数数值的和 使用格式 SUM Number1 Number2 参数说明 Number1 Number2 代表需要计算的值 可以是具体的数值 引用的单元格 区域 逻辑值等 应用举例 如在D64单元格中输入公式 SUM D2 D63 确认后即可求出语文的总分 14 函数名称 DSUM 用途 返回数据清单或数据库的指定列中 满足给定条件单元格中的数字之和 语法 DSUM database field criteria 参数 Database构成列表或数据库的单元格区域 Field指定函数所使用的数据列 Criteria为一组包含给定条件的单元格区域 15 SUMIF函数 主要功能 计算符合指定条件的单元格区域内的数值和 使用格式 SUMIF Range Criteria Sum Range 参数说明 Range代表条件判断的单元格区域 Criteria为指定条件表达式 Sum Range代表需要计算的数值所在的单元格区域 应用举例 如在D64单元格中输入公式 SUMIF C2 C63 男 D2 D63 确认后即可求出 男 生的语文成绩和 16 COUNTIF函数 主要功能 统计某个单元格区域中符合指定条件的单元格数目 使用格式 COUNTIF Range Criteria 参数说明 Range代表要统计的单元格区域 Criteria表示指定的条件表达式 应用举例 在C17单元格中输入公式 COUNTIF B1 B13 80 确认后 即可统计出B1至B13单元格区域中 数值大于等于80的单元格数目 17 DCOUNTA函数 用途 返回数据库或数据清单指定字段中满足给定条件的非空单元格数目 语法 DCOUNTA database field criteria 参数 Database构成列表或数据库的单元格区域 Field指定函数所使用的数据列 Criteria为一组包含给定条件的单元格区域 18 MATCH函数 主要功能 返回在指定方式下与指定数值匹配的数组中元素的相应位置 使用格式 MATCH lookup value lookup array match type 参数说明 Lookup value代表需要在数据表中查找的数值 Lookup array表示可能包含所要查找的数值的连续单元格区域 Match type表示查找方式的值 1 0或1 如果match type为 1 查找大于或等于lookup value的最小数值Lookup array必须按降序排列 如果match type为1 查找小于或等于lookup value的最大数值 Lookup array必须按升序排列 如果match type为0 查找等于lookup value的第一个数值 Lookup array可以按任何顺序排列 如果省略match type 则默认为1 19 MOD函数 主要功能 求出两数相除的余数 使用格式 MOD number divisor 参数说