相信很多同学或是上班族都十分憧憬不用鼠标就可以完成 Excel 神操作吧，但是又苦于没有 Excel 快捷键的汇总，所以也一直没有学习进阶。

今天生活君为大家献上压箱底的 Excel 快捷键大全，让你从此变身成大神！

处理工作表

在工作表内移动和滚动

在选定区域内移动

以“结束”模式移动或滚动

在 Scroll Lock 打开的状态下移动和滚动

扩展选定区域

编辑内容

计算类

编辑数据

编辑单元格

选定工作表行、列及对象

选定有特征的单元格

设置单元格边框

图表类

设置数据格式

虽然快捷键很多，记下来的难度比较大，但是大家可以先收藏下来，每天练习两三个左右，久而久之就 get 了强大的技能了~

你可以等自己一万年，但世界不会多等你一秒。想要成为更优秀的人，一定要在背后付出努力才能成就自己，所以加油吧！

第一部分 如何做一份实用的表格

上文已经提到过，使用excel的一些思路，现在我们来具体的探讨一下，如何做一份实用的表格。

一、何为实用性表格

那么，何为“实用”呢？相信对excel产生困惑的朋友们，都经常被下发一些表格，或者每个月都要固定的教至少一份报表。以我为例，每个月都必然要用到的就是工资表；对于其他部门，有可能是销售报表、财务报表等等。

而在我看来，实用性基本上要满足以下三个条件，

1.可持续性

月度报表最好考虑到以后的可操作性，能设置成公式的部分尽量用公式取代，不要造成“每个月都要重新做一次”的情况。

2.可变性

有一些表格的过程量，尽量以源数据的形式体现，便于更改。

举个例子，如果领导要测算年终发放方案的合理性，可能开始给出了一个比例系数，这时候就要考虑方案中的比例系数的可变性，不要把系数直接乘在公式里，而是要以“未知数”的形式单列出单元格，所有公式中需要用到系数的部分，都直接以该单元格替代。

如图所示，

当表格中有不确定系数时，第二种做法的实用性，显然好过第一种。

3.可验证性

有的表格过程繁琐，源数据无数，数量关系复杂。而涉及到数据的表格，往往是十分重要的内容，如工资、收入、账目等等。当我们做完表格时候往往要验证是否正确，而从头到尾屡一遍，往往费时费力，还可能有疏漏。

所以在每个表格制作过程中也要考虑，从哪方面可以对表格进行准确性验证，例如，反推出源数据，或者与其他表格进行对比等，用不同于第一次做表的方式去验证表格的准确性。以后的实例中，会具体提到这部分内容。

二、把表格当做一个系统

为什么说要把表格当做一个系统呢？

因为一个完整的表格差不多要包含输入内容（源数据），输出内容（需要的结果），以及计算过程甚至纠错过程。简单的表格可以全部在一个工作表上体现，复杂的表可能需要一个工作簿中的多张工作表共同组成。

例如，我每个月使用的工资表，一共包含10多个附表，可能有点夸张，但是使用起来还是很便利的；附表中有很多是源数据，例如从社保拷回来的明细等，由于社保系统拷出来的数据含有很多内容，开始我是每张都经过处理，然后录入工资表中。

时间长了，渐渐发现很不便利，五险就是五张表（我公司人员变动性较大大且财务要求实交实扣），每个月都要处理再录入。后来，我直接把社保明细当做附表加入到工作薄中，再在工资明细表中用公式调用相应的数据列。以后每个月，只要把拷回来的明细直接粘进相应的附表即可。

那么，如何构建你的专属系统呢？

首先，知道自己有什么，比如，做一份工资表，手里就要有人员明细、社保明细、出勤情况等；其次，知道自己要什么，例如，我需要工资明细单，我还需要各个部门汇总明细等。

然后，我所要做的就是，建立源数据和结果之间的过程关系。最后，还可以建立一个或几个纠错用表，通过结果，反推过程，与源数据对比，以减少错误发生

