Один из подходов к созданию приложения на языке VBA в EXCELс применением процедуры Поиск решения

В.И. Болгов

Саратовский ГАУ им. Н. И. Вавилова, г. Саратов

По некоторым оценкам (Гарнаев А.Ю. Microsoft Excel 2000. Разработка приложений. – СПб.: БХВ–Санкт-Петербург, 2000. – 576 с.; ил.), примерно 90 % от общего числа пользователей работают только со средствами рабочего листа Excel без применения языка программирования VBA, а следовательно, они используют менее 10 % реальных возможностей приложения Excel. Таким образом, большая часть средств и возможностей Excel остается невостребованной просто потому, что многие не знают о наличии таких возможностей или не умеют ими пользоваться.

Чтобы использовать некоторые из этих невостребованных возможностей, надо научиться создавать свои приложения на встроенном в приложение
Excel языке программирования VBA. В данной статье автор показывает один из подходов к созданию такого приложения на примере использования встроенной в приложение Excel
процедуры «Поиск решения» при решении задач оптимизации. На рисунке дается пример задачи с применением данной процедуры.

Пример задачи получения максимальной прибыли

Магазин фирмы ведет учет покупателей и стоимость сделанных ими покупок. Вычислена средняя стоимость покупки (ячейка G2). В целях привлечения большего количества покупателей в магазине разработана реклама в виде выдачи призов за большую покупку. Чтобы поддерживать интерес клиента к магазину, покупателю посылается открытка, по которой он может получить приз от магнитика до футболки. Чем лучше приз, тем больше вероятность, что покупатель еще раз посетит магазин.

В столбце А перечислены названия призов, в столбце В – их стоимость, в столбце С – количество призов. Для упрощения задачи в таблице не учтены стоимость открыток, затраты на печать открыток, стоимость рассылки. Сумма продажи для конкретного вида приза зависит не от количества отосланных открыток, а от количества отзывов на них (столбец D), и определяется как разность между выручкой от продажи и стоимостью призов за сделанные покупки по формуле =($G$2-B3)*C3*D3). Вычитая стоимость выданных призов из общей выручки от продажи, получим чистую прибыль.

Для получения максимальной прибыли от продажи с учетом призов применяем процедуру «Поиск решения». Установив курсор на целевую ячейку Е8, в которой должна быть формула получения максимальной прибыли от продаж, выполняем команду Сервис – Поиск решения. Появляется одноименное диалоговое окно, в котором необходимо установить соответствующие параметры и ограничения. Такими ограничениями в данной задаче могут быть значения расходов на призы (ячейка В10) и количество отправляемых открыток (ячейка D8), а также и другие ограничения для решения данной задачи.

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

При повторном запуске процедуры «Поиск решения» можно установить другие значения ограничений с целью получения другого лучшего результата максимальной выручки от продаж. С целью сокращения времени многократного запуска на решение одной и той же задачи целесообразно записать макрос, который будет вызываться на исполнение или нажатием комбинации клавиш, или нажатием нарисованной кнопки на рабочем листе. Однако при попытке запуска макроса на повторное решение с применением процедуры «Поиск решения» макрос на исполнение не вызывался. Появилось диалоговое окно «Ошибка» с кнопкой «Справка». Вызванная на экран справка ясного ответа не дала. Попытка описать первую функцию процедуры «Поиск решения» результата не дала. После вызова общей справки по процедуре SOLVER (Поиск решения) стало видно, что необходимо изучать справки по всем функциям, входящим в процедуру «Поиск решения».

SolverReset – у функции нет параметров. Устанавливает значения параметров «Поиск решения», используемые по умолчанию.

SolverOptions (MaxTime, Iterations, AssumeLinear, StepThru, Estimates, Derivatives, Search, IntTolerance, Scaling, Convergence, AssumeNonNeg) – задает значения тех параметров, которые вручную устанавливаются в окне Параметры поиска решения.

SolverAdd (CellRef, Relation, FormulaText) – позволяет добавлять ограничения в модель. Задает значения тех параметров, которые вручную устанавливаются в окне Добавление ограничения.

SolverOk (SetCell, MaxMinVal, ValueOf, ByChange) – задает значения тех параметров, которые вручную устанавливаются в окне Поиск решения. Ставит задачу оптимизации.

SolverSolve (UserFinish, ShowRef) – запускает процедуру на решение.

SolverSave (SaveArea) – сохраняет модель. Параметр функции задает диапазон ячеек, в котором сохраняется модель.

SolverDelete (CellRef, Relation, FormulaText) – удаляет ограничения, если список ограничений оптимизационной задачи уже задан.

SolverChange (CellRef, Relation, FormulaText) – изменяет ограничения в заданном списке ограничений.

