3.2. Форматирование числовых данных

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

В этом случае в правой части вкладки Число появятся: поле со счетчиком, в котором можно задать количество отображаемых десятичных знаков; флажок, при установке которого используется разделитель тысячных разрядов (обычно это пробел); а также список для выбо­ра варианта отображения отрицательных чисел (с выделением красным цветом или без).

Рис. 3.2. Средства настройки числового формата

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

Например, если отформатировать ячейку так, как это показано на рис. 3.2, то после ввода в нее числа 7555888 оно отобразится в виде 7 555 888.00.

Наряду со средствами диалогового окна Формат ячеек, для форматирования чисел можно использовать кнопки панели инструментов Форматирование:

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

Увеличить разрядность. Щелкая на этой кнопке, можно увеличить число десятичных знаков до нужного количества. Например, если выделить ячейку с числом 5.1 и дважды щелкнуть на этой кнопке, в ячейке отобразится 5.100.

Уменьшить разрядность. Щелкая на этой кнопке, можно уменьшить число десятич­ных знаков до нужного количества. При уменьшении разрядности число отображается в округленном виде. Например, если выделить ячейку с числом 7.1564 и последовательно щелкать на этой кнопке, то число будет приобретать вид: 7.156, 7.16, 7.2 и, наконец, 7.

Денежные форматы. Поскольку в Excel часто выполняются финансовые расчеты, популярными встроенными форматами являются Денежный и Финансовый (см. рис. 3.1).

П

57.00 грн.

-7 500.50 грн.

-55.25 грн.

ри выборе формата Денежный в правой части вкладки Число появятся: поле со счет­чи­ком для задания количества отображаемых десятичных знаков (стандартно – два знака); раскрывающийся список, в котором можно выбрать необходимое обозначение денежной единицы (грн., Евро, $ США и др.); а также список для выбора варианта отображения отрицательных денежных сумм (с выделением красным цветом или без). Разделитель тысячных разрядов в этом формате используется по умолчанию.

Если каким-то ячейкам столбца присвоен формат Денежный, то введенные в эти ячейки числа выровняются по десятичной точке. Например:

Ф

57.00 грн.

- 7 500.50 грн.

- 55.25 грн.

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

Для присвоения выделенным ячейкам денежного формата можно использовать также расположенную на панели инструментов Форматирование кнопку Денежный формат . В результате такого форматирования любое число отобразится с двумя десятичными разрядами и знаком денежной единицы, заданной при настройке Windows в системной папке Панель управления с помощью утилиты Язык и региональные стандарты. Например, если выделить ячейку с числом 7555.358 и щелкнуть на кнопке Денежный формат, то в ячейке может отобразиться 7 555.36 грн.

Форматы дат и времени. В общем случае в Excel может использоваться формат, где дата и время представляют собой единое целое. Когда время достигает 24-х часов, происходит переход к следующей дате. За точку отсчета принято 0 часов, 00 минут и 00 секунд 1-го января 1900 года. Это значение может отобразиться в виде 01.01.1900 0:00:00.

Дата сохраняется как целая часть числа (количество дней, начиная с 1-го января 1900 г.), а время сохраняется как дробная часть числа (доля от 24-часовых суток). То есть, 12 часов 1-го января 1900 года будет сохраняться как 1.5; а 6 часов 31-го января 1900 года – как 31.25. Такое представление дат и времени позволяет производить любые вычисления с датами и временем (определять число суток и часов между двумя значениями; вычислять да­ту и время, отстоящие от заданных значений на указанное число суток и часов; и т.д.).

В частном случае дата и время могут использоваться автономно. При этом дата сохраняется как целое число, а время – как дробь. Например, дата 1 мая 2003 года будет храниться как число 37742 (именно столько дней прошло с 1-го января 1900 г.). А время 15 часов, 25 минут, 30 секунд будет храниться как 0.642708333 (это сумма 15/24, 25/60/24 и 30/60/60/24).

