Поиск  Пользователи  Правила 
Закрыть
Логин:
Пароль:
Забыли свой пароль?
Регистрация
Войти
 
Страницы: 1
RSS
Перебор значений в ячейке, которая сама себя задействует в расчёте, до соблюдения условия., Поиск решения ситуации.
 
Добрый день!
При составлении программки появилась следующая проблема: Как в ячейку (AB4) задать перебор значений, которые находятся в диапазоне (AN4 - IF4), чтобы выдавалось значение при котором будет выполняться условие в (AC4)?
Проблема в том, что искомое значение является основным в расчете и в зависимости от него меняются все числа, в том числе и само условие, которое должно быть выполнено.
Возможно ли решение данной задачи без применения макроса?

P.S. Так же в идеале чтоб подбиралось значение не из заданного диапазона, а любое, чтоб выполнялось условие.
 
Цитата
Даниил Любимов написал:
чтобы выдавалось значение при котором будет выполняться условие в (AC4)?
в данном случае можно использовать поиск решения, но можно и собрать всё в одну формулу и подставив массив (AN4:IF4) просчитать все варианты и выбрать нужный, только внимание вопрос. А если будет несколько решений? Какое лучше, то что ближе к 0 или ближе к 5%?
По вопросам из тем форума, личку не читаю.
 
БМВ, поиск решения будет применим конкретно для нахождения одного числа, а для расчёта требуется много таких значений и каждый раз пересчитывать вручную 300-400 раз будет очень долго.
Вариант с несколькими решениями имеет оооочень малую вероятность, но, если такие будут, то лучше ближе к 0.
При сборе одной формулы Вы имеете в виду собрать формулы с ячейки (AA) по (AL)? Но тогда внутри этой большой формулы она должна будет ссылаться сама на себя (ячейка AB) - в этом случае необходимо итеративное вычисление?
 
Задачка интересная, хочется расчет разобрать и каждую ссылку и расчет перенести в VBA. Думаю макрос с бесконечными циклами справится в миллион раз эффективнее любой таблицы в данном случае.
В примере ниже просто h0 раскопировал в низ, в соответствии с значениями из AN4 - IF4. Через автофильтр можно быстро найти минимальное или нужно значение.
СТрок можно сделать и 300 и 400 и 4 000 и т.д. и указать нужные значения h0. Но повторюсь - эффективнее конечно бы макросом, но для этого в расчеты нужно погрузиться.

PS, я же правильно понял, что таблица AN4 - IF4 - просто набросок нескольких из множества возможных вариантов? уточнив которые до ,001 или 0,00001 максимально приблизиться к 0?
доп-поправил формулу в столбце V
Изменено: Shama - 15 янв 2021 08:50:00
Не перестаю удивляться возможностям excel и VBA.
 
Цитата
Даниил Любимов написал:
Но тогда внутри этой большой формулы она должна будет ссылаться сама на себя (ячейка AB) - в этом случае необходимо итеративное вычисление?
не совсем
в формуле вместо AB указывается массив при этом с учетом значений 0-2 это (СТРОКА(1:201)-1)% что даст последовательность  0, 0,01, 0,02 ,,,, 0,99, 2
результатом будет 200 расчетных значений дельты из которых найти меньше 5  и исходя из этого взять одно из 0, 0,01, 0,02 ,,,, 0,99, 2 - не сложно.
Массивная
=(MATCH(1=1;ABS(ROUND(((F4-ROUND(ROUND(ROUND((1/ROUND(Z4*(IF(W4<0,05;$AO$5;IF(W4>0,05;$AO$6)));3))*
ROUND(ROUND((ROW($1:$201)-1)%*AA4+1,5*(ROW($1:$201)-1)%^2;3)/ROUND(AA4+2*(ROW($1:$201)-1)%*SQRT(1+1,5^2);2);2)^ROUND(2,5*SQRT(ROUND(Z4*(IF(W4<0,05;$AO$5;IF(W4>0,05;$AO$6)));3))-0,13-0,75*SQRT(ROUND(ROUND((ROW($1:$201)-1)%*AA4+1,5*(ROW($1:$201)-1)%^2;3)/ROUND(AA4+2*(ROW($1:$201)-1)%*SQRT(1+1,5^2);2);2))*(SQRT(ROUND(Z4*(IF(W4<0,05;$AO$5;IF(W4>0,05;$AO$6)));3))-0,1);3);2)*SQRT(ROUND(ROUND((ROW($1:$201)-1)%*AA4+1,5*(ROW($1:$201)-1)%^2;3)/ROUND(AA4+2*(ROW($1:$201)-1)%*SQRT(1+1,5^2);2);2)*W4);2)*ROUND((ROW($1:$201)-1)%*AA4+1,5*(ROW($1:$201)-1)%^2;3);2))/X4)*100;2))<5;)-1)%


