Создание макросов Visual Basic с помощью решения Microsoft Excel в Excel 97

Переводы статьи Переводы статьи
Код статьи: 843304 - Vizualiza?i produsele pentru care se aplic? acest articol.
Развернуть все | Свернуть все

В этой статье

Аннотация

В данной статье описывается создание макросов Microsoft Visual Basic с помощью решения Microsoft Excel в Microsoft Excel 97. Поиск Microsoft Excel является надстройка Microsoft Excel.

Кроме того Эта статья содержит сведения о создавать макросы, сконструировать макрос и работа с ограничениями макроса. В этой статье также рассматриваются алгоритм и методы, используемые для поиска решения Microsoft Excel. Ниже перечислены все разделы, рассмотренных в данной статье.

ВВЕДЕНИЕ

Данная статья содержит сведения о программе Поиск решения.

Дополнительная информация

Описание поиска решения Microsoft Excel

Поиск Microsoft Excel является поиск решения надстройки Microsoft Excel позволяет Microsoft Excel определить оптимальное значение для формулы в определенном Целевая ячейка на листе Microsoft Excel. Microsoft Excel подбираются значения других ячейки, которые относятся к целевой ячейки с помощью формулы. По окончании создавать формулы и определяют набор параметров или ограничений для переменные в уравнении решения Microsoft Excel пытается различных решений для прибытия на ответ, удовлетворяющий всем ограничениям. Поиск Microsoft Excel использует следующие элементы «решения» формулу:
  • Целевая ячейка - целевая ячейка является целью. Он представляет собой ячейку модели листа, который будет свернуто развернуто или установить определенное значение.
  • Изменяемые ячейки - изменяемые ячейки являются переменными принятия решений. Эти ячейки влияют на значение целевой ячейки. Эти ячейки доступны изменить с помощью Microsoft Excel Solver найти оптимальное решение для целевой ячейки.
  • Ограничения - ограничения, ограничения на содержимое ячеек. Для Пример, одну ячейку в листе модели может быть ограничен для целочисленных значений в то время как другой ячейки могут быть ограничены меньше заданного значения.
Создания и обработки моделей поиска решения Microsoft Excel, можно автоматизировать с помощью Microsoft Visual Basic для приложений (VBA) макрос. В данной статье описывается использование макрос VBA язык для использования функций поиска решения Microsoft Excel в Microsoft Excel 97. В данной статье предполагается, что вы знакомы с языком VBA и редактор Microsoft Visual Basic для Microsoft Excel 97. Примеры, используемые в этой статье доступны для загрузки на веб-узле корпорации Майкрософт:
http://download.Microsoft.com/download/excel97win/solverex/1.0/WIN98Me/en-US/SolverEx.exe
Примечание Также можно использовать макросы и примеров, описанных в данном статья в Microsoft Excel версии 5.0 и 7.0.

Использование функции поиска решения Microsoft Excel в макрос VBA

Для использования функции надстроек Microsoft Excel поиск макроса VBA, вы необходимо сослаться добавить в проект VBA из книги, содержит макросы. Если ссылка не надстройка "Поиск решения" Microsoft Excel, вы получите Ошибка компиляции ниже, при попытке запустить макрос:
Ошибка компиляции: Подпрограмма или функция не определена.
Для Надстройка "Поиск решения" Microsoft Excel макросов в книге ссылок, используйте следующие действия:
  1. Откройте книгу.
  2. На Сервис Выберите пунктМакрос, а затем нажмите кнопку Редактор Visual Basic..
  3. На Сервис меню, нажмите кнопкуСсылки.
  4. В Доступные ссылки список, щелкните Выберите Solver.xls Установите флажок и нажмите кнопку ОК.

    Примечание Если вы не видите Solver.xls в Доступные ссылкиВыберите Обзор. В Добавить Ссылка диалоговом окне найдите и выберите файл Solver.xla, а затем Нажмите кнопку Открыть. Solver.xla файл обычно находится в Вложенной папке C:\Program Office\Office\Library\Solver создаваемую.
Теперь вы готовы для использования функций поиска решения Microsoft Excel Макрос VBA.

Как создавать макроса VBA, который создает и решает простую модель поиска решения Microsoft Excel

Несмотря на то, что поиск решения Microsoft Excel предлагает множество функций, следующие три функции являются фундаментальным для создания и решения модели:
  • Функция SolverOK
  • Функция SolverSolve
  • Функция SolverFinish

Функция SolverOK

