Страницы: 1
RSS
Найти пересечение по кол-ву клиентов сделавших покупки в разных магазинах
 
Здравствуйте! Помогите, пожалуйста!
Нужно найти пересечение количества клиентов сделавших покупки в разных магазинах, формулами.
Я так понимаю, что в конце выйдет квадратная диагональная матрица, но формулы никак не подберу.
 
Доброе время суток.
Леся, не подскажите, почему в примере соединения "Пример" для Сеть2/Сеть2 стоит 1, для Сеть4/Сеть4 - тоже 1, а для Сеть1/Сеть1 и Сеть3/Сеть3 по 0?
И ещё один момент, вы подсчитываете количество клиентов в сетях или сумму количеств покупок?
Будем считать, что сумма. Дальше уже можно будет и допилить по потребностям.
Изменено: Андрей VG - 26.06.2020 19:32:13
 
На сколько я понимаю Сеть2/Сеть2 и Сеть4/Сеть4 стоит 1 потому что есть один покупатель который купил только в Сети №2/Сети №4, а Сеть1/Сеть1 и Сеть3/Сеть3 стоит 0, потому что все покупатели этой сети покупали и в других сетях.


Да, верно подсчитываем сумму количеств покупок. Спасибо з сводную, только у меня есть условие сделать таблицу формулами.
 
Леся Лалак, спасибо за ответ, так даже интереснее.
Цитата
Леся Лалак написал:
у меня есть условие сделать таблицу формулами.
Ну, это если медведь с пасеки сюда заглянет. Не хочу обидеть других формулистов, но боюсь им не позубам. Разве что допускается использование вспомогательных столбцов.
 
Буду очень благодарна, если поделитесь примером, как это сделать с вспомогательным столбиком.
Изменено: Леся Лалак - 27.06.2020 07:39:12
 
Леся Лалак, добрый вечерок)
если правильно понял
Код
=СУММЕСЛИ(ИНДЕКС(Данные!$B$2:$Q$231;1;ПОИСКПОЗ('Финальная таблица'!B$2;Данные!$B$1:$Q$1;0)):ИНДЕКС(Данные!$B$2:$Q$231;СЧЁТЗ(Данные!$A:$A)-1;ПОИСКПОЗ('Финальная таблица'!B$2;Данные!$B$1:$Q$1;0));"<>"&"";ИНДЕКС(Данные!$B$2:$Q$231;1;ПОИСКПОЗ('Финальная таблица'!$A3;Данные!$B$1:$Q$1;0)):ИНДЕКС(Данные!$B$2:$Q$231;СЧЁТЗ(Данные!$A:$A)-1;ПОИСКПОЗ('Финальная таблица'!$A3;Данные!$B$1:$Q$1;0)))
Изменено: Mershik - 26.06.2020 22:00:36
Не бойтесь совершенства. Вам его не достичь.
 
Цитата
Mershik написал:
если правильно понял
По сводной сбиваемся, но есть один неприятный момент
Цитата
Леся Лалак написал:
Сеть2/Сеть2 и Сеть4/Сеть4 стоит 1 потому что есть один покупатель который купил только в Сети №2/Сети №4, а Сеть1/Сеть1 и Сеть3/Сеть3 стоит 0, потому что все покупатели этой сети покупали и в других сетях.
То есть на пересечении Сеть1/Сеть1 учитываются только покупатели, которые кроме Сети1 нигде больше не покупали. И вот тут, боюсь формулами крутить и крутить.
 
Андрей VG, а если так ...то нужно колдовать или лучше с доп.столбцом) и еще наверное есть какие-то условия))
Изменено: Mershik - 26.06.2020 22:19:15
Не бойтесь совершенства. Вам его не достичь.
 
Mershik, нет доп условий больше нет.
Спасибо, но если я не ошибаюсь должна получиться квадратная диагональная матрица/таблица.
Количество клиентов которые сделали покупку в сети 2 і в сети1 ровно количеству клиентов которые сделали покупку в сети1 и в сети2, а выводим суму количеств покупок.
Может есть еще какие-то идеи?
 
Цитата
Mershik написал:
то нужно колдовать
По идее, как должно быть с колдовством :)
 
