8.3.8. Форматирование данных в таблицах

При работе с электронными таблицами бывает необходимо не только ввести табличные данные и выполнить расчеты, но и представить информацию в нужном формате.

При работе с Excel форматирование можно выполнять на уровне страниц, строк и столбцов, отдельных ячеек и символов текста, представляющих значения ячеек.

Для определения формата страницы следует выполнить команду Параметры страницы меню Файл. В открывшемся диалоговом окне, выбрав соответствующую вкладку, можно определить характеристики страниц (размер бумаги и ориентацию, а также другие свойства), размеры полей, ввести или отменить колонтитулы, выводимые при печати внизу и вверху каждой страницы, задать способ печати содержимого рабочих листов.

Ниже приведена процедура форматирования страницы с помощью которой устанавливаются следующие параметры: размер бумаги – A4; ориентация – книжная («портрет»); качество печати – 300 точек на дюйм; в центра верхнего колонтитула выводится строка "Пример форматирования страницы"; в нижний колонтитул включается дата (слева), номер страницы (в центре) и время (справа); размеры всех полей – 2 см (размеры переведены в дюймы); отступы колонтитулов – по 1,3 см (переведены в дюймы); таблица при печати центрируется по горизонтали и по вертикали; сетка не печатается; комментарии не печатаются; номер первой страницы определяется автоматически; установлены в качестве сквозных первая строка и первый столбец (если таблица не войдет при печати на одну страницу, они будут выводиться на каждой странице); если таблица разбивается на несколько страниц, то порядок их печати будет «вниз, затем вправо». Текст процедуры можно получить с помощью средств макрогенерации.

Sub Параметры_Страницы()

With ActiveSheet.PageSetup

.PrintTitleRows = "$1:$1"

.PrintTitleColumns = "$A:$A"

End With

ActiveSheet.PageSetup.PrintArea = ""

With ActiveSheet.PageSetup

.LeftHeader = ""

.CenterHeader = "Пример форматирования страницы"

.RightHeader = ""

.LeftFooter = "&D"

.CenterFooter = "&P"

.RightFooter = "&T"

.LeftMargin = _

Application.InchesToPoints(0.78740157480315)

.RightMargin = _

Application.InchesToPoints(0.78740157480315)

.TopMargin = _

Application.InchesToPoints(0.78740157480315)

.BottomMargin = _

Application.InchesToPoints(0.78740157480315)

.HeaderMargin = _

Application.InchesToPoints(0.511811023622047)

.FooterMargin = _

Application.InchesToPoints(0.511811023622047)

.PrintHeadings = False

.PrintGridlines = False

.PrintComments = xlPrintNoComments

.PrintQuality = 300

.CenterHorizontally = True

.CenterVertically = True

.Orientation = xlPortrait

.Draft = False

.PaperSize = xlPaperA4

.FirstPageNumber = xlAutomatic

.Order = xlDownThenOver

.BlackAndWhite = False

.Zoom = 100

End With

End Sub

Excel позволяет вставить разметку страниц в таблицу «принуди­тель­но» с помощью команды Разрыв страницы меню Вставка. Эта команда выполняется программно с помощью оператора

ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell

Часть информации можно задать непосредственно с помощью команды Печать в меню Файл.

С помощью автоформатирования автоматически выполняется форматирование данных на рабочем листе. Для его активизации следует выделить таблицу, подлежащую форматированию, и выполнить команду Автоформат в меню Формат. В открывшемся диалоговом окне можно выбрать из списка подходящий формат, просматривая образцы. Изменить некоторые параметры можно с помощью кнопки Параметры. Для программного обращения к этой команде используется оператор

Selection.AutoFormat _

Format:=xlRangeAutoFormatClassic1, Number:=True, _

Font:= False, Alignment:=True, Border:=True, _

Pattern:=True, Width:=True

который указывает выбранный из списка существующих формат и применяемые параметры форматирования.

Excel предоставляет возможность работы с набором шрифтов. Шрифт устанавливается при форматировании ячейки. Для установки шрифта следует выделить нужную ячейку или диапазон ячеек. Применить установку шрифта или стиля можно не только ко всей ячейке, но и к отдельному фрагменту текста, размещенного в ячейке. Excel дает также возможность изменить цвет шрифта, который используется для ввода и отображения данных (через команду форматирования ячейки и вкладку «Шрифт»).

Если в ячейку вводится большой текстовый фрагмент, то при форматировании ячейки можно включить режим переноса по словам, чтобы сформатировать абзац внутри ячейки. В этом режиме текст будет выводиться не в одну строку, а будет формироваться абзац по ширине столбца, при этом высота ячейки автоматически регулируется таким образом, чтобы весь текстовый фрагмент поместился в ячейке.

Для улучшения представления данных, повышения их наглядности можно изменить параметры выравнивания (горизонтального и вертикального), а также изменить ориентацию текста, наклон строк. По умолчанию для ячеек установлены следующие типы горизонтального выравнивания для данных: числа выравниваются по правому краю, текст – по левому краю, логические значения – по центру, ошибки – по центру. Параметры выравнивания можно изменить с помощью соответствующих установок на вкладке «Выравнивание» диалогового окна форматирования ячеек.

