vlambda博客
学习文章列表

Excel自从支持了，以及有了和后，就进入了另外一个境界。可以说，现在，使用Excel处理数据跟以前有了完全不同的思路和方式。本文以一个真实的案例，探讨在Excel中进行函数式编程的方法。

问题

在一个项目中，需要为客户进行数据分析，客户的明细数据已经汇总整合完毕：

在此基础上就可以进行各种分析了。不料，客户说发现系统外有一些新数据，需要整合进来。

新数据被放在一个文件中，有多个SHEET：

每个SHEET的格式是一样的 。

红色方框中是有用的数据：A1单元格是一个文本，记录了客户编号和其他信息，只有客户编号是需要的。下面红色方框中的四列都是需要的。

而我们需要的明细数据（已经整合好的数据）如下图：

为了清晰起见，我将字段标成不同颜色。只有部分数据是从新数据中可以获得（客户编号，货品名称，箱数，瓶数，收入），创建日期是个固定值：2022/2/1。其余信息必须从其他来源中查找得到。

其余信息就两类：产品资料和客户资料

这是产品资料表：

这是客户资料表：

现在的需求就是将这些表整合进明细数据表中。

分析

这是一个非常常见的数据整合场景。一般来说有两个方案：

1.  函数+手工方案
   这个方案就是将每个表的数据复制到明细数据表对应列中，比如将客户编号和产品名称复制过去，然后写公式查找对应的属性。

2. 用
   是整合数据的利器，尤其是它可以非常方便地将多个格式一致的表格整合在一起。
   

第一个方案实际上手工工作比较多，看似使用了，但是你需要使用很多次。做多了就会发现是一个比较鸡肋的方案，只有数据量（工作表）较少的时候才比较合适。

第二个方案在很多场合下都是首选方案。但是，这个方案其实最大的好处是可以重复使用。如果同样的事情需要多次执行，那么用Power Query非常合适。另外一个好处就是如果特别多的工作表需要整合（比如，几十个，上百个）的优势也很明显。但是对于我面临的场景，只有不到10个表，而且工作肯定是一次性的，用有点得不偿失。

当然，可以写VBA，但是那就显得有点过于复杂了。

实话实说，在以前，我肯定是选择方案2，用来做这件事情。但是现在，我希望 尝试一下：写一个公式解决这个问题。

公式解决-函数式编程的尝试

下面我将详细说明如何写公式解决这个问题。为了简单，我先介绍如何处理一个表。

我们使用（为了提高可读性，我们用了换行，可以用ALT+回车输入：

第一步，我们先将需要合并的数据区域放到变量里：

接下来，取出客户编码，放在变量customer_code中，并用客户编码生成一个数组。数组的长度（行数）应该等于源数据的行数。所以，我们先计算源数据的行数，并作为中间变量存起来：

生成第一列客户编码，并作为结果返回：

在这里，我们用了从一个文本中取出客户ID，又用了生成了一列客户ID。

我们将生成的客户ID列作为结果返回。注意在LET函数中，最后一个参数是最终的返回结果。

这个公式其实已经成型了。不过只返回了一列，接下来继续返回其余的列。

绿色字段是产品相关的，所以可以一次性的取出来。

这里我们用到两个变量：产品属性表以及给定的产品列表：

需要的产品列表在源数据区域的第一列，用到了。

然后，需要根据这个产品列表在产品属性表中筛选并返回对应行和列：

这里，我们使用了一个公式：

CHOOSEROWS(product_properties, XMATCH(productlist, TAKE(product_properties,,1)))

这可以作为一个固定用法，就是返回一组满足条件的记录的对应列。具体原理请参见。

同时，要注意最后的返回值，使用将客户ID列和产品属性列组合在一起。

下面有一个单独的日期列，因为日期是固定值，所以使用常量即可：

接下来的数量列来自于源数据：

所以，直接取源数据的后三列就可以了：

用比较简单。

下面就只有客户相关的属性了，需要在客户资料表中筛选。很简单，跟前面产品相关的部分一样：

大功告成！

下面是完整的公式：

=LET(src_rng, '[奥运北京厂配送明细---北京.xlsx]NBC'!$A$3:$D$15,code_str, '[奥运北京厂配送明细---北京.xlsx]NBC'!$A$1,
rows_src, ROWS(src_rng),
customer_id, TEXTBEFORE(code_str," "),col_customer_id,MAKEARRAY(rows_src, 1,LAMBDA(r,c,customer_id)),
product_properties, 产品资料表,productlist, CHOOSECOLS(src_rng,1),cols_product, CHOOSEROWS(product_properties, XMATCH(productlist, TAKE(product_properties,,1))),
col_date, MAKEARRAY(rows_src, 1, LAMBDA(r,c, DATE(2022, 2, 1))),
cols_qty, DROP(src_rng, ,1),
customers, 客户资料表,rows_customers, DROP(FILTER(customers, TAKE(customers,,1)=customer_id),,1),
cols_customers, MAKEARRAY(rows_src,7,LAMBDA(r,c,INDEX(rows_customers,1,c))),
HSTACK(col_customer_id, cols_product, col_date,cols_qty,cols_customers))

总结

在Excel的函数式编程中，是个主力。尽管可以使用其他函数完成很多工作，但是它们通常是充当中间结果。如果想一次性完成工作，恐怕绕不开。

其实，这个跟Power Query中的M语言非常类似。下面是一段M语言的代码：

可以看到，跟我们这里的基本上没有区别。毕竟同为函数式编程语言，又是从Excel发展起来的。

这里，还需要强调一下，我们说动态数组是Excel非常重大的一步改变。之前，很多人会理解就是省去了CTRL+SHIFT+ENTER的麻烦。其实，最重要的是Excel的函数（大部分）都可以像处理普通数值一样处理数组了，这样，它们就可以在函数式编程中作为中间的步骤了。

最后，这个案例还没有做完，还需要将不同工作表的数据整合在一起。我们下次再交流具体的实现方法。

Excel+Power Query+Power Pivot+Power BI

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

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

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