Как получить первую значащую цифру из числа в Excel?

У меня есть столбец чисел в Excel 2016. Числа охватывают много порядков, но все они положительные. Некоторые меньше нуля. Как я могу вернуть первую значащую цифру каждой ячейки в новом столбце?

Например, для числа 1,9 результат должен быть 1. Для числа 0,9 результат должен быть 9.

Что я пробовал:

  • Использование LEFT() для получения первого символа. Это работает для значений больше 1, но для чисел от 0 до 1 возвращает 0 (то есть LEFT(0.3, 1) возвращает 0). Я пытался использовать это с форматированием научной нотации, и он возвращает тот же результат.
  • Я искал Google и SO для решения этой проблемы. Есть много сообщений об округлении до значащих цифр, но я ищу усечение, а не округление.
  • Чтение онлайн-документов Office, касающихся научных обозначений.

excel excel-formula excel-2016 significant-digits
person indigochild    schedule 24.07.2019    source источник
comment
А как насчет 0.003, будет ли он по-прежнему 3?   -  person Scott Craner    schedule 24.07.2019
comment
@ СкоттКранер Да.   -  person indigochild    schedule 24.07.2019

Ответы (4)


arrow_upward
2
arrow_downward

Вы можете использовать научную нотацию:

=LEFT(TEXT(A1,"0.000000000000000E+00"))

введите здесь описание изображения

Примечание. В Excel может быть только 15 цифр точности, так что это должно быть в порядке.

person Tom Sharpe    schedule 24.07.2019
comment
Кажется, что использование 0.0E+00 также работает. Хорошее решение! =--LEFT(TEXT(A1,"0.0E+00")) - person Terry W; 25.07.2019
comment
Спасибо! Я был обеспокоен тем, что в случае 2,9999999 оно может быть округлено до 3, если я не добавлю достаточно нулей, но, конечно, вы все равно найдете число, подобное 2,99999999999, которое в конечном итоге будет округлено. - person Tom Sharpe; 25.07.2019

arrow_upward
2
arrow_downward

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

=--LEFT(A1*10^LEN(A1),1)

![введите описание изображения здесь

person Scott Craner    schedule 24.07.2019
comment
+1. Спасибо, что опубликовали это как ответ. Это определенно работает, но требует знания порядка величины наименьшего значения. Я надеюсь на решение, которое работает независимо от порядка величины значений. - person indigochild; 24.07.2019
comment
@indigochild см. редактировать, теперь это не имеет значения. Множитель будет достаточно большим в любом случае. - person Scott Craner; 24.07.2019

arrow_upward
2
arrow_downward

Считайте значение ячейки как текст, замените точки и нули (./0) ничем, верните самый левый «символ»; умножьте его на 1, чтобы вернуть его в число:

=LEFT(SUBSTITUTE(SUBSTITUTE(TEXT(A1,"@"),".",""),"0",""))*1
person David    schedule 24.07.2019
comment
Привет, Дэвид, твоя идея довольно хороша, и я немного изменил формулу, и она все еще работала =--LEFT(--SUBSTITUTE(A2,".",""),1), не нужно заменять 0, просто нужно избавиться от . и преобразовать новую строку в число и найти первое число слева. - person Terry W; 25.07.2019

arrow_upward
0
arrow_downward

Вы также можете создать пользовательскую UDF (определяемую пользователем функцию), которая использует регулярные выражения для выполнения этой задачи. Для этого потребуется скопировать/вставить знание VBA, а также установить ссылку на:

Регулярные выражения Microsoft VBScript 5.5

(что можно сделать, перейдя в VBE (Alt+F11), Инструменты > Ссылки. Затем установите флажок для ссылки, указанной выше)

Вставьте следующую пользовательскую функцию в стандартный модуль кода в VBE:

Public Function SigNum(ByVal InputNumber As Double) As Long

    Dim s As SubMatches

    With New RegExp

        .Pattern = "\.0*([^0])|^([^0])"
        If .test(InputNumber) Then

            Set s = .Execute(InputNumber)(0).SubMatches
            If s(0) > 0 Then    ' This is before the period
                SigNum = s(0)
            Else
                SigNum = s(1)
            End If

        End If

    End With

End Function

На своем рабочем листе вы сможете использовать только что созданную формулу как таковую:

=SigNum(A1)

Вы можете увидеть, что он соответствует, в примере на regex101. При просмотре этого сайта выделенные зеленым цветом числа будут возвращены, если значение равно ‹ 0, а красным цветом будут возвращены значения, если значение > 0). Если значение = 0, это вернет 0.

Разрушение шаблона

Вот как работает шаблон \.0*([^0])|^([^0]). Во-первых, вы можете видеть, что есть оператор |, который по сути действует как оператор Or, поэтому мы разделим его на две части.

Первый раздел \.0*([^0])

  • \. будет соответствовать буквальному периоду. Это гарантирует, что мы смотрим на значение меньше 0.
  • 0* соответствует всем нулям, от 0 до неограниченного количества * раз. Мы используем * (от нуля до неограниченного) вместо + (от 1 до неограниченного), потому что ноль не обязательно должен стоять перед значащим числом, но сам ноль не является значащим.
  • [^0] Это отрицательный класс символов [^...]. Это означает, что он будет соответствовать всему, чего нет в этом классе. Поскольку наше значимое число должно быть значением, отличным от нуля, мы не хотим совпадать с нулем. И поскольку он окружен группой захвата (...), это то, что возвращается обратно в функцию.

Второй раздел ^([^0])

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

  • ^ это точка привязки, соответствующая началу строки. В первом разделе нам это не требовалось, потому что мы фактически использовали период \. в качестве точки привязки. Поскольку наше значение больше 0, нам нужно убедиться, что мы начинаем с абсолютного левого угла входного числа.
  • (...) Группа захвата. Все в этой группе будет возвращено как частичное совпадение и, в конечном счете, обратно в функцию в качестве возвращаемого значения.
  • [^0] Отрицательный класс символов. Он будет соответствовать чему угодно, кроме 0.
person K.Dᴀᴠɪs    schedule 24.07.2019