Как заставить excel отображать определенное количество значащих цифр?

Формула (A2 содержит значение, а B2 sigfigs)

=ROUND(A2/10^(INT(LOG10(A2))+1),B2)*10^(INT(LOG10(A2))+1)

может дать вам номер, который вы хотите, скажем, в C2. Но если последняя цифра равна нулю, то она не будет отображаться в общем формате. Затем вы должны применить числовой формат, специфичный для этой комбинации (значение, sigfigs), и это через VBA. Следующее должно работать. Вы должны передать три параметра (val,sigd,trg), trg — это целевая ячейка для форматирования, где у вас уже есть нужный номер.

Sub fmt(val As Range, sigd As Range, trg As Range)
    Dim fmtstr As String, fmtstrfrac As String
    Dim nint As Integer, nfrac As Integer
    nint = Int(Log(val) / Log(10)) + 1
    nfrac = sigd - nint
    If (sigd - nint) > 0 Then
      'fmtstrfrac = "." & WorksheetFunction.Rept("0", nfrac)
      fmtstrfrac = "." & String(nfrac, "0")
    Else
      fmtstrfrac = ""
    End If
    'fmtstr = WorksheetFunction.Rept("0", nint) & fmtstrfrac
    fmtstr = String(nint, "0") & fmtstrfrac
    trg.NumberFormat = fmtstr
End Sub

Если вы не возражаете против строки вместо числа, вы можете получить строку формата (скажем, в D2) как

=REPT("0",INT(LOG10(A2))+1)&IF(B2-(INT(LOG10(A2))+1)>0,"."&REPT("0",B2-(INT(LOG10(A2))+1)),"")

(это повторяет код VBA), а затем использовать (скажем, в E2)

=TEXT(C2,D2).

где ячейка C2 по-прежнему имеет приведенную выше формулу. Вы можете использовать ячейку E2 для целей визуализации и число, полученное в C2, для других математических операций, если это необходимо.

ВНИМАНИЕ: впереди невероятно длинная формула Excel

Я также хотел работать со значащими цифрами, но не смог использовать VBA, поскольку электронные таблицы их не поддерживают. Я зашел на этот вопрос / ответ и на многие другие сайты, но все ответы, похоже, не всегда касаются всех чисел. Меня заинтересовал принятый ответ, и он был близок к этому, но как только мои цифры стали ‹ 0,1, я получил ошибку #value!. Я уверен, что мог бы исправить это, но я уже был на пути и просто продолжал.

Проблема:

Мне нужно было сообщить переменное количество значащих цифр в положительном и отрицательном режиме с числами от 10 ^ -5 до 10 ^ 5. Кроме того, согласно клиенту (и purple math), если значение 100 было задано с точностью до +/- 1, и мы хотим представить с 3-значными цифрами, что ответ должен быть «100». поэтому я включил и это.

Решение:

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

Он длинный, но, по-видимому, дает правильные результаты в соответствии с моим тестированием (изложенным ниже) независимо от запрошенного числа и значащих цифр. Я уверен, что это можно упростить, но в настоящее время это не входит в объем. Если кто-то хочет предложить упрощение, пожалуйста, оставьте мне комментарий!

=TEXT(IF(A1<0,"-","")&LEFT(TEXT(ABS(A1),"0."&REPT("0",sigfigs-1)&"E+00"),sigfigs+1)*10^FLOOR(LOG10(TEXT(ABS(A1),"0."&REPT("0",sigfigs-1)&"E+00")),1),(""&(IF(OR(AND(FLOOR(LOG10(TEXT(ABS(A1),"0."&REPT("0",sigfigs-1)&"E+00")),1)+1=sigfigs,RIGHT(LEFT(TEXT(ABS(A1),"0."&REPT("0",sigfigs-1)&"E+00"),sigfigs+1)*10^FLOOR(LOG10(TEXT(ABS(A1),"0."&REPT("0",sigfigs-1)&"E+00")),1),1)="0"),LOG10(TEXT(ABS(A1),"0."&REPT("0",sigfigs-1)&"E+00"))<=sigfigs-1),"0.","#")&REPT("0",IF(sigfigs-1-(FLOOR(LOG10(TEXT(ABS(A1),"0."&REPT("0",sigfigs-1)&"E+00")),1))>0,sigfigs-1-(FLOOR(LOG10(TEXT(ABS(A1),"0."&REPT("0",sigfigs-1)&"E+00")),1)),0)))))

