Последовательность выполнения практического задания. Цель работы – рассмотреть принципы работы с надстройкой «поиск решения» в электронных таблицах MS Excel для решения экономических задач

ЦЕЛЬ РАБОТЫ

Цель работы – рассмотреть принципы работы с надстройкой «Поиск решения» в электронных таблицах MS Excel для решения экономических задач.

ЗАДАНИЕ

Задача: Фирма выпускает ковбойские шляпы двух фасонов. Трудоемкость изготовления шляпы фасона 1 вдвое выше трудоемкости изготовления шляпы фасона 2. Если бы фирма выпускала только шляпы фасона 1, суточный объем производства мог бы составить 60 шляп. Суточный объем сбыта шляп обоих фасонов ограничен диапазоном от 50 до 100 штук. Прибыль от продажи шляпы фасона 1 равна $8, а фасона 2 - $5. Определить, какое количество шляп каждого фасона следует изготавливать, чтобы максимизировать прибыль.

Для решения поставленной задачи необходимо создать приложение в Visual Basic for Application, которое будет представлять собой шаблон решения задачи. В приложении необходимо на панель быстрого доступа добавить кнопку, при нажатии на которую открывается форма «Расчет максимальной прибыли». На форме разместить поля для ввода значения прибыли от продажи шляпы фасона 1 и фасона 2, кнопку «Поиск решения» и кнопку «Сформировать отчет».

ТЕОРЕТИЧЕСКИЕ СВЕДЕНИЯ

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

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

ПОСЛЕДОВАТЕЛЬНОСТЬ ВЫПОЛНЕНИЯ ПРАКТИЧЕСКОГО ЗАДАНИЯ

Наше приложение будет состоять из рабочей книги MS Excel и формы «Расчет максимальной прибыли», на которой будут размещены следующие объекты: кнопки «Поиск решения» и «Сформировать отчет», объекты Label, объекты TextBox, в которые будем вводить значения прибыли от продажи шляпы фасона 1 и фасона 2.

При открытии рабочей книги появляется шаблон, в который пользователь вводит все необходимые исходные данные (исходя из условия задачи). На панели быстрого доступа появляется кнопка, при нажатии на которую открывается форма «Расчет максимальной прибыли».

При нажатии кнопки «Поиск решения» вызывается надстройка «Поиск решения». При нажатии кнопки «Сформировать отчет» создается документ MS Word.

1. Постановка задачи. Прибыль от продажи шляп обоих фасонов будет рассчитываться по формуле: прибыль от продажи шляпы фасона 1 * количество шляп фасона 1 + прибыль от продажи шляпы фасона 2 * количество шляп фасона 2.

Суточный объем сбыта шляп обоих фасонов рассчитывается по формуле: количество шляп фасона 1 + количество шляп фасона 2.

Исходя из того, что трудоемкость изготовления шляпы фасона 1 вдвое выше трудоемкости изготовления шляпы фасона 2, общая производительность будет рассчитываться как: количество шляп фасона 1 + 0,5 * количество шляп фасона 2.

Для решения задачи будем использовать надстройку «Поиск решения». В качестве целевой функции будет максимальная прибыль, которую будем вычислять, изменяя значения ячеек, содержащих количество шляп фасона 1 и фасона 2. Необходимо ввести следующие ограничения: количество шляп фасона 1 и фасона 2 должно быть целым числом и больше либо равно 0, суточный объем сбыта шляп должен быть больше либо равен 50, но меньше либо равен 100. Общая производительность должна быть меньше либо равна 60.

2. Создадим рабочим книгу MS Excel. Добавим на панель быстрого доступа кнопку, при нажатии на которую будет открываться форма «Рассчитать максимальную прибыль». Создадим шаблон для ввода данных.

3. Создадим форму «Расчет максимальной прибыли». Выберем команду Insert – UserForm. Разместим на ней все необходимые объекты и определим для них все необходимые параметры. Форма примет следующий вид:

4. Напишем код программы:

Обработчик события нажатия кнопки на панели быстрого доступа

Sub Макрос1()

UserForm1.Show

End Sub

Обработчик события нажатия кнопки «Поиск решения»

Необходимо подключить библиотеку «SOLVER»

Private Sub CommandButton1_Click()

Range("C2").Value = TextBox1.Value

Range("C3").Value = TextBox2.Value

SolverReset

SolverOk SetCell:="$B$7", MaxMinVal:=1, ValueOf:="0", ByChange:="$B$2:$B$3"

SolverAdd CellRef:="$B$2:$B$3", Relation:=3, FormulaText:="0"

SolverAdd CellRef:="$B$2:$B$3", Relation:=4, FormulaText:="öåëîå"

SolverAdd CellRef:="$B$5", Relation:=3, FormulaText:="50"

SolverAdd CellRef:="$B$5", Relation:=1, FormulaText:="100"

SolverAdd CellRef:="$B$6", Relation:=1, FormulaText:="60"

SolverSolve

End Sub

Обработчик события нажатия кнопки «Сформировать отчет»

Необходимо подключить библиотеку «Microsoft Word 12.0»

Private Sub CommandButton2_Click()

Dim myWord As New Word.Application

myWord.Visible = True

Set myDoc = myWord.Documents.Add

myWord.Selection.ParagraphFormat.Alignment = wdAlignParagraphCenter

myWord.Selection.Font.Name = "Times New Roman"

myWord.Selection.Font.Size = 14

myWord.Selection.TypeText "Отчет"