excel数据分析教程.ppt

2011-12-15,图书馆信息咨询部 于 明,巧用Excel处理和分析数据,Excel 可以做什么？,Excel Microsoft Office 组件之一功能：对数据进行记录、计算与分析应用：管理/财经/金融/统计等领域 完成商业和工业任务个人事务处理专业科学运算、统计分析熟练掌握Excel的使用技巧 提高学习和工作效率,2011-12-15,1,主要内容,Excel 概述基本操作公式与函数数据图表化数据分析总结,2011-12-15,2,Excel 2003,Excel 2007,Excel 2010,主要内容,Excel 概述基本操作公式与函数数据图表化数据分析总结,2011-12-15,3,基本概念界面,1.Excel 概述,1.1 基本概念工作簿：Excel创建的文件(Book1/2/3)工作表：工作簿的组成部分，存储和处理数据的主要文档数据工作表(Sheet1/2/3)图表工作表(Chart1/2/3)单元格：工作表存储数据的最基本单元行列交叉点(列标A/B/C+行标1/2/3A1/B2/C3)单元格区域：多个单元格组成的矩形区域左上角单元格:右下角单元格(A1:F9),2011-12-15,4,1.Excel 概述,1.1 基本概念工作簿、工作表、单元格之间的关系方格/横格一页纸笔记本,2011-12-15,5,单元格,工作表,工作簿,1.Excel 概述,1.2 Excel 2010界面,2011-12-15,6,1.Excel 概述,1.2 Excel 2010界面功能区(Excel 2003 菜单栏)选项卡(集中某一项核心任务)组(相关项的组合)命令(执行某一具体任务)更多地显示出以前隐藏在菜单和工具栏中的命令和功能,2011-12-15,7,快速访问工具栏(Excel 2003 工具栏)固定显示最常用的命令,对话框启动器点击获取更多选项,1.Excel 概述,1.2 Excel 2010界面文件选项卡(Excel 2003 文件菜单)Excel 选项(Excel 2003 工具菜单 选项),2011-12-15,8,1.Excel 概述,1.2 Excel 2010界面名称框+编辑栏,2011-12-15,9,编辑栏 显示活动单元格中的数据输入/编辑数据（值/公式）,名称框 显示活动单元格的地址/名称定义名称/快速定位单元格或区域,取消,确认,插入函数向导,1.Excel 概述,1.2 Excel 2010界面状态栏单元格模式就绪/输入/编辑自动计算结果平均值/计数/最值/求和,2011-12-15,10,1.Excel 概述,1.2 Excel 2010界面活动单元格（当前选中的单元格）黑色边框右下角填充柄（扩展/填充序列）选中的单元格区域黑色边框+高亮活动单元格反色填充柄,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-111900-1-222011-12-15408929999-12-312958465时间 默认使用:|24小时制1 天 24 小时10:00:00023:59:590.999999992011/12/15 19:45:3340892.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+:输入当前时间输入分数整数+空格+分子/分母 3 1/20+空格+分子/分母 0 3/5、0 9/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:My DocumentsBook1.xlsxSheet1!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 创建图表Excel 2010创建图表操作简单确保数据适合于图表选择包含数据的区域插入图表选择图表类型生成基本图表“图表工具”关联菜单 更改外观/布局、图表数据、图表元素修饰/修改图表,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.数据图表化,2011-12-15,66,主要内容,Excel 概述基本操作公式与函数数据图表化数据分析总结,2011-12-15,67,使用数据透视表使用What-If假设分析模拟变量表方案管理器使用反转的假设分析单变量求解规划求解使用分析工具库方差分析/相关系数/协方差/描述统计/指数平滑F-检验/傅里叶分析/直方图/移动平均/随机数发生器排位与百分比排位/回归分析/抽样分析/t-检验/z-检验,2011-12-15,68,主要内容,Excel 概述基本操作公式与函数数据图表化数据分析总结,2011-12-15,69,2011-12-15,70,2011-12-15,71,2011-12-15,72,2011-12-15,西区图书馆303 信息咨询部Tel:0551-3602330,