Google Sheets分组查询后按首次出现日期排序的实现方法

数据表内容 #

M   1000    income      12/02/2023
b   2000    income      12/03/2023
a   -400    spending    22/03/2023
d   2000    income      14/01/2023
b   -300    spending    12/01/2023
a   1000    income      12/08/2023

当前使用的QUERY公式 #

=QUERY(A1:B6;"select A, sum(B), count(B) group by A";)

需求 #

希望按A列分组的首次出现日期对查询结果排序，期望顺序为：b → d → M → a

实现方法 #

可以通过SORT+XMATCH+SORTN组合公式实现，直接替换原QUERY公式即可：

=SORT(QUERY(A1:D6;"select A, sum(B), count(B) group by A");
      XMATCH(QUERY(A1:D6;"select A group by A");
             INDEX(SORTN(A1:D6;9^9;2;1;1);;1));
      1)

公式说明

1. SORTN(A1:D6;9^9;2;1;1)：对全表数据去重，保留每个A列分组的第一条记录（即首次出现的那条）
2. INDEX(...,;1)：提取去重后记录的A列值，得到按首次出现顺序排列的分组列表
3. XMATCH(...)：匹配QUERY结果中每个分组在上述列表中的位置，以此作为排序依据
4. SORT(...)：按这个位置升序排序，最终得到符合需求的结果

也可以用另一种等价写法：

=SORT(QUERY(A1:D6;"select A, sum(B), count(B) group by A");
      ARRAYFORMULA(XMATCH(QUERY(A1:D6;"select A group by A");
                          UNIQUE(FILTER(A1:A6;ROW(A1:A6)=MATCH(A1:A6;A1:A6;0)))));
      TRUE)

内容的提问来源于stack exchange，提问作者Aurore