У меня есть форма Google, принимающая данные от разных пользователей, которая идет на лист и используется для СУММИРОВАНИЯ значений по столбцам в конце дня. Проблема в том, что пользователи могут повторно отправлять формы для обновления итоговых данных, если в конце дня есть изменения:
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
В моем текущем запросе я суммирую() столбцы после фильтрации по дате следующим образом:
SELECT SUM(K), SUM(M), SUM(N) WHERE C = date '"&TEXT($B$1,"yyyy-mm-dd")&
$B$1 — это ячейка с указателем даты, а столбец C — дата отправки формы пользователем. Col A имеет уникальную форму, сгенерированную временными метками отправки
Как видите, SUM для каждого столбца будет отключена из-за дополнительной отправки от Салли. Мне нужно включить только самые последние материалы от любого из пользователей и игнорировать все предыдущие на эту дату. Я не уверен, как фильтровать таким образом и суммировать только самый последний экземпляр от каждого уникального пользователя.
** РЕДАКТИРОВАТЬ ** Я должен отметить, что исходные данные формы находятся на другом листе, а ссылки на ячейки выполняются через запрос к этому диапазону. Форма также отправляется ежедневно, поэтому в запросе должна быть возможность указать рассматриваемую дату для суммирования записей.
Конечно - вот лист с примерами данных и моим запросом: docs.google.com/spreadsheets/d/…
Смотрите мой ответ и пишите свой ответ нам.
Попробуйте следующую формулу:
=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)")
Спасибо. В моем реальном случае использования datatable живет на другом рабочем листе с именем «ввод данных». Можно ли использовать это решение, если на данные ссылаются на другом листе?
Да, просто используйте имя листа перед диапазонами, например Sheet2!A2:M7
.
Спасибо, похоже, второй запрос работает, за исключением того, что мне также нужно указать интересующую дату. Поскольку электронная таблица собирает входные данные каждый день, мне нужно указать дату отправки, а затем получить только самую последнюю отправку в пределах этой даты для каждого пользователя. Можно ли добавить этот фильтр в запрос?
@ user1837608 Да, возможно. На самом деле вам не нужно указывать дату, потому что вторая формула всегда будет автоматически извлекать самый последний ответ каждого человека. Если вы хотите добавить критерии даты, попробуйте- =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,C2:C7=date(2023,2,17)),1,0),"limit 1")}))),"select sum(Col11), sum(Col12), sum(Col13)")
.
Спасибо, это сработало как нужно. Я очень ценю вашу помощь здесь.
Здесь у вас есть еще один вариант, создание вспомогательного столбца, который возвращает 1, если он соответствует дате и является последней отметкой времени.
=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")
Используйте пункты
Group by
иOrder By
. Поделитесь образцом так же, как в вашем листе.