我有一个工作簿,其中包含已发布的组件序列号。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
() not
CODE()` 因为后者比其他任何东西都更适合 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","")