Реферат: Решение экономических задач с помощью VBA

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

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)

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 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:

Option 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

UserFORM1

Обработчик события кнопки <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

UserForm2

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

Private 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

  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