Поиск  Пользователи  Правила 
Закрыть
Логин:
Пароль:
Забыли свой пароль?
Регистрация
Войти
 
Страницы: Пред. 1 2 3 След.
RSS
Проценты по вкладу в зависимости от нескольких различных вариантов условия задачи, формула с кучей ЕСЛИ
 
Цитата
skais675 написал:
=ПРОСМОТР(G3*H3*I3*3+G3*H3*I4*4;СТРОКА(L3:L12);L3:L12)
К сожалению этот вариант не работает. Показывает только при соблюдении первых двух условий. Если же в задаче будут любые из 3-10 условий, то покажет ошибку #Н/Д.
Цитата
БМВ написал:
Прикрепленные файлы
Copy of пример1148.xlsx  (13.39 КБ)
Ваша формула =СУММ($K$3;$K$4;$K$5;$K$6;$K$7;$K$8;$K$9;$K$10;$K$11;$K$12) вроде работает.
Цитата
БМВ написал:
так это то понятно, но у вас по этой формуле високосным будет только единственный год (1), все остальные окажутся простыми.  ИЛИ(2016;2020) даст всегда ИСТИНУ ( да даже если будет И тоже)  Истина эквивалентна 1 и сравнение года будет с ней, что будет означать всегда ЛОЖЬ и 365 в результате.Вариант предложен в файле что #13, собственно как и прочие оптимизации.
Вы были правы, я нажал кнопку Вычислить формулу, и посмотрел пошагово как идет расчет, после чего обнаружил, что функция ИЛИ делает не так как мне было нужно, в результате чего на 2016 или 2020 год все равно выходит 365 дней. Спасибо большое за вашу формулу, она здорово поможет в расчетах, вы сделали очень важное изменение, т.к. эта формула встречается в очень многих моих расчетах.


Посмотрите пожалуйста новый вид моей таблицы с вариантами условий, я ее сделал более простой для понимания.
Как думаете, функций СУММ является самой простой и удобной для реализации моей просьбы? Возможно ли еще как то упростить формулу, чтобы в ней учитывались все эти условия?
 
RamRiz, Вы читаете невнимательно
Цитата
Решение: Это пример - все условия пропишите по аналогии. (работает если одновременно выполняется строго одно условие)
- Я привел Вам пример для первых двух условий, допишите сами по аналогии остальные (тут же все очевидно).
Чем эта формула не устроила?
Код
=ЕСЛИ(ОСТАТ(ГОД($C$1);4);366;365)

А вместо
Цитата
Ваша формула=СУММ($K$3;$K$4;$K$5;$K$6;$K$7;$K$8;$K$9;$K$10;$K$11;$K$12) вроде работает.
уж проще
Код
 =СУММ(K3:K12)
Изменено: skais675 - 22 Сен 2019 19:46:44
 
Цитата
skais675 написал:
RamRiz, Вы читаете невнимательно ЦитатаРешение: Это пример - все условия пропишите по аналогии. (работает если одновременно выполняется строго одно условие)- Я привел Вам пример для первых двух условий, допишите сами по аналогии остальные (тут же все очевидно).
Извиняюсь. Тогда буду делать и смотреть)
Цитата
skais675 написал:
Чем эта формула не устроила? =ЕСЛИ(ОСТАТ(ГОД($C$1);4);366;365)
Все устраивает, как то не обратил внимание на нее.
А в чем разница между этой =ЕСЛИ(ОСТАТ(ГОД($C$1);4);366;365) и этой (365+(ОСТАТ(ГОД(C1);4)=0)) ?
 
Решил сравнить сам. Для этого взял формулу из условия 1. Год в ячейке С1 указан 2017.
1) =$C$10*$C$3/(365+(ОСТАТ(ГОД(C1);4)=0))*$C$4   = 8446,03
2) =$C$10*$C$3/ЕСЛИ(ОСТАТ(ГОД($C$1);4);366;365)*$C$4    = 8422,95

Вижу что ответы разняца. Проверил каждый шаг с помощью кнопки "Вычислить формулу", после чего обнаружил что во второй формуле эта часть ЕСЛИ(ОСТАТ(ГОД($C$1);4);366;365) вычисляет в 2017 году 366 дней, что не верно.
 
