информатика курсовая. Методические указания к выполнению курсовой работы по дисциплине Информатика

• Название: Методические указания к выполнению курсовой работы по дисциплине Информатика
• Анкор: информатика курсовая
• Дата: 11.11.2021
• Размер: 0.89 Mb.
• Имя файла: metodichka_kursov_dopoln_izm_2019 (1).docx
• Тип: Методические указания; #268855
• страница: 13 из 14

ПРИЛОЖЕНИЕ 1

ГОСУДАРСТВЕННОЕ ОБРАЗОВАТЕЛЬНОЕ УЧРЕЖДЕНИЕ
ВЫСШЕГО ПРОФЕССИОНАЛЬНОГО ОБРАЗОВАНИЯ

«ДОНЕЦКИЙ НАЦИОНАЛЬНЫЙ ТЕХНИЧЕСКИЙ УНИВЕРСИТЕТ»

Кафедра прикладной математики

ПОЯСНИТЕЛЬНАЯ ЗАПИСКА

к курсовой работе по дисциплине «Информатика»

на тему: «Создание автоматизированных бланков отчетности в MS Excel с использованием встроенного языка VBA for applications»

Выполнил студент ____________

(группа, факультет)

____________________________

(фамилия, инициалы)

Руководитель работы__________

____________________________

(фамилия, инициалы)

Донецк, 20__

ПРИЛОЖЕНИЕ 2

Содержание

Введение

1. 
   Постановка задачи.
   
2. 
   Теоретическая часть
   
3. 
   Описание алгоритма решения задачи
   
4. 
   Инструкция пользователя
   
5. 
   Заключение
   

Список использованных источников и литературы

Приложения

6. 
   
   

ПРИЛОЖЕНИЕ 3

Список использованных источников и литературы

1. 
   Экономико-математические модели и методы: Учеб. пособие для студ. экон. спец. БГУИР всех форм обуч. / С.А. Поттосина, В.А. Журавлев. – Мн.: БГУИР, 2003. – 94 с.: ил.
   
2. 
   Алесинская Т.В. Учебное пособие по решению задач по курсу "Экономико-математические методы и модели". Таганрог: Изд-во ТРТУ, 2002, 153 с.
   
3. 
   Расторгуев, Д. Н. Методические рекомендации к практическим занятиям по компьютерному моделированию социально-экономических процессов. – Ульяновск: УлГТУ, 2006. – 32 с.
   
4. 
   Кутузов А. Л. Математические методы в экономике и менеджменте Учеб пособие. СПб.: Изд-во СПб ГТУ, 2001.
   
5. 
   Курицкии Б. Я. Поиск оптимальных решений средствами Excel 7 0. СПб.: БХВ — Санкт-Петербург, 1997.
   
6. 
   URL: http://excel2.ru/articles/funkciya-prosmotr-v-ms-excel-prosmotr (дата последнего обращения 08.12.2016)
   
7. 
   URL: http://excel.nighthtr.ru/lab_8.html (дата последнего обращения 15.11.2016)
   
8. 
   URL: http://www.excelworld.ru/board/vba/udf/8 (дата последнего обращения 25.12.2016)
   

ПРИЛОЖЕНИЕ 4

Текст кода макроса список_заказов_бланк(форма «Список заказов за месяц»)

Sub Список_заказов_бланк()

Cells.Select

Selection.Clear

Range("B2").Select

ActiveCell.FormulaR1C1 = "Дата выполнения"

Range("C2").Select

ActiveCell.FormulaR1C1 = "Наименование фирмы"

Range("D2").Select

ActiveCell.FormulaR1C1 = "Код фирмы"

Range("E2").Select

ActiveCell.FormulaR1C1 = "Код заказа"

Range("F2").Select

ActiveCell.FormulaR1C1 = "Наименование товара"

Range("G2").Select

ActiveCell.FormulaR1C1 = "Код товара"

Range("H2").Select

ActiveCell.FormulaR1C1 = "Количество"

Range("I2").Select

ActiveCell.FormulaR1C1 = "Сумма, руб"