При вводе заголовков таблиц или надписей на рабочих листах их приходится размещать в нескольких столбцах и соответственно центрировать. Excel предоставляет для автоматизации этих действий возможность центрирования текста по нескольким выделенным столбцам. Кроме того, существует возможность объединения нескольких соседних ячеек, но используя эту операцию, следует помнить, что объединение ячеек может помешать выполнению некоторых команд при работе с таблицей (например, команд меню Данные).

Данные, представленные виде таблицы, в Excel можно оформить на профессиональном уровне и придать им более привлекательный вид с помощью обрамления и заливки ячеек. Существуют разные типы обрамления, они отличаются типом и толщиной линий, цветом. Для выделенных ячеек можно также установить цвет фона (заливки) и узор. Эти установки можно сделать на вкладках «Границы» и «Вид» диалогового окна форматирования ячеек.

Для изменения формата собственно данных следует выделить содержащие эти данные ячейки и изменить их формат с помощью команды ФорматЯчейки, установив нужные параметры на вкладке «Число» диалогового окна. При форматировании данных сначала выбирается из списка нужный формат числа (тип данных), а затем, если это необходимо для выбранного формата, настраиваются его параметры (например, количество знаков после запятой, присутствие разделителя или названия денежной единицы).

Определить нужные значения параметров форматирования можно с помощью справочной системы или путем записи макроса, при выполнении которого осуществляются все необходимые установки. Например:

Sub Формат_ячейки()

Selection.NumberFormat = "h:mm:ss"

With Selection

.HorizontalAlignment = xlCenter

.VerticalAlignment = xlCenter

.WrapText = False

.Orientation = 0

.ShrinkToFit = True

.MergeCells = False

End With

With Selection.Font

.Name = "Arial Cyr"

.FontStyle = "обычный"

.Size = 9

.Strikethrough = False

.Superscript = False

.Subscript = False

.OutlineFont = False

.Shadow = False

.Underline = xlUnderlineStyleDoubleAccounting

.ColorIndex = xlAutomatic

End With

Selection.Borders(xlDiagonalDown).LineStyle = xlNone

Selection.Borders(xlDiagonalUp).LineStyle = xlNone

Selection.Borders(xlEdgeLeft).LineStyle = xlNone

Selection.Borders(xlEdgeRight).LineStyle = xlNone

With Selection.Borders(xlEdgeTop)

.LineStyle = xlContinuous

.Weight = xlThin

.ColorIndex = xlAutomatic

End With

With Selection.Borders(xlEdgeBottom)

.LineStyle = xlContinuous

.Weight = xlThin

.ColorIndex = xlAutomatic

End With

With Selection.Interior

.ColorIndex = 15

.Pattern = xlSolid

.PatternColorIndex = xlAutomatic

End With

Selection.Locked = True

Selection.FormulaHidden = False

End Sub

Строка "h:mm:ss" в данном примере определяет формат представления времени («часы:минуты:секунды»). Эта строка представляет собой «шаблон», в соответствии с которым данные будут отображаться в ячейке при просмотре рабочего листа. Пользователь может определить собственные форматы для ввода специфических данных или для ускорения их поиска (например, формат номеров телефонов, формат преджстаавления денежных сумм).

Для ввода нового формата следует выполнить следующие действия:

• выполнить команду ФорматЯчейки;

• в диалоговом окне открыть вкладку «Число»;

• из списка «Числовые форматы» выбрать категорию «(все форматы)»;

• в списке «Тип» выбрать код формата, который наиболее соответствует тому формату, который создается;

• в строке ввода исправить формат, приведя его к нужному виду (действия по составлению кода формата описаны ниже);

• щелкнуть на кнопке OK для сохранения нового формата.

Ненужные форматы могут быть удалены в том же диалоговом окне (кнопка Удалить).

Коды форматов состоят последовательных секций для определения формата положительных чисел, формата отрицательных чисел, формата нулевых значений, формата текста.

Секции отделяются друг от друга точкой с запятой (символом ‘;’). Если в коде отсутствует секция, определяющая формат текста, то текст будет вводиться без форматирования. Если код будет состоять только из двух секций, то первая часть будет задавать формат положительных чисел и нулей, а вторая – формат отрицательных чисел. Если код состоит только из одной секции, то все числа будут принимать формат, заданный в ней. Символы, используемые при описании кода, приведены в табл. 3.

Например, с помощью шаблона, записанного в строке

+# ##0,00_ руб.;[Красный]-# ##0,00_ руб.;[Синий]0,00_ руб.;[Зеленый]"Ошибка"

для ячейки можно установить следующий формат: перед положительными и отрицательными числами отображается знак (плюс или минус); значения округляются до двух десятичных знаков после запятой; тысячи в записи числа отделяются разделителем; отрицательные числа показываются красным цветом, а нулевые значения – синим; после чисел через пробел добавляется наименование «руб.»; если вводится значение, которое Excel не распознает как число, зеленым цветом выводится слово "Ошибка".

