Как я могу распределить положительное число среди отрицательных чисел в Excel?
Итак, я пытаюсь автоматизировать проблему, скажем, если у вас до 5 чисел и есть хотя бы одно отрицательное число, как можно минимизировать это отрицательное число, взяв из положительных чисел?
Возьмем, к примеру, массив (-27,5, -22, 19,5). Посмотрев на него, это довольно просто: мы можем распределить 19,5 как 19,5 x 27,5 / (27,5 + 22) = 10,8 и 19,5 x 22 / (27,5 + 22) = 8,7, поэтому получаем (-27,5 + 10,8, -22 + 8,7, 19,5 - 19,5) = (-16,7, -13,3, 0).
Возьмем другой пример (14, -6, 18), который должен дать -2,625, 6, -3,375, а затем дать (11,375, 0, 14,625).
Это довольно простая математика, так как я могу определить, какие ячейки мне нужно распределить, но мне очень сложно программировать в Excel.
Допустим, если строка C14:E19, ячейка C14 будет следующей:
=ЕСЛИ(C14<0, МИН(СУММЕСЛИ($C14:$E14,">0"), ABS(C14)) * ABS(C14) / СУММЕСЛИ($C14:$E14,"<0"), ЕСЛИ( C14>0, -C14 * СУММЕСЛИ($C14:$E14,"<0") / СУММЕСЛИ($C14:$E14,">0"), 0))
Во-первых, это дает мне неверные знаки в моих тестовых примерах, но это мелочь. Другая проблема заключается в том, что, возьмем мой первый случай: для 19,5 он возвращает 49,5, а не -19,5, чтобы приравнять его к нулю, а во второй задаче -6 возвращает еще -6.
В общем, я чувствую, что почти у цели, но, потратив на это приличное количество времени, я не могу продвинуться дальше. Я надеюсь, что кто-нибудь со свежей головой сможет остановить мою глупость....
Вы можете сделать его короче: =LET(α, A1, δ, $A1:$C1, φ, SUMIF(δ,">0"), ε, SUMIF(δ,"<0"), IF(α<0, MIN(φ, ABS(α)) * α / ε, IF(α>0,α * ε / MAX(φ,ABS(ε)), 0)))
Ответы 1
Попытавшись немного обобщить, я получил:
=LET(range,A2:A4,
sumPos,SUMIF(range,">0"),
sumNeg,SUMIF(range,"<0"),
toAdd,MIN(sumPos,-sumNeg),
IF(range<0,range+toAdd*range/sumNeg,range-range*toAdd/sumPos))
Попробуйте:
=IF(A1<0, MIN(SUMIF($A1:$C1,">0"), ABS(A1)) * A1 / SUMIF($A1:$C1,"<0"), IF(A1>0,A1 * SUMIF($A1:$C1,"<0") / MAX(SUMIF($A1:$C1,">0"),ABS(SUMIF($A1:$C1,"<0"))), 0))