Ram Riz, Вам нужно сперва определить, что вам нужно. Нужна вам вспомогательная таблица или нет. нужно собирать все в одну формулу,  или удобнее держать раздельно , чтоб потом редактировать было проще.
По вопросам из тем форума, личку не читаю.
 
RamRiz поменяйте там местами 366 и 365 (пардон).
Так должно быть
Код
=ЕСЛИ(ОСТАТ(ГОД($C$1);4);365;366)
Изменено: skais675 - 22 Сен 2019 20:19:17
 
Цитата
БМВ написал:
Ram Riz , Вам нужно сперва определить, что вам нужно. Нужна вам вспомогательная таблица или нет. нужно собирать все в одну формулу,  или удобнее держать раздельно , чтоб потом редактировать было проще.
Я хочу объединить в одну, но при этом иметь вспомогательную таблицу, чтобы в случае обновления тарифов или еще каких данных, я мог легко изменить какие то значения без риска испортить всю формулу.
Цитата
skais675 написал:
RamRiz проверку формулы нужно производить на дате а не на числе 2019 = 11.07.1905
Так и было. Формула ссылалась на ячейку С1, в которой указана дата, в которой год был 2017, но формула все равно высчитала 366 дней.
Цитата
skais675 написал:
- Я привел Вам пример для первых двух условий, допишите сами по аналогии остальные (тут же все очевидно).Чем эта формула не устроила?
Получилось. =ПРОСМОТР(G3*H3*I3*3+G4*H4*I4*4+G5*H5*I5*5+G6*H6*I6*6+G7*H7*I7*7+G8*H8*I8*J8*8+G9*H9*I9*J9*9+G10*H10*I10*10+G11*H11*I11*J11*11+G12*H12*I12*J12*12;СТРОКА(L3:L12);L3:L12).
Но формула получилось длинной, возможно ее как то сократить?
Например так
=ПРОСМОТР(G3:I3*3+G4:I4*4+G5:I5*5+G6:I6*6+G7:I7*7+G8:J8*8+G9:J9*9+G10:I10*10+G11:J11*11+G12:J12*12;СТРОКА(L3:L12);L3:L12)
Изменено: RamRiz - 22 Сен 2019 20:24:51
 
Цитата
RamRiz написал:
Я хочу объединить в одну, но при этом иметь вспомогательную таблицу
Можно ли перефразировать, что вы хотите убрать столбец К?
По вопросам из тем форума, личку не читаю.
 
Цитата
skais675 написал:
Так должно быть
=ЕСЛИ(ОСТАТ(ГОД($C$1);4);365;366)
Заработало, спасибо)
 
Цитата
БМВ написал:
Можно ли перефразировать, что вы хотите убрать столбец К?
Да. Именно это я и хотел) Делал ее, чтобы просто было проще понять, к чему я иду.
Можно ли вашу формулу переделать под таблицу G3:J12?
 
RamRiz В Вашем случае иногда полезно оставить такую раздробленность, тогда действительно можно легко разобраться. Если же нужно все свести в одну формулу без промежуточных - это тоже возможно но будет выглядеть громоздко и сложно поддаваться тестированию и выявлению ошибок. В принципе Вы уже достигли результата - сейчас Вы на пути оптимизации. Столбец K очень удобен я б не избавлялся от него, иначе используем длинную формулу ПРОСМОТР.
Изменено: skais675 - 22 Сен 2019 20:43:21
 
Я б так массивно записал
=INDEX(L3:L12;MATCH(1;G3:G12*H3:H12*I3:I12*IF(J3:J12<>"";J3:J12;1);))

=ИНДЕКС(L3:L12;ПОИСКПОЗ(1;G3:G12*H3:H12*I3:I12*ЕСЛИ(J3:J12<>"";J3:J12;1);))
Изменено: БМВ - 22 Сен 2019 21:11:47
По вопросам из тем форума, личку не читаю.
 
