根据 OP 评论进行更新

Question 1

因为您说您有很多规则，所以我建议使用单独的工作表来跟踪所有规则。这将使调试更容易。（斯宾塞的建议使用条件格式的方法也行得通，因为它可以让您区分规则。但是，快速筛选显示所有不符合任何规则的条目并不那么容易。）

首先，我编辑了你的数据，让某人真正违反规则：

姓名	唯一身份＃	类型	数量

约翰	969	苹果	20
莎拉	7567	苹果	66
莎拉	7567	橙子	22
莎拉	7567	香蕉	2000
迈克尔	1112	橙子	4
迈克尔	1112	梨	40
乔治	四十二	苹果	9999
乔治	四十二	香蕉	20
乔治	四十二	梨	72
乔治	四十二	橙子	21

莎拉会因为香蕉太多而失败，而乔治也会因为苹果太多而橙子不够而失败。

接下来，我制作了第二个选项卡，最终看起来像这样：（黄色突出显示是手动应用的，但也可以轻松进行条件格式设置）

行标题是手动输入的，其余都是公式。前两列相当简单。A2引入唯一 ID 的排序列表。它还包括列标题和空白（转换为 0）。您可以制作更复杂的公式来删除它们或忽略它们。B2将用于检查所有规则是否都通过。如果您想过滤以查看谁失败了任何规则，您需要过滤此列。更新其范围以包含您拥有的规则列数。A2
：=SORT(UNIQUE(Sheet1!B:B))
B2：=AND(C2:D2)

之后，每条规则都会有一列。每列的公式都是唯一的，并且根据您给出的两个示例，可能会很长。以下是 C2：

=IF(SUMIFS(Sheet1!D:D,Sheet1!B:B,Sheet2!A2,Sheet1!C:C,"Apples")<88,TRUE,SUMIFS(Sheet1!D:D,Sheet1!B:B,Sheet2!A2,Sheet1!C:C,"Oranges")>=22)

和 D2：

=SUMIFS(Sheet1!D:D,Sheet1!B:B,Sheet2!A2,Sheet1!C:C,"Bananas")<=20

继续以这种方式为每条规则添加列。检查每条规则并在过程中进行调试以确保它们正常工作。完成后，请确保更新 B2 中的公式以包含所有规则，然后根据需要复制/粘贴。

这应该可以作为概念证明，并足以让您入门。有几种方法可以改进：

忽略 A2 公式中的空白和列标题
将 B2 公式改为动态的，自动包含每条规则
将所有公式改为溢出公式这样你就不必复制/粘贴了
添加条件格式以突出显示失败
^{_{为那些痴迷于囤积水果的人提供心理帮助}}

如果您想进行这些改进但不知道如何做，请先搜索一下，如果遇到困难，再回来提出新问题。

根据 OP 评论进行更新

看起来您有一长串相互关联的规则。您应该做的第一件事（如果您还没有做的话）是写出每一条规则并确定它们的应用顺序。例如，如果一条规则增加了最大允许值，而另一条规则将最大值设置为新值，那么哪条规则先出现？新的最大值规则会覆盖增加规则吗？增加规则会修改新的最大值吗？

一旦你把这些整理好，就可以用公式来完成这一切。这需要时间，但我敢打赌，这需要很多时间现在确定每个用户是否符合规则。我会选择花很多时间一次，而不是花很多时间多次。（这里有一个相关的西科光盘帮助你判断是否值得花时间，这里还有另一个西科光盘警告您尝试此操作的潜在危险。）

如果我要在 Excel 中解决这个问题，结果将是这样的：

这与上面的基本想法相同，但经过扩展以适应更多规则。我将第一张工作表命名为“数据”，将第二张工作表命名为“规则”。

A2 中的公式相同：=SORT(UNIQUE(Data!B:B))

B2 中的公式被复制到 B2:E8 中的所有单元格（或您需要的行数）。请注意，将$范围标记为固定引用而不是相对引用，因此当您将其复制/粘贴到其他地方时它不会发生变化。

=SUMIFS(Data!$D:$D,Data!$B:$B,$A3,Data!$C:$C,B$2)

之后，我计算每种水果的最大允许值。所引用的范围会因每种水果而异，但思路是一样的。从基本最大值开始，将其更改为该水果“设置新最大值”部分中的最后一个值，然后添加该水果“修改最大值”部分中的任何值。

=IFERROR(LOOKUP(2,1/(J3:L3<>""),J3:L3),"maxA")&" + "&SUM(M3:P3)

^{注意：这看起来有点奇怪，因为我让它返回文本，这样你就能看到它在做什么。如果原来的 maxA 是 50，那么公式将是=IFERROR(LOOKUP(2,1/(J3:L3<>""),J3:L3),50)+SUM(M3:P3)}

