☰

通过检测条件格式来简化包含大量 IF/AND/OR 语句的公式

tag-icon • tag-icon microsoft-excel worksheet-function sap
通过检测条件格式来简化包含大量 IF/AND/OR 语句的公式

我正在尝试开发一个公式来分析Part Number具有MS与层次结构绑定的多个值的特定材料（对应于一个值）。由于材料主数据中的文档不正确和维护不当，许多使用相关 ERP 的公司都会发生这种情况。正确的值只能从相应的MRPcn和X-Plant值中确定，它们始终保持一致，因为它们基于 SAP 中的客户端级别。

电子表格截图#1

我已经对MRPcn列进行了条件格式化，以便根据图例按颜色对值进行分类。之所以进行条件格式化，是因为有 500 多个唯一MRPcn值。这样，我就可以根据需要使用颜色进行过滤或编写公式。

有 20 个唯一X-Plant值。其中 7 个用于值MS。它们是P2(生产)、N2(工程)、18/ 19/ 4/ 1(过时) 和15(全球运营/服务)。这些也已按条件格式化。请注意，有些值X-Plant不只属于这些类别，但这四个是整个数据集中最相关且最一致的。

我一直在尝试使用//IF语句，并且之前也用它们做过一些高级工作，但由于需要合并大量不同的唯一值，公式最终会变得庞大而笨重。此外，我甚至无法使用少量值创建部分公式。ANDOR

由于的正确值MS实际上取决于值的类别MRPcn和值的类别X-Plant，我希望以某种方式利用单元格的背景颜色（对应于类别）来简化公式。

例如，如果MS零件的值为P2和N2，且MRPcn= C43(生产计划) 和X-Plant= P2，则我们知道正确的MS值为P2。（材料可能具有和这两个截然相反的MS值的原因在于，该零件可能在 NPI 期间作为工程计划零件在 SAP 中启动，但后来转移到制造厂的生产中，而不是在 MM 中维护。）P2N2

以下是更多示例数据显示的一些更正MS：

电子表格截图#2

查看行85:86，部件1301386有两个不同的MS值18和，P2分别表示停产/过时和生产，MRPcn表示工程，以及X-Plant表示停产，因此该部件可能需要为MS（18过时）。

首字母缩略词定义：

  • MM = 物料主数据
  • MRPcn = 物料资源计划控制器
  • MS = 物料状态
  • NPI=新产品介绍。
  • X-Plant MS - 客户级物料状态，识别与供应链规划相关的物料阻塞或状态

答案1

由于已经有条件格式，我可以想到至少三种不同的方法来解决这个问题：

  1. 编写一个 UDF 来检测单元格的条件格式颜色，然后制作一个包含最多十六个嵌套IF(AND(),…)语句或等效语句的公式。
    • 这种方法没有任何实际优势
    • 缺点是使用条件格式很慢，UDF 并不简单，需要手动重新评估条件格式公式，并且所需公式很长，其中嵌入了“规则”，因此很难看到/修改
  2. 制作九个（有些非常）长的定义名称字符串，并编写一个只需要 4 个嵌套IF(…)s 的 公式
    • 这种方法的优点是不需要条件格式或任何额外的单元格
    • 缺点是“规则”隐藏在定义的名称中，很难理解/修改
  3. 使用三个表来定义映射，并使用只需要三个嵌套VLOOKUP(…)函数的 公式
    • 优点是不需要条件格式，规则紧凑，很容易查看/修改
    • 唯一的缺点是需要三张桌子

我将展示如何实现第三种方法。

这是一份测试工作表，展示了所提供的屏幕截图中的数据样本，以及三个必需的表格，其中填写了部分数据（其中一些是虚构的）：

测试电子表格的屏幕截图

MRPcn第一个表包含从值到适当类别的简单映射。

第二个表包含从值到其相应类别的映射X-Plant。如果某个值（例如假设的某个值Z）不属于这四个类别之一，则需要将类别值设置为唯一的值。（我使用了值X-Plant本身。）

第三个表将前两个表中两个类别的“交叉乘积”映射到适当的MS值。这是定义“规则”的地方。交叉乘积只是将Category表 1 的列中的每个唯一值与表 1 的列中的每个唯一值连接起来Category。

请注意，交叉积不需要按任何特定顺序排列。还请注意，对于表 2 中的每个不可分类条目，需要在表 3 中创建四个条目。


最后，如屏幕截图所示，在 中输入以下公式G2：

=VLOOKUP(VLOOKUP(D2,$I:$J,2,FALSE)&VLOOKUP(F2,$L:$M,2,FALSE),$O:$P,2,FALSE)

相关内容