Формула кумулятивной разницы

У меня есть таблица, которая вычисляет оптимальное распределение затрат по конкретному ресурсу по часам, а затем распределяет эти данные на основе результатов. Распределение творит чудеса, но моя проблема заключается в том, что есть несколько строк с одинаковым часом. Мне не удалось создать формулу в Excel, которая могла бы учитывать предыдущие строки, а затем вычитать использованную сумму из доступного ресурса. Если весь ресурс был израсходован, по умолчанию следует использовать следующий доступный ресурс. Вот текущая формула:

=IF(Q2=0,"NONE",INDEX('RESOURCE ALLOCATION'!$F$3:$I$3,1,MATCH(LARGE(INDIRECT("'RESOURCE ALLOCATION'!F"&B2+3&" :I"&B2+3),1),INDIRECT("'RESOURCE ALLOCATION'!F"&B2+3&" :I"&B2+3),0)))

Q2 = первая ячейка с числом в ней

Resource Allocation — рабочий лист в этой книге, где диапазон $F$3:$I$3 — это доступные ресурсы по именам.

Формула MATCH просто смотрит на час текущей строки, указывает его на соответствующую строку в таблице, а затем возвращает связанный ресурс по имени.

Каждая последующая строка увеличивается на единицу следующим образом:

=IF(Q3=0,"NONE",INDEX('RESOURCE ALLOCATION'!$F$3:$I$3,1,MATCH(LARGE(INDIRECT("'RESOURCE ALLOCATION'!F"&B3+3&" :I"&B3+3),1),INDIRECT("'RESOURCE ALLOCATION'!F"&B3+3&" :I"&B3+3),0)))

Таблица, которую я использую, выглядит так:

Hour Resource 1 Resource 2  Resource 3  Resource 4    TOTAL

  21         50          0           0           10    60

На этот час требуется 60 единиц. 50 выделено против Resource 1 и 10 выделено против Resource 4. По текущей формуле он просто распределяет все против Resource 1.


function excel excel-formula cumulative-sum
person sheltered harbor    schedule 22.10.2015    source источник
comment
Мне непонятно (а) что вы пытаетесь сделать, и (2) где вы терпите неудачу. Не могли бы вы добавить некоторые образцы данных или изображения соответствующих частей ваших электронных таблиц, чтобы я мог ускорить процесс?   -  person xidgel    schedule 23.10.2015
comment
На 21-й час доступно 50 единиц на Ресурсе 1 и 283 доступных единицы на Ресурсе 4. В этот час происходят две сделки: сделка А на 20 единиц и сделка Б на 40 единиц. Результатом, который я хотел бы видеть, будут первые 20 единиц сделки A, выделенные для ресурса 1, следующие 30 единиц сделки B, выделенные для ресурса 1, и последние 10 единиц сделки B, выделенные для ресурса 4.   -  person sheltered harbor    schedule 23.10.2015
comment
Я не могу понять, как добавить параметр, который проверяет повторяющиеся часы в текущей формуле. Я могу использовать COUNTIF для поиска дубликатов, но тогда я не уверен, как заставить последующие формулы возвращать другое значение в зависимости от того, сколько часов уже было использовано. Я стараюсь избегать использования vba, если это возможно, но если это проще, мне, возможно, придется создать макрос.   -  person sheltered harbor    schedule 23.10.2015

Ответы (1)


arrow_upward
1
arrow_downward

Это мое решение:

Решение

Я добавил несколько дополнительных столбцов для удобочитаемости.

В столбце C у вас есть простые суммы, проверяющие сумму, потраченную за этот конкретный час для данной ячейки и всех предыдущих.

Формула в C2:

=SUMIF($A$2:A2,A2,$B$2:B2)

Столбец D — это просто диапазон, который войдет в функцию ДВССЫЛ.

Формула в D2:

="I"&MATCH(A2,H:H,0)&":L"&MATCH(A2,H:H,0)

Столбец E показывает ресурс, который использовался в качестве 1-го источника в этот конкретный час, и его появление.

Формула в E2:

=5-IFERROR((1/(A2=A1))*IFERROR(MATCH(C1,INDIRECT(D2),0)-1,MATCH(C1,INDIRECT(D2),-1)),4)

Столбец F показывает ресурс, который использовался в качестве последнего источника в этот конкретный час, и его появление. Возвращает ошибку, если не хватает ресурсов (не знаю, может ли быть так).

Формула в F2:

=5-MATCH(C2,INDIRECT(D2),-1)

Резюме:

Идея довольно проста, но если требует, чтобы вы изменили свой подход. Накопление должно происходить в пределах ресурсов, а не в итоговой таблице отчета. R4 должен быть суммой фактического ресурса 4 и всех остальных ресурсов, т. е. R4 = ресурс 4 + ресурс 3 + ресурс 2 + ресурс 1, R3 = ресурс 3 + ресурс 2 + ресурс 1, R2 = ресурс 2 + ресурс 1, R1 = ресурс 1. И имея это, просто используйте сопоставление с аргументом -1.

Поскольку у вас нет всех ресурсов, вы можете добавить какое-то исключение в диапазон, который сообщается как 1-й и последний использованные ресурсы.

person Community    schedule 23.10.2015
comment
Возвращает ошибку, если не хватает ресурсов (не знаю, может ли быть так). Ресурсов никогда не будет не хватать, подумайте об этом как о банковском счете, где на учетной записи 1 доступно 50 долларов в час, на учетной записи 2 доступно 100 долларов в час и так далее. Учетная запись 1 всегда имеет приоритет, поэтому, если вы совершили покупку на 70 долларов, то 50 долларов будут выделены для учетной записи 1, а 20 долларов будут выделены для учетной записи 2. В следующий час, если вы совершили покупку на 100 долларов, 50 долларов будут выделены для учетной записи 1 и 50 долларов для учетной записи 1. счет 2 и так далее. - person sheltered harbor; 23.10.2015
comment
Ошибка возникает, если сумма всех покупок за данный час больше суммы всех доступных ресурсов за этот час. В вашем примере ошибки не будет. Посмотрите на ячейку F6 - ошибка произошла, потому что вы хотели потратить 30 единиц после того, как уже потратили 120, когда ваш общий лимит всех ресурсов вместе был 135. Я думаю, что мое решение делает то, что вы хотели. - person ; 23.10.2015