Excel-2010教程大全完整版

1、2011-12-15 图书馆信息咨询部 于 明 巧用Excel处理和分析数据 Excel 可以做什么？ Excel Microsoft Office 组件之一组件之一 功能：对数据进行记录、计算与分析功能：对数据进行记录、计算与分析 应用：应用：管理管理/财经财经/金融金融/统计等领域统计等领域 完成商业和工业任务完成商业和工业任务 个人事务处理 专业科学运算、统计分析 熟练掌握熟练掌握Excel的使用的使用技巧技巧 提高学习和工作效率提高学习和工作效率 2011-12-151 主要内容 Excel 概述概述 基本操作基本操作 公式与函数公式与函数 数据图表化数据图表化 数据分析数据分析 总结

2、总结 2011-12-152 Excel 2003 Excel 2007 Excel 2010 主要内容 Excel 概述概述 基本操作基本操作 公式与函数公式与函数 数据图表化数据图表化 数据分析数据分析 总结总结 2011-12-153 基本概念基本概念 界面界面 1. Excel 概述 1.1 基本概念基本概念 工作簿工作簿：Excel创建的文件( (Book1/2/3) ) 工作表：工作表：工作簿的组成部分，存储和处理数据的主要文档 数据工作表(Sheet1/2/3) 图表工作表(Chart1/2/3) 单元格：单元格：工作表存储数据的最基本单元 行列交叉点(列标A/B/C+行标1/2

3、/3A1/B2/C3) 单元格区域：单元格区域：多个单元格组成的矩形区域 左上角单元格 : 右下角单元格(A1:F9) 2011-12-154 1. Excel 概述 1.1 基本概念基本概念 工作簿、工作表、单元格之间的关系工作簿、工作表、单元格之间的关系 方格方格/ /横格横格一页纸一页纸笔记本笔记本 2011-12-155 单元格单元格工作表工作表工作簿工作簿 1. Excel 概述 1.2 Excel 2010界面界面 2011-12-156 功能区功能区 名称框名称框 编辑栏编辑栏 全选全选 标题栏标题栏( (工作簿名称工作簿名称) )快速访问工具栏快速访问工具栏 文件文件 状态栏状

4、态栏 活动单元格活动单元格 工作表标签工作表标签 行标题行标题 1 1,048,576 列标题列标题 AXFD (16,384) 1. Excel 概述 1.2 Excel 2010界面界面 功能区功能区( ( Excel 2003 菜单栏菜单栏) ) 选项卡(集中某一项核心任务) 组(相关项的组合) 命令(执行某一具体任务) 更多地显示出以前隐藏在菜单和工具栏中的命令和功能更多地显示出以前隐藏在菜单和工具栏中的命令和功能 2011-12-157 快速访问工具栏快速访问工具栏( ( Excel 2003 工具栏工具栏) ) 固定显示最常用的命令 对话框启动器对话框启动器 点击获取更多选项 1.

5、 Excel 概述 1.2 Excel 2010界面界面 文件选项卡文件选项卡 ( (Excel 2003 文件菜单文件菜单) ) Excel 选项选项 ( (Excel 2003 工具菜单工具菜单 选项选项) ) 2011-12-158 1. Excel 概述 1.2 Excel 2010界面界面 名称框名称框+编辑栏编辑栏 2011-12-159 编辑栏编辑栏 显示活动单元格中的数据 输入/编辑数据（值/公式） 名称框 显示活动单元格的地址/名称 定义名称/快速定位单元格或区域 取消取消 确认确认 插入函数向导插入函数向导 1. Excel 概述 1.2 Excel 2010界面界面 状态

6、栏状态栏 单元格模式单元格模式 就绪 / 输入 / 编辑 自动计算结果自动计算结果 平均值 / 计数 / 最值 / 求和 2011-12-1510 1. Excel 概述 1.2 Excel 2010界面界面 活动单元格（当前选中的单元格）活动单元格（当前选中的单元格） 黑色边框 右下角填充柄（扩展/填充序列） 选中的单元格区域选中的单元格区域 黑色边框+高亮 活动单元格反色 填充柄 2011-12-1511 主要内容 Excel 概述概述 基本操作基本操作 公式与函数公式与函数 数据图表化数据图表化 数据分析数据分析 总结总结 2011-12-1512 单元格单元格 输入输入/ /编辑数据编

