Xreferat.ru » Рефераты по информатике и программированию » Решение экономических задач с помощью VBA

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

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

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

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

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



Overview

Содержание
Задание1
Задание2
Задание3
Задание4
Задание5
БД
Опт.капитал
Раскрой


Sheet 1: Содержание


































































Sheet 2: Задание1

Месяц М а г а з и н ы







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

























Sheet 3: Задание2










Месяц М