Составление документа в среде Microsoft Excel

Размещено на http://www.allbest.ru/

57

Размещено на http://www.allbest.ru/

составление документа в среде Microsoft Excel

ВВЕДЕНИЕ

документ microsoft excel программа

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

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

Если говорить о среде написания программ для решения поставленных задач - Microsoft Visual Basic for Application - то это сочетание одного из самых простых языков программирования и всех вычислительных возможностей такой многогранной системы как Excel. С помощью VBA можно легко и быстро создавать разнообразные приложения, даже не являясь специалистом в области программирования. VBA содержит относительно мощную графическую среду, позволяющую наглядно конструировать экранные формы и управляющие элементы. В общем Visual Basic for Application позволяет с легкостью решать многие задачи.

1. ТЕХНИКО-ЭКОНОМИЧЕСКАЯ ХАРАКТЕРИСТИКА ОБЪЕКТА

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

На предприятии существует возможность вложить максимум в 6-ть филиалов от 1 до 7 млн. руб. Вначале у бухгалтерского отдела предприятия берется ведомость математических ожиданий всех филиалов за прошедший год и строится таблица этих ожиданий. Дальше с помощью математических формул строится таблица, в которой будет приведено максимальные варианты прибыли при разных ситуациях вложения в филиалы. По таблице видно, что максимальная прибыль имеется (в размере 1,01 млн. руб. (хотя максимальная прибыль может быть больше чем 1 вариант)) при вложении 7 млн. руб. в 6 филиалов. В третьей таблице путем совмещения информации из двух предыдущих таблиц и при помощи математических изысканий мы видим, что нужно эти 7 млн. руб. распределить следующим образом: 5 млн. руб. вложить в филиалы с 1-го по 5-ый (т.е. по 1 млн. руб. на каждый филиал) и целых 2 млн. руб. нужно вложить в 6-ой филиал.

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

2. АНАЛИЗ ИСПОЛЬЗУЕМОЙ ОБРАБОТКИ ИНФОРМАЦИИ НА ПРЕДПРИЯТИИ

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

2.1 Организационно-экономическая сущность задачи

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

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

Входная и выходная информация

1) Начисление премии в виде комиссионных и дополнительной оплаты

Входными данными в этом типе заданий являются исходные таблицы с данными о доходах магазинов за указанные месяцы, а также сумма доходов по месяцам. В программе эти значения присваиваются различным переменным при помощи объекта WorkSheets(“<Лист>”).Range(“<Ячейка>”).Value. Выходными данными являются премиальные, полученные в результате вычислений в соответствии с условием задания.

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

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

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

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

3) Задача об оптимальных капиталовложениях

В этой задаче входными данными являются значения мат. ожиданий прибыли как функций капиталовложений, в соответствии с условием 6 филиалов и 7 млн. руб.

Выходными данными являются таблица где производится: оптимальное распределение средств, когда А млн. вкладываются в 1-й и 2-й филиалы вместе, оптимальное распределение средств, когда А млн. вкладываются в 1-й,2-й и 3-й филиалы вместе, оптимальное распределение средств, когда А млн. вкладываются в 1-й,2-й,3-й,4-й филиалы вместе, и оптимальное распределение средств, когда А млн. вкладываются в 1-й,2-й,3-й,4-й и 5-й филиалы вместе и 1-й,2-й,3-й,4-й,5-й и 6-й.

Из этой таблицы находится максимальная ожидаемая прибыль в данном случае равная 1,01 млн. и оптимальные капиталовложения, из таблицы видно, сколько млн. и в какие филиалы нужно вложить:

6 филиал - 2 млн.

5 филиал - 1 млн.

6 филиал - 1 млн.

6 филиал - 1 млн.

6 филиал - 1 млн.

6 филиал - 1 млн.

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

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

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

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

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

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

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

2.2 Аппаратные и программные средства обеспечения решения задачи

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

Компьютер: Операционная система Пакет обновления ОС Системная плата: Тип ЦП Системная память Дисплей: Видеоадаптер Монитор Хранение данных: Флоппи-накопитель Дисковый накопитель Оптический дисковод Ввод: Клавиатура Мышь Периферийные устройства: Принтер	Microsoft Windows XP Professional Service Pack 2 Intel Celeron-A, 440 MHz (5.5 x 80) 128 Мб (SDRAM) NVIDIA GeForce2 MX/MX 400 (64 Мб) LG StudioWorks 575N Дисковод гибких дисков ST380011A (80 Гб, 7200 RPM, Ultra-ATA/100) _NEC CD-RW NR-9100A (40x/10x/40x CD-RW) Расширенная клавиатура PS/2 PC/AT (101/102 клавиши) A4Tech PS/2 Port Mouse hp deskjet 940c series
Microsoft Office Excel 2003	11.5604.5703

3. ИНФОРМАЦИОННОЕ ОБЕСПЕЧЕНИЕ

3.1 Описание входной оперативной информации

