☰

如何将两列中具有相似数据的行计为 1 个数据条目？

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 worksheet-function pivot-table
如何将两列中具有相似数据的行计为 1 个数据条目？

输入的 SPC 程序测试数据无法执行合规性验证。以下是导出到 excel 的数据图像。如果日期和时间完全匹配，则视为 1 次完成的测试。行数将根据未显示的列（收集的数据）而变化。我在那些应该算作 1 次完成的测试的行周围加了一个框。

我希望能够使用数据透视表来显示每次班次开始时完成的测试数量（而不是行数），以便我可以创建趋势图来显示合规性（合规性要求在计算字段中）。我正在寻找有关实现此目标的最佳方法的建议（即带有公式的辅助列、数据透视计算字段等），以便我可以每周快速导出/导入数据并将图表发送给我的团队。

在此处输入图片描述

答案1

我将添加另一列（示例中为 N），用于标记完整测试的最后一行。我们将此列称为“唯一测试”。公式如下：

=IF(OR(G2<>G3, H2<>H3), TRUE, FALSE)

现在，如果您过滤 N 列以仅显示TRUE值，您将获得已完成测试的列表，每个测试一行。

无论使用哪种过滤器，您都可以创建一个数据透视表来计算每个班次的测试数量，如下所示：

  1. 选择 M 和 N 列。
  2. 插入数据透视表（在Insert选项卡上）。
  3. 将ShiftStart字段拖至行标签区域。
  4. 将Unique Test字段拖到值区域（Count of Unique Test作为值获取）。

  5. 在选项卡PivotTable Tools下Options，单击Insert Slicer，选择Unique Test，然后单击TRUE切片器。

    在此处输入图片描述

    在此处输入图片描述

答案2

如上所述，数据透视表在 2013 年及以后包含此功能。

结果

在此处输入图片描述 在此处输入图片描述

方法

假设您的数据从第 2 行开始，计算最有效且最容易理解的方式似乎是：

=IF(COUNTIFS($G$2:G2,G2,$H$2:H2,H2)>1,0,1)

对于 x=2 至 n，计算 Gx=Gn AND Hx=Hn 的次数

countifs 部分计算 G 和 H 的唯一出现次数。使用 IF，这将为重复值返回 0，为 g 和 h 组合的第一次出现返回 1。

将其拖到表格底部，然后将此列的总和添加到最终的数据透视表中，这样您将获得唯一时间和日期的数量。

这也适用于非连续数据，其中重复值不是连续的（尽管这似乎不适用于您的数据）。

对于不包含以下内容的 Excel 版本=countifs：

SUMPRODUCT(($G$2:$G3=G3)*($H$3:$H3=H3))

注意 $s。此公式将从第 2 行应用到当前行，向下扩展选择范围。这将对从 x=2 到 x=n 的每一行 x 的乘积TRUE和TRUE（1 和 1）求和，其中 Gx=Gn 和 Hx=Hn。当前行（第 n 行）被计算在内。如果在当前行之前找到组合，则公式将计算它（通过和TRUE*TRUE）。

简而言之，它计算 G 和 H 的唯一出现次数。因此 PUT

N2 = IF(SUMPRODUCT(($G$2:$G2=G2)*($H$2:$H2=H2))>1,0,1)

相关内容