Реферат: Задание на нахождение оптимального раскроя 25 4 База данных 27

СОДЕРЖАНИЕ


ВВЕДЕНИЕ 4

1 ЭКОНОМИЧЕСКАЯ ПОСТАНОВКА ЗАДАЧИ 5

2 МАТЕМАТИЧЕСКАЯ ПОСТАНОВКА ЗАДАЧИ 6

3 ВЫБОР МЕТОДА РЕАЛИЗАЦИИ МОДЕЛИ. ОБОСНОВАНИЕ ВЫБОРА 7

4 ТЕХНИЧЕСКИЕ И ИНСТРУМЕНТАЛЬНЫЕ СРЕДСТВА ОБЕСПЕЧЕНИЯ ЗАДАЧИ 13

4.1 Краткая характеристика ЭВМ и ее программного обеспечения 13

4.2 Обоснование выбора языка программирования 14

4.3 Схема алгоритма и его описание 16

5 РЕШЕНИЕ ЗАДАЧИ-ТЕСТА ДЛЯ НАПИСАНИЯ И ОТЛАДКИ ПРОГРАММЫ 18

6 АНАЛИЗ ПОЛУЧЕННЫХ РЕЗУЛЬТАТОВ. 19

7 ИНСТРУКЦИЯ ПОЛЬЗОВАТЕЛЮ И ОПИСАНИЕ ПРОГРАММЫ 20

a. Описание переменных 20

b. Входные и выходные данные 21

c. Подробное описание задач 22

7.3.1 Составление ведомости расчета прибыли от товара 22

7.3.2 Модель управления запасами 23

7.3.3 Задание на нахождение оптимального раскроя 25

7.3.4 База данных 27

d. Описание интерфейса 28

e. Функция пользователя 30

f. Переменные и постоянные 31

g. Стандартные функции пользователя для работы с массивами и матрицами 31

h. Объекты, свойства и методы VBA 32

i. Операторы цикла 32

ЗАКЛЮЧЕНИЕ 34

СПИСОК СОКРАЩЕНИЙ 35

СПИСОК ЛИТЕРАТУРЫ 36

ПРИЛОЖЕНИЯ 37

ПРИЛОЖЕНИЕ 1 37

ПРИЛОЖЕНИЕ 2 38



ВВЕДЕНИЕ


Задачей данной курсовой работы является составление документа в среде Microsoft Excel включающего в себя программы написанные на языке Microsoft Visual Basic решающие 8 поставленных задач, а именно начисление премии в виде коммисионных и дополнительной оплаты, начисление премиальных по определенным условиям, модель управления запасами, задача об оптимальных капиталовложениях, задачи на построение матрицы и вычисление суммы, нахождение бумажного раскроя и база данных.

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

Если говорить о среде написания программ для решения поставленных задач - Microsoft Visual Basic for Application – то это сочетание одного из самых простых языков программирования и всех вычислительных возможностей такой многогранной системы как Excel. С помощью VBA можно легко и быстро создавать разнообразные приложения даже не являясь специалистом в области программирования. VBA содержит относительно мощную графическую среду, позволяющую наглядно конструировать экранные формы и управляющие элементы. В общем Visual Basic for Application позволяет с легкостью решать многие задачи.
^ ЭКОНОМИЧЕСКАЯ ПОСТАНОВКА ЗАДАЧИ


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


^ МАТЕМАТИЧЕСКАЯ ПОСТАНОВКА ЗАДАЧИ


1) Составление ведомости расчета прибыли от товара.

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

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


^ 2) Модель управления запасами

Функция пользователя CALC вычисляющая финансовые исходы при всевозможных вариантах событий покупки журналов и их реализации. В функции используются следующие переменные: Цена покупки, Цена продажи, Цена возврата.


^ 3) Задание на нахождение оптимального раскроя

Для построения математической модели в программе производится перебор всевозможных вариантов раскроя рулона стандартной длины на рулоны требуемой длины.


^ Логическая схема расчета симплекс методов

