二维码 购物车
部落窝在线教育欢迎您!

BYROW和BYCOL函数用法详解

 

作者:小窝来源:部落窝教育发布时间:2024-04-16 11:10:19点击:550

分享到:
0
收藏    收藏人气:0人
版权说明: 原创作品,禁止转载。
编按:

BYROWBYCOL函数可以对区域中的数据逐行或者逐列进行聚合运算,输出一个单列或单行的数组。下面详解这两个函数的用法。

 

今天学习LAMBDA最后两个配套函数BYROWBYCOL。这两个函数相比其他四个配套函数更简单易懂,并且两者参数和用法类似,所以小窝将它们放在一起介绍。

1.作用与语法

BYROW函数将LAMBDA算式应用于数组的每一行(可以理解为一行一行的计算),并返回一个单列等行(与被计算的数组行数相等)的数组。

=BYROW(数组,LAMBDA运算式)

BYCOL函数则是将LAMBDA算式应用于数组的每一列(可以理解为一列一列的计算),并返回一个单行等列(与被计算的数组列数相等)的数组。

=BYCOL(数组,LAMBDA运算式)

数组,要分列或分行计算的数组。

LAMBDA运算式,按行或者列进行聚合计算,只有一个变量参数。

注意:因为Excel函数不支持数组的数组,所以BYROW(BYCOL)LAMBDA每次运算结果须是单值。

2. BYROWBYCOL基本用法

1LAMBDA对行或者列进行聚合运算

下方是销售员各月的销售额。现在需要求每人最高月销量,以及当月最高单人销量。

 

表格
描述已自动生成

 

1)各人最高月销量

传统做法就是输入公式=MAX(C3:H3)并向下填充。

 

表格
描述已自动生成

 

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

 

表格
描述已自动生成

 

说明:

LAMBDA(R,MAX(R)),依次扫描C3:H11中第1行、第2行、第3行等数据,并获取各行MAX值。

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

2)当月最高单人销量

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

 

表格
描述已自动生成

 

说明:

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

 

2)如果LAMBDA运算结果是多个值

如果公式中LAMBDA运算结果并非单值,则BYROWBYCOL函数出现#CALC错误。

譬如求各人最高的两月销量。

公式=BYROW(C17:H25,LAMBDA(R,LARGE(R,{1,2}))),结果错误:

 

表格
描述已自动生成

 

说明:

LARGE(R,{1,2})会返回每行第一、第二大的数值,输出的并非单个值,因此出现#CALC错误。

 

这个时候,可以继续嵌套聚合函数,让值变成单个。

譬如此处在LARGE函数外嵌套文本组合函数TEXTJOIN函数,TEXTJOIN("",,LARGE(R,{1,2})),结果就正确了:

 

表格
描述已自动生成

 

3. BYROWBYCOL典型运用

似乎BYROWBYCOL并没有什么实际用处:求最大值中,用它们,公式比用传统函数的公式还复杂。实际上BYROWBYCOL的主要价值并非是直接用来得到最终结果,而是将它们的结果作为内存数组供其他函数使用。

 

1)求平均月销量大于280的人员

 

表格
描述已自动生成

 

求平均销量大于某个值的人员名单,肯定需要先得到各人的平均值,然后进行比较筛选。

此时,如果用传统函数就较繁琐。

一种方法是增加一列平均销量辅助列,然后用FILTER进行筛选。

 

表格
描述已自动生成

 

一种方法是用SUBTOTAL取代AVERAGE,并搭配OFFSET函数获得平均值数组,然后筛选。公式比较复杂还不易理解:

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

 

表格
描述已自动生成

 

说明:

不用辅助列,就需要把平均值作为数组用于公式中,但AVRERAGE是聚合函数,单用它没法得到平均值数组。此处SUBTOTALOFFSET的作用就是得到平均值数组。这里有点神奇,大家可以留意。

单写公式=OFFSET(C32:H32,ROW(1:9),,1,6),因为函数不支持数组的数组,所以结果会是#VALUE错误;但当在外面嵌套上SUBTOTAL后,得到正确结果。

 

图形用户界面, 表格
描述已自动生成

 

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

 

如果用今天的BYROW,则相对简单并好理解:

 

表格
描述已自动生成

 

2)求销量前3位人员名单

 

游戏界面截图
低可信度描述已自动生成

 

如果不用动态数组函数,公式较复杂:

=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优化,也比较长:

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

 

表格
描述已自动生成

 

如果用动态数组函数和BYROW结合,公式比较简单:

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

 

表格
描述已自动生成

 

说明:

BYROW(C33:H41,LAMBDA(x,SUM(x))),获得各行的销售合计;

接着用HSTACK将姓名与销售合计列组成新数组;

然后再用SORT函数对新数组降序排序;

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

 

本文配套的练习课件请添加客服微信buluowojiaoyu索取。

Excel高手,快速提升工作效率,部落窝教育Excel精品好课任你选择

扫下方二维码关注公众号,可随时随地学习Excel

IMG_256

相关推荐:

数据与表格合并函数HSTACK和VSTACK

数组提取函数TAKE和DROP

以一敌十的SUBTOTAL函数,你怎能错过?

SORT函数排序比用排序命令还好用

版权申明:

本文作者小窝;部落窝教育享有稿件专有使用权。若需转载请联系部落窝教育。