Здравствуйте, подскажите, пожалуйста, может кто сталкивался с ситуацией когда есть количество товара на складе и его надо оптимально переместить по магазинам. В файле образец где первые 2 листа данные а третий то что долг получится в итоге. Спасибо за помощь
Дополню замечания предыдущего оратора в части того, что на листах 1 и 2 пишется "тариф 1", а на листе 3 "тариф1" (без пробела), что создаёт преодолимые, но совершенно не нужные трудности.
В связи с этими трудностями сложно строить ВПРы(точнее ИНДЕКС+ПОИСКПОЗ).
Я так понимаю, что всем должны дать столько они хотят (если хватает на складе), либо дать соответствующую долю от того, что есть:
Магазин1 хочет - 80; Магазин2 хочет - 20. Есть - 50.
Всего хотят: 100, есть всего 50, значит - не хватает. Магазин1 хочет 80/100 = 0,8 от общего объёма; Магазин2 хочет 20/100 = 0,2 от общего объёма;
Прошу прощения за опечатки в названиях магазин 1 и магазин1 в итоге это одно и тоже. А логика такая что магазин1 хочет разное количество в зависимости от тариф 1 2 3 4 а далее все как описали
=ОКРУГЛ(СУММПРОИЗВ((B2='необходимое количество'!B$2:B$59)*(D2='необходимое количество'!C$1:H$1)*'необходимое количество'!C$2:H$59)*МИН(1;ВПР(D2;'Наличие на складе'!B$2:C$6;2;)/СУММПРОИЗВ((D2='необходимое количество'!C$1:H$1)*'необходимое количество'!C$2:H$59));)
Только надо исправить в заголовке "тариф 6 пробел", ну и указанные выше недочёты в данных. Округление может в сумме дать ошибку между есть и надо в 1-2 единицы товара.
Задача точно решаема формулой. Сейчас попробую. Там останется некий нераспределённый остаток (из-за округления) и его придётся потом руками распределить наобум. Исправьте, если возможно, номера магазинов в первом сообщении, чтобы тот, кто тоже решит попробовать не мучился.
Это называется распределение товара пропорционально заявкам. Вот возможный вариант, но без непонятной для меня по смыслу "итоговой" таблицы. Зелёные столбцы это "хотелки" магазинов, жёлтые - результат распределения товаров. Сверху - остаток по товару. Точно эту задачу не решить, потому, как при округлении может и "плюс" остаться и "минус". Если на остатке получился "минус" (он будет небольшой), то с какго-либо магазина надо будет ручками сминусовать до нуля по итогу.
Исправил файл + на листе "необходимое количество" мой вариант решения но как из него сделать таблицу вида как на листе "Итоговая таблица" не могу придумать
В приложении мой вариант: =ОТБР(МИН(ИНДЕКС('необходимое количество'!$C$60:$H$60;1;ПОИСКПОЗ('Итоговая таблица'!$D2;'необходимое количество'!$C$1:$H$1;0));ИНДЕКС('Наличие на складе'!$C$2:$C$6;ПОИСКПОЗ('Итоговая таблица'!$D2;'Наличие на складе'!$B$2:$B$6;0)))*(ИНДЕКС('необходимое количество'!$C$2:$H$59;ПОИСКПОЗ('Итоговая таблица'!$B2;'необходимое количество'!$B$2:$B$59;0);ПОИСКПОЗ('Итоговая таблица'!$D2;'необходимое количество'!$C$1:$H$1;0))/ИНДЕКС('необходимое количество'!$C$60:$H$60;1;ПОИСКПОЗ('Итоговая таблица'!$D2;'необходимое количество'!$C$1:$H$1;0)))) Вариант Мартын мне нравится больше. 1. Я брал за основу исходную таблицу не стал заморачиваться с удалением пробелов. Поэтому не считается "тариф6 ", нужно просто подправить заголовок на листе "необходимое количество". Оставил как пример возможной проблемы+пути её решения. 2. На листе "Итоговая таблица" указаны не все пары магазин/тариф поэтому там обще количество требуемых товаров не будет стыковаться с требуемым количеством товаров на листе "необходимое количество". Именно поэтому решение Мартын мне нравится больше - там в одном месте и сколько хотят и сколько можно выдать. 3. Пока писал сообщение понял, что можно сделать небольшой справочник, в котором можно из списка выбрать нужный магазин/тариф и узнать сколько туда нужно направить.
PMO87 написал: смысл в том что такой шаблон прогружается логистам и далее идёт в работу, поэтому форму нельзя менять
Тогда поздравляю - у Вас новая задача - сформировать для логистов из листа "необходимое количество" столбцы A:E листа "Итоговая таблица", чтобы туда попали все магазины, у которых есть какие-то требования по поставке товаров, а не как сейчас - часть попала, часть не попала.
По какому принципу должен распределяться товар,если его недостаточно на складе? Обычно за основу берутся планы магазинов и так называемые мин-максы. У Вас же этого в файле нет. Т.к. занимался подобными вещами, то могу сказать, что при наличии этих двух данных, ваша задача решается в два этапа формулами: первый этап-распределение товара согласно доли плана каждого магазина в общем плане всех магазинов, второй этап - дораспределение оставшегося остатка склада после проведения первого этапа.
Или берутся планы магазинов и их потребность в товаре, затем считается агрегирующий показатель необходимости и на основе его в два этапа распределяется товар. Например: есть два магазина - у магазина 1 план составляет 60 рублей, а потребность 3 штуки, у магазина 2 план составляет 40 рублей, потребность 5 штук. Соответственно, у магазина 1 доля плана 60%, а доля потребности 37,5%; у магазина 2 доля плана составляет 40%, а доля потребности - 62,5%. Итого агрегирующий показатель потребности у магазина 1 - 60+37,5=97,5, а у магазина 2 – 40+62,5=102,5. Таким образом, у магазина 1 доля распределения равна 97,5/(97,5+102,5)=48,75%, а у магазина 2 – 102,5//(97,5+102,5)=51,25%. Вот теперь всё сделано "по справедливости". Остается распределить остаток склада в два этапа: первый этап - распределение по этим долям, второй - распределение остатка после первого этапа, например, в порядке убывания плана. Всё это легко сделать формулами. И не забудьте - если Вы распределяете штуки, а они находятся в коробках, то нужно учесть кратность упаковки,т.е. использовать функцию ОКРУГЛТ или ОКРВВЕРХ.МАТ, ОКРВНИЗ.МАТ