Использование языка программирования VBA

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

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

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

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

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

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

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

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

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

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

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

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 выводит эти оптимальные значения в окне сообщений.

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

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

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

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

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

    Например, по условию в соответствии с вариантом стандартная длина раскроя равна 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 
 

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

 
 

    Пусть 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    

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

5. База данных