分享一些让Excel事半功倍的实用技巧

-回复 -浏览
楼主 2019-11-11 09:54:46
举报 只看此人 收藏本贴 楼主

1、excel的快速访问工具栏:

我的快速访问工具栏由左到右主要是“保存”、“新建”、“撤销”、“恢复”、“升序排列”、“降序排列”、“打印预览”、“插入数据透视表”、“合并后居中”、“格式刷”、“选择性粘贴为数值”、“筛选”等,这个比较基础。

2、搜狗输入法的自定义短语设置

跟excel无关,但是效果拔群。要不是有这个功能,我早就放弃一堆广告的搜狗输入法了。。

3、分享一些我整理给身边初学者的常用快捷键,虽然助涨了伸手的风气,但这个问题实在正点,忍不住想答。

(1)F键功能键盘区

F4:非常实用,功能是重复上一次操作。例如要将多个不连续的单元格改为填充黄色,可以选择第一个单元格,设置填充黄色,然后依次选择其余的单元格,每次按一下F4。

Shift + F11:插入空白的工作表。注意与Ctrl + N比较,前者新建工作表,是sheet1,后者新建工作簿,是book1。

(2)数字键区域

Ctrl + 1:设置单元格格式

Ctrl + 2:加粗(同Ctrl +B)

Ctrl + 3:倾斜

Ctrl + 4:下划线

(3)符号键区域

Alt + 加号:求和,可以选定需要求和的内容后按该快捷键得出结果,也可以先按该快捷键,再选择需要求和的内容。

Alt + 分号:定位可见单元格,相当于按Ctrl +G,在“定位条件”中选择“可见单元格”。修改筛选状态下的表格时,先选定内容,按Alt +“;”,再操作,可确保仅对可见单元格操作而不影响未被筛选出来的部分。具体说明,如须在工资表中删除男性的工资,筛选性别为“男”,将筛选出来的内容删除行,直接操作可能导致中间性别为“女”的部分也被删除,需要在鼠标选择筛选出来的内容后,按Alt +“;”,再删除行。

Ctrl +减号:删除,选择某一行或某一列,按该快捷键,直接删除行或列。若选择单元格后按该快捷键,提示“右侧单元格左移”或“下方单元格上移”。

Ctrl + 双引号:清除单元格内容(用delete更快)。

Ctrl + 分号:将当前单元格内容改为当天日期,格式为2014-XX-XX。

Ctrl +“↑”“↓”“←”“→”:光标移动到最上/下/左/右边的单元格,如果单元格有数据,会移动到数据的边缘;单元格没有数据,则移动到整个工作表的边缘或遇到其他数据停下来。

Ctrl + Shift +“↑”“↓”“←”“→”:同上,但光标移动路径上的单元格会被选取。

(4)字母键区域

Ctrl + Z:撤销

Ctrl + X:剪切

Ctrl + C:复制

Ctrl + V:粘贴

Ctrl + B:加粗(同Ctrl + 2)

Ctrl + I :倾斜(同Ctrl + 3)

Ctrl + U:下划线(同Ctrl + 4)

Ctrl + N:新建工作簿

Ctrl + A:全选,单元格有内容则选取全部内容,单元格空白则选取整个工作表

Ctrl + S:保存

Ctrl + D:选择性粘贴,相当于按住单元格右下角的实心十字向下拉(Ctrl + R为向右拉)

Ctrl + F:查找/替换(Ctrl + H替换)

Ctrl + G:定位

Ctrl + K:插入超链接

Ctrl + P:打印

Ctrl + W:关闭当前工作簿

(5)Windows

Win + E:打开“我的电脑”,非常实用,提高查找和打开文件的速度

Win + D:显示桌面,相当于全部最小化

Win + L:锁定计算机

Win + X:管理电源、连接、亮度等

(6)其他键盘区

Ctrl + PageUp/PageDown:移动到上/下一张工作表

PageUp/PageDown:工作表向上/下滚动一页

Alt + PageUp/PageDown:工作表向左/右滚动一页

Ctrl + Home:光标移动到工作表的左上角

Ctrl + End:光标移动到工作表的数据的最右下方

Ctrl + Enter:批量选择性粘贴。选定多数单元格,输入数值或公式,按该快捷键,则所有单元格都被填充。

(7)日期函数

①date函数:=date(A1,B1,C1),可用于将分布在不同单元格的月、日等转换为日期格式。如=date(2014,12,31),显示“2014-12-31”

②datedif函数:=datedif(A1,today(),”y”)

=datedif(A1,today(),”m”)

=datedif(A1,today(),”d”)

求两个日期之间的差值,可用于计算年龄、工龄等

③month函数、day函数,返回某一日期的月份或天数


4、快捷键和函数的组合技

