Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Выборка минимальных значений в подгруппах с учётом ограничения
 
#1
28.01.2020 19:04:02
Добрый день.

Весь день ломаю голову над задачкой. Уже пробовал и SQL, и excel, но никак не получается автоматизировать задачку.

Предистория.
Есть заказ, в котором есть N товарных позиций. Для каждой позиции может быть какое-то хаотичное число ПОТЕНЦИАЛЬНЫХ поставщиков, которые дают свою цену за позицию, причем цены могут совпадать. Также поставщик может не дать цены вообще.

Суть.
Хочу научиться определять для каждой товарной позиции поставщика таким образом, чтобы сумма всего заказа была минимальной. Но при этом есть ограничение на число поставщиков в заказе. Для начала взял его равным 2 поставщика на заказ.

Буду рад, если подскажете и покажете как это реализовать.

PS:
Также думал над небольшой хитростью, которую тоже не получилось реализовать.
Сортируем так, чтобы в самом верху были позиции с самой дорогой минимальной ценой. -> Дальше выбираем самого выгодного поставщика. -> И рядом пишем счётчик кол-ва поставщиков в заказе. -> Выбираем для следующего по дороговизне товара поставщика -> Следим за кол-вом поставщиков и выбираем следующие позиции -> как только кол-во поставщиков становится максимальным -> фиксируем список поставщиков и выбираем для остальных товаров только среди них.
 
 
 
#2
29.01.2020 09:07:08
Доброе время суток.
Цитата
OPTIKAL написал:
Есть заказ, в котором есть N товарных позиций
А где данные в файле по заказу? Есть только таблица данных поставщиков :(
 
 
 
#3
29.01.2020 10:04:33
Поясню. Есть заказ с 37 позициями.

На каждую позицию поставщики дают свои цены, но у кого-то может не быть товара, а соответственно и цены.
Отсюда и получается, что на каждую позицию в заказе ±9 цен от поставщиков.
Задача в том и состоит, чтобы выбрать оптимально двух поставщиков так, чтобы цена за весь заказ была минимальной.

Итого:
Матрица 37 на 9, где есть 37 товаров и 9 поставщиков. И нам нужно выбрать для товаров поставщиков так, чтобы суммарная цена была минимальной, а количество поставщиков не превышало двух. Для любой позиции в заказе мы выбираем только 1 поставщика, то есть не должно получится, что, например, позиция1 поедет три раза от трёх поставщиков. Она должна поехать только от 1 поставщика, выбранного оптимальным образом.
Изменено: OPTIKAL - 29.01.2020 10:07:26
 
 
 
#4
29.01.2020 10:40:43
Цитата
OPTIKAL написал:
Поясню. Есть заказ с 37 позициями.
Где есть?
 
 
 
#5
29.01.2020 10:56:53
Цитата
Светлый написал:
Где есть?
Левая таблица - это данные по позициям заказа, для каждой из позиций отобраны поставщики такой позиции и их цены.

Если создать умную таблицу с именем Позиции, то вариант для не более двух поставщиков на Power Query
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Позиции"]}[Content],
    positions = List.Buffer(List.Distinct(Source[Номер позиции])),
    positionCount = List.Count(positions),
    salerPositions = Table.Group(Source, {"Имя поставщика"}, {"positions", each [Номер позиции]}),
    leftSalers = Table.RenameColumns(salerPositions, {{"Имя поставщика", "leftSaler"}, {"positions", "leftPositions"}}),
    rightSalers = Table.RenameColumns(salerPositions, {{"Имя поставщика", "rightSaler"}, {"positions", "rightPositions"}}),
    joined = Table.Join(leftSalers, {}, rightSalers, {}),
    canbe = Table.SelectRows(joined, each [leftSaler] > [rightSaler] and List.Count(List.Distinct([leftPositions] & [rightPositions])) = positionCount),
    definePairPrice = Table.AddColumn(canbe, "temp", (rec) =>
    let
        salers = Table.FromColumns({{rec[leftSaler], rec[rightSaler]}}, {"saler"}),
        salerPositions = Table.Join(Source, {"Имя поставщика"}, salers, {"saler"}),
        best = Table.Group(salerPositions, {"Номер позиции"}, {"temp", each Table.Min(_, "Цена")})
    in
        Table.ExpandRecordColumn(best, "temp", {"Цена", "Имя поставщика"})
    ),
    min = List.Min(Table.ToRecords(definePairPrice), null, (a, b) => List.Sum(a[temp][Цена]) - List.Sum(b[temp][Цена]))[temp]
in
    min

Решение лобовое - полным перебором пар поставщиков и выбором лучшей по минимуму суммы цен позиций (естественно, если у пары есть общие позиции, то для позиции выбирается тот поставщик, который имеет минимальную цену).
Изменено: Андрей VG - 29.01.2020 11:18:55 (Что-то я перезамудрил. И тёзка с Алексеем не подсказывают :( )
 
 
 
Страницы: 1
Наверх