1 Теоретическая часть
1.1 Числовые форматы Excel и их назначение, категории и коды
При воде данных в ячейки рабочего листа Microsoft Excel различают фактические и видимые значения, которые могут существенно отличаться друг от друга. Фактическое значение отображается в строке формул, а видимое – в ячейке. Отображение значения в ячейке определяется числовым форматом.
Числовой формат - способ отображения содержимого в ячейке. Формат изменяет видимое значение в ячейке, но не изменяет фактическое значение в строке формул.
Для задания формата следует выделить область ячеек для форматирования, вызвать команду Формат ячеек. Появится окно диалога «Формат ячеек», в котором нужно выбрать вкладку «Число». Эту же команду можно вызвать и с помощью контекстного меню.
В левой части окна диалога «Формат ячеек» в списке «Числовые форматы» приведены названия всех используемых в Excel форматов (рис.1).
Рисунок 1 – Диалоговое окно «Формат ячеек»
Ниже приведен список категорий числовых форматов с некоторыми комментариями.
Таблица 1 – Категории числовых форматов
Формат |
Описание |
Общий |
Общий числовой формат — это формат, принятый по умолчанию. При его выборе числа отображаются в виде целых чисел, десятичных дробей или В экспоненциальном формате, если число слишком большое и не помешается в ячейке. |
Числовой |
Данный формат используется для общего отображения чисел. Можно указать используемое количество десятичных знаков, необходимость использовать разделитель разрядов, а также способ отображения отрицательных чисел. |
Денежный |
Этот формат используется для отображения общих денежных значений и выводит установленный по умолчанию денежный знак вместе с числами. Можно указать используемое количество десятичных знаков, необходимость использовать разделитель разрядов, а также способ отображения отрицательных чисел. |
Финансовый |
Этот формат также используется для денежных значений, но при его использовании осуществляется выравнивание денежных величин по разделителю целой и дробной части в столбце. |
Дата |
Эта категория служит для отображения даты и времени, представленных числами, в виде дат с учетом указанного типа и языка (местоположения). За исключением элементов, помеченных звездочками (*) в списке Тип (вкладка Число диалогового окна Формат ячеек), порядок элементов в применяемых форматах дат не меняется при смене операционной системы. |
Время |
Эта категория используется для отображения даты и времени, представленных числами, в виде времени с учетом указанного типа и языка (местоположения). В применяемых форматах, за исключением помеченных звездочкой (*) в списке Тип (вкладка Число, диалоговое окно Формат ячеек), порядок элементов даты и времени не меняется при смене формата операционной системы. |
Процентный |
Этот формат используется для умножения значения ячейки на 100 и отображения результата с символом процента. Можно указать используемое количество десятичных знаков. |
Дробный |
Этот формат используется для отображения числа в виде дроби с учетом указанного типа дроби. |
Экспоненциальный |
Этот формат используется для отображения чисел в экспоненциальном представлении и замены части числа на E+n, где E (экспонент) равно предыдущему числу, умноженном на 10 в степени n. Например, в экспоненциальном формате, где количество знаков после запятой равно двум, число 12345678901 отобразится как 1,23E+10, то есть как 1,23, умноженное на 10 в 10-й степени. Можно указать используемое количество десятичных знаков. |
Текстовый |
При использовании этого формата, содержимое ячейки рассматривается как текст и отображается в том виде, в котором пользователь вводит его, даже при вводе чисел. |
Дополнительный |
Этот формат используется для отображения числа в виде почтового индекса, телефонного номера или табельного номера. |
(все форматы) |
Этот тип формата позволяет изменять копию существующего кода числового формата. Создается настраиваемый числовой формат, который добавляется к списку кодов числовых форматов. Можно добавить от 200 до 250 настраиваемых числовых форматов, в зависимости от того, версия Excel для какого языка была установлена. |
Если в ячейке находится ряд символов "решетка" (#########), то это значит, что столбец недостаточно широк для отображения числа в выбранном вами формате. Увеличьте ширину столбца или измените числовой формат. Если вы не изменяли вручную ширину столбцов, то не должны видеть такие символы, поскольку Excel автоматически подгоняет ширину столбцов так, чтобы полностью отобразить введенные данные.
Чаше всего числовые форматы применяются к ячейкам, в которых уже содержатся числа. Но можно также заранее отформатировать ячейки с помощью некоторого числового формата. Если после этого вы введете числовое значение, то оно будет отображено в выбранном формате. Вы можете заранее отформатировать отдельные ячейки, целые строки или столбцы и даже весь рабочий лист.
Однако, вместо того чтобы вручную форматировать всю рабочую таблицу, лучше изменить числовой формат для стиля Обычный. При формировании всем ячейкам назначается стиль Обычный, если не указан другой стиль. Для изменения параметров стиля Обычный выберите команду Стили – Стили ячеек – Обычный – Изменить (для MS Excel 2010), а затем для стиля Обычный выберите новый числовой формат.
Если часто приходится работать с нестандартным отображением значений в ячейках, удобно создавать пользовательские числовые форматы. Для этого применяются специальные коды управления форматом (шаблоны).
Создание формата выполняется по команде Формат ячеек – Число – (все форматы). При этом становится доступным поле Тип, содержащее спецификации текущего формата, и список спецификаций всех доступных форматов. Для определения собственного формата необходимо в поле Тип записать последовательность кодов в соответствии со структурой пользовательского формата, принятой в Microsoft Excel.
Пользовательский формат состоит из 4-х спецификаций, разделенных с помощью «;» (точки с запятой) [3]:
Спецификация 1; Спецификация 2; Спецификация 3; Спецификация 4
Спецификация 1 описывает формат для значений больше 0;
Спецификация 2 описывает формат для значений меньше 0;
Спецификация 3 описывает формат для значений равных 0;
Спецификация 4 описывает формат для текста.
Например, код финансового формата с двумя знаками после запятой и денежной единицей р. имеет вид: _-* # ##0,00р._-;-* # ##0,00р._-;_-* "-"??р._-;_-@_-
В каждой спецификации могут использоваться наборы кодов числовых форматов, основные из которых приведены в таблице 2, коды цветов и условий.
Если какая-либо из спецификаций не описана, то соответствующие значения в ячейке не отображаются.
Если в поле Тип заданы только разделители спецификаций ;;; - это означает «скрывающий» пользовательский формат (ни одно из вводимых значений в ячейке не отображается).
Таблица 2 – Коды числовых форматов и правила их использования
Код |
Описание |
Пример |
Число |
Результат |
0 |
Цифровой шаблон (обязательный). Используется для того, чтобы слева и справа от десятичного разделителя вывести не менее определенного количества цифр. При нехватке в числе значащих цифр в позиции шаблона выводятся 0. Если число имеет больше цифр после запятой, чем символов в шаблоне, то оно округляется. |
000,000
0,00 |
1,23
123,45678
1,2
123,45678 |
001,230
123,457
1,20
123,46 |
# |
Цифровой шаблон (необязательный). Действует так же, как и шаблон 0, но незначащие нули слева и справа от десятичной запятой не выводятся. Если число имеет больше цифр после запятой, чем символов в шаблоне, то оно округляется. |
###,###
#,## |
1,23
123,45678
1,2
123,45678 |
1,23
123,457
1,2
123,46 |
? |
Цифровой шаблон (обязательный). Действует так же, как и шаблон 0, но незначащие нули слева и справа от десятичной запятой заменяются пробелами. Это дает возможность выравнивать числа по запятой. |
???,???
?,?? |
1,23
123,45678
1,2
123,45678 |
1,23
123,457
1,2
123,46 |
, |
Десятичная запятая. Определяет, сколько цифр (в соответствии с количеством шаблонов 0,#,?) будет выводиться слева и справа от запятой. Если слева от запятой в формате указаны только символы #, то числа, меньше 1, будут начинаться с десятичной запятой. Чтобы избежать этого, в качестве первого шаблона слева от запятой следует использовать знак 0. |
#,## |
0,123
25,0 |
,12
25, |
% |
Проценты. Число умножается на 100 и справа добавляется знак % |
#,##% |
123,5 |
12350,% |
пробел |
1. Разделитель разрядов. Используется между символами шаблона для отделения сотен, тысяч, миллионов. 2. Масштабирующий и округляющий фактор: один пробел в конце формата округляет число до тысяч и выводит его в количестве тысяч, два пробела – до миллиона и т.д. |
# ### # #(пробел) # #(2пробела) |
123456 123456 456867 123456789 23658912 |
123 456 123 457 123 24 |
E- E+ e- e+ |
Экспоненциальный (научный) формат. Если формат справа от символов E- E+ e- e+ содержит цифровые шаблоны 0 или #, то число отображается в научном формате: выводится символ Е или е, а вслед за ним – показатель степени 10. |
0,00E+00 |
123456 0,123456 |
1,23E+05 1,23E-01 |
$ - + () : р /. |
Стандартные отображаемые символы. Эти символы могут непосредственно вставляться в формат, тогда как для отображения любого другого символа его нужно заключить в кавычки или поставить перед ним обратную косую черту. |
0,0$ $ 0,00 |
123456 12345 |
123456,0$ $ 12345,00 |
\ |
Обратная косая черта. Используется для вывода следующего непосредственно за ней символа, сама черта при этом не выводится. |
0,0\q |
123456 |
123456,0q |
_ |
Подчеркивание. Используется для вывода пробела, ширина которого равна ширине следующего символа. |
0,0_w$ |
123 |
123,0 $ |
“текст” |
Кавычки ” ”. Используются для отображения любого заключенного между ними текста. |
0,0” число” 0,0”у.е.” |
1 5 |
1,0 число 5,0у.е. |
* |
Звездочка. Используется для повторения следующего за ней символа столько раз, сколько нужно, чтобы заполнить всю ширину столбца. В каждом коде формата должен быть только один символ *. |
0,0*= |
123 |
123,0==== |
@ |
Текстовый шаблон. Текст, выводится в том месте формата, где помещен символ @. |
@”-текст” “Графа ”@ |
Ном Ном |
Ном-текст Графа Ном |
[] |
Квадратные скобки. Используются для записи цвета или условий. Условие состоит из оператора сравнения и значения. |
[Красный]0,0 [<100] [Синий]0,00” меньше сотни”;0” больше ста” |
12 12
120,6 |
12,0 12,00 меньше сотни 121 больше ста |