Range("B2:I2").Select

Selection.Font.Bold = True

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

With Selection

.HorizontalAlignment = xlGeneral

.VerticalAlignment = xlBottom

.WrapText = True

.Orientation = 0

.AddIndent = False

.IndentLevel = 0

.ShrinkToFit = False

.ReadingOrder = xlContext

.MergeCells = False

End With

With Selection

.HorizontalAlignment = xlCenter

.VerticalAlignment = xlBottom

.WrapText = True

.Orientation = 0

.AddIndent = False

.IndentLevel = 0

.ShrinkToFit = False

.ReadingOrder = xlContext

.MergeCells = False

End With

With Selection

.HorizontalAlignment = xlCenter

.VerticalAlignment = xlCenter

.WrapText = True

.Orientation = 0

.AddIndent = False

.IndentLevel = 0

.ShrinkToFit = False

.ReadingOrder = xlContext

.MergeCells = False

End With

Columns("B:B").ColumnWidth = 12.14

Columns("B:B").ColumnWidth = 13.14

Columns("C:C").ColumnWidth = 16.57

Columns("D:D").ColumnWidth = 9.71

Columns("E:E").ColumnWidth = 10

Columns("F:F").ColumnWidth = 16.71

Columns("H:H").ColumnWidth = 11.86

Selection.Font.Bold = False

Selection.Font.Bold = True

Range("B2:H93").Select

ActiveWindow.SmallScroll Down:=-87

Range("B2:I53").Select

ActiveWindow.SmallScroll Down:=-45

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

With Selection.Borders(xlInsideVertical)

.LineStyle = xlContinuous

.ColorIndex = 0

.TintAndShade = 0

.Weight = xlThin

End With

With Selection.Borders(xlInsideHorizontal)

.LineStyle = xlContinuous

.ColorIndex = 0

.TintAndShade = 0

.Weight = xlThin

End With

Range("B3:B53").Select

Selection.NumberFormat = "dd/mm/yy;@"

Range("C3:C53").Select

With Selection.Validation

.Delete

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _

xlBetween, Formula1:="=заказчик"

.IgnoreBlank = True

.InCellDropdown = True

.InputTitle = ""

.ErrorTitle = ""

.InputMessage = ""

.ErrorMessage = ""

.ShowInput = True

.ShowError = True

End With

Range("F3:F53").Select

ActiveWindow.SmallScroll Down:=-45

With Selection.Validation

.Delete

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _

xlBetween, Formula1:="=товар"

.IgnoreBlank = True

.InCellDropdown = True

.InputTitle = ""

.ErrorTitle = ""

.InputMessage = ""

.ErrorMessage = ""

.ShowInput = True

.ShowError = True

End With

Range("D3").Select

ActiveCell.FormulaR1C1 = _

"=IF(RC[-1]="""","""",LOOKUP(RC[-1],заказчик,заказчики!R3C1:R8C1))"

Range("G3").Select

ActiveCell.FormulaR1C1 = _

"=IF(RC[-1]="""","""",LOOKUP(RC[-1],товар,товары!R3C1:R8C1))"

Range("I3").Select

Range("I3").Select

ActiveCell.FormulaR1C1 = _

