Разделение долга по месяцам

Страница 1 из 1 1
Модератор форума: китин, _Boroda_, DrMini

Разделение долга по месяцам (Формулы/Formulas)

Garik007

Дата: Четверг, 09.02.2017, 18:23 | Сообщение № 1

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 102

Репутация: 0 ±

Замечаний: 0% ±

Excel 2013, 2016

Доброе время суток. В выделенных желтым цветом ячейках имеется формула. Считает она вроде нормально, но если сумма в равных частях не делится на количество месяцев, то последний платеж обрабатывается некорректно, и сумма по месяцам получается больше или меньше суммы долга.
1. Как можно поправить размер последнего платежа.
2. Можно ли, если установить последний или первый платеж вручную, чтобы оставшиеся платежи распределялись равными частями.
Буду благодарен за помощь.

К сообщению приложен файл: 7246268.xlsx (14.4 Kb)

Ответить

gling

Дата: Четверг, 09.02.2017, 18:52 | Сообщение № 2

Группа: Друзья

Ранг: Участник клуба

Сообщений: 2641

Репутация: 738 ±

Замечаний: 0% ±

2010

Здравствуйте. Может так

Код

=ЕСЛИ(СЧЁТ($D$5:D5)<$D$2;ЕСЛИ(СЧЁТ($D$5:D5)=$D$2-1;$D$1-СУММ($D$5:D5);ОКРУГЛ($D$1/$D$2;2));"")

ЯД-41001506838083

Ответить

Nic70y

Дата: Четверг, 09.02.2017, 20:24 | Сообщение № 3

Группа: Друзья

Ранг: Экселист

Сообщений: 9130

Репутация: 2415 ±

Замечаний: 0% ±

Excel 2010

вариант

Код

=ЕСЛИ(СТРОКА(D1)>D$2-1;D$1-СУММ(D$5:D5);ОКРУГЛ(D$1/D$2;2))

ЮMoney 41001841029809

Ответить

Perfect2You

Дата: Четверг, 09.02.2017, 21:02 | Сообщение № 4

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 237

Репутация: 59 ±

Замечаний: 0% ±

Excel 2010

В ячейку D7:

Код

=ВЫБОР(ЗНАК(СЧЁТЗ($D$6:$D6)-$D$2+1)+2;ОКРВВЕРХ(($D$1-СУММ($D$6:$D6))/($D$2-СЧЁТЗ($D$6:$D6));0,01);$D$1-СУММ($D$6:$D6);"")

и протянуть.

После на числовые значения вручную любую сумму меняете - остаток распределяется равномерно.

Сообщение отредактировал Perfect2You - Четверг, 09.02.2017, 21:07

Ответить

Garik007

Дата: Пятница, 10.02.2017, 08:51 | Сообщение № 5

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 102

Репутация: 0 ±

Замечаний: 0% ±

Excel 2013, 2016

Всем огромное спасибо. Больше подошла формула Perfect2You, единственное если поставить последний платеж, например 5 тыс., то предыдущие платежи не пересчитываются в сторону уменьшения.

Ответить

Perfect2You

Дата: Пятница, 10.02.2017, 12:02 | Сообщение № 6

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 237

Репутация: 59 ±

Замечаний: 0% ±

Excel 2010

А вот это допусловие - удар ниже пояса стандартной работе формул EXCEL. Циклическая ссылка - вещь полезная только при очень умелом и тонком обращении.

Обойти эту сложность можно, подключив на помощь VBA. Но... еще: нарушается однозначность алгоритма. Как программе догадаться: где нужно менять, а где Вы зафиксировали?

Ответить

Garik007

Дата: Пятница, 10.02.2017, 12:32 | Сообщение № 7

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 102

Репутация: 0 ±

Замечаний: 0% ±

Excel 2013, 2016

Цитата Perfect2You, 10.02.2017 в 12:02, в сообщении № 6 ()

Как программе догадаться: где нужно менять, а где Вы зафиксировали?

А если сделать дополнительную ячейку с суммой последнего платежа и отталкиваться уже от этого?

Ответить

Nic70y

Дата: Пятница, 10.02.2017, 13:11 | Сообщение № 8

