Excel 里，其实还藏着很多高效「神技」，比如……撩妹！

在咨询公司、VC/PE/Hedge Fund 等基金混迹多年，一直靠着 Excel 的各种技巧安身立命和升职加薪。可能是因为程序员出身的原因，在学会以上各个答案提到的装逼炫酷图表、快捷键和一些略复杂的函数（VLookup 等）之外，总是希望从更深的层次去探索 Excel 及各类 Office 软件，直到遇到了数组函数和 VBA 编程。这些技能一旦掌握能将工作效率提高数倍甚至是十倍以上，然而周围却很少有人掌握。如果对这些技能感兴趣，可以关注这个活动：粉丝回馈：程序让你更性感 - 数据冰山 - 知乎专栏。

数组函数和 VBA 编程，简直就是为程序员而生的，数组函数充满了数据库的思维，而 VBA 本身就是彻头彻尾的编程，再加之各种接口，能够将 Office 各套软件以及 OS 下的各种功能完美结合在一起。因为 Excel+VBA 是图灵完备的，最后辅以 Excel 简单高效的数据呈现界面，所以在我的心目中，Excel+ 数组函数 +VBA，简直就是网页前端 + 客户端 + 后台程序 + 数据库。感觉学会了这些，某种意义上就是成为了 Full Stack Developer（全栈工程师），各互联网公司梦寐以求想招到的人。

一、数组函数

数组函数往往会和 Index、Indirect 及 Address 等地址相关和数据块相关的函数搭配使用，如果不考虑效率的话，基本可以替代各种 SQL 语句了。

数组函数之案例 1：计算某类产品的总价值

计算 AA 产品的总价值，替代 select sum(产品数量 x 产品单价) from ... where 产品编号=‘AA’

{=SUM(IF(($B$4:$B$8="AA"),($C$4:$C$8)*($D$4:$D$8),0))}

如果没有数组函数，使用 sumif 等函数也会很复杂。

数组函数之案例 2：挑选不重复的值并计算总和

左边的白色区域是原始数据，右边的彩色区域使用了数组函数的输出区域。数组函数实现了两大功能：

黄色区域：将不重复的 name+month 筛选出来。
蓝色区域：替代了 select sum(tot) from ... group by name, month，将 name+month 对应的 tot 进行加总
如果没有数组函数，只能使用 Pivot Table 等复杂方式，不仅程序开销很大，而且还不够灵活。

数组函数之案例 3：

这是一个帮助某国际家用电器厂商预测中国各家电品类市场潜力及规模的项目，从 2005-2024 年。一般的 Excel 函数只能解决两维的问题，而这次客户提出了这个变态的 n 维需求，需要精确到年份、电器品类、渠道类型、用户高中低端以及城市级别共 5 个维度来查看市场规模及潜力。简单说就是利用下面这个表格随时查看指定维度下的某年份的市场潜力及规模。

通过使用数组函数建模轻松实现如下功能，只要在指定的区域内选择相关值，就能计算值所需的市场规模及潜力，等于使用了 SQL 语句：select * from table where 条件 1=A1 and 条件 2=A2 and 条件 3=A3……（共 5 个条件）

复杂的数组函数编码如下

=SUM(((Summary_Market!$S$71:$S$308=$A7)+(Summary_Market!$S$71:$S$308=$V7)+(Summary_Market!$S$71:$S$308=$AA7)+($A7="")>0)*((Summary_Market!$T$71:$T$308=$B7)+(Summary_Market!$T$71:$T$308=$W7)+(Summary_Market!$T$71:$T$308=$AB7)+($B7="")>0)*((Summary_Market!$U$71:$U$308=$C7)+(Summary_Market!$U$71:$U$308=$X7)+(Summary_Market!$U$71:$U$308=$AC7)+($C7="")>0)*((Summary_Market!$V$71:$V$308=$D7)+(Summary_Market!$V$71:$V$308=$Y7)+(Summary_Market!$V$71:$V$308=$AD7)+($D7="")>0)*(Summary_Market!BB$71:BB$308))

整个模型的界面及复杂的数组函数如下图，左边部分的界面其实就是图形化的 SQL 语句。这个模型被该客户及我们咨询公司使用了不下 5 年，部分依赖于其超强的灵活性。

