麦肯锡EXCEL培训课件

你
格
选择数据 选择数据区 ，
筛选： 选择数据：筛选：
更
动筛选
• &/ 个项 可 仅仅显 具 筛选 • 选择 个标 （ 动筛选 可 选3个）可 个 • ºb 加你 击 义来 加你 筛选标
练习
• 课堂布置
8
8. IF 函数
需要了解 之原因
Leabharlann Baidu
•所有工作表中应用条件对照 •了解如何结合其它函数连锁式使用IF ，将会节省时间 了解如何结合其它函数连锁式使用IF
Excel 小贴士
2006.11.15
1. 拆分窗口并冻结窗格
需要了解 之原因
• 拆分窗口，可以同时操作一个大工作表的若干部分 拆分窗口， • 冻结窗格，可以始终保存此工作表的某个部分（例如，列位 冻结窗格，可以始终保存此工作表的某个部分（例如，
或行标） 或行标）
如何应用 此特征
• 拖拆分的水平及竖直图标至所期望位置 • 从工具列中点击冻结窗格图标，以冻结窗格 从工具列中点击冻结窗格图标，
group/ungroup这项功能，因为这样你可以更加容易的在隐藏 这项功能， 这项功能 /取消隐藏之间转换 取消隐藏之间转换
如何应用 此特征
• 选中你所需要暂时“隐藏”的行或者列 选中你所需要暂时“隐藏” • 选择 Data: Group and Outline: Group • 通过出现的“-”和“+”号来选择显示或者不显示你所选择的行 通过出现的“ 和 号来选择显示或者不显示你所选择的行
12
12. COUNT函数 函数
为什么要 了解这个
• 避免您浪费时间去数数 避免因为失误得出不准确的结果 避免您浪费时间去数数, 避免因为失误得出不准确的结果.
如何使用 这个功能
• COUNT(范围 范围 值1,...) ==> 计算含有定义值的单元格数 范围1,范围 范围 范围2,值
目 • COUNT(范围 范围 值1,...) ==> 计算非空单元格的数目 范围1,范围 范围 范围2,值 • COUNTBLANK(范围 ==> 在指定范围内计算空单元格的数目 范围) 范围 • COUNTIF(< 围,”标 ”) ==> 在指定范围内计算含有定义标准 << < 标 的单元格数目. 的单元格数目 • : “” 该 号 须 标识标
冻结窗格图标 拆分屏幕图标
• 拆分屏幕，那么： 拆分屏幕，那么：
练习 – 带有列标志行显现于窗格顶端 – 带有储备名称的列显现于窗格左边 • 冻结窗格
1
2. 隐藏及公开命令
需要了解 之原因
• 可以隐藏或公开特殊行及列
– 该工作表操作简单化 – 防止某些信息被查看
如何应用 此特征
• 选择将要隐藏 显示的行或列 选择将要隐藏/显示的行或列 • 选择格式：行：隐藏/显示或格式： 选择格式： 隐藏 显示或格式： 显示或格式 • 列：隐藏 显示 隐藏/显示
如何使用 这个功能
• ROUND(数字 位数 ==> 将数字四舍五入到指定的位数 数字,位数 数字 位数)
– 如果位数 = 0, 则四舍五入到最接近的整数 – 如果位数 0, 则四舍五入到指定的小数位 如果位数> – 如果位数 0, 则在小数点左侧进行四舍五入 如果位数< • ROUNDDOWN(数字 位数 和ROUNDUP(数字 位数 与 数字,位数 数字,位数 数字 位数)和 数字 位数) ROUND函数功能相似 只是取舍的方向依据不同需要而定 函数功能相似, 函数功能相似
练习
• 试一下吧
10
10. SUM 及 SUMIF 函数
需要了解 之原因
• 实际上 实际上SUM用于所有工作表 用于所有工作表 • 如果清楚如何使用该函数，大部分工作表中的 如果清楚如何使用该函数，大部分工作表中的SUMIF函数可 函数可
以节省很多时间 • SUM(第一列，第二列，值1 ,…) 第一列， 第一列 第二列， • SUMIF(列，“对比”，列总和） 对比” 列总和） 列 – 如果未确定列总和，SUMIF 将单元格相加，满足指定列中 如果未确定列总和， 将单元格相加， 的对比标准 –如果确定一个列总和， SUMIF 在相应单元格满足对比标准 如果确定一个列总和， 如果确定一个列总和 的列总数中将单元格相加 •注：“”符号必须用于对比值 “”符号必须用于对比值
如何应用 此特征
• 如果有标题行，指出将不会包含在种类中的部分 如果有标题行， • 选择选项，以使用自定义清单（首先创建，如下） 选择选项，以使用自定义清单（首先创建，如下）
•选择工具/选项/自定义清单，来创建具体分类顺序等等。 选择工具/选项/自定义清单，来创建具体分类顺序等等。 –代替字母顺序，根据日历顺序将月数及周日数分类 代替字母顺序， –以具体顺序重新整理清单（例如高/中/低录入） 以具体顺序重新整理清单（例如高/ 低录入）
练习
• 课堂布置
2
3. GROUP/UNGROUP 部分工作表
需要了解 之原因
• 你需要经常使用隐藏 取消隐藏一些复杂的工作表的部分吗？ 你需要经常使用隐藏/取消隐藏一些复杂的工作表的部分吗 取消隐藏一些复杂的工作表的部分吗？ • 如果你的答案是肯定的，那么你一定非常需要使用 如果你的答案是肯定的，
间的单元格
练习
• Select all cells with data using the Ctrl, Shift, and 箭头 使
用Ctrl, Shift及 箭头 键选择所有数据单元格 及
4
5. 命名单元格/列 命名单元格/
需要了解 之原因
• 可以通过名称查阅具体单元格或单元格范围 • 可以写入等式，例如 数量×费用，替代 可以写入等式，例如=数量 费用，替代=$B$12*$C$4 数量×
格的完整性） 格的完整性） •使用多个标准，点击数据表格的任何单元格，选择数据…分类 使用多个标准，点击数据表格的任何单元格，选择数据… •将选择数据表
如何应用 此特征
可以分为3 可以分为3类，使用下拉清单来选择 字段，确定A 字段，确定A-Z或Z-A
6
6. 分类命令（续） SORT COMMAND (CONTINUED)
练习
• 计算数据表中 计算数据表中KFC店铺的数目 店铺的数目
13
13. ROUND, ROUNDUP和 ROUNDDOWN 函数 和
为什么要 了解这个
• 很多情况下 在计算的过程中你需要了解的是整数部分 而不是 很多情况下, 在计算的过程中你需要了解的是整数部分,而不是
小数(例如 银行的分支有多少,该数字不可能为 小数 例如.,银行的分支有多少 该数字不可能为536.235) 例如 银行的分支有多少 该数字不可能为
如何应用 此特征
练习
课堂布置
11
11. SUMPRODUCT 函数
需要了解 之原因
• 如果需要将两个列位相乘并得出乘积之和， sumproduct 轻松 如果需要将两个列位相乘并得出乘积之和，
解决
如何应用 此特征
• 插入 插入=sumproduct（第一列，第二列 ） （第一列，
练习
• 将两列或行相乘，并得出乘积之和 将两列或行相乘，
或者列
练习
• 课堂布置
3
4. 使用 使用Ctrl, Shift, 及 箭头 键围绕工作表移动 ,
需要了解 之原因
• 省时 • 无需滚动鼠标就可以移动邻近数据块的首位或末尾单元格
如何应用 此特征
• Ctrl-箭头 : 沿箭头方向移至首个/末尾数据单元格 Ctrl-箭头 沿箭头方向移至首个/ • Ctrl-Shift-箭头 : 选择当前单元格与首个/末尾数据单元格之 选择当前单元格与首个/ 箭头
练习
• 按自己意愿使用标志创建分类清单
7
7. 自动筛选指令 (AUTOFILTER COMMAND)
为什么要 了解这个
• 你有一大堆的数据又希望找到一些特定的信息，比如所有符合 你有一大堆的数据又希望找到一些特定的信息，
标准的数据集或前10位项目等 标准的数据集或前 位项目等
如何使用 这个功能
•b
如何应用 此特征
• IF(对比,IF(Comparison,TrueAction,FalseAction) 对比, 对比 • IF(对比,TrueAction,) ==> 如果条件为绝对值IF单元格显示0 对比, 如果条件为绝对值IF单元格显示 对比 IF单元格显示 • IF(对比,TrueAction,””) ==> 如果条件为绝对值IF单元格显示 对比, 如果条件为绝对值IF IF单元格显示 对比
如何应用 此特征
•选择单元格或单元格列 •选择插入：名称:定义于菜单列 选择插入：名称:
练习
•定义单元格A2: A125 作为“序列” 定义单元格A2: 作为“序列”
5
6.分类命令 SORT COMMAND 分类命令
需要了解 之原因
• 对一系列行或列进行正确分类 • 对于许多建模工作，未剥离数据是关键 对于许多建模工作， •按单一种类分类，仅点击列，永远不要突出显示列（将毁坏表 按单一种类分类，仅点击列，永远不要突出显示列（
– 在包括多种信息的电子数据表中搜索部分数据 例如 在一个 在包括多种信息的电子数据表中搜索部分数据(例如 例如:在一个 小型收据库中) 小型收据库中 – 需要进行某种计算 需要搜索相关的数据值 需要进行某种计算,需要搜索相关的数据值
16
15. VLOOKUP 和HLOOKUP函数 (继续 继续) 函数 继续
练习
• 课堂布置
14
14. 条件格式
为什么要 了解这个
• 有时候你想不同的单元格内容运用不同的颜色，比如负值用红 有时候你想不同的单元格内容运用不同的颜色，
色，正值用黑色，或加一个边框等等 正值用黑色，
如何使用 这个功能
• 选择 关 区 ，选择格 ：条件格 • 选择格 选择格 改变 标 并调 格 . 实际 你可 改变 • b 击 加来选择 bb
如何使用 这个功能
• VLOOKUP 和HLOOKUP 可以帮助您在一个较大的数据范围内搜索到指
定的单元格数据 – 当每行包含一个独立的记录 与此记录相关的单元格包含了与该记录相 当每行包含一个独立的记录, 关的数据,此时可以用 此时可以用VLOOKUP 关的数据 此时可以用 – 当每列包含一个独立的记录,此时可以用 当每列包含一个独立的记录 此时可以用VLOOKUP 此时可以用 • VLOOKUP(搜索值 范围 列数 错误值 ==> 设定搜索值和列数 搜索指 搜索值,范围 列数,错误值 设定搜索值和列数, 搜索值 范围,列数 错误值) 定行的值 – }¢ 值 “ 适 钥 ” (如: 搜索与第一列中搜索值相关的行的数 如 据 – 范围指包含数据的单元格范围 – 列数指包含您所需要的相关数据的列数 – 当EXCEL不能搜索到您需要的数据时显示错误值 输入 不能搜索到您需要的数据时显示错误值. 不能搜索到您需要的数据时显示错误值 输入FALSE代表您 代表您 想搜索精确匹配值,但如果没有搜索到 但如果没有搜索到, 将显示#N/A. 输入 想搜索精确匹配值 但如果没有搜索到 EXCEL将显示 将显示 TRUE 代表您想搜索近似匹配值 EXCEL将显示与搜索值近似的略小 代表您想搜索近似匹配值, 将显示与搜索值近似的略小 的数值. 的数值 • HLOOKUP(搜索值 范围 列数 错误值 ==> 设定搜索值和行数 搜索指 搜索值,范围 列数,错误值 设定搜索值和行数, 搜索值 范围,列数 错误值) 定列的值
格 标
、边
颜
练习
• 定义一个单元格的格式，如果是负值就使用红色字体和蓝色背 定义一个单元格的格式，
景，如果值大于10就用粗体和粗框线 如果值大于 就用粗体和粗框线
15
15. VLOOKUP 和HLOOKUP函数 函数
为什么要 了解这个
• 在较大的数据表中 可以为您自动搜索指定的单元格数据 在较大的数据表中,可以为您自动搜索指定的单元格数据
为空
•创建一个“汉城”变量 创建一个“汉城”
练习 –如果存于汉城内为1 如果存于汉城内为1 如果存于汉城内为 –如果存于其它地方为 如果存于其它地方为0 如果存于其它地方为
9
9. 同时编辑多个工作表
为什么要 了解这个
• 避免在一个工作簿里面的多个工作表重复同样的工作
如何使用 这个功能
• 选择第一个要编辑的工作表 • 按住 键并点击其他的工作表 按住Ctrl键并点击其他的工作表 • 开始编辑
注: 当用VLOOKUP时,应选择第一列的值作为搜索值,当用HLOOKUP时 应选择第一行的值作为搜索值
17
15. VLOOKUP 和 HLOOKUP函数(继续 继续) 函数 继续