Excel:精确查找两个相似列的匹配程度

Question

我想比较两个有些相似的列，看看它们到底有多相似(重叠率)。我的网格大约是600x600，但这里有一个简短的例子。

        100 101 102 103 104 105
   A    1   1   1   1   1   1
   B    1   1   1   1   1   1
   C    1   1   1   1   1   1
   D        1       1   1   1
   E            1   1   1   
   F                        1
   G                        
   TTL  3   4   4   5   5   5
       100% 75% 75% 80% 80% 80%

我们会说这些是汽车和它们的零件。网格中的区域是二进制是/否，1/空白指示符。汽车#101和#102都有4个零件，但它们之间只有3个相同，因此它们被认为是75%相似。103号汽车和104号汽车是完全相同的，但在所有有5个部件的汽车之间，它们只有80%相似。

如何在大型数据集中获取这些数字？可能有30辆“汽车”，其中有600个“零件”需要比较。我有除百分比外的所有数据。

Answer 1

这里有一个可能的解决方案。创建两个命名区域: 1) models -引用包含数字100、101等的标题单元格

2)部分--这是数据左侧单元格的单列，其中包含数字A、B、.假设它位于包含1的网格的左侧

假设您有两个单元格，例如F15和G15，它们在其他单元格中包含类似于101或102的数字--输入以下公式：

=SUMPRODUCT(OFFSET(parts,0,MATCH(F15,models)),OFFSET(parts,0,MATCH(G15,models)))/SUM(OFFSET(parts,0,MATCH(G15,models)))

这应该计算由F15和G15中的值指定的两列中的行数，这两个值都是1，除以第二列中的1的数量。

Answer 2

如果您对示例中类似'TTL‘这样的帮助器行感到满意，那么只需使用SUMIF：

=SUMIF(**TTLRange**, 3)

您只需将TTLRange替换为包含总计的单元格。

编辑：

您可以在每个现有列之后添加帮助器列，使用示例中的数据，此列可以包含：

=IF(A1<>"", A1=C1, FALSE)

然后，在TTL行下面的另一个辅助器行中，添加以下内容：

=SUMPRODUCT((B1:B7=TRUE)*1)/C8