информатика курсовая. Методические указания к выполнению курсовой работы по дисциплине Информатика
 Скачать 0.89 Mb.
|
|
Текст кода макроса форма_заказа (форма «Форма заказа по номеру») Sub форма_заказа() Dim заказ As Integer Dim i As Integer, j As Integer, s As Double, pr As String Dim дата As Date, фирма(1 To 30) As String, товар(1 To 30) As String Dim код_товара(1 To 30) As Integer, количество(1 To 30) As Integer, сумма(1 To 30) As Double Range("A6:L259").Select Selection.Clear заказ = InputBox("Введите номер заказа", "Ввод данных") Sheets("список заказов").Select i = 1 j = 3 s = 0 pr = "" Do While Cells(j, 5) <> "" If Cells(j, 5) = заказ Then pr = "есть" дата = Cells(j, 2) фирма(i) = Cells(j, 3) товар(i) = Cells(j, 6) код_товара(i) = Cells(j, 7) количество(i) = Cells(j, 8) сумма(i) = Cells(j, 9) s = s + сумма(i) i = i + 1 j = j + 1 Else j = j + 1 End If Loop n = i - 1 If pr <> "" Then Sheets("форма заказа").Select j = 6 For i = 1 To n Cells(j, 2) = фирма(i) Cells(j, 3) = товар(i) Cells(j, 4) = код_товара(i) Cells(j, 5) = количество(i) Cells(j, 6) = сумма(i) j = j + 1 Next i Cells(j, 3) = "Итого " Cells(j, 6) = s Cells(j + 1, 5) = "Принял" Cells(2, 4) = заказ Cells(3, 3) = дата Else MsgBox "Заказа с таким номером нет" End If End Sub Текст кода макроса итого_по_фирмам_бланк (форма «Итоговые суммы заказов по фирмам-заказчикам») Sub итого_по_фирмам_бланк() Cells.Select Selection.Clear Range("C1").Select ActiveCell.FormulaR1C1 = "Итоговые суммы заказов по фирмам-зазказчикам" Range("C3").Select ActiveCell.FormulaR1C1 = "Наименование фирмы" Range("F3").Select ActiveCell.FormulaR1C1 = "Всего, руб" Range("B1:G1").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With With Selection .HorizontalAlignment = xlGeneral .VerticalAlignment = xlCenter .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With With Selection.Font .Name = "Calibri" .Size = 12 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ThemeColor = xlThemeColorLight1 .TintAndShade = 0 .ThemeFont = xlThemeFontMinor End With Selection.Font.Bold = True With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With Range("C3:D3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge Selection.Font.Bold = True Columns("D:D").ColumnWidth = 12.71 Range("F3:G3").Select Selection.Font.Bold = True With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With Range("C4").Select Sheets("заказчики").Select Range("B3:B8").Select Selection.Copy Sheets("итого по фирмам").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("C4:D4").Select Application.CutCopyMode = False With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge With Selection .HorizontalAlignment = xlLeft .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With Selection.Font.Bold = True Selection.Font.Italic = True Selection.Copy Range("C5").Select Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False Selection.Copy Range("C6").Select Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Range("C7").Select Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Range("C8").Select Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Range("C9").Select Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False Range("F4:G4").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge With Selection .HorizontalAlignment = xlRight .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With Selection.NumberFormat = "0.00" Selection.Copy Range("F5").Select Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Range("F6").Select Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Range("F7").Select Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Range("F8").Select Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Range("F9").Select Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False Range("C3:G9").Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .ColorIndex = xlAutomatic .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .ColorIndex = xlAutomatic .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .ColorIndex = xlAutomatic .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .ColorIndex = xlAutomatic .TintAndShade = 0 .Weight = xlThin End With Selection.Borders(xlInsideVertical).LineStyle = xlNone With Selection.Borders(xlInsideHorizontal) .LineStyle = xlContinuous .ColorIndex = xlAutomatic .TintAndShade = 0 .Weight = xlThin End With Range("C3:D9").Select Range("C3:E9").Select Range("C3:E9").Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With Selection.Borders(xlInsideVertical).LineStyle = xlNone With Selection.Borders(xlInsideHorizontal) .LineStyle = xlContinuous .ColorIndex = xlAutomatic .TintAndShade = 0 .Weight = xlThin End With Range("C10:E10").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge ActiveCell.FormulaR1C1 = "Общий итог" Range("C10:E10").Select Selection.Font.Bold = True With Selection .HorizontalAlignment = xlLeft .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With Range("F10:G10").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge With Selection .HorizontalAlignment = xlRight .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With Selection.NumberFormat = "0.00" Range("C13").Select ActiveCell.FormulaR1C1 = "=TODAY()" Selection.Font.Italic = True Range("F13").Select ActiveCell.FormulaR1C1 = "менеджер" Range("F13").Select Selection.Font.Italic = True Range("G16").Select Range("F10:G10").Select ActiveCell.FormulaR1C1 = "=SUM(R[-6]C:R[-1]C[1])" Range("F11").Select End Sub Текст кода макроса(форма «Итоговые суммы заказов по фирмам-заказчикам») Function общая_сумма_по_фирме(фирма As String) As Double Dim i As Integer, s As Double i = 3 s = 0 Do While Worksheets("список заказов").Cells(i, 3) <> "" If Worksheets("список заказов").Cells(i, 3) = фирма Then s = s + Worksheets("список заказов").Cells(i, 9) End If i = i + 1 Loop общая_сумма_по_фирме = s End Function |