Домашняя страница Undo Do Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Статьи об Excel

МЕНЮ САЙТА
  • 1
  • 2
  • 3

КАТЕГОРИИ РАЗДЕЛА

ОПРОСЫ
Какой версией Excel Вы пользуетесь?
Всего ответов: 35788
Главная » Статьи » Эффективная работа в Excel » Форматирование

Пользовательский формат

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

 
Правила построения формата
 
Для того, чтобы создать пользовательский формат, следует в диалоговом окне Формат ячейки перейти на вкладку Число, выбрать в списке числовых форматов вариант (все форматы) и в поле Тип ввести новый формат
 
 
Пользовательский формат может иметь от одного до четырёх разделов, разделенных точкой с запятой. Эти разделы определяют формат положительных, отрицательных чисел, нуля и текста (именно в таком порядке). Если раздел всего один, то он относится ко всем числам. Если разделов два, то первый определяет представление положительных чисел и нуля, а второй - отрицательных чисел. Если какой-то из разделов пропускается, то точку с запятой всё равно надо поставить. Например, формат ;;;@ позволяет отображать в ячейке только текст, а числа отображаться не будут. Если разделов один, два или три, то текст, введённый в ячейку, всё равно отображается.
 
Важно. Формат числа никак не влияет на его значение, с помощью формата мы изменяем только внешнее представление числа в ячейке
 
Запись формата формируется с помощью специальных символов, которые могут быть дополнены произвольным текстом, а также указанием цвета шрифта и некоторых условий.
 
При создании пользовательского формата используются следующие коды
 
Код Описание Пример формата Содержимое ячейки Результат
Основной общий числовой формат, числа отображаются так, как были введены Основной 1234,56 
78 
0,9
1234,56 
78 
0,9
# любая цифра необязательная, лишние цифры после запятой округляются #,# 1234,56 
78 
0,9
1234,6
78,
,9
0 любая цифра обязательная. Недостающие цифры заменяются нулём, как в целой части, так и в дробной,
лишние цифры после запятой округляются
000,0 1234,56 
78 
0,9
1234,6
078,0
000,9
? любая цифра обязательная. Недостающие цифры заменяются пробелом (используется для выравнивания чисел по десятичной запятой), лишние цифры после запятой округляются ???,? 1234,56 
78 
0,9
-25
-3,4
      1234,6
         78,
            ,9
     -   25,
     -     3,4
% умножает число на 100 и добавляет знак % после него 0,0% 0,9 90,0%
пробел используется как разделитель разрядов, если стоит в середине формата, и уменьшает число в 1000 раз, если стоит в конце формата # ##0,0
# ##0,0 (пробел)
# ##0,0  (два пробела)
123456,7 123 456,7
123,5
0,1
E+  E- используется для экспоненциальной записи числа 0,0E+00
0E+0
123456,7 1,2E+05
1E+5
@ используется для вывода текста      
" " (кавычки) выводит текст, заключенный в кавычки      
$- + / ( ) :  пробел эти символы отображаются в ячейке, даже если записаны в формате без кавычек      
\ используется для экранирования, т.е. для отображения следующего за ним символа без изменений      
* заполняет ячейку символом, который следует за * *- 1234,56 ------------
_ (подчёркивание) делает символ, следующий за ним невидимым, но оставляет свободное место, равное ширине символа. Используется для выравнивания чисел по десятичной запятой, если, например, отрицательное число заключается в скобки # ##0,00_);(# ##0,00);- 1234,5
-1234,5
1 234,50 
(1 234,50)

 

