终于，Excel数据处理的方式来到了一个十字路口：Excel中的函数式编程简介

Excel的数据处理方式来到了一个十字路口，是继续沿用的方式还是使用函数式编程的思路。后者可以很方便的创建业务逻辑和数据分离的解决方案，其优势是非常明显的。

今天我们简单介绍一下Excel中的函数式编程。

在Excel中提到编程，很多人的第一反应是VBA，宏等词汇，下一步的反应就是：

那东西太难了！

我肯定学不会！

其实，我们这里说的是“函数式”编程。它没有那么复杂，也没有那么高的学习难度，因为它全部是使用Excel的函数创建公式而已。

你需要做的是调整在Excel中使用函数处理数据的思路。

函数式编程，Functional Programming，跟面向对象编程一样，是一种编程范式。

它的主要特点就是将一切都看作函数。

这恰恰是Excel所擅长的。

尤其是Excel现在已经支持了动态数组，有了LET，LAMBDA等高级函数，已经成为了一个“图灵完备”的编程平台了。

当然，我们绝不是要成为传说中的“程序员”，我们不需要学习过多的编程理论知识，我们只不过是要处理数据而已。

不过是在处理数据时稍微改变一下思路而已。

没错，只要你使用了Excel，你就已经在编程了。

不信，你往下看。

这是一个司空见惯的简单表格：

A2:D2中有数据；A4:A6中有公式计算。从具体公式来看，A4和A5单元格是辅助单元格，用于保存中间结果的，A6单元格才是我们需要的结果。

现在，忘掉表格，将其中有数据或公式的单元格记录下来，比如记录在一个文本文件中：

象不象一个简单的程序！

其实还可以更象一点，在Excel中你甚至可以使用变量：名称。

比如，我们将A2:D2单元格分别命名为x，y，n，m，将A4，A5分别命名为step1，step2，

公式就变成这样了：

再一次，将表格去掉，只记录数据和公式的单元格：

任何一个程序员都不会怀疑这是一段货真价实的程序。

但是这种“程序”是有很大的缺陷的：

• 可读性差 - 相信大家都有个读不懂别人写的公式的经历。甚至连自己以前写的公式都不一定读的懂。

• 可移植性差 -因为你的公式实际上是跟表格紧密绑定的。比如，在上面的例子中，要将A6中的公式用到其他表格，不仅仅目标表格中A2:D4需要有数据，还必须同时将A4，A5一起一致过去。

  其中，业务逻辑和数据紧密绑定是这种方案的最大问题。修改数据结构时需要同时修改公式（即业务逻辑），其中，辅助单元格，辅助列，辅助表等中间数据加剧了这个问题。

Excel中的LET公式迈出了非常重要的一步，让我们通过一个公式就得到最后的计算结果，摆脱了对这些中间结果的依赖：

可以看到，整个公式中不过是复制了之前定义名称之后的一系列公式而已。但是表格清爽多了：

进一步，还可以将业务逻辑和数据完全分离，将业务逻辑封装在一个自定义函数中：

之后就可以对所有形式的数据应用同样的业务逻辑了：

=rslt(A3,B3,C3,D3)=rslt(x,y,n,m)=rslt(1,2,3,4)

这就是我们使用函数式编程处理数据的追求：

业务逻辑和数据分离！

下面我们看一个实际例子：

表格中记录了每个员工的能力评估，包括通用能力和领导力，现在我们需要根据这两个能力为员工评级，评级规则如下：

每个能力高低的评价依据是看看该项得分是否大于全体员工该项得分的中间值（即最大值和最小值的平均值）。

你可以先想一下自己会如何写这个公式。

多数情况下，你会写出一个这样的公式：

=IF(AND(D2>(MIN(D2:D55)+MAX(D2:D55))/2,E2>(MIN(E2:E55)+MAX(E2:E55))/2), "A",IF(AND(D2>(MIN(D2:D55)+MAX(D2:D55))/2,E2<(MIN(E2:E55)+MAX(E2:E55))/2),"B",IF(AND(D2<(MIN(D2:D55)+MAX(D2:D55))/2,E2>(MIN(E2:E55)+MAX(E2:E55))/2),"C","D")))

