sigleona--Excel2007 数据分析及处理高级技巧.ppt

1、Excel2007 数据分析及处理高级技巧Manage 插入当前日期 Ctrl+Shift+;插入当前时间,第二章 基本操作,2.1.1数据输入注意事项 -格式化的先后顺序：,脑筋急转弯,1+1在什么情况下不等于2？,不等于2， 是你的错， 还是我的错？,提问：,随便找个人回答,重新认识数据格式,答案：在单元格格式设置错误的情况下,文本数据转数值数据的方法,第二章 基本操作,2.2 数据的填充 自动输入数据 填充柄的使用：黑色细十字光标 序列输入 Office 按钮excel选项编辑自定义列表：手动输入与导入 按序列排序：数据排序次序自定义序列 填充相同数据 填充柄 Ctrl+Enter,高效

2、率人事必备技能之一（批量输入）,Ctrl+Enter,CTRL+G与F5,选择数据区域 按F5或者CTRL+G,关键点一：输入0,关键点二：输入0后，先按CTRL，再按ENTER,第二章 基本操作,2.3 数据有效性 数据有效性 利用有效性控制表格内容输入 利用序列(list，下拉列表)控制输入内容 控制输入文本长度 设置数据有效性 设置提示信息文字 设置警告信息 清除数据的有效性 数据有效性设置任何值,第二章 基本操作,2.4 数据移动、复制 数据移动、复制 选择性粘贴：可多次使用 数值 列宽 行列转置 比较转置函数Transpose(),第二章 基本操作,2.5 数据查找和替换 查找指定的

3、数据 按照特定的条件查找指定数据 替换指定的数据 按照特定的条件替换指定的数据,第二章 基本操作,2.6超链接和链接函数 超链接 创建超链接 修改超链接 删除超链接 链接函数 HYPERLINK(link_location, friendly_name) =HYPERLINK(,H13) =HYPERLINK(FINANCEStatements1stqtr.xls, D5),第三章 工作表格式美化设计,3.1 单元格格式 设置单元格格式(1) 格式单元格 右键设置单元格格式 数字类型：数值、货币、日期、自定义 文本对齐方式 手动换行（Alt+Enter）与自动换行 单元格字体 单元格边框 单元

4、格图案 单元格保护（在保护工作表状态下生效） 锁定 隐藏 可编辑区域,第三章 工作表格式美化设计,3.2 工作表格式 行列格式 开始单元格格式 自动调节行高、列宽（双击） 手动输入调节行列高：右击设置 行列的插入 行列的隐藏和取消隐藏 复制工作表 重命名工作表 工作表标签颜色,第三章 工作表格式美化设计,3.3 工作表的屏幕显示 冻结和拆分窗口 视图冻结窗格/拆分窗口 陷藏和显示工作表 开始单元格格式 右击工作表标签 3.4 选择性粘贴(格式) 3.5 使用格式刷：单击与双击,第三章 工作表格式美化设计,3.6 条件格式 开始条件格式 使用公式：=and（表达式1，表达式2），主要绝对与相对引

5、用使用“$” 设置不同效果的条件格式 设置员工出生日期为1975年前的显示红色 设置员工年龄大于35岁为蓝色 3.7 套用格式 开始样式套用表格格式 编辑设计 表（table）对象与区域,第三章 工作表格式美化设计,案例：企业员工档案管理表 知识点：数据有效性、文本函数、日期函数、逻辑函数、数据输入及格式修饰技巧,第四章 数据数据分析管理,4.1 记录单 数据库：具有相同结构方式存储的数据集合。 如电话簿、公司客户名录、库存帐等。 Excel中，数据库作为一个数据清单存在。 数据库中，信息按纪录存储。纪录由字段组成。同一字段存放相似的信息。,第四章 数据数据分析管理,记录单 在记录单内移动光标