Цитата
skais675 написал:
RamRiz В Вашем случае иногда полезно оставить такую раздробленность, тогда действительно можно легко разобраться. Если же нужно все свести в одну формулу без промежуточных - это тоже возможно но будет выглядеть громоздко и сложно поддаваться тестированию и выявлению ошибок. В принципе Вы уже достигли результата - сейчас Вы на пути оптимизации. Столбец K очень удобен я б не избавлялся от него, иначе используем длинную формулу ПРОСМОТР.
Раздробленность я конечно оставлю, понял что это очень полезно в случае необходимости вносить какие то изменения.
Просто не вижу смысла сохранять столбец К, ведь это просто склеенная формула из других частей, и она не используется в итоговой формуле. Ваша формула ПРОСМОТР очень помогает в этом плане.
Цитата
БМВ написал:
Я б так массивно записал=INDEX(L3:L12;MATCH(1;G3:G12*H3:H12*I3:I12*IF(J3:J12<>"";J3:J12;1))
Ваша формула дает ошибку #ИМЯ?
 
Цитата
RamRiz написал:
#ИМЯ
не удивляет, вы на русский перевели? Выше добавил формулу в варианте локализованном, не забывайте что она массивная и вводится тремя кнопками.
По вопросам из тем форума, личку не читаю.
 
Цитата
БМВ написал:
=ИНДЕКС(L3:L12;ПОИСКПОЗ(1;G3:G12*H3:H12*I3:I12*ЕСЛИ(J3:J12<>"";J3:J12;1))
Теперь получилось.
Вот самая крутая формула, и маленькая и по всем условиям работает без нариканий.
В общем то и другие формулы работают правильно. Те хотя бы мне более понятны, и я смогу подстроить те формулы под другие таблицы, коих у меня много.
Эта формула для меня слишком сложная, надеюсь когда нибудь я смогу делать такие)

Спасибо Вам skais675 и БМВ Большое, что уделили время моей проблеме.
Изменено: RamRiz - 22 Сен 2019 21:25:09
 
Вариант:
Код
=СУММПРОИЗВ(G3:G12*H3:H12*I3:I12*J3:J12*L3:L12)

в четвертую ячейку по умолчанию вместо пусто поставьте истину.
Изменено: skais675 - 22 Сен 2019 21:43:30
 
Цитата
RamRiz написал:
Эта формула для меня слишком сложная
она проще чем вам кажется.
G3:G12*H3:H12*I3:I12*ЕСЛИ(J3:J12<>"";J3:J12;1) - создаст массив результатов из 1 и 0, так как ИСТИНА-1 и ЛОЖЬ -0 , то 1 получится только в случае когда все 1 в строке, но четвертый параметр нужен не всегда, а только когда не пустая ячейка, по этому ЕСЛИ(J3:J12<>"";J3:J12;1) если она пустая, то используем 1  Массивная формула позволяет построчно перебирать значения, ну и ищем первую 1 среди полученных значений. Далее по номеру найденного просто берется значения из соседнего столбца. Только конкретно такой вариант работает на варианте, когда для анализа берутся значения из одной стоки, а не вразброс, как было в первом примере.
По вопросам из тем форума, личку не читаю.
 
Цитата
skais675 написал:
Вариант:
=СУММПРОИЗВ(G3:G12*H3:H12*I3:I12*J3:J12*L3:L12)
Нет предела совершенству)
Она и короткая, и простая, и ее легче адаптировать под другие таблицы.
Наверное все таки остановлюсь на этой)
Цитата
skais675 написал:
в четвертую ячейку по умолчанию вместо пусто поставьте истину.
Хорошая идея
 
Цитата
БМВ написал:
она проще чем вам кажется.G3:G12*H3:H12*I3:I12*ЕСЛИ(J3:J12<>"";J3:J12;1) - создаст массив результатов из 1 и 0, так как ИСТИНА-1 и ЛОЖЬ -0 , то 1 получится только в случае когда все 1 в строке, но четвертый параметр нужен не всегда, а только когда не пустая ячейка, по этому ЕСЛИ(J3:J12<>"";J3:J12;1) если она пустая, то используем 1  Массивная формула позволяет построчно перебирать значения, ну и ищем первую 1 среди полученных значений. Далее по номеру найденного просто берется значения из соседнего столбца. Только конкретно такой вариант работает на варианте, когда для анализа берутся значения из одной стоки, а не вразброс, как было в первом примере.
Что то стало понятно, но все равно не просто дается. А если вместо пустых ячеек будет написано ИСТИНА, как в формуле у skais675, можно ли ее упростить?
 
