Доступные числовые форматы в Excel
В Excel числа, содержащиеся в ячейках, можно преобразовать, например, в денежные единицы, проценты, десятичные числа, даты, номера телефонов или номера социального страхования США.
-
Выделите ячейку или диапазон ячеек.
-
На вкладке Главная выберите в раскрывающемся списке формат Числовой.
Вы также можете выбрать один из следующих вариантов:-
Нажмите клавиши CTRL + 1 и выберите число.
-
Щелкните ячейку или диапазон ячеек правой кнопкой мыши, выберите команду Формат ячеек… и формат Числовой.
-
Нажмите кнопку вызова диалогового окна
рядом с надписью число
а затем выберите число.
-
-
Выберите нужный формат.
рядом с надписью число
а затем выберите число.Числовые форматы
Чтобы просмотреть все доступные числовые форматы, на вкладке Главная в группе Число нажмите кнопку вызова диалогового окна рядом с надписью Число.
Формат | Описание |
|---|---|
|
|
При вводе чисел в Excel этот формат используется по умолчанию. В большинстве случаев числа, имеющие формат Общий, отображаются так, как вводятся. Однако если ширины ячейки недостаточно для отображения всего числа, числа в формате Общий округляются. Для чисел, содержащих более 12 знаков, в формате Общий используется научное (экспоненциальное) представление. |
|
Числовой |
Используется как основной для вывода чисел. Можно задать количество отображаемых знаков после запятой, применение разделителя групп разрядов и способ отображения отрицательных чисел. |
|
Денежный |
Используется для денежных значений и выводит рядом с числом обозначение денежной единицы по умолчанию. Можно задать количество отображаемых знаков после запятой, применение разделителя групп разрядов и способ отображения отрицательных чисел. |
|
Финансовый |
Используется для отображения денежных значений с выравниванием обозначений денежных единиц и десятичных разделителей в столбце. |
|
Дата |
Отображает числовые представления даты и времени как значения даты в соответствии с заданным типом и языковым стандартом (местоположением). Форматы даты, начинающиеся со звездочки (*), соответствуют формату отображения даты и времени, заданному на панели управления. На форматы без звездочки параметры, заданные на панели управления, не влияют. |
|
Время |
Отображает числовые представления даты и времени как значения времени в соответствии с заданным типом и языковым стандартом (местоположением). Форматы времени, начинающиеся со звездочки ( *), соответствуют формату отображения даты и времени, заданному на панели управления. На форматы без звездочки параметры, заданные на панели управления, не влияют. |
|
Процентный |
В этом формате значение ячейки умножается на 100, а результат отображается со знаком процента (%). Можно задать количество знаков в дробной части. |
|
Дробный |
Отображает число в виде дроби выбранного типа. |
|
Экспоненциальный |
Отображает число в экспоненциальном представлении, заменяя часть числа на E+n, где E обозначает экспоненциальное представление, то есть умножение предшествующего числа на 10 в степени n. Например, экспоненциальный формат с двумя знаками в дробной части отображает 12345678901 как 1,23E+10, то есть 1,23, умноженное на 10 в 10-й степени. Можно задать количество знаков в дробной части. |
|
Текстовый |
Содержимое ячейки (включая числа) обрабатывается как текст и отображается именно так, как было введено. |
|
Дополнительный |
Число отображается в виде почтового индекса, телефонного номера или страхового номера (SSN). |
|
(все форматы) |
Позволяет изменять копию существующего кода числового формата. При этом создается пользовательский числовой формат, добавляемый в список кодов числовых форматов. В зависимости от языковой версии Microsoft Excel можно ввести от 200 до 250 пользовательских числовых форматов. Дополнительные сведения см. в статье Создание и удаление пользовательских числовых форматов. |
Вы можете применить различные форматы к числам, чтобы изменить способ их отображения. Только форматы изменяют способ отображения чисел и не влияют на значения. Например, если вы хотите, чтобы число отображалось в денежных единицах, щелкните ячейку с числовым значением > денежный.
Применение числового формата только в том случае, если номер отображается и не влияет на значения ячеек, которые используются для выполнения вычислений. Фактическое значение можно просмотреть в строка формул.
Ниже перечислены доступные форматы номеров и способы их использования в Excel в Интернете.
Формат номера | Описание |
|---|---|
|
Общие |
Числовой формат по умолчанию. Если ячейка не слишком широкая, чтобы показать целое число, этот формат Округляет число. Например, 25,76 отображается как 26. Кроме того, если число состоит из 12 или более цифр, в поле Общий
|
|
Число. |
Работает очень похоже на Общий формат, но изменяет способ отображения чисел с разделителями десятичных разрядов и отрицательными числами. Ниже приведено несколько примеров отображения чисел в обоих форматах.
|
|
Денежный |
Денежный символ с цифрами. Число десятичных разрядов можно указать с помощью параметра Увеличить разрядность или Уменьшить разрядность.
|
|
Финансовый |
Также используется для денежных значений, но выравнивает символы денежных единиц и десятичные разделители чисел в столбце. |
|
Краткий формат даты |
Отображает дату в следующем формате:
|
|
Длинный формат даты |
Показывает месяц, день и год в следующем формате:
|
|
Системное время |
Отображает число серийных номеров даты и времени как значения времени. |
|
Процентный |
В этом формате значение ячейки умножается на 100, а результат отображается со знаком процента (%). Используйте параметр Увеличить разрядность или Уменьшить разрядность , чтобы указать нужное число десятичных знаков.
|
|
Дробь |
Число отображается в виде дроби. Например, 0,5 отображается как 1/2. |
|
Экспоненциальный |
Выводит числа в экспоненциальном представлении, заменяя часть числа на E +n, где E (экспонента) умножает предшествующее число на 10 для n-го электричества. Например, экспоненциальный формат с двумя знаками в дробной части отображает 12345678901 как 1,23E+10, то есть 1,23, умноженное на 10 в 10-й степени. Чтобы указать нужное количество десятичных разрядов, примените к нему больше десятичных знаков или Уменьшите число десятичныхразрядов. |
|
Текстовые функции |
Значение ячейки интерпретируется как текст и отображается точно так же, как при вводе чисел. Дополнительные сведения о форматировании чисел в текстовом формате. |
Что такое числовой формат в Excel?
Каждый раз, работая в Excel, старайтесь использовать подходящие числовые форматы для Ваших данных. Числовые форматы сообщают программе, какой тип данных Вы используете, будь то проценты (%), валюты ($), время, дата и так далее. Посмотрите, для примера, на следующую таблицу, в которой использованы различные числовые форматы:
Для чего нужны числовые форматы?
Числовые форматы нужны не только для того, чтобы данные в таблице было легче читать, но, и чтобы таблицей было легче пользоваться. Устанавливая числовой формат, Вы сообщаете таблице, какой тип данных хранится в ячейке. Например, формат даты говорит о том, что Вы помещаете в ячейку определённую календарную дату. Все это позволяет Excel лучше понимать Ваши данные и гарантирует, что они будут оставаться адекватными, а формулы будут вычисляться корректно.
Если Вы не считаете нужным установить определённый числовой формат, Excel по умолчанию применяет формат General (Общий). Общий числовой формат также немного изменяет форматирование Ваших данных.
Установка числовых форматов
Применить числовой формат можно точно также, как и прочие виды форматирования, вроде изменения цвета текста. Для этого нужно выделить ячейки и выбрать нужные опции форматирования. Настраивать числовые форматы позволяет каждое приложение, работающее с электронными таблицами, но этот процесс в разных приложениях может сильно отличаться.
- В Microsoft Excel 2007-2013 откройте вкладку Home (Главная), в разделе Number (Число) кликните выпадающее меню Number Format (Числовые форматы) и выберите нужный формат. Либо под выпадающим меню Вы можете кликнуть одну из иконок быстрого доступа.
- В Excel 2003 и более ранних версиях откройте меню Format (Формат) > Cells (Ячейки).
- В Google Sheets нажмите кнопку More Formats (Другие форматы) в левой части панели инструментов – эта кнопка выглядит, как цифры 123. Слева от этой команды есть иконки Currency (Денежный формат) или Percent (Процентный формат) для быстрого доступа к этим форматам.
В большинстве версий Microsoft Excel доступ к настройкам форматирования можно открыть, если выделить нужные ячейки и нажать Ctrl+1.
В этом примере мы применим числовой формат Currency (Денежный), который добавляет символ валюты ($) и отображает два десятичных знака для любого числового значения.
Если Вы выделите любую ячейку с числовым форматом, то в строке формул сможете увидеть реально содержащееся в ней число. Именно это значение Excel использует при работе с формулами и в других вычислениях.
Используем числовые форматы правильно
Тема числовых форматов не заканчивается выделением ячейки и применением к ней одного из форматов. Электронные таблицы могут устанавливать многие числовые форматы автоматически, в зависимости от введенных данных. Это значит, что Вы должны вводить данные понятным для приложения образом и проверять, чтобы в ячейках установился правильный числовой формат. Для примера, на картинке ниже показано, как правильно вводить даты, проценты и время.
Теперь, когда Вам уже многое известно о работе числовых форматов, давайте рассмотрим некоторые из них в действии.
Процентный формат
Один из наиболее полезных форматов – это процентный формат (%). Он отображает значения как проценты, например, 20% или 55%, что особенно полезно при расчете величин скидок или чаевых. Если вслед за числом ввести знак процента (%), к нему будет автоматически применен процентный формат.
Возможно, Вы еще помните из школьного курса математики, что проценты могут быть записаны в виде десятичных дробей, т.е. 15% – это то же самое, что 0,15, а 7,5% – это 0,075.
Процентный формат будет полезен не однократно. На изображениях ниже Вы видите пример, что получается в каждой таблице при разном форматировании размера скидки (5, 5% или 0,05):
Как видите, в таблице слева вычисления выполнены не верно. Без применения процентного формата, Excel думает, что мы хотим умножить $22,50 на 5, а не на 5%. Таблица справа хоть и справляется с этой задачей, но прочесть проще всего средний вариант.
Формат даты
Работая с датами, Вам пригодится формат даты, чтобы сообщить Excel, что Вы ссылаетесь на определённую календарную дату, например, 15 июля 2014. Кроме этого, формат даты дает возможность работать с мощным инструментарием функций категории Даты и время, которые используют информацию о дате и времени для вычисления результатов.
Электронные таблицы понимают информацию не так, как это делает человек. Например, если Вы введете в ячейку слово “October”, Excel не поймет, что Вы имеете в виду дату, и сочтёт это обычным текстом. Вместо этого, при вводе дат, используйте определённый формат, который понятен Excel, например, месяц/день/год или день/месяц/год – в зависимости от того, в какой стране Вы находитесь и какой локализацией программы пользуетесь. В примере ниже мы введём “10/12/2014”, чтобы получить дату October 12, 2014. В этом случае Excel автоматически установит для ячейки формат даты.
Теперь, когда для наших дат задан правильный числовой формат, мы многое сможем с ними сделать. Например, мы можем использовать маркер автозаполнения, чтобы продолжить последовательность дат в столбце и получить в каждой ячейке новую дату.
Если формат даты не был применен автоматически, значит приложение Excel не распознало дату, которую Вы ввели. В следующем примере мы ввели “March 15th”. Excel не распознал в этой записи дату, поэтому формат ячейки остался General (Общий).
С другой стороны, если мы введем “March 15” (без th), Excel распознает дату. Поскольку год в этой дате не указан, автоматически добавится текущий год, и дата будет содержать всю необходимую информацию. Мы также можем записать дату несколькими другими способами, например, 3/15, 3/15/2014 или March 15 2014, и таблица по-прежнему поймёт, что это дата.
В русской локализации 15/3, 15/3/2014 или 15 март 2014.
Другие варианты форматов даты
В некоторых приложениях присутствует гораздо больший выбор форматов даты. Доступ к ним немного отличается, в зависимости от приложения, которое Вы используете. В Excel 2007-2013 откройте выпадающее меню Number Format (Числовые форматы) и выберите в нем пункт More Number Formats (Другие числовые форматы).
Откроется диалоговое окно Формат ячеек, где Вы сможете выбрать нужный формат даты.
Посмотрев в строку формул, Вы можете убедиться, что пользовательский формат даты не изменяет значение, хранящееся в ячейке. Изменяется только способ отображения даты.
Хитрости при форматировании чисел
Вот несколько хитростей, которые помогут Вам лучше справиться с числовыми форматами:
- Применяйте числовой формат ко всему столбцу: Если Вы собираетесь использовать столбец для конкретного вида данных, например, для дат или процентов, проще всего выделить сразу весь столбец, кликнув по его букве, и применить нужный числовой формат. Теперь любые данные, введенные в этот столбец, будут уже заранее иметь правильный числовой формат. Обратите внимание, что строка заголовков (если она есть), как правило, не должна быть затронута числовым форматированием.
- Дважды проверьте значения в ячейках после применения к ним числового формата: Если Вы применяете числовой формат к существующим данным, результат может быть неожиданным. Например, применяя процентный формат (%) к ячейке со значением 5, Вы получите 500%, а не 5%. В этом случае придется заново ввести корректные значения в каждую ячейку.
- Если Вы ссылаетесь в формуле на ячейку с числовым форматом, Excel может автоматически применить то же числовое форматирование к итоговой ячейке. Например, если Вы используете в формуле значение с денежным форматом, результат вычисления тоже будет иметь денежный формат.
- Если Вы хотите, чтобы данные в ячейке отображались в точности так, как они были введены, нужно использовать текстовый формат. Этот формат особенно хорош для чисел, над которыми Вы не хотите производить какие-либо вычисления, например, номера телефонов, почтовые индексы или числа, начинающиеся с нуля (02415). Примените текстовый формат к таким ячейкам, прежде чем вводить данные.
Оцените качество статьи. Нам важно ваше мнение:
Microsoft Excel позволяет применять к числам множество самых различных числовых форматов. Используя форматирование, можно изменять внешний вид чисел без изменения самих значений. В данном уроке мы рассмотрим основные встроенные числовые форматы в Excel, а также несколько полезных опций для работы с числами.
Одним из мощнейших инструментов Excel является возможность применения определенного форматирования к тексту и числам. Во избежание одинакового отображения содержимого во всех ячейках, Вы можете применить к ним нужное форматирование. С помощью форматирования числам в ячейках Excel можно придавать внешний вид даты, времени, процентов, денежный формат и многое другое.
Как применить числовое форматирование в Excel
В следующем примере мы изменим числовое форматирование в нескольких ячейках, чтобы изменить способ отображения даты.
- Выделите ячейки, к которым требуется применить числовой формат.
- На вкладке Главная нажмите на стрелку рядом с полем Числовой формат. Появится выпадающее меню.
- Выберите нужный формат. В нашем примере мы выберем Длинный формат даты.
- Числовое форматирование в выбранных ячейках изменится. Для некоторых форматов можно использовать команды Увеличить или Уменьшить разрядность, чтобы изменить количество выводимых десятичных знаков.
Варианты числовых форматов в Excel
На рисунке ниже представлены 11 вариантов числовых форматов, которые нам предлагает Excel.
На самом деле предустановленных числовых форматов намного больше, но получить доступ к ним можно только на вкладке Число диалогового окна Формат ячеек.
1. Общий
Формат Общий установлен по умолчанию во всех ячейках. При использовании такого формата, Excel автоматически определяет, какое числовое форматирование является наиболее подходящим.
Например, если Вы вводите “1-5”, в ячейке отобразится число в кратком формате даты “1/5/2010”.
2. Числовой
В формате Числовой значения отображаются с десятичными знаками. Например, если Вы введете в ячейку число “4”, то в ней отобразится “4,00”.
3. Денежный
Формат Денежный отображает значение в ячейке со знаком денежной единицы. Например, если ввести в ячейку число “4” и применить рублевой формат, то в ней отобразится “4,00р.”. Формат Денежный позволяет применять отрицательным числам особое форматирование, а также выставлять количество знаков после запятой.
4. Финансовый
Финансовый формат очень похож на денежный, но есть ряд существенных различий. Например, к ячейкам в финансовом формате нельзя применить горизонтальное форматирование, нулевые значения отображаются в виде тире, а знаки денежных единиц выровнены по вертикали. Такое форматирование позволяет легче воспринимать длинные денежные списки.
5. Краткий формат даты
Краткий формат даты отображает содержимое ячейки в виде ДД.ММ.ГГГГ. Например, 8 августа 2014 года будет выглядеть как “08.08.2014”.
6. Длинный формат даты
Длинный формат даты отобразит то же самое значение в виде “8 августа 2014 г.”.
7. Время
Формат Время, позволяет отображать содержимое ячеек в виде Ч:ММ:СС. Например: 8:40:25 или 17:35:15.
8. Процентный
В процентном формате значения в ячейках умножаются на 100 и выводятся со знаком процента. По умолчанию отображается два знака после запятой, но Вы можете задать любое другое количество десятичных знаков. Например, если ввести в ячейку число “0,75”, то оно будет выглядеть как “75,00%”.
9. Дробный
Данный формат позволяет отобразить содержимое ячейки в виде дроби. Например, если ввести значение “1/4” в ячейку, которая имеет формат Общий, то в итоге мы получим дату “01.апр“. Если же ввести точно такое же значение в ячейку, имеющую формат Дробный, то отобразится дробь “1/4”.
10. Экспоненциальный
Данный формат отображает значения в ячейках в экспоненциальном виде. Например, если ввести в ячейку число”140000″, то оно отобразится как “1,40E+05”.
Excel по умолчанию назначает ячейке формат экспоненциальный, когда вводимое значение содержит большое целое число. Если же вы не хотите отображать большие числа в экспоненциальном виде, то используйте формат Числовой.
11. Текстовый
Текстовый формат предполагает, что все вводимые Вами значения будут отображаться именно так, как вы их ввели. Excel автоматически устанавливает этот формат, если в ячейке содержатся числа и текст.
Другие числовые форматы
Вы можете без труда настроить любой формат в разделе Другие числовые форматы.
Например, Вы можете сменить денежный знак рубля на любой другой, настроить отображение запятых в числах или изменить количество десятичных знаков.
Оцените качество статьи. Нам важно ваше мнение:
Создайте и настройте пользовательский числовой формат для отображения чисел в виде процентов, денежных единиц, дат и т. д. Чтобы узнать, как изменять коды числовых форматов, ознакомьтесь со статьей Рекомендации по настройке числовых форматов.
-
Выберите числовые данные.
-
На вкладке Главная в группе число нажмите кнопку вызова диалогового окна.
-
Выберите пункт (все форматы).
-
В списке Тип выберите существующий формат или введите в поле новый.
-
Чтобы добавить текст в числовой формат, сделайте следующее:
-
Введите текст в кавычках.
-
Добавьте пробел, чтобы отделить текст от числа.
-
-
Нажмите кнопку ОК.
В приложении Excel числа можно отобразить в различных форматах (например в процентном, денежном или в формате даты). Если встроенные форматы не подходят, может потребоваться создать пользовательский числовой формат.
Вы не можете создавать пользовательские форматы в Excel в Интернете, но если у вас есть классическое приложение Excel, можно нажать кнопку Открыть в Excel , чтобы открыть книгу и создать ее. Дополнительные сведения можно найти в разделе Создание настраиваемого числового формата.
С помощью различных числовых форматов можно выводить числовые данные как проценты, даты, валюты и т. д. Например, при работе над квартальным бюджетом можно использовать формат «Денежный» для отображения денежных значений.
Просмотр видео
Процедура
|
|
Выделите ячейки, которые нужно отформатировать.
|
|
|
На вкладке Главная в группе Число нажмите кнопку вызова диалогового окна рядом с надписью Число (или просто нажмите клавиши CTRL+1).
|
|
|
В списке Категория выберите нужный формат и при необходимости настройте его параметры. Например, при использовании формата «Денежный» можно выбирать различные символы валюты, изменять число выводимых десятичных знаков и способ отображения отрицательных значений.
Дополнительные сведения о числовых форматах можно найти в разделе Доступные числовые форматы. |
Дальнейшие действия
-
Если после изменения числового формата в ячейке Microsoft Excel отображаются символы #####, вероятно, ширина ячейки недостаточна для отображения данных. Чтобы увеличить ширину ячейки, дважды щелкните правую границу столбца, содержащего ячейки с ошибкой #####. Размер столбца автоматически изменится таким образом, чтобы отобразить число. Кроме того, можно перетащить правую границу столбца, увеличив его ширину.
-
Чаще всего числовые данные отображаются правильно независимо от того, вводятся ли они в таблицу вручную или импортируются из базы данных или другого внешнего источника. Однако иногда Excel применяет к данным неправильный числовой формат, из-за чего приходится изменять некоторые настройки. Например, при вводе числа, содержащего косую черту (/) или дефис (-), Excel может обработать данные как дату и преобразовать их в формат даты. Если необходимо ввести значения, не подлежащие расчету, такие как 10e5, 1 p или 1-2, то чтобы предотвратить их преобразование во встроенный числовой формат, к соответствующим ячейкам можно применить формат «Текстовый», а затем ввести нужные значения.
-
Если встроенный числовой формат не соответствует требованиям, можно создать собственный числовой формат. Поначалу код, используемый для создания числовых форматов, может показаться сложным для понимания, поэтому в качестве заготовки удобно использовать один из встроенных форматов. Затем можно изменить любой фрагмент кода для создания собственного числового формата. Чтобы просмотреть код встроенного числового формата, выберите категорию Пользовательская и обратите внимание на поле Тип. Например, код [<=9999999]###-####;(###) ###-#### используется для отображения номера телефона (555) 555-1234. Дополнительные сведения можно найти в разделе Создание и удаление настраиваемых числовых форматов.
К началу страницы
Примечание: Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).
Коды числовых форматов — Excel для Mac
Коды числовых форматов можно использовать для управления отображением цифр перед десятичным знаком и после него. Для отображения только значащих цифр в числе следует использовать знак решетки (#). Этот знак не позволяет выводить незначащие нули. Чтобы отобразить незначащие нули, если число содержит меньше цифр, чем указано в коде формата, необходимо использовать цифровой символ нуля (0). Знак вопроса (?) используется, чтобы добавить пробелы вместо незначащих нулей с обеих сторон десятичного разделителя для выравнивания десятичных разделителей при форматировании с использованием моноширинного шрифта, например Courier New. Вопросительный знак (?) также можно использовать для отображения дробей, которые содержат переменное количество цифр в числителе и знаменателе.
Если в целой части числа больше цифр, чем заполнителей в коде формата, в ячейке будут отображаться дополнительные цифры. Однако если в дробной части числа больше цифр, чем заполнителей в коде формата, число будет округлено до количества десятичных знаков, равного количеству заполнителей. Если код формата содержит только знаки решетки (#) слева от десятичного разделителя, числа, значение которых меньше 1, будут начинаться с десятичного разделителя, а не с нуля, за которым следует десятичный разделитель.
Элементы, которые нужно отобразить | В виде | Используйте код |
|---|---|---|
|
1234,59 |
1234,6 |
####.# |
|
8,9 |
8,900 |
#,000 |
|
0,631 |
0,6 |
0,# |
|
12 1234,568 |
12,0 1234,57 |
#,0# |
|
Число: 44,398 102,65 2,8 |
С выравниванием десятичных разделителей: 44,398 102,65 2,8 |
???.??? |
|
Число: 5,25 5,3 |
С выравниванием числителей дробей 5 1/4 5 3/10 |
# ???/??? |
Для отображения пробела в качестве разделителя групп разрядов или представления числа в масштабе, кратном 1000, включите пробел в код числового формата.
Элементы, которые нужно отобразить | В виде | Используйте код |
|---|---|---|
|
12000 |
12 000 |
#,### |
|
12000 |
12 |
#, |
|
12200000 |
12,2 |
0,0 (два пробела) |
Для отображения начальных или конечных нулей перед целым числом или после него используйте коды из следующей таблицы:
Элементы, которые нужно отобразить | В виде | Используйте код |
|---|---|---|
|
12 123 |
00012 00123 |
00000 |
|
12 123 |
00012 000123 |
«000»# |
|
123 |
0123 |
«0»# |
Чтобы задать цвет для раздела кода формата, введите в этом разделе одно из восьми перечисленных ниже названий цветов в квадратных скобках. Код цвета должен быть первым элементом в разделе.
[Черный] [Синий] [Голубой] [Зеленый] [Фиолетовый] [Красный] [Белый] [Желтый]
Чтобы задать числовой формат, который должен применяться только к числам, удовлетворяющим указанному условию, введите это условие в квадратных скобках. Условие состоит из оператора сравнения и значения. Например, приведенный ниже числовой формат задает отображение чисел, меньших или равных 100, красным цветом, а чисел, больших 100, — синим цветом.
[Красный][<=100];[Синий][>100]
Чтобы скрыть нули или все значения в ячейках, создайте пользовательский формат с использованием приведенных ниже кодов. Скрытые значения отображаются только в строке формул. Они не видны при печати листа. Чтобы скрытые значения снова стали видимыми, измените формат на общий числовой формат или на соответствующий формат даты или времени.
Чтобы скрыть | Используйте код |
|---|---|
|
Нулевые значения |
0;–0;;@ |
|
Все значения |
;;; (три точки с запятой) |
Пользовательские форматы в Excel
При необходимости Вы можете легко добавить к стандартным числовым форматам Excel свои собственные. Для этого выделите ячейки, к которым надо применить пользовательский формат, щелкните по ним правой кнопкой мыши и выберите в контекстном меню команду Формат ячеек (Format Cells) — вкладка Число (Number), далее — Все форматы (Custom):
В появившееся справа поле Тип: введите маску нужного вам формата из последнего столбца этой таблицы:
Как это работает…
На самом деле все очень просто. Как Вы уже, наверное, заметили, Excel использует несколько спецсимволов в масках форматов:
- 0 (ноль) — одно обязательное знакоместо (разряд), т.е. это место в маске формата будет заполнено цифрой из числа, которое пользователь введет в ячейку. Если для этого знакоместа нет числа, то будет выведен ноль. Например, если к числу 12 применить маску 0000, то получится 0012, а если к числу 1,3456 применить маску 0,00 — получится 1,35.
- # (решетка) — одно необязательное знакоместо — примерно то же самое, что и ноль, но если для знакоместа нет числа, то ничего не выводится
- (пробел) — используется как разделитель групп разрядов по три между тысячами, миллионами, миллиардами и т.д.
- [ ] — в квадратных скобках перед маской формата можно указать цвет шрифта. Разрешено использовать следующие цвета: черный, белый, красный, синий, зеленый, жёлтый, голубой.
Плюс пара простых правил:
- Любой пользовательский текст (кг, чел, шт и тому подобные) или символы (в том числе и пробелы) — надо обязательно заключать в кавычки.
- Можно указать несколько (до 4-х) разных масок форматов через точку с запятой. Тогда первая из масок будет применяться к ячейке, если число в ней положительное, вторая — если отрицательное, третья — если содержимое ячейки равно нулю и четвертая — если в ячейке не число, а текст (см. выше пример с температурой).
Ссылки по теме
- Товары
- Клиенты
- Случаи использования
- Переполнение стека Публичные вопросы и ответы
- Команды Частные вопросы и ответы для вашей команды
- предприятие Частные вопросы и ответы для вашего предприятия
- работы Программирование и связанные с ним технические возможности карьерного роста
- Талант Нанимать технический талант
- реклама Связаться с разработчиками по всему миру
Загрузка…
- Авторизоваться зарегистрироваться
текущее сообщество
Создать пользовательский формат чисел
Создайте и создайте собственный числовой формат для отображения ваших чисел в процентах, валюте, датах и т. Д. Чтобы узнать больше о том, как изменить коды формата чисел, см. Руководство по просмотру для настройки формата чисел.
Выберите числовые данные.
На вкладке Home в группе Number щелкните панель запуска диалогового окна.
Выберите Пользовательский .
В списке Тип выберите существующий формат или введите новый в поле.
Чтобы добавить текст в ваш формат номера:
Выберите OK .
Excel предоставляет множество опций для отображения чисел в различных форматах, таких как проценты, валюта и даты. Если встроенные форматы не соответствуют вашим потребностям, вы можете создать собственный числовой формат.
Вы не можете создавать пользовательские форматы в Excel для Интернета, но если у вас есть приложение Excel для рабочего стола, вы можете нажать кнопку Открыть в Excel , чтобы открыть книгу и создать их.Для получения дополнительной информации см. Создание пользовательского формата чисел.
,Преобразование текста в числа в Excel
В Excel часто встречаются числа, хранящиеся в виде текста. Это приводит к неправильным вычислениям при использовании этих ячеек в функциях Excel, таких как SUM и AVERAGE (поскольку эти функции игнорируют ячейки, в которых есть текстовые значения). В таких случаях вам необходимо преобразовать ячейки, содержащие числа в виде текста, обратно в числа.
Теперь, прежде чем двигаться дальше, давайте сначала рассмотрим несколько причин, по которым вы можете получить рабочую книгу, в которой числа хранятся в виде текста.
- Использование ‘(апостроф) перед числом.
- Многие люди вводят апостроф перед числом, чтобы сделать его текстовым. Иногда это также имеет место при загрузке данных из базы данных. Хотя это приводит к тому, что числа отображаются без апострофа, это влияет на ячейку, заставляя ее воспринимать числа как текст.
- Получение чисел в результате формулы (например, LEFT, RIGHT или MID)
- Если вы извлекаете числовую часть текстовой строки (или даже часть числа), используя функции TEXT, результатом будет число в текстовом формате.
Теперь давайте посмотрим, как решать такие случаи.
Преобразование текста в числа в Excel
В этом руководстве вы узнаете, как преобразовать текст в числа в Excel.
Метод, который вам нужно использовать, зависит от того, как число было преобразовано в текст. Вот те, которые рассматриваются в этом уроке.
- Использование опции «Преобразовать в номер».
- Измените формат с текста на общий / номер.
- Использование Paste Special.
- Использование текста в столбце.
- Использование комбинации значений VALUE, TRIM и CLEAN.
Преобразование текста в числа с помощью параметра «Преобразовать в число»
Когда апостроф добавляется к числу, он меняет формат числа на текстовый. В таких случаях вы заметите, что в верхней левой части ячейки есть зеленый треугольник.
В этом случае вы можете легко преобразовать числа в текст, выполнив следующие действия:
- Выберите все ячейки, которые вы хотите преобразовать из текста в числа.
- Нажмите на желтый значок ромба, который появляется в правом верхнем углу. В появившемся меню выберите «Преобразовать в номер».
Это мгновенно преобразует все числа, хранящиеся в виде текста, обратно в числа. Вы заметили бы, что числа выровнены по правому краю после преобразования (в то время как они были выровнены по левому краю при сохранении в виде текста).
Преобразование текста в числа путем изменения формата ячейки
Когда числа отформатированы как текстовые, вы можете легко преобразовать их обратно в числа, изменив формат ячеек.
Вот шаги:
- Выберите все ячейки, которые вы хотите преобразовать из текста в числа.
- Перейти на главную -> Номер. В раскрывающемся списке Формат номера выберите Общие.
Это мгновенно изменит формат выбранных ячеек на Общие, и числа будут выровнены вправо. Если вы хотите, вы можете выбрать любой из других форматов (например, Число, Валюта, Учет), что также приведет к тому, что значение в ячейках будет считаться числом.
Преобразование текста в числа с помощью специальной опции вставки
Для преобразования текста в числа с помощью специальной опции Вставить:
- Введите 1 в любой пустой ячейке на листе. Убедитесь, что он отформатирован как число (т. Е. Выровнено по правому краю ячейки).
- Скопируйте ячейку, содержащую 1.
- Выберите ячейки, которые вы хотите преобразовать из текста в числа.
- Щелкните правой кнопкой мыши и выберите «Специальная вставка».
- В диалоговом окне «Специальная вставка» выберите «Умножить» в категории «Операция».
- Нажмите ОК.
Преобразование текста в числа, используя текст в столбец
Этот метод подходит для случаев, когда у вас есть данные в одном столбце.
Вот шаги:
- Выберите все ячейки, которые вы хотите преобразовать из текста в числа.
- Перейдите в Данные -> Инструменты данных -> Текст в столбцы.
- В мастере преобразования текста в столбец:
- На шаге 1: выберите «С разделителями» и нажмите «Далее».
- На шаге 2: выберите Tab в качестве разделителя и нажмите Next.
- На шаге 3: в формате данных столбца убедитесь, что выбран общий. Вы также можете указать пункт назначения, где вы хотите результат. Если вы ничего не укажете, он заменит исходный набор данных.
- На шаге 1: выберите «С разделителями» и нажмите «Далее».
Хотя вы можете по-прежнему находить результирующие ячейки в текстовом формате, а числа по-прежнему выровнены по левому краю, теперь они будут работать в таких функциях, как SUM и AVERAGE.
Преобразование текста в числа с использованием функции VALUE
Вы можете использовать комбинацию VALUE, TRIM и CLEAN для преобразования текста в числа.
- Функция
- VALUE преобразует любой текст, который представляет число, обратно в число.
- Функция TRIM удаляет все начальные или конечные пробелы. Функция
- CLEAN удаляет лишние пробелы и непечатаемые символы, которые могут проникнуть, если вы импортируете данные или загрузите их из базы данных.
Предположим, вы хотите преобразовать ячейку A1 из текста в числа, вот формула:
= ЗНАЧЕНИЕ (TRIM (CLEAN (A1)))
Если вы хотите применить это и к другим ячейкам, вы можете скопировать и использовать формулу.
Наконец, вы можете преобразовать формулу в значение с помощью специальной вставки.
Вам также могут понравиться следующие учебные пособия по Excel:
,В учебном пособии объясняются основы формата Excel для чисел, текста, валюты, процентов, бухгалтерского номера, научной записи и многого другого. Кроме того, он демонстрирует быстрые способы форматирования ячеек во всех версиях Excel 2016, 2013, 2010, 2007 и ниже.
Когда дело доходит до форматирования ячеек в Excel, большинство пользователей знают, как применять базовые текстовые и числовые форматы. Но знаете ли вы, как отображать требуемое количество десятичных знаков или определенный символ валюты и как применять только правильный формат научной нотации или номера бухгалтерского номера? А знаете ли вы ярлыки числового формата Excel для применения желаемого форматирования в один клик?
Основы формата Excel
По умолчанию все ячейки в листах Microsoft Excel отформатированы в формате General .При форматировании по умолчанию все, что вы вводите в ячейку, обычно остается как есть и отображается как напечатанное.
В некоторых случаях Excel может не отображать значение ячейки точно так, как вы ввели его, хотя формат ячейки остается общим. Например, если вы вводите большое число в узком столбце, Excel может отобразить его в формате нотации Scientific, например, 2.5E + 07. Но если вы посмотрите число в строке формул, вы увидите введенный вами исходный номер (25000000).
Бывают ситуации, когда Excel может автоматически изменить общий формат на какой-либо другой в зависимости от значения, введенного в ячейку.Например, если вы введете 1/4/2016 или 1/4, Excel будет рассматривать ее как дату и соответственно изменяет формат ячейки.
Быстрый способ проверить формат, примененный к определенной ячейке, — это выбрать ячейку и посмотреть на поле Числовой формат на вкладке Home , в группе Number :
Важно помнить, что форматирование ячеек в Excel изменяет только внешний вид или визуальное представление значения ячейки, но не само значение.
Например, если в какой-то ячейке есть число 0,5678, и вы отформатируете эту ячейку, чтобы отображать только 2 десятичных знака, число будет отображаться как 0,57. Но базовое значение не изменится, и Excel будет использовать исходное значение (0,5678) во всех расчетах.
Аналогично, вы можете изменить отображаемое представление значений даты и времени так, как вам нужно, но Excel сохранит исходное значение (порядковые номера для дат и десятичные дроби для времени) и будет использовать эти значения во всех функциях даты и времени и других формулах. ,
Чтобы увидеть базовое значение за числовым форматом, выберите ячейку и посмотрите на строку формул:
Как отформатировать ячейки в Excel
Всякий раз, когда вы хотите изменить внешний вид числа или даты, отобразить границы ячеек, изменить выравнивание текста и ориентацию или внести другие изменения в форматирование, основным элементом для использования является диалог Формат ячеек . И поскольку эта функция наиболее часто используется для форматирования ячеек в Excel, Microsoft сделала ее доступной различными способами.
4 способа открыть диалог Формат ячеек
Чтобы изменить форматирование определенной ячейки или блока ячеек, выберите ячейки, которые вы хотите отформатировать, и выполните любое из следующих действий:
- Нажмите Ctrl + 1 ярлык.
- Щелкните правой кнопкой мыши по ячейке (или нажмите Shift + F10) и выберите Формат ячеек … во всплывающем меню.
- Щелкните стрелку Диалоговое окно в правом нижнем углу группы , , Выравнивание или Группа шрифтов , чтобы открыть соответствующую вкладку диалога Формат ячеек :
- На вкладке Home в группе Cells нажмите кнопку Format , а затем нажмите Format Cells…
Откроется диалоговое окно Format Cells , и вы можете начать форматирование выбранных ячеек с помощью различных параметров на любой из шести вкладок.
6 вкладок диалога Формат ячеек
Диалоговое окно Format Cells имеет шесть вкладок, которые предоставляют различные параметры форматирования для выбранных ячеек. Чтобы узнать больше о каждой вкладке, нажмите на соответствующую ссылку:
Вкладка «Число» — применить определенный формат к числовым значениям
Используйте эту вкладку, чтобы применить желаемый формат с точки зрения числа, даты, валюты, времени, процента, доли, научной нотации, формата номера счета или текста. Доступные параметры форматирования зависят от выбранной категории .
Excel Числовой формат
Для номеров вы можете изменить следующие параметры:
- Сколько десятичных знаков для отображения.
- Показать или скрыть разделитель тысяч .
- Специальный формат для отрицательных чисел .
По умолчанию формат числа Excel выравнивает значения прямо в ячейках.
Совет. В соответствии с Образец , вы можете просмотреть предварительный просмотр 9009 того, как число будет отформатировано на листе.
Форматы валюты и учета
Формат Валюта позволяет настроить следующие три параметра:
- Количество десятичных знаков для отображения
- Символ валюты для использования
- Формат для применения к отрицательным числам
Совет. Чтобы быстро применить формат валюты по умолчанию с двумя десятичными знаками, выберите ячейку или диапазон ячеек и нажмите сочетание клавиш Ctrl + Shift + $.
Формат Excel Accounting предоставляет только первые две из указанных выше опций, отрицательные числа всегда отображаются в скобках:
Оба формата валюты и учета используются для отображения денежных значений.Разница заключается в следующем:
- Формат Excel Валюта размещает символ валюты непосредственно перед первой цифрой в ячейке.
- Числовой формат Excel Бухгалтерия выравнивает символ валюты слева и значения справа, нули, как показано тире.
Форматы даты и времени
Microsoft Excel предоставляет множество предопределенных форматов Дата и Время для различных языков:
Для получения дополнительной информации и подробного руководства о том, как создать пользовательский формат даты и времени в Excel, см.
.Процентный формат
Формат Percentage отображает значение ячейки с символом процента.Единственный параметр, который вы можете изменить, это количество десятичных знаков.
Чтобы быстро применить формат Процент без десятичных разрядов, используйте сочетание клавиш Ctrl + Shift +%.
Примечание. Если вы примените формат Percentage к существующим числам, числа будут умножены на 100.
Для получения дополнительной информации см. Как показать проценты в Excel.
Фракционный формат
Этот формат позволяет выбирать из множества встроенных стилей дроби:
Примечание. При вводе дроби в ячейку, которая не отформатирована как Дробь , вам может потребоваться ввести ноль и пробел перед дробной частью. Например, если вы введете 1/8, если ячейка отформатирована как General , Excel преобразует ее в дату (08 января). Чтобы ввести дробь, введите 0 1/8 в ячейку.
Научный формат
Научный формат (также называемый формой Standard или Standard Index ) представляет собой компактный способ отображения очень больших или очень маленьких чисел.Это обычно используется математиками, инженерами и учеными.
Например, вместо записи 0,0000000012 можно написать 1,2 x 10 -9 . И если вы примените формат записи Excel Scientific к ячейке, содержащей 0,0000000012, число будет отображаться как 1.2E-09.
При использовании формата научной нотации в Excel единственная опция, которую вы можете установить, это количество десятичных знаков:
Чтобы быстро применить формат записи Excel Scientific по умолчанию с двумя десятичными знаками, нажмите Ctrl + Shift + ^ на клавиатуре.
Excel Текстовый формат
Когда ячейка отформатирована как текст, Excel будет обрабатывать значение ячейки как текстовую строку, даже если вы введете число или дату. По умолчанию формат Excel Text выравнивает значения, оставленные в ячейке. При применении текстового формата к выбранным ячейкам через диалоговое окно Формат ячеек опция не подлежит изменению.
Помните, что формат Excel Text , применяемый к номерам , датам или , запрещает их использование в функциях и вычислениях Excel.Числовые значения, отформатированные как текст, заставляют маленький зеленый треугольник появляться в верхнем левом углу ячеек, указывая, что что-то может быть не так с форматом ячейки. И если ваша, казалось бы, правильная формула Excel не работает или возвращает неправильный результат, первое, что нужно проверить, — это числа, отформатированные как текст.
Для исправления текстовых чисел недостаточно задать формат ячейки «Общий» или «Число». Самый простой способ преобразовать текст в число — выбрать проблемные ячейки, щелкнуть появившийся предупреждающий знак и затем щелкнуть Преобразовать в число во всплывающем меню.Несколько других методов описаны в разделе Как преобразовать текстовые цифры в число.
Специальный формат
Специальный формат позволяет отображать числа в формате, обычном для почтовых индексов, телефонных номеров и номеров социального страхования:
Пользовательский формат
Если ни один из встроенных форматов не отображает данные так, как вы хотите, вы можете создать свой собственный формат для чисел, дат и времени. Вы можете сделать это, изменив один из предопределенных форматов, близких к желаемому результату, или используя символы форматирования в ваших собственных комбинациях.В следующей статье мы предоставим подробные инструкции и примеры для создания собственного формата чисел в Excel.
Вкладка выравнивания — изменить выравнивание, положение и направление
Как следует из названия, эта вкладка позволяет изменять выравнивание текста в ячейке. Кроме того, он предоставляет ряд других опций, в том числе:
- Выровняйте содержимого ячейки по горизонтали, вертикали или центру. Кроме того, вы можете центрировать значение по выбору (отличная альтернатива объединению ячеек!) Или отступ с любого края ячейки.
- Оберните текст в несколько строк в зависимости от ширины столбца и длины содержимого ячейки.
- Уменьшить до — эта опция автоматически уменьшает видимый размер шрифта, чтобы все данные в ячейке помещались в столбец без переноса. Реальный размер шрифта, примененный к ячейке, не изменяется.
- Объедините двух или более ячеек в одну ячейку.
- Измените направление текста на , чтобы определить порядок чтения и выравнивание.Значением по умолчанию является «Контекст», но вы можете изменить его на «Справа налево» или «Слева направо».
- Изменить текст ориентация . Ввод положительного числа в поле Градусы вращает содержимое ячейки слева направо и снизу вверх, а отрицательный градус выполняет вращение слева направо вниз. Этот параметр может быть недоступен, если для данной ячейки выбраны другие параметры выравнивания.
На снимке экрана ниже показаны настройки вкладки выравнивания по умолчанию:
Вкладка «Шрифт» — изменение типа, цвета и стиля шрифта
Используйте параметры вкладки «Шрифт» для изменения типа шрифта, цвета, размера, стиля, эффектов шрифта и других элементов шрифта:
Вкладка Граница — создайте границы ячеек разных стилей
Используйте параметры вкладки «Граница», чтобы создать рамку вокруг выделенных ячеек в выбранном вами цвете и стиле.Если вы не хотите удалять существующую границу, выберите Нет .
Совет. Для скрытия линий сетки в определенном диапазоне ячеек, вы можете применить белые границы (Outline и Inside) к выделенным ячейкам, как показано на скриншоте ниже:Заполнить вкладку — изменить цвет фона ячейки
Используя параметры этой вкладки, вы можете заполнять ячейки разными цветами, узорами и специальными эффектами заполнения.
Защитная вкладка — блокировка и скрытие ячеек
Используйте параметры защиты, чтобы заблокировать или скрыть определенные ячейки при защите листа.Для получения дополнительной информации, пожалуйста, ознакомьтесь со следующими уроками:
Параметры форматирования ячейки на ленте
Как вы только что видели, диалоговое окно Format Cells предоставляет большое разнообразие вариантов форматирования. Для нашего удобства наиболее часто используемые функции также доступны на ленте.
Самый быстрый способ применения форматов чисел Excel по умолчанию
Чтобы быстро применить один из форматов Excel по умолчанию с точки зрения числа, даты, времени, валюты, процента и т. Д., сделайте следующее:
- Выберите ячейку или диапазон ячеек, формат которых вы хотите изменить.
- Нажмите маленькую стрелку рядом с полем Числовой формат на вкладке Домашняя страница в группе Номер и выберите нужный формат:
Параметры формата учета на ленте
Помимо изменения формата ячейки, группа с номером предоставляет некоторые из наиболее часто используемых вариантов формата учета:
- Чтобы применить формат бухгалтерских номеров Excel с символом валюты по умолчанию , выберите ячейку (и) и щелкните значок «Бухгалтерский номер ».
- Чтобы выбрать символ валюты , щелкните стрелку рядом со значком Accounting Number и выберите необходимую валюту из списка. Если вы хотите использовать какой-либо другой символ валюты, нажмите Больше учетных форматов… в конце списка, откроется диалоговое окно Формат ячеек с дополнительными параметрами.
- Чтобы использовать разделитель тысяч , щелкните значок запятой.
- Чтобы отобразить больше или меньше знаков после запятой , щелкните значок Увеличение десятичного числа или Уменьшение десятичного знака соответственно.Этот параметр можно использовать для формата учета в Excel, а также для числовых, процентных и валютных форматов.
Другие параметры форматирования на ленте
На вкладке Home ленты Excel вы можете найти гораздо больше параметров форматирования, таких как изменение границ ячейки, цвета заливки и шрифта, выравнивание, ориентация текста и т. Д.
Например, чтобы быстро добавить границы в выбранные ячейки, нажмите стрелку рядом с кнопкой Граница в группе «Шрифт » и выберите нужный макет, цвет и стиль:
Ярлыки форматирования Excel
Если вы внимательно следили за предыдущими частями этого учебника, вы уже знаете большинство ярлыков форматирования Excel.В таблице ниже представлена сводная информация.
| Ярлык | Формат |
| Ctrl + Shift + ~ | Общий формат |
| Ctrl + Shift +! | Числовой формат с разделителем тысяч и двумя десятичными знаками. |
| Ctrl + Shift + $ | Формат валюты с 2 десятичными знаками и отрицательными числами, отображаемыми в скобках |
| Ctrl + Shift +% | Процентный формат без десятичных знаков |
| Ctrl + Shift + ^ | Формат научного обозначения с двумя десятичными знаками |
| Ctrl + Shift + # | Формат даты (дд-ммм-гг) |
| Ctrl + Shift + @ | Формат времени (чч: мм AM / PM) |
Формат чисел Excel не работает
Если после применения одного из числовых форматов Excel в ячейке появляется несколько символов хеша (######), это обычно происходит по одной из следующих причин:
- Ячейка недостаточно широка для отображения данных в выбранном формате.Чтобы исправить это, все, что вам нужно сделать, это увеличить ширину столбца, перетаскивая правую границу. Или дважды щелкните правую границу, чтобы автоматически изменить размер столбца, чтобы он соответствовал наибольшему значению в столбце.
- Ячейка содержит отрицательную дату или дату, выходящую за пределы поддерживаемого диапазона дат (с 1 января 1900 г. по 31 декабря 1999 г.).
Чтобы различать два случая, наведите указатель мыши на ячейку со знаками хеша. Если ячейка содержит допустимое значение, которое слишком велико, чтобы поместиться в ячейку, Excel отобразит всплывающую подсказку со значением.Если ячейка содержит недопустимую дату, вы получите уведомление о проблеме:
Это то, как вы используете основные параметры форматирования чисел в Excel. В следующем уроке мы обсудим самые быстрые способы копирования и очистки форматирования ячеек, а после этого проводника продвинутые методы создания пользовательских форматов чисел. Я благодарю вас за чтение и надеюсь увидеть вас снова на следующей неделе!