Есть ли способ автоматизировать формулы поиска целей в Excel?

Question

Я должен использовать следующую формулу:

= МОЩНОСТИ (б, (г +1))- N * (б-1)-1

Мне нужно найти b, поэтому каждый раз, когда я запускаю это, мне приходится выбирать значения для d и N, а затем цель стремиться сделать b вычисленным значением. Проблема в том, что у меня есть около 1500 записей для расчета б. Есть ли способ автоматизировать расчет? Как способ, которым мы перетаскиваем входные значения для более простых функций?

Answer 1

Во-первых, ваш последний «1» должен быть «б», как некоторые другие воспитали.

Во-вторых, я обнаружил, что поиск цели действительно не очень хорошо работает (если вы введете «b»), потому что 1 всегда решает это уравнение. Это означает, что Goal Seek на самом деле не решит ее, поскольку нет способа ограничить минимум на значении, НО, если вы хотите просто автоматизировать Goal Seeker, есть эта дорогая надстройка.

В-третьих, VBA способ запустить GoalSeek является

Листы ("Лист1").Range ("A1").GoalSeek Goal:= 0, ChangingCell:= Sheets ("Sheet1").Range ("B1")

где здесь "A1" будет ячейкой с формулой, а "B1" будет пустой ячейкой, которая в итоге получит значение "b". Таким образом, вы можете создать цикл для этого и автоматизировать этот путь.

В-четвертых, лучший способ - использовать Solver в VBA. Включите надстройку Solver, затем в VBA включите ссылку на Solver. Используйте следующий код в качестве примера, где столбец D имеет формулу, столбец C - это то место, где в конце будет «b», а $ H $ 1 - это значение 2, поэтому мы не даем ему решить его с b = 1:

Public Sub SolveGeometric()

Dim i As Integer

    For i = 2 To 3
        SolverReset
        SolverOk SetCell:="$D$" & i, MaxMinVal:=3, ValueOf:="0", _
            ByChange:="$C$" & i
        SolverAdd CellRef:="$C$" & i, Relation:=4  'keep it an integer
        SolverAdd CellRef:="$C$" & i, Relation:=3, FormulaText:="$H$1"
        ' $H$1 = 2, so that it won't find 1 which always solves the equation
        SolverSolve userFinish:=True
    Next i

End Sub

Answer 2

Я предлагаю вам посмотреть, что такое "геометрический ряд", чтобы вы могли проверить формулу, которую вы пытаетесь решить.

Одна идея, которая приходит на ум (а может, а может и не работать эффективно или вовсе) для решения вашей проблемы, - это использовать надстройку "решатель" в Excel. У меня были бы столбцы значений для N, d и начальных b. один для вычисленных значений N и один для квадрата разности между рассчитанными и желаемыми значениями N.

Суммируйте квадраты различий в значениях N и используйте решатель, чтобы установить для него значение 0, изменив ячейки в столбце значений b.