使用 VBA 或函数查看序列号是否已被使用

使用 VBA 或函数查看序列号是否已被使用

我有一个工作簿,其中包含已发布的组件序列号。A 列包含序列号,B 列包含零件号。如果序列号已用于 B 列中输入的零件号,我想在 C 列中进行验证。我最好的猜测是使用 If/AND 函数来检查:

=IF(AND(B2:B100=B2,A2:A100=A2),"已使用","打开")

我的想法是,这将检查 B 列是否与 B2(即 ASD01-1)匹配,然后检查 A 列是否与 A2(即 1)匹配。如果两者都匹配,则会显示语句“已使用”。但是公式不起作用。任何有关此事的帮助都将不胜感激。

答案1

以下是一种方法:

=IF(SUM((FILTER($A$2:$A2,$B$2:$B2=B2)=A2)*1)-1<>0,"Used","Open")

创建FILTER()一个从 UP 范围this row到第一行的列表。因此,如果在第 11 行,范围是 A2:A11 和 B2:B11。换句话说,它将测试其上方的所有内容是否是感兴趣的值。以及当前行。

因此,与此行的序列号列单元格进行比较时,返回的是 TRUE/FALSE。将它们乘以 1 会将它们强制转换为 1 和 0。请注意比较表达式周围的括号,这样您就不会乘以行的比较值!

所以现在您有了 1 和 0,并且可以将SUM()它们取出。由于当前行始终匹配,因此您将始终至少返回 1。因此,由于这是一个问题,请将其减去。

(您可以执行范围操作以仅对顶部较高的一行进行操作,但是……那么您如何处理顶行本身呢?因此,虽然听起来更干净,但实际上确实更干净。)

所以最后,您得到的SUM()要么是 0(序列号可以开放使用),要么是 1(序列号已经使用过一次,不可开放使用)。

请注意,您必须有坚定的实践,即在未先更改有问题的序列号的情况下绝不继续,否则您可能会得到各种“第三”输出SUM():例如 3 和 47,表示这是第四次或第 48 次使用给定零件号的相同序列号。即使这是真的,您仍然会知道这一行需要一个新的序列号,因此它不会给出误报。

嗯,它没有设置为捕捉大写和小写字母的差异。为此,您可以将引用从简单范围更改为UNICODE(simple range or cell address)形式,以便比较 Unicode 值,而不是人类字符。(当您遇到此问题时,您几乎可以随时执行此操作。UNICODE UNICODE() is hugely useful that way. Also, use() notCODE()` 因为后者比其他任何东西都更适合 ASCII,并将高于该设置的值降低到该设置,因此如果您在零件编号中使用任何 Unicode(非 ASCII)字符,您可能会遇到许多不幸的匹配。)

但需要注意的是,无论哪种方式都会给您带来问题,部分 A12nnT8 和部分 A12nNT8 会在开始时在公式中显示相同。

答案2

您不能只将单元格与这样的范围进行比较。您的场景正是 COUNTIFS 存在的原因。

COUNTIFS 将计算 Value1 在 Range1 中出现的次数,同时 Value2 也出现在 Range2 中在相应的位置(即,对于彼此相邻的范围,同一行,但它们不必是邻居,对齐,甚至在同一个方向。如果该计数大于 1,则表示有重复。

就我个人而言,我会将重复项标记为“已使用”或其他什么,但将未使用/单一项留空,以便更容易发现重复项(您可以添加条件格式来实现类似的结果,但为什么要使用 2 个东西,而单独 1 个就可以了?)

直奔主题,小心那些美元符号 - 范围需要保持不变,而 A2、B2 等中的值会随着行向下移动而更新。在 C2 中,输入以下内容,然后向下复制:

=IF(COUNTIFS($A$2:$A$100,$A2,$B$2:$B$100,$B2)>1,"Used","")

相关内容