Форматирование таблицы
1. В блоке ячеек A2 : F2
- На вкладке выравнивание окна формат ячеек устанавливаем перенос текста по словам;
- значения данных выравниваем по центру по горизонтали и по верхнему краю по вертикали;
- устанавливаем шрифт “полужирный”,
2. Устанавливаем в столбцах A, B, C, E, F такую ширину столбцов, чтобы шапка таблицы была такой же, как показано на образце (рис. 8).
3. Выделяем блок ячеек A1:F1 и объединяем их, располагая информацию в центре. Устанавливаем в объединенной ячейке шрифт Arial Cyr, размер кегля 14, жирный, горизонтальное выравнивание – по центру, вертикальное – по нижнему краю.
4. Внутренние и внешние границы ячеек разделяем тонкими линиями, текст с данными таблицы сверху и снизу отделяем двойными тонкими линиями, объединяем ячейки A11:E11, информацию в объединенной ячейке выравниваем по правому краю.
5. Выделяем диапазоны ячеек A3:A10 и E3:E10 устанавливаем для них формат ячеек Числовой с количеством десятичных знаков, равным 0.
6. Выделяем диапазоны ячеек D3:D10 и F3:F11, и устанавливаем для них формат ячеек Денежный с количеством десятичных знаков, равным 2 и с обозначением денежных единиц в рублях.
7. Выделяем диапазон ячеек A3:C10 и устанавливаем для него выравнивание по горизонтали по левому краю.
8. В диапазоне ячеек D3:D10 устанавливаем горизонтальное выравнивание по центру.
9. В диапазоне ячеек E3:F10 станавливаем горизонтальное выравнивание по правому краю.
10. Слово «Итого:» объединенной ячейки и общую сумму поставок товаров выделяем жирным шрифтом и курсивом.
В результате получим макрос:
Sub Форматирование()
'
' Форматирование Макрос
' Макрос записан 09.10.2015 (Neklyudova)
'
Range("A2:F2").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlTop
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Font.Bold = True
Columns("A:A").ColumnWidth = 11
Columns("A:A").ColumnWidth = 8.71
Columns("B:B").ColumnWidth = 13
Columns("B:B").ColumnWidth = 14.29
Columns("B:B").ColumnWidth = 15.43
Columns("C:C").ColumnWidth = 12.14
Columns("D:D").ColumnWidth = 11.14
Columns("E:E").ColumnWidth = 11.71
Columns("E:E").ColumnWidth = 11.86
Columns("F:F").ColumnWidth = 15.57
Range("A1:F1").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.Font
.Name = "Arial"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
With Selection.Font
.Name = "Arial"
.Size = 14
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Selection.Font.Bold = True
Range("A2:F11").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
Range("A11:E11").Select
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Range("A3:F3").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 = xlDouble
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThick
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
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("A10:F10").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 = xlDouble
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThick
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
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("D3:D10,F3:F11").Select
Range("F3").Activate
Selection.NumberFormat = "#,##0.00$"
Range("A3:C10").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("D3:D10").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("E3:F10").Select
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("A11:F11").Select
Selection.Font.Bold = True
Selection.Font.Italic = True
End Sub
Ввод данных
Ниже приведена таблица с исходными данными (Код товара, Наименование товара, Единица измерения, Цена, Количество) и результатными данными, полученными при выполнении макросов ЗаголовокШапка, Формулы, Форматирование и ВводДанных.
| Поставка товаров | |||||
| Код товара | Наименование товара | Единица измерения | Цена | Количество | Сумма |
| Товар1 | шт | 100,00р. | 50 000,00р. | ||
| Товар2 | шт | 200,00р. | 1 000 000,00р. | ||
| Товар3 | шт | 300,00р. | 15 000 000,00р. | ||
| 0,00р. | |||||
| 0,00р. | |||||
| 0,00р. | |||||
| 0,00р. | |||||
| 0,00р. | |||||
| Итого: | 16 050000,00р. |
Рис.19. Результат выполнения макросов
В результате записи макроса получим следующий программный код:
Sub ВводДанных()
'
' ВводДанных Макрос
' Макрос записан 09.10.2054 (Neklyudova)
'
Range("A3").Select
ActiveCell.FormulaR1C1 = "101"
Range("B3").Select
ActiveCell.FormulaR1C1 = "Товар1"
Range("C3").Select
ActiveCell.FormulaR1C1 = "шт."
Range("D3").Select
ActiveCell.FormulaR1C1 = "100"
Range("E3").Select
ActiveCell.FormulaR1C1 = "500"
Range("A4").Select
ActiveCell.FormulaR1C1 = "102"
Range("B4").Select
ActiveCell.FormulaR1C1 = "Товар2"
Range("C4").Select
ActiveCell.FormulaR1C1 = "шт."
Range("D4").Select
ActiveCell.FormulaR1C1 = "200"
Range("E4").Select
ActiveCell.FormulaR1C1 = "5000"
Range("A5").Select
ActiveCell.FormulaR1C1 = "103"
Range("B5").Select
ActiveCell.FormulaR1C1 = "Товар3"
Range("C5").Select
ActiveCell.FormulaR1C1 = "шт."
Range("D5").Select
ActiveCell.FormulaR1C1 = "300"
Range("E5").Select
ActiveCell.FormulaR1C1 = "50000"
Range("A6").Select
End Sub
Итак, в результате получен программный код, содержащий макросы, которые были получены при выполнении пунктов:
- Ввод заголовка и “шапки” таблицы
- Ввод расчётных формул
- Форматирование таблицы
- Ввод данных.
Option Explicit
 |