Структура книги Excel состоит из 8 листов: 1 лист - лист содержания, со 2 по 5 и с 7 по 8 включительно - сами задания, а на 6 листе находится база данных предприятия. Через меню Сервис/Макрос/Редактор Visual Basic (или при нажатии Alt+F11) мы можем попасть в Редактор Visual Basic, где находятся формы и модули нашей программы:

В UserForm1 находится форма базы данных работников компании.

В UserForm2 находится 1-ая форма модели управления запасами, а именно ввод данных.

В UserForm3 находится 2-ая форма модели управления запасами, а именно результаты вычислений.

В Модуле 1 находится текст первого листа, этот текст активирует содержание.

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

В Модуле 3 находится текст задания - модель управления запасами.

3.2 Выбор метода реализации модели. Обоснование выбора

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



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

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

При Х₁ =Х₂ =…=0 получим L=j₀

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

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

1 Если все коэффициенты J₁,J₂…J_k положительно то мы не сможем уменьшить (L) и найденное решение будет оптимальным.

2 Если среди коэффициентов J₁,J₂…J_k есть отрицательный элемент то увеличивая при нем (Х) мы можем улучшить (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

Выполняя операцию X₂ - Y₃, мы хотим в разрешающей строке поместить переменную Y_3, а в разрешающем столбце - переменную X₂ (это отмечено в таблице 1).

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

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

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

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

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

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

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

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

Таблица 2

	Свободный член	X1	Y3	X3	X4
Y1
Y2
X2
Y4
Y5

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

которую нужно минимизировать. Если эта функция выражена через прежние свободные переменные X₁,X₂,…,X_n, то, очевидно, после замены X_j - Y_i ее нужно выразить через новые свободные переменные X₁, X₂,…, X_j-1, Y_i, X_j+1,…, X_n. Нетрудно убедится, что для этого может быть применен тот же алгоритм, что и для преобразования любой строки стандартной таблицы. Приводя L к стандартной форме

,

где Y₁= - c₁

Y₂= - c₂

Y_n= -c_n

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

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

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

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

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

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

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

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

Имеем:

X₁ = X₂ = … = X_n = 0;

Y₁ = b₁;

Y₂ = b₂;

Y_m = b_m.

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

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

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

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

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

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

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

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

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

3.3 Схема алгоритма симплекс-метода

Размещено на http://www.allbest.ru/

57

Размещено на http://www.allbest.ru/

4. ПРОГРАММНОЕ ОБЕСПЕЧЕНИЕ ЗАДАЧИ

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

VB никогда не проектировался как язык, заменяющий такие системные языки, как Cи (С++). Он предназначен прежде всего как язык управления объектами, которые создаются на C. Пример тому, ядро DBEngine - выполено на C++. Hо эксплуатация его производится в VB.

На мой взгляд очень сильное преимущество VB в его, скажем так, распространенности или стандартизованности. VB доступен во всех офисных приложениях, а также в некоторых других продуктах. В том числе имеются реализации VB очень похожие на VBA, но все таки не VBA ;) Например, RoseScript (или RoseBasic) встроенный в Rational Rose, который переносится практически один-в-один на VB. То есть этого стандарта придерживаются разработчики клонов Бейсика.

Таким образом, зная VB мне, как разработчику, не составит труда написать приложение для автоматизации офисного продукта или продукта для разработки, написать продвинутый "батник" на VBScript, а при необходимости легко перенести написанный код в другую программу, либо в готовое приложение. Кроме того, подобная универсальность позволяет практически любому программисту разобраться в моем коде, внести изменения.

Следующим очень важным преимуществом VB я считаю его "родство" с технологией COM. На VB проще всего писать полноценные COM-компоненты. Как следствие, на VB очень просто писать масштабируемые приложения ;) При необходимости, класс легко выносится в отдельный компонент, компонент легко переносится на другой компьютер (DCOM) или на сервер компонентов (MTS) и т.д. и т.п. Причем все это не требует модификации (!) собственно кода.

Также на VB удобно создавать макеты приложений, библиотек, которые потом будут перенесены на C - удобные средства отладки, более-менее устойчивый интерфейс

Ввиду специфики языка VB - отсутствие указателей, ссылок и пр., присутствующих в ObjectPascal (Delphi) и C/C++, программист на VB может больше времени уделять непосредственно разработке бизнес процессов приложения, не задумываясь о динамическом распределении памяти, передаче параметров по ссылкам и пр., что отнимает кучу времени при программировании на других языках и порождает такую же кучу ошибок, которые приходится потом вылавливать. Я не имею ввиду, что ошибки обязательно есть, некоторые умеют писать вовсе без них, я имею ввиду, что один и тот же человек прогрммируя на VB сделает гораздо меньше ошибок связанных с перечисленным выше, чем если он будет писать программу на Delphi или C++.

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

1) Начисление премии в виде комиссионных и дополнительной оплаты

MAS1(3) - массив из 3-х элементов в который заносятся значения доходов магазинов за указанные месяцы

MAS2(3) - массив в который заносятся доходы только тех магазинов доход которых превышает 1490,00 руб.

MAS_I1(3) - массив индексов

B,C,D - вспомогательные переменные