=MAX(
SUM(--(MMULT(SIGN(Данные!$B$2:$Q$231*((Данные!$B$1:$Q$1=B$2)+(Данные!$B$1:$Q$1=$A3)));TRANSPOSE(COLUMN(Данные!$B:$Q)^0))>1));
SUM(--(1=MMULT((Данные!$B$2:$Q$231>0)*($A3=B$2);TRANSPOSE(COLUMN(Данные!$B:$Q)^0))*MMULT(SIGN(Данные!$B$2:$Q$231*((Данные!$B$1:$Q$1=B$2)+(Данные!$B$1:$Q$1=$A3)));TRANSPOSE(COLUMN(Данные!$B:$Q)^0)))))


Не скажу что это идеальный вариант, вроде работает, но есть ощущение, что можно подсократить и оптимизировать
например так
=IF($A3=B$2;
SUM(--(1=MMULT(--(Данные!$B$2:$Q$231>0);TRANSPOSE(COLUMN(Данные!$B:$Q)^0))*MMULT(SIGN(Данные!$B$2:$Q$231*((Данные!$B$1:$Q$1=B$2)+(Данные!$B$1:$Q$1=$A3)));TRANSPOSE(COLUMN(Данные!$B:$Q)^0))));
SUM(--(MMULT(SIGN(Данные!$B$2:$Q$231*((Данные!$B$1:$Q$1=B$2)+(Данные!$B$1:$Q$1=$A3)));TRANSPOSE(COLUMN(Данные!$B:$Q)^0))>1)))
Изменено: БМВ - 27.06.2020 12:12:22
По вопросам из тем форума, личку не читаю.
 
Можно ещё добавить спомогательные столбцы на лист "Данные", в которых указан признак того, что в той или иной сети совершена покупка. На листе"Финальная таблица" указать столбцы для каждой сети из этих вспомогательных таблиц именно так, как я указал и чеерз формулу "СУММПРОИЗВ" подсчитаь количество пересечений. Единственное "НО!" - при изменении вспомогательных столбцов придётся каждый раз их названия корректировать на листе "финальная таблица"
 
Цитата
БМВ написал:
Не скажу что это идеальный вариант, вроде работает, но есть ощущение, что можно подсократить и оптимизировать
Есть ошибки. Например, сеть1-сеть1 - количество 37, а не 23
 
БМВ, есть ошибки. Например, сеть1-сеть1 - количество 37, а не 23

Цитата
Андрей VG написал: По идее, как должно быть с колдовством
Неправильно. Должно быть количество клиентов совершивших покупки, а у вас количество покупок
 
Не соглашусь.
Если данные отфильтровать,  а это я проверял, то только у 23 клиентов единственная сеть1 . Если это не так, то не так и в вашем примере и в пояснении.
По вопросам из тем форума, личку не читаю.
 
Я ошибся, снимаю шляпу. Мой вариант не прокатит
 
Цитата
lesya.l написал:
а выводим суму количеств покупок.
а вот это противоречит названию темы.
По вопросам из тем форума, личку не читаю.
 
Цитата
Иванов Вадим написал:
Неправильно. Должно быть количество клиентов совершивших покупки, а у вас количество покупок
Вадим, не думаю, что это такая напряжная сложность переключить в сводной расчёт с суммы на количество :) . Тем более, что об этом писал.
 
Цитата
БМВ написал:
а вот это противоречит названию темы.
БМВ, прошу прощения, ошибочно сформулировала.
Вы могли бы  подсказать, как в вашем варианте изменить чтобы выводило суму количеств покупок?
Буду очень благодарна !
 
=IF($A3=B$2;
SUM(INDEX(Данные!$B$2:$Q$231;;MATCH(B$2;Данные!$B$1:$Q$1;))*(1=MMULT(--(Данные!$B$2:$Q$231>0);TRANSPOSE(COLUMN(Данные!$B:$Q)^0))*MMULT(SIGN(Данные!$B$2:$Q$231*((Данные!$B$1:$Q$1=B$2)+(Данные!$B$1:$Q$1=$A3)));TRANSPOSE(COLUMN(Данные!$B:$Q)^0))));
SUM((INDEX(Данные!$B$2:$Q$231;;MATCH($A3;Данные!$B$1:$Q$1;))+INDEX(Данные!$B$2:$Q$231;;MATCH(B$2;Данные!$B$1:$Q$1;)))*(MMULT(SIGN(Данные!$B$2:$Q$231*((Данные!$B$1:$Q$1=B$2)+(Данные!$B$1:$Q$1=$A3)));TRANSPOSE(COLUMN(Данные!$B:$Q)^0))>1)))