Функция SolverOK определяет основные модель поиска решения Microsoft Excel. Как правило, является функция SolverOK Первая функция, которая используется для построения модели поиска решений Microsoft Excel. Функция SolverOK Это эквивалентно нажатию кнопки Поиск решения на Сервисменю, а затем указав параметры, находящиеся в Поиск решениядиалоговое окно. Ниже приведен синтаксис функции SolverOK .
SolverOK (SetCell MaxMinVal ValueOf, ByChange)
Ниже описывается синтаксис функции SolverOK :
  • SetCell указывает значение целевой ячейки.
  • Хотите ли вы решить целевая ячейка соответствует MaxMinVal Максимальное значение (1), минимальное значение (2) или (3) определенное значение.
  • ValueOf задает значение, с которым сопоставляется значение целевой ячейки. Если вы установить MaxMinVal 3, необходимо указать этот аргумент. Если MaxMinVal значение 1 или 2, этот аргумент можно опустить.
  • ByChange указывает ячейку или диапазон ячеек, которые будут изменены.
На рисунке 1 связывает аргументы для функции SolverOK с параметры в Поиск решения диалоговое окно.

Рисунок 1. Параметры, связанные с SolverOK аргументы

Свернуть это изображениеРазвернуть это изображение
 Рисунок 1. Параметры, связанные

		  с аргументами SolverOK


Функция SolverSolve

Функция SolverSolve решает модели с помощью параметров заданная с помощью функции SolverOK . Выполнение функции SolverSolve эквивалент Нажатие Решения в Поиск решения диалоговое окно. Ниже приведен синтаксис функции SolverSolve .
SolverSolve (UserFinish, ShowRef)
Ниже описывается синтаксис функции SolverSolve :
  • UserFinish указывает, должен ли пользователь для завершения решения модель.

    Для возврата результатов без отображения Поиск решения Результаты диалогового окна задайте значение этого аргумента значение TRUE. Для возврата результатов и отображения Результаты поиска решения диалогового окна задайте значение этого аргумента значение FALSE
  • ShowRef определяет макрос, который вызывается, когда Microsoft Excel выдается Промежуточные решения.

    Аргумент ShowRef следует использовать только в том случае, если аргумент StepThru функции SolverOptions передается значение TRUE.

Функция SolverFinish

Указывает, что делать с результатами, функция SolverFinish а также от типа создаваемого отчета после завершения процесса решения. Ниже приведен синтаксис функции SolverFinish .
SolverFinish (KeepFinal, ReportArray)
Ниже описывается синтаксис функции SolverFinish :
  • KeepFinal указывает, что делать с окончательные результаты. Если KeepFinal имеет значение 1, значения окончательного решения хранятся в изменяемых ячеек замена значений. Если KeepFinal равно 2, отбрасываются значения окончательного решения и первый значения будут восстановлены.
  • Указывает массив, который указывает тип отчета Microsoft ReportArray Excel создаст при достижении решения. Если ReportArray имеет значение 1, Microsoft Excel создает отчет по ответам. Если присвоено значение 2, Microsoft Excel создает отчет об устойчивости и если значение 3 Microsoft Excel создает отчет о пределах. Дополнительные сведения об этих отчетах см. "Как создавать отчеты для решений"раздел.
На рисунке 2. Параметры результатов поиска решения Microsoft Excel, связанные с аргументами SolverFinish

Свернуть это изображениеРазвернуть это изображение
 На рисунке 2. Параметры результатов поиска решения

		  связанные с аргументами SolverFinish


В данной статье описывается создание простого модель поиска решения Microsoft Excel в интерактивном режиме. Первым шагом является Создайте лист для модели. Лист будет содержать некоторые ячейки данных и хотя бы одной ячейки, содержащей формулу. Эта формула зависит от других ячейки на листе. После настройки на листе, нажмите кнопку Поиск решения на Сервис меню. ВПоиск решения диалоговое окно Укажите целевую ячейку, значение, требуется решить для диапазона ячеек, которые будут изменены, и ограничения. Нажмите кнопку Решения Чтобы начать процесс решения. После Microsoft Excel найденное решение, результаты отображаются в листе и решающая Microsoft Excel Отображает окно сообщения с запросом подтверждения, если необходимо сохранить окончательный результат или Если требуется, чтобы отказаться от них. При выборе одного из этих параметров завершения поиска решения Microsoft Excel.

На рисунке 3 Демонстрирует простой модели, можно создать, выполнив следующие действия.

На рисунке 3. Простая модель: квадратный корень модели

Свернуть это изображениеРазвернуть это изображение
 На рисунке 3. A

		  Простая модель: квадратный корень модели


В этом примере изменение ячейке A1, содержащей формулу = A1 ^ 2, значение, которое сделает в ячейке A2 равно значение 50. Другими словами найти квадратный корень 50. Квадратный корень модели существуют без ограничений. Макрос Find_Square_Root выполняет следующие задачи:
  • Она настраивает модель, которая решит значение ячейки A2 значение 50 Изменяя значение ячейки A1.
  • Он решает модели.
  • Она сохраняет лист без окончательные результаты Отображение Результаты поиска решения диалоговое окно.
