Google Таблицы: выберите N лучших обращений в группах с помощью QUERY

Я хочу сделать отчет: «выберите продавцов, у которых N последних кодов статуса = 2,3,4,5».

Структурный пример моих данных содержит 35 строк (включая 1 строку заголовка): ссылка. В этом файле есть поля Дата, Код продажи (идентификатор продавца), Код состояния (идентификатор успешности транзакции) и другие поля. которые не нужны для этой цели.

В итоге я использовал три формулы:

  1. функция QUERY с IMPORTRANGE.
    В примере данных это немного проще - взять только коды продаж, код состояния и дату с другого листа, а затем упорядочить по дате и коду продаж. Формула в A9:
    =QUERY({Source!D1:E, Source!A1:A}, $B$4, 1)

  2. дополнительный столбец с порядковой нумерацией. Формула в D10:
    =ArrayFormula(if(len(A10:A), ROW(A10:A) - MATCH(A10:A,A10:A,0) - 8, ))

  3. функция QUERY для извлечения только N случаев (скажем, 5). Формула в F9:
    =QUERY(A9:D, "select A, B where D <="&B3, 1)

Есть ли способ объединить все 3 шага в один, чтобы получить результат, как в F10: G24, используя одну (и, надеюсь, быструю :)) формулу? Формула, которую я пробовал (расширена для удобства чтения):

=QUERY(
    {
      QUERY({Source!D1:E, Source!A1:A}, $B$4, 1),
      ArrayFormula(
         IF(len(J10:J),
            ROW(J10:J) - MATCH(J10:J, J10:J, 0) - 8,
         )
      )
    },
    "select Col1, Col2 where Col4 <="&B3,
    1
)

Это дает мне ошибку:

"Параметр 2 функции ARRAY_ROW имеет несоответствующий размер строки. Ожидаемый: 28. Фактический: 991."

Я также пробовал диапазоны конечных данных в ROW() и MATCH(), но это дает пустую таблицу. В исходной базе данных ~ 3500 строк, и они будут расширяться, поэтому я думаю, что мне следует придерживаться бесконечных диапазонов, чтобы автоматизировать извлечение данных.


google-sheets google-sheets-query
person Andrey Ampilogov    schedule 29.07.2018    source источник
comment
Можно ли предположить, что исходные данные отсортированы по коду продаж, т.е. все продажи конкретного продавца собраны вместе?   -  person Tom Sharpe    schedule 29.07.2018
comment
@TomSharpe, в исходных данных все продажи добавляются ежедневно, а последняя информация вводится в верхней части базы данных. Сортировка не применяется. Так что в любой день может быть любой продавец с любым статусом продаж.   -  person Andrey Ampilogov    schedule 29.07.2018
comment
Думаю, это выполнимо, но сортировку придется повторить - чуть позже опубликую.   -  person Tom Sharpe    schedule 29.07.2018

Ответы (1)


arrow_upward
1
arrow_downward

Хм, это похоже на кошмар. ТБХ - подобный вопрос возникал и раньше, но на него нелегко. Вот черновик, который предполагает повторение основного запроса несколько раз:

=ArrayFormula(query({query(A2:E,"select * where E>=2 and E<=5  order by D, A desc"),
row(indirect("2:"&count(filter(E2:E,E2:E>=2,E2:E<=5))+1))-
match(query(A2:E,"select D where E>=2 and E<=5  order by D, A desc"),
query(A2:E,"select D where E>=2 and E<=5  order by D, A desc"),0)
},"select * where Col6<=5"))

Но необходимо изучить больше, чтобы понять, можно ли его упростить.

Это версия с полными столбцами, включая заголовки - я думаю, что это нормально

=ArrayFormula(query({query(A:E,"select * where E>=2 and E<=5  order by D, A desc"),
row(indirect("1:"&count(filter(E:E,E:E>=2,E:E<=5))+1))+1-
match(query(A:E,"select D where E>=2 and E<=5  order by D, A desc"),
query(A:E,"select D where E>=2 and E<=5  order by D, A desc"),0)
},"select * where Col6<=5"))

введите здесь описание изображения

person Tom Sharpe    schedule 29.07.2018
comment
Ух ты! Спасибо! Работает! Реализует эту логику в большом наборе данных, используя IMPORTRANGE. - person Andrey Ampilogov; 30.07.2018