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