18个好用到爆的Excel操作技巧,不收藏太可惜了!

发布时间:2019-12-26 来源:网络


01  删除数值只保留公式


除了利用定位条件来处理外,还有更快捷的方法:选中表格中数据区域,点击“查找和选择” ,选择“常量” ,按Delete键删除。



02  快速筛选


当我们筛选一些简单数据时,直接选中你要筛选的单元格数据,单击鼠标右键选择“按所选单元格的值筛选”即可。



03  提取括号里的内容


选择数据区域,【开始】选项卡下点击【查找和选择】下拉框中的“替换”,查找内容分别输入 *( 和 )* ,替换为空即可 。



04  随机生成字母


这里我们通过Randbetween及Index函数来实现。比如帮主要输入“A、B、C、D、E ”五个字母,输入公式“=INDEX({"A","B","C","D","E"},RANDBETWEEN(1,5))”



05  批量删除数据间空格


在处理表格数据时,我们会发现数据间存在若干不等量的空格,该如何删除那些不必要的空格呢?这时我们可以利用Trim函数,该函数主要用来删除字符串首尾的空白,同时保留字符串内部作为词与词之间分隔的空格。



06  逆向查找指定内容


大家都知道在“查找和替换”对话框中单击“查找下一个”时,会接着往下查找指定内容。如果在单击“查找下一个”命令前,我们先按住Shift键,即可逆向进行查找。



07  批量删除前缀内容


如下动图,在详细地址中删除前缀省份:【数据】选项卡下点击“分列”打开“文本分列向导”对话框,“文本类型”选择“固定宽度”,在“江苏省”后面单击鼠标拉出分列线,点击下一步,“列数据格式”选择“不导入此列”,点击完成即可删除不需要的省份内容。



08  快速选取指定列


选取表格区域,【公式】选项卡下点击“根据所选内容创建”对话框,选择“首行”,设置完成后,打开左上角的名称框,点击相应列标即可跳转到指定列。



09  隔列隐藏


选中第一行,按【Ctrl+F】打开“查找和替换”对话框,输入“备注”,查找全部,按Ctrl+A全选,然后关闭对话框,按【Ctrl+0】隐藏。



10  隔行填充颜色


点击“条件格式”选择“新建规则”,打开对话框,在对话框中选择“使用公式确定要设置格式的单元格”,输入公式“=Mod(Row(),2)”。



11  快速隔列求和


这里利用SUMIF函数进行条件求和:在H3单元格输入公式:=SUMIF($B$2:$G$2,H$2,$B3:$G3)



12  循环生成序列


利用MOD函数输入“1、2、3、4、5、1、2、3、4、5……”循环序列:在单元格D1中输入公式“=MOD(ROW(D5),5)+1”,向下填充即可。(说明:Mod(   )取余数;Row(   )取行号)



13  数据合并


选择需要合并的数据列,拉宽,点击“开始”下的“编辑”组,选择“两端对齐即可”。



14  合并单元格内部排序

如下图,按部门进行内部升序排列:建一辅助列,输入公式:

=COUNTA($A$2:A2)*10000+C2”,下拉填充。选择B1:D1单元格区域,进入筛选状态,选择升序,最后删除辅助列即可。



15  把单元格中指定的0替换掉


如下图我们需要把成绩为0替换为“缺考”,同时还需要防止其它成绩非0但含有数字0的单元格也被替换。这里用到“查找和替换”功能,注意一定要勾选“单元格匹配”后再全部替换。



16  批量删除错误值


如果在表格中有错误值,可以通过定位错误值来直接删除单元格。



17  取消合并单元格并填充空白值


取消表格内的所有合并单元格,选中空白单元格所在列区域,按F5打开定位条件窗口,选择“空值”,在第一个空白单元格输入公式=A2,按【Ctrl+Enter】键填充。



18  实现随机排序


借助辅助列快速实现随机排序:插入辅助列,输入公式:=RAND()即可。