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

Вход

Регистрация

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

 

= Мир MS Excel/Частота встречаемости продуктов в заказах - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Частота встречаемости продуктов в заказах (Формулы/Formulas)
Частота встречаемости продуктов в заказах
Wine1 Дата: Вторник, 08.06.2021, 20:00 | Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

Всем привет!

Буду очень благодарна, если вы мне подскажите, как создать таблицу/формулу со следующими данными. И в принципе, можно ли такую формулу создать?! Мозговой штурм :)
Суть:
Есть продажи (номер заказа, состав заказа (артикул; шт и т.д.))
Хочется проанализировать составы заказов, чтобы выявить продукты, которые в одном заказе встречаются вместе чаще всего. Проще говоря, какие продукты клиенты заказывают вместе.
Ума не приложу, как задать формулу, которая будет в таблице выдавать артикулы по частоте использования в одном заказе.
Из таблицы важный параметр, который должен получаться на выходе - это артикулы.
При этом не учитывать заказы, в котором есть только 1 продукт.
Тут важно выявить комбо продукты.

Во вложении пример таблицы.
К сообщению приложен файл: 8567025.xlsx(9.9 Kb)


Сообщение отредактировал Wine1 - Вторник, 08.06.2021, 20:01
 
Ответить
СообщениеВсем привет!

Буду очень благодарна, если вы мне подскажите, как создать таблицу/формулу со следующими данными. И в принципе, можно ли такую формулу создать?! Мозговой штурм :)
Суть:
Есть продажи (номер заказа, состав заказа (артикул; шт и т.д.))
Хочется проанализировать составы заказов, чтобы выявить продукты, которые в одном заказе встречаются вместе чаще всего. Проще говоря, какие продукты клиенты заказывают вместе.
Ума не приложу, как задать формулу, которая будет в таблице выдавать артикулы по частоте использования в одном заказе.
Из таблицы важный параметр, который должен получаться на выходе - это артикулы.
При этом не учитывать заказы, в котором есть только 1 продукт.
Тут важно выявить комбо продукты.

Во вложении пример таблицы.

Автор - Wine1
Дата добавления - 08.06.2021 в 20:00
Светлый Дата: Вторник, 08.06.2021, 22:13 | Сообщение № 2
Группа: Друзья
Ранг: Старожил
Сообщений: 1617
Репутация: 426 ±
Замечаний: 0% ±

Excel 2010, 2013
Здравствуйте!
Есть готовое решение.
*Для пробы:
К сообщению приложен файл: 8567025-1.xlsx(15.6 Kb)


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

Сообщение отредактировал Светлый - Вторник, 08.06.2021, 23:41
 
Ответить
СообщениеЗдравствуйте!
Есть готовое решение.
*Для пробы:

Автор - Светлый
Дата добавления - 08.06.2021 в 22:13
Wine1 Дата: Среда, 09.06.2021, 05:03 | Сообщение № 3
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

Да, допустила ошибку в артикулах. Исправила :)
Правильно понимаю, что значения выделенные голубым цветом как раз и будут те самые повторения в заказах? Т.е нужно обращать внимание на значение 2 и больше.
К сообщению приложен файл: 8567025-1-2.xlsx(16.1 Kb)
 
Ответить
СообщениеДа, допустила ошибку в артикулах. Исправила :)
Правильно понимаю, что значения выделенные голубым цветом как раз и будут те самые повторения в заказах? Т.е нужно обращать внимание на значение 2 и больше.

Автор - Wine1
Дата добавления - 09.06.2021 в 05:03
Светлый Дата: Среда, 09.06.2021, 08:10 | Сообщение № 4
Группа: Друзья
Ранг: Старожил
Сообщений: 1617
Репутация: 426 ±
Замечаний: 0% ±