I - переменная цикла

Max - переменная при помощи которой находится максимальное значение в массиве доходов(сначала этой переменной специально присваивается очень маленькое значение)

Indm, Indm2, Indm3 - индексы элементов массива которые являются максимумами если исключить предыдущий найденный максимум, например сначала максимумом является Indm, затем находится максимум из оставшихся и т.д.

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

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

3) Задача об оптимальных капиталовложениях

К - переменная типа Integer, отвечает за количество капиталовложений в млн. руб.

R() - массив переменной длины, в программе используется как прибыль от вложения i млр. в j-тый филиал, где в R[i,j] - i(1..7), а j(1..6)

I,j,p - переменные циклов.

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

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

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

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

4.3 Алгоритм решения задач

Начисление премии в виде комиссионных и дополнительной оплаты

Создаем таблицу начисления премий, в ячейки B4:D10 заносим значения доходов магазинов за указанные месяцы, сбоку в ячейках A4:A10 будут располагаться названия месяцев, согласно варианту - с ноября по май, а в B3:D3 - магазины, таким образом на пересечении будет показана величина дохода магазина который находится в этом столбце и месяца который расположен в этой строке.

В ячейке B11 считаем доход 1-го магазина за все месяцы по формуле =СУММ(B4:B10), и растягиваем маркер чертежа до ячейки D10, таким образом производится подсчет доходов всех магазинов за все месяцы.

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

Do

k = mas1(i)

If k > 1490 Then mas2(i) = mas1(i) Else mas2(i) = 0

i = i + 1

Loop Until i = 4

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

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

Do

i = i + 1

If mas2(i) > Max Then

Max = mas2(i)

indm = i

End If

Loop Until i = 3

Складываем полученные 2% с теми, что начисляются дополнительно за 1,2,3 и т.д. места, и заносим результаты в таблицу в строку “Премиальные”

Worksheets([лист]).Cells([координаты ячеек]).Value = Max * 0.02 + Max * 0.04

Месяц	Магазины
	1	2	3
Ноябрь	100	100	120
Декабрь	300	150	650
Январь	1000	130	250
Февраль	1000	120	50
Март	0	100	760
Апрель	100	100	0
Май	310	600	500
Всего	2810	1300	2330
Премиальные!	168,6	0	93,2

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

Вводим исходные значения, т.е. значения покупки продавцом журналов, продажи этих журналов и возврата в типографию в случае не реализации товара. Ввод всего этого производится в диалоговом окне, которое создается как 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

Определение оптимальных капиталовложений

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

	Филиалы
Ммлн. руб.	1	2	3	4	5	6
0	0	0	0	0	0	0
1	0,11	0,12	0,18	0,2	0,17	0,12
2	0,11	0,13	0,18	0,22	0,17	0,23
3	0,12	0,13	0,19	0,24	0,18	0,24
4	0,12	0,13	0,19	0,26	0,18	0,24
5	0,13	0,13	0,2	0,29	0,19	0,25
6	0,13	0,13	0,2	0,31	0,19	0,25
7	0,14	0,13	0,2	0,33	0,2	0,26

Для дальнейшего решения задачи, вводим следующие обозначения:

Пусть R(i,j) - прибыль получаемая от вложения i млн. руб. В j-тый филиал, где в соотв. С вариантом i от (0,7), а j от (0,6)

F(A,1,2) - оптимальное распределение средств, когда А млн. руб. вкладываются в 1,2 филиалы вместе

F(A,1,2,3) - оптимальное распределение средств, когда А млн. руб. вкладываются в 1,2,3 филиалы вместе

F(A,1,2,3,4) - оптимальное распределение средств, когда А млн. руб. вкладываются в 1,2,3,4 филиалы вместе.

F(A,1,2,3,4,5) - оптимальное распределение средств, когда А млн. руб. вкладываются в 1,2,3,4,5 филиалы вместе.

F(A,1,2,3,4,5,6) - оптимальное распределение средств, когда А млн. руб. вкладываются в 1,2,3,4,5 филиалы вместе.

Значения I при которых достигается максимум определяют оптимальные капиталовложения в филиалы.

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

Максимумы
1 и 2	1,2 и 3	1,2,3 и 4	1,2,3,4 и 5	1,2,3,4,5 и 6
0	0	0	0	0
0,12	0,18	0,2	0,2	0,2
0,23	0,3	0,38	0,38	0,38
0,24	0,41	0,5	0,55	0,55
0,24	0,42	0,61	0,67	0,67
0,25	0,42	0,63	0,78	0,79
0,25	0,43	0,65	0,8	0,9
0,26	0,43	0,67	0,82	1,01

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

В диапазон ячеек (B14:K22) выводится оптимальное распределение капиталовложений по филиалам. После вычислений можно увидеть что максимальная ожидаемая прибыль составляет 1,01 млн. руб. , из таблицы видны следующие результаты:

6 филиал - 2 млн.

5 филиал - 1 млн.

4 филиал - 1 млн.

3 филиал - 1 млн.

2 филиал - 1 млн.

1 филиал - 1 млн.

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

	Филиалы