Sub ЗаголовокШапка()
'
' ЗаголовокШапка Макрос
' Макрос записан 09.01.2015 (Neklyudova)
'
ActiveCell.FormulaR1C1 = "Поставка товаров"
Range("A2").Select
ActiveCell.FormulaR1C1 = "Код товара"
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("F3").Select
End Sub
| |
Sub Формулы()
' Формулы Макрос
' Макрос записан 09.10.2015 (Neklyudova)
'
ActiveCell.FormulaR1C1 = "=RC[-2]*RC[-1]"
Range("F3").Select
Selection.AutoFill Destination:=Range("F3:F10"), Type:=xlFillDefault
Range("F3:F10").Select
Range("E11").Select
ActiveCell.FormulaR1C1 = "Итого:"
Range("F11").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-8]C:R[-1]C)"
Range("F12").Select
Range("F12").Select
End Sub
 |
Sub Форматирование()
'
' Форматирование Макрос
' Макрос записан 09.10.2015 (Neklyudova)
'
Range("A2:F2").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlTop
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Font.Bold = True
Columns("A:A").ColumnWidth = 11
Columns("A:A").ColumnWidth = 8.71
Columns("B:B").ColumnWidth = 13
Columns("B:B").ColumnWidth = 14.29
Columns("B:B").ColumnWidth = 15.43
Columns("C:C").ColumnWidth = 12.14
Columns("D:D").ColumnWidth = 11.14
Columns("E:E").ColumnWidth = 11.71
Columns("E:E").ColumnWidth = 11.86
Columns("F:F").ColumnWidth = 15.57
Range("A1:F1").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.Font
.Name = "Arial"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
With Selection.Font
.Name = "Arial"
.Size = 14
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Selection.Font.Bold = True
Range("A2:F11").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
Range("A11:E11").Select
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Range("A3:F3").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 = xlDouble
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThick
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
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("A10:F10").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 = xlDouble
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThick
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
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("D3:D10,F3:F11").Select
Range("F3").Activate
Selection.NumberFormat = "#,##0.00$"
Range("A3:C10").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("D3:D10").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("E3:F10").Select
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("A11:F11").Select
Selection.Font.Bold = True
Selection.Font.Italic = True
End Sub
 |
