二維碼 購物車(chē)
部落窩在線(xiàn)教育歡迎您!

BYROW和BYCOL函數用法詳解

?

作者:小窩來(lái)源:部落窩教育發(fā)布時(shí)間:2024-04-16 11:10:19點(diǎn)擊:858

分享到:
0
收藏    收藏人氣:0人
版權說(shuō)明: 原創(chuàng )作品,禁止轉載。
編按:

BYROWBYCOL函數可以對區域中的數據逐行或者逐列進(jìn)行聚合運算,輸出一個(gè)單列或單行的數組。下面詳解這兩個(gè)函數的用法。

 

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

1.作用與語(yǔ)法

BYROW函數將LAMBDA算式應用于數組的每一行(可以理解為一行一行的計算),并返回一個(gè)單列等行(與被計算的數組行數相等)的數組。

=BYROW(數組,LAMBDA運算式)

BYCOL函數則是將LAMBDA算式應用于數組的每一列(可以理解為一列一列的計算),并返回一個(gè)單行等列(與被計算的數組列數相等)的數組。

=BYCOL(數組,LAMBDA運算式)

數組,要分列或分行計算的數組。

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

注意:因為Excel函數不支持數組的數組,所以BYROW(BYCOL)LAMBDA每次運算結果須是單值。

2. BYROWBYCOL基本用法

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

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

 

表格
描述已自動(dòng)生成

 

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

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

 

表格
描述已自動(dòng)生成

 

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

 

表格
描述已自動(dòng)生成

 

說(shuō)明:

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

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

2)當月最高單人銷(xiāo)量

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

 

表格
描述已自動(dòng)生成

 

說(shuō)明:

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

 

2)如果LAMBDA運算結果是多個(gè)值

如果公式中LAMBDA運算結果并非單值,則BYROWBYCOL函數出現#CALC錯誤。

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

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

 

表格
描述已自動(dòng)生成

 

說(shuō)明:

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

 

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

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

 

表格
描述已自動(dòng)生成

 

3. BYROWBYCOL典型運用

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

 

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

 

表格
描述已自動(dòng)生成

 

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

此時(shí),如果用傳統函數就較繁瑣。

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

 

表格
描述已自動(dòng)生成

 

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

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

 

表格
描述已自動(dòng)生成

 

說(shuō)明:

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

單寫(xiě)公式=OFFSET(C32:H32,ROW(1:9),,1,6),因為函數不支持數組的數組,所以結果會(huì )是#VALUE錯誤;但當在外面嵌套上SUBTOTAL后,得到正確結果。

 

圖形用戶(hù)界面, 表格
描述已自動(dòng)生成

 

由于不存在隱藏行,因此SUBTOTAL函數功能代碼用1也可以。1101都表示求平均值。

 

如果用今天的BYROW,則相對簡(jiǎn)單并好理解:

 

表格
描述已自動(dòng)生成

 

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

 

游戲界面截圖
低可信度描述已自動(dòng)生成

 

如果不用動(dòng)態(tài)數組函數,公式較復雜:

=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)化,也比較長(cháng):

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

 

表格
描述已自動(dòng)生成

 

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

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

 

表格
描述已自動(dòng)生成

 

說(shuō)明:

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

接著(zhù)用HSTACK將姓名與銷(xiāo)售合計列組成新數組;

然后再用SORT函數對新數組降序排序;

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

 

本文配套的練習課件請添加客服微信buluowojiaoyu索取。

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

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

IMG_256

相關(guān)推薦:

數據與表格合并函數HSTACK和VSTACK

數組提取函數TAKE和DROP

以一敵十的SUBTOTAL函數,你怎能錯過(guò)?

SORT函數排序比用排序命令還好用

版權申明:

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