巧用Excel处理和分析数据.ppt

2011 12 15 图书馆信息咨询部于明 巧用Excel处理和分析数据 Excel可以做什么 Excel MicrosoftOffice组件之一功能 对数据进行记录 计算与分析应用 管理 财经 金融 统计等领域 完成商业和工业任务个人事务处理专业科学运算 统计分析熟练掌握Excel的使用技巧 提高学习和工作效率 2011 12 15 1 主要内容 Excel概述基本操作公式与函数数据图表化数据分析总结 2011 12 15 2 Excel2003 Excel2007 Excel2010 主要内容 Excel概述基本操作公式与函数数据图表化数据分析总结 2011 12 15 3 基本概念界面 1 Excel概述 1 1基本概念工作簿 Excel创建的文件 Book1 2 3 工作表 工作簿的组成部分 存储和处理数据的主要文档数据工作表 Sheet1 2 3 图表工作表 Chart1 2 3 单元格 工作表存储数据的最基本单元行列交叉点 列标A B C 行标1 2 3 A1 B2 C3 单元格区域 多个单元格组成的矩形区域左上角单元格 右下角单元格 A1 F9 2011 12 15 4 1 Excel概述 1 1基本概念工作簿 工作表 单元格之间的关系方格 横格 一页纸 笔记本 2011 12 15 5 单元格 工作表 工作簿 1 Excel概述 1 2Excel2010界面 2011 12 15 6 活动单元格 工作表标签 行标题1 1 048 576 列标题A XFD 16 384 1 Excel概述 1 2Excel2010界面功能区 Excel2003菜单栏 选项卡 集中某一项核心任务 组 相关项的组合 命令 执行某一具体任务 更多地显示出以前隐藏在菜单和工具栏中的命令和功能 2011 12 15 7 快速访问工具栏 Excel2003工具栏 固定显示最常用的命令 对话框启动器 点击获取更多选项 1 Excel概述 1 2Excel2010界面文件选项卡 Excel2003文件菜单 Excel选项 Excel2003工具菜单 选项 2011 12 15 8 1 Excel概述 1 2Excel2010界面名称框 编辑栏 2011 12 15 9 编辑栏 显示活动单元格中的数据输入 编辑数据 值 公式 名称框 显示活动单元格的地址 名称定义名称 快速定位单元格或区域 取消 确认 插入函数向导 1 Excel概述 1 2Excel2010界面状态栏单元格模式就绪 输入 编辑自动计算结果平均值 计数 最值 求和 2011 12 15 10 1 Excel概述 1 2Excel2010界面活动单元格 当前选中的单元格 黑色边框右下角 填充柄 扩展 填充序列 选中的单元格区域黑色边框 高亮活动单元格反色填充柄 2011 12 15 11 主要内容 Excel概述基本操作公式与函数数据图表化数据分析总结 2011 12 15 12 单元格 输入 编辑数据工作表 工作簿数据处理 排序 筛选 分类汇总 2 基本操作 2 1单元格选择输入数据编辑数据设置格式查找 替换 插入 删除 清除 复制 移动 粘贴 2011 12 15 13 2 基本操作 2 1单元格 选择单元格 鼠标左键单击单元格区域 左键按住 拖动 单击首单元格 Shift 单击末单元格 连续的单元格区域单击首单元格 Ctrl 单击其他单元格 不连续的单元格 区域整行与整列 单击行标 列标 连续 拖动 Shift 不连续 Ctrl 2011 12 15 14 2 基本操作 2 1单元格 选择通过名称框 地址或名称选定 利用名称框下拉列表利用定位功能按条件选定 开始 查找与选择 转到 定位条件 2011 12 15 15 2 基本操作 2 1单元格 输入数据数据类型数字 可进行数学计算 默认右对齐 数值 整数 小数 分数 日期 时间文本 说明数据的含义 不进行数学计算 默认左对齐 中文汉字 字母数字和文本或符号的组合逻辑值 TRUE FALSE 默认居中对齐 公式 数字 文本 逻辑值 函数 2011 12 15 16 2 基本操作 2 1单元格 输入数据 日期和时间 数字日期 默认使用 或 默认1900日期系统 Windows 1904日期系统 Macintosh 日期 序列1900 1 1 11900 1 2 2 2011 12 15 40892 9999 12 31 2958465时间 默认使用 24小时制1天 24小时 10 00 00 0 23 59 59 0 999999992011 12 1519 45 33 40892 823299 2011 12 15 17 2 基本操作 2 1单元格 输入数据 技巧 手动输入数据选中 键入单元格方向键 Enter Tab Shift Enter Tab反向 快速移动光标选中单元格连续单元格区域Enter Tab Shift Enter Shift Tab 在区域内快速移动选中 鼠标 方向键 取消对区域的选择多个单元格 区域同时输入相同数据选中目标单元格 区域 在活动单元格中键入数据 Ctrl Enter 2011 12 15 18 2 基本操作 2 1单元格 输入数据 技巧 手动输入数据多个工作表同时输入相同数据Shift Ctrl 选中多个工作表 在其中一个表中键入数据单元格内数据多行显示自动换行 随列宽调整 Alt Enter 强制换行 自动换行选中 自动输入小数点输入大量相同小数位数的数据 输入结束后及时取消勾选 2011 12 15 19 2 基本操作 2 1单元格 输入数据 技巧 手动输入数据输入日期和时间日期 空格 时间Ctrl 输入当前日期Ctrl Shift 输入当前时间输入分数整数 空格 分子 分母 31 20 空格 分子 分母 03 5 09 5强制为文本类型所有类型的数据以 英文单引号 开始 文本类型 不能参与数值计算 001023 TRUE 2011 12 15 20 2 基本操作 2 1单元格 输入数据 技巧 自动输入数据记忆式键入同列 连续单元格 文本类型有效水平对齐方式 填充将输入的数据 符号重复填满整个单元格填充柄 自动填充向外拖动 扩展填充相同数据 所有数据类型 序列 数字 含数字的文本 升序 上 下 左 右降序 反向拖动向内拖动 清除 2011 12 15 21 Enter 2 基本操作 2 1单元格 输入数据 技巧 自动输入数据序列自定义序列 2011 12 15 22 文本类型 2 基本操作 2 1单元格 输入数据 技巧 导入数据数据来源 Access SQL等数据库 网站 文本文件导入文本文件两种常用文件格式 txt 制表符 tab 为分隔符 csv 英文逗号 为分隔符 默认被Excel打开文本导入向导设置分列依据的分隔符形式 按需要导入文本将文本格式的数值或日期转换为数字格式 进行数值计算 2011 12 15 23 2 基本操作 2 1单元格 输入数据 技巧 导入数据文本导入向导 3步 以 csv文件为例 2011 12 15 24 2 基本操作 2 1单元格 输入数据 技巧 数据有效性数据类型数值范围 2011 12 15 25 选中单元格时出现提示信息 输入无效数据时显示出错警告 2 基本操作 2 1单元格 输入数据 技巧 数据有效性序列 将输入的数据限制为下拉列表中的值 2011 12 15 26 键入 英文逗号间隔 或者 2 基本操作 2 1单元格 编辑数据编辑状态 修改局部数据 双击单元格 单击 光标移至编辑栏 单击 F2键鼠标 方向键 光标定位 编辑修改 Enter Tab Shift Enter Shift Tab 移动光标至其它单元格输入状态 覆盖原有数据 F2键 切换编辑 输入状态 2011 12 15 27 2 基本操作 2 1单元格 设置格式格式 样式格式 数字格式 条件格式样式格式字体 字号 文字颜色 加粗 倾斜 下划线 删除线 单元格边框 填充颜色 底纹文本方向 对齐方式使用内置单元格样式 修改 2011 12 15 28 右键 2 基本操作 2 1单元格 设置格式数字格式 更改外观显示 不更改数值 实际值显示在编辑栏中 2011 12 15 29 2 基本操作 2 1单元格 设置格式数字格式 自定义格式最多包含四个代码部分 只含三个代码部分 只含两个代码部分 只含一个代码部分 2011 12 15 30 2 基本操作 2 1单元格 设置格式条件格式 基于条件更改单元格区域的外观 2011 12 15 31 2 基本操作 2 1单元格 查找 替换 2011 12 15 32 2 基本操作 2 1单元格 插入 删除与清除右键 删除 单元格 行列清除 单元格中的内容 格式 批注 Delete键 清除内容 2011 12 15 33 2 基本操作 2 1单元格 移动和复制移动拖动 剪切 粘贴 插入已剪切复制Ctrl 拖动 复制 粘贴 插入复制粘贴 覆盖式插入已剪切 插入复制 插入式 2011 12 15 34 右键 右键 2 基本操作 2 1单元格 选择性粘贴复制 右键 见上页 剪切 拖动 Ctrl 拖动 2011 12 15 35 2 基本操作 2 2工作表选择 Shift 连续 Ctrl 不连续 单击工作表标签插入 删除 重命名 移动 复制右键 2011 12 15 36 2 基本操作 2 2工作表 设置格式应用主题添加背景页面设置纸张大小 方向页边距页眉 页脚工作表打印设置 打印内容选定区域 图表 表工作表工作簿 2011 12 15 37 2 基本操作 2 2工作表 设置数据显示方式 2011 12 15 38 隐藏行 列 冻结窗格 拆分工作表 行高 列宽 0 2 基本操作 2 3工作簿创建空白工作簿基于模板基于现有工作簿保存打开关闭 2011 12 15 39 2 基本操作 2 4数据处理 排序 数据有序化 普通排序 内置序列 自定义排序 自定义序列 单列 行排序 多列 行排序 2011 12 15 40 2 基本操作 2 4数据处理 筛选仅显示满足条件的数据 隐藏不满足条件的数据自动筛选自定义筛选高级筛选筛选不重复记录 临时隐藏重复值删除重复项 2011 12 15 41 自动筛选 复杂条件 2 基本操作 2 4数据处理 分类汇总对数据按类别进行汇总 统计分析分类汇总之前一定要先进行排序各列须有列标题 字段名 分类汇总类型单字段分类汇总多字段分类汇总嵌套分类汇总 2011 12 15 42 取消勾选 主要内容 Excel概述基本操作公式与函数数据图表化数据分析总结 2011 12 15 43 公式引用 地址 名称函数 3 公式与函数 3 1概念公式和函数是Excel实现数据计算的重要工具公式 是对数据执行计算和操作的等式函数 是预先定义的计算公式 可实现复杂的计算和操作 2011 12 15 44 公式 A1 A2 A3 A100 100 函数 AVERAGE A1 A100 3 公式与函数 3 2公式 组成以等号 开始引用 单元格引用 区域引用 名称引用 运算符 函数 常量 数字或文本 2011 12 15 45 3 公式与函数 3 2公式 使用引用引用 通过指定单元格或区域的地址 名称来使用其中的数据 使用数据的地址 名称并非数据本身建立数据之间的联系赋予工作表动态特性 随被引单元格的值变化 引用类型 单元格引用单元格区域引用名称引用 2011 12 15 46 3 公式与函数 3 2公式 单元格引用相对引用 当公式复制到其他单元格时 行和列引用相应改变 引 被引单元格相对位置不变 A1绝对引用 当公式复制到其他单元格时 行和列引用不会改变 行标和列标之前加上 A 1混合引用 行 列引用中一个是相对引用 另一个是绝对引用 A1 A 1F4键 切换引用方式输入公式时 用方向键选取引用单元格编辑公式时 用方向键在公式中移动光标移动公式时 引用均不变化 就是原有单元格中的引用地址 2011 12 15 47 3 公式与函数 3 2公式 名称引用使用名称 意义明确 便于记忆 使公式更直观易读 引用准确定义名称 在名称框中 或 单元格 区域 不相邻多单元格组合常量 不必存在于单元格中 公式 不必存在于单元格中 管理名称 2011 12 15 48 工作表 3 公式与函数 3 2公式 引用格式以单元格引用为例 工作表内引用 单元格地址工作表间引用 工作表名称 单元格地址工作簿间引用 工作簿名称 工作表名称 单元格地址 完整路径 工作簿名称 工作表名称 单元格地址引用技巧 通过单击单元格来实现引用 非手工输入 2011 12 15 49 打开状态 关闭且路径 名称含空格 单引号括起 C MyDocuments Book1 xlsx Sheet1 A1 3 公式与函数 3 2公式 计算运算符 2011 12 15 50 3 公式与函数 3 3函数 组成函数名称参数 用括号 括起参数类型 无参数型 但括号必须保留有参数型 1个 固定数量 不确定数量必要参数可选参数 用 括起 参数形式 引用 表达式 函数 常量 2011 12 15 51 函数名称 参数1 参数2 参数3 SUM A1 C3 C6 E5 3 SUM F1 F9 325 3 公式与函数 3 3函数 类型内置函数自定义函数 VBA编程定义 2011 12 15 52 3 公式与函数 3 3函数 常用SUM number1 number2 求和函数AVERAGE number1 number2 算术平均函数MAX number1 number2 求最大值函数IF logical test value if true value if false 判断函数COUNT value1 value2 计算区域包含数字单元格个数COUNTA value1 value2 计算区域非空单元格个数TODAY 返回当前日期 Ctrl NOW 返回当前时间 Ctrl Shift 2011 12 15 53 3 公式与函数 3 4高级公式使用数组用Ctrl Shift Enter确认 结束键入系统自动生成大括号 括起数组公式 不能手动输入 数组常量 用 括起 手动输入 Ctrl Shift Enter结束键入 1 2 3 4 5 6 7 8 9 分号换行 逗号换列 外 自动生成 使用迭代和循环引用 2011 12 15 54 3 公式与函数 3 5公式错误值类型 2011 12 15 55 主要内容 Excel概述基本操作公式与函数数据图表化数据分析总结 2011 12 15 56 创建图表编辑图表 4 数据图表化 4 1图表 基本功能图表 是数据的图形化表示是动态的 随对应数据的变化而自动更新将数据用图表来描述 抽象 枯燥的数据 形象直观 易于理解清晰表现出数据的差异 变化趋势和数据间的关系采用不同的图表类型 可以发现数据不同方面的特点规律图表放置位置 与数据位于相同工作表中 嵌入式图表单独占据一个新工作表 图表工作表 2011 12 15 57 切换 4 数据图表化 4 1图表 组成图表区绘图区数据系列 某一行 列 横坐标轴 分类轴 纵坐标轴 数值轴 网格线图例标题 图表标题 横纵坐标轴标题 数据标签 2011 12 15 58 4 数据图表化 4 1图表 常用类型柱形图 显示一段时间内的数据变化 各项之间的比较情况折线图 显示在相等时间间隔下数据的变化趋势饼图 显示一个数据系列中各项大小与各项总和的比例条形图 显示各项之间的比较情况 适于分类轴标签很长的情况 面积图 强调数量随时间变化的程度 引起对总值趋势的注意XY散点图 显示若干数据系列中两个变量之间的关系 2011 12 15 59 4 数据图表化 4 1图表 其他类型股价图 显示股价的波动 显示温度的波动曲面图 显示两组数据之间的不同组合圆环图 显示各个部分与整体之间的关系气泡图 对成组的三个变量进行比较雷达图 比较若干数据系列的聚合值 2011 12 15 60 4 数据图表化 4 2创建图表Excel2010 创建图表操作简单确保数据适合于图表选择包含数据的区域插入 图表 选择图表类型 生成基本图表 图表工具 关联菜单 更改外观 布局 图表数据 图表元素修饰 修改图表 2011 12 15 61 4 数据图表化 4 3修改图表图表工具 设计 更改外观 图表类型 布局 样式 和图表数据 2011 12 15 62 4 数据图表化 4 3修改图表图表工具 布局 更改图表元素 2011 12 15 63 形状样式 文本样式 排列 大小 4 数据图表化 4 4操作技巧使用Excel表格 数据适合于图表 非必要条件 2011 12 15 64 表范围内独立操作表行 列操作排序 筛选 汇总计算 表工具 关联菜单 设计 表 区域 套用表格式 4 数据图表化 4 4操作技巧快速添加图表数据复制要添加的数据区域 数据系列 分类 在图表中直接粘贴数据创建组合图表针对不同数据系列设置不同图表类型 结合次要纵坐标 数值 轴选择合适的图表类型最合适的应该是以最简单的方式表现数据的图表类型掌握图表的关键不断进行操作 图表选项很多 需要反复熟练 2011 12 15 65 4 数据图表化 4 5迷你图嵌入在单元格中的微型图表查看某行 列数据的分布形态 趋势 2011 12 15 66 主要内容 Excel概述基本操作公式与函数数