首页 文章

谷歌Spreasheet QUERY功能我可以在ARRAYFORMULA中使用的东西

提问于
浏览 次
7

我有以下查询功能

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

C2和H2相对于每一行是唯一的（C2，C3，C4，......和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），转置（符号（列（C：C））） ））

所需的专栏是：

CHECK
162.5
80.75
1345
656
2033
2762
1757

如果我输入每一行公式：= SUMIFS（C $ 2：C，A $ 2：A，“=”＆A2，B $ 2：B，“<=”＆B2）当从第2行手动扩展时它正在工作

提前致谢 .

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

google-sheets array-formulas

1 回答

  • 1

    这是答案：

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

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

    Explanations

    我们使用 mmult 函数，它本身就是一个数组函数 . 这是Help Center info about mmult . 所以我们必须准备两个矩阵 . 带有条件的第一个矩阵，第二个带有Numbers的矩阵，我们已经有了 . 尝试输入此部分或公式以获取具有条件的矩阵：

    =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]和C.

    • A B * C - A OR [B和C]

    Conclusion

    这对我来说是一个真正的挑战 . 顺便说一句，我找到了5个不同的公式来计算它，但无法将它们转换为ArrayFormula：

    • 查询，问题中的公式

    • sumifs

    • sumproduct

    • sum（filter（...））

    • sum（if（A * B，...，0））

    看file with example

    回复于

相关问题