Excel中基于原物料转换系数计算新物料数量的公式咨询

需求说明 #

已知原物料的数量、原单位、原单位转最小单位（EA）的规则字符串（OrigUOMString），以及新单位转最小单位的规则字符串（NewUOMString）、目标单位，需通过两步计算得到转换后的新数量（NewQty）：

1. 先将原数量转换为最小计量单位（EA）
2. 再将EA数量转换为目标单位的数量

适用场景与示例 #

示例数据：

公式实现（Excel 365及以上版本） #

假设数据对应列：

• 原数量：A列
• 原单位：B列
• OrigUOMString：C列
• NewUOMString：D列
• 目标单位：E列

在F2（NewQty列）输入以下公式后下拉：

=(A2 * VALUE(TEXTBEFORE(TEXTAFTER(C2, "1 " & B2 & "="), " EA"))) / VALUE(TEXTBEFORE(TEXTAFTER(D2, "1 " & E2 & "="), " EA"))

公式逻辑拆解

1. 提取原单位转EA的系数：

   • TEXTAFTER(C2, "1 " & B2 & "=")：定位到原单位对应的转换数值起始位置
   • TEXTBEFORE(..., " EA")：提取该位置到" EA"之间的数字文本，用VALUE()转为数值
   • 乘以原数量得到总EA数

2. 提取目标单位转EA的系数：

   • 用同样逻辑从NewUOMString中提取目标单位对应的EA转换系数
   • 总EA数除以该系数，得到目标单位的数量

兼容旧版Excel的公式（无TEXTBEFORE/TEXTAFTER函数） #

如果使用Excel 2019及更早版本，可改用以下公式：

=(A2 * VALUE(MID(SUBSTITUTE(C2, "1 " & B2 & "=", ""), 1, FIND(" EA", SUBSTITUTE(C2, "1 " & B2 & "=", "")) - 1))) / VALUE(MID(SUBSTITUTE(D2, "1 " & E2 & "=", ""), 1, FIND(" EA", SUBSTITUTE(D2, "1 " & E2 & "=", "")) - 1))

逻辑说明

通过SUBSTITUTE替换掉单位标识，再用MID+FIND定位并提取转换系数的数字部分，后续计算逻辑与新版公式一致。

内容的提问来源于stack exchange，提问作者Ryan Kapper