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