Excel使用技巧提升培训.ppt

Excel使用技巧提升培训 段 勇 Copyright 2007 by HuaAT ConfidentialCopyright 2006 by HuaAT Page 2 Excel能做什么？比我们想象的更多 图 表VBA工具 函数 ConfidentialCopyright 2006 by HuaAT Page 3 看看自己处于哪个阶段: 新人 初级用户 中级用户 高级用户 专家 n掌握Excel 的基本操作方法和常用功能，如输入数据，查找 替换，设置单元格格式，排序、筛选和保存工作簿 等。 n经常在工作中使用Excel,如绘制一些表格，画一张图表，使 用简单的函数,知道Excel比较强大，但是还不知道怎么用。 n1）熟练使用各个Excel 菜单命令；2）熟练使用数据透视表 ；3）至少掌握20 个常用函数以及函数的嵌套运用。 nSUM 、IF 、VLOOKUP 、INDEX 、MATCH等 n1）熟练运用数组公式，用花括号包围起来的，必须用 组合键才能完成录入的公式； n2）能够利用VBA 编写不是特别复杂的自定义函数或过程。 n具备高级用户的Excel技能，能结合自身的行业开发不同的 Excel应用的高手才能称为专家。 ConfidentialCopyright 2006 by HuaAT Page 4 如何提高Excel技能 1、积极的心态兴趣、好学、思考。 2、循序渐进的学习从新手初级用户中级用户高级用户专家 3、善用资源，学以致用利用一切能利用的资源，学Excel是为了解决问题 ConfidentialCopyright 2006 by HuaAT Page 5 是否只有学会VBA才能成为Excel高手？ n尽量使用Excel函数来实现你想要实现的需求,迫不得已才使用VBA！ n函数是训练思维的利器 ConfidentialCopyright 2006 by HuaAT Page 6 今天交流的主要内容 实例一：他山之石可以攻玉Excel打开文本文件 实例二：众里寻它千百度Excel筛选功能 实例三：透过现象看本质Excel数据透视表功能 实例四：以静制动、以逸待劳Excel宏功能 实例五：以不变应万变Excel函数 实例六：另辟蹊径Excel快捷操作方式 ConfidentialCopyright 2006 by HuaAT Page 7 实例一：他山之石可以攻玉用Excel打开文本文件 ConfidentialCopyright 2006 by HuaAT Page 8 实例二：众里寻它千百度Excel筛选功能 基本筛选 n选择筛选的值 n自定义筛选条件 n“包含”运算符的使用 n通配符*的使用 高级筛选 n选择不重复的纪录 n有条件筛选：筛选存在条件区域中的纪录 ConfidentialCopyright 2006 by HuaAT Page 9 实例三：透过现象看本质Excel数据透视表功能 透视表 n数据汇总统计：如按品牌汇总、按地市汇总等 n自定义的单维、二维或者多维的报表 透视图 n把数据表格转换成图形表达 ConfidentialCopyright 2006 by HuaAT Page 10 实例三：透过现象看本质Excel数据透视表功能 ConfidentialCopyright 2006 by HuaAT Page 11 实例四：以静制动、以逸待劳Excel宏功能（VBA） 宏的主要功能 把一系列菜单操作转化为代码（VBA），运行宏代码即可实现菜单操作 对每一个工作簿/工作表均可执行已定义好的宏，实现期望的操作 如果对大量的工作簿/工作表有重复性的操作，可用宏来代替，一劳永逸 ConfidentialCopyright 2006 by HuaAT Page 12 实例五：以不变应万变Excel函数 第一招：大海捞针Vlookup/Hlookup/Index函数 第二招：左右逢源IF函数 第三招：投石问路IS函数 第四招：留头去尾Left函数 第五招：去头留尾Right函数 第六招：掐头去尾MID函数 第七招：瞎子摸象Find函数 第八招：鲁班神尺 Len函数 第九招：诸葛神算Countif、Sumif、sum()、sumproduct函数 ConfidentialCopyright 2006 by HuaAT Page 13 第一招：大海捞针Vlookup/Hlookup/Index函数 VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) Lookup_value ：需要查找的值 Table_array ：需要在其中查找数据的数据表。 Col_index_num ： table_array 中待返回的匹配值的列序号。 Range_lookup ：逻辑值，指明函数 VLOOKUP 返回时是精确匹配还是近似匹配。 主要功能：数据关联 Index(array,row_number) Array 为单元格区域或数组常量 Row_numbser：为要查找值的位置（行数） ConfidentialCopyright 2006 by HuaAT Page 14 第二招：左右逢源If函数 IF(logical_test,value_if_true,value_if_false) Logical_test 表示计算结果为 TRUE 或 FALSE 的任意值或表达式。例如，A1=60 就是 一个逻辑表达式 Value_if_true logical_test 为 TRUE 时返回的值。 Value_if_false logical_test 为 FALSE 时返回的值。 主要功能：根据不同的条件执行相应的指令 ConfidentialCopyright 2006 by HuaAT Page 15 第三招：投石问路IS函数 ISBLANK(value) ISERR(value) ISERROR(value) ISLOGICAL(value) ISNA(value) ISNONTEXT(value) ISNUMBER(value) ISREF(value) ISTEXT(value) 主要功能：判断函数，返回结果只有两种：是、非 ConfidentialCopyright 2006 by HuaAT Page 16 第四招：留头去尾Left函数 LEFT(text,num_chars) Text 是包含要提取字符的文本字符串，可以直接输入含有目标文字的单元格名称。 Num_chars 指定要由 LEFT 所提取的字符数。Num_chars 必须大于或等于 0，如果 num_chars 大于文本长度，则 LEFT 返回所有文本，如果省略 num_chars，则假定其 为 1。 主要功能：对原始数据进行截取 ConfidentialCopyright 2006 by HuaAT Page 17 第五招：去头留尾Right函数 RIGHT(text,num_chars) Text 是包含要提取字符的文本字符串，可以直接输入含有目标文字的单元格名称。 Num_chars 指定要由 Right所提取的字符数。Num_chars 必须大于或等于 0，如果 num_chars 大于文本长度，则 Right返回所有文本，如果省略 num_chars，则假定其为 1。 主要功能：对原始数据进行截取 ConfidentialCopyright 2006 by HuaAT Page 18 第六招：掐头去尾MID函数 MID(text,start_num,num_chars) Start_num 是文本中要提取的第一个字符的位置。文本中第一个字符的 start_num 为 1 ，以此类推。 Num_chars 指定希望 MID 从文本中返回字符的个数 主要功能：对原始数据进行截取 ConfidentialCopyright 2006 by HuaAT Page 19 第七招：瞎子摸象Find函数 FIND(find_text,within_text,start_num) Find_text 是要查找的文本。 Within_text 是包含要查找文本的文本。 Start_num 指定开始进行查找的字符。within_text 中的首字符是编号为 1 的字符。如果 忽略 start_num，则假设其为 1。 主要功能：对原始数据中某个字符串进行定位，以确定其位置。 ConfidentialCopyright 2006 by HuaAT Page 20 第八招：鲁班神尺 Len函数 LEN(text) Text 是要查找其长度的文本。空格将作为字符进行计数。可以直接输入含有目标文字的 单元格名称。 主要功能：对单元格内容的长度进行测量，得其长度。 ConfidentialCopyright 2006 by HuaAT Page 21 第九招：诸葛神算Countif、Sumif、sum()、sumproduct函数 Countif(“单元格区域”,条件) Sumif(“单元格区域”,条件） Sum(“单元格区域”）* (“单元格区域”） Sumproduct(“单元格区域”， “单元格区域”） ConfidentialCopyright 2006 by HuaAT Page 22 Case1：关于有重复值的记录处理高级筛选、数据透视表、Countif应用。 IDVar1Var2 112AA 114BB 215AA 218BB 320AA 需求一：只需要知道不重复的ID有多少个？ 高级筛选 数据透视表 Countif+筛选 需求二：对于ID重复的记录只保留Var2=“AA”的记录 Countif+筛选 需求三：对于ID重复的记录重新对ID编号，如变成1.11.22.12.2 Countif+”&” Countif ConfidentialCopyright 2006 by HuaAT Page 23 Case2：数据汇总与统计CountifSumif数据透视表应用 电话号码品牌分公司ARPU 135全球通朝阳325 136神州行海淀55 137动感地带东城86 138全球通西城159 139神州行海淀56 需求一：分别统计每个品牌、每个分公司的用户数。 Countif Sumif 需求二：统计品牌与分公司组合的用户数。 辅助列 &连接符 CountifSumif ConfidentialCopyright 2006 by HuaAT Page 24 Case3：用窗体制作动态表格微调框、函数的组合应用 ConfidentialCopyright 2006 by HuaAT Page 25 实例六：另辟蹊径Excel快捷操作方式 n单元格相对引用与绝对引用 B3$B$3 选择B3，按F4键 n快速选择单元格 选中一个单元格，向下拖动 选中起始单元格，按住Shift，再选中结束单元格 选中起始单元格，同时按住ctrl+shift+方向键 n快速填充单元格 选中单元格，复制 双击单元格右下脚的“小黑点（出现“+”号）” nAlt键的灵活使用 通过Alt键来调出相应的菜单，而不是用鼠标。如Alt+D调出“数据”菜单，再 按D调出自动筛选菜单。 华院分析技术（上海）有限公司