Excel:动态公式，用于更改单位大小(KB，MB，GB，TB等)...自动 [英] Excel: Dynamic formula for change unit size in KB, MB, GB, TB, etc... Automatically

问题描述

如何将公式校正为对KB，MB，GB，TB等所有单位都是动态的.它完美适用于MB和GB，但不适用于其他单位.我的公式是:

How to correct the formula to be dynamic for all units KB, MB, GB, TB, etc ... It works perfectly for MB and GB but does not work for other units. my formula is:

{=IF(SUM(IF(ISNUMBER(VALUE(LEFT(A1:A10,LEN(A1:A10)-3))+0),VALUE(LEFT(A1:A10,LEN(A1:A10)-3))+0))>=1000,TEXT(SUM(IF(ISNUMBER(VALUE(LEFT(A1:A10,LEN(A1:A10)-3))+0),VALUE(LEFT(A1:A10,LEN(A1:A10)-3))+0))/1024,"0.00")&" GB",TEXT(SUM(IF(ISNUMBER(VALUE(LEFT(A1:A10,LEN(A1:A10)-3))+0),VALUE(LEFT(A1:A10,LEN(A1:A10)-3))+0)),"0.00")&" MB")}

推荐答案

使用情况:

=ROUND(SUMPRODUCT(LEFT(A1:A4,FIND(" ",A1:A4)-1)*10^(MATCH(RIGHT(A1:A4,2),{"KB","MB","GB","TB"},0)*3))/(10^(INT((LEN(SUMPRODUCT(LEFT(A1:A4,FIND(" ",A1:A4)-1)*10^(MATCH(RIGHT(A1:A4,2),{"KB","MB","GB","TB"},0)*3)))-1)/3)*3)),2) & " " & INDEX({"KB","MB","GB","TB"},INT((LEN(SUMPRODUCT(LEFT(A1:A4,FIND(" ",A1:A4)-1)*10^(MATCH(RIGHT(A1:A4,2),{"KB","MB","GB","TB"},0)*3)))-1)/3))

这篇关于Excel:动态公式，用于更改单位大小(KB，MB，GB，TB等)...自动的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！