Этот простой макрос создает модель поиска решения Microsoft Excel и решает его без вмешательства пользователя. Ниже описаны макрос Find_Square_Root .
    Sub Find_Square_Root()

    ' Set up the parameters for the model.
    ' Set the target cell A2 to a value of 50 by changing cell A1.
    SolverOK SetCell:=Range("A2"), MaxMinVal:=3, ValueOf:=50, _
         ByChange:=Range("A1")

    ' Solve the model but do not display the Solver Results dialog box.
    SolverSolve UserFinish:=True

    ' Finish and keep the final results.
    SolverFinish KeepFinal:=1

    End Sub
Макрос Find_Square_Root2 , является модификацией макросов Find_Square_Root . При использовании функции InputBox макрос Find_Square_Root2 запрашивает значение, которое необходимо решить целевой ячейки. По окончании ввода значения Этот параметр задает в качестве значения аргумента SolverOKvalueof , устранены, сохраняет результаты в переменной Find_Square_Root2 макрос квадратный корень отменяет решения и восстанавливает значения на листе в исходное состояние. По сути макрос Find_Square_Root2 показано, как сохранить результаты в одной или нескольких переменные и затем восстановить изменение ячейки исходные значения.

Ниже описаны макрос Find_Square_Root2 .
    Sub Find_Square_Root2()

    Dim val
    Dim sqroot

    ' Request the value for which you want to obtain the square root.
    val = Application.InputBox( _
         prompt:="Please enter the value for which you want " & _
         "to find the square root:", Type:=1)

    ' Set up the parameters for the model.
    SolverOK SetCell:=Range("A2"), MaxMinVal:=3, ValueOf:=val, _
         ByChange:=Range("A1")

    ' Do not display the Solver Results dialog box.
    SolverSolve UserFinish:=True

    ' Save the value of cell A1 (the changing cell) before you discard 
    ' the results.
    sqroot = Range("a1")

    ' Finish and discard the results.
    SolverFinish KeepFinal:=2

    ' Show the result in a message box.
    MsgBox "The square root of " & val & " is " & Format(sqroot, "0.00")

    End Sub

Как создавать отчеты для решений

Поиск Microsoft Excel предлагает несколько типов отчетов, которые описывают способ результаты изменения и насколько близко ограничения документации критические значения. Каждый отчет помещается на отдельном листе книги. Выполнив это типы отчетов что предлагает решающая Microsoft Excel:
  • Отчет по ответам – отчет по ответам список изменяемых ячеек и целевой ячейки с их соответствующих исходных и конечных значений, ограничения, и сведения об ограничениях.
  • Отчет об устойчивости - чувствительность отчета содержит сведения о чувствительности решение заключается в небольшие изменения в формуле для целевой ячейки.
  • Отчет о пределах – отчет о пределах список изменяемых ячеек и целевой ячейки целевой объект, нижний и верхний пределы и соответствующих значений значения.
Для создания отчетов для моделей, необходимо укажите массив значений для аргумента ReportArray функции SolverFinish . Дополнительные сведения о Аргумент ReportArray , см.»SolverFinish (KeepFinal, ReportArray)"раздел. Например, если требуется создать отчет для ограничения модель, которая создает макрос Find_Square_Root2 и решает, изменения в функции SolverFinish макрос, которая выглядит примерно в следующем примере кода:
    SolverFinish KeepFinal:=2, ReportArray:= Array(3)
Для создания нескольких отчетов, измените функцию SolverFinish Таким образом, чтобы она выглядит примерно в следующем примере кода:
    SolverFinish KeepFinal:=2, ReportArray:= Array(1,2)

Использование функции Microsoft ExcelSolver в цикле макроса

Во многих случаях это хороший способ поиска решения Microsoft Excel решения Целевая ячейка для нескольких значений. Обычно это можно сделать с помощью одного циклические структуры, доступных в VBA.

