當前位置:首頁 > 科技 > 正文

Excel新函數BYROW和BYCOL函數用法詳解:逐行逐列批量運算

文 前 推 薦

表格合并、跨表查找就和

數組提取函數TAKE和DROP以一敵十的函數,你怎能錯過?SORT函數排序比用排序命令還好用

編按:

BYROW和BYCOL函數可以對區域中的數據逐行或者逐列進行聚合運算,輸出一個單列或單行的數組。下面詳解這兩個函數的用法。

今天學習最後兩個配套函數BYROW和BYCOL。這兩個函數相比其他四個配套函數更簡單易懂,并且兩者參數和用法類似,所以小窩将它們放在一起介紹。

1. 作用與語法BYROW函數将算式應用于數組的每一行(可以理解為一行一行的計算),并返回一個單列等行(與被計算的數組行數相等)的數組。=BYROW(數組,運算式)BYCOL函數則是将算式應用于數組的每一列(可以理解為一列一列的計算),并返回一個單行等列(與被計算的數組列數相等)的數組。=BYCOL(數組,運算式)數組,要分列或分行計算的數組。運算式,按行或者列進行聚合計算,隻有一個變量參數。注意:因為Excel函數不支持數組的數組,所以BYROW(BYCOL)中每次運算結果須是單值。2.BYROW和BYCOL基本用法1)對行或者列進行聚合運算下方是銷售員各月的銷售額。現在需要求每人最高月銷量,以及當月最高單人銷量。

Excel新函數BYROW和BYCOL函數用法詳解:逐行逐列批量運算

(1)各人最高月銷量傳統做法就是輸入公式=MAX(C3:H3)并向下填充。

Excel新函數BYROW和BYCOL函數用法詳解:逐行逐列批量運算

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

Excel新函數BYROW和BYCOL函數用法詳解:逐行逐列批量運算

說明:(R,MAX(R)),依次掃描C3:H11中第1行、第2行、第3行等數據,并獲取各行MAX值。公式不用向下填充,直接獲得各行的最大值。(2)當月最高單人銷量用BYCOL,公式=BYCOL(C3:H11,(C,MAX(C))).

Excel新函數BYROW和BYCOL函數用法詳解:逐行逐列批量運算

說明:公式不用向右填充,直接向右逐列掃描獲得各列的最大值。2)如果運算結果是多個值如果公式中運算結果并非單值,則BYROW或BYCOL函數出現#CALC錯誤。譬如求各人最高的兩月銷量。公式=BYROW(C17:H25,(R,LARGE(R,{1,2}))),結果錯誤:

Excel新函數BYROW和BYCOL函數用法詳解:逐行逐列批量運算

說明:LARGE(R,{1,2})會返回每行第一、第二大的數值,輸出的并非單個值,因此出現#CALC錯誤。這個時候,可以繼續嵌套聚合函數,讓值變成單個。譬如此處在LARGE函數外嵌套文本組合函數函數,("、",,LARGE(R,{1,2})),結果就正确了:

Excel新函數BYROW和BYCOL函數用法詳解:逐行逐列批量運算

3.BYROW和BYCOL典型運用似乎BYROW和BYCOL并沒有什麼實際用處:求最大值中,用它們,公式比用傳統函數的公式還複雜。實際上BYROW或BYCOL的主要價值并非是直接用來得到最終結果,而是将它們的結果作為内存數組供其他函數使用。1)求平均月銷量大于280的人員

Excel新函數BYROW和BYCOL函數用法詳解:逐行逐列批量運算

求平均銷量大于某個值的人員名單,肯定需要先得到各人的平均值,然後進行比較篩選。此時,如果用傳統函數就較繁瑣。一種方法是增加一列平均銷量輔助列,然後用進行篩選。

Excel新函數BYROW和BYCOL函數用法詳解:逐行逐列批量運算

一種方法是用取代,并搭配函數獲得平均值數組,然後篩選。公式比較複雜還不易理解:=(B33:B41,(101,(C32:H32,ROW(1:9),,1,6))>280)

Excel新函數BYROW和BYCOL函數用法詳解:逐行逐列批量運算

說明:不用輔助列,就需要把平均值作為數組用于公式中,但是聚合函數,單用它沒法得到平均值數組。此處加的作用就是得到平均值數組。這裡有點神奇,大家可以留意。單寫公式=(C32:H32,ROW(1:9),,1,6),因為函數不支持數組的數組,所以結果會是#VALUE錯誤;但當在外面嵌套上後,得到正确結果。

Excel新函數BYROW和BYCOL函數用法詳解:逐行逐列批量運算

由于不存在隐藏行,因此函數功能代碼用1也可以。1和101都表示求平均值。如果用今天的BYROW,則相對簡單并好理解:

Excel新函數BYROW和BYCOL函數用法詳解:逐行逐列批量運算

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

Excel新函數BYROW和BYCOL函數用法詳解:逐行逐列批量運算

如果不用動态數組函數,公式較複雜:=(LARGE((9,(C44:H44,ROW(1:9),,1,6)),{1;2;3}),(9,(C44:H44,ROW(1:9),,1,6)),B45:B53)即便用LET優化,也比較長:=LET(a,(9,(C44:H44,ROW(1:9),,1,6)),(LARGE(a,{1;2;3}),a,B45:B53))

Excel新函數BYROW和BYCOL函數用法詳解:逐行逐列批量運算

如果用動态數組函數和BYROW結合,公式比較簡單:公式=TAKE(SORT((B45:B53,BYROW(C33:H41,(x,SUM(x)))),2,-1),3,1)

Excel新函數BYROW和BYCOL函數用法詳解:逐行逐列批量運算

說明:BYROW(C33:H41,(x,SUM(x))),獲得各行的銷售合計;接着用将姓名與銷售合計列組成新數組;然後再用SORT函數對新數組降序排序;最後用TAKE函數取排序後的第1列(姓名列)前3行。

你可能想看:

有話要說...

取消
掃碼支持 支付碼