Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Подсчет количества повторов из списка комбинаций
 
#1
15.09.2015 18:56:00
Уважаемые эксперты, доброго времени суток!
Нуждаюсь в вашем совете, сам не могу додуматься.
Имеется список чеков, в которых показаны продажи шапок и шарфов.Нужно вычислить какое сочетание артикулов в чеках встречается чаще всего. Т.е. какие позиции больше всего покупают в комплектах. Интересует сочетание 2-х артикулов, но можно ли вычислять еще и по сочетанию трех артикулов?
Excel 2010. Пример во вложении.

С уважением,  Lari.
 
 
 
#2
15.09.2015 21:03:50
Lari, здравия. Покажите в примере, как Вы видите результат вручную.

Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
 
 
 
#3
19.09.2015 08:51:02
Вариант с помощью консолидации:
1) щелкните в ячейке G1 - вкладка "Данные" - Консолидация
2) щелкните в поле "Ссылка" и выделите два столбца D:E - Добавить
3) внизу поставьте галочку "значения левого столбца" - OK
4) появятся данные. В ячейку H1 впишите заголовок (чтобы потом можно было провести сортировку)
5) проведите сортировку от большего к меньшему и вверху таблицы окажутся два артикула, которые покупаются чаще.
 
 
 
#4
21.09.2015 17:42:02
 JayBhagavan, доброго времени суток!
Желаемый результат во вложении.
Сделал по двум позициям и возможно ли по трем?
Условие, чтобы наименования были разными и по разные стороны, меньшего приоритета чем выведение самого частого сочетания двух или трех позиций покупаемых в одном чеке.
С уважением, Lari.
Изменено: Lari - 21.09.2015 17:42:41
 
 
 
#5
21.09.2015 17:44:31
Karataev, здравствуйте!
Спасибо за подробную инструкцию, но к сожалению  , этот вариант не выдает самых часто покупаемых позиций вместе.
 
 
 
#6
21.09.2015 20:00:20
Lari, здравия. Не вижу каким образом у Вас в файле образовалась сумма в графе "кол-во покупок в одном чеке".

Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
 
 
 
#7
22.09.2015 06:23:22
JayBhagavan
У меня нет метода расчета, только вручную. В этом и заключается мой вопрос, как через формулу или макрос посчитать и вывести артикула покупаемые вместе, т.е. в одном чеке.В какой последовательности не важно, важна группировка.
 
 
 
#8
22.09.2015 06:44:34
Lari, мне и нужно понять, как у Вас получились вручную хотя бы пару чисел. Покажите какие чеки суммировались для пары чисел. (я пока ещё не представляю себе алгоритм, думаю, он есть, но пока не вижу его)

Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
 
 
 