Макрос Create_Square_Root_Table показано, как функции поиска решения Microsoft Excel в макросе циклических зависимостей. Create_Square_Root_Table макрос создает таблицы в новый лист. Он вставляет номера, один через десять и соответствующий квадратный корень из каждого номера. Макрос Create_Square_Root_Table создает таблицу с помощью цикла для перебора чисел от 1 до 10 и решения Целевая ячейка в значения, которое соответствует номеру модели квадратный корень итерации. Ниже описаны макрос Create_Square_Root_Table .
    Sub Create_Square_Root_Table()

    ' Add a new worksheet to the workbook.
    Set w = Worksheets.Add

    ' Put the value 2 in cell C1 and the formula =C1^2 in cell C2.
    w.Range("C1").Value = 2
    w.Range("C2").Formula = "=C1^2"

    ' A loop that will make 10 iterations, starting with the number 1, 
    ' and finishing at the number 10.
    For i = 1 To 10

        ' Set the Solver parameters that indicate that Solver should
        ' solve the cell C2 for the value of i (where i is the number
        ' of the iteration) by changing cell C1.
        SolverOk SetCell:=Range("C2"), ByChange:=Range("C1"), _
            MaxMinVal:=3, ValueOf:=i

        ' Do not display the Solver Results dialog box.
        SolverSolve UserFinish:=True

        ' Save the value of i in column A and the results of the 
        ' changing cell in column B.
        w.Cells(i, 1) = i
        w.Cells(i, 2) = Range("C1")

        ' Finish and discard the final results.
        SolverFinish KeepFinal:=2

    Next

    ' Clear the range C1:C2
    w.Range("C1:C2").Clear

    End Sub

Макрос Create_Square_Root_Table формирует таблицу показано на рис. 4.

На рисунке 4. Вывод, созданный макрос Create_Square_Root_Table

Свернуть это изображениеРазвернуть это изображение
На рисунке 4. Вывод, созданный

		  Макрос Create Square Root Table


Работа с ограничениями

Ограничение является ограничением по содержимому одного или нескольких ячейки. Модель может иметь одно или несколько ограничений. Набор ограничений Установите набор, необходимые для удаления определенных сочетаний значений равенства или неравенства для переменных решения из решения. Например ограничение может требуется одна ячейка быть больше нуля и другой ячейки содержат только значение типа integer.

Квадратный корень модели, мы обсудили до сих пор — Простая модель, которая не содержит какие-либо ограничения. На рисунке 5 показан модель, которая использует ограничения. Эта модель предназначена для поиска оптимального сочетание продуктов для максимальной прибыли.

На рисунке 5. Номенклатура продуктов с уменьшают прибыль

Свернуть это изображениеРазвернуть это изображение
На рисунке 5. Номенклатура продуктов с

		  уменьшают прибыль


Например, телевизоры, выпускаемых компанией стереосистемы и динамики и использует общий запас деталей, блоков питания динамика конусы и т. д. Части, ограниченной питания. Ваша цель — Определите наиболее выгодные смесь продуктов для построения. Ваш прибыль на единицу уменьшается с тома, поскольку необходимых дополнительных цена стимулы для загрузки канал распространения. Степень снижения эффективности является 0,9. Это возведения в степень используется для расчета прибыли по продуктам в диапазоне G11:I11.

Ваш целью является найти максимальной прибыли (ячейка G14). Значения, которые можно будет Измените чтобы найти максимальную прибыль — это число единиц, которые необходимо построить. В диапазон G9:G11 представляет изменяемых ячеек в этой модели. Единственное ограничение Это, что количество используемых частей не может превышать количество частей, достаточно в наличии. Решения Microsoft Excel это ограничение отображается как E3:E7<=B3:B7. if="" you="" were="" to="" build="" this="" microsoft="" excel="" solver="" model="" interactively,="" the="" microsoft="" excel="" solver="" parameters="" would="" look="" similar="" to="" those="" that="" are="" in="" figure="" 6.=""></=B3:B7.>

На рисунке 6. Параметры поиска решения Microsoft Excel Номенклатура продуктов с моделью уменьшают прибыль

Свернуть это изображениеРазвернуть это изображение
На рисунке 6. Параметры поиска решения Microsoft Excel

		  Номенклатура продуктов с моделью уменьшают прибыль


Для создания и решить номенклатуры продуктов с помощью модели уменьшают прибыль, следует использовать Новая функция, функция SolverAdd , в дополнение к функции VBA решения Microsoft Excel, которые были описано выше. Функция SolverAdd добавляет ограничение к модели. Выполнение функции SolverAdd эквивалентен нажатию кнопки Добавить кнопки в Поиск решения диалоговое окно. В Функция SolverAdd имеет следующий синтаксис:
SolverAdd (CellRef, отношения, FormulaText)
Ниже описывается синтаксис функции SolverAdd :
  • CellRef ссылается на одну или несколько ячеек, формирующих у левого края ограничение.
  • Отношение является арифметическим связь между левой и правой сторон ограничение.
  • Отношение может принимать значение от 1 до 5, как в следующем примере:
    • 1 Значение меньше или равно)<>
    • Vaue 2 равно (=).
    • Значение 3 больше или равно (настроек =).
    • Значение 4 представляет собой целое число.
    • Значение 5 — двоичный файл (значение 0 или 1).
  • FormulaText ссылается на одну или несколько ячеек, формирующих с правой стороны CONSTRAINT.* *
