Hello大家好，我是帮帮。今天跟大家分享一下筛选后粘贴的技巧，磨人多年彻底解决。

【最近真的有点忙，年底了有时顾不上，大家谅解下！重度酒精考验。。。】

觉得帮帮还不错的，分享分享朋友圈呀，亲们^^<——非常重要！！！

大家请看范例图片。假设两个表格之间数据复制。如果开启筛选功能，直接复制粘贴，肯定不行。定位可见单元格也行不通。磨人多年的难题，这时我们需要一点技巧。

筛选后给需要粘贴的单元格区域上色。

关闭筛选，将有色单元格排序。

直接将表2的数据复制粘贴过来即可，这种i方法往往要用到辅助列，不然你表格样式会发生变化。

我们来利用函数问题解决。框选整个粘贴区域，输入公式=SUBTOTAL(3,A2:A2)，在第一个A2处选中按下F4，加上绝对引用，在公式结尾按下CTRL+回车。（大家需要注意，A列数据完成，不能有空白）

SUBTOTAL函数忽略隐藏行计数。

然后我们直接在这个函数前面输入INDEX，点击表2，框选需要复制的区域，加个逗号，嵌套函数最后再补一个反括号。

框选筛选区域，在函数末尾按下CTRL+回车即可，将表2的数据全部引用过来。

磨人多年的筛选后粘贴可以通过多种方法实现，希望大家都能GET到。

希望大家喜欢今天的教学：）拜拜，下课-。-

想快速在公司通讯录查询某个员工的信息，你会怎么办？今天教大家用excel搭建一个自动查询系统，输入任一一个员工的工号或者姓名、就可以查看该员工的信息啦~

员工登记表，因为涉及到的内容较多，每次都很长一行，为了查看，总是需要来回拖动鼠标，创业型的小公司稍微还好一点，如果是那种几百上千号人，看着看着眼睛都要花了。但是我们可以发现，每个人的信息格式其实是一样的，那么只需用在新的一个sheet中设置一个模板样式，然后通过函数公式就可以实现数据自动查询显示了。

步骤演示：

1、新建模板样式表

在sheet2中，创建一个信息查询表，也就是把希望查询后展示出的内容项都列出来。

2、调整输入项工号格式

一般工号都是001开头，如果直接输入001将变成1，所以这时需要设置为“文本”格式

3、配置公式

在姓名栏D4输入公式：=VLOOKUP($D$3,员工登记表!$A$1:$N5,MATCH(C4,员工登记表!$A$1:$N$1,0),0)

那么当输入工号001则该员工的姓名信息就同步过来了。将公式复制到其他需要同步的内容项。

公式说明：

1.MATCH(C4,员工登记表!$A$1:$N$1,0)

指的是依据单元格C4，在A1-N1即整个区域进行查询，反馈对应的列。

2、VLOOKUP($D$3,员工登记表!$A$1:$N$5,MATCH(C4,员工登记表!$A$1:$N$1,0),0)

指的是根据输入的工号（D3）,在员工登记表内进行查询，得到对应的行，再与match函数得到的列，最终反馈交叉的值。

4、容错处理

当改变工号，员工相关信息会随着改变，但也有可能出现单元格信息显示为0，这表示该员工的某项信息在原始登记表中就是空的。可以菜单栏点击文件-选项-高级，去掉“在具有零值的单元格中显示零”选项的勾选，就可以了。同样，日期字段也会显示成数字，改一下格式就可以。

另外，当输入一个不存在的工号时，则会反馈错误。如下图：

这时我们可以在公式前加一个容错函数IFERROR，则D4的公式变为：

=IFERROR(VLOOKUP($D$3,员工登记表!$A$1:$N$5,MATCH(C4,员工登记表!$A$1:$N$5,0),0),"")

回车后，可以看到当输入不存在的工号时，表格为空。

5、照片的动态跟随

选中照片-公式-定义名称。

在弹出对话框输入公式：=INDEX(员工登记表!$D:$D,MATCH(员工信息查询表!$D$3,员工登记表!$A:$A,0))，命名为“照片”，点击确定。

然后在excel选项-快速访问工具栏，将照相机添加进来，这时excel页面左上方会出现照相机按钮。

点击照相机，在“照片”单元格内拖动鼠标，画出方框，编辑公式=照片；那么回车后，工号对应的照片也就显示出来了。

改变工号，对应的信息和照片都会一起随着改变了。

还有一种更简单的实现方式，只需2步！

1、用表单大师制作一个在线员工登记信息表，将需要登记的信息字段都添加到表单中；员工照片可以用文件上传字段来收集

2、表单制作好后，只需设置一个公开查询的功能，输入查询条件，这样就能实现上面那么复杂的操作了。还可以灵活勾选允许查看哪些信息，设置更简单，功能更强大；甚至还可以设置提醒，比如提交信息后，可以提醒hr进行查看呢