Формирование целевой функции и определение ограничивающим условиям




Преобразование ограничений из неравенств в систему равенств




Построим симплекс таблицы




Нахождение опорного плана




Проверка, если да решение найдено

нет

Выбор разрешающего столбца




Все ли (отрицательные?) –да L – неограничен (расчета нет)

нет

Выбор разрешающего столбца




Пересчет элементов



^ ВЫБОР МЕТОДА РЕАЛИЗАЦИИ МОДЕЛИ. ОБОСНОВАНИЕ ВЫБОРА


Пусть мы имеем случай, когда ранг системы меньше числа неизвестных тогда выберем k – переменных в качестве свободных элементов (Х1,Х2,…Хk), а остальные базисные выразим через свободные элементы.





Прировняем к 0 свободные элементы Х1=0, Х2=0, Хк=0 получим решение



Если все значения β не отрицательна то мы получим допустимое решение, такое решение называется опорным. Нам надо выяснить будет ли оно оптимальным чтобы проверить это подставим свободные переменные в функцию L получим:



При Х1 =Х2 =…=0 получим L=j0

Надо выяснить можно улучшить полученное решение, то есть уменьшить (L) увеличивая какую ни будь переменную Х1,Х2…Хn

Может быть два случая:

Если все коэффициенты J1,J2…Jk положительно то мы не сможем уменьшить (L) и найденное решение будет оптимальным.

Если среди коэффициентов J1,J2…Jk есть отрицательный элемент то увеличивая при нем (Х) мы можем улучшить (L).


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


Таблица 1






Свободный член

X1

X2

X3

X4

Y1

B1

α 11

α 12

α 13

α 14

Y 2

B2

α 21

α 22

α 23

α 24

Y 3

B3

α 31

α 32

α 33

α 34

Y 4

B4

α 41

α 42

α 43

α 44

Y 5

B5

α 51

α 52

α 53

α 54








Выполняя операцию X2 ↔ Y3, мы хотим в разрешающей строке поместить переменную Y3, а в разрешающем столбце – переменную X2 (это отмечено в таблице 1).

Найдем коэффициенты, которые нужно будет представить в таблице после обмена X2 ↔ Y3. начнем с преобразования разрешающей строки. Решая уравнение относительно Х2, получим:




Таким образом, преобразованные элементы разрешающей строки найдены. Составим правило преобразования остальных строк. После приведения членов получим:





Нетрудно убедится, что совершенно аналогичным образом преобразовываются все остальные строки. В результате мы получим преобразованную таблицу (смотри таблицу 3), в которой операция X2 ↔ Y3 уже совершенна.


Рассмотрев таблицу 2, мы можем так сформулировать алгоритм преобразования коэффициентов стандартной таблицы.

Разрешающий элемент заменяется на обратную ему величину.

Все остальные элементы разрешающей строки делятся на разрешающий элемент.

Все элементы разрешающего столбца (кроме самого разрешающего элемента) меняют знак и делятся на разрешающий элемент.

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


Таблица 2





Свободный член

X1

Y3

X3

X4

Y1











Y2











X2











Y4











Y5












Рассмотрев таблицу 2, мы можем так сформулировать алгоритм преобразования коэффициентов стандартной таблицы.

Разрешающий элемент заменяется на обратную ему величину.

Все остальные элементы разрешающей строки делятся на разрешающий элемент.

Все элементы разрешающего столбца (кроме самого разрешающего элемента) меняют знак и делятся на разрешающий элемент.

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


Алгоритм преобразования Xj↔ Yi стандартной таблицы сводится при этом к следующим операциям.

Выделить в таблице разрешающий элемент αij. Вычислить его обратную величину λ=1/ αij и записать в нижней части той же ячейки (в правом нижнем углу).

Все элементы разрешающей строки (кроме самого αij) умножить на λ; результат записать в нижней части той же ячейки.

Все элементы разрешающего столбца (кроме самого αij) умножить на – λ; результат записать в нижней части той же ячейки.

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

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

