Добрый день просмотрел форум на подобные задачу, но точного ответа ненашел.
Есть 2 таблицы со списком продуктов и их ценой и списком менеджером и их оборотом (например). Нужно получить новую таблицу в которой будут распределены менеджеры по продуктам соглано суммы оборота менеджера (грубо говоря из сводной таблицы по менеджерам нужно получить исходную таблицу). Для наглядности добавил файл и его скриншот P.S 100% совпадение суммы по менеджеру не обязательно, но хотелось бы чтобы итоговая сумма продуктов была максимально близка к сумме оборота по менеджеру
Моя логика решения задачи: 1. узнать количество строк (countrows) 2. разделить сумму менеджера на количество строк, чтобы узнать ~ сколько продуктов должно быть под ним (нужно будет округление применить) 3. посчитать долю для каждого менеджера от общей суммы оборота 4. нужен цикл который буде проверять сумму продуктов
Моя логика решения задачи: В дополнительном столбике поделить Total Amount каждого менеджера на количество строк этого менеджера в итоговой таблице т.е. примерно так =ВПР(I2;D$2:E$5;2;0)/СЧЁТЕСЛИ(I:I;I2)
Андрей VG, Спасибо большое! интересный вариант, и примечательно что на формулах легко все понять! а возможный ли такой вариант на PQ или DAX? или там будет намного сложнее и другой алгоритм нужно применять?
Опыт и практика - великое дело! Век живи, Век учись!
Учитывайте такой момент, что алгоритм не точный. Вполне может существовать точное решение, но жадный его не найдёт.
Цитата
sanych09 написал: а возможный ли такой вариант на PQ или DAX?
На Power Query - можно, хотя и не столь просто, но принцип тот же. А вот на DAX - с трудом верю. Спросите Максима Зеленского - он кудесник DAX, ему виднее...
исключительно на спортивном интересе см.вложение, в таблице в шапке запись 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