но так как таблица зеркальна относительно диагонали, то чтоб не считать два раза
=IF(AND(ROW()>ROW($A$2)+COLUMN()-1;COLUMN()<ROW($A$2)+ROW()-3);INDEX($1:$1048576;COLUMN()+1;ROW()-1);
IF($A3=B$2;
SUM(INDEX(Данные!$B$2:$Q$231;;MATCH(B$2;Данные!$B$1:$Q$1;))*(1=MMULT(--(Данные!$B$2:$Q$231>0);TRANSPOSE(COLUMN(Данные!$B:$Q)^0))*MMULT(SIGN(Данные!$B$2:$Q$231*((Данные!$B$1:$Q$1=B$2)+(Данные!$B$1:$Q$1=$A3)));TRANSPOSE(COLUMN(Данные!$B:$Q)^0))));
SUM((INDEX(Данные!$B$2:$Q$231;;MATCH($A3;Данные!$B$1:$Q$1;))+INDEX(Данные!$B$2:$Q$231;;MATCH(B$2;Данные!$B$1:$Q$1;)))*(MMULT(SIGN(Данные!$B$2:$Q$231*((Данные!$B$1:$Q$1=B$2)+(Данные!$B$1:$Q$1=$A3)));TRANSPOSE(COLUMN(Данные!$B:$Q)^0))>1))))
Изменено: БМВ - 29.06.2020 11:44:35
По вопросам из тем форума, личку не читаю.
 
Надо MMULT (как фирменный прием) добавить на аватарку БМВ v 2.2 :)  
Владимир
 
БМВ, большое спасибо!!!!
 
Цитата
БМВ написал:
TRANSPOSE(COLUMN(Данные!$B:$Q)^0)
БМВ, Объясните, пожалуйста, этот момент. Это не всегда ровно 1?
 
нет, это равно множеству вертикальных единиц, по количеству равному количеству столбцов B:Q

Цитата
sokol92 написал:
добавить на аватарку БМВ v 2.2
сказано-сделано.
Изменено: БМВ - 29.06.2020 19:17:55
По вопросам из тем форума, личку не читаю.
 
:excl:  
Владимир
 
БМВ, можно было проще сделать:

=ЕСЛИ($A3=B$2;СУММ((МУМНОЖ(ЗНАК(Данные!$B$2:$Q$231);($A$3:$A$18=$A3)*98+1)=99)*МУМНОЖ(Ч(+Данные!$B$2:$Q$231);($A$3:$A$18=$A3)+($A$3:$A$18=B$2)))/2;СУММ((МУМНОЖ(ЗНАК(Данные!$B$2:$Q$231);($A$3:$A$18=$A3)+($A$3:$A$18=B$2))=2)*МУМНОЖ(Ч(+Данные!$B$2:$Q$231);($A$3:$A$18=$A3)+($A$3:$A$18=B$2))))
А если использовать массивную формулу, то:
=СУММ((МУМНОЖ(ЗНАК(Данные!$B$2:$Q$231);($A$3:$A$18=$A3)+ЕСЛИ($A3=B$2;1;$A$3:$A$18=B$2))=2)*МУМНОЖ(Ч(+Данные!$B$2:$Q$231);($A$3:$A$18=$A3)+($A$3:$A$18=B$2)))/(($A3=B$2)+1)
*Чуть причесал:
=СУММ((МУМНОЖ(Ч(Данные!$B$2:$Q$231>0);($A$3:$A$18=$A3)+ЕСЛИ($A3=B$2;1;$A$3:$A$18=B$2))=2)*МУМНОЖ(Ч(+Данные!$B$2:$Q$231);($A$3:$A$18=$A3)+($A3<>B$2)*($A$3:$A$18=B$2)))
Изменено: Светлый - 29.06.2020 22:26:44
 
Светлый,  Я по быстрому по просьбе Андрея накидал
Цитата
БМВ написал:
но есть ощущение, что можно подсократить и оптимизировать
Но тут вопрос опят встанет между краткостью и производительностью.
По вопросам из тем форума, личку не читаю.
Страницы: 1
Наверх