Форматирование числа с необязательными десятичными знаками в Excel

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

Например.

  • 15 следует отформатировать как 15, а не 15.00
  • 14.3453453 следует отформатировать как 14.35
  • 12.1 следует отформатировать как 12.1
  • 0 следует отформатировать как 0

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

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

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

Формат номера: General;[Red](General)

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

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

Примеры:

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

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

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

  1. Формат A1 как «###, ###. 00». Это будет использоваться для десятичного числа.
  2. Определить условное форматирование для не десятичных чисел.

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

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

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

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

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

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

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

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

Пример:

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

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

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

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

Вот один метод с использованием условного форматирования.

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

    Edit – лучшая формула: «= MOD (ROUND (H32,2), 1) = 0», а «2» – желаемое количество знаков после запятой. Предыдущая формула оставляет конечную десятичную точку, если число округляется до целого числа.

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

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

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

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

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

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

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

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

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

 =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| ------------------------- 

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

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

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

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

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

Все сделано.

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

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

Опять же, где A1 – это ссылка на ячейку.

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

 ROUND(MyValue,Sig.figs - 1 - INT(LOG10(ABS(MyValue))) 
 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.##")) 

Больше нет сирот десятичных точек.

Это, по-видимому, самый простой способ показать десятичное число, когда есть дробные числа – и No Decimal для целых чисел:

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

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

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

дает

15 = 15
9,23432234 = 9,23

  • HYPERLINK не работает, когда ссылка на внешнюю книгу закрыта
  • Поиск в Excel для подсчета подстроки и отображения
  • Группировка меток и объединение их текстовых значений (например, сводной таблицы)
  • Как найти и заменить имя листа в формуле?
  • Хотите использовать HLOOKUP для перетаскивания всего столбца, как я могу это сделать?
  • Почему в Microsoft Excel не работают клавиши со стрелками?
  • Как применять формат строки adiacent в Excel, когда ячейка пуста
  • Excel 2013 - Как удалить дубликаты из 1 столбца, но оставить пустые ячейки вместо дубликатов?
  • Как настроить Excel для импорта всех столбцов CSV-файлов в виде текста?
  • Не удается открыть файл Excel 2010 на SharePoint Foundation 2013
  • Переместите каждую ячейку после столбца A на новую строку
  • Interesting Posts

    Понимание .AsEnumerable () в LINQ to SQL

    Ошибка манифеста слияния с несколькими ошибками в Android Studio

    Существуют ли другие распространенные «c-like» или «c-like» языки с ненулевой позицией индекса?

    Маленькие подручные версии Linux (или темы для Linux)

    Как сделать снимок экрана в Java?

    Идентификация ASP.NET с базой данных EF Первый MVC5

    htaccess код для удаления расширения и добавить + принудительное завершение косой черты?

    Захватывающие группы Java Regex

    Есть ли недостаток в использовании ведущего двойного слэша для наследования протокола в URL? т.е. src = “// domain.com”

    Есть ли способ закладки всех вкладок в Chrome?

    Windows 7 64bit – 16GB Ram, использующий только 4 ГБ?

    Как я могу разобрать CSV в Perl с регулярным выражением?

    Кнопка печати ReportViewer несовместима с IE 10?

    Установка Git на CentOS

    Каков наименее инвазивный способ чтения заблокированного файла на C # (возможно, в небезопасном режиме)?

    Давайте будем гением компьютера.