☰

根据两个条件复制多个单元格的公式

tag-icon • tag-icon microsoft-excel
根据两个条件复制多个单元格的公式

请帮忙。我被难住了！我不太熟悉 Excel 公式。

我的工作簿有两页。第一页是晨报中包含的参考页。第二页是我们希望操作员填写的数据收集页。

有两个标准可以将信息从数据页带到参考页。

  1. 工作完成了吗？如果答案是“是”，单元格值将为 Y，如果答案是“否”，单元格值将为 N。我只想看到在标有“工作已完成”的列中包含单元格值 N 的行。

  2. 此标准查找值以识别设备。我想要一个公式来识别设备，查看工作是否完成，如果没有完成，则将整行复制到参考表。

可以用公式来实现吗？有人能帮忙吗？

答案1

我将根据几个假设进行思考：

  1. 报告表上有一个单元格 A1，人们可以在其中输入他们感兴趣的设备。
  2. 您的“数据收集”页面中有两列，A 和 B：设备名称和完整性的 y/n
  3. 目前，您只想在报告页面上看到“已完成”的行（可以通过对公式进行细微更改来扩展）。
  4. 报告页面上已完成的行将从第 2 行开始

首先，您需要一种方法来识别在报告选项卡的单元格 A1 中输入的设备是否与数据收集的 A 列中的每一行相匹配，以及 B 列中的工作是否完成。您可以使用数据收集选项卡的 C 列中的“and”公式来执行此操作。

=AND(A1=reference!$A$1, B1='Y')

如果两个条件都满足，则返回 TRUE，如果其中一个或两个条件都不满足，则返回 FALSE。

接下来，我们必须对此进行更深入的了解。我们需要将数据输入表中的 TRUE 行移至报告表。我们不希望报告表在移至的 TRUE 行之间有一堆空白行。否则，我们可以对 TRUE 进行一些 vlookup 或索引/匹配，然后就大功告成了。相反，我们可以使用 RANK 来确定行从数据收集页面移至参考页面的顺序。

为此，我们需要在数据收集表的 D 列中对 TRUE 进行排序，从 1 到 TRUE 行数。由于无法确定某一 TRUE 行的排名高于另一行，因此我们将使用 ROW() 作为行号来确定排名。在 D 列中输入：

=IF(C1=TRUE, ROW(), 0)

所有这些操作都将在满足 C 列中建立的条件时在 D 列中写入行。现在要添加 RANK，请在 E 列中输入：

=RANK(D1,D1:D14)*(D1>0)

这会做两件事。首先，它确定行的排名，从 1 开始，直到前 14 行中“TRUE”行的数量（增加该数量以覆盖所有数据输入行）。其次，*(D1>0)如果 D 列中的值为 0，则在 E 列中放置 0，因为如果它是 FALSE 行，我们甚至不想考虑排名。

现在，您应该在 E 列中根据条件是否为 TRUE 和行号按降序排列了排名值。现在我们需要将这些值拖到报告表上。为此，在报告表中从单元格 A2 开始开始沿列向下编号 1、2、3、4、...。这些将“匹配”到 E 列中数据收集表中的排名。

现在要获取第一列匹配的行，请在报告表的 B 列（从 B1 开始）中输入以下内容：

=INDEX(Sheet2!A:A, MATCH(Sheet1!A2, Sheet2!E:E,0), 1)

这是使用索引函数，它允许您从范围内的特定行和列中提取值。我们的范围是 Sheet2!A:A（数据收集表的 A 列）。我们的行是与我们在报告表的 A 列中输入的数字相匹配的排名（sheet2!E:E）值。我们的列只有 1。如果您不熟悉这一点，请在 Google 中查找“Excel 中的索引匹配”以进一步了解这一点。您可以用相同的方式提取数据收集 B 列。在报告表的 B 列（从 B2 开始）输入以下内容：

=INDEX(Sheet2!B:B, MATCH(Sheet1!A2, Sheet2!E:E,0), 1)

这就是我的简单假设。本质上，您需要三列来确定数据收集选项卡中是否满足您的标准，然后将该数据从 1 到 n 进行排序。并且您需要使用索引匹配（或 vlookup，如果您稍微重新排列一下）将这些排名行拉到您的报告表中。如果您想在数据收集选项卡中带出标记为不完整的行，您可以复制相同的逻辑来对 FALSE 进行排序。

最后，在您理解了这一点之后，请考虑将数据收集选项卡中的 C、D、E 列移至其他选项卡，以便进行数据收集的人不会弄错您的公式。 Index/Match 函数仍将正常工作，并且您的数据收集人员将看不到工作簿内部运作的复杂细节。

相关内容