Для представления дат можно использовать различные форматы. Они выбираются из про­кручиваемого списка Тип, который появляется в правой части вкладки Число, если в списке Числовые форматы (см. рис. 3.1) выбрать формат Дата. Например, дату 1 мая 2003 года можно отобразить в виде: 01.05.03; 01 мая 2003 г.; 1 май и т.д.; или со временем – 01.05.03 0:00.

Если на вкладке Число выбрать формат Время, в правой части вкладки появится про­кручиваемый список Тип, в котором можно выбрать необходимый вариант отображения вре­мени: с секундами, без секунд, с датой и т.д. (буквы PM означают, что соответствующие варианты относятся к 12-часовому формату времени).

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

Результаты процентного форматирования зависят от настроек, выполненных в окне Параметры на вкладке Правка (см. Приложение 2, рис. П2.3). Если на этой вкладке снят флажок Автоматический ввод процентов, то все числа, вводимые в ячейки с форматом Процентный, будут умножаться на 100 и дополняться символом "%". Если же флажок установлен, то на 100 будут умножаться только числа меньшие единицы, которые были введены без нуля перед десятичной точкой (например, .5, а не 0.5).

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

Дробный формат применяется для отображения десятичных чисел в виде простых дробей. Для увеличения точности представления чисел можно выбрать формат в виде дроби с двумя или даже тремя цифрами в числителе и знаменателе. Например, число 5.1234 можно отобразить в виде: 5 1/8, 5 10/81 или 5 106/859.

Экспоненциальный формат предназначен для отображения чисел в экспоненциальной фор­ме. При выборе этого формата пользователь получает возможность изменить число знаков после десятичной точки (по умолчанию заданы два десятичных знака). Если, например, этот формат присвоить ячейке с числом 12765, то оно отобразится в виде: 1.28Е+04. Здесь восьмерка появилась в результате округления, а Е+04 означает: 10 в 4-й степени. Напомним, что это лишь форма представления данных. Само число 12765 осталось неизменным.

