EXCEL几个常用技巧.docx
- 文档编号:4434435
- 上传时间:2022-12-01
- 格式:DOCX
- 页数:8
- 大小:20.82KB
EXCEL几个常用技巧.docx
《EXCEL几个常用技巧.docx》由会员分享,可在线阅读,更多相关《EXCEL几个常用技巧.docx(8页珍藏版)》请在冰豆网上搜索。
EXCEL几个常用技巧
EXCEL的几个常用技巧
一、Excel如何合并两个单元格内容1
技巧一:
用连字符“&”来合并单元格内容1
技巧二:
利用CONCATENATE函数2
二、Excel中的数据筛选和高级筛选3
1、合并单元格的自动筛选3
2、自动筛选与高级筛选4
3、条件行-筛选条件的种类6
三、Excel查找筛选并删除重复数据10
1)标识重复数据10
2)筛选重复数据11
3)分离重复数据11
4)删除重复数据11
5)提取不重复数据11
6)重复数据计数12
四、Excel如何删除公式保留数值13
1)快捷键F913
2)选择性粘贴13
3)如何删除数据而不删除公式13
一、Excel如何合并两个单元格内容
技巧一:
用连字符“&”来合并单元格内容
如果我们想将多列的内容合并到一列中,不需要利用函数,一个小小的连字符“&”就能将它搞定。
例如想把A、B、C列合并到D列中,操作方法如下:
1、只需要在D1单元格中输入公式:
=A1&B1&C1即可;
2、再次选中D1单元格,移动鼠标,当鼠标形状变成黑色实心十字时(即利用“填充柄”将上述公式复制到D列下面的单元格中),然后按住鼠标左键向下拖动,这样A、B、C列的内容即被合并到D列对应的单元格中。
3、选中D列,执行“复制”操作,然后再次选中D列,执行“编辑→选择性粘贴”命令,打开“选择性粘贴”对话框,选中其中的“数值”选项,按下“确定”按钮,D列的内容就是合并后的结果,而不是公式。
另外,如果希望在合并文本之间添加空格或者中划线(-),请键入&""&或者&"-"&。
第一步就需要改成=A1&"-"&B1&"-"&C1。
技巧二:
利用CONCATENATE函数
CONCATENATE函数可将多个单元格的文本合并到一个单元格中,如上例:
1、在D1单元格中输入公式:
=CONCATENATE(A1,B1,C1)即可;
然后按照第2、3步依次操作即可!
这里如果想加中划线(-),可以这样输入:
=CONCATENATE(A1,&"-"&,B1,&"-"&,C1)
学技巧提示:
完成第1、2步的操作,合并效果已经实现,但此时如果删除A、B、C列,公式会出现错误。
故须进行第3步操作,将公式转换为不变的“值”。
二、Excel中的数据筛选和高级筛选
Excel中提供了自动筛选(包括数字筛选、文本筛选和自定义筛选)、和高级筛选两种数据筛选操作。
Excel会根据数据类型来自动判断显示数字筛选或文本筛选。
筛选过的数据仅显示那些满足指定条件的行,并隐藏那些不希望显示的行。
使用自动筛选可以创建三种筛选类型:
按列表值、按格式或按条件。
对于每个单元格区域或列表来说,这三种筛选类型是互斥的。
不能既按单元格颜色又按数字列表进行筛选,只能在两者中任选其一;也不能既按图标又按自定义条件进行筛选,只能在两者中任选其一。
Excel中数据常用三大类型:
数值型、日期型、文本型。
在自定义筛选中,筛选范围时,通配符(?
和*)只能配合「文本型」数据使用;如果数据是日期型和数值型,则需要设置限定范围(>=或<)等来实现。
1、合并单元格的自动筛选
①取消标题行单元格合并,而后进行自动筛选,然后重新合并原单元格区域。
②标题行下所属数据是合并的单元格时,先复制备份单元格列的合并格式到其它列,取消原单元格合并~F5定位~定位条件~空值~第一个活动单元格输入=再向上键~Ctrl+Enter,复制转换过的区域~选择性粘贴~数值~确定,再将备份的单元格列的合并格式复制粘贴到原列,最后才能进行自动筛选。
——Excel高级筛选
2、自动筛选与高级筛选
自动筛选一般用于条件简单的普通筛选操作,且只能在当前操作区进行,符合条件的记录显示在原来的数据表格中。
高级筛选也称多条件筛选,用于条件较复杂的筛选操作,其筛选的结果可显示在原数据表格中,不符合条件的记录被隐藏起来;也可以在新的位置显示筛选结果,不符合的条件的记录同时保留在数据表中而不会被隐藏起来,这样便于进行数据比对。
例如我们要筛选出「基本工资」或「职务工资」超过500且「实发」工资超过800的符合条件的记录,用「自动筛选」就无能为力了,而「高级筛选」可方便地实现这一操作。
高级筛选
①方式:
在原有区域显示筛选结果,将筛选结果复制到其他位置;
②列表区域:
原工作表中需要筛选的表格区域;
条件区域:
用来筛选的条件表达区域;
复制到:
如果(方式)选中将筛选结果复制到其他位置,可以选择筛选结果放在哪个地方;
选择不重复的记录:
指的是去除原数据列的重复值所产生的筛选结果(筛选不重复记录要求数据区带有标题行)。
上图是在Excel工作簿中的两张Sheet表,Sheet1表标签叫「数据源表」,A1:
J24是数据源表当前的数据区域;Sheet2表标签叫「条件区域和筛选结果」,我们将在Sheet2表中进行操作。
下一张图是我们要进行操作的Sheet2表。
其中A1:
F5可以称做条件区域。
目前条件区域的内容,只列了一行,也就代表这个条件区域,实际上只是A1:
F2区域(做为条件的标题一定要和数据源表的标题一致)。
条件区域
高级筛选需要在数据区外设置一个条件区域,由标题行和条件行组成。
筛选条件行允许使用带运算符的表达式,还可以同时设置多列条件,或多行条件的表达式。
①要在条件区域的第一行写上条件中用到的字段名(标题),比如要筛选数据清单中「年龄」在30岁以上,「学历」为本科的职员,其中「年龄」和「学历」是数据清单中对应列的列名,称作字段名,那么在条件区域的第一行一定是写这两个列的名称(字段名),即「年龄」和「学历」,而且字段名的一定要写在同一行。
②在字段名行的下方书写筛选条件,条件的数据要和相应的字段在同一列,比如上例中年龄为30岁,则「30」这个数据要写在条件区域中「年龄」所在列,同时「本科」要写在条件区域中「学历」所在的列。
③条件种类涵盖自动筛选中所有定制格式的条件,包括等于=、不等于<>、大于>、小于<、大于等于>=、小于等于<=等。
如果是等于关系,则直接写值,不需加符号。
另外要注意的是,这些符号必须是英文半角符号,不可以是全角符号,最好在英文状态下输入。
3、条件行-筛选条件的种类
一)不包含单元格引用的筛选条件
此类表达式的特点不能以等号开头,允许以>=或<=开始的表达式;
条件区域标题的填写,必须填写与数据区标题相同名称。
①不带通配符的筛选条件:
>500:
表示筛选出大于500的记录;
<3:
表示筛选出小于3的记录;
0:
表示筛选出等于0的记录(如果该单元格设置的是文本格式,则筛选出的为所有包含0的记录)
>=2012/12/26:
表示大于等于2012年12月26日的记录;
②带通配符的条件设置
「*」代表多个字符;「?
」代表单个字符;
「~*」代表筛选「*」;「~?
」代表筛选「?
」。
③文本型条件的设置
「张」表示以张开始的任何字符串;「=张」表示筛选只有一个字符张的记录;
「*德」表示人名中,只要包含德的都筛选出来;「=*德」表示姓名以德结尾;
「>M」表示所有打头字母在M到Z;
二)包含单元格引用的筛选条件,如:
「=C2<>D2」表示筛选出同行次的C列与D列值不相等的记录
「=D2>800」表示筛选出D列数值中大于800的记录。
「=ISNUMBER(FIND("8",C2))」表示筛选C列数据中包含8的记录。
「C2=""」表示筛选出C列数据中为空的记录。
①查找空白:
在图号中查找为空白的记录。
在H2中录入“=C6=""”,H1中不用录入任何数据,然后再进行列表区域和条件区域选择,最后会显示出我们所要的结果来。
(如下图)
②查找数字:
例如:
在图号中查找与“8”有关的记录。
在H2中录入公式“=ISNUMBER(FIND("8",C6))”,H1中还是不用录入数据,然后再进行列表区域和条件区域选择,最后会显示出我们所要的结果来。
(如下图)
此类表达式的特点是必须以等号开头,表达式中可以包含各类函数,单元格引用是数据记录的第一条单元格地址,并且是相对引用;
条件区域标题不能使用数据区域中的标题,可任填其它或不填(与「不包含单元格引用的筛选条件」的规则刚好好相反)。
三)多条件筛选
在写条件时,一般都同时有多个条件,这些条件是个什么关系,又怎么在条件区域中分布呢?
我们平时所用的逻辑条件,如果有多个,它们之间总是会存在两种关系,一种是「或」关系,一种是「与」关系。
多条件筛选分为「条件与」、「条件或」和「条件与、或」的综合使用。
①同时满足多个条件数据的筛选(条件与)
要求:
在「员工基本情况登记表」中,把「职称」为「高级工程师」,并且「学历」为「大学」的人员挑选出来。
②并列满足多个条件数据的筛选(条件或)
要求:
在「员工基本情况登记表」中,把「职称」为「高级工程师」,「学历」为「大学」的人员都挑选出来。
在具体写条件时,如果是与关系,这些条件要写到同一行中;如是是或关系,这些条件要写到不同的行中。
也就是说不同行的条件表示或关系,同行的条件表示与关系。
下面几个具体实例,看一下筛选条件的写法:
①选择出语文、数学、英语三门成绩中至有少一门不及格的学生。
语文 数学 英语
<60
<60
<60
②筛选出语文、数学、英语三门课程都及格的学生。
语文 数学 英语
>=60 >=60 >=60
③筛选出语文、数学、英语三门课程中只有一门不及格的学生。
语文 数学 英语
<60 >=60 >=60
>=60 <60 >=60
>=60 >=60 <60
④筛选出「系别」为英语系、中文系,「年龄」在20岁以上,「籍贯」是北京、天津的学生。
系别 年龄 籍贯
英语系 20 北京
英语系 20 天津
中文系 20 北京
中文系 20 天津
三、Excel查找筛选并删除重复数据
Excel工作表的规模比较庞大、内容比较多,手工查找和删除重复数据很难做到“完全彻底”,Excel2007几个新功能可以轻松解决这类问题。
1)标识重复数据
选中A列,单击“开始→条件格式→突出显示单元格规则→重复值→颜色填充”。
已经标识的重复数据,可以通过2)筛选和3)排序进一步操作。
如果是Excel2003
①选择A列,单击菜单“格式→条件格式”;
②弹出“条件格式”对话框,在对话框左侧的下拉列表中选择“公式”,在右侧的文本框中输入公式:
=COUNTIF(A:
A,A1)>1(计算A列区域内有多少单元格的值与单元格A1相同,然后进行对比以确定该计数是否大于1)
③单击“格式→单元格格式→图案→选择红色→确定”。
如何查看Excel的版本?
打开Excel,点菜单栏「帮助」→关于MicrosoftOfficeExcel看版本
2)筛选重复数据
如果需要进一步识别并删除重复数据,可以根据上面的标识结果将数据筛选出来:
选中标识了重复数据的单元格区域,单击“数据”选项卡→“筛选”→“按颜色筛选”→“按字体颜色排序”,即可将存在重复数据或记录的行筛选出来,这时就可以查看并手工删除重复数据了。
3)分离重复数据
对于已经用颜色标识的重复数据,还可以用排序分离出来。
排序:
当前选定区域(扩展选定区域)-依据(单元格颜色)-次序(X颜色在顶端)
4)删除重复数据
选中可能存在重复数据或记录的区域,单击“数据”选项卡中的“删除重复项”按钮。
5)提取不重复数据
①数据→筛选→高级筛选→选择不重复记录;
②(筛选后的不重复数据)复制到→自定义区域。
6)重复数据计数
①重复数据总数:
上面4)删除重复数据时,可以看到重复数据的总个数;
②统计重复数据:
先排序,然后分类汇总,就可以查看各条重复数据的个数。
【条件格式-计算重复数据】
如果已知重复数据,并且重复数据不多,可以使用CountIf手动计算。
①在H1输入=CountIf(A:
A,A1),即可得到A列中与A1重复的单元格个数;然后将鼠标移到H1右下角,出现实心“+”号,下拉复制公式到其他单元格。
②在H1输入=countif(A:
A,A1)>1,如果A1有重复数据,那么H1将返回值TRUE,无重复将返回值FAUSE,可以依次下拉复制公式到其他H列单元格,还可以将所有数据按H列排序一下,对数据进行处理。
行重复
列重复
数据透视表
条件格式?
IF_SUMIF_COUNTIF三大条件函
四、Excel如何删除公式保留数值
Excel表中有的数据是用公式或函数连接和计算完成的,有时需要清除公式而保留结果值,或者将公式转化为数值。
1)快捷键F9
在单元格或编辑栏输完公式后按下F9→Enter就可以把公式计算的结果转换为普通的数字,也就是说,F9键将公式删除了。
2)选择性粘贴
选中数据区域→右击→复制→再右击→选择性粘贴→数值→确定(在原数据区域复制粘贴操作),这一列的公式就全部消失,只剩下数值结果。
3)如何删除数据而不删除公式
①按Ctrl+G(或F5)→「定位条件」→「常量」→就可以选中不带公式的所有单元格;
②点击主菜单上的「编辑」→清除→内容。
怎样显示公式而不显示数据
①快捷键Ctrl+~(Tab键上面):
在工作表中切换显示单元格值和公式;
②或者,工具→选项→视图→窗口选项→「公式」前面的「√」去掉即可。
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- EXCEL 几个 常用 技巧