(1)连续单元格的批量填充

将一个单元格的数值或公式拖到下面的多数单元格中,我通常见到大家是在单元格的右下角拖动或双击实心十字,但是如果行数较多,拖动的话太慢,双击则不能确定中间是否有断行。

举例:一个10,000行的数据,A列是序号,B列为公式,B1=A1*2,需要将B列填满。我的做法如下:

①鼠标点在A列任一单元格,按ctrl+“”,光标移动到A10,000

②光标向右移一格,到达B10,000

③按ctrl+shift+“↑”,光标选择了B1到B10,000的区域

④按ctrl+D,自动填充

这样做的优点是可以顺便判断A、B两列是否有断行,而且比较直观,如果顺利完成,说明这两列的数据都是完整的。


(2)非连续单元格的批量填充


例图是金蝶系统导出的明细账,有的数据多达几万、几十万行,而且每一个科目要填充的内容都不同,没法一次性拖动来填充。假设数据有5万行,我的做法是:


①鼠标点在C列任一单元格,按ctrl+“”,光标移动到C50,000

②鼠标点击C50,000,按住拖动到B50,000,形成这样


注意要从C列拖到B列,反之不行。因为C列才是连续单元格,可以进行下一步


③按ctrl+shift+“↑”,光标选择了B1到C50,000的区域

④按ctrl+G,选择“定位条件”为“空值”


按确定后如下图,定位了B列所有的空值:



⑤此时直接输入“=”,公式会出现在第一个空值,即B3单元格,输入“=B2”,即等于上一单元格



⑥按ctrl+enter,之前定位的每一个空值都被填充公式“等于上一单元格”



注意:如果电脑不怕卡,可以在第二步从C列直接拖动到A列,把A、B两列一起填充


PS:如果连C列也是不连续的,可以在A列左边插入一列,全部填满,就可以移动到最下面一行来向上操作了。


(3)文本格式的日期转换为日期格式

这个不知道有没有更简洁的方式,我的方法如下:


(4)为表格创建主索引

仍然以导出的明细账为例,筛选销售费用后发现有几笔异常费用,想看下完整的凭证。但是明细账是按科目排列的,同一笔分录分散在若干科目中。通常做法是筛选凭证号,再筛选月份,可以定位到某一笔凭证。如果增加一列数据为月份+凭证号,就可以作为筛选凭证的主索引了。

如图中B列:


(5)双向链接判断是否两列数据是否一一映射

举例两个顺序不同的人员名单,要检查是否相同


图中对两列工号分别链接后,可见工号1是工号2的子集,但是工号2的100014没有包含在工号1中。(☆注:公式写错了,左边F:F应改成D:D,右边F2应改成D2,我不会删图。。



进阶应用:判断应付职工薪酬的贷方计提数是否都对应成本和费用的借方(注意数据都是按”月份+凭证号“透视汇总后的)

一、设置适用于自己的快捷操作

1. 搜狗输入法

设置属性-高级-自定义短语设置

2. 快速访问工具栏,设置常用操作

3. F1,帮助窗口

4. Alt,快捷键

5. 百度(熟悉专有名词,可准确搜索)


二、窗口操作

1) Ctrl+tab     在对话框中,可切换到下一个选项卡

Word或者excel中文件以如下图所示的方式列示时,可用于文档或表格间的切换

2) Alt+tab 窗口切换

3) Windows+E 打开“我的电脑”

4) Windows+D 显示桌面(全部窗口最小化)

5) Ctrl+shift+拖动文件图标,创建链接;ctrl+拖动文件图标,复制文件


三、排版

1. 将光标定位在首先阅览的位置,ctrl+home,定位A1;ctrl+end,定位最后一个单元格;home,定位在本列的第一个单元格

2. 冻结单元格/首行/首列

3. 隐藏不必要的行和列,Ctrl+9 隐藏选定的行;Ctrl+0 隐藏选定的列

4. 套用表格格式,Ctrl+L/T

5. 插入超链接,Ctrl+K

6. 打印预览,Ctrl+F2

7. 自动调整列宽 格式

8. Ctrl+1 设置单元格格式

9. Ctrl+2 加粗

10. Ctrl+3 倾斜

11. Ctrl+4 下划线

12. Ctrl+5 应用或取消删除线

13. 选择性粘贴(值,转置)

14. 格式刷(单个-单击/多个-双击)

15. 打印标题 页面布局


四、操作

1.分列(数据-分列)

复制表 ctrl+移动表

选中列 ctrl+shift+↓

插入行/列:F4/ctrl+y,选中多行插入,或者同时操作

数据有效性,定义序列


2.数据透视表

插入月份列,使用month函数提取


3.高级筛选(常用于查找对应科目)

打开/关闭筛选:ctrl+shift+L