Переписать таблицу, заменив;

Xj на Yi и обратно,

Элементы разрешающей строки и столбца – числами, стоящими в нижних частях той же ячейки,

Каждый из остальных элементов заменить суммой чисел стоящих в верхней и нижней части той же ячейки.


В задаче линейного программирования, кроме уравнений-ограничений, существует еще и линейная функция



которую нужно минимизировать. Если эта функция выражена через прежние свободные переменные X1,X2,…,Xn, то, очевидно, после замены Xj↔ Yi ее нужно выразить через новые свободные переменные X1, X2,…, Xj-1, Yi, Xj+1,…, Xn. Нетрудно убедится, что для этого может быть применен тот же алгоритм, что и для преобразования любой строки стандартной таблицы. Приводя L к стандартной форме

,

где Y1= - c1

Y2= - c2

………

Yn= -cn

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

Нахождение решения каждой задачи линейного программирования распадается на два этапа:

Отыскание опорного решения;

Отыскание оптимального решения, минимизирующего линейную функцию L.


отыскиние опорного решения основной задачи линейного программирования.


Пусть имеется ОЗЛП с ограничениями – равенствами, записанными в стандартной форме:

(1) (обращение в тексте)


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

Имеем:

X1 = X2 = … = Xn = 0;

Y1 = b1;

Y2 = b2;

………………………

Ym = bm.

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

Существует ряд способов выбора разрешающего элемента для приближения к опорному решению.


отыскание оптимального решения основной задачи линейного программирования. (озлп)


в предыдущем разделе искали опорное решение системы уравнений ОЗЛП. Теперь мы будем заниматься оптимизацией решения, то есть отысканием такого опорного решения, которое обращает в минимум линейную функцию.



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

Правила нахождения оптимального решения ОЗЛП симплекс – методом.

если все свободные члены в симплекс таблице не отрицательны, а в строке L нет ни одного положительного элемента, то оптимальное решение достигнуто.

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

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


В заключении остановимся на так называемом «вырожденном» случае, когда один (или более) свободных членов в уровнениях-ограничениях получается равным нулю. Это означает, что в данном опорном решении обращаются в нуль не только свободные переменные, но и некоторые из базисных.


^ ТЕХНИЧЕСКИЕ И ИНСТРУМЕНТАЛЬНЫЕ СРЕДСТВА ОБЕСПЕЧЕНИЯ ЗАДАЧИ



Краткая характеристика ЭВМ и ее программного обеспечения


Таблица 3 – Характеристика ЭВМ и ПО


Компьютер:

Операционная система

Пакет обновления ОС

Internet Explorer

Имя компьютера

Имя пользователя

Вход в домен


Системная плата:

Тип ЦП

Системная плата


Чипсет системной платы

Системная память

Тип BIOS

Коммуникационный порт

Коммуникационный порт

Коммуникационный порт


Дисплей:

Видеоадаптер

3D-акселератор

Монитор


Мультимедиа:

Звуковой адаптер


Хранение данных:

Флоппи-накопитель

Дисковый накопитель

Дисковый накопитель

Оптический дисковод

Оптический дисковод


Разделы:


Ввод:

Клавиатура

Мышь


Сеть:

Первичный адрес IP

Первичный адрес MAC

Модем


Периферийные устройства:

Принтер

USB-устройство


Microsoft Windows XP Professional

Service Pack 2

6.0.2900.2180

SDIMON (SDimon)

SDimon

SDIMON


Intel Celeron-A, 440 MHz (5.5 x 80)

PCPartner AP133AS1/AP133AS3/AP133BS1/AP133BS3/AP133MS1/AP133MS3/APPAS1/APPAS3/APPBS1/APPBS3/APPMS1/APPMS3/VIA955D/VIB887D / A-Trend ATA-V930-SX0 / Octek Rhino i845GL-M4E

VIA VT82C693A Apollo Pro133

128 Мб (SDRAM)

Award Modular (10/09/00)

