Внешнее соединение Google Sheets на 2 таблицах, которые суммируются

У меня есть отчет, который я разрабатываю, который должен быть в таблицах Google. Лист 1 содержит все текущие дела, которые открыты и принадлежат владельцу дела, 1 строка на дело, несколько дел на каждого владельца дела в месяц (в листе образцов есть 4 месяца данные) Лист 2 содержит все дела, которые были закрыты и кто их закрыл, по 1 строке на дело, несколько дел на каждого владельца дела в месяц (в листе образцов содержатся данные за 4 месяца)

В открытом листе могут быть владельцы дел, которых нет на закрытом листе. В закрытом листе могут быть владельцы дел, которых нет на открытом листе.

Я знаю, как создать функцию query (), которая суммирует оба необработанных данных по отдельности - я хочу иметь возможность запрашивать объединенную таблицу, чтобы получить открытые и закрытые случаи в одной таблице. В файле примера есть 2 таблицы в форматах A5 и F5. Я смотрел, как работает функция query () - фактический отчет, который мне нужен, находится в K5.

Я борюсь с тем, чтобы собрать все это в один сводный отчет, который показывает данные за последние 3 месяца.

Чтобы уточнить - я хочу иметь одну функцию, которая запрашивает из 2-х RAW-листов и создает сводный отчет.

В листе с образцами следует более четко объяснить, что мне нужно делать.

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

Вывод для ОТКРЫТЫХ случаев:

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

Вывод для ЗАКРЫТЫХ дел

=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")

Есть указатели?


google-sheets transpose google-sheets-formula array-formulas google-sheets-query
person Gazing South    schedule 06.05.2020    source источник

Ответы (1)


arrow_upward
0
arrow_downward

пытаться:

=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"))

0

=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"))

0

person player0    schedule 06.05.2020
comment
Спасибо за быстрый ответ - к сожалению, я понимаю, что мой первоначальный вопрос был непонятен - мне нужно сообщить прямо из необработанных таблиц данных. Две таблицы, которые у меня были, были просто для демонстрации того, что я понял, как запрашивать таблицы (фактическая формула запроса намного сложнее, чем образец) - person Gazing South; 07.05.2020
comment
Большое спасибо - это дает мне желаемый результат - я сейчас разбираю формулу - вы можете помочь мне понять ее логику - кажется, что он выполняет некоторые строковые подстановки для заголовков, но я strill изо всех сил пытается понять логику - person Gazing South; 07.05.2020
comment
если вы ссылаетесь на функцию SUBSTITUTE, то да, QUERY сортируется в алфавитном порядке, что означает, что C стоит перед O, но вы хотите, чтобы он был отсортирован Open, а затем Close, поэтому мы добавляем A перед O, затем сортируем его и затем заменяем AO ... на O. .. - person player0; 07.05.2020