Вычитание из набора ячеек строки по принципу от максимального к минимальному

Автор gregory9791, 12.11.2015, 12:27

« назад - далее »

gregory9791

  Доброго дня, комрады!

  Имеется: столбец (А) значений (целочисленных) и рядом с ним  двумерная таблица (B1:U147)со значениями лишь в некоторых из ячеек. Но в каждой строке есть хотя бы одно значение (целочисленное).
   Задача: обработать каждую строку двумерной таблицы (B1:U147) следующим образом: Определить непустые ячейки и их количество. Из ячейки с большим значением вычесть значение в столбце (А). И далее если остаток от вычитания больше нуля, то вычесть его(остаток) из ячейки со значением вторым после максимального. И так далее. Пока остаток от вычитания не будет меньше нуля или в строке будут отсутствовать другие значения.
   Таблица с результатом может находиться как в том же листе, так и на новом листе.

vikttur

Цитировать=ЕСЛИ(СЧЁТ(B1:U1)>1;НАИБОЛЬШИЙ(B1:U1;2))-МАКС(;МАКС(B1:U1)-A1)
Но это только: от второго наибольшего отнять разность.
Что значит "И так далее" Вы, надеюсь, объясните.
Результат - столбец V? Или результат  - не один столбец?

gregory9791

Столбец V попал в таблицу случайно. Прошу извинить.

Прикладываю таблицу с листом результатов первых пяти строк, а также 15-й строки (т.к. она более показательна).
Поясню алгоритм на примере 15-й строки.
В данной строке имеются ячейки со следующими значениями: 140, 3600, 140, 10800, 700. Максимальное значение 10800, поэтому вычитаем из него значение из столбца А 10800-12600=-1800. Т.к. результат меньше нуля, то ячейку со значением 10800 обнуляем, а остаток (по модулю) 1800 вычитаем из следующей по максимуму ячейки: 3600-1800=1800. Меняем значение ячейки со значением 3600 на 1800. Т.к. результат больше ноля, то обработка строки завершена.



gregory9791

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

vikttur

Создайте такую же таблице на втором листе. В В1:
=ЕСЛИ(исх!B1;ЕСЛИ(исх!B1-исх!$A1-СУММЕСЛИ(исх!$B1:$U1;">"&исх!B1)>0;исх!B1-исх!$A1-СУММЕСЛИ(исх!$B1:$U1;">"&исх!B1);исх!B1);)
Чтобы не мешали нули в ячейках.
-Вар1:
Файл-Параметры-Дополнительно, в параметрах листа снять галку "показывать нули в ячейках..."
Вар2 - вместо нуля в формуле записать "" (пустая строка). Хуже, т.к. пустая строка - текст, а наличие текста среди чисел может мешать некоторым вычислениям.

gregory9791

vikttur, большое спасибо! Вроде все верно считается. Нули нисколько не мешают.
Теперь успеваю вовремя подготовить данные.
:)

gregory9791

vikttur, к сожалению не все строки отрабатываются верно. Если в строке одно лишь значение, оно почему-то не уменьшается.  :(


gregory9791

Вобщем, не работает в случае, когда значение в столбце А точно равно сумме значений в строке (B:U)

vikttur

Ошибка в логике. Проверьте:
=ЕСЛИ(исх!B1;ЕСЛИ(исх!$A1-исх!B1-СУММЕСЛИ(исх!$B1:$U1;">"&исх!B1)>0;;МИН(исх!B1;исх!B1-исх!$A1+СУММЕСЛИ(исх!$B1:$U1;">"&исх!B1)));)

zs

Комбинация из двух строк, с использованием ранга:)
=ЕСЛИ(СЧЁТЕСЛИ(C12:F12;D12)>1;РАНГ(D12;$C$12:$F$12)-СЧЁТЕСЛИ(C12:F12;D12);РАНГ(D12;$C$12:$F$12))

=ЕСЛИ(ЕСЛИ(И(СУММЕСЛИ($C$13:$F$13;"<"&РАНГ(D12;$C$12:$F$12);$C$12:$F$12)+D12>$A$12;D12<$A$12-СУММЕСЛИ($C$13:$F$13;"<"&РАНГ(D12;$C$12:$F$12);$C$12:$F$12)-1);СУММЕСЛИ($C$13:$F$13;"<"&РАНГ(D12;$C$12:$F$12)-1;$C$12:$F$12)+D12;ЕСЛИ(И(СУММЕСЛИ($C$13:$F$13;"<"&0;C12:F12);ЕСЛИ(СЧЁТЕСЛИ(C12:F12;D12)>1;РАНГ(D12;$C$12:$F$12)-СЧЁТЕСЛИ(C12:F12;D12);РАНГ(D12;$C$12:$F$12))>0);СУММЕСЛИ($C$13:$F$13;"<"&РАНГ(D12;$C$12:$F$12);$C$12:$F$12)+D12-СУММЕСЛИ(B13:E13;"<"&0;B12:E12);СУММЕСЛИ($C$13:$F$13;"<"&РАНГ(D12;$C$12:$F$12);$C$12:$F$12)+D12))<$A$12;D12;"")