Использование в числовом формате произвольного текста

 
Иногда требуется к числу присоединить текст, но так, чтобы число осталось числом, и с ним можно было производить вычисления (например, добавить единицы измерения). В этом случае текст можно добавить через формат ячейки. Добавляемый текст, как правило, заключается в кавычки, без кавычек можно записывать текст, если в нём нет зарезервированных символов. Например, можно записать без кавычек т (тонн), но нельзя кг, так как символ г зарезервирован для форматирования дат. Чтобы зарезервированные символы отобразились, как обычные, можно перед ними поставить обратный слеш \ (к\г). Но на мой взгляд проще и надёжнее заключить текст в кавычки
 
 
Формат Содержимое ячейки Результат Примечание
# ##0,00" кг" 456 456,00 кг  
# ##0,00 " т" 456 0,46 т пробел после нуля уменьшает число в 1000 раз
0" кг",000" г" 456,123 456 кг,123 г  
"ABCD-"# 456 ABCD-456  
"Приход"*.0,00;"Расход"*.0,00;- 456
-456
0
Приход…..456,00
Расход…..456,00
-
сочетание *. (звёздочка-точка) обеспечивает разделение текста и числа точками, т.е. текст располагается по левому краю ячейки, число располагается по правому краю, а все пространство между ними заполняется точками
"Избыток";"Недостаток";"Баланс"; 456
-456
0
текст
Избыток
Недостаток
Баланс
 
так как четвёртый текстовый раздел пустой, а точка с запятой для него поставлена, то текст отображаться в ячейке не будет

Задание в формате цвета шрифта
 
Цвет шрифта может задаваться в любом из четырёх разделов формата. Название цвета указывается в квадратных скобках и должно быть первым в разделе. Допускается использовать один из восьми цветов: чёрный (black), зеленый (green), белый (white), синий (blue), фиолетовый (magenta), желтый (yellow), голубой (cyan), красный (red), а также задавать цвет индексом цвет n, где n может принимать значения от 1 до 56, правда 10 из них повторяются по два раза. По умолчанию используется чёрный цвет.
 
Формат Содержимое ячейки Результат Примечание
[Синий]# ##0,00;[Красный]-# ##0,00 456
-456
456,00
-456,00
 
# ##0,00" кг";[Красный]"Ошибка!" 456
-456
456 кг
Ошибка!
 
[Красный]"Избыток";[Синий]"Недостаток";[Зеленый]"Баланс"; 456
-456
0
Избыток
Недостаток
Баланс
 
[Черный]Основной 456
#ЗНАЧ!
456
 
Если цвет шрифта установить в цвет фона ячейки (белый по белому), то при таком формате скроются значения ошибок
0,0;-0,0;-;[Цвет46] 456
-456
0
текст
456
-456
-
текст
Выведет текст цветом 46 (оранжевым)
 
Задание в формате условия
 
По умолчанию в пользовательском формате для чисел установлены условия >0; <0; =0. Но при желании можно установить разный формат для данных, сгруппированных по иным условиям. Условия формируются с помощью знаков отношений: = < >.
 
Формат Содержимое ячейки Результат Примечание
[Синий][<100]# ##0,00;[Красный][>500]# ##0,00;0,00

50
456
620
-100

50,00
456
620,00
-100

 
[Красный][<0]"Ошибка";[<100]00" коп.";0" руб." 00" коп." 456
56
-100
4 руб. 56 коп.
56 коп.
Ошибка
 
[Красный][<10000]"Неверный номер";
[>9999999]#(000)##0-00-00;##0-00-00
55896
7335566
8127777777
89817249519
1234
5-58-96
733-55-66
(812)777-77-77
8(981)724-95-19
Неверный номер
Форматирует номера телефонов. Номера более 7 цифр отображаются с кодом региона, от 5 до 7 цифр - без кода региона, менее 5 цифр - ошибочны
[>1000000]0  " млн. руб.";[>1000]0 " тыс. руб.";0" руб." 123456789
123456
123
123 млн. руб.
123 тыс. руб.
123 руб.
 

 

Пользовательские форматы Даты/Времени

 

В большинстве случаев для форматирования ячеек, содержащих дату/время бывает достаточно встроенных форматов Дата и Время. Эти форматы составляются из символов Д (день), М (месяц), Г (год), ч (часы), м (минуты), с (секунды). Различия заключаются в разном количестве этих символов в записи формата, а также в разделителях. Все буквы, кроме "м" можно писать как строчными, так и прописными буквами

 

Формат Результат для 09.02.2014 9:05:00  Примечание
Д
ДД
ДДД
ДДДД

9
09
Вс
воскресенье

Символ Д отображает день для заданной даты

М
ММ
МММ
ММММ
МММММ