6、：Ctrl+光标； Ctrl+Shift+光标 管理数据库的命令有 排序 筛选 分类汇总 数据透视表 使用函数,第四章 数据数据分析管理,4.2 数据的排序：数据排序 单条件排序 主要关键字 选项 按列排序 笔画 多条件排序 次要关键字 按自定义序列排序 恢复：按编号排序,第四章 数据数据分析管理,4.3 数据的筛选：数据筛选 自动筛选的使用 选定包含字段的记录 数据排序和筛选自动筛选 高级筛选 设置条件区域 数据排序和筛选高级筛选 指定结果位置 恢复 数据排序和筛选清除,第四章 数据数据分析管理,4.4 数据的分类汇总：数据分类汇总，subtotal 组合与分级显示 创建分类汇总 先排序，再

7、汇总 隐藏与显示分类汇总信息 分类汇总（小记、总计、分级显示） 恢复（取消汇总） 分类汇总全部删除 高级分类汇总 嵌套分类汇总,第四章 数据数据分析管理,4.5 汇总函数 Subtotal :分类汇总（自动生成） Count :计数（数字） Counta : 计数（非空单元格） Countif :条件计数 Sumif :条件求和,第四章 数据数据分析管理,4.6 数据透视表：插入数据透视表 数据按不同的组织方式交叉分析 创建数据透视表雏形 设置数据透视表数据 编辑数据透视表 美化数据透视表,第四章 数据数据分析管理,修改数据透视表 查看透视表中数据的具体来源 利用页字段创建分页显示 建立多字段

8、的数据透视表 修改数据透视表的概要函数 修改透视表数据的显示方式 显示数据项的明细数据 透视图表的实例解析 创建数据透视图 制作数据透视图表,第四章 数据数据分析管理,4.7 案例 案例：企业日常费用统计系统 案例：销售统计系统表 知识点 数据有效性、排序、筛选 比较 分类汇总 利用函数实现数据汇总 数据透视进行数据交差分析汇总 4.8 Excel与外部数据 将外部数据导入到Excel工作表 Excel向导工具,第五章 函数与公式,5.1 公式 5.1.1 使用公式 定义 公式是对工作表中的数据进行分析和处理的一个等式。 功用 用于算术运算，逻辑运算，日期、时间的计算等。 公式具备三个条件 以

9、等式“=”开头 含运算符、运算对象 有结果 示例：,第五章 函数与公式,示例： =SUM(A1：A4)-B5 =SUM(A1,a2,a3,A4)-B5 =A1+a2+a3+A4 -B5 =SUM(A1,a2,a3,A4,-B5) 5.1.2底层值与显示值 底层值:编辑栏 显示值:单元格,第六章 函数与公式,5.1.3 运算符及其优先级别 运算符是表示特定运算的符号 4类运算符： 算术运算符：+ - * / （乘幂） 文本运算符：&，比较concatenate函数 比较运算符：返回逻辑值TRUE或FALSE = = 引用运算符：引用运算对象（某个单元格或区域） ：区域运算符 SUM(A1：A4)

10、 ，联合运算符 SUM(A1：A3，B1：B3) （空格）交叉运算符,第六章 函数与公式,运算符优先规则: Excel首先处理括号中的表达式 先乘除后加减 优先级相同,从左至右依次执行 使用”公式求值”：公式公式审核公式求值,第六章 函数与公式,6.1.4 输入公式、引用单元格地址 输入公式： 编辑栏输入 单元格直接输入 引用：指明公式所使用的数据位置（单元格或单元格区域） 内部引用：同一工作表单元格，同一工作薄的不同工作表的单元格 外部引用：不同工作薄的单元格 绝对引用与相对引用：复制公式时 相对引用：例A5,复制和粘贴时,相对引用会自动调整 绝对引用：例$A$5 混合引用：例$A1 引用整

