用公式生成的数组是否不能作为SUMIFS的第一参数？该如何解决？

Holiday2022

在做论坛上[求个汇总公式-Excel函数与公式-ExcelHome技术论坛 -](https://club.excelhome.net/thread-1685982-1-1.html)这一道题时，遇到了一些问题。我将我的问题简化，在此向各位老师请教。

题目内容：

求酒店总收入。酒店总收入=该酒店下各技师收入之和，各技师收入=其科目A+科目B+科目C之和。

我自己提出的要求：

1. 需要用一个公式得到各酒店的总收入，将最终结果横向（HSTACK）堆叠


我的解题步骤：

1. 先对A列用UNIQUE函数取唯一值

1.    UNIQUE(A2:A31)

复制代码

2. 用BYROW函数计算每一行的和，得到各技师三项收入：

1.   BYROW(科目得分,LAMBDA(x,SUM(x)))

复制代码

3. 堆叠酒店名称、技师名称、各技师收入，并记为构建新表：

1.    HSTACK(酒店和相应技师,各技师三项收入)

复制代码

4. 用SUMIFS求酒店总收入

1.    SUMIFS(TAKE(构建新表,,-1),TAKE(构建新表,,1),酒店名称)

复制代码

5. 用HSTACK将结果1和4横向堆叠。

将上述公式整合起来就是：

1. =LET(酒店名称,UNIQUE(A2:A31),
   
2.     酒店和相应技师,A2:B31,
   
3.     科目得分,C2:E31,
   
4.     各技师三项收入,BYROW(科目得分,LAMBDA(x,SUM(x))),
   
5.     构建新表,HSTACK(酒店和相应技师,各技师三项收入),
   
6.     SUMIFS(TAKE(构建新表,,-1),TAKE(构建新表,,1),酒店名称)
   
7.     )

复制代码

遇到的困难，即问题1：

在上述第4步中，公式为SUMIFS()的第一参数似乎不支持由别的函数组成的数组（我记得COUNTIFS也是这个尿性），结果为`#VALUE!`。而当我把上述步骤用虚拟列表式出来时，SUMIFS()的结果是正确的。我该如何解决这一问题呢？

（2024-03-07补充：上面的说法更合适。SUMIFS()的第一参数，支持多维引用，不支持内存数组）



问题2：

为解决问题1，我自己也尝试了一个方法，见sheet2，我通过scan来循环c列，公式是：

1. =SCAN(0,C2:C31,IF(OFFSET(y,,-2)=OFFSET(y,-1,-2),x+y,y))

复制代码

结果显示`#VALUE!`。请帮我看看问题出在了哪里？用修改后的公式能解决问题1吗？

miaojohn45

1. =LET(酒店名称,UNIQUE(A2:A31),各技师收入,BYROW(C2:E31,LAMBDA(x,SUM(x))),REDUCE({"酒店","总收入"},酒店名称,LAMBDA(x,y,VSTACK(x,HSTACK(y,SUM(FILTER(各技师收入,A2:A31=y)))))))

复制代码

1. =SCAN(0,C2:C31,LAMBDA(x,y,IF(OFFSET(y,,-2)=OFFSET(y,-1,-2),x+y,y)))

复制代码

jivy1212

1. =SUM(SUMIF(A:A,L2,OFFSET(C:C,,{0,1,2})))

复制代码

为何要写这么复杂，新函数并不是万能的

Holiday2022

miaojohn45 发表于 2024-3-5 08:26

我天，马大哈忘了lambda

Holiday2022

jivy1212 发表于 2024-3-5 08:29
为何要写这么复杂，新函数并不是万能的

我想要在问题1里的使用这个公式。如果用辅助列的形式，直接sumifs我就能解决了。

hxj119

参考公式：=SUMPRODUCT((A2=Sheet1!$A$2:$A$31)*(B2=Sheet1!$B$2:$B$31)*Sheet1!$C$2:$E$31)

Holiday2022

hxj119 发表于 2024-3-5 08:46
参考公式：=SUMPRODUCT((A2=Sheet1!$A$2:$A$31)*(B2=Sheet1!$B$2:$B$31)*Sheet1!$C$2:$E$31)

我想要在问题1里的使用这个公式。如果用辅助列的形式，直接sumifs我就能解决了。

Holiday2022

各位老师好，问题2已解决，我想知道的是SUMIFS()的第一参数是否不能是内存数组？如果不用我在问题2中提出的方法，有无其他更好的方法来代替问题1中的SUMIFS()？

hxj119

L2=UNIQUE(A2:A31)
M2=SUMPRODUCT(($A$2:$A$31=L2)*$C$2:$E$31)

无关岁月

1. =GROUPBY(A1:A31,BYROW(C1:E31,SUM),SUM,1,0)

复制代码