0	0	0	0	0	0	0	0	0	0	0
1	0	1	0	1	0	1	1	0	1	0
2	1	1	1	1	1	1	2	0	2	0
3	1	2	2	1	2	1	2	1	3	0
4	1	3	3	1	3	1	3	1	3	1
5	3	2	2	3	3	2	4	1	4	1
6	3	3	3	3	3	3	5	1	4	2
7	5	2	2	5	3	4	6	1	5	2
Млн. руб.	1	2	1,2	3	1,2,3	4	1,2,3 и 4	5	1,2,3,4 и 5	6

База данных

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

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

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

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

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

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



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

4.4 Описание интерфейса и инструкция по работе с программой

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

1) Начисление премии в виде комиссионных и дополнительной оплаты

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

Рисунок 3 - Главное меню

после того, как нажмете кнопку модель начисление премии в виде комиссионных и дополнительной оплаты, необходимо оставить данные по месяцам прибыли 3-х магазинов или ввести новые, как показано на рисунке 4;

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

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

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

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

Чтобы перейти на эту задачу щелкните на кнопке, как показано на рисунке 6;

Рисунок 6 - Главное меню

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

Рисунок 7 - окно «Ввод данных»

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

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

3) Нахождение оптимальных капиталовложений

Чтобы перейти на эту задачу щелкните на кнопке, как показано на рисунке 9;

Рисунок 9 - Главное меню

после того, как нажмете кнопку нахождение оптимальных капиталовложений, необходимо оставить данные по математическим ожиданиям в 6-ти филиалах предприятия по производству быстрого питания или ввести новые (значения не могут быть больше 0.5), как показано на рисунке 10;

Рисунок 10 - окно «Ввод данных»

После ввода значений в строки, нажмите на кнопку «Вычислить» и программа сама посчитает ожидаемую прибыль в каждом из филиалов, как показано на рисунке 11.

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

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

Чтобы перейти на эту задачу щелкните на кнопке, как показано на рисунке 12;

Рисунок 12 - Главное меню

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

Рисунок 13 - окно «Ввод данных»

После ввода значений следует нажать на кнопку «OK» и вся информация добавиться в таблицу, как показано на рисунке 14.



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

4.5 Решение задачи-теста для симплекс-метода и отладка программы

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). Также решение на персональном компьютере было проще и меньше затрачено времени, в отличие от ручного варианта.

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

ЗАКЛЮЧЕНИЕ

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

Для решения данной задачи была использована среда Microsoft Visual Basic Application 6.4.8869.

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

ПЕРЕЧЕНЬ СОКРАЩЕНИЙ

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

ПЭВМ - персональная электронно-вычислительная машина

ИБП - источник бесперебойного питания

ПК - персональный компьютер

ЭВМ - электронная вычислительная машина

ПО - программное обеспечение

Basic - Microsoft Visual Basic

VBA - Visual Basic Application

СПИСОК ИСПОЛЬЗУЕМОЙ ЛИТЕРАТУРЫ

1. А. Гарнаев. Использование MS Excel и VBA в экономике и финансах.

2. С. Браун, Visual Basic 5.0 с самого начала, Москва 1999, издательство “Питер”.

3. Microsoft Visual Basic - on-Line HELP.

ПРИЛОЖЕНИЕ 1

Блок-схема модели управления запасами

ПРИЛОЖЕНИЕ 2

Листинг программы на языке Visual Basic Application

Модуль 1:

Sub Return_To_MainMenu()

Worksheets("Содержание").Activate

End Sub

Модуль 2:

Sub Task1()

Worksheets("Задание1").Activate

End Sub

Sub Task2()

Worksheets("Задание2").Activate

End Sub

Sub Task3()

Worksheets("Задание3").Activate

End Sub

Sub Task4()

Worksheets("Задание4").Activate

End Sub

Sub Task1_Evrica()

Dim Max As Long

Dim mas1(3) As Integer

Dim mas2(3) As Integer

Dim Mas_I1(3) As Integer

Dim B As Integer

Dim c As Integer

Dim D As Integer

Dim k As Integer

Dim indm As Integer

Dim i As Integer

Dim l As Integer

Dim indm2 As Integer

Dim indm3 As Integer

B = Worksheets("Задание1").Range("B11").Value

c = Worksheets("Задание1").Range("C11").Value

D = Worksheets("Задание1").Range("D11").Value

mas1(1) = B

mas1(2) = c

mas1(3) = D

i = 1

l = 0

Do

k = mas1(i)

''''' Занесение в массив Mas2 эл-тов >1490

If k > 1490 Then mas2(i) = mas1(i) Else mas2(i) = 0

i = i + 1

Loop Until i = 4

Max = -1

i = 0

Do

i = i + 1

If mas2(i) > Max Then

Max = mas2(i)

indm = i

End If

Loop Until i = 3

Worksheets("Задание1").Cells(12, indm + 1).Value = Max * 0.02 + Max * 0.04

'Worksheets("Задание1").Range("f15").Value = r

'GoTo l

''''' Находим MAx эл-т из оставшихся,