7、辑数据 工作表工作表/ /工作簿工作簿 数据处理数据处理 排序排序/ /筛选筛选/ /分类汇总分类汇总 2. 基本操作 2.1 单元格单元格 选择选择 输入数据输入数据 编辑数据编辑数据 设置格式设置格式 查找查找/替换替换/插入插入/删除删除/清除清除/复制复制/移动移动/粘贴粘贴 2011-12-1513 2. 基本操作 2.1 单元格单元格 选择选择 单元格：单元格：鼠标左键单击 单元格区域：单元格区域： 左键按住+拖动 / 单击首单元格+(Shift+单击末单元格) 连续的单元格区域 单击首单元格+(Ctrl+单击其他单元格) 不连续的单元格/区域 整行与整列：整行与整列：单击行标/列

8、标(连续：拖动/Shift不连续： Ctrl) 2011-12-1514 2. 基本操作 2.1 单元格单元格 选择选择 通过名称框通过名称框地址或名称选定：地址或名称选定：利用名称框下拉列表 利用定位功能按条件选定：利用定位功能按条件选定：开始查找与选择转到(定位条件) 2011-12-1515 2. 基本操作 2.1 单元格单元格 输入数据输入数据 数据类型数据类型 数字数字 可进行数学计算 默认右对齐 数值（整数、小数、分数） 日期、时间 文本文本 说明数据的含义，不进行数学计算 默认左对齐 中文汉字、字母 数字和文本或符号的组合 逻辑值逻辑值 TRUE、FALSE 默认居中对齐 公式公

9、式 数字、文本、逻辑值数字、文本、逻辑值 函数函数 2011-12-1516 2. 基本操作 2.1 单元格单元格 输入数据输入数据？日期和时间？日期和时间 数字数字 日期日期 默认使用默认使用 / 或或 | 默认1900 日期系统(Windows) | 1904 日期系统(Macintosh) 日期日期 序列序列 1900-1-1 1 1900-1-2 2 2011-12-15 40892 9999-12-31 2958465 时间时间 默认使用默认使用 : | 24小时制 1 天天 24 小时小时 1 0:00:00 0 23:59:59 0.99999999 2011/12/15 19:

10、45:33 40892.823299 2011-12-1517 2. 基本操作 2.1 单元格单元格 输入数据输入数据？技巧？技巧 手动输入数据手动输入数据 选中选中键入键入 单元格单元格 方向键() Enter()/Tab() Shift + Enter/Tab反向 快速移动光标选中单元格 连续单元格区域连续单元格区域 Enter/Tab/Shift+Enter/Shift+Tab 在区域内快速移动选中 鼠标/方向键 取消对区域的选择 多个单元格多个单元格/区域同时输入相同数据区域同时输入相同数据 选中目标单元格/区域在活动单元格中键入数据Ctrl+Enter 2011-12-1518 2.

11、 基本操作 2.1 单元格单元格 输入数据输入数据？技巧？技巧 手动输入数据手动输入数据 多个工作表同时输入相同数据多个工作表同时输入相同数据 Shift/Ctrl 选中多个工作表 在其中一个表中键入数据 单元格内数据多行显示单元格内数据多行显示 自动换行（随列宽调整） Alt+Enter 强制换行（自动换行选中） 自动输入小数点自动输入小数点 输入大量相同小数位数的数据 ！输入结束后及时取消勾选 2011-12-1519 2. 基本操作 2.1 单元格单元格 输入数据输入数据？技巧？技巧 手动输入数据手动输入数据 输入日期和时间输入日期和时间 日期+空格+时间 Ctrl+; 输入当前日期 C

