Подбор значений в ячейке в заданном диапазоне

Дата: Воскресенье, 30.03.2014, 09:03 | Сообщение № 1

Группа: Пользователи

Ранг: Новичок

Сообщений: 11

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

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

Excel 2007

Добрый день, форумчане!

Помогите пожалуйста не знакомому с VBA человеку в решении одной задачи:
Есть набор показателей (показатель 1, показатель 2, показатель 3 ...) с балльной оценкой (от 0 до 10)
Есть определенные веса к данным показателям для получения интегральной оценки (например, оценка = балл 1*вес 1 + балл2*вес2...)
Есть некая производная ячейка (проверочная) для оценки всего этого "мероприятия" значение которой, чем больше - тем лучше.

Требуется написать макрос для перебора значений в ячейке "вес1" в диапазоне от -100% до 100% с шагом 0,5% для получения максимального значения проверочной ячейки ( результат выводить просто на экран, допустим :максимальное значение 0,5 при весе 25%)

встроенный инструмент "Подбор параметра", "поиск значений" не подходит

Прошу помогите мне

пример грубый, но суть в нем прокомментировал

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

Сообщение отредактировал Igrik555 - Воскресенье, 30.03.2014, 09:03

Дата: Воскресенье, 30.03.2014, 12:42 | Сообщение № 2

Группа: Авторы

Ранг: Старожил

Сообщений: 1613

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

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

Excel 2013, 2016

Цитата Igrik555, 30.03.2014 в 09:03, в сообщении № 1

макрос для перебора значений в ячейке "вес1"

только для одной ячейки?
например, так:
[vba]

Код

Sub ertert()
Dim m!, t!, i!, s$
Application.ScreenUpdating = False
m = Range("D2").Value: s = "максимальное значение " & Format(m, "00.00%") & " при весе " & Format(Cells(3, 7), "00.00%")
For i = -1 To 1 Step 0.005
     Cells(3, 7) = i
     t = Range("D2").Value
     If t > m Then m = t: s = "максимальное значение " & Format(t, "00.00%") & " при весе " & Format(i, "00.00%")
Next i
Application.ScreenUpdating = True
MsgBox s, 64
End Sub

[/vba]

Яндекс.Деньги 4100159601573

Дата: Воскресенье, 30.03.2014, 16:13 | Сообщение № 3

Группа: Пользователи

Ранг: Новичок

Сообщений: 11

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

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

Excel 2007

спасибо огромное! то что надо yes

Цитата nilem, 30.03.2014 в 12:42, в сообщении № 2

только для одной ячейки?

вообще надо для всех ячеек, то есть функция нашла максимальное значение для "вес1" - установила там, дальше перешла в соседнюю "вес2" - там установила максимальную если нашла и до следующей. Это конечно идеальный вариант.
А вообще, можно ли написать такую функцию, что она перебирает все три ячейки с весами и устанавливает в проверочной самое максимальное значение из всех вариантов перебора?

Дата: Воскресенье, 30.03.2014, 17:29 | Сообщение № 4

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

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

Сообщений: 4512

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

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

Excel 2003

Цитата Igrik555, 30.03.2014 в 16:13, в сообщении № 3

то что надо

Мне кажется что код, который вам понравился можно сократить до
[vba]

Код

Sub ertert()
MsgBox ("максимальное значение " & Format(Range("D2"), "#0.00%") & " при весе " & Format(1, "#0.00%"))
End Sub

[/vba]работает в 400 раз быстрее.
Дело в том, что в макросе вычисления отсутствуют и на выводе всегда 100% и значение из D2
И еще задание не совсем понятно. Может быть надо найти не максимальное, а максимально близкое значение?

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

Дата: Воскресенье, 30.03.2014, 18:17 | Сообщение № 5

Группа: Авторы

Ранг: Старожил

Сообщений: 1613

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

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

Excel 2013, 2016

Цитата AlexM, 30.03.2014 в 17:29, в сообщении № 4

в макросе вычисления отсутствуют

они есть на листе.

Igrik555,
поставьте 100% в качестве веса для всех трех показателей и точно получите максимальное значение в производной ячейке. Наверное, нужно получить макс. значение произв-й ячейки при условии, что сумма весов показателей = 100%. Так?

Яндекс.Деньги 4100159601573

Дата: Воскресенье, 30.03.2014, 18:39 | Сообщение № 6

Группа: Пользователи

Ранг: Новичок

Сообщений: 11

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

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

Excel 2007

ага, вычисления есть на листе, но они от фонаря.

На самом деле, данная функция нужна для подбора индикатора, отвечающего за некие качественные показатели модели. Сама производная (D2) формально должна соответствовать критериям - чем выше,тем точнее. Но так как, информация в примере не полная, конечно при 100% весе получается максимальное значение. На самом деле оно не так.

Поэтому, вариант решения nilem меня устраивает. Спасибо! Дальше я буду просто подбирать по каждой ячейке наилучшее значение производной.

