Использование формата для перевода в другие единицы измерения (например, в тысячи) и для форматирования

Предположим, что мы делаем некоторый отчет. Данные, которые используются у нас представлены в рублях. А отчет должен быть представлен в тысячах тех же самых рублей.

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

Метод топора. Просто в каждой ячейке отчета, где стоит цифра добавляется деление на 1,000. Просто, но грубо. Если поверх готового отчета понадобятся вычисления – готовьтесь к путанице, потому что половина цифр будет выражена в тысячах, а половина в рублях. Соответственно, в дальнейших расчетах потребуется умножать или делить половину цифр на тысячу.

Метод изящной резьбы. Для целей вычислений все цифры оставляем в рублях. А форматом убираем лишние разряды.

Во-первых, пожалуйста, посмотрите на приложенный файл. Это набросок упрощенного отчета о финансовом положении компании ОАО «Рога и копыта». Он строится на основе данных из оборотки на листе «ОСВ». Обратите внимание, что данные на листе «ОСВ» представлены в рублях, в то время как на листе «ОоФП» они уже представлены в тысячах рублей. При этом деления нет.

Это было достигнуто с помощью создания специального числового формата, а именно такого, который прячет ненужные мне три последних разряда (они просто не отражаются), а последний отражаемый округляется. Чтобы настроить формат числа, выделите необходимые ячейки (желательно, чтобы в первой из них было число, тогда вы будете видеть настраиваемый формат на пример) и перейдите в настройку формата числа (есть несколько способов (1) Ctrl + 1, затем вкладка Number, (2) нажать на ячейку правой кнопкой, выбрать Format cells…, затем вкладка Number, (3) На ленте выбрать Home, в секции Number в выпадающем списке выбратьMorenumberformats… Слева выберите тип Customи введите необходимый формат в строку Type. Описание формата приведено ниже.

Немного о форматах вообще

То, каким образом записываются форматы сильно зависит от настроек системы, а именно региональных настроек системы (см. Пуск => Панель управления => Язык и региональные стандарты, вкладка Форматы, кнопка Дополнительные параметры…).

По-умолчанию в русской версии стоят немного другие параметры: разделитель целой и дробной части – запятая, а не точка; разделитель групп разрядов – пробел, а не запятая. Мои настройки повторяют стандартные настройки английской версии операционной системы. Почему это важно? Потому что прописывать формат ячейки в экселе мы будем именно символами, присвоенными операционной системой. Числовые форматы

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

1. 0 — любой числовой символ, важно обратить внимание, что если он стоит, а такого разряда в числе нет, то в начале числа будет стоять 0.
2. # — также любой числовой символ, но в отличие от 0 – необязательный. То есть, если будет стоять такой символ для разряда числа, которого нет, то никаких дополнительных чисел отражаться не будет.
3. Например, в ячейке стоит число 23. Если мы укажем формат 000, то это число будет отражаться как 023. Если же мы укажем формат ##0, то число будет отражаться просто как 23. Прошу обратить ваше внимание, что первый формат не является неправильным, просто им редко пользуются. Для некоторых конкретных случаев он может быть очень полезен. Например, программа SAPBWотражает центры затрат в формате 00001XXXXXпо-умолчанию, в то время как при выгрузке данных из SAP просто, центры затрат отражаются в формате 1XXXXX. Формат с нулями в начале может быть очень полезен для унификации.
4. Разделитель групп разрядов (каким бы он ни был) – отражает разделение числа на группы по три цифры (например на калькуляторе для этих целей обычно используется апостроф).
5. Разделитель целой и дробной части числа (опять-таки, будь то точка или запятая – не важно). При записи формата поможет нам определить, сколько дробных разрядов будет в прописываемом формате.
6. Кавычки – указатель строки. Так например, если нам понадобится прописать в формате какой-то конкретный текст, который в себе использует какой-нибудь из указанных выше символов, то его понадобится взять в кавычки.

Примеры

Далее представлено шесть наиболее ходовых примеров форматов одного и того же числа.

Первые пять форматов представляют собой унифицированную запить вне зависимости от того, положительное ли число или отрицательное. Шестой формат, напротив, представляет собой запись, состоящую из трех частей: (1) положительное число, (2) отрицательное число, (3) ноль. Эти части разделяются между собой точкой с запятой. Что касается нуля, то для того, чтобы он был заменен на прочерк, число должно быть точно равно нулю, если оно отличается от нуля хотя бы и на 0.000000001, такое число будет представлено в соответствии с настройками для положительного или отрицательно числа. Чтобы избежать такой записи, может потребоваться округление.

Подробнее я хотел бы остановиться на четвертой записи. В формате указано, что необходимо разделить число на группы по три разряда, и не показывать последний разряд (после последней запятой не стоят ни 000, ни ###, ни ##0. Это означает, что они будут спрятаны. Таким же образом можно показать число в миллионах, соответственно необходимо будет в конце формата поставить две запятых (или пробела, если пробел выбран разделителем групп разрядов в глобальных настройках системы).

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

Файл с данными настройками приложен.