Вместе с этим товаром так же покупают и...

Старая форма входа

Страница 1 из 1 1
Модератор форума: китин

Serge_007

Дата: Среда, 11.09.2013, 22:36 | Сообщение № 1

Группа: Админы

Ранг: Местный житель

Сообщений: 16475

Репутация: 2749 ±

Замечаний: ±

Excel 2016

Друзья!
Вопрос скорее рациональный, нежели праздный

Есть матрица заказов товаров. Из неё надо выбрать формулами сопутствующие товары из разных заказов и подсчитать количество дублирований

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

Вот, собственно и все

Что необходимо:
1. Ускорить работу формул. Реальный массив данных равен 900 000 строк и 15 000 столбцов
2. Традиционно - написать самые короткие формулы

Задачи не связаны друг с другом, но если кому-то удастся их совместить - тогда респект

Файл для тестирования во вложении. Файл заполнен правильными результами.

К сообщению приложен файл: goods.xls (29.5 Kb) · 9057385.gif (29.2 Kb)

ЮMoney:41001419691823 | WMR:126292472390

Ответить

MCH

Дата: Четверг, 12.09.2013, 12:04 | Сообщение № 2

Группа: Админы

Ранг: Старожил

Сообщений: 2004

Репутация: 752 ±

Замечаний: ±