Последовательный порт (COM1)

Последовательный порт (COM2)

Порт принтера (LPT1)


NVIDIA GeForce2 MX/MX 400 (64 Мб)

nVIDIA GeForce2 MX/MX 400

LG StudioWorks 575N


C-Media CMI8738/C3DX Audio Device


Дисковод гибких дисков

ST34313A (4 Гб, 5400 RPM, Ultra-ATA/66)

ST380011A (80 Гб, 7200 RPM, Ultra-ATA/100)

_NEC CD-RW NR-9100A (40x/10x/40x CD-RW)

AXV CD/DVD-ROM SCSI CdRom Device (Virtual DVD-ROM)


C: (NTFS) 10244 Мб (2975 Мб свободно)

D: (NTFS) 30804 Мб (22484 Мб свободно)

E: (NTFS) 35267 Мб (18063 Мб свободно)

F: (NTFS) 4110 Мб (4087 Мб свободно)


Расширенная клавиатура PS/2 PC/AT (101/102 клавиши)

A4Tech PS/2 Port Mouse


127.0.0.1

00-00-00-00-00-02

CXT10B6 - HCF PCI Modem


hp deskjet 940c series

Поддержка USB принтера


Microsoft Visual Basic

VBA: Retail


6.3

6.4.8869




Обоснование выбора языка программирования


Графические интерфейсы пользователя, или GUIs, революционизировали микрокомпьютерную индустрию. Они продемонстрировали, что выражение "Лучше один раз увидеть, чем сто раз услышать не потеряло своего смысла для большинства пользователей компьютеров. Вместо загадочной командной строки. С:/, которую как долго наблюдали пользователи DOS, теперь они смотрят на "рабочий стол" (desktop), заполненный значками программ, управляя ими при помощи мыши или посредством меню.

Вероятно, при длительном использовании Windows то, как выглядят приложения, менее важно, чем-то, как они созданы. В Windows, как правило, программы имеют стандартный интерфейс пользователя. Это означает, что пользователи потратят больше времени на работу с приложением и меньше на изучение меню, диалоговых панелей и комбинации клавиш (Конечно, приложения Windows 98 и Windows XP выглядят немного не так, как приложения Windows 3.1: стандарты сохраняются внутри версий Windows, а не между ними).

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

За все это долгое время существовало лишь несколько таких средств разработки. До появления Visual Basic в 1991 году разработка приложений Windows была намного тяжелее процесса создания приложений для DOS. Программисты должны были позаботиться буквально обо всем, например, о работе с мышью, обработке событий меню, и даже отслеживать, щелкнул пользователь один либо два раза в конкретном месте экрана. Разработка приложений Windows требовала экспертных знаний по программированию на языке С и сотен строк кода для выполнения простейшие задач. Проблемы были даже у профессионалов. (Комплект Microsoft Windows Software Development Kit, необходимый в то время в дополнение к компилятору С, весил тогда около пяти килограммов).

Вот почему, когда появился Visual Basic 1.0, Билл Гейтс, глава и владелец компании Microsoft, назвал ею "внушительным". Стив Гибсон из InfoWorld описал данный язык как "новое великолепное чудо", которое сильно изменит взгляд людей на использование Microsoft Windows". Стюарт Олшоп, давая интервью газете "New York Times", назвал Visual Basic "идеальной средой разработки программ 90-х годов". Чарльз Петцольд, автор одной из классических книг по программированию на С для Windows, писал в "New York Times": "Для тех из нас, кто всю свою жизнь посвятил обучению сложностям программирования Windows, Visual Basic представляет реальную угрозу потерять средства к существованию". Последняя версия Visual Basic продолжает данную традицию: сложные приложения Windows 98 и Windows XP разрабатываются теперь за гораздо меньший период времени. Ошибки в программах (bugs) возникают не так часто, и их локализация происходит быстрее и легче. С Visual Basic программирование в Windows не становится более эффективным, но оно становится более простым (в большинстве случаев).



