有人知道在 MS Excel 中是否可以显示带有 SI 前缀的数字吗？
我想要得到：
... 1n、1µ、1m、1、1k、1M、1G ...
而不是科学计数法：
... 1E-09、1E-06、1E-03、1、1E+03、1E+06、1E+09 ...
也许还可以添加单位，如 V（伏特）、F（法拉）等。
最好是单元格仍然包含数字，而不是字符串，这样可以轻松地更改为另一种格式（返回科学计数法或其他格式）。
MS Excel中数字格式的SI前缀
11
- jost21
2
你在superuser.com上提问可能会得到更好的答案。 - Stewbob
我之前已经添加了单位符号，这不是问题，但是前缀肯定是问题所在。（我指的是单位的前缀 :) 但它们可能也是数字的后缀） - jost21
8个回答
4
您可以使用LOG和CHOOSE来将其保留在单个公式中并且相对紧凑。
在这个公式中:
=ROUND(
E10 / (1000 ^ INT(LOG(ABS(E10),1000)) )
,0
) & CHOOSE(
INT(LOG(ABS(E10),1000)) + 6
,"f","p","n","µ","m","","k","M","G","T","P"
)
在这个公式中:
- 三次引用的E10是包含原始值的单元格。
- ROUND格式化数字以进行显示，这里四舍五入到没有小数（0）。
- INT(LOG(ABS(E10),1000))是前缀索引-5至+5。
- CHOOSE是要使用的前缀（需要正索引，因此为+6）。
- Mark
4
您可以像这样做，这是我从“百事得先生”的百万和千位数自定义数字格式化中学到的：
[>=1000000] #,##0.0,," MΩ";[<1000000] #,##0.0," kΩ";General
400渲染出0.4 kΩ（可能不是您想要的）4000渲染出4.0 kΩ40e3渲染出40.0 kΩ40e6渲染出40.0 MΩ
但是，您可能可以添加更多条款以覆盖其他范围。 算了，您不能。
- endolith
1
没有比科学计数法更好的解决方案。
如果您使用自定义数字格式，则必须手动输入它们（或使用 VBA），以便它们掩盖单元格中的实际内容。
例如，如果您想显示以下格式对：
如果您有0.001，您需要将格式设置为
您可以将其设置为两列表格，其中左侧为值，并使用公式在右侧显示单位，但这样做会导致无法对格式化的值进行计算。
所以基本上，答案是“不”，这是不可能的。 您理论上可以编写一个VBA脚本，每当更改数字时就根据单元格内容自动更改可见内容，但脚本会很庞大，并且会给关闭宏的人员带来严重麻烦。它还需要各种角落的情况，具体取决于您是否希望在某些单元格中以“正常”格式显示数字。因此，虽然理论上可能是可能的，但实际上是不可能的。
如果您使用自定义数字格式，则必须手动输入它们（或使用 VBA），以便它们掩盖单元格中的实际内容。
例如，如果您想显示以下格式对：
1 n 1E-09
1 µ 1E-06
1 m 1E-03
1 1
1 k 1E+03
1 M 1E+06
1 G 1E+09
如果您有0.001，您需要将格式设置为
"1 m" - 这将掩盖数字，因此如果您有0.002，则需要将其设置为"2 m" - 如果您将其更改为0.004，则仍将显示2 m作为结果。这显然不是理想的。您可以将其设置为两列表格，其中左侧为值，并使用公式在右侧显示单位，但这样做会导致无法对格式化的值进行计算。
所以基本上，答案是“不”，这是不可能的。 您理论上可以编写一个VBA脚本，每当更改数字时就根据单元格内容自动更改可见内容，但脚本会很庞大，并且会给关闭宏的人员带来严重麻烦。它还需要各种角落的情况，具体取决于您是否希望在某些单元格中以“正常”格式显示数字。因此，虽然理论上可能是可能的，但实际上是不可能的。
- jmac
1
2谢谢，至少现在我不会再浪费时间在这上面了。 - jost21
1
有可能使用转换表和“match”和“index”函数，尽管这样做比较繁琐。
从这样的转换表中（两列）：
1.E+15 P
1.E+12 T
1.E+09 G
1.E+06 M
1.E+03 k
1.E+00
1.E-03 m
1.E-06 µ
1.E-09 n
1.E-12 p
1.E-15 f
您可以进行以下翻译：
然后您可以执行以下翻译
3.68437E+11 --> 368.44 G
如果您在A列和B列中有转换表，且G1单元格中含有未格式化的数字。
H1
=G1/INDEX(A:A,MATCH(G1,$A:$A,-1)+1)
I1
=INDEX($B:$B,MATCH(G1,$A:$A,-1)+1)
然后适当的数字将显示在H列，前缀/后缀将显示在I列。
这仍然很繁琐，只应用于最终输出，因为从修改后的数字进行的计算必须包括反向翻译。
- Mike
1
刚刚意识到我在转换表中把B（表示十亿）写成了G，然后又在后面写了一个G。这搞乱了顺序。只需删除B，使G为10^9，T为10^12等等...
对于这个愚蠢的错误，我感到非常抱歉。 - Mike
0
我不知道有什么方法可以将数字格式化（您可以将格式化的数字作为任何其他数值进行后续计算），但是如果只是使用SI前缀呈现数字，这里是我使用的公式。 它采用指定单元格中的公式（通常在其旁边，在本例中为E28），将数字进行比例缩放，四舍五入到指定的有效数字位数（在本例中为3），附加适当的SI前缀，然后附加指定的单位（在本例中为“F”表示法拉第）。 这里的优点是公式是自包含的，不需要任何外部参考表。 该公式适用于10^-15到10^12的飞托（femto）至特拉（Tera），但可以轻松扩展以获得更多前缀。
=CONCAT(
ROUND(
IF(E28>1E12, E28/1E12,
IF(E28>1E9, E28/1E9,
IF(E28>1E6, E28/1E6,
IF(E28>1E3, E28/1E3,
IF(E28>1, E28,
IF(E28>1E-3, E28*1E3,
IF(E28>1E-6, E28*1E6,
IF(E28>1E-9, E28*1E9,
IF(E28>1E-12, E28*1E12,
E28*1E15
) ) ) ) ) ) ) ) ),
3 +N("This is the number of significant digits to round to")
-(1+INT(LOG10(ABS(
IF(E28>1E12, E28/1E12,
IF(E28>1E9, E28/1E9,
IF(E28>1E6, E28/1E6,
IF(E28>1E3, E28/1E3,
IF(E28>1, E28,
IF(E28>1E-3, E28*1E3,
IF(E28>1E-6, E28*1E6,
IF(E28>1E-9, E28*1E9,
IF(E28>1E-12, E28*1E12,
E28*1E15
) ) ) ) ) ) ) ) ) ))))
),
IF(E28>1E12, "T",
IF(E28>1E9, "G",
IF(E28>1E6, "M",
IF(E28>1E3, "k",
IF(E28>1, "",
IF(E28>1E-3, "m",
IF(E28>1E-6, "µ",
IF(E28>1E-9, "n",
IF(E28>1E-12, "p",
"f"
) ) ) ) ) ) ) ) ),
"F" +N("This is the unit symbol that will be appended to the end")
)
如果你想要将数字保留到固定的小数位数而不是有效数字，公式会更简单一些：
=CONCAT(
ROUND(
IF(E28>1E12, E28/1E12,
IF(E28>1E9, E28/1E9,
IF(E28>1E6, E28/1E6,
IF(E28>1E3, E28/1E3,
IF(E28>1, E28,
IF(E28>1E-3, E28*1E3,
IF(E28>1E-6, E28*1E6,
IF(E28>1E-9, E28*1E9,
IF(E28>1E-12, E28*1E12,
E28*1E15
) ) ) ) ) ) ) ) ),
3 +N("This is the number of decimal digits to round to")
),
IF(E28>1E12, "T",
IF(E28>1E9, "G",
IF(E28>1E6, "M",
IF(E28>1E3, "k",
IF(E28>1, "",
IF(E28>1E-3, "m",
IF(E28>1E-6, "µ",
IF(E28>1E-9, "n",
IF(E28>1E-12, "p",
"f"
) ) ) ) ) ) ) ) ),
"F" +N("This is the unit symbol that will be appended to the end")
)
请注意，我已将所有缩放常数都写成指数表示法，当您输入公式时，Excel会将其更改为普通数字。通过使用相对单元格引用，很容易将公式复制并粘贴到需要的位置。
该公式可以压缩为一个IF/CONCAT/ROUND语句块，但是像我在这里所做的那样排列，将舍入常数分离成公式中的一个单独点，使其更易于更改。
- ajb
0
这是一个针对Google Sheets的有限答案，使用实际数字格式而不是输出文本的表达式。
从endolith的答案中分离出来，我最终选择了这个：
[>=1E6] #,##0.0,,"M";[>1E3] #,##0.0,"K";0.#####################
它适用于1到<1E16的数字，但无法扩展到超过M的单位。不适用于负数或小数。它受Google Sheets能够解析的条件部分数量的限制。
文档：https://developers.google.com/sheets/api/guides/formats#number_format_patterns
- Anm
0
只需选择您想要包含给定符号的单元格或单元格范围。右键单击该单元格，然后选择“格式单元格”。在左侧选择“数字”格式，在右侧输入小数位等内容。现在沿着左侧的格式选项列表向下滚动，并选择“自定义”。（重要提示：不要在右侧选择任何自定义格式选项。）左键单击“类型：”下方的框，位于自定义格式选项列表上方。（此框显示您当前选择的格式。{如果您选择默认数字格式，则为0.00}您希望保留此格式并添加其他格式。）单击0.00右侧并键入以下内容：“μ”单击“确定”，然后可以像平常一样输入数据。格式化单元格不会影响您输入的值。您可以像平常一样执行所有功能。我附上了一张图片，其中我使用相同的说明将字母和希腊MU符号应用于值，并执行了一些基本计算，而不会妨碍Excel的功能。 Excel中的特殊符号
- Anon2017
0
' Hans Wolfgang Schulze 20190921, cause I always need this and need to write it again cause I forgot where I saved it.
' Paste this into Excel's Macro Editor (F11) and use from any cell.
' Copyleft 2019. Please include original author's name in all derivative works.
'
' Note that the conversions in this code is assuming normal Base 10, and not Binary 1024. Lots of code has to change.
' Currently recognizes numbers like "-123123.123G" or "123E15" with or without actual units.
' Special case of Excel's "-" nothing equals 0.
' Assumes, if exists, that the rightmost character is the SI exponent designation - See expS below.
' Usage: =DSci("45e9k") gives "4.5E12" as an answer.
Const expS = "-qryzafpnum KMGTPEZYRQ" ' https://en.wikipedia.org/wiki/Metric_prefix
Function DSci(inputS As String) As Double
Dim which As Integer
which = InStr(expS, Right(inputS, 1))
whichUnitary = InStr(expS, " ") ' offset into expS for " " unity value
If which = 0 Then
which = InStr("----F-Nµ- k-gt-e", Right(inputS, 1)) ' try alt case and form that aren't obscure ie k=K or m!=M
End If
If which > 0 Then ' has a terminating exponential character. 1 is not found.
If which = 1 Then ' "-"
DSci = 0 ' excel nothing value (0)
Else ' convert only the left side of input
DSci = CDbl(Left(inputS, Len(inputS) - 1)) * 10# ^ ((which - whichUnitary) * 3#) ' fix for Binary K's
End If
Else
DSci = CDbl(inputS) ' convert whole string instead ' special case devide by 1.024 for each 1000 for Binary K's
End If
End Function
' Formats to SI convention 10 ^ (-30 ... +30) and recent suggested expansions
' Usage =Sci(5.531e9, "B") gives string of "5.531GB"
' Significant digits are suggested as 4, can be any positive number.
Function Sci(value As Double, optionalUnit As String, Optional significant As Integer = 4) As String
Dim mant As Double, exp As Double, rank As Integer
rankUnitary = InStr(expS, " ") ' offset into expS for " " unity value
If value = 0 Then exp = 0 Else exp = Log(value) / Log(10#) ' nDigits
mant = value / (10# ^ exp) '
While mant >= 999.9999999999 ' don't want 2000K, rather 2M. Change to 1023.9999999999# for Binary K's
exp = exp + 3#
mant = mant / 1000# ' change to 1024# for binary K's etc.
Wend
rank = Int((exp + 0.0000000000001) / 3#) ' should be >1E-300 or so? Why not? 3 != 3#? More changes for Binary K's ?
mant = mant * 10# ^ (-rank * 3# + exp) ' adjust mantussa after de-ranking. Change?? for Binary K's
If Abs(rank) >= rankUnitary Then ' outside of +/- yY bounds
expChar = "?" ' what do you call it then? Not defined.
Else
expChar = Mid(expS, rank + rankUnitary, 1) ' add SI
End If
Sci = Left(mant, Abs(significant)) ' don't allow negative numbers, pretend they are positive lengths
If Right(Sci, 1) = "." Then Sci = Left(Sci, Len(Sci) - 1) ' lop off right DP
Sci = Sci & " " & expChar & optionalUnit
End Function
- Hans Schulze
4
请您能否添加一些注释，说明这是什么以及如何根据 SO 准则使用它？ - pfabri
抱歉，我不确定您的问题是在问什么。此外，SO是什么意思，您是指SI吗？从注释中看来，这段代码是用于Excel的。 - Hans Schulze
1SO是Stack Overflow :) 通常不支持仅包含代码的答案，如果您查看指南。如果您编辑答案并解释如何使用它，将不胜感激。这是一个宏 - 放置在哪里，如何将其链接到相关工作表。 - pfabri
完成。花费了半小时左右的时间去解决。 - Hans Schulze
网页内容由stack overflow 提供, 点击上面的可以查看英文原文，
原文链接
原文链接