Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Уменьшение распределения на указанный шаг до указанной суммы - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Уменьшение распределения на указанный шаг до указанной суммы (Формулы/Formulas)
Уменьшение распределения на указанный шаг до указанной суммы
Nicotine Дата: Понедельник, 10.12.2018, 18:51 | Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 5
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Добрый вечер.
Прошу помочь в одном вопросе.

Нужен способ обработки заказов на магазины, а именно - уменьшение заказа до остатка склада.
во вложении пример.

левое поле - заказы магазинов
правое поле - остатки склада

нужна формула/макрос которая будет делать следующее:
- суммировать заказ по артикулу
- в случае, если сумма заказа больше остатка склада, начинаем уменьшать левое поле. столбик "изначальный заказ".
- уменьшать на определенный шаг (столбик кратность. для каждого артикула своя цифра) каждую строку левого поля, до тех пор, пока сумма заказа будет не больше суммы остатка.
- начинать уменьшение с самого большого значения "заказ" по каждому артикулу
- в случае, если уменьшение всех заказов по артикулу на одну кратность не уменьшает сумму заказа до остатка склада, запустить уменьшение каждого заказа снова (каждый раз начиная с самого большого заказа), пока сумма заказа не станет меньше или равна сумме остатка склада.

столбик финальный заказ - то, что нужно на выходе и автоматически.

Коллеги, очень нужна помощь, тратим уйму времени на простейшие и однотипные операции.
Спасибо!
К сообщению приложен файл: 0066342.xlsx (11.5 Kb)
 
Ответить
СообщениеДобрый вечер.
Прошу помочь в одном вопросе.

Нужен способ обработки заказов на магазины, а именно - уменьшение заказа до остатка склада.
во вложении пример.

левое поле - заказы магазинов
правое поле - остатки склада

нужна формула/макрос которая будет делать следующее:
- суммировать заказ по артикулу
- в случае, если сумма заказа больше остатка склада, начинаем уменьшать левое поле. столбик "изначальный заказ".
- уменьшать на определенный шаг (столбик кратность. для каждого артикула своя цифра) каждую строку левого поля, до тех пор, пока сумма заказа будет не больше суммы остатка.
- начинать уменьшение с самого большого значения "заказ" по каждому артикулу
- в случае, если уменьшение всех заказов по артикулу на одну кратность не уменьшает сумму заказа до остатка склада, запустить уменьшение каждого заказа снова (каждый раз начиная с самого большого заказа), пока сумма заказа не станет меньше или равна сумме остатка склада.

столбик финальный заказ - то, что нужно на выходе и автоматически.

Коллеги, очень нужна помощь, тратим уйму времени на простейшие и однотипные операции.
Спасибо!
Автор - Nicotine
Дата добавления - 10.12.2018 в 18:51
Nicotine Дата: Вторник, 11.12.2018, 10:00 | Сообщение № 2
Группа: Пользователи
Ранг: Прохожий
Сообщений: 5
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
up
 
Ответить
Сообщениеup
Автор - Nicotine
Дата добавления - 11.12.2018 в 10:00
Nicotine Дата: Среда, 12.12.2018, 10:30 | Сообщение № 3
Группа: Пользователи
Ранг: Прохожий
Сообщений: 5
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
up
 
Ответить
Сообщениеup
Автор - Nicotine
Дата добавления - 12.12.2018 в 10:30
Светлый Дата: Среда, 12.12.2018, 11:13 | Сообщение № 4
Группа: Друзья
Ранг: Старожил
Сообщений: 1829
Репутация: 510 ±
Замечаний: 0% ±

Excel 2013, 2016
Добрый день!
Удалось придумать только такую массивную формулу:
Код
=E2-F2*ИЛИ(НАИБОЛЬШИЙ((C$2:C$16=C2)*(E$2:E$16-СТРОКА($2:$16)%%);СТРОКА(ДВССЫЛ("1:"&(СУММЕСЛИ(C$2:C$16;C2;E$2:E$16)-ВПР(C2;I$2:L$16;3))/ВПР(C2;I$2:L$16;4))))=E2-СТРОКА()%%)
Погоняйте с разными исходными данными. Может дать неправильный ответ.
*Так лучше:
Код
=E2-F2*ИЛИ(ЕСЛИОШИБКА(НАИБОЛЬШИЙ((C$2:C$16=C2)*(E$2:E$16-СТРОКА($2:$16)%%);СТРОКА(ДВССЫЛ("1:"&(СУММЕСЛИ(C$2:C$16;C2;E$2:E$16)-ВПР(C2;I$2:L$16;3))/ВПР(C2;I$2:L$16;4))));)=E2-СТРОКА()%%)
**Уменьшает только на одну порцию, даже если большое количество заказано.

Программировать проще, чем писать стихи.

