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

Question

Я использую Excel и хочу отобразить значение для определенного количества значащих цифр.

Я попытался использовать следующее уравнение

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

со значением, замененным числом, которое я использую, и sigfigs, замененным количеством значащих цифр, которые я хочу.

Эта формула работает иногда, но в других случаях это не так.

Например, значение 18.036 изменится на 18, что имеет 2 значащие цифры. Обходной путь - изменить исходное форматирование, сохранив 1 десятичное место. Но это может внести дополнительную значимость. Например, если результат был 182, а затем десятичное место изменило его на 182,0, теперь у меня было бы 4 сиг-фига вместо 3.

Как мне получить Excel, чтобы установить для меня количество сиг-фиг, чтобы мне не приходилось вычислять это вручную?

Answer 1

Формула (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 для другой математики, если это необходимо.

Answer 2

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

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

Проблема:

Мне нужно было сообщать переменное количество значащих цифр в положительном и отрицательном режиме с числами от 10^-5 до 10^5. Кроме того, согласно клиенту (и к фиолетовой математике), если значение 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

Результаты теста:

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

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

Тест 3 Sig Figs

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

Заметки:

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

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

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

Answer 3

Используйте научную запись, скажем, если у вас есть 180000, и вам нужно 4 sigfigs, единственный способ - набрать как 1.800x10^5

Answer 4

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

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

значение:: Число, которое вы хотите округлить.

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

Answer 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.

Answer 6

Это старый вопрос, но я изменил VBA-код sancho.s, чтобы эта функция принимала два аргумента: 1) число, которое вы хотите отобразить с соответствующим sig figs (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

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

Answer 7

Формула ниже работает отлично. Количество значащих цифр задается в первой текстовой формуле. 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, чтобы избежать распространения незначительных ошибок.

Answer 8

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

Т.е. #, ###. покажет цифры в тысячах. #, ###.. показывает цифры в миллионах.

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

Answer 9

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

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

,

Домашняя страница> Стили> Условное форматирование

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

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

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

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

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

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

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

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

,

Убедитесь, что они в этом порядке, и установите все флажки "Stop If True".

Answer 10

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

Вот ускоренный курс: 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