''''' и запоминаем его индеск

Max = -1

i = 0

Do

i = i + 1

If i <> indm And mas2(i) > Max Then

Max = mas2(i)

indm2 = i

End If

Loop Until i = 3

Worksheets("Задание1").Cells(12, indm2 + 1).Value = Max * 0.02 + Max * 0.02

''''' Находим MAx эл-т из оставшихся,

''''' и запоминаем его индеск

Max = -1

i = 0

Do

i = i + 1

If mas2(i) > Max And i <> indm2 And i <> indm Then

Max = mas2(i)

indm3 = i

End If

Loop Until i = 3

Worksheets("Задание1").Cells(12, indm3 + 1).Value = Max * 0.02 + Max * 0.01

End Sub

Sub Task2_Evrica()

Dim AA_1(3) As Integer

Dim B As Integer

Dim c As Integer

Dim D As Integer

Dim i As Integer

B = Worksheets("Задание2").Range("B11").Value

c = Worksheets("Задание2").Range("C11").Value

D = Worksheets("Задание2").Range("D11").Value

AA_1(1) = B

AA_1(2) = c

AA_1(3) = D

i = 0

Do

i = i + 1

If AA_1(i) < 700 Then Worksheets("Задание2").Cells(12, i + 1).Value = Worksheets("Задание2").Cells(11, i + 1).Value * 0.01

If AA_1(i) >= 700 And AA_1(i) < 1400 Then Worksheets("Задание2").Cells(12, i + 1).Value = Worksheets("Задание2").Cells(11, i + 1).Value * 0.015

If AA_1(i) >= 1400 And AA_1(i) < 2800 Then Worksheets("Задание2").Cells(12, i + 1).Value = Worksheets("Задание2").Cells(11, i + 1).Value * 0.023

If AA_1(i) >= 2800 Then Worksheets("Задание2").Cells(12, i + 1).Value = Worksheets("Задание2").Cells(11, i + 1).Value * 0.025

Loop Until i = 3

End Sub

Sub Task3_Evrica()

Dim AA_2(10) As Integer

Dim i As Integer

Dim Max As Long

Dim Min As Long

Dim MM_1(10) As Integer

Dim MM_2(10) As Integer

Dim MM_3(10) As Integer

Dim MM_4(10) As Integer

Dim MM_5(10) As Integer

Dim MM_6(10) As Integer

Dim MM_7(10) As Integer

Dim MM_8(10) As Integer

Dim MM_9(10) As Integer

Dim mm As Integer

Dim mm2 As Integer

Dim x As Integer

Dim x1 As Integer

Dim x2 As Integer

Dim x3 As Integer

Dim x4 As Integer

Dim x5 As Integer

Dim x6 As Integer

Dim x7 As Integer

Dim x8 As Integer

Dim x9 As Integer

Worksheets("Задание3").Range("I3:I12").Clear

Worksheets("Задание3").Range("b3:h12").Font.Bold = False

Worksheets("Задание3").Range("b3:h12").Font.Size = 10

Worksheets("Задание3").Range("b3:h12").Font.Italic = False

i = 0

Do

i = i + 1

AA_2(i) = Worksheets("Задание3").Cells(i + 2, 7).Value

Loop Until i = 9

Max = -1

i = 0

Do

i = i + 1

If AA_2(i) > Max Then

Max = AA_2(i)

mm = i

Worksheets("Задание3").Range("H3:H11").Clear

End If

Loop Until i = 9

Worksheets("Задание3").Cells(mm + 2, 8).Value = "Макс. Цена на товар"

Min = 100000

i = 0

Do

i = i + 1

If AA_2(i) < Min Then

Min = AA_2(i)

mm2 = i

End If

Loop Until i = 9

Worksheets("Задание3").Cells(mm2 + 2, 8).Value = "Миним. Цена на товар"

'''''''''''''''''''''''''''''

i = 0

Do

i = i + 1

MM_1(i) = Worksheets("Задание3").Cells(i + 2, 2).Value

MM_2(i) = Worksheets("Задание3").Cells(i + 2, 3).Value

MM_3(i) = Worksheets("Задание3").Cells(i + 2, 4).Value

MM_4(i) = Worksheets("Задание3").Cells(i + 2, 5).Value

MM_5(i) = Worksheets("Задание3").Cells(i + 2, 6).Value

Loop Until i = 9

'''' 1

Min = 100000

i = 0

Do

i = i + 1

If MM_1(i) < Min Then

Min = MM_1(i)

x1 = i

End If

Loop Until i = 9

Worksheets("Задание3").Cells(x1 + 2, 2).Font.Bold = True

Worksheets("Задание3").Cells(x1 + 2, 2).Font.Size = 11

Worksheets("Задание3").Cells(x1 + 2, 2).Font.Italic = True

'''' 2

Min = 100000

i = 0

Do

i = i + 1

If MM_2(i) < Min Then

Min = MM_2(i)

x2 = i

End If

Loop Until i = 9

Worksheets("Задание3").Cells(x2 + 2, 3).Font.Bold = True

