让你的表格飞起来.ppt

1 让你的表格飞起来 ExcelTraining产品管理部 杨庆 2 Excel2007基本界面 3 让你的表格飞起来 选择性粘贴相对地址和绝对地址数据透视表一些常用的函数绘图技巧简单的数据分析工具打印表格的小技巧 4 选择性粘贴 5 选择性粘贴 我们手头有这样一个数据表 行表示学生姓名 列表示学生的各科成绩 如果我们想得到一个以行表示各科成绩 以列表示学生姓名的数据表 应该怎样做呢 其实很简单 EXCEL为我们提供了转置功能 先选中要进行转换的数据区A1 D10 按下Ctrl C将其复制 再将光标定位在另一个工作表的A1单元格中 然后选择 编辑 菜单中的 选择性粘贴 命令 在 选择性粘贴 对话框中 选中 转置 复选框 单击 确定 6 选择性粘贴 男筛选 7 选择性粘贴 当你在对一组数据进行了筛选的功能 并想将筛选好的数据copy到另一页上 但不想将不符合筛选条件的也同时copy过去时 可以试试选择性粘贴中的跳空项 先选中要进行转换的数据区 按下Ctrl C将其复制 再将光标定位在另一个工作表的A1单元格中 然后选择 编辑 菜单中的 选择性粘贴 命令 在 选择性粘贴 对话框中 选中 跳空 复选框 单击 确定 8 选择性粘贴 如何快速地批量修改数据 假如有一份Excel工作簿 里面有所有职工工资表 现在想将所有职工的补贴增加50 元 当然你可以用公式进行计算 但除此之外还有更简单的批量修改的方法 即使用 选择性粘贴 功能 首先在某个空白单元格中输入50 选定此单元格 选择 编辑 复制 选取想修改的单元格区域 例如从E2到E150 然后选择 编辑 选择性粘贴 在 选择性粘贴 对话框 运算 栏中选中 加 运算 按 确定 健即可 最后 要删除开始时在某个空白单元格中输入的50 9 相对地址和绝对地址 Excel一般使用相对地址来引用单元格的位置 当把一个含有单元格地址的公式拷贝到一个新的位置 公式中的单元格地址会随着改变 你可以在列号或行号前添加符号 来冻结单元格地址 使之在拷贝时保持固定不变 10 数据透视表 如果你有一组销售人员的业绩数据 其中人员和他们每个月 甚至每天的业绩的排列都没有规律 怎样最快地统计出1 12月每个人的业绩呢 你可以通过数据透视表实现 点击数据表中的任何一个单元格 选 数据 数据透视表和数据透视图 并选中 下一步 确定你要透视的数据表的范围 再选中 下一步 选 布局 11 数据透视表 在右边列出的字段按钮拖放到左边的布局图上就可以构成数据透视表了 我们选定了行为Month 列列出Salesman的名字 这样如下的表格构成了 数据透视表可供选择的数据计算方式有求和 平均值 最大值 计数等等 12 数据透视表 已经生成的数据透视表 如果原始数据更改 只要在点击了数据透视表 右边出现了数据透视表的工具栏 点击 更新 按键 即可 生成的数据透视表也可以任意通过从工具栏上拖放字段 或将不需要显示的字段隐藏来更改 如果要将数值的计算方式从求和更改为均值或其他 只要双击左上角的 求和 在弹出的选择表中选择所需要的计算方式就可以了 13 函数 Excel函数一共有11类 分别是数据库函数 日期与时间函数 工程函数 财务函数 信息函数 逻辑函数 查询和引用函数 数学和三角函数 统计函数 文本函数以及用户自定义函数 如果你对需要使用的Excel函数的用法不熟悉 可以点击Toolbar上的fx按钮 弹出的提示框 会提示你具体的用法 14 基础数学函数 15 基础数学函数的应用 16 If函数 17 If函数 我们知道 利用If函数 可以实现按照条件显示 一个常用的例子 就是教师在统计学生成绩时 希望输入60以下的分数时 能显示为 不及格 反之显示为 及格 假设成绩在A2单元格中 判断结果在A3单元格中 那么在A3单元格中输入公式 if A20 SUM A1 A5 0 此式就利用了嵌套函数 意思是 当A1至A5的和大于0时 返回这个值 如果小于0 那么就返回0 还有一点要提醒你注意 以上的符号均为半角 而且IF与括号之间也不能有空格 18 Index函数 INDEX用于返回表格或区域中的数值或对数值的引用 INDEX array row num column num 返回数组中指定单元格或单元格数组的数值 Array为单元格区域或数组常数 Row num为数组中某行的行序号 函数从该行返回数值 Column num为数组中某列的列序号 函数从该列返回数值 需注意的是Row num和column num必须指向array中的某一单元格 否则 函数INDEX返回错误值 REF 比如要返回韦小宝的出现剧作 如果你知道韦小宝在这个数据表中是第三行 出现剧作的是第四列 可以用这样的语句实现 index A1 D7 3 4 19 Lookup函数 LOOKUP函数可以返回向量 单行区域或单列区域 或数组中的数值 此系列函数用于在表格或数值数组的首行查找指定的数值 并由此返回表格或数组当前列中指定行处的数值 当比较值位于数据表的首行 并且要查找下面给定行中的数据时 使用函数HLOOKUP 当比较值位于要进行数据查找的左边一列时 使用函数VLOOKUP 其基本语法形式为HLOOKUP lookup value table array row index num range lookup VLOOKUP lookup value table array col index num range lookup 比如要返回韦小宝的出现剧作 但你并不知道韦小宝在这个数据表中是第几行 但你知道 出现剧作 的是第四列 可以用这样的语句实现 vlookup 韦小宝 A1 D7 4 false 20 巧妙地使用Lookup函数和命名区域 在Vlookup或Hlookup语句中有一些小技巧可以提高语句的准确性和易用性 查找的首行名称可以不用 韦小宝 这样的输入字段 而可以将 韦小宝 放入某一个单元格 这里放入了A11 这样 VLookup公式可以用Vlookup A11 A1 D7 4 false 代替原来的Vlookup 韦小宝 A1 D7 4 false 使之更方便 如果用已经命名好的Range代替原来Vlookup中的A1 D7的数组说明 也可以更快捷 例如 我们定义了A1 D7这个区域的名称叫金庸 这样 原来的vlookup函数可以这样写 vlookup A11 金庸 4 false 命名一个区域的名称可以有多种方法 最直接的方法就是 把要命名的区域选中 直接在ExcelToolbar的左上方的名称框中键入金庸 即可 21 数据库函数 在MicrosoftExcel中包含了一些工作表函数 它们用于对存储在数据清单或数据库中的数据进行分析 这些函数统称为数据库函数Dfunctions 这一类函数具有一些共同特点 1 每个函数均有三个参数 database field和criteria 这些参数指向函数所使用的工作表区域 2 除了GETPIVOTDATA函数之外 其余十二个函数都以字母D开头 3 如果将字母D去掉 可以发现其实大多数数据库函数已经在Excel的其他类型函数中出现过了 比如 DAVERAGE将D去掉的话 就是求平均值的函数AVERAGE 22 数据库函数 Database为构成数据清单或数据库的单元格区域 数据库是包含一组相关数据的数据清单 其中包含相关信息的行为记录 而包含数据的列为字段 数据清单的第一行包含着每一列的标志项 Field为指定函数所使用的数据列 数据清单中的数据列必须在第一行具有标志项 Field可以是文本 即两端带引号的标志项 如 使用年数 或 产量 此外 Field也可以是代表数据清单中数据列位置的数字 1表示第一列 2表示第二列 等等 Criteria为一组包含给定条件的单元格区域 可以为参数criteria指定任意区域 只要它至少包含一个列标志和列标志下方用于设定条件的单元格 23 数据库函数 1 例 某果园的果树的高度 使用年数 产量与利润的统计数据表如图所示 希望求出1 有多少种苹果树的树高在10 16英尺之间2 苹果树与梨树的最大利润值是多少3 高度大于10英尺的苹果树的最小利润是多少4 苹果树的总利润5 高度大于10英尺的苹果树的平均产量6 果园中所有树种的平均使用年数7 求苹果树和梨树产量的估算标准偏差 真实标准偏差 估算方差 真实方差 24 数据库函数 由于第一问需要求解有多少种苹果树的树高在10 16英尺之间 因此建立查询条件高度在10与16之间利用函数DCOUNT求解满足条件的单元格数来计算高度在10 16英尺的苹果树的种类 公式为 DCOUNT A4 E10 使用年数 A1 F2 求出有一种苹果树满足条件 25 数据库函数 由于第二问为求苹果树与梨树的最大利润值 因此再建立一个查询条件 梨树 如图 利用函数DMAX求出苹果树与梨树的最大利润 同理可以利用函数DMIN求出高度大于10英尺苹果树的最小利润 具体公式为 DMAX A4 E10 利润 A1 A3 求出苹果树与梨树的最大利润为105 DMIN A4 E10 利润 A1 B2 求出高度大于10英尺苹果树的最小利润为75 26 数据库函数 27 常用文本函数 28 常用文本函数 29 日期函数 30 用图表说话 通常 一个销售人员在表现他的客户数的增加和销售额的增加时 会用两张不同的图表 其实 将这两组紧密相关的数据表达在同一张图上的效果可能更好 但是由于客户数和销售额的数量出现在同一张图上数值差距很大 出现下面的图 31 用图表说话 32 用图表说话 33 用图表说话 34 运用图表巧妙求回归 案例 某分析人员想了解各城市人口和可乐销售的关系 他没有选用复杂的函数 而是用图表得出了简单的 1 先用城市人口作Xz轴 可乐销售数量作为Y轴画出一张XY散点图2 点击图形中的数据组 右键 点击 添加趋势线 3 在选项中选 显示公式 显示R平方值 35 打印表格的小技巧 用Excel做多页的表格时 怎样像Word的表格那样做一个标题 即每页的第一行 或几行 是一样的 但不是用页眉来完成 在EXCEL的文件菜单 页面设置 工作表 打印标题 可进行顶端或左端标题设置 通过按下折叠对话框按钮后 用鼠标划定范围即可 这样Excel就会自动在各页上加上你划定的部分作为表头 页面布局 工作表选项 36 一些其他技巧 尽可能多地使用快捷键 例如Ctrl C Copy Ctrl X Cut Ctrl V Paste Ctrl P Print Ctrl S Save Ctrl F Find Ctrl H FindCtrl 数字用百分比格式显示 F2为修改单元格内容