根据第一列中的数据对第二列中的数字求和

发布于 2025-01-11 02:25:37 字数 928 浏览 1 评论 0原文

          A                       B                       C
1     Timestamp               Hours Worked            Total Hours
2     1/2/2022 17:33:41       6
3     1/3/2022 19:59:41       2                       January = 
4     1/7/2022 13:20:51       1
5     1/9/2022 12:49:02       3                       February = 
6     1/15/2022 12:04:21      3
7     2/16/2022 15:58:10      9                       March = 
8     2/22/2022 11:57:31      3
9     2/24/2022 5:45:12       5
10    2/2/2022 17:33:41       4
11    2/3/2022 19:59:41       2
12    2/7/2022 13:20:51       1
13    3/9/2022 12:49:02       3
14    3/15/2022 12:04:28      3
15    3/16/2022 15:58:10      7
16    3/22/2022 11:57:31      3
17    3/24/2022 5:45:12       5

我想按日期/月份汇总小时数。我可以通过以下方式总结小时数列，=sum(b2:b)。但是我如何将 B 列中的时间求和 A 列的日期/月份？

在另一篇文章中，一名成员引用了“Google Sheets 功能列表”。我发现这对我一直在做的其他事情非常有帮助。但我仍然对这个问题不知所措。

约翰

原文

          A                       B                       C
1     Timestamp               Hours Worked            Total Hours
2     1/2/2022 17:33:41       6
3     1/3/2022 19:59:41       2                       January = 
4     1/7/2022 13:20:51       1
5     1/9/2022 12:49:02       3                       February = 
6     1/15/2022 12:04:21      3
7     2/16/2022 15:58:10      9                       March = 
8     2/22/2022 11:57:31      3
9     2/24/2022 5:45:12       5
10    2/2/2022 17:33:41       4
11    2/3/2022 19:59:41       2
12    2/7/2022 13:20:51       1
13    3/9/2022 12:49:02       3
14    3/15/2022 12:04:28      3
15    3/16/2022 15:58:10      7
16    3/22/2022 11:57:31      3
17    3/24/2022 5:45:12       5

I would like to sum the hours by date/month. I can sum the hours by
column, =sum(b2:b). But how do I sum the hours in Column B by
date/month of column A?

In another post, a member referenced the 'Google Sheets function list'.
I have found that to be very helpful on other things I have been doing.
But I am still at a lose for this problem.

John

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

风为裳 2025-01-18 02:25:37

您可以将 query 函数与 SQL 结合使用：

=QUERY({(data)}, "select sum(Col2) group by Col1 label Col1 'Date', Col2 'Sum'")

这将返回一个新表，并且您可以通过更改代表 SQL 字符串的第二个参数来调整它。您可以在此处查看有关查询功能的 Google 文档。此外，您还可以在此处找到有关查询语言的文档。

You could use the query function with SQL:

=QUERY({(data)}, "select sum(Col2) group by Col1 label Col1 'Date', Col2 'Sum'")

That would return a new table, and you can adjust it by changing the second parameter, representing the SQL string. You can view Google's docs for the query function here. In addition you can find their docs for the query language here.

回复收藏 0 原文

大姐，你呐 2025-01-18 02:25:37

最简单的解决方案（无需任何公式）是构建数据透视表。

回复收藏 0 原文

﹏半生如梦愿梦如真 2025-01-18 02:25:37

使用：

=ARRAYFORMULA(REGEXREPLACE(TO_TEXT(QUERY({TEXT(A2:A, "yyymm\×mmmm"), B2:B}, 
 "select Col1,sum(Col2) where Col2 is not null group by Col1 label sum(Col2)''")), "(.*×)", ))

use:

=ARRAYFORMULA(REGEXREPLACE(TO_TEXT(QUERY({TEXT(A2:A, "yyymm\×mmmm"), B2:B}, 
 "select Col1,sum(Col2) where Col2 is not null group by Col1 label sum(Col2)''")), "(.*×)", ))

回复收藏 0 原文

~没有更多了~