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 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 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
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 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 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) = 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(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
End If
If p > 2 Then
A(j) = Cells(j + 3, p + 5).Value
For n = 1 To k + 1 m = -1
For j = 1 To n
If m
Cells(n + 3, 6 + p).Value = m l = t
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
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
Обработчик события кнопки 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
Обработчик события кнопки Private Sub CommandButton2_Click() UserForm1.Hide Worksheets("БД").Activate End Sub
UserForm2
Обработчик события кнопки 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
Обработчик события кнопки Private Sub CommandButton2_Click() UserForm2.Hide End Sub
UserForm3
Private Sub CommandButton1_Click() UserForm3.Hide End Sub
Страницы: 1, 2, 3, 4