Sub ВводДанных()
'
' ВводДанных Макрос
' Макрос записан 09.10.2015 (Neklyudova)
'
Range("A3").Select
ActiveCell.FormulaR1C1 = "101"
Range("B3").Select
ActiveCell.FormulaR1C1 = "Товар1"
Range("C3").Select
ActiveCell.FormulaR1C1 = "шт."
Range("D3").Select
ActiveCell.FormulaR1C1 = "100"
Range("E3").Select
ActiveCell.FormulaR1C1 = "500"
Range("A4").Select
ActiveCell.FormulaR1C1 = "102"
Range("B4").Select
ActiveCell.FormulaR1C1 = "Товар2"
Range("C4").Select
ActiveCell.FormulaR1C1 = "шт."
Range("D4").Select
ActiveCell.FormulaR1C1 = "200"
Range("E4").Select
ActiveCell.FormulaR1C1 = "5000"
Range("A5").Select
ActiveCell.FormulaR1C1 = "103"
Range("B5").Select
ActiveCell.FormulaR1C1 = "Товар3"
Range("C5").Select
ActiveCell.FormulaR1C1 = "шт."
Range("D5").Select
ActiveCell.FormulaR1C1 = "300"
Range("E5").Select
ActiveCell.FormulaR1C1 = "50000"
Range("A6").Select
End Sub
Сохраните книгу, содержащую эти макросы под именем Запись_макроса_исходная.
Макросы можно корректировать в редакторе кода VBE. Например, макрос ЗаголовокШапка можно упростить следующим образом:
- удалить инструкции активизации ячеек;
- удалить строки кода, определяющие свойства шрифта надписей;
- использовать инструкцию присваивания в стиле Range.
В результате произведенных изменений получим следующий код:
Option Explicit
Sub ЗаголовокШапка()
'
' ЗаголовокШапка Макрос
' Макрос записан 09.10.2015 (Neklyudova)
'
Range("A1") = "Поставка товаров"
Range("A2") = "Код товара"
Range("B2") = "Наименование товара"
Range("C2") = "Единица измерения"
Range("D2") = "Цена"
Range("E2") = "Количество"
Range("F2") = "Сумма"
End Sub
Макрос Формулы можно откорректировать так:
Sub Формулы()
'
' Формулы Макрос
' Макрос записан 09.10.2015 (Neklyudova)
'
Range("F3").Select
ActiveCell = "=D3*E3"
Selection.AutoFill Destination:=Range("F3:F10"), Type:=xlFillDefault
Range("E11") = "Итого:"
Range("F11") = "=SUM(F3:F10)"
End Sub
Примечание
Если нет опасности перегрузить оперативную память компьютера или пользователь не обладает достаточной квалификацией в области программирования на VBA, то лучше оставить макрос таким, как его спроектировал Excel.
Пока у вас нет достаточных знаний для полного понимания кода откорректируем только один макрос ЗаголовокШапка.
Создайте копию книги Запись_макроса_исходная под именем Запись_макроса_отредактированная. При этом в новую книгу будут скопированы все макросы из исходной книги.Измените макрос ЗаголовокШапка как показано выше.
Чтобы удостовериться в том, что при выполнении изменённого макроса получился тот же самый результат, очистить заполненные ячейки, выполнить записанный макрос и сравнить результат с рис.11.
Процедуры не могут в своём составе содержать вложенные процедуры, но они могут вызывать друг друга. Одним из способов вызова процедуры является использование оператора Call. Добавив в конец макроса ЗаголовокШапка три оператора
Call Формулы
Call Форматирование
Call ВводДанных
макросы Формулы, Форматирование и ВводДанных будут выполняться при вызове на выполнение макроса ЗаголовокШапка, в результате чего на рабочий лист будут выведены название таблицы и заголовки столбцов и выполнены инструкции макросов Формулы, Форматирование и ВводДанных, т.е. получена таблица, представленная на рис. 8.
Создайте копию книги Запись_макроса_отредактированная под именем Запись_макроса_шаблон. Удалите листы: Формулы, Форматирование и ВводДанных. Переименуйте лист Шапка в Поставка товаров. Измените макрос ЗаголовокШапка, сохраните книгу с поддержкой макросов.
Итак, у вас имеются три рабочих книги:
Запись_макроса_исходная
Запись_макроса_отредактированная
Запись_макроса_шаблон
В рабочей книге Запись_макроса_исходная хранятся макросы, полученные в результате записи в кодах VBA действий пользователя: ЗаголовокШапка, Формулы, Форматирование и ВводДанных. На рабочем листе Шапка приведён результат выполнения макроса Шапка, на листе Формулы – последовательности макросов Шапка ® Формулы, на листе Форматирование – последовательности макросов Шапка ® Формулы ® Форматирование и на листе ВводДанных – макросов Шапка ®Формулы ® Форматирование ® ВводДанных.
Конечно, все действия пользователя, включая ввод исходных данных, можно записать в один макрос, но это неудобно при отладке макросов.
В рабочей книге Запись_макроса_отредактированная макрос ЗаголовокШапка отредактирован c целью его упрощения. В обеих этих книгах для наглядности рабочие листы: Лист1, Лист2, Лист3, Лист4 – переименованы соответственно в Шапка, Формулы, Форматирование и ВводДанных.
В рабочей книге Запись_макроса_Шаблон содержится один рабочий лист Поставка товаров, и в конец макроса ЗаголовокШапка добавлены инструкции вызова макросов Формулы, Форматирование и ВводДанных:
В данной лабораторной работе макрос ВводДанных приведён в качестве иллюстрации возможности записи в макрос исходных данных. На практике это может быть использовано при вводе данных в таблицы, в которых меняется небольшая часть их значений.