我的公式如下：
{=TRUNC(SUM(IFERROR(LEFT(A1:A600,FIND(" ",A1:A600)-1)*10^(MATCH(RIGHT(A1:A600,2),{"KB","MB","GB","TB","PB","EB","ZB","YB","BB"},0)*3),0))/(10^(INT((LEN(SUM(IFERROR(LEFT(A1:A600,FIND(" ",A1:A600)-1)*10^(MATCH(RIGHT(A1:A600,2),{"KB","MB","GB","TB","PB","EB","ZB","YB","BB"},0)*3),0)))-1)/3)*3)),2)&" "&INDEX({"KB","MB","GB","TB","PB","EB","ZB","YB","BB"},INT((LEN(SUM(IFERROR(LEFT(A1:A600,FIND(" ",A1:A600)-1)*10^(MATCH(RIGHT(A1:A600,2),{"KB","MB","GB","TB","PB","EB","ZB","YB","BB"},0)*3),0)))-1)/3))}
一切都完美地符合“PB”
“EB”给出结果：1024000000000000 MB
“ZB”给出结果：10240000000000000000 MB
“YB”给出结果：1.024E+21 MB
“BB”给出结果：1.024E+24 MB
我猜问题出现是因为它有超过 15 位数字。如何强制 excel 正确显示所有测量单位。
我使用 Excel 2016
答案1
我也经常遇到这种情况。我认为这只是格式问题。选择您的列并将下拉格式从“常规”更改为“数字”。然后确保您的列足够宽以显示所有字符。
刚刚注意到你最后还有“MB”......在这种情况下可能需要尝试“文本”格式以强制它按原样显示并且不对公式结果执行任何后续计算
答案2
MrK 完全正确，这是带有“文本”公式的公式
=TEXT(TRUNC(SUM(IFERROR(LEFT(A1:A600,FIND(" ",A1:A600)-1)*10^(MATCH(RIGHT(A1:A600,2),{"KB","MB","GB","TB","PB","EB","ZB","YB","BB"},0)*3),0))/(10^(INT((LEN(SUM(IFERROR(LEFT(A1:A600,FIND(" ",A1:A600)-1)*10^(MATCH(RIGHT(A1:A600,2),{"KB","MB","GB","TB","PB","EB","ZB","YB","BB"},0)*3),0)))-1)/3)*3)),2),"0")&" "&INDEX({"KB","MB","GB","TB","PB","EB","ZB","YB","BB"},INT((LEN(SUM(IFERROR(LEFT(A1:A600,FIND(" ",A1:A600)-1)*10^(MATCH(RIGHT(A1:A600,2),{"KB","MB","GB","TB","PB","EB","ZB","YB","BB"},0)*3),0)))-1)/3))