12、trl+Shift+: 输入当前时间 输入分数输入分数 整数+空格+分子/分母 3 1/2 0+空格+分子/分母 0 3/5、0 9/5 强制为文本类型强制为文本类型 所有类型的数据以 (英文英文单引号)开始文本类型 不能参与数值计算 001023、TRUE 2011-12-1520 2. 基本操作 2.1 单元格单元格 输入数据输入数据？技巧？技巧 自动输入数据自动输入数据 记忆式键入记忆式键入 同列、连续单元格、文本类型有效 水平对齐方式水平对齐方式 填充填充 将输入的数据/符号重复填满整个单元格 填充柄填充柄 自动填充自动填充 向外拖动向外拖动 扩展填充 相同数据相同数据 所有数据类型

13、序列序列 数字/含数字的文本 升序: 上下 / 左右 降序: 反向拖动 向内拖动向内拖动 清除 2011-12-1521 Enter 填充柄 2. 基本操作 2.1 单元格单元格 输入数据输入数据？技巧？技巧 自动输入数据自动输入数据 序列序列 自定义序列自定义序列 2011-12-1522 文本类型文本类型 2. 基本操作 2.1 单元格单元格 输入数据输入数据？技巧？技巧 导入数据导入数据 数据来源：数据来源：Access/SQL等数据库；网站；等数据库；网站；文本文件文本文件 导入文本文件导入文本文件 两种常用文件格式两种常用文件格式 .txt: 制表符(tab)为分隔符 .csv: 英

14、文逗号(,)为分隔符 / 默认被Excel打开 文本导入向导文本导入向导 设置分列依据的分隔符形式按需要导入文本 将文本格式的数值或日期转换为数字格式进行数值计算 2011-12-1523 2. 基本操作 2.1 单元格单元格 输入数据输入数据？技巧？技巧 导入数据导入数据 文本导入向导（文本导入向导（3步）步）以以.csv文件为例文件为例 2011-12-1524 2. 基本操作 2.1 单元格单元格 输入数据输入数据？技巧？技巧 数据有效性数据有效性 数据类型数据类型 数值范围数值范围 2011-12-1525 公式 选中单元格时出现提示信息 输入无效数据时 显示出错警告图标图标 类型类型

15、 停止停止 禁止输禁止输入无效数据入无效数据 警告警告 不禁止输入无效数据不禁止输入无效数据 是/否/取消接受/编辑/删除 信息信息 不禁止输入无效数据不禁止输入无效数据 确定/取消接受/拒绝 限制 2. 基本操作 2.1 单元格单元格 输入数据输入数据？技巧？技巧 数据有效性数据有效性 序列序列 将输入将输入的的数据限制为下拉列表中的值数据限制为下拉列表中的值 2011-12-1526 键入英文逗号间隔 或者 单击压缩对话框 在工作表中选择单元格区域 单击展开对话框 2. 基本操作 2.1 单元格单元格 编辑数据编辑数据 编辑状态（修改局部数据）编辑状态（修改局部数据） 双击单元格单击+光标

16、移至编辑栏单击+F2键 鼠标/方向键 光标定位编辑修改 Enter/Tab/Shift+Enter/Shift+Tab移动光标至其它单元格 输入状态（覆盖原有数据）输入状态（覆盖原有数据） F2键键切换编辑切换编辑/输入状态输入状态 2011-12-1527 2. 基本操作 2.1 单元格单元格 设置格式设置格式 格式：格式：样式格式、样式格式、数字格式、条件格式数字格式、条件格式 样式格式样式格式 字体/字号、文字颜色/加粗/倾斜/下划线/删除线 单元格边框、填充颜色/底纹 文本方向/对齐方式 使用内置单元格样式（修改） 2011-12-1528 /右键 2. 基本操作 2.1 单元格单元格

17、 设置格式设置格式 数字格式数字格式( (更改外观显示更改外观显示/ /不更改数值实际值显示在编辑栏中不更改数值实际值显示在编辑栏中) ) 2011-12-1529 2. 基本操作 2.1 单元格单元格 设置格式设置格式 数字格式数字格式 自定义格式自定义格式 最多包含四个代码部分：; 只含三个代码部分：; 只含两个代码部分：; 只含一个代码部分： 2011-12-1530 代码含义格式代码原始数据显示结果 #数字占位符/不显示无效0#.#99.999.9 0数字占位符/位数比代码少时，显示无效0000.0099.9099.90 ?数字占位符/无效0位用空格对齐小数点?.?99.9 99.9