二、VBA 编程

首先不要被“编程”二字吓跑，因为 VBA 不会编程也可以进行，通过录制宏的方式就可以搞定。分享一些学习 VBA 的小技巧（一些学习 VBA 的小技巧在这个回答里：Excel VBA 如何快速学习？ - 何明科的回答），录制宏的诀窍见下图。

如果真要升级成为 VBA 编程达人，还是需要自己学习和自己编写 VBA 程序。下面列举若干自己亲手编写的若干 Excel+VBA 项目。

VBA 编程之案例 1：自动打印

刚进职场的新人，只要爸爸不是李刚，基本都做过影帝影后（影=印，各种复印打印的体力劳动）。特别是咨询投行服务行业，在某次给客户的大汇报或者大忽悠会议之前，花数小时或者整晚来打印数个文件，并不是天方夜谭。而且这件事情是对着同样一堆不断修改的文件，会经常不断重复发生。

我加入 BCG 的第一个项目，就是帮助某大型企业从上到下设计 KPI 体系并实施。从上到下涉及到几十个部门，大概有 100 多张的 KPI 表格需要完成，这些 KPI 表格分布在各个 Excel 文件里。我们 4 个咨询顾问的任务：

设定好 KPI 的基本格式，然后每个顾问负责几个部门，在 Excel 里不断修改 KPI 表格，打印出来后去各个当事人及其领导那里讨论并修改

每周把所有的 Excel 文件中的 KPI 表格归集在一起，按顺序分部门打印出来，并需要多份，找负责该项目的 HR 头儿汇报进度和情况

这里面有个费时费力的环节，每周需要在多个 Excel 文件中找出目标 Worksheet，然后选定合适的区域作为输出的表格，按照一定的格式和一定的顺序，打印出这 100 多张表格。之前我们全是凭借人力，每周由一个 Analyst 把所有最新的 Excel 文件收集在一起，然后挨个打开文件选中合适的 Worksheet，选中区域设置好格式进行打印。每进行一次，几乎耗费一两个小时，还不能保证不出错。

于是写下了我的第一个 VBA 程序，而且基本上是宏录制之后来改的，没有使用参考书及搜索引擎，全靠 F1 和自动提示，所以贴出来特别纪念一下。实现的功能就是将上述的人肉实现的功能全部自动化。按下一个妞，就慢慢等着打印机按顺序出结果吧。

后来这个程序的升级版是：调度多台打印机，进一步提高效率，以及将打印机卡纸造成队列错误的概率降到极小的范围内。

VBA 编程之案例 2：制作复杂的矩阵式分析图表

下图是研究各个车型之间的用户相互转换关系，因为要将一维的转化率向量，变成两维的矩阵，所以使用了如下的复杂公式。

=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 保持了最高灵活度和效率。

VBA 编程之案例 3：管理分布的任务流，并将 Excel 表格输出到 Powerpoint

这是协助某国际大型汽车制造厂完成新品牌及其新款车型上市，面临车型即将断档的窘境，该新车型的上市非常关键，不能错失时间节点。然而，新车型上市涉及到无数分支：制造、产品、市场、渠道、营销、公关、财务等等，同时还要协调欧洲的两个总部以及中国的两个分部。

这次咨询的核心任务就是项目管理，总控整个大项目的进度，并每周向中国区的 CEO 汇报进度并发掘出易出现问题的关键节点以调配资源。我们 4 个咨询顾问分配下去各自负责几个部门或者项目分支，和团队一起规划流程、画甘特图、确认里程碑及时间点、安排负责人等等。当每天回到办公室大家将进度汇总在一起的时候发现了挑战及难点，每条任务线并不是独立发展的，而是各条任务线交织在一起并互相影响。

某些核心人员在多个任务线出现。比如：负责预算的财务人员，几乎要出现在各条线中负责相关预算的审批环节

某些任务线的里程碑是其他任务线里程碑的必要条件而相互关联。比如：新车的下线时间影响发布会的时间，相关法规测试的通过又影响车辆的下线时间等等

