gpt4 book ai didi

google-sheets - 谷歌电子表格查询函数到我可以在数组公式中使用的东西

转载 作者:行者123 更新时间:2023-12-03 18:37:34 27 4
gpt4 key购买 nike

我有以下查询功能

=QUERY(C:J,"SELECT SUM(J) WHERE C='"&C2&"' AND H<=date'"&TEXT(H2,"yyyy-MM-dd")&"' LABEL SUM(J) ''",0)

WHERE C2 and H2 relative and on each row are unique (C2,C3,C4,... and H2,H3,H4...)

我需要将它放入 ARRAYFORMULA,所以它会在每一行上产生一些结果

我已经花了一整天的时间,用 SUMIFS 检查所有其他解决方案,并尝试用 MMULT 重写公式,但没有任何成功。

这是简化的示例:
Proj    Date        Hours
APROJ 6/29/2015 81.75
APROJ 6/22/2015 80.75
BPROJ 8/3/2015 689
BPROJ 8/2/2015 656
BPROJ 8/10/2015 688
BPROJ 8/11/2015 729
CPROJ 8/12/2015 1757

我的尝试没有成功: =arrayformula(mmult((A:A=A1:A)*(B:B<=B1:B)*N(C:C),transpose(sign(column(C:C))) ))

所需的列是:
CHECK
162.5
80.75
1345
656
2033
2762
1757

如果我在每一行中输入公式:=SUMIFS(C$2:C,A$2:A,"="&A2,B$2:B,"<="&B2) 从第二个开始手动扩展时它正在工作

提前致谢。

如果您想玩,只需复制给自己: https://docs.google.com/spreadsheets/d/12F4EsHvkiZb5gAPVo_uosd2YpZ1nw9QED_JlSAcVQYU/edit?usp=sharing

最佳答案

答案如下:

=MMULT(TRANSPOSE(ArrayFormula(--($A$2:$A$8=TRANSPOSE(A2:A8))*--($B$2:$B$8<=TRANSPOSE(B2:B8)))),C2:C8)

这个公式可以转换成更通用的公式,动态计算范围,即 OFFSET(A2,,,COUNTA(A2:A)) .

说明

我们使用 mmult函数本身就是一个数组函数。这是 Help Center info about mmult .所以我们必须准备两个矩阵。第一个带条件的矩阵,第二个带数字的矩阵,我们已经有了。尝试输入此部分或公式以获取带条件的矩阵:
=ArrayFormula(--($A$2:$A$8=TRANSPOSE(A2:A8))*--($B$2:$B$8<=TRANSPOSE(B2:B8)))

在这一部分,我们得到了 Zoros 和 Ones 的表格/矩阵。我更喜欢使用“--”将 Boolaen 转换为整数: related question

必须使用其中的转置才能使公式展开。这是主要的技巧。 Arrayformula 不能用于比较两个垂直数组。所以你必须调换其中之一。
在您的问题中,我们有两个带有 AND 逻辑的条件:A * B。所以我们将条件的两部分相乘。但我认为,可以添加超过 2 个条件。
如果您想制作 OR 条件,则需要将它们添加为 A + B。一些示例:
  • (A + B) * C -- [A 或 B] AND C
  • A + B * C -- A 或 [B 和 C]


  • 结论

    这对我来说是一个真正的挑战。顺便说一下,我找到了 5 个不同的公式来计算这个,但无法将它们转换为 ArrayFormula:
  • 查询,您问题中的公式
  • sumifs
  • sumproduct
  • 总和(过滤器(...))
  • sum(if(A*B, ... , 0))

  • file with example

    关于google-sheets - 谷歌电子表格查询函数到我可以在数组公式中使用的东西,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/31987283/

    27 4 0
    Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
    广告合作:1813099741@qq.com 6ren.com