这30个Excel实用技巧,效率大神都忍不住收藏了。

Excel在日常工作中的重要性,已无需赘言,然而,无论对于初学者和高级用户,在实际应用过程中,仍不可避免地忽略了很多实用的技巧和窍门。文章梳理了30个常用小技巧,分享给大家参考尝试。

1、不复制隐藏的单元格

比如,截图中5-8行单元格都隐藏掉了,复制的时候只想复制可见单元格。如果直接复制、粘贴,那被隐藏的数据也会被复制出来,怎样才能不复制隐藏单元格呢?

方法1: 按“Ctrl + A”选中表格,接着再按“Alt + ;(分号)”选中可见单元格,然后,按“Ctrl + C”和“Ctrl + V”复制粘贴即可。

方法2: 首先按“Ctrl + A”选中表格,接着按“Ctrl + G”打开定位窗口,设置定位条件为“可见单元格”,最后再复制粘贴即可。

2、批量删除空行

选取工作表范围,按CTRL+G定位,定位条件为-空值,然后删除行。

3、下拉菜单的制作方法

操作步骤:选中要插入下拉菜单的单元格 - 数据 - 数据有效性 - 序列 - 在下面的来源框中输入以逗号分隔的字符串。注意逗号是英文的逗号,不是中文。

4、单元格内强制换行

按Alt+Enter可以强制换行。

5、锁定标题行

如果数据太多,当表格滚动到后面时,标题行就不见了,这对于查看数据来说极为不便,如何使Excel某一行固定不动?

步骤:视图 - 冻结窗格 - 冻结首行,就可以锁定第一行不动。如果是冻结第2行,就选取第3行 - 窗口 - 冻结窗口,以此类推。如果想撤销冻结,可以点击窗口-解冻一项,就恢复了表格。

6、删除重复值

方法1:选中单元格范围 - 数据 - 删除重复值,可以按某列删除重复,也可以按整行判断重复。

方法2:选中单元格范围 - 数据 - 筛选 - 高级筛选,在筛选时勾选“选择不重的记录”。

7、同时查看多个Excel文件

同时查看同一个工作簿的2个或2个以上工作表,要先视图-新建窗口,然后再点全部重排,重排可以根据自己需要垂直并排或者水平并排。效果如图。

8、日期格式的转换

方法:1:选定数据范围 - 数据 - 分列 - 分隔符号 - tab键 - 日期 - 确定。

方法2:用text函数公式:=--TEXT(B2,"0-00-00")。需要注意:Text转换的是文本型日期,加--可以转换为数值型日期如图。

9、单元格添加对角线

选择要添加对角线的单元格 - 右键“单元格格式”- 边框 - 选择对角线(参考截图);下来在单元格内输入内容,比如,先输入“日期项目”4个字,接着把鼠标定位在"日期后面,按Alt+回车,"项目"就强制换到第二行了,然后,在“日期”前面按空格到合适的位置即可。

10、隔行填色

步骤:选定需要隔行填色的目标单元格 - 条件格式 - 新建规则 - 使用公式确定要设置格式的单元格 - 输入公式:=mod(row(),2)=1 - 单击右下角的“格式”-填充 - 选取填充色并点确定 - 确定。

注:公式=mod(row(),2)=1,是判断当前行是不是奇数行,如果是奇数行,则执行“格式”命令,否则不执行。

11、快速插入或删除行

快速插入:选取需要插入的行数,按快捷键 Ctrl+Shift++(加号),则新行在当前选中行的上方插入。

快速删除:选择目标行,按快捷键Ctrl+-(减号)即可,其他行自动向上移动。

12、统一添加单位或后缀

选中要添加后缀的单元格,右击选择“设置单元格格式”,打开单元格格式设置窗口。在“数字”下选择“自定义“----G/通用格式,在后方输入字符“万元”,确定即可。

13、统一添加前缀

和添加后缀类似,右击选中“设置单元格格式”- 数字 - 自定义 - 通用格式,区别是在最前边加上要添加的后缀,比如"收入-",注意添加的前缀必须用英文双引号("")括起来。

14、对齐长度不一的字符

选中目标单元格,快捷键Ctrl+1打开“设置单元格格式”,选择“对齐”- 水平对齐 -分散对齐(缩进)- 确定。

15、跨表格复制粘贴

同一个工作簿,如果多个sheet都想填充同样的内容,可以参照如下方式。

按Shift键,同时选中多个工作表,然后选择要复制的区域,接着点击:开始——编辑——填充——成组工作表,即可。

16、快速生成图表

方法1:选择需要绘制的数据,按下F11键,Excel将自动为创建图表。

方法2:选好要绘制图表的数据,按下"Alt+F1",也可以得到相同的结果。

17、批量求和

如果连续求和区域的右边一列和下面一行为空白,可以用鼠标将此区域选中,注意要包含其右边一列或下面一行。然后,单击"Σ"求和图标,则选中区域的右边一列或下面一行自动生成求和公式,而且系统能自动识别选中区域中的非数值型单元格,求和公式不容易产生错误如图。

18、保持序号连续性

在处理表格数据时,经常对指定行数进行删减或隐藏等操作,这时如何保持序号连续性呢?可以使用SUBTOTAL函数,在A2单元格输入公式:=SUBTOTAL(103,$B$2:B2),往下填充即可,如图,输入公式后,隐藏了4、5、6三行,序号也可以保持连续性。

19、文字快速分行

对Excel表格使用不太熟练的人,常常会将所有的内容全部输入到一个单元格内,如何将这些内容,分到不同行当中呢?举例,将截图中单元格数据每两个字分一行,填充到不同行。

操作步骤:将文字所在单元格宽度调整为大致2个字的宽度,然后点击开始 - 填充 - 两端对齐 - 确定。

20、快速“行转列,列转行”

在制表过程中,经常会对行列进行转换,如何才能快速有效的转换呢?最简单的方法就是,先复制表格,然后,右键“选择性粘贴”,勾选“转置”,确定即可。如图效果。

21、比CTRL+A还简单的全选方式

我们多数人在进行文档全选时候,都会使用CTRL + A快捷键,不过,还有一个更快的方式,可以尝试,就是点击文档左上角的按钮,如截图位置,会更快。

22、同时打开多个Excel表格

当你需要处理多个文件时,一个个打开太麻烦了,可以尝试一次性选中所有要打开的文档,然后点击回车键,就可以一次性搞定。

23、快速移动或复制数据

在Excel表格中,如果想要快速移动一列数据,简单方法就是选择它,然后将指针移动到边境后,它会变成一个十字箭头图标,接着拖动此列到目标位置即可。如果你要复制的此数据,同样可以按此方法来移动,区别就是拖动时候,按住Ctrl键。

24、模糊搜索

Excel工作中,常常会遇到无法精准录入或查找的情况,不得不采用关键词进行模糊查找,具体如何操作呢?首先,在某个单元格输入要查找数据的关键词或简称,然后,在另外一个单元格输入公式=VLOOKUP("*"&D2&"*",B2:B8,1,)。

解析:B2:B8是查找范围;D2是关键字所在单元格;函数中“*”代表任意字符,也就是在B2:B8内查找D2文本前后任意字符的内容,即,包含D2文本的意思。其中的1代表返回B2:B8数据区域第一列结果,最后一个逗号后省略参数代表的是精确匹配,也可以输入0或FALSE。

25、输限制与数据验证功能

为了保持数据的有效性,有时需要限制输入值,并提供一些建议步骤。例如,做问卷调查,某一列要输入年龄,那这列数据内容,就要限定为整数,且要求参与本次调查的所有的人应该是18至60岁。要确保这个年龄范围之外的数据没有进入,就要设置如下限制:点击数据 - 数据有效性 - 依次进行“设置”-“输入信息”-“出错警告”设置。

输入信息内容参考:“请输入您的年龄,限定为整数,且范围为18到60。如果实际输入的内容不符合以上要求,则会收到警告信息。

26、转变文本的大小写

比如,要把=E列的英文字母,分别转换成F列的大写,以及G列的小写。操作方法如下:大写=UPPER(E2);小写=Lower(E2),E2为转换前的文本位置。

27、输入值要从0开始,该如何输入?

当输入值从0开始(比如0001,0002),Excel将在默认情况下,删除这些零,而不是重新设置单元格格式。要解决这个问题,很简单,只需要在第一个零前边,增加一个单引号即可。

28、“双击”,重命名工作表

有多种方法来重命名工作表,比如,点击鼠标右键,选择重命名。不过,还有更简单的方式,就是双击,然后就可以直接将其重命名。

29、文档限定编辑范围

涉及多人操作或者跨部门合作用表等情况下,为防止表格内容被误操作,可以限定工作表可编辑的范围。

操作方法:选择允许编辑的区域(例如手机号这里) - 点击菜单“审阅”-“允许用户编辑的区域”-“保护工作表” -勾选“选定锁定单元格”和“选定未锁定的单元格”,点击确定这样就只有手机号一列选定框范围可以编辑,而试图更改该区域外的任意单元格会提示只读。

30、文档加密和解密

Excel2003版本:点击“工具”菜单 - 选择“选项”命令 - 安全性 - 在“打开权限密码”后面输入密码 - 重新输入密码,确定。

Excel2007版本:点击左上角的“Office按钮”- 选择“准备”-“加密文档”,输入密码,再次确认密码,即可。

Excel2010版本:进入“文件”- 信息- 保护工作簿 - 保护工作簿 - 用密码进行加密 - 输入密- 确定,在弹出的“确认密码”对话框中重新输入一次密码,确定即可。

职场、工作,不懂得使用正确的方法,就等于浪费时间,而如果已经知道了方法,却不愿意或不去用,就更是罪过了。

【本文由“盛夏职场工具箱”发布,2019年8月25日】

感谢关注,感谢分享。

本站是提供个人知识管理的网络存储空间,所有内容均由用户发布,不代表本站观点。如发现有害或侵权内容,可拔打24小时举报电话4000070609 或 在线举报
来自:盛夏职场工具箱  > 职场
举报
[荐]  原创奖励计划来了,万元大奖等你拿!
猜你喜欢
类似文章
Excel2007 常用技巧100招
Excel用得好那是神器,用不好就是个表格
必须会的8大Excel技巧,看Excel大神怎么让Excel又快又“好看”
精选43个Excel表格的操作技巧|推荐收藏
EXCEL制作
求出Excel 2007中连续4个季度都上涨的数据
更多类似文章 >>
生活服务