两步搞定，就是这么简单

不信，点击试试这套查询系统吧！

今天在论坛有个小伙伴问到一个关于Excel查找的相关问题，但是他的要求是多个条件进行限制，然后匹配对应的数据出来，今天小菜就以这种情况和大家分享关于Excel双（多）条件查找的5种技巧！

现在需求是匹配出营销部的小菜底薪。会发现公司叫小菜的不只是营销部，因此不能做为单一条件去匹配，故此这类我们称多条件查找。

方法1：用SUMPRODUCT函数

公式：=SUMPRODUCT(($A$2:$A$8=E2)*($B$2:$B$8=F2)*($C$2:$C$8))

满足姓名是小菜，部门是营销部，然后对底新列求和。

方法2：用max函数+数组

公式：=MAX(($A$2:$A$8=E2)*($B$2:$B$8=F2)*($C$2:$C$8))，编辑完公式在三键结束。

方法3：Index+Match实现

公式：=INDEX($C$2:$C$8,MATCH(E2&F2,$A$2:$A$8&$B$2:$B$8,0))

Match这里巧妙把两个条件用&连接起来，就变成了一个条件，MATCH(E2&F2,$A$2:$A$8&$B$2:$B$8,0)

方法4：使用lookup函数

公式：=LOOKUP(1,0/(($A$2:$A$8=E2)*($B$2:$B$8=F2)),$C$2:$C$8)

这是lookup一个常用套路=lookup(1,0/((条件1区域=条件1)*(条件2区域=条件2)),(返回的结果区域))

方法5：数据库函数DSUM

此函数有3个参数。第1参数：要引用的数据源；第2参数要求和的列；第3参数：条件，注意一定要引用单元格区域包括列标题字段，如E1:F2

方法还有其他的，希望这些方法能解决大家的问题，如果OK，记得点个赞，分享个朋友圈，这样小菜也有更加的动力为大家写文章……

如果你是新朋友，扫码关注下方二维码，便每天可以和小菜一起学习，一起提升技能！当然大家也可以技巧分享，学习更多办公技巧哦！

每天一起学习，一起进步。

Excel竟然还有还有这么一个神奇的功能，自动填充序号，效果如下：

这是效果图，据体操作步骤如下

喜欢的朋友帮忙点个赞，有什么疑问可以给我留言，关注我的头号会有更精彩的教程

或是加Excel学习群（226948776）

考勤表、绩效表、补发补扣表、社保表、

员工工资表、工资发放汇总表

.......

各式各样的Excel表格，

是HR焦头烂额、各种加班的罪魁祸首。

不过你可知道，

Excel隐藏很多令人神往的秘技，

想让自己不加班？那就赶快来看一看吧。

1. 提取姓名、手机号

想拆分姓名和手机号码？先输入第一个姓名，选取它和下面的空行，按Ctrl+E，一秒搞定拆分（提取手机号码同样方法）

2、对比同行数据是否相同

选取两列后，按Ctrl+

3、隔列粘粘数据

复制 -选择性粘贴 -跳过空单元。可以跳过空列/行粘贴。

4、恢复E+的数字格式显示

数字长度如果大于11位会以科学记数法显示，设置自定义格式0可以让数字正常显示出来。

5、公式转数值

按CTRL键分两段选取并复制，选取要粘贴的位置，按Enter即完成转换

6、转换日期

不规范的数字和字串，用分列可以快速转换成标准的日期格式

7、快速合并多行数据

把列拉宽 -选取数据 ，开始 -填充-两端对齐（Excel2003是内容重排）

8、快速跨表复制粘贴

按shift选取多个工作表，选取工复制的表格区域，开始 -填充 -成组工作表。

9、快速设置求和公式

Ctrl+g定位空值 -按alt+=一键设置求和公式

10、快速选取公式中的区域

当我在设置Sum求和公式时，引用区域只能用光标向下拖.....有时懒省事直接设置一个足够大的行区域。从没想过有简单的方法。

（怎么不设置=sum(b:b),原因是如果B列还有其他不需要求和的数据呢）

其实在公式中选取区域时直接按ctrl+shift+↓就OK了。

你以为这就结束了？

赶紧消化上面的内容，

更牛的技能要来了！！！！

一、员工信息表公式

1、计算性别(F列)

=IF(MOD(MID(E3,17,1),2),"男","女")

2、出生年月(G列)

=TEXT(MID(E3,7,8),"0-00-00")

3、年龄公式(H列)

=DATEDIF(G3,TODAY,"y")

4、退休日期(I列)

=TEXT(EDATE(G3,12*(5*(F3="男")+55)),"yyyy/mm/dd aaaa")

5、籍贯(M列)

