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

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

1.作用與語(yǔ)法

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

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

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

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

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

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

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

2. BYROW和BYCOL基本用法

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

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

（1）各人最高月銷(xiāo)量

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

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

說(shuō)明：

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

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

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

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

說(shuō)明：

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

2）如果LAMBDA運(yùn)算結(jié)果是多個(gè)值

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

譬如求各人最高的兩月銷(xiāo)量。

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

說(shuō)明：

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

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

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

3. BYROW和BYCOL典型運(yùn)用

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

1）求平均月銷(xiāo)量大于280的人員

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

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

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

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

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

說(shuō)明：

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

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

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

如果用今天的BYROW，則相對(duì)簡(jiǎn)單并好理解：

2）求銷(xiāo)量前3位人員名單

如果不用動(dòng)態(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)化，也比較長(zhǎng)：

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

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

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

說(shuō)明：

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

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

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

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

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

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

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

相關(guān)推薦：

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

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

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

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

版權(quán)申明：

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