Worksheets("Задание3").Cells(x2 + 2, 3).Font.Size = 11

Worksheets("Задание3").Cells(x2 + 2, 3).Font.Italic = True

'''' 3

Min = 100000

i = 0

Do

i = i + 1

If MM_3(i) < Min Then

Min = MM_3(i)

x3 = i

End If

Loop Until i = 9

Worksheets("Задание3").Cells(x3 + 2, 4).Font.Bold = True

Worksheets("Задание3").Cells(x3 + 2, 4).Font.Size = 11

Worksheets("Задание3").Cells(x3 + 2, 4).Font.Italic = True

'''' 4

Min = 100000

i = 0

Do

i = i + 1

If MM_4(i) < Min Then

Min = MM_4(i)

x4 = i

End If

Loop Until i = 9

Worksheets("Задание3").Cells(x4 + 2, 5).Font.Bold = True

Worksheets("Задание3").Cells(x4 + 2, 5).Font.Size = 11

Worksheets("Задание3").Cells(x4 + 2, 5).Font.Italic = True

'''' 5

Min = 100000

i = 0

Do

i = i + 1

If MM_5(i) < Min Then

Min = MM_5(i)

x5 = i

End If

Loop Until i = 9

Worksheets("Задание3").Cells(x5 + 2, 6).Font.Bold = True

Worksheets("Задание3").Cells(x5 + 2, 6).Font.Size = 11

Worksheets("Задание3").Cells(x5 + 2, 6).Font.Italic = True

'''' 6

Min = 100000

i = 0

Do

i = i + 1

If MM_6(i) < Min Then

Min = MM_6(i)

x6 = i

End If

Loop Until i = 9

Worksheets("Задание3").Cells(x6 + 2, 7).Font.Bold = True

Worksheets("Задание3").Cells(x6 + 2, 7).Font.Size = 11

Worksheets("Задание3").Cells(x6 + 2, 7).Font.Italic = True

''''7

Min = 100000

i = 0

Do

i = i + 1

If MM_7(i) < Min Then

Min = MM_7(i)

x7 = i

End If

Loop Until i = 9

Worksheets("Задание3").Cells(x7 + 2, 8).Font.Bold = True

Worksheets("Задание3").Cells(x7 + 2, 8).Font.Size = 11

Worksheets("Задание3").Cells(x7 + 2, 8).Font.Italic = True

''''8

Min = 100000

i = 0

Do

i = i + 1

If MM_8(i) < Min Then

Min = MM_8(i)

x8 = i

End If

Loop Until i = 9

Worksheets("Задание3").Cells(x8 + 2, 9).Font.Bold = True

Worksheets("Задание3").Cells(x8 + 2, 9).Font.Size = 11

Worksheets("Задание3").Cells(x8 + 2, 9).Font.Italic = True