Excel 2010, 2013
Формулу поменял. Какая будет работать быстрее на больших данных, не проверял. Тормозить будут сильно.
*Файл не вкладывается. Выбрасывает на гостя.
Код
=ЕСЛИ(СТРОКА(I1)+СТОЛБЕЦ(A2)<$G$1;СУММПРОИЗВ(СЧЁТЕСЛИМН($A$2:$A$30;Ч(ИНДЕКС(($H2=$B$2:$B$30)*$A$2:$A$30;));$B$2:$B$30;I$1));"")
**Для больших данных должна работать быстрее:
Код
=ЕСЛИ(СТРОКА(I1)+СТОЛБЕЦ(A2)<$G$1;СУММПРОИЗВ(СЧЁТЕСЛИМН($A$2:$A$30;Ч(ИНДЕКС(НАИБОЛЬШИЙ(($H2=$B$2:$B$30)*$A$2:$A$30;СТРОКА(ДВССЫЛ("1:"&СЧЁТЕСЛИ($B$2:$B$30;$H2))));));$B$2:$B$30;I$1));"")
К сообщению приложен файл: 8567025-2.xlsx(13.5 Kb)


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

Сообщение отредактировал Светлый - Среда, 09.06.2021, 08:45
 
Ответить
СообщениеФормулу поменял. Какая будет работать быстрее на больших данных, не проверял. Тормозить будут сильно.
*Файл не вкладывается. Выбрасывает на гостя.
Код
=ЕСЛИ(СТРОКА(I1)+СТОЛБЕЦ(A2)<$G$1;СУММПРОИЗВ(СЧЁТЕСЛИМН($A$2:$A$30;Ч(ИНДЕКС(($H2=$B$2:$B$30)*$A$2:$A$30;));$B$2:$B$30;I$1));"")
**Для больших данных должна работать быстрее:
Код
=ЕСЛИ(СТРОКА(I1)+СТОЛБЕЦ(A2)<$G$1;СУММПРОИЗВ(СЧЁТЕСЛИМН($A$2:$A$30;Ч(ИНДЕКС(НАИБОЛЬШИЙ(($H2=$B$2:$B$30)*$A$2:$A$30;СТРОКА(ДВССЫЛ("1:"&СЧЁТЕСЛИ($B$2:$B$30;$H2))));));$B$2:$B$30;I$1));"")

Автор - Светлый
Дата добавления - 09.06.2021 в 08:10
Wine1 Дата: Среда, 09.06.2021, 12:45 | Сообщение № 5
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

Взяла для теста 350 строк
Выглядит это примерно вот так %) http://prntscr.com/14v3mru
К сообщению приложен файл: 8703449.jpg(24.3 Kb)
 
Ответить
СообщениеВзяла для теста 350 строк
Выглядит это примерно вот так %) http://prntscr.com/14v3mru

Автор - Wine1
Дата добавления - 09.06.2021 в 12:45
Светлый Дата: Среда, 09.06.2021, 13:39 | Сообщение № 6
Группа: Друзья
Ранг: Старожил
Сообщений: 1617
Репутация: 426 ±
Замечаний: 0% ±

Excel 2010, 2013
Выглядит это

Страшно выглядит. Возьмите мой последний файл. Там УФ выделено и формулы быстрые.
Сколько реально строк будет и сколько артикулов?


Программировать проще, чем писать стихи.
 
Ответить
Сообщение
Выглядит это

Страшно выглядит. Возьмите мой последний файл. Там УФ выделено и формулы быстрые.
Сколько реально строк будет и сколько артикулов?

Автор - Светлый
Дата добавления - 09.06.2021 в 13:39
Wine1 Дата: Среда, 09.06.2021, 15:26 | Сообщение № 7
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

Честно говоря, не совсем понимаю, чем он отличается от предыдущего, тк в обоих вариантах артикулы по горизонтали будут иметь такой же вид, как на скриншоте выше :(
http://prntscr.com/14vtauo

В файле может быть 300 артикулов и 5тыс строк
Выборка достаточно большая.

Мне кажется мы решаем две разные задачи)) либо я не понимаю логику в файле
В вашем файле отмечены желтым цветом - кол-во повторений.
А мне необходимо найти товары, которые комбинацией встречаются вместе в одном уникальном заказе. Как я для себя поняла, это голубой цвет.
 
Ответить
СообщениеЧестно говоря, не совсем понимаю, чем он отличается от предыдущего, тк в обоих вариантах артикулы по горизонтали будут иметь такой же вид, как на скриншоте выше :(
http://prntscr.com/14vtauo

В файле может быть 300 артикулов и 5тыс строк
Выборка достаточно большая.

Мне кажется мы решаем две разные задачи)) либо я не понимаю логику в файле
В вашем файле отмечены желтым цветом - кол-во повторений.
А мне необходимо найти товары, которые комбинацией встречаются вместе в одном уникальном заказе. Как я для себя поняла, это голубой цвет.

