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行。