Excel 到底有多厉害？ 何明科，不写程序的数据工程师不是好产品经理 （在做投行、行研、咨询等金融岗位，有没有什么好用的找数据技巧呢？中重点讲了如何利用爬虫来收集数据和做出炫酷的表格，这篇主要讲讲如果用程序来替代人工，一个顶俩：虽然不如 AlphaGo，但也是机器替代人类的一个小侧面） 许多高级程序员瞧不上 VBA。因为程序员是有鄙视链的：汇编 > C > C++ > Python > Java 及 C# 及 PHP（这三者相互撕） > VB > HTML。在这长长的鄙视链中，甚至都没有 VBA 的位置。 可是，Excel+VBA 是图灵完备的（谢谢 @Octolet 的精辟总结），所以被程序员用来耍酷的各类性感语言能实现的大部分功能，Excel+VBA 都能实现，而且往往是以更高效更快捷的方式，在这里不谈效率和优雅。而且考虑到大部分普通群众是没有编程环境的（各种依赖各种包，各种 OS 各种编译环境，还有 IDE），然而使用 VBA，只需要打开装机自带的 Office，然后按下 Alt+F11 就自动进入编程和执行环境；甚至可以更简单的通过录制宏来解决写程序的问题，只需要在简单的代码基础上修修补补就可以执行。再考虑到 VBA 和 Office 各软件的完美整合，所以在便捷性方面，VBA 是无可比拟的。最后，Office+VBA 的分享性和移植性很强，任何测试通过的程序放到别的机器上也可轻易执行；而其他程序，哪怕是一段最简单的“Hello World”，也不一定。 因此本文讨论各种通过 Excel+VBA 能实现的各种炫酷功能（也会拓展到 Office+VBA），主要是为 Professional Service 以及各行各业不写程序但是又严重依赖于 Office 的职场人士服务的。 曾经有一个朋友和我说，“Excel 根本不需要编程，像我这样的 Excel 大牛靠函数和自定义函数能解决所有的问题。”对于这样的评论，我想起自己小学时的一段经历。因为不能理解虚数 i (i^2 = -1) 的价值，问我爸 i 有卵用？我爸说，“等你长大了，遇到更多的问题，就知道 i 的价值。” 1、自动打印 刚进职场的新人，只要爸爸不是李刚，基本都做过影帝影后（影=印，各种复印打印的体力劳动）。特别是咨询投行服务行业，在某次给客户的大汇报或者大忽悠会议之前，花数小时或者整晚来打印数个文件，并不是天方夜谭。而且这件事情是对着同样一堆不断修改的文件，会经常不断重复发生。 我加入 BCG 的第一个项目，就是帮助某大型企业从上到下设计 KPI 体系并实施。从上到下涉及到几十个部门，大概有 100 多张的 KPI 表格需要完成，这些 KPI 表格分布在各个 Excel 文件里。我们 4 个咨询顾问的任务： 设定好 KPI 的基本格式，然后每个顾问负责几个部门，在 Excel 里不断修改 KPI 表格，打印出来后去各个当事人及其领导那里讨论并修改 每周把所有的 Excel 文件中的 KPI 表格归集在一起，按顺序分部门打印出来，并需要多份，找负责该项目的 HR 头儿汇报进度和情况 这里面有个费时费力的环节，每周需要在多个 Excel 文件中找出目标 Worksheet，然后选定合适的区域作为输出的表格，按照一定的格式和一定的顺序，打印出这 100 多张表格。之前我们全是凭借人力，每周由一个 Analyst 把所有最新的 Excel 文件收集在一起，然后挨个打开文件选中合适的 Worksheet，选中区域设置好格式进行打印。每进行一次，几乎耗费一两个小时，还不能保证不出错。 于是写下了我的第一个 VBA 程序，而且基本上是宏录制之后来改的，没有使用参考书及搜索引擎，全靠 F1 和自动提示，所以贴出来特别纪念一下。实现的功能就是将上述的人肉实现的功能全部自动化。按下一个妞，就慢慢等着打印机按顺序出结果吧。 后来这个程序的升级版是：调度多台打印机，进一步提高效率，以及将打印机卡纸造成队列错误的概率降到极小的范围内。 2、制作图表及 GIF 动画 图表制作是每个 Office 一族的必备任务，制得一手好表格，绝对是升职加薪和偷懒放风的利器。在回答（黄焖鸡米饭是怎么火起来的？ - 何明科的回答），就利用 Excel+VBA 做出数张炫酷的信息地图，利用 VBA 为每个省的图形涂色。 同时，为了进一步增强炫酷结果，还利用 VBA 将这些连续变化的图表做成了 GIF 动画，可惜知乎不支持 GIF 的显示。 3、制作复杂的分析图表 下图是研究各个车型之间的用户相互转换关系，因为要将一维的转化率向量，变成两维的矩阵，所以使用了如下的复杂公式。 =IF(ISERROR(OFFSET($C$2,MATCH(CONCATENATE(ROW(A4),"-",COLUMN(A4)),$D$3:$D$600,FALSE),0)/OFFSET($C$2,MATCH(CONCATENATE(ROW(A4),"-",ROW(A4)),$D$3:$D$600,FALSE),0)),"",OFFSET($C$2,MATCH(CONCATENATE(ROW(A4),"-",COLUMN(A4)),$D$3:$D$600,FALSE),0)/OFFSET($C$2,MATCH(CONCATENATE(ROW(A4),"-",ROW(A4)),$D$3:$D$600,FALSE),0)) 同时为了用颜色的深浅来表示转化率的大小关系而便于比较，使用了 VBA 对下面的矩阵进行着色。当然有人肯定会说可以使用条件化格式，但是使用 VBA 保持了最高灵活度和效率。 4、根据格式化信息，生成标准的 Word 文件 这是帮朋友实现的一个项目，他们实验室是研究某类事故并对重大事故进行鉴定，最后发布 Word 版的正式报告。之前的工作流程是在专业的软件中完成计算和仿真，最后按照正式报告八股文的行文，把各种关键信息填进去，最后写成 Word 文件。写报告的过程枯燥而没有技术含量，但却要反复进行。 通过下图的 Word+VBA，完成主要的交互界面并连接计算软件。在通过简单的交互获取主要信息后，在后台完成计算并将主要信息填写入八股文的 Word 模版，最终完成报告，同时将结构化的信息存入 Access 数据库。 希望有机会和 @Raymond Wang 和 @金有元 等大律师合作，将 Termsheet 的书写及 Termsheet 到 SPA 及 M&A 等的法律文件书写工作彻底自动化。 5、通过 Excel 管理分布的任务流，并将 Excel 表格输出到 PowerPoint 这是协助某国际大型汽车制造厂完成新品牌及其新款车型上市，面临车型即将断档的窘境，该新车型的上市非常关键，不能错失时间节点。然而，新车型上市涉及到无数分支：制造、产品、市场、渠道、营销、公关、财务等等，同时还要协调欧洲的两个总部以及中国的两个分部。 这次咨询的核心任务就是项目管理，总控整个大项目的进度，并每周向中国区的 CEO 汇报进度并发掘出易出现问题的关键节点以调配资源。我们 4 个咨询顾问分配下去各自负责几个部门或者项目分支，和团队一起规划流程、画甘特图、确认里程碑及时间点、安排负责人等等。当每天回到办公室大家将进度汇总在一起的时候发现了挑战及难点，每条任务线并不是独立发展的，而是各条任务线交织在一起并互相影响。 某些核心人员在多个任务线出现。比如：负责预算的财务人员，几乎要出现在各条线中负责相关预算的审批环节 某些任务线的里程碑是其他任务线里程碑的必要条件而相互关联。比如：新车的下线时间影响发布会的时间，相关法规测试的通过又影响车辆的下线时间等等 当任务线增多以及任务线之间的交叉越发频繁的时候，汇总的任务将会几何级数增加，这就是我们在项目过程中遇到的问题。于是我利用 Excel+VBA 完成了这个工作的自动化。主要实现的功能： 自动将 4 个顾问手中分散的 Excel 文件汇集在一起形成一个大的总表，如下图 各顾问手中的表格是按照部门维度来划分的，汇总后需要按照不同的维度来输出不同类型的表格，比如：按任务线输出表格、按责任人输出表格、所有延误任务的表格、所有需要资源重点投入任务的表格等等 在此基础之上，还要将上面提到的各种维度下的所有表格（大概有 200 多张），按要求格式粘贴到 PPT 中，每周提交给中国区的总部进行汇报和评估。密密麻麻的表格如下图。于是，我又写了一个程序将 Excel 中的表格输出到 Powerpoint 中，将一个秘书每次需要数小时才能完成的工作，简化成了一键发布，并可以在 Excel 中完成对 PPT 的更新。 这个项目的程序量不小，近似于写了一个迷你版的 Microsoft Project 来进行项目管理。 最后，下图中密密麻麻的 PPT 每周需要更新一次，每次都是快 100 张的工作量，然而基本上都是靠 Excel 来自动完成更新的。因为 PPT 的模版每次变化不大，我将这些模版记录下来，每周更新的时候只要根据 Excel 中最新的数据更改 PPT 中的数据即可。 6、根据结果倒推假设 一般的 Financial Model 都是根据重重假设计算最终结果。而在为某顶级手机品牌服务的过程中，我们却遭遇了逆向的尴尬。本来是根据地面销售人员的一定服务水平，计算所需要的销售人员数量；结果在项目过程中，总部已经确定好了销售人数的 Head Count，转而要求我们根据 HC 确定服务水平。然而，服务水平不是一个单变量，是由零售店的覆盖率、销售拜访频率、拜访中的服务深度等多重因素来决定的，同时还可以根据一线至无线城市来变化。 于是只好再次寄出 Excel+VBA 法宝。先根据常规思路建立好 Financial Model，得出 HC 的初步结果。然后写 VBA 程序，根据不同的情景、不同的优先级以及不同的权重来调节零售店的覆盖率、销售拜访频率、拜访中的服务深度等多因素，同时设定这几大因素的可接受范围，逐步逼近 HC 的预设值。 如果没有程序，以前基本是靠人工手动调节来凑结果，而且因为各种情景的不同，还需要多次调节。而通过程序，基本是自动完成，还可智能得设置优先级及权重，无需人工参与。 7、海量下载 Bloomberg 数据并完成分析 通过 Bloomberg 的 VBA API，海量下载数百只目标股票的 tick data 以及 order book。 并根据实现构建好的数学模型，在后台完成计算，将上述的实时数据转化成每只股票实时的 trading cost，实时展现在交易员最常用的 Excel 界面中，方便交易员评估当下的交易成本以便于优化交易策略。 8、结语 计算了一下，我在 BCG 做了三年咨询顾问，大概写了几万行 VBA 程序（都是自己手工输入的，没有复制拷贝和系统自动生成），每个项目一千至几千行程序不等。最后将 Excel 用成了中控界面，类似 EMACS，在 Excel 可以随意操控全公司的打印机、Word、Powerpoint 等等，自动完成各种任务以及数据更新和抓取。因为 Excel 的数据更结构化，所以将其作为中控平台，比 Word 和 Powerpoint 更有优势。 一些学习 VBA 的小技巧在这个回答里：Excel VBA 如何快速学习？ - 何明科的回答。 最后，这些程序中的一些公共模块，打包给了 BCG Global IT，BCG 给了我一个 Sponsorship 去 Stanford GSB 读书。程序的注释行里面有我几个好朋友、亲人及导师的名字，祝他们一生平安。 更多精彩文章请看：数据冰山 - 知乎专栏 阅读原文
