Отформатируйте число с дополнительными десятичными знаками в Excel

Question

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

Например.

• 15 должно быть отформатировано как 15, НЕ 15.00
• 14.3453453 должен быть отформатирован как 14.35
• 12.1 должен быть отформатирован как 12.1
• 0 должен быть отформатирован как 0

Самый близкий код пользовательского формата, который я придумал, - 0.## . К сожалению, это форматирует 15.00 как 15. (обратите внимание на дополнительный десятичный знак).

Чтобы еще больше усложнить проблему, электронная таблица является результатом экспорта из служб отчетов SQL Server. Так что макросы невозможны. О, хорошо, похоже, 0.## - моя лучшая ставка, и они могут просто жить с дополнительным периодом.

Answer 1

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

Числовой формат: General;[Red](General)

Это добавит десятичное число и дробное значение соответственно, а форматирование отрицательных чисел будет более разборчивым.

Что касается исходного вопроса плаката, вам все равно нужно округлить / усечь "лишние" десятичные точки, используя формулу. Тем не менее, это простая формула =ROUND(<value>,<desired decimal places>) которая не является чрезвычайно дорогой в вычислительном отношении.

Примеры:

2500 -> 2500
0.25 -> 0.25
-2500 -> (2500)
-0.25 -> (0.25)

Answer 2

Примените условное форматирование для недесятичных чисел.

Например, А1 является целевой ячейкой.

1. Отформатируйте A1 как "###, ###. 00". Это будет использоваться для десятичного числа.

2. Определите условное форматирование для недесятичных чисел.

   • Условие: значение ячейки равно = TRUNC(A1).
   • Формат: ###,###

Ниже приведен результат:

12       =>  12
14.231   => 14.23
15.00000 => 15
17.3     => 17.30

Answer 3

Пользовательские форматы Excel могут дать частичный ответ

Пользовательские форматы для чисел в Excel вводятся в этом формате:

• формат положительного числа; формат отрицательного числа; нулевой формат; формат текста

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

• , ##.??;(. #, ## ??); 0

Пример:

• 15 отображается как 15.
• 14.3453453 отображается как 14.35
• 12.1 отображается как 12.1
• 0 отображается как 0

Answer 4

Я столкнулся с этим недавно в Excel 2007 и только что использовал функцию 'TRUNC' в ячейках значений:

  value  =TRUNC(B5,1)
      0      0
      5      5
    5.4    5.4
  65.43   65.4
765.432  765.4

Работало именно так, как я хотел ...

Answer 5

Вот один метод, использующий условное форматирование.

1. Щелкните правой кнопкой мыши по ячейке, выберите "Формат ячейки"
2. Выберите "Custom"
3. Введите «0. ####» (добавьте больше или меньше #, чтобы контролировать максимальное количество знаков после запятой)
4. Нажмите ОК
5. Если ячейка еще выбрана, используйте "Условное форматирование" (это может быть пункт меню в "Формате" или кнопка в "Домашнем" в зависимости от версии Excel)

6. Добавить новое правило на основе формулы "= MOD (H32,1)= 0"

   изменить - лучше формула "= MOD (ROUND (H32,2), 1)= 0" с "2" является желаемым количеством десятичных знаков. предыдущая формула оставляет запятой после запятой, если число округляется до целого числа.

   Замените H32 в формуле идентификатором вашей ячейки, но УБЕДИТЕСЬ, ЧТО ЗНАКОВ НЕТ! (Без знаков $ вы можете скопировать формат в другие ячейки)

7. Для формата этого правила выберите вкладку номера, выберите "Пользовательский"

8. На этот раз введите формулу "0"
9. Нажмите OK достаточно раз, чтобы вернуться к листу (зависит от версии Excel)

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

Answer 6

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

iif(floor(Fields!numericFieldName.Value)=Fields!numericFieldName.Value,"0","0.##")

как выражение числового формата. Это привело к созданию файла Excel с двумя форматами ячеек, выбранными в зависимости от того, было ли значение целым числом.

Answer 7

Мой ответ удаляет десятичную точку И удаляет 0 значение

в условном форматировании напишите:

General;#;;@

Это должно сделать свое дело.

Answer 8

Это не должно быть настолько сложным - это можно сделать исключительно с помощью условного форматирования. У меня есть Excel 2010, так что это может не работать для более ранних версий.

1. Отформатируйте ячейки как общие. Количество десятичных разрядов для некоторых чисел может быть очень большим.
2. Выделите ячейку и нажмите "Условное форматирование". Нажмите Новое правило, затем "Использовать формулу".
3. Используйте формулу =(B1-INT(B1))> 0 (где B1 - ячейка, которую вы форматируете)
4. Нажмите кнопку "Формат" и выберите "Пользовательский". Введите [= 0] 0;. ## в поле Тип.
5. Нажмите Enter несколько раз.
6. Снова нажмите "Условное форматирование", нажмите "Управление правилами" и измените только что созданное правило. Измените диапазон в поле "Относится к", чтобы охватить диапазон, который вы хотите охватить.

Все сделано.

Answer 9

Вы / ваши пользователи вводите значения непосредственно в ячейки, или они заполняются формулой или макросом?

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

=IF(ROUND(A1,2)=INT(A1),TEXT(A1,"0"),TEXT(A1,"0.0#"))

(где 'A1' является ячейкой, на которую ссылаются)

Формула будет отображать такие значения:

 -------------------------
 |Original     |Formatted|
 |-------------+---------|
 |         15  |       15|
 | 14.3453453  |    14.35|
 |       12.1  |     12.1|
 |          0  |        0|
 |    -15.123  |   -15.12|
 |      1.004  |        1|
 -------------------------

Примечание: вывод формулы - это текстовая строка, а не числовая, поэтому:

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

Answer 10

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

Answer 11

B1=IF(ROUND(A1,2)=INT(A1),TEXT(A1,"0"),TEXT(A1,"0.##"))
C1=IF(ROUND(B1,2)=INT(B1),TEXT(B1,"0"),TEXT(B1,"0.##"))

Нет больше сиротских десятичных знаков.

Answer 12

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

=IF((A1=""), "",IF(ROUND(A1,2)=INT(A1),TEXT(A1,"0"),TEXT(A1,"0.0#")))

Снова, где A1 - ячейка, на которую ссылаются.

Answer 13

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

ROUND(MyValue,Sig.figs - 1 - INT(LOG10(ABS(MyValue)))

Answer 14

Похоже, что это самый простой способ показать десятичное число, если есть дробные числа, а для целых чисел нет десятичного числа:

=TEXT(ROUND(Base_Number,Max_Decimal_Places),"general")

или со ссылками на ячейки:

=TEXT(ROUND(A1,2),"general")

дает

15 = 15
9,23432234 = 9,23