如何在Google Sheets中合并查询实现出入库数据分类汇总？

针对示例数据的简洁实现 #

如果用ARRAYFORMULA结合SUMIFS，可以直接生成无冗余标签的汇总表，无需多次查询或转置后调整：

=ARRAYFORMULA({
  "model", "IN", "OUT";
  "model_1", SUMIFS(B:B, A:A, "in"), SUMIFS(B:B, A:A, "out");
  "model_2", SUMIFS(C:C, A:A, "in"), SUMIFS(C:C, A:A, "out")
})

执行后直接得到你需要的格式：

model          IN    OUT
-------------------------
model_1        22     7
model_2        15    12

如果坚持用QUERY函数，可通过PIVOT实现单查询生成结果，再转置调整：

=TRANSPOSE(QUERY(A1:C6, "SELECT SUM(B), SUM(C) GROUP BY A PIVOT A LABEL SUM(B)'model_1', SUM(C)'model_2'", 1))

转置后手动将表头的in/out改为IN/OUT即可，也可通过数组公式自动替换表头：

=ARRAYFORMULA({
  {"model", "IN", "OUT"};
  TRANSPOSE(QUERY(A1:C6, "SELECT SUM(B), SUM(C) GROUP BY A PIVOT A LABEL SUM(B)'model_1', SUM(C)'model_2'", 1))
})

针对你的实际表格（'Réponses au formulaire 2'!A1:W） #

方案1：ARRAYFORMULA + SUMIFS（推荐，无冗余标签） #

假设in/out标识在B列，D列用于筛选DEPOT，E到W对应19个型号，公式如下：

=ARRAYFORMULA({
  "model", "IN", "OUT";
  "model_1", SUMIFS(E:E, D:D, "DEPOT", B:B, "in"), SUMIFS(E:E, D:D, "DEPOT", B:B, "out");
  "model_2", SUMIFS(F:F, D:D, "DEPOT", B:B, "in"), SUMIFS(F:F, D:D, "DEPOT", B:B, "out");
  "model_3", SUMIFS(G:G, D:D, "DEPOT", B:B, "in"), SUMIFS(G:G, D:D, "DEPOT", B:B, "out");
  "model_4", SUMIFS(H:H, D:D, "DEPOT", B:B, "in"), SUMIFS(H:H, D:D, "DEPOT", B:B, "out");
  "model_5", SUMIFS(I:I, D:D, "DEPOT", B:B, "in"), SUMIFS(I:I, D:D, "DEPOT", B:B, "out");
  "model_6", SUMIFS(J:J, D:D, "DEPOT", B:B, "in"), SUMIFS(J:J, D:D, "DEPOT", B:B, "out");
  "model_7", SUMIFS(K:K, D:D, "DEPOT", B:B, "in"), SUMIFS(K:K, D:D, "DEPOT", B:B, "out");
  "model_8", SUMIFS(L:L, D:D, "DEPOT", B:B, "in"), SUMIFS(L:L, D:D, "DEPOT", B:B, "out");
  "model_9", SUMIFS(M:M, D:D, "DEPOT", B:B, "in"), SUMIFS(M:M, D:D, "DEPOT", B:B, "out");
  "model_10", SUMIFS(N:N, D:D, "DEPOT", B:B, "in"), SUMIFS(N:N, D:D, "DEPOT", B:B, "out");
  "model_11", SUMIFS(O:O, D:D, "DEPOT", B:B, "in"), SUMIFS(O:O, D:D, "DEPOT", B:B, "out");
  "model_12", SUMIFS(P:P, D:D, "DEPOT", B:B, "in"), SUMIFS(P:P, D:D, "DEPOT", B:B, "out");
  "model_13", SUMIFS(Q:Q, D:D, "DEPOT", B:B, "in"), SUMIFS(Q:Q, D:D, "DEPOT", B:B, "out");
  "model_14", SUMIFS(R:R, D:D, "DEPOT", B:B, "in"), SUMIFS(R:R, D:D, "DEPOT", B:B, "out");
  "model_15", SUMIFS(S:S, D:D, "DEPOT", B:B, "in"), SUMIFS(S:S, D:D, "DEPOT", B:B, "out");
  "model_16", SUMIFS(T:T, D:D, "DEPOT", B:B, "in"), SUMIFS(T:T, D:D, "DEPOT", B:B, "out");
  "model_17", SUMIFS(U:U, D:D, "DEPOT", B:B, "in"), SUMIFS(U:U, D:D, "DEPOT", B:B, "out");
  "model_18", SUMIFS(V:V, D:D, "DEPOT", B:B, "in"), SUMIFS(V:V, D:D, "DEPOT", B:B, "out");
  "model_19", SUMIFS(W:W, D:D, "DEPOT", B:B, "in"), SUMIFS(W:W, D:D, "DEPOT", B:B, "out")
})

方案2：优化后的QUERY函数 #

通过PIVOT合并IN/OUT统计，避免冗余标签，同时完成筛选：

=TRANSPOSE(QUERY('Réponses au formulaire 2'!A1:W, "SELECT SUM(E), SUM(F), SUM(G), SUM(H), SUM(I), SUM(J), SUM(K), SUM(L), SUM(M), SUM(N), SUM(O), SUM(P), SUM(Q), SUM(R), SUM(S), SUM(T), SUM(U), SUM(V), SUM(W) WHERE D='DEPOT' GROUP BY B PIVOT B LABEL SUM(E)'model_1', SUM(F)'model_2', SUM(G)'model_3', SUM(H)'model_4', SUM(I)'model_5', SUM(J)'model_6', SUM(K)'model_7', SUM(L)'model_8', SUM(M)'model_9', SUM(N)'model_10', SUM(O)'model_11', SUM(P)'model_12', SUM(Q)'model_13', SUM(R)'model_14', SUM(S)'model_15', SUM(T)'model_16', SUM(U)'model_17', SUM(V)'model_18', SUM(W)'model_19'", 1))

转置后若需要将in/out替换为IN/OUT，可嵌套数组公式完成：

=ARRAYFORMULA({
  {"model", "IN", "OUT"};
  TRANSPOSE(QUERY('Réponses au formulaire 2'!A1:W, "SELECT SUM(E), SUM(F), SUM(G), SUM(H), SUM(I), SUM(J), SUM(K), SUM(L), SUM(M), SUM(N), SUM(O), SUM(P), SUM(Q), SUM(R), SUM(S), SUM(T), SUM(U), SUM(V), SUM(W) WHERE D='DEPOT' GROUP BY B PIVOT B LABEL SUM(E)'model_1', SUM(F)'model_2', SUM(G)'model_3', SUM(H)'model_4', SUM(I)'model_5', SUM(J)'model_6', SUM(K)'model_7', SUM(L)'model_8', SUM(M)'model_9', SUM(N)'model_10', SUM(O)'model_11', SUM(P)'model_12', SUM(Q)'model_13', SUM(R)'model_14', SUM(S)'model_15', SUM(T)'model_16', SUM(U)'model_17', SUM(V)'model_18', SUM(W)'model_19'", 1))
})

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