Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Как ограничить диапазон суммирования, ограничение диапазона по сумме значений в него входящих
 
#1
16.09.2014 12:11:53
Доброго всем времени суток.
Существует проблема, которую никак не могу решить.
Описание самой проблемы (см. прикрепленный файл):
Есть товар определенного артикула, кол-во которого в закупке квотировано. Продается он по четырем разным ценам. Еженедельно полученные заказы (они выполняются с существенной отсрочкой во времени) вносятся в обобщенную таблицу. Однако не все они будут выполнены в силу наличия квот.
Можно ли ограничить диапазон суммирования по условию достижения выделенных квот? Это нужно что бы вести статистику реального спроса и, одновременно, считать реально получаемый доход (не учитывающий превышение полученных заказов над квотами). Последнее значение, которое дает превышение квоты уже можно не учитывать в расчете (пример сделан руками для артикулов 0001 и 0002).

Желательно формульное решение. Но если возможно только на VBA, то буду очень благодарен за пояснение в тексте макроса (т.к. с пониманием макросов есть проблемы).
Excel 2013, однако хорошо если решение будет работать и в 2003.

Заранее признателен всем за помощь.
 
 
 
#2
16.09.2014 12:24:45
Где суммировать? В выделенных ячейках? Какой результат должен быть?
 
 
 
#3
16.09.2014 12:33:52
В столбце P, Q, R, S считаются все принятые заказы на протяжении установленного срока (сезона). В столбцах K, L, M, N, для двух первых артикулов, я руками изменил диапазон суммирования (как мне хотелось бы автоматически) - так должны считаться заказы до достижения установленного размера квот.
 
 
 
#4
16.09.2014 12:49:20
Все равно не понял...
Почему в Р4 и далее не применить =СУММ(U4;Y4)?
 
 
 
#5
16.09.2014 13:02:26
Лучше бы Вы нарисовали, что у Вас есть и что должно получится (соседняя таблица). Тогда может и понятней бы стало, а пока "тёмный лес".
"..Сладку ягоду рвали вместе, горьку ягоду я одна."
 
 
 
#6
16.09.2014 13:03:35
Сезон в течение которого собираются заказы идет от 25 до 40 недель (и разбивка может быть и не по-недельно, а чаще), потому делать простое суммирование ячеек, муторно, и применяется формула массива. В столбцах P, Q, R, S суммируются все заказы собранные за сезон. Однако, когда общая сумма заказов достигает предельной (для первого артикула это наступает в ячейке Y4), дальше планируемый доход по этой позиции расти не будет, т.к. есть ограничение квотами (т.е. продавать будет нечего). Потому в столбцах K, L, M, N диапазон суммирования и должен быть автоматически уменьшен.
 
 
 
#7
16.09.2014 13:22:55
Должно получиться так:
- в ячейке P4 суммируются все заказы которые по-недельно вносятся в правой части таблицы (U4, Y4, AC4 и т.д. - таблица не продлена для компактности). Аналогично для ячеек Q, R, S. Сумма ячеек P, Q, R, S (она считается в ячейке T) дает сумму ВСЕХ принятых заказов по артикулу 0001. Эта сумма может превышать сумму выделенной квоты (ячейка B4).
- в ячейке К4 суммируются принятые заказы в диапазоне до Y4 (аналогично для L, M и N), после которой начнется превышение выделенных квот. Поэтому в ячейке О4 (сумма ВСЕХ заказов принятых по квоте) значение не должно превышать квоты. Вот поэтому мне и нужно автоматически ограничить диапазон суммирования в ячейках K, L, M, N.
 
 
 
#8
16.09.2014 13:49:50
Постараюсь проговорить еще раз. На примере одного артикула.
Я заказываю товар у производителя. Мне выделяется квота в 5 единиц. Этот товар я продаю по четырем разным ценам. Заказы я собираю в течение определенного времени, разбитого на 40 периодов, которые суммирую. Сумма товаров, заказанных по разным ценам в течение всех периодов дает мне сумму всех заказов на артикул. Я собрал за весь сезон 15 заказов. 10 из которых я продать не смогу в силу ограничения квотами. Следовательно, планируемый доход я должен посчитать только в диапазоне где сумма заказов не превышает квоты. Отсюда задача:
- в ячейке T4 (сумма ячеек P, Q, R, S) я считаю все заказы принятые за сезон (это чисто для статистики и анализа).
- в ячейке O4 (сумма ячеек K, L, M, N) я считаю все заказы которые попадают в размер квоты, т.е. по которым я получу реальный доход. Для этого мне нужно принудительно (формулой или другим способом) обрезать диапазон суммирования в ячейках K, L, M, N. В примере я сделал это принудительно руками.

Вопрос. Можно ли принудительно ограничивать диапазон суммирования (по достижению условия: кол-во принятых заказов по квоте в O4, не превышает размера самой квоты в B4)? Или проще: превышен размер квоты - диапазон суммирования закончился (в ячейке P4 диапазон U4:AG4, а в K4 - U4:Y4).

Прошу простить, если ранее дал пояснения не совсем корректно. Еще раз все большое спасибо за участие.
 
 
 
Страницы: 1
Наверх