"=IF(ISERROR(RC[-1]*VLOOKUP(RC[-2],товары!R3C1:R8C5,5)),"""",RC[-1]*VLOOKUP(RC[-2],товары!R3C1:R8C5,5))"

Selection.AutoFill Destination:=Range("I3:I17"), Type:=xlFillDefault

Range("I3:I17").Select

Selection.AutoFill Destination:=Range("I3:I53"), Type:=xlFillDefault

Range("I3:I53").Select

ActiveWindow.SmallScroll Down:=-36

Range("I3").Select

Selection.AutoFill Destination:=Range("I3:I53"), Type:=xlFillDefault

Range("I3:I53").Select

ActiveWindow.SmallScroll Down:=-42

End Sub

Текст кода макроса бланк_заказа_фирма (форма «Форма заказа по названию фирмы-заказчика»)

Sub бланк_заказа_фирма()

Cells.Select

Selection.Clear

Range("B2").Select

ActiveCell.FormulaR1C1 = "Наименование фирмы"

Range("D2").Select

With Selection.Validation

.Delete

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _

xlBetween, Formula1:="=заказчик"

.IgnoreBlank = True

.InCellDropdown = True

.InputTitle = ""

.ErrorTitle = ""

.InputMessage = ""

.ErrorMessage = ""

.ShowInput = True

.ShowError = True

End With

Range("C4").Select

ActiveCell.FormulaR1C1 = "Дата выполнения"

Range("D4").Select

ActiveCell.FormulaR1C1 = "Код заказа"

Range("E4").Select

ActiveCell.FormulaR1C1 = "Наименование товара"

Range("F4").Select

ActiveCell.FormulaR1C1 = "Количество"

Range("G4").Select

ActiveCell.FormulaR1C1 = "Сумма, руб"

Range("C4:G4").Select

With Selection

.HorizontalAlignment = xlGeneral

.VerticalAlignment = xlBottom

.WrapText = True

.Orientation = 0

.AddIndent = False

.IndentLevel = 0

.ShrinkToFit = False

.ReadingOrder = xlContext

.MergeCells = False

End With

With Selection

.HorizontalAlignment = xlGeneral

.VerticalAlignment = xlCenter

.WrapText = True

.Orientation = 0

.AddIndent = False

.IndentLevel = 0

.ShrinkToFit = False

.ReadingOrder = xlContext

.MergeCells = False

End With

With Selection

.HorizontalAlignment = xlCenter

.VerticalAlignment = xlCenter

.WrapText = True

.Orientation = 0

.AddIndent = False

.IndentLevel = 0

.ShrinkToFit = False

.ReadingOrder = xlContext

.MergeCells = False

End With

Columns("E:E").ColumnWidth = 14.71

Columns("G:G").ColumnWidth = 13.43

Range("E2").Select

ActiveCell.FormulaR1C1 = "адрес:"

Range("H2").Select

ActiveCell.FormulaR1C1 = "телефон:"

Range("B2:C2").Select

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

Range("E2").Select

With Selection

.HorizontalAlignment = xlRight

.VerticalAlignment = xlBottom

.WrapText = False

.Orientation = 0

.AddIndent = False

.IndentLevel = 0

.ShrinkToFit = False

.ReadingOrder = xlContext

.MergeCells = False

End With

Selection.Font.Bold = True

Range("H2").Select

Selection.Font.Bold = True

With Selection

.HorizontalAlignment = xlRight

.VerticalAlignment = xlBottom

.WrapText = False

.Orientation = 0

.AddIndent = False

.IndentLevel = 0

.ShrinkToFit = False

.ReadingOrder = xlContext

.MergeCells = False

End With

Range("C4:G4").Select

Selection.Font.Bold = True

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

With Selection.Borders(xlInsideVertical)

.LineStyle = xlContinuous

.ColorIndex = 0

.TintAndShade = 0

.Weight = xlThin

End With

With Selection.Borders(xlInsideHorizontal)

.LineStyle = xlContinuous

.ColorIndex = 0

.TintAndShade = 0

.Weight = xlThin

End With

End Sub

Текст кода макроса список_заказов_фирма (форма «Форма заказа по названию фирмы-заказчика»)

Sub список_заказов_фирма()

Dim фирма As String, i As Integer, s As Double, n As Integer, j As Integer

Dim адрес As String, телефон As String

Dim дата(1 To 30) As Date, заказ(1 To 30) As String, товар(1 To 30) As String

Dim количество(1 To 30) As Integer, сумма(1 To 30) As Double

фирма = Cells(2, 4)

Range("A5:L259").Select

Selection.Clear

Sheets("заказчики").Select

j = 3

Do While Cells(j, 2) <> ""

If Cells(j, 2) = фирма Then

адрес = Cells(j, 3)

телефон = Cells(j, 4)

End If

j = j + 1

Loop

Sheets("список заказов").Select

i = 1

j = 3

s = 0

Do While Cells(j, 3) <> ""

If Cells(j, 3) = фирма Then

дата(i) = Cells(j, 2)

заказ(i) = Cells(j, 5)

товар(i) = Cells(j, 6)

количество(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

Sheets("список заказов фирма").Select
j = 5

For i = 1 To n

Cells(j, 3) = дата(i)

Cells(j, 4) = заказ(i)

Cells(j, 5) = товар(i)

Cells(j, 6) = количество(i)

Cells(j, 7) = сумма(i)

j = j + 1

Next i

Cells(j + 1, 3) = "Итого по " + фирма

Cells(j + 1, 7) = s

Cells(2, 6) = адрес

Cells(2, 9) = телефон

End Sub

Текст кода макроса бланк_формы_заказа (форма «Форма заказа по номеру»)

Sub бланк_формы_заказа()

Cells.Select

Selection.Clear

Range("B1").Select

ActiveCell.FormulaR1C1 = "ООО ""Инициатива"""