三、做表，有时候也需要灵感

上文提到过，我的工作簿一开始也没有这么多附表，而是在使用过程中，后添加的。所以，做一个“实用”的表格，永远都不可能一蹴而就。也需要在使用中去发现、改进。例如，发现更便利的公式，或者像上文中减少源数据的处理过程而直接调用。

所以，后期我渐渐发现，除了我需要交给领导审阅的工资明细和交给财务的部门汇总清单以外，导入税务系统的报税表、给银行使用的发放清册甚至财务的凭证封面都可以在一个工作簿中完成，大大的提高了效率。

这里需要特别提到是，由于我公司的财务凭证是使用A5纸打印的，但是有固定的格式，我以前都是手动输入总金额，由于工资总额都是百万级，有时候，为了着急发放工资，所以有时会填写错误，后来我把这张凭证也作为附表，加入我的工作薄了，总金额只要输入一个“=”即可，方便，且准确。

所以，与其去一遍一遍核对数字，永远不如一个“=”方便、快捷。

纸上谈兵了这么多，下一次，我们就开始实际操作表格~~

（待续）

前中我们已经提到过，原模板已经预设了五险一金以及个税的公式，所以，我们先来分析一下，原表中的公式是如何使用的，本次文章我们主要开看一下五险一金计算方式以及roundup函数的应用实例。

原表中，五险一金的公式如下，

我们都知道，五险一金的个人及企业部分等于该险种基数与相应比例的乘积（如果有医疗大病项需要单独加入）。Sum求和公式我们不用多说，除此之外，我们不难发现，表中还有两个特殊的细节。

第一，原表中的被乘数即是前面的社保基数一项而乘数选择绝对引用了上面的一个单元格。这个部分是我后期优化的，正是为了前面我们提到过的“可变原则”。

因为，社保的比例未必是恒定的，根据不同地区或者政策变化都会发生改变。单独列出来，清晰明了，比如下个月医疗的公司部分突然改成了10%，我们只需改一个单元格即可，防止了后期改公式的繁琐，也无需点开公式就可以知道现在实行的比例。

有的朋友可能觉得自己脑袋好用，没有必要这么麻烦。但是在此必须提醒各位：做工资是个细节特别多的过程，所以有些地方改的越方便越好，一劳永逸。其他工作也是一样的，宁愿前期多费工夫，也不要后期花时间去检查。

后来，我公司人员同时使用了两种医疗表现比例，却要体现在一个表上，这个以后我们再分析。

第二，我们发现在乘积的同时，又在外面加上了一个roundup函数，它有什么作用呢？在excel中，该函数的解释如下，

Roundup（number，mum_digits）向上舍入数字

即为“保留n位小数，其他部分上前进一”用函数，number指的是需要处理的内容，mum_digits则为需要保留的小数位数，输入“1”，则为保留一位小数，以此类推。

为什么要使用这个公式呢？因为在我市的社保系统中，缴纳金额保留两位小数，且多余部分舍去，向前进一，而非四舍五入。如果不使用这个函数，偶尔就会出现工资表中的社保总额比社保局的金额上下差几分钱的情况。

由此，我们可以举一反三知道三个公式的用法，

这里我们做个小提示，当你还不熟悉某个函数的使用时，可以先输入该公式“=f(x)”后，同时按下ctrl+A键。如果我们使用的是roundup函数则出现下图，

只要按提示输入需要的部分然后确定即可，根据不同的函数，输入可能是某个数字、某个单元格甚至嵌套另一个公式。

Excel的条件格式功能可以根据单元格内容有选择地自动应用不同的格式，它为我们的数据筛选工作带来很多方便。但如果让条件格式和公式结合使用，则可以发挥更大的威力。今天我就提供几个在条件格式中使用公式的应用实例，希望能给大家带来一点启发，并将其运用到实际工作当中去。