Сообщение отредактировал Светлый - Среда, 12.12.2018, 12:14
 
Ответить
СообщениеДобрый день!
Удалось придумать только такую массивную формулу:
Код
=E2-F2*ИЛИ(НАИБОЛЬШИЙ((C$2:C$16=C2)*(E$2:E$16-СТРОКА($2:$16)%%);СТРОКА(ДВССЫЛ("1:"&(СУММЕСЛИ(C$2:C$16;C2;E$2:E$16)-ВПР(C2;I$2:L$16;3))/ВПР(C2;I$2:L$16;4))))=E2-СТРОКА()%%)
Погоняйте с разными исходными данными. Может дать неправильный ответ.
*Так лучше:
Код
=E2-F2*ИЛИ(ЕСЛИОШИБКА(НАИБОЛЬШИЙ((C$2:C$16=C2)*(E$2:E$16-СТРОКА($2:$16)%%);СТРОКА(ДВССЫЛ("1:"&(СУММЕСЛИ(C$2:C$16;C2;E$2:E$16)-ВПР(C2;I$2:L$16;3))/ВПР(C2;I$2:L$16;4))));)=E2-СТРОКА()%%)
**Уменьшает только на одну порцию, даже если большое количество заказано.
Автор - Светлый
Дата добавления - 12.12.2018 в 11:13
Nicotine Дата: Среда, 12.12.2018, 11:57 | Сообщение № 5
Группа: Пользователи
Ранг: Прохожий
Сообщений: 5
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Спасибо!
к вечеру проверю и отпишусь.
 
Ответить
СообщениеСпасибо!
к вечеру проверю и отпишусь.
Автор - Nicotine
Дата добавления - 12.12.2018 в 11:57
Светлый Дата: Среда, 12.12.2018, 12:43 | Сообщение № 6
Группа: Друзья
Ранг: Старожил
Сообщений: 1829
Репутация: 510 ±
Замечаний: 0% ±

Excel 2013, 2016
До четырёх порций из каждого может вычитать. Формула массива:
Код
=E2+F2*СУММ(-(ЕСЛИОШИБКА(НАИБОЛЬШИЙ((C$2:C$16=C2)*(E$2:E$16-{0;1;2;3}*F$2:F$16-СТРОКА($2:$16)%%);СТРОКА(ДВССЫЛ("1:"&(СУММЕСЛИ(C$2:C$16;C2;E$2:E$16)-ВПР(C2;I$2:L$16;3))/ВПР(C2;I$2:L$16;4))));)=E2-{0;1;2;3}*F2-СТРОКА()%%))
*Ну и расширенная универсальная. До 9 порций с заказа:
Код
=E2+F2*СУММ(-(ЕСЛИОШИБКА(НАИБОЛЬШИЙ((C$2:C$16=C2)*(E$2:E$16-(СТОЛБЕЦ(A:I)-1)*F$2:F$16-СТРОКА($2:$16)%%);СТРОКА(ДВССЫЛ("1:"&(СУММЕСЛИ(C$2:C$16;C2;E$2:E$16)-ВПР(C2;I$2:L$16;3))/ВПР(C2;I$2:L$16;4))));)=E2-(СТОЛБЕЦ(A:I)-1)*F2-СТРОКА()%%))

Программировать проще, чем писать стихи.

Сообщение отредактировал Светлый - Среда, 12.12.2018, 13:29
 
Ответить
СообщениеДо четырёх порций из каждого может вычитать. Формула массива:
Код
=E2+F2*СУММ(-(ЕСЛИОШИБКА(НАИБОЛЬШИЙ((C$2:C$16=C2)*(E$2:E$16-{0;1;2;3}*F$2:F$16-СТРОКА($2:$16)%%);СТРОКА(ДВССЫЛ("1:"&(СУММЕСЛИ(C$2:C$16;C2;E$2:E$16)-ВПР(C2;I$2:L$16;3))/ВПР(C2;I$2:L$16;4))));)=E2-{0;1;2;3}*F2-СТРОКА()%%))
*Ну и расширенная универсальная. До 9 порций с заказа:
Код
=E2+F2*СУММ(-(ЕСЛИОШИБКА(НАИБОЛЬШИЙ((C$2:C$16=C2)*(E$2:E$16-(СТОЛБЕЦ(A:I)-1)*F$2:F$16-СТРОКА($2:$16)%%);СТРОКА(ДВССЫЛ("1:"&(СУММЕСЛИ(C$2:C$16;C2;E$2:E$16)-ВПР(C2;I$2:L$16;3))/ВПР(C2;I$2:L$16;4))));)=E2-(СТОЛБЕЦ(A:I)-1)*F2-СТРОКА()%%))
Автор - Светлый
Дата добавления - 12.12.2018 в 12:43
Nicotine Дата: Пятница, 14.12.2018, 19:28 | Сообщение № 7
Группа: Пользователи
Ранг: Прохожий
Сообщений: 5
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
добрый вечер.
в последней Вашей формуле поиск наибольшего значения идет по колонке С, а должен по колонке Е.
поправьте, если Вас не затруднит.