Цитата (Serge_007, Среда, 11.09.2013, 22:36 # 1)

Реальный массив данных равен 900 000 строк и 15 000 столбцов

Эта задача не для формул (не для формул массива в одной ячейке), больше подходит для макросов

Сколько всего товаров и заказов?
Сколько уникальных записей товар/заказ?
Можно на базе исходной таблицы

Построить сводную и по ней макросом пройтись

Ответить

MCH

Дата: Четверг, 12.09.2013, 12:30 | Сообщение № 3

Группа: Админы

Ранг: Старожил

Сообщений: 2004

Репутация: 752 ±

Замечаний: ±

А почему для "Товар3" кол-во = 0? он ведь с "Товар5" в одном заказе, и "Заказ4" куда-то делся

Ответить

Serge_007

Дата: Четверг, 12.09.2013, 12:52 | Сообщение № 4

Группа: Админы

Ранг: Местный житель

Сообщений: 16475

Репутация: 2749 ±

Замечаний: ±

Excel 2016

Цитата (MCH, Четверг, 12.09.2013, 12:04 # 2)

Эта задача не для формул

Я не говорю о решении реальной задачи (она уже решена), а говорю об увеличении скорости формул, чисто спортивный интерес

Цитата (MCH, Четверг, 12.09.2013, 12:30 # 3)

почему для "Товар3" кол-во = 0?

Цитата (Serge_007, Среда, 11.09.2013, 22:36 # 1)

Если товар присутствует только в одном заказе, то сопутствующих товаров нет, как и дублирований

Цитата (MCH, Четверг, 12.09.2013, 12:30 # 3)

"Заказ4" куда-то делся

Это не принципиально. Например "Заказ4" был внутренним, для нужд компании. А надо анализировать только заказы клиентов

ЮMoney:41001419691823 | WMR:126292472390

Ответить

MCH

Дата: Четверг, 12.09.2013, 12:52 | Сообщение № 5

Группа: Админы

Ранг: Старожил

Сообщений: 2004

Репутация: 752 ±

Замечаний: ±

я бы так стал решать (исходную таблицу переделал)
Если на формулах медленно на больших массивах, то можно макросом

К сообщению приложен файл: _goods.xls (72.5 Kb)

Ответить

MCH

Дата: Четверг, 12.09.2013, 12:59 | Сообщение № 6

Группа: Админы

Ранг: Старожил

Сообщений: 2004

Репутация: 752 ±

Замечаний: ±

Цитата (Serge_007, Четверг, 12.09.2013, 12:52 # 4)

Если товар присутствует только в одном заказе, то сопутствующих товаров нет, как и дублирований

Ну так с этим же товаром продался "Товар5", получается, что он ему не сопутствует? а в обратную сторону - сопутствует

Ответить

Serge_007

Дата: Четверг, 12.09.2013, 13:33 | Сообщение № 7

Группа: Админы

Ранг: Местный житель

Сообщений: 16475

Репутация: 2749 ±

Замечаний: ±

Excel 2016

"Товар3" присутствует только в одном заказе - Заказ6, а "Товар5" есть в двух заказах - Заказ6 и Заказ7
Если тебе так будет удобнее, то приравниваем единичную продажу к случайной, а такой товар к неликвиду. Анализировать случайную продажу нет смысла, смотрим только на ликвидный товар

ЮMoney:41001419691823 | WMR:126292472390

Ответить

MCH

Дата: Четверг, 12.09.2013, 16:12 | Сообщение № 8

Группа: Админы

Ранг: Старожил

Сообщений: 2004

Репутация: 752 ±

Замечаний: ±

Я так понимаю что задачу не обязательно решать одной формулой, можно ведь несколькими (с доп столбцами и т.п.), важно быстродействие?
Задача 1 (количество) и задача 2 (перечень) это ведь разные задачи не зависящие друг от друга, что нужно первоначально - определить количество?
Сколько допустимо использовать вспомогательных ячеек?
Решение из 5-го поста не подходит? (исключить ситуацию, что товар находится только в одном заказе очень легко)

Сообщение отредактировал MCH - Четверг, 12.09.2013, 16:21

Ответить

Serge_007

Дата: Четверг, 12.09.2013, 16:19 | Сообщение № 9

Группа: Админы

Ранг: Местный житель

Сообщений: 16475

Репутация: 2749 ±

Замечаний: ±

Excel 2016

Цитата (MCH, Четверг, 12.09.2013, 16:12 # 8)

задачу не обязательно решать одной формулой

Обязательно

Цитата (MCH, Четверг, 12.09.2013, 16:12 # 8)

что нужно первоначально - определить количество?

Да

Цитата (MCH, Четверг, 12.09.2013, 16:12 # 8)

Решение из 5-го поста не подходит?

Исходные данные изменять нельзя

ЮMoney:41001419691823 | WMR:126292472390

Ответить

MCH

Дата: Четверг, 12.09.2013, 16:47 | Сообщение № 10

Группа: Админы

Ранг: Старожил

Сообщений: 2004

Репутация: 752 ±

Замечаний: ±

Для количеств есть формула в 87 знаков (с "=")
Для перечня - 178 (с "=")

Сами формулы работают по тому же принципу, что и формулы в 5 посте, только при другой организации данных значительно легче получить нужный результат, и расчетов получается меньше, что сказывается на времени расчетов

Сообщение отредактировал MCH - Четверг, 12.09.2013, 17:41

Ответить

Serge_007

Дата: Четверг, 12.09.2013, 21:52 | Сообщение № 11

Группа: Админы

Ранг: Местный житель

Сообщений: 16475

Репутация: 2749 ±

Замечаний: ±

Excel 2016

Великолепно!
Миша, лови плюс

ЮMoney:41001419691823 | WMR:126292472390

Ответить

Rioran

Дата: Вторник, 17.06.2014, 11:58 | Сообщение № 12

Группа: Авторы

Ранг: Ветеран

Сообщений: 903

Репутация: 290 ±

Замечаний: 0% ±

Excel 2013

Интересно было бы взглянуть на сами решения.

Роман, Москва, voronov_rv@mail.ru
Яндекс-Деньги: 41001312674279

Ответить

SLAVICK

Дата: Среда, 30.09.2015, 11:37 | Сообщение № 13

Группа: Модераторы

Ранг: Старожил

Сообщений: 2290

Репутация: 766 ±

Замечаний: 0% ±

2019

Цитата Rioran, 17.06.2014 в 11:58, в сообщении № 12

Интересно было бы взглянуть на сами решения.

Как - то я упустил эту тему год назад

.
Когда - то была такая тема здесь.
там мы с ANVG cделали интересный шаблон - он у меня в копилке, немного доработанный для моих нужд

Ой сначала написал пост - потом увидел, что это мозговой штурм... ну пусть будет и мой пример.

К сообщению приложен файл: 21_2013-09-29_1.xlsm (87.1 Kb)

Иногда все проще чем кажется с первого взгляда.

Сообщение отредактировал SLAVICK - Среда, 30.09.2015, 11:46

Ответить

Светлый

Дата: Понедельник, 12.10.2015, 11:42 | Сообщение № 14

Группа: Друзья

Ранг: Старожил

Сообщений: 1851

Репутация: 523 ±

Замечаний: 0% ±

Excel 2013, 2016

Долго не мог ничего придумать. Для количеств 85 знаков. (без =)
И список 177 знаков.
Так как все сроки прошли, вскрываю свой вариант для количества:

Код

(СЧЁТЗ(B5:E5)>1)*(СЧЁТ(МУМНОЖ(1-ЕОШИБКА(ПОИСКПОЗ(B$5:E$11;B5:E5;));СТРОКА(1:4))^0)-1)

или

Код

(СЧЁТЗ(B5:E5)>1)*(СЧЁТ(1/МУМНОЖ(1-ЕОШИБКА(ПОИСКПОЗ(B$5:E$11;B5:E5;));СТРОКА(1:4)))-1)

что быстрее будет работать на больших массивах.
И для списка:

Код

ЕСЛИ($G15<СТОЛБЕЦ(A1);"";ИНДЕКС($A$5:$A$11;НАИМЕНЬШИЙ(ЕСЛИ(МУМНОЖ(1-ЕОШИБКА(ПОИСКПОЗ($B$5:$E$11;$B5:$E5;));СТРОКА(1:4))*(СТРОКА(A1)<>СТРОКА($1:$7));СТРОКА($1:$7));СТОЛБЕЦ(A1))))

Программировать проще, чем писать стихи.

Сообщение отредактировал Светлый - Вторник, 13.10.2015, 06:35

Ответить

Вместе с этим товаром так же покупают и...

Страница 1 из 1
1