=VLOOKUP(LEFT(E3,6)*1,地址库!E:F,2,)

注：附带示例中有地址库代码表

6、社会工龄(T列)

=DATEDIF(S3,NOW,"y")

7、公司工龄(W列)

=DATEDIF(V3,NOW,"y")&"年"&DATEDIF(V3,NOW,"ym")&"月"&DATEDIF(V3,NOW,"md")&"天"

8、合同续签日期(Y列)

=DATE(YEAR(V3)+LEFTB(X3,2),MONTH(V3),DAY(V3))-1

9、合同到期日期(Z列)

=TEXT(EDATE(V3,LEFTB(X3,2)*12)-TODAY,"[

10、工龄工资(AA列)

=MIN(700,DATEDIF($V3,NOW,"y")*50)

11、生肖(AB列)

=MID("猴鸡狗猪鼠牛虎兔龙蛇马羊",MOD(MID(E3,7,4),12)+1,1)

二、员工考勤表公式

1、本月工作日天数(AG列)

=NETWORKDAYS(B$5,DATE(YEAR(N$4),MONTH(N$4)+1,),)

2、调休天数公式(AI列)

=COUNTIF(B9:AE9,"调")

3、扣钱公式(AO列)

婚丧扣10块，病假扣20元，事假扣30元，矿工扣50元

=SUM((B9:AE9={"事";"旷";"病";"丧";"婚"})*{30;50;20;10;10})

三、员工数据分析公式

1、本科学历人数

=COUNTIF(D:D,"本科")

2、办公室本科学历人数

=COUNTIFS(A:A,"办公室",D:D,"本科")

3、30~40岁总人数

=COUNTIFS(F:F,">=30",F:F,"

四、其他公式

1、提成比率计算

=VLOOKUP(B3,$C$12:$E$21,3)

2、个人所得税计算

假如A2中是应税工资，则计算个税公式为：

=5*MAX(A2*{0.6,2,4,5,6,7,9}%-{21,91,251,376,761,1346,3016},)

3、工资条公式

=CHOOSE(MOD(ROW(A3),3)+1,工资数据源!A$1,OFFSET(工资数据源!A$1,INT(ROW(A3)/3),,),"")

注：

A3:标题行的行数+2,如果标题行在第3行，则A3改为A5

工资数据源!A$1:工资表的标题行的第一列位置

4、Countif函数统计身份证号码出错的解决方法

由于Excel中数字只能识别15位内的，在Countif统计时也只会统计前15位，所以很容易出错。不过只需要用&"*"转换为文本型即可正确统计。

=Countif(A:A,A2&"*")

五、利用数据透视表完成数据分析

1、各部门人数占比

统计每个部门占总人数的百分比

2、各个年龄段人数和占比

公司员工各个年龄段的人数和占比各是多少呢？

3、各个部门各年龄段占比

分部门统计本部门各个年龄段的占比情况

4、各部门学历统计

各部门大专、本科、硕士和博士各有多少人呢？

5、按年份统计各部门入职人数

每年各部门入职人数情况

一个不精通Excel的HR，不是一个合格的HR，学会小编传授的Excel“真功夫”，专业维度、准确计算、精美排版，让你瞬间变身HR资深老司机！

文章来源：企飞人力（ID：qifeirenli）智能薪酬服务专家

每天加班到深夜，有的时候并不是你敬业，而是你效率低下。不加班的财务才是好财务！

那怎么样才能学好Excel，早做完不加班呢？

1.多读书

书籍一般都是高手经过多年的心血总结而来，系统而全面。有的书籍还会提供一些学习方法，通过看书你可以少走很多弯路。Excel类书籍，卢子起码读了20本。

多看书，多看经典的好书！

2.多练习

将你从书中所学运用到工作中，这样你才能记得牢固。但有的人会说，工作上用Excel的地方并不多，怎样才能练习？

卢子也曾经有这种苦恼，后来想到了解决的良策，在Excel论坛、交流群，每天帮人答疑。帮助别人的同时，自己也巩固了知识。

这就是传说中的教学互长！

下面就是答疑系列：

01 增值税专用发票号都为八位数字，统一补足位数。

在核对增值税进项税金时，工作人员失误将发票号转变数值后使得一些发票以0开头的都出现错误，现在通过比较已将那些缺失的发票号找出请教如何一次性实现补足发票号，不用手工一个个改。

论坛的朋友提供了一系列公式：

职称酱看过后看见，补充了一个TEXT函数：

=TEXT(A2,"00000000")

如果要装得有点技术，可用：

=TEXT(A2,REPT("0",8))

REPT函数就是将0重复显示8次。

重要的事说三遍，财务必须：细心！细心！细心！

=REPT("细心！",3)

02 根据岗位，获取补助金额。

一级领导补助1000，二级领导补助800，一般人员补助600。

现在只是分成3个岗位，直接用IF函数就能解决。

=IF(B2="一级领导",1000,IF(B2="二级领导",800,600))

如果岗位比较多的情况下，建立一个对应表，然后用VLOOKUP函数查询。

03 对系统导出来的金额求和。

正常情况下，从系统导出来的金额都是文本格式，直接用SUM函数求和出错，需要转换成数字格式才可以。

04 统计每个月份的金额。

明细表只有每一天的数据，如果要统计每个月份的金额，正常是先添加一个辅助列获取月份，而借助数据透视表就能一步到位。

数据透视表简直就是神器，直接借助勾选与创建组2个功能，10秒钟就完成了按月份统计。

05 查询项目对应的金额。

正常查询对应值都是用VLOOKUP函数，但这里全部出错，什么原因呢？

其实如果细心观察，你会发现D列的项目前面有空格，只要将这些空格替换掉就可以。当然也可以用TRIM函数去除多余的空格，再用SUMIF函数查找金额。

=SUMIF(A:A,TRIM(D2),B:B)

这个函数有一个很大的好处，就是查找不到金额，就返回0，而不是显示错误值。

06 隔列统计每个月的预算。

=SUMIF($B$2:$I$2,"预算",B3:I3)

一. 谁的青春不迷茫

刘同写过一本书叫做《谁的青春不迷茫》。此刻，很想知道，从大学毕业走上职场的你们，青春是否迷茫？

上班打酱油，下班后刷朋友圈，熬夜打个游戏，吃着薯片刷个电影，没有兴趣爱好，生活没有规划，不想早起，不想读书，不想健身与思考，周末葛优躺，静静的发呆，内心无比空虚。几年后回首，感觉职业上没进步，身体健康越来越差，满满的迷茫，孤独与焦虑。

你是否就像这样，每天浑浑噩噩，随波逐流，内心几千头草泥马从心中奔腾而过，为生活焦虑，内心也曾想改变，可是就是找不到方向，此刻的人生就像大海中的小船，没有航标，随风飘荡。毫无意义的耗费着宝贵的青春。

有时候，很多年轻人并不想下班后葛优躺玩手机，其实内心真的很想改变自己，可是却找不到一个可以改变的点。有时候是否感觉到，自己好像拖着疲惫受伤的身躯，漫无目的行走在苍茫的荒原上，不知道去向何方？

《你的孤独，虽败犹荣》中写到，你脸上云淡风轻，谁也不知道你的牙咬得有多紧。你走路带着风，谁也不知道你膝盖上仍有曾摔伤的淤青。你笑得没心没肺，没人知道你哭起来只能无声落泪。要让人觉得毫不费力，只能背后极其努力。要坚信，我们没有改变不了的未来，只有不想改变的过去。

谁的青春不迷茫，跌跌撞撞的才是青春！

二. 我为什么想成为斜杠青年

「斜杠」一词源于英文的slash，这个概念出自2007年《纽约时报》专栏作家 Marci Alboher 写的一本书《One Person/multiple careers》: A New Model for Work Life Success。她在书中提到说，如今越来越多的年轻人不再满足“专一职业”这种单调枯燥的生活方式，而是开始选择一种能够拥有多重职业和多重身份的多元生活。这些人在自我介绍中会用斜杠来区分不同职业。

那么，作为普通人的我，为何想成为一名斜杠青年？

1.丰富业余生活

在北上广这些不相信眼泪的城市中，每个人的工作很忙，但是真的有那么忙吗？如果花时间来统计下，下班后玩手机（王者荣耀，微信聊天，刷朋友圈，刷剧……）的时间，每天最少有2-3小时。每当一遍一遍刷朋友圈时，刷完时是否有一种莫名的空虚？

20多岁的我们，难道不想业余生活丰富一些吗？

喂，骚年，再不疯狂就老啦。现在，不去做想做的事，以后估计就没机会啦。我不想天天下班后，漫无目的消耗人生的美好时光，这不是我想要的生活。很渴望丰富自己的业余生活，人生变得更加有意义。

2.开拓视野和格局

如果只是从事自己的本职工作，生活交际圈是越来越窄。同时，视野和格局的局限性也越来越明显。如果不适当跳出狭窄的视野，人生或许会被定格。

为了开拓视野和格局，我决定学一项技能。当你真正下定决心的时候，你会通过各种途径来寻找相关领域内的牛人，会了解到他们的技能背后的各种故事。认识不同领域的人，学不同领域的知识，会使你的视野和格局更加开阔。

3.变现

当把兴趣变为一项硬技能后，可以通过很多途径变现。比如微课，接广告，培训等等。如今，物价飞涨，房价虚高，每一个年轻人都非常浮躁，难以找到心灵的慰藉。如果找到一门技能，快乐学习的同时，还可以赚到钱，人生岂不完美？

当你有了技能后，你会感受到自己的价值所在。同时也会更有利于勾搭各领域的大牛。

三. 我如何找到了Excel作为自己的第二职业

1.多尝试，挖掘自己的兴趣点

笔者2015年6月毕业时，懵懵懂懂的来到了魔都，担任IT系统工程师，当时只想着学点IT技术。工作一段时间后发现，如果每天都是这样，难免生活太过于单调。尤其在魔都，赚的工资刚刚够自己生活。如果一直这样浑浑噩噩下去，不如早点离开魔都，去一个三四线城市。

跟大部分朋友一样，笔者来自于北方的一个小城市，小时候也没学过其他特长。工作后，每天都很浮躁，不知所措。总感觉生活缺少点什么。后续，陆陆续续加入了一些社群，偶然的机会了解到office软件魅力所在，开始在锐普社群中尝试PPT制作，学习，开公众号分享自己所学所得。

做了一段时间后，感觉自己在PPT设计，美感方面都很差。

2016年，大学好友星爷来魔都出差，聊了聊如今自媒体的发展现状等。由于工科出身，学Excel有先天优势，决定抱星爷大腿主攻Excel领域，辅助PPT领域。

我们如今的二十七八的年龄，说太多的理想和梦想，难免太过于空洞。找一件自己喜欢的事，不要考虑太多，就是闷着头直接干！

2.多参加活动结交朋友

如今网络如此发达，社群又非常火爆。你是否加入了一些社群来勾搭牛人 ，从而拓展自己的格局和视野？

2016年初开始，偶尔的机会加入了BetterMe大本营，参加了很多线上和线下活动。认识了很多青年才俊，会绘画，设计，演讲等等，自己变的更加热爱生活。认识了小米，安仔，白老师，锦边墨，乌素，秦老师，徐敏，kate，哈默等等…..

认识了她们，我才知道世界和生活原来可以这么精彩.

人生不止有眼前的苟且还有诗和远方…

四. 我是如何一步一步强化自己的第二技能

1.买书，买课程，报名微课等进行修炼，增强自己的第二技能

读史使人明智，读诗使人灵秀，数学使人周密，科学使人深刻，伦理学使人庄重，逻辑修辞使人善辩，凡有所学，皆成性格。 ———— （英国）培根

每当自己心浮气躁时，千万不要葛优躺，也不要朋友圈刷几十遍。如今对于年轻人而言，最大的投资就是投资自己。我们出生在一个很好的时代，因为互联网，知识比之前容易变现了。在我确定了自己的第二技能后，买了很多本书和网络课程。丰富自己。

别犹豫，别纠结，时间不等人！一句话，就是闷着头，直接干！干！

那些年，雷哥买过的Excel书

2. 适当输出

有了很多输入，没有输出是无法加深理解的。输出是最好的输入。

雷哥看了很多Excel图书，买了很多网络课程，在学习的同时，会把学到的知识放在自媒体平台上，分享给有需要的小伙伴。同时，一年来，为很多微信好友，网友等无偿答疑，解决了很多问题，同时也丰富了自己的知识体系。

另外，还有一种非常好的输出方式是，去相关领域的论坛（比如，Excel方面的可以去Excelhome论坛）答疑，帮助其他小伙伴。帮助别人的时候，自己也加深了理解，加速了成长。

赠人玫瑰，手留余香！

经历了各种尝试和参加了各种活动后，经历了各种尝试，有了自己的兴趣定位后，后面就是坚持学习，坚持分享，分享包括微课，输出文章，帮网友答疑等等。

雷哥经历了一些尝试后，开始抱星爷的大腿，主攻Excel方向。不断的买课买书进行学习，在自媒体平台输出文章。坚信 “有志者事竟成”。

3.坚持

《少有人走的路》中写到，自律是消除人生痛苦的重要手段。唯有自律，坚持，才能让你有所收获和成就。前段时间很火的《人民的民义》，他们不仅演技惊艳四座，而且身材保持的也是杠杠的。电视剧的火爆，更是折射出老戏骨们对演技和健身的坚持不懈地努力。

雷哥确定学习Excel等后，就开始利用业务时间，坚持看书，看公众号，发文章等方式来坚持。

Where there is a will, there is a way!

附图：Excel方面的一些证书

Microsoft Office Specialist Expert

丰富业余时间的同时，工作方面也没放松，考取了两个证书

另外，除了工作，技能学习方面，今年也注意了身体的健康。控制饮食，加强锻炼。年度体检由2016年的三项异常，到今年的体检指标0异常。

五. 我在什么动因下开始出书力作

人外有人，天外有天。虽然牛人很多，我们作为普通人中的一员，如果可以保持勤勤恳恳，相信机会是留给有准备的人的。我们无需轰轰烈烈，最起码自己退休以后，往事回首，有很多值得回味的人生财富！

对于还在提升初步期的朋友，要沉下心来静心修炼，丰富自己的兴趣的知识结构和体系。沉淀一段时间后，建议联系一些平台做微课分享。另外，也可以做一些小范围线下的分享。

2016.7月左右，星爷给我打电话说，他收到的电子工业出版社的写书邀请，问我要不要一起。一方面，当时特别犹豫，感觉自己实力一般，另外由于自己是跨行就业（IT系统工程师），毕业一年，工作压力也很大。另一方面，感觉也是一个很好的促进学习Excel的机会。最后，抱星爷的大腿，和他一起开始了写作之旅。

从2016.7到如今的2017.7，每天工作十个多小时后，回家还要打开电脑开始写作，经历了多少通宵达旦，其中的艰辛只有自己知道，其中的喜悦也只有自己可以深入体会到。一句话，痛并快乐着。一年的心血，一年的积累，终于和星爷完成了一本拙作。

总之，没有改变不了的未来，只有改变不了的过去。从这一刻开始，心动，行动，全新的你就会诞生。

每一个普通的白领都可以成为斜杠青年，我的经验你可以复制，我可以成功，那么你也可以成功。

今天介绍Excel里面两个强大到逆天但很多人还不知道的功能，不信你看看？

一、二维表转一维表

如下图所示，如果你在工作中遇到这种表格转换需求，会怎么做呢？

思考10秒钟......

我见过最多的办法就是......复制粘贴，没错，大量重复繁琐操作最后搞的人头晕眼花脑发胀！

看完下面这招，不花1分钟你就可以轻松搞定。

步骤1：单击表格内任意区域，单击【数据】-【从表格】

步骤2：选中最左列，单击【转换】-【逆透视其他列】

步骤3：这时你会惊奇的发现，Excel变魔术一样的得到了如下图所示的表格！

这时只需单击【开始】-【关闭并上载】即可完成。

步骤4：得到如下所示的表格，正是你想要的。

整个过程的操作演示动画，请点击如下gif观看

你没看过，平时那么繁重的工作，用Excel 2016就这么快捷！

如果你觉得Excel 2016只有这点本事的话，那你可太小看它啦，下面来个更逆天的，让你大开眼界！

二、拆分复杂数据列并整理为规范表格

如果要你把下图左侧的表格变成右边的样子，你该怎么办呢？

按空格分列然后一个个粘贴？数组公式？VBA编程？

别想啦，有了Excel 2016，分分钟搞定它，看步骤吧。

步骤1：选中表格中任意单元格，单击【数据】-【从表格】

步骤2：选中需要拆分的列，单击【拆分列】-【按分隔符】

步骤3：选择按空格拆分，单击确定按钮。

拆分后的样子如下图所示

步骤4：选中作者所在的多列，单击【转换】-【逆透视列】

Excel大变魔术，瞬间得到如下图所示的表格

步骤5：这时只需将没用的列删除即可

得到想要的表格后，单击【开始】-【关闭并上载】，大功告成

得到想要的表格

整个操作过程其实很简单，30秒足以搞定，点击以下gif动态图片即可查看

Excel是不是比你想象中更强大，这些技巧既简单又实用！

1

排版篇

给他人发送Excel前，请尽量将光标定位在需要他人首先阅览的位置，例如Home位置（A1），例如结论sheet，长表尽量将位置定位到最顶端

有必要的时候请冻结首行；没必要但可追究的内容，可以隐藏处理

行标题、列标题加粗，适当处理文字颜色、填充颜色，利人利己，可参见：Excel 中的颜色要怎么搭配，视觉上更能接受且区分度高？ - 设计

占用空间比较小的表格，可以放置在左上角，但留空A列和1行，并给表格加上合适的框线，观感很不错哦~

同类型数据的行高、列宽、字体、字号，求你尽量一致，非要逼死强迫症吗！

定义好比较标准的格式，例如百分比预留几位小数，手机号的列宽设置足够，时间显示尽量本土化...

不要设置其他电脑没有的字体，除非这个表格就在这一台电脑使用...

2

操作篇

Alt+Enter在表格内换行，楼上有提到

Ctrl+Shift+上/下，选择该列所有数据，当然加上左右可选择多列

Ctrl+上/下，跳至表格最下方

Ctrl+C/V，不仅仅复制表格内容，也可以复制格式和公式！

Ctrl+D/R，复制上行数据/左列数据

还有个很好用的单元格格式转换，推荐大家用熟

（有点不清晰...当初偷懒直接把图片截到印象笔记的...）

Ctrl+F/H的查找、替换，点击“选项”，可以替换某种格式等等，另一片天地有木有！

F4，对，你没看错，就是F4！重复上一步操作，比如，插入行、设置格式等等频繁的操作，F4简直逆天！

（分号后面那个） 比如输入网址的时候，一般输入完会自动变为超链接，在网址前输入’就解决咯

复制，选择性粘贴里面有几个非常好用的——仅值，转置（个人推荐用transpose公式）

公式里面切换绝对引用，直接点选目标，按F4轮流切换，例如A1，$A$1，$A1，A$1

3

公式篇

if、countif、sumif、countifs、sumifs，这几个一起学，用于条件计数、条件求和

max、min、large，这几个一起，用于简单的数据分析

rand、randbetween，这俩一起，用于生成随机数，也可以用于生成随机密码（用rand配合char可生成中英文大小写随机的）

定位类型的函数：MID、SEARCH、LEN、LEFT、RIGHT一起学吧，简单但异常实用

四舍五入个人偏好用round函数，举个简单例子，一列数据，2.04、2.03并求和，显示保留1位小数，你会在界面上看到2.0、2.0，求和却是4.1，表格打印出来会比较让人难理解

subtotal：用于对过滤后的数据进行汇总分析

sumproduct：返回一个区域的乘积之和，不用A1*B1之后再下拉再求和

Vlookup函数，这个不多说了，神器；另外推荐lookup函数：LOOKUP(1,0/(条件),查找数组或区域)

offset函数，常用于配合其他函数使用，例如想将10*20的表中的每行复制成3行按原顺序变成30行：=OFFSET($A$1,INT((ROW(A1)-1)/3),COLUMN(A1)-1,1,1) 下拉，由于不用到列，所以等同于=OFFSET($A$1,INT((ROW(A1)-1)/3),0)，我当初是这么做笔记的....：=（A1，向下偏移（向下取整（行数-1）/3），向右偏移0）

text，例如19880110 text（A1，"0-00-00")，转为1988-01-10，用法很多

weekday，让你做时间计划表什么的时候，把日期转为“星期X”

column（目标单元格），返回目标单元格所在列数，有时候真的很好用...还有 @黄老邪推荐的columns

transpose（目标区域），神奇的转置，把行变成列，把列变成行...

&，可在目标单元格后面增加某些字符，偶尔用（我这种强迫患者用的是concatenate公式，我特么有病！）

数组，虽然复杂，但是有的公式配上数组简直爽爆

多百度，例如曾经碰到一个难题，把X分X秒，转为X秒，例如172分52秒，百度半天得到的公式：=IF( IFERROR( FIND( "分", $E2 ), 0) > 0, LEFT( $E2, FIND( "分", $E2 ) - 1 ) * 60 + IFERROR( MID( $E2, FIND( "分",$E2 ) + 1, FIND( "秒", $E2 ) - FIND( "分", $E2 ) - 1 ), 0 ), LEFT( $E2, FIND( "秒", $E2 ) - 1 ) * 1 ） 度娘很厉害的（评论里面直接用=TEXT(SUBSTITUTE(SUBSTITUTE("00:"&A1,"秒",""),"分",":"),"[s]")，把文本转为时分秒的标准格式再转秒，确实是更好的方法）

4

图表篇

不同的场景请用不同的图，转个非常精髓的图：

数据透析表、数据透析图，嗯嗯，推荐的人太多了...

图表设计——布局，灵活运用好多类数据时的“次坐标轴”

选择数据——右键——更改图标类型，灵活在一张表上结合起来柱状图和折线图

5

技巧篇

数据——分列，将列内的数据拆分成多列，比如“XXX省XXX市”，拆成省、市两列，“XX小时XX分钟”拆成时、分两列，可以按照宽度、文本、标点等作为界定进行拆分，非常多的场景会使用到，请优先学会...

如果你不是靠Excel吃饭，请不用那么geek，而是学会Excel的逻辑——配合简单的公式、排序、替换、if等全局操作能得出的结果，不一定非要用一个长公式然后下拉，举例：

如何将无规律的一列上下翻转？

——创建一列，标上1、2、3……，下拉，以该列为主排序，改升序为降序，扩展目标列，得到结果，之后可以删掉创建的辅助排序

如何将目标区域的每一行数据下面插入一条空行？

——创建一列，标上1、2、3……，下拉，下面空白行标上

1.5、2.5、3.5……下拉，同理排序~Tada~

条件格式——突出显示单元格规则，里面的“重复值”，在实时录入和检查标记时很实用

在条件允许的情况下，升级到office 2013吧，excel 2013比2010好到爆啊！比如新增的sumifs、averageifs等多条件if，比如选择一个区域，右下角小标“快速分析”自动生成数据条、色阶、柱形图、汇总图、透视表、折线图等等啊，秒中出啊有木有！（诶，好像哪里不对的样子）

6

插件篇

Power Map ：在线地图+在线演示+制作视频，随便来个中国壕热力图：

当然，也有柱状图：

Power View：带可视化交互效果的图表，很适合演示

默认配色就很不错，而且演示的时候点击时会直接按你点击的类型帮你显示对应的数据（例如上面的堆积柱状图）

Power Query：这个用法很多，我主要用于以下两点：

1、在线Web抓取：不需要学会某个编程语言也能爬虫+分析一些简单数据，随便举个例子输入新浪股票的网址，它自动帮忙抓取到N个表，我随便打开一个：

2、连接数据库：不需要学会SQL语法也能查询+分析数据库内的数据，这个就不方便截图了...

打比方说，常见的 select * from ... where xxx = xxx and xxx>xxx group by xxx这种sql语法查询的内容，可以在Power Query中直接通过点击、筛选等操作就列出来

各种Excel工具箱，这个不多介绍了，不常用，也就不打广告了，但是挺适合部分长期使用Excel的职场人士使用

SmartArt也是一大神器，我终于不用在Ai或者PPT上作图再粘过来了...

7

其它篇

不会写宏没关系，要懂得怎么使用别人的宏（自行百度“excel宏大全”吧~），怎么保存xlsm，怎么录制宏。当你把机械化的一套操作通过录制宏实现，并用xlsm配合auto_open自动操作，眼看表格自动化操作，在两秒内给你返回原来每天固定要做十几分钟的数据分析结果时，那个鸡皮疙瘩（蔡健雅腔调...）

有时间推荐泡泡Excel的论坛，Excelhome什么的，神人太多了....

非常推荐给初学者、中阶以及“中介”的视频：文库课程，哪怕前几个视频，看标题简单到爆，但中间真的有很多操作上的亮点不为普通人自学所知~

Excel满足不了你，又懂编程，想秀逼格的，请右转百度 SPSS

[完]

大作头照

会计对账1

会计对账2

[续集]

有人因为PPT做得太丑被开除，不用说大家都知道是谁。

还有人因为Excel太菜被开除，而且简历上往往写着精通Excel，这真的很讽刺。

Excel的重要性不言而喻，不过很多人抱怨：想学不知道上哪学，报个班又怕浪费钱。

没关系，趴姐精心整理了5大Excel免费学习网站，既能系统的学习Excel教程，又能解决临时遇到的难题，非常好用！

趴姐绝对不是在打广告哦，人家又不给钱，我只是把有用的东西分享给你们。

接下来，我们进入正题：

（排名不分先后）

1、Excel学习网

网址：http://excelcn/

Excel学习网是一项公益学习的项目，里面的内容基本都是Excel爱好者自己产出的，很多的小技巧是用户亲身体验总结出来的，非常具有实用性。

它最吸引我的地方，在于无需注册，完全免费！

虽然它的界面不是很简洁，缺乏美观感，但因为是专业的Excel学习网站，受到很多用户的青睐。

趴姐最喜欢它的视频教程和Excel模板这两部分，特别是Excel模板有时候真的能解燃眉之急。

2、ExcelHome

网址：http://excelhome/

ExcelHome相对于Excel学习网来说，网页界面更简洁，用起来更舒服，体验感有很大的提升。

它的Excel教程也非常全，视频版、图文版、动画版等等，同时还有模板、office软件下载，而且，它也按office的版本推荐视频教程。

很尴尬的，是它的视频教程片头都会有Excel书籍广告，如果你不care，就完全没问题。不过，如果它的视频来源是腾讯视频的话，你就要忍受长达75秒的广告，相信绝大多数人忍受不了。

3、网易云课堂

网址：http://study.163/

之所以推荐网易云课堂，因为我用过之后确实不错。

Excel学习内容很全，既有初级版，也有高手进阶版，如果你只想专攻Excel某一板块，也能找到详细的教程。不过，缺点是免费课程较少，很多深入的课程都是收费的。

我觉得很赞的是，它的网页操作体验非常好，没有很多复杂的东西影响体验。了解的人都知道，网易出品都是精品，我信赖它的品质。

4、Excel精英培训

网址：http://excelpx/

Excel精英培训是主打论坛交流的形式，遇到不懂的难题，可以在论坛里上传Excel压缩文档向高手求助，这不失为学习交流的好方式。

趴姐认为，它并不适合初学者，内容比较零散不够系统化。当然，网页界面体验也就不舒服，各种小广告也是蛮多的。

5、我要自学网

网址：http://51zxw/

我要自学网是一家综合性的计算机软件学习网站，Excel只是其中非常小的一部分，小到趴姐只能在首页密密麻麻的推荐中，找到几个教程。

不过，趴姐依然把它推荐给大家肯定是有原因的，因为体验了Excel2013视频教程，视频很清晰，讲师讲解得也不错，内容也很系统。

所以，大家可以忽略它的界面和操作体验感，安安静静认真学习就好。