Схема алгоритма и его описание







^ ОПИСАНИЕ АЛГОРИТМА

Начало программы

Ввод размерности матрицы

Формирование симплекс таблицы

Создание объекта класса TSimplex

Ввод данных в таблицу

Расчет симплекс-метода

Вывод полученных результатов

Конец программы



^ РЕШЕНИЕ ЗАДАЧИ-ТЕСТА ДЛЯ НАПИСАНИЯ И ОТЛАДКИ ПРОГРАММЫ







L=

L=

0-()






















Св

X1

X2

L

0

8

5

-32

-8

0

Y1

12

0

5

0

0

0

Y2

4

1

0

4

1

0

Y3

20

4

5

-16

-4

0






Св

Y2

X2

L

-32

-8

5

-4

4

-1

Y1

12

0

5

-4

4

-1

X1

4

1

0

0

0

0

Y3

4

-4

5

4/5

-4/5

1/5






Св

Y2

X2

L

-36

-4

-1










Y1

8

4

-1










X1

4

1

0










Y3

4/5

-4/5

1/5









MIN = -36


^ АНАЛИЗ ПОЛУЧЕННЫХ РЕЗУЛЬТАТОВ.


Решить задачу можно по-разному при помощи компьютера, другого человека, но как узнать кто из них прав и вообще правильное ли получившееся решение. Определить правильный результат очень легко, взять ручку, листок бумаги, прочитать внимательное условие задачи и решить ее самому. И только после этого можно будет судить, правильно ли решает ПК и другой человек. В нашем случае мы будем сравнивать два решения: первое решение нашей программы на персональном компьютере, а второе решенное собственноручно мной.

Решив задачу, я получил одинаковые решения, однако одно условие в ручном способе было не учтено, человек округлял, поэтому на персональном компьютере ответы получились намного точнее (до 0,0001). Также решение на персональном компьютере было проще и меньше затрачено времени, в отличие от ручного варианта.

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


^ ИНСТРУКЦИЯ ПОЛЬЗОВАТЕЛЮ И ОПИСАНИЕ ПРОГРАММЫ


Описание переменных

1) Составление ведомости расчета прибыли от товара.

Для определения максимумов для каждого варианта стоимости товара полученные суммы находящиеся в ячейках (b3:f11) заносятся в массив АА_2(5), для отображения в ведомости максимальной и минимальной цены на товар.

Массивы MM_1(9) .. MM_6(9) используются для отображения минимальных цен на каждый товар по всем вариантам продаж.


^ 2) Модель управления запасами

Функция пользователя CALC вычисляющая финансовые исходы при всевозможных вариантах событий покупки журналов и их реализации. В функции используются следующие переменные: Цена_покупки, Цена_продажи, Цена_Возврата; i, j –переменные циклов. NROWS,ROWS – переменные типа Integer. Res() – массив того же типа переменной длины.


^ 3) Задание на нахождение оптимального раскроя

Для построения математической модели в программе производится перебор всевозможных вариантов раскроя рулона стандартной длины на рулоны требуемой для этого в программе используются следующие переменные:

i1,i2,i3,i4 – переменные циклов

a1,a2,a3,a4 – переменные которым присваиваются значения заказанных длин рулонов.

t – переменная которой функция Floor возвращает наибольшее целое число, не превышающее данное.

M – минимум среди заказанных длин


^ 4) База данных

i – переменная цикла используется в добавлении записей в БД при поиске пустого поля

l1 – метка на которую ссылается оператор Goto

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


^ Входные и выходные данные

1) Составление ведомости расчета прибыли от товара.


Входными данными являются 9 различных видов цен на 5 комплектующих в соответствии с условием, а также значения цен на товар до комплектации.

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


^ 2) Модель управления запасами


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

цена возврата в случае не реализации товара продавцом, также известно количество реализуемых за раз продавцом пачек, и число событий за отчетный период времени.

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


^ 3) Задание на нахождение оптимального раскроя