#9
22.09.2015 08:57:00
Доброе время суток
JayBhagavan, ТС во многом не понятен. В исходном посте - иногда встречающаяся тема "найти с этим товаром чаще всего покупают", а в примере результата выводятся только с какими артикулами шапок какие артикулы шарфов покупают (хотя в есть чеки, где есть и шапки разных артикулов).
Количество покупок в чеке - это вообще "песня". Сколько с данным артикулом шапки купили данного артикула шарфа? Или Сколько куплено на данный артикул шарфа данного артикула шапки? Или их сумма? Почему не сбивается с примером исходных данных никаким образом :(
В общем как я сам понял. Сделано на SQL. В папку c:\1 кладём скачанный файл, на Листе8 на вкладке "Данные" жмём кнопку "Обновить". Получаем следующий результат.
Артикул шапки, артикул шарфа, Итого шапок (сколько шапок данного артикула было куплено с данным артикулом шарфа, если они были в одном чеке), Итого шарфов (аналогично).
Таблицу База чеков можно пополнять, изменять - заголовки только не трогайте.
Успехов.
Изменено: Андрей VG - 22.09.2015 08:58:18
 
 
 
#10
22.09.2015 19:27:42
JayBhagavan,Андрей VG,  прошу прощения за нечетко сформулированный вопрос.
JayBhagavan, Пример:
в корзине №1 лежит яблоко и груша
в корзине №2 лежит яблоко и апельсин
в корзине №3 лежит груша и яблоко
в корзине №4 лежит абрикос и апельсин
Имеем сочетаний: яблоко и груша - 2
                            яблоко и апельсин - 1
                            абрикос и апельсин - 1
Конечно задача усложняется когда в корзине больше двух позиций.Значит ли это что для решения этой задачи нужно оставлять только те корзины где 2 позиции?!
1-й файл с исходными данными я ниоткуда не скачивал, сам его создал, но он имеет такой вид когда я его скачиваю из базы данных в ексель.
2-й файл с необходимым результатом я не высчитывал а лишь показал как я представляю себе результат.

Андрей VG, Ваш файл скачал, разместил на диске С в папке 1, но пишет что "объект"база чеков$" не найден ядром СУБД аксесс"

С уважением, Lari.
Изменено: Lari - 22.09.2015 19:28:18
 
 
 
#11
23.09.2015 09:14:31
Доброе время суток
Цитата
Lari написал: Ваш файл скачал, разместил на диске С в папке 1, но пишет что "объект"база чеков$" не найден ядром СУБД аксесс"
Что то не верится. Для страховки. Сделайте активной ячейку таблицы на "Лист8". На вкладке "Работа с таблицами" в группе "Работа со внешними таблицами" нажмите кнопку "Свойства", далее в диалоге нажмите кнопку свойства подключения. В ещё одном диалоге выберите вкладку "Определение", в текстовом поле "Строка подключения" после Data Source= проверьте путь и имя файла. Должно быть написан полный путь и имя файла, который вы открыли.

Цитата
Lari написал: прошу прощения за нечетко сформулированный вопрос
Lari, понятнее не стало. Вы хотите классическое (по вашим первым данным) с этим товаром чаще всего в чеке покупают? Или вы хотите для каждого артикула шапки определить какие артикулы шарфов чаще покупают с этими артикулами шапок (это из вашего примера результата вытекает, как я это понял)?
 
 
 
#12
23.09.2015 19:18:20
Андрей VG, здравствуйте!
Ваш файл работает.Результат фантастика,такой аналитики я  не ожидал.Позвольте задать несколько вопросов:
1. Где производятся все вычисления, я с таким раньше никогда не сталкивался?
2. Не сочтите за наглость, я уже рад выше крыши,но возможно ли добавить на "лист 8" столбец "количество общих чеков"?
3. Я так понимаю столбец "№" во вкладке "база чеков" никак не участвует в расчетах?!
4. Если вместо шарфов и шапок (не трогал  названия столбцов), поставить трусы и майки,то таблица не выдает результата.(придется их кодировать шарфами и шапками).
Большое спасибо за эту таблицу.
С уважением, Lari.
Изменено: Lari - 23.09.2015 20:18:03
 
 
 
#13
24.09.2015 03:24:26
Доброе время суток, Lari.
Давайте по порядку
Цитата
1. Где производятся все вычисления, я с таким раньше никогда не сталкивался?
Выполняется SQL-запрос средствами драйвера баз данных Access к таблице на листе книги Excel. Вы можете посмотреть его в свойствах подключения на вкладке "Определение" поле ввода "Текст команды". Выше я писал, как до этого места добраться (в плане изменения пути и имени файла). Если вы занимаетесь аналитикой, то вам прямая дорога к изучению SQL ;)
Цитата
2. Не сочтите за наглость, я уже рад выше крыши,но возможно ли добавить на "лист 8" столбец "количество общих чеков"?
Не проблема - это одна строчка
Код
Count(t2.[Кол-во]) As [Всего в чеках]
. Правда, с этим могут быть проблемы. Если по каким-то причинам в чеке будет больше одного раза упомянут один и тот же артикул, то количество увеличится на величину таких упоминаний. В вашем примере такого нет, но всё может быть. При необходимости, вы можете изменить SQL-запрос так, чтобы подавить подобные ошибки.
Цитата
3. Я так понимаю столбец "№" во вкладке "база чеков" никак не участвует в расчетах?!
Нет не используется. Насколько понимаю - это числовой аналог артикула, при желании его вы его тоже можете вывести, разобравшись с SQL.
Цитата
4. Если вместо шарфов и шапок (не трогал  названия столбцов), поставить трусы и майки,то таблица не выдает результата.
Изменил, теперь на листе "Отчёт" в ячейки B1 и B2 введите интересующие наименования и на вкладке "Данные" нажмите кнопку "Обновить всё", перекодирование не потребуется.