Код
=E2+F2*СУММ(-(ЕСЛИОШИБКА(НАИБОЛЬШИЙ((C$2:C$16=C2)*(E$2:E$16-(СТОЛБЕЦ(A:I)-1)*F$2:F$16-СТРОКА($2:$16)%%);СТРОКА(ДВССЫЛ("1:"&(СУММЕСЛИ(C$2:C$16;C2;E$2:E$16)-ВПР(C2;I$2:L$16;3))/ВПР(C2;I$2:L$16;4))));)=E2-(СТОЛБЕЦ(A:I)-1)*F2-СТРОКА()%%))
 
Ответить
Сообщениедобрый вечер.
в последней Вашей формуле поиск наибольшего значения идет по колонке С, а должен по колонке Е.
поправьте, если Вас не затруднит.

Код
=E2+F2*СУММ(-(ЕСЛИОШИБКА(НАИБОЛЬШИЙ((C$2:C$16=C2)*(E$2:E$16-(СТОЛБЕЦ(A:I)-1)*F$2:F$16-СТРОКА($2:$16)%%);СТРОКА(ДВССЫЛ("1:"&(СУММЕСЛИ(C$2:C$16;C2;E$2:E$16)-ВПР(C2;I$2:L$16;3))/ВПР(C2;I$2:L$16;4))));)=E2-(СТОЛБЕЦ(A:I)-1)*F2-СТРОКА()%%))
Автор - Nicotine
Дата добавления - 14.12.2018 в 19:28
Светлый Дата: Пятница, 14.12.2018, 20:38 | Сообщение № 8
Группа: Друзья
Ранг: Старожил
Сообщений: 1829
Репутация: 510 ±
Замечаний: 0% ±

Excel 2013, 2016
Добрый вечер!
Оформите формулу в своём сообщении тегом. Выделить и нажать fx.
Цитата Nicotine, 14.12.2018 в 19:28, в сообщении № 7 ()
поиск наибольшего значения идет по колонке С, а должен по колонке Е
Где Вы это увидели? Наибольшее берётся из выражения:
Код
(C$2:C$16=C2)*(E$2:E$16-(СТОЛБЕЦ(A:I)-1)*F$2:F$16-СТРОКА($2:$16)%%)
Столбец C здесь участвует только для группировки по Глобал (одинаковые).
Формулу немного сократил. Это, если кратности строго одинаковые в группе Глобал и в правой таблице:
Код
=E2+F2*СУММ(-(ЕСЛИОШИБКА(НАИБОЛЬШИЙ((C$2:C$16=C2)*(E$2:E$16-(СТОЛБЕЦ(A:I)-1)*F$2:F$16-СТРОКА($2:$16)%%);СТРОКА(ДВССЫЛ("1:"&(СУММЕСЛИ(C$2:C$16;C2;E$2:E$16)-ВПР(C2;I$2:L$16;3))/F2)));)=E2-СТОЛБЕЦ(A:I)*F2+F2-СТРОКА()%%))

Программировать проще, чем писать стихи.
 
Ответить
СообщениеДобрый вечер!
Оформите формулу в своём сообщении тегом. Выделить и нажать fx.
Цитата Nicotine, 14.12.2018 в 19:28, в сообщении № 7 ()
поиск наибольшего значения идет по колонке С, а должен по колонке Е
Где Вы это увидели? Наибольшее берётся из выражения:
Код
(C$2:C$16=C2)*(E$2:E$16-(СТОЛБЕЦ(A:I)-1)*F$2:F$16-СТРОКА($2:$16)%%)
Столбец C здесь участвует только для группировки по Глобал (одинаковые).
Формулу немного сократил. Это, если кратности строго одинаковые в группе Глобал и в правой таблице:
Код
=E2+F2*СУММ(-(ЕСЛИОШИБКА(НАИБОЛЬШИЙ((C$2:C$16=C2)*(E$2:E$16-(СТОЛБЕЦ(A:I)-1)*F$2:F$16-СТРОКА($2:$16)%%);СТРОКА(ДВССЫЛ("1:"&(СУММЕСЛИ(C$2:C$16;C2;E$2:E$16)-ВПР(C2;I$2:L$16;3))/F2)));)=E2-СТОЛБЕЦ(A:I)*F2+F2-СТРОКА()%%))
Автор - Светлый
Дата добавления - 14.12.2018 в 20:38
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Уменьшение распределения на указанный шаг до указанной суммы (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2024 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!