1、应用实例1--聚光灯

=OR(CELL("row")=ROW(),CELL("col")=COLUMN())

注：因单元格不会自动计算，故需选中单元格同时按F9,才可实现以下效果。

数据聚光灯，无需写入VBA代码。当选中任一单元格后，将其所在行和所在列均着色。

2、应用实例2--隔行填色

=MOD(ROW(),2)

隔行填色

3、应用实例3--隔两行填色

=MOD(ROW()+2,3)

隔两行填色

4、应用实例4--根据指定条件动态着色

=$E2<$H$2

根据指定条件动态着色

5、应用实例5--智能提醒

=$D2=TODAY() 注：此条件放在上面优先应用。显示为红色背景。合同到期日在当天的。

=AND(TODAY()-$D2<=10,TODAY()-$D2>=0) 注：此条件放在下面次优先应用。显示为橙色背景。合同到期日在10天内的(不含当天)。

智能提醒功能

结语：条件格式的功能很强大，好玩又有趣，快来动手试试看吧！

千万别学excel

上一次说了对本月员工是否转正做高亮显示——我们用到的是“条件格式”。

有朋友表示算转正很麻烦，每次都算错，今天就专门来说一下，转正和提薪的测算方法。

不过关于转正及提薪，不同的公司、薪资结构不同、都会造成具体测算方式的不同，但是大体上是一样的，所以这里只是提供思路，要根据实际情况修改，不可以拿来主义哦。

麻烦的点在哪？

觉得转正测算麻烦无非因为以下几种情况，

1.员工当月非全月转正

如果全月转正只要改薪酬标准就ok了，最怕就是算非全月。

2.工资表里没有给提薪设置单独的列

例如我的表格里，转正工资是要写在“其他”列里的，里面还包括罚款、奖励等等异常项，如果一位员工当月有很多种异常，很难核对。

3.当月涉及到法定节假日

还有一种情况就是，员工转正日刚好包括了几天法定节假日，就是那种“不用上班，还要拿钱”的美丽日子

如何规避麻烦？

1.建立附表

之前我已经反复说过，只要是需要测算或者核查的过程，我都会单列附表。

不然想像一下，如果员工来核对转正工资，但是这小子这个月拾金不昧奖了点小钱，结果上班睡觉又扣了点钱，全都捏在其他里……你看着其他列的一个数字，你要给他解释明白就得再算一遍。然后员工还会觉得“算工资那小子不靠谱”。

2.与考勤表结合

员工哪天上班、哪天休息、哪天不用上班也拿钱，最权威的凭证是什么？就是考勤表啦，这里小晒一下我设计的请休假台帐。

下面要建一个小图示

这样就比较一目了然了，以后有请假的直接在表上标一下，也不用每个月末都核假，同时，如果员工问你自己还有几天年假之类的问题，也可以很快的回答。

当然这个表在每个月初都需要设定星期几、法定节假日，但是相对于它说带来的便利性，这个过程并不麻烦。

有法定节假日的就是这样的了。

转正、提薪附表设计

我的转正提薪附表结构很简单，

而转正天数，我每个月是通过考勤表数出来填上的，因为转正提薪比较还是少数人，要用复杂的公式来规避诸如法定节假日等影响，莫不如每个月都扫一眼踏实。

所以做转正工资，我的操作步骤如下，

从花名册调取当月应转正人员日期，填在附表里

计算该员工当月转正天数，填入附表

转正、提薪公式设计

要设计公式就要知道“这个数是哪里来的”，

首先，我们主表已经按员工非转正工资计算了，所以等于我们“差他点钱”

差多少呢？

比如该员工工资为3000，转正前就是2400，假如当月有22个计薪日，

则分配到天就是该员工的日薪为3000除以22，136.36元/天；同理转正前日薪为109.09元/天。

