Здравствуйте. Необходимо рассчитать процент скидки до маржи на определенный процент (ссылка на ячейку к примеру 15%). Я сделал колхозный вариант в 60 вложений ЕСЛИ с подстановкой в каждое выражение скидки.
1) упёрся в ограничение 8192 знаков; 2) сократив формулу до 8000 знаков попал на непонятный баг, что с 100 строки формула визуально и технически ломается. Пропадает формула ЕСЛИ, но вроде как она считает, только книгу как .xlsx сохранить нельзя. Сильно напрягает такой вариант поведения формулы.
Просьба помочь, как ещё можно добиться варианта перебора значений. То ли как-то формулы массива применить и в них не особо понимаю. Перебор значений вместо ЕСЛИ.xlsx(57.15 КБ)
Vik_tor, Формула для маржи, как считаем: (Цена по скидке - все затраты (кгвп) - себес) / (цена по скидке - все затраты (кгвп))
Цена по скидке. Перебирается подстановкой скидки. Все затраты (кгвп) напрямую зависят от Цены по скидке пунктом выше.
Перебор скидки с максимальной пусть 60 потолок до минимальной 1, тогда условие > правильно. По этому 4 раза подставляю одинаковую скидку и условием > 15% проверяю на истину, если верно, то на выходе тот самый подставленный процент, если ложь, то перебор на скидку ниже до следующей лжи.
Речь именно о том, что когда говорят "дать скидку до маржи N%". Не руками же перебирать эту самую скидку. Столбец D показывает реальную маржу, но руками в столбце C надо перебирать скидку.
Msi2102, мишка Балу, как можем так и делаем. А догадка есть что не так со вторым пунктом? В файле если протянуть формулу до конца, то будет как у меня ошибка. Мне непонятен костыль Эксель. Почему ест эту громадину до Х строки и дальше выдаёт: "Фи!". Как-то под капотом интересно работает..
Кароче как я понял, исходя из всех сложных расчётов надо в столбце С сделать такую скидку чтобы МАРЖА в столбце D оставалась максимально близка к той цифре которая указана в ячейке K4 но не ниже, и допустим взбрело вам в голову поменять 15 на 20 все скидки должны пересчитаться?
тут либо формулу переписывать на более короткую/оптимальную, либо переходить на Power Query или макросы (например, написать пользовательскую функцию UDF)
как я понял себестоимость (переменные и постоянный затраты) также зависят от цены с учетом скидки, иначе можно было бы легко математически вычислить скидку от заданной маржи Перебор в одной ячейке можно сделать значительно проще с помощью функции строка(), например, перебрать скидку формулой: строка($1:$99)/100 и далее через ПРОСМОТР() или ИНДЕКС()/ПОИСКПОЗ() подтянуть нужное значение скидки. и не нужно делать огромное количество вложенных ЕСЛИ
В исходной формуле слишком много лишних скобок, чтобы получилось в ней разобраться (у меня сходу не получилось)
UPD: Вариант, см. формулу в желтых ячейках, перебор скидки сделан до десятых процента, попробуйте поменять значение в K4 и посмотрите результат
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. А уж будет ли решение или нет при введенных остальных данных - это другой вопрос
БМВ, насколько я помню, при умножении обеих частей неравенства на отрицательную величину, выражение меняет свой знак на обратный: [f(x) / g(x)] > k => f(x) > k*g(x), если g(x) >0 и f(x) < k*g(x) в противном случае...