发布日期：2024-10-14 16:46 点击次数：159
文 前 推 荐
表格合并、跨表查找就HSTACK和VSTACK数组提取函数TAKE和DROP以一敌十的SUBTOTAL函数，你怎能错过？SORT函数排序比用排序命令还好用图片
图片
编按：
BYROW和BYCOL函数可以对区域中的数据逐行或者逐列进行聚合运算，输出一个单列或单行的数组。下面详解这两个函数的用法。
今天学习LAMBDA最后两个配套函数BYROW和BYCOL。这两个函数相比其他四个配套函数更简单易懂，并且两者参数和用法类似，所以小窝将它们放在一起介绍。1. 作用与语法BYROW函数将LAMBDA算式应用于数组的每一行（可以理解为一行一行的计算），并返回一个单列等行（与被计算的数组行数相等）的数组。=BYROW(数组,LAMBDA运算式)BYCOL函数则是将LAMBDA算式应用于数组的每一列（可以理解为一列一列的计算），并返回一个单行等列（与被计算的数组列数相等）的数组。=BYCOL(数组,LAMBDA运算式)数组，要分列或分行计算的数组。LAMBDA运算式，按行或者列进行聚合计算，只有一个变量参数。注意：因为Excel函数不支持数组的数组，所以BYROW(BYCOL)中LAMBDA每次运算结果须是单值。2. BYROW和BYCOL基本用法1）LAMBDA对行或者列进行聚合运算下方是销售员各月的销售额。现在需要求每人最高月销量，以及当月最高单人销量。图片
（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运算结果并非单值，则BYROW或BYCOL函数出现#CALC错误。譬如求各人最高的两月销量。公式=BYROW(C17:H25,LAMBDA(R,LARGE(R,{1,2})))，结果错误：图片
说明：LARGE(R,{1,2})会返回每行第一、第二大的数值，输出的并非单个值，因此出现#CALC错误。这个时候，可以继续嵌套聚合函数，让值变成单个。譬如此处在LARGE函数外嵌套文本组合函数TEXTJOIN函数，TEXTJOIN("、",,LARGE(R,{1,2}))，结果就正确了：图片
3. BYROW和BYCOL典型运用似乎BYROW和BYCOL并没有什么实际用处：求最大值中，用它们，公式比用传统函数的公式还复杂。实际上BYROW或BYCOL的主要价值并非是直接用来得到最终结果，而是将它们的结果作为内存数组供其他函数使用。1）求平均月销量大于280的人员图片
求平均销量大于某个值的人员名单，肯定需要先得到各人的平均值，然后进行比较筛选。此时，如果用传统函数就较繁琐。一种方法是增加一列平均销量辅助列，然后用FILTER进行筛选。图片
一种方法是用SUBTOTAL取代AVERAGE，并搭配OFFSET函数获得平均值数组，然后筛选。公式比较复杂还不易理解：=FILTER(B33:B41,SUBTOTAL(101,OFFSET(C32:H32,ROW(1:9),,1,6))>280)图片
说明：不用辅助列，就需要把平均值作为数组用于公式中，但AVRERAGE是聚合函数，单用它没法得到平均值数组。此处SUBTOTAL加OFFSET的作用就是得到平均值数组。这里有点神奇，大家可以留意。单写公式=OFFSET(C32:H32,ROW(1:9),,1,6)，因为函数不支持数组的数组，所以结果会是#VALUE错误；但当在外面嵌套上SUBTOTAL后，得到正确结果。图片
由于不存在隐藏行，因此SUBTOTAL函数功能代码用1也可以。1和101都表示求平均值。如果用今天的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行。本站仅提供存储服务，所有内容均由用户发布，如发现有害或侵权内容，请点击举报。