有条件地根据相关表中的条目概括了GoogleSheet列
假设我在Google表中有两个相关的纸/标签。一个表/标签的标题为“类别”，另一个标题为“度量”。
类别：
| USERID | catcode |
|---|---|
| 1 | A |
| 1 | B |
| 2 | A |
| 3 | C |
度量：
| 用户ID | catcode | 点 |
|---|---|---|
| 1 | A | 5 |
| 1 | B | 5 |
| 1 | C | 3 |
| 2 | A | 4 |
| 3 | C | 3 |
的每个用户我希望能够从测量表中总结点类别表中的用户存在catcode的位置。理想情况下，使用自动扩展/填充公式（例如arrayformula或查询）。
我有些了解如何使用SQL语句（加入相关表，或选择存在的位置），但是我是Googlesheets的新手，并且会在这里欣赏一些方向。我已经对此进行了一些实验，并假设A列中使用用户ID的第三个表名为“用户”，我可以添加此公式：
= sum（filter（MEATE！C2：C4，MEATH！a2：a2：a4 =用户） ！
这种方法似乎与ArrayFormula不兼容，并且不允许我自动填充新添加的用户IDED的用户选项卡。总和本身显然与arrayformula不相容。此外，如果我将上述内容封闭在arrayformula中，并用sumproduct或其他某些方法替换总和来求和，我将无法吸引用户！a2引用将扩展到我期望通过诸如用户！a2：a：
任何帮助/方向都将不胜感激。谢谢！
Say I have two related sheets/tabs within a google sheet. One sheet/tab is titled "Categories", the other is "Measures".
Categories:
| userid | catcode |
|---|---|
| 1 | a |
| 1 | b |
| 2 | a |
| 3 | c |
Measures:
| userid | catcode | points |
|---|---|---|
| 1 | a | 5 |
| 1 | b | 5 |
| 1 | c | 3 |
| 2 | a | 4 |
| 3 | c | 3 |
For each user I'd like to be able to sum the points from the Measures table where the catcode is present for the user in the categories table. Ideally using an auto-extending/filling formula (like an arrayformula or query).
I have some idea how I'd approach this with SQL statements (joining the related tables, or doing a select where exists), but I'm new to googlesheets and would appreciate some direction here. I've experimented with this a bit and assuming a third table named "Users" with userids in column A, I can add this formula:
=sum(filter(measure!C2:C4, measure!A2:A4=users!A2, not(iserror(vlookup(measure!B2:B4, unique(filter(categories!B2:B5, categories!A2:A5=users!A2)), 1, false)))))
However this approach doesn't seem to be compatible with arrayformula and won't allow me to autofill down the Users tab for newly added userids. Sum itself is apparently incompatible with arrayformula. Additionally, if I enclose the above in arrayformula and replace sum with sumproduct or some other approach to the summation, I'm unable to get the users!A2 references to extend down as I'd expect via something like users!A2:A.
Any help/direction would be appreciated. Thanks!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
尝试：
Try: