Как сделать текстовый формат в excel

Применение пользовательских форматов

Создание пользовательских форматов

Excel позволяет создать свой (пользовательский) формат ячейки. Многие знают об этом, но очень редко пользуются из-за кажущейся сложности. Однако это достаточно просто, главное понять основной принцип задания формата.

Для того, чтобы создать пользовательский формат необходимо открыть диалоговое окно Формат ячеек и перейти на вкладку Число. Можно также воспользоваться сочетанием клавиш Ctrl + 1.

В поле Тип вводится пользовательские форматы, варианты написания которых мы рассмотрим далее.

Диалоговое окно Формат ячеек

В поле Тип вы можете задать формат значения ячейки следующей строкой:

[цвет]"любой текст"КодФормата"любой текст"

Посмотрите простые примеры использования форматирования. В столбце А — значение без форматирования, в столбце B — с использованием пользовательского формата (применяемый формат в столбце С)

Какие цвета можно применять

В квадратных скобках можно указывать один из 8 цветов на выбор:

Синий, зеленый, красный, фиолетовый, желтый, белый, черный и голубой.

Далее рассмотрим коды форматов в зависимости от типа данных.

Числовые форматы

Символ Описание применения Пример формата До форматирования После форматирования
# Символ числа. Незначащие нули в начале или конце число не отображаются ###### 001234 1234
0 Символ числа. Обязательное отображение незначащих нулей 000000 1234 001234
, Используется в качестве разделителя целой и дробной части ####,# 1234,12 1234,1
пробел Используется в качестве разделителя разрядов # ###,#0 1234,1 1 234,10

Форматы даты

Формат Описание применения Пример отображения
М Отображает числовое значение месяца от 1 до 12
ММ Отображает числовое значение месяца в формате 00 от 01 до 12
МММ Отображает сокращенное до 3-х букв значение месяца от Янв до Дек
ММММ Полное наименование месяца Январь — Декабрь
МММММ Отображает первую букву месяца от Я до Д
Д Выводит число даты от 1 до 31
ДД Выводит число в формате 00 от 01 до 31
ДДД Выводит день недели от Пн до Вс
ДДДД Выводит название недели целиком Понедельник — Пятница
ГГ Выводит последние 2 цифры года от 00 до 99
ГГГГ Выводит год даты полностью 1900 — 9999

Стоит обратить внимание, что форматы даты можно комбинировать между собой. Например, формат "ДД.ММ.ГГГГ" отформатирует дату в привычный нам вид 31.12.2017, а формат "ДД МММ" преобразует дату в вид 31 Дек.

Форматы времени

Аналогичные форматы есть и для времени.

Формат Описание применения Пример отображения
ч Отображает часы от 0 до 23
чч Отображает часы в формате 00 от 00 до 23
м Отображает минуты от 0 до 59
мм Минуты в формате 00 от 00 до 59
с Секунды от 0 до 59
сс Секунды в формате 00 от 00 до 59
[ч] Формат истекшего времени в часах например, [ч]:мм -> 30:15
[мм] Формат истекшего времени в минутах например, [мм]:сс -> 65:20
[сс] Формат истекшего времени в секундах
AM/PM Для вывода времени в 12-ти часовом формате например, Ч AM/PM -> 3 PM
A/P Для вывода времени в 12-ти часовом формате например, чч:мм AM/PM -> 03:26 P
чч:мм:сс.00 Для вывода времени с долями секунд

Текстовые форматы

Текстовый форматов как таковых не существует. Иногда требуется продублировать значение в ячейке и дописать в начало и конец дополнительный текст. Для этих целей используют символ @.

ДО форматирования ПОСЛЕ форматирования Примененный формат
Россия страна — Россия "страна — "@

Создание пользовательских форматов для категорий значений

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

  1. Положительные числа
  2. Отрицательные числа
  3. Нулевые значения
  4. Текстовый формат

Для этого мы можем в поле Тип указать следующую конструкцию:

Формат положительных значений ; отрицательных ; нулевых ; текстовых

Соответственно для каждой категории можно применять формат уже описанного нами вида:

[цвет]"любой текст"КодФормата"любой текст"

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

Смотрите какой эффект это дает. В зависимости от значения, меняется форматирование, а если вместо числа указано текстовое значения, то Excel выдает "нет данных".

Редактирование и копирование пользовательских форматов

Чтобы отредактировать созданный пользовательский формат необходимо:

  1. Выделить ячейки, формат которых вы хотите отредактировать.
  2. Открыть диалоговое окно Формат ячеек и перейти на вкладку Число. Можно также воспользоваться сочетанием клавиш Ctrl + 1.
  3. Изменить строку форматирования в поле Тип.

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

Копирование по образцу

  1. Использовать функцию копирования по образцу.
  2. Выделить ячейки, открыть окно Формат ячеек, на вкладке Число в списке Все форматы выбрать нужный формат и нажать ОК.

Для удаления установленного формата ячейки, можно просто задать другой формат или удалить созданный из списка:

Форматирование чисел в виде текста

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

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

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

Совет: Можно также выделить пустые ячейки, отформатировать их как текст, а затем ввести числа. Такие числа будут иметь текстовый формат.

На вкладке Главная в группе Число щелкните стрелку рядом с полем Числовом формате и выберите текст.

Примечание: Если вы не видите параметр Текст, прокрутите список с помощью ручений.

Применение текстового формата к числам

Чтобы использовать десятичные знаки в числах, хранящихся как текст, возможно, придется вводить эти числа с десятичными разделителями.

При вводе числа, начинающегося с нуля (например, кода продукта), этот ноль по умолчанию удаляется. Если требуется сохранить ноль, можно создать пользовательский числовой формат, который не позволит приложению Excel удалять начальные нули в числах. Например, при вводе десятизначного кода продукта Excel по умолчанию изменяет число 0784367998 на 784367998. В данном случае можно создать пользовательский числовой формат с кодом 0000000000, чтобы в Excel отображались все десять знаков кода продукта, включая начальный ноль. Дополнительные сведения об этой проблеме см. в статьях Создание и удаление пользовательских числовых форматов и Сохранение начальных нулей в числовых кодах.

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

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

Ссылка на основную публикацию