《EXCEL常用技巧》PPT课件.ppt

,ECXEL常用技巧,目 录,筛选重复值,关联查找,数据透视表,工单明细编辑,其他小技巧,直接取不重复的唯一记录（1）,选中单元格区域，单击菜单“数据”-“筛选”-“高级筛选”，在方式中选择“将筛选结果复制到其他位置”，保持条件区域为空，在“复制到”文本框中选取C1单元格，并勾选“选择不重复的记录” ，得到的结果如图：,直接取不重复的唯一记录（2）,加辅助列，运用函数countif($A$2：A2，A2)。这个公式在每一个值第1次出现的时候返回1，第2次出现的时候返回2，以此类推 ！,筛选重复记录（1）,MODE函数：返回在某一数组或数据区域中的众数(出现频率最高的数) 。缺点是只能一个一个号码来鉴别是否有重复的号码，而且必须先处理了前面的重复号码，才会显示后面的重复号码。优点是当数据量极大，但重复号码极少时，或者你仅仅需要验证一下这组号码是否有重复号码时，这个函数的运算速度很快 。,筛选重复记录（2）,加辅助列，运用函数countif(A：A，A2)。这个公式直接返回每个值在范围内出现的总次数。,限制输入重复数据,选定A列，单击菜单-数据-有效性-设置，在“允许”下拉列表选择“自定义”选项，在“公式”文本框中输入“=countif(a:a,a1)=1”，如图 ：,双条件筛选重复值,用2个辅助列：先用连接符“&”将两个数据连接在一起，再用COUNTIF函数，如图结果不等于1的均是重复记录。,目 录,筛选重复值,关联查找,数据透视表,工单明细编辑,其他小技巧,VLOOKUP函数,最常用的查找函数，在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值 ，语法为： VLOOKUP(lookup_value，table_array，col_index_num，range_lookup) Lookup_value为需要在数据表第一列中查找的数值，它可以是数值、引用或文字串 Table_array 为需要在其中查找数据的数据表，可以使用对区域或区域名称的引用。 Col_index_num 为table_array 中待返回的匹配值的列序号。 Range_lookup为TRUE 或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于lookup_value 的最大数值；如果为FALSE， 函数将返回精确匹配值。如果找不到，则返回错误值#N/A。 查询条件必须与查询范围首列的数字格式保持一致，否则不能正确地返回结果。 当存在多条满足条件的记录时，函数只能返回第一个满足条件的记录。,VLOOKUP函数,目 录,筛选重复值,关联查找,数据透视表,工单明细编辑,其他小技巧,数据透视表,（1）在菜单栏上选择“数据数据透视表和数据透视图”选项，弹出“数据透视表和数据透视图向导”对话框。 （2）指定待分析数据的数据源类型和需创建的报表类型，单击“下一步”。 （3）选择要建立数据透视表的数据源区域，单击“下一步”按钮。 （4）单击“新建工作表”单选项，再单击“完成”。（5）根据需要用鼠标分别将“数据透视表”工具栏中的字段按钮拖放到透视表的相应位置。,建立数据透视表,数据透视表,数据透视表,数据透视表,数据透视表,数据透视表,数据透视表,练习,1、拖放字段：可以随意拖动、拖出 2、页视图：分类 3、重命名字段标题 4、更改排序方法 5、刷新数据,1、更改汇总方法 2、显示明细数据 3、同一拖放区使用2个字段 4、合并标志 5、格式化报告,目 录,筛选重复值,关联查找,数据透视表,工单明细编辑,其他小技巧,工单明细编辑,第一步操作：将空文本改为空单元格。 第二步操作：选中A1：A18区域，按CTRL+G键，弹出定位对话框，选择定位条件为空值,工单明细编辑,选中所有空白单元格后，输入=a2，按住CTRL键，再按回车键。,工单明细编辑,目 录,筛选重复值,关联查找,数据透视表,工单明细编辑,其他小技巧,（1）号码分类,先将需要的号码标上底色。 选择单元格A1，插入-名称-定义，例如定义名称为x，引用位置=GET.CELL(38,sheet1!$A1),（1）号码分类,在B列加辅助列，=x,（2）MID函数,返回文本串中从指定位置开始的特定数目的字符。 语法：MID(text，start_num， num_chars) Text 是包含要提取字符的文本串。 Start_num 是文本中要提取的第一个字符的位置， Num_chars指定希望MID 从文本中返回字符的个数,（3）稽核身份证号码,Lenb(text): LENB 返回文本串中所有字符的字节数。Text待要查找其长度的文本，包括空格。,（4）选择性粘贴的妙用,1、转置：行转列，列转行，行列合并 2、所有数据同时乘以同一个数 3、取消工作表中的超链接 4、防止空单元格覆盖,（5）其他小技巧,1、迅速交换两列数据：shift键 2、大量15位号码以科学计数法显示：分列 3