18、“ ”附加双引号中的文本#.#“ 公里”99.999.9 公里 文本占位符/引用原始文本“安徽”合肥安徽合肥 颜色 指定显示的颜色绿色#.#99.999.9 2. 基本操作 2.1 单元格单元格 设置格式设置格式 条件格式条件格式( (基于条件更改单元格区域的外观基于条件更改单元格区域的外观) ) 2011-12-1531 2. 基本操作 2.1 单元格单元格 查找查找/替换替换 2011-12-1532 2. 基本操作 2.1 单元格单元格 插入、删除与清除插入、删除与清除 右键右键 删除删除 单元格单元格/行列行列 清除清除 单元格中的内容单元格中的内容/格式格式/批注批注( (Delet

19、e键键=清除内容清除内容) ) 2011-12-1533 2. 基本操作 2.1 单元格单元格 移动和复制移动和复制 移动移动 拖动 剪切 + 粘贴/插入已剪切 复制复制 Ctrl+拖动 复制 + 粘贴/插入复制 粘贴粘贴 覆盖式覆盖式 插入已剪切插入已剪切/插入复制插入复制 插入式插入式 2011-12-1534 右键 右键 2. 基本操作 2.1 单元格单元格 选择性粘贴选择性粘贴 复制复制 右键右键( (见上页见上页) ) 剪切剪切 / 拖动拖动 / Ctrl+拖动拖动 2011-12-1535 粘贴区域中的值 =原值 + - / 复制区域中的值 2. 基本操作 2.2 工作表工作表 选

20、择选择: Shift连续 / Ctrl不连续 + 单击工作表标签 插入插入/删除、重命名、移动删除、重命名、移动/复制复制 右键 2011-12-1536 勾选 复制 在打开的工作簿之间移动 2. 基本操作 2.2 工作表工作表 设置格式设置格式 应用主题应用主题 添加背景添加背景 页面设置页面设置 纸张大小/方向 页边距 页眉/页脚 工作表 打印设置打印设置 打印内容打印内容 选定区域/图表/表 工作表 工作簿 2011-12-1537 2. 基本操作 2.2 工作表工作表 设置数据显示方式设置数据显示方式 2011-12-1538 隐藏行/列冻结窗格拆分工作表 行高/列宽=0 2. 基本操

21、作 2.3 工作簿工作簿 创建创建 空白工作簿 基于模板 基于现有工作簿 保存保存 打开打开 关闭关闭 2011-12-1539 XML 文件类型文件类型扩展名扩展名 工作簿工作簿.xlsx 启用了宏的工作簿启用了宏的工作簿.xlsm 模板模板.xltx 启用了宏的模板启用了宏的模板.xltm 非非 XML 二进制工作簿二进制工作簿.xlsb 启用了宏的加载项启用了宏的加载项 .xlam 2. 基本操作 2.4 数据处理数据处理 排序排序( (数据有序化数据有序化) ) 普通排序（内置序列）自定义排序（自定义序列）普通排序（内置序列）自定义排序（自定义序列） 单列单列/行排序多列行排序多列/行

22、排序行排序 2011-12-1540 2. 基本操作 2.4 数据处理数据处理 筛选筛选 仅显示满足条件的数据，隐藏不满足条件的数据仅显示满足条件的数据，隐藏不满足条件的数据 自动筛选 自定义筛选 高级筛选 筛选不重复记录筛选不重复记录 临时隐藏重复值临时隐藏重复值 删除重复项删除重复项 2011-12-1541 /数字筛选 自动筛选 复杂条件 2. 基本操作 2.4 数据处理数据处理 分类汇总分类汇总 对数据按类别进行汇总对数据按类别进行汇总统计分析统计分析 分类汇总之前一定要先进行排序 各列须有列标题（字段名） 分类汇总类型 单字段分类汇总 多字段分类汇总 嵌套分类汇总 2011-12-1