Файл примера нужно положить в ту же папку c:\1 с названием как во вложении (ну, или задать путь и имя файла после DBQ= в свойствах подключения).

Успехов.
Изменено: Андрей VG - 24.09.2015 03:28:27
 
 
 
#14
27.09.2015 17:24:10
Андрей VG, все работает замечательно. Вы мне открыли новый мир .Ваше решение этой задачи восхитительно. Да, я занимаюсь аналитикой, но еще в начале пути. Буду искать литературу для освоения Access и SQL.
С уважением, Lari.
 
 
 
#15
07.02.2017 13:00:09
Всем добрый день!
Возникла схожая задача, но наименований не 2, а 15. Как скорректировать запрос, который предложил Андрей VG, чтобы в итоге выводился список, где бы отображалось частота встречаемости разных комбинаций из двух товаров (в порядке убывания)?

К примеру:
У нас есть 15 позиций молочных изделий. Мне нужно:
Скрытый текст

Формат данных привел к столбцам на листе "база чеков".
Также предвижу, что в скором времени будет не 15 позиций, а 150-200 (целая категория)
Есть возможность унифицировать выдачу для разного количества вводимых параметров?

Заранее спасибо.
Изменено: dentyur - 07.02.2017 13:12:03
 
 
 
#16
09.02.2017 08:59:09
UP. Может есть еще инструменты для оценки "этот товар чаще всего встречается с"?
 
 
 
#17
09.02.2017 10:20:25
Доброе время суток.
Цитата
dentyur написал:
этот товар чаще всего встречается с"
Пробовали?
 
 
 
#18
09.02.2017 11:20:18
Решение нашел, спасибо! Кому интересно, вот ссылка: http://www.planetaexcel.ru/bitrix/components/bitrix/forum.interface/show_file.php?fid=77561&action=d...
 
 
 
#19
15.02.2017 14:52:10
Интересен файл, но страшно скачивать :(
 
 
 
#20
15.02.2017 15:17:46
Доброе время суток.
Цитата
Lari написал:
Интересен файл, но страшно скачиват
Чем страшен? Или теперь PQL only? :)
 
 
 
#21
15.02.2017 19:05:22
Меня ссылка на битрикс приводила, я по незнанию испугался :-)

В итоге файл скачал, все очень круто работает.
Я так понял что чеки с единичными продажам он отсекает и автоматически определяет количество заполненных строк.
Осталось попробовать с большим количеством данных, поместится ли результат в ексель.
 
 
 
#22
14.09.2021 05:22:30
Что-то ссылка из сообщения 18 не работает. хотя ссылка на этот сайт.
Изменено: Lari - 14.09.2021 05:46:08 (нашел у себя в архиве файл из той ссылки)
 
 
 
#23
14.09.2021 15:58:52
Попробовал реализовать на PQ.
Не совсем красиво, но получилось, если конечно правильно понял.
Изменено: Msi2102 - 14.09.2021 16:05:37
 
 
 
#24
23.01.2023 14:23:48
Цитата
написал:
Решение нашел, спасибо! Кому интересно, вот ссылка:  http://www.planetaexcel.ru/bitrix/components/bitrix/forum.interface/show_file.php?fid=77561&action=d...
в этом решении, как я поняла, подсчитывается количество заказов одного товара с другим. а хотелось бы количество совместных заказов. такое возможно?
Изменено: Юлия А - 23.01.2023 14:39:49
 
 
 
Страницы: 1
Наверх