Входными данными являются значения заказанных длин и их кол-ва, а также значение стандартной длины.

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


^ 4) База данных


Здесь входные данные являются выходными, т.е что пользователь ввел то и отобразится в таблице. База данных содержит следующие поля:

2 поля ввода: Фамилия, имя

3 раскрывающихся списка: Работа, Стаж, Рабочий день (час)

Группы по 2 флажка: Кредитная карточка, заграничный паспорт

2 группы по 2 переключателя: Пол, Семейное положение

поля ввода со счетчиками: Возраст, Оклад, отпуск



^ Подробное описание задач


Составление ведомости расчета прибыли от товара

Заполняем таблицу значениями, как указано в условии т.е 5 разновидностей комплектующих расположенных в ячейках B2:F2, и 9 вариантов стоимостей комплектующих в ячейках A3:A11. В ячейках B3:F12 будет располагаться значения стоимостей комплектующих и стоимости работы до комплектации.

В ячейках G3 по формуле =СУММ(B3:F3) считается общая стоимость всех комплектующих, растягиваем маркер ячейки G3 до ячейки G11, и получаем стоимость всех комплектующих для всех вариантов стоимостей.

В программе определяется какая деталь в каком месте самая дешевая, если не учитывать транспортные затраты и задаться целью купить детали по минимальным ценам. Для этого в программе определяются минимальные стоимости по 5-ти деталям.

Полученная ведомость будет выглядеть следующим образом:

Варианты

В и д ы к о м п л е к т у ю щ и х

MIN / MAX

Стоимости

^ 1-я деталь

2-я деталь

3-я деталь

4-я деталь

5-я деталь

Всего




1-й

20

90

5

50

60

225




2-й

19

85

4

55

50

213




3-й

20

81

4

50

56

211

Миним. Цена на товар

4-й

25

87

8

57

58

235




5-й

29

87

5

55

60

236




6-й

18

88

4

40

61

211




7-й

30

99

9

66

60

264




8-й

30

99

9

66

64

268

Макс. Цена на товар

9-й

21

90

6

54

55

226




До комплектации

15

75

3

40

50

183






^ Модель управления запасами


Вводим исходные значения , т.е. значения покупки продавцом журналов, продажи этих журналов и возврата в типографию в случае не реализации товара. Ввод всего этого производится в диалоговом окне, которое создается как UserForm со специальными кнопками и полями ввода покупки журналов, продажи, и возврата к типографию. Окно ввода выглядит в соответствии с рисунком 1.





Рисунок 1 – Окно «Ввод данных»


Составляем таблицу состоящую из объема реализации, числа событий, и вероятности этих событий, первые два нам даны по условию а вероятность этих событий нужно посчитать. Вводим в ячейку D7 следующую формулу вычисления вероятностей {=D6/СУММ($D$5:$I$5)}

и растягиваем маркер до ячейки I7.

В ячейках C10:H15 с помощью функции пользователя CALC Вычисляем финансовые исходы при всевозможных вариантах событий покупки журналов и их реализации


Function CALC(buy As Variant) As Variant

Dim Цена_продажи, Цена_покупки, Цена_возврата, NRows, i, j As Integer, Result() As Integer

NRows = buy.Rows.Count

Цена_продажы = Range("a2").Value

Цена_покупки = Range("b2").Value

Цена_возврата = Range("c2").Value

ReDim Result(NRows, NRows)

For i = 1 To NRows

For j = 1 To NRows

If i <= j Then Result(i, j) = buy(i) * (Цена_продажы - Цена_покупки)

If i > j Then Result(i, j) = buy(j) * (Цена_продажы - Цена_покупки) - (buy(i) - buy(j)) * (Цена_покупки - Цена_возврата)

Next j

Next i

CALC = Result

End Function


В ячейках J11:J16 с помощью формулы {=МУМНОЖ(C10:H15;ТРАНСП(D7:I7))} находим ожидаемую прибыль, соответствующую различным вариантам покупки журналов.