** При указании диапазона ячеек для аргумента FormulaText функции SolverAdd , обратите внимание, является ли ссылка является относительным или абсолютным. Как правило необходимо указать абсолютный ссылка для аргумента FormulaText . При указании относительных ссылок для аргумента FormulaText понимают что ссылка будет относиться к целевой ячейки и не активную ячейку.

Примечание В Microsoft Excel версии 5.0 и 7.0, нотация R1C1 При указании на ячейку или диапазон ячеек в аргументе FormulaText . Напротив в Microsoft Excel 97 с помощью стиля A1 нотация для задания аргумента FormulaText .

На рисунке 7. Поля, связанные с Аргументы SolverAdd

Свернуть это изображениеРазвернуть это изображение
 На рисунке 7. Поля, связанные с

		  аргументы SolverAdd


Макрос Maximum_Profit , который создает модель для номенклатуры продуктов с помощью уменьшающихся Возвращает модель. Этот макрос выполняет следующие функции или аргументы:

  • Функция SolverOK устанавливает значение целевой ячейки с максимальной эффективностью и Задает для изменения ячейки.
  • Функция SolverAdd добавляет ограничение к модели.
  • Функция SolverSolve находит решение без отображенияРезультаты поиска решения диалоговое окно.
  • Функция SolverFinish возвращает окончательный результат на лист.
Описывает следующий код макроса Maximum_Profit :
    Sub Maximum_Profit()

    ' Set up the parameters for the model.
    ' Determine the maximum value for the sum of profits in cell G14
    ' by changing the number of units to build in cells G9:I9.
    Solverok setcell:=Range("G14"), maxminval:=1, _
        bychange:=Range("G9:I9")

    ' Add the constraint for the model. The only constraint is that the
    ' number of parts used does not exceed the parts on hand-- 
    ' E3:E7<=B3:B7
    SolverAdd CellRef:=Range("E3:E7"), Relation:=1, _
        FormulaText:="$B$3:$B$7"

    ' Do not display the Solver Results dialog box.
    SolverSolve UserFinish:=True

    ' Finish and keep the final results.
    SolverFinish KeepFinal:=1

    End Sub

Примечание В Microsoft Excel версии 5.0 и 7.0, нотация R1C1 При указании на ячейку или диапазон ячеек в аргументе FormulaText . Напротив в Microsoft Excel 97 с помощью стиля A1 нотация для задания аргумента FormulaText .

При запуске макроса Maximum_Profit поиск Microsoft Excel будет найти решения построения 160 телевизоров, 200 стереосистемы и 80 динамики наибольший доход от $14,917 долларов.

Изменение и удаление ограничений

Ограничения в модели может быть изменено программными средствами или удалены. Ограничения определяются их аргументы CellRef и отношения .

Чтобы программно изменить существующий ограничения, используйте функцию SolverChange . Ниже приведен синтаксис для SolverChange функции:
SolverChange (CellRef, отношения, FormulaText)
Обратите внимание, что аргументы для функции SolverChange то же самое для тех, которые можно применять с функцией SolverAdd .

Если требуется Чтобы изменить ограничение в номенклатуры продуктов с помощью модели снижения эффективности вы следует использовать функцию SolverChange . Например, в настоящее время ограничения, Указанный том, что количество используемых частей не меньше или равно число частей в наличии (E3:E7<= b3:b7).="" if="" you="" want="" to="" change="" this="" constraint="" so="" that="" the="" number="" of="" parts="" used="" is="" less="" than="" or="" equal="" to="" the="" number="" of="" parts="" projected="" (number="" of="" parts="" on="" hand="" plus="" number="" of="" parts="" ordered).="" this="" new="" constraint="" would="" look="" like="" e3:e7=""></=><= d3:d7.="" the="" following="" macro="" would="" change="" the="" existing="" constraint=""></=><=B3:B7 to="" e3:e7=""></=B3:B7><= d3:d7="" and="" solve="" for="" a="" solution.=""></=>

Ниже описаны макрос Change_Constraint_and_Solve .
    Sub Change_Constraint_and_Solve()

    ' Change the constraint.
    SolverChange CellRef:=Range("E3:E7"), Relation:=1, _
       FormulaText:="$D$3:$D$7"

    ' Return the results and display the Solver Results dialog box.
    SolverSolve UserFinish:=False

    End Sub

Из-за ограничения определяются по CellRef и отношение аргументов, FormulaText аргумент для ограничения можно изменять только с помощью функции SolverChange . CellRef и отношение значения не соответствуют существующее ограничение, необходимо удалить ограничения и затем добавить измененный ограничение. Чтобы удалить ограничение, с помощью функция SolverDelete . Ниже приведен синтаксис функции SolverDelete .