Автор - Wine1
Дата добавления - 09.06.2021 в 15:26
Светлый Дата: Среда, 09.06.2021, 16:54 | Сообщение № 8
Группа: Друзья
Ранг: Старожил
Сообщений: 1617
Репутация: 426 ±
Замечаний: 0% ±

Excel 2010, 2013
Как я для себя поняла, это голубой цвет.
Совершенно верно.
* Артикулы сами протянете до 300 вниз и вправо.
**Добавил в файл УФ и выбор ограничения. Перезалил.
К сообщению приложен файл: 8567025-3.xlsx(69.6 Kb)


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

Сообщение отредактировал Светлый - Четверг, 10.06.2021, 06:19
 
Ответить
Сообщение
Как я для себя поняла, это голубой цвет.
Совершенно верно.
* Артикулы сами протянете до 300 вниз и вправо.
**Добавил в файл УФ и выбор ограничения. Перезалил.

Автор - Светлый
Дата добавления - 09.06.2021 в 16:54
Wine1 Дата: Четверг, 10.06.2021, 11:03 | Сообщение № 9
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

Доброе утро.

В целом круто, спасибо большое :) работает, но большой объем данных (3тыс строк) не протянул даже за ночь :( попробую частями делать
Второй вопрос, формула по определению работает только на 2 sku? Или можно сделать больше? до 6, например.
 
Ответить
СообщениеДоброе утро.

В целом круто, спасибо большое :) работает, но большой объем данных (3тыс строк) не протянул даже за ночь :( попробую частями делать
Второй вопрос, формула по определению работает только на 2 sku? Или можно сделать больше? до 6, например.

Автор - Wine1
Дата добавления - 10.06.2021 в 11:03
Светлый Дата: Четверг, 10.06.2021, 11:33 | Сообщение № 10
Группа: Друзья
Ранг: Старожил
Сообщений: 1617
Репутация: 426 ±
Замечаний: 0% ±

Excel 2010, 2013
работает только на 2 sku?
Что такое "sku"?
В большой вспомогательной таблице формула подсчитывает сколько раз данная ПАРА (строка-столбец) встречается совместно в одном заказе.
В средней табличке: Сколько раз встречается (по убыванию) и пара артикулов.
Столбец и строка позволяют подсчитывать только пары на пересечении.
Формулами я решал только из спортивного интереса. Такие задачи надо решать макросами.


Программировать проще, чем писать стихи.
 
Ответить
Сообщение
работает только на 2 sku?
Что такое "sku"?
В большой вспомогательной таблице формула подсчитывает сколько раз данная ПАРА (строка-столбец) встречается совместно в одном заказе.
В средней табличке: Сколько раз встречается (по убыванию) и пара артикулов.
Столбец и строка позволяют подсчитывать только пары на пересечении.
Формулами я решал только из спортивного интереса. Такие задачи надо решать макросами.

Автор - Светлый
Дата добавления - 10.06.2021 в 11:33
Wine1 Дата: Четверг, 10.06.2021, 11:36 | Сообщение № 11
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

Цитата Светлый, 10.06.2021 в 11:33, в сообщении № 10 ()
В большой вспомогательной таблице формула подсчитывает сколько раз данная ПАРА (строка-столбец) встречается совместно в одном заказе.

Да-да, как это в этом и был вопрос.
Подсчитывает только 2 товара.
Будет работать подсчет на 3 товара, на 4, 5 и 6?

Цитата Светлый, 10.06.2021 в 11:33, в сообщении № 10 ()
Что такое "sku"?

Товар
 
Ответить
Сообщение
Цитата Светлый, 10.06.2021 в 11:33, в сообщении № 10 ()
В большой вспомогательной таблице формула подсчитывает сколько раз данная ПАРА (строка-столбец) встречается совместно в одном заказе.

Да-да, как это в этом и был вопрос.
Подсчитывает только 2 товара.
Будет работать подсчет на 3 товара, на 4, 5 и 6?

Цитата Светлый, 10.06.2021 в 11:33, в сообщении № 10 ()
Что такое "sku"?

Товар

Автор - Wine1
Дата добавления - 10.06.2021 в 11:36
Светлый Дата: Четверг, 10.06.2021, 15:04 | Сообщение № 12
Группа: Друзья
Ранг: Старожил
Сообщений: 1617
Репутация: 426 ±
Замечаний: 0% ±

Excel 2010, 2013
Будет работать подсчет на 3 товара, на 4, 5 и 6?
Формулами не будет.
не протянул даже за ночь
Или вообще не дождётесь, когда пересчитает.


Программировать проще, чем писать стихи.
 
Ответить
Сообщение
Будет работать подсчет на 3 товара, на 4, 5 и 6?
Формулами не будет.
не протянул даже за ночь
Или вообще не дождётесь, когда пересчитает.

Автор - Светлый
Дата добавления - 10.06.2021 в 15:04
Wine1 Дата: Четверг, 10.06.2021, 15:15 | Сообщение № 13
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

Я тут решила облегчить таблицу.
Поняла, что
- по вертикали идут артикулы без дублей, те нужен просто список уникальный значений. Скопировала его отдельно, удалила дубли. Убрала формулу, вставила список артикулов.
- по горизонтали, исходя из формулы, идут те же самые артикулы, только в обратном порядке. Скопировала их в отдельный столбец, записала макрос для обратного порядка значений, поставила выполняться. Как выполнится, вставлю в верхнюю горизонтальную строку
И далее, буду протягивать формулу по центру. Посмотрим, может полегче будет и быстрее пойдет. %) :)