23、542 取消勾选 主要内容 Excel 概述概述 基本操作基本操作 公式与函数公式与函数 数据图表化数据图表化 数据分析数据分析 总结总结 2011-12-1543 公式公式 引用引用 地址地址/ /名称名称 函数函数 3. 公式与函数 3.1 概念概念 公式和函数是公式和函数是Excel实现数据计算的重要工具实现数据计算的重要工具 公式：是公式：是对数对数据据执行计算执行计算和操作和操作的等式的等式 函数：是预先定义的计算公式，可实现复杂的计算和操作函数：是预先定义的计算公式，可实现复杂的计算和操作 2011-12-1544 公式：=(A1+A2+A3+A100)/100 函数：=AVERA

24、GE(A1:A100) 3. 公式与函数 3.2 公式公式 组成组成 以等号（以等号（=）开始）开始 引用（单元格引用引用（单元格引用/区域引用区域引用/名称引用名称引用） 运算符运算符 函数函数 常量常量( (数字或文本数字或文本) ) 2011-12-1545 公式：= 表达式 =(A3-average)/SUM(A1:A20)+12 3. 公式与函数 3.2 公式公式 使用引用使用引用 引用引用：通过指定：通过指定单元格单元格或或区域区域的地址的地址/名称来使用其中的数据名称来使用其中的数据 使用数据的地址使用数据的地址/名称并非数据本身名称并非数据本身 建立数据之间的联系 赋予工作表动

25、态特性(随被引单元格的值变化) 引用引用类型：类型： 单元格引用单元格引用 单元格区域引用 名称引用名称引用 2011-12-1546 3. 公式与函数 3.2 公式公式 单元格引用单元格引用 相对引用：当公式相对引用：当公式复制复制到其他单元格时，行和列引用相应改变到其他单元格时，行和列引用相应改变 （引、被引单元格相对位置不变）（引、被引单元格相对位置不变） A1 绝对引用：当公式绝对引用：当公式复制复制到其他单元格时，行和列引用不会改变到其他单元格时，行和列引用不会改变 （行标和列标之前加上（行标和列标之前加上$） $A$1 混合引用：行、列引用中一个是相对引用，另一个是绝对引用混合引用

26、：行、列引用中一个是相对引用，另一个是绝对引用 NOW( ) 返回当前时间返回当前时间 Ctrl+Shift+: 2011-12-1553 3. 公式与函数 3.4 高级公式高级公式 使用数组使用数组 用Ctrl+Shift+Enter确认结束键入 系统自动生成大括号括起数组公式（！不能手动输入） 数组常量数组常量: 用括起( (手动输入手动输入) )Ctrl+Shift+Enter结束键入 =1,2,3;4,5,6;7,8,9(分号换行/逗号换列外自动生成) 使用迭代和循环引用使用迭代和循环引用 2011-12-1554 3. 公式与函数 3.5 公式错误值类型公式错误值类型 2011-12

27、-1555 错误值类型错误值类型说明说明 #单元格列宽不足以显示内容，或日期或时间为负值单元格列宽不足以显示内容，或日期或时间为负值 #DIV/0! 0作为除数，或引用空白单元格作为除数作为除数，或引用空白单元格作为除数 #N/A无信息无信息/数值可引用数值可引用 #NAME?引用无法识别的文本引用无法识别的文本/不存在的名称不存在的名称 #NULL! 指定两个并不相交区域的交点指定两个并不相交区域的交点 #NUM! 引用无效的数值（超限，应引用无效的数值（超限，应介于 -1*103071*10307之间）之间） #REF!引用无效的单元格（被引单元格被删除或覆盖）引用无效的单元格（被引单元格

