Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Вместе с этим товаром так же покупают и... - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин  
Мир MS Excel » Работа и общение » Мозговой штурм » Вместе с этим товаром так же покупают и...
Вместе с этим товаром так же покупают и...
Понравился вопрос?
Serge_007 Дата: Среда, 11.09.2013, 22:36 | Сообщение № 1
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2748 ±
Замечаний: ±

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

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


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

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

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

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

Файл для тестирования во вложении. Файл заполнен правильными результами.
К сообщению приложен файл: goods.xls (29.5 Kb) · 9057385.gif (29.2 Kb)


ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
СообщениеДрузья!
Вопрос скорее рациональный, нежели праздный :)

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


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

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

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

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

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

Автор - Serge_007
Дата добавления - 11.09.2013 в 22:36
MCH Дата: Четверг, 12.09.2013, 12:04 | Сообщение № 2
Группа: Админы
Ранг: Старожил
Сообщений: 2002
Репутация: 751 ±
Замечаний: ±

Цитата (Serge_007, Среда, 11.09.2013, 22:36 # 1)
Реальный массив данных равен 900 000 строк и 15 000 столбцов

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

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

Построить сводную и по ней макросом пройтись
 
Ответить
Сообщение
Цитата (Serge_007, Среда, 11.09.2013, 22:36 # 1)
Реальный массив данных равен 900 000 строк и 15 000 столбцов

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

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

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

Автор - MCH
Дата добавления - 12.09.2013 в 12:04
MCH Дата: Четверг, 12.09.2013, 12:30 | Сообщение № 3
Группа: Админы
Ранг: Старожил
Сообщений: 2002
Репутация: 751 ±
Замечаний: ±

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

Автор - MCH
Дата добавления - 12.09.2013 в 12:30
Serge_007 Дата: Четверг, 12.09.2013, 12:52 | Сообщение № 4
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2748 ±
Замечаний: ±

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

почему для "Товар3" кол-во = 0?
Цитата (Serge_007, Среда, 11.09.2013, 22:36 # 1)
Если товар присутствует только в одном заказе, то сопутствующих товаров нет, как и дублирований


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


ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
Сообщение
Эта задача не для формул
Я не говорю о решении реальной задачи (она уже решена), а говорю об увеличении скорости формул, чисто спортивный интерес :)

почему для "Товар3" кол-во = 0?
Цитата (Serge_007, Среда, 11.09.2013, 22:36 # 1)
Если товар присутствует только в одном заказе, то сопутствующих товаров нет, как и дублирований


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

Автор - Serge_007
Дата добавления - 12.09.2013 в 12:52
MCH Дата: Четверг, 12.09.2013, 12:52 | Сообщение № 5
Группа: Админы
Ранг: Старожил
Сообщений: 2002
Репутация: 751 ±
Замечаний: ±

я бы так стал решать (исходную таблицу переделал)
Если на формулах медленно на больших массивах, то можно макросом
К сообщению приложен файл: _goods.xls (72.5 Kb)
 
Ответить
Сообщениея бы так стал решать (исходную таблицу переделал)
Если на формулах медленно на больших массивах, то можно макросом

Автор - MCH
Дата добавления - 12.09.2013 в 12:52
MCH Дата: Четверг, 12.09.2013, 12:59 | Сообщение № 6
Группа: Админы
Ранг: Старожил
Сообщений: 2002
Репутация: 751 ±
Замечаний: ±

Цитата (Serge_007, Четверг, 12.09.2013, 12:52 # 4)
Если товар присутствует только в одном заказе, то сопутствующих товаров нет, как и дублирований

Ну так с этим же товаром продался "Товар5", получается, что он ему не сопутствует? а в обратную сторону - сопутствует
 
Ответить
Сообщение
Цитата (Serge_007, Четверг, 12.09.2013, 12:52 # 4)
Если товар присутствует только в одном заказе, то сопутствующих товаров нет, как и дублирований

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

Автор - MCH
Дата добавления - 12.09.2013 в 12:59
Serge_007 Дата: Четверг, 12.09.2013, 13:33 | Сообщение № 7
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2748 ±
Замечаний: ±

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


ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
Сообщение"Товар3" присутствует только в одном заказе - Заказ6, а "Товар5" есть в двух заказах - Заказ6 и Заказ7
Если тебе так будет удобнее, то приравниваем единичную продажу к случайной, а такой товар к неликвиду. Анализировать случайную продажу нет смысла, смотрим только на ликвидный товар

Автор - Serge_007
Дата добавления - 12.09.2013 в 13:33
MCH Дата: Четверг, 12.09.2013, 16:12 | Сообщение № 8
Группа: Админы
Ранг: Старожил
Сообщений: 2002
Репутация: 751 ±
Замечаний: ±

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


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

Автор - MCH
Дата добавления - 12.09.2013 в 16:12
Serge_007 Дата: Четверг, 12.09.2013, 16:19 | Сообщение № 9
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2748 ±
Замечаний: ±

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

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

Решение из 5-го поста не подходит?
Исходные данные изменять нельзя


ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
Сообщение
задачу не обязательно решать одной формулой
Обязательно

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

Решение из 5-го поста не подходит?
Исходные данные изменять нельзя

Автор - Serge_007
Дата добавления - 12.09.2013 в 16:19
MCH Дата: Четверг, 12.09.2013, 16:47 | Сообщение № 10
Группа: Админы
Ранг: Старожил
Сообщений: 2002
Репутация: 751 ±
Замечаний: ±

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

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


Сообщение отредактировал MCH - Четверг, 12.09.2013, 17:41
 
Ответить
СообщениеДля количеств есть формула в 87 знаков (с "=")
Для перечня - 178 (с "=")

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

Автор - MCH
Дата добавления - 12.09.2013 в 16:47
Serge_007 Дата: Четверг, 12.09.2013, 21:52 | Сообщение № 11
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2748 ±
Замечаний: ±

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


ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
СообщениеВеликолепно!
Миша, лови плюс :)

Автор - Serge_007
Дата добавления - 12.09.2013 в 21:52
Rioran Дата: Вторник, 17.06.2014, 11:58 | Сообщение № 12
Группа: Авторы
Ранг: Ветеран
Сообщений: 903
Репутация: 290 ±
Замечаний: 0% ±

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


Роман, Москва, voronov_rv@mail.ru
Яндекс-Деньги: 41001312674279
 
Ответить
СообщениеИнтересно было бы взглянуть на сами решения.

Автор - Rioran
Дата добавления - 17.06.2014 в 11:58
SLAVICK Дата: Среда, 30.09.2015, 11:37 | Сообщение № 13
Группа: Модераторы
Ранг: Старожил
Сообщений: 2290
Репутация: 766 ±
Замечаний: 0% ±

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

Как - то я упустил эту тему год назад :) .
Когда - то была такая тема здесь.
там мы с ANVG cделали интересный шаблон - он у меня в копилке, немного доработанный для моих нужд ;)
Ой сначала написал пост - потом увидел, что это мозговой штурм... ну пусть будет и мой пример.
К сообщению приложен файл: 21_2013-09-29_1.xlsm (87.1 Kb)


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

Сообщение отредактировал SLAVICK - Среда, 30.09.2015, 11:46
 
Ответить
Сообщение
Интересно было бы взглянуть на сами решения.

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

Автор - SLAVICK
Дата добавления - 30.09.2015 в 11:37
Светлый Дата: Понедельник, 12.10.2015, 11:42 | Сообщение № 14
Группа: Друзья
Ранг: Старожил
Сообщений: 1823
Репутация: 507 ±
Замечаний: 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
 
Ответить
СообщениеДолго не мог ничего придумать. Для количеств 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))))

Автор - Светлый
Дата добавления - 12.10.2015 в 11:42
Мир MS Excel » Работа и общение » Мозговой штурм » Вместе с этим товаром так же покупают и...
  • Страница 1 из 1
  • 1
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2024 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!