excel辅助列公式使用教程

2017-04-08 13:12:03 来源：网络作者：豆渣

文 | 卢子

一篇能够将你洗脑的文章，改变只需一瞬间！

你懂Excel吗？

写Excel技能的人千千万万，但我要做那个唯一。每一篇文章都融入我内心的想法，让我每天写10个技能，我可以写1001天，但那时你依然不会有任何改变，这种叫做：分~享~不~看~系~列。

Excel不仅仅是一个垃圾的办公软件那么简单，而是一个可以完成你梦想的软件。畅销书《晨间日记的奇迹》就是采用Excel来做的。

但你真的懂Excel吗？你以为学会了几个菜单的操作、几个函数就是会Excel吗？错了，大错特错。即使你精通了所有功能的使用，依然是门外汉，你不懂Excel。Excel是用来帮你解决问题，比如你要知道自己的每个月或者每年的消费，你逐一记录在Excel，30秒就可以完成你想要的结果。

你在玩Excel，还是Excel在玩你。

你要做大神，表格任你设计，你要做卢子，表格听我的！

年少无知，自己犯下的错，要自己承担。

成长是需要付出代价的，多从别人那里获取失败的经验，这样你就会少走很多弯路。闭门造车是不可取的，要不终将付出惨重的代价！

卢子接手中山隆成那边的工作，中山隆成有事先设置好的表格模板。

像这种二维表格经常会见到，好处就是录入数据简单。那时没有考虑到以后要对数据源进行汇总分析，也就没去重新设置表格模板。虽然表格存在很多不合理，如合并单元格，因贪图一时之便，后来害死自己。

后来领导要卢子对不良进行统计，好查看每个月各类产品的出货检查情况。通过这张明细表进行汇总，得到这样一张表。

通过明细表获得这张汇总表，对当时的卢子来说难度难以上青天，暂时为了应付领导利用了各种转换方法，费了九牛二虎之力才勉强可以完成，效率极低。

为了寻求更方便快捷的方法，苦思一周无果。

不得以在网络上各处求助，终于皇天不负有心人，在wangjguo44老师的帮助下完成了这项艰巨的任务，在这里对他老人家说一声：谢谢！

其中一条公式：

=INDEX(小天使!G:G,RIGHT(TEXT(LARGE(MMULT((LOOKUP(ROW($5:$136),IF(小天使!$B$5:$B$136<>'',ROW($5:$136)),小天使!$B$5:$B$136)=$B$4)*(MONTH(小天使!$I$4:$DJ$4)=$F$4)*(小天使!$G$5:$G$136<>'检查数')*(小天使!$G$5:$G$136<>'其它')*小天使!$I$5:$DJ$136,ROW($1:$106)^0) (LOOKUP(ROW($5:$136),IF(小天使!$B$5:$B$136<>'',ROW($5:$136)),小天使!$B$5:$B$136)=$B$4)*(小天使!$G$5:$G$136<>'检查数')*(小天使!$G$5:$G$136<>'其它')*(小天使!$G$5:$G$136<>'')*0.1 ROW($5:$136)%%,ROW(A1)),'0.0000'),3)*1)

我的天啊，这些是人看的公式吗？简直就是神才能看得懂的公式。卢子也懒得去看，拿来就用，而且一用就是一年，期间都不敢再去看公式一眼。

一年后的某一天，卢子不甘心使用看不懂的公式，所以开始对数据源进行小改动，然后设置公式。添加一列辅助列获取俗称。

辅助列公式为：

=LOOKUP('座',C$5:C5)

座是一个接近最大的文本，查找最后一个满足条件的文本就用“座”，查找最后满足条件的数字就用9E 307。

在不确定为文本或者数字的情况下，可以用通用公式：

=LOOKUP(1,0/(C$5:C5<>''),C$5:C5)

另外考虑到日期会跨年，在月度不良统计表增加一个年份作为筛选的条件。

现在设计公式就稍微简单点，毕竟可以不用去想合并单元格的事儿。

其中一条公式：

不良描述使用多单元格数组，选择区域B10:B16输入公式，按三键结束。

=INDEX(小天使!H:H,MOD(LARGE(MMULT((I4&F4=TEXT(小天使!J4:NJ4,'em'))*(小天使!A5:A162=B4)*(小天使!H5:H162<>'检查数')*(小天使!H5:H162<>'其它')*小天使!J5:NJ162,ROW(1:365)^0)*10^4 (小天使!A5:A162=B4)*(小天使!H5:H162<>'检查数')*(小天使!H5:H162<>'其它')*(小天使!H5:H162<>'')*ROW(5:162),ROW(1:7)),10^4))

虽然说字符减少一半，但还是很难，有木有！

这可怎么办，如果让别人看到这些公式，依然只有晕菜的命，愁啊！

公式虽然很好用，但对于不规范的数据源使用公式还是很鸡肋。对于大多数人而已，要让他设置如此复杂的公式，还不如让他去死好了。

七年以后再回首，来自卢子内心的呐喊！

你Excel这么牛B，BOSS知道吗？

2016年某天，群内聊天感悟。

大神们，怎样将左边的格式批量转为右边的格式？

我的天啊，你这是要累死队友的节奏！

卢子发了感概后不再发言，而其他群友各出奇招。

有人说用分列再结合Word，有人说借助VBA，不过都是提建议没有出手。

最后有好事者帮忙写了一个很牛B的数组公式，输入公式后需要按Ctrl Shift Enter结束，跟普通公式不一样。

=MID(LEFT($A2,SMALL(IF(FREQUENCY(ROW($1:$49),((MID($A2,ROW($1:$49),1)>'Z')=(MID($A2,ROW($2:$50),1)>'Z'))*ROW($1:$49)),51,ROW($1:$50)),COLUMN(A1))),SUM(LEN($B2:B2)) 1,99)

我不禁产生了疑惑，你们用Excel是来干什么的？难道就是为了折腾自己，同时折腾别人？

这样的数据源，即使提取出来数据后，又有什么用途呢？可以直接对各种材料统计数量吗？当然也是可以的，再花很长的时间，写一个超级公式或者VBA的代码，但这样有意义吗？

就以这个例子来说，完成各种统计，至少要半天时间才可以，如此的低效率你老板知道吗？职场上并不存在任何炫耀技能，老板要的是效率！效率！除了效率还是效率！！！准确而又高效。

去年我去新公司应聘，老板看见我的书名《Excel效率手册早做完，不加班》就破格录取我了，仅仅就是一个书名。因为他实在太喜欢这个书名，现在要的是高效率。

对于刚才的问题，如果我制作表格，我一分钟就能完成各种统计分析。

1分钟 VS 半天

来看看卢子是如何做表的，跟如何统计的？

以表格的形式记录数据，一个单元格一种格式，分成2列显示。