Вам большое спасибо за помощь и разработку предложенного формата. Он рабочий на небольшом кол-ве данных ;) буду пользоваться.


Сообщение отредактировал Wine1 - Четверг, 10.06.2021, 15:16
 
Ответить
СообщениеЯ тут решила облегчить таблицу.
Поняла, что
- по вертикали идут артикулы без дублей, те нужен просто список уникальный значений. Скопировала его отдельно, удалила дубли. Убрала формулу, вставила список артикулов.
- по горизонтали, исходя из формулы, идут те же самые артикулы, только в обратном порядке. Скопировала их в отдельный столбец, записала макрос для обратного порядка значений, поставила выполняться. Как выполнится, вставлю в верхнюю горизонтальную строку
И далее, буду протягивать формулу по центру. Посмотрим, может полегче будет и быстрее пойдет. %) :)

Вам большое спасибо за помощь и разработку предложенного формата. Он рабочий на небольшом кол-ве данных ;) буду пользоваться.

Автор - Wine1
Дата добавления - 10.06.2021 в 15:15
Светлый Дата: Четверг, 10.06.2021, 16:37 | Сообщение № 14
Группа: Друзья
Ранг: Старожил
Сообщений: 1617
Репутация: 426 ±
Замечаний: 0% ±

Excel 2010, 2013
те нужен просто список уникальный значений
Немного переделал структуру и формулы. Может быть, будет быстрее работать.
К сообщению приложен файл: 8567025-4.xlsx(80.2 Kb)


Программировать проще, чем писать стихи.
 
Ответить
Сообщение
те нужен просто список уникальный значений
Немного переделал структуру и формулы. Может быть, будет быстрее работать.

Автор - Светлый
Дата добавления - 10.06.2021 в 16:37
bmv98rus Дата: Четверг, 10.06.2021, 22:22 | Сообщение № 15
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3496
Репутация: 616 ±
Замечаний: 0% ±

Excel 2013/2016
Что такое "sku"?

Товар
это не совсем товар это Stock Keeping Unit что означает не прост товар, а именно складская единица учета. И это может быть штучный товар или упаковка этого товара, например в промо упаковке, и это будет разной SKU, но содержимое одинаковое.

ну и про кросс не забываем


Замечательный Временно просто медведь , процентов на 20.

Сообщение отредактировал bmv98rus - Четверг, 10.06.2021, 22:25
 
Ответить
Сообщение
Что такое "sku"?

Товар
это не совсем товар это Stock Keeping Unit что означает не прост товар, а именно складская единица учета. И это может быть штучный товар или упаковка этого товара, например в промо упаковке, и это будет разной SKU, но содержимое одинаковое.

ну и про кросс не забываем

Автор - bmv98rus
Дата добавления - 10.06.2021 в 22:22
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Частота встречаемости продуктов в заказах (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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