英文:
how can I query sum across columns, but avoid duplicated submissions / rows from unique users?
问题
我有一个接受不同用户数据的Google表单，这些数据被发送到一个表格，然后在一天结束时用于对列值进行求和。问题是，如果在一天结束时有更改，用户可以重新提交表单以更新数据总计：
在我当前的查询中，我按日期筛选后对列进行SUM()操作，如下所示：
SELECT SUM(K), SUM(M), SUM(N) WHERE C = date '"&TEXT($B$1,"yyyy-mm-dd")&
$B$1是一个带有日期选择器的单元格，列C是用户提交表单的日期。列A包含唯一的表单生成的提交时间戳。
正如你所看到的，每个列的SUM将受到Sally的额外提交的影响。我需要只包括来自任何用户的最新提交，并忽略这一日期之前的任何提交。我不确定如何以这种方式进行筛选，并只对每个唯一用户的最新实例求和。
编辑
我应该注意原始表单数据位于另一个表中，单元格是通过查询引用的。该表单每天都会提交，因此查询必须能够指定要对条目进行求和的日期。
英文:
I have a Google form accepting data from different users, which goes to a sheet and is used to SUM the values across columns at the end of the day. The problem is that users can re-submit forms to update the data totals if there is a change at the end of the day:
NAME K L M
ALF 4 0 1
BILL 1 0 0
SALLY 1 0 1
DENNIS 1 1 1
RICK 0 0 1
SALLY 2 1 1 <--- SALLY RESUBMITTED HER FORM AGAIN WITH UPDATED VALUES
In my current Query, I SUM() the columns after filtering by the date like this:
SELECT SUM(K), SUM(M), SUM(N) WHERE C = date '"&TEXT($B$1,"yyyy-mm-dd")&
$B$1 is a cell with a datepicker and col C is the user submitted form date. Col A has the unique form generated submission timestamps
As you can see, the SUM for each column will be off by the extra submission from Sally. I need to include only the most recent submissions from any of the users, and ignore any prior ones for this date. I'm not sure how to filter in this manner and sum just the most recent instance from each unique user.
** EDIT **
I should note the original form data is on another sheet and the cells are referenced via a query to this range. The form is also submitted daily, so the query must be able to specify the date in question for summation of entries.
答案1
得分: 1
尝试以下公式：
=QUERY(INDEX(REDUCE({0,0,0,0},UNIQUE(J2:J7),LAMBDA(a,b,{a;SORTN(FILTER(J2:M7,J2:J7=b,C2:C7=date(2023,2,17)),1)}))), " select sum(Col2), sum(Col3), sum(Col4)")
如果您实际上想要对最近的响应进行求和，则使用：
=QUERY(INDEX(REDUCE(SEQUENCE(1,COLUMNS(A2:M7),0,0),UNIQUE(J2:J7),LAMBDA(a,b,{a;QUERY(SORT(FILTER(A2:M7,J2:J7=b),1,0),"limit 1")})), "select sum(Col11), sum(Col12), sum(Col13)")
英文:
Give a try on following formula-
=QUERY(INDEX(REDUCE({0,0,0,0},UNIQUE(J2:J7),LAMBDA(a,b,{a;SORTN(FILTER(J2:M7,J2:J7=b,C2:C7=date(2023,2,17)),1)})))," select sum(Col2), sum(Col3), sum(Col4)")
If you actually want most recent response to sum then use-
=QUERY(INDEX(REDUCE(SEQUENCE(1,COLUMNS(A2:M7),0,0),UNIQUE(J2:J7),LAMBDA(a,b,{a;QUERY(SORT(FILTER(A2:M7,J2:J7=b),1,0),"limit 1")}))),"select sum(Col11), sum(Col12), sum(Col13)")
答案2
得分: 0
=QUERY({K:M,
MAP(A:A,C:C,J:J,LAMBDA(ts,date,n,IF(date<>B1,0,IF(ts=MAX(FILTER(A:A,J:J=n,C:C=date)),1,0))))},
"SELECT SUM(Col1),SUM(Col2),SUM(Col3) where Col4=1")
英文:
Here you have another option, creating an auxiliary column that returns 1 if it corresponds to the date and is the last timestamp
=QUERY({K:M,
MAP(A:A,C:C,J:J,LAMBDA(ts,date,n,IF(date<>B1,0,IF(ts=MAX(FILTER(A:A,J:J=n,C:C=date)),1,0))))},
"SELECT SUM(Col1),SUM(Col2),SUM(Col3) where Col4=1")
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论