SolverDelete (CellRef, отношения, FormulaText)

Обратите внимание, что аргументы для функции SolverDelete таким же пользователям, которым используется с функциями SolverChange и SolverAdd .

Следующий макрос показывает, как удалить и добавить ограничение. В этом примере макрос Change_Constraint_and_Solve2 удаляет ограничение E3:E7<=B3:B7 from="" the="" product="" mix="" with="" diminishing="" returns="" model="" and="" adds="" a="" new="" constraint.="" the="" new="" constraint="" is="" just="" a="" modification="" of="" the="" original="" constraint,="" where="" the="" left="" and="" right="" sides="" of="" the="" constraint="" are="" reversed.=""></=B3:B7>

Ниже описаны макрос Change_Constraint_and_Solve2 .
    Sub Change_Constraint_and_Solve2()

    ' Reverse the left and right sides of the constraint...
    ' Delete the constraint E3:E7<=B3:B7 and add the
    ' constraint B3:B7>=E3:E7.
    SolverDelete CellRef:=Range("E3:E7"), Relation:=1, _
        FormulaText:="$B$3:$B$7"
    SolverAdd CellRef:=Range("B3:B7"), Relation:=3, _
        FormulaText:="$E$3:$E$7"
    
    ' Return the results and display the Solver Results dialog box.
    SolverSolve UserFinish:=False
    
    End Sub

Примечание В Microsoft Excel версии 5.0 и 7.0, нотация R1C1 При указании на ячейку или диапазон ячеек в аргументе FormulaText . Напротив в Microsoft Excel 97 с помощью стиля A1 нотация для задания аргумента FormulaText .

Загрузка и сохранение моделей

При сохранении книги, последними параметрами, можно указано в Поиск решения диалоговое окно сохраняются вместе с книга. Таким образом, при открытии книги, параметры являются Аналогично после последнего сохранения рабочей книги.

Можно определить более чем одна из проблем для листа. Каждая проблема состоит из ячеек и ограничения, введенные в Параметр "Поиск решения" и Параметры поиска решения диалоговые окна. Так как только последняя проблема сохраняется вместе с лист, будут потеряны все проблемы без специального сохранения их. Чтобы сохранить их, нажмите кнопку Сохранить модель в Поиск решения Параметры диалоговое окно. Аналогично, если вы хотите восстановить ранее сохранить параметры, нажмите кнопку Загрузить модель в Поиск решения Параметры диалоговое окно.

Сохраненные в самых разных моделей поиска решения ячейки на листе. Формула содержит первую ячейку в диапазоне Целевая ячейка. Содержит формулу, которая определяет вторую ячейку в диапазоне изменяемых ячеек в модели. Массив содержит последнюю ячейку в диапазоне Представляет параметры, установленные в Параметры поиска решения диалоговое окно поле. Ячейки между вторую ячейку и последней ячейки содержат формулы что представляют ограничения, накладываемые в модели.

На рисунке 8 показана модель для планирования сотрудника. Предположим, что работать для небольших Изготовитель. В данной таблице перечислены почасовая ставка оплаты, номер каждого сотрудника часов запланированных и планируемое количество единиц каждого сотрудника можно привести в один час. Ваша цель — в соответствии с конкретной квоты для количества единиц произведено при одновременном снижении затрат на трудовые ресурсы.

На рисунке 8. Сотрудник Режим планирования
l
Свернуть это изображениеРазвернуть это изображение
 На рисунке 8. Планирование сотрудника

		  модель


Два дополнительных факторов (или ограничения), вы должны Рассмотрим ряд Минимальное/максимальное число часов работы любого одного сотрудника и число единиц, которые планируется создать. Если для указанной недели, вы должны Для получения 3975 единиц и необходимо каждому сотруднику работать от 30 до 45 часы параметры поиска решения Microsoft Excel будет выглядеть аналогично описанным в следующих Таблица:

Свернуть эту таблицуРазвернуть эту таблицу
ПараметрДиапазон ячеекОписание
Целевая ячейка $D$ 12 Затраты труда.
Изменяемые ячейки $C$ 2:$ C$ 8 Часы трудозатрат за Сотрудник.
Ограничения $C$ 2:$ C$ 8<=></=> Максимум часов на одного сотрудника — 45.
$C$ 2:$ C$ 8 НАСТРОЕК = 30 Минимальное число часов для Сотрудник составляет 35.
$G$ 12 = 3975 Количество единиц составляет 3975.


Ваши цели, решения для оптимального затраты на еженедельно выполняем еженедельно сохранение каждой модели и иметь возможность загружать любые еженедельно модель, когда она нужна.