所以，最通俗的话讲就是，我转正后的几天你应该按136.36元/天，你却按109.09元/天给我发的，所以每天少发了27.27元/天。

当月差多少？那就乘以转正天数就ok了啊（记得不要算周六周日哦）。

所以我们的公式就有了：

（转正后工资-转正前工资）÷当月计薪天数乘以转正天数

落实到表格里就是：

基础数据是什么？

之前我们设在最前面的计薪天数呗~，这里也看出来有的参数但列出来的好处，下个月直接改基础数据的表格，后面这些内容都联动了，不用再一个一个改了。

之后把这个数据vlookup到主表就ok了。

我们不论在学习还是在办公的过程中可能用到最多的是word 文档和Excel。虽然经常使用，但是其实我们好多人对EXCEL并没有完全深入的了解，只是懂得一点而已，好多其他的东西并没有掌握，今天我们就来看一些大神是怎么把表格做的那么漂亮的，看看他们是怎么玩转EXCEL的吧。

第1步：更换和弱化表格线，突出显示数据。

去掉表格背景网络线

除表头和表尾外，数据部分用浅灰色表格线。

第2步：设置隔行背景色，可以选浅灰或浅蓝色填充

填允一行后，用格式刷向下刷

稍增加行高

第3步：修改字体类型。

标题用黑体

数字用Arial

汉字用微软雅黑

合计行字体加粗

第4步：用条形图增加的年合计的可视性。

选取G5:G11 - 条件格式 - 数据条

完工！

以上就是一些制作表格的小技巧，做出来的表格果然要比我们平时自己做的漂亮，看上去也舒服，而且操作起来也比较简单。可是我们不仅在办公的过程中会用到EXCEL表格，有时候会因为办公的不同需求需要把EXCEL转换成PDF。那么我们应该怎么转换呢？我们不用着急，一起来看下吧。

迅捷PDF转换器

一款功能强大、操作简单的PDF转换成word转换器，支持PDF文档和doc、ppt、excel图片以及txt文档等多种格式之间的转换，是目前转换效果最佳，转换质量最好的新一代文件格式转换器。我们就可以使用它来将excel表格转换成PDF

更多的办公软件小教程会在以后陆续发布哦，请大家继续关注，欢迎评论收藏！

前文我们已经研究了关于员工“计薪天数”的计算方法，今天来研究一下工资表的核心——岗位薪酬的设置以及校验。

首先我的岗位工资一列是手填的，但是涉及到一个问题：

员工转正与否对工资的影响，因为在我司，试用期员工为3个月，薪酬为80%，而且转正涉及到提前或者延后的差别，还有非全月转正的员工，需要计算这个差额。

这也是工资核算、校验需要注意的一点。

做工资的时候要逐项核查一个员工是否转正对我来说特麻烦，所以必须找个一劳永逸的办法。

思路

转正与否以什么为准呢？当然是人资专员手里的花名册了，只要与花名册进行比对，不是很轻松就校验了转正工资正确性了吗？

获取源数据

根据可持续性，我把花名册直接复制到我的工资表里作为一个附表。

这里也可以看到，我在这张表上设置了很多检查项，当然有一些后来不涉及了，就空掉了，因为花名册对人员的各种信息非常全面。

而白色的部分就是人资专员提供给咱们的数据。可以看到，是否转正已经很清晰了。

公式实操

接下来的部分就很简单了，我们黄色部分的第一列，对基本工资的检验公式为：

=VLOOKUP(C3,'201507工资明细'!E:J,6,0)

就是获取到主表的岗位薪酬数。

“检查列”一项的公式为，

=IF(OR(AND(L3=已转正,M3=N3),AND(L3=未转正,0.8*M3=N3)),正确,需调整)

翻译如下，

如果已经转正，花名册的工资等于工资表、或者没转正的话工资表里等于花名册的百分之八十，如果符合这个就显示“正确”，如果不符合这个就显示“需调整”。

