Google表格 - 根据列值和最后日期获取分组行的子集

发布于 2025-02-11 19:11:55 字数 1154 浏览 2 评论 0原文

我有一个带有交易列表的Google表。

对于每一行，我都有一个日期，帐户及其相对余额。

日期	帐户	余额
1-MAR-2022	税	$ 100
10-FEB-2022	费用	$ 6
1-FEB	-2022费用	$ 20
6-1月2022	税	$ 10
3-JAN-2022	税	$ 1
10-JAN-2022	费用	$ 50
1-JAN-JAN-20222	费用	$ 30，

我需要获得的是根据按以下日期按日期分组的每个帐户的最后一个余额值的子集的表。

日期	帐户	余额
20122年	税	$ $ 100
2月100日，	费用	$ 6
JAN-2022	税	$ 10
JAN-2022	费用	$ 50

我获得的最接近的东西是每个帐户的所有最低和最大值的列表，但不是，但不是返回我要搜索的准确值的最后一个值。

有什么方法可以使用最后一个值的聚合函数？

= QUERY（DATASET，“选择b，月（a），最大（c），min（c），b，按月（a）按月（a）desc订购”，1），1）

谢谢提前

原文

I have a Google sheet with a list of transactions.

For each row, I've got a date, an account, and its relative balance.

Date	Account	Balance
1-Mar-2022	TAX	$100
10-Feb-2022	EXPENSES	$6
1-Feb-2022	EXPENSES	$20
6-Jan-2022	TAX	$10
3-Jan-2022	TAX	$1
10-Jan-2022	EXPENSES	$50
1-Jan-2022	EXPENSES	$30

What I need to get is a table based on a subset of the last balance value for each account grouped by date like the following.

Date	Account	Balance
Mar-2022	TAX	$100
Feb-2022	EXPENSES	$6
Jan-2022	TAX	$10
Jan-2022	EXPENSES	$50

The closest thing I've got is a list of all the MIN and MAX values for each account for each month but it is not returning the last value that is the accurate one I'm searching for.

Is there any way to use an aggregate function with the last value?

=QUERY(DATASET, "SELECT B, MONTH(A), MAX(C), MIN(C) GROUP BY B,MONTH(A) ORDER BY MONTH(A) DESC",1)

Thanks in advance

分享到QQ

分享到微博

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

发布评论

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

初懵 2025-02-18 19:11:55

这有点晚了，但是假设数据集在A1：C8中，以下公式会生成所需的结果表：

=arrayformula({{"Date","Account","Balance"};query(ifna(vlookup(flatten(unique(month(A2:A8))&transpose(unique(B2:B8))),{month(index(sort(A2:C8,1,0),,1))&index(sort(A2:C8,1,0),,2),sort(A2:C8,1,0)},{2,3,4},0)),"where Col1 is not null")})

使用仅显示月份和年份的自定义日期格式格式化日期列，否则您将获得完整的日期。这是有点复杂的解释，但是我们要做的是使用交叉加入在垂直阵列中创建每个月和帐户类型的每个组合带有附加列的源表，包含一个月和帐户相互串联 - 因此，我们获得了每个有效组合的最新日期。 IFNA和查询，然后将结果整理好，删除Vlookup找不到结果的行。

This is a bit late, but assuming the dataset is in A1:C8, the following formula generates the required result table:

=arrayformula({{"Date","Account","Balance"};query(ifna(vlookup(flatten(unique(month(A2:A8))&transpose(unique(B2:B8))),{month(index(sort(A2:C8,1,0),,1))&index(sort(A2:C8,1,0),,2),sort(A2:C8,1,0)},{2,3,4},0)),"where Col1 is not null")})

Format the date column using a custom date format which only shows the month and year, otherwise you'll get full dates. This one is a bit complicated to explain, but what we're doing is to use a cross-join to create every combination of month and account type in a vertical array, then doing a VLOOKUP using this array against a reverse-sorted copy of the source table with an additional column containing the month and account concatenated to each other - so we get the most recent date for each valid combination. The IFNA and QUERY then just tidy the results up, removing the rows where the VLOOKUP didn't find a result.

回复收藏 0 原文

回忆那么伤 2025-02-18 19:11:55

您可以利用Vookup。这是为每个项目创建一个键|各个月的第一个（费用| 44562）。查找表以相同的速度开始，然后单独使用第一个，然后是B2：c的普通数据。这是由第一列以降序排序的。

由于Vlookup返回了第一场比赛，因此这将返回最新值。

=ARRAYFORMULA(
  IFERROR(
   VLOOKUP(
    UNIQUE(B2:B&"|"&EOMONTH(A2:A,-1)+1),
    SORT({B2:B&"|"&EOMONTH(A2:A,-1)+1,EOMONTH(A2:A,-1)+1,B2:C},1,FALSE),
    {2,3,4},FALSE)))

You could leverage a VLOOKUP. This is creating a key for each item that is the category|the first of the respective month (EXPENSES|44562). The lookup table starts with the same, then has the firsts on their own followed by the normal data from B2:C. This is sorted by the first column in descending order.

Since VLOOKUP returns the first match, this will return the latest value.

=ARRAYFORMULA(
  IFERROR(
   VLOOKUP(
    UNIQUE(B2:B&"|"&EOMONTH(A2:A,-1)+1),
    SORT({B2:B&"|"&EOMONTH(A2:A,-1)+1,EOMONTH(A2:A,-1)+1,B2:C},1,FALSE),
    {2,3,4},FALSE)))

回复收藏 0 原文

~没有更多了~