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({{IF(ISNUMBER(QUERY('Raw Data'！A:N，选择B，count(A)，其中N> =日期'2020-02-01'组，B枢轴N限制0"))* 1)，QUERY('Raw Data'！A:N，选择B，count(A)，其中N> =日期'2020-02-01'通过B轴N限制0组进行分组")&"AOpen"，QUERY('Raw Data'！A:N，选择B，count(A)，其中N> =日期'2020-02-01'组，B枢轴N限制0")))；询问(QUERY('Raw Data'！A:N，选择B，count(A)，其中N> =日期'2020-02-01'通过B轴心N组")，偏移量1"，0)}，IFNA(VLOOKUP(INDEX(QUERY('Raw Data'！A:N，选择B，count(A)，其中N> =日期'2020-02-01'通过B轴心N组")，1)，{IF(ISNUMBER(QUERY(原始数据-已关闭"！A:N，选择A，count(B)，其中H> =日期'2020-02-01'和H&=日期'2020-04-01'由A分组枢轴H极限0)* 1)，QUERY(原始数据-已关闭"！A:N，选择A，count(B)，其中H> =日期'2020-02-01'和H&=日期'2020-04-01'由A分组枢轴H限制0)&"已关闭，QUERY(原始数据-已关闭"！A:N，选择A，count(B)，其中H> =日期'2020-02-01'和H&=日期'2020-04-01'由A分组枢轴H极限0)))；询问(QUERY(原始数据-已关闭"！A:N，选择A，count(B)，其中H> =日期'2020-02-01'和H <= date'2020-04-01'由A分组枢轴H)，"偏移1，0)}，SEQUENCE(1，COLUMNS(QUERY(原始数据-已关闭"！A:N，选择A，count(B)，其中H> =日期'2020-02-01'和H&=日期'2020-04-01'由A分组枢轴H))-1，2，1，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屋！