☰

提取数据并计算

tag-icon • tag-icon microsoft-excel
提取数据并计算

我的 Excel 中有大约 900 行数据，需要解析并计算每行中的特定信息并将其放入另一行中。

示例行如下：

系统 xyz 有 4 x 10TB 的数据分配给客户端。

我需要能够找到 4 x 10TB 并在单独的列中将其计算为 40TB。但是，有些人将其记为 4x10TB 或 4 x 10 TB 或 4 x 10TB。有些数据只有 GB。

是否存在一个公式可以至少提取存储？

答案1

我可能会添加 4 列，以便以后更容易排除故障和执行数学/转换。

  1. 要解析的字符串
  2. 数数
  3. 尺寸
  4. 计量单位（TB 或 GB）

这“要解析的字符串”是我们需要计算的注释的相关部分。正则表达式可以在这里为您提供帮助。

为正则表达式添加用户定义函数，如下所示： https://stackoverflow.com/questions/9744602/how-do-you-execute-a-regular-expression-in-excel

我认为你想要的正则表达式是：

"[0-9 x]*[GT]B"

根据这些字符串（删除所有空格后）将返回如下结果：

BOB HAS 400 x 10   gB --> "400x10gB"
4x10TB IN THE building xx --> "4x10TB"
I have 20TB of space --> "20TB"

因此，为了得到这个“要解析的字符串”，假设你将其放在 B2 中，目标单元格为 A2：

=SUBSTITUTE(udfRegEx(A2,"[0-9 x]*[GT]B")," ","")

数数可以提取（注意，当没有提供倍数时，它填充为 1）：

=IF(ISERROR(FIND("x",B2)),1,VALUE(LEFT(B2,FIND("x",B2)-1)))

尺寸可以使用以下方法提取：

=IF(ISERROR(FIND("x",B2)),VALUE(LEFT(B2,LEN(B2)-2)),VALUE(MID(B2,FIND("x",B2)+1,LEN(B2)-2-FIND("x",B2))))

测量单位（TB 或 GB）具有：

=UPPER(RIGHT(B2,2))

计数和大小都以数字形式返回，因此您应该能够从那里进行乘法和加法......

请注意，单个 VBA 函数可以完成所有这些操作并隐藏大部分复杂性，但我认为最好让 VBA 函数尽可能通用，并在合理的情况下在 Excel 中完成其余操作。

相关内容