Как написать запрос, который выводит наиболее распространенные комбинации с запросом Google

У меня есть ситуация, когда у меня есть список идентификаторов транзакций, содержащий несколько комбинаций продуктов. Я пытаюсь написать запрос, который покажет мне наиболее распространенные комбинации продуктов. В этом примере данных у меня есть идентификатор транзакции в A, который связывает продукты вместе, и название продукта в B. Я хочу получить результат, который дает мне информацию в D и E - он не должен выглядеть как это, но необходимо предоставить ту же информацию.

Я хотел бы поделиться своими текущими работами, но я абсолютно не знаю, как даже начать заниматься этим, поэтому любые идеи будут тепло приняты.

https://docs.google.com/spreadsheets/d/1flJ4yM-E8v7A2nRMjjR1J27oqVa-eE5-8wH7TFd6UQg/edit#gid=1452792924

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


google-sheets google-sheets-query
person Takeshi Patterson    schedule 08.05.2017    source источник

Ответы (2)


arrow_upward
2
arrow_downward

Попробуй это. В C4 поместите:

=unique(A4:A25)

В D4 поместите и скопируйте:

=join(", ",query(FILTER(A4:B25,A4:A25=C4),"Select Col2"))

В E4 поместите и скопируйте:

=JOIN(", ",SORT(TRANSPOSE(SPLIT(D4,", ",FALSE))))

В F4 поставьте:

=QUERY({E:E,E:E},"select Col1, count(Col2) where Col1 != '' group by Col1 order by count(Col2) desc  label count(Col2) ''",1)

Вы можете скрыть Col C, D и E.

person Ed Nelson    schedule 08.05.2017
comment
Потрясающие! Единственная проблема заключается в том, что это решение полагается на товары, покупаемые в одном порядке, например. брюки, рубашка, обувь - это то, что показывают мои тестовые данные, - но я обнаружил, что если в транзакции есть те же продукты, но в другом порядке, например обувь, рубашка, брюки решение видит это как другую комбинацию, поскольку я предполагаю, что он ищет ту же самую строку - person Takeshi Patterson; 09.05.2017
comment
Попробуйте новый ответ. Сортировка слов в объединенных ячейках. - person Ed Nelson; 09.05.2017

arrow_upward
0
arrow_downward

Пожалуйста, попробуйте эту формулу:

=QUERY( {UNIQUE(FILTER(A4:A,A4:A>0)),TRANSPOSE(SPLIT(JOIN("",FILTER(B4:B&", "&if(A4:A<>OFFSET(A4:A,1,0),"|",""),A4:A<>0)),", |",0))}, "select Col2, count(Col1) group by Col2 order by count(Col1) desc label count(Col1) ''")

Формула предполагает, что список идентификаторов отсортирован. Если нет, то я предлагаю сначала отсортировать данные.

person Max Makhrov    schedule 10.05.2017