2
02
фев
Февраль
ф
Символ М отображает месяц для заданной даты
ГГ (или Г)
ГГГГ (или ГГГ)
14
2014
Символ Г отображает год для заданной даты
Д.М.Г
ДД.ММ.ГГГГ
ДД/ММ/ГГГГ
ДД-МММ-ГГГГ
9.2.14
09.02.2014
09/02/2014
09-фев-2014
 
[$-FC19]ДД ММММ ГГГГ "г."
[$-FC22]ДД ММММ ГГГГ "г."
[$-FC23]ДД ММММ ГГГГ "г."
[$-F800]
09 февраля 2014 г.
09 лютого 2014 г.
09 лютага 2014 г.
9 февраля 2014 г.
Чтобы название месяца отображалось в родительном падеже, следует указать специальный код. Например, для дат на русском языке одним из таких кодов является [$FC19], на украинском [$-FC22], на белорусском [$-FC23].
Формат [$-F800] отображает дату в соответствии с региональными настройками Windows
ч:м
чч:мм:сс
[ч]:мм
чч:мм:сс,00
ч:мм AM/PM
9:05
09:05:00
1000305:05
09:05:00,00
9:05 AM
Символ ч служит для отображения часов, м - минут, а с - секунд. 
Если символ заключён в квадратные скобки [ч] (или [м]), то значение может превышать 23 часа (или 59 минут) 
Формат с запятой после секунд позволяет учитывать доли секунды (в примере сотые)
AM - до полудня, PM - после полудня
ДД.ММ.ГГГГ чч:мм 09.02.2014 09:05  

 

Использование в формате различных символов

 

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

 

Формат Содержимое ячейки Результат Примечание
0,0°

25,36
12
-5,22

25,4°
12,0°
-5,2°

Символ градуса ° можно ввести с помощью набора на клавиатуре Alt+0176 (цифры набираются на цифровой клавиатуре) или через буфер обмена из таблицы символов
[ч]°мм'сс'' 55:45:30 55°45'30'' Такой формат можно применять для отображения величины угла (градусы, минуты, секунды). Так как в угловой мере в градусе 60 минут, а в минуте 60 секунд (как и при измерении времени), вычисления будут производиться корректно.
После сс ставятся не кавычки, а два апострофа
# ##0,0" м²" 45,6
13,58
45,6 м²
13,6 м²
Верхний индекс ² можно взять из таблицы символов или получить с помощью Alt+0178 (правда, для этого должна быть включена английская раскладка)
[Зеленый]↑;[Красный]↓;[Черный]↔ 5
-5
0
Символы стрелок берутся из таблицы символов
[Красный][=3]˜;[Желтый][=2]˜;[Зеленый]˜; 1
2
3
Символ (круг) берётся из таблицы символов, в данном случае шрифт Wingdings 2, но в отличие от стрелок эти символы при вставке в запись формата не отображаются. Но они там есть!!!

 

Несколько слов о текстовом формате

 

Если для ячейки установлен текстовый формат @, то всё, что вводится в ячейку, будет восприниматься как текст, будь то число, дата или формула. Иногда это создаёт проблемы при использовании этих значений в формулах

 

Формат Содержимое ячейки  Результат
@

00456
09.02.2014
=A1+B1

00456
09.02.2014
=A1+B1

Категория: Форматирование | Добавил: Pelena (24.12.2013)
Просмотров: 16889 | Комментарии: 4 | Теги: Пользовательский формат, Форматирование | Рейтинг: 5.0/11


Всего комментариев: 4
0   Спам
1    dx84   (05.11.2014 16:06)
   Автору спасибо!

0   Спам
2    zuj   (17.01.2015 12:02)
   делаю формат мм минуты. После открытия документа становится как ММ месяцы. Что делать.

0   Спам
3    китин   (17.03.2016 08:24)
   Насколько я понял все эти форматы можно мспользовать и в функции ТЕКСТ ?

0  
4    Pelena   (20.03.2016 00:03)
   Почти. Цвет нельзя задать, например

Добавлять комментарии могут только зарегистрированные пользователи.
[ Регистрация | Вход ]
Яндекс цитирования
© 2010-2016 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!