Примечание. У меня есть именованный диапазон под названием "sigfigs", и мои числа начинаются в ячейке A1

Результаты тестирования:

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

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

Тест трех цифр

99.99 -> 100.
99.9 -> 99.9
100 -> 100.
101 -> 101

Примечания:

Обработка отрицательных чисел

Чтобы обрабатывать отрицательные числа, я включил конкатенацию с отрицательным знаком, если меньше 0, и использовал абсолютное значение для всей остальной работы.

Метод построения: изначально он был разделен примерно на 6 столбцов в Excel, которые выполняли различные шаги, а в конце я объединил все шаги в одну формулу выше.

Используйте научную нотацию, скажем, если у вас 180000 и вам нужно 4 сигфига, единственный способ — ввести 1.800x10^5.

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

=TEXT(ROUND(A1,2-1-INT(LOG10(ABS(A1)))),"0"&IF(INT(LOG10(ABS(ROUND(A1,2-1-INT(LOG10(ABS(A1)))))))<1,"."&REPT("0",2-1-INT(LOG10(ABS(ROUND(A1,2-1-INT(LOG10(ABS(A1)))))))),""))

Например, если ячейка A1 имеет значение = 1/3000, что равно 0,000333333.., приведенная выше формула в том виде, в котором она написана, выводит 0,00033.

Это старый вопрос, но я изменил код VBA sancho.s, чтобы он представлял собой функцию, которая принимает два аргумента: 1) число, которое вы хотите отобразить, с соответствующими знаками (val) и 2) количество знаков. инжир (sigd). Вы можете сохранить это как надстройку в Excel для использования в качестве обычной функции:

Public Function sigFig(val As Range, sigd As Range)
    Dim nint As Integer
    Dim nfrac As Integer
    Dim raisedPower As Double
    Dim roundVal As Double
    Dim fmtstr As String
    Dim fmtstrfrac As String

    nint = Int(Log(val) / Log(10)) + 1
    nfrac = sigd - nint

    raisedPower = 10 ^ (nint)
    roundVal = Round(val / raisedPower, sigd) * raisedPower

    If (sigd - nint) > 0 Then
        fmtstrfrac = "." & String(nfrac, "0")
    Else
        fmtstrfrac = ""
    End If

    If nint <= 0 Then
        fmtstr = String(1, "0") & fmtstrfrac
    Else
        fmtstr = String(nint, "0") & fmtstrfrac
    End If

    sigFig = Format(roundVal, fmtstr)
End Function

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

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

Пример отображения четырех цифр:

.

Используйте Главная > Стили > Условное форматирование

Новое правило > Форматировать только те ячейки, которые содержат

Значение ячейки> между> -10> 10> Формат числа 3 десятичных знака

Новое правило > Форматировать только те ячейки, которые содержат

Значение ячейки> между> -100> 100> Формат числа 2 десятичных знака

Новое правило > Форматировать только те ячейки, которые содержат

Значение ячейки> между> -1000> 1000> Формат числа 1 десятичный знак

Новое правило > Форматировать только те ячейки, которые содержат

Значение ячейки > не между > -1000 > 1000 > Формат номера 0 знаков после запятой

.

Убедитесь, что они в этом порядке, и установите все флажки «Остановить, если правда».

Формула ниже работает нормально. Количество значащих цифр задается в первой текстовой формуле. 0.00 и 4 для 3sf, 0.0 и 3 для 2sf, 0.0000 и 6 для 5sf и т.д.

=(LEFT((TEXT(A1,"0.00E+000")),4))*POWER(10,
(RIGHT((TEXT(A1,"0.00E+000")),4)))

Формула действительна для E+/-999, если у вас есть число, превышающее это, увеличьте количество последних трех нулей и замените вторые 4 на количество нулей +1.

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

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

т.е. #,###. будет показывать числа в тысячах. #,###.. показывает числа в миллионах.

Надеюсь это поможет

Вместо этого вы можете попробовать пользовательское форматирование.

Вот ускоренный курс: https://support.office.com/en-nz/article/Create-a-custom-number-format-78f2a361-936b-4c03-8772-09fab54be7f4?ui=en-US&rs=en-NZ&ad=NZ.

Для трех значащих цифр я ввожу это в поле пользовательского типа: [>100]##.0;[‹=100]#,##0

Вы могли бы попробовать

=ROUND(value,sigfigs-(1+INT(LOG10(ABS(value)))))

value :: Число, которое вы хотите округлить.

sigfigs :: Количество значащих цифр, до которого нужно округлить.