☰

多个案例的下一个可用号码

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2016
多个案例的下一个可用号码

我有一张包含两列的 Excel 工作表：A 列和 B 列。

A列包含所有现有的产品代码。

B 列包含所有现有和新产品类别。产品类别为两位数（根据需要带前导零）。（现有产品的产品类别实际上与本问题无关。）

旧产品代码由两部分组成：两个字母和六位数字。字母始终为“ER”，而数字则是随机选取的。因此，现有产品代码介于 ER000001 和 ER999999 之间。

新产品代码将遵循新模式

“ER”+产品类别+四位数字

产品类别和四位数字均按要求带有前导零。例如，如果产品类别为 14，则此类别中的所有新产品代码应介于 ER140001 和 ER149999 之间。

我需要一个公式，该公式将根据 B 列中的产品类别创建新的产品代码，但不使用 A 列中上述任何旧代码。

现在，A 列有 1100 种产品，B 列中有各自的类别，现在我们添加了另外 500 行，其中 B 列填充了类别，但这些行的 A 列仍然是空的。

我正在思考使用参考样式的公式。

If(RC2=R2C2:R[-1]C2,MID(R2C:R[-1]C,3,6))

获取类别中的产品数组

然后使用公式row(indirect("$1:$9999"))结合min(IF(IFNA(match(

检查某个值是否被使用...我有点迷茫。

最好的情况是从可用的左侧随机取数，但顺序数也行。不过，知道如何做到这两点也没什么坏处。

         AB
（1）ER044747 05
（2）ER044748 05
（三）ER044749 05
⑷ ER044750 05
⑸ ER050009 05
⑹ ER069317 18
⑺ ER069318 18
⑻ ER420001 17
⑼ ER031134 17
⑽17
⑾ 17
⑿ 05
⒀ 22
⒁ 22
⒂42
05 产品（第 12 行）可取 ER050001 和 ER059999 之间的任意值，但 ER050009 除外，因为它之前曾使用过（第 5 行）。

第一个 17（第 10 行）将取 ER170001 和 ER179999 之间的任意值。第二个 17（第 11 行）将排除其上方使用的值。

42（第 15 行）可取 ER420002 和 ER429999 之间的任意值。ER420001 不可用，因为它已在第 8 行使用。

所以结果可以像这样： 

         AB
（1）ER044747 05
（2）ER044748 05
（三）ER044749 05
⑷ ER044750 05
⑸ ER050009 05
⑹ ER069317 18
⑺ ER069318 18
⑻ ER420001 17
⑼ ER031134 17
⑽    ER170001    17
⑾    ER170002    17
⑿    ER050001    05
⒀    ER220001    22
⒁    ER220002    22
⒂    ER420002    四十二
单元格 A10:A15 中的粗体斜体值是由公式创建的。

答案1

转到单元格A1101；即 A 列中的第一个空白单元格。（这对应于A10问题中的示例数据。）输入

="ER" & TEXT(B10,"00") & TEXT(MIN(IF(ISERROR(MATCH("ER"&TEXT(B10,"00")&TEXT(ROW($1:$9999),"0000"),A$1:A9,0)),ROW($1:$9999),10000)),"0000")

（用实际行号替换出现的两个数字10 ）并按Ctrl+ Shift+ Enter。如果您已经在使用 R1C1 样式，请使用

="ER" & TEXT(RC[1],"00") & TEXT(MIN(IF(ISERROR(MATCH("ER"&TEXT(RC[1],"00")&TEXT(ROW(R1:R9999),"0000"),R1C:R[-1]C,0)),ROW(R1:R9999),10000)),"0000")

（您不需要做任何调整，因为RC[1]这意味着当前行中的下一个单元格，而不需要使用实际的行号。）然后向下拖动/填充以覆盖 B 列有值的所有行。

这将在每一行生成下一个可用的“ER”+产品类别+嗯嗯值；即以前未使用过的最低可能值（即在 A 列中，当前行上方）。

                电子表格

（单元格 A10:A15 中的粗体值由公式生成；其余的是常量。）

相关内容