28、被删除或覆盖） #VALUE! 引用不正确的数据类型引用不正确的数据类型 主要内容 Excel 概述概述 基本操作基本操作 公式与函数公式与函数 数据图表化数据图表化 数据分析数据分析 总结总结 2011-12-1556 创建图表创建图表 编辑图表编辑图表 4. 数据图表化 4.1 图表图表 基本功能基本功能 图表：是数据的图形化表示图表：是数据的图形化表示 是动态的是动态的 随对应数据的变化而自动更新随对应数据的变化而自动更新 将数据用图表来描述：将数据用图表来描述： 抽象、枯燥的数据 形象直观、易于理解 清晰表现出数据的差异、变化趋势和数据间的关系 采用不同的图表类型，可以发现数据不同方面

29、的特点规律 图表放置位置：图表放置位置： 与数据位于相同工作表中 嵌入式图表 单独占据一个新工作表 图表工作表 2011-12-1557 切换 4. 数据图表化 4.1 图表图表 组成组成 图表区图表区 绘图区绘图区 数据系列（某一行数据系列（某一行/列）列） 横坐标轴（分类轴）横坐标轴（分类轴） 纵坐标轴（数值轴）纵坐标轴（数值轴） 网格线网格线 图例图例 标题（图表标题、横纵坐标轴标题标题（图表标题、横纵坐标轴标题 ） 数据标签数据标签 2011-12-1558 4. 数据图表化 4.1 图表图表 常用类型常用类型 柱形图：显示一段时间内的数据变化/各项之间的比较比较情况 折线图：显示在相

30、等时间间隔下数据的变化趋势趋势 饼图：显示一个数据系列中各项大小与各项总和的比例比例 条形图：显示各项之间的比较比较情况(适于分类轴标签很长的情况) 面积图：强调数量随时间变化的程度程度/引起对总值趋势的注意 XY 散点图：显示若干数据系列中两个变量之间的关系关系 2011-12-1559 4. 数据图表化 4.1 图表图表 其他类型其他类型 股价图：显示股价的波动显示温度的波动 曲面图：显示两组数据之间的不同组合 圆环图：显示各个部分与整体之间的关系 气泡图：对成组的三个变量进行比较 雷达图：比较若干数据系列的聚合值 2011-12-1560 4. 数据图表化 4.2 创建图表创建图表 Ex

31、cel 2010创建图表操作简单创建图表操作简单 确保数据适合于图表 选择包含数据的区域 插入图表选择图表类型生成基本图表基本图表 “图表工具”关联菜单 更改外观/布局、图表数据、图表元素 修饰修饰/修改图表修改图表 2011-12-1561 4. 数据图表化 4.3 修改图表修改图表 图表工具图表工具设计：更改外观设计：更改外观( (图表类型图表类型/ /布局布局/ /样式样式) )和图表数据和图表数据 2011-12-1562 4. 数据图表化 4.3 修改图表修改图表 图表工具图表工具布局：更改图表元素布局：更改图表元素 2011-12-1563 形状样式、文本样式、排列、大小 4. 数

32、据图表化 4.4 操作技巧操作技巧 使用 Excel 表格 数据适合于图表(非必要条件) 2011-12-1564 表范围内独立操作 表行/列操作 排序/筛选/汇总计算 “表工具”关联菜单设计 连续单元格区域Excel表 Ctrl+T 表区域 /套用表格式 4. 数据图表化 4.4 操作技巧操作技巧 快速添加图表数据 复制复制要添加的数据区域(数据系列)/分类在图表中直接粘贴粘贴 数据 创建组合图表 针对不同数据系列设置不同图表类型/结合次要纵坐标(数值) 轴 选择合适的图表类型 最合适的应该是以最简单最简单的方式表现数据的图表类型 掌握图表的关键 不断进行操作 图表选项很多，需要反复熟练 2011-12-1565 4. 数据图表化 4.5 迷你图迷你图 嵌入在单元格中的微型图表嵌入在单元格中的微型图表 查看某行/列数据的分布形态/趋势 2011-12-1566 主要内容 Excel 概述概述 基本操作基本操作 公式与函数公式与函数 数据图表化数据图表化 数据分析数据分析 总结总结 2011-12-1567 5. 数据分析 使用数据