Как заставить запятую исчезнуть, если после нее будут только нули?

Я хотел бы, чтобы числа отображались следующим образом:

1250000 -> 1,25M
2000000 -> 2M
300000  -> 300k

Итак, если число меньше 1 миллиона, тогда отобразите (число /1000)k; если оно больше миллиона, отобразите (число /1000000)M.

Пока у меня есть

[>=1000000]0,00  "M";[<1000000]0 "k"

Проблема в том, что он отображает, например, 1000000 как 1,00M.

Я мог бы поменять на

[>=1000000]0,##  "M";[<1000000]0 "k"

но это показывает 1000000 как 1,M.

Кто-нибудь знает, как сделать так, чтобы запятая исчезла на несколько миллионов?

Источник

2 ответа

Решение

Я думаю, что вам не хватает нескольких запятых в коде формата (я работаю с локализацией в США). Для формата чисел в ваших ячейках используйте [>=1000000]#.##,, "M";[<1000000]0,"k", Это даст вам:

1250000 -> 1.25M
2000000 -> 2.M
300000  -> 300k

Чтобы избавиться от. в 2.M вы можете использовать условный формат. Если число кратно 1 000 000, используйте другой формат.

  1. Выделите свои ячейки, и Условное форматирование -> Новое правило...
  2. Выберите "Использовать формулу..." и введите =MOD(CellAddress,1000000)=0
  3. Нажмите "Формат" и установите для пользовательского формата номера значение [>=1000000]#,, "M";[<1000000]#,"k"

На шаге 2 вы также можете использовать =MOD(H30,1000000)<4900 если вы имеете дело с не круглыми числами. Это должно дать вам:

1250000 -> 1.25M
2000001 -> 2M
 300000 -> 300k
1000000 -> 1M
1005000 -> 1.01M (Rounded up)
Источник

Я создал небольшую UDF (пользовательскую функцию) для решения этой проблемы:

Function formatLargeNumber(myValue As Long) As String

If myValue < 1000000 Then
    formatLargeNumber = CStr(Round(myValue / 1000, 0)) & " K"
    Exit Function
End If

If XLMod(myValue, 1000000) < 5000 Then
    formatLargeNumber = CStr(Round(myValue / 1000000, 0)) & " M"
Else
    formatLargeNumber = Format(CStr(Round(myValue / 1000000, 2)), "#.00") & " M"
End If

End Function
Function XLMod(a, b)
    ' This replicates the Excel MOD function
    XLMod = a - b * Int(a / b)
End Function

Вы можете применить это прямо в электронную таблицу, набрав:

=formatLargeNumber(cellreference)

Или проверьте это с помощью этого саба:

Sub testFunction()
    Dim x As Long, y As String
    x = 1005001 'change his to test
    y = formatLargeNumber(x)
    Debug.Print y
End Sub

Загляните сюда, если вы хотите узнать больше о UDF: пользовательские функции (Excel Easy) или создание UDF (лучшие решения)

Источник
Другие вопросы по тегам