RamRiz
Цитата
Что то стало понятно, но все равно не просто дается. А если вместо пустых ячеек будет написано ИСТИНА, как в формуле у skais675, можно ли ее упростить?
а смысл? хотите использовать две функции вместо одной?
Изменено: skais675 - 22 Сен 2019 22:29:24
 
Цитата
RamRiz написал:
Нет предела совершенству)
Я б так не сказал, как минимум, из-за опять Вашей ошибки, может быть искаженный результат. Возможно это трудно повторить в реалии, но С2 может быть равно С9, тогда при сравнении будет две ИСТИНЫ, и сумма даст задвоение.

Цитата
RamRiz написал:
А если вместо пустых ячеек будет написано ИСТИНА
то
=ИНДЕКС(L3:L12;ПОИСКПОЗ(1;G3:G12*H3:H12*I3:I12*J3:J12;))

Так как бродит пример с ошибкой определения високосного года, то я не хотел бы его использовать.
Изменено: БМВ - 22 Сен 2019 22:24:04
По вопросам из тем форума, личку не читаю.
 
Цитата
БМВ написал:
Я б так не сказал, как минимум, из-за опять Вашей ошибки, может быть искаженный результат. Возможно это трудно повторить в реалии, но С2 может быть равно С9, тогда при сравнении будет две ИСТИНЫ, и сумма даст задвоение.
Ошибка будет в любом случае.
Ваше замечание верно, на это нужно обратить внимание, а еще лучше добавить формулу сообщающую о такой ситуации.
Изменено: skais675 - 22 Сен 2019 22:29:10
 
Цитата
skais675 написал:
Ошибка будет в любом случае.
ну вот опять же вернемся к утренним обидам. Посмотрите внимательно на формулы в L7 И L8
=SUM($C$10*$C$9/IF(YEAR($C$1)=OR(2016;2020);366;365)*$C$4;
   $D$10*($C$2-$C$9)/IF(YEAR($C$1)=OR(2016;2020);366;365)*$C$4)

=$C$10*$C$2/IF(YEAR($C$1)=OR(2016;2020);366;365)*$C$4

Если С9=С2, и если забыть про високосность, то в первой формуле второе слагаемое из-за $D$10*($C$2-$C$9) будет равно 0 а первое будет равно результату второй формулы. Правда это дистижимо только при сроке вклада на четное число дней и при неудачном расположении звезд.
Изменено: БМВ - 22 Сен 2019 22:32:59
По вопросам из тем форума, личку не читаю.
 
БМВ Речь идет о том, что если получится более одного решения - то понять какое верно никто не сможет, хоть Вы возьмете оба хоть по отдельности. Потому в этой ситуации ошибка гарантирована. Если где-то есть ошибки их конечно нужно исправлять - чтобы исключить возможность таких ситуаций.
Изменено: skais675 - 22 Сен 2019 22:37:44
 
skais675, прочтите то что я написал выше!!! Получится два равных результата. Оба верных, и не важно из какой ячейке их брать,  но не их сумма. Забудьте про Excel хоть на  минутку. Решите задачу в общем случае при C2=C9.
По вопросам из тем форума, личку не читаю.
 
БМВ Оба верных не может быть в принципе - это уже ошибка (тем более в такой теме: Проценты по вкладу)!
Когда Вы хотите что-то доказать - пример в студию (я не телепат).
Вы хотите весь файл по молекулам разобрать я вижу, если по факту: то ТС давно получил, что хотел.
Изменено: skais675 - 22 Сен 2019 22:52:36
 
Цитата
skais675 написал:
БМВ Речь идет о том, что если получится более одного решения - то понять какое верно никто не сможет, хоть Вы возьмете оба хоть по отдельности. Потому В этой ситуации ошибка гарантирована.
Такое не должно случиться. Я проверял каждый из 10 вариантов событий, не может быть так, чтобы оба варианта из этого списка были полностью ИСТИНой.
Цитата
БМВ написал:
ну вот опять же вернемся к утренним обидам. Посмотрите внимательно на формулы в L7 И L8=SUM($C$10*$C$9/IF(YEAR($C$1)=OR(2016;2020);366;365)*$C$4;    $D$10*($C$2-$C$9)/IF(YEAR($C$1)=OR(2016;2020);366;365)*$C$4)=$C$10*$C$2/IF(YEAR($C$1)=OR(2016;2020);366;365)*$C$4Если С9=С2, и если забыть про високосность, то в первой формуле второе слагаемое из-за $D$10*($C$2-$C$9) будет равно 0 а первое будет равно результату второй формулы. Правда это дистижимо только при сроке вклада на четное число дней и при неудачном расположении звезд.
Не вижу тут ошибки. Давайте объясню:

