Страницы: 1
RSS
Создание таблицы с распределением на основе двух таблиц с данными
 
Добрый день
просмотрел форум на подобные задачу, но точного ответа ненашел.

Есть 2 таблицы со списком продуктов и их ценой и списком менеджером и их оборотом (например). Нужно получить новую таблицу в которой будут распределены менеджеры по продуктам соглано суммы оборота менеджера (грубо говоря из сводной таблицы по менеджерам нужно получить исходную таблицу). Для наглядности добавил файл и его скриншот
P.S 100% совпадение суммы по менеджеру не обязательно, но хотелось бы чтобы итоговая сумма продуктов была максимально близка к сумме оборота по менеджеру

Моя логика решения задачи:
1. узнать количество строк (countrows)
2. разделить сумму менеджера на количество строк, чтобы узнать ~ сколько продуктов должно быть под ним (нужно будет округление применить)
3. посчитать долю для каждого менеджера от общей суммы оборота
4. нужен цикл который буде проверять сумму продуктов

заранее спасибо
Изменено: sanych09 - 18.01.2020 16:27:11
Опыт и практика - великое дело! Век живи, Век учись!
 
Моя логика решения задачи:
В дополнительном столбике поделить Total Amount каждого менеджера на количество строк этого менеджера в итоговой таблице
т.е. примерно так
=ВПР(I2;D$2:E$5;2;0)/СЧЁТЕСЛИ(I:I;I2)
Изменено: Valera2 - 18.01.2020 19:10:33
 
Valera2, спасибо за ответ. формула возвращает цифры...а мне нужно что-бы проставила manager1, manager2 и т.д
Опыт и практика - великое дело! Век живи, Век учись!
 
Доброе время суток.
Версия "жадным" алгоритмом". Обратите внимание на сортировку.
 
Андрей VG, Спасибо большое! интересный вариант, и примечательно что на формулах легко все понять! а возможный ли такой вариант на PQ или DAX? или там будет намного сложнее и другой алгоритм нужно применять?
Опыт и практика - великое дело! Век живи, Век учись!
 
Цитата
sanych09 написал:
интересный вариант,
Учитывайте такой момент, что алгоритм не точный. Вполне может существовать точное решение, но жадный его не найдёт.
Цитата
sanych09 написал:
а возможный ли такой вариант на PQ или DAX?
На Power Query - можно, хотя и не столь просто, но принцип тот же. А вот на DAX - с трудом верю. Спросите Максима Зеленского - он кудесник DAX, ему виднее...
 
Андрей VG, возможно точный вариант и есть... Если есть другой алгоритм. В слюбом случае, результат есть. Спасибо
Опыт и практика - великое дело! Век живи, Век учись!
 
Цитата
sanych09 написал:
Если есть другой алгоритм
Можно, в принципе, поиском решения озадачиться. Можете к Михаилу MCH - он тока в таких задачах. Может и согласиться чем-нибудь помочь.
 
Андрей VG, спасибо. Для меня эта задача чисто спортивный интерес... Пришла в голову случайно. Вот решил спросить на форуме умных людей.
Опыт и практика - великое дело! Век живи, Век учись!
 
исключительно на спортивном интересе
см.вложение, в таблице в шапке запись mN.V, значит менеджер номер N, вариант загрузки V,
итого видим
у 1-го 40
у 2-го 5
у 3-го 26
у 4-го 19 разных вариантов до полной загрузки доступными позициями
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
Ігор Гончаренко, я так понимаю, решение с нахождением всех вохможных комбинаций? только непонятно как реализовали? потом, из всех комбинаций нунжно выбрать максимально близкие к 100%
Опыт и практика - великое дело! Век живи, Век учись!
 
Максим Зеленский, возможно ли такую задачу решить в Power Query? пытался сделать по алгоритму Андрей VG, но как обработь строки не знаю (видимо нужно как-то с List.Generate работать).
заранее спасибо
Опыт и практика - великое дело! Век живи, Век учись!
 
Хоть я и не Максим, но можно реализовать в PQ greedy Algorithm примерно так:
Код
let
//функция поиска имени поля записи с максимальным значением, это чтобы полнимать из какого поля записи вычитать очередной элемент списка
    MaxFieldRec = (r as record)=>  
        Record.FieldNames( r ){ List.PositionOf( Record.ToList( r ), List.Max( Record.ToList( r ) ) ) },

//функция реализации жадного алгоритма по максималке и прилепления столбца с результатом к исходной таблице
    Greedy = (sub as table, col as text, rec as record)=> 
        let
            itemCount = Table.RowCount( sub ),
            sumList = List.Buffer( Table.ToColumns( Table.SelectColumns( sub, {col}) ){0} ),
            calc = List.Generate(
                () => [ id = 0, 
                        NeededFieldRec = MaxFieldRec( rec ), 
                        runRec = Record.TransformFields( rec, {NeededFieldRec, (x) => x - sumList{id} })],
                each [id] < itemCount,
                each [  id = [id] + 1,
                        NeededFieldRec = MaxFieldRec( [runRec] ),
                        runRec = Record.TransformFields( [runRec], {NeededFieldRec, (x) => x - sumList{id} })],
                each [NeededFieldRec]
            ),
            result = Table.FromColumns( Table.ToColumns( sub ) & {calc}, Table.ColumnNames( sub ) & {"spreading"} )
        in
            result,

    goods = Excel.CurrentWorkbook(){[Name="Goods"]}[Content][[Product name],[amount]],
    managers = Table.Buffer( Table.PromoteHeaders( Table.Transpose( Excel.CurrentWorkbook(){[Name="Managers"]}[Content] ) ) ),
    SortGoods = Table.Sort( goods, {{"amount", Order.Descending}} ),
    Out = Greedy( SortGoods, "amount", managers{0} )
in
    Out

С результатом Андрея все совпадает.
Изменено: PooHkrd - 20.01.2020 13:21:48 (чуть подправил код)
Вот горшок пустой, он предмет простой...
 
Цитата
sanych09 написал:
выбрать максимально близкие к 100%
не максимально близкие а совпадающие с требуемой суммой на 100%
(подбил снизу суммы)
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
PooHkrd, мощно!!! спасибо! мне до такого харкода далеко)))))  посмотрю на просторах что это за алгоримт такой
Опыт и практика - великое дело! Век живи, Век учись!
 
Ігор Гончаренко, понял, спасибо! а как на эти комбинация вышли?
Опыт и практика - великое дело! Век живи, Век учись!
 
sanych09, алгоритм тот же самый. Я просто логику формул Андрея реализовал в коде на языке М. Не более того.
Вот горшок пустой, он предмет простой...
Страницы: 1
Наверх