这里又涉及到两个新的函数，但是很简单，and以及or，我们看一下excel给我们的解释。

通俗的讲，or就是，这些条件有一个对的就是对！而and是，这一排条件都对了才对。

条件格式的设置

我们看一下，这个公式设置完了，如果错了，我的表格会怎么样呢？

可以看到不但显示“需调整”还明显的变为了红色，这里怎么做到的呢？

我们要用到的是“条件格式”，

条件格式位于开始选项卡中非常明显的位置，

我们选中整列，点击新建规则

做如上设置就ok了。

条件格式的应用扩展

条件格式用处很多，比如显示重复值、或者显示小于多少的值（如显示不及格的同学），等等，十分适用。

朋友们可以牢记“如果我的表格想要做醒目的提示，我应该想到使用条件格式的功能”，

在日常办公中，我们经常要面对大量的表格。如果，你对你的Excel表格的技术有一些没过关，那到时别人弄好了，可以开开心心的下班了，你就悲剧了，估计就要加班了。

今天，小编来为大家出几个对于excel表格的值得学习的小技巧。在平常多看看，多学习，下次在遇到这类情况时候，就可以快速解决。这样你也可以正常下班啦！

(一) 如何快速应用函数

在统计表格时，有时候我们会需要应用函数计算，为了节约时间，我们可以用一些小方法让它自动计算。

当你设置好了第一行单元格的函数，只需要把光标移动到单元格的右下角，等到它变成一个黑色的小加号时，双击，公式就会被应用到这一列剩下的所有单元格里。

这是不是比用鼠标拖拉容易多了？！

是不是很方便快捷呢，

下图是图片教学，不明白的小伙伴可以用作参考。

（二）如何快速增加或删除一列

我们在统计表格的有时候会需要在表格的上方或者下方增加一列表格，

当你想快速插入一列时，键入Ctrl + Shift + ‘=' （Shift + ‘='其实就是+号啦）就能在你所选中那列的左边插入一列。

而Ctrl + ‘-‘（减号）就能删除你所选中的一列。

是不是很方便快捷呢，

下图是图片教学，不明白的小伙伴可以用作参考。

（三）如何快速调整列宽

有时候为了更方便更直接的看到表格里的内容，我们需要调整表格的整体宽度，

想让Excel根据你的文字内容自动调整列宽？

这时候你只需要把鼠标移动到列首的右侧，双击一下就大功告成啦~

是不是很方便快捷呢，

下图是图片教学，不明白的小伙伴可以用作参考。

（四）如何在不同的工作表之间快速切换

有时候我们切换表格检查数据的时候，几乎都是用手动切换的，其实

在不同的工作表之间切换，不代表你的手真的要离开键盘（可以想象如果你学会了这些酷炫狂拽的快捷键，你根本不需要摸鼠标）。

“Ctrl + PgDn”可以切换到右边的工作表，反之，“Ctrl + PgUp”可以切换回左边。

是不是很方便快捷呢，

下图是图片教学，不明白的小伙伴可以用作参考。

以上便是今天的excel表格的小技巧，我们会持续更新，如果以上内容对你有所帮助，就请点击收藏关注我们吧！

一、表格整体分析

好，现在如果我们要做一位员工的工资表，需要什么内容呢？

一般来讲，

实发工资=岗位薪酬×月出勤率（出勤天数/21.75）+补贴（车补、年薪）+加班费-五险一金的个人扣款-个税

通常成熟的企业都有自己的工资模板，有的企业已经开始使用EHR系统，那样更加便捷，这里暂不做讨论。我们先来看一下我所使用的工资模板。

我们可以看到，标准为黄色的部分实际就是当月的应发工资部分，而蓝色的位置则是工资中需要扣去的五险一金及个税的部分，从应发中扣去蓝色的部分即可得到实发工资。