В макросе, параметры поиска решения Microsoft Excel для модели можно сохранять и загружать с помощью функции SolverLoad и SolverSave соответственно. Имеют следующие функции SolverLoad и SolverSave синтаксис:

SolverSave (SaveArea)

SolverLoad (LoadArea)

SolverSave и SolverLoad функции имеют только один аргумент, SaveArea и LoadArea аргументы соответственно. Эти аргументы задают диапазон на листе где хранятся сведения о модели.

Следующий макрос New_Employee_Schedule показано, как создать, решения и сохранить модель, основанная на входе пользователя. Пользователю выдается запрос на ввод даты модели количество единицы измерения для производства и минимальное и максимальное количество часов на одного сотрудника. Эти данные затем используется для создания модели. Устранены модели и затем Сохранить ввода информации пользователем.

Ниже описаны макрос New_Employee_Schedule .
    Sub New_Employee_Schedule()
    
    ' Prompt the user for the date of the model, the units to produce,
    ' and the maximum and minimum number of hours per employee.
    ModelDate = Application.InputBox( _
       Prompt:="Date of Model:", Type:=2)
    Units = Application.InputBox( _
       Prompt:="Projected Number of Units:", Type:=1)
    MaxHrs = Application.InputBox( _
       Prompt:="Maximum Number of Hours Per Employee:", Type:=1)
    MinHrs = Application.InputBox( _
       Prompt:="Minimum Number of Hours Per Employee:", Type:=1)
    
    ' Clear any previous Solver settings.
    SolverReset
    
    ' Set the target cell, D12, to a minimum value by changing
    ' the range, C2:C8.
    SolverOk SetCell:=Range("$D$12"), MaxMinVal:=2, _
       ByChange:=Range("C2:C8")
    
    ' Add the constraint that number of hours worked <= MaxHrs.
    SolverAdd CellRef:=Range("C2:C8"), Relation:=1, FormulaText:=MaxHrs
    
    ' Add the constraint that number of hours worked >=MinHrs.
    SolverAdd CellRef:=Range("C2:C8"), Relation:=3, FormulaText:=MinHrs
    
    ' Add the constraint that number of units produced = Units.
    SolverAdd CellRef:=Range("G12"), Relation:=2, FormulaText:=Units
    
    ' Solve the model and keep the final results.
    SolverSolve UserFinish:=True
    SolverFinish KeepFinal:=1
    
    ' Save the input values for ModelDate, MaxHrs, MinHrs, and Units
    ' in columns I:L.
    Set ModelRange = Range("I2:R2").CurrentRegion.Offset( _
       Range("I2:R2").CurrentRegion.Rows.Count).Resize(1, 1)
    ModelRange.Resize(1, 4) = Array("'" & Format(ModelDate, "m/d/yy"), _
       Units, MaxHrs, MinHrs)
    
    ' Save the model parameters to the range M:R in the worksheet.
    SolverSave SaveArea:=ModelRange.Offset(, 4).Resize(1, 6)
    
    End Sub
Примечание В Microsoft Excel версии 5.0 и 7.0, нотация R1C1 При указании на ячейку или диапазон ячеек в аргументе FormulaText . Напротив в Microsoft Excel 97 с помощью стиля A1 нотация для задания аргумента FormulaText .

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

На рисунке 9. Сведения о модели сохраненную в макросе New_Employee_Schedule

Свернуть это изображениеРазвернуть это изображение
На рисунке 9. Сведения о модели, которая сохраняется

		  в макросе New Employee Schedule


Каждая новая модель сохраняет макрос New_Employee_Schedule на лист. Макрос Load_Employee_Schedule можно загрузить одну из этих моделей сохраненные. Предлагает макроса пользователь для загрузки, а затем поиск столбцов модели я даты модели. Если найти Дата модели макрос Load_Employee_Schedule загружает соответствующей модели, решает ее, и затем сохраняет окончательные результаты.

Ниже описаны макрос New_Employee_Schedule.
    Sub Load_Employee_Schedule()
    
    ' Prompt for the date of the model. 
    ModelDate = Application.InputBox( _
       Prompt:="Date of Model to Load:", Type:=2)
    
    ' Locate the date in column I.
    Set DateRange = Range("I2").CurrentRegion.Resize(, 1)
    r = Application.Match(ModelDate, DateRange, 0)
    
    If IsError(r) Then
        ' Display a message if the model date is not found
        MsgBox "Cannot find a model with the date " & ModelDate
    Else
        ' If the model date is found, load the model into Solver,
        ' solve the model, and keep the final results.
        SolverLoad LoadArea:=DateRange.Offset(r - 1, 4).Resize(1, 6)
        SolverSolve UserFinish:=True
        SolverFinish KeepFinal:=1
    End If
    
    End Sub
