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

Вход

Регистрация

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

 

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

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

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

ОПРОСЫ
Какой версией Excel Вы пользуетесь?
Всего ответов: 56845
Главная » Статьи » Эффективная работа в 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)
Просмотров: 67370 | Комментарии: 15 | Теги: Пользовательский формат, форматирование | Рейтинг: 4.5/20


Всего комментариев: 15
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) [ Материал]
   Почти. Цвет нельзя задать, например

0   Спам
5    Soonik7   (21.04.2017 11:23) [ Материал]
   Товарищи, сломал голову, чувствую ответ где то рядом, но не дается мне)))

Помогите пожалуйста, требуется формат для ячейки, где данные будут записываться так: 0000 000000 (ну вместо нулей естественно цифры),
Загвоздка в том, что иногда данные вводятся как 00АА000000, где АА это буквы, нужно чтоб получалось также с пробелом - 00АА 000000.

0   Спам
6    Юрий_Ф   (28.07.2020 12:41) [ Материал]
   Очень информативно!
Но не понял, как вставить в формулу зелёный кружок, чтобы отображалось, если ячейка пока пуста?

0  
7    Serge_007   (24.11.2020 18:09) [ Материал]
   В какую формулу?

0  
8    Serge_007   (09.03.2021 13:46) [ Материал]
   Добавлю ещё один прием с форматами.

Пользовательский формат
Цитата
J;L;K
и шрифт ячейки Wingdings дают возможность получать в ячейке эмодзи - смайл при положительных числах, нейтральное лицо при нуле и грустное лицо при отрицательных числах

0   Спам
9    elovkov   (07.03.2023 15:22) [ Материал]
   Добрый день.
Хочу сделать формат для пикетов (1ПК=100м)
делаю так:

Код
"ПК "0+00,##


И тогда "1234,56" отображается как "ПК 12+34,56"
Проблема в том, что дробная часть есть не всегда, и тогда в конце остается просто запятая "ПК 12+34,"
Решается через УФ заданием:
Код
=H6=ОТБР(H6;0) - формула
"ПК "0+00 - формат

Но неужели нельзя просто как-то убрать эту запятую?

+1  
10    Serge_007   (07.03.2023 15:29) [ Материал]
   Нет
Недавно об этом была тема на форуме: Формат числа один знак после запятой, но при необходимости

0   Спам
11    elovkov   (07.03.2023 15:47) [ Материал]
   Даже странно, такая вроде естесственная мелочь, не так сложно добавить условий, просто странно

0  
12    Serge_007   (07.03.2023 15:56) [ Материал]
   В Excel до 2007-го года не было функции ЕСЛИОШИБКА(), хотя казалось бы - такая востребованная функция, а функция ОБЪЕДИНИТЬ() вообще появилась только в Excel 2016, хотя необходимость в ней была ещё в неленточных (2003 и ранее) релизах :)
Может и формат с исчезающей точкой когда-то разработчики MS добавят...

0   Спам
13    navek   (26.01.2024 15:37) [ Материал]
   Здравствуйте! А существует возможность создать пользовательский формат вида "01 января 2024 года"? Не январЬ, а именно январЯ. Думал в сторону пользовательских списков, но пока не понял, как подружить его с форматом ячейки. Если есть идеи, как это сделать - напишите. Спасибо!

0  
14    Serge_007   (26.01.2024 21:11) [ Материал]
   navek, существует
В статье об этом написано

0   Спам
15    navek   (29.01.2024 08:32) [ Материал]
   Serge_007, действительно. Не увидел сразу, спасибо!

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