而绿色的部分等于是五险一金的计算过程，由于该表格构成为依据基数用公式测算五险一金，所以依据上文我们提过的纠错性原则，我们想到，可以把用员工保险基数计算的当月应缴纳的五险一金与社保下载的明细做对比，以验证双方准确性。

最后白色的部分是用于预算中使用，以及对工资表的异常项做备注的位置，

二、构思表格整体构成

我们以及基本了解了工资明细表的构成，下一步，就是要把所有影响工资的项目考虑全面并且归类，如员工是否处于试用期、当月是否产生加班等等。

之后我们，大约能够描绘出，大概的表格系统样式，如下图所示，

工资明细

为整个核算的核心部分,所有薪资的细项都会体现在这张表中，完成从基础数据到最终实发工资的计算。

输入部分

黄色区域为输入部分，依据我公司情况包含人员异动、转正、补贴、社保以及加班费；需要据实际情况填写或导入明细。

输出部分

蓝色区域为输出部分，包含oa附件、银行批量发放工资用表、报税用表以及供员工查阅用的工资条。

表格的构思对做好一个excel表是十分重要的，excel不单纯是一个“计算器”，很多时候，我们脑中要有做程序的思维。时刻保持脑中思路顺畅，理清一下几点：

我要得到什么结果？我已经有了哪些数据？源数据通过什么样的计算得到结果？得到的结果我要怎么用（如何显示、打印）？

有了这些思维，奠定了表格的基石，才可以去谈之后的步骤。

（未完待续，之前内容请翻我以前的文章，感谢阅读。）

Excel中通过数据有效性设置下拉菜单功能可以帮助我们节省很多输入时间，通过选取下拉菜单中的值来实现输入数据，非常快捷、方便、并且能最大限度减少差错的发生。但是日常工作中，我们常需要一个下拉菜单，让后面的下拉菜单依据前面的下拉菜单的内容的改变而改变（也就是多级联动、动态更新的下拉菜单）。

目前网上能看到的教程大多是通过逐级进行定义名称，在通过indirect函数引用名称的方式实现联动菜单的效果。但此种方式有很大的局限性。具体来说有以下几点：

定义名称繁琐。前一级菜单有多少个选项，就需要定义多少个名称，如果有上千个选项就需要定义上千个名称，工作量太大。

选项可能不能定义为名称，名称的格式有要求：如必须以下划线或文字、字母开头，不能出现特殊的符号等，这样就限制了选项的范围。

更新选项不便。若选项需要调整，需要逐级修改定义名称或范围，这对于需要经常更新的工作场景下显得非常不便。

那么有没有一种方法，可以避免上述的问题呢？答案是有的。今天我就这一方法首次公布于众，希望能给你的工作带来帮助……

我们先来看下最终的效果：（四级动态选择，若需要增加、减少选项只需在AreaCode工作表中进行操作，再选择全部刷新选项即可，更新维护非常简单。）

多级联动菜单演示效果

这一方法的实现思路是：

利用数据透视表的去重功能得到不重复的选项数据。

利用OFFSET函数实现对选项数据的动态范围引用，并将该动态区域定义为名称。

利用数据有效性将名称作为数据源，从而形成联动效果。

下面我们就分步骤来看下如何实现这一效果：

第1步：将数据源定义为名称，方便后续建立透视表

第2步：在辅助表中建立数据透视表，为OFFSET函数提供数据

第3步：构建省份的动态引用

第4步：创建第2个透视表为城市提供数据

第5步：构建城市的动态引用

第6步：构建区县的动态引用

第7步：检查各项参数

第8步：新建名称

第9步：新建的名称及引用位置

第10步：为每个选项设置数据有效性

结语：其实excel多级联动菜单原理非常简单，只需要熟悉offset、match、countif、counta等函数的用法，就能轻松实现这一效果。在此基础之上，你还可以做出多级联动列表框的效果，是不是很酷呢？快来试试看吧！

多级联动列表框演示效果

千万别学excel