Кен спросил, можно ли сохранить количество значащих цифр для значений, введенных в Excel. Например, «12.345600» и «1.230».

Короткий ответ, Кен, ты не можешь. Зачем? Потому что Excel всегда внутренне преобразует все до 15 значащих цифр. Таким образом, если вы вводите 12, 12,0 или 12,00000, Excel всегда считает их одинаковыми как 12,0000000000000, даже если это может быть технически неверным в истинном математическом смысле. Кроме того, поскольку Excel преобразует все в 15 значащих цифр, он обрезает любые конечные нули в любых числах, отображаемых с использованием общего формата чисел.

(Из приведенного выше объяснения должно быть очевидно, что термины «значащие цифры», «точность» и «количество десятичных знаков» означают совершенно разные вещи. Например, число 12.000 имеет три десятичных разряда, но имеет пять значащих цифр. (Я уверен, что теоретики математики в аудитории будут очень рады поправить меня, если я ошибаюсь.)

Вы можете использовать собственный числовой формат для отдельных ячеек, в котором будут отображаться ваши информации с использованием правильного количества значащих цифр, но это не повлияет на внутреннюю работу Excel. Например, вы можете отформатировать ячейку для отображения 12, 12,0 или 12,00000, но это не меняет того факта, что Excel по-прежнему считает все числа быть 12,0000000000000.

Один из способов решения этой проблемы — ввести числа в виде текста, поставив перед ними апостроф. Другими словами, вместо ввода 12,0 вы должны ввести ’12 .0. Excel затем анализирует запись как текст (из-за апоса trophe) и отображает в ячейке 12.0. Единственным недостатком этого подхода является то, что вы не можете напрямую ссылаться на ячейку во всех математических функциях; некоторые из них вернут ошибку, потому что вы включаете в функцию то, что Excel считает текстом. Самый простой способ справиться с этим — убедиться, что вы включили функцию = VALUE как часть общей формулы. Например, если вы вводите число в виде текста в ячейку B7, а затем вам нужно указать его в формуле, вы можете сделать это следующим образом:

=VALUE(B7) * 100

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

Это подводит нас ко второму потенциальному решению: сохранение либо значащих цифр, либо количества десятичных знаков отдельно от фактического значения. Для многих технических математических целей это лучший способ справиться с ситуацией. Например, в столбце C вы можете ввести свои числа в виде чисел. Excel, конечно, внутренне преобразует их в 15 значащих цифр. В столбце D вы можете ввести различные числа, которые представляют количество десятичных разрядов, которые вы хотите применить к значениям в столбце C. Затем вы можете использовать значения в столбце D, чтобы помочь в формулах, которые относятся к значениям в столбце C.

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

С оговоркой, упомянутой выше (что такое форматирование не влияет на внутреннее представление чисел в Excel), это может быть лучшим решением. Конечно, индивидуально настроить форматирование отображения пары ячеек легко, но сделать это для ста или пятисот ячеек — это совсем другое дело. Здесь может пригодиться макрос. Рассмотрим следующий макрос:

Sub SigPlaces()

Dim c As Range Dim strcell As String, NumFormat As String, DecSep As String Dim DecPtLoc As Integer, stringLen As Integer Dim numDecPlaces As Long

DecSep = "."



For Each c In Selection.Cells     strcell = c.Value     If IsNumeric(strcell) Then         NumFormat = "#0"

DecPtLoc = InStr(strcell, DecSep)

If DecPtLoc > 0 Then             NumFormat = NumFormat & DecSep             stringLen = Len(strcell)

numDecPlaces = stringLen - DecPtLoc             NumFormat = NumFormat & String(numDecPlaces, "0")

End If         c.Value = Val(strcell)

c.NumberFormat = NumFormat     End If Next c End Sub

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

_Примечание: _

Если вы хотите узнать, как использовать макросы, описанные на этой странице (или на любой другой странице на сайтах ExcelTips), я подготовил специальную страницу, содержащую полезную информацию.

link: / excelribbon-ExcelTipsMacros [Щелкните здесь, чтобы открыть эту специальную страницу в новой вкладке браузера].

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (2045) применим к Microsoft Excel 97, 2000, 2002 и 2003.