Excel新函數BYROW和BYCOL函數用法詳解：逐行逐列批量運算
文 前 推 薦
表格合并、跨表查找就和
數組提取函數TAKE和DROP以一敵十的函數，你怎能錯過？SORT函數排序比用排序命令還好用
編按：
BYROW和BYCOL函數可以對區域中的數據逐行或者逐列進行聚合運算，輸出一個單列或單行的數組。下面詳解這兩個函數的用法。
今天學習最後兩個配套函數BYROW和BYCOL。這兩個函數相比其他四個配套函數更簡單易懂，并且兩者參數和用法類似，所以小窩将它們放在一起介紹。
1. 作用與語法BYROW函數将算式應用于數組的每一行（可以理解為一行一行的計算），并返回一個單列等行（與被計算的數組行數相等）的數組。=BYROW(數組,運算式)BYCOL函數則是将算式應用于數組的每一列（可以理解為一列一列的計算），并返回一個單行等列（與被計算的數組列數相等）的數組。=BYCOL(數組,運算式)數組，要分列或分行計算的數組。運算式，按行或者列進行聚合計算，隻有一個變量參數。注意：因為Excel函數不支持數組的數組，所以BYROW(BYCOL)中每次運算結果須是單值。2.BYROW和BYCOL基本用法1）對行或者列進行聚合運算下方是銷售員各月的銷售額。現在需要求每人最高月銷量，以及當月最高單人銷量。
（1）各人最高月銷量傳統做法就是輸入公式=MAX(C3:H3)并向下填充。
現在用BYROW，公式=BYROW(C3:H11,(R,MAX(R)))
說明：(R,MAX(R))，依次掃描C3:H11中第1行、第2行、第3行等數據，并獲取各行MAX值。公式不用向下填充，直接獲得各行的最大值。（2）當月最高單人銷量用BYCOL，公式=BYCOL(C3:H11,(C,MAX(C))).
說明：公式不用向右填充，直接向右逐列掃描獲得各列的最大值。2）如果運算結果是多個值如果公式中運算結果并非單值，則BYROW或BYCOL函數出現#CALC錯誤。譬如求各人最高的兩月銷量。公式=BYROW(C17:H25,(R,LARGE(R,{1,2})))，結果錯誤：
說明：LARGE(R,{1,2})會返回每行第一、第二大的數值，輸出的并非單個值，因此出現#CALC錯誤。這個時候，可以繼續嵌套聚合函數，讓值變成單個。譬如此處在LARGE函數外嵌套文本組合函數函數，("、",,LARGE(R,{1,2}))，結果就正确了：
3.BYROW和BYCOL典型運用似乎BYROW和BYCOL并沒有什麼實際用處：求最大值中，用它們，公式比用傳統函數的公式還複雜。實際上BYROW或BYCOL的主要價值并非是直接用來得到最終結果，而是将它們的結果作為内存數組供其他函數使用。1）求平均月銷量大于280的人員
求平均銷量大于某個值的人員名單，肯定需要先得到各人的平均值，然後進行比較篩選。此時，如果用傳統函數就較繁瑣。一種方法是增加一列平均銷量輔助列，然後用進行篩選。
一種方法是用取代，并搭配函數獲得平均值數組，然後篩選。公式比較複雜還不易理解：=(B33:B41,(101,(C32:H32,ROW(1:9),,1,6))>280)
說明：不用輔助列，就需要把平均值作為數組用于公式中，但是聚合函數，單用它沒法得到平均值數組。此處加的作用就是得到平均值數組。這裡有點神奇，大家可以留意。單寫公式=(C32:H32,ROW(1:9),,1,6)，因為函數不支持數組的數組，所以結果會是#VALUE錯誤；但當在外面嵌套上後，得到正确結果。
由于不存在隐藏行，因此函數功能代碼用1也可以。1和101都表示求平均值。如果用今天的BYROW，則相對簡單并好理解：
2）求銷量前3位人員名單
如果不用動态數組函數，公式較複雜：=(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))
如果用動态數組函數和BYROW結合，公式比較簡單：公式=TAKE(SORT((B45:B53,BYROW(C33:H41,(x,SUM(x)))),2,-1),3,1)
說明：BYROW(C33:H41,(x,SUM(x)))，獲得各行的銷售合計；接着用将姓名與銷售合計列組成新數組；然後再用SORT函數對新數組降序排序；最後用TAKE函數取排序後的第1列（姓名列）前3行。
最新文章
-
太師修行問答48：人生隻是演戲瑜伽也能修禅，能達到幾禅
7分鐘前 -
![[書法]閑暇草書彙03](https://m.74hao.com/zb_users/upload/2024/10/202410161729086639361289.png)
[書法]閑暇草書彙03
24分鐘前 -
3D技巧：兩碼和差選号法
46分鐘前 -
“落日無情最有情，遍催萬樹暮蟬鳴”楊萬裡詩五首，風格清新自然
1小時前 -
《玄理賦》五行生克的太過不及 等
1小時前 -
《老山1984-1989，中越老山之戰實錄》連載之二：118團8連收敲掉54
2小時前 -
攝影：故鄉的秋色（2）
2小時前 -
論入宅歸火擇吉（續）第二，坐向要吉 坐向要吉，主要是指所選日課
2小時前
有話要說...