Google Sheets 外连接 2 个汇总表 [英] Google Sheets outer join on 2 tables that get summarised

问题描述

我有一份我正在开发的报告需要放在 Google 表格中工作表 1 包含所有当前由案例所有者打开和拥有的案例，每个案例 1 行，每个案例所有者每月多个案例(样本表有 4 个月的数据)表 2 包含所有关闭的案例以及关闭它们的人，每个案例 1 行，每个案例所有者每月多个案例(样本表有 4 个月的数据)

开放工作表中可能存在封闭工作表中不存在的案例所有者封闭表中可能存在开放表中不存在的案例所有者

我知道如何创建一个 query() 函数来分别汇总两个原始数据 - 我希望能够做的是查询一个连接表以在 1 个表中获取打开和关闭的案例.在示例文件中，A5 和 F5 中有 2 个表.这些是我在查看 query() 函数是如何工作的 - 我想要的实际报告在 K5 中

我正在努力的地方是将这些汇总到一份汇总报告中，其中显示了过去 3 个月的数据

澄清 - 我想要做的是有一个功能可以从 2 张 RAW 表中查询并创建汇总报告

样本表应该更清楚地说明我需要做什么

<小时>

=ARRAYFORMULA(SUBSTITUTE(TRANSPOSE(QUERY(TRANSPOSE({{如果(ISNUMBER(QUERY('Raw Data'!A:N, "select B,count(A) where N >= date '2020-02-01' group by B pivot N limit 0")*1),QUERY('Raw Data'!A:N, "select B,count(A) where N >= date '2020-02-01' group by B pivot N limit 0")&" AOpen",QUERY('Raw Data'!A:N, "select B,count(A) where N >= date '2020-02-01' group by B pivot N limit 0"));询问(QUERY('Raw Data'!A:N, "select B,count(A) where N >= date '2020-02-01' group by B pivot N"), "offset 1", 0)},IFNA(VLOOKUP(索引(QUERY('Raw Data'!A:N, "select B,count(A) where N >= date '2020-02-01' group by B pivot N"),,1), {IF(ISNUMBER(QUERY('Raw Data - Closed'!A:N, "select A,count(B) where H >= date '2020-02-01' and H <= date '2020-04-01' group by A枢轴 H 限制 0")*1),QUERY('Raw Data - Closed'!A:N, "select A,count(B) where H >= date '2020-02-01' and H <= date '2020-04-01' group by A枢轴 H 限制 0")&" 关闭",QUERY('Raw Data - Closed'!A:N, "select A,count(B) where H >= date '2020-02-01' and H <= date '2020-04-01' group by A枢轴 H 限制 0"));询问(QUERY('Raw Data - Closed'!A:N, "select A,count(B) where H >= date '2020-02-01' and H <= date '2020-04-01' group by A枢轴 H"), "偏移量 1", 0)},序列(1，列(QUERY('Raw Data - Closed'!A:N, "select A,count(B) where H >= date '2020-02-01' and H <= date '2020-04-01' group by A枢轴 H"))-1, 2, 1), 0))}), "按 Col1 排序", 1)), " AOpen", " Open"))

I have a report I am developing that needs to be in google sheets Sheet 1 contains all the current cases that are opened and owned by a case owner, 1 row per case, multiple cases per case owner per month (sample sheet has 4 months of data) Sheet 2 contains all the cases that were closed and who closed them, 1 row per case, multiple cases per case owner per month (sample sheet has 4 months of data)

There could be case owners in the open sheet that don't exist in the closed sheet There could be case owners in the closed sheet that don't exist in the open sheet

I know how create a query() function that summarises both raw data separately - what I want to be able to do is query a joined table to get open and closed cases in 1 table. In the sample file there are 2 tables in A5 and F5. These were me looking at how the query() function worked - the actual report I want is in K5

Where I am struggling is bringing this together into one summary report which shows the last 3 months of data

To clarify - what I want to do is have one function that queries from the 2 RAW sheets and creates the summary report

The sample sheet should more clearly explain what I need to do

https://docs.google.com/spreadsheets/d/1QEd9ZauY0YrbRWNu35tDTk6SkL1PaIZLtSOCd1A2fDY/edit?usp=sharing

Output for the OPEN cases:

=query('Raw Data'!A:N,"Select B, count(A) where N >= date '2020-02-01' group by B pivot N")

Output for the CLOSED cases

=query('Raw Data - Closed'!A:N,"Select A, count(B) where H >= date '2020-02-01' AND H <= date '2020-04-01' group by A pivot H")

Any pointers?

解决方案

try:

=ARRAYFORMULA(SUBSTITUTE(TRANSPOSE(QUERY(TRANSPOSE({{
 IF(ISNUMBER(A5:D5*1), A5:D5&" A"&A3, A5:D5); A6:D}, 
 IFNA(VLOOKUP(A5:A, {IF(ISNUMBER(F5:I5*1), F5:I5&" "&F3, F5:I5); F6:I}, 
 COLUMN(G5:I5)-(COLUMN(F5)-1), 0))}), "order by Col1", 1)), " AOpen", " Open"))

---

=ARRAYFORMULA(SUBSTITUTE(TRANSPOSE(QUERY(TRANSPOSE({{
 IF(ISNUMBER(
 QUERY('Raw Data'!A:N, "select B,count(A) where N >= date '2020-02-01' group by B pivot N limit 0")*1), 
 QUERY('Raw Data'!A:N, "select B,count(A) where N >= date '2020-02-01' group by B pivot N limit 0")&" AOpen", 
 QUERY('Raw Data'!A:N, "select B,count(A) where N >= date '2020-02-01' group by B pivot N limit 0")); QUERY(
 QUERY('Raw Data'!A:N, "select B,count(A) where N >= date '2020-02-01' group by B pivot N"), "offset 1", 0)}, 
 IFNA(VLOOKUP(INDEX(
 QUERY('Raw Data'!A:N, "select B,count(A) where N >= date '2020-02-01' group by B pivot N"),,1), {IF(ISNUMBER(
 QUERY('Raw Data - Closed'!A:N, "select A,count(B) where H >= date '2020-02-01' and H <= date '2020-04-01' group by A pivot H limit 0")*1), 
 QUERY('Raw Data - Closed'!A:N, "select A,count(B) where H >= date '2020-02-01' and H <= date '2020-04-01' group by A pivot H limit 0")&" Closed", 
 QUERY('Raw Data - Closed'!A:N, "select A,count(B) where H >= date '2020-02-01' and H <= date '2020-04-01' group by A pivot H limit 0")); QUERY(
 QUERY('Raw Data - Closed'!A:N, "select A,count(B) where H >= date '2020-02-01' and H <= date '2020-04-01' group by A pivot H"), "offset 1", 0)}, 
 SEQUENCE(1, COLUMNS(
 QUERY('Raw Data - Closed'!A:N, "select A,count(B) where H >= date '2020-02-01' and H <= date '2020-04-01' group by A pivot H"))-1, 2, 1), 0))}), "order by Col1", 1)), " AOpen", " Open"))

这篇关于Google Sheets 外连接 2 个汇总表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！