Excel自從支持了動態(tài)數(shù)組，以及有了LET和LAMBDA函數(shù)后，就進入了另外一個境界?？梢哉f，現(xiàn)在，使用Excel處理數(shù)據(jù)跟以前有了完全不同的思路和方式。本文以一個真實的案例，探討在Excel中進行函數(shù)式編程的方法。

問題

在一個項目中，需要為客戶進行數(shù)據(jù)分析，客戶的明細數(shù)據(jù)已經匯總整合完畢：

在此基礎上就可以進行各種分析了。不料，客戶說發(fā)現(xiàn)系統(tǒng)外有一些新數(shù)據(jù)，需要整合進來。

新數(shù)據(jù)被放在一個文件中，有多個SHEET：

每個SHEET的格式是一樣的 。

紅色方框中是有用的數(shù)據(jù)：A1單元格是一個文本，記錄了客戶編號和其他信息，只有客戶編號是需要的。下面紅色方框中的四列都是需要的。

而我們需要的明細數(shù)據(jù)（已經整合好的數(shù)據(jù)）如下圖：

為了清晰起見，我將字段標成不同顏色。只有部分數(shù)據(jù)是從新數(shù)據(jù)中可以獲得（客戶編號，貨品名稱，箱數(shù)，瓶數(shù)，收入），創(chuàng)建日期是個固定值：2022/2/1。其余信息必須從其他來源中查找得到。

其余信息就兩類：產品資料和客戶資料

這是產品資料表：

這是客戶資料表：

現(xiàn)在的需求就是將這些表整合進明細數(shù)據(jù)表中。

分析

這是一個非常常見的數(shù)據(jù)整合場景。一般來說有兩個方案：

1.  函數(shù)+手工方案
   這個方案就是將每個表的數(shù)據(jù)復制到明細數(shù)據(jù)表對應列中，比如將客戶編號和產品名稱復制過去，然后寫VLOOKUP公式查找對應的屬性。

2. 用Power Query
   Power Query是整合數(shù)據(jù)的利器，尤其是它可以非常方便地將多個格式一致的表格整合在一起。
   

第一個方案實際上手工工作比較多，看似使用了VLOOKUP函數(shù)，但是你需要使用很多次。做多了就會發(fā)現(xiàn)是一個比較雞肋的方案，只有數(shù)據(jù)量（工作表）較少的時候才比較合適。

第二個方案在很多場合下都是首選方案。但是，這個方案其實最大的好處是可以重復使用。如果同樣的事情需要多次執(zhí)行，那么用Power Query非常合適。另外一個好處就是如果特別多的工作表需要整合（比如，幾十個，上百個），Power Query的優(yōu)勢也很明顯。但是對于我面臨的場景，只有不到10個表，而且工作肯定是一次性的，用Power Query有點得不償失。

當然，可以寫VBA，但是那就顯得有點過于復雜了。

實話實說，在以前，我肯定是選擇方案2，用Power Query來做這件事情。但是現(xiàn)在，我希望 嘗試一下：寫一個公式解決這個問題。

公式解決-函數(shù)式編程的嘗試

下面我將詳細說明如何寫公式解決這個問題。為了簡單，我先介紹如何處理一個表。

我們使用LET函數(shù)（為了提高可讀性，我們用了換行，可以用ALT+回車輸入：

第一步，我們先將需要合并的數(shù)據(jù)區(qū)域放到變量里：

接下來，取出客戶編碼，放在變量customer_code中，并用客戶編碼生成一個數(shù)組。數(shù)組的長度（行數(shù)）應該等于源數(shù)據(jù)的行數(shù)。所以，我們先計算源數(shù)據(jù)的行數(shù)，并作為中間變量存起來：

生成第一列客戶編碼，并作為結果返回：

在這里，我們用了TEXTBEFORE函數(shù)從一個文本中取出客戶ID，又用了MAKEARRAY函數(shù)生成了一列客戶ID。

我們將生成的客戶ID列作為結果返回。注意在LET函數(shù)中，最后一個參數(shù)是最終的返回結果。

這個公式其實已經成型了。不過只返回了一列，接下來繼續(xù)返回其余的列。

綠色字段是產品相關的，所以可以一次性的取出來。

這里我們用到兩個變量：產品屬性表以及給定的產品列表：

需要的產品列表在源數(shù)據(jù)區(qū)域的第一列，用到了CHOOSECOLS函數(shù)。

然后，需要根據(jù)這個產品列表在產品屬性表中篩選并返回對應行和列：

這里，我們使用了一個公式：

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

這可以作為一個固定用法，就是返回一組滿足條件的記錄的對應列。具體原理請參見這里。

同時，要注意最后的返回值，使用HSTACK函數(shù)將客戶ID列和產品屬性列組合在一起。

下面有一個單獨的日期列，因為日期是固定值，所以使用常量即可：

接下來的數(shù)量列來自于源數(shù)據(jù)：

所以，直接取源數(shù)據(jù)的后三列就可以了：

用DROP函數(shù)比較簡單。

下面就只有客戶相關的屬性了，需要在客戶資料表中篩選。很簡單，跟前面產品相關的部分一樣：

大功告成！

下面是完整的公式：

=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的函數(shù)式編程中，LET函數(shù)是個主力。盡管可以使用其他函數(shù)完成很多工作，但是它們通常是充當中間結果。如果想一次性完成工作，恐怕繞不開LET函數(shù)。

其實，這個跟Power Query中的M語言非常類似。下面是一段M語言的代碼：

可以看到，跟我們這里的LET函數(shù)基本上沒有區(qū)別。畢竟同為函數(shù)式編程語言，又是從Excel發(fā)展起來的。

這里，還需要強調一下，我們說動態(tài)數(shù)組是Excel非常重大的一步改變。之前，很多人會理解就是省去了CTRL+SHIFT+ENTER的麻煩。其實，最重要的是Excel的函數(shù)（大部分）都可以像處理普通數(shù)值一樣處理數(shù)組了，這樣，它們就可以在函數(shù)式編程中作為中間的步驟了。

最后，這個案例還沒有做完，還需要將不同工作表的數(shù)據(jù)整合在一起。我們下次再交流具體的實現(xiàn)方法。

Excel+Power Query+Power Pivot+Power BI

自定義函數(shù)  底部菜單：知識庫->自定義函數(shù)

面授培訓  底部菜單：培訓學習->面授培訓

也可以在歷史文章中學習Excel，Power Query，Power Pivot，Power BI，Power Automate各種技巧。