''''9

Min = 100000

i = 0

Do

i = i + 1

If MM_9(i) < Min Then

Min = MM_9(i)

x9 = i

End If

Loop Until i = 9

Worksheets("Задание3").Cells(x9 + 2, 10).Font.Bold = True

Worksheets("Задание3").Cells(x9 + 2, 10).Font.Size = 11

Worksheets("Задание3").Cells(x9 + 2, 10).Font.Italic = True

End Sub

Sub Task5()

Worksheets("Задание5").Activate

End Sub

Sub Task6()

Worksheets("Задание5").Activate

End Sub

Sub Task5_Evrica()

Dim G(4, 4)

Dim c(4)

Dim i As Integer

Dim j As Integer

c(1) = Worksheets("Задание5").Range("a1")

c(2) = Worksheets("Задание5").Range("b1")

c(3) = Worksheets("Задание5").Range("c1")

c(4) = Worksheets("Задание5").Range("d1")

Worksheets("Задание5").Range("a3:d6").Value = ""

For i = 1 To 4

For j = 1 To 4

If i <= j + 1 Then G(i, j) = c(i) * (Cos(c(j))) ^ 2

If i > j + 1 Then G(i, j) = Abs(c(i - j) ^ 3 - c(i))

Next

Next

For i = 1 To 4

For j = 1 To 4

Worksheets("Задание5").Cells(i + 2, j).Value = G(i, j)

Next

Next

End Sub

Sub Task6_Evrica()

Dim i As Integer

Dim m As Integer

Dim s As Integer

Dim s1 As Integer

Dim s2 As Integer

Dim s3 As Integer

Dim x(4)

Dim Y(4)

x(1) = Worksheets("Задание5").Range("a12")

x(2) = Worksheets("Задание5").Range("a13")

x(3) = Worksheets("Задание5").Range("a14")

x(4) = Worksheets("Задание5").Range("a15")

Y(1) = Worksheets("Задание5").Range("b12")

Y(2) = Worksheets("Задание5").Range("b13")

Y(3) = Worksheets("Задание5").Range("b14")

Y(4) = Worksheets("Задание5").Range("b15")

s1 = 0

s2 = 0

s3 = 0

m = 4

For i = 1 To m

s1 = s1 + x(i)

s2 = s2 + x(i) * Y(i)

s3 = s3 + x(i) * x(i)

Next

s = (2 * s1 + s2) * (2 - s1) + 3 + s3

Worksheets("Задание5").Range("D15").Value = s

End Sub

Sub Task7()

Worksheets("Раскрой").Activate

End Sub

Sub Task7_DB()

UserForm1.ComboBox1.Clear

UserForm1.ComboBox2.Clear

UserForm1.ComboBox3.Clear

UserForm1.ComboBox1.AddItem ("Директор")

UserForm1.ComboBox1.AddItem ("Зам. директора")

UserForm1.ComboBox1.AddItem ("Менеджер")

UserForm1.ComboBox1.AddItem ("Сектетарь")

UserForm1.ComboBox1.AddItem ("Администратор")

UserForm1.ComboBox1.AddItem ("Охрана")

UserForm1.ComboBox1.AddItem ("Водитель")

UserForm1.ComboBox1.AddItem ("Сторож")

UserForm1.ComboBox1.AddItem ("Уборщик")

UserForm1.ComboBox2.AddItem ("10 лет.")

UserForm1.ComboBox2.AddItem ("9 лет.")

UserForm1.ComboBox2.AddItem ("8 лет.")

UserForm1.ComboBox2.AddItem ("3 года.")

UserForm1.ComboBox2.AddItem ("2 года.")

UserForm1.ComboBox2.AddItem ("1 год.")

UserForm1.ComboBox2.AddItem ("меньше года.")

UserForm1.ComboBox3.AddItem ("5 часов")

UserForm1.ComboBox3.AddItem ("6 часов")

UserForm1.ComboBox3.AddItem ("7 часов")

UserForm1.ComboBox3.AddItem ("8 часов")

UserForm1.Show

End Sub

Sub Task7_List()

Worksheets("БД").Activate

End Sub

Sub Model_of_storekeeping()

UserForm2.Show

End Sub

Модуль 3:

ption Explicit

'МОДЕЛЬ УПРАВЛЕНИЯ ЗАПАСАМИ

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

Sub Begin()

Worksheets("Содержание").Activate

End Sub

Sub Optimum_capital_investmentsEVR()

Dim i, j, k, n, p, l, t As Integer

Dim m, r(), A() As Double

k = 7

ReDim r(k + 1, 6), A(k + 1)

For i = 1 To k + 1

For j = 2 To 7

r(i, j - 1) = Cells(i + 3, j).Value

Next j

Next i

t = 2

For p = 2 To 6

If p = 2 Then

For j = 1 To k + 1

A(j) = Cells(j + 3, 2).Value

Next j

End If

If p > 2 Then

For j = 1 To k + 1

A(j) = Cells(j + 3, p + 5).Value

Next j

End If

For n = 1 To k + 1

m = -1

For j = 1 To n

If m < A(j) + r(n + 1 - j, p) Then

m = A(j) + r(n + 1 - j, p)

End If

Next j

Cells(n + 3, 6 + p).Value = m

l = t

For j = 1 To n

If m = A(j) + r(n + 1 - j, p) Then

Cells(n + 6 + k, l).Value = j - 1

Cells(n + 6 + k, l + 1).Value = n - j

l = l + 2

End If

Next j

Next n

t = l

Next p

End Sub

Модуль 4:

Sub Раскрой()

Dim r, i1, i2, i3, i4, s, t As Integer

Dim l, a1, a2, a3, a4, a5, m As Integer

'Dim F, TT, SS, ZZ As String

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

Range("J4").FormulaLocal = "=СУММПРОИЗВ($I$4:$I$" & r - 1 & ";B4:B" & r - 1 & ")"

Range("K4").FormulaLocal = "=СУММПРОИЗВ($I$4:$I$" & r - 1 & ";C4:C" & r - 1 & ")"

Range("L4").FormulaLocal = "=СУММПРОИЗВ($I$4:$I$" & r - 1 & ";D4:D" & r - 1 & ")"

Range("M4").FormulaLocal = "=СУММПРОИЗВ($I$4:$I$" & r - 1 & ";E4:E" & r - 1 & ")"

Range("N4").FormulaLocal = "=СУММПРОИЗВ($I$4:$I$" & r - 1 & ";F4:F" & r - 1 & ")+B3*(СУММПРОИЗВ($I$4:$I$" & r - 1 & ";B4:B" & r - 1 & ")-J3)+C3*(СУММПРОИЗВ($I$4:$I$" & r - 1 & ";C4:C" & r - 1 & ")-K3)+D3*(СУММПРОИЗВ($I$4:$I$" & r - 1 & ";D4:D" & r - 1 & ")-L3)+E3*(СУММПРОИЗВ($I$4:$I$" & r - 1 & ";E4:E" & r - 1 & ")-M3)"

End Sub

Sub Optimum_capital_investments()

Worksheets("Опт.капитал").Activate

End Sub

Sub Quit()

Application.Quit

End Sub

UserForm1

Dim i As Integer

Private Sub ComboBox1_Change()

UserForm1.ComboBox1.AddItem ("AAA")

UserForm1.ComboBox1.AddItem ("AAA")

End Sub

Обработчик события кнопки <OK>

Private Sub CommandButton1_Click()

If UserForm1.TextBox1.Text = "" Then GoTo ll

i = 0

Do

i = i + 1

Loop Until Worksheets("БД").Cells(i, 1) = ""

Worksheets("БД").Cells(i, 1) = UserForm1.TextBox1.Text

Worksheets("БД").Cells(i, 2) = UserForm1.TextBox3.Text

If UserForm1.CheckBox2 = True Then

Worksheets("БД").Cells(i, 6) = "Есть"

Else

Worksheets("БД").Cells(i, 6) = "Нет"

End If

If UserForm1.CheckBox1 = True Then

Worksheets("БД").Cells(i, 7) = "Есть"

Else

Worksheets("БД").Cells(i, 7) = "Нет"

End If

Worksheets("БД").Cells(i, 8) = UserForm1.TextBox5.Text + " руб."

Worksheets("БД").Cells(i, 9) = UserForm1.TextBox2.Text

Worksheets("БД").Cells(i, 10) = UserForm1.TextBox6.Text + " раб.дн."

If UserForm1.OptionButton3 = True Then Worksheets("БД").Cells(i, 11).Value = "Есть семья"

If UserForm1.OptionButton4 = True Then Worksheets("БД").Cells(i, 11).Value = "Нет семьи"

If UserForm1.OptionButton5 = True Then Worksheets("БД").Cells(i, 12).Value = " M "

If UserForm1.OptionButton6 = True Then Worksheets("БД").Cells(i, 12).Value = " Ж "

Worksheets("БД").Cells(i, 3).Value = ComboBox1.Value

Worksheets("БД").Cells(i, 4).Value = ComboBox2.Value

Worksheets("БД").Cells(i, 5).Value = ComboBox3.Value

ll:

UserForm1.Hide

Worksheets("БД").Activate

End Sub

Обработчик события кнопки <Cancel>

Private Sub CommandButton2_Click()

UserForm1.Hide

Worksheets("БД").Activate

End Sub

Private Sub SpinButton1_Change()

UserForm1.TextBox2.Text = UserForm1.SpinButton1.Value

End Sub

Private Sub SpinButton2_Change()

UserForm1.TextBox5.Text = UserForm1.SpinButton2.Value

End Sub

Private Sub SpinButton3_Change()

UserForm1.TextBox6.Text = UserForm1.SpinButton3.Value

End Sub

UserForm2

Dim r, v As Integer

Обработчик события кнопки <OK>

rivate Sub CommandButton1_Click()

Worksheets("Задание4").Range("c10:h15").Value = ""

Worksheets("Задание4").Range("j11:j16").Value = ""

Worksheets("Задание4").Range("b2").Value = UserForm2.TextBox1

Worksheets("Задание4").Range("a2").Value = UserForm2.TextBox2

Worksheets("Задание4").Range("c2").Value = UserForm2.TextBox3

UserForm2.Hide

Range("C10:H15").FormulaArray = "=Модуль3.CALC(I11:I16)"

Range("J11:J16").FormulaArray = "=MMULT((C10:H15),TRANSPOSE(d7:i7))"

Range("f16").Select

ActiveCell.FormulaR1C1 = "=large(r[-5]c[4]:rc[4],1)"

Range("f17").Select

ActiveCell.FormulaR1C1 = "=(match(large(r[-6]c[4]:r[-1]c[4],1),r[-6]c[4]:r[-1]c[4],0)-1)*5"

r = Range("f16").Value

v = Range("f17").Value

'r = Format(r, "#.##")

UserForm3.Label3.Caption = Worksheets("Задание4").Range("f16")

UserForm3.Label4.Caption = Worksheets("Задание4").Range("f17")

UserForm3.Show

End Sub

Обработчик события кнопки <Cancel>

Private Sub CommandButton2_Click()

UserForm2.Hide

End Sub

UserForm3

Private Sub CommandButton1_Click()

UserForm3.Hide

End Sub

UserForm4

Private Sub CommandButton1_Click()

Worksheets("Раскрой").Range("j3").Value = UserForm4.TextBox2

Worksheets("Раскрой").Range("k3").Value = UserForm4.TextBox4

Worksheets("Раскрой").Range("l3").Value = UserForm4.TextBox5

Worksheets("Раскрой").Range("m3").Value = UserForm4.TextBox3

UserForm4.Hide

End Sub

Private Sub CommandButton2_Click()

End

End Sub

Private Sub CommandButton3_Click()

TextBox1 = " "

TextBox2 = " "

TextBox3 = " "

TextBox4 = " "

TextBox5 = " "

Worksheets("Раскрой").Range("j3").Value = " "

Worksheets("Раскрой").Range("k3").Value = " "

Worksheets("Раскрой").Range("l3").Value = " "

Worksheets("Раскрой").Range("m3").Value = " "

Worksheets("Раскрой").Range("a4: f273").Value = " "

End Sub

Размещено на Allbest.ru