Но конечно, если бы я шарил в макросах, я бы сделал больше. А именно, чтобы все веса перебирались одновременно, что бы производная получила максимальное значение из перебора всех возможных вариантов трех ячеек.

Кстати, вариант (что значения трех ячеек =100%) тоже хорош, только я не смогу это сделать

Дата: Воскресенье, 30.03.2014, 19:20 | Сообщение № 7

Группа: Авторы

Ранг: Старожил

Сообщений: 1613

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

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

Excel 2013, 2016

Цитата Igrik555, 30.03.2014 в 18:39, в сообщении № 6

... из перебора всех возможных вариантов трех ячеек...

вот тупо в лоб перебор всех вариантов
[vba]

Код

Sub ertert()
Dim m!, t!, a!, b!, c!, x
Application.ScreenUpdating = False
m = Range("D2").Value: x = Range("G3:I3").Value
For a = -1 To 1 Step 0.005
       Cells(3, 7) = a
       For b = -1 To 1 Step 0.005
           Cells(3, 8) = b
           For c = -1 To 1 Step 0.005
               Cells(3, 9) = c
               t = Range("D2").Value
               If t > m Then m = t: x = Range("G3:I3").Value
           Next c
       Next b
Next a
Range("G3:I3").Value = x
Application.ScreenUpdating = True
End Sub

[/vba]
но будет работать очень-очень долго (если надоест ждать, нажмите Ctrl+Break)

edited лишняя переменная

Яндекс.Деньги 4100159601573

Сообщение отредактировал nilem - Воскресенье, 30.03.2014, 19:24

Дата: Воскресенье, 30.03.2014, 19:52 | Сообщение № 8

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

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

Сообщений: 4512

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

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

Excel 2003

Если первый макрос выводил значение из D2 и 100%, то второй совсем ничего не делает.
Смотрите сами четвертая строка
m = D2 в массиве х значения трех ячеек G3, H3 и I3
далее t = D2
Условие никогда не будет ИСТИНА так как m и t равны
В третьей строке снизу из массива, в котором значения G3, H3 и I3 вставляем в эти же ячейки.
Так что исключение циклов из кода ничего не изменит.

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

Дата: Воскресенье, 30.03.2014, 20:05 | Сообщение № 9

Группа: Пользователи

Ранг: Новичок

Сообщений: 11

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

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

Excel 2007

мне кажется у вас между собой идет спор )))) AlexM, логически вы правы. Как тогда можно решить данную задачу? Я просто не силен в этом

Дата: Воскресенье, 30.03.2014, 20:15 | Сообщение № 10

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

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

Сообщений: 4512

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

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

Excel 2003

Igrik555, больше вам поясняю строки макроса.
Строки Application.ScreenUpdating = False/True отключают/включают обновление экрана, это увеличивает скорость работы макроса и не мельтешат числа в клетках. Когда экран не обновляется макрос в цикле меняет значения ячеек G3, H3 и I3 от 1 до 100%, но так как в сообщении выше я писал, что в этих ячейки после работы макроса возвратятся значения, которые были перед запуском макроса, то зачем тогда циклы.
Я не вижу задачу, поэтому не предлагаю решение. извините.

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

Дата: Воскресенье, 30.03.2014, 20:35 | Сообщение № 11

Группа: Авторы

Ранг: Старожил

Сообщений: 1613

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

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

Excel 2013, 2016

Ну вот я тоже теперь засомневался

Цитата AlexM, 30.03.2014 в 19:52, в сообщении № 8

m = D2 в массиве х значения трех ячеек G3, H3 и I3
далее t = D2
Условие никогда не будет ИСТИНА так как m и t равны

Вот смотрите: после x = Range("G3:I3").Value пишем на лист в цикле значения Cells(3, 7) = a, Cells(3, 8) = b и Cells(3, 9) = c
после этого (вернее после каждого изменения) происходит пересчет формул на листе (D2 и G3:I3 связаны формулами), и получаем новое значение в D2.
Присваиваем t = Range("D2").Value. Если оно больше первоначального значения If t > m Then m = t, то запоминаем его и берем массивчик х уже с новыми значениями: x = Range("G3:I3").Value
А в конце записываем то, что получилось Range("G3:I3").Value = x
Вроде бы логично. Или все-таки нет?

Яндекс.Деньги 4100159601573

Дата: Воскресенье, 30.03.2014, 20:51 | Сообщение № 12

Группа: Пользователи

Ранг: Новичок

Сообщений: 11

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

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

Excel 2007

вообщем запустил, посмотрим к чему приедем

Дата: Воскресенье, 30.03.2014, 20:56 | Сообщение № 13

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

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

Сообщений: 4512

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

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

Excel 2003

В начальный момент в D2 максимальное значение рассчитанное формулами, а при меньших процентах значение будет меньше. Поэтому значение t будет меньше или равно m

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