excel辅助列公式使用教程.docx
《excel辅助列公式使用教程.docx》由会员分享，可在线阅读，更多相关《excel辅助列公式使用教程.docx（11页珍藏版）》请在冰点文库上搜索。
excel辅助列公式使用教程
excel辅助列公式使用教程
文|卢子
一篇能够将你洗脑的文章，改变只需一瞬间！
你懂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））（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.1ROW（$5:
$136）%%,ROW（A1））,’0.0000’）,3）*1）
我的天啊，这些是人看的公式吗？
简直就是神才能看得懂的公式。
卢子也懒得去看，拿来就用，而且一用就是一年，期间都不敢再去看公式一眼。
一年后的某一天，卢子不甘心使用看不懂的公式，所以开始对数据源进行小改动，然后设置公式。
添加一列辅助列获取俗称。
辅助列公式为：
=LOOKUP（‘座’,C$5:
C5）
座是一个接近最大的文本，查找最后一个满足条件的文本就用“座”，查找最后满足条件的数字就用9E307。
在不确定为文本或者数字的情况下，可以用通用公式：
=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））*10（小天使!
A5:
A162=B4）*（小天使!
H5:
H162’检查数’）*（小天使!
H5:
H162’其它’）*（小天使!
H5:
H162’’）*ROW（5:
162）,ROW（1:
7））,10））
虽然说字符减少一半，但还是很难，有木有！
这可怎么办，如果让别人看到这些公式，依然只有晕菜的命，愁啊！
公式虽然很好用，但对于不规范的数据源使用公式还是很鸡肋。
对于大多数人而已，要让他设置如此复杂的公式，还不如让他去死好了。
七年以后再回首，来自卢子内心的呐喊！
你Excel这么牛B，BOSS知道吗？
2016年某天，群内聊天感悟。
大神们，怎样将左边的格式批量转为右边的格式？
我的天啊，你这是要累死队友的节奏！
卢子发了感概后不再发言，而其他群友各出奇招。
有人说用分列再结合Word，有人说借助VBA，不过都是提建议没有出手。
最后有好事者帮忙写了一个很牛B的数组公式，输入公式后需要按CtrlShiftEnter结束，跟普通公式不一样。
=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列显示。
表格形式记录有两大好处：
1、隔行填充色，防止看错行；
2、能够提供动态的数据源，加上数据透视表堪称绝配。
接下来就是统计。
单击数据源任意单元格，单击“插入”选项卡的“数据透视表”，保持默认不变，单击“确定”按钮。
直接勾选品名跟数量就完成统计。
这个统计说真的，还不到30秒。
学好Excel真的很重要，不为别的，就为了你自己，不用以后每天加班加点，不用每天到处求助于人。
更高层次的靠Excel发家致富，那就是你精通以后的事儿。
如果你还是小白，不妨好好学习一些如何做表吧！
升级会员 