Группа: Друзья

Ранг: Экселист

Сообщений: 9130

Репутация: 2415 ±

Замечаний: 0% ±

Excel 2010

Цитата Garik007, 10.02.2017 в 12:32, в сообщении № 7 ()

дополнительную ячейку

можно с ней, можно без, но лучше с ней - зачем формулу портить

Код

=ЕСЛИ(СТРОКА()>D$2+5;"";ЕСЛИ(СТРОКА()=6;D$1-СУММ(D$7:D$29);ЕСЛИОШИБКА(ОКРУГЛ((D$1-ИНДЕКС(D7:D$29;ПОИСКПОЗ(9E+307;D7:D$29)))/(D$2-1);2);ЕСЛИ(E$1;E$1;ОКРУГЛ(D$1/D$2;2)))))

К сообщению приложен файл: 7496761.xlsx (12.8 Kb)

ЮMoney 41001841029809

Ответить

AlexM

Дата: Пятница, 10.02.2017, 15:42 | Сообщение № 9

Группа: Друзья

Ранг: Участник клуба

Сообщений: 4517

Репутация: 1130 ±

Замечаний: 0% ±

Excel 2003

В ячейку D6 и протяжной вниз. Все формулы одинаковые

Код

=ЕСЛИ(СТРОКА(A1)>D$2;"";МИН(D7;D$1/СТРОКА(A1)))

Использовано решение из МШ
Вариант решения на втором листе. Все формулы протягиваемые и отличаются от формул автора вопроса.

К сообщению приложен файл: 7246268.xls (48.5 Kb)

Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.

Ответить

Garik007

Дата: Пятница, 10.02.2017, 16:01 | Сообщение № 10

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 102

Репутация: 0 ±

Замечаний: 0% ±

Excel 2013, 2016

Nic70y, AlexM, спасибо, то что надо.

Ответить

Perfect2You

Дата: Пятница, 10.02.2017, 16:52 | Сообщение № 11

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 237

Репутация: 59 ±

Замечаний: 0% ±

Excel 2010

Что ж, признаю: невнимательно задание прочитал.

Реализация такая:
Последний платеж вводить в ячейку D31

В ячейке D6:

Код

=ОКРВВЕРХ(($D$1-$D$31)/($D$2-($D$31<>0));0,01)

В D7 и протянуть:

Код

=ЕСЛИ($D$31;ВЫБОР(ЗНАК(СЧЁТЗ($D$6:$D6)-$D$2+1)+2;ЕСЛИ(СЧЁТЗ($D$6:$D6)-$D$2+2;ОКРВВЕРХ(($D$1-$D$31-СУММ($D$6:$D6))/($D$2-СЧЁТЗ($D$6:$D6)-1);0,01);$D$1-$D$31-СУММ($D$6:$D6));$D$31;"");ВЫБОР(ЗНАК(СЧЁТЗ($D$6:$D6)-$D$2+1)+2;ОКРВВЕРХ(($D$1-СУММ($D$6:$D6))/($D$2-СЧЁТЗ($D$6:$D6));0,01);$D$1-СУММ($D$6:$D6);""))

И первой, и последней, и средними можно подыгрывать.

Файл прикладываю.

К сообщению приложен файл: 7246268_1.xlsx (15.2 Kb)

Сообщение отредактировал Perfect2You - Пятница, 10.02.2017, 16:58

Ответить

AlexM

Дата: Пятница, 10.02.2017, 17:29 | Сообщение № 12

Группа: Друзья

Ранг: Участник клуба

Сообщений: 4517

Репутация: 1130 ±

Замечаний: 0% ±

Excel 2003

Еще вариант.
Мой вариант из сообщения №9 работает не совсем корректно. Чтобы это было видно, формат ячеек сделал 4 знака после запятой.
Сравните результат на втором и третьем листе.
Формула в D7

Код

=ЕСЛИ(СТРОКА(A1)>D$2;"";ОКРВВЕРХ(D$1/D$2;1%)+(СТРОКА(A1)=D$2)*(D$1-ОКРВВЕРХ(D$1/D$2;1%)*D$2))