这个公式使用嵌套的IF公式进行判断，判断的依据是通用能力是否超过中间值以及领导力是否超过中间值。

这个公式的缺陷我们就不重复了！无论是可读性，可维护性，可移植性都很差。

你可能可以改进这个公式，比如将通用能力和领导力中间值的计算从公式中移出，放到单元格L1:M2中，

这里的公式变成了：

=IF(AND(D2>($L$1+$M$1)/2,E2>($L$2+$M$2)/2), "A",IF(AND(D2>($L$1+$M$1)/2,E2<($L$2+$M$2)/2),"B",IF(AND(D2<($L$1+$M$1)/2,E2>($L$2+$M$2)/2),"C","D")))

简化了吗？也许，从长度上看是简化了。

但是可读性并没有真正的提高。而且，公式更加紧密的与表格布局结合在了一起。要在其他表格中用同样的逻辑进行分级，必须同时再现L1:M2的计算。

那么，如果我们怎么才能创建业务逻辑与数据分离的方案呢？

这个过程设置都不需要借助Excel。

首先，我们对问题进行分析，我们的要求实际上是创建一个函数：

GRADE

这个函数依赖于四个对象：

• 个人通用能力得分 - 记作g

• 全体通用能力得分 - 记作gs

• 个人领导力得分 - 记作l

• 全体领导力得分 - 记作ls

即得到一个函数：

GRADE(l, gs, l, ls)

这个函数的处理逻辑可以通过下面的公式表示：

=if( and(通用能力.高, 领导力.高), “A”,if(and(通用能力.高,领导力.低), “B”, if( and(通用能力.低,领导力.高), “C”, “D” ) ))

这个逻辑实际上就是之前我们在Excel中实现的公式方案。只不过现在我们将其参数抽象为一些变量。

这个逻辑中还需要解决一个问题，即判断通用能力和领导力的分数高低。这就需要一个中间函数：

HIGH_LOW(s, totals)

其中，

s -个人得分

totals - 全体得分

实现逻辑可以使用下面的公式：

=if(s>=(min(totals)+max(totals))/2,“高”,“低”)

有了这个HIGH_LOW函数，GRADE中的逻辑就可以实现了。

问题已经从逻辑上被解决了。剩下的事情就是在Excel中实现它们。

//判断得分高低HIGH_LOW = LAMBDA( s, //个人得分 totals, //全体得分 IF(s >= (MIN(totals)+MAX(totals))/2,"高","低"));
//根据领导力和通用能力得分在全体得分中的位置，为候选人分级GRADE = LAMBDA( g, //个人通用能力得分 gs, //全体通用能力分数列表 l, //个人领导力得分 ls, //全体领导力分数列表 LET( g_hl, HIGH_LOW(g, gs), //个人通用能力高或低 l_hl, HIGH_LOW(l, ls), //个人领导力高或低IF( and(g_hl="高",l_hl="高"), "A", IF( AND(g_hl="高",l_hl="低"), "B", IF( AND(g_hl="低",l_hl="高"), "C", "D" ) ) ) ))

在高级公式环境中创建上面的两个自定义函数。

到现在为止，我们都没有用到数据，仅仅是在实现业务逻辑而已。

下面就可以使用自定义函数具体处理数据了：

这个公式可以导出移植，因为它不牵涉到任何的数据。即使数据布局和结果发生了改变，也不需要改变自定义函数的定义，因为业务逻辑并没有改变。

详细解释请看视频

加入E学会，学习更多Excel应用技巧

/portal/learn/class_list

Excel+Power Query+Power Pivot+Power BI

自定义函数底部菜单：知识库->自定义函数

面授培训底部菜单：培训学习->面授培训

Excel企业应用底部菜单：企业应用

也可以在历史文章中学习Excel，Power Query，Power Pivot，Power BI，Power Automate各种技巧。