Таблица поиска Excel и значения совпадений на основе различий строк

Question

У меня есть ежедневная панель инструментов, которую я создаю, где мне нужно умножить ежедневное потребление на определенное значение, которое изменяется, когда ежедневное потребление превышает определенное число уровней. Проблема, с которой я сталкиваюсь в настоящее время, заключается в том, что я не могу умножить правильный множитель уровня, если количество уровней достигнуто в течение определенного дня. Это означает, что множитель более высокого уровня распределяется по всем ежедневным потреблениям за этот день, а не по разнице, которая оказалась на более высоком уровне в течение этого дня. Любая помощь будет отличной! Спасибо.

Предоставлен рабочий пример:

1) У меня есть таблица, в которой есть множители (инвестиции), а также начальный и конечный уровни.

2) Уровень впуска попал 11/12. Как вы увидите, если вы сложите числа, 11/11 было 464, а 11/12 было 506, что означает, что 11 из 42 потребления на 11/12 следует умножить на 150, а не на 200 долларов, что не происходит с моим текущая формула

3) Умножение сумм потребления на значения таблицы, основанные на наличии «2-го уровня» в таблице выше =IF(E6="",$V$3*$D6,$V$4*D6)

Answer 1

Вот пересмотренный ответ. В таблице ниже указаны правильно рассчитанные инвестиции на 12 ноября.

Формула в F2:

=IF(SUM(D$2:E2)>475,IF(E1="",50*(SUM(D$2:D2)-475)+150*D2,200*D2),150*D2)

Большая часть этого проста. Если текущая сумма столбца D (SUM (D $ 2:Dnn) составляет менее 475, то инвестиции составляют 150 * Dnn. Если текущая сумма больше 475, а предыдущая строка столбца E не пуста, то инвестиции составляют 200 * Dnn.

В случае , когда текущая сумма больше , чем 475 , и предыдущая строка Колонка E является пустой (12 ноября) , то часть потребления умножается на 200 , а остаток умножается на 150.

Часть потребления, которая должна быть умножена на 200, является превышением текущей суммы над 475 - это просто текущая сумма за вычетом 475. Таким образом, эта часть инвестиций составляет 200*(SUM(C$2:Cnn)-475) . Остаток суммы в столбце D равен Dnn минус это избыточное количество или Dnn-(SUM(C$2:Cnn)-475) , и эта часть должна быть умножена на 150. Представляя текущую сумму как "Сумма", общая сумма инвестиций составляет:

200*(Sum - 475) + 150*(Dnn-(Sum-475))

Это выражение можно использовать в формуле, но его можно упростить. Упрощение дает:

200*Sum - 200*475 + 150*Dnn - 150*Sum + 150*475
50*Sum - 50*475 + 150*Dnn
50*(Sum-475) + 150*Dnn

и это «значение, если true» во внутреннем выражении IF():

50*(SUM(D$2:D2)-475)+150*D2

Я надеюсь, что это поможет, и, пожалуйста, извините за фальстарт в моем комментарии выше.