当任务线增多以及任务线之间的交叉越发频繁的时候，汇总的任务将会几何级数增加，这就是我们在项目过程中遇到的问题。于是我利用 Excel+VBA 完成了这个工作的自动化。主要实现的功能：

自动将 4 个顾问手中分散的 Excel 文件汇集在一起形成一个大的总表，如下图

各顾问手中的表格是按照部门维度来划分的，汇总后需要按照不同的维度来输出不同类型的表格，比如：按任务线输出表格、按责任人输出表格、所有延误任务的表格、所有需要资源重点投入任务的表格等等

在此基础之上，还要将上面提到的各种维度下的所有表格（大概有 200 多张），按要求格式粘贴到 PPT 中，每周提交给中国区的总部进行汇报和评估。密密麻麻的表格如下图。于是，我又写了一个程序将 Excel 中的表格输出到 Powerpoint 中，将一个秘书每次需要数小时才能完成的工作，简化成了一键发布，并可以在 Excel 中完成对 PPT 的更新。

这个项目的程序量不小，近似于写了一个迷你版的 Microsoft Project 来进行项目管理。

最后，下图中密密麻麻的 PPT 每周需要更新一次，每次都是快 100 张的工作量，然而基本上都是靠 Excel 来自动完成更新的。因为 PPT 的模版每次变化不大，我将这些模版记录下来，每周更新的时候只要根据 Excel 中最新的数据更改 PPT 中的数据即可。

VBA 编程之案例 4：构建 Financial Model 并根据结果倒推假设

一般的 Financial Model 都是根据重重假设计算最终结果。而在为某顶级手机品牌服务的过程中，我们却遭遇了逆向的尴尬。本来是根据地面销售人员的一定服务水平，计算所需要的销售人员数量；结果在项目过程中，总部已经确定好了销售人数的 Head Count，转而要求我们根据 HC 确定服务水平。然而，服务水平不是一个单变量，是由零售店的覆盖率、销售拜访频率、拜访中的服务深度等多重因素来决定的，同时还可以根据一线至无线城市来变化。

于是只好再次寄出 Excel+VBA 法宝。先根据常规思路建立好 Financial Model，得出 HC 的初步结果。然后写 VBA 程序，根据不同的情景、不同的优先级以及不同的权重来调节零售店的覆盖率、销售拜访频率、拜访中的服务深度等多因素，同时设定这几大因素的可接受范围，逐步逼近 HC 的预设值。

如果没有程序，以前基本是靠人工手动调节来凑结果，而且因为各种情景的不同，还需要多次调节。而通过程序，基本是自动完成，还可智能得设置优先级及权重，无需人工参与。

VBA 编程之案例 5：海量下载 Bloomberg 数据并完成分析

通过 Bloomberg 的 VBA API，海量下载数百只目标股票的 tick data 以及 order book。

并根据实现构建好的数学模型，在后台完成计算，将上述的实时数据转化成每只股票实时的 trading cost，实时展现在交易员最常用的 Excel 界面中，方便交易员评估当下的交易成本以便于优化交易策略。

三、数组函数 +VBA 的项目：全栈项目

在项目中（RIO 是如何席卷大江南北的？ - 数据冰山 - 知乎专栏）对 Excel 的要求很综合。首先通过数组函数，对每年对 RIO 酒购买时刻的提及率按省进行统计。

其次，利用 VBA 程序，将上表中 H 列和 I 列的数据，按省份涂色到下图中的地图中。

（涂色部分来自于网上的一段程序，制作 GIF 动画的是自己完成的）

最后，再次利用 VBA 编程以及调用外部程序（GIFSICLE），将一幅幅图表合成在一起生成 GIF 动画。

最后的彩蛋，还可以用 VBA 来画油画，零基础成为用美术作品把妹撩汉的艺术青年（Excel 有什么奇技淫巧，让你相见恨晚？ - 何明科的回答），下图把女神用 Excel 画成油画送给她。

（图文转载自知乎日报，作者何明科）

注：凡新西兰先驱报中文网引用、摘录或转自其他媒体的作品，本网对其观点和真实性恕不负责。新西兰先驱报中文网致力于帮助文章传播，希望能够与作者建立长期合作关系。若有任何问题请联系[email protected]。

Chinesenzherald.co.nz All Rights Reserved 版权所有