В ячейке F16 с помощью формулы =НАИБОЛЬШИЙ(J11:J16;1)

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

=Макс(J11:J16)

В ячейке F17 по формуле =(ПОИСКПОЗ(НАИБОЛЬШИЙ(J11:J16;1);J11:J16;0)-1)*5

соответствующий оптимальный объем покупок газет. Затем функция CALC выводит эти оптимальные значения в окне сообщений.

Функция наибольший возвращает К-е наибольшее значение из множества данных . Эта функция используется для того чтобы выбрать значение по его относительному местоположению. Например, функцию НАИБОЛЬШИЙ можно использовать для того чтобы определить наилучший, второй, третий результат в балах, показанный при тестировании. Синтаксис программы такой:

НАИБОЛЬШИЙ (массив; К) где Массив – это массив или диапазон ячеек где определяется наибольшее значение, к – позиция (начиная с наибольшей) в массиве или диапазоне.

Все результаты занесенные в таблицу будут выглядеть следующим образом:








П р о д а ж а










П




0

4

8

12

14

18







о

0

0

0

0

0

0

0

Покупка

Прибыль

к

4

0

0

0

0

0

0

0

- р.

у

8

0

-20

16

16

16

16

4

- р.

п

12

0

-40

-4

32

32

32

8

12,94р.

к

14

0

-60

-24

12

48

48

12

16,88р.

а

18

0

-70

-34

2

38

56

14

9,00р.







Максимальная прибыль

16,88р.







18

0,28р.







Оптимальный объем

15















^ Задание на нахождение оптимального раскроя

Составляем таблицу в которой будут приведены остатки от раскроя на заказ при различных вариантах раскроя.

Например по условию в соответствии с вариантом стандартная длина раскроя равна 28 метров,

т.е. первый вариант раскроя будет составлять 0 рулон длиной 4 м, 0 рулонов длиной 6м и 4 рулона длиной 9 м, рулонов длиной 11 м. не будет, что в сумме даст 27, следовательно отходы будут составлять 1 метр. Второй вариант когда 1 рулон по 6 м и два по 11 м, в этом случае остатков не будет и т.д. Всего получается 19 вариантов раскроя.

В программе это будет выглядеть таким образом:


l = 28

a1 = 4: a2 = 6

a3 = 9: a4 = 11

r = 4

m = Application.Min(a1, a2, a3, a4)

t = Application.Floor(l / m, 1)

For i1 = 0 To t

For i2 = 0 To t

For i3 = 0 To t

For i4 = 0 To t

s = 28 - a1 * i1 - a2 * i2 - a3 * i3 - a4 * i4

If s >= 0 And s < m Then

Cells(r, 1).Value = r - 3

Cells(r, 2).Value = i1

Cells(r, 3).Value = i2

Cells(r, 4).Value = i3

Cells(r, 5).Value = i4

Cells(r, 6).Value = s

r = r + 1

End If

Next i4

Next i3

Next i2

Next i1


На листе это будет выглядеть так:


Д л и н ы р у л о н о в

н а з а к а з

Варианты













Остаток

раскройки

4

6

9

11

от раскроя

1

0

0

3

0

1

2

0

1

0

2

0

3

0

1

1

1

2

4

0

3

1

0

1

5

1

0

0

2

2

6

1

1

2

0

0

7

1

2

0

1

1

8

1

2

1

0

3

9

1

4

0

0

0

10

2

0

1

1

0

11

2

0

2

0

2

12

2

1

0

1

3

13

2

3

0

0

2

14

3

1

1

0

1

15

4

0

0

1

1

16

4

0

1

0

3

17

4

2

0

0

0

18

5

1

0

0

2

19

7

0

0

0

0



Пусть Xj – кол-во стандартных рулонов, разрезанных по варианту j, где j[1..19]. Ограничения налагаемые на переменные Xj связаны с требованием обеспечить изготовление заказанного кол-ва нестандартных рулонов. Функция цели учитывает суммарные отходы, получаемые при выполнении заказа. Таким образом имеем следующую мат. модель:

Минимизировать:

Z=x1+2x3+x4+2x5+x7+3x8+2x11+2x12+2x13+x14+x15+3x16+

+2x18 + 4(x5+x6+x7+x8+x9+2x10+2x11+2x12+2x13+3x14+4x15+4x16+4x17+5ч18+7x19-220)+ 6(...-210)+9(...-350)+

+11(...-380)

Отведем диапазон ячеек (i4:i22) под переменные . Введем в диапазон ячеек (j3:m3) левые части ограничений, определенные следующими формулами:

=СУММПРОИЗВ($I$4:$I$22;B4:B22)

=СУММПРОИЗВ($I$4:$I$22;c4:c22)

=СУММПРОИЗВ($I$4:$I$22;d4:d22)

=СУММПРОИЗВ($I$4:$I$22;e4:e22)

В ячейку N4 введем функцию цели:

=СУММПРОИЗВ($I$4:$I$22;F4:F22)+B3*(СУММПРОИЗВ($I$4:$I$22;B4:B22)-J3)+C3*(СУММПРОИЗВ($I$4:$I$22;C4:C22)-K3)+D3*(СУММПРОИЗВ($I$4:$I$22;D4:D22)-L3)+E3*(СУММПРОИЗВ($I$4:$I$22;E4:E22)-M3)


где в ячейки B3:E3 введены длины, а в ячейки J3:M3 – кол-ва заказанных рулонов

Выберем команду сервис – Поиск решения и заполним открывшееся диалоговое окно Поиск решения (Solver):

- Установим целевую ячейку – N4

- Изменяя ячейки I4:I22

- Ограничения $I$4:$I$22=целое

$I$4:$I$22>=0

$j$4:$m$4>=$j$3:$m$3

- Функция = минимизация



К о л - в о з а к а з а н н ы х р у л о н о в

220

210

350

380

Отходы

220

210

350

380

49,99996



^ База данных


Создадим поля базы данных, и занесем их в таблицу. База данных будет заполняться программой, программе не требуются названия полей, но для облегчения ориентации в первой строке введем данные соответствующие полям БД

Создадим кнопку “Добавление” для добавления записей в БД, делается это так: Вызываем панель инструментов на которой расположены примитивы, т.е. окна ввода, кнопки и т.д. Создаем на форме кнопку, и спомощью св-ва Caption присваиваем ей название “Добавление”

Создадим макрос который будет отвечать за обработку событий по нажатию этой кнопки. Перейдем в среду Visual Basic for Application и в меню «Вставка» выберем UserForm, на эту форму и поместим все обьекты оговоренные в условии(m раскрывающихся списков, n полей ввода, ...).

В макросе отвечающем за событие кнопки «Добавление» введем процедуру которая будет активизировать форму UserForm1, и заносить все данные из окна ввода в ячейки листа A4:L4, A5:L5 и т.д.

По нажатию кнопки “OK” выполнится следующий код программы.

Окно ввода показано в соответствии с рисунком 2.





Рисунок 2 – окно «База данных работников компании»


^ Описание интерфейса

Для работы с этой программой нужно прочитать инструкцию. Для того чтобы открыть эту программу необходимо запустить Прога.xls.

Чтобы перейти на нужный лист щелкните на кнопке, как показано на рисунке 3;





Рисунок 3 – Начало работы с программой


после того, как нажмете кнопку модель управления запасами, необходимо нажать на кнопку «Вычислить», как показано на рисунке 4;





Рисунок 4 – окно «Ввод данных»


После ввода значений в окно «Ввод данных», нажмите на кнопку «Ok» и программа сама посчитает максимальную прибыль и оптимальный объем и выведет это все в окне «Результаты», как показано на рисунке 5.





Рисунок 5 – окно «Результаты»


^ Функция пользователя

Функция пользователя создается на рабочем листе с названием модуль VBA, после чего с ними можно работать с помощью мастера функций. Для создания модул