BYROW和BYCOL函數(shù)可以對區(qū)域中的數(shù)據(jù)逐行或者逐列進(jìn)行聚合運算，輸出一個單列或單行的數(shù)組。下面詳解這兩個函數(shù)的用法。

今天學(xué)習(xí)LAMBDA最后兩個配套函數(shù)BYROW和BYCOL。這兩個函數(shù)相比其他四個配套函數(shù)更簡單易懂，并且兩者參數(shù)和用法類似，所以小窩將它們放在一起介紹。

1.作用與語法

BYROW函數(shù)將LAMBDA算式應(yīng)用于數(shù)組的每一行（可以理解為一行一行的計算），并返回一個單列等行（與被計算的數(shù)組行數(shù)相等）的數(shù)組。

=BYROW(數(shù)組,LAMBDA運算式)

BYCOL函數(shù)則是將LAMBDA算式應(yīng)用于數(shù)組的每一列（可以理解為一列一列的計算），并返回一個單行等列（與被計算的數(shù)組列數(shù)相等）的數(shù)組。

=BYCOL(數(shù)組,LAMBDA運算式)

數(shù)組，要分列或分行計算的數(shù)組。

LAMBDA運算式，按行或者列進(jìn)行聚合計算，只有一個變量參數(shù)。

注意：因為Excel函數(shù)不支持?jǐn)?shù)組的數(shù)組，所以BYROW(BYCOL)中LAMBDA每次運算結(jié)果須是單值。

2. BYROW和BYCOL基本用法

1）LAMBDA對行或者列進(jìn)行聚合運算

下方是銷售員各月的銷售額?，F(xiàn)在需要求每人最高月銷量，以及當(dāng)月最高單人銷量。

（1）各人最高月銷量

傳統(tǒng)做法就是輸入公式=MAX(C3:H3)并向下填充。

現(xiàn)在用BYROW，公式=BYROW(C3:H11,LAMBDA(R,MAX(R)))

說明：

LAMBDA(R,MAX(R))，依次掃描C3:H11中第1行、第2行、第3行等數(shù)據(jù)，并獲取各行MAX值。

公式不用向下填充，直接獲得各行的最大值。

（2）當(dāng)月最高單人銷量

用BYCOL，公式=BYCOL(C3:H11,LAMBDA(C,MAX(C))).

說明：

公式不用向右填充，直接向右逐列掃描獲得各列的最大值。

2）如果LAMBDA運算結(jié)果是多個值

如果公式中LAMBDA運算結(jié)果并非單值，則BYROW或BYCOL函數(shù)出現(xiàn)#CALC錯誤。

譬如求各人最高的兩月銷量。

公式=BYROW(C17:H25,LAMBDA(R,LARGE(R,{1,2})))，結(jié)果錯誤：

說明：

LARGE(R,{1,2})會返回每行第一、第二大的數(shù)值，輸出的并非單個值，因此出現(xiàn)#CALC錯誤。

這個時候，可以繼續(xù)嵌套聚合函數(shù)，讓值變成單個。

譬如此處在LARGE函數(shù)外嵌套文本組合函數(shù)TEXTJOIN函數(shù)，TEXTJOIN("、",,LARGE(R,{1,2}))，結(jié)果就正確了：

3. BYROW和BYCOL典型運用

似乎BYROW和BYCOL并沒有什么實際用處：求最大值中，用它們，公式比用傳統(tǒng)函數(shù)的公式還復(fù)雜。實際上BYROW或BYCOL的主要價值并非是直接用來得到最終結(jié)果，而是將它們的結(jié)果作為內(nèi)存數(shù)組供其他函數(shù)使用。

1）求平均月銷量大于280的人員

求平均銷量大于某個值的人員名單，肯定需要先得到各人的平均值，然后進(jìn)行比較篩選。

此時，如果用傳統(tǒng)函數(shù)就較繁瑣。

一種方法是增加一列平均銷量輔助列，然后用FILTER進(jìn)行篩選。

一種方法是用SUBTOTAL取代AVERAGE，并搭配OFFSET函數(shù)獲得平均值數(shù)組，然后篩選。公式比較復(fù)雜還不易理解：

=FILTER(B33:B41,SUBTOTAL(101,OFFSET(C32:H32,ROW(1:9),,1,6))>280)

說明：

不用輔助列，就需要把平均值作為數(shù)組用于公式中，但AVRERAGE是聚合函數(shù)，單用它沒法得到平均值數(shù)組。此處SUBTOTAL加OFFSET的作用就是得到平均值數(shù)組。這里有點神奇，大家可以留意。

單寫公式=OFFSET(C32:H32,ROW(1:9),,1,6)，因為函數(shù)不支持?jǐn)?shù)組的數(shù)組，所以結(jié)果會是#VALUE錯誤；但當(dāng)在外面嵌套上SUBTOTAL后，得到正確結(jié)果。

由于不存在隱藏行，因此SUBTOTAL函數(shù)功能代碼用1也可以。1和101都表示求平均值。

如果用今天的BYROW，則相對簡單并好理解：

2）求銷量前3位人員名單

如果不用動態(tài)數(shù)組函數(shù)，公式較復(fù)雜：

=XLOOKUP(LARGE(SUBTOTAL(9,OFFSET(C44:H44,ROW(1:9),,1,6)),{1;2;3}),SUBTOTAL(9,OFFSET(C44:H44,ROW(1:9),,1,6)),B45:B53)

即便用LET優(yōu)化，也比較長：

=LET(a,SUBTOTAL(9,OFFSET(C44:H44,ROW(1:9),,1,6)),XLOOKUP(LARGE(a,{1;2;3}),a,B45:B53))

如果用動態(tài)數(shù)組函數(shù)和BYROW結(jié)合，公式比較簡單：

公式=TAKE(SORT(HSTACK(B45:B53,BYROW(C33:H41,LAMBDA(x,SUM(x)))),2,-1),3,1)

說明：

BYROW(C33:H41,LAMBDA(x,SUM(x)))，獲得各行的銷售合計；

接著用HSTACK將姓名與銷售合計列組成新數(shù)組；

然后再用SORT函數(shù)對新數(shù)組降序排序；

最后用TAKE函數(shù)取排序后的第1列（姓名列）前3行。

本文配套的練習(xí)課件請?zhí)砑涌头⑿?span>buluowojiaoyu索取。

做Excel高手，快速提升工作效率，部落窩教育Excel精品好課任你選擇！

掃下方二維碼關(guān)注公眾號，可隨時隨地學(xué)習(xí)Excel：

相關(guān)推薦：

數(shù)據(jù)與表格合并函數(shù)HSTACK和VSTACK

數(shù)組提取函數(shù)TAKE和DROP

以一敵十的SUBTOTAL函數(shù)，你怎能錯過？

SORT函數(shù)排序比用排序命令還好用

版權(quán)申明：

本文作者小窩；部落窩教育享有稿件專有使用權(quán)。若需轉(zhuǎn)載請聯(lián)系部落窩教育。