Применить этот нестандартный формат в программе можно с помощью оператора

Selection.NumberFormat = _

"+#,##0.00_ руб\.;[Red]-#,##0.00_ руб\.;[Blue]0.00_ руб\.;[Green]""Ошибка"""

(Эта строка не соответствует той, которая вводится при определении формата в диалоговом окне форматирования ячейки, так как в программе на VBA запись должна соответствовать правилам языка VBA.)

Можно скопировать формат отдельной ячейки присвоить его целому диапазону ячеек. Для этого можно воспользоваться командами Копировать и Специальная вставка меню Правка, но можно также воспользоваться и кнопкой Формат по образцу стандартной панели инструментов.

Стиль – это набор всех параметров форматирования, одновременно присваиваемых выделенным ячейкам. Удобно ввести свой стиль, если часто используется одно и то же сочетание параметров форматирования (например, сочетание шрифта, размера символов, их начертания, шаблона для ввода данных и т.д.). При работе со стилями при изменении некоторых параметров стиля автоматически будут переформатированы все ячейки всех рабочих листов, отформатированные этим стилем. Для создания нового стиля или изменения существующего предусмотрена команда ФорматСтиль.

Для строк и столбцов рабочего листа можно задать их высоту и ширину соответственно. Столбцы и строки рабочего листа можно скрыть (например: при печати отчета можно скрыть столбцы, содержащие промежуточные результаты). Эти действия выполняются через меню формат (команды Строка и Столбец).

Сгенерировать нужный код можно с помощью средств записи макросов.

При разработке приложений на базе Excel можно использовать и команду условного форматирования. Например, приведенный ниже код устанавливает следующий формат: строка «отлично» отображается красным цветом, «хорошо» – зеленым, «удовлетворительно» – синим, а «неудовлетворительно» отображается цветом, установленным при обычном форматировании ячейки.

Sub Условное_форматирование()

Selection.FormatConditions.Delete

Selection.FormatConditions.Add _

Type:=xlCellValue, Operator:=xlEqual, _

Formula1:="=""отлично"""

Selection.FormatConditions(1).Font.ColorIndex = 3

Selection.FormatConditions.Add _

Type:=xlCellValue, Operator:=xlEqual, _

Formula1:="=""хорошо"""

Selection.FormatConditions(2).Font.ColorIndex = 50

Selection.FormatConditions.Add _

Type:=xlCellValue, Operator:=xlEqual, _

Formula1:="=""удовлетворительно"""

Selection.FormatConditions(3).Font.ColorIndex = 55

End Sub

При добавлении каждого нового условия можно задать соответствующие ему параметры форматирования (шрифт для текста, границу и заливку для ячейки).

Установленный формат можно изменять. Например, следующий оператор добавляет двойное подчеркивание к параметрам, соответствующим первому условию:

Selection.FormatConditions(1).Font.Underline=xlUnderlineStyleDouble

Пользователь может задать при определении условного формата различные операции сравнения. Например, в следующем коде устатавливается зеленый фон и граница для ячеек, значения которых попадают в диапазон от 3 до 5:

Selection.FormatConditions.Delete

Selection.FormatConditions.Add _

Type:=xlCellValue, Operator:=xlBetween, _

Formula1:="3", Formula2:="5"

With Selection.FormatConditions(1).Borders(xlLeft)

.LineStyle = xlContinuous

.Weight = xlThin

.ColorIndex = xlAutomatic

End With

With Selection.FormatConditions(1).Borders(xlRight)

.LineStyle = xlContinuous

.Weight = xlThin

.ColorIndex = xlAutomatic

End With

With Selection.FormatConditions(1).Borders(xlTop)

.LineStyle = xlContinuous

.Weight = xlThin

.ColorIndex = xlAutomatic

End With

With Selection.FormatConditions(1).Borders(xlBottom)

.LineStyle = xlContinuous

.Weight = xlThin

.ColorIndex = xlAutomatic

End With

Selection.FormatConditions(1).Interior.ColorIndex = 4

Условия могут налагаться как на значение ячейки, так и на формулу, записанную в ячейку. Код, соответствующий различным условиям и форматам, можно получить с помощью средств макрогенерации. Более подробная информация о средствах условного форматирования содержится в справочной системе.

Если во время работы с таблицей она не отображается на экране целиком и возникает необходимость просматривать параллельно две ее части, удаленные друг от друга, можно разбить окно на две части с помощью вешки разбивки, находящейся в верхней части вертикальной полосы прокрутки и правой части горизонтальной полосы. Программным способом установить вешку разбивки на нужном расстоянии от верхней и левой границ окна рабочего листа можно с помощью операторов вида:

ActiveWindow.SplitRow = 1

ActiveWindow.SplitColumn = 1

Эти операторы «закрепляют» в левой части окна один столбец, а сверху – одну строку, которые могут содержать, например, заголовки.