Страницы: 1
RSS
Варианты перебора подставления значений вместо нескольких десятков ЕСЛИ
 
Здравствуйте.
Необходимо рассчитать процент скидки до маржи на определенный процент (ссылка на ячейку к примеру 15%).
Я сделал колхозный вариант в 60 вложений ЕСЛИ с подстановкой в каждое выражение скидки.

1) упёрся в ограничение 8192 знаков;
2) сократив формулу до 8000 знаков попал на непонятный баг, что с 100 строки формула визуально и технически ломается. Пропадает формула ЕСЛИ, но вроде как она считает, только книгу как .xlsx сохранить нельзя. Сильно напрягает такой вариант поведения формулы.
Код
=ЕСЛИОШИБКА(ЕСЛИ(((B5-(B5*60/100)-((((B5-(B5*60/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*60/100)-((((B5-(B5*60/100))*G5%)+E5+F5)+J5))>$K$4;60;
ЕСЛИ(((B5-(B5*59/100)-((((B5-(B5*59/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*59/100)-((((B5-(B5*59/100))*G5%)+E5+F5)+J5))>$K$4;59;
ЕСЛИ(((B5-(B5*58/100)-((((B5-(B5*58/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*58/100)-((((B5-(B5*58/100))*G5%)+E5+F5)+J5))>$K$4;58;
ЕСЛИ(((B5-(B5*57/100)-((((B5-(B5*57/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*57/100)-((((B5-(B5*57/100))*G5%)+E5+F5)+J5))>$K$4;57;
ЕСЛИ(((B5-(B5*56/100)-((((B5-(B5*56/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*56/100)-((((B5-(B5*56/100))*G5%)+E5+F5)+J5))>$K$4;56;
ЕСЛИ(((B5-(B5*55/100)-((((B5-(B5*55/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*55/100)-((((B5-(B5*55/100))*G5%)+E5+F5)+J5))>$K$4;55;
ЕСЛИ(((B5-(B5*54/100)-((((B5-(B5*54/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*54/100)-((((B5-(B5*54/100))*G5%)+E5+F5)+J5))>$K$4;54;
ЕСЛИ(((B5-(B5*53/100)-((((B5-(B5*53/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*53/100)-((((B5-(B5*53/100))*G5%)+E5+F5)+J5))>$K$4;53;
ЕСЛИ(((B5-(B5*52/100)-((((B5-(B5*52/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*52/100)-((((B5-(B5*52/100))*G5%)+E5+F5)+J5))>$K$4;52;
ЕСЛИ(((B5-(B5*51/100)-((((B5-(B5*51/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*51/100)-((((B5-(B5*51/100))*G5%)+E5+F5)+J5))>$K$4;51;
ЕСЛИ(((B5-(B5*50/100)-((((B5-(B5*50/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*50/100)-((((B5-(B5*50/100))*G5%)+E5+F5)+J5))>$K$4;50;
ЕСЛИ(((B5-(B5*49/100)-((((B5-(B5*49/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*49/100)-((((B5-(B5*49/100))*G5%)+E5+F5)+J5))>$K$4;49;
ЕСЛИ(((B5-(B5*48/100)-((((B5-(B5*48/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*48/100)-((((B5-(B5*48/100))*G5%)+E5+F5)+J5))>$K$4;48;
ЕСЛИ(((B5-(B5*47/100)-((((B5-(B5*47/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*47/100)-((((B5-(B5*47/100))*G5%)+E5+F5)+J5))>$K$4;47;
ЕСЛИ(((B5-(B5*46/100)-((((B5-(B5*46/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*46/100)-((((B5-(B5*46/100))*G5%)+E5+F5)+J5))>$K$4;46;
ЕСЛИ(((B5-(B5*45/100)-((((B5-(B5*45/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*45/100)-((((B5-(B5*45/100))*G5%)+E5+F5)+J5))>$K$4;45;
ЕСЛИ(((B5-(B5*44/100)-((((B5-(B5*44/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*44/100)-((((B5-(B5*44/100))*G5%)+E5+F5)+J5))>$K$4;44;
ЕСЛИ(((B5-(B5*43/100)-((((B5-(B5*43/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*43/100)-((((B5-(B5*43/100))*G5%)+E5+F5)+J5))>$K$4;43;
ЕСЛИ(((B5-(B5*42/100)-((((B5-(B5*42/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*42/100)-((((B5-(B5*42/100))*G5%)+E5+F5)+J5))>$K$4;42;
ЕСЛИ(((B5-(B5*41/100)-((((B5-(B5*41/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*41/100)-((((B5-(B5*41/100))*G5%)+E5+F5)+J5))>$K$4;41;
ЕСЛИ(((B5-(B5*40/100)-((((B5-(B5*40/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*40/100)-((((B5-(B5*40/100))*G5%)+E5+F5)+J5))>$K$4;40;
ЕСЛИ(((B5-(B5*39/100)-((((B5-(B5*39/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*39/100)-((((B5-(B5*39/100))*G5%)+E5+F5)+J5))>$K$4;39;
ЕСЛИ(((B5-(B5*38/100)-((((B5-(B5*38/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*38/100)-((((B5-(B5*38/100))*G5%)+E5+F5)+J5))>$K$4;38;
ЕСЛИ(((B5-(B5*37/100)-((((B5-(B5*37/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*37/100)-((((B5-(B5*37/100))*G5%)+E5+F5)+J5))>$K$4;37;
ЕСЛИ(((B5-(B5*36/100)-((((B5-(B5*36/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*36/100)-((((B5-(B5*36/100))*G5%)+E5+F5)+J5))>$K$4;36;
ЕСЛИ(((B5-(B5*35/100)-((((B5-(B5*35/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*35/100)-((((B5-(B5*35/100))*G5%)+E5+F5)+J5))>$K$4;35;
ЕСЛИ(((B5-(B5*34/100)-((((B5-(B5*34/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*34/100)-((((B5-(B5*34/100))*G5%)+E5+F5)+J5))>$K$4;34;
ЕСЛИ(((B5-(B5*33/100)-((((B5-(B5*33/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*33/100)-((((B5-(B5*33/100))*G5%)+E5+F5)+J5))>$K$4;33;
ЕСЛИ(((B5-(B5*32/100)-((((B5-(B5*32/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*32/100)-((((B5-(B5*32/100))*G5%)+E5+F5)+J5))>$K$4;32;
ЕСЛИ(((B5-(B5*31/100)-((((B5-(B5*31/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*31/100)-((((B5-(B5*31/100))*G5%)+E5+F5)+J5))>$K$4;31;
ЕСЛИ(((B5-(B5*30/100)-((((B5-(B5*30/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*30/100)-((((B5-(B5*30/100))*G5%)+E5+F5)+J5))>$K$4;30;
ЕСЛИ(((B5-(B5*29/100)-((((B5-(B5*29/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*29/100)-((((B5-(B5*29/100))*G5%)+E5+F5)+J5))>$K$4;29;
ЕСЛИ(((B5-(B5*28/100)-((((B5-(B5*28/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*28/100)-((((B5-(B5*28/100))*G5%)+E5+F5)+J5))>$K$4;28;
ЕСЛИ(((B5-(B5*27/100)-((((B5-(B5*27/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*27/100)-((((B5-(B5*27/100))*G5%)+E5+F5)+J5))>$K$4;27;
ЕСЛИ(((B5-(B5*26/100)-((((B5-(B5*26/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*26/100)-((((B5-(B5*26/100))*G5%)+E5+F5)+J5))>$K$4;26;
ЕСЛИ(((B5-(B5*25/100)-((((B5-(B5*25/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*25/100)-((((B5-(B5*25/100))*G5%)+E5+F5)+J5))>$K$4;25;
ЕСЛИ(((B5-(B5*24/100)-((((B5-(B5*24/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*24/100)-((((B5-(B5*24/100))*G5%)+E5+F5)+J5))>$K$4;24;
ЕСЛИ(((B5-(B5*23/100)-((((B5-(B5*23/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*23/100)-((((B5-(B5*23/100))*G5%)+E5+F5)+J5))>$K$4;23;
ЕСЛИ(((B5-(B5*22/100)-((((B5-(B5*22/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*22/100)-((((B5-(B5*22/100))*G5%)+E5+F5)+J5))>$K$4;22;
ЕСЛИ(((B5-(B5*21/100)-((((B5-(B5*21/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*21/100)-((((B5-(B5*21/100))*G5%)+E5+F5)+J5))>$K$4;21;
ЕСЛИ(((B5-(B5*20/100)-((((B5-(B5*20/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*20/100)-((((B5-(B5*20/100))*G5%)+E5+F5)+J5))>$K$4;20;
ЕСЛИ(((B5-(B5*19/100)-((((B5-(B5*19/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*19/100)-((((B5-(B5*19/100))*G5%)+E5+F5)+J5))>$K$4;19;
ЕСЛИ(((B5-(B5*18/100)-((((B5-(B5*18/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*18/100)-((((B5-(B5*18/100))*G5%)+E5+F5)+J5))>$K$4;18;
ЕСЛИ(((B5-(B5*17/100)-((((B5-(B5*17/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*17/100)-((((B5-(B5*17/100))*G5%)+E5+F5)+J5))>$K$4;17;
ЕСЛИ(((B5-(B5*16/100)-((((B5-(B5*16/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*16/100)-((((B5-(B5*16/100))*G5%)+E5+F5)+J5))>$K$4;16;
ЕСЛИ(((B5-(B5*15/100)-((((B5-(B5*15/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*15/100)-((((B5-(B5*15/100))*G5%)+E5+F5)+J5))>$K$4;15;
ЕСЛИ(((B5-(B5*14/100)-((((B5-(B5*14/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*14/100)-((((B5-(B5*14/100))*G5%)+E5+F5)+J5))>$K$4;14;
ЕСЛИ(((B5-(B5*13/100)-((((B5-(B5*13/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*13/100)-((((B5-(B5*13/100))*G5%)+E5+F5)+J5))>$K$4;13;
ЕСЛИ(((B5-(B5*12/100)-((((B5-(B5*12/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*12/100)-((((B5-(B5*12/100))*G5%)+E5+F5)+J5))>$K$4;12;
ЕСЛИ(((B5-(B5*11/100)-((((B5-(B5*11/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*11/100)-((((B5-(B5*11/100))*G5%)+E5+F5)+J5))>$K$4;11;
ЕСЛИ(((B5-(B5*10/100)-((((B5-(B5*10/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*10/100)-((((B5-(B5*10/100))*G5%)+E5+F5)+J5))>$K$4;10;
ЕСЛИ(((B5-(B5*9/100)-((((B5-(B5*9/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*9/100)-((((B5-(B5*9/100))*G5%)+E5+F5)+J5))>$K$4;9;
"Меньше 9"))))))))))))))))))))))))))))))))))))))))))))))))))));"")
Ломается




Просьба помочь, как ещё можно добиться варианта перебора значений. То ли как-то формулы массива применить и в них не особо понимаю.
Перебор значений вместо ЕСЛИ.xlsx (57.15 КБ)
Изменено: jiumohoo - 07.09.2022 12:00:56
 
опишите принцип % или дополните файл таблице условий.
 
Vik_tor, Формула для маржи, как считаем:
(Цена по скидке - все затраты (кгвп) - себес) / (цена по скидке - все затраты (кгвп))

Цена по скидке. Перебирается подстановкой скидки.
Все затраты (кгвп) напрямую зависят от Цены по скидке пунктом выше.

Перебор скидки с максимальной пусть 60 потолок до минимальной 1, тогда условие > правильно.
По этому 4 раза подставляю одинаковую скидку и условием > 15% проверяю на истину, если верно, то на выходе тот самый подставленный процент,  если ложь, то перебор на скидку ниже до следующей лжи.

Речь именно о том, что когда говорят "дать скидку до маржи N%". Не руками же перебирать эту самую скидку. Столбец D показывает реальную маржу, но руками в столбце C надо перебирать скидку.  
Изменено: jiumohoo - 07.09.2022 11:58:48
 
Цитата
jiumohoo написал:
1) упёрся в ограничение 8192 знаков;
Моему восхищению нет предела :excl:
 
Msi2102, мишка Балу, как можем так и делаем. А догадка есть что не так со вторым пунктом? В файле если протянуть формулу до конца, то будет как у меня ошибка. Мне непонятен костыль Эксель. Почему ест эту громадину до Х строки и дальше выдаёт: "Фи!". Как-то под капотом интересно работает..
Изменено: jiumohoo - 07.09.2022 12:47:34
 
jiumohoo, Я абсолютно не понял Вашего объяснения в Сообщении №3.
 
Кароче как я понял, исходя из всех сложных расчётов надо в столбце С сделать такую скидку чтобы МАРЖА в столбце D оставалась максимально близка к той цифре которая указана в ячейке K4 но не ниже, и допустим взбрело вам в голову поменять 15 на 20 все скидки должны пересчитаться?

Так получается?

В целом ничего не понятно но очень интересно.
Изменено: Wild.Godlike - 07.09.2022 16:05:04
 
тут либо формулу переписывать на более короткую/оптимальную, либо переходить на Power Query или макросы (например, написать пользовательскую функцию UDF)
Изменено: New - 07.09.2022 16:00:37
 
ничего не понял. если нужно оставить определенный % маржи, то в чем проблема рассчитать скидку?
 
как я понял себестоимость (переменные и постоянный затраты) также зависят от цены с учетом скидки, иначе можно было бы легко математически вычислить скидку от заданной маржи
Перебор в одной ячейке можно сделать значительно проще с помощью функции строка(), например, перебрать скидку формулой: строка($1:$99)/100 и далее через ПРОСМОТР() или ИНДЕКС()/ПОИСКПОЗ() подтянуть нужное значение скидки.
и не нужно делать огромное количество вложенных ЕСЛИ

В исходной формуле слишком много лишних скобок, чтобы получилось в ней разобраться (у меня сходу не получилось)

UPD: Вариант, см. формулу в желтых ячейках, перебор скидки сделан до десятых процента, попробуйте поменять значение в K4 и посмотрите результат
Изменено: MCH - 07.09.2022 17:20:04
 
jiumohoo, Мда, упорству можно позавидовать, но
1. в лоб мы имеем некоторое упрощение связанное с элементарной математикой
это
((B5-(B5*60/100)-((((B5-(B5*60/100))*G5%)+E5+F5)+J5))-H5)/(B5-(B5*60/100)-((((B5-(B5*60/100))*G5%)+E5+F5)+J5))
заменяется более коротким используя просто элементарные сокращения математические
=(B5*( (1-60%)*(1-G5%))-E5-F5-J5-H5)/(B5*( (1-60%)*(1-G5%))-E5-F5-J5)
хотя и не решает задачи абсолютного упрощения
2. А вот сократить до  массивной
=MATCH(-$K$4;-(B5*( (1-ROW($9:$60)%)*(1-G5%))-E5-F5-J5-H5)/(B5*( (1-ROW($9:$60)%)*(1-G5%))-E5-F5-J5))+8 как отметил MCH,  ну разве что подкрутить чтоб писало "Меньше 9"
3.  но скорее всего можно решить уравнение
(B5*( (1-X%)*(1-G5%))-E5-F5-J5-H5)/(B5*( (1-X%)*(1-G5%))-E5-F5-J5)>$K$4 относительно X, что  сразу даст почти нужный результат. Останется округлить  и  отбросить лишнее что больше 60 и меньше 9
По вопросам из тем форума, личку не читаю.
 
Если я нигде не ошибся, то последнее неравенство можно упростить до следующего (индексы строк опущены для простоты восприятия):
X < 1 - {E+F+J + H/(1-K)}/B/(1-G) при условии, что: B>0; G<1; H/(1-K)>0
Для других сочетаний условий следует уточнить упрощение исходного неравенства...
 
IKor, ну там же единственное ограничение
(B5*( (1-X%)*(1-G5%))-E5-F5-J5) <>0 что даст область определения X. А уж будет ли решение или нет при введенных остальных данных  - это другой вопрос
По вопросам из тем форума, личку не читаю.
 
MCH, БМВ, Удивительные формулы. Как понять механизм функции СТРОКА? Нечто новое, как куда оно пляшет зачем берется значение и вычитать диапазон   8-0  
 
Цитата
jiumohoo написал:
механизм функции СТРОКА
прост. в моей ROW($9:$60) заменяется на массив чисел  от 9 до 60, на значения номеров указанных строк.
По вопросам из тем форума, личку не читаю.
 
БМВ,
насколько я помню, при умножении обеих частей неравенства на отрицательную величину, выражение меняет свой знак на обратный:
[f(x) / g(x)] > k => f(x) > k*g(x), если g(x) >0 и f(x) < k*g(x) в противном случае...  
 
IKor,  область определения от этого не меняется, а вот решение да.
По вопросам из тем форума, личку не читаю.
Страницы: 1
Наверх