关键点：这是我们确定规则应用顺序的地方。在这里，我决定先设置一个新的最大值，然后根据需要进行修改。我还设置了它，以便如果有多个规则可以更改新的最大值，则后面的规则将覆盖前面的规则。例如，如果“P>90”和“O<50”（我编造的）都适用，那么只会应用“O<50”规则，因为它更靠右。

所有规则的公式都必须是自定义的。我编写的公式非常短，因为我采取了额外的步骤来提取“总计”列。例如，以下是“P>90”的公式：

=IF(E3>90,"newMax","")

...这里是“A>30”（For every 2 Apples over 30, Oranges has to go up by 1）：

=ROUNDDOWN(MAX(0,B3-30)/2,0)

您也可以使用相同的逻辑来设置和修改每种水果的最小值。您说过您有很多混乱的规则，无法在这里全部列出。没关系，因为我们不是来为您写一百个公式的。我只是想提出一种格式，您可以将其融入您的解决方案中。

上面我没有提到的一件事是检查事情是否通过的最终步骤。但是，一旦您有了总数、计算出的最大值（应用所有规则后）以及（如果需要）计算出的最小值，这应该非常简单。

Answer

因为您说您有很多规则，所以我建议使用单独的工作表来跟踪所有规则。这将使调试更容易。（斯宾塞的建议使用条件格式的方法也行得通，因为它可以让您区分规则。但是，快速筛选显示所有不符合任何规则的条目并不那么容易。）

首先，我编辑了你的数据，让某人真正违反规则：

姓名	唯一身份＃	类型	数量

约翰	969	苹果	20
莎拉	7567	苹果	66
莎拉	7567	橙子	22
莎拉	7567	香蕉	2000
迈克尔	1112	橙子	4
迈克尔	1112	梨	40
乔治	四十二	苹果	9999
乔治	四十二	香蕉	20
乔治	四十二	梨	72
乔治	四十二	橙子	21

莎拉会因为香蕉太多而失败，而乔治也会因为苹果太多而橙子不够而失败。

接下来，我制作了第二个选项卡，最终看起来像这样：（黄色突出显示是手动应用的，但也可以轻松进行条件格式设置）

行标题是手动输入的，其余都是公式。前两列相当简单。A2引入唯一 ID 的排序列表。它还包括列标题和空白（转换为 0）。您可以制作更复杂的公式来删除它们或忽略它们。B2将用于检查所有规则是否都通过。如果您想过滤以查看谁失败了任何规则，您需要过滤此列。更新其范围以包含您拥有的规则列数。A2
：=SORT(UNIQUE(Sheet1!B:B))
B2：=AND(C2:D2)

之后，每条规则都会有一列。每列的公式都是唯一的，并且根据您给出的两个示例，可能会很长。以下是 C2：

=IF(SUMIFS(Sheet1!D:D,Sheet1!B:B,Sheet2!A2,Sheet1!C:C,"Apples")<88,TRUE,SUMIFS(Sheet1!D:D,Sheet1!B:B,Sheet2!A2,Sheet1!C:C,"Oranges")>=22)

和 D2：

=SUMIFS(Sheet1!D:D,Sheet1!B:B,Sheet2!A2,Sheet1!C:C,"Bananas")<=20

继续以这种方式为每条规则添加列。检查每条规则并在过程中进行调试以确保它们正常工作。完成后，请确保更新 B2 中的公式以包含所有规则，然后根据需要复制/粘贴。

这应该可以作为概念证明，并足以让您入门。有几种方法可以改进：

忽略 A2 公式中的空白和列标题
将 B2 公式改为动态的，自动包含每条规则
将所有公式改为溢出公式这样你就不必复制/粘贴了
添加条件格式以突出显示失败
^{_{为那些痴迷于囤积水果的人提供心理帮助}}

如果您想进行这些改进但不知道如何做，请先搜索一下，如果遇到困难，再回来提出新问题。

根据 OP 评论进行更新

看起来您有一长串相互关联的规则。您应该做的第一件事（如果您还没有做的话）是写出每一条规则并确定它们的应用顺序。例如，如果一条规则增加了最大允许值，而另一条规则将最大值设置为新值，那么哪条规则先出现？新的最大值规则会覆盖增加规则吗？增加规则会修改新的最大值吗？

一旦你把这些整理好，就可以用公式来完成这一切。这需要时间，但我敢打赌，这需要很多时间现在确定每个用户是否符合规则。我会选择花很多时间一次，而不是花很多时间多次。（这里有一个相关的西科光盘帮助你判断是否值得花时间，这里还有另一个西科光盘警告您尝试此操作的潜在危险。）

如果我要在 Excel 中解决这个问题，结果将是这样的：

这与上面的基本想法相同，但经过扩展以适应更多规则。我将第一张工作表命名为“数据”，将第二张工作表命名为“规则”。