Чтобы вариант №5 стал ИСТИНой, нужно чтобы выполнились такие условия как:
1. В условии задачи есть дополнительное пополнение вклада. В ячейке С6 Дата, а в ячейке D6  сумма пополнения.
2. Досрочного закрытия вклада не производили. В ячейке С7 пусто или 0.
3. Не происходит смены года, ячейка С2 =0

К примеру, вот условие для задачи:
Дата открытия вклада 01/01/16, Срок 181 день, Процентная ставка по вкладу - 8,4%. В тот же день было внесено 100 тр. После чего 15/01/16 было еще раз пополнение.на 50тр.
Необходимо посчитать, сколько будет начислено процентов за период действия вклада.

В этом случае по формуле идет такой расчет:
1) 100 000 * 14 / 366 * 8,4% = 322,19 - сумма % за 14 дней с момента внесения и до пополнения.
2) 150 000 * 167/366*8,4% = 5764,93 - сумма % за 167 дней с момента пополнения вклада и до его закрытия.
В итоге, общая сумма начисленных % равняется 6087,12
 
Цитата
skais675 написал:
я не телепат
и я
Цитата
skais675 написал:
то ТС давно получил, что хотел.
да, получил методику и не ведает об ошибках что были заложены в другой части формул ранее или сегодня.

Цитата
RamRiz написал:
Не вижу тут ошибки.
ошибка не  тут. Смотрите пример. Да, он синтетический, но в данном случае я за чистоплотность.
Изменено: БМВ - 22 Сен 2019 23:01:03
По вопросам из тем форума, личку не читаю.
 
БМВ Ну и что здесь такого, нужно либо ошибки на корню исправлять, либо сообщение выдавать, что получилось более одного решения. То что они одинаковые не дает Вам право выбрать одно из них, потому как они могут быть и разные и т.д. - это ошибка, о чем тут еще говорить? Либо алгоритм так устроен, что предоставляется несколько вариантов - что тоже может быть в принципе. Но это все вопросы к ТС, что и как он там считает и какие действия применять по ситуации. Что касается изначального вопроса - то он решен полностью, Все остальное это вопросы к ТС, так можно до бесконечности.
 
Теперь давайте сравним эту формулу L7 с L8
Чтобы вариант №6 с формулой в L8 стал ИСТИНой, нужно чтобы выполнились несколько условий:
1. В условии задачи есть дополнительное пополнение вклада. В ячейке С6 Дата, а в ячейке D6  сумма пополнения.
2. Досрочного закрытия вклада не производили. В ячейке С7 пусто или 0.
3. Происходит смена года, ячейка С2 больше 0.
4. Дополнительное условие, которое бывает редко, но все же возможно. Это когда количества дней до пополнения С9 меньше чем количество дней до нового года С2. (Это условие на самом деле очень часто бывает. А вот в варианте №7 в условии 4 наоборот С9 больше чем С2. Это может быть в случае если открытие вклада произошло ближе к нового году, а вот пополнение уже в следующем году.)

Для таких условий, придумаю задачу:
Дата открытия вклада 01/01/16, Срок 367 день, Процентная ставка по вкладу - 8,4%. В тот же день было внесено 100 тр. После чего 15/01/16 было еще раз пополнение.на 50тр.
Необходимо посчитать, сколько будет начислено процентов за период действия вклада.

Решение:

1. 100 000 * 14/366*8,4% = 322,19
2. 150 000 * (365-14)/366*8,4%= 150 000 * 351/366*8,4%= 12116,71

Сумма начисленных % до нового года =12438,90. Результат заносится в ячейку С11
Сумма начисленных % после нового года = 150000*2/365*8,4% = 69,04. Результат заносится в ячейку D11
Общая сумма % за весь период вклада = 12507,95. Результат заносится в ячейку С12
Изменено: RamRiz - 22 Сен 2019 23:07:18
Страницы: Пред. 1 2 3 След.
Читают тему (гостей: 1)
Наверх