Примеры настраиваемых форматов ячеек

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

Минуточку внимания!

Главное помнить в отличие от других способов форматирования или представления данных, настраиваемые форматы не изменяют значение. Настраиваемые форматы влияют только на отображение значения. Это очень важно! Например, если Вы работаете с финансовым документом, то на рабочем листе приобретенных товаров, вы можете вводить значения в рублях с двумя знаками в копейках. На другом листе или в том же листе Вы можете ссылаться на исходные ячейки, но применять для отображения формат в тысячах рублей. Если завтра потребуется переделать отчет и отразить значения в миллионах рублей, достаточно будет применить соответствующее форматирование к ячейкам. При этом Вы быстро получите необходимый результат и сохраните в рабочей книге исходные значения.

Исходные значения

Возьмем для примеров такие значения:

Здесь в ячейках B2:B10 исходные значения, в ячейках С2:C10 ссылки на исходные значения. Для наглядности условное форматирование будем применять к ячейкам С2:C10.

Добавляем текст к значению

Если необходимо при форматировании добавить к значению какой то текст, то достаточно указать в шаблоне значение в кавычках.

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

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

Если изменить формат  скажем на — «Размер:» #» мм» получим в результате такое отображение:

Остались ли значения?

Хотя конечно текст перед значением используется не так часто. Но всё таки можно настроить и такое представление данных. Сейчас для нас визуально выглядит так как будто к диапазону С2:C10 применена формула (что бывает очень часто, для специального отображения используется формула, а не настраиваемый формат). Что будет если вернуть отображение ячеек С2:C10 к прежнему виду, применив «Общий» формат, а к ячейкам B2:B10 применить первый настроенный формат с окончанием «мм». Давайте попробуем, и в результате получим:

Вот сейчас мы видим действие форматирования наиболее ярко. Как Вы помните в ячейках С2:C10 только ссылки на значения B2:B10, при этом ячейки С2:C10 отображаются «как есть», те просто отображают значения других ячеек без применения дополнительного форматирования. При этом к исходным ячейкам применено настраиваемое форматирование, поэтому они отображаются с окончанием «мм».

Что ж, решим теперь другую проблему, в нашем первом формате производится округление, изменим форматирование таким образом, что бы значение отображалось со знаками после запятой. Для этого исправим форматирование на — #,0″ мм». В результате получим:

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

Добавляем нули

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

Что бы решить такую задачу, введем новое форматирование, например до 4 знаков – 0000. Довольно просто:

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

Тысячи

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

Применим к ячейкам С2:С10 следующий формат – # ###,00 (ВНИМАНИЕ! В формате последний знак не отображается — это пробел)

Результат будет такой. Для наглядности к ячейкам В2:В10 применено числовое форматирование с разделением на разряды. Кроме того обратите внимание что в формате на самом деле два знака пробела, но интерпретируются они по-разному. Первый пробел делит значение на разряды, в то время как последний знак формата, пробел, делит при отображении значение на 1 000. Тем самым меняя представление числа на представление в тысячах.

Миллионы

Если мы добавим к формату еще один пробел в окончании, то получим

Получили значения в миллионах. Таким образом,  один пробел делит значение на 1 000, каждый следующий пробел возводит начальное значение в степень равную позиции пробела. То есть два пробела это 1000 во 2 степени, три пробела 1000 в 3 степени и т.д. Вернемся к формату, да получили значение в миллионах но пропали значения до запятой, всё из за того что мы использовали знак «#» определяющий цифровой разряд. Если мы изменим формат, заменив последний перед запятой символ «#» символом «0» — # ##0,00  (не забудьте в конце два пробела). То получим, вполне правильное отображение чисел в миллионах.

И снова несколько замечаний, во первых обратите внимание что значения округляются, округляются до последнего знака после запятой, или если десятичной запятой нет то к ближайшему целому. В нашем примере если бы не было в формате «,00» то кроме значений 1 и 30 все остальные были бы равны 0. В примере отображения в тысячах и миллионах наиболее четко видно отличие применения символа «#» от «0». Если бы в формате мы заменили все символы решетки нулем, то не получили бы результат который хотели бы. Нуль определяет значащий разряд, если в разряде нет значения – отображается нуль. Решетка определяет просто цифровой разряд, если в разряде нет значения, то и отображаться ни чего не будет. Поэтому если бы мы заменили все символы «#» на нули в формате, то получили бы значения в миллионах, но слева от значения стояли бы нули.

Место для подписи

Приходилось ли Вам делать в Excel печатную форму какого либо отчета в котором необходимо предусмотреть место для подписи? Если Вы давно работаете с Excel то, скорее всего да. С помощью настраиваемых форматов сделать это можно просто и элегантно.

Опять поменяем исходные данные, но теперь просто введем в ячейку В12 фамилию, а в ячейке С12 сделаем ссылку на ячейку с фамилией. Теперь к ячейке С12 применим форматирование – @*_

Красиво получилось. Но самое интересно в том, что если менять размер ячейки С12, то символ подчеркивания всё равно будет заполнять всё свободное место ячейки. Теперь представьте, в отчете у Вас не одно место для подписи, а несколько, поменяли размер, сколько времени потребуется для того что бы подогнать значения в каждой ячейки, добавив или удалив символы подчеркивания. Примените форматирование, и Excel сделает всё за Вас.

Суть этого формата довольна, проста, символ «@» определяет место для текста, символ «*» указывает, что следующий за ним знак надо повторить столько раз, сколько необходимо для заполнения ячейки, а символ подчеркивания – просто символ заполнитель.

Выделяем результаты в отчете

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

Для этого используем три элемента формата, задав для положительных и отрицательных чисел выделение цветом — [Синий]# ##0,0#;[Красный]-# ##0,0#;0

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

В качестве заключения

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