A2 中的公式相同：=SORT(UNIQUE(Data!B:B))

B2 中的公式被复制到 B2:E8 中的所有单元格（或您需要的行数）。请注意，将$范围标记为固定引用而不是相对引用，因此当您将其复制/粘贴到其他地方时它不会发生变化。

=SUMIFS(Data!$D:$D,Data!$B:$B,$A3,Data!$C:$C,B$2)

之后，我计算每种水果的最大允许值。所引用的范围会因每种水果而异，但思路是一样的。从基本最大值开始，将其更改为该水果“设置新最大值”部分中的最后一个值，然后添加该水果“修改最大值”部分中的任何值。

=IFERROR(LOOKUP(2,1/(J3:L3<>""),J3:L3),"maxA")&" + "&SUM(M3:P3)

^{注意：这看起来有点奇怪，因为我让它返回文本，这样你就能看到它在做什么。如果原来的 maxA 是 50，那么公式将是=IFERROR(LOOKUP(2,1/(J3:L3<>""),J3:L3),50)+SUM(M3:P3)}

关键点：这是我们确定规则应用顺序的地方。在这里，我决定先设置一个新的最大值，然后根据需要进行修改。我还设置了它，以便如果有多个规则可以更改新的最大值，则后面的规则将覆盖前面的规则。例如，如果“P>90”和“O<50”（我编造的）都适用，那么只会应用“O<50”规则，因为它更靠右。

所有规则的公式都必须是自定义的。我编写的公式非常短，因为我采取了额外的步骤来提取“总计”列。例如，以下是“P>90”的公式：

=IF(E3>90,"newMax","")

...这里是“A>30”（For every 2 Apples over 30, Oranges has to go up by 1）：

=ROUNDDOWN(MAX(0,B3-30)/2,0)

您也可以使用相同的逻辑来设置和修改每种水果的最小值。您说过您有很多混乱的规则，无法在这里全部列出。没关系，因为我们不是来为您写一百个公式的。我只是想提出一种格式，您可以将其融入您的解决方案中。

上面我没有提到的一件事是检查事情是否通过的最终步骤。但是，一旦您有了总数、计算出的最大值（应用所有规则后）以及（如果需要）计算出的最小值，这应该非常简单。

Question 2

辅助表是实现此目的的一种方法，但是还有两种更简单且更快捷的方法。

条件格式。如果违反任何规则，则将单元格突出显示为红色，或者如果满足所有规则，则将单元格突出显示为绿色。这样，您可以分别添加不同的规则，然后只需查看颜色并确定要批准或拒绝哪些规则。
使用相当复杂的IF公式，在单独的列中返回“同意”或“拒绝”。这写起来很复杂，但仍然比辅助表方法更快。

我不知道您对公式的熟练程度如何，但上述任何一种情况都需要您使用嵌套的各种公式，在输出“批准”或“拒绝”之前检查所有可能性。对于您提到的例子：

如果一个人有 88 个苹果，那么他也一定有 22 个橘子。

检查这一点的公式是（在工作表的新列中，从第 3 行开始）：

=IF(AND(C3="Apples", D3=88), IF(SUMIFS(D:D, C:C, "Oranges", A:A, A3)=22, "Approve", "Reject"))

您可以通过谷歌搜索找到有关各个公式的所有信息。如果您需要有关如何组合公式的帮助，请找出您需要公式执行的确切操作，然后尝试自己做，然后将其作为问题发布在这里或 StackOverflow（如果您无法自己做的话）。

Answer

辅助表是实现此目的的一种方法，但是还有两种更简单且更快捷的方法。

条件格式。如果违反任何规则，则将单元格突出显示为红色，或者如果满足所有规则，则将单元格突出显示为绿色。这样，您可以分别添加不同的规则，然后只需查看颜色并确定要批准或拒绝哪些规则。
使用相当复杂的IF公式，在单独的列中返回“同意”或“拒绝”。这写起来很复杂，但仍然比辅助表方法更快。

我不知道您对公式的熟练程度如何，但上述任何一种情况都需要您使用嵌套的各种公式，在输出“批准”或“拒绝”之前检查所有可能性。对于您提到的例子：

如果一个人有 88 个苹果，那么他也一定有 22 个橘子。

检查这一点的公式是（在工作表的新列中，从第 3 行开始）：

=IF(AND(C3="Apples", D3=88), IF(SUMIFS(D:D, C:C, "Oranges", A:A, A3)=22, "Approve", "Reject"))

您可以通过谷歌搜索找到有关各个公式的所有信息。如果您需要有关如何组合公式的帮助，请找出您需要公式执行的确切操作，然后尝试自己做，然后将其作为问题发布在这里或 StackOverflow（如果您无法自己做的话）。

Question 3