Макрос New_Employee_Schedule представляет функцию SolverReset . SolverReset функция может использоваться для удаления всех выбранных ячеек и ограничения Поиск решения диалоговое окно и сбросить все параметры Функция SolverReset не имеет. аргументы.

Как найти дополнительные сведения о поиске решения Microsoft Excel

Следующие ресурсы содержат сведения об использовании Microsoft Excel "Поиск решения" надстройка.

  • Для получения справки по поиск конкретного сообщения см.Frontline Систем.
  • Советы по разработке для чтения управляемые моделями смFrontline Систем.
  • Для получения дополнительных сведений о поиске решения ограничивает ограничений и щелкните следующий номер статьи базы знаний Майкрософт:
    75714Ограничения поиска решения для ограничения
  • Некоторые примеры использования надстройки "Поиск решения" Microsoft Excel в Microsoft В приложении Excel, содержатся в файле Solvsamp.xls образца.
  • Ниже приведен пример файла, который входит в состав расположение по умолчанию Microsoft Excel 97:
    \Program Files\Microsoft Office\Office\Examples\Solver\SolvSamp.xls
  • Ниже приведен пример файла, который входит в состав расположение по умолчанию Microsoft Excel 7.0:
    \MSOffice\Excel\Examples\Solver\SolvSamp.xls
  • Ниже приведен пример файла, который входит в состав расположение по умолчанию Microsoft Excel 5.0:
    \Excel\Examples\Solver\SolvSamp.xls

Как узнать больше о алгоритмов и методов, используемых Microsoft Excel поиска решения

Поиск Microsoft Excel использует обобщенные ограниченной градиента Код нелинейной оптимизации (GRG2), разработанный Леон Lasdon, Университет Техас в Остине и Allan Waren, государственный университет Кливленда.

Дополнительные сведения об алгоритме поиска решения Microsoft Excel щелкните следующий номер статьи базы знаний Майкрософт:
82890"Поиск решения" использует обобщенные ограниченной


Использование линейных и целочисленных задач симплексный метод с границами на переменные и ветви и привязкой метод реализуется John Watson и Дэн Филстра, Frontline Systems, Inc. Дополнительные сведения о внутренние решения процесс поиска решения, контакт:

Frontline Systems, Inc.
P.O. Box 4288
Incline Village, NV 89450-4288 
(702) 831-0300
Web site: http://www.frontsys.com
Electronic mail: info@frontsys.com 


Поиск Microsoft Excel, выбранных программный код, об авторских правах 1990, 1991, 1992 и 1995 первую системами Участки Inc., об авторских правах 1989 оптимальная методы, Inc.

Примечание В Microsoft Excel надстройка, описанное в данной статье предоставляется «как есть», и мы не гарантирует, что он может использоваться во всех ситуациях. Хотя корпорация Майкрософт поддерживает Специалисты могут помочь с установкой и существующих функций надстройки, они не изменит предоставляют новые функциональные возможности надстройки.

НИКАКИХ гарантий. Программное обеспечение предоставляется "как-," без гарантий любого рода, и любое использование данного программного обеспечения на ваш собственный риск.

Свойства

Код статьи: 843304 - Последний отзыв: 29 марта 2013 г. - Revision: 7.0
Информация в данной статье относится к следующим продуктам.
  • Microsoft Excel 97 Standard Edition
Ключевые слова: 
kbhowto kbmacroexample kberrmsg kbaddin kbvba kbprogramming kbinfo kbmt KB843304 KbMtru
Переведено с помощью машинного перевода
ВНИМАНИЕ! Перевод данной статьи был выполнен не человеком, а с помощью программы машинного перевода, разработанной корпорацией Майкрософт. Корпорация Майкрософт предлагает вам статьи, переведенные как людьми, так и средствами машинного перевода, чтобы у вас была возможность ознакомиться со статьями базы знаний KB на родном языке. Однако машинный перевод не всегда идеален. Он может содержать смысловые, синтаксические и грамматические ошибки, подобно тому как иностранец делает ошибки, пытаясь говорить на вашем языке. Корпорация Майкрософт не несет ответственности за неточности, ошибки и возможный ущерб, причиненный в результате неправильного перевода или его использования. Корпорация Майкрософт также часто обновляет средства машинного перевода.
Эта статья на английском языке: 843304
Заявление об отказе относительно содержимого статьи о продуктах, поддержка которых прекращена
Эта статья содержит сведения о продуктах, поддержка которых корпорацией Майкрософт прекращена. Поэтому она предлагается как есть и обновляться не будет.

Отправить отзыв

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com