Range("C2").Select

ActiveCell.FormulaR1C1 = "Заказ №"

Range("B3").Select

ActiveCell.FormulaR1C1 = "Дата выполнения"

Range("B4").Select

Columns("B:B").ColumnWidth = 17.43

Columns("C:C").ColumnWidth = 10.86

Range("C2").Select

With Selection

.HorizontalAlignment = xlRight

.VerticalAlignment = xlBottom

.WrapText = False

.Orientation = 0

.AddIndent = False

.IndentLevel = 0

.ShrinkToFit = False

.ReadingOrder = xlContext

.MergeCells = False

End With

Range("B5").Select

ActiveCell.FormulaR1C1 = "Наименование фирмы"

Range("C5").Select

ActiveCell.FormulaR1C1 = "Наименование товара"

Range("D5").Select

ActiveCell.FormulaR1C1 = "Код товара"

Range("E5").Select

ActiveCell.FormulaR1C1 = "Количество"

Range("F5").Select

ActiveCell.FormulaR1C1 = "Сумма"

Range("B1").Select

With Selection.Font

.Name = "Calibri"

.Size = 14

.Strikethrough = False

.Superscript = False

.Subscript = False

.OutlineFont = False

.Shadow = False

.Underline = xlUnderlineStyleNone

.ThemeColor = xlThemeColorLight1

.TintAndShade = 0

.ThemeFont = xlThemeFontMinor

End With

Selection.Font.Bold = True

Range("B1:C1").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

Range("C2").Select

Selection.Font.Bold = True

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

Range("B3").Select

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

Range("B5:F5").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

With Selection.Borders(xlInsideVertical)

.LineStyle = xlContinuous

.ColorIndex = 0

.TintAndShade = 0

.Weight = xlThin

End With

With Selection.Borders(xlInsideHorizontal)

.LineStyle = xlContinuous

.ColorIndex = 0

.TintAndShade = 0

.Weight = xlThin

End With

With Selection

.HorizontalAlignment = xlGeneral

.VerticalAlignment = xlBottom

.WrapText = True

.Orientation = 0

.AddIndent = False

.IndentLevel = 0

.ShrinkToFit = False

.ReadingOrder = xlContext

.MergeCells = False

End With

With Selection

.HorizontalAlignment = xlCenter

.VerticalAlignment = xlBottom

.WrapText = True

.Orientation = 0

.AddIndent = False

.IndentLevel = 0

.ShrinkToFit = False

.ReadingOrder = xlContext

.MergeCells = False

End With

With Selection

.HorizontalAlignment = xlCenter

.VerticalAlignment = xlCenter

.WrapText = True

.Orientation = 0

.AddIndent = False

.IndentLevel = 0

.ShrinkToFit = False

.ReadingOrder = xlContext

.MergeCells = False

End With

Columns("C:C").ColumnWidth = 12.86

Columns("C:C").ColumnWidth = 14.71

Columns("E:E").ColumnWidth = 11.71

Columns("F:F").ColumnWidth = 12

Range("B6").Select

End Sub