В справке по каждой из этих функций есть общий текст. Перевод его подсказал, что до использования перечисленных функций необходимо установить ссылку на файл Solver. xla в редактореVisual Basic в окне References, отображаемого на экране, выбором команды Tools – References. Если файл Solver. xla
отсутствует в списке Avialable References, то необходимо нажать кнопку Browse и открыть файл Solver. xla из папки OfficeLibrarySolver. После установки такой ссылки запуск программы осуществился. Но только в той версии Microsft Excel, где эта установка осуществлялась. Скопированный файл всей задачи на компьютере с другой версией пакета Microsoft Office не заработал. Программа не запустилась.

Программу созданного приложения удалось запустить на другом компьютере с одноименным пакетом Microsoft Office XP но другой версии следующим образом. После запуска программы Microsоft Excelсоздали пустую Книгу1. В эту книгу раздельно скопировали таблицу задачи и текст программы. Текст программы вставляли в уже созданный на другом компьютере макрос и затем его редактировали.

Далее приведен текст фрагмента программы для ввода количества призов. Ввод количества каждого вида приза осуществляется в цикле. Послезавершения цикла формируются переменные
fut, kup, ruch, которые в дальнейшем в программе процедуры «Поиск решения» будут использоваться при формировании ограничений.

Case «Количество призов»

‘Заполнение колонки «Количество»

Range («C3»). Select ‘Активизация ячейки. C3

l = ActiveCell. Row ‘Присвоение переменной «L» значения номера строки

m = ActiveCell. Column ‘Присвоение переменной «m» значения номера столбца

c = «Введите количество призов данного вида» ‘Присвоение переменной «c» значения «Текст»

For i = l To 7
‘Организация ввода исходных данных в цикле

Cells (i, m). Activate ‘Активизация ячейки с координатами (i,m)

priz = ActiveCell. Offset (0, – 2). Value ‘Присвоение переменной priz названия приза

cen = InputBox$(priz, c, 2, 6000, 4500) ‘Присвоение переменной cen значения с клавиатуры

ActiveCell. Value = cen ‘Присвоение активной ячейке значения переменной cen

Next i

fut = Cells (3, 3)

kup = Cells (4, 3)

ruch = Cells (5, 3)

Range («A1»). Select ‘Активизация ячейки A1

MsgBox («Конец выбранного режима работы») ‘Вывод на экран текста в окно функции вывода.

В цикле используется функция Offset (0, – 2), которая позволяет в окне функции ввода InputBox
() видеть название приза, количество которого вводим.

Ниже приведен фрагмент программы, в котором видно, как с помощью переменных fut, kup, ruch можно изменять ограничения при повторном запуске программы приложения с целью получения другого результата решения задачи оптимизации.

‘Вызов процедуры «Поиск решения»

SolverReset ‘Стирание установок и ограничений «Поиска решения»

SolverAdd CellRef: ="$B$10», Relation: =2, FormulaText: = vv1

SolverAdd CellRef: ="$C$3:$C$7», Relation: =4, FormulaText:=«целое»

SolverAdd CellRef: ="$C$3», Relation: =3, FormulaText: = fut

SolverAdd CellRef: ="$C$4», Relation: =3, FormulaText: = kup

SolverAdd CellRef: ="$C$5», Relation: =3, FormulaText: = ruch

SolverAdd CellRef: ="$C$3:$C$7», Relation: =3, FormulaText: =«0»

SolverAdd CellRef: ="$C$8», Relation: =2, FormulaText:= vvod

SolverOptions MaxTime: =100, Iterations: =100, precision: =0.000001,

AssumeLinear: =True, StepThru: =False, Estimates: =1, Derivatives: =1,_

SearchOption: =1, Convergence: =0.0001, AssumeNonNeg: =False

SolverOk SetCell: ="$E$8», MaxMinVal: =1, ValueOf:=«1», ByChange:="$C$3:$C$7»

SolverSolve

Похожие статьи:

1. Один из методов решения задач унификации
2. Сследование возможностей табличного процессора MSEXCEL для решения финансовых задач
3. Программа построения дискретных и интервальных вариационных рядов распределения на языке VBA
4. АВТОМАТИЗАЦИЯ РАБОТЫ С ДОКУМЕНТАМИ В ПРИЛОЖЕНИИ MICROSOFTWORD НА ОСНОВЕ РАЗРАБОТКИ ПРОГРАММЫ НА ЯЗЫКЕ VISUALBASIC
5. ИССЛЕДОВАНИЕ ПРОЦЕССА ПРОРАСТАНИЯ ПШЕНИЦЫ С ПРИМЕНЕНИЕМ МЕТОДА ХЕМИЛЮМИНЕСЦЕНТНОГО АНАЛИЗА