https://pastein.ru/t/vHg
Загрузка данных
Sub CreateAllInOneFile()
Dim wb As Workbook
Set wb = ActiveWorkbook
Application.ScreenUpdating = False
' Создаем 5 листов в одном файле (Лаб 7 + 4 задания Практической 6)
Do While wb.Worksheets.Count < 5
wb.Worksheets.Add After:=wb.Worksheets(wb.Worksheets.Count)
Loop
Dim ws0 As Worksheet, ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet, ws4 As Worksheet
Set ws0 = wb.Worksheets(1)
Set ws1 = wb.Worksheets(2)
Set ws2 = wb.Worksheets(3)
Set ws3 = wb.Worksheets(4)
Set ws4 = wb.Worksheets(5)
On Error Resume Next
ws0.Name = "Лаб. работа 7"
ws1.Name = "Задание 1"
ws2.Name = "Задание 2"
ws3.Name = "Задание 3"
ws4.Name = "Задание 4"
On Error GoTo 0
Dim i As Integer, j As Integer, r As Integer
' ==========================================
' 1. ЛАБОРАТОРНАЯ РАБОТА 7 (Товары)
' ==========================================
ws0.Cells.Clear
ws0.Cells.Font.Name = "Times New Roman"
ws0.Cells.Font.Size = 11
ws0.Range("A1").Value = "Номер"
ws0.Range("B1").Value = "Наименование" & vbLf & "товара"
ws0.Range("C1").Value = "Цена"
ws0.Range("D1").Value = "Цена"
With ws0.Range("A1:D1")
.Font.Bold = True
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
ws0.Range("B1").WrapText = True
Dim items7 As Variant
items7 = Array( _
Array(101, "Компьютер Р5-66", 999), Array(102, "КомпьютерР5-100", 1500), _
Array(103, "Компьютер 486-66", 780), Array(104, "Компьютер 486-75", 819), _
Array(201, "Принтер лазерный ОХ", 1300), Array(202, "Принтер лазерный ПХ", 740), _
Array(203, "Принтер струйный ЧБ", 299), Array(204, "Принтер струйный Ц", 460), _
Array(301, "Монитор 14”", 220), Array(302, "Монитор 15”", 337), Array(304, "Монитор 17”", 518))
For i = 0 To 10
ws0.Cells(i + 2, 1).Value = items7(i)(0)
ws0.Cells(i + 2, 1).HorizontalAlignment = xlLeft
ws0.Cells(i + 2, 2).Value = items7(i)(1)
ws0.Cells(i + 2, 3).Value = items7(i)(2)
Next i
ws0.Range("A13:B13").Merge
ws0.Range("A13").Value = "Курс доллара"
ws0.Range("A13").HorizontalAlignment = xlCenter
ws0.Range("C13").Value = 13
ws0.Range("C13").NumberFormat = "0""р."""
ws0.Range("C13").HorizontalAlignment = xlLeft
ws0.Range("D2:D12").Formula = "=C2*$C$13"
ws0.Range("C2:C12").NumberFormat = "[$$-409] #,##0.00"
ws0.Range("D2:D12").NumberFormat = "#,##0.00 ""р."""
ws0.Range("A1:D13").BorderAround LineStyle:=xlContinuous, Weight:=xlThin
ws0.Range("A1:D1").BorderAround LineStyle:=xlContinuous, Weight:=xlThin
ws0.Range("A13:D13").BorderAround LineStyle:=xlContinuous, Weight:=xlThin
ws0.Range("A1:D12").Borders(xlInsideVertical).LineStyle = xlContinuous
ws0.Columns("B").AutoFit
ws0.Columns("A").ColumnWidth = 9
ws0.Columns("C").ColumnWidth = 14
ws0.Columns("D").ColumnWidth = 16
' ==========================================
' 2. ПРАКТИЧЕСКАЯ 6 - ЗАДАНИЕ 1 (Мебель)
' ==========================================
ws1.Cells.Clear
ws1.Cells.Font.Name = "Calibri"
ws1.Cells.Font.Size = 11
ws1.Range("A1:E1").Merge
ws1.Range("A1").Value = "Заказ на мягкую мебель"
ws1.Range("A1").Font.Size = 14
ws1.Range("A1").HorizontalAlignment = xlCenter
ws1.Range("A3").Value = "№"
ws1.Range("B3").Value = "ТИП"
ws1.Range("C3").Value = "Цена за 1" & vbLf & "комплект"
ws1.Range("D3").Value = "Кол-во" & vbLf & "комплектов"
ws1.Range("E3").Value = "ВСЕГО"
ws1.Range("A3:E3").HorizontalAlignment = xlCenter
ws1.Range("A3:E3").VerticalAlignment = xlCenter
ws1.Range("A3:E3").WrapText = True
Dim t1 As Variant
t1 = Array( _
Array(1, """НАДЕЖДА""", 20000, 2), Array(2, """СВЕТЛАНА""", 15000, 4), _
Array(3, """РОЗА""", 24500, 3), Array(4, """АННА""", 45000, 4), _
Array(5, """НЕЗАБУДКА""", 13800, 5), Array(6, """СОЛНЦЕ""", 34900, 4), _
Array(7, """ДЕТСКАЯ""", 21870, 6), Array(8, """ОЛЬГА""", 15000, 10))
For i = 0 To 7
ws1.Cells(i + 4, 1).Value = t1(i)(0)
ws1.Cells(i + 4, 1).HorizontalAlignment = xlCenter
ws1.Cells(i + 4, 2).Value = t1(i)(1)
ws1.Cells(i + 4, 3).Value = t1(i)(2)
ws1.Cells(i + 4, 4).Value = t1(i)(3)
ws1.Cells(i + 4, 4).HorizontalAlignment = xlCenter
ws1.Cells(i + 4, 5).Formula = "=C" & (i + 4) & "*D" & (i + 4)
Next i
ws1.Range("B12").Value = "ИТОГО:"
ws1.Range("B12").Font.Bold = True
ws1.Range("C12").Formula = "=SUM(C4:C11)"
ws1.Range("D12").Formula = "=SUM(D4:D11)"
ws1.Range("D12").HorizontalAlignment = xlCenter
ws1.Range("E12").Formula = "=SUM(E4:E11)"
ws1.Range("E4:E12, C12:D12").Interior.Color = RGB(0, 255, 0)
ws1.Range("C4:C12, E4:E12").NumberFormat = "#,##0.00""р."""
ws1.Range("A3:E12").Borders.LineStyle = xlContinuous
ws1.Range("B14:D14").Merge: ws1.Range("B14").Value = "минимальная стоимость заказа": ws1.Range("E14").Formula = "=MIN(E4:E11)"
ws1.Range("B15:D15").Merge: ws1.Range("B15").Value = "максимальная стоимость заказа": ws1.Range("E15").Formula = "=MAX(E4:E11)"
ws1.Range("B16:D16").Merge: ws1.Range("B16").Value = "средняя стоимость заказ": ws1.Range("E16").Formula = "=AVERAGE(E4:E11)"
ws1.Range("B14:D16").Font.Bold = True
ws1.Range("E14:E16").Interior.Color = RGB(255, 192, 0)
ws1.Range("E14:E16").NumberFormat = "#,##0.00""р."""
ws1.Range("B14:E16").Borders.LineStyle = xlContinuous
ws1.Columns("A:E").AutoFit
' ==========================================
' 3. ПРАКТИЧЕСКАЯ 6 - ЗАДАНИЕ 2 (Зарплата)
' ==========================================
ws2.Cells.Clear
ws2.Range("A1:F1").Merge
ws2.Range("A1").Value = "Расчёт заработной платы за сентябрь 2011 г."
ws2.Range("A1").Font.Bold = True
ws2.Range("A1").HorizontalAlignment = xlCenter
ws2.Range("A2").Value = "№": ws2.Range("B2").Value = "ФИО"
ws2.Range("C2").Value = "Начисленная" & vbLf & "заработная плата руб."
ws2.Range("D2").Value = "НДФЛ (13%) руб.": ws2.Range("E2").Value = "ЗП к выдаче руб.": ws2.Range("F2").Value = "РАНГ"
ws2.Range("A2:F2").HorizontalAlignment = xlCenter
ws2.Range("A2:F2").WrapText = True
Dim t2 As Variant
t2 = Array( _
Array(1, "Иванов Александр Иванович", 5699.89), Array(2, "Петров Олег Яковлевич", 10250.15), _
Array(3, "Сидорова Ольга Ивановна", 6100.3), Array(4, "Федорова Марина Игоревна", 4370.6), _
Array(5, "Яковлева Елена Петровна", 6700.03))
For i = 0 To 4
ws2.Cells(i + 3, 1).Value = t2(i)(0)
ws2.Cells(i + 3, 2).Value = t2(i)(1)
ws2.Cells(i + 3, 3).Value = t2(i)(2)
ws2.Cells(i + 3, 4).Formula = "=C" & (i + 3) & "*13%"
ws2.Cells(i + 3, 5).Formula = "=C" & (i + 3) & "-D" & (i + 3)
ws2.Cells(i + 3, 6).Formula = "=RANK(C" & (i + 3) & ",$C$3:$C$7,1)"
Next i
ws2.Range("B8").Value = "ИТОГО:": ws2.Range("B8").Font.Bold = True: ws2.Range("B8").HorizontalAlignment = xlRight
ws2.Range("C8").Formula = "=SUM(C3:C7)": ws2.Range("D8").Formula = "=SUM(D3:D7)": ws2.Range("E8").Formula = "=SUM(E3:E7)"
ws2.Range("C3:E8").NumberFormat = "#,##0.00"
ws2.Range("D3:F7, E8").Interior.Color = RGB(191, 191, 191)
ws2.Range("A2:F8").Borders.LineStyle = xlContinuous
ws2.Range("B10").Value = "Максимально начисленная ЗП": ws2.Range("C10").Formula = "=MAX(C3:C7)"
ws2.Range("B11").Value = "Минимально начисленная ЗП": ws2.Range("C11").Formula = "=MIN(C3:C7)"
ws2.Range("B12").Value = "Средняя начисленная ЗП": ws2.Range("C12").Formula = "=AVERAGE(C3:C7)"
ws2.Range("C10:C12").NumberFormat = "#,##0.00"
ws2.Range("C10:C12").Interior.Color = RGB(191, 191, 191)
ws2.Range("C10:C12").Borders.LineStyle = xlContinuous
ws2.Columns("A:F").AutoFit
' ==========================================
' 4. ПРАКТИЧЕСКАЯ 6 - ЗАДАНИЕ 3 (Кондитерские)
' ==========================================
ws3.Cells.Clear
ws3.Range("A1:G1").Merge
ws3.Range("A1").Value = "Отчет о продажах в сети кондитерских за 01.09.11 и 02.09.11"
ws3.Range("A1").Font.Bold = True: ws3.Range("A1").HorizontalAlignment = xlCenter
ws3.Range("A3").Value = "№": ws3.Range("B3").Value = "Магазин": ws3.Range("C3").Value = "Продукция" & vbLf & "(торты)"
ws3.Range("D3").Value = "Дата": ws3.Range("E3").Value = "Объём ( в шт.)": ws3.Range("F3").Value = "Цена (за 1 шт.)": ws3.Range("G3").Value = "Продажа в день"
ws3.Range("A3:G3").Font.Bold = True: ws3.Range("A3:G3").Interior.Color = RGB(191, 191, 191): ws3.Range("A3:G3").HorizontalAlignment = xlCenter
Dim prods As Variant, prc As Variant, vols As Variant
prods = Array("Птичье молоко", "Черничный торт", "Яблочный торт", "Киевский", "Слоенный", "Прага")
prc = Array(350, 220, 200, 300, 280, 320)
vols = Array(42, 28, 43, 16, 12, 32, 45, 11, 38, 26, 17, 21, 35, 28, 43, 16, 12, 32, 45, 11, 38, 26, 20, 14)
For i = 0 To 23
r = i + 4
ws3.Cells(r, 1).Value = i + 1
ws3.Cells(r, 2).Value = IIf(i < 12, "ул. Чехова", "ул. Зеленоградская")
ws3.Cells(r, 3).Value = prods(i Mod 6)
ws3.Cells(r, 4).NumberFormat = "@"
ws3.Cells(r, 4).Value = IIf((i Mod 12) < 6, "01.09.2011", "02.09.2011")
ws3.Cells(r, 5).Value = vols(i)
ws3.Cells(r, 6).Value = prc(i Mod 6)
ws3.Cells(r, 7).Formula = "=E" & r & "*F" & r
Next i
ws3.Range("F4:G27").NumberFormat = "#,##0.00""р."""
ws3.Range("A3:G27").Borders.LineStyle = xlContinuous
ws3.Range("A29:C29").Merge: ws3.Range("A29").Value = "Объём продаж продукции болеее 40 шт.": ws3.Range("D29").Formula = "=COUNTIF(E4:E27,"">40"")"
ws3.Range("A30:C30").Merge: ws3.Range("A30").Value = "Объём продаж продукции менее 20 шт.": ws3.Range("D30").Formula = "=COUNTIF(E4:E27,""<20"")"
ws3.Range("A31:C31").Merge: ws3.Range("A31").Value = "Продажи более12 000 руб. в день": ws3.Range("D31").Formula = "=COUNTIF(G4:G27,"">12000"")"
ws3.Range("A32:C32").Merge: ws3.Range("A32").Value = "Продажи менее 6 000 руб. в день": ws3.Range("D32").Formula = "=COUNTIF(G4:G27,""<6000"")"
ws3.Range("D29:D32").Interior.Color = RGB(255, 192, 0)
ws3.Range("A29:D32").Borders.LineStyle = xlContinuous
ws3.Columns("A:G").AutoFit
' ==========================================
' 5. ПРАКТИЧЕСКАЯ 6 - ЗАДАНИЕ 4 (Расходы)
' ==========================================
ws4.Cells.Clear
ws4.Range("A1:I1").Merge: ws4.Range("A1").Value = "Расходы": ws4.Range("A1").Font.Bold = True: ws4.Range("A1").HorizontalAlignment = xlCenter
Dim h4 As Variant
h4 = Array("Месяц", "Кварплата", "Телефон", "Транспорт", "Питание", "Хоз. нужды", "Прочее", "Итого", "Ранжирование")
For i = 0 To 8: ws4.Cells(3, i + 1).Value = h4(i): Next i
ws4.Range("A3:I3").Font.Bold = True: ws4.Range("A3:I3").HorizontalAlignment = xlCenter
Dim t4 As Variant
t4 = Array( _
Array("Январь", 2200, 500, 1500, 6000, 450, 3000), Array("Февраль", 2400, 600, 1600, 4000, 360, 2470), _
Array("Март", 1900, 500, 1200, 5000, 460, 1900), Array("Апрель", 2300, 420, 1000, 4000, 540, 2200), _
Array("Май", 2400, 500, 1100, 6000, 540, 3200), Array("Июнь", 1250, 600, 2000, 3000, 640, 2900), _
Array("Июль", 1500, 800, 2000, 4000, 600, 2850), Array("Август", 1600, 900, 2000, 3000, 610, 2850), _
Array("Сентябрь", 2500, 900, 3000, 4000, 630, 3660), Array("Октябрь", 2300, 600, 1200, 6000, 620, 1650), _
Array("Ноябрь", 2600, 600, 1300, 5000, 610, 2670), Array("Декабрь", 2200, 700, 1500, 7000, 700, 1781))
For i = 0 To 11
r = i + 4
For j = 0 To 6: ws4.Cells(r, j + 1).Value = t4(i)(j): Next j
ws4.Cells(r, 8).Formula = "=SUM(B" & r & ":G" & r & ")"
ws4.Cells(r, 9).Formula = "=RANK(H" & r & ",$H$4:$H$15,0)"
Next i
ws4.Range("A16").Value = "Итого": ws4.Range("A17").Value = "Сред. значение"
ws4.Range("A18").Value = "Миним. расходы": ws4.Range("A19").Value = "Максим. расходы"
ws4.Range("A16:A19").Font.Bold = True
Dim cLet As Variant
cLet = Array("B", "C", "D", "E", "F", "G")
For i = 0 To 5
ws4.Range(cLet(i) & "16").Formula = "=SUM(" & cLet(i) & "4:" & cLet(i) & "15)"
ws4.Range(cLet(i) & "17").Formula = "=AVERAGE(" & cLet(i) & "4:" & cLet(i) & "15)"
ws4.Range(cLet(i) & "18").Formula = "=MIN(" & cLet(i) & "4:" & cLet(i) & "15)"
ws4.Range(cLet(i) & "19").Formula = "=MAX(" & cLet(i) & "4:" & cLet(i) & "15)"
Next i
ws4.Range("B4:H15, B16:G19").NumberFormat = "#,##0.00""р."""
ws4.Range("H4:I15, B16:G19").Interior.Color = RGB(191, 191, 191)
ws4.Range("A3:I19").Borders.LineStyle = xlContinuous
ws4.Range("A21:C21").Merge: ws4.Range("A21").Value = "Сумма за год": ws4.Range("D21").Formula = "=SUM(H4:H15)": ws4.Range("D21").NumberFormat = "#,##0.00""р."""
ws4.Range("A22:C22").Merge: ws4.Range("A22").Value = "Кол-во месяцев сумма которых более 12 000": ws4.Range("D22").Formula = "=COUNTIF(H4:H15,"">12000"")"
ws4.Range("A21:A22").Font.Bold = True: ws4.Range("D21:D22").Interior.Color = RGB(191, 191, 191): ws4.Range("A21:D22").Borders.LineStyle = xlContinuous
ws4.Columns("A:I").AutoFit
Application.ScreenUpdating = True
MsgBox "Все работы собраны в этом файле!", vbInformation, "Готово"
End Sub
