我经常会在 Excel 中获得有关文件大小的数据，其中的值可能包括以下内容：
0
1K
850K
7.12K
19.2M
43.1G
1012G
28T
1.02P
（好吧，最后一个情况并不常见，但最终在理论上是可能的）
我经常想找出大的或小的异常值，按大小顺序对它们进行排序，或者使用条件格式来帮助我查看哪些异常值比周围的异常值更大。
我的前两次尝试是：
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"T","000000000000"),"M","000000"),"K","000"),"G","000000000")
=IFERROR(LOG(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"T","000000000000"),"M","000000"),"K","000"),"G","000000000"),10),0)
（日志有效地转换为“数字”，并且 iferror 处理零情况）
虽然 1000 与 1024 相比不够精确，但这对我来说并不重要，因为我只对发现异常值感兴趣，并且第二个方法完美地满足了我的目的，但是当有小数点时它就完全失败了。
我理想情况下是在寻找一些快速而又方便的东西，当我花 5 分钟解释日志或异常报告时，我可以复制/粘贴（或者更好地输入），我从来不打算保存我正在使用的表格，只是提取信息，从中获取信息，然后转到下一个工作。
如果它包含“G”，则突出显示红色，如果它包含“T”，则突出显示紫色，这对我来说几乎足够了，但 1G 和 990G 之间的差异实在太大了。
有什么好的建议吗？
如果它实际上在支持 1024 倍数方面很强大，并且能够像处理 M 一样处理 MB，那么这将是很棒的，但对于我的目的来说不是必要的。
答案1
基本答案：
有一种更直接的方法可以做到这一点，而且更容易扩展以包含更大的文件大小（就好像当您已经以 PB 为单位计算时确实需要这样做一样）。如果您的数据以 开头A1，则公式将是：
=IFERROR(VALUE(A1),VALUE(LEFT(A1,LEN(A1)-1)))*1000^IFERROR(SEARCH(RIGHT(A1),"KMGTPEZY"),0)
这其中有两大部分：
IFERROR(VALUE(A1),VALUE(LEFT(A1,LEN(A1)-1)))
第一个方法通过尝试将文本直接转换为数字来提取字符串的数字部分，如果失败，则删除最右边的字符并重试。只要您的文本始终是有效数字，后跟 0 或 1 个非数字字符，这种方法就会奏效。
1000^IFERROR(SEARCH(RIGHT(A1),"KMGTPEZY"),0)
第二部分允许您根据最后一个字符将提取的数字乘以 10 的某个幂。它通过提取最右边的字符RIGHT(A1)并在列表中查找来实现此SEARCH(~,"KMGTPEZY")目的。如果它在该列表中找不到该字符，则默认为零IFERROR(~,0)。然后它将 1000 的该幂提升1000^~。如果最右边的字符是K，则它将是1000^1 = 1,000。对于G，这将是10^3 = 1,000,000,000。如果值以字节为单位，则最右边的字符将是一个数字，因此不会在列表中，因此，由于IFERROR，它将是1000^0 = 1。
1,000 与 1,024：
需要说明的是，上述公式基于十进制计数（K = 千字节 = 1,000 字节）。如果您使用二进制（K = 千字节 = 1,024 字节），则需要稍微修改后半部分：
=IFERROR(VALUE(A1),VALUE(LEFT(A1,LEN(A1)-1)))*1024^IFERROR(SEARCH(RIGHT(A1),"KMGTPEZY"),0)
处理末尾的“B”：
至于处理KB、MB等，您可以修改对 的引用以A1删除B。 为了不让公式变得更长，我们可以使用该LET()函数将结果保存SUBSTITUTE()为单个字符以供以后参考。
=LET(s,SUBSTITUTE(A1,"B",""),IFERROR(VALUE(s),VALUE(LEFT(s,LEN(s)-1)))*1000^IFERROR(SEARCH(RIGHT(s),"KMGTPEZY"),0))
如果您没有该LET()功能（我认为它仅适用于 Office 365），那么您可以坚持使用非常长的版本 OR（我建议）制作一个辅助列来删除任何 B 实例。
长公式版本：
=IFERROR(VALUE(SUBSTITUTE(A1,"B","")),VALUE(LEFT(SUBSTITUTE(A1,"B",""),LEN(SUBSTITUTE(A1,"B",""))-1)))*1000^(IFERROR(SEARCH(RIGHT(SUBSTITUTE(A1,"B","")),"KMGTPEZY"),0)
辅助列版本：
=IFERROR(VALUE(B1),VALUE(LEFT(B1,LEN(B1)-1)))*1000^(IFERROR(SEARCH(RIGHT(B1),"KMGTPEZY"),0)
