學不完．教不停．用不盡

在這篇文章中，我們將以一個實際案例來介紹如何將 Google 表單中的複選題填答結果轉換成A、B、C、D、E答案。這是一個關於 Excel 中多個強大函數的實際運用練習，包括 LAMBDA 函數、BYCOL 函數、XLOOKUP 函數、TEXTSPLIT 函數和 TEXTJOIN 函數。

雖然這可能會帶來一些挑戰，但這些新的函數都是經過精心設計的，並且在實際應用中具有重要的價值。通過深入理解這些函數的運作原理，不僅能夠解決目前的問題，還能夠為未來的公式設計提供有力的支持。儘管這些函數在一開始可能讓人感到有些困惑，但透過實際案例的操作和說明，將會更深入地理解它們的優勢和運用方式。這將有助於你在今後的工作中更靈活地運用這些函數，並更加高效地進行公式的設計與處理。

公式設計與解析

因為 Google 表單的填答結果對於複選題是直接記錄被選項目的內容，並非記錄其對應的「A,B,C,D,E」。所以處理起來比較麻煩。例如，下圖中直接記錄「選項2, 選項3」，每個內容中間以「,」和「空格」隔開。

儲存格F7：=TEXTJOIN("",,BYCOL(TEXTSPLIT(SUBSTITUTE(B7," ",""),","),
LAMBDA(ans,XLOOKUP(ans,F$1:F$5,$E$1:$E$5))))

複製儲存格F7，貼至儲存格F7:H21。

(1) SUBSTITUTE(B7," ","")

利用 SUBSTITUTE 函數將儲存格B7中內容裡的「空格」置換為空字串。

例如：「Excel, PowerPoint, Edge, Word」→「Excel,PowerPoint,Edge,Word」。

(2) TEXTSPLIT(SUBSTITUTE(B7," ",""),",")

利用 TEXTSPLIT 函數將第(1)式傳回值裡的內容以「,」 分隔符號隔開成為陣列。

例如：「Excel,PowerPoint,Edge,Word」→{"Excel","PowerPoint","Edge","Word"}。

(3) LAMBDA(ans,XLOOKUP(ans,F$1:F$5,$E$1:$E$5))

利用 XLOOKUP 函數進行查詢，由 LAMBDA 函數建構這個自訂函數運算。

(4) BYCOL(第(2)式,第(3)式))

利用 BYCOL 函數讓 LAMBDA 建構的自訂函數以欄逐一執行查詢工作，例如：Excel→Ｅ、PowerPoint→Ｃ、Edge→Ｄ、Word→Ａ。

(5) TEXTJOIN("",,第(4)式)

將第(4)式以欄逐一執行的結果以 TEXTJOIN 函數用「空字串」串接在一起，例如：ECDA。

【參考資料】

 XLOOKUP 函數參考微軟提供的說明：XLOOKUP 函數

 TEXTSPLIT 函數參考微軟提供的說明：TEXTSPLIT 函數

 TEXTJOIN 函數參考微軟提供的說明：TEXTJOIN 函數

 LAMBDA 函數參考微軟提供的說明：LAMBDA 函數

 BYROW 函數參考微軟提供的說明：BYROW 函數

 BYCOL 函數參考微軟提供的說明：BYCOL 函數

 SUBSTITUTE 函數參考微軟提供的說明：SUBSTITUTE 函數