Текстовый формат используется, если необходимо чтобы числовые данные или формулы воспринимались как текст. Этого можно достичь, конечно, и другим способом – вводить перед такими данными апостроф ( ' ). Но проще присвоить ячейкам текстовый формат.

Например, если ввести в ячейку текст, начинающийся со знака "плюс", "минус" или "равенства", он будет воспринят как ошибочная формула. Чтобы этого не произошло, следует присвоить такой ячейке текстовый формат.

Дополнительный формат используется весьма редко. Из четырех форматов этой группы может найти применение только Номер телефона. Если присвоить этот формат ячейкам с шести- и семизначными номерами телефонов, появятся дефисы, разделяющие группы цифр (в последней группе всегда четыре цифры). Например, числа 554433 и 6554433 отобразятся в виде 55-4433 и 655-4433. Если цифр больше семи, последние семь цифр будут восприняты как номер телефона, а остальные – как индекс. Например, 86554433 отобразится так: (8) 655-4433.

Пользовательские форматы. В тех случаях, когда встроенных форматов недостаточно, можно создать собственные форматы и использовать их в документах. Например, если вычисляются объемы каких-то предметов, можно создать формат "м.куб."; формат даты можно дополнить соответствующим днем недели; и т.д. Для создания собственного формата, пользователю необходимо выполнить следующие операции:

1. Выделить одну или несколько ячеек, содержимое которых требуется отформатировать с использованием создаваемого формата.

2. Одним из описанных выше способов открыть окно Формат ячеек (см. рис. 3.1) и выбрать на вкладке Число в списке числовых форматов пункт все форматы (см. рис. 3.3).

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

4. Завершить создание формата щелчком на кнопке OK. При этом окно Формат ячеек закроется и выделенным ячейкам будет присвоен созданный формат. Этот формат по­падет в прокручиваемый список (см. рис. 3.3) и может быть использован в дальнейшем для любого листа данной рабочей книги.

Рис. 3.3. Создание формата пользователя

Обычно пользовательские форматы создают для данных трех типов: чисел, дат и времени. Рассмотрим правила создания масок этих форматов.

Маски форматов для чисел. При создании масок этого типа чаще других используются сле­дующие управляющие символы:

# – символ десятичных разрядов. Чаще всего используется для вставки разделителей тысячных разрядов в целой части чисел. Например, если отформатировать ячейку с использо­вани­ем маски # ###, то после ввода в нее числа 12345 в ячейке отобразится 12 345.

0 – символ десятичных разрядов. Обычно этот символ применяется для ограничения количества знаков дробной части чисел. Например, если отформатировать ячейку с использо­вани­ем маски #.000, то после ввода в нее числа 12.3457 в ячейке отобразится 12.346. Удобно создавать маски с использованием символов "#" для целой части и символов "0" для дроб­ной части¹. Например, если отформатировать ячейку с использо­вани­ем маски # ###.000, то после ввода в нее числа 12345.3457 в ячейке отобразится 12 345.346 (см. рис. 3.3).

\ – интерпретатор строковых констант. Этот символ используется, если за числом должен следовать текст. Символ "\", введенный без пробела за последним символом десятичных разрядов, предваряет любую строковую константу (например, "м.куб."). Многие строковые константы можно не заключать в кавычки, однако при вводе некоторых символов (например, буквы "ч") в этом случае появляется сообщение об ошибке. Поэтому целесообразно заключать все строковые константы в кавычки. А чтобы текст отделить от числа, ну­жно вставить один или два пробела между символом "\" и строковой константой. Таким образом, при использовании, например, шаблона # ###.00\ "м.куб." число 2.257 отобразится в виде: 2.26 м.куб.

Маски форматов для дат. При создании масок этого типа чаще всего используются следующие управляющие символы:

Д – представление дней месяца без начальных нулей (1, 2, 3, … , 30, 31).

ДД – представление дней месяца с начальными нулями (01, 02, 03, … , 30, 31).

ДДД – представление названий дней недели сокращенно (Пн, Вт, Ср, и т.д.).

ДДДД – представление названий дней недели полно (понедельник, вторник, среда, и т.д.).

М – представление месяцев без начальных нулей (1, 2, 3, … , 11, 12).

ММ – представление месяцев с начальными нулями (01, 02, 03, … , 11, 12).

МММ – представление названий месяцев сокращенно (янв, фев, мар, и т.д.).

ММММ – представление названий месяцев полно (Январь, Февраль, Март, и т.д.).

ГГ – представление года двумя последними цифрами (95, 03, и т.д.).

ГГГГ – представление года четырьмя цифрами (1995, 2003, и т.д.).

Если отформатировать ячейку с использованием маски ДД ММММ ГГГГ "г." (ДДДД), а затем ввести туда 20.03.03, то в ячейке отобразится 20.Март.2003 г. (четверг). Можно было бы привести и другие примеры создания пользовательских форматов дат.

Маски форматов для времени. При создании масок этого типа чаще всего используются следующие управляющие символы:

ч – представление часов без начальных нулей (0, 1, 2, … , 22, 23).

чч – представление часов с начальными нулями (00, 01, 02, … , 22, 23).

м – представление минут без начальных нулей (1, 2, 3, … , 58, 59).

мм – представление минут с начальными нулями (01, 02, 03, … , 58, 59).

с – представление секунд без начальных нулей (1, 2, 3, … , 58, 59).

сс – представление секунд с начальными нулями (01, 02, 03, … , 58, 59).

В качестве примера можно использовать маску: чч "часов" мм "минут" сс "секунд". Если с ее помощью отформатировать какую-то ячейку, а затем ввести туда 15:30:45, то в ячейке отобразится 15 часов 30 минут 45 секунд.