11、行,整列:例4:4,H:H,第六章 函数与公式,6.1.5 编辑公式 选定单元格，单击编辑栏，输入修改内容，单击确定（对勾）按钮（或回车） 复制公式、自动填充公式 移动公式 6.1.6 在公式中使用区域名称 公式定义名称/用于公式,第六章 函数与公式,6.1.7 公式算法监控 公式审核 追踪 求值 检查套用公式的计算结果 *在工作表中单元格(显示值)显示公式 公式公式审核显示公式 *在编辑栏(底层值)中隐藏公式 单元格属性保护隐藏 审阅更改保护工作表 Sheet密码破解：使用宏：差旅费报销单,第六章 函数与公式,6.1.8 公式计算中的常见错误：error.xls,第六章 函数与公式,6.2

12、函数 6.2.1 使用函数 函数概念 Function，fx，函数的本质即公式 函数是预置excel系统内部的公式，直接调用 函数的组成 函数名（参数，参数，参数，） 某些函数没有参数 PI( ) Now( ) Rand( ),第六章 函数与公式,参数类型 数字、文本、逻辑值、数组、单元格的引用、常量、公式或其他函数 表达式 =sum(sin(A1*PI(),2*cos(A2*PI() 数字值 文本值 =text(now(),”mm dd,yyyy”) 逻辑值 =if(A1=true,”Future”,”Past”)&”History” 名称引用 数组 =Transpose(array),第六

13、章 函数与公式,6.2.2 函数的嵌套 例：SUM(3,5,7) 例： IF(AVERAGE(A1：A4)100,1,0) 显示公式与结果 使用流程图 示例：考试分数、计算所得税,第六章 函数与公式,6.2.3 函数类型（按使用范围划分) 财务函数 日期与时间函数 数学与三角函数 统计函数 查找和引用函数 数据库函数 文本函数 逻辑函数 信息函数,第六章 函数与公式,1 常用的查询和引用函数 LOOKUP函数 HLOOKUP函数 VLOOKUP函数 MATCH函数 返回与指定数值匹配的数组中的元素位置 INDEX函数,第六章 函数与公式,2 常用汇总功能函数 COUNT函数 计数 COUNTA

14、函数 计数 COUNTIF函数 条件计数 SUMIF函数 条件求和,第六章 函数与公式,3 逻辑函数的典型应用 IF函数 按条件返回值 AND函数 计算逻辑值的交集 OR函数 计算逻辑值的并集 综合实例 运用逻辑函数判断闰年 使用YEAR函数计算年龄,第六章 函数与公式,4 文本和时间函数的典型应用 LEFT RIGHT和MID 基于指定的字符数，返回文本字符串的第一个或前几个字符 CONCATENATE函数 例： DATE函数计算出生年月 MOD函数判断性别 案例：员工档案管理系统 案例：员工工资管理系统 知识点：工资表构成、查询统计函数查询工资信息、逻辑函数计算所得税、出勤统计方法。,第七

15、章 输出及选项设置,7.1 页面设置 设置页面 设置页边距 设置页眉和页脚 设置工作表,第七章 输出及选项设置,打印 设置打印区域 Office按钮打印区域 视图工作薄视图分页预览 超大表格 锁定表头打印 页面布局页面设置工作表打印标题 屏幕显示设置 视图窗口拆分 视图窗口冻结窗格,第七章 输出及选项设置,7.2 Excel选项设置 工具选项 显示网格线/公式 自定义列表 新工作表数 文件保存位置 自动恢复文件位置 安全性 Enter键方向,第七章 输出及选项设置,7.3 数据保护 设置工作表密码 审阅更改 指定可编辑区域 隐藏公式 设置工作簿密码 文件保存工具常规选项,第七章 输出及选项设置,*解密工作表密码:使用宏 *解密工作簿密码:下载小软件 案例：差旅费报销单 知识点：文本函数、逻辑函数、数据保护、公式隐藏、宏应用。,第八章 使用宏,8.1 VBA与宏 Visual Basic for Application Macro 8.2 录制宏 录制操作 查看宏代码 宏与按钮的