我回答，因为我已经理解了，这可能不是理想的答案，但它可能会提供一些线索来找到理想的解决方案。我以以下 34 个观察结果作为数据。数据表位于 A1:D35

此后，在数据表下方，我创建了双向表；A39：E43 来对数据进行分类。

第一个单元格 B40 中 George 和 Apples 的公式如下：

=SUMIFS($D$2:$D$35,$A$2:$A$35,$A40,$C$2:$C$35,B$39)

Sumifs 根据 A2:A35 中的名称和类型 C2:C35 添加 D2:D35 列的数量这是一个多重 sumifs 公式。使用 $ 符号是为了对所有内容使用一个公式。此公式根据行和列变量将数据表相加。

一旦在第一个单元格中输入公式，您将得到值 114。现在只需将此公式复制到双向表的其余单元格中。

获得以下结果。

此后获得以下条件表：I39 到 L46：输出为：

给出的条件是苹果最低 65，香蕉最低 100，橙子最低 70，梨最低 30。这些都输入在 I45:L45 中

这表明：乔治满足所有条件，约翰满足苹果和梨的条件，但香蕉和橙子的条件不及格。迈克尔满足苹果和橙子的条件，但香蕉和梨的条件较差。莎拉不满足苹果的最低条件，其余条件均符合。

测试条件的公式对于单元格 I40 来说；对于 George 和 Apples 来说

I40 = IF(B40>$I$45,1,0) 类似所有公式。

可以改变条件值来查看新的结果，从而做出最终的决定。

如果有疑问的话，我将提供工作表 Outlook 来帮助您理解公式。

谢谢。

Answer

我回答，因为我已经理解了，这可能不是理想的答案，但它可能会提供一些线索来找到理想的解决方案。我以以下 34 个观察结果作为数据。数据表位于 A1:D35

此后，在数据表下方，我创建了双向表；A39：E43 来对数据进行分类。

第一个单元格 B40 中 George 和 Apples 的公式如下：

=SUMIFS($D$2:$D$35,$A$2:$A$35,$A40,$C$2:$C$35,B$39)

Sumifs 根据 A2:A35 中的名称和类型 C2:C35 添加 D2:D35 列的数量这是一个多重 sumifs 公式。使用 $ 符号是为了对所有内容使用一个公式。此公式根据行和列变量将数据表相加。

一旦在第一个单元格中输入公式，您将得到值 114。现在只需将此公式复制到双向表的其余单元格中。

获得以下结果。

此后获得以下条件表：I39 到 L46：输出为：

给出的条件是苹果最低 65，香蕉最低 100，橙子最低 70，梨最低 30。这些都输入在 I45:L45 中

这表明：乔治满足所有条件，约翰满足苹果和梨的条件，但香蕉和橙子的条件不及格。迈克尔满足苹果和橙子的条件，但香蕉和梨的条件较差。莎拉不满足苹果的最低条件，其余条件均符合。

测试条件的公式对于单元格 I40 来说；对于 George 和 Apples 来说

I40 = IF(B40>$I$45,1,0) 类似所有公式。

可以改变条件值来查看新的结果，从而做出最终的决定。

如果有疑问的话，我将提供工作表 Outlook 来帮助您理解公式。

谢谢。

Question 4

在编辑模式下；格式发生变化，因此很难理解。：

真值表如下：

由此;

如果两个苹果都 > 65，和香蕉 > 100 ：只有乔治有资格，如果任何一个苹果状况或者香蕉条件得到满足，则四个香蕉均符合资格。

如果苹果必须> 65和橙子必须 > 70 ；那么 George 和 Michael 都符合资格，另外两个人 John 和 Sarah 不符合资格。

如果满足任意两个条件：苹果和香蕉、A 和橙子、A 和梨、香蕉和橙子、B 和梨、橙子和梨；则所有 4 个均符合条件。

如果满足任意三个条件：ABO、ABP、AOP、BOP，那么只有 George 和 Sarah 两个才有资格。

谢谢。

Answer

在编辑模式下；格式发生变化，因此很难理解。：

真值表如下：

由此;

如果两个苹果都 > 65，和香蕉 > 100 ：只有乔治有资格，如果任何一个苹果状况或者香蕉条件得到满足，则四个香蕉均符合资格。

如果苹果必须> 65和橙子必须 > 70 ；那么 George 和 Michael 都符合资格，另外两个人 John 和 Sarah 不符合资格。

如果满足任意两个条件：苹果和香蕉、A 和橙子、A 和梨、香蕉和橙子、B 和梨、橙子和梨；则所有 4 个均符合条件。

如果满足任意三个条件：ABO、ABP、AOP、BOP，那么只有 George 和 Sarah 两个才有资格。

谢谢。

根据 OP 评论进行更新

答案1

根据 OP 评论进行更新

答案2

答案3

答案4

相关内容