在Excel中将KG，MB，GB，TB转换为GB的公式 [英] formula to convert KG,MB,GB,TB to GB in excel

问题描述

我正在尝试转换Excel工作表中存在的数据，其中一些以GB，MB，TB，KB为单位.我想将所有内容都转换为GB

这是我使用的公式

  = IF(ISERROR(FIND("GB"，H2))= FALSE，VALUE(LEFT(H2，FIND("GB"，H2)-1)* 1)，IF(ISERROR(FIND("TB"，H2))= FALSE，VALUE(LEFT(H2，FIND("TB"，H2)-1)* 1024)，VALUE(LEFT(H2，FIND("MB"，H2)-1)/1024)，VALUE(LEFT(H2，FIND("KB"，H2)-1)/1048576))) 

我收到太多参数错误

解决方案

使用此数组公式:

  = LEFT(A1，MIN(IFERROR(FIND({"KB"，"MB"，"GB"，"TB"}，A1)，1E + 99))-1)/INDEX({1048576,1024,1,0.0009765625}，MATCH(RIGHT(A1,2)，{"KB"，"MB"，"GB"，"TB"}，0))&"GB" 

作为数组公式，需要使用Ctrl-Shift-Enter而不是enter进行确认.如果操作正确，则excel会将 {} 放在公式周围.

---

如果您的字符串始终以大小结尾，那么您可以使用以下常规公式:

  = LEFT(A1，LEN(A1)-2)/INDEX({1048576,1024,1,0.0009765625}，MATCH(RIGHT(A1,2)，{"KB"，"MB"，"GB，" TB}，0))&"GB" 

i am tryinng to convert the data present in an excel sheet, some in GB, MB, TB, KB. I want to convert everything into GB

here is the formula i use

=IF(ISERROR(FIND("GB",H2))=FALSE,VALUE(LEFT(H2,FIND("GB",H2)-1)*1),IF(ISERROR(FIND("TB",H2))=FALSE,VALUE(LEFT(H2,FIND("TB",H2)-1)*1024),VALUE(LEFT(H2,FIND("MB",H2)-1)/1024),VALUE(LEFT(H2,FIND("KB",H2)-1)/1048576)))

I get an error too many arguments

解决方案

Use this array formula:

=LEFT(A1,MIN(IFERROR(FIND({"KB","MB","GB","TB"},A1),1E+99))-1)/INDEX({1048576,1024,1,0.0009765625},MATCH(RIGHT(A1,2),{"KB","MB","GB","TB"},0)) & "GB"

Being an array formula it needs to be confirmed with Ctrl-Shift-Enter instead of enter. If done correctly then excel will put {} around the formula.

---

If your string always ends with the size then you can use this normal formula:

=LEFT(A1,LEN(A1)-2)/INDEX({1048576,1024,1,0.0009765625},MATCH(RIGHT(A1,2),{"KB","MB","GB","TB"},0)) & "GB"

这篇关于在Excel中将KG，MB，GB，TB转换为GB的公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！