☰

检查在一个 Excel 范围内找到的值，但不检查另一个范围内找到的值

tag-icon • tag-icon worksheet-function microsoft-excel-2013
检查在一个 Excel 范围内找到的值，但不检查另一个范围内找到的值

我有两个命名范围，用户输入Profiles（一列的垂直范围）和预先确定的ProfileNames（一行的水平范围）。我的工具中有一个数据验证步骤，我想检查输入的任何值Profiles是否不是找到ProfileNames。我试图找到一种用一个公式来做到这一点的方法，但我似乎被难住了。

目前，这是这样实现的：中的每个单元ProfileNames格都有自己的名称（Profile1、Profile2等）。然后使用以下公式：

COUNTIFS(Profiles,"<>"&Profile1, Profiles,"<>"&Profile2, ...)

任何大于 0 的值都会触发弹出窗口。我想跳过这个，因为最终将有 200 多个配置文件需要检查，而这个公式太难用了。我需要一个公式来替换上面的公式，它将检测出 中与Profiles中至少一个单元格不匹配的任何单元格ProfileNames。

样本数据：

Cooling | Heating | Cooking 1 .5 .75

上面第一行 (Cooling:Cooking) 是ProfileNames“冷却”是Profile1，加热是Profile2，等等。此列是Profiles：

Cooling Cooling Cooking Heating Heating

我希望避免使用Profile1等，因为其中会有 200 多个，并且现在使用的公式可能会扩展到超过最大字符限制。

答案1

您可以使用数组公式来检查。输入

=SUM((Profiles = ProfileNames) * 1)

进入单元格，但不要使用 Enter 键，而是按 Control-Shift-Enter 键。结果是与Profiles中的条目匹配的中的条目数ProfileNames。因此，您知道，如果 中有 5 个条目，Profiles并且总和为 5，则所有条目都包含在 中ProfileNames。如果总和小于 5，则 中的条目之一Profiles不正确。

解释：创建一个由s 和sProfiles = ProfileNames组成的矩阵，对应于检查 中的每一行与 中的每一列。将该矩阵转换为数字矩阵，然后对它们求和。TRUEFALSEProfilesProfileNames* 1SUM

相关内容