Только лучше сделать через имена, Я посмотрю, если успею, на работе завал, но и то что есть работает.
Смотрите на последнем листе и в именах.  так проще.

Цитата
Shama написал:
Думаю макрос с бесконечными циклами справится в миллион раз эффективнее любой таблицы в данном случае.
ну как видите нет тут бесконечных циклов.
По вопросам из тем форума, личку не читаю.
 
Shama, понимаю, что макросом было бы классно, но, к сожалению, с ним никогда не работал.
Посмотрел пример - просто вытянуть вниз в этом случае не вариант, т.к. этим способом можно добиться наиболее точного определения необходимой дельты, но расчет предполагает добавление n(ого) количества строк и тогда получится на каждую строку необходимого расчетного значения будет приходиться по 200 строк поиска значения.
 
Цитата
Shama написал:
PS, я же правильно понял, что таблица AN4 - IF4 - просто набросок нескольких из множества возможных вариантов? уточнив которые до ,001 или 0,00001 максимально приблизиться к 0?
Значит правильно понял. Ну раз количество строк - не известно, ровно как и количество циклов в предпологаемом макросе - такое под силу только ему.
Или с другой стороны посмотреть - попробовать формулы отобразить/перевернуть/изменить - так, что бы рассчитывался именно h0, при ∆ (%) = 0.  
Не перестаю удивляться возможностям excel и VBA.
 
Цитата
Даниил Любимов написал:
понимаю, что макросом было бы классно, но, к сожалению, с ним никогда не работал
Пора начинать
Изменено: msi2102 - 15 янв 2021 09:04:03
 
Цитата
msi2102 написал:
Пора начинать
не начинал и обхожусь :-)
По вопросам из тем форума, личку не читаю.
 
Цитата
БМВ написал:
не начинал и обхожусь :-)
Прям уж и не начинал, а ТУТ   :D  
Изменено: msi2102 - 15 янв 2021 09:20:13
 
Перечитал, не правильно понял)  
Изменено: Shama - 15 янв 2021 09:29:54
Не перестаю удивляться возможностям excel и VBA.
 
Цитата
msi2102 написал:
Прям уж и не начинал, а  ТУТ  
оно само  :D .
Просто начал изредка использовать. А если серьезно, то наверно не стоит этим гордится, но не прочел ни одной книжки или курса.
Изменено: БМВ - 15 янв 2021 10:03:42
По вопросам из тем форума, личку не читаю.
 
БМВ, смотрю Ваш пример и да, формула эта работает, но пока не понял ее составления, т.к. ее нельзя протянуть или куда-либо копировать т.к. считает сразу не верно. И вроде при протягивании в формуле строки меняются верно и расчёт идет, но значения уже получаются не правильными. Пока не могу понять в чем проблема. Можете чуть подробнее написать зависимость этой формулы?
На первом листе смотрю формулу.
Изменено: Даниил Любимов - 15 янв 2021 10:07:31
 
Раз уж я сделал с именами смотрите в них. Удобнее и понятнее. я именовал также как у вас в таблице, сразу видно что от куда. ну или файл с второй и третьей строкой выложите. Может что не закрепил я или наоборот.
Изменено: БМВ - 15 янв 2021 10:34:33
По вопросам из тем форума, личку не читаю.
 
А я вот попробую предложить решение)
В массиве AV:NI считается "решение"(дельта %) по установленным в таблице значениям + на каждый вариант h0.
В итоге в основную таблицу просто попадает значение h0, которое по расчетному массиву дало наименьшее решение.

Осмелился поэксперементировать с данными, вроде работает.
Протягивайте столько, сколько хотите, но не забывайте также протянуть формулы в массиве AV:NI.

Теоретически можно проработать вариант с 2000 вариантами h0, тем самым уточнить расчет до 0,001, но может это никому не надо)  
Изменено: Shama - 15 янв 2021 11:06:31
Не перестаю удивляться возможностям excel и VBA.
 
БМВ, разобрался, все получилось и работать начало как нужно прям!! Вот только не понял почему в функции "ПОИСКПОЗ(1=1;..." в качестве искомого значения стоит 1=1? И как сделать округление до 3 знака после запятой, а не до 2? (везде в формулах поменял округление до 3 и в именах тоже) но в значении все равно третий знак везде пишет 0. (в некоторых строках чтобы получить дельту до 5% нужен третий знак, который почему то округляется).

Shama, Ваше решение тоже хорошо работает, только большая таблица рядом не комильфо :)

Спасибо вам огромное!
 
ПОИСКПОЗ(1=1  -  ЭТО искать ИСТИНА можно было ПОИСКПОЗ(1:--ECЛИ...)
В смысле менять не сотые а тысячные, тогда (СТРОКА(1:2001)-1)*10^-3
По вопросам из тем форума, личку не читаю.
Страницы: 1
Читают тему (гостей: 3)
Наверх