4.排序

可设置序列辅助列,防止数据无法恢复


5.Vlookup

双击填充柄填充公式

从其他工作簿移动表格

绝对引用和相对引用

Ctrl,在同一单元格输入多数据


6.在Excel多个单元格内一次性输入同样的内容

选中单元格,按ctrl,在一个单元格中输入内容,按ctrl+enter

多个单元格复制上一单元格内容


7.零值显示的切换

当显示为数值格式时,在excel选项,高级中,不勾选“具有零值的单元格显示零值”,会计专用格式无法执行此操作



8. 当前时间ctrl:,当前日期ctrl;

动态时间now,动态日期today

Datedif(开始日期,结束日期,参数)

日期计算函数,计算两日期的差。参数决定返回的是年还是月等。


9.ctrl+G,

定位空值

定位可见单元格


10.分账龄,连续年度的审计

1.账龄1年以内的函数为A=IF(本年贷方发生额>=年初余额,年末余额,本年借方发生额)

2.账龄为1-2年的函数为:B=IF((年末余额一本年胀龄 1年以内的金额A>=上年度账龄1年以内的金额,上年度账龄1年以内的金额,年末余额一本年账龄1年以内的金额A)

3.账龄为2-3年的函数为C=IF((年末余额—本年账龄 1年以内的金额A-本年账龄1-2年的金额B>=上年账龄 1-2年的金额,上年账龄1-2年的金额,年末余额一本年账龄1年以内的金额A-本年账龄1-2年的金额B)

4.账龄为3年以上的函数为;D=年末余额一本年账龄1年以内的金额A-本年账龄1-2年的金额B-本年账龄2— 3年的金额C

上述运用Excel函数计算应收款项的方法,必须建立在“借方发生额均为实际发生的应收数额,贷方发生额均为实际收回或核销的金额”这一前提条件下,否则函数计算的账龄将存在差错(详见表四示例)。


11. IFERROR(公式,””)

算比例时,由于有一列为空值,显示为错误,影响汇总数


12.底稿设定

数据-编辑链接



五、组合键

F建常用推荐:

F1 :excel帮助(重要)

Alt+F1:在当前区域中创建数据

Shift+F11:插入新工作表

Shift+F3:插入函数

F4/ Ctrl+Y : 重复上次操作

F5/ctrl+G:定位

Ctrl+F9:将工作簿窗口最小化为图标

F10:打开或关闭键提示

F11:创建当前范围内数据的图表


Ctrl+F1:显示或隐藏功能区

Ctrl+F2 打印预览

Ctrl+F3 定义名称

F4 输入公式时快速切换“相对引用”和“绝对引用”即将C1:C5转化为$C$1:$C$5

Shift+Fn+F11 插入新工作表


Ctrl+1 设置单元格格式

Ctrl+2 加粗

Ctrl+3 倾斜

Ctrl+4 下划线

Ctrl+5 应用或取消删除线

Ctrl+6 在隐藏对象和显示对象之间切换

Ctrl+8 显示或隐藏分级显示符号

Ctrl+9 隐藏选定的行

Ctrl+0 隐藏选定的列


Ctrl+D 选择性粘贴上一单元格内容

Ctrl+R 选择性粘贴左侧内容

Ctrl+K 插入超链接

Ctrl+L/T 套用表格格式

Ctrl+N 新建工作簿

Ctrl+W 关闭当前工作簿


Windows+E 打开“我的电脑”

Windows+D 显示桌面(全部窗口最小化)

Windows+L 锁定电脑

Windows+X 管理电源、亮度、声音、连接等

移动类

· Home 移动到行首

· Ctrl+Home 移动到工作表的开头

· Ctrl+End 移动到工作表的最后一个单元格

· PageDown 向下移动一屏

· PageUp 向上移动一屏

· Alt+PageDown 向右移动一屏

· Alt+PageUp 向左移动一屏

· Ctrl+PageDown 向右移动一个工作表

· Ctrl+PageUp  向左移动一个工作表


Shift+Enter 在选定区域内从下往上移动

Shift+Tab 在选定区域中从右向左移动。如果选定单列中的单元格,则向上移动



连续单元格的批量填充

选择连续列中任一单元格,按Ctrl+↓ 右移一格,Ctrl+Shift+↑ Ctrl+D

非连续单元格的批量填充

选择连续列中任一单元格,按Ctrl+↓ 拖动至不连续的单元格,Ctrl+Shift+↑ Ctrl+G,选择空值 输入=上一单元格(如在A7单元格中输入“=A6单元格”), ctrl+enter

  • 如果你想进一步聊聊作品集

  • 请添加